GeneralLedger.Item_Codes_Balance

by

Purpose

This view supports financial control and transparency by standardising balance-level data for consistent reporting and reconciliation. It helps finance and business leaders understand balance movements, inter-company positions, and the relationship between projects, programmes, and cost centres.

Use Case

  • Owner: Soon Tan
  • Typical Use Cases:
    • Support period close and reconciliation – Analyse balances by period and ensure smooth roll-forward between accounting cycles.
    • Drill into ledger structure – Break down balances by posting combination and dimensional code for detailed validation.
    • Provide portfolio-level visibility – Report balances by PRU, programme, project, or inter-company relationship to identify trends and variances.
    • Link with companion views – Join with the Balance (Values) view to include numeric measures for full financial reporting.
  • Style: Each row = one coded attribute of a ledger item. Grain is set by OBJECT_SEQ (company + ledger) and ITEM_KEY (posting combination). The item is typed by ITEM_TYPE (DATES, KEY_CODE, JOURNAL).

Suggested dashboard insights:

  • Balance Sheet vs Profit & Loss distribution by financial area (FA Sheet).
  • Period balance trends by PRU, Programme, or Project.
  • Inter-company balance positions and reconciliation variances.

Source

  • Primary Source(s): ifs.FACT_GL_BALANCE_OL
    (Example WP shortcode: — TBC)
  • Refresh cadence: Daily (TBC)
  • Load path: Bronze → Silver (aligned IFS fact + refs) → Gold/CDM (generalledger.item_codes_balance)

Integration with Dimension / Reference Tables

Joins used (per SQL): (none in this extract — raw codes emitted)

Common optional joins:

  • ifs.DIM_CODE_*_OL (B/C/D/E/F/G/H) for descriptions where needed — TBC
  • ifs.DIM_ACCOUNT_OL for account descriptions — TBC

Sample Exec

For sample data execution, use the code:

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

Response (Output Schema)

Columns emitted by final SELECT:

Identifiers

  • OBJECT_SEQ: json (varchar) — { "COMPANY": "<code>", "LEDGER_ID": "<ACCOUNT|POSTING_COMBINATION_ID_KEY>" }
  • ITEM_KEY: json (varchar) — { "POSTING_COMBINATION_ID": "<id>" }
  • ITEM_TYPE: varchar — one of: DATES | KEY_CODE | JOURNAL

Attributes by ITEM_TYPE

  • DATES
    • ACC_PERIOD_FROM
  • KEY_CODE
    • Inter_Company (CODE_H), CURRENCY_CODE (CODE_G), ASSET_CODE (CODE_F), PROJECT_ID (CODE_D), PRU_CODE (CODE_E)
  • JOURNAL
    • ACCOUNTING_PERIOD_KEY, ACCOUNTING_YEAR_KEY, ACCOUNT_ID

Common fields

  • ATTRIBUTE: varchar — attribute name
  • CODE_VALUE: varchar — canonical attribute value
  • VALUE_METADATA: json (varchar) — typically { "CODE": "…", "DESC": "…" } (quotes sanitised to ^ where applicable)

Audit & Status

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

Keys & Uniqueness

  • Primary (logical) key: OBJECT_SEQ, ITEM_KEY, ITEM_TYPE, ATTRIBUTE, CODE_VALUE
  • Grain statement: One row per ledger balance item attribute.
  • De‑duplication rule(s): Prefer most recent CREATE_DATE; CHECKSUM for change detection.

Data Quality & Rules

  • Mandatory fields: OBJECT_SEQ, ITEM_KEY, ITEM_TYPE, ATTRIBUTE, CODE_VALUE
  • Validations:
    • LEDGER_ID = ACCOUNT|POSTING_COMBINATION_ID_KEY
    • POSTING_COMBINATION_ID present
    • Period keys consistent with ACC_PERIOD_FROM
  • Business rules applied:
    • Normalise values; optional descriptions added via DIM joins in downstream steps
  • Assumptions/limitations: No descriptions in this extract for codes without joins.

Companion View — Balance (Values)

  • Proposed view name: generalledger.item_values_balance (name TBC) from ifs.FACT_GL_BALANCE_OL.
  • In‑scope attributes (per SQL): CURR_CREDIT, CURR_DEBIT, CURR_AMOUNT, QTY, CREDIT, DEBIT, AMOUNT.
  • ITEM_TYPE: BALANCE with numeric values formatted to 3 dp in VALUE_METADATA.VALUE.
  • Zero suppression: rows where parsed numeric value = 0 are excluded.
  • Keys: same OBJECT_SEQ + ITEM_KEY composition as Item_Codes_Balance.

Dependencies

  • Upstream: ifs.FACT_GL_BALANCE_OL
  • Downstream: Finance datamarts, reconciliation dashboards, management packs, audit trails

SQL Code & Artefacts

BMT-DWH-DEV/GeneralLedger_Item_Codes_Balance.sql

Leave a Comment