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 — 1:N joins yield one row per detail line
-- Tables : F0902, F0911, F0901
-- 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.
-- Omitted: F0010 is cataloged for this program, but no verified join condition connects it to F0902, so it is left out of this skeleton. Add the join yourself using the key fields on each table's page.
-- ============================================================
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."
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
WHERE
  h.GBAID = '<AID>'
ORDER BY h.GBAID;