dodil/erp-brick-gl
DODIL ERP brick: general ledger
src/brick_gl/application/reconcile.py
"""Reconciling a control account against the module that owns the open items behind it (GL-08, GL-31, GL-36, GL-39).
gl keeps no subledger: what is outstanding on an invoice is payments' answer (GET /settlement), on a bill purchasing's (GET
/bills/open), on an asset class fixed-assets' register (GET /assets/balances). The ledger side is derived from journal lines
attributed to a document (the line's against_document, else its journal's source document); the difference is shown per
document, each break with its fix. A reconciliation is stored (recon_id '<company>:<control account>:<as_at>') and a break
may be ACCEPTED with a reason; the close gates receivable_reconciled / payable_reconciled read it. gl never reads pay_, pur_ or
fa_ tables.
"""
import json
from datetime import date
from decimal import Decimal
from typing import Optional
from erp_platform.bricks import Refused
from sqlalchemy import select
from brick_gl.application.brick import (
GL,
RECONCILE_RUN,
company_row,
decide,
leading_ledger,
real_now,
repository,
today,
who,
)
from brick_gl.application.inputs import CompanyCode, IsoDate, Reason
from brick_gl.domain import dates
from brick_gl.domain.errors import RuleBroken
from brick_gl.domain.money import ZERO, convert
from brick_gl.infrastructure import seams
from brick_gl.infrastructure import tables as t
from brick_gl.infrastructure.repositories import _Keyed, utc
def _control_lines(repo, company: dict, subtype: str, as_at: date) -> list:
"""[{key, account_id, debit, credit, f_debit, f_credit, currency, fx_rate, source_document}] of journal lines on the control
accounts up to a day, in the leading ledger (common journals count), attributed to the document they belong to."""
j, ln, a = t.gl_journal.c, t.gl_journal_line.c, t.gl_account.c
lead = leading_ledger(repo)
q = (select(ln.journal_id, ln.account_id, ln.debit, ln.credit, ln.functional_debit, ln.functional_credit, ln.against_document,
j.source_document, j.source_module, j.currency, j.fx_rate, j.reverses_journal_id, j.ledger_id)
.select_from(t.gl_journal_line.join(t.gl_journal, j.journal_id == ln.journal_id).join(t.gl_account, a.account_id == ln.account_id))
.where(j.company_code == company["company_code"], j.status.in_(("posted", "reversed")), j.journal_date <= as_at, a.subtype == subtype,
(j.ledger_id.is_(None)) | (j.ledger_id == lead)))
rows = [dict(r._mapping) for r in repo.conn.execute(q)]
originals = {}
for r in rows:
if r["reverses_journal_id"] and r["reverses_journal_id"] not in originals:
o = repo.journals.get(r["reverses_journal_id"])
originals[r["reverses_journal_id"]] = o["source_document"] if o else r["source_document"]
out = []
for r in rows:
doc = r["against_document"] or (originals.get(r["reverses_journal_id"]) if r["reverses_journal_id"] else r["source_document"])
is_credit_side = (r["credit"] or ZERO) > 0 and (r["debit"] or ZERO) == 0
key = doc
if not r["against_document"] and r["source_module"] in ("payments", "pay") and is_credit_side == (subtype == "receivable"):
key = f"unattributed:{r['source_document']}"
out.append({**r, "key": key})
return out
def _persist(repo, company: dict, kind: str, account_id: Optional[int], as_at: date, gl_balance, outstanding, breaks: list) -> dict:
rid = f"{company['company_code']}:{account_id}:{as_at}"
row = {"recon_id": rid, "company_code": company["company_code"], "control_account_id": account_id, "as_at": as_at, "kind": kind,
"gl_balance": gl_balance, "outstanding_sum": outstanding, "variance": gl_balance - outstanding,
"breaks_json": json.dumps(breaks, default=str), "accepted_by": None, "accepted_reason": None}
K = _Keyed(repo.conn, t.gl_reconciliation, ("recon_id",))
old = K.get(rid)
if old and old["accepted_by"] and old["variance"] == row["variance"]:
row["accepted_by"], row["accepted_reason"] = old["accepted_by"], old["accepted_reason"]
K.put(row)
return row
def _view(row: dict) -> dict:
return {"recon_id": row["recon_id"], "kind": row["kind"], "as_at": row["as_at"], "gl_balance": row["gl_balance"],
"outstanding_sum": row["outstanding_sum"], "variance": row["variance"], "reconciled": row["variance"] == 0 or bool(row["accepted_by"]),
"accepted_by": row["accepted_by"], "accepted_reason": row["accepted_reason"],
"breaks": json.loads(row["breaks_json"] or "[]")}
def _reconcile_control(repo, caller, company: dict, subtype: str, as_at: date, seam, outstanding_of) -> dict:
kind = subtype
lines = _control_lines(repo, company, subtype, as_at)
accounts = sorted({r["account_id"] for r in lines})
sign = 1 if subtype == "receivable" else -1
docs: dict = {}
for r in lines:
d = docs.setdefault(r["key"], {"net": ZERO, "f_net": ZERO, "rate": None})
d["net"] += (r["debit"] or ZERO) - (r["credit"] or ZERO)
d["f_net"] += (r["functional_debit"] or ZERO) - (r["functional_credit"] or ZERO)
if r["fx_rate"] is not None and (r["debit"] or ZERO) * sign > 0:
d["rate"] = d["rate"] or r["fx_rate"]
gl_balance = sum((d["f_net"] for d in docs.values()), ZERO) * sign
keys = sorted(k for k in docs if not k.startswith("unattributed:"))
answer = outstanding_of(keys, as_at, who(caller))
breaks, outstanding_sum = [], ZERO
for k in sorted(docs):
d = docs[k]
if k.startswith("unattributed:"):
if d["net"] != 0:
breaks.append({"document": k, "kind": "unattributed", "ours": d["net"] * sign, "theirs": ZERO, "difference": d["net"] * sign,
"fix": "allocate the receipt to its invoice in payments (the ledger then follows it)" if subtype == "receivable"
else "post the payment against its bill"})
continue
theirs = answer.get(k)
ours = d["net"] * sign
if theirs is None:
if ours != 0:
breaks.append({"document": k, "kind": "posted_not_in_" + ("payments" if subtype == "receivable" else "purchasing"),
"ours": ours, "theirs": ZERO, "difference": ours,
"fix": "the open-item owner does not know this document: correct or reverse the journal"})
continue
outstanding_sum += convert(theirs, d["rate"] or Decimal(1)) if d["rate"] else theirs
if ours != theirs:
kind_ = "settled_not_posted" if ours > theirs else "amounts_differ"
breaks.append({"document": k, "kind": kind_, "ours": ours, "theirs": theirs, "difference": ours - theirs,
"fix": "post the missing receipt / credit (or allocate it with against_document)" if ours > theirs
else "check the journal amount against the document"})
for k, theirs in answer.items():
if k not in docs and theirs != 0:
breaks.append({"document": k, "kind": "open_not_posted", "ours": ZERO, "theirs": theirs, "difference": -theirs,
"fix": "post the document to the ledger (ar / purchasing retries pending postings)"})
outstanding_sum += theirs
row = _persist(repo, company, kind, accounts[0] if accounts else None, as_at, gl_balance, outstanding_sum, breaks)
return _view(row)
@GL.use_case(kind="command", route="POST /reconcile/receivable", permission=RECONCILE_RUN, idempotent=True)
def reconcile_receivable(caller: dict, as_at: Optional[IsoDate] = None, company_code: Optional[CompanyCode] = None) -> dict:
"""Reconcile the receivable control account against payments' open balances as at a date: the ledger balance, payments'
outstanding for the invoices posted, and the difference per invoice - posted but not in payments, settled in payments but
not posted (a receipt posted without against_document is listed as unattributed), amounts that differ - each break with its
fix. Stored (recon_id company:account:as_at); the close gate receivable_reconciled reads it. gl asks payments' GET /settlement
with the invoice ids (200 per call) and never reads pay_ tables."""
with repository() as repo, decide():
company = company_row(repo, caller, company_code)
on = dates.parse(as_at) if as_at else today(repo)
if not seams.PAYMENTS.installed:
raise RuleBroken("payments is not installed: the receivable control account has no open-item owner to reconcile against", status=409)
def outstanding_of(keys, day, actor):
if not keys:
return {}
a = seams.neighbours.settlement(keys, day, actor)
if not a.ok:
raise RuleBroken(f"payments did not answer ({a.status or 'unreachable'}): {a.reason}", status=503)
return {i["invoice_id"]: Decimal(str(i["outstanding"])) for i in a.data["invoices"]}
return _reconcile_control(repo, caller, company, "receivable", on, seams.PAYMENTS, outstanding_of)
@GL.use_case(kind="command", route="POST /reconcile/payable", permission=RECONCILE_RUN, idempotent=True)
def reconcile_payable(caller: dict, as_at: Optional[IsoDate] = None, company_code: Optional[CompanyCode] = None) -> dict:
"""With purchasing installed, reconcile the payables control account against purchasing's open bills (GET /bills/open) as at a
date, per bill (the bill id is the source_document of purchasing's journals): B-17 outstanding 116.00 on both sides is no
break; a bill open in purchasing but not posted, or posted at a different amount, is a break with its fix. gl never reads pur_
tables and serves no subledger-items route."""
with repository() as repo, decide():
company = company_row(repo, caller, company_code)
on = dates.parse(as_at) if as_at else today(repo)
if not seams.PURCHASING.installed:
raise RuleBroken("purchasing is not installed: the payables control account has no open-item owner to reconcile against", status=409)
def outstanding_of(keys, day, actor):
a = seams.neighbours.open_bills(day, actor)
if not a.ok:
raise RuleBroken(f"purchasing did not answer ({a.status or 'unreachable'}): {a.reason}", status=503)
return {b["bill_id"]: Decimal(str(b["outstanding"])) for b in a.data.get("items", [])}
return _reconcile_control(repo, caller, company, "payable", on, seams.PURCHASING, outstanding_of)
@GL.use_case(kind="command", route="POST /reconcile/assets", permission=RECONCILE_RUN, idempotent=True)
def reconcile_assets(caller: dict, as_at: Optional[IsoDate] = None, company_code: Optional[CompanyCode] = None) -> dict:
"""With fixed-assets installed, reconcile each asset account (cost and accumulated depreciation, per class) of every ledger of the
company against fixed-assets' register (GET /assets/balances) as at a date: each break per class and ledger with its fix. gl
never reads fa_ tables."""
with repository() as repo, decide():
company = company_row(repo, caller, company_code)
code = company["company_code"]
on = dates.parse(as_at) if as_at else today(repo)
if not seams.ASSETS.installed:
raise RuleBroken("fixed-assets is not installed: the asset accounts have no register to reconcile against", status=409)
year = dates.fiscal_year_of(on, int(company["financial_year_end_month"]))
breaks, gl_total, reg_total = [], ZERO, ZERO
for ledger_id in repo.company_ledgers.assigned(code, year):
a = seams.neighbours.asset_balances(code, ledger_id, on, who(caller))
if not a.ok:
raise RuleBroken(f"fixed-assets did not answer ({a.status or 'unreachable'}): {a.reason}", status=503)
for acct in a.data.get("accounts", []):
aid = int(acct["account_id"])
sums = repo.ledger.sums(code, ledger_id, upto=on, account_ids=[aid], group_by=("account_id",))
ours = (sums[0]["f_debit"] - sums[0]["f_credit"]) if sums else ZERO
theirs = Decimal(str(acct["balance"]))
if acct.get("role") == "accumulated_depreciation":
ours = -ours if ours < 0 else ours
gl_total += ours
reg_total += theirs
if ours != theirs:
breaks.append({"document": f"{ledger_id}:{acct.get('class_code')}:{acct.get('role')}", "kind": "amounts_differ", "ours": ours,
"theirs": theirs, "difference": ours - theirs,
"fix": "post the missing asset journal in fixed-assets, or correct the register"})
row = _persist(repo, company, "assets", None, on, gl_total, reg_total, breaks)
return _view(row)
@GL.use_case(kind="command", route="POST /reconcile/accept-difference", permission=RECONCILE_RUN)
def accept_reconciliation_difference(caller: dict, kind: Reason, as_at: IsoDate, reason: Reason,
company_code: Optional[CompanyCode] = None) -> dict:
"""Accept the remaining difference of a reconciliation (kind receivable, payable or assets) with a reason, recorded: the close gate
then reads it as reconciled. Accepting when nothing differs is refused."""
with repository() as repo, decide():
company = company_row(repo, caller, company_code)
on = dates.parse(as_at)
rows = repo.conn.execute(t.gl_reconciliation.select().where(
t.gl_reconciliation.c.company_code == company["company_code"], t.gl_reconciliation.c.kind == kind,
t.gl_reconciliation.c.as_at == on)).first()
if rows is None:
raise Refused(404, f"no {kind} reconciliation as at {on} (run it first)")
r = dict(rows._mapping)
if r["variance"] == 0 and not json.loads(r["breaks_json"] or "[]"):
raise RuleBroken("nothing differs: nothing to accept", status=409)
_Keyed(repo.conn, t.gl_reconciliation, ("recon_id",)).set((r["recon_id"],), accepted_by=who(caller), accepted_reason=reason)
return _view({**r, "accepted_by": who(caller), "accepted_reason": reason})
@GL.use_case(kind="query", route="GET /reconciliations", permission=RECONCILE_RUN)
def list_reconciliations(caller: dict, company_code: Optional[CompanyCode] = None) -> dict:
"""The reconciliations run for a company, newest first, with their breaks and whether each was accepted."""
with repository() as repo:
company = company_row(repo, caller, company_code)
rows = [dict(r._mapping) for r in repo.conn.execute(t.gl_reconciliation.select().where(
t.gl_reconciliation.c.company_code == company["company_code"]).order_by(t.gl_reconciliation.c.as_at.desc()))]
return {"reconciliations": [_view(r) for r in rows]}
_ = (utc, real_now)