mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 09:50:01 +00:00
admin
03286f865a
Per the Financial Reports Master Spec: - §1.5 A/R fix: invoice settlements now post to the GL (Dr Undeposited / Cr A/R from invoice.paid_amount); payments are the cash sub-ledger only and no longer separately credit A/R (avoids double-count). A/R control = open balance, so recon R7 passes for managed companies (Ashley Manor 39,248 -> 0). Bills already settled (R8 ok). Migration applied + backfilled managed companies. - §9/§10: add a "Reconciliation Checks" report that surfaces R1/R2/R7/R8 residuals (never plugged) so imbalances are visible — e.g. Bridgewater's imported GL is unbalanced (R1) and its sub-ledgers don't tie (R7/R8). Imported companies (Bridgewater/Bent Oak) left untouched per decision; their residuals now surface in the Reconciliation report. Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
72 lines
3.9 KiB
PL/PgSQL
72 lines
3.9 KiB
PL/PgSQL
-- §1.5 conformance: Accounts Receivable must be the OPEN balance (invoices net of
|
||
-- payments applied), not gross invoiced. Previously invoices debited A/R but only
|
||
-- payments_received credited it — so native invoices marked paid (paid_amount set,
|
||
-- no payment row) left A/R overstated (recon R7 failed).
|
||
--
|
||
-- Fix: settle A/R from invoice.paid_amount (the canonical "payments applied"):
|
||
-- invoice -> Dr A/R / Cr income (acmacc_inv)
|
||
-- invoice settled -> Dr Undeposited / Cr A/R (acmacc_invpay, = paid_amount)
|
||
-- Payments are the cash sub-ledger only; they no longer post a separate A/R credit
|
||
-- (that would double-count, since paid_amount already reflects applied payments).
|
||
-- Net A/R control = total invoiced − total paid = open balance. Bills already
|
||
-- settle (Dr A/P / Cr bank), so A/P was already correct.
|
||
|
||
create or replace function accounting.post_invoice_gl(_invoice_id uuid) returns void
|
||
language plpgsql security definer set search_path to 'public','accounting' as $$
|
||
declare i accounting.invoices%rowtype; _ar uuid; _inc uuid; _cash uuid; _je uuid;
|
||
begin
|
||
select * into i from accounting.invoices where id=_invoice_id;
|
||
if not found then return; end if;
|
||
perform accounting._gl_clear(i.company_id, 'acmacc_inv', i.id::text);
|
||
perform accounting._gl_clear(i.company_id, 'acmacc_invpay', i.id::text);
|
||
if not accounting.gl_managed(i.company_id) then return; end if;
|
||
if coalesce(i.total,0) = 0 or i.status = 'void' then return; end if;
|
||
|
||
_ar := accounting.coa_ar(i.company_id);
|
||
_inc := accounting.coa_income_for(i.company_id, coalesce(nullif(i.notes,''), i.number));
|
||
|
||
-- Billing: Dr A/R / Cr income (gross)
|
||
insert into accounting.journal_entries (company_id, date, description, reference, external_source, external_id)
|
||
values (i.company_id, i.issue_date, coalesce(nullif(i.notes,''), 'Invoice ' || i.number), i.number, 'acmacc_inv', i.id::text)
|
||
returning id into _je;
|
||
insert into accounting.journal_entry_lines (journal_entry_id, account_id, debit, credit, description) values
|
||
(_je, _ar, i.total, 0, 'Invoice ' || i.number),
|
||
(_je, _inc, 0, i.total, 'Invoice ' || i.number);
|
||
|
||
-- Settlement: Dr Undeposited / Cr A/R for the amount paid (open balance falls out)
|
||
if coalesce(i.paid_amount,0) > 0 then
|
||
_cash := accounting.coa_undeposited(i.company_id);
|
||
insert into accounting.journal_entries (company_id, date, description, reference, external_source, external_id)
|
||
values (i.company_id, coalesce(i.updated_at::date, i.issue_date), 'Payment on Invoice ' || i.number, i.number, 'acmacc_invpay', i.id::text)
|
||
returning id into _je;
|
||
insert into accounting.journal_entry_lines (journal_entry_id, account_id, debit, credit, description) values
|
||
(_je, _cash, i.paid_amount, 0, 'Payment on Invoice ' || i.number),
|
||
(_je, _ar, 0, i.paid_amount, 'Payment on Invoice ' || i.number);
|
||
end if;
|
||
end$$;
|
||
|
||
-- Payments are the cash/undeposited sub-ledger only; A/R settlement is posted from
|
||
-- invoice.paid_amount above. Drop any legacy payment JE to avoid double-crediting A/R.
|
||
create or replace function accounting.post_payment_gl(_payment_id uuid) returns void
|
||
language plpgsql security definer set search_path to 'public','accounting' as $$
|
||
declare p accounting.payments_received%rowtype;
|
||
begin
|
||
select * into p from accounting.payments_received where id=_payment_id;
|
||
if not found then return; end if;
|
||
perform accounting._gl_clear(p.company_id, 'acmacc_pay', p.id::text);
|
||
end$$;
|
||
|
||
-- Re-post managed companies: invoices gain the settlement leg; payments drop theirs.
|
||
do $$
|
||
declare r record;
|
||
begin
|
||
for r in select i.id from accounting.invoices i join accounting.companies c on c.id=i.company_id
|
||
where accounting.gl_managed(c.id) loop
|
||
perform accounting.post_invoice_gl(r.id);
|
||
end loop;
|
||
for r in select p.id from accounting.payments_received p join accounting.companies c on c.id=p.company_id
|
||
where accounting.gl_managed(c.id) loop
|
||
perform accounting.post_payment_gl(r.id);
|
||
end loop;
|
||
end$$;
|