#167 fix(report): entity columns read only the ownership window (#161)

[Anonymous]

Originally created by: grynn-in

What changed

scripts/build_consolidation_report.py: fetch_entity_pnl and fetch_entity_bs now read only the entity-periods the group consolidates. Both add one predicate, built by the new ownership_window_filter(cfg):

(data_area_id, fiscal_year, fiscal_period) IN (
    SELECT data_area_id, fiscal_year, fiscal_period
    FROM epm_gold.gold_entity_ownership
    WHERE consolidation_group = '<group>' AND fiscal_year = <year>
      AND outside_ownership_window = 0
      AND has_complete_chain = 1
      AND consolidation_method NOT IN ('equity', 'none'))

• P&L: a quarter holds only the in-window periods' movements.
• BS: a quarter-end snapshot appears only when that quarter-end period is inside the window. The balance itself is still the entity's full cumulative balance.
• The NI plug and the Diagnostics sheet follow automatically, because they are computed from the fetched data.

New tests/test_consolidation_report.py tests three things:

• both fetches carry the window predicate;
• a drift guard asserts that gold_consolidated_trial_balance.sql still filters on consolidation_method not in ('equity', 'none') and has_complete_chain = 1;
• the quarter bucketing is unchanged.

Why this approach (and not reading gold_consolidated_trial_balance)

• Same rule as the consolidated column. The window comes from the same model (gold_entity_ownership) and uses the same predicates the consolidated model applies (gold_consolidated_trial_balance.sql:338-339). The equity/none exclusion is included because the consolidated model drops those periods too. has_complete_chain = 1 already implies outside_ownership_window = 0; both are written out for the reader.
• The BS needs cumulative balances. The consolidated trial balance holds only period movements, fanned out once per ancestor group. Rebuilding an entity's quarter-end balance from it would mean re-cumulating every prior year, and it would silently change the BS entity sheet's numbers for every entity, not just the mid-year ones.
• The entity sheets keep their source and sign convention. They still read gold_trial_balance / gold_balance_sheet in local currency. Only the periods shown change.
• Section discovery is deliberately not windowed. A balance-sheet account whose only movements predate the acquisition still carries a balance at in-window quarter-ends. Filtering discovery by period movements would drop it from the BS sheets.

Verification

• python3 -m py_compile on the script and the test: OK.
• pytest tests/test_consolidation_report.py tests/test_docs.py tests/test_stack_cleanup.py: 21 passed. CI runs no pytest; the new test skips without openpyxl.
• Live A/B on the running stack (no deploy):
• Throwaway data, created through the konsol app:
  • entity ZZOP (USD);
  • group ZZGRP (USD);
  • an Ownership Period at 100%, full, effective 2099-07-01 (P07);
  • FY2099 Trial Balance Submissions for P1–P12 with 1000×P on accounts 1010 and 4010.
• dbt was built from a copy of this branch's dbt_project (unchanged from main) with dbt run --select +gold_consolidated_trial_balance+ gold_balance_sheet --exclude gold_spread_budget+ gold_ic_eliminations gold_ic_reconciliation. It printed OK created for silver_gl_entries, silver_entity_currencies, gold_trial_balance, gold_balance_sheet, gold_entity_ownership, gold_consolidated_trial_balance and gold_fully_consolidated_tb. PASS=39 ERROR=0.
• gold_entity_ownership for ZZGRP: P1–P6 outside_ownership_window = 1, P7–P12 in window. The consolidated trial balance holds P7–P12 only.
• The report was run on the same ClickHouse, first with main's script and then with this branch's:

(4010 revenue, sheet values)	Q1	Q2	Q3	Q4
main — P&L Entity	6,000	15,000	24,000	33,000
main — P&L Consolidated (Entity layer)	0	0	24,000	33,000
branch — P&L Entity	0	0	24,000	33,000
branch — P&L Consolidated (Entity layer)	0	0	24,000	33,000

(1010 cash, BS Entity sheet)	Q1	Q2	Q3	Q4
main	-6,000	-21,000	-45,000	-78,000
branch	0	0	-45,000	-78,000

• The entity P&L minus the consolidated entity layer, per quarter: main {Q1: -6000, Q2: -15000, Q3: 0, Q4: 0}; branch all 0. The full report ran with rc=0 on both versions.
• Cleanup:
• Every ZZ record was deleted: TBS cancelled then deleted, Ownership Period cancelled then deleted, both group nodes, the entity, and the files.
• The raw ZZ rows were removed and reconcile_all() was run.
• Every Build Approval the test caused was cancelled.
• A second dbt run purged the gold tables. The ZZ row count is now 0 in gold_trial_balance, gold_balance_sheet, gold_entity_ownership, gold_consolidated_trial_balance, gold_fully_consolidated_tb and silver_entity_currencies.

Open questions (pre-existing, not changed here)

1. BS consolidated vs entity at acquisition. The report's consolidated BS (fetch_consolidated_bs) cumulates the consolidated trial balance's period movements from P1 of the year. For ZZOP at Q3 that gives 24,000 (the P7–P9 movements), while the entity's balance at P9 is 45,000. The report has no acquisition-date balance sheet or opening balance, so the BS entity and BS consolidated columns still differ for an acquired entity. Should an acquisition opening balance land in the consolidated layer (gold_acquisition_adjustments), or should the report cumulate differently?
2. The optional --include-epm / --include-delta sheets use K.EPM formulas over the unwindowed entity measures. For an out-of-window quarter they will now differ from the hardcoded sheet.
3. Not related to this change: the auto-approved staging scope builds triggered during the test failed on assert_spread_has_12_periods (4 results).

Closes [#161]

🤖 Generated with Claude Code

https://claude.ai/code/session_013WewQKFQgG7o2M3mUDPRR5

[Anonymous]

Ticket changed by: grynn-in

• status: open --> closed