In this post, I am sharing the opening balance logic/ query which will help you to identify opening balance of customers. Sometime organization or person deliver the product or service to his customer with pending amount due to a good selling relationship and this amount customer will pay later.
opening balance calculated by using transactions, receipts, applications, adjustment, refund, write-off etc.
Tables used in Query
RA_CUSTOMER_TRX_ALL
RA_CUST_TRX_LINE_GL_DIST_ALL
HZ_CUST_ACCOUNTS
HZ_PARTIES
RA_CUST_TRX_TYPES_ALL
AR_CASH_RECEIPTS_ALL
AR_CASH_RECEIPT_HISTORY_ALL
AR_ADJUSTMENTS_ALL
AR_PAYMENT_SCHEDULES_ALL
AR_RECEIVABLE_APPLICATIONS
RA_CUST_TRX_LINE_GL_DIST_ALL
HZ_CUST_ACCOUNTS
HZ_PARTIES
RA_CUST_TRX_TYPES_ALL
AR_CASH_RECEIPTS_ALL
AR_CASH_RECEIPT_HISTORY_ALL
AR_ADJUSTMENTS_ALL
AR_PAYMENT_SCHEDULES_ALL
AR_RECEIVABLE_APPLICATIONS
Detailed Query
SELECT NVL (SUM (NVL (open_bal.debit, 0) - NVL (open_bal.credit, 0)), 0)
BEGINNING_BAL,
open_bal.cust_account_id,
open_bal.customer_name cust_name,
open_bal.org_id,
open_bal.CustomerNumber cust_num
FROM (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,
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 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 < NVL (:p_from_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 '2' 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,
'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,
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, 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) <
NVL (:p_from_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 '3' 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,
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) <
NVL (:p_from_date, TRUNC (apsa.gl_date))
AND acra.org_id = NVL (:p_org_id, acra.org_id)
AND NOT 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_from_date,TRUNC (acrh1.gl_date))
)
AND hca.account_number = NVL (:p_account_number, hca.account_number)
UNION ALL --customer detail
SELECT '4' typ,NULL "Business Unit",
hca1.cust_account_id,
to_number(:p_org_id) org_id,
hca1.ACCOUNT_NUMBER CustomerNumber,
hp.party_name customername,
hp.ADDRESS1
|| ','
|| CHR (10)
|| hp.ADDRESS2
|| ','
|| CHR (10)
|| hp.ADDRESS3
address,
hp.city city,
hp.COUNTRY,
NULL currency,
NULL transactiondate,
NULL trantype,
NULL transactionnumber,
NULL vouchernumber,
NULL termname,
NULL ponumber,
NULL description,
NULL innvoicecurrency,
0 debit,
0 credit,
NULL transactionstatus,
NULL cash_receipt_id,
NULL EXCHANGE_RATE_TYPE,
NULL EXCHANGE_DATE,
NULL EXCHANGE_RATE,
NULL entered_amount
FROM hz_cust_accounts hca1, hz_parties hp
WHERE 1 = 1 AND hca1.party_id = hp.party_id
AND hca1.account_number = NVL (:p_account_number, hca1.account_number)
AND EXISTS
(SELECT DISTINCT hca.CUST_ACCOUNT_ID
FROM hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
WHERE 1 = 1
AND hp.party_id = hca.party_id
AND hca.cust_account_id = hcas.cust_account_id
AND hcas.cust_acct_site_id =
hcsu.cust_acct_site_id
AND hcsu.set_id =
fnd_setid_utility.
getsetid ('HZ_CUSTOMER_ACCOUNT_SITE',
'BU',
:P_ORG_ID)
AND hca1.cust_account_id = hca.cust_account_id)
UNION -- Below Query for Receipt Data Refund/ write-off
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.CURRENCY_CODE) currency,
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) < NVL (:p_from_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)
AND NOT 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_from_date,TRUNC (acrh1.gl_date))
)
UNION --Credit Memo Application
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.invoice_CURRENCY_CODE) currency,
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 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) < NVL (:p_from_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)
) open_bal
GROUP BY open_bal.cust_account_id,
open_bal.org_id,
open_bal.customer_name,
open_bal.CustomerNumber
BEGINNING_BAL,
open_bal.cust_account_id,
open_bal.customer_name cust_name,
open_bal.org_id,
open_bal.CustomerNumber cust_num
FROM (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,
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 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 < NVL (:p_from_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 '2' 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,
'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,
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, 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) <
NVL (:p_from_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 '3' 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,
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) <
NVL (:p_from_date, TRUNC (apsa.gl_date))
AND acra.org_id = NVL (:p_org_id, acra.org_id)
AND NOT 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_from_date,TRUNC (acrh1.gl_date))
)
AND hca.account_number = NVL (:p_account_number, hca.account_number)
UNION ALL --customer detail
SELECT '4' typ,NULL "Business Unit",
hca1.cust_account_id,
to_number(:p_org_id) org_id,
hca1.ACCOUNT_NUMBER CustomerNumber,
hp.party_name customername,
hp.ADDRESS1
|| ','
|| CHR (10)
|| hp.ADDRESS2
|| ','
|| CHR (10)
|| hp.ADDRESS3
address,
hp.city city,
hp.COUNTRY,
NULL currency,
NULL transactiondate,
NULL trantype,
NULL transactionnumber,
NULL vouchernumber,
NULL termname,
NULL ponumber,
NULL description,
NULL innvoicecurrency,
0 debit,
0 credit,
NULL transactionstatus,
NULL cash_receipt_id,
NULL EXCHANGE_RATE_TYPE,
NULL EXCHANGE_DATE,
NULL EXCHANGE_RATE,
NULL entered_amount
FROM hz_cust_accounts hca1, hz_parties hp
WHERE 1 = 1 AND hca1.party_id = hp.party_id
AND hca1.account_number = NVL (:p_account_number, hca1.account_number)
AND EXISTS
(SELECT DISTINCT hca.CUST_ACCOUNT_ID
FROM hz_parties hp,
hz_cust_accounts hca,
hz_cust_acct_sites_all hcas,
hz_cust_site_uses_all hcsu
WHERE 1 = 1
AND hp.party_id = hca.party_id
AND hca.cust_account_id = hcas.cust_account_id
AND hcas.cust_acct_site_id =
hcsu.cust_acct_site_id
AND hcsu.set_id =
fnd_setid_utility.
getsetid ('HZ_CUSTOMER_ACCOUNT_SITE',
'BU',
:P_ORG_ID)
AND hca1.cust_account_id = hca.cust_account_id)
UNION -- Below Query for Receipt Data Refund/ write-off
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.CURRENCY_CODE) currency,
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) < NVL (:p_from_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)
AND NOT 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_from_date,TRUNC (acrh1.gl_date))
)
UNION --Credit Memo Application
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.invoice_CURRENCY_CODE) currency,
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 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) < NVL (:p_from_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)
) open_bal
GROUP BY open_bal.cust_account_id,
open_bal.org_id,
open_bal.customer_name,
open_bal.CustomerNumber