Supplier Balance by Account Report

Payables

The open Payables balance owed to each supplier, broken out by the liability account it sits in, as of a date — so AP can reconcile the supplier subledger to the GL liability accounts and see which suppliers and accounts make up the payable.

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

Sample build · illustrative

Filters

From Acc

Sample

From Brand

Sample

From Cc

Sample

From Entity

Globex Holdings

From Future1

Sample

From Future2

Sample

$8.42M

Open AP

312

Suppliers

Liability accounts

Supplier	Liability Account	Open Balance	Invoices Open	Oldest Invoice	Currency
Acme Industrial	1000-2100-000	$1,240,500.00	Sample	Sample	USD
Northwind Trading	1000-5400-000	$842,150.75	—	—	USD
Globex Holdings	1000-1410-000	$96,400.00	Sample	Sample	USD
Initech LLC	2000-2100-000	$1,005,233.10	—	—	USD
Umbrella Corp	1000-6300-000	$58,720.40	Sample	Sample	USD
Acme Industrial	1000-2100-000	$1,240,500.00	Sample	Sample	USD

AI Analyst · active

reading

The report sums open amounts from AP_PAYMENT_SCHEDULES_ALL by supplier and the liability account on each distribution.

flag

One liability account holds about 70% of the open balance — fine if intended, but a single account carrying most of AP often means supplier-site account derivation isn't splitting by entity or expense type as designed.

root cause & next step

Confirm the liability-account derivation on supplier sites; if everything lands in one account, reconciling to GL by entity becomes manual.

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 SECURITY_TBL AS
(
	SELECT 
			DISTINCT HOU.organization_id
	FROM 
			hr_operating_units  HOU,
			fun_user_role_data_asgnmnts FURDA,
			per_roles_dn PRD,
			per_user_roles PUR,
			per_users PU
	WHERE 
			1							=1
		AND FURDA.org_id 				=HOU.organization_id
		AND UPPER(FURDA.role_name) IN ('XXC_ACCOUNTS_PAYABLE_ANALYST_JOB', 'XXC_ACCOUNTS_PAYABLES_MANAGER_JOB')
		AND UPPER(FURDA.active_flag) 	='Y'
		AND UPPER(FURDA.role_name) 		=UPPER(PRD.role_common_name)
		AND PRD.role_id 				=PUR.role_id
		AND PUR.user_id 				=PU.user_id
		AND upper(pu.username) 			=upper(:xdo_user_name)
		AND pu.user_guid 				=FURDA.user_guid
)

SELECT			
			ACCOUNT
		,	DESCRIPTION
		,	S_NUM
		,	VENDOR_NAME
		,	PP_DR
		,	PP_CR
		,	EPPBD
		,	EPPBC
		,	CP_DR
		,	CP_CR
		,	TSBD
		,	TSBC
		,	TEPBD
		,	TEPBC
		,	T_PP_DR
		,	T_PP_CR
		,	T_CP_DR
		,	T_CP_CR

