Invoice Settlement Report

Receivables

How each invoice was settled — the receipts, credit memos, adjustments, and write-offs that cleared it to zero, by customer and account, so AR can prove every closed invoice and explain how it was satisfied.

Sample build of the Invoice Settlement Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.

Sample build · illustrative

Filters

P Cust Num

1003

P Date From

2026-02-28

P Date To

2026-02-28

P Entity

Globex Holdings

P Organization Id

1003

P Payment Term Type

Standard

4,800

Invoices settled

$18.20M

Settled value

$74K

Settled by write-off

Invoice	Customer	Original Amount	Receipts	Adjustments	Write-off	Settled Date
Sample	Acme Industrial	$1,240,500.00	Sample	Sample	Sample	2026-04-30
—	Northwind Trading	$842,150.75	—	—	—	2026-03-31
Sample	Globex Holdings	$96,400.00	Sample	Sample	Sample	2026-02-28
—	Initech LLC	$1,005,233.10	—	—	—	2026-01-31
Sample	Umbrella Corp	$58,720.40	Sample	Sample	Sample	2025-12-31
Sample	Acme Industrial	$1,240,500.00	Sample	Sample	Sample	2026-04-30

AI Analyst · active

reading

The report reads the applications and adjustments that cleared each invoice to zero.

flag

$74K of invoices were cleared by write-off rather than cash — revenue recognized but never collected.

root cause & next step

Review the write-off authorizations; a rising settled-by-write-off figure is bad debt arriving quietly.

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

select business_unit
       ,Entity
	   ,customer_name
	   ,receipt_number
	   ,customer_number
	   ,receipt_method
	   ,Selling_Profile
	   ,transaction_class
	   ,Transaction_class_Name
	   ,cust_trx_type_seq_id
	   ,bill_to_location
	   ,Document_Number
	   ,gl_date
	   ,payment_term_type
	   ,amount
	   ,Payment_Terms
	   ,Payment_Terms_desc
	   ,to_char(deposit_date ,'MM/DD/YYYY') deposit_date
	   ,to_char(trx_date,'MM/DD/YYYY') Transaction_Date
	   ,due_date
	   ,transaction_type
	   ,terms_sequence_number
	   ,amount_due_original
	   ,amount_due_remaining
	   ,receipt_gl_date
	   ,cash_receipt_id
	   ,to_char(bank_in_date,'MM/DD/YY') bank_in_date
	   ,issuing_bank_name
	   ,(trunc(bank_in_date) - trunc(trx_date)) number_of_presentation_days 
	   ,(trunc(deposit_date)- trunc(bank_in_date))  number_of_settlement_days_bank_in_date
	   ,trunc(deposit_date)- trunc(trx_date) number_of_settlement_days_trans_date
	   ,entd_currency
	   ,entd_amt_due_original
	   ,entd_applied_amt
	   ,entd_amt_due_remaining
	   ,rate
	   ,fun_currency_code
	   ,fun_amt_due_original
	   ,fun_amt_applied_amt
	   ,fun_amt_due_re
	   ,exchange_gain_loss
	   ,status
	   ,Price_Term
