The customer statement will provide the information of customer transactions, happened till as of date. These transactions can be invoice, debit memo, credit memo, receipts, applications, adjustments. This report also shows the closing balance of customer/s till as of date.
This is one of the main report which management demands almost in all the organizations/ companies. We provided the detailed query of customers happened in between the dates. You can refer Customer Opening Query to get customer opening balance.
Detailed Query
SELECT '1' typ,hou.name "Business Unit",
hca.cust_account_id,
rcta.org_id org_id,
hca.ACCOUNT_NUMBER CustomerNumber,
hp.party_name customer_name,
hp.ADDRESS1
|| ','
|| CHR (10)
|| hp.ADDRESS2
|| ','
|| CHR (10)
|| hp.ADDRESS3
address,
hp.city city,
hp.COUNTRY,
UPPER (rcta.INVOICE_CURRENCY_CODE) currency,
rcta.trx_date transactiondate,
rctta.name trantype,
rcta.trx_number transactionnumber,
rcta.doc_sequence_value vouchernumber,
rt.name termname,
rcta.purchase_order ponumber,
rcta.comments description,
rcta.invoice_currency_code innvoicecurrency,
(CASE
WHEN rctta.TYPE IN ('INV', 'DM')
THEN
(SELECT SUM (ABS (NVL (extended_amount, 0)))
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = rcta.customer_trx_id)
ELSE
0
END)
* (NVL (rcta.EXCHANGE_RATE, 1))
debit,
(CASE
WHEN rctta.TYPE IN ('CM')
THEN
(SELECT SUM (ABS (NVL (extended_amount, 0)))
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = rcta.customer_trx_id)
ELSE
0
END)
* (NVL (rcta.EXCHANGE_RATE, 1))
credit,
CASE
WHEN (SELECT COUNT (*)
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = rcta.customer_trx_id
AND gl_posted_date IS NOT NULL) > 0
THEN
'Accounted'
WHEN (SELECT COUNT (*)
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = rcta.customer_trx_id
AND gl_posted_date IS NOT NULL) < 0
THEN
'Not Accounted'
END
transactionstatus,
rcta.CUSTOMER_TRX_ID,
rcta.EXCHANGE_RATE_TYPE,
rcta.EXCHANGE_DATE,
rcta.EXCHANGE_RATE,
(SELECT SUM (NVL (extended_amount, 0))
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = rcta.customer_trx_id)
entered_amount
FROM ra_customer_trx_all rcta,
hz_cust_accounts hca,
hz_parties hp,
ra_cust_trx_types_all rctta,
ra_terms rt,
hr_operating_units hou
WHERE 1 = 1
AND rcta.bill_to_customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND rcta.cust_trx_type_seq_id = rctta.cust_trx_type_seq_id
AND rt.term_id(+) = rcta.term_id
AND hou.organization_id = rcta.org_id
AND rcta.complete_flag = 'Y'
AND EXISTS (SELECT '1'
FROM ra_cust_trx_line_gl_dist_all d
WHERE d.customer_trx_id = rcta.customer_trx_id
AND d.accounting_date BETWEEN NVL (:p_from_date,
TRUNC (d.accounting_date))
AND NVL (:p_to_date,
TRUNC (d.accounting_date))
)
AND rcta.org_id = NVL (:p_org_id, rcta.org_id)
AND hca.account_number = NVL (:p_account_number, hca.account_number)
UNION -- Below Query For Adjusted Invoices
SELECT '3' typ,hou.name "Business Unit",
hca.cust_account_id,
rcta.org_id org_id,
hca.ACCOUNT_NUMBER CustomerNumber,
hp.party_name customer_name,
hp.ADDRESS1
|| ','
|| CHR (10)
|| hp.ADDRESS2
|| ','
|| CHR (10)
|| hp.ADDRESS3
address,
hp.city city,
hp.COUNTRY,
UPPER (rcta.INVOICE_CURRENCY_CODE) currency,
adj.trx_date transactiondate,
'Adjustment' trantype,
adj.trx_number transactionnumber,
adj.doc_sequence_value vouchernumber,
rt.name termname,
rcta.purchase_order ponumber,
rcta.comments description,
rcta.invoice_currency_code innvoicecurrency,
CASE
WHEN NVL (adj.amount, 0) >= 0
THEN
( (ABS (adj.amount)) * (NVL (rcta.EXCHANGE_RATE, 1)))
ELSE
0
END
debit,
CASE
WHEN NVL (adj.amount, 0) < 0
THEN
( (ABS (adj.amount)) * (NVL (rcta.EXCHANGE_RATE, 1)))
ELSE
0
END
credit, --
NULL transactionstatus,
rcta.CUSTOMER_TRX_ID,
rcta.EXCHANGE_RATE_TYPE,
rcta.EXCHANGE_DATE,
rcta.EXCHANGE_RATE,
ABS (adj.amount) entered_amount
FROM ra_customer_trx_all rcta,
hz_cust_accounts hca,
hz_parties hp,
ra_cust_trx_types_all rctta,
ra_terms rt,
hr_operating_units hou,
( SELECT ada.CUSTOMER_TRX_ID,
ada.APPLY_DATE trx_date,
ada.ADJUSTMENT_NUMBER trx_number,
ada.DOC_SEQUENCE_VALUE,
SUM (NVL (ada.AMOUNT, 0)) amount,
ada.gl_date
FROM AR_ADJUSTMENTS_ALL ada
WHERE ada.STATUS = 'A'
GROUP BY ada.CUSTOMER_TRX_ID,
ada.ADJUSTMENT_NUMBER,
ada.APPLY_DATE,
ada.DOC_SEQUENCE_VALUE, ada.gl_date) adj
WHERE 1 = 1
AND rcta.bill_to_customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND rcta.cust_trx_type_seq_id = rctta.cust_trx_type_seq_id
AND rt.term_id(+) = rcta.term_id
AND hou.organization_id = rcta.org_id
AND rcta.complete_flag = 'Y'
AND rcta.CUSTOMER_TRX_ID = adj.CUSTOMER_TRX_ID
AND TRUNC (adj.gl_date) BETWEEN NVL (:p_from_date,
TRUNC (adj.gl_date))
AND NVL (:p_to_date,
TRUNC (adj.gl_date))
AND rcta.org_id = NVL (:p_org_id, rcta.org_id)
AND hca.account_number = NVL (:p_account_number, hca.account_number)
UNION -- Below Query for Receipt Data
SELECT '2' typ,hou.name "Business Unit",
hca.cust_account_id,
acra.org_id org_id,
hca.ACCOUNT_NUMBER CustomerNumber,
hp.party_name customername,
hp.ADDRESS1
|| ','
|| CHR (10)
|| hp.ADDRESS2
|| ','
|| CHR (10)
|| hp.ADDRESS3
address,
hp.city city,
hp.COUNTRY,
UPPER (acra.CURRENCY_CODE) currency,
acra.receipt_date transactiondate,
'Receipts' trantype,
acra.receipt_number transactionnumber,
acra.doc_sequence_value vouchernumber,
NULL termname,
NULL ponumber,
acra.comments description,
acra.currency_code innvoicecurrency,
0 debit,
(NVL (acra.amount, 0) * NVL (acra.EXCHANGE_RATE, 1)) credit,
DECODE (apsa.status, 'OP', 'Unapplied/Partially Applied', 'CL', 'Applied') transactionstatus,
acra.cash_receipt_id,
acra.EXCHANGE_RATE_TYPE,
acra.EXCHANGE_DATE,
acra.EXCHANGE_RATE,
acra.AMOUNT entered_amount
FROM ar_cash_receipts_all acra,
hz_cust_accounts hca,
hz_parties hp,
ar_payment_schedules_all apsa,
hr_operating_units hou
WHERE 1 = 1
AND acra.pay_from_customer = hca.cust_account_id
AND hp.party_id = hca.party_id
AND apsa.cash_receipt_id = acra.cash_receipt_id
AND hou.organization_id = acra.org_id
AND TRUNC (apsa.gl_date) BETWEEN NVL (:p_from_date,
TRUNC (apsa.gl_date))
AND NVL (:p_to_date,
TRUNC (apsa.gl_date))
AND acra.org_id = NVL (:p_org_id, acra.org_id)
AND hca.cust_account_id = NVL (:p_customer_id, hca.cust_account_id)
AND hca.account_number = NVL (:p_account_number, hca.account_number)
UNION -- Below Query for Receipt Data
SELECT '4' typ,hou.name "Business Unit",
hca.cust_account_id,
acra.org_id org_id,
hca.ACCOUNT_NUMBER CustomerNumber,
hp.party_name customername,
hp.ADDRESS1
|| ','
|| CHR (10)
|| hp.ADDRESS2
|| ','
|| CHR (10)
|| hp.ADDRESS3
address,
hp.city city,
hp.COUNTRY,
UPPER (acra.CURRENCY_CODE) currency,
--acra.receipt_date transactiondate,
ara.gl_date transactiondate,
'Refund' trantype,
acra.receipt_number transactionnumber,
acra.doc_sequence_value vouchernumber,
NULL termname,
NULL ponumber,
acra.comments description,
acra.currency_code innvoicecurrency,
(NVL (ara.amount_applied, 0) * NVL (acra.EXCHANGE_RATE, 1)) debit,
0 credit,
'Refund' transactionstatus,
acra.cash_receipt_id,
acra.EXCHANGE_RATE_TYPE,
acra.EXCHANGE_DATE,
acra.EXCHANGE_RATE,
ara.amount_applied entered_amount
FROM ar_cash_receipts_all acra,
hz_cust_accounts hca,
hz_parties hp,
ar_receivable_applications ara,
hr_operating_units hou
WHERE 1 = 1
AND acra.pay_from_customer = hca.cust_account_id
AND hp.party_id = hca.party_id
AND ara.applied_payment_schedule_id IN (-8,-3) -- -8 For refund and -3 for Receipt Write-off
AND ara.cash_receipt_id = acra.cash_receipt_id
AND hou.organization_id = acra.org_id
AND TRUNC (ara.gl_date) BETWEEN NVL (:p_from_date,
TRUNC (ara.gl_date))
AND NVL (:p_to_date,
TRUNC (ara.gl_date))
AND acra.org_id = NVL (:p_org_id, acra.org_id)
AND hca.account_number = NVL (:p_account_number, hca.account_number)
UNION
SELECT '5' typ,hou.name "Business Unit",
hca.cust_account_id,
acra.org_id org_id,
hca.ACCOUNT_NUMBER CustomerNumber,
hp.party_name customername,
hp.ADDRESS1
|| ','
|| CHR (10)
|| hp.ADDRESS2
|| ','
|| CHR (10)
|| hp.ADDRESS3
address,
hp.city city,
hp.COUNTRY,
UPPER (acra.invoice_CURRENCY_CODE) currency,
--acra.receipt_date transactiondate,
ara.gl_date transactiondate,
'Refund' trantype,
acra.trx_number transactionnumber,
acra.doc_sequence_value vouchernumber,
NULL termname,
NULL ponumber,
acra.comments description,
acra.invoice_currency_code innvoicecurrency,
(NVL (ara.amount_applied, 0) * NVL (acra.EXCHANGE_RATE, 1)) debit,
0 credit,
'Refund' transactionstatus,
acra.customer_trx_id,
acra.EXCHANGE_RATE_TYPE,
acra.EXCHANGE_DATE,
acra.EXCHANGE_RATE,
ara.amount_applied entered_amount
FROM ra_customer_trx_all acra,
hz_cust_accounts hca,
hz_parties hp,
ar_receivable_applications ara,
hr_operating_units hou
WHERE 1 = 1
AND acra.bill_to_customer_id = hca.cust_account_id
AND hp.party_id = hca.party_id
AND ara.applied_payment_schedule_id IN (-8,-3) -- -8 For refund and -3 for Receipt Write-off
AND ara.customer_trx_id = acra.customer_trx_id
AND hou.organization_id = acra.org_id
AND ara.display = 'Y'
AND TRUNC (ara.gl_date) BETWEEN NVL (:p_from_date,
TRUNC (ara.gl_date))
AND NVL (:p_to_date,
TRUNC (ara.gl_date))
AND acra.org_id = NVL (:p_org_id, acra.org_id)
AND hca.account_number = NVL (:p_account_number, hca.account_number)
UNION ALL -- Below Query for Receipt Data
SELECT '6' typ,hou.name "Business Unit",
hca.cust_account_id,
acra.org_id org_id,
hca.ACCOUNT_NUMBER CustomerNumber,
hp.party_name customername,
hp.ADDRESS1
|| ','
|| CHR (10)
|| hp.ADDRESS2
|| ','
|| CHR (10)
|| hp.ADDRESS3
address,
hp.city city,
hp.COUNTRY,
UPPER (acra.CURRENCY_CODE) currency,
acrh.gl_date transactiondate,
--acra.receipt_date transactiondate,
'Receipts' trantype,
acra.receipt_number transactionnumber,
acra.doc_sequence_value vouchernumber,
NULL termname,
NULL ponumber,
acra.comments description,
acra.currency_code innvoicecurrency,
(NVL (acra.amount, 0) * NVL (acra.EXCHANGE_RATE, 1)) debit,
0 credit,
'Reversed' transactionstatus,
acra.cash_receipt_id,
acra.EXCHANGE_RATE_TYPE,
acra.EXCHANGE_DATE,
acra.EXCHANGE_RATE,
acra.AMOUNT entered_amount
FROM ar_cash_receipts_all acra,
hz_cust_accounts hca,
hz_parties hp,
ar_payment_schedules_all apsa,
hr_operating_units hou,
ar_cash_receipt_history_all acrh
WHERE 1 = 1
AND acra.pay_from_customer = hca.cust_account_id
AND hp.party_id = hca.party_id
AND apsa.cash_receipt_id = acra.cash_receipt_id
AND hou.organization_id = acra.org_id
AND TRUNC (acrh.gl_date) BETWEEN NVL (:p_from_date,
TRUNC (acrh.gl_date))
AND NVL (:p_to_date,
TRUNC (acrh.gl_date))
AND acra.org_id = NVL (:p_org_id, acra.org_id)
AND hca.account_number = NVL (:p_account_number, hca.account_number)
AND acrh.status = 'REVERSED'
AND acra.cash_receipt_id = acrh.cash_receipt_id
AND EXISTS (SELECT acrh1.cash_receipt_history_id
FROM ar_cash_receipt_history_all acrh1
WHERE acra.cash_receipt_id = acrh1.cash_receipt_id
AND acrh1.status = 'REVERSED'
AND acrh1.gl_date <= NVL (:p_to_date,TRUNC (acrh1.gl_date))
)
ORDER BY transactiondate, typ
hca.cust_account_id,
rcta.org_id org_id,
hca.ACCOUNT_NUMBER CustomerNumber,
hp.party_name customer_name,
hp.ADDRESS1
|| ','
|| CHR (10)
|| hp.ADDRESS2
|| ','
|| CHR (10)
|| hp.ADDRESS3
address,
hp.city city,
hp.COUNTRY,
UPPER (rcta.INVOICE_CURRENCY_CODE) currency,
rcta.trx_date transactiondate,
rctta.name trantype,
rcta.trx_number transactionnumber,
rcta.doc_sequence_value vouchernumber,
rt.name termname,
rcta.purchase_order ponumber,
rcta.comments description,
rcta.invoice_currency_code innvoicecurrency,
(CASE
WHEN rctta.TYPE IN ('INV', 'DM')
THEN
(SELECT SUM (ABS (NVL (extended_amount, 0)))
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = rcta.customer_trx_id)
ELSE
0
END)
* (NVL (rcta.EXCHANGE_RATE, 1))
debit,
(CASE
WHEN rctta.TYPE IN ('CM')
THEN
(SELECT SUM (ABS (NVL (extended_amount, 0)))
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = rcta.customer_trx_id)
ELSE
0
END)
* (NVL (rcta.EXCHANGE_RATE, 1))
credit,
CASE
WHEN (SELECT COUNT (*)
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = rcta.customer_trx_id
AND gl_posted_date IS NOT NULL) > 0
THEN
'Accounted'
WHEN (SELECT COUNT (*)
FROM ra_cust_trx_line_gl_dist_all
WHERE customer_trx_id = rcta.customer_trx_id
AND gl_posted_date IS NOT NULL) < 0
THEN
'Not Accounted'
END
transactionstatus,
rcta.CUSTOMER_TRX_ID,
rcta.EXCHANGE_RATE_TYPE,
rcta.EXCHANGE_DATE,
rcta.EXCHANGE_RATE,
(SELECT SUM (NVL (extended_amount, 0))
FROM ra_customer_trx_lines_all
WHERE customer_trx_id = rcta.customer_trx_id)
entered_amount
FROM ra_customer_trx_all rcta,
hz_cust_accounts hca,
hz_parties hp,
ra_cust_trx_types_all rctta,
ra_terms rt,
hr_operating_units hou
WHERE 1 = 1
AND rcta.bill_to_customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND rcta.cust_trx_type_seq_id = rctta.cust_trx_type_seq_id
AND rt.term_id(+) = rcta.term_id
AND hou.organization_id = rcta.org_id
AND rcta.complete_flag = 'Y'
AND EXISTS (SELECT '1'
FROM ra_cust_trx_line_gl_dist_all d
WHERE d.customer_trx_id = rcta.customer_trx_id
AND d.accounting_date BETWEEN NVL (:p_from_date,
TRUNC (d.accounting_date))
AND NVL (:p_to_date,
TRUNC (d.accounting_date))
)
AND rcta.org_id = NVL (:p_org_id, rcta.org_id)
AND hca.account_number = NVL (:p_account_number, hca.account_number)
UNION -- Below Query For Adjusted Invoices
SELECT '3' typ,hou.name "Business Unit",
hca.cust_account_id,
rcta.org_id org_id,
hca.ACCOUNT_NUMBER CustomerNumber,
hp.party_name customer_name,
hp.ADDRESS1
|| ','
|| CHR (10)
|| hp.ADDRESS2
|| ','
|| CHR (10)
|| hp.ADDRESS3
address,
hp.city city,
hp.COUNTRY,
UPPER (rcta.INVOICE_CURRENCY_CODE) currency,
adj.trx_date transactiondate,
'Adjustment' trantype,
adj.trx_number transactionnumber,
adj.doc_sequence_value vouchernumber,
rt.name termname,
rcta.purchase_order ponumber,
rcta.comments description,
rcta.invoice_currency_code innvoicecurrency,
CASE
WHEN NVL (adj.amount, 0) >= 0
THEN
( (ABS (adj.amount)) * (NVL (rcta.EXCHANGE_RATE, 1)))
ELSE
0
END
debit,
CASE
WHEN NVL (adj.amount, 0) < 0
THEN
( (ABS (adj.amount)) * (NVL (rcta.EXCHANGE_RATE, 1)))
ELSE
0
END
credit, --
NULL transactionstatus,
rcta.CUSTOMER_TRX_ID,
rcta.EXCHANGE_RATE_TYPE,
rcta.EXCHANGE_DATE,
rcta.EXCHANGE_RATE,
ABS (adj.amount) entered_amount
FROM ra_customer_trx_all rcta,
hz_cust_accounts hca,
hz_parties hp,
ra_cust_trx_types_all rctta,
ra_terms rt,
hr_operating_units hou,
( SELECT ada.CUSTOMER_TRX_ID,
ada.APPLY_DATE trx_date,
ada.ADJUSTMENT_NUMBER trx_number,
ada.DOC_SEQUENCE_VALUE,
SUM (NVL (ada.AMOUNT, 0)) amount,
ada.gl_date
FROM AR_ADJUSTMENTS_ALL ada
WHERE ada.STATUS = 'A'
GROUP BY ada.CUSTOMER_TRX_ID,
ada.ADJUSTMENT_NUMBER,
ada.APPLY_DATE,
ada.DOC_SEQUENCE_VALUE, ada.gl_date) adj
WHERE 1 = 1
AND rcta.bill_to_customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
AND rcta.cust_trx_type_seq_id = rctta.cust_trx_type_seq_id
AND rt.term_id(+) = rcta.term_id
AND hou.organization_id = rcta.org_id
AND rcta.complete_flag = 'Y'
AND rcta.CUSTOMER_TRX_ID = adj.CUSTOMER_TRX_ID
AND TRUNC (adj.gl_date) BETWEEN NVL (:p_from_date,
TRUNC (adj.gl_date))
AND NVL (:p_to_date,
TRUNC (adj.gl_date))
AND rcta.org_id = NVL (:p_org_id, rcta.org_id)
AND hca.account_number = NVL (:p_account_number, hca.account_number)
UNION -- Below Query for Receipt Data
SELECT '2' typ,hou.name "Business Unit",
hca.cust_account_id,
acra.org_id org_id,
hca.ACCOUNT_NUMBER CustomerNumber,
hp.party_name customername,
hp.ADDRESS1
|| ','
|| CHR (10)
|| hp.ADDRESS2
|| ','
|| CHR (10)
|| hp.ADDRESS3
address,
hp.city city,
hp.COUNTRY,
UPPER (acra.CURRENCY_CODE) currency,
acra.receipt_date transactiondate,
'Receipts' trantype,
acra.receipt_number transactionnumber,
acra.doc_sequence_value vouchernumber,
NULL termname,
NULL ponumber,
acra.comments description,
acra.currency_code innvoicecurrency,
0 debit,
(NVL (acra.amount, 0) * NVL (acra.EXCHANGE_RATE, 1)) credit,
DECODE (apsa.status, 'OP', 'Unapplied/Partially Applied', 'CL', 'Applied') transactionstatus,
acra.cash_receipt_id,
acra.EXCHANGE_RATE_TYPE,
acra.EXCHANGE_DATE,
acra.EXCHANGE_RATE,
acra.AMOUNT entered_amount
FROM ar_cash_receipts_all acra,
hz_cust_accounts hca,
hz_parties hp,
ar_payment_schedules_all apsa,
hr_operating_units hou
WHERE 1 = 1
AND acra.pay_from_customer = hca.cust_account_id
AND hp.party_id = hca.party_id
AND apsa.cash_receipt_id = acra.cash_receipt_id
AND hou.organization_id = acra.org_id
AND TRUNC (apsa.gl_date) BETWEEN NVL (:p_from_date,
TRUNC (apsa.gl_date))
AND NVL (:p_to_date,
TRUNC (apsa.gl_date))
AND acra.org_id = NVL (:p_org_id, acra.org_id)
AND hca.cust_account_id = NVL (:p_customer_id, hca.cust_account_id)
AND hca.account_number = NVL (:p_account_number, hca.account_number)
UNION -- Below Query for Receipt Data
SELECT '4' typ,hou.name "Business Unit",
hca.cust_account_id,
acra.org_id org_id,
hca.ACCOUNT_NUMBER CustomerNumber,
hp.party_name customername,
hp.ADDRESS1
|| ','
|| CHR (10)
|| hp.ADDRESS2
|| ','
|| CHR (10)
|| hp.ADDRESS3
address,
hp.city city,
hp.COUNTRY,
UPPER (acra.CURRENCY_CODE) currency,
--acra.receipt_date transactiondate,
ara.gl_date transactiondate,
'Refund' trantype,
acra.receipt_number transactionnumber,
acra.doc_sequence_value vouchernumber,
NULL termname,
NULL ponumber,
acra.comments description,
acra.currency_code innvoicecurrency,
(NVL (ara.amount_applied, 0) * NVL (acra.EXCHANGE_RATE, 1)) debit,
0 credit,
'Refund' transactionstatus,
acra.cash_receipt_id,
acra.EXCHANGE_RATE_TYPE,
acra.EXCHANGE_DATE,
acra.EXCHANGE_RATE,
ara.amount_applied entered_amount
FROM ar_cash_receipts_all acra,
hz_cust_accounts hca,
hz_parties hp,
ar_receivable_applications ara,
hr_operating_units hou
WHERE 1 = 1
AND acra.pay_from_customer = hca.cust_account_id
AND hp.party_id = hca.party_id
AND ara.applied_payment_schedule_id IN (-8,-3) -- -8 For refund and -3 for Receipt Write-off
AND ara.cash_receipt_id = acra.cash_receipt_id
AND hou.organization_id = acra.org_id
AND TRUNC (ara.gl_date) BETWEEN NVL (:p_from_date,
TRUNC (ara.gl_date))
AND NVL (:p_to_date,
TRUNC (ara.gl_date))
AND acra.org_id = NVL (:p_org_id, acra.org_id)
AND hca.account_number = NVL (:p_account_number, hca.account_number)
UNION
SELECT '5' typ,hou.name "Business Unit",
hca.cust_account_id,
acra.org_id org_id,
hca.ACCOUNT_NUMBER CustomerNumber,
hp.party_name customername,
hp.ADDRESS1
|| ','
|| CHR (10)
|| hp.ADDRESS2
|| ','
|| CHR (10)
|| hp.ADDRESS3
address,
hp.city city,
hp.COUNTRY,
UPPER (acra.invoice_CURRENCY_CODE) currency,
--acra.receipt_date transactiondate,
ara.gl_date transactiondate,
'Refund' trantype,
acra.trx_number transactionnumber,
acra.doc_sequence_value vouchernumber,
NULL termname,
NULL ponumber,
acra.comments description,
acra.invoice_currency_code innvoicecurrency,
(NVL (ara.amount_applied, 0) * NVL (acra.EXCHANGE_RATE, 1)) debit,
0 credit,
'Refund' transactionstatus,
acra.customer_trx_id,
acra.EXCHANGE_RATE_TYPE,
acra.EXCHANGE_DATE,
acra.EXCHANGE_RATE,
ara.amount_applied entered_amount
FROM ra_customer_trx_all acra,
hz_cust_accounts hca,
hz_parties hp,
ar_receivable_applications ara,
hr_operating_units hou
WHERE 1 = 1
AND acra.bill_to_customer_id = hca.cust_account_id
AND hp.party_id = hca.party_id
AND ara.applied_payment_schedule_id IN (-8,-3) -- -8 For refund and -3 for Receipt Write-off
AND ara.customer_trx_id = acra.customer_trx_id
AND hou.organization_id = acra.org_id
AND ara.display = 'Y'
AND TRUNC (ara.gl_date) BETWEEN NVL (:p_from_date,
TRUNC (ara.gl_date))
AND NVL (:p_to_date,
TRUNC (ara.gl_date))
AND acra.org_id = NVL (:p_org_id, acra.org_id)
AND hca.account_number = NVL (:p_account_number, hca.account_number)
UNION ALL -- Below Query for Receipt Data
SELECT '6' typ,hou.name "Business Unit",
hca.cust_account_id,
acra.org_id org_id,
hca.ACCOUNT_NUMBER CustomerNumber,
hp.party_name customername,
hp.ADDRESS1
|| ','
|| CHR (10)
|| hp.ADDRESS2
|| ','
|| CHR (10)
|| hp.ADDRESS3
address,
hp.city city,
hp.COUNTRY,
UPPER (acra.CURRENCY_CODE) currency,
acrh.gl_date transactiondate,
--acra.receipt_date transactiondate,
'Receipts' trantype,
acra.receipt_number transactionnumber,
acra.doc_sequence_value vouchernumber,
NULL termname,
NULL ponumber,
acra.comments description,
acra.currency_code innvoicecurrency,
(NVL (acra.amount, 0) * NVL (acra.EXCHANGE_RATE, 1)) debit,
0 credit,
'Reversed' transactionstatus,
acra.cash_receipt_id,
acra.EXCHANGE_RATE_TYPE,
acra.EXCHANGE_DATE,
acra.EXCHANGE_RATE,
acra.AMOUNT entered_amount
FROM ar_cash_receipts_all acra,
hz_cust_accounts hca,
hz_parties hp,
ar_payment_schedules_all apsa,
hr_operating_units hou,
ar_cash_receipt_history_all acrh
WHERE 1 = 1
AND acra.pay_from_customer = hca.cust_account_id
AND hp.party_id = hca.party_id
AND apsa.cash_receipt_id = acra.cash_receipt_id
AND hou.organization_id = acra.org_id
AND TRUNC (acrh.gl_date) BETWEEN NVL (:p_from_date,
TRUNC (acrh.gl_date))
AND NVL (:p_to_date,
TRUNC (acrh.gl_date))
AND acra.org_id = NVL (:p_org_id, acra.org_id)
AND hca.account_number = NVL (:p_account_number, hca.account_number)
AND acrh.status = 'REVERSED'
AND acra.cash_receipt_id = acrh.cash_receipt_id
AND EXISTS (SELECT acrh1.cash_receipt_history_id
FROM ar_cash_receipt_history_all acrh1
WHERE acra.cash_receipt_id = acrh1.cash_receipt_id
AND acrh1.status = 'REVERSED'
AND acrh1.gl_date <= NVL (:p_to_date,TRUNC (acrh1.gl_date))
)
ORDER BY transactiondate, typ