General Ledger Report for China Secondary Ledgers

General Ledger

The general ledger as kept in the Chinese-GAAP secondary ledger — balances and movements for the secondary ledger that mirrors the primary under local accounting standards, the basis for the China statutory accounts.

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 General Ledger Report for China Secondary Ledgers — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.

Sample build · illustrative

Filters

Currency Type Param

USD

Encumbrance Type Param

Standard

End Eff Period Num

FEB-26

Entered Currency Param

USD

Ledger Currency Param

USD

Ledger Id Param

US Primary

1,150

Secondary accounts

Periods open

$72K

Primary–secondary gap

Secondary Ledger	Account	Period	Opening	Net Movement	Closing
US Primary	1000-2100-000	APR-26	Sample	Sample	Sample
EU Primary	1000-5400-000	MAR-26	—	—	—
US Primary	1000-1410-000	FEB-26	Sample	Sample	Sample
UK Primary	2000-2100-000	JAN-26	—	—	—
US Primary	1000-6300-000	DEC-25	Sample	Sample	Sample
US Primary	1000-2100-000	APR-26	Sample	Sample	Sample

AI Analyst · active

reading

The report reads GL_BALANCES for the secondary ledger linked to the primary via ledger-set assignment.

flag

A $72K gap sits between primary and secondary on accounts where a local-GAAP adjustment didn't post — the secondary should equal the primary plus statutory adjustments.

root cause & next step

Post the missing local-GAAP adjustment journals to the secondary ledger; an unposted adjustment is why a secondary ledger drifts from the primary.

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 QRY.LEDGER_NAME,
       QRY.LEDGER_ID,
       QRY.BEGIN_DR,
       QRY.BEGIN_CR,
       QRY.BEGIN_BAL,
       QRY.PERIOD_DR,
       QRY.PERIOD_CR,
       QRY.END_DR,
       QRY.END_CR,
       QRY.END_BAL,
       QRY.BEGIN_ACC_DR,
       QRY.BEGIN_ACC_CR,
       QRY.BEGIN_ACC_BAL,
       QRY.PERIOD_ACC_DR,
       QRY.PERIOD_ACC_CR,
       QRY.END_ACC_DR,
       QRY.END_ACC_CR,
       QRY.END_ACC_BAL,
       QRY.BEGIN_BALANCE_DR_CR_SIGN,
       QRY.END_BALANCE_DR_CR_SIGN,
       QRY.PERIOD_NAME,
       QRY.CCID,
       QRY.PERIOD_NUM,
       QRY.PERIOD_YEAR,
       QRY.TEMPLATE_ID,
       QRY.ENCUMBRANCE_TYPE,
       QRY.BAL_FIELD,
       QRY.BAL_DESC,
       QRY.ACCT_FIELD,
       QRY.ACCT_DESC,
       QRY.FLEXFIELD,
       QRY.FLEXDESC,
       QRY.EFFECTIVE_PERIOD_NUM,
       QRY.COST_CENTER_VALUE,
       QRY.INTERCOMPANY_SEGMENT_VALUE,
       QRY.LEGAL_ENTITY_ID,
       QRY.LEGAL_ENTITY_NAME,
       QRY.LE_ADDRESS_LINE_1,
       QRY.LE_ADDRESS_LINE_2,
       QRY.LE_ADDRESS_LINE_3,
       QRY.LE_CITY,
       QRY.LE_POSTAL_CODE,
       QRY.LE_REGISTRATION_NUMBER,
       QRY.LE_ACTIVITY_CODE
    FROM
