France GL Extract for ETAFI Report
The general-ledger extract that feeds France's ETAFI statutory filing — trial-balance and journal data mapped to the French chart of accounts (PCG), in the structure ETAFI needs to build the liasse fiscale.
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 France GL Extract for ETAFI Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.
| Pcg Account | Period | Opening | Debit | Credit | Closing |
|---|---|---|---|---|---|
| 1000-2100-000 | APR-26 | Sample | $1,240,500.00 | $1,240,500.00 | Sample |
| 1000-5400-000 | MAR-26 | — | $842,150.75 | $842,150.75 | — |
| 1000-1410-000 | FEB-26 | Sample | $96,400.00 | $96,400.00 | Sample |
| 2000-2100-000 | JAN-26 | — | $1,005,233.10 | $1,005,233.10 | — |
| 1000-6300-000 | DEC-25 | Sample | $58,720.40 | $58,720.40 | Sample |
| 1000-2100-000 | APR-26 | Sample | $1,240,500.00 | $1,240,500.00 | Sample |
The report maps GL balances and journals to the French PCG account structure ETAFI consumes.
Five active accounts have no PCG mapping — their balances won't carry into ETAFI, so the liasse fiscale would understate by that amount.
Complete the PCG mapping for the five accounts; an unmapped local account is the usual reason a French statutory extract doesn't tie to the ledger.
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
Show / hide SQL
WITH SECURITY_TBL AS
(
SELECT DISTINCT GL.LEDGER_ID
FROM
GL_LEDGERS GL,
GL_ACCESS_SETS GAS,
FUN_USER_ROLE_DATA_ASGNMNTS FURDA,
PER_ROLES_DN PRD,
PER_USER_ROLES PUR,
PER_USERS PU
WHERE
UPPER(GL.LEDGER_CATEGORY_CODE) <> 'NONE'
AND GAS.ACCESS_SET_ID=GL.IMPLICIT_ACCESS_SET_ID
AND (GAS.ACCESS_SET_ID =FURDA.ACCESS_SET_ID OR GL.LEDGER_ID=FURDA.LEDGER_ID)
AND UPPER(FURDA.role_name) in ('XXC_GL_INQUIRY_JOB','XXC_GENERAL_ACCOUNTANT_JOB','XXC_GENERAL_ACCOUNTING_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
CAST(LPAD(CAST(cc.segment4 AS VARCHAR(6)), 6, '0') AS NUMERIC) ACCOUNT_SEGMENT
,T4.DESCRIPTION ACCOUNT_SEGMENT_DESC
, nvl(SUM(gjl.accounted_dr),0)
Cummulative_Debit
, nvl(SUM(gjl.accounted_cr),0)
Cummulative_Credit
,NVL(SUM(gjl.accounted_dr),0)-NVL(SUM(gjl.accounted_cr),0) Ending_Balance
--,gjh.period_name
FROM
gl_je_headers gjh
,gl_je_batches gjb
,GL_JE_LINES gjl
,GL_LEDGERS gll
,gl_code_combinations cc
,gl_je_sources_tl gjst
,gl_je_categories_tl gjct
--,gl_daily_conversion_types gdct
--,gl_je_action_log gja
,fnd_flex_values_vl A1
,fnd_flex_values_vl A4
,fnd_flex_values_tl T4
, SECURITY_TBL ST
WHERE 1=1
AND ST.LEDGER_ID=GLL.LEDGER_ID
AND gjl.JE_HEADER_ID = gjh.JE_HEADER_ID
AND gjh.JE_BATCH_ID = gjb.JE_BATCH_ID
--AND (nvl(GJL.ACCOUNTED_DR,0)-nvl(GJL.ACCOUNTED_CR,0))!=0
AND gjl.code_combination_id = cc.code_combination_id
AND gll.ledger_id = gjl.ledger_id
AND gll.LEDGER_ID = gjh.LEDGER_ID
AND gjst.je_source_name = gjh.je_source
AND gjst.LANGUAGE = USERENV('LANG')
AND gjct.je_category_name = gjh.JE_CATEGORY
AND gjct.LANGUAGE = USERENV('LANG')
--AND gjl.currency_conversion_type = gdct.conversion_type(+)
AND gjb.status = 'P'
--AND gjh.status = 'P'
--AND gjl.currency_code!= 'STAT'
--AND gja.je_batch_id = gjb.je_batch_id
--AND gja.action_code = 'POSTED'
AND A1.flex_value = cc.segment1
AND A1.VALUE_CATEGORY = 'ENTITY VALUE SET'
AND A4.flex_value = cc.segment4
AND A4.VALUE_CATEGORY = 'FRANCE ACCOUNT VALUE SET'
and A4.FLEX_VALUE_ID=T4.FLEX_VALUE_ID
AND T4.LANGUAGE='F'
AND GLL.NAME LIKE UPPER('SL FRANCE EUR LOCAL%')
--and (gjh.period_name IN (:P_PERIOD) OR 'All' IN ('All'||:P_PERIOD))
AND gjl.EFFECTIVE_DATE BETWEEN (select distinct START_DATE
from GL_PERIODS gp
where gp.period_num=1
and gp.period_year= (select distinct period_year from gl_periods gp1 where gp1.period_name=:P_PERIOD and gp1.period_set_name=gp.period_set_name )
and gp.period_set_name=gll.period_set_name
)
AND (select distinct END_DATE from GL_PERIODS gp2 where gp2.period_name = :P_PERIOD and gp2.period_set_name=gll.period_set_name)
and (A1.DESCRIPTION IN (:P_ENTITY) OR 'All' IN ('All'||:P_ENTITY))
AND (CAST(cc.segment4 as numeric) BETWEEN nvl(:P_ACCOUNT_FROM,000001) AND nvl(:P_ACCOUNT_TO,999999))
and (gll.NAME IN (:P_LEDGER) OR 'All' IN ('All'||:P_LEDGER))
GROUP BY LPAD(CAST(cc.segment4 AS VARCHAR(6)), 6, '0')
,T4.DESCRIPTION
--,gjh.period_name
ORDER BY LPAD(CAST(cc.segment4 AS VARCHAR(6)), 6, '0')
,T4.DESCRIPTION
--,gjh.period_nameThe 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.
| Element | Type | Definition |
|---|---|---|
| GL_JE_HEADERS | dimension | dimension |
| GL_JE_LINES | dimension | dimension |
| GL_BALANCES | dimension | dimension |
| GL_CODE_COMBINATIONS | dimension | dimension |
| Debit | measure | measure |
| Credit | measure | measure |
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_JE_HEADERS | 34 | 2 |
| GL_JE_LINES | 26 | 2 |
| GL_BALANCES | Setup / configuration table — joined for reference, not exposed for analytics | |
| GL_CODE_COMBINATIONS | 7 | 61 |