Introduction: This query will fetch you the below data.
1) Total Number of invoices
2) Total value of invoices (USD)
3) Cash collected (USD)
The input parameter passed is from date and to date.
SQL Query:
SELECT
‘Total Number of invoices’,
COUNT(1) number_of_invoices
FROM
ap_invoices_all aia,
ap_suppliers aps
WHERE
aia.vendor_id = aps.vendor_id
AND aia.creation_date BETWEEN TO_DATE(:from_date_ddmonyy,’DD-MON-YY’) AND TO_DATE(:to_date_ddmonyy,’DD-MON-YY’) + 1 – 1 / ( 24 * 3600 )
AND aia.source ‘GLOBAL_INTERCOMPANY’
–AND CANCELLED_DATE IS NULL
AND nvl(aps.vendor_type_lookup_code,’-1′) ‘EMPLOYEE’
UNION ALL
SELECT
‘Total value of invoices (USD)’,
round(SUM(aia.invoice_amount * nvl( (
SELECT
gld.conversion_rate
FROM
gl_daily_rates gld
WHERE
gld.to_currency = ‘USD’
AND gld.conversion_date = TO_DATE(:from_date_ddmonyy,’DD-MON-YY’)
AND gld.from_currency = aia.invoice_currency_code
AND gld.conversion_type = ‘Corporate’
),1) ),2) total_invoice_amount
FROM
ap_invoices_all aia,
ap_suppliers aps
WHERE
aia.vendor_id = aps.vendor_id
AND aia.creation_date BETWEEN TO_DATE(:from_date_ddmonyy,’DD-MON-YY’) AND TO_DATE(:to_date_ddmonyy,’DD-MON-YY’) + 1 – 1 / ( 24 * 3600 )
AND aia.source ‘GLOBAL_INTERCOMPANY’
–AND CANCELLED_DATE IS NULL
AND nvl(aps.vendor_type_lookup_code,’-1′) ‘EMPLOYEE’
UNION ALL
SELECT
‘Cash collected (USD)’,
round(SUM(acrha.amount * nvl( (
SELECT
gld.conversion_rate
FROM
gl_daily_rates gld
WHERE
gld.to_currency = ‘USD’
AND gld.conversion_date = TO_DATE(:from_date_ddmonyy,’DD-MON-YY’)
AND gld.from_currency = arc.currency_code
AND gld.conversion_type = ‘Corporate’
),1) ),2) total_cash_received_app
FROM
ar_cash_receipt_history_all acrha,
ar_cash_receipts_all arc,
gl_code_combinations gcc
WHERE
arc.cash_receipt_id = acrha.cash_receipt_id
AND arc.creation_date BETWEEN TO_DATE(:from_date_ddmonyy,’DD-MON-YY’) AND TO_DATE(:to_date_ddmonyy,’DD-MON-YY’) + 1 – 1 / ( 24 * 3600 )
AND acrha.status IN (
‘CLEARED’
)
AND acrha.account_code_combination_id = gcc.code_combination_id
AND reversal_gl_posted_date IS NULL;
Recent Posts
