In this post, I am sharing customer outstanding amount which is calculated on accounting date. The logic includes override functionality also. Sometimes user selected wrong account and further he changed account by using override functionality available in account receivables.
You can use this query for AR Aging report. fun_outstanding_amt field shows values in ledger currency.
Detail Query of Customer Outstanding/ opening balance
SELECT hp.party_name customer,
rcta.trx_number,
hca.customer_class_code,
hca.party_id,
rcta.bill_to_customer_id,
rcta.org_id,
hca.account_name,
hca.account_number,
rcta.customer_trx_id,
(SELECT name
FROM hr_all_organization_units hr
WHERE hr.organization_id = rcta.org_id) bu_name,
rcta.trx_number,
hca.customer_class_code,
hca.party_id,
rcta.bill_to_customer_id,
rcta.org_id,
hca.account_name,
hca.account_number,
rcta.customer_trx_id,
(SELECT name
FROM hr_all_organization_units hr
WHERE hr.organization_id = rcta.org_id) bu_name,
apsa.amount_due_original invoice_amount,
(SELECT segment1
|| '.'
|| segment2
|| '.'
|| segment3
|| '.'
|| segment4
|| '.'
|| segment5
|| '.'
|| segment6
FROM ra_cust_trx_line_gl_dist_all ardist,
gl_code_combinations gcc
WHERE ardist.account_class = 'REC'
AND gcc.code_combination_id = ardist.code_combination_id
AND ardist.customer_trx_id = rcta.customer_trx_id
AND ROWNUM = 1) account,
a.segment6 nat_acct,
( amount_due_original
- ( NVL ((SELECT SUM (ara.amount_applied)
FROM ar_cash_receipts_all acr,
ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.applied_customer_trx_id
AND acr.cash_receipt_id = ara.cash_receipt_id
-- AND ara.applied_payment_schedule_id =
--apsa.payment_schedule_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CASH')
AND ARA.DISPLAY = 'Y'
--AND ara.reversal_gl_date IS NULL
AND ara.gl_date <=
TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
'DD-MM-YYYY'
),
'DD-MM-YYYY'
)
)),
0
)
+ NVL ((SELECT SUM (ara.amount_applied)
FROM ar_cash_receipts_all acr,
ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.applied_customer_trx_id
AND acr.cash_receipt_id = ara.cash_receipt_id
-- AND ara.applied_payment_schedule_id =
--apsa.payment_schedule_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CASH')
AND ARA.DISPLAY = 'N'
AND ara.reversal_gl_date IS NOT NULL
AND ara.gl_date <=
TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
'DD-MM-YYYY'
),
'DD-MM-YYYY'
)
)),
0
)
--
+ NVL
((SELECT SUM ((aav.amount * -1))
FROM ar_adjustments_all aav
WHERE rcta.customer_trx_id = aav.customer_trx_id
AND aav.payment_schedule_id =
apsa.payment_schedule_id
AND TRUNC (aav.gl_date) <=
TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
'DD-MM-YYYY'
),
'DD-MM-YYYY'
)
)
AND aav.status = 'A'),
0
)
+ (-1) * NVL ((SELECT SUM (ara.amount_applied)
FROM ar_receivable_applications_all ara
WHERE rcta.customer_trx_id = ara.customer_trx_id
AND ara.status IN ('ACTIVITY') --, 'APP')
AND ara.display = 'Y'
AND ara.gl_date <=
TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
'DD-MM-YYYY'
),
'DD-MM-YYYY'
)
)),
0
)
+ (-1) * NVL ((SELECT SUM ( ara.amount_applied
-- * (-1)
)
FROM ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.customer_trx_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
AND ara.cash_receipt_id IS NULL
--AND ara.reversal_gl_date IS NULL
AND ara.display = 'Y'
AND ara.gl_date <=
TRUNC (:p_as_of_date)),
0
)
+ NVL ((SELECT SUM ( ara.amount_applied)
FROM ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.applied_customer_trx_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
AND ara.cash_receipt_id IS NULL
--AND ara.reversal_gl_date IS NULL
AND ara.display = 'Y'
AND ara.gl_date <=
TRUNC (:p_as_of_date)),
0
)
+ (-1) * NVL ((SELECT SUM ( ara.amount_applied
--* (-1)
)
FROM ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.customer_trx_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
AND ara.cash_receipt_id IS NULL
AND ara.reversal_gl_date IS NOT NULL
AND ara.display = 'N'
AND ara.gl_date <=
TRUNC (:p_as_of_date)),
0
)
+ NVL ((SELECT SUM ( ara.amount_applied)
FROM ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.applied_customer_trx_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
AND ara.cash_receipt_id IS NULL
AND ara.reversal_gl_date IS NOT NULL
AND ara.display = 'N'
AND ara.gl_date <=
TRUNC (:p_as_of_date)),
0
)
)
) ent_outstanding_amt,
( (amount_due_original * NVL(rcta.exchange_rate,1))
- ( NVL ((SELECT SUM (ara.amount_applied * NVL(rcta.exchange_rate,1))
FROM ar_cash_receipts_all acr,
ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.applied_customer_trx_id
AND acr.cash_receipt_id = ara.cash_receipt_id
-- AND ara.applied_payment_schedule_id =
--apsa.payment_schedule_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CASH')
AND ara.display = 'Y'
--AND ara.reversal_gl_date IS NULL
AND ara.gl_date <=
TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
'DD-MM-YYYY'
),
'DD-MM-YYYY'
)
)),
0
)
+ NVL ((SELECT SUM (ara.amount_applied * NVL(rcta.exchange_rate,1))
FROM ar_cash_receipts_all acr,
ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.applied_customer_trx_id
AND acr.cash_receipt_id = ara.cash_receipt_id
-- AND ara.applied_payment_schedule_id =
--apsa.payment_schedule_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CASH')
AND ara.display = 'N'
AND ara.reversal_gl_date IS NOT NULL
AND ara.gl_date <=
TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
'DD-MM-YYYY'
),
'DD-MM-YYYY'
)
)),
0
)
--
+ NVL
((SELECT SUM ((aav.amount * -1) * NVL(rcta.exchange_rate,1))
FROM ar_adjustments_all aav
WHERE rcta.customer_trx_id = aav.customer_trx_id
AND aav.payment_schedule_id = apsa.payment_schedule_id
AND TRUNC (aav.gl_date) <=
TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
'DD-MM-YYYY'
),
'DD-MM-YYYY'
)
)
AND aav.status = 'A'),
0
)
+ (-1) * NVL ((SELECT SUM ((ara.amount_applied
) * NVL(rcta.exchange_rate,1))
FROM ar_receivable_applications_all ara
WHERE rcta.customer_trx_id = ara.customer_trx_id
AND ara.status IN ('ACTIVITY')
AND ara.display = 'Y'
AND ara.gl_date <=
TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
'DD-MM-YYYY'
),
'DD-MM-YYYY'
)
)),
0
)
+ (-1) * NVL ((SELECT SUM ( ara.amount_applied
-- * (-1)
* NVL(rcta.exchange_rate,1)
)
FROM ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.customer_trx_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
AND ara.cash_receipt_id IS NULL
--AND ara.reversal_gl_date IS NULL
AND ara.display = 'Y'
AND ara.gl_date <=
TRUNC (:p_as_of_date)),
0
)
+ NVL ((SELECT SUM ( ara.amount_applied
-- * (-1)
* NVL(rcta.exchange_rate,1)
)
FROM ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.applied_customer_trx_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
AND ara.cash_receipt_id IS NULL
--AND ara.reversal_gl_date IS NULL
AND ara.display = 'Y'
--AND ARA.APPLY_DATE <=
AND ara.gl_date <=
TRUNC (:p_as_of_date)),
0
)
+ NVL ((SELECT SUM ( ara.amount_applied
--* (-1)
* NVL(rcta.exchange_rate,1)
)
FROM ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.applied_customer_trx_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
AND ara.cash_receipt_id IS NULL
AND ara.reversal_gl_date IS NOT NULL
AND ara.display = 'N'
--AND ARA.APPLY_DATE <=
AND ara.gl_date <=
(:p_as_of_date)),
0
)
+ (-1) * NVL ((SELECT SUM ( ara.amount_applied
-- * (-1)
* NVL(rcta.exchange_rate,1)
)
FROM ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.customer_trx_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
AND ara.cash_receipt_id IS NULL
AND ara.reversal_gl_date IS NOT NULL
AND ara.display = 'N'
--AND ARA.APPLY_DATE <=
AND ara.gl_date <=
TRUNC (:p_as_of_date)),
0
)
)
) fun_outstanding_amt,
(( apsa.amount_due_original
+ NVL (apsa.amount_credited, 0)
+ NVL (apsa.amount_adjusted, 0)
- apsa.tax_remaining
)
)
* .1 retainage_amt,
(( apsa.amount_due_original
+ NVL (apsa.amount_credited, 0)
+ NVL (apsa.amount_adjusted, 0)
- apsa.tax_remaining
)
)
* .1 * NVL (rcta.exchange_rate, 1) retainage_amt1,
DECODE ((SELECT COUNT (1)
FROM ra_batch_sources_all
WHERE batch_source_seq_id =
rcta.batch_source_seq_id
AND NAME = 'MIGRATION'),
1, 'NO',
DECODE (UPPER (rcta.attribute1),
'YES', 'YES',
'NO', 'NO',
NULL, DECODE (hca.customer_class_code,
'RETENTION', 'YES',
'NO'
),
'NO'
)
) eligible,
apsa.amount_due_original, apsa.amount_credited,
apsa.amount_adjusted, apsa.tax_remaining, apsa.due_date,
apsa.amount_due_remaining, apsa.gl_date, rctt.TYPE,
rctt.NAME trx_type, rcta.trx_date invoice_date,
hp.party_number,
DECODE (rcta.invoice_currency_code,
'SAR', 1,
rcta.exchange_rate
) cur_cd,
rcta.doc_sequence_value, rcta.invoice_currency_code,
(SELECT rt.NAME
FROM ra_terms rt
WHERE rcta.term_id = rt.term_id) payment_term,
NVL (( TO_DATE (TO_CHAR (due_date, 'MM-DD-YYYY'),
'MM-DD-YYYY'
)
- TO_DATE (TO_CHAR (:p_as_of_date, 'MM-DD-YYYY'),
'MM-DD-YYYY'
)
),
0
) past_due_days,
rcta.exchange_rate_type
FROM hz_cust_accounts hca,
ra_customer_trx_all rcta,
ar_payment_schedules_all apsa,
hz_parties hp,
ra_cust_trx_types_all rctt,
(SELECT gcc.segment6,
ardist.customer_trx_id
FROM gl_code_combinations gcc,
xla_distribution_links xdl,
ra_cust_trx_line_gl_dist_all ardist,
xla_ae_lines xal,
xla_ae_headers xah
WHERE ardist.account_class IN ('REC')
AND ardist.cust_trx_line_gl_dist_id = xdl.source_distribution_id_num_1
AND xdl.application_id = 222
AND xdl.ae_header_id = xal.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xal.ae_header_id = xah.ae_header_id
AND xah.je_category_name IN ('Sales Invoices','Credit Memos','Debit Memos')
AND xal.accounting_class_code = 'RECEIVABLE'
AND xah.ae_header_id = (SELECT MAX(xal1.ae_header_id)
FROM xla_ae_lines xal1,
xla_ae_headers xah1
WHERE xah1.entity_id = xah.entity_id
AND xal1.ae_header_id = xah1.ae_header_id
AND xal1.accounting_class_code = 'RECEIVABLE'
AND xah1.event_id = xdl.event_id
AND xah1.je_category_name IN ('Sales Invoices','Credit Memos','Debit Memos')
AND xal1.accounting_date <= :p_as_of_date
GROUP BY xah1.entity_id
)
-- Overridden logic
AND gcc.code_combination_id IN DECODE(xal.override_reason,NULL,
ardist.code_combination_id,
(SELECT xal1.code_combination_id
FROM xla_ae_lines xal1
WHERE xal1.accounting_class_code = 'RECEIVABLE'
AND xal1.ae_header_id=xal.ae_header_id
AND xal1.overridden_code_combination_id IS NOT NULL
AND xal1.ae_header_id = (SELECT MAX(xal1.ae_header_id)
FROM xla_ae_lines xal1,
xla_ae_headers xah1
WHERE xah1.entity_id=xah.entity_id
AND xal1.ae_header_id = xah1.ae_header_id
AND xal1.accounting_class_code = 'RECEIVABLE'
AND xah1.je_category_name IN ('Sales Invoices','Credit Memos','Debit Memos')
AND xah1.event_id = xdl.event_id
AND xal1.accounting_date <= :p_as_of_date
GROUP BY xah1.entity_id
)
))
) a
WHERE hca.cust_account_id = rcta.bill_to_customer_id
AND rcta.customer_trx_id = apsa.customer_trx_id
AND hca.party_id = hp.party_id
AND rcta.cust_trx_type_seq_id = rctt.cust_trx_type_seq_id
AND rcta.customer_trx_id = a.customer_trx_id
AND TRUNC (apsa.gl_date) <= NVL (:p_as_of_date, SYSDATE)
AND rcta.bill_to_customer_id IN (
SELECT DISTINCT hzc.cust_account_id
FROM hz_cust_accounts hzc,
hz_parties hzp
WHERE hzc.party_id = hzp.party_id
AND ( hzp.party_name IN (:p_cust_name)
OR LEAST (:p_cust_name) IS NULL
)
AND ( hzc.account_name IN (:p_cust_acc)
OR LEAST (:p_cust_acc) IS NULL
))
AND rcta.org_id IN (SELECT organization_id
FROM hr_all_organization_units
WHERE name = :p_bu_name)
(SELECT segment1
|| '.'
|| segment2
|| '.'
|| segment3
|| '.'
|| segment4
|| '.'
|| segment5
|| '.'
|| segment6
FROM ra_cust_trx_line_gl_dist_all ardist,
gl_code_combinations gcc
WHERE ardist.account_class = 'REC'
AND gcc.code_combination_id = ardist.code_combination_id
AND ardist.customer_trx_id = rcta.customer_trx_id
AND ROWNUM = 1) account,
a.segment6 nat_acct,
( amount_due_original
- ( NVL ((SELECT SUM (ara.amount_applied)
FROM ar_cash_receipts_all acr,
ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.applied_customer_trx_id
AND acr.cash_receipt_id = ara.cash_receipt_id
-- AND ara.applied_payment_schedule_id =
--apsa.payment_schedule_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CASH')
AND ARA.DISPLAY = 'Y'
--AND ara.reversal_gl_date IS NULL
AND ara.gl_date <=
TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
'DD-MM-YYYY'
),
'DD-MM-YYYY'
)
)),
0
)
+ NVL ((SELECT SUM (ara.amount_applied)
FROM ar_cash_receipts_all acr,
ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.applied_customer_trx_id
AND acr.cash_receipt_id = ara.cash_receipt_id
-- AND ara.applied_payment_schedule_id =
--apsa.payment_schedule_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CASH')
AND ARA.DISPLAY = 'N'
AND ara.reversal_gl_date IS NOT NULL
AND ara.gl_date <=
TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
'DD-MM-YYYY'
),
'DD-MM-YYYY'
)
)),
0
)
--
+ NVL
((SELECT SUM ((aav.amount * -1))
FROM ar_adjustments_all aav
WHERE rcta.customer_trx_id = aav.customer_trx_id
AND aav.payment_schedule_id =
apsa.payment_schedule_id
AND TRUNC (aav.gl_date) <=
TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
'DD-MM-YYYY'
),
'DD-MM-YYYY'
)
)
AND aav.status = 'A'),
0
)
+ (-1) * NVL ((SELECT SUM (ara.amount_applied)
FROM ar_receivable_applications_all ara
WHERE rcta.customer_trx_id = ara.customer_trx_id
AND ara.status IN ('ACTIVITY') --, 'APP')
AND ara.display = 'Y'
AND ara.gl_date <=
TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
'DD-MM-YYYY'
),
'DD-MM-YYYY'
)
)),
0
)
+ (-1) * NVL ((SELECT SUM ( ara.amount_applied
-- * (-1)
)
FROM ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.customer_trx_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
AND ara.cash_receipt_id IS NULL
--AND ara.reversal_gl_date IS NULL
AND ara.display = 'Y'
AND ara.gl_date <=
TRUNC (:p_as_of_date)),
0
)
+ NVL ((SELECT SUM ( ara.amount_applied)
FROM ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.applied_customer_trx_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
AND ara.cash_receipt_id IS NULL
--AND ara.reversal_gl_date IS NULL
AND ara.display = 'Y'
AND ara.gl_date <=
TRUNC (:p_as_of_date)),
0
)
+ (-1) * NVL ((SELECT SUM ( ara.amount_applied
--* (-1)
)
FROM ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.customer_trx_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
AND ara.cash_receipt_id IS NULL
AND ara.reversal_gl_date IS NOT NULL
AND ara.display = 'N'
AND ara.gl_date <=
TRUNC (:p_as_of_date)),
0
)
+ NVL ((SELECT SUM ( ara.amount_applied)
FROM ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.applied_customer_trx_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
AND ara.cash_receipt_id IS NULL
AND ara.reversal_gl_date IS NOT NULL
AND ara.display = 'N'
AND ara.gl_date <=
TRUNC (:p_as_of_date)),
0
)
)
) ent_outstanding_amt,
( (amount_due_original * NVL(rcta.exchange_rate,1))
- ( NVL ((SELECT SUM (ara.amount_applied * NVL(rcta.exchange_rate,1))
FROM ar_cash_receipts_all acr,
ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.applied_customer_trx_id
AND acr.cash_receipt_id = ara.cash_receipt_id
-- AND ara.applied_payment_schedule_id =
--apsa.payment_schedule_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CASH')
AND ara.display = 'Y'
--AND ara.reversal_gl_date IS NULL
AND ara.gl_date <=
TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
'DD-MM-YYYY'
),
'DD-MM-YYYY'
)
)),
0
)
+ NVL ((SELECT SUM (ara.amount_applied * NVL(rcta.exchange_rate,1))
FROM ar_cash_receipts_all acr,
ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.applied_customer_trx_id
AND acr.cash_receipt_id = ara.cash_receipt_id
-- AND ara.applied_payment_schedule_id =
--apsa.payment_schedule_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CASH')
AND ara.display = 'N'
AND ara.reversal_gl_date IS NOT NULL
AND ara.gl_date <=
TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
'DD-MM-YYYY'
),
'DD-MM-YYYY'
)
)),
0
)
--
+ NVL
((SELECT SUM ((aav.amount * -1) * NVL(rcta.exchange_rate,1))
FROM ar_adjustments_all aav
WHERE rcta.customer_trx_id = aav.customer_trx_id
AND aav.payment_schedule_id = apsa.payment_schedule_id
AND TRUNC (aav.gl_date) <=
TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
'DD-MM-YYYY'
),
'DD-MM-YYYY'
)
)
AND aav.status = 'A'),
0
)
+ (-1) * NVL ((SELECT SUM ((ara.amount_applied
) * NVL(rcta.exchange_rate,1))
FROM ar_receivable_applications_all ara
WHERE rcta.customer_trx_id = ara.customer_trx_id
AND ara.status IN ('ACTIVITY')
AND ara.display = 'Y'
AND ara.gl_date <=
TRUNC (TO_DATE (TO_CHAR (:p_as_of_date,
'DD-MM-YYYY'
),
'DD-MM-YYYY'
)
)),
0
)
+ (-1) * NVL ((SELECT SUM ( ara.amount_applied
-- * (-1)
* NVL(rcta.exchange_rate,1)
)
FROM ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.customer_trx_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
AND ara.cash_receipt_id IS NULL
--AND ara.reversal_gl_date IS NULL
AND ara.display = 'Y'
AND ara.gl_date <=
TRUNC (:p_as_of_date)),
0
)
+ NVL ((SELECT SUM ( ara.amount_applied
-- * (-1)
* NVL(rcta.exchange_rate,1)
)
FROM ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.applied_customer_trx_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
AND ara.cash_receipt_id IS NULL
--AND ara.reversal_gl_date IS NULL
AND ara.display = 'Y'
--AND ARA.APPLY_DATE <=
AND ara.gl_date <=
TRUNC (:p_as_of_date)),
0
)
+ NVL ((SELECT SUM ( ara.amount_applied
--* (-1)
* NVL(rcta.exchange_rate,1)
)
FROM ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.applied_customer_trx_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
AND ara.cash_receipt_id IS NULL
AND ara.reversal_gl_date IS NOT NULL
AND ara.display = 'N'
--AND ARA.APPLY_DATE <=
AND ara.gl_date <=
(:p_as_of_date)),
0
)
+ (-1) * NVL ((SELECT SUM ( ara.amount_applied
-- * (-1)
* NVL(rcta.exchange_rate,1)
)
FROM ar_receivable_applications_all ara
WHERE rcta.customer_trx_id =
ara.customer_trx_id
AND ara.status IN ('APP')
AND ara.application_type IN ('CM')
AND ara.cash_receipt_id IS NULL
AND ara.reversal_gl_date IS NOT NULL
AND ara.display = 'N'
--AND ARA.APPLY_DATE <=
AND ara.gl_date <=
TRUNC (:p_as_of_date)),
0
)
)
) fun_outstanding_amt,
(( apsa.amount_due_original
+ NVL (apsa.amount_credited, 0)
+ NVL (apsa.amount_adjusted, 0)
- apsa.tax_remaining
)
)
* .1 retainage_amt,
(( apsa.amount_due_original
+ NVL (apsa.amount_credited, 0)
+ NVL (apsa.amount_adjusted, 0)
- apsa.tax_remaining
)
)
* .1 * NVL (rcta.exchange_rate, 1) retainage_amt1,
DECODE ((SELECT COUNT (1)
FROM ra_batch_sources_all
WHERE batch_source_seq_id =
rcta.batch_source_seq_id
AND NAME = 'MIGRATION'),
1, 'NO',
DECODE (UPPER (rcta.attribute1),
'YES', 'YES',
'NO', 'NO',
NULL, DECODE (hca.customer_class_code,
'RETENTION', 'YES',
'NO'
),
'NO'
)
) eligible,
apsa.amount_due_original, apsa.amount_credited,
apsa.amount_adjusted, apsa.tax_remaining, apsa.due_date,
apsa.amount_due_remaining, apsa.gl_date, rctt.TYPE,
rctt.NAME trx_type, rcta.trx_date invoice_date,
hp.party_number,
DECODE (rcta.invoice_currency_code,
'SAR', 1,
rcta.exchange_rate
) cur_cd,
rcta.doc_sequence_value, rcta.invoice_currency_code,
(SELECT rt.NAME
FROM ra_terms rt
WHERE rcta.term_id = rt.term_id) payment_term,
NVL (( TO_DATE (TO_CHAR (due_date, 'MM-DD-YYYY'),
'MM-DD-YYYY'
)
- TO_DATE (TO_CHAR (:p_as_of_date, 'MM-DD-YYYY'),
'MM-DD-YYYY'
)
),
0
) past_due_days,
rcta.exchange_rate_type
FROM hz_cust_accounts hca,
ra_customer_trx_all rcta,
ar_payment_schedules_all apsa,
hz_parties hp,
ra_cust_trx_types_all rctt,
(SELECT gcc.segment6,
ardist.customer_trx_id
FROM gl_code_combinations gcc,
xla_distribution_links xdl,
ra_cust_trx_line_gl_dist_all ardist,
xla_ae_lines xal,
xla_ae_headers xah
WHERE ardist.account_class IN ('REC')
AND ardist.cust_trx_line_gl_dist_id = xdl.source_distribution_id_num_1
AND xdl.application_id = 222
AND xdl.ae_header_id = xal.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
AND xal.ae_header_id = xah.ae_header_id
AND xah.je_category_name IN ('Sales Invoices','Credit Memos','Debit Memos')
AND xal.accounting_class_code = 'RECEIVABLE'
AND xah.ae_header_id = (SELECT MAX(xal1.ae_header_id)
FROM xla_ae_lines xal1,
xla_ae_headers xah1
WHERE xah1.entity_id = xah.entity_id
AND xal1.ae_header_id = xah1.ae_header_id
AND xal1.accounting_class_code = 'RECEIVABLE'
AND xah1.event_id = xdl.event_id
AND xah1.je_category_name IN ('Sales Invoices','Credit Memos','Debit Memos')
AND xal1.accounting_date <= :p_as_of_date
GROUP BY xah1.entity_id
)
-- Overridden logic
AND gcc.code_combination_id IN DECODE(xal.override_reason,NULL,
ardist.code_combination_id,
(SELECT xal1.code_combination_id
FROM xla_ae_lines xal1
WHERE xal1.accounting_class_code = 'RECEIVABLE'
AND xal1.ae_header_id=xal.ae_header_id
AND xal1.overridden_code_combination_id IS NOT NULL
AND xal1.ae_header_id = (SELECT MAX(xal1.ae_header_id)
FROM xla_ae_lines xal1,
xla_ae_headers xah1
WHERE xah1.entity_id=xah.entity_id
AND xal1.ae_header_id = xah1.ae_header_id
AND xal1.accounting_class_code = 'RECEIVABLE'
AND xah1.je_category_name IN ('Sales Invoices','Credit Memos','Debit Memos')
AND xah1.event_id = xdl.event_id
AND xal1.accounting_date <= :p_as_of_date
GROUP BY xah1.entity_id
)
))
) a
WHERE hca.cust_account_id = rcta.bill_to_customer_id
AND rcta.customer_trx_id = apsa.customer_trx_id
AND hca.party_id = hp.party_id
AND rcta.cust_trx_type_seq_id = rctt.cust_trx_type_seq_id
AND rcta.customer_trx_id = a.customer_trx_id
AND TRUNC (apsa.gl_date) <= NVL (:p_as_of_date, SYSDATE)
AND rcta.bill_to_customer_id IN (
SELECT DISTINCT hzc.cust_account_id
FROM hz_cust_accounts hzc,
hz_parties hzp
WHERE hzc.party_id = hzp.party_id
AND ( hzp.party_name IN (:p_cust_name)
OR LEAST (:p_cust_name) IS NULL
)
AND ( hzc.account_name IN (:p_cust_acc)
OR LEAST (:p_cust_acc) IS NULL
))
AND rcta.org_id IN (SELECT organization_id
FROM hr_all_organization_units
WHERE name = :p_bu_name)