# AccountReports Catalog One entry per report family. Each entry has four sections — read the one for your role, skip the rest: - **For End Users** — plain-language: what the report shows and when you'd run it. - **For Client Admin / ImpTeam** — what's configurable, and what's already wired vs. what still needs a menu/permission decision for a given client. - **For Developers** — routes, files, the SQL approach, and known gotchas. - **Status** — what's actually been verified, and how (not just "done"). All routes below are `POST` requests under the `AccountsSL` service (HRFinanceHost). All of them take a `Login` header, a JSON body carrying filter criteria, and `FirstNumber`/`MaxResult` query parameters for paging. --- ## 1. Account Register / Account Register Summary **For End Users**: The Register is the most basic accounting report — every voucher line posted against a set of accounts in a date range, in order, with running debit/credit columns. The Summary version rolls the same data up to one row per account (or account group / control account), showing total debit and credit for the period. Use Register when you need to see every individual transaction; use Summary when you just need the totals. **For Client Admin / ImpTeam**: Filterable by date range, account(s), account group, control account, voucher type/class, currency, cost center, business dimension (V1-V5), mode of payment, and document number/amount ranges. Detail supports bill-allocation, cost-center, instrument, addon, and TDS "child row" enrichment on request. **Known gap**: unlike every other report in this catalog, the legacy `MREPORT` row this is wired to is not recorded in a code comment — if you need the exact `REPORTID`/`MREPORTVIEW` row for a client, check `MREPORTVIEW`/`MREPORTVSFIELDS` live rather than assuming; this should be documented properly the next time this report is touched. **For Developers**: - Routes: `POST /AccountReports/AccountRegisterDetailReport`, `POST /AccountReports/AccountRegisterSummaryReport`. - BLL: `IAccountRegisterReportBLL.GetAccountRegisterDetailReport`, `IAccountRegisterSummaryBLL.GetAccountRegisterSummaryReport`. - QB: `AccountsDAL/Query/AccountReports/AccountReportsQB.cs`. - SQL shape: a plain `TVOUCHER`/`TVOUCHERDETAIL` join with flat `CASE WHEN DETAILTYPE=...` Debit/Credit columns — no running balance, no currency-basis axis (that's Ledger). Summary is a real `GROUP BY` on account/account-group/control-account, not just a different column layout. - Access control: `EnforceAsync` (this is a period-range report — `PeriodFromDate`/`PeriodToDate` are both mandatory). - Menu IDs are currently caller-supplied placeholders — `AccountReportAccessControlBLL`'s own code comment notes real `MMENU` entries for these reports don't exist yet. --- ## 2. Account Ledger / Account Ledger Summary (Statement of Account) **For End Users**: The Ledger is a per-account statement — opening balance, every transaction in date order, and a running balance after each one, exactly like a bank statement. The Summary version shows one row per account: opening balance, total debit, total credit, closing balance — no transaction-level detail. This is the report to use when someone asks "show me this customer's account" or "what's the closing balance for this period." **For Client Admin / ImpTeam**: Same filter surface as Register, plus two Ledger-specific options: **Currency Basis** (view amounts in the OU's base currency, the transaction's own currency, the customer's currency, or the account group's currency) and an optional **Report Currency** to convert everything into on the fly, using that date's exchange rate. If a request doesn't narrow the report to a specific account, account group, or control account, the system rejects it rather than running an unbounded "print the whole general ledger" query (capped at 200 distinct accounts) — if a client needs a true full-ledger export, use the account-group or control-account filter, not "no filter at all." Wired as three sibling views under the existing legacy "Statement of Account" `MREPORT` row (see [AccountReports_Reference.md](AccountReports_Reference.md) for the exact `REPORTID`). **For Developers**: - Routes: `POST /AccountReports/AccountLedgerReport` (Detail/Normal), `POST /AccountReports/AccountLedgerSummaryReport`. - BLL: `IAccountLedgerReportBLL` (also exposes `GetAccountLedgerDetailStream` for unbounded export), `IAccountLedgerSummaryBLL`. - QB: `AccountsDAL/Query/AccountReports/AccountLedgerReportsQB.cs`. - SQL shape: the running balance is a genuine window function (`SUM(...) OVER (PARTITION BY VD.VOUCHERACCOUNTID ORDER BY V.VOUCHERDATE, VD.SLNO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)`), added to a correlated `OUTER APPLY` opening-balance subquery (everything strictly before `PeriodFromDate`). **Not a CTE** — `IQueryExecutor. QueryPagedAsync` has only one overload and auto-wraps the caller's SQL as `SELECT COUNT(*) FROM () AS Total`, which breaks on a leading `;WITH`. This is the reason every report in this catalog avoids leading CTEs; see the Developer Playbook. - Summary computes Opening/Closing via a scalar correlated subquery instead of the window function, since window functions and `GROUP BY` don't mix in the same query. - Access control: `EnforceAsync` (period-range report). --- ## 3. Outstanding Detail / Outstanding Summary **For End Users**: Shows what's currently owed — to you (receivable) or by you (payable) — **as of any date you choose**, not just today. This matters for questions like "what did this customer owe us at month-end last quarter?" Detail shows one row per pending bill; Summary rolls it up by account, account group, control account, salesperson/in-charge, price category, route, or OU (your choice). **For Client Admin / ImpTeam**: Configurable: as-of date, which grouping to summarize by, whether to show receivable only / payable only / both, overdue-only / not-yet-due / all, and filters for in-charge, price category, and route. Wired as new sibling views under the existing legacy "Outstanding Detail" and "Outstanding Summary" `MREPORT` rows — both already had a rich pre-existing field catalog (105 field rows total) reused as-is. **For Developers**: - Routes: `POST /AccountReports/AccountOutstandingDetailReport`, `POST /AccountReports/AccountOutstandingSummaryReport`. - QB: `AccountsDAL/Query/AccountReports/AccountOutstandingReportsQB.cs`. - The core building block is the DB function **`FNPENDINGBILL(@AsOnDate, @OUIds, @InfoRequired)`** — a real, live SQL Server table function (identical to legacy) that sums `TBILLALLOCATION.ALLOCATEDAMOUNT` per bill as of any date. **Do not use `VPENDINGBILL`/ `VPENDINGBILLDETAIL`** for anything that needs to work "as of a past date" — those views are undated (always "as of right now"). `@InfoRequired`'s polarity is inverted from this codebase's own `IncludeInformational` flag — confirmed by live A/B testing, not by reading the function body; see the Reference doc. - `PB.BALANCE` sign convention: positive = receivable, negative = payable (confirmed against real settled data). - Access control: `EnforceForAsOnDateAsync` (as-of-date report, no period range). --- ## 4. Ageing Detail / Ageing Summary **For End Users**: The same outstanding-balance data as Outstanding, but broken into age buckets (how many days old is this unpaid balance) instead of one lump figure. Default buckets are 15/30/45/ 60/75 days plus an overflow bucket for anything older, but the number of buckets (1-9) and their day-boundaries are configurable per request, not hardcoded. Ageing is bucketed by **bill date** (when the bill was raised) — the separate "overdue" concept (has the due date passed) is a different, independent filter also available on this report. **For Client Admin / ImpTeam**: Same grouping/filter options as Outstanding Summary, minus "AccountGroup"/"OU" (Ageing's own field catalog doesn't have those dimensions) — Ageing's catalog calls the in-charge/price-category dimensions "Agent"/"Category" rather than "InCharge"/ "PriceCategory" (same underlying data, different display label, matched to what already existed). **For Developers**: - Routes: `POST /AccountReports/AccountAgeingDetailReport`, `POST /AccountReports/AccountAgeingSummaryReport`. - QB: `AccountsDAL/Query/AccountReports/AccountAgeingReportsQB.cs`. - Reuses `FNPENDINGBILL` for the balance and the DB function **`FNAGESLAB(@AsOnDate, @ForDate, @StartAge, @EndAge)`** (also a real, live, legacy-identical function) for bucket membership. The bucket SQL fragment (a variable number of `SUM(CASE dbo.FNAGESLAB(...))` expressions) is generated in C# based on the caller's requested bucket count — safe because every value in the generated text is a bound numeric parameter, never a string. - Access control: `EnforceForAsOnDateAsync`. --- ## 5. Settlement Register / Settlement Register With Rate **For End Users**: An audit trail — for every bill, every settlement event posted against it (advance, against, batch, etc.), in date order. This is the report to use when someone asks "how was this bill actually paid off" or "show me every settlement in this date range," as opposed to Outstanding/Ageing which only show the *current unsettled* balance. The "With Rate" variant adds the account's country, the transaction's currency, and both base- and transaction-currency amounts — use it when currency detail matters. **For Client Admin / ImpTeam**: Filterable by the settling voucher's own date range (not the original bill's date — a settlement posted last week shows up in "last week," even if the original bill is a year old), account, account group, control account, voucher-number/ID/amount range, voucher type, and a specific bill (via its allocation-line ID, for drilling into one bill's full history). Wired as new sibling views under the existing legacy "Settlement Register" and "Settlement Register With Rate" `MREPORT` rows — both already had a full field catalog (48 rows combined) reused as-is. **For Developers**: - Routes: `POST /AccountReports/AccountSettlementRegisterReport`, `POST /AccountReports/AccountSettlementRegisterWithRateReport`. - QB: `AccountsDAL/Query/AccountReports/AccountSettlementReportsQB.cs`. - Architecturally different from Outstanding/Ageing/Register/Ledger — a flat join (`TBILLALLOCATION` joined to itself via `ALLOCATIONLINEID`, plus the settling voucher), not an as-of-date balance calculation. Doesn't use `FNPENDINGBILL` at all. - Two fixed, non-configurable business rules are baked directly into the SQL (matching legacy): only auto/manual bill-allocation accounts, and only account-posted vouchers. - Access control: `EnforceAsync` (this one has a genuine period range, unlike Outstanding/Ageing). - Doesn't reuse the shared `AccountReportFilterBuilder.Build()` method — its own FROM-clause alias set (`C/A/TYP/B/VB/AV`) doesn't match the `V/VD/ACC/BTT` convention every other report in this catalog uses, so it has its own dedicated filter method (`BuildSettlementOnly`). See the Developer Playbook for when you need to do the same thing. --- ## 6. Collection/Payment Projection **For End Users**: For every account, this answers two questions: "at our historical rate, how many more weeks until we collect what's owed to us?" and, just as importantly, "how many more weeks until we'd pay off what we owe, at our historical payment rate?" — the second question ("projected outflow of funds") is new; the legacy version of this report only ever answered the first. It also flags any early-payment cash discount still available on a bill, for both what customers owe you and what you owe suppliers. **For Client Admin / ImpTeam**: As-of date, and an "aged balance" day-threshold used by one of the flag columns. This report is wired under the existing legacy **"Outstanding Analysis Report"** `MREPORT` row, found already live with one existing view and a 41-field catalog — this migration added 6 new field-catalog rows for the new payable-side and cash-discount columns (the first report in this whole wave that needed genuinely new field-catalog rows; every earlier one reused 100% of an existing catalog). **For Developers**: - Route: `POST /AccountReports/AccountCollectionProjectionReport`. - QB: `AccountsDAL/Query/AccountReports/AccountCollectionProjectionReportsQB.cs`. - Legacy's version (`GetOutstandingAnalysisQueryReport`) was a 15-step `#temp`-table pipeline, receivable-only. This is reimplemented as one flat query using `OUTER APPLY` + window functions (no temp tables, same no-leading-CTE constraint as Ledger) and extended to compute the payable side symmetrically (`QtrPayAvg`/`ExpectedWeeksToPay`, built on the Payment `BizTransactionClassId` the same way legacy built the receivable side on Receipt/Sales-Invoice classes). - Cash-discount benefit is ported from a *separate* legacy report ("Suggested Bill To Pay", payables-only) and generalized to receivables too, since `TMMHEAD.PAYMENTTERMSID` turns out to carry the payment term for both Sales and Purchase Invoice vouchers in this schema — confirmed live, not assumed from legacy's MM-module-centric naming. - One deliberate correctness fix vs. legacy: `ExpectedWeeks` now divides by a direction-pure receivable-only total, not legacy's own net (receivable-minus-payable) total, which understated exposure whenever an account carried offsetting payables. - Access control: `EnforceForAsOnDateAsync`. --- ## Verification status summary | Report | Live SQL validated (sqlcmd) | Deployed to GB5DEMO | HTTP-tested | FE click-through | Remarks/Description populated | |---|---|---|---|---|---| | Account Register / Summary | Yes | Yes | Yes | Not done | Unknown — reused pre-existing legacy `MREPORTVIEW` rows as-is, no new REMARKS text authored this session; not yet checked | | Account Ledger / Summary | Yes | Yes | Yes | Not done | Yes — authored in the seed migration | | Outstanding Detail / Summary | Yes | Yes | Yes | Not done | Yes — authored in the seed migration | | Ageing Detail / Summary | Yes | Yes | Yes | Not done | Yes — authored in the seed migration | | Settlement Register / With Rate | Yes | Yes | Yes | Not done | Yes — authored in the seed migration | | Collection/Payment Projection | Yes | Yes | Yes | Not done | Yes — authored in the seed migration | **"FE click-through: Not done"** is a real, current gap for every report in this catalog — every one of them has been verified by calling the backend endpoint directly (a real login session, real GB5DEMO data, checking the JSON response), but none has yet been clicked through in the actual GB5 ReportViewer screen in a browser. Since all of them are wired through the standard MetaReport/ReportView framework (the same one other GB5 reports already use in the FE), they *should* render without extra frontend work — but "should" isn't "verified," and this is the natural next step before calling any of these fully done from an end-to-end perspective. **"Remarks/Description populated"** now feeds a real FE affordance, not just documentation: as of the discoverability plan's MVP, `MREPORTVIEW.Remarks` is selected through to the FE and shown as a tooltip on the view-picker dropdown (`gbreportaction.component.ts`), and the same text is what a future report/menu-level duplicate-check (see the Developer Playbook) will score against. A report family marked "Unknown" here should have this checked and, if empty, filled in before its next touch — see the Developer Playbook's step 6 gate.