#71 Bug: silver_gl_entries zeroes fiscal_year/period (actuals land in FY0/P0), breaking =EPM actuals

[Anonymous]

Originally created by: grynn-in

Summary

silver_gl_entries recomputes fiscal_year/fiscal_period from the account-entry line's accounting_date, which is empty in the D365 demo data. The result is fiscal_year=0, fiscal_period=0 for every GL row — even though the correct values (2024, periods 1–12) are present upstream. This makes gold_trial_balance land entirely in FY0/P0, so Excel actuals lookups by real year/period (=K.EPM(entity, 2024, 1, account)) return 0.

Where it breaks (traced layer by layer)

epm_staging.stg_d365_fo__gl_entries : fiscal_year=2024  (862 rows)   ✅ (from header FiscalCalendarYear)
epm_staging.stg_gl_entries           : fiscal_year=2024  (862 rows)   ✅
epm_silver.silver_gl_entries         : fiscal_year=0     (862 rows)   ❌  <-- recomputed here
epm_gold.gold_trial_balance          : all rows FY0 / P0

Root cause — models/silver/silver_gl_entries.sql:24-25

coalesce(fp.fiscal_year,   {{ extract_year('gae.accounting_date') }})  as fiscal_year,
coalesce(fp.fiscal_period, {{ extract_month('gae.accounting_date') }}) as fiscal_period,

with the calendar join at lines 59-61:

left join fiscal_dates as fp
  on gae.accounting_date = fp.calendar_date
  and fp.calendar_id = coalesce(efc.fiscal_calendar_id, 'Fiscal')

In the demo, the line-level gae.accounting_date is an empty string (Cannot parse date: value is too short: while converting '' to Date). So the calendar join misses (fp.* null) and the [#65] fallback extract_year('') returns 0. The model never falls back to the already-correct upstream fiscal_year (carried from the journal header's FiscalCalendarYear).

Relationship to [#65]

[#65] added the accounting_date fallback for 'calendar join misses', but it's insufficient when the line-level date itself is empty. Suggested fix: prefer the upstream fiscal_year/fiscal_period already on stg_gl_entries, or derive from the header accounting/posting date rather than the line's empty accounting_date.

Repro

# (requires the #60 workaround so gold_trial_balance builds at all)
dbt build --vars '{erp_sources: [d365_fo]}' --exclude path:models/staging/erpnext
clickhouse: SELECT fiscal_year, count() FROM epm_silver.silver_gl_entries GROUP BY fiscal_year;  -- 0: 862

Impact

Primary actuals fact unusable by period/year — the core Excel =EPM() actuals path returns 0 for any real fiscal period. Found smoke-testing a fresh one-click deploy on macOS (ClickHouse 24.8).

[Anonymous]

Ticket changed by: grynn-in

• status: open --> closed

[Anonymous]

Originally posted by: grynn-in

Closing — resolved on main. The FY0/P0 root cause is fixed by [#65] (if(fp.fiscal_year != 0, …), the ClickHouse LEFT-JOIN 0-fill fallback) plus the seed fix in [#76] (demo entities AMHQ/AMUS/AMDE → the Standard calendar that's actually loaded), so the calendar join now matches and actuals resolve to 2024 P1–12. Verified end-to-end: =K.EPM("AMHQ",2024,1,"1010") → 3045306. A regression guard for this exact class of bug was added in [#79].