R09801

-- ============================================================
-- Program: R09801 General Ledger Post — validate approved journal entry batches and post them to account balances
-- Purpose: General Ledger Post — validate approved journal entry batches and post them to account balances — auto-generated boilerplate from program-table-map
-- Grain  : F0902 × F0911, F0901, F0010 — 1:N joins yield one row per detail line
-- Tables : F0902, F0911, F0901, F0010
-- Notes  : Auto-generated skeleton. Julian dates are converted to DATE and implied-decimal amounts are scaled (verify decimals in F9210). Audit columns (…USER/…PID/…UPMJ) are omitted — see the quirks guide. Set catalog/schema to your Unity Catalog location; fill remaining placeholders (<...>) for your tenant.
-- ============================================================
SELECT
  h.GBAID AS "Account ID; joins to F0901 for the account's BU.Object.Subsidiary and attributes.",
  h.GBCTRY AS "Century component of the fiscal year key.",
  h.GBFY AS "Two-digit fiscal year of the balance row.",
  h.GBFQ AS "Fiscal quarter key column (obsolete but still part of the unique key; typically blank).",
  h.GBLT AS "Ledger type (AA actual, CA foreign, BA budget, etc.); always filter on it when aggregating.",
  h.GBSBL AS "Subledger value splitting balances below the account, blank when unused.",
  h.GBSBLT AS "Type of the subledger value (address, item, work order, etc.).",
  h.GBCRCD AS "Transaction currency of the balance row; part of the key when balances are kept by currency.",
  f.GLDCT AS "Document type of the journal entry (JE, PV, RI, etc.).",
  f.GLDOC AS "Document number of the journal entry.",
  f.GLKCO AS "Company that assigned the document number (key company).",
  CASE WHEN f.GLDGJ IS NULL OR f.GLDGJ = 0 THEN NULL ELSE DATE_ADD(MAKE_DATE(1900 + CAST(f.GLDGJ AS INT) DIV 1000, 1, 1), CAST(f.GLDGJ AS INT) % 1000 - 1) END AS "G/L date driving the fiscal period the line posts to.",  -- CYYDDD Julian → DATE
  f.GLJELN AS "Line number within the journal entry.",
  f.GLEXTL AS "Line extension code completing the unique key.",
  f00.CCCO AS "Company code; the key every financial table carries as CO or KCO."
FROM <catalog>.<schema_data>.f0902 h
LEFT JOIN <catalog>.<schema_data>.f0911 f
  ON f.GLLT = h.GBLT
LEFT JOIN <catalog>.<schema_data>.f0901 f0
  ON f0.GMAID = h.GBAID
LEFT JOIN <catalog>.<schema_data>.f0010 f00
  ON 1 = 1  -- TODO: define join condition (no shared aliases detected)
ORDER BY h.GBAID;