FROM
(
	SELECT /*+parallel(12)*/
			ACCOUNT
		,	DESCRIPTION
		,	S_NUM
		,	VENDOR_NAME
		,	PP_DR
		,	PP_CR
		,	CASE WHEN PP_DR-PP_CR>0 THEN PP_DR-PP_CR ELSE 0 END EPPBD
		,	CASE WHEN PP_DR-PP_CR<0 THEN PP_DR-PP_CR ELSE 0 END EPPBC
		,	CP_DR
		,	CP_CR
		,	PP_DR+CP_DR		TSBD
		,	PP_CR+CP_CR		TSBC
		,	CASE WHEN ((PP_DR+CP_DR)-(PP_CR+CP_CR))>0 THEN ((PP_DR+CP_DR)-(PP_CR+CP_CR)) ELSE 0 END TEPBD
		,	CASE WHEN ((PP_DR+CP_DR)-(PP_CR+CP_CR))<0 THEN ((PP_DR+CP_DR)-(PP_CR+CP_CR)) ELSE 0 END TEPBC
		,	SUM(PP_DR) OVER(PARTITION BY ACCOUNT) T_PP_DR
		,	SUM(PP_CR) OVER(PARTITION BY ACCOUNT) T_PP_CR
		,	SUM(CP_DR) OVER(PARTITION BY ACCOUNT) T_CP_DR
		,	SUM(CP_CR) OVER(PARTITION BY ACCOUNT) T_CP_CR
FROM
(
	SELECT 
			ACCOUNT
		,	DESCRIPTION
		,	S_NUM
		,	VENDOR_NAME
		,	SUM(PREV_PER_DR)		PP_DR
		,	SUM(PREV_PER_CR)		PP_CR
		,	SUM(CURR_PER_DR)  		CP_DR
		,	SUM(CURR_PER_CR)  		CP_CR	
FROM		
(
	SELECT
			XEP.name			LE_NAME
		,	NVL(POZ.segment1,(SELECT PAP.person_number FROM per_all_people_f PAP
								WHERE TO_CHAR(PAP.person_id)=HP.ORIG_SYSTEM_REFERENCE
								AND TRUNC(SYSDATE) BETWEEN PAP.EFFECTIVE_START_DATE AND PAP.EFFECTIVE_END_DATE))		S_NUM
		,	HP.party_name		VENDOR_NAME
		,	POZ.vendor_type_lookup_code  
		,	PSS.vendor_site_code 
		,	GCC.segment1		ENITY
		,	GCC.segment2        SM
		,	GCC.segment3        CC
		,	GCC.segment4        ACCOUNT
		,	FT.description
		,	GCC.segment5        LOCATION
		,	GCC.segment6        BRAND
		,	GCC.segment7        INTERCOMP
		,	GCC.segment8        FUTURE1
		,	GCC.segment9        FUTURE2
		--,	NVL(XAL.entered_dr,0) 	CURR_PER_DR
		, 	CASE WHEN GLP.start_date >= (select distinct start_date from gl_periods where period_set_name = GLP.period_set_name and period_name = :P_FROM_PERIOD) AND GLP.end_date <= (select distinct end_date from gl_periods where period_set_name = GLP.period_set_name and period_name = :P_TO_PERIOD) THEN NVL(XAL.accounted_dr,0) ELSE 0 END CURR_PER_DR
		--,	NVL(XAL.entered_cr,0) 	CURR_PER_CR	
		,	CASE WHEN GLP.start_date >= (select distinct start_date from gl_periods where period_set_name = GLP.period_set_name and period_name = :P_FROM_PERIOD) AND GLP.end_date <= (select distinct end_date from gl_periods where period_set_name = GLP.period_set_name and period_name = :P_TO_PERIOD) THEN NVL(XAL.accounted_cr,0) ELSE 0 END CURR_PER_CR
		--,	NVL(BB.beg_bal_dr,0) 		PREV_PER_DR
		--,	NVL(BB.beg_bal_cr,0) 		PREV_PER_CR
		,	CASE WHEN GLP.start_date < (select distinct start_date from gl_periods where period_set_name = GLP.period_set_name and period_name = :P_FROM_PERIOD) THEN NVL(XAL.accounted_dr,0) ELSE 0 END PREV_PER_DR	
		,	CASE WHEN GLP.start_date < (select distinct start_date from gl_periods where period_set_name = GLP.period_set_name and period_name = :P_FROM_PERIOD) THEN NVL(XAL.accounted_cr,0) ELSE 0 END PREV_PER_CR
		
	FROM
			gl_ledgers					GLL
		,	gl_je_headers				GJH
		,	gl_je_lines					GJL
		,	gl_je_batches				GJB
		--,	gl_je_sources_tl            GJS
        --,	gl_je_categories_tl         GJC
		,	gl_code_combinations		GCC
		,	gl_periods					GLP
		,	fnd_flex_values 			FFV
		,	fnd_flex_values_tl			FT
		,	gl_import_references 		GIR
		,	xla_ae_lines 				XAL
		,	xla_ae_headers			 	XAH
		,	xla_transaction_entities 	XTE
		,	ap_invoices_all				AIA
		,	hz_parties					HP
		,	poz_suppliers_v				POZ
		, 	poz_supplier_sites_all_m 	PSS
		,	hr_operating_units     	 	HOU
		,	xle_entity_profiles			XEP
		,	security_tbl 				ST
		
	WHERE
		1									=1
		AND	GLL.ledger_id					=GJH.ledger_id
		AND	GJH.je_batch_id					=GJB.je_batch_id
		AND GJH.je_header_id				=GJL.je_header_id
		AND GJL.code_combination_id	    	=GCC.code_combination_id
		AND GLP.period_name 				=GJB.default_period_name 
        AND GLP.period_type 				=GLL.accounted_period_type
		AND GCC.segment4					=FFV.flex_value
		AND FFV.value_category				='GREECE ACCOUNT VALUE SET'
		AND FFV.flex_value_id 				=FT.flex_value_id
		AND UPPER(FT.language)				='EL'
		AND GJL.je_header_id 				=GIR.je_header_id 
		AND GJL.je_line_num 				=GIR.je_line_num 
		AND GIR.gl_sl_link_id 				=XAL.gl_sl_link_id 	
		AND GIR.gl_sl_link_table 			=XAL.gl_sl_link_table 
		AND XAL.ae_header_id 				=XAH.ae_header_id
		AND XAH.application_id 				=XTE.application_id
        AND XAH.entity_id 					=XTE.entity_id
		AND XTE.source_id_int_1 			=AIA.invoice_id
		AND AIA.party_id					=HP.party_id
		AND HP.party_id 					=POZ.party_id(+)
		AND AIA.vendor_site_id      	 	=PSS.vendor_site_id(+)
		AND AIA.org_id						=HOU.organization_id
		AND AIA.legal_entity_id				=XEP.legal_entity_id
		AND ST.organization_id				=HOU.organization_id
		AND GJH.je_source					='Payables'
		AND XTE.entity_code 				='AP_INVOICES'
		AND XAH.accounting_entry_status_code='F'
		AND XAH.GL_TRANSFER_STATUS_CODE		='Y'
		AND HOU.NAME						='GR BU EUR'
		AND GLP.end_date <= (select distinct end_date from gl_periods where period_set_name = GLP.period_set_name and period_name = :P_TO_PERIOD)
		
UNION ALL
	
	SELECT
			XEP.name			LE_NAME
		,	NVL(POZ.segment1,(SELECT PAP.person_number FROM per_all_people_f PAP
								WHERE TO_CHAR(PAP.person_id)=HP.ORIG_SYSTEM_REFERENCE
								AND TRUNC(SYSDATE) BETWEEN PAP.EFFECTIVE_START_DATE AND PAP.EFFECTIVE_END_DATE))		S_NUM
		,	HP.party_name		VENDOR_NAME
		,	POZ.vendor_type_lookup_code  
		,	PSS.vendor_site_code 
		,	GCC.segment1		ENITY
		,	GCC.segment2        SM
		,	GCC.segment3        CC
		,	GCC.segment4        ACCOUNT
		,	FT.description
		,	GCC.segment5        LOCATION
		,	GCC.segment6        BRAND
		,	GCC.segment7        INTERCOMP
		,	GCC.segment8        FUTURE1
		,	GCC.segment9        FUTURE2
		--,	NVL(XAL.entered_dr,0) 	CURR_PER_DR
		--,	NVL(XAL.entered_cr,0) 	CURR_PER_CR
		, 	CASE WHEN GLP.start_date >= (select distinct start_date from gl_periods where period_set_name = GLP.period_set_name and period_name = :P_FROM_PERIOD) AND GLP.end_date <= (select distinct end_date from gl_periods where period_set_name = GLP.period_set_name and period_name = :P_TO_PERIOD) THEN NVL(XAL.accounted_dr,0) ELSE 0 END CURR_PER_DR
		,	CASE WHEN GLP.start_date >= (select distinct start_date from gl_periods where period_set_name = GLP.period_set_name and period_name = :P_FROM_PERIOD) AND GLP.end_date <= (select distinct end_date from gl_periods where period_set_name = GLP.period_set_name and period_name = :P_TO_PERIOD) THEN NVL(XAL.accounted_cr,0) ELSE 0 END CURR_PER_CR
		--,	NVL(BB.beg_bal_dr,0) 	PREV_PER_DR
		--,	NVL(BB.beg_bal_cr,0) 	PREV_PER_CR
		,	CASE WHEN GLP.start_date < (select distinct start_date from gl_periods where period_set_name = GLP.period_set_name and period_name = :P_FROM_PERIOD) THEN NVL(XAL.accounted_dr,0) ELSE 0 END PREV_PER_DR	
		,	CASE WHEN GLP.start_date < (select distinct start_date from gl_periods where period_set_name = GLP.period_set_name and period_name = :P_FROM_PERIOD) THEN NVL(XAL.accounted_cr,0) ELSE 0 END PREV_PER_CR
		
	FROM
			gl_ledgers					GLL
		,	gl_je_headers				GJH
		,	gl_je_lines					GJL
		,	gl_je_batches				GJB
		--,	gl_je_sources_tl            GJS
        --,	gl_je_categories_tl         GJC
		,	gl_code_combinations		GCC
		,	gl_periods					GLP
		,	fnd_flex_values 			FFV
		,	fnd_flex_values_tl			FT
		,	gl_import_references 		GIR
		,	xla_ae_lines 				XAL
		,	xla_ae_headers			 	XAH
		,	xla_transaction_entities 	XTE
		,	ap_checks_all 				AC
		,	hz_parties					HP
		,	poz_suppliers_v				POZ
		, 	poz_supplier_sites_all_m 	PSS
		,	hr_operating_units     	 	HOU
		,	xle_entity_profiles			XEP
		,	security_tbl 				ST
		
	WHERE
		1									=1
		AND	GLL.ledger_id					=GJH.ledger_id
		AND	GJH.je_batch_id					=GJB.je_batch_id
		AND GJH.je_header_id				=GJL.je_header_id
		AND GJL.code_combination_id	    	=GCC.code_combination_id
		AND GLP.period_name 				=GJB.default_period_name 
        AND GLP.period_type 				=GLL.accounted_period_type
		AND GCC.segment4					=FFV.flex_value
		AND FFV.value_category				='GREECE ACCOUNT VALUE SET'
		AND FFV.flex_value_id 				=FT.flex_value_id
		AND UPPER(FT.language)				='EL'
		AND GJL.je_header_id 				=GIR.je_header_id 
		AND GJL.je_line_num 				=GIR.je_line_num 
		AND GIR.gl_sl_link_id 				=XAL.gl_sl_link_id 	
		AND GIR.gl_sl_link_table 			=XAL.gl_sl_link_table 
		AND XAL.ae_header_id 				=XAH.ae_header_id
		AND XAH.application_id 				=XTE.application_id
        AND XAH.entity_id 					=XTE.entity_id
		AND xte.source_id_int_1 			=AC.check_id
		AND AC.party_id						=HP.party_id
		AND HP.party_id 					=POZ.party_id(+)
		AND AC.vendor_site_id      	 		=PSS.vendor_site_id(+)
		AND AC.org_id						=HOU.organization_id
		AND AC.legal_entity_id				=XEP.legal_entity_id
		AND ST.organization_id				=HOU.organization_id
		AND GJH.je_source					='Payables'
		AND XTE.entity_code 				='AP_PAYMENTS'
		AND HOU.NAME						='GR BU EUR' 
		AND XAH.accounting_entry_status_code='F'
		AND XAH.GL_TRANSFER_STATUS_CODE		='Y'
		AND GLP.end_date <= (select distinct end_date from gl_periods where period_set_name = GLP.period_set_name and period_name = :P_TO_PERIOD)
)
WHERE 
			1			=1
		AND CAST(FUTURE2 AS NUMERIC) BETWEEN NVL(:P_FROM_FUTURE2,CAST(FUTURE2 AS NUMERIC)) AND NVL(:P_TO_FUTURE2,CAST(FUTURE2 AS NUMERIC))
		AND CAST(FUTURE1 AS NUMERIC) BETWEEN NVL(:P_FROM_FUTURE1,CAST(FUTURE1 AS NUMERIC)) AND NVL(:P_TO_FUTURE1,CAST(FUTURE1 AS NUMERIC))
		AND CAST(INTERCOMP AS NUMERIC) BETWEEN NVL(:P_FROM_IC,CAST(INTERCOMP AS NUMERIC)) AND NVL(:P_TO_IC,CAST(INTERCOMP AS NUMERIC))
		AND CAST(BRAND AS NUMERIC) BETWEEN NVL(:P_FROM_BRAND,CAST(BRAND AS NUMERIC)) AND NVL(:P_TO_BRAND,CAST(BRAND AS NUMERIC))
		AND CAST(LOCATION AS NUMERIC) BETWEEN NVL(:P_FROM_LOC,CAST(LOCATION AS NUMERIC)) AND NVL(:P_TO_LOC,CAST(LOCATION AS NUMERIC))
		AND CAST(ACCOUNT AS NUMERIC) BETWEEN NVL(CAST(:P_FROM_ACC AS NUMERIC),CAST(ACCOUNT AS NUMERIC)) AND NVL(CAST(:P_TO_ACC AS NUMERIC),CAST(ACCOUNT AS NUMERIC))
		AND CAST(CC AS NUMERIC) BETWEEN NVL(:P_FROM_CC,CAST(CC AS NUMERIC)) AND NVL(:P_TO_CC,CAST(CC AS NUMERIC))
		AND CAST(SM AS NUMERIC) BETWEEN NVL(:P_FROM_SM,CAST(SM AS NUMERIC)) AND NVL(:P_TO_SM,CAST(SM AS NUMERIC))
		AND CAST(ENITY AS NUMERIC) BETWEEN NVL(:P_FROM_ENTITY,CAST(ENITY AS NUMERIC)) AND NVL(:P_TO_ENTITY,CAST(ENITY AS NUMERIC))
		AND (S_NUM IN (:P_S_NUM) OR  'All' IN (:P_S_NUM||'All'))
		AND (VENDOR_SITE_CODE IN (:P_S_SITE) OR  'All' IN (:P_S_SITE||'All'))
		AND (VENDOR_NAME IN (:P_S_NAME) OR  'All' IN (:P_S_NAME||'All'))
		AND (VENDOR_TYPE_LOOKUP_CODE IN (:P_VTYPE) OR  'All' IN (:P_VTYPE||'All'))
		AND (LE_NAME IN (:P_LE_NAME) OR  'All' IN (:P_LE_NAME||'All'))

GROUP BY 
			ACCOUNT
		,	DESCRIPTION
		,	S_NUM
		,	VENDOR_NAME
)
)
WHERE
		(CASE WHEN :P_INC_LINES='Yes' THEN 'Yes' 
			  WHEN TSBD+TSBC<>0 AND :P_INC_LINES='No' THEN 'No' END)=NVL(:P_INC_LINES,'Yes')
	AND  (CASE WHEN :P_INC_BAL='Yes' THEN 'Yes' 
			  WHEN TEPBD+TEPBC<>0 AND :P_INC_BAL='No' THEN 'No' END)=NVL(:P_INC_BAL,'Yes')
			  
