Oracle Fusion · Receivables
AR Aging Detail Report
Receivables
Open receivables aged by days past due, line by line — customer, transaction, due date, and bucket — with the receipt and adjustment activity behind each balance, so collections can work the detail and finance can tie aged AR to the GL.
Related The line-level detail companion to the flagship Receivables Aging Report.
Run note · Performance The subledger (XLA) join — especially Projects-to-XLA — is a known BI Publisher performance trap on large ledgers. Pre-join it in the warehouse, or apply tight ledger, period, and source filters before running online.
Sample build of the AR Aging Detail Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.
AR Aging Detail Report
Sample build · illustrativeFilters
Business Unit
US Operations
As of
31-JAN-2026
Currency
USD
$11.60M
Open AR
$2.80M
Past due
$540K
90+ days
| Customer | Current | 1–30 | 31–60 | 61–90 | 90+ | Total Open |
|---|---|---|---|---|---|---|
| Northwind Traders | 240,000 | 60,000 | 0 | 0 | 0 | 300,000 |
| Contoso Retail | 95,000 | 0 | 40,000 | 0 | 18,000 | 153,000 |
| Fabrikam Inc | 0 | 52,000 | 0 | 11,000 | 0 | 63,000 |
| Tailspin Toys | 180,500 | 30,000 | 9,500 | 0 | 6,000 | 226,000 |
| Total | 515,500 | 142,000 | 49,500 | 11,000 | 24,000 | 742,000 |
AI Analyst · active
reading
The report ages open schedules in AR_PAYMENT_SCHEDULES_ALL by due date and ties each to its transaction and any partial receipts.
flag
$540K in the 90+ bucket is concentrated in five customers, two of which have unapplied receipts on account — so their true overdue is lower once cash is applied.
root cause & next step
Apply the on-account receipts before escalating; aged balances overstated by unapplied cash send collections after customers who have already paid.
Illustrative data. The live interactive version — drill-through, filters, export, and the AI Analyst —
runs on your warehouse. See it live →
This is the report's BI Publisher data model — the SQL data set BI Publisher runs against Oracle tables to produce the output. The same SQL becomes a dbt model in your warehouse, so one definition drives both the formatted report and the analytics layer.
Data sources
- FND_FLEX_VALUES_VL
- AR_CASH_RECEIPTS_ALL
- XLA_AE_LINES
- XLA_AE_HEADERS
- XLA_TRANSACTION_ENTITIES
- GL_CODE_COMBINATIONS
- RA_CUSTOMER_TRX_ALL
- AR_RECEIVABLE_APPLICATIONS_ALL
- HZ_CUST_PROFILE_AMTS_F
- CR_LMT_CONDITION
- HZ_CUSTOMER_PROFILES_F
- SITE_CR_LMT
How it interconnects: this data set reads the physical tables above. Those same tables surface in OTBI as subject areas and in BICC as PVOs — three lenses on one source. Open any table to trace its subject areas and View Objects.
Show / hide SQL
WITH REC_ACC AS
(
SELECT /*+ MATERIALIZE */
ACRA.cash_receipt_id
, XAL.accounting_class_code
--, XAL.accounting_date
, GCC.segment1
, GCC.segment2
, GCC.segment3
, GCC.segment4
, GCC.segment5
, GCC.segment6
, GCC.segment7
, GCC.segment8
, GCC.segment9
, (SELECT F.DESCRIPTION FROM fnd_flex_values_vl F
WHERE 1=1
AND GCC.segment1 =F.flex_value
AND F.value_category ='ENTITY VALUE SET'
AND F.enabled_flag ='Y') LE_DESC
, SUM(NVL(XAL.ENTERED_DR,0)-NVL(XAL.ENTERED_CR,0)) BAL_AMOUNT
FROM
ar_cash_receipts_all ACRA
, xla_ae_lines XAL
, xla_ae_headers XAH
, xla_transaction_entities XTE
, gl_code_combinations GCC
WHERE
1 =1
AND XTE.entity_id =XAH.entity_id
AND XAH.ae_header_id =XAL.ae_header_id
AND XTE.application_id =222
AND XTE.application_id =XAL.application_id
AND XTE.entity_code ='RECEIPTS'
AND XTE.source_id_int_1 =ACRA.cash_receipt_id
AND XAL.ledger_id =ACRA.set_of_books_id
AND XAL.code_combination_id =GCC.code_combination_id
AND XAL.accounting_class_code IN ('UNAPP','ACC','CLAIM')
AND trunc(XAL.accounting_date) <=TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD')
GROUP BY
ACRA.cash_receipt_id
, XAL.accounting_class_code
--, XAL.accounting_date
, GCC.segment1
, GCC.segment2
, GCC.segment3
, GCC.segment4
, GCC.segment5
, GCC.segment6
, GCC.segment7
, GCC.segment8
, GCC.segment9
)
, TRX_ACC AS
(
SELECT /*+ MATERIALIZE */
RCTA.customer_trx_id
, XAL.accounting_class_code
, GCC.segment1
, GCC.segment2
, GCC.segment3
, GCC.segment4
, GCC.segment5
, GCC.segment6
, GCC.segment7
, GCC.segment8
, GCC.segment9
, SUM(NVL(XAL.ENTERED_DR,0)-NVL(XAL.ENTERED_CR,0)) Bal_Amount
FROM
ra_customer_trx_all RCTA
, xla_ae_lines XAL
, xla_ae_headers XAH
, xla_transaction_entities XTE
, gl_code_combinations GCC
WHERE
1 =1
AND XTE.entity_id =XAH.entity_id
AND XAH.ae_header_id =XAL.ae_header_id
AND XTE.application_id =222
AND XTE.application_id =XAL.application_id
AND XTE.source_id_int_1 =RCTA.customer_trx_id
AND XAL.ledger_id =RCTA.set_of_books_id
AND XAL.code_combination_id =GCC.code_combination_id
AND XAL.accounting_class_code IN ('RECEIVABLE')
AND XTE.entity_code ='TRANSACTIONS'
GROUP BY
RCTA.customer_trx_id
, XAL.accounting_class_code
, GCC.segment1
, GCC.segment2
, GCC.segment3
, GCC.segment4
, GCC.segment5
, GCC.segment6
, GCC.segment7
, GCC.segment8
, GCC.segment9
)
/*APPLIED_RECEIPTS AS
(
SELECT
ARAA.payment_schedule_id
, ARAA.code_combination_id
, SUM(ARAA.amount_applied) applied_receipt_amount
FROM
ar_receivable_applications_all ARAA
WHERE
1 =1
AND NVL(ARAA.confirmed_flag,'Y')='Y'
AND ARAA.status ='UNAPP'
GROUP BY
ARAA.payment_schedule_id
, ARAA.code_combination_id
)
, ONACC_RECEIPTS AS
(
SELECT
ARAA.payment_schedule_id
, ARAA.code_combination_id
, SUM(ARAA.amount_applied) applied_receipt_amount
FROM
ar_receivable_applications_all ARAA
WHERE
1 =1
AND NVL(ARAA.confirmed_flag,'Y')='Y'
AND ARAA.display ='Y'
AND ARAA.status ='ACC'
GROUP BY
ARAA.payment_schedule_id
, ARAA.code_combination_id
)*/
, CR_LMT_CONDITION AS
(
SELECT
HCPA.cust_account_profile_id
, COUNT(DISTINCT HCPA.last_update_date) LU_DATE_CNT
, COUNT(HCPA.cust_acct_profile_amt_id) CR_LMT_CNT
FROM
hz_cust_profile_amts_f HCPA
GROUP BY HCPA.cust_account_profile_id
)
, SITE_CR_LMT AS
(
SELECT
HCPF.cust_account_profile_id
, NVL(
(SELECT
HCPA.overall_credit_limit
FROM
hz_cust_profile_amts_f HCPA
, cr_lmt_condition CLC
WHERE
1 =1
AND CLC.cust_account_profile_id =HCPA.cust_account_profile_id
AND HCPF.cust_account_profile_id=HCPA.cust_account_profile_id
AND HCPF.effective_start_date =HCPA.effective_start_date
AND HCPF.effective_end_date =HCPA.effective_end_date
AND CLC.cr_lmt_cnt =1
)
,(SELECT
HCPA.overall_credit_limit
FROM
hz_cust_profile_amts_f HCPA
, cr_lmt_condition CLC
WHERE
1 =1
AND CLC.cust_account_profile_id =HCPA.cust_account_profile_id
AND HCPF.cust_account_profile_id=HCPA.cust_account_profile_id
AND HCPF.effective_start_date =HCPA.effective_start_date
AND HCPF.effective_end_date =HCPA.effective_end_date
AND HCPA.last_update_date =(SELECT MAX(H1.last_update_date) FROM hz_cust_profile_amts_f H1 WHERE H1.cust_account_profile_id=HCPA.cust_account_profile_id)
AND CLC.cr_lmt_cnt >1
AND CLC.LU_DATE_CNT >1)
) SITE_CR_LIMIT_VAL
FROM
hz_customer_profiles_f HCPF
WHERE
1=1
AND TRUNC(SYSDATE) BETWEEN TRUNC(HCPF.effective_start_date) AND TRUNC(HCPF.effective_end_date)
AND HCPF.site_use_id IS NOT NULL
)
, ACC_CR_LMT AS
(
SELECT
HCPF.cust_account_profile_id
, NVL(
(SELECT
HCPA.overall_credit_limit
FROM
hz_cust_profile_amts_f HCPA
, cr_lmt_condition CLC
WHERE
1 =1
AND CLC.cust_account_profile_id =HCPA.cust_account_profile_id
AND HCPF.cust_account_profile_id=HCPA.cust_account_profile_id
AND HCPF.effective_start_date =HCPA.effective_start_date
AND HCPF.effective_end_date =HCPA.effective_end_date
AND CLC.cr_lmt_cnt =1
)
,(SELECT
HCPA.overall_credit_limit
FROM
hz_cust_profile_amts_f HCPA
, cr_lmt_condition CLC
WHERE
1 =1
AND CLC.cust_account_profile_id=HCPA.cust_account_profile_id
AND HCPF.cust_account_profile_id=HCPA.cust_account_profile_id
AND HCPF.effective_start_date =HCPA.effective_start_date
AND HCPF.effective_end_date =HCPA.effective_end_date
AND HCPA.last_update_date =(SELECT MAX(H1.last_update_date) FROM hz_cust_profile_amts_f H1 WHERE H1.cust_account_profile_id=HCPA.cust_account_profile_id)
AND CLC.cr_lmt_cnt >1
AND CLC.lu_date_cnt >1)
) ACC_CR_LIMIT_VAL
FROM
hz_customer_profiles_f HCPF
WHERE
1=1
AND TRUNC(SYSDATE) BETWEEN TRUNC(HCPF.effective_start_date) AND TRUNC(HCPF.effective_end_date)
AND HCPF.site_use_id IS NULL
)
SELECT /*+parallel(12)*/
REGION
, BU
, LE
, SEGMENT1
, SEGMENT2
, SEGMENT3
, SEGMENT4
, SEGMENT5
, SEGMENT6
, SEGMENT7
, SEGMENT8
, SEGMENT9
, CUSTOMER_CLASS_CODE
, PROFILE_CLASS
, LOC_DESC
, CUSTOMER_NUM
, CUSTOMER_NAME
, GL_DATE
, TRX_DATE
, PAYMENT_TERMS
, DUE_DATE
, DOC_NUM
, TRX_CLASS
, TRX_TYPE
, PO
, CROSS_REF
, CLAIM
, REASON
, CUST_REF
, AMT_ENT
, BAL_ENT
, CURRENCY
, RATE
, AMT_FUNT
, BAL_FUN
, AGEBUC
, AGED_DAYS
, WEEK
, GOVT_INV_NUM
, GOLD_TAX
, SALESPERSON
, SHIP_TO
, DELIVER_DATE
, ASN
, LC_BANK
, LC_NUM
, SHIP_FROM
, SHIP_STATUS
, ETD
, SUBMIT_DATE
, BILL_NUM
, RCT_STS
, COPRO_STATUS
, OMS_STATUS
, AKA
, CUST_GRP
, CR_HD
, CR_LMT
, SUM(BAL_ENT) OVER (PARTITION BY CUST_GRP) NET_BAL
, CASE WHEN SUM(BAL_ENT) OVER (PARTITION BY CUST_GRP)>0 THEN 'Debit'
WHEN SUM(BAL_ENT) OVER (PARTITION BY CUST_GRP)<0 THEN 'Credit' END CR_DR
FROM
(
SELECT
(SELECT distinct FLV.attribute1
FROM fnd_flex_values_vl FLV
WHERE
FLV.value_category='XXC_CLOUD_BU_TO_REGION_CODE_MAPPING'
AND FLV.enabled_flag = 'Y'
AND FLV.flex_value=HOU.name) REGION
, HOU.name BU
, XEP.name LE
, TA.segment1
, TA.segment2
, TA.segment3
, TA.segment4
, TA.segment5
, TA.segment6
, TA.segment7
, TA.segment8
, TA.segment9
, HCA.customer_class_code
, CASE WHEN HCP.name='DEFAULT' THEN HCASA.attribute2 ELSE HCP.name||' '||HCASA.attribute2 END PROFILE_CLASS
, HCSUA.location LOC_DESC
, HCA.account_number CUSTOMER_NUM
, HP.party_name CUSTOMER_NAME
, HP.party_number CUST_NUM
, APSA.gl_date GL_DATE
, RCT.trx_date TRX_DATE
, RT.name PAYMENT_TERMS
, APSA.due_date DUE_DATE
, RCT.trx_number DOC_NUM
, RCTTA.name TRX_TYPE
, RCTTA.TYPE TRX_CLASS
, RCT.purchase_order PO
, CASE WHEN RBSA.NAME='Global Intercompany' THEN RCT.interface_header_attribute4 ELSE RCT.interface_header_attribute1 END CROSS_REF
, NULL CLAIM
, RCT.reason_code REASON
, NULL CUST_REF
, APSA.amount_due_original AMT_ENT
, ROUND(AR_ARXAGMW_XMLP_PKG.DUE_AMT_AS_OF_DATE(NVL(TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD'),TRUNC(SYSDATE)),apsa.amount_due_remaining,apsa.payment_schedule_id,apsa.class),2) BAL_ENT
, RCT.invoice_currency_code CURRENCY
, RCT.exchange_rate RATE
, NVL(APSA.amount_due_original,0)*NVL(RCT.exchange_rate,1) AMT_FUNT
, ROUND(AR_ARXAGMW_XMLP_PKG.DUE_AMT_AS_OF_DATE(NVL(TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD'),TRUNC(SYSDATE)),apsa.amount_due_remaining,apsa.payment_schedule_id,apsa.class),2)*NVL(RCT.exchange_rate,1) BAL_FUN
, CASE WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(APSA.due_date) BETWEEN 1 AND 30 THEN '1-30'
WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(APSA.due_date) BETWEEN 31 AND 60 THEN '31-60'
WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(APSA.due_date) BETWEEN 61 AND 90 THEN '61-90'
WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(APSA.due_date) BETWEEN 91 AND 180 THEN '91-180'
WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(APSA.due_date) BETWEEN 181 AND 365 THEN '181-365'
WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(APSA.due_date)>365 THEN '365+' ELSE 'CURRENT' END AGEBUC
, TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') -TRUNC(APSA.due_date) AGED_DAYS
, CASE WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(APSA.due_date) <= 0 THEN
(CASE WHEN TRUNC(APSA.due_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 0 AND 7 THEN 'Week1'
WHEN TRUNC(APSA.due_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 8 AND 14 THEN 'Week2'
WHEN TRUNC(APSA.due_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 15 AND 21 THEN 'Week3'
WHEN TRUNC(APSA.due_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 22 AND 28 THEN 'Week4'
WHEN TRUNC(APSA.due_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 29 AND 35 THEN 'Week5'
WHEN TRUNC(APSA.due_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 36 AND 42 THEN 'Week6'
WHEN TRUNC(APSA.due_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 43 AND 49 THEN 'Week7'
WHEN TRUNC(APSA.due_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 50 AND 56 THEN 'Week8'
WHEN TRUNC(APSA.due_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 57 AND 63 THEN 'Week9'
WHEN TRUNC(APSA.due_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 64 AND 70 THEN 'Week10'
WHEN TRUNC(APSA.due_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 71 AND 77 THEN 'Week11'
WHEN TRUNC(APSA.due_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 78 AND 84 THEN 'Week12'
WHEN TRUNC(APSA.due_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 85 AND 91 THEN 'Week13'
WHEN TRUNC(APSA.due_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 92 AND 98 THEN 'Week14'
WHEN TRUNC(APSA.due_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') > 98 THEN 'Week15+' END
) END WEEK
, RCT.attribute1 GOVT_INV_NUM
, GTA.gta_trx_number GOLD_TAX
, RCT.attribute2 SALESPERSON
, RCT.attribute3 SHIP_TO
, RCT.attribute4 DELIVER_DATE
, RCT.attribute5 ASN
, RCT.attribute6 LC_BANK
, RCT.attribute7 LC_NUM
, CASE WHEN RCT.attribute_category='Import Export Receivables' THEN RCT.attribute11 END SHIP_FROM
, CASE WHEN RCT.attribute_category='Import Export Receivables' THEN RCT.attribute8 END SHIP_STATUS
, CASE WHEN RCT.attribute_category='Import Export Receivables' THEN RCT.attribute13 END ETD
, CASE WHEN RCT.attribute_category='Import Export Receivables' THEN RCT.attribute14 END SUBMIT_DATE
, ACI.cons_billing_number BILL_NUM
, NULL RCT_STS
, CASE WHEN RCT.attribute_category='XXC_COPROCESS_IC_TRANS_STATUS' THEN RCT.attribute11 END COPRO_STATUS
--, RCT.attribute15 VAT
, HCASA.attribute7 OMS_STATUS
, HCASA.attribute5 AKA
, NVL(HCASA.attribute6,HP.party_name) CUST_GRP
, HCPF.credit_hold CR_HD
, NVL(NVL(NVL((SELECT
SCL.site_cr_limit_val
FROM
site_cr_lmt SCL
WHERE
1 =1
AND HCPF.cust_account_profile_id=SCL.cust_account_profile_id)
, (SELECT
HCPA.overall_credit_limit
FROM
hz_cust_profile_amts_f HCPA
WHERE
1 =1
AND HCPF.cust_account_profile_id=HCPA.cust_account_profile_id
AND HCPF.effective_start_date =HCPA.effective_start_date
AND HCPF.effective_end_date =HCPA.effective_end_date
AND GL.currency_code =HCPA.currency_code)
)
, (SELECT
ACL.acc_cr_limit_val
FROM
acc_cr_lmt ACL
WHERE
1 =1
AND HCPF.cust_account_profile_id=ACL.cust_account_profile_id))
, (SELECT
HCPA1.overall_credit_limit
FROM
hz_customer_profiles_f HCPF1
, hz_cust_profile_amts_f HCPA1
WHERE
1 =1
AND HCA.cust_account_id =HCPF1.cust_account_id
AND HCPF1.cust_account_profile_id=HCPA1.cust_account_profile_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(HCPF1.effective_start_date) AND TRUNC(HCPF1.effective_end_date)
AND HCPF1.site_use_id IS NULL
AND HCPF1.effective_start_date =HCPA1.effective_start_date
AND HCPF1.effective_end_date =HCPA1.effective_end_date
AND GL.currency_code =HCPA1.currency_code)) CR_LMT
-- , SUM(AR_ARXAGMW_XMLP_PKG.DUE_AMT_AS_OF_DATE(NVL(TO_DATE(:P_AS_OF_DATE,'YYYY-MM-DD'),TRUNC(SYSDATE)),apsa.amount_due_remaining,apsa.payment_schedule_id,apsa.class)) OVER (PARTITION BY HP.party_number) NET_BAL
FROM
hr_organization_units HOU
, ra_customer_trx_all RCT
, xle_entity_profiles XEP
/*, (
SELECT
RDA.customer_trx_id
, RDA.code_combination_id
, SUM(RDA.amount) AMOUNT
FROM
ra_cust_trx_line_gl_dist_all RDA
WHERE
RDA.account_class ='REC'
AND NVL(RDA.latest_rec_flag,'Y') ='Y'
GROUP BY
RDA.customer_trx_id
, RDA.code_combination_id
) DIST
, gl_code_combinations GCC*/
, hz_cust_accounts HCA
, hz_parties HP
, hz_party_sites HPS
, hz_cust_acct_sites_all HCASA
, hz_cust_site_uses_all HCSUA
, hz_customer_profiles_f HCPF
--, hz_cust_profile_amts_f HCPA
, hz_cust_profile_classes HCP
, ra_terms RT
, ar_payment_schedules_all APSA
, ra_cust_trx_types_all RCTTA
, ra_batch_sources_all RBSA
, ja_cn_trx_headers_all GTA
, ar_cons_inv_all ACI
, gl_ledgers GL
, trx_acc TA
WHERE
1 =1
AND HOU.organization_id =RCT.org_id
AND XEP.legal_entity_id =RCT.legal_entity_id
AND RCT.set_of_books_id =GL.ledger_id
--AND RCT.customer_trx_id =DIST.customer_trx_id
--AND DIST.code_combination_id =GCC.code_combination_id
AND RCT.bill_to_customer_id =HCA.cust_account_id
AND HCA.party_id =HP.party_id
AND RCT.bill_to_site_use_id =HCSUA.site_use_id
AND HCSUA.cust_acct_site_id =HCASA.cust_acct_site_id
AND HCASA.party_site_id =HPS.party_site_id
AND HP.party_id =HPS.party_id
AND HCA.cust_account_id =HCPF.cust_account_id
AND HCSUA.site_use_id =HCPF.site_use_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(HCPF.effective_start_date) AND TRUNC(HCPF.effective_end_date)
AND HCPF.site_use_id IS NOT NULL
AND HCPF.profile_class_id =HCP.profile_class_id(+)
AND RCT.term_id =RT.term_id(+)
AND RCT.customer_trx_id =APSA.customer_trx_id
AND RCT.customer_trx_id =TA.customer_trx_id
AND RCT.cust_trx_type_seq_id =RCTTA.cust_trx_type_seq_id
AND RCT.batch_source_seq_id =RBSA.batch_source_seq_id
AND RCT.customer_trx_id =GTA.ra_trx_id(+)
AND APSA.cons_inv_id =ACI.cons_inv_id(+)
--AND RCT.TRX_NUMBER='15000'--'234174'
AND trunc(APSA.gl_date) <=TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD')
AND trunc(APSA.gl_date_closed) >TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD')
AND (HCA.customer_type IN (:P_CUST_TYPE) OR 'All' IN (:P_CUST_TYPE||'All'))
AND (HOU.name IN (:P_BU) OR 'All' IN (:P_BU||'All'))
AND (XEP.name IN (:P_LE) OR 'All' IN (:P_LE||'All'))
AND (TA.segment5 IN (:P_LOC) OR 'All' IN (:P_LOC||'All'))
AND (TA.segment2 IN (:P_SM) OR 'All' IN (:P_SM||'All'))
AND (HCASA.attribute2 IN (:P_SELL_PROF) OR 'All' IN (:P_SELL_PROF||'All'))
UNION ALL
SELECT
(SELECT distinct FLV.attribute1
FROM fnd_flex_values_vl FLV
WHERE
FLV.value_category='XXC_CLOUD_BU_TO_REGION_CODE_MAPPING'
AND FLV.enabled_flag = 'Y'
AND FLV.flex_value=HOU.name) REGION
, HOU.name BU
, RA.le_desc LE
, RA.segment1
, RA.segment2
, RA.segment3
, RA.segment4
, RA.segment5
, RA.segment6
, RA.segment7
, RA.segment8
, RA.segment9
, HCA.customer_class_code
, CASE WHEN HCP.name='DEFAULT' THEN HCASA.attribute2 ELSE HCP.name||' '||HCASA.attribute2 END PROFILE_CLASS
, HCSUA.location LOC_DESC
, HCA.account_number CUSTOMER_NUM
, HP.party_name CUSTOMER_NAME
, HP.party_number CUST_NUM
, APSA.gl_date GL_DATE
, ACR.receipt_date TRX_DATE
, NULL PAYMENT_TERMS
, ACR.receipt_date DUE_DATE
, ACR.receipt_number DOC_NUM
, 'Unapplied' TRX_TYPE
, 'PMT' TRX_CLASS
, NULL PO
, NULL CROSS_REF
, NULL CLAIM
, NULL REASON
, NULL CUST_REF
, ACR.amount AMT_ENT
--, NVL(AR.applied_receipt_amount,0)*-1 BAL_ENT
, NVL(RA.bal_amount,0) BAL_ENT
, ACR.currency_code CURRENCY
, ACR.exchange_rate RATE
, (NVL(ACR.amount,0)*NVL(ACR.exchange_rate,1)) AMT_FUNT
--, (NVL(AR.applied_receipt_amount,0)*NVL(ACR.exchange_rate,1))*-1 BAL_FUN
, (NVL(RA.bal_amount,0)*NVL(ACR.exchange_rate,1)) BAL_FUN
, CASE WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(ACR.receipt_date) BETWEEN 1 AND 30 THEN '1-30'
WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(ACR.receipt_date) BETWEEN 31 AND 60 THEN '31-60'
WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(ACR.receipt_date) BETWEEN 61 AND 90 THEN '61-90'
WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(ACR.receipt_date) BETWEEN 91 AND 180 THEN '91-180'
WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(ACR.receipt_date) BETWEEN 181 AND 365 THEN '181-365'
WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(ACR.receipt_date)>365 THEN '365+' ELSE 'CURRENT' END AGEBUC
, TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') -TRUNC(ACR.receipt_date ) AGED_DAYS
, CASE WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(ACR.receipt_date ) <= 0 THEN
(CASE WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 0 AND 7 THEN 'Week1'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 8 AND 14 THEN 'Week2'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 15 AND 21 THEN 'Week3'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 22 AND 28 THEN 'Week4'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 29 AND 35 THEN 'Week5'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 36 AND 42 THEN 'Week6'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 43 AND 49 THEN 'Week7'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 50 AND 56 THEN 'Week8'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 57 AND 63 THEN 'Week9'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 64 AND 70 THEN 'Week10'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 71 AND 77 THEN 'Week11'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 78 AND 84 THEN 'Week12'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 85 AND 91 THEN 'Week13'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 92 AND 98 THEN 'Week14'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') > 98 THEN 'Week15+' END
) END WEEK
, NULL GOVT_INV_NUM
, NULL GOLD_TAX
, NULL SALES_PERSON
, NULL SHIP_TO
, NULL DELIVER_DATE
, NULL ASN
, NULL LC_BANK
, NULL LC_NUM
, NULL SHIP_FROM
, NULL SHIP_STATUS
, NULL ETD
, NULL SUBMIT_DATE
, NULL BILL_NUM
, ACRH.STATUS RCT_STS
, NULL COPRO_STATUS
, HCASA.attribute7 OMS_STATUS
, HCASA.attribute5 AKA
, NVL(HCASA.attribute6,HP.party_name) CUST_GRP
, HCPF.credit_hold CR_HD
, NVL(NVL(NVL((SELECT
SCL.site_cr_limit_val
FROM
site_cr_lmt SCL
WHERE
1 =1
AND HCPF.cust_account_profile_id=SCL.cust_account_profile_id)
, (SELECT
HCPA.overall_credit_limit
FROM
hz_cust_profile_amts_f HCPA
WHERE
1 =1
AND HCPF.cust_account_profile_id=HCPA.cust_account_profile_id
AND HCPF.effective_start_date =HCPA.effective_start_date
AND HCPF.effective_end_date =HCPA.effective_end_date
AND GL.currency_code =HCPA.currency_code)
)
, (SELECT
ACL.acc_cr_limit_val
FROM
acc_cr_lmt ACL
WHERE
1 =1
AND HCPF.cust_account_profile_id=ACL.cust_account_profile_id))
, (SELECT
HCPA1.overall_credit_limit
FROM
hz_customer_profiles_f HCPF1
, hz_cust_profile_amts_f HCPA1
WHERE
1 =1
AND HCA.cust_account_id =HCPF1.cust_account_id
AND HCPF1.cust_account_profile_id=HCPA1.cust_account_profile_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(HCPF1.effective_start_date) AND TRUNC(HCPF1.effective_end_date)
AND HCPF1.site_use_id IS NULL
AND HCPF1.effective_start_date =HCPA1.effective_start_date
AND HCPF1.effective_end_date =HCPA1.effective_end_date
AND GL.currency_code =HCPA1.currency_code)) CR_LMT
--, SUM(NVL(AR.applied_receipt_amount,0)) OVER (PARTITION BY HP.party_number) NET_BAL
--CR/not CR
FROM
hr_organization_units HOU
, ar_cash_receipts_all ACR
, ar_cash_receipt_history_all ACRH
, xle_entity_profiles XEP
, hz_cust_accounts HCA
, hz_parties HP
, hz_party_sites HPS
, hz_cust_acct_sites_all HCASA
, hz_cust_site_uses_all HCSUA
, hz_customer_profiles_f HCPF
, hz_cust_profile_classes HCP
, ar_payment_schedules_all APSA
, rec_acc RA
--, applied_receipts AR
, gl_ledgers GL
WHERE
1 =1
AND HOU.organization_id =ACR.org_id
AND XEP.legal_entity_id =ACR.legal_entity_id
AND ACR.set_of_books_id =GL.ledger_id
AND ACR.pay_from_customer =HCA.cust_account_id
AND HCA.party_id =HP.party_id
AND ACR.customer_site_use_id =HCSUA.site_use_id
AND HCSUA.cust_acct_site_id =HCASA.cust_acct_site_id
AND HCASA.party_site_id =HPS.party_site_id
AND HP.party_id =HPS.party_id
AND HCA.cust_account_id =HCPF.cust_account_id
AND HCSUA.site_use_id =HCPF.site_use_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(HCPF.effective_start_date) AND TRUNC(HCPF.effective_end_date)
AND HCPF.site_use_id IS NOT NULL
AND HCPF.profile_class_id =HCP.profile_class_id(+)
AND ACR.cash_receipt_id =ACRH.cash_receipt_id
AND ACRH.current_record_flag ='Y'
AND ACR.cash_receipt_id =APSA.cash_receipt_id
AND NVL(APSA.receipt_confirmed_flag,'Y') = 'Y'
AND ACR.cash_receipt_id =RA.cash_receipt_id
AND RA.accounting_class_code ='UNAPP'
--AND trunc(APSA.gl_date) <=TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD')
--AND trunc(APSA.gl_date_closed) > TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD')
AND (HCA.customer_type IN (:P_CUST_TYPE) OR 'All' IN (:P_CUST_TYPE||'All'))
AND (HOU.name IN (:P_BU) OR 'All' IN (:P_BU||'All'))
AND (RA.le_desc IN (:P_LE) OR 'All' IN (:P_LE||'All'))
AND (RA.segment5 IN (:P_LOC) OR 'All' IN (:P_LOC||'All'))
AND (RA.segment2 IN (:P_SM) OR 'All' IN (:P_SM||'All'))
AND (HCASA.attribute2 IN (:P_SELL_PROF) OR 'All' IN (:P_SELL_PROF||'All'))
UNION ALL
SELECT
(SELECT distinct FLV.attribute1
FROM fnd_flex_values_vl FLV
WHERE
FLV.value_category='XXC_CLOUD_BU_TO_REGION_CODE_MAPPING'
AND FLV.enabled_flag = 'Y'
AND FLV.flex_value=HOU.name) REGION
, HOU.name BU
, RA.le_desc LE
, RA.segment1
, RA.segment2
, RA.segment3
, RA.segment4
, RA.segment5
, RA.segment6
, RA.segment7
, RA.segment8
, RA.segment9
, HCA.customer_class_code
, CASE WHEN HCP.name='DEFAULT' THEN HCASA.attribute2 ELSE HCP.name||' '||HCASA.attribute2 END PROFILE_CLASS
, HCSUA.location LOC_DESC
, HCA.account_number CUSTOMER_NUM
, HP.party_name CUSTOMER_NAME
, HP.party_number CUST_NUM
, APSA.gl_date GL_DATE
, ACR.receipt_date TRX_DATE
, NULL PAYMENT_TERMS
, ACR.receipt_date DUE_DATE
, ACR.receipt_number DOC_NUM
, 'On Account' TRX_TYPE
, 'PMT' TRX_CLASS
, NULL PO
, NULL CROSS_REF
, NULL CLAIM
, NULL REASON
, NULL CUST_REF
, 0 AMT_ENT
--, NVL(AR.applied_receipt_amount,0)*-1 BAL_ENT
, NVL(RA.bal_amount,0) BAL_ENT
, ACR.currency_code CURRENCY
, ACR.exchange_rate RATE
, 0 AMT_FUNT
--, (NVL(AR.applied_receipt_amount,0)*NVL(ACR.exchange_rate,1))*-1 BAL_FUN
, (NVL(RA.bal_amount,0)*NVL(ACR.exchange_rate,1)) BAL_FUN
, CASE WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(ACR.receipt_date) BETWEEN 1 AND 30 THEN '1-30'
WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(ACR.receipt_date) BETWEEN 31 AND 60 THEN '31-60'
WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(ACR.receipt_date) BETWEEN 61 AND 90 THEN '61-90'
WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(ACR.receipt_date) BETWEEN 91 AND 180 THEN '91-180'
WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(ACR.receipt_date) BETWEEN 181 AND 365 THEN '181-365'
WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(ACR.receipt_date)>365 THEN '365+' ELSE 'CURRENT' END AGEBUC
, TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') -TRUNC(ACR.receipt_date ) AGED_DAYS
, CASE WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(ACR.receipt_date ) <= 0 THEN
(CASE WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 0 AND 7 THEN 'Week1'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 8 AND 14 THEN 'Week2'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 15 AND 21 THEN 'Week3'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 22 AND 28 THEN 'Week4'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 29 AND 35 THEN 'Week5'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 36 AND 42 THEN 'Week6'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 43 AND 49 THEN 'Week7'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 50 AND 56 THEN 'Week8'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 57 AND 63 THEN 'Week9'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 64 AND 70 THEN 'Week10'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 71 AND 77 THEN 'Week11'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 78 AND 84 THEN 'Week12'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 85 AND 91 THEN 'Week13'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 92 AND 98 THEN 'Week14'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') > 98 THEN 'Week15+' END
) END WEEK
, NULL GOVT_INV_NUM
, NULL GOLD_TAX
, NULL SALES_PERSON
, NULL SHIP_TO
, NULL DELIVER_DATE
, NULL ASN
, NULL LC_BANK
, NULL LC_NUM
, NULL SHIP_FROM
, NULL SHIP_STATUS
, NULL ETD
, NULL SUBMIT_DATE
, NULL BILL_NUM
, ACRH.STATUS RCT_STS
, NULL COPRO_STATUS
, HCASA.attribute7 OMS_STATUS
, HCASA.attribute5 AKA
, NVL(HCASA.attribute6,HP.party_name) CUST_GRP
, HCPF.credit_hold CR_HD
, NVL(NVL(NVL((SELECT
SCL.site_cr_limit_val
FROM
site_cr_lmt SCL
WHERE
1 =1
AND HCPF.cust_account_profile_id=SCL.cust_account_profile_id)
, (SELECT
HCPA.overall_credit_limit
FROM
hz_cust_profile_amts_f HCPA
WHERE
1 =1
AND HCPF.cust_account_profile_id=HCPA.cust_account_profile_id
AND HCPF.effective_start_date =HCPA.effective_start_date
AND HCPF.effective_end_date =HCPA.effective_end_date
AND GL.currency_code =HCPA.currency_code)
)
, (SELECT
ACL.acc_cr_limit_val
FROM
acc_cr_lmt ACL
WHERE
1 =1
AND HCPF.cust_account_profile_id=ACL.cust_account_profile_id))
, (SELECT
HCPA1.overall_credit_limit
FROM
hz_customer_profiles_f HCPF1
, hz_cust_profile_amts_f HCPA1
WHERE
1 =1
AND HCA.cust_account_id =HCPF1.cust_account_id
AND HCPF1.cust_account_profile_id=HCPA1.cust_account_profile_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(HCPF1.effective_start_date) AND TRUNC(HCPF1.effective_end_date)
AND HCPF1.site_use_id IS NULL
AND HCPF1.effective_start_date =HCPA1.effective_start_date
AND HCPF1.effective_end_date =HCPA1.effective_end_date
AND GL.currency_code =HCPA1.currency_code)) CR_LMT
FROM
hr_organization_units HOU
, ar_cash_receipts_all ACR
, ar_cash_receipt_history_all ACRH
, xle_entity_profiles XEP
, hz_cust_accounts HCA
, hz_parties HP
, hz_party_sites HPS
, hz_cust_acct_sites_all HCASA
, hz_cust_site_uses_all HCSUA
, hz_customer_profiles_f HCPF
, hz_cust_profile_classes HCP
, ar_payment_schedules_all APSA
--, onacc_receipts AR
, rec_acc RA
, gl_ledgers GL
WHERE
1 =1
AND HOU.organization_id =ACR.org_id
AND XEP.legal_entity_id =ACR.legal_entity_id
AND ACR.set_of_books_id =GL.ledger_id
AND ACR.pay_from_customer =HCA.cust_account_id
AND HCA.party_id =HP.party_id
AND ACR.customer_site_use_id =HCSUA.site_use_id
AND HCSUA.cust_acct_site_id =HCASA.cust_acct_site_id
AND HCASA.party_site_id =HPS.party_site_id
AND HP.party_id =HPS.party_id
AND HCA.cust_account_id =HCPF.cust_account_id
AND HCSUA.site_use_id =HCPF.site_use_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(HCPF.effective_start_date) AND TRUNC(HCPF.effective_end_date)
AND HCPF.site_use_id IS NOT NULL
AND HCPF.profile_class_id =HCP.profile_class_id(+)
AND ACR.cash_receipt_id =ACRH.cash_receipt_id
AND ACRH.current_record_flag ='Y'
AND ACR.cash_receipt_id =APSA.cash_receipt_id
AND NVL(APSA.receipt_confirmed_flag,'Y') = 'Y'
AND ACR.cash_receipt_id =RA.cash_receipt_id
AND RA.accounting_class_code ='ACC'
--AND trunc(APSA.gl_date) <=TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD')
--AND trunc(APSA.gl_date_closed) > TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD')
AND (HCA.customer_type IN (:P_CUST_TYPE) OR 'All' IN (:P_CUST_TYPE||'All'))
AND (HOU.name IN (:P_BU) OR 'All' IN (:P_BU||'All'))
AND (RA.le_desc IN (:P_LE) OR 'All' IN (:P_LE||'All'))
AND (RA.segment5 IN (:P_LOC) OR 'All' IN (:P_LOC||'All'))
AND (RA.segment2 IN (:P_SM) OR 'All' IN (:P_SM||'All'))
AND (HCASA.attribute2 IN (:P_SELL_PROF) OR 'All' IN (:P_SELL_PROF||'All'))
UNION ALL
SELECT
(SELECT distinct FLV.attribute1
FROM fnd_flex_values_vl FLV
WHERE
FLV.value_category='XXC_CLOUD_BU_TO_REGION_CODE_MAPPING'
AND FLV.enabled_flag = 'Y'
AND FLV.flex_value=HOU.name) REGION
, HOU.name BU
, RA.le_desc LE
, RA.segment1
, RA.segment2
, RA.segment3
, RA.segment4
, RA.segment5
, RA.segment6
, RA.segment7
, RA.segment8
, RA.segment9
, HCA.customer_class_code
, CASE WHEN HCP.name='DEFAULT' THEN HCASA.attribute2 ELSE HCP.name||' '||HCASA.attribute2 END PROFILE_CLASS
, HCSUA.location LOC_DESC
, HCA.account_number CUSTOMER_NUM
, HP.party_name CUSTOMER_NAME
, HP.party_number CUST_NUM
, ARA.gl_date GL_DATE
, CCA.claim_date TRX_DATE
, NULL PAYMENT_TERMS
, ACR.receipt_date DUE_DATE
, ACR.receipt_number DOC_NUM
, CCT.claim_type_name TRX_TYPE
, 'CLM' TRX_CLASS
, NULL PO
, NULL CROSS_REF
, CCA.claim_number CLAIM
, CCR.reason_code_name REASON
, CCA.customer_ref_number CUST_REF
, 0 AMT_ENT
, NVL(CCA.amount,0) - NVL(CCA.amount_adjusted,0) BAL_ENT
, ACR.currency_code CURRENCY
, ACR.exchange_rate RATE
, 0 AMT_FUNT
, NVL(CCA.acctd_amount,0) - NVL(CCA.acctd_amount_adjusted,0) BAL_FUN
, CASE WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(ACR.receipt_date) BETWEEN 1 AND 30 THEN '1-30'
WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(ACR.receipt_date) BETWEEN 31 AND 60 THEN '31-60'
WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(ACR.receipt_date) BETWEEN 61 AND 90 THEN '61-90'
WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(ACR.receipt_date) BETWEEN 91 AND 180 THEN '91-180'
WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(ACR.receipt_date) BETWEEN 181 AND 365 THEN '181-365'
WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(ACR.receipt_date)>365 THEN '365+' ELSE 'CURRENT' END AGEBUC
, TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') -TRUNC(ACR.receipt_date ) AGED_DAYS
, CASE WHEN TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') - TRUNC(ACR.receipt_date ) <= 0 THEN
(CASE WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 0 AND 7 THEN 'Week1'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 8 AND 14 THEN 'Week2'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 15 AND 21 THEN 'Week3'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 22 AND 28 THEN 'Week4'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 29 AND 35 THEN 'Week5'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 36 AND 42 THEN 'Week6'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 43 AND 49 THEN 'Week7'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 50 AND 56 THEN 'Week8'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 57 AND 63 THEN 'Week9'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 64 AND 70 THEN 'Week10'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 71 AND 77 THEN 'Week11'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 78 AND 84 THEN 'Week12'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 85 AND 91 THEN 'Week13'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') BETWEEN 92 AND 98 THEN 'Week14'
WHEN TRUNC(ACR.receipt_date) - TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD') > 98 THEN 'Week15+' END
) END WEEK
, NULL GOVT_INV_NUM
, NULL GOLD_TAX
, NULL SALES_PERSON
, NULL SHIP_TO
, NULL DELIVER_DATE
, NULL ASN
, NULL LC_BANK
, NULL LC_NUM
, NULL SHIP_FROM
, NULL SHIP_STATUS
, NULL ETD
, NULL SUBMIT_DATE
, NULL BILL_NUM
, ACRH.STATUS RCT_STS
, NULL COPRO_STATUS
, HCASA.attribute7 OMS_STATUS
, HCASA.attribute5 AKA
, NVL(HCASA.attribute6,HP.party_name) CUST_GRP
, HCPF.credit_hold CR_HD
, NVL(NVL(NVL((SELECT
SCL.site_cr_limit_val
FROM
site_cr_lmt SCL
WHERE
1 =1
AND HCPF.cust_account_profile_id=SCL.cust_account_profile_id)
, (SELECT
HCPA.overall_credit_limit
FROM
hz_cust_profile_amts_f HCPA
WHERE
1 =1
AND HCPF.cust_account_profile_id=HCPA.cust_account_profile_id
AND HCPF.effective_start_date =HCPA.effective_start_date
AND HCPF.effective_end_date =HCPA.effective_end_date
AND GL.currency_code =HCPA.currency_code)
)
, (SELECT
ACL.acc_cr_limit_val
FROM
acc_cr_lmt ACL
WHERE
1 =1
AND HCPF.cust_account_profile_id=ACL.cust_account_profile_id))
, (SELECT
HCPA1.overall_credit_limit
FROM
hz_customer_profiles_f HCPF1
, hz_cust_profile_amts_f HCPA1
WHERE
1 =1
AND HCA.cust_account_id =HCPF1.cust_account_id
AND HCPF1.cust_account_profile_id=HCPA1.cust_account_profile_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(HCPF1.effective_start_date) AND TRUNC(HCPF1.effective_end_date)
AND HCPF1.site_use_id IS NULL
AND HCPF1.effective_start_date =HCPA1.effective_start_date
AND HCPF1.effective_end_date =HCPA1.effective_end_date
AND GL.currency_code =HCPA1.currency_code)) CR_LMT
FROM
hr_organization_units HOU
, cjm_claims_all CCA
, cjm_claim_types_tl CCT
, cjm_claim_reason_codes_tl CCR
, ar_cash_receipts_all ACR
, ar_cash_receipt_history_all ACRH
, ar_payment_schedules_all APSA
, xle_entity_profiles XEP
, hz_cust_accounts HCA
, hz_parties HP
, hz_party_sites HPS
, hz_cust_acct_sites_all HCASA
, hz_cust_site_uses_all HCSUA
, hz_customer_profiles_f HCPF
, hz_cust_profile_classes HCP
, ar_receivable_applications_all ARA
, gl_ledgers GL
, rec_acc RA
WHERE
1 =1
AND HOU.organization_id =CCA.BU_ID
AND XEP.legal_entity_id =CCA.legal_entity_id
AND CCA.bill_to_customer_id =HCA.cust_account_id
AND HCA.party_id =HP.party_id
AND CCA.bill_to_site_use_id =HCSUA.site_use_id
AND HCSUA.cust_acct_site_id =HCASA.cust_acct_site_id
AND HCASA.party_site_id =HPS.party_site_id
AND HP.party_id =HPS.party_id
AND HCA.cust_account_id =HCPF.cust_account_id
AND HCSUA.site_use_id =HCPF.site_use_id
AND TRUNC(SYSDATE) BETWEEN TRUNC(HCPF.effective_start_date) AND TRUNC(HCPF.effective_end_date)
AND HCPF.site_use_id IS NOT NULL
AND HCPF.profile_class_id =HCP.profile_class_id(+)
AND CCA.claim_type_id =CCT.claim_type_id(+)
AND CCT.LANGUAGE(+) =USERENV('LANG')
AND CCA.reason_code_id =CCR.reason_code_id(+)
AND CCR.LANGUAGE(+) =USERENV('LANG')
AND CCA.receivable_application_id=ARA.receivable_application_id
--AND ARA.code_combination_id =GCC.code_combination_id
AND ARA.cash_receipt_id =ACR.cash_receipt_id
AND ACR.set_of_books_id =GL.ledger_id
AND ACR.cash_receipt_id =APSA.cash_receipt_id
AND NVL(APSA.receipt_confirmed_flag,'Y') = 'Y'
AND ACR.cash_receipt_id =RA.cash_receipt_id
AND RA.accounting_class_code ='CLAIM'
AND ACR.cash_receipt_id =ACRH.cash_receipt_id
AND ACRH.current_record_flag ='Y'
--AND CCA.status_code ='OPEN'
AND trunc(ARA.gl_date) <=TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD')
AND ((ARA.reversal_gl_date IS NOT NULL AND ARA.reversal_gl_date > TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD')) OR ARA.reversal_gl_date IS NULL)
AND (HCA.customer_type IN (:P_CUST_TYPE) OR 'All' IN (:P_CUST_TYPE||'All'))
AND (HOU.NAME IN (:P_BU) OR 'All' IN (:P_BU||'All'))
AND (RA.le_desc IN (:P_LE) OR 'All' IN (:P_LE||'All'))
AND (RA.segment5 IN (:P_LOC) OR 'All' IN (:P_LOC||'All'))
AND (RA.segment2 IN (:P_SM) OR 'All' IN (:P_SM||'All'))
AND (HCASA.attribute2 IN (:P_SELL_PROF) OR 'All' IN (:P_SELL_PROF||'All'))
)
WHERE 1=1
AND (REGION IN (:P_REGION) OR 'All' IN (:P_REGION||'All'))
AND BU NOT IN ('CY BU USD','RU BU RUB')
AND NVL(BAL_ENT,0) <>0
ORDER BY
CUSTOMER_NAME
, CUSTOMER_NUM
, BU
, LE
, GL_DATE
, TRX_DATE:P_AS_OF_DATE :P_BU :P_CUST_TYPE :P_LE :P_LOC :P_REGION :P_SELL_PROF :P_SM
The data-warehouse model — one fact surrounded by conformed dimensions (what you slice by) and measures (what you aggregate), expressed as dbt so it migrates with you. Grain: one row per source transaction.
●— fact → dimension join
| Element | Type | Definition |
|---|---|---|
| AR_CASH_RECEIPTS_ALL | dimension | dimension |
| XLA_AE_LINES | dimension | dimension |
| XLA_AE_HEADERS | dimension | dimension |
| XLA_TRANSACTION_ENTITIES | dimension | dimension |
| Open Amount | measure | measure |
Runs on your cloud warehouse — Snowflake, BigQuery, Redshift, or Synapse on AWS, Google Cloud, Azure, or any provider. Reconciled to the source control total — 0% variance by design. You own the code, the model, and the data.
How the data gets here: a BICC bulk extract of the source tables above, on the same pattern for every report. See the extraction pattern & data flow →
See the complete model
How this report's fact and dimensions fit the full picture, via conformed keys.
Every source object behind this report. Each linked table has its own page with full column descriptions, drawn from the Oracle BICC lineage and articulated for practitioners.
| Table | Reporting columns | Subject areas |
|---|---|---|
| FND_FLEX_VALUES_VL | 72 | 0 |
| AR_CASH_RECEIPTS_ALL | 25 | 9 |
| XLA_AE_LINES | 23 | 17 |
| XLA_AE_HEADERS | 16 | 19 |
| XLA_TRANSACTION_ENTITIES | 2 | 3 |
| GL_CODE_COMBINATIONS | 7 | 61 |
| RA_CUSTOMER_TRX_ALL | 58 | 16 |
| AR_RECEIVABLE_APPLICATIONS_ALL | 35 | 2 |
| HZ_CUST_PROFILE_AMTS_F | 22 | 1 |
| CR_LMT_CONDITION | Setup / configuration table — joined for reference, not exposed for analytics | |
| HZ_CUSTOMER_PROFILES_F | 47 | 1 |
| SITE_CR_LMT | Setup / configuration table — joined for reference, not exposed for analytics | |
Reporting columns = fields the report selects that are exposed as analytics attributes; subject areas = the OTBI subject areas the table appears in. Setup and configuration tables (master data, ledger and book setup, lookups) are referenced by the report's joins but aren't exposed as analytics columns or subject areas — that's expected, not a gap.