Customer Statement of Open Balance Report

Receivables

Each customer's open balance as a statement — every unpaid invoice, debit memo, and chargeback, net of credits and on-account cash, with aging — formatted to send to the customer so they can reconcile and pay.

Sample build of the Customer Statement of Open Balance Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.

Sample build · illustrative

Filters

Sample

Account Type

1000-1410-000

As Of Date

2026-02-28

Bu Name

Globex Holdings

Cust Name

Globex Holdings

Legal Entity

Globex Holdings

410

Statements

$11.60M

Open balance

$180K

Disputed

Customer	Transaction	Type	Date	Original Amount	Open Amount	Days Outstanding
Acme Industrial	Sample	Standard	2026-04-30	$1,240,500.00	$1,240,500.00	Sample
Northwind Trading	—	Corporate	2026-03-31	$842,150.75	$842,150.75	—
Globex Holdings	Sample	Standard	2026-02-28	$96,400.00	$96,400.00	Sample
Initech LLC	—	Default	2026-01-31	$1,005,233.10	$1,005,233.10	—
Umbrella Corp	Sample	Standard	2025-12-31	$58,720.40	$58,720.40	Sample
Acme Industrial	Sample	Standard	2026-04-30	$1,240,500.00	$1,240,500.00	Sample

AI Analyst · active

reading

The report reads open schedules in AR_PAYMENT_SCHEDULES_ALL by customer, netting on-account credits into a statement balance.

flag

$180K across the statements is flagged in dispute — sending a statement that includes disputed items often delays payment of the undisputed balance too.

root cause & next step

Split disputed items onto a separate track so the clean balance can be collected; bundling disputes into the statement is a common reason DSO creeps up.

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

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.

SQL data set · genericized · parameterized · no hardcoded segments

Show / hide SQL

---
WITH UNAPPLIED_RECEIPTS AS
(
	SELECT 
				ARAA.payment_schedule_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
)
, 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
)

SELECT 
			KEYID
		,	LEGAL_ENTITY
		,	LEGAL_ENTITY_ADD1
		,	LEGAL_ENTITY_ADD2
		,	LEGAL_ENTITY_ADD3
		,	LE_ADDRESS
		,	CUSTOMER_NUMBER
		,	CUSTOMER_NAME
		,	CUSTOMER_ADD1
		,	CASE WHEN CUSTOMER_ADD2 IS NOT NULL THEN CUSTOMER_ADD2 END||CASE WHEN CUSTOMER_ADD2 IS NOT NULL THEN ' ' END||CUSTOMER_ADD3 	CUSTOMER_ADD2
		,	CASE WHEN CUSTOMER_ADD4 IS NOT NULL THEN CUSTOMER_ADD4 END||CASE WHEN CUSTOMER_ADD4 IS NOT NULL THEN ' ' END||CASE WHEN CUSTOMER_ADD5 IS NOT NULL THEN CUSTOMER_ADD5 END||CASE WHEN CUSTOMER_ADD5 IS NOT NULL THEN ' 'END||CUSTOMER_ADD6 		CUSTOMER_ADD3
		,	AKA_NAME
		,	BU_NAME
		,   BILL_TO_CUSTOMER_NUMBER
		,   FUNC_CURR_CODE
		,	TO_CHAR(SYSDATE,'YYYY-MM-DD HH12:MI:SS') 		PRINT_DATE
		,	TO_CHAR(TRUNC(:P_AS_OF_DATE),'DD fmMonth YYYY','nls_date_language=American') ASOFDATE
		,	TRX_NUMBER
		,	TRX_TYPE
		,	TRX_DATE
		,	PAYMENT_TERMS
		,	DUE_DATE
		,	DAYS_LATE
		,	PO_NUMBER
		,	INV_CURR_CODE
		,	CUSTOMER_REFERENCE
		,	ORIGINAL_AMOUNT_TRX
		,	OPEN_AMOUNT_INV_TRX
		,	OPEN_AMOUNT_FUNC_TRX