ORDER BY
			CAST(ACCOUNT AS INT)

:P_FROM_ACC :P_FROM_BRAND :P_FROM_CC :P_FROM_ENTITY :P_FROM_FUTURE1 :P_FROM_FUTURE2 :P_FROM_IC :P_FROM_LOC :P_FROM_PERIOD :P_FROM_SM :P_INC_BAL :P_INC_LINES :P_LE_NAME :P_S_NAME :P_S_NUM :P_S_SITE :P_TO_ACC :P_TO_BRAND :P_TO_CC :P_TO_ENTITY :P_TO_FUTURE1 :P_TO_FUTURE2 :P_TO_IC :P_TO_LOC :P_TO_PERIOD :P_TO_SM :P_VTYPE :xdo_user_name

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_PAYMENT_SCHEDULES_ALL	dimension	dimension
AP_INVOICE_DISTRIBUTIONS_ALL	dimension	dimension
POZ_SUPPLIERS	dimension	dimension
GL_CODE_COMBINATIONS	dimension	dimension
Open Balance	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.

Payables 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
AP_INVOICES_ALL	63	15
AP_PAYMENT_SCHEDULES_ALL	21	2
AP_INVOICE_DISTRIBUTIONS_ALL	59	11
POZ_SUPPLIERS	145	75
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.