GL Period Status Report
The open, closed, or future status of every accounting period by ledger, so the close team can confirm which periods accept posting and catch a ledger left open or closed out of sequence.
Sample build of the GL Period Status Report — reconciled, and rendered tool-neutral so it runs in Power BI, ThoughtSpot, or Tableau.
| Ledger | Period | Status | Period Year | Application |
|---|---|---|---|---|
| US Primary | APR-26 | Open | APR-26 | Sample |
| EU Primary | MAR-26 | Posted | MAR-26 | — |
| US Primary | FEB-26 | Validated | FEB-26 | Sample |
| UK Primary | JAN-26 | Open | JAN-26 | — |
| US Primary | DEC-25 | Paid | DEC-25 | Sample |
| US Primary | APR-26 | Open | APR-26 | Sample |
The report reads GL_PERIOD_STATUSES joined to GL_PERIODS and GL_LEDGERS, listing each period's status per application and ledger.
One ledger has two periods open at once — the prior period was not closed before the new one opened, which lets late entries post to a period that should be locked.
Close the prior period for that ledger once reconciliations are signed off; keeping a single period open at a time is the control you want during close.
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
- GL_LEDGERS
- GL_PERIOD_STATUSES
- GL_PERIODS
- FND_APPLICATION_VL
- FND_ID_FLEX_STRUCTURES_VL
Show / hide SQL
SELECT
FA.application_name,
GL.name ledgr_name,
CASE WHEN SUBSTR(GP.period_name,1,2) IN ('01','02','03','04','05','06','07','08','09','10','11','12')
THEN TO_CHAR(TO_DATE(SUBSTR(GP.period_name,1,2)||'-01-'||SUBSTR(GP.period_name,4,2),'MM-DD-YY'),'Mon-yy','NLS_DATE_LANGUAGE=English')
ELSE
GP.period_name END period_name ,
(CASE
WHEN GPS.closing_status='O' THEN 'Open'
WHEN GPS.closing_status='F' THEN 'Future Enterable'
else closing_status end) AS closing_status,
'Q'||GP.QUARTER_NUM||'-'||SUBSTR(TO_CHAR(GP.period_year),3,2) QUARTER_NUM,
CASE WHEN GP.adjustment_period_flag='Y' THEN 'ADJ'
WHEN GP.adjustment_period_flag='N' THEN ''
ELSE GP.adjustment_period_flag END adjustment_period_flag, /*BR6*/
GP.period_year,
GP.PERIOD_NUM
--GPS.closing_status
FROM
GL_LEDGERS GL,
GL_PERIOD_STATUSES GPS,
GL_PERIODS GP,
FND_APPLICATION_VL FA,
FND_ID_FLEX_STRUCTURES_VL FIFSV
WHERE 1=1
AND GL.ledger_id=GPS.ledger_id
AND GPS.period_name=GP.period_name
AND gl.period_set_name=GP.period_set_name
AND GPS.application_id=FA.application_id
AND UPPER(GL.LEDGER_CATEGORY_CODE) IN ('PRIMARY','SECONDARY','ALC')
AND UPPER(GL.NAME) NOT LIKE 'DO%NOT%'
AND UPPER(FA.application_name)='GENERAL LEDGER'
AND GPS.closing_status IN ('O','F') /*BR1*/
AND GL.CHART_OF_ACCOUNTS_ID=FIFSV.ID_FLEX_NUM(+)
--AND UPPER(FIFSV.ID_FLEX_STRUCTURE_NAME)<>'GLOBAL COA' /*BR4 COMMENTED THIS AS PER SONIA CONFIRMATION AS BR4 STATES THAT ITS SHOULD PULL ALL LEDGERS INCLUDING GLOBAL COA*/
ORDER BY
GL.name,
GP.period_year,
GP.PERIOD_NUMThe 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_PERIOD_STATUSES | dimension | dimension |
| GL_PERIODS | dimension | dimension |
| FND_APPLICATION_VL | dimension | dimension |
| FND_ID_FLEX_STRUCTURES_VL | dimension | dimension |
| Amount | 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_PERIOD_STATUSES | 11 | 1 |
| GL_PERIODS | Setup / configuration table — joined for reference, not exposed for analytics | |
| FND_APPLICATION_VL | 1 | 1 |
| FND_ID_FLEX_STRUCTURES_VL | Setup / configuration table — joined for reference, not exposed for analytics | |