Oracle Fusion · Intercompany
Intercompany Invoiced Reconciliation Report
Intercompany
Reconciles intercompany transactions that flowed through Payables and Receivables as invoices — matching the payable one entity raised to the receivable the other booked — so finance can confirm invoiced intercompany activity agrees on both sides.
Related Part of the reconciliation family — see also the Global Intercompany Balances Report and the subledger-to-GL reconciliation reports.
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 Intercompany Invoiced Reconciliation Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.
Intercompany Invoiced Reconciliation Report
Sample build · illustrativeFilters
Customer Name
Globex Holdings
From Gl Date
2026-02-28
Supplier Name
Globex Holdings
To Gl Date
2026-02-28
320
Invoice pairs
94.7%
Matched
$680K
Unmatched
| From Entity | To Entity | Ap Invoice | Ar Transaction | Ap Amount | Ar Amount | Difference |
|---|---|---|---|---|---|---|
| Acme Industrial | Acme Industrial | Sample | Sample | $1,240,500.00 | $1,240,500.00 | Sample |
| Northwind Trading | Northwind Trading | — | — | $842,150.75 | $842,150.75 | — |
| Globex Holdings | Globex Holdings | Sample | Sample | $96,400.00 | $96,400.00 | Sample |
| Initech LLC | Initech LLC | — | — | $1,005,233.10 | $1,005,233.10 | — |
| Umbrella Corp | Umbrella Corp | Sample | Sample | $58,720.40 | $58,720.40 | Sample |
| Acme Industrial | Acme Industrial | Sample | Sample | $1,240,500.00 | $1,240,500.00 | Sample |
AI Analyst · active
reading
The report pairs intercompany payables in AP_INVOICES_ALL with the matching receivables in RA_CUSTOMER_TRX_ALL, comparing amounts on both sides.
flag
$680K of invoiced intercompany activity has a payable with no matching receivable or the reverse — usually one side invoiced and the other hadn't booked it by cut-off.
root cause & next step
Align intercompany invoicing to a shared cut-off so both sides book in the same period; persistent one-sided invoices point to a missing trading-partner setup.
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
- AP_INVOICES_ALL
- AP_INVOICE_PAYMENTS_ALL
- RA_CUSTOMER_TRX_ALL
- AR_PAYMENT_SCHEDULES_ALL
- XLA_AE_LINES
- GL_CODE_COMBINATIONS
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 AP_PAY AS
(
SELECT DISTINCT
APSA.PAYMENT_METHOD_CODE
, ALV3.MEANING PAYMENT_METHOD
, AIA.INVOICE_ID
, AIA.INVOICE_NUM
FROM
AP_INVOICES_ALL AIA
, AP_PAYMENT_SCHEDULES_ALL APSA
, AP_INVOICE_PAYMENTS_ALL AIPA
, AP_CHECKS_ALL ACA
, AP_LOOKUP_VALUES ALV3
WHERE
APSA.INVOICE_ID =AIA.INVOICE_ID
AND APSA.PAYMENT_NUM =AIPA.PAYMENT_NUM(+)
AND APSA.INVOICE_ID =AIPA.INVOICE_ID(+)
AND AIA.INVOICE_ID =AIPA.INVOICE_ID(+)
AND AIPA.CHECK_ID =ACA.CHECK_ID(+)
AND ALV3.LOOKUP_TYPE ='PAYMENT METHOD'
AND ALV3.LOOKUP_CODE =APSA.PAYMENT_METHOD_CODE
)
, FIRST_AP_CCID AS (
select distinct ael.AE_HEADER_ID , ael.APPLICATION_ID
, MIN(ael.code_combination_id) KEEP (DENSE_RANK FIRST ORDER BY ael.ae_line_num) CODE_COMBINATION_ID
from xla_ae_lines ael
,GL_CODE_COMBINATIONS GCC3
where AEL.ACCOUNTING_CLASS_CODE = 'LIABILITY'
AND GCC3.CODE_COMBINATION_ID = AEL.CODE_COMBINATION_ID
AND GCC3.SEGMENT4 IN ('290111','213005','222501008','5001000000','479102290111','320003')
GROUP BY ael.AE_HEADER_ID , ael.APPLICATION_ID)
, FIRST_AR_CCID AS (
select distinct ael.AE_HEADER_ID , ael.APPLICATION_ID
, MIN(ael.code_combination_id) KEEP (DENSE_RANK FIRST ORDER BY ael.ae_line_num) CODE_COMBINATION_ID
from xla_ae_lines ael
,GL_CODE_COMBINATIONS GCC3
where AEL.ACCOUNTING_CLASS_CODE = 'RECEIVABLE'
AND GCC3.CODE_COMBINATION_ID = AEL.CODE_COMBINATION_ID
AND GCC3.SEGMENT4 IN ('190111','113505','132005004','3001000001','179003190111','131001')
GROUP BY ael.AE_HEADER_ID , ael.APPLICATION_ID)
SELECT
*
FROM
(
SELECT
PROVIDER_LEDGER PROVIDER_LEDGER_NT
, AR_TRANSACTION_NUMBER AR_TRANSACTION_NUMBER_NT
, AR_TRANSACTION_DATE AR_TRANSACTION_DATE_NT
, AR_CURRENCY AR_CURRENCY_NT
, CUSTOMER_NAME CUSTOMER_NAME_NT
, CUSTOMER_NUMBER CUSTOMER_NUMBER_NT
, IC_RECEIVABLES_ACCOUNT_COMBINATION IC_RECEIVABLES_ACCOUNT_COMBINATION_NT
, AR_TRANSACTION_SOURCE AR_TRANSACTION_SOURCE_NT
, AR_TRANSACTION_TYPE AR_TRANSACTION_TYPE_NT
, (CASE WHEN Total_receipted_amount=0 THEN 'Not Paid'
WHEN AR_AMOUNT+Total_receipted_amount=0 THEN 'Fully Paid'
ELSE 'Partially Paid' END) AR_Transaction_Status_NT
, AR_AMOUNT AR_AMOUNT_NT
, Total_receipted_amount Total_receipted_amount_NT
, Adjustment_to_AR_transaction Adjustment_to_AR_transaction_NT
, RECEIVER_LEDGER RECEIVER_LEDGER_NT
, IC_PAYABLES_ACCOUNT_COMBINATION IC_PAYABLES_ACCOUNT_COMBINATION_NT
, SUPPLIER_NAME SUPPLIER_NAME_NT
, SUPPLIER_NUMBER SUPPLIER_NUMBER_NT
, VENDOR_SITE VENDOR_SITE_NT
, AP_Transaction_Status AP_Transaction_Status_NT
, AP_Amount AP_Amount_NT
, Total_payment_amount Total_payment_amount_NT
, Adjustment_to_AP_transaction Adjustment_to_AP_transaction_NT
, AP_Invoice_Number AP_Invoice_Number_NT
, BATCH_NUMBER BATCH_NUMBER_NT
, BATCH_DESCRIPTION BATCH_DESCRIPTION_NT
, AR_AMOUNT+AP_Amount+Adjustment_to_AR_transaction+Adjustment_to_AP_transaction TRANSACTION_VARIANCE_NT
, Total_receipted_amount+Total_payment_amount SETTLEMENT_VARIANCE_NT
, AR_AMOUNT+Adjustment_to_AR_transaction+Total_receipted_amount AR_Remaining_Balance_NT
, AP_Amount+Adjustment_to_AP_transaction+Total_payment_amount AP_Remaining_Balance_NT
FROM
(
SELECT
GLL.NAME PROVIDER_LEDGER
, RCTA.TRX_NUMBER AR_TRANSACTION_NUMBER
, RCTA.TRX_DATE AR_TRANSACTION_DATE
, RCTA.INVOICE_CURRENCY_CODE AR_CURRENCY
, HP1.PARTY_NAME CUSTOMER_NAME
, HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER
, GCC.SEGMENT1 || '.' || GCC.SEGMENT2 || '.' || GCC.SEGMENT3 || '.' || GCC.SEGMENT4 || '.' || GCC.SEGMENT5 || '.' || GCC.SEGMENT6 || '.' || GCC.SEGMENT7 || '.' || GCC.SEGMENT8|| '.' || GCC.SEGMENT9 IC_RECEIVABLES_ACCOUNT_COMBINATION
, RBSA.NAME AR_TRANSACTION_SOURCE
, RCTTA.NAME AR_TRANSACTION_TYPE
,
(CASE WHEN AEH1.GL_TRANSFER_STATUS_CODE = 'Y' THEN NVL((SELECT
NVL(SUM(LINES.EXTENDED_AMOUNT),0)
FROM
RA_CUSTOMER_TRX_LINES_ALL LINES
WHERE
LINES.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
GROUP BY RCTA.CUSTOMER_TRX_ID
),0)
ELSE 0 END) AR_Amount
, NVL(-1*(SELECT
NVL(SUM(ARAALL.AMOUNT_APPLIED),0) --ACRA.AMOUNT*NVL(ACRA.EXCHANGE_RATE,1)),0)
FROM
AR_RECEIVABLE_APPLICATIONS_ALL ARAALL
, AR_CASH_RECEIPTS_ALL ACRA
WHERE
NVL(TO_CHAR(ARAALL.REVERSAL_GL_DATE,'MM/DD/YYYY'),'NA') = 'NA'
AND ARAALL.APPLIED_CUSTOMER_TRX_ID =RCTA.CUSTOMER_TRX_ID
AND ACRA.CASH_RECEIPT_ID =ARAALL.CASH_RECEIPT_ID
GROUP BY RCTA.CUSTOMER_TRX_ID
),0) Total_receipted_amount
, NVL((SELECT
NVL(SUM(LINES.EXTENDED_AMOUNT),0)
FROM
RA_CUSTOMER_TRX_ALL RCTA1
, RA_CUSTOMER_TRX_LINES_ALL LINES
WHERE
LINES.CUSTOMER_TRX_ID =RCTA1.CUSTOMER_TRX_ID
AND RCTA.CUSTOMER_TRX_ID =RCTA1.PREVIOUS_CUSTOMER_TRX_ID
GROUP BY RCTA.CUSTOMER_TRX_ID
),0) Adjustment_to_AR_transaction
, GLL2.NAME AS RECEIVER_LEDGER
, GCC2.SEGMENT1 || '.' || GCC2.SEGMENT2 || '.' || GCC2.SEGMENT3 || '.' || GCC2.SEGMENT4 || '.' || GCC2.SEGMENT5 || '.' || GCC2.SEGMENT6 || '.' || GCC2.SEGMENT7 || '.' || GCC2.SEGMENT8 || '.' || GCC2.SEGMENT9 IC_PAYABLES_ACCOUNT_COMBINATION
, HP.PARTY_NAME SUPPLIER_NAME
, ASA.SEGMENT1 SUPPLIER_NUMBER
, APAY.PAYMENT_METHOD VENDOR_SITE
, ALV2.MEANING AP_Transaction_Status
,(CASE WHEN AEH2.GL_TRANSFER_STATUS_CODE = 'Y' THEN -1 * NVL(AIA.INVOICE_AMOUNT, 0)
ELSE 0 END) AP_Amount
--, -1*NVL(AIA.INVOICE_AMOUNT,0) AP_Amount
, NVL(AIA.AMOUNT_PAID,0) Total_payment_amount
, NVL(-1*(SELECT
NVL(SUM(apha2.AMOUNT),0)
FROM
ap_invoice_lines_all apha1
, ap_invoice_lines_all apha2
WHERE
apha1.INVOICE_ID =AIA.INVOICE_ID
and apha1.INVOICE_ID =apha2.CORRECTED_INV_ID
and apha1.LINE_NUMBER =apha2.CORRECTED_LINE_NUMBER
GROUP BY AIA.INVOICE_ID
),0) Adjustment_to_AP_transaction
, AIA.INVOICE_NUM AP_Invoice_Number
, FTB.BATCH_NUMBER
, FTB.DESCRIPTION BATCH_DESCRIPTION
FROM
RA_CUSTOMER_TRX_ALL RCTA
, RA_BATCH_SOURCES_ALL RBSA
, RA_CUST_TRX_TYPES_ALL RCTTA
, GL_CODE_COMBINATIONS GCC
, GL_LEDGERS GLL
, XLA_TRANSACTION_ENTITIES ENT1
, XLA_AE_HEADERS AEH1
, AR_LOOKUP_VALUES ALV1
, HZ_PARTIES HP1
, HZ_CUST_ACCOUNTS HCA
, FUN_TRX_BATCHES FTB
, FUN_TRX_HEADERS FTH
, AP_INVOICES_ALL AIA
, POZ_SUPPLIERS ASA
, HZ_PARTIES HP
, GL_CODE_COMBINATIONS GCC2
, POZ_SUPPLIER_SITES_V ASSA
, GL_LEDGERS GLL2
, XLA_TRANSACTION_ENTITIES ENT2
, XLA_AE_HEADERS AEH2
, AP_LOOKUP_VALUES ALV2
, AP_PAY APAY
, FIRST_AP_CCID AP_ACC
, FIRST_AR_CCID AR_ACC
WHERE
1 =1
AND RCTA.BATCH_SOURCE_SEQ_ID =RBSA.BATCH_SOURCE_SEQ_ID
AND RCTTA.CUST_TRX_TYPE_SEQ_ID =RCTA.CUST_TRX_TYPE_SEQ_ID
AND RBSA.NAME ='Global Intercompany'
AND RCTA.CUSTOMER_TRX_ID =ENT1.SOURCE_ID_INT_1
AND ENT1.APPLICATION_ID = '222'
AND AEH1.APPLICATION_ID(+) =ENT1.APPLICATION_ID
AND AEH1.ENTITY_ID(+) =ENT1.ENTITY_ID
AND AEH1.LEDGER_ID(+) =ENT1.LEDGER_ID
AND AEH1.GL_TRANSFER_STATUS_CODE(+) != 'NT'
AND AEH1.AE_HEADER_ID = AR_ACC.AE_HEADER_ID(+)
AND AEH1.APPLICATION_ID = AR_ACC.APPLICATION_ID(+)
AND AR_ACC.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID(+)
/*AND GCC.CODE_COMBINATION_ID = (select distinct MIN(ael.code_combination_id) KEEP (DENSE_RANK FIRST ORDER BY ael.ae_line_num)
from xla_ae_lines ael
,GL_CODE_COMBINATIONS GCC3
where ael.AE_HEADER_ID = AEH1.AE_HEADER_ID
AND ael.APPLICATION_ID=AEH1.APPLICATION_ID
and AEL.ACCOUNTING_CLASS_CODE = 'RECEIVABLE'
AND GCC3.CODE_COMBINATION_ID = AEL.CODE_COMBINATION_ID
AND GCC3.SEGMENT4 IN ('190111', '113505','132005004','3001000001','179003190111','131001'))*/
AND GLL.LEDGER_ID =RCTA.SET_OF_BOOKS_ID
AND ALV1.LOOKUP_TYPE(+) ='INVOICE_TRX_STATUS'
AND ALV1.LOOKUP_CODE(+) =RCTA.STATUS_TRX
AND HP1.PARTY_ID =HCA.PARTY_ID
AND UPPER(HCA.CUSTOMER_CLASS_CODE) ='INTERCOMPANY'
AND HCA.CUST_ACCOUNT_ID =RCTA.BILL_TO_CUSTOMER_ID
AND FTB.FROM_LEDGER_ID =GLL.LEDGER_ID
AND FTB.BATCH_ID =FTH.BATCH_ID
AND UPPER(FTB.STATUS) ='COMPLETE'
AND FTH.AR_CUSTOMER_TRX_ID =RCTA.CUSTOMER_TRX_ID
AND FTH.AP_INVOICE_ID =AIA.INVOICE_ID
AND ASA.VENDOR_ID =AIA.VENDOR_ID
AND AIA.VENDOR_SITE_ID =ASSA.VENDOR_SITE_ID(+)
AND ASA.VENDOR_ID =ASSA.VENDOR_ID(+)
AND HP.PARTY_ID =ASA.PARTY_ID
AND AIA.INVOICE_ID =ENT2.SOURCE_ID_INT_1
AND ENT2.APPLICATION_ID = '200'
AND AEH2.APPLICATION_ID(+) =ENT2.APPLICATION_ID
AND AEH2.ENTITY_ID(+) =ENT2.ENTITY_ID
AND AEH2.LEDGER_ID(+) =ENT2.LEDGER_ID
AND AEH2.GL_TRANSFER_STATUS_CODE(+) != 'NT'
AND AEH2.AE_HEADER_ID = AP_ACC.AE_HEADER_ID(+)
AND AEH2.APPLICATION_ID = AP_ACC.APPLICATION_ID(+)
AND AP_ACC.CODE_COMBINATION_ID = GCC2.CODE_COMBINATION_ID(+)
/*AND GCC2.CODE_COMBINATION_ID = (select distinct MIN(ael.code_combination_id) KEEP (DENSE_RANK FIRST ORDER BY ael.ae_line_num)
from xla_ae_lines ael
,GL_CODE_COMBINATIONS GCC3
/* ,FND_VS_VALUE_SETS FVVS
,FND_VS_VALUES_B FVVB
,FND_VS_VALUES_TL FVVT
where ael.AE_HEADER_ID = AEH2.AE_HEADER_ID
AND ael.APPLICATION_ID=AEH2.APPLICATION_ID
and AEL.ACCOUNTING_CLASS_CODE = 'LIABILITY'
AND GCC3.CODE_COMBINATION_ID = AEL.CODE_COMBINATION_ID
AND GCC3.SEGMENT4 IN ('290111', '213005','222501008','5001000000','479102290111','320003'))*/
AND GLL2.LEDGER_ID =AIA.SET_OF_BOOKS_ID
AND ALV2.LOOKUP_TYPE(+) ='INVOICE PAYMENT STATUS'
AND ALV2.LOOKUP_CODE(+) =AIA.PAYMENT_STATUS_FLAG
AND UPPER(ASA.VENDOR_TYPE_LOOKUP_CODE) ='INTERCOMPANY'
AND APAY.INVOICE_ID(+) =AIA.INVOICE_ID
AND RCTA.TRX_NUMBER =AIA.INVOICE_NUM
AND RCTA.TRX_DATE BETWEEN nvl((select distinct START_DATE from GL_PERIODS gp1 where gp1.period_name = :P_FROM_GL_DATE and gp1.adjustment_period_flag='N'), RCTA.TRX_DATE) AND nvl((select distinct END_DATE from GL_PERIODS gp2 where gp2.period_name = :P_TO_GL_DATE and gp2.adjustment_period_flag='N'),RCTA.TRX_DATE )
AND ( HP1.PARTY_NAME IN (:P_CUSTOMER_NAME) OR 'All' IN ('All'||:P_CUSTOMER_NAME))
AND (HP.PARTY_NAME IN (:P_SUPPLIER_NAME) OR 'All' IN ('All'||:P_SUPPLIER_NAME))
--AND RCTA.TRX_CLASS IN ('INV')
UNION
SELECT
GLL.NAME PROVIDER_LEDGER
, RCTA.TRX_NUMBER AR_TRANSACTION_NUMBER
, RCTA.TRX_DATE AR_TRANSACTION_DATE
, RCTA.INVOICE_CURRENCY_CODE AR_CURRENCY
, HP1.PARTY_NAME CUSTOMER_NAME
, HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER
, GCC.SEGMENT1 || '.' || GCC.SEGMENT2 || '.' || GCC.SEGMENT3 || '.' || GCC.SEGMENT4 || '.' || GCC.SEGMENT5 || '.' || GCC.SEGMENT6 || '.' || GCC.SEGMENT7 || '.' || GCC.SEGMENT8|| '.' || GCC.SEGMENT9 IC_RECEIVABLES_ACCOUNT_COMBINATION
, RBSA.NAME AR_TRANSACTION_SOURCE
, RCTTA.NAME AR_TRANSACTION_TYPE
,(CASE WHEN AEH1.GL_TRANSFER_STATUS_CODE = 'Y' THEN NVL((SELECT
NVL(SUM(LINES.EXTENDED_AMOUNT),0)
FROM
RA_CUSTOMER_TRX_LINES_ALL LINES
WHERE
LINES.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
GROUP BY RCTA.CUSTOMER_TRX_ID
),0)
ELSE 0 END) AR_Amount
/*, NVL((SELECT
NVL(SUM(LINES.EXTENDED_AMOUNT),0)
FROM
RA_CUSTOMER_TRX_LINES_ALL LINES
WHERE
LINES.CUSTOMER_TRX_ID =RCTA.CUSTOMER_TRX_ID
GROUP BY RCTA.CUSTOMER_TRX_ID
),0) AR_AMOUNT*/
, NVL(-1*(SELECT
NVL(SUM(ARAALL.AMOUNT_APPLIED),0) --ACRA.AMOUNT*NVL(ACRA.EXCHANGE_RATE,1)),0)
FROM
AR_RECEIVABLE_APPLICATIONS_ALL ARAALL
, AR_CASH_RECEIPTS_ALL ACRA
WHERE
NVL(TO_CHAR(ARAALL.REVERSAL_GL_DATE,'MM/DD/YYYY'),'NA') = 'NA'
AND ARAALL.APPLIED_CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
AND ACRA.CASH_RECEIPT_ID = ARAALL.CASH_RECEIPT_ID
GROUP BY RCTA.CUSTOMER_TRX_ID
),0) Total_receipted_amount
, NVL((SELECT
NVL(SUM(LINES.EXTENDED_AMOUNT),0)
FROM
RA_CUSTOMER_TRX_ALL RCTA1
, RA_CUSTOMER_TRX_LINES_ALL LINES
WHERE
LINES.CUSTOMER_TRX_ID =RCTA1.CUSTOMER_TRX_ID
AND RCTA.CUSTOMER_TRX_ID =RCTA1.PREVIOUS_CUSTOMER_TRX_ID
GROUP BY RCTA.CUSTOMER_TRX_ID
),0) Adjustment_to_AR_transaction
, GLL2.NAME AS RECEIVER_LEDGER
, GCC2.SEGMENT1 || '.' || GCC2.SEGMENT2 || '.' || GCC2.SEGMENT3 || '.' || GCC2.SEGMENT4 || '.' || GCC2.SEGMENT5 || '.' || GCC2.SEGMENT6 || '.' || GCC2.SEGMENT7 || '.' || GCC2.SEGMENT8 || '.' || GCC2.SEGMENT9 IC_PAYABLES_ACCOUNT_COMBINATION
, HP.PARTY_NAME SUPPLIER_NAME
, ASA.SEGMENT1 SUPPLIER_NUMBER
, APAY.PAYMENT_METHOD VENDOR_SITE
, ALV2.MEANING AP_Transaction_Status
,(CASE WHEN AEH2.GL_TRANSFER_STATUS_CODE = 'Y' THEN -1 * NVL(AIA.INVOICE_AMOUNT, 0)
ELSE 0 END) AP_Amount
--, NVL(-1*AIA.INVOICE_AMOUNT,0) AP_Amount
, NVL(AIA.AMOUNT_PAID,0) Total_payment_amount
, NVL(-1*(SELECT
NVL(SUM(apha2.AMOUNT),0)
FROM
ap_invoice_lines_all apha1
, ap_invoice_lines_all apha2
WHERE
apha1.INVOICE_ID =AIA.INVOICE_ID
and apha1.INVOICE_ID =apha2.CORRECTED_INV_ID
and apha1.LINE_NUMBER =apha2.CORRECTED_LINE_NUMBER
GROUP BY AIA.INVOICE_ID
),0) Adjustment_to_AP_transaction
, AIA.INVOICE_NUM AP_Invoice_Number
, FTB.BATCH_NUMBER
, FTB.DESCRIPTION BATCH_DESCRIPTION
FROM
RA_CUSTOMER_TRX_ALL RCTA
, RA_BATCH_SOURCES_ALL RBSA
, RA_CUST_TRX_TYPES_ALL RCTTA
, GL_CODE_COMBINATIONS GCC
, GL_LEDGERS GLL
, XLA_TRANSACTION_ENTITIES ENT1
, XLA_AE_HEADERS AEH1
, AR_LOOKUP_VALUES ALV1
, HZ_PARTIES HP1
, HZ_CUST_ACCOUNTS HCA
, FUN_TRX_BATCHES FTB
, FUN_TRX_HEADERS FTH
, AP_INVOICES_ALL AIA
, POZ_SUPPLIERS ASA
, HZ_PARTIES HP
, GL_CODE_COMBINATIONS GCC2
, POZ_SUPPLIER_SITES_V ASSA
, GL_LEDGERS GLL2
, XLA_TRANSACTION_ENTITIES ENT2
, XLA_AE_HEADERS AEH2
, AP_LOOKUP_VALUES ALV2
, AP_PAY APAY
, FIRST_AP_CCID AP_ACC
, FIRST_AR_CCID AR_ACC
WHERE
1 =1
AND RCTA.BATCH_SOURCE_SEQ_ID =RBSA.BATCH_SOURCE_SEQ_ID
AND RCTTA.CUST_TRX_TYPE_SEQ_ID =RCTA.CUST_TRX_TYPE_SEQ_ID
AND RBSA.NAME ='Global Intercompany'
AND RCTA.CUSTOMER_TRX_ID =ENT1.SOURCE_ID_INT_1
AND ENT1.APPLICATION_ID = '222'
AND AEH1.APPLICATION_ID(+) =ENT1.APPLICATION_ID
AND AEH1.ENTITY_ID(+) =ENT1.ENTITY_ID
AND AEH1.LEDGER_ID(+) =ENT1.LEDGER_ID
AND AEH1.GL_TRANSFER_STATUS_CODE(+) != 'NT'
AND AEH1.AE_HEADER_ID = AR_ACC.AE_HEADER_ID(+)
AND AEH1.APPLICATION_ID = AR_ACC.APPLICATION_ID(+)
AND AR_ACC.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID(+)
/*AND GCC.CODE_COMBINATION_ID = (select distinct MIN(ael.code_combination_id) KEEP (DENSE_RANK FIRST ORDER BY ael.ae_line_num)
from xla_ae_lines ael
,GL_CODE_COMBINATIONS GCC3
where ael.AE_HEADER_ID = AEH1.AE_HEADER_ID
AND ael.APPLICATION_ID=AEH1.APPLICATION_ID
and AEL.ACCOUNTING_CLASS_CODE = 'RECEIVABLE'
AND GCC3.CODE_COMBINATION_ID = AEL.CODE_COMBINATION_ID
AND GCC3.SEGMENT4 IN ('190111', '113505','132005004','3001000001','179003190111','131001'))*/
AND GLL.LEDGER_ID =RCTA.SET_OF_BOOKS_ID
AND ALV1.LOOKUP_TYPE(+) ='INVOICE_TRX_STATUS'
AND ALV1.LOOKUP_CODE(+) =RCTA.STATUS_TRX
AND HP1.PARTY_ID =HCA.PARTY_ID
AND UPPER(HCA.CUSTOMER_CLASS_CODE) ='INTERCOMPANY'
AND HCA.CUST_ACCOUNT_ID =RCTA.BILL_TO_CUSTOMER_ID
AND FTB.FROM_LEDGER_ID =GLL.LEDGER_ID
AND FTB.BATCH_ID =FTH.BATCH_ID
AND UPPER(FTB.STATUS) ='COMPLETE'
AND FTH.AR_CUSTOMER_TRX_ID =RCTA.CUSTOMER_TRX_ID
AND FTH.AP_INVOICE_ID =AIA.INVOICE_ID
AND ASA.VENDOR_ID =AIA.VENDOR_ID
AND AIA.VENDOR_SITE_ID =ASSA.VENDOR_SITE_ID(+)
AND ASA.VENDOR_ID =ASSA.VENDOR_ID(+)
AND HP.PARTY_ID =ASA.PARTY_ID
AND AIA.INVOICE_ID =ENT2.SOURCE_ID_INT_1
AND ENT2.APPLICATION_ID = '200'
AND AEH2.APPLICATION_ID(+) =ENT2.APPLICATION_ID
AND AEH2.ENTITY_ID(+) =ENT2.ENTITY_ID
AND AEH2.LEDGER_ID(+) =ENT2.LEDGER_ID
AND AEH2.GL_TRANSFER_STATUS_CODE(+) != 'NT'
AND AEH2.AE_HEADER_ID = AP_ACC.AE_HEADER_ID(+)
AND AEH2.APPLICATION_ID = AP_ACC.APPLICATION_ID(+)
AND AP_ACC.CODE_COMBINATION_ID = GCC2.CODE_COMBINATION_ID(+)
/*AND GCC2.CODE_COMBINATION_ID = (select distinct MIN(ael.code_combination_id) KEEP (DENSE_RANK FIRST ORDER BY ael.ae_line_num)
from xla_ae_lines ael
,GL_CODE_COMBINATIONS GCC3
where ael.AE_HEADER_ID = AEH2.AE_HEADER_ID
AND ael.APPLICATION_ID=AEH2.APPLICATION_ID
and AEL.ACCOUNTING_CLASS_CODE = 'LIABILITY'
AND GCC3.CODE_COMBINATION_ID = AEL.CODE_COMBINATION_ID
AND GCC3.SEGMENT4 IN ('290111', '213005','222501008','5001000000','479102290111','320003'))*/
AND GLL2.LEDGER_ID =AIA.SET_OF_BOOKS_ID
AND ALV2.LOOKUP_TYPE(+) ='INVOICE PAYMENT STATUS'
AND ALV2.LOOKUP_CODE(+) =AIA.PAYMENT_STATUS_FLAG
AND UPPER(ASA.VENDOR_TYPE_LOOKUP_CODE)='INTERCOMPANY'
AND APAY.INVOICE_ID(+) =AIA.INVOICE_ID
AND RCTA.TRX_NUMBER =AIA.INVOICE_NUM
AND RCTA.TRX_DATE BETWEEN nvl((select distinct START_DATE from GL_PERIODS gp1 where gp1.period_name = :P_FROM_GL_DATE and gp1.adjustment_period_flag='N'), RCTA.TRX_DATE ) AND nvl((select distinct END_DATE from GL_PERIODS gp2 where gp2.period_name = :P_TO_GL_DATE and gp2.adjustment_period_flag='N'),RCTA.TRX_DATE )
AND ( HP1.PARTY_NAME IN (:P_CUSTOMER_NAME) OR 'All' IN ('All'||:P_CUSTOMER_NAME))
AND (HP.PARTY_NAME IN (:P_SUPPLIER_NAME) OR 'All' IN ('All'||:P_SUPPLIER_NAME))
AND RCTA.TRX_CLASS IN ('ONACC')
UNION
SELECT
GLL.NAME PROVIDER_LEDGER
, RCTA.TRX_NUMBER AR_TRANSACTION_NUMBER
, RCTA.TRX_DATE AR_TRANSACTION_DATE
, RCTA.INVOICE_CURRENCY_CODE AR_CURRENCY
, HP1.PARTY_NAME CUSTOMER_NAME
, HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER
, GCC.SEGMENT1 || '.' || GCC.SEGMENT2 || '.' || GCC.SEGMENT3 || '.' || GCC.SEGMENT4 || '.' || GCC.SEGMENT5 || '.' || GCC.SEGMENT6 || '.' || GCC.SEGMENT7 || '.' || GCC.SEGMENT8|| '.' || GCC.SEGMENT9 IC_RECEIVABLES_ACCOUNT_COMBINATION
, RBSA.NAME AR_TRANSACTION_SOURCE
, RCTTA.NAME AR_TRANSACTION_TYPE
,
(CASE WHEN AEH1.GL_TRANSFER_STATUS_CODE = 'Y' THEN NVL((SELECT
NVL(SUM(LINES.EXTENDED_AMOUNT),0)
FROM
RA_CUSTOMER_TRX_LINES_ALL LINES
WHERE
LINES.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID
GROUP BY RCTA.CUSTOMER_TRX_ID
),0)
ELSE 0 END) AR_Amount
, NVL(-1*(SELECT
NVL(SUM(ARAALL.AMOUNT_APPLIED),0)--ACRA.AMOUNT*NVL(ACRA.EXCHANGE_RATE,1)),0)
FROM
AR_RECEIVABLE_APPLICATIONS_ALL ARAALL
, AR_CASH_RECEIPTS_ALL ACRA
WHERE
NVL(TO_CHAR(ARAALL.REVERSAL_GL_DATE,'MM/DD/YYYY'),'NA') = 'NA'
AND ARAALL.APPLIED_CUSTOMER_TRX_ID =RCTA.CUSTOMER_TRX_ID
AND ACRA.CASH_RECEIPT_ID =ARAALL.CASH_RECEIPT_ID
GROUP BY RCTA.CUSTOMER_TRX_ID
),0) Total_receipted_amount
, NVL((SELECT
NVL(SUM(LINES.EXTENDED_AMOUNT),0)
FROM
RA_CUSTOMER_TRX_ALL RCTA1
, RA_CUSTOMER_TRX_LINES_ALL LINES
WHERE
LINES.CUSTOMER_TRX_ID =RCTA1.CUSTOMER_TRX_ID
AND RCTA.CUSTOMER_TRX_ID =RCTA1.PREVIOUS_CUSTOMER_TRX_ID
GROUP BY RCTA.CUSTOMER_TRX_ID
),0) Adjustment_to_AR_transaction
, GLL2.NAME AS RECEIVER_LEDGER
, GCC2.SEGMENT1 || '.' || GCC2.SEGMENT2 || '.' || GCC2.SEGMENT3 || '.' || GCC2.SEGMENT4 || '.' || GCC2.SEGMENT5 || '.' || GCC2.SEGMENT6 || '.' || GCC2.SEGMENT7 || '.' || GCC2.SEGMENT8 || '.' || GCC2.SEGMENT9 IC_PAYABLES_ACCOUNT_COMBINATION
, HP.PARTY_NAME SUPPLIER_NAME
, ASA.SEGMENT1 SUPPLIER_NUMBER
, APAY.PAYMENT_METHOD VENDOR_SITE
, ALV2.MEANING AP_Transaction_Status
,nvl((CASE WHEN AEH2.GL_TRANSFER_STATUS_CODE = 'Y' AND AIA.APPROVAL_STATUS != 'NEVER APPROVED' THEN -1 * NVL(AIA.INVOICE_AMOUNT, 0)
ELSE NULL END),0) AP_Amount
, NVL(AIA.AMOUNT_PAID,NULL) Total_payment_amount
, NVL(-1*(SELECT
NVL(SUM(apha2.AMOUNT),0)
FROM
ap_invoice_lines_all apha1
, ap_invoice_lines_all apha2
WHERE
apha1.INVOICE_ID =AIA.INVOICE_ID
and apha1.INVOICE_ID =apha2.CORRECTED_INV_ID
and apha1.LINE_NUMBER =apha2.CORRECTED_LINE_NUMBER
GROUP BY AIA.INVOICE_ID
),0) Adjustment_to_AP_transaction
, AIA.INVOICE_NUM AP_Invoice_Number
, FTB.BATCH_NUMBER
, FTB.DESCRIPTION BATCH_DESCRIPTION
FROM
RA_CUSTOMER_TRX_ALL RCTA
, RA_BATCH_SOURCES_ALL RBSA
, RA_CUST_TRX_TYPES_ALL RCTTA
, GL_CODE_COMBINATIONS GCC
, GL_LEDGERS GLL
, XLA_TRANSACTION_ENTITIES ENT1
, XLA_AE_HEADERS AEH1
, AR_LOOKUP_VALUES ALV1
, HZ_PARTIES HP1
, HZ_CUST_ACCOUNTS HCA
, FUN_TRX_BATCHES FTB
, FUN_TRX_HEADERS FTH
, AP_INVOICES_ALL AIA
, POZ_SUPPLIERS ASA
, HZ_PARTIES HP
, GL_CODE_COMBINATIONS GCC2
, POZ_SUPPLIER_SITES_V ASSA
, GL_LEDGERS GLL2
, XLA_TRANSACTION_ENTITIES ENT2
, XLA_AE_HEADERS AEH2
, AP_LOOKUP_VALUES ALV2
, AP_PAY APAY
, FIRST_AP_CCID AP_ACC
, FIRST_AR_CCID AR_ACC
WHERE 1 =1
AND RCTA.BATCH_SOURCE_SEQ_ID =RBSA.BATCH_SOURCE_SEQ_ID
AND RCTTA.CUST_TRX_TYPE_SEQ_ID =RCTA.CUST_TRX_TYPE_SEQ_ID
AND RBSA.NAME ='Global Intercompany'
AND RCTA.CUSTOMER_TRX_ID =ENT1.SOURCE_ID_INT_1
AND ENT1.APPLICATION_ID = '222'
AND AEH1.APPLICATION_ID(+) =ENT1.APPLICATION_ID
AND AEH1.ENTITY_ID(+) =ENT1.ENTITY_ID
AND AEH1.LEDGER_ID(+) =ENT1.LEDGER_ID
AND AEH1.GL_TRANSFER_STATUS_CODE(+) != 'NT'
AND AEH1.AE_HEADER_ID = AR_ACC.AE_HEADER_ID(+)
AND AEH1.APPLICATION_ID = AR_ACC.APPLICATION_ID(+)
AND AR_ACC.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID(+)
/*AND GCC.CODE_COMBINATION_ID IN (select distinct ael.code_combination_id
from xla_ae_lines ael
,GL_CODE_COMBINATIONS GCC3
where ael.AE_HEADER_ID = AEH1.AE_HEADER_ID
AND ael.APPLICATION_ID=AEH1.APPLICATION_ID
and AEL.ACCOUNTING_CLASS_CODE = 'RECEIVABLE'
AND GCC3.CODE_COMBINATION_ID = AEL.CODE_COMBINATION_ID
AND GCC3.SEGMENT4 IN ('190111', '113505','132005004','3001000001','179003190111','131001'))*/
AND GLL.LEDGER_ID =RCTA.SET_OF_BOOKS_ID
AND ALV1.LOOKUP_TYPE(+) ='INVOICE_TRX_STATUS'
AND ALV1.LOOKUP_CODE(+) =RCTA.STATUS_TRX
AND HP1.PARTY_ID =HCA.PARTY_ID
AND UPPER(HCA.CUSTOMER_CLASS_CODE) ='INTERCOMPANY'
AND HCA.CUST_ACCOUNT_ID =RCTA.BILL_TO_CUSTOMER_ID
AND FTB.FROM_LEDGER_ID =GLL.LEDGER_ID
AND FTB.BATCH_ID =FTH.BATCH_ID
AND (UPPER(FTB.STATUS) != 'COMPLETE' OR AIA.APPROVAL_STATUS = 'NEVER APPROVED')
AND FTH.AR_CUSTOMER_TRX_ID =RCTA.CUSTOMER_TRX_ID
AND FTH.AP_INVOICE_ID =AIA.INVOICE_ID(+)
AND ASA.VENDOR_ID(+) =AIA.VENDOR_ID
AND AIA.VENDOR_SITE_ID =ASSA.VENDOR_SITE_ID(+)
AND ASA.VENDOR_ID =ASSA.VENDOR_ID(+)
AND HP.PARTY_ID(+) =ASA.PARTY_ID
AND AIA.INVOICE_ID =ENT2.SOURCE_ID_INT_1(+)
AND ENT2.APPLICATION_ID(+) = '200'
AND AEH2.APPLICATION_ID(+) =ENT2.APPLICATION_ID
AND AEH2.ENTITY_ID(+) =ENT2.ENTITY_ID
AND AEH2.LEDGER_ID(+) =ENT2.LEDGER_ID
AND AEH2.AE_HEADER_ID = AP_ACC.AE_HEADER_ID(+)
AND AEH2.APPLICATION_ID = AP_ACC.APPLICATION_ID(+)
AND AP_ACC.CODE_COMBINATION_ID = GCC2.CODE_COMBINATION_ID(+)
AND AEH2.GL_TRANSFER_STATUS_CODE(+) != 'NT'
/*AND GCC2.CODE_COMBINATION_ID IN (select distinct ael.code_combination_id
from xla_ae_lines ael
,GL_CODE_COMBINATIONS GCC3
where ael.AE_HEADER_ID = AEH2.AE_HEADER_ID
AND ael.APPLICATION_ID=AEH2.APPLICATION_ID
and AEL.ACCOUNTING_CLASS_CODE = 'LIABILITY'
AND GCC3.CODE_COMBINATION_ID = AEL.CODE_COMBINATION_ID
AND GCC3.SEGMENT4 IN ('290111', '213005','222501008','5001000000','479102290111','320003'))*/
AND GLL2.LEDGER_ID(+) =AIA.SET_OF_BOOKS_ID
AND ALV2.LOOKUP_TYPE(+) ='INVOICE PAYMENT STATUS'
AND ALV2.LOOKUP_CODE(+) =AIA.PAYMENT_STATUS_FLAG
AND ASA.VENDOR_TYPE_LOOKUP_CODE(+) ='INTERCOMPANY'
AND APAY.INVOICE_ID(+) =AIA.INVOICE_ID
AND RCTA.TRX_NUMBER =AIA.INVOICE_NUM(+)
AND RCTA.TRX_DATE BETWEEN nvl((select distinct START_DATE from GL_PERIODS gp1 where gp1.period_name = :P_FROM_GL_DATE and gp1.adjustment_period_flag='N'), RCTA.TRX_DATE) AND nvl((select distinct END_DATE from GL_PERIODS gp2 where gp2.period_name = :P_TO_GL_DATE and gp2.adjustment_period_flag='N'),RCTA.TRX_DATE )
AND ( HP1.PARTY_NAME IN (:P_CUSTOMER_NAME) OR 'All' IN ('All'||:P_CUSTOMER_NAME))
AND (HP.PARTY_NAME IN (:P_SUPPLIER_NAME) OR 'All' IN ('All'||:P_SUPPLIER_NAME))
--AND RCTA.TRX_CLASS IN ('INV')
)
)
WHERE ( NVL(TRANSACTION_VARIANCE_NT,0)<>0
or NVL(SETTLEMENT_VARIANCE_NT,0)<>0 )
OR (UPPER(AR_Transaction_Status_NT)<>'FULLY PAID' or UPPER(AP_Transaction_Status_NT)<>'FULLY PAID')
ORDER BY PROVIDER_LEDGER_NT
,AR_TRANSACTION_NUMBER_NT:P_CUSTOMER_NAME :P_FROM_GL_DATE :P_SUPPLIER_NAME :P_TO_GL_DATE
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 |
|---|---|---|
| AP_INVOICE_PAYMENTS_ALL | dimension | dimension |
| RA_CUSTOMER_TRX_ALL | dimension | dimension |
| AR_PAYMENT_SCHEDULES_ALL | dimension | dimension |
| XLA_AE_LINES | dimension | dimension |
| Ap Amount | measure | measure |
| Ar 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 |
|---|---|---|
| AP_INVOICES_ALL | 63 | 15 |
| AP_INVOICE_PAYMENTS_ALL | 22 | 2 |
| RA_CUSTOMER_TRX_ALL | 58 | 16 |
| AR_PAYMENT_SCHEDULES_ALL | 32 | 6 |
| XLA_AE_LINES | 23 | 17 |
| GL_CODE_COMBINATIONS | 7 | 61 |
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.