Cash & Working-Capital Orchestrator Copy link

🚨 Blitz data = Excel worksheet UDFs, not MCP tools/connectors. Extract data only by writing =br_...() into a cell (via execute_office_js) and reading the spill — never tool_search for br_*, never refresh_mcp_connectors, and never conclude "no Oracle EBS / Blitz connector is set up" (the Blitz Report add-in is an Excel add-in, not an MCP connector, so it never appears in the connector list — its absence there is expected). Load the extraction mechanism first: read_skill("br-run-reports").

Performance: Read br-analytics-standards at the start of every run (read_skill("br-analytics-standards")). All pipeline steps must follow those rules. Target runtime: under 20 minutes (chained orchestration).

Assemble a single cash & working-capital scorecard across three levers. You drive the sequence, the cross-lever cross-check, and the master roll-up; each lever's own skill (or its primary report) does the detail. Run steps in order, keeping every lever's sheets.

Requires: br-run-reports, br-data-dashboard, and the lever skills br-ap-duplicate-payments, br-ar-collections-worklist, br-ap-payment-optimizer.

Division of responsibility — READ FIRST

• You do NOT run Blitz reports yourself — extraction is always via br-run-reports (directly, or inside the lever skills). Never call br_* functions; never touch a result sheet until its run is confirmed complete.
• Delegate one lever at a time and wait for it to finish before starting the next. The runs are asynchronous; overlapping them stalls them.
• Robust hand-off: invoke each lever skill to produce its sheets. If a lever skill does not fire (composition can be unreliable), fall back to running that lever's primary report directly via br-run-reports and scoring it inline with the same rules. Never skip a lever silently — show it as "not assessed" if neither path worked.
• Read-and-recommend only: nothing here executes a fix (no hold, no supplier claim, no payment batch, no dunning send, no credit hold). It quantifies the opportunity and recommends the sequence; a human executes each step.

When to use

"Working-capital review", "where's our cash / how do we free up cash", "cash opportunities pack", "combined AP/AR cash position", a treasurer/controller cash review. For a single lever, use that lever's skill directly.

Inputs (collect up front)