FROM
(
SELECT  /*+parallel(12)*/
			RCTA.LEGAL_ENTITY_ID||RCTA.ORG_ID||RCTA.BILL_TO_CUSTOMER_ID||APSA.CUSTOMER_SITE_USE_ID||GLL.CURRENCY_CODE   KEYID
		,	XLE.NAME 										LEGAL_ENTITY
		,	XLE.ADDRESS_LINE_1 								LEGAL_ENTITY_ADD1
		,	XLE.ADDRESS_LINE_2||' '||XLE.TOWN_OR_CITY 		LEGAL_ENTITY_ADD2
		,	XLE.POSTAL_CODE||' '||FTLE.NLS_TERRITORY 		LEGAL_ENTITY_ADD3
		,	(
				SELECT 
							REPLACE(FFV.ATTRIBUTE6,'|',CHR(13))
				FROM 
							fnd_flex_value_sets FFVS
						,	fnd_flex_values 	FFV
				WHERE 
							FFVS.flex_value_set_id 	=FFV.flex_value_set_id
						AND FFV.flex_value			=XLE.legal_entity_identifier 
						AND XLE.legal_entity_id 	=RCTA.legal_entity_id
						AND FFV.enabled_flag='Y'
						AND TRUNC(SYSDATE) BETWEEN NVL(FFV.start_date_active, trunc(SYSDATE)) AND NVL(FFV.end_date_active, trunc(SYSDATE + 1))
						AND FFVS.flex_value_set_name ='CUSTOM_INVOICE_TEMPLATE_REPORT_INSTRUCTIONS'
			)												LE_ADDRESS
		,	HCA.ACCOUNT_NUMBER  							CUSTOMER_NUMBER
		,	HP.PARTY_NAME 									CUSTOMER_NAME
		,	HL.ADDRESS1 									CUSTOMER_ADD1
		,	HL.ADDRESS2 									CUSTOMER_ADD2
		--,	HL.ADDRESS2||' '||HL.CITY 						CUSTOMER_ADD2
		--,	HL.POSTAL_CODE||' '||HL.STATE||' '||FTC.NLS_TERRITORY 	CUSTOMER_ADD3
		,	HL.CITY											CUSTOMER_ADD3
		,	HL.POSTAL_CODE									CUSTOMER_ADD4
		,	HL.STATE										CUSTOMER_ADD5
		,	FTC.NLS_TERRITORY								CUSTOMER_ADD6
		,	CASE WHEN HL.COUNTRY='CN' THEN HCCSAM.TRANSLATED_CUSTOMER_NAME  ELSE HCCSAM.ATTRIBUTE5 END AKA_NAME
		,	BU.BU_NAME 										BU_NAME
		,	HCCSAM.ATTRIBUTE1	 							BILL_TO_CUSTOMER_NUMBER
		,	GLL.CURRENCY_CODE  								FUNC_CURR_CODE
		,	RCTA.TRX_NUMBER 											TRX_NUMBER
		,	DECODE(RCTTA.TYPE,'INV','Invoice','CM','Credit Memo','DM','Debit Memo','BR','Bill Receivable', RCTTA.TYPE) TRX_TYPE
		,	APSA.TRX_DATE								 				TRX_DATE
		,	RT.NAME PAYMENT_TERMS 
		,	APSA.DUE_DATE							 					DUE_DATE
		,	TO_DATE(TRUNC(:P_AS_OF_DATE), 'YYYY-MM-DD') - APSA.DUE_DATE DAYS_LATE
		,	RCTA.PURCHASE_ORDER 										PO_NUMBER
		,	RCTA.INVOICE_CURRENCY_CODE 									INV_CURR_CODE
		,	CASE WHEN HCA.CUSTOMER_TYPE='I' THEN (CASE WHEN RBSA.NAME='Global Intercompany' THEN RCTA.interface_header_attribute4 ELSE RCTA.interface_header_attribute1 END)||CASE WHEN (
														SELECT DISTINCT
																FTB.attribute3||'|'||FTB.description
														FROM		
																fun_trx_batches FTB
															,	fun_trx_headers FTH
														WHERE	1=1
															AND FTB.BATCH_ID=FTH.BATCH_ID
															AND FTH.AR_CUSTOMER_TRX_ID=RCTA.CUSTOMER_TRX_ID) IS NOT NULL THEN '|' END|| (
														SELECT DISTINCT
																FTB.attribute3||'|'||FTB.description
														FROM		
																fun_trx_batches FTB
															,	fun_trx_headers FTH
														WHERE	1=1
															AND FTB.BATCH_ID=FTH.BATCH_ID
															AND FTH.AR_CUSTOMER_TRX_ID=RCTA.CUSTOMER_TRX_ID) 
				WHEN HCA.CUSTOMER_TYPE='R' THEN (CASE WHEN RBSA.NAME='Global Intercompany' THEN RCTA.interface_header_attribute4 ELSE RCTA.interface_header_attribute1 END) END  CUSTOMER_REFERENCE
		,	NVL(APSA.AMOUNT_DUE_ORIGINAL,0) 							ORIGINAL_AMOUNT_TRX
		,	NVL(APSA.AMOUNT_DUE_REMAINING,0)							OPEN_AMOUNT_INV_TRX
		,	NVL(APSA.ACCTD_AMOUNT_DUE_REMAINING,0)						OPEN_AMOUNT_FUNC_TRX

FROM

			RA_CUSTOMER_TRX_ALL RCTA JOIN AR_PAYMENT_SCHEDULES_ALL APSA ON RCTA.CUSTOMER_TRX_ID=APSA.CUSTOMER_TRX_ID
									AND APSA.AMOUNT_DUE_REMAINING	<>0
			JOIN RA_CUST_TRX_TYPES_ALL RCTTA ON RCTTA.CUST_TRX_TYPE_SEQ_ID=RCTA.CUST_TRX_TYPE_SEQ_ID
			JOIN RA_BATCH_SOURCES_ALL RBSA ON RCTA.BATCH_SOURCE_SEQ_ID=RBSA.BATCH_SOURCE_SEQ_ID
			JOIN HZ_CUST_ACCOUNTS HCA ON RCTA.BILL_TO_CUSTOMER_ID=HCA.CUST_ACCOUNT_ID
			JOIN HZ_PARTIES HP ON HCA.PARTY_ID=HP.PARTY_ID
			JOIN HZ_CUST_SITE_USES_ALL HCSUA ON APSA.CUSTOMER_SITE_USE_ID=HCSUA.SITE_USE_ID
			JOIN HZ_CUST_ACCT_SITES_ALL HCCSAM ON HCSUA.cust_acct_site_id=HCCSAM.cust_acct_site_id
			JOIN HZ_PARTY_SITES HPS ON HCCSAM.PARTY_SITE_ID=HPS.PARTY_SITE_ID
			JOIN HZ_LOCATIONS HL ON HPS.LOCATION_ID=HL.LOCATION_ID
			LEFT OUTER JOIN FND_TERRITORIES FTC ON HL.COUNTRY=FTC.TERRITORY_CODE
			JOIN FUN_ALL_BUSINESS_UNITS_V BU ON RCTA.ORG_ID=BU.BU_ID
			JOIN GL_LEDGERS GLL ON RCTA.SET_OF_BOOKS_ID=GLL.LEDGER_ID
			JOIN XLE_FIRSTPARTY_INFORMATION_V XLE ON RCTA.LEGAL_ENTITY_ID=XLE.LEGAL_ENTITY_ID
			LEFT OUTER JOIN FND_TERRITORIES FTLE ON  XLE.COUNTRY=FTLE.TERRITORY_CODE
			LEFT OUTER JOIN RA_TERMS RT ON RCTA.TERM_ID=RT.TERM_ID
			
WHERE
			1							=1
		AND APSA.STATUS					='OP'
		AND RBSA.STATUS 				='A'
		AND HCA.CUSTOMER_TYPE 			=:P_ACCOUNT_TYPE
		AND APSA.TRX_DATE				<=NVL(TO_DATE(TRUNC(:P_TRX_DATE),'YYYY-MM-DD'),TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD'))
		AND (RBSA.NAME IN (:P_TRX_SOURCE) OR 'ALL' IN  (:P_TRX_SOURCE||'ALL'))
		AND (BU.BU_NAME IN (:P_BU_NAME) OR 'ALL' IN  (:P_BU_NAME||'ALL'))
		AND (XLE.NAME IN (:P_LEGAL_ENTITY) OR 'ALL' IN  (:P_LEGAL_ENTITY||'ALL'))
		AND (HP.PARTY_NAME IN (:P_CUST_NAME) OR 'ALL' IN  (:P_CUST_NAME||'ALL'))
		
UNION

SELECT /*+parallel(12)*/
			ACR.LEGAL_ENTITY_ID||ACR.ORG_ID||ACR.PAY_FROM_CUSTOMER||ACR.CUSTOMER_SITE_USE_ID||GLL.CURRENCY_CODE   KEYID	
		,	XLE.NAME 										LEGAL_ENTITY
		,	XLE.ADDRESS_LINE_1 								LEGAL_ENTITY_ADD1
		,	XLE.ADDRESS_LINE_2||' '||XLE.TOWN_OR_CITY 		LEGAL_ENTITY_ADD2
		,	XLE.POSTAL_CODE||' '||FTLE.NLS_TERRITORY 		LEGAL_ENTITY_ADD3
		,	(
				SELECT 
							REPLACE(FFV.ATTRIBUTE6,'|',CHR(13))
				FROM 
							fnd_flex_value_sets FFVS
						,	fnd_flex_values 	FFV
				WHERE 
							FFVS.flex_value_set_id 	=FFV.flex_value_set_id
						AND FFV.flex_value			=XLE.legal_entity_identifier 
						AND XLE.legal_entity_id 	=ACR.legal_entity_id
						AND FFV.enabled_flag='Y'
						AND TRUNC(SYSDATE) BETWEEN NVL(FFV.start_date_active, trunc(SYSDATE)) AND NVL(FFV.end_date_active, trunc(SYSDATE + 1))
						AND FFVS.flex_value_set_name ='CUSTOM_INVOICE_TEMPLATE_REPORT_INSTRUCTIONS'
			)												LE_ADDRESS
		,	HCA.ACCOUNT_NUMBER  							CUSTOMER_NUMBER
		,	HP.PARTY_NAME 									CUSTOMER_NAME
		,	HL.ADDRESS1 									CUSTOMER_ADD1
		,	HL.ADDRESS2 									CUSTOMER_ADD2
		--,	HL.ADDRESS2||' '||HL.CITY 						CUSTOMER_ADD2
		--,	HL.POSTAL_CODE||' '||HL.STATE||' '||FTC.NLS_TERRITORY 	CUSTOMER_ADD3
		,	HL.CITY											CUSTOMER_ADD3
		,	HL.POSTAL_CODE									CUSTOMER_ADD4
		,	HL.STATE										CUSTOMER_ADD5
		,	FTC.NLS_TERRITORY								CUSTOMER_ADD6
		,	CASE WHEN HL.COUNTRY='CN' THEN HCCSAM.TRANSLATED_CUSTOMER_NAME  ELSE HCCSAM.ATTRIBUTE5 END AKA_NAME
		,	BU.BU_NAME 										BU_NAME
		,	HCCSAM.ATTRIBUTE1	  							BILL_TO_CUSTOMER_NUMBER
		,	GLL.CURRENCY_CODE  								FUNC_CURR_CODE
		,	ACR.RECEIPT_NUMBER 								TRX_NUMBER
		,	'Unapplied'										TRX_TYPE
		,	APSA.TRX_DATE								 	TRX_DATE
		,	NULL											PAYMENT_TERMS 
		,	NULL							 				DUE_DATE
		,	NULL											DAYS_LATE
		,	NULL				 							PO_NUMBER
		,	ACR.currency_code 								INV_CURR_CODE
		,	ACR.comments 									CUSTOMER_REFERENCE
		,	NVL(ACR.amount,0) 								ORIGINAL_AMOUNT_TRX
		,	NVL(AR.applied_receipt_amount,0)*-1				OPEN_AMOUNT_INV_TRX
		,	NVL(AR.applied_receipt_amount*NVL(ACR.exchange_rate,1),0)*-1	OPEN_AMOUNT_FUNC_TRX

FROM

			ar_cash_receipts_all ACR JOIN ar_payment_schedules_all APSA ON ACR.cash_receipt_id=APSA.cash_receipt_id
																		AND NVL(APSA.receipt_confirmed_flag,'Y') = 'Y' 
			JOIN unapplied_receipts AR ON APSA.payment_schedule_id=AR.payment_schedule_id
			JOIN hz_cust_accounts HCA ON ACR.pay_from_customer=HCA.cust_account_id
			JOIN hz_parties HP ON HCA.party_id=HP.party_id
			JOIN hz_cust_site_uses_all HCSUA ON ACR.customer_site_use_id=HCSUA.site_use_id
			JOIN hz_cust_acct_sites_all HCCSAM ON HCSUA.cust_acct_site_id=HCCSAM.cust_acct_site_id
			JOIN hz_party_sites HPS ON HCCSAM.party_site_id=HPS.party_site_id
			JOIN hz_locations HL ON HPS.location_id=HL.location_id
			LEFT OUTER JOIN fnd_territories FTC ON HL.country=FTC.territory_code
			JOIN fun_all_business_units_v BU ON ACR.org_id=BU.bu_id
			JOIN gl_ledgers GLL ON ACR.set_of_books_id=GLL.ledger_id
			JOIN xle_firstparty_information_v XLE ON ACR.legal_entity_id=XLE.legal_entity_id
			LEFT OUTER JOIN fnd_territories FTLE ON  XLE.country=FTLE.territory_code
			
WHERE
			1							=1
		--AND ACR.STATUS					='UNAPP'
		AND APSA.STATUS					='OP'
		AND HCA.CUSTOMER_TYPE 			=:P_ACCOUNT_TYPE
		AND APSA.TRX_DATE				<=NVL(TO_DATE(TRUNC(:P_TRX_DATE),'YYYY-MM-DD'),TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD'))
		AND (BU.BU_NAME IN (:P_BU_NAME) OR 'ALL' IN  (:P_BU_NAME||'ALL'))
		AND (XLE.NAME IN (:P_LEGAL_ENTITY) OR 'ALL' IN  (:P_LEGAL_ENTITY||'ALL'))
		AND (HP.PARTY_NAME IN (:P_CUST_NAME) OR 'ALL' IN  (:P_CUST_NAME||'ALL'))
		
UNION
		
SELECT /*+parallel(12)*/
			ACR.LEGAL_ENTITY_ID||ACR.ORG_ID||ACR.PAY_FROM_CUSTOMER||ACR.CUSTOMER_SITE_USE_ID||GLL.CURRENCY_CODE   KEYID	
		,	XLE.NAME 										LEGAL_ENTITY
		,	XLE.ADDRESS_LINE_1 								LEGAL_ENTITY_ADD1
		,	XLE.ADDRESS_LINE_2||' '||XLE.TOWN_OR_CITY 		LEGAL_ENTITY_ADD2
		,	XLE.POSTAL_CODE||' '||FTLE.NLS_TERRITORY 		LEGAL_ENTITY_ADD3
		,	(
				SELECT 
							REPLACE(FFV.ATTRIBUTE6,'|',CHR(13))
				FROM 
							fnd_flex_value_sets FFVS
						,	fnd_flex_values 	FFV
				WHERE 
							FFVS.flex_value_set_id 	=FFV.flex_value_set_id
						AND FFV.flex_value			=XLE.legal_entity_identifier 
						AND XLE.legal_entity_id 	=ACR.legal_entity_id
						AND FFV.enabled_flag='Y'
						AND TRUNC(SYSDATE) BETWEEN NVL(FFV.start_date_active, trunc(SYSDATE)) AND NVL(FFV.end_date_active, trunc(SYSDATE + 1))
						AND FFVS.flex_value_set_name ='CUSTOM_INVOICE_TEMPLATE_REPORT_INSTRUCTIONS'
			)												LE_ADDRESS
		,	HCA.ACCOUNT_NUMBER  							CUSTOMER_NUMBER
		,	HP.PARTY_NAME 									CUSTOMER_NAME
		,	HL.ADDRESS1 									CUSTOMER_ADD1
		,	HL.ADDRESS2 									CUSTOMER_ADD2
		--,	HL.ADDRESS2||' '||HL.CITY 						CUSTOMER_ADD2
		--,	HL.POSTAL_CODE||' '||HL.STATE||' '||FTC.NLS_TERRITORY 	CUSTOMER_ADD3
		,	HL.CITY											CUSTOMER_ADD3
		,	HL.POSTAL_CODE									CUSTOMER_ADD4
		,	HL.STATE										CUSTOMER_ADD5
		,	FTC.NLS_TERRITORY								CUSTOMER_ADD6
		,	CASE WHEN HL.COUNTRY='CN' THEN HCCSAM.TRANSLATED_CUSTOMER_NAME  ELSE HCCSAM.ATTRIBUTE5 END AKA_NAME
		,	BU.BU_NAME 										BU_NAME
		,	HCCSAM.ATTRIBUTE1	  							BILL_TO_CUSTOMER_NUMBER
		,	GLL.CURRENCY_CODE  								FUNC_CURR_CODE
		,	ACR.RECEIPT_NUMBER 								TRX_NUMBER
		,	'On-Account'									TRX_TYPE
		,	APSA.TRX_DATE								 	TRX_DATE
		,	NULL											PAYMENT_TERMS 
		,	NULL							 				DUE_DATE
		,	NULL											DAYS_LATE
		,	NULL				 							PO_NUMBER
		,	ACR.currency_code 								INV_CURR_CODE
		,	ACR.comments 									CUSTOMER_REFERENCE
		--,	NVL(ACR.amount,0) 								ORIGINAL_AMOUNT_TRX
		,	0				 								ORIGINAL_AMOUNT_TRX
		,	NVL(AR.applied_receipt_amount,0)*-1				OPEN_AMOUNT_INV_TRX
		,	NVL(AR.applied_receipt_amount*NVL(ACR.exchange_rate,1),0)*-1	OPEN_AMOUNT_FUNC_TRX

FROM

			ar_cash_receipts_all ACR JOIN ar_payment_schedules_all APSA ON ACR.cash_receipt_id=APSA.cash_receipt_id
																		   AND NVL(APSA.receipt_confirmed_flag,'Y') = 'Y' 
			JOIN onacc_receipts AR ON APSA.payment_schedule_id=AR.payment_schedule_id
			JOIN hz_cust_accounts HCA ON ACR.pay_from_customer=HCA.cust_account_id
			JOIN hz_parties HP ON HCA.party_id=HP.party_id
			JOIN hz_cust_site_uses_all HCSUA ON ACR.customer_site_use_id=HCSUA.site_use_id
			JOIN hz_cust_acct_sites_all HCCSAM ON HCSUA.cust_acct_site_id=HCCSAM.cust_acct_site_id
			JOIN hz_party_sites HPS ON HCCSAM.party_site_id=HPS.party_site_id
			JOIN hz_locations HL ON HPS.location_id=HL.location_id
			LEFT OUTER JOIN fnd_territories FTC ON HL.country=FTC.territory_code
			JOIN fun_all_business_units_v BU ON ACR.org_id=BU.bu_id
			JOIN gl_ledgers GLL ON ACR.set_of_books_id=GLL.ledger_id
			JOIN xle_firstparty_information_v XLE ON ACR.legal_entity_id=XLE.legal_entity_id
			LEFT OUTER JOIN fnd_territories FTLE ON  XLE.country=FTLE.territory_code
			
WHERE
			1							=1
		AND APSA.STATUS					='OP'
		--AND ACR.STATUS					='UNAPP'
		AND HCA.CUSTOMER_TYPE 			=:P_ACCOUNT_TYPE
		AND APSA.TRX_DATE				<=NVL(TO_DATE(TRUNC(:P_TRX_DATE),'YYYY-MM-DD'),TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD'))
		AND (BU.BU_NAME IN (:P_BU_NAME) OR 'ALL' IN  (:P_BU_NAME||'ALL'))
		AND (XLE.NAME IN (:P_LEGAL_ENTITY) OR 'ALL' IN  (:P_LEGAL_ENTITY||'ALL'))
		AND (HP.PARTY_NAME IN (:P_CUST_NAME) OR 'ALL' IN  (:P_CUST_NAME||'ALL'))
		
UNION

		SELECT /*+parallel(12)*/
			CCA.LEGAL_ENTITY_ID||CCA.BU_ID||CCA.bill_to_customer_id||CCA.bill_to_site_use_id||GLL.currency_code   KEYID	
		,	XLE.NAME 										LEGAL_ENTITY
		,	XLE.ADDRESS_LINE_1 								LEGAL_ENTITY_ADD1
		,	XLE.ADDRESS_LINE_2||' '||XLE.TOWN_OR_CITY 		LEGAL_ENTITY_ADD2
		,	XLE.POSTAL_CODE||' '||FTLE.NLS_TERRITORY 		LEGAL_ENTITY_ADD3
		,	(
				SELECT 
							REPLACE(FFV.ATTRIBUTE6,'|',CHR(13))
				FROM 
							fnd_flex_value_sets FFVS
						,	fnd_flex_values 	FFV
				WHERE 
							FFVS.flex_value_set_id 	=FFV.flex_value_set_id
						AND FFV.flex_value			=XLE.legal_entity_identifier 
						AND XLE.legal_entity_id 	=CCA.legal_entity_id
						AND FFV.enabled_flag='Y'
						AND TRUNC(SYSDATE) BETWEEN NVL(FFV.start_date_active, trunc(SYSDATE)) AND NVL(FFV.end_date_active, trunc(SYSDATE + 1))
						AND FFVS.flex_value_set_name ='CUSTOM_INVOICE_TEMPLATE_REPORT_INSTRUCTIONS'
			)												LE_ADDRESS
		,	HCA.ACCOUNT_NUMBER  							CUSTOMER_NUMBER
		,	HP.PARTY_NAME 									CUSTOMER_NAME
		,	HL.ADDRESS1 									CUSTOMER_ADD1
		,	HL.ADDRESS2 									CUSTOMER_ADD2
		--,	HL.ADDRESS2||' '||HL.CITY 						CUSTOMER_ADD2
		--,	HL.POSTAL_CODE||' '||HL.STATE||' '||FTC.NLS_TERRITORY 	CUSTOMER_ADD3
		,	HL.CITY											CUSTOMER_ADD3
		,	HL.POSTAL_CODE									CUSTOMER_ADD4
		,	HL.STATE										CUSTOMER_ADD5
		,	FTC.NLS_TERRITORY								CUSTOMER_ADD6
		,	CASE WHEN HL.COUNTRY='CN' THEN HCCSAM.TRANSLATED_CUSTOMER_NAME  ELSE HCCSAM.ATTRIBUTE5 END AKA_NAME
		,	BU.BU_NAME 										BU_NAME
		,	HCCSAM.ATTRIBUTE1 								BILL_TO_CUSTOMER_NUMBER
		,	GLL.CURRENCY_CODE  								FUNC_CURR_CODE
		,	CCA.claim_number 								TRX_NUMBER
		,	'Claim'											TRX_TYPE
		,	CCA.claim_date								 	TRX_DATE
		,	NULL											PAYMENT_TERMS 
		,	NULL							 				DUE_DATE
		,	NULL											DAYS_LATE
		,	NULL				 							PO_NUMBER
		,	CCA.currency_code								INV_CURR_CODE
		,	NULL		 									CUSTOMER_REFERENCE
		--,	NVL(CCA.amount,0) 								ORIGINAL_AMOUNT_TRX
		,	0				 								ORIGINAL_AMOUNT_TRX
		,	NVL(CCA.amount,0) - NVL(CCA.amount_adjusted,0) 	OPEN_AMOUNT_INV_TRX
		,	NVL(CCA.acctd_amount,0) - NVL(CCA.acctd_amount_adjusted,0)	OPEN_AMOUNT_FUNC_TRX

FROM

			cjm_claims_all CCA JOIN hz_cust_accounts HCA ON CCA.bill_to_customer_id=HCA.cust_account_id
			JOIN ar_receivable_applications_all ARA ON CCA.receivable_application_id=ARA.receivable_application_id
			JOIN ar_cash_receipts_all ACR ON ARA.cash_receipt_id=ACR.cash_receipt_id
			JOIN gl_ledgers GLL ON ACR.set_of_books_id=GLL.ledger_id
			LEFT OUTER JOIN cjm_claim_types_tl CCT ON CCA.claim_type_id=CCT.claim_type_id
										AND CCT.LANGUAGE=USERENV('LANG')
			JOIN hz_parties HP ON HCA.party_id=HP.party_id
			JOIN hz_cust_site_uses_all HCSUA ON CCA.bill_to_site_use_id=HCSUA.site_use_id
			JOIN hz_cust_acct_sites_all HCCSAM ON HCSUA.cust_acct_site_id=HCCSAM.cust_acct_site_id
			JOIN hz_party_sites HPS ON HCCSAM.party_site_id=HPS.party_site_id
			JOIN hz_locations HL ON HPS.location_id=HL.location_id
			LEFT OUTER JOIN fnd_territories FTC ON HL.country=FTC.territory_code
			JOIN fun_all_business_units_v BU ON CCA.bu_id=BU.bu_id
			JOIN xle_firstparty_information_v XLE ON CCA.legal_entity_id=XLE.legal_entity_id
			LEFT OUTER JOIN fnd_territories FTLE ON  XLE.country=FTLE.territory_code
			
WHERE
			1							=1
		AND CCA.STATUS_CODE				='OPEN'
		AND HCA.CUSTOMER_TYPE 			=:P_ACCOUNT_TYPE
		AND CCA.claim_date				<=NVL(TO_DATE(TRUNC(:P_TRX_DATE),'YYYY-MM-DD'),TO_DATE(TRUNC(:P_AS_OF_DATE),'YYYY-MM-DD'))
		AND (BU.BU_NAME IN (:P_BU_NAME) OR 'ALL' IN  (:P_BU_NAME||'ALL'))
		AND (XLE.NAME IN (:P_LEGAL_ENTITY) OR 'ALL' IN  (:P_LEGAL_ENTITY||'ALL'))
		AND (HP.PARTY_NAME IN (:P_CUST_NAME) OR 'ALL' IN  (:P_CUST_NAME||'ALL'))
)
WHERE OPEN_AMOUNT_INV_TRX<>0

:MI :P_ACCOUNT_TYPE :P_AS_OF_DATE :P_BU_NAME :P_CUST_NAME :P_LEGAL_ENTITY :P_TRX_DATE :P_TRX_SOURCE :SS

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
RA_CUSTOMER_TRX_ALL	dimension	dimension
AR_RECEIVABLE_APPLICATIONS_ALL	dimension	dimension
RA_CUST_TRX_TYPES_ALL	dimension	dimension
HZ_CUST_ACCOUNTS	dimension	dimension
Original Amount	measure	measure
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.

Receivables data model →Enterprise model →

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
AR_PAYMENT_SCHEDULES_ALL	32	6
RA_CUSTOMER_TRX_ALL	58	16
AR_RECEIVABLE_APPLICATIONS_ALL	35	2
RA_CUST_TRX_TYPES_ALL	7	8
HZ_CUST_ACCOUNTS	14	43

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.