GeneralLedger.Item_Codes_Transaction

by

Purpose

This view provides a clear and consistent way to analyse individual General Ledger transactions across the organisation. It breaks each posting down into structured attributes — such as journal, project, programme, or inter-company activity — so that financial movements can be explored, reconciled, and connected to operational detail.

Use Case

  • Owner: Soon Tan
  • Typical Use Cases:
    • Drill into financial transactions – Trace voucher or journal entries to their source using stable and consistent identifiers.
    • Maintain clear audit trails – Understand who, what, and where for every posting, supporting assurance and compliance processes.
    • Analyse movements by dimension – View General Ledger activity by PRU, Programme, or Project to identify trends or anomalies.
    • Balance financial views – Combine with companion Values and Dates views to build a complete, balanced financial picture.
  • Style: Each row = one coded attribute of a transaction item. Grain is controlled by OBJECT_SEQ (company + ledger) and ITEM_KEY (posting combination + sequence key). The item is typed by ITEM_TYPE (JOURNAL, KEY_CODE, TRANSACTION, PARTY, ACTIVITY).

Suggested dashboard insights:

  • Postings by PRU, Programme, or Project and financial period.
  • Inter-company activity by source system and voucher.
  • Activity-level utilisation and spend patterns across projects and sub-projects.

Source

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

Integration with Dimension / Reference Tables

Joins used (per SQL):

  • IFS.DIM_ACTIVITY_OL — activity id/no; maps CODE_D + PROJ_ACTIVITY_SEQ_NO
  • ifs.DIM_SUB_PROJECT_OL — parent sub‑project context

Common optional joins:

  • ifs.DIM_ACCOUNT_OL for account descriptions (TBC)
  • cdm.project_core / cdm.customer_core (TBC)

Sample Exec

For sample data execution, use the code:

EXEC get.myView @dataMart = 'GeneralLedger', @viewName = 'Item_Codes_Transaction', @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>" }
  • ITEM_KEY: json (varchar) — { "POSTING_COMBINATION_ID": "<id>", "SEQ_NO_KEY": "<YEAR|PERIOD|VOUCHER_NO|ROW_NO>" }
  • ITEM_TYPE: varchar — one of: JOURNAL | KEY_CODE | TRANSACTION | PARTY | ACTIVITY

Attributes by ITEM_TYPE

  • JOURNAL
    • ACCOUNT_ID, ACCOUNTING_YEAR_KEY, ACCOUNTING_PERIOD_KEY, VOUCHER_NO, ROW_NO, VOUCHER_TYPE, JOURNAL_NUMBER, VOUCHER_TEXT, VOUCHER_ROW_TEXT
  • KEY_CODE
    • ASSET_CODE (CODE_F), CURRENCY_CODE (CODE_G), Inter_Company (CODE_H), PRU_CODE (CODE_E), PROJECT_ID (CODE_D), PROGRAMME (CODE_C)
  • TRANSACTION
    • TRANSACTION_CODE, REFERENCE_SERIES, REFERENCE_NUMBER
  • PARTY
    • IDENTITY
  • ACTIVITY (via dimension joins)
    • ACTIVITY_SEQ (IFS.DIM_ACTIVITY_OL.id), ACTIVITY_NO, PARENT_SUB_PROJECT_ID (from IFS.DIM_SUB_PROJECT_OL)

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_TRANSACTION_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 transaction 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
    • Year/period/voucher/row compose a unique SEQ_NO_KEY
    • Codes present where expected; joins to activity/sub‑project succeed where keys exist
  • Business rules applied:
    • Normalise and carry minimal DESC where helpful in VALUE_METADATA
  • Assumptions/limitations: No descriptions for account and programme codes unless extra joins are added.

Security & Privacy

  • PII/Commercial sensitivity: Financial transactional data → Internal/Confidential.
  • Access control: Finance/reporting roles; optional row‑level security by COMPANY.
  • Retention: Align with finance record retention (TBC).

Companion Views (Values / Dates)

  • Values View:generalledger.item_values_transaction (name TBC) from ifs.FACT_GL_TRANSACTION_OL.
    • Attributes (per SQL): AMOUNT, DEBIT_AMOUNT, CREDIT_AMOUNT, QUANTITY, CURR_AMOUNT, CURR_DEBIT, CURR_CREDIT.
    • Emitted as ITEM_TYPE='VALUES' with numeric values formatted to 3 dp in VALUE_METADATA.VALUE.
    • Filter excludes zero values (TRY_PARSE(...) <> 0).
  • Dates View: generalledger.item_dates_transaction (placeholder — TBC) for accounting and posting dates if required.

SQL Code & Artefacts

BMT-DWH-DEV/GeneralLedger_Item_Codes_Transaction.sql

Leave a Comment