(WITH &P_SEG_DESC_WITH
   SELECT  l2.name LEDGER_NAME ,
         l2.ledger_id LEDGER_ID,
-- entered
DECODE(:CURRENCY_TYPE_PARAM, 'E', DECODE(bal.currency_code, :LEDGER_CURRENCY_PARAM, bal.begin_balance_dr_beq, bal.begin_balance_dr),
                            'T', null,
                            'S', bal.begin_balance_dr)
 BEGIN_DR,
DECODE(:CURRENCY_TYPE_PARAM, 'E', DECODE(bal.currency_code, :LEDGER_CURRENCY_PARAM, bal.begin_balance_cr_beq, bal.begin_balance_cr),
                            'T', null,
                            'S', bal.begin_balance_cr)
 BEGIN_CR,
DECODE(:CURRENCY_TYPE_PARAM, 'E', DECODE(bal.currency_code, :LEDGER_CURRENCY_PARAM, abs(bal.begin_balance_dr_beq - bal.begin_balance_cr_beq), abs(bal.begin_balance_dr - bal.begin_balance_cr)),
                            'T', null,
                            'S', abs(bal.begin_balance_dr - bal.begin_balance_cr))
 BEGIN_BAL,
DECODE(:CURRENCY_TYPE_PARAM, 'E', DECODE(bal.currency_code, :LEDGER_CURRENCY_PARAM, bal.period_net_dr_beq, bal.period_net_dr),
                            'T', null,
                            'S', bal.period_net_dr)
 PERIOD_DR,
DECODE(:CURRENCY_TYPE_PARAM, 'E', DECODE(bal.currency_code, :LEDGER_CURRENCY_PARAM, bal.period_net_cr_beq, bal.period_net_cr),
                            'T', null,
                            'S', bal.period_net_cr)
 PERIOD_CR,
DECODE(:CURRENCY_TYPE_PARAM, 'E', DECODE(bal.currency_code, :LEDGER_CURRENCY_PARAM, bal.begin_balance_dr_beq + bal.period_net_dr_beq, bal.begin_balance_dr + bal.period_net_dr),
                            'T', null,
                            'S', bal.begin_balance_dr + bal.period_net_dr)
 END_DR,
DECODE(:CURRENCY_TYPE_PARAM, 'E', DECODE(bal.currency_code, :LEDGER_CURRENCY_PARAM, bal.begin_balance_cr_beq + bal.period_net_cr_beq, bal.begin_balance_cr + bal.period_net_cr),
                            'T', null,
                            'S', bal.begin_balance_cr + bal.period_net_cr)
 END_CR,
DECODE(:CURRENCY_TYPE_PARAM, 'E', DECODE(bal.currency_code, :LEDGER_CURRENCY_PARAM, abs(bal.begin_balance_dr_beq + bal.period_net_dr_beq - bal.begin_balance_cr_beq - bal.period_net_cr_beq),
                                                                                  abs(bal.begin_balance_dr + bal.period_net_dr - bal.begin_balance_cr - bal.period_net_cr)),
                            'T', null,
                            'S', abs(bal.begin_balance_dr + bal.period_net_dr - bal.begin_balance_cr - bal.period_net_cr))
 END_BAL,
-- accounted
DECODE(:CURRENCY_TYPE_PARAM, 'E',bal.begin_balance_dr_beq,
                            'T', bal.begin_balance_dr,
                            'S', bal.begin_balance_dr)
 BEGIN_ACC_DR,
DECODE(:CURRENCY_TYPE_PARAM, 'E',bal.begin_balance_cr_beq,
                            'T', bal.begin_balance_cr,
                            'S', bal.begin_balance_cr)
 BEGIN_ACC_CR,
DECODE(:CURRENCY_TYPE_PARAM, 'E',abs(bal.begin_balance_dr_beq - bal.begin_balance_cr_beq),
                            'T', abs(bal.begin_balance_dr - bal.begin_balance_cr),
                            'S', abs(bal.begin_balance_dr - bal.begin_balance_cr))
 BEGIN_ACC_BAL,
DECODE(:CURRENCY_TYPE_PARAM, 'E',bal.period_net_dr_beq,
                            'T', bal.period_net_dr,
                            'S', bal.period_net_dr)
 PERIOD_ACC_DR,
DECODE(:CURRENCY_TYPE_PARAM, 'E',bal.period_net_cr_beq,
                            'T', bal.period_net_cr,
                            'S', bal.period_net_cr)
 PERIOD_ACC_CR,
DECODE(:CURRENCY_TYPE_PARAM, 'E',bal.begin_balance_dr_beq + bal.period_net_dr_beq,
                            'T', bal.begin_balance_dr + bal.period_net_dr,
                            'S', bal.begin_balance_dr + bal.period_net_dr)
 END_ACC_DR,
DECODE(:CURRENCY_TYPE_PARAM, 'E',bal.begin_balance_cr_beq + bal.period_net_cr_beq,
                            'T', bal.begin_balance_cr + bal.period_net_cr,
                            'S', bal.begin_balance_cr + bal.period_net_cr)
 END_ACC_CR,
DECODE(:CURRENCY_TYPE_PARAM, 'E',abs(bal.begin_balance_dr_beq + bal.period_net_dr_beq - bal.begin_balance_cr_beq - bal.period_net_cr_beq),
                            'T', abs(bal.begin_balance_dr + bal.period_net_dr - bal.begin_balance_cr - bal.period_net_cr),
                            'S', abs(bal.begin_balance_dr + bal.period_net_dr - bal.begin_balance_cr - bal.period_net_cr))
 END_ACC_BAL,
DECODE(substr(DECODE(:CURRENCY_TYPE_PARAM, 'E', DECODE(bal.currency_code, :LEDGER_CURRENCY_PARAM, bal.begin_balance_dr_beq - bal.begin_balance_cr_beq, bal.begin_balance_dr - bal.begin_balance_cr),
                            'T', bal.begin_balance_dr - bal.begin_balance_cr,
                            'S', bal.begin_balance_dr - bal.begin_balance_cr), 1, 1), '-' , GL_GLGENLED_XMLP_PKG.CR_SIGN, GL_GLGENLED_XMLP_PKG.DR_SIGN) BEGIN_BALANCE_DR_CR_SIGN,
DECODE(substr(DECODE(:CURRENCY_TYPE_PARAM, 'E', DECODE(bal.currency_code, :LEDGER_CURRENCY_PARAM, bal.begin_balance_dr_beq + bal.period_net_dr_beq - bal.begin_balance_cr_beq - bal.period_net_cr_beq,
                                                                                  bal.begin_balance_dr + bal.period_net_dr - bal.begin_balance_cr - bal.period_net_cr),
                            'T', bal.begin_balance_dr + bal.period_net_dr - bal.begin_balance_cr - bal.period_net_cr,
                            'S', bal.begin_balance_dr + bal.period_net_dr - bal.begin_balance_cr - bal.period_net_cr), 1, 1), '-' , GL_GLGENLED_XMLP_PKG.CR_SIGN, GL_GLGENLED_XMLP_PKG.DR_SIGN) END_BALANCE_DR_CR_SIGN,
                bal.period_name PERIOD_NAME,
                cc.code_combination_id CCID,
                bal.period_num  PERIOD_NUM,
                bal.period_year  PERIOD_YEAR,
                cc.template_id  TEMPLATE_ID,
                decode(:P_ACTUAL_FLAG, 'A', null, 'E', nvl(:ENCUMBRANCE_TYPE_PARAM,
                       gl_glgenled_xmlp_pkg.enc_type(bal.encumbrance_type_id))) ENCUMBRANCE_TYPE,
                &P_BALANCING_SEGMENT_VALUE BAL_FIELD,
--                &BALANCING_SEGMENT_VALUE BAL_FIELD_W_DEP,
                &FLEX_GL_BALANCING_DESC BAL_DESC,
                &P_ACCT_FIELD ACCT_FIELD,
--              &P_ACCT_FIELD ACCT_FIELD_W_DEP,
                &FLEX_GL_ACCOUNT_DESC ACCT_DESC,
                &P_FLEXFIELD FLEXFIELD,
                &P_FLEXDESC FLEXDESC,
                PS.EFFECTIVE_PERIOD_NUM,
                &SELECT_CC_SEG COST_CENTER_VALUE,
                &SELECT_IC_SEG INTERCOMPANY_SEGMENT_VALUE
                &SELECT_RPT
   FROM  GL_LEDGERS L,
            GL_LEDGER_SET_ASSIGNMENTS LS,
            GL_LEDGERS L2,
            GL_CODE_COMBINATIONS CC,
            GL_PERIOD_STATUSES PS,
            GL_BALANCES BAL			
            &SEG_DESC_FROM
            &P_ACCT_DESC_FROM
            &P_FROM_LNSV
WHERE l.ledger_id = :LEDGER_ID_PARAM
AND   ls.ledger_set_id(+) =l.ledger_id
AND   bal.ledger_id = l2.ledger_id
AND   l2.ledger_id = nvl(ls.ledger_id, l.ledger_id)
--AND   l2.currency_code = nvl(:ENTERED_CURRENCY_PARAM,:LEDGER_CURRENCY_PARAM) -- rrajarap bug 9524131
AND   l2.currency_code = :LEDGER_CURRENCY_PARAM
AND   cc.chart_of_accounts_id = :P_STRUCT_NUM --cc.chart_of_accounts_id
AND   ps.ledger_id = l2.ledger_id
AND   bal.code_combination_id = cc.code_combination_id
AND   bal.period_name = ps.period_name
AND   bal.actual_flag = :P_ACTUAL_FLAG
/*AND   bal.currency_code = decode(:CURRENCY_TYPE_PARAM, 'S','STAT',nvl(:ENTERED_CURRENCY_PARAM, :LEDGER_CURRENCY_PARAM))*/
AND  &WHERE_CURRENCY_BAL
AND  &WHERE_TRANSLATED_FLAG
&P_FLEX_SECURE

AND   ps.application_id = 101
/*AND   nvl(bal.translated_flag, 'X')= decode (:CURRENCY_TYPE_PARAM , 'E' , decode(:ENTERED_CURRENCY_PARAM,:LEDGER_CURRENCY_PARAM, 'X', 'R'), 'X')*//*rrajarap bug 11729713 */
AND   ps.effective_period_num
              BETWEEN :START_EFF_PERIOD_NUM
              AND :END_EFF_PERIOD_NUM
&P_GL_RPT_WHERE
&SEG_DESC_WHERE
&P_ACCT_DESC_WHERE
AND   &WHERE_ACTUAL_TYPE
-- &X_WHERE_EXPRESSION
 AND &LEX_FILTER_WHERE
 &P_PERIOD_DATE_WHERE
-- bug 11785547. Uncommented the following
 &WHERE_DAS
 &P_LE_WHERE
--/*ORDER BY LEDGER_NAME, &ORDERBY_BAL, &ORDERBY_BAL2,
--&ORDERBY_ACCT, &ORDERBY_ACCT2, &ORDERBY_ALL */
ORDER BY &P_ORDER_BY) QRY

:CURRENCY_TYPE_PARAM :ENCUMBRANCE_TYPE_PARAM :END_EFF_PERIOD_NUM :ENTERED_CURRENCY_PARAM :LEDGER_CURRENCY_PARAM :LEDGER_ID_PARAM :P_ACTUAL_FLAG :P_STRUCT_NUM :START_EFF_PERIOD_NUM

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_LEDGER_SET_ASSIGNMENTS	dimension	dimension
GL_CODE_COMBINATIONS	dimension	dimension
GL_PERIOD_STATUSES	dimension	dimension
GL_BALANCES	dimension	dimension
Net Movement	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_LEDGERS	10	104
GL_LEDGER_SET_ASSIGNMENTS	2	38
GL_CODE_COMBINATIONS	7	61
GL_PERIOD_STATUSES	11	1
GL_BALANCES	Setup / configuration table — joined for reference, not exposed for analytics

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.