GeneralLedger.Meta_Codes_Details

by

Purpose

This view provides a consistent and detailed picture of General Ledger activity across the organisation. By standardising ledger attributes and dimensional codes, it enables reliable analysis of financial movements, balances, and reconciliations — all within a single, governed structure.

This view supports finance and audit teams in maintaining clarity and confidence in financial data. It provides the foundation for drill-through analysis, reconciliations, and performance insights across all ledger sources.

Use Case

  • Owner: Soon Tan
  • Typical Use Cases:
    • Streamline financial reconciliation and period close – Ensure consistent alignment between journals, balances, and reporting summaries.
    • Enable detailed drill-through – Trace summary values from the trial balance down to posting-level detail and dimensional codes.
    • Support cross-system lineage – Compare ledger entries between source systems (Set Analysis vs Balance views) to confirm completeness and accuracy.
    • Analyse activity by dimension – Filter and aggregate GL activity by cost centre, programme, PRU, asset, inter-company, or project to reveal key financial patterns.
  • Style: Each row represents one attribute of a ledger key (LEDGER_ID = ACCOUNT|POSTING_COMBINATION_ID_KEY) for a given company, typed by META_TYPE (JOURNAL, KEY_CODE). CODE_VALUE carries the canonical value; VALUE_METADATA holds compact JSON (CODE and optional DESC).

Suggested dashboard insights:

  • GL coverage by financial area (Balance Sheet, P&L, or Statistical Accounts).
  • Period movements by cost centre, programme, or PRU.
  • Inter-company posting and reconciliation trends.

Source

  • Primary Source(s):
    • ifs.FACT_GL_BAL_SET_ANALYS_OL (Set Analysis)
    • ifs.FACT_GL_BALANCE_OL (Balance)
    (Example WP shortcode: — TBC)
  • Refresh cadence: Daily (TBC)
  • Load path: Bronze → Silver (aligned IFS facts + code dims) → Gold/CDM (generalledger.meta_codes_details)

Integration with Dimension / Reference Tables

Joins used (per SQL):

  • ifs.DIM_CODE_B_OL → COST_CENTRE (CODE_B)
  • ifs.DIM_CODE_C_OL → PROGRAMME (CODE_C)
  • ifs.DIM_CODE_E_OL → PRU_CODE (CODE_E)
  • ifs.DIM_CODE_F_OL → ASSET_CODE (CODE_F)
  • ifs.DIM_CODE_H_OL → Inter_Company (CODE_H)

Descriptions are sourced from the respective DIM tables and placed in VALUE_METADATA.DESC where available.

Sample Exec

For sample data execution, use the code:

EXEC get.myView @dataMart = 'GeneralLedger', @viewName = 'Meta_Codes_Details', @token = '<myToken>', @version = '<202404>'

Response (Output Schema)

Columns emitted by get.myView:

Identifiers

  • OBJECT_SEQ: json (varchar) — canonical ledger key JSON: { "COMPANY_ID": "<code>", "LEDGER_ID": "<ACCOUNT|POSTING_COMBINATION_ID_KEY>" }.
  • META_TYPE: varchar — JOURNAL | KEY_CODE.

Meta Codes (attributes by META_TYPE)

  • JOURNAL (from both Set Analysis and Balance facts)
    • ACC_PERIOD_FROM
    • ACCOUNT_GROUP (Balance only)
    • ACCOUNT_ID
    • FA_SHEET (LOGICAL_ACCOUNT_TYPE mapped to Profit and Loss / Balance Sheet / Statistics)
    • POSTING_COMBINATION_ID_KEY
  • KEY_CODE (with DIM lookups for descriptions)
    • COST_CENTRE (CODE_B)
    • PROGRAMME (CODE_C)
    • PROJECT_ID (CODE_D)
    • PRU_CODE (CODE_E)
    • ASSET_CODE (CODE_F)
    • CURRENCY_CODE (CODE_G)
    • Inter_Company (CODE_H)

Common fields

  • CODE_VALUE: varchar — canonical attribute value.
  • VALUE_METADATA: json (varchar) — { "CODE": "…", "DESC": "…" } where applicable; single quotes are sanitised to ^.

Audit & Status

  • CREATE_DATE: date — GETDATE() at load time
  • EXPIRY_DATE: date — '2099‑12‑31'
  • SOURCE_SYSTEM: varchar — e.g., {"Source":"ifs.FACT_GL_BALANCE_OL"} / {"Source":"ifs.FACT_GL_BAL_SET_ANALYS_OL"}
  • ACTIVE_FLAG: int — 1
  • CHECKSUM: nvarchar(32) — MD5 of (OBJECT_SEQ, ATTRIBUTE, CODE_VALUE)

Keys & Uniqueness

  • Primary (logical) key: OBJECT_SEQ, META_TYPE, ATTRIBUTE, CODE_VALUE
  • Grain statement: One row per ledger attribute for a given LEDGER_ID and COMPANY_ID.
  • De‑duplication rule(s): Prefer most recent CREATE_DATE; checksum for change detection.

Data Quality & Rules

  • Mandatory fields: OBJECT_SEQ, META_TYPE, ATTRIBUTE, CODE_VALUE
  • Validations:
    • FA_SHEET mapping from LOGICAL_ACCOUNT_TYPE — R|C → Profit and Loss, S → Statistics, else Balance Sheet
    • LEDGER_ID composition = ACCOUNT|POSTING_COMBINATION_ID_KEY (non‑blank parts)
    • Code values castable to expected types (periods, ids)
  • Business rules applied:
    • Descriptions sourced from DIM tables where available
    • Sanitise single quotes within DESC → ^
  • Assumptions/limitations:
    • Performance: In raw form, initial creation can take up to ~15 minutes; consider pre‑aggregation/GROUP BY or materialising hot subsets.

Security & Privacy

  • PII/Commercial sensitivity: Financial data — treat as Internal/Confidential.
  • Access control: Finance/reporting roles; consider row‑level filtering by COMPANY_ID.
  • Retention: Align with finance record retention (TBC).

Dependencies

  • Upstream: ifs.FACT_GL_BAL_SET_ANALYS_OL, ifs.FACT_GL_BALANCE_OL, ifs.DIM_CODE_B_OL, ifs.DIM_CODE_C_OL, ifs.DIM_CODE_E_OL, ifs.DIM_CODE_F_OL, ifs.DIM_CODE_H_OL
  • Downstream: Finance datamarts, reconciliation dashboards, statutory/management reporting, lineage and audits

SQL Code & Artefacts

BMT-DWH-DEV/GeneralLedger_Meta_Codes_Details.sql

Leave a Comment