China Account Analysis Detail Report

General Ledger

The detailed account analysis Chinese statutory reporting expects — every voucher line behind each general ledger account, with the counterparty, in the layout local auditors and the tax bureau look for.

Run note · BIP run High-volume GL extracts can exceed BI Publisher's online output limit and time out. Run it as a scheduled process (ESS) with output bursted to file or email rather than online preview, and bound it by ledger and period.

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

Sample build · illustrative

Filters

From Account

1000-1410-000

From Period

FEB-26

Ledger

US Primary

Legal Entity

Globex Holdings

Period Num

FEB-26

To Account

1000-1410-000

1,280

Accounts

36,400

Vouchers

$0 diff

Reconciled

Account	Voucher	Date	Counterparty	Debit	Credit	Balance
1000-2100-000	Sample	2026-04-30	Acme Industrial	$1,240,500.00	$1,240,500.00	$1,240,500.00
1000-5400-000	—	2026-03-31	Northwind Trading	$842,150.75	$842,150.75	$842,150.75
1000-1410-000	Sample	2026-02-28	Globex Holdings	$96,400.00	$96,400.00	$96,400.00
2000-2100-000	—	2026-01-31	Initech LLC	$1,005,233.10	$1,005,233.10	$1,005,233.10
1000-6300-000	Sample	2025-12-31	Umbrella Corp	$58,720.40	$58,720.40	$58,720.40
1000-2100-000	Sample	2026-04-30	Acme Industrial	$1,240,500.00	$1,240,500.00	$1,240,500.00

AI Analyst · active

reading

The report ties GL_JE_LINES to their vouchers per account and joins the supplier or customer as counterparty.

flag

Vouchers on the payables and receivables control accounts are missing a counterparty — Chinese audit expects every control-account voucher to be attributable to a party.

root cause & next step

Enforce counterparty capture on control-account entries; an unattributable control voucher is the first thing a local auditor queries.

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 BEGIN_BAL AS

(
	SELECT /*+ MATERIALIZE */
	
		gb.ledger_id
	,	gcc.segment1
	,	gcc.segment4
	,	GB.period_name
	,	SUM(NVL(GB.begin_balance_dr_beq, 0)) BEG_BAL_DR
	,	SUM(NVL(GB.begin_balance_cr_beq, 0)) BEG_BAL_CR 
	,	(SUM(NVL(GB.begin_balance_dr_beq, 0))-SUM(NVL(GB.begin_balance_cr_beq, 0))) BEG_BAL_TOTAL
	,	SUM(NVL(PERIOD_NET_DR_BEQ,0)) 		  period_dr
	,	SUM(NVL(PERIOD_NET_CR_BEQ,0))         period_cr
	,	SUM(NVL(begin_balance_dr_beq,0)+NVL(PERIOD_NET_DR_BEQ,0)) end_dr
	,	SUM(NVL(begin_balance_cr_beq,0))+SUM(NVL(PERIOD_NET_CR_BEQ,0)) end_cr

	FROM 
		gl_balances GB 
	,	gl_code_combinations gcc
	
	WHERE 
		1				=1
	AND GCC.code_combination_id =gb.code_combination_id
	
	GROUP BY 
	
		gb.ledger_id
	,	gcc.segment1
	,	gcc.segment4
	,	gb.period_name

)

, YTD_BALANCES AS
(
	SELECT /*+ MATERIALIZE */
			
			gb.ledger_id
		,	gcc.segment1
		,	gcc.segment4
		,	SUM(NVL(PERIOD_NET_DR_BEQ,0)) 		  YTD_DR
		,	SUM(NVL(PERIOD_NET_CR_BEQ,0))         YTD_CR
	
	FROM 
			gl_balances GB 
		,	gl_code_combinations gcc
		
	WHERE 
			1						=1
		AND GCC.code_combination_id =gb.code_combination_id
		AND GB.period_year			=:P_YEAR 
		AND GB.period_num 			<:P_PERIOD_NUM
		
	GROUP BY 
			gb.ledger_id
		,	gcc.segment1
		,	gcc.segment4

)

SELECT	 /*+parallel(12)*/
			GL_DATE
		,	ACCOUNTING_SEQUENCE_NUMBER
		,	JE_SOURCE_NAME
		,	JE_CATEGORY_NAME
		,	LINE_DESCRIPTION
		,	ENTERED_CURRENCY
		,	LEGAL_ENTITY
		,	ENTITY_DESCRIPTION
		,	SEGMENT4
		,	ACCOUNT_DESCRIPTION
		,	ACCOUNTING_PERIOD
		,	BEG_BAL_DR
		,	BEG_BAL_CR
		,	BEG_BAL_TOTAL
		,	PERIOD_DR
		,	PERIOD_CR
		,	END_DR
		,	END_CR
		,	END_BAL
		,	YTD_BALANCE
		,	NVL(YTD_DR,0) 		YTD_DR
		,	NVL(YTD_CR,0)		YTD_CR
		,	SUM(ACCOUNTED_DR)	ACCOUNTED_DR
		,	SUM(ACCOUNTED_CR)	ACCOUNTED_CR
		,	SUM(ENTERED_CR)		ENTERED_CR
		,	SUM(ENTERED_DR)		ENTERED_DR
		, 	abs(SUM(ENTERED_DR)-SUM(ENTERED_CR)) ENT_AMT
		,	TO_CHAR(SYSDATE,'MM-DD-YYYY') RUN_DATE
		,	SRC