from (  
	select 
	 haot.name business_unit
	 
	,(
	SELECT 
	ffv.flex_value||' - '||ffv.description  entity_value
	FROM FND_ID_FLEX_SEGMENTS_VL ffs, fnd_flex_values_vl ffv 
	,gl_ledgers gl 
	,GL_LEDGER_NORM_SEG_VALS leseg
	,hr_operating_units hou
	WHERE ffs.segment_name ='Entity' and ffs.enabled_flag='Y'
	and ffs.FLEX_VALUE_SET_ID = ffv.FLEX_VALUE_SET_ID and ffv.ENABLED_FLAG ='Y'	
	and gl.chart_of_accounts_id = ffs.ID_FLEX_NUM
	and leseg.ledger_id= gl.ledger_id
	and leseg.SEGMENT_VALUE = ffv.flex_value
	and hou.SET_OF_BOOKS_ID  = gl.LEDGER_ID	
	AND hou.organization_id = acra.org_id
	and ffv.flex_value = cc.segment1 
	)Entity
	,hp.party_name customer_name
	,acra.receipt_number
	,hca.account_number customer_number
	,arm.name receipt_method
	,(select attribute2 from hz_cust_acct_sites_all 
					   where cust_acct_site_id= hcsu.cust_acct_site_id 
						 and cust_account_id=hca.cust_account_id ) Selling_Profile
	,rctta.type transaction_class
	,flv.meaning Transaction_class_Name
	,rcta.cust_trx_type_seq_id
	,hcsu.location bill_to_location
	,rcta.trx_number Document_Number
	,to_char(araa.gl_date,'MM/DD/YYYY') gl_date
	,CASE
		WHEN upper(rtt.name) like 'LC%' THEN 'Letter of credit'
		ELSE 'Open account'
	END payment_term_type
	,acra.amount
	,rtt.name Payment_Terms	
	,rtt.Description Payment_Terms_desc
	--,to_char(acra.deposit_date,'MM/DD/YYYY') deposit_date
	,acra.deposit_date
	--,to_char(rcta.trx_date,'MM/DD/YYYY') Transaction_Date
	,rcta.trx_date
	,to_char(apsa.due_date,'MM/DD/YYYY') due_date
	,rctta.name transaction_type
	,apsa.terms_sequence_number
	,apsa.amount_due_original
	,apsa.amount_due_remaining
	,to_char(acrha.gl_date,'MM/DD/YYYY') receipt_gl_date
	,araa.cash_receipt_id
	--,rcta.attribute14 bank_in_date  
	--,to_char(to_date (rcta.attribute14,'YYYY/MM/DD'),'MM/DD/YYYY')  bank_in_date--REF-289228
	  ,( CASE
			-- Check for the first format (e.g., 'MM/DD/YY')  -- Conversion data format
			WHEN REGEXP_LIKE(rcta.attribute14, '^[0-9]{2}/[0-9]{2}/[0-9]{2}$') THEN
				TO_DATE(rcta.attribute14, 'MM/DD/YY')
			-- Check for the second format (e.g., 'YYYYMMDD') -- Integraton format
			WHEN REGEXP_LIKE(rcta.attribute14, '^[0-9]{8}$') THEN
		        TO_DATE(rcta.attribute14, 'YYYY/MM/DD')
			-- Check for the second format (e.g., 'YYYY/DD/MM')
			WHEN REGEXP_LIKE(rcta.attribute14, '^[0-9]{4}/[0-9]{2}/[0-9]{2}$') THEN
		        TO_DATE(rcta.attribute14, 'YYYY/MM/DD')
				
			ELSE
				NULL -- Handle cases with unmatched formats
		END ) bank_in_date  --REF-289228

	--,to_char(rcta.ATTRIBUTE_DATE1,'MM/DD/YYYY') bank_in_date
	,rcta.attribute6 issuing_bank_name
	--,trunc(to_date(rcta.attribute14 ,'YYYY/MM/DD'))- trunc(rcta.trx_date) number_of_presentation_days 
	--,trunc(acra.deposit_date)- trunc(to_date(rcta.attribute14 ,'YYYY/MM/DD'))  number_of_settlement_days_bank_in_date
	--,trunc(rcta.ATTRIBUTE_DATE1 )- trunc(rcta.trx_date) number_of_presentation_days
	--,trunc(acra.deposit_date)- trunc(rcta.ATTRIBUTE_DATE1 )  number_of_settlement_days_bank_in_date
	,trunc(acra.deposit_date)- trunc(rcta.trx_date) number_of_settlement_days_trans_date
	,apsa.invoice_currency_code entd_currency
	,apsa.amount_due_original entd_amt_due_original
	--,apsa.amount_applied entd_applied_amt  
	,araa.AMOUNT_APPLIED entd_applied_amt
	,apsa.amount_due_remaining entd_amt_due_remaining
	,nvl(apsa.exchange_rate,1) rate
	,(select gl.currency_code from gl_ledgers gl where gl.ledger_id =rcta.set_of_books_id)fun_currency_code
	,apsa.amount_due_original* nvl(apsa.exchange_rate,1) fun_amt_due_original
	--,apsa.amount_applied*nvl(apsa.exchange_rate,1) fun_amt_applied_amt
	,araa.AMOUNT_APPLIED*nvl(apsa.exchange_rate,1) fun_amt_applied_amt
	,apsa.acctd_amount_due_remaining    fun_amt_due_re
	,(araa.acctd_amount_applied_from - araa.acctd_amount_applied_to) exchange_gain_loss 
	,(select meaning from fnd_lookup_values 
					where lookup_type='PAYMENT_SCHEDULE_STATUS' 
					and language = USERENV ('LANG') and lookup_code =apsa.status ) status
	,rcta.attribute11 Price_Term				
	from 
		 ar_receivable_applications_all araa
		,ra_customer_trx_all rcta
		,ra_cust_trx_types_all rctta
		,fnd_lookup_values flv
		,ar_payment_schedules_all apsa
		,ar_cash_receipts_all acra
		,ar_cash_receipt_history_all acrha
		,hr_organization_units_f_tl haot
		,ar_receipt_methods arm
		,hz_parties hp
		,hz_cust_accounts hca
		,hz_cust_site_uses_all hcsu
		,ra_terms_tl rtt
		,gl_code_combinations cc
		/*,CE_BANK_ACCT_USES_OU          REMIT_BANK
		,CE_BANK_ACCOUNTS                CBA
		,CE_BANK_BRANCHES_V              BB*/
		
	where 1=1
	 and    araa.display = 'Y'
	 and    araa.cash_receipt_id = acra.cash_receipt_id
	 and    acra.cash_receipt_id = acrha.cash_receipt_id
	 and    acrha.current_record_flag ='Y'
	 and    araa.applied_customer_trx_id = rcta.customer_trx_id
	 and    rcta.cust_trx_type_seq_id =rctta.cust_trx_type_seq_id
	 and    rctta.type =flv.lookup_code
	 and    flv.lookup_type ='INV/CM'
	 and    flv.language = USERENV ('LANG')
	 and    rctta.type IN ('INV','CM') 
	 and    apsa.customer_trx_id = rcta.customer_trx_id
	 and    acra.org_id =haot.organization_id
	 and    haot.language =USERENV ('LANG')
	 and    sysdate between haot.effective_start_date and haot.effective_end_date
	 and    acra.receipt_method_id=arm.receipt_method_id
	 and    acra.pay_from_customer = hca.cust_account_id
	 and    hca.party_id = hp.party_id
	 and    acra.customer_site_use_id = hcsu.site_use_id   -- bill_to
	 and    rcta.term_id = rtt.term_id(+)
	 and    rtt.language(+) = USERENV ('LANG')
	 and    araa.applied_payment_schedule_id =apsa.payment_schedule_id
	 and araa.code_combination_id = to_number(cc.code_combination_id)
	 and (cc.segment1  in (:p_entity ) or 'All' in ( :p_entity||'All') )
	 --and    acra.org_id = :p_organization_id
	 
	 and    (acra.org_id in (:p_organization_id) or 'All' in (:p_organization_id||'All'))
	 
	 and (((araa.gl_date BETWEEN NVL(:p_date_from , araa.gl_date) and NVL(:p_date_to, araa.gl_date)) and :p_run_by_date='GL Date')
		  or
		 (( acra.deposit_date between NVL(:p_date_from , acra.deposit_date) and NVL(:p_date_to, acra.deposit_date)) and :p_run_by_date='Deposit Date')) 
	and (hca.account_number in (:p_cust_num) or '1' in (:p_cust_num||'1'))
	and  nvl(rtt.term_id,0)  = nvl( :p_term_id,nvl(rtt.term_id,0))
	and rctta.type = nvl(:p_transaction_class,rctta.type)
	and    acra.org_id in 
	(
	SELECT distinct bu.bu_id
	FROM fusion.fun_all_business_units_v bu
	,fusion.fun_user_role_data_asgnmnts role
	,fusion.per_users pu
	,fusion.per_roles_dn_vl pr
	WHERE role.org_id = bu.bu_id
	AND pu.user_guid = role.user_guid
	AND pr.role_common_name = role.ROLE_NAME
	and role.active_flag ='Y' 
	and pu.username = fnd_global.user_name
	)

	--and    acra.receipt_number in ('4')
	--and rcta.trx_number='32003'
	 /*AND REMIT_BANK.BANK_ACCT_USE_ID(+) = acra.REMIT_BANK_ACCT_USE_ID
	 AND REMIT_BANK.ORG_ID(+) = acra.ORG_ID
	 AND remit_bank.bank_account_id = CBA.bank_account_id(+)
	 AND BB.BRANCH_PARTY_ID(+) = CBA.BANK_BRANCH_ID*/

 )
where 1=1
 and  payment_term_type = nvl( :p_payment_term_type,payment_term_type)
order by 1,2,3,4

:p_cust_num :p_date_from :p_date_to :p_entity :p_organization_id :p_payment_term_type :p_run_by_date :p_term_id :p_transaction_class

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_PAYMENT_SCHEDULES_ALL	dimension	dimension
AR_RECEIVABLE_APPLICATIONS_ALL	dimension	dimension
AR_ADJUSTMENTS_ALL	dimension	dimension
HZ_CUST_ACCOUNTS	dimension	dimension
Original 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
RA_CUSTOMER_TRX_ALL	58	16
AR_PAYMENT_SCHEDULES_ALL	32	6
AR_RECEIVABLE_APPLICATIONS_ALL	35	2
AR_ADJUSTMENTS_ALL	19	2
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.