• Operating Unit (required) — used by all three levers. (AR Aging takes Reporting Level + Reporting Context: set Reporting Level = Operating Unit, Reporting Context = the OU; AP reports take Operating Unit directly. The lever skills handle their own param shape.) Ledger optional.
• As-of Date (AR aging snapshot; default today).
• Pay Through Date (the AP pay-run horizon; default = end of the current month).
• Optional: Cash available (drives the optimizer's constrained pay-run AND the combined net-cash view); duplicate lookback (default 24 months); materiality thresholds; cost-of-cash rate (default 10%).

CONFIG

Lever → skill → primary report (fallback if the skill doesn't fire)

Each lever skill runs its own reports directly by name (no broad discovery search). If a lever skill doesn't fire and you run its fallback report yourself, pass the documented name + params below directly — do NOT br_report_list_external-search first; only fall back to a search on #BLITZ_REPORT_NOT_FOUND.

Cash-opportunity roll-up (the master metrics)

From each lever's own returned figures:

• Recover now = duplicate Recoverable $ (cash back from suppliers — true new cash).
• Protect now = duplicate Preventable $ (open duplicate invoices to hold before they pay).
• Capture this week = optimizer discount $ capturable (Capture-Discount total — true new cash).
• Collect = collections collectible past-due $ (the worklist's prioritized at-risk total — this is cash timing, not new cash; label it so).
• DPO headroom = optimizer Defer-To-Due $ (cash you can legitimately keep longer).
• Total cash opportunity (hard) = Recover now + Capture this week (the two unambiguous new-cash levers). Show Protect / Collect / DPO as separate timing/risk levers — do NOT add them into the hard number (they're prevention, timing, and headroom, not realized cash).

🚨 Cross-check (the orchestration centerpiece — only the orchestrator can do this)

Join the duplicate skill's Preventable pairs (open duplicate invoices) against the optimizer's recommended pay-run (Pay_Schedule) on INVOICE_ID — both AP Invoices and Lines and AP Cash Requirement expose it, so it's an exact match (do NOT join on supplier number: the pay-run carries the supplier as TRADING_PARTNER name, not a number; fall back to TRADING_PARTNER+INVOICE_NUM only if an INVOICE_ID is somehow missing). 🚨 Use the EXACT INVOICE_ID join — the duplicate lever's Preventable list MUST carry the real INVOICE_ID values (see br-ap-duplicate-payments Step 3: INVOICE_ID A/B, not a row index). If you find yourself joining on invoice number, you dropped the id upstream — go back and carry it. A pure INVOICE_NUM-only fallback is a false-positive magnet (a real bug seen in a run: invoice number "1" matched a junk pay-run row → a bogus RED match): if you ever must fall back, require TRADING_PARTNER+INVOICE_NUM and skip degenerate invoice numbers (length ≤ 2, all-zero, or pure "1"/"2") — never RED-flag on INVOICE_NUM alone. Canonical JS — COPY VERBATIM (ports of tools/skill_tests/skill_logic.py::cross_check_by_id and ::cross_check_plausible):

function crossCheckById(preventablePairs, payRun) {
  const ids = new Set(payRun.map(p => String(p.invoiceId)));   // exact INVOICE_ID join
  return preventablePairs.filter(pr =>
    ids.has(String(pr.invoiceIdA)) || ids.has(String(pr.invoiceIdB)));
}
// Gate the RED set on the PAY-RUN size, NOT the preventable count: preventable
// invoices are open, and open invoices are what the pay-run contains, so
// RED/preventable is ~100% (normal). > ~10% of the PAY-RUN = systematic -> "review".
const crossCheckPlausible = (redCount, payRunCount, maxRate = 0.10) =>
  !payRunCount ? true : (redCount / payRunCount) <= maxRate;

Any match = an open duplicate the pay-run is about to pay → flag it RED: pull it from the run and hold it. This is the single highest-value insight of the pack — a duplicate you'd otherwise pay this week and then have to claw back. Report each match with supplier, both invoice numbers, $, and the pay-by date it was scheduled for.

🚨 The cross-check inherits the duplicate lever's guards — it cannot launder a false positive into a headline. The Preventable list you join MUST already have the same-amount cluster guard applied (ERS / auto-invoicing groups of ≥3 demoted) and its $ rolled up by distinct invoice, not per pair. If you skip those upstream, the cross-check faithfully amplifies the noise: a live run reported "1,316 RED matches, $120.9M about to be paid twice" that was entirely ERS same-amount clusters (invoice numbers ERS-13578-224817 etc.), ~36% of the AP universe — the join key was right, the input was garbage. So: (1) the RED $ is the distinct-invoice sum, never the per-pair exposure added up; and (2) apply the plausibility gate against the RIGHT denominator — RED ÷ pay-run count, NOT RED ÷ preventable count. Preventable invoices are open, and open invoices are exactly what the pay-run contains, so nearly every preventable pair WILL appear in the pay-run (RED/preventable is ~90–100% and is normal, not a trigger). A live run mis-gated on RED/preventable = 84/89 = 94% and falsely declared "systematic" when RED/pay-run = 84/4061 = 2% was perfectly plausible. Use cross_check_plausible(redCount, payRunCount): only when RED exceeds ~10% of the pay-run is the duplicate lever surfacing a systematic pattern → present "high volume — likely ERS/recurring, review before holding", NOT a confident "$X about to be paid twice". A real about-to-pay-a-duplicate set is a handful of invoices, not thousands. (And if the cross-check still shows many ERS pairs, the duplicate lever skipped its ERS-vs-ERS guard upstream — fix it there, not with an ad-hoc split here.)

🚨 A "0 RED / CLEAN" result is only trustworthy if the two INVOICE_ID sets can actually join — validate the join key before reporting CLEAN. A live run reported a false "CLEAN" because the duplicate lever handed over worksheet row indices (3, 4, 5, …) instead of real INVOICE_IDs, while the pay-run carried real Oracle ids (242901, …) — different id spaces, so the join matched nothing by construction. Before trusting a zero/low result, sanity-check: Preventable invoices are OPEN (unpaid) — they are exactly what an AP Cash Requirement pay-run contains — so if you flagged N preventable invoices and ZERO appear in the pay-run, the join key is broken, NOT clean. Confirm the two id sets are the same kind of value (both large Oracle ids; ranges overlap) — if they're disjoint in magnitude (one set is small sequential integers ≈ the dup universe size, the other is large ids), report "cross-check could not run — INVOICE_ID mismatch, the duplicate lever must carry the real Invoice Id (its far-right column), not a row index", NOT "CLEAN".

Overall status

• Action needed — any cross-check RED match, OR hard cash opportunity above a material threshold.
• Opportunity — discounts/recoverables found but none colliding with the pay-run.
• Clean — no duplicates, no capturable discounts, no material past-due.

Output sheets & tab order (left to right)

Cash_Working_Capital (master, first), then AP_Dup_Recovery, AR_Collections, AP_Pay_Run (the lever dashboards), then each lever's detail sheets after them.

Pipeline

1. Levers, in sequence — DELEGATE one at a time, wait for each. a. br-ap-duplicate-payments (Operating Unit + lookback) → AP_Dup_Recovery + Dup_Candidates. Capture Recoverable $, Preventable $, and the open Preventable invoice list for the cross-check. b. br-ar-collections-worklist (OU + As-of Date) → AR_Collections + Worklist. Capture collectible past-due $ and the top accounts. c. br-ap-payment-optimizer (OU + Pay Through Date + cash cap) → AP_Pay_Run + Pay_Schedule. Capture discount $ capturable, Defer-To-Due $, recommended pay-run $, and the pay-run invoice list for the cross-check. On a no-fire, run that lever's fallback report and score inline with the same rules; capture the same figures from the values the sub-skill returned in its own final JS call — do not insert extra get_cell_ranges reads against a lever's sheet between invocations (br-analytics-standards Rule 8). Trust the sub-skill return values.
2. Cross-check (single execute_office_js call). This is a genuine barrier — it needs BOTH the Preventable list (1a) and the pay-run (1c). In one call: join them per the CONFIG rule and build the Cross_Check warning set (RED matches). Return the match count and $. (If either lever didn't produce its list, skip with a noted caveat — never fail the pack.)
3. Master scorecard (single execute_office_js call) (delegate to br-data-dashboard if it fires; else build inline). On a NEW Cash_Working_Capital sheet at position 0, in one call: the headline Total cash opportunity (hard) band; the per-lever rows (Recover / Protect / Capture / Collect / DPO headroom — each with $, the source lever, and the recommended action); the cross-check RED panel (the about-to-pay-a-duplicate matches, top of the actions); and, when a cash figure is available, a net-cash-by-week mini-view (AR collectible inflow vs the pay-run outflow across the horizon). Every KPI is a formula referencing the lever dashboards/details (COUNTIFS/SUMIFS) so it refreshes when a lever is re-run. Read the per-lever figures from the values each sub-skill returned in its final JS call — do not insert get_cell_ranges reads against the lever sheets here (Rule 8). Any charts go in a second execute_office_js call.
   • Build mechanics (house standard): build the master on a NEW sheet at position 0; leave every lever's own sheet untouched and do NOT rename it (the master links by name). Do NOT use a multi-row copyToRange to fill the per-lever rows (it can latch onto the wrong source box and duplicate headers) — write each row's formula explicitly. After building, scan beyond the dashboard's column range, clear stray cells, and widen amount columns so values don't render as ######.
4. Action narrative (single set_cell_range call). Write all ≤6 ranked bullets on Cash_Working_Capital in one call, ordered by speed-to-cash: (1) any cross-check RED — hold these before the pay-run; (2) claw back the biggest Recoverable duplicate; (3) capture this week's discounts ($ + the pay-by date); (4) chase the top collectible accounts; (5) the DPO headroom you can keep; (6) the single biggest data-quality fix (e.g. a duplicate vendor master). Do not call set_cell_range once per bullet.
5. Arrange tabs + final validation (single execute_office_js call + one read). Reorder tabs per CONFIG and make Cash_Working_Capital active — only after the master exists. Then the one permitted verification read: confirm each lever's $ on the master ties to that lever's own dashboard, and that every cross-check RED match exists on both Dup_Candidates and Pay_Schedule.

Validation

• Every lever appears on the master with its $ (or an explicit "not assessed").
• Per-lever figures on the master tie to that lever's own sheet.
• The hard total = Recover now + Capture this week only (Protect / Collect / DPO are shown separately, never folded into the hard number).
• Every cross-check RED match is present in both the Preventable list and the pay-run.

Closed-loop (roadmap, not executed here)

Each fix is a separate human-approved step: hold an open/preventable duplicate (Blitz Upload), raise a supplier credit/refund (manual), build the payment run (Payment Process Request), send dunning (external), set a credit hold (customer-profile upload). This orchestrator quantifies and sequences; a human executes.