FROM
(
	SELECT 
			GLL.NAME											LEDGER
		,	TO_CHAR(GJH.default_effective_date,'YYYY-MM-DD')	GL_DATE
		,	GJH.posting_acct_seq_value							ACCOUNTING_SEQUENCE_NUMBER		
		,	GJS.USER_JE_SOURCE_NAME 							JE_SOURCE_NAME				
		,	GJC.USER_JE_CATEGORY_NAME 						    JE_CATEGORY_NAME
		,	GJL.Je_Line_Num										
		,	CASE WHEN GJS.je_source_name='Payables' THEN
					(CASE WHEN GJC.je_category_name IN ('Debit Memos','Credit Memos','Purchase Invoices')
						   THEN (XAL.description||'-'||(SELECT DISTINCT vendor_name FROM poz_suppliers_v POZ WHERE XAL.party_id=POZ.vendor_id)||'-'||XTE.TRANSACTION_NUMBER)
					  WHEN GJC.je_category_name IN ('Payments','Reconciled Payments')
						   THEN (XTE.TRANSACTION_NUMBER||'-'||(SELECT DISTINCT ac.vendor_name FROM ap_checks_all ac WHERE XTE.SOURCE_ID_INT_1=AC.check_id)) END) 
				 WHEN GJS.je_source_name='Receivables' THEN 
					(CASE WHEN GJC.je_category_name IN ('Sales Invoices','Debit Memos','Credit Memos','Adjustment') 
								THEN ((SELECT DISTINCT party_name FROM hz_parties HP, hz_cust_accounts HCA WHERE XAL.party_id = HCA.cust_account_id AND HCA.party_id =HP.party_id)||'-'||XTE.TRANSACTION_NUMBER)
						  WHEN GJC.je_category_name IN ('Receipts','Misc Receipts')
								THEN (XTE.TRANSACTION_NUMBER||'-'||(SELECT DISTINCT party_name FROM hz_parties HP, hz_cust_accounts HCA WHERE XAL.party_id = HCA.cust_account_id AND HCA.party_id =HP.party_id)) END)
				 WHEN GJS.je_source_name='Assets' THEN GJC.USER_JE_CATEGORY_NAME 
				 WHEN GJS.je_source_name='Project Accounting' THEN XAL.description
				 WHEN GJS.je_source_name='Revaluation' THEN GJH.DESCRIPTION 
				 ELSE GJL.description   END  					LINE_DESCRIPTION
		,	GJL.CURRENCY_CODE									ENTERED_CURRENCY
		,	GCC.SEGMENT1										LEGAL_ENTITY
		,	GCC.SEGMENT4										SEGMENT4
		,	GLP.PERIOD_NAME										ACCOUNTING_PERIOD
		,	GJL.code_combination_id
        ,  	GLL.ledger_id
		,	NVL(NVL(XAL.ACCOUNTED_DR,GJL.ACCOUNTED_DR),0) 		ACCOUNTED_DR
		,	NVL(NVL(XAL.ACCOUNTED_CR,GJL.ACCOUNTED_CR),0) 		ACCOUNTED_CR
		,	NVL(NVL(XAL.ENTERED_DR,GJL.ENTERED_DR),0) 			ENTERED_DR
		,	NVL(NVL(XAL.ENTERED_CR,GJL.ENTERED_CR),0)		 	ENTERED_CR
		,	FT1.DESCRIPTION 									ENTITY_DESCRIPTION
		,	FT.DESCRIPTION 										ACCOUNT_DESCRIPTION
		,	BAL.BEG_BAL_DR
		,	BAL.BEG_BAL_CR
		,	BAL.BEG_BAL_TOTAL
		,	BAL.period_dr
		,	BAL.period_cr
		,	BAL.end_dr
		,	BAL.end_cr
		,	BAL.end_dr-BAL.end_cr										END_BAL
		,	BAL.beg_bal_dr+BAL.period_dr-BAL.beg_bal_cr-BAL.period_cr 	YTD_BALANCE
		,	YTD_BAL.YTD_DR
		,   YTD_BAL.YTD_CR
		,	GJS.je_source_name SRC
	FROM

			gl_ledgers					GLL
		,	gl_je_headers				GJH
		,	gl_je_lines					GJL
		,	gl_je_batches				GJB
		--,	fnd_lookup_values_tl		FLV
		,	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
		,	fnd_flex_values 			FFV1
		,	fnd_flex_values_tl			FT1
		,	gl_import_references 		GIR
		,	xla_ae_lines 				XAL
		,	xla_ae_headers			 	XAH
		,	xla_transaction_entities 	XTE
		,	begin_bal					BAL
		,	ytd_balances				YTD_BAL
		
	WHERE
		1									=1
		AND	GLL.ledger_id					=GJH.ledger_id
		AND	GJH.je_batch_id					=GJB.je_batch_id
		AND UPPER(GJB.status)				='P'
		AND GJH.je_header_id				=GJL.je_header_id
		AND GJH.je_source 					=GJS.je_source_name
		AND GJS.LANGUAGE					='ZHS'
		AND GJH.je_category 				=GJC.je_category_name
		AND GJC.LANGUAGE					='ZHS'
		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.flex_value_id 				=FT.flex_value_id
		AND FT.LANGUAGE						='ZHS'
		AND FFV.flex_value_set_id IN (SELECT FLEX_VALUE_SET_ID 
                                   FROM fnd_id_flex_segments 
                                   WHERE application_id = '101' 
                                   AND id_flex_code = 'GL#' 
                                   AND enabled_flag = 'Y' 
                                   AND application_column_name = 'SEGMENT4')
		AND GCC.segment1					=FFV1.flex_value
		AND FFV1.VALUE_CATEGORY	 			='ENTITY VALUE SET'
		AND	FFV1.ENABLED_FLAG				='Y'
		AND FFV1.flex_value_id 				=FT1.flex_value_id
		AND FT1.LANGUAGE					='ZHS'
		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 XAH.ledger_id 					=XTE.ledger_id(+)     
		AND GCC.segment1 					=BAL.segment1
		AND GCC.segment4					=BAL.segment4
		AND GJL.ledger_id 					=BAL.ledger_id
		AND GCC.segment1 					=YTD_BAL.segment1(+)
		AND GCC.segment4					=YTD_BAL.segment4(+)
		AND GJL.ledger_id 					=YTD_BAL.ledger_id(+)
		AND GJL.period_name 				=BAL.period_name
		AND (GLL.name IN (:P_LEDGER) OR  'All' IN (:P_LEDGER||'All'))
		AND ((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') IN (:P_LEGAL_ENTITY) OR  'All' IN (:P_LEGAL_ENTITY||'All'))
		AND CAST(GCC.segment4 AS NUMERIC) BETWEEN NVL(:P_FROM_ACCOUNT,CAST(GCC.segment4 AS NUMERIC)) AND NVL(:P_TO_ACCOUNT,CAST(GCC.segment4 AS NUMERIC))
		AND 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)
)

GROUP BY 

			GL_DATE
		,	ACCOUNTING_SEQUENCE_NUMBER
		,	JE_SOURCE_NAME
		,	JE_CATEGORY_NAME
		,	LINE_DESCRIPTION
		,	ENTERED_CURRENCY
		,	LEGAL_ENTITY
		,	ENTITY_DESCRIPTION
		,	SEGMENT4
		,	ACCOUNT_DESCRIPTION
		,	JE_SOURCE_NAME
		,	ACCOUNTING_PERIOD
		,	BEG_BAL_DR
		,	BEG_BAL_CR
		,	BEG_BAL_TOTAL
		,	period_dr
		,	period_cr
		,	END_DR
		,	END_CR
		,	END_BAL
		,	YTD_BALANCE
		,	YTD_DR
		,	YTD_CR
		,	SRC
ORDER BY
			LEGAL_ENTITY
		,	SEGMENT4
		,	ACCOUNTING_PERIOD
        ,   GL_DATE
        ,   ACCOUNTING_SEQUENCE_NUMBER

:P_FROM_ACCOUNT :P_FROM_PERIOD :P_LEDGER :P_LEGAL_ENTITY :P_PERIOD_NUM :P_TO_ACCOUNT :P_TO_PERIOD :P_YEAR

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
GL_JE_LINES	dimension	dimension
GL_JE_HEADERS	dimension	dimension
GL_CODE_COMBINATIONS	dimension	dimension
GL_LEDGERS	dimension	dimension
Debit	measure	measure
Credit	measure	measure
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.

General Ledger 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
GL_BALANCES	Setup / configuration table — joined for reference, not exposed for analytics
GL_JE_LINES	26	2
GL_JE_HEADERS	34	2
GL_CODE_COMBINATIONS	7	61
GL_LEDGERS	10	104

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.