mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 09:50:01 +00:00
admin
2fd311c8a2
- Opening balances now post a single "Opening Balances" journal entry to the GL (migration applied to prod), scoped to managed companies (imported-GL associations already carry theirs). Triggers on opening_balances / opening_balances_setup keep it in sync; Ashley Manor backfilled. - Balance Sheet: read balances from the GL only (drop the separate opening add, which also double-counted imported companies). - Trial Balance: compute balances from journal_entry_lines as of the report date (was accounts.balance). - General Ledger report: read from journal_entry_lines (was transactions); opening rolls in from the GL. All four reports now share one source. Verified Ashley Manor TB balances (debits = credits = $90,073.23) with opening cash (BOA +$47,304.31) flowing through. Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
55 lines
2.8 KiB
PL/PgSQL
55 lines
2.8 KiB
PL/PgSQL
-- Post opening balances (accounting.opening_balances) to the general ledger as a
|
|
-- single "Opening Balances" journal entry, so they flow into every GL-based
|
|
-- report (Trial Balance, General Ledger, P&L, Balance Sheet) from one source.
|
|
--
|
|
-- Scoped by accounting.gl_managed(): only companies whose GL we generate (no
|
|
-- imported/foreign journal entries). Imported-GL companies (e.g. Bridgewater,
|
|
-- Bent Oak) already carry their opening balances inside the imported GL, so we
|
|
-- must NOT post theirs again. Keyed external_source='acmacc_opening',
|
|
-- external_id=company_id (one entry per company) — idempotent.
|
|
|
|
create or replace function accounting.post_opening_balance_gl(_company_id uuid) returns void
|
|
language plpgsql security definer set search_path to 'public','accounting' as $$
|
|
declare _dt date; _je uuid; _has boolean;
|
|
begin
|
|
perform accounting._gl_clear(_company_id, 'acmacc_opening', _company_id::text);
|
|
if not accounting.gl_managed(_company_id) then return; end if;
|
|
|
|
select exists (
|
|
select 1 from accounting.opening_balances
|
|
where company_id=_company_id and (coalesce(debit,0) <> 0 or coalesce(credit,0) <> 0)
|
|
) into _has;
|
|
if not _has then return; end if;
|
|
|
|
select as_of_date into _dt from accounting.opening_balances_setup where company_id=_company_id;
|
|
_dt := coalesce(_dt, date_trunc('year', now())::date);
|
|
|
|
insert into accounting.journal_entries (company_id, date, description, reference, external_source, external_id)
|
|
values (_company_id, _dt, 'Opening Balances', 'OPENING', 'acmacc_opening', _company_id::text)
|
|
returning id into _je;
|
|
|
|
insert into accounting.journal_entry_lines (journal_entry_id, account_id, debit, credit, description)
|
|
select _je, ob.account_id, coalesce(ob.debit,0), coalesce(ob.credit,0), 'Opening balance'
|
|
from accounting.opening_balances ob
|
|
where ob.company_id=_company_id and (coalesce(ob.debit,0) <> 0 or coalesce(ob.credit,0) <> 0);
|
|
end$$;
|
|
|
|
create or replace function accounting.tg_opening_balance_gl() returns trigger
|
|
language plpgsql security definer set search_path to 'public','accounting' as $$
|
|
begin
|
|
begin
|
|
perform accounting.post_opening_balance_gl(coalesce(new.company_id, old.company_id));
|
|
exception when others then
|
|
raise warning 'accounting: opening-balance GL post failed for %: %', coalesce(new.company_id, old.company_id), sqlerrm;
|
|
end;
|
|
return coalesce(new, old);
|
|
end$$;
|
|
|
|
drop trigger if exists trg_acct_opening_gl on accounting.opening_balances;
|
|
create trigger trg_acct_opening_gl after insert or update or delete on accounting.opening_balances
|
|
for each row execute function accounting.tg_opening_balance_gl();
|
|
|
|
drop trigger if exists trg_acct_opening_setup_gl on accounting.opening_balances_setup;
|
|
create trigger trg_acct_opening_setup_gl after insert or update on accounting.opening_balances_setup
|
|
for each row execute function accounting.tg_opening_balance_gl();
|