AR Collections Worklist 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: Standard run under 5 minutes; the payment-behaviour add-on (Step 4) costs an extra 12-month report extract plus an RFM pass, so a full run is 7+ minutes. Add it only when asked (Step 0).

Produce a ranked AR collections worklist in one Excel workbook. You own the orchestration, the priority scoring, the dunning tiering, and the drafted messages; the specialist skills do the extraction, the payment-behaviour scoring, and the dashboard build. Run steps in order, on separate turns, keeping every intermediate sheet so a collector can audit each account.

Requires: br-run-reports (extraction); br-data-dashboard (dashboard build — if it doesn't fire, build inline per Step 6). Add-on only (Step 4, when selected in Step 0): br-rfm-analysis (payment-behaviour segmentation — if it doesn't fire, score reliability inline).

Division of responsibility — READ FIRST

You do NOT run Blitz reports yourself. All extraction is performed by the br-run-reports skill, which owns add-in connection, authentication, asynchronous execution, completion polling, and deterministic cell population.

• Never call br_* add-in functions directly.
• Never rename the result sheet, read its values, sum a column, or start any analysis until extraction is confirmed complete — touching the workbook while the add-in's run is still in flight can stall it and the run will never finish.
• Your job is only: (1) ask br-run-reports to fetch the data, then (2) once it has fully landed, score, tier, draft, and present it.

This skill is read-and-recommend only. It does NOT send any dunning message, place a credit hold, or apply a receipt. It produces a prioritized worklist with drafted text and a recommended action per account; contacting the customer and any system change are separate, human-approved steps.

Tone caveat: the drafted dunning text is a starting point, not a sent message. Always show it for the collector to review and adjust before sending — wording, legal/late-fee language, and escalation thresholds are customer-policy-specific.

When to use

"Build me a collections worklist", "who should we chase first", "dunning priorities", "reduce DSO", "which overdue customers to call", "AR collections prioritization", "draft reminders for past-due accounts". For a plain aging report with no scoring, use br-run-reports directly.

Inputs (collect up front)

• Operating Unit (required) — AR Aging takes Reporting Level + Reporting Context. Reporting Level defaults to Ledger, so for an OU-scoped run set Reporting Level = Operating Unit and Reporting Context = <OU> (the same MOAC pattern as the other AR skills); ask if not supplied.
• As-of Date (default: today) — the aging snapshot date.
• Payment-behaviour scoring — the one deep-dive add-on; default No, see Step 0.
• Optional: Customer filter (scope to one account); minimum-balance threshold (default $50 — accounts below this drop off the worklist); a credit-limit check.

Reports used (authoritative names — do NOT broad-search for them)

These exact report names are known. When you ask br-run-reports to run one, pass the documented name directly — do NOT run a br_report_list_external discovery search first. Inspect parameters only if you need an exact LOV spelling. Fallback: only if a run returns #BLITZ_REPORT_NOT_FOUND do a name search. If a run returns a bare ERROR: ERROR, the param names didn't match — have br-run-reports inspect params (br_report_params_external) and adapt, then retry; don't abandon it.

Verified AR Aging output columns (r12, parsed): CUSTOMER, CUSTOMER_NUMBER, COLLECTOR, CREDIT_LIMITS, Billing/Invoice Number, Invoice Date, Due Date, Days Past Due Date, AMOUNTS_CURRENCY, ORIGINAL_AMOUNT, OUTSTANDING_AMOUNT, PAST_DUE_AMOUNT, plus the dynamic aging-bucket columns (named per the selected bucket set, e.g. Current, Week 5, Week 6 …). Use PAST_DUE_AMOUNT directly for the past-due figure and Days Past Due Date for severity — do NOT key on the bucket-column names (they change with the bucket set).

CONFIG — edit to match the customer; logic below reads from here

Aging-severity weight (per open invoice — from Days Past Due Date, NOT bucket names)

past_due_$ per customer = SUM of PAST_DUE_AMOUNT (the report's own past-due column — don't re-derive it from buckets). weighted_exposure = Σ(invoice PAST_DUE_AMOUNT × its day-band weight) — old money counts more than money one day late. The aging-bucket columns are dynamically named (e.g. Current/Week 5/Week 6), so weight by the numeric Days Past Due Date, never by bucket name.

Payment-reliability segment (from AR Applied Receipts history; RFM-style)

Score each customer on how they pay — Recency (days since last receipt), Frequency (receipts in window), Monetary (total paid). Map to a reliability segment:

• Reliable Payer — pays regularly, recently → likely to pay on a reminder (chase, expect quick win)
• Slow Payer — pays but late/sporadically → firmer follow-up
• Erratic / At-risk — long recency, shrinking payments → escalate sooner, watch credit
• New — too little history → standard reminder, build the relationship
• Dormant — no receipts in the window but an open balance → escalate / verify the account is live If the history report isn't available, default every customer to Slow Payer and note that reliability scoring was skipped.

Priority score (ranks the worklist — chase big, old, collectible money first)

priority = weighted_exposure × reliability_multiplier, where the multiplier rewards collectibility: Reliable 1.0, Slow 0.9, New 0.8, Erratic/At-risk 0.7, Dormant 0.6. (A huge balance from a Dormant account still ranks high on raw exposure but is nudged below an equally-large Reliable balance that will actually pay on a call.) Sort the worklist by priority descending.

Dunning tier (by worst open bucket × reliability) → drafted message

Draft one message per worklisted customer at its tier, with the customer name, past-due $, worst bucket, and oldest-invoice date merged in.

Exclusions / adjustments (apply BEFORE scoring)

• Disputed invoices (from AR Disputed Invoice, joined by CUSTOMER_NUMBER + BILLING_NUMBER) → flag, do not dun; subtract their DISPUTE_AMOUNT / BALANCE_DUE from past_due_$ so a customer isn't chased for money they're formally disputing (keep on a side tab).
• Unapplied / on-account receipts and credit memos → net against the customer's open balance before scoring (don't chase a customer who has an unapplied payment covering the balance).
• Below minimum-balance threshold → drop from the worklist (keep on the full tab).
• Credit-hold flag (optional) — when past_due_$ > the customer's CREDIT_LIMITS (already in AR Aging — no snapshot run needed), set the credit-hold recommendation and force T3.

Output sheet names & tab order (left to right)

AR_Collections (dashboard, first), Worklist (ranked customers + drafted message), AR_Aging (the extracted base — its report name), [AR_Disputes], [AR_Receipts].

Pipeline

Step 0 — Analysis depth (one ask_user_question, before any extraction)

The Standard run — Steps 1–2 + 5–8 over AR Aging (+ AR Disputed Invoice) — always executes and already produces a correct, ranked, tiered worklist with drafted messages. Step 4 (payment-behaviour reliability) is the one deep-dive add-on: it costs an extra report extraction (AR Applied Receipts, 12 months) plus an RFM pass, and it only re-orders an already-valid worklist through the reliability multiplier. Never run it unasked.

Ask once, up front — nothing pre-selected:

"Standard worklist (aging severity, disputes excluded, dunning tiers, drafted messages) runs by default. Also score payment behaviour? It adds a 12-month receipt-history extract — slower — and re-ranks by how reliably each customer actually pays." → Yes — add reliability scoring / No — standard only

Skip the question when the request already implies it: "who actually pays", "reliable payers", "payment behaviour", "full analysis" → add Step 4; "quick worklist", "who do we chase", "just the aging" → Standard only. Never re-ask mid-pipeline; Step 7 offers it afterwards.

When Step 4 is skipped (the default): do not extract AR Applied Receipts, default every customer to Slow Payer (multiplier 0.9 — constant, so the ranking is by weighted_exposure alone, which is still a valid worklist), and label the dashboard "reliability scoring not run".

Step 1 — Extract (DELEGATE to br-run-reports — do not run it yourself)

Ask br-run-reports to run "AR Aging" with Reporting Level + Reporting Context = {OU} and As Of Date = {input}. Then (recommended) "AR Disputed Invoice" for the OU → AR_Disputes. Only when Step 0 selected the add-on, also run "AR Applied Receipts" for the OU + a 12-month Apply Date window → AR_Receipts. Pass each exact name directly; wait for each to fully complete before the next; do nothing mid-run.

• #BLITZ_NOT_CONNECTED → relay it, ask the user to connect via the Blitz Report ribbon, retry.
• Fallback: if the hand-off doesn't fire or the data never lands, tell the user to run "AR Aging" themselves for the OU + as-of date, then ask you to analyse it; resume at 1b.

Step 1b — Anchor (single execute_office_js call)

In one call: locate the columns (CUSTOMER, CUSTOMER_NUMBER, OUTSTANDING_AMOUNT, PAST_DUE_AMOUNT, Days Past Due Date, CREDIT_LIMITS), record the customer count, total open AR (SUM OUTSTANDING_AMOUNT), and total past-due $ (SUM PAST_DUE_AMOUNT) as the reconciliation anchors, and return them. If no past-due balances: report "no past-due AR for {OU} as of {date}" and stop with a clean verdict.

Step 2 — Apply exclusions + derive exposure (single execute_office_js call)

In one call: per customer, net out unapplied/on-account credits and subtract disputed amounts (joined from AR_Disputes on CUSTOMER_NUMBER + BILLING_NUMBER), compute past_due_$ (SUM PAST_DUE_AMOUNT), weighted_exposure (per the day-band weights), the max Days Past Due Date (severity), and the oldest-invoice date — writing all headers + seed row + autoFill in the same block (Rule 4). Drop below-threshold customers. Text-format only literal label cells, never the formula cells (Excel-correctness #1/#2). Track the ACTUAL adjustments you apply, as running totals, for the Step-8 reconciliation — disputes_subtracted (the sum of dispute amounts you genuinely subtracted from a past-due invoice, NOT the gross total of every dispute row) and onaccount_netted (the sum of on-account / credit-memo amounts you netted off past-due) — and blobs.setJSON them. Return the surviving customer count + total weighted exposure.

Step 3 — (no-op placeholder) (reserved)

Reliability scoring is Step 4; keep Steps 2 and 4 as separate single-call blocks rather than merging, so a missing receipt history (Step 4 skipped) doesn't force a re-run of Step 2.

Step 4 — Payment-behaviour reliability (OPTIONAL — only if selected in Step 0; single execute_office_js call) (delegate to br-rfm-analysis if it fires; else inline)

In one call: from AR_Receipts, aggregate per customer by CUSTOMER_NUMBER (last RECEIPT_DATE, receipt count, total RECEIPT_AMOUNT / AMOUNT_APPLIED_TO_TRX), compute RFM quintiles, assign the reliability segment per CONFIG, and write the segment + reliability_multiplier back onto the worklist customers. Return the segment counts. If AR_Receipts wasn't extracted — because Step 0 chose Standard, or the report isn't available — skip this step, default all to Slow Payer, and note it on the dashboard.

Step 5 — Score, tier, draft (single execute_office_js call)

In one call: compute priority = weighted_exposure × reliability_multiplier, sort descending, assign the dunning tier per CONFIG (forcing T3 when past_due_$ > CREDIT_LIMITS), set the credit-hold recommendation, draft the per-customer message (merge name / past-due $ / max days past due / oldest-invoice date), create Worklist, and write every row (customer, past-due $, weighted exposure, max days past due, reliability segment, priority, tier, recommended action, drafted message). Return the worklist count and total past_due_$ on it.

Step 6 — Dashboard (two execute_office_js calls: data then charts) (delegate to br-data-dashboard if it fires; else inline)

Call 1 — all data sections on a NEW AR_Collections sheet at position 0: KPI band (Total open AR, Total past-due $, # accounts to chase, $ past due 90+, Disputed excluded $). The "Disputed excluded $" KPI must be the disputes_subtracted actually removed from the worklist's past-due (from Step 2), NOT the gross total of every AR_Disputes row — most disputes are on settled / not-past-due invoices (zero Balance Due), so the real figure is often $0 even when there are hundreds of dispute rows. Showing the gross total here overstates what was filtered; if you want to surface the raw dispute volume, put it in a separate "(# dispute rows on file)" sub-label, not the excluded-$ KPI; a day-band Pareto (0 / 1-30 / 31-60 / 61-90 / 90+) computed at INVOICE grain — read Days Past Due Date+Past Due AmountfromAR_Aging and add each invoice's past-due into its own band. Do NOT bucket a whole customer by its single worst (max) day — that dumps the customer's entire balance into one band and leaves the others at $0, a misleading aging chart. top-N worklist (highest-priority customers with past-due $, max days past due, reliability, tier, recommended action); and a tier-distribution table (T1/T2/T3 counts and $). Every KPI is a formula referencing Worklist / AR_Aging (COUNTIFS/SUMIFS) so it refreshes on re-run. When Step 4 was skipped, label the reliability column "not scored" rather than showing a uniform "Slow Payer" that reads as a finding — and never point a KPI at AR_Receipts, which does not exist on a Standard run. Call 2 — charts only: a day-band bar chart and a tier-distribution bar chart.

• Build mechanics (house standard): build only on the new sheet; do not rename the report sheet (KPIs link to it). Do NOT use a multi-row copyToRange to fill worklist rows — write each formula explicitly. After building, scan beyond the dashboard range, clear stray cells, widen amount columns so values don't render as ######.

Step 7 — Narrative (single set_cell_range call)

Write all ≤6 bullets on AR_Collections in one call: total past-due $ and DSO context; the single biggest collectible account (customer + $ + tier); how much sits in 90+ and how many T3 accounts need escalation/credit-hold; how much was excluded as disputed (so the user trusts the filter); the recommended call order (top-priority customers first); and a one-line "what changed vs the obvious 'sort by balance' list" — i.e. which mid-size accounts to call first for a quick DSO win (name the reliable ones only when Step 4 ran; otherwise the contrast is aging severity vs raw balance). Draw a reliability bullet only if Step 4 ran.

Then, in your reply (not on the sheet), if the add-on was skipped, offer it in one line — and say it needs no re-extract of the aging: e.g. "Want the worklist re-ranked by how reliably each customer actually pays? I can add that on this data — it pulls 12 months of receipt history."

Step 8 — Arrange tabs + final validation (single execute_office_js call + one read)

Reorder tabs to AR_Collections, Worklist, AR_Aging, [AR_Disputes], [AR_Receipts]; delete the blitz_ helper sheet; activate AR_Collections. Then the one permitted verification read: reconcile to the Step-1b total past-due anchor using the actual adjustments tracked in Step 2 — `worklisted past-due $ + disputes_subtracted + onaccount_netted

• below-threshold $ = anchor`. Do NOT add the gross total dispute amount (it includes disputes on non-past-due / non-worklisted invoices and overshoots the anchor), and do NOT omit onaccount_netted (it reduced the worklist) — those two omissions are what produce a phantom GAP. If it still doesn't tie, a derived column or join dropped rows: fix it and report the fix — NEVER end the run on an unreconciled GAP. This is the only explicit re-read outside Step 1b.

Validation (before declaring done)

• Reconcile: worklisted past-due $ + disputes_subtracted + onaccount_netted + below-threshold $ = the Step-1b total past-due anchor (use the ACTUAL adjustments tracked in Step 2, not the gross dispute total). A non-zero gap is a blocker — fix before declaring done; never ship an unreconciled GAP.
• Tier sanity: every T3 has max Days Past Due Date ≥ 61, a CREDIT_LIMITS breach, or an Erratic/Dormant segment; no not-yet-due-only account is on the worklist.
• Exclusion sanity: spot-check 2–3 disputed invoices are flagged/excluded, and 2–3 top worklist rows by reading their values.
• No ###### and no stray cells outside the dashboard range.

Re-run / refresh

Two different requests — do not confuse them, and never re-extract when you don't have to:

• "Now add reliability scoring" / "who actually pays" (same aging) — AR_Aging and AR_Disputes are already on the sheet. Do NOT re-run them. Confirm AR_Aging still exists and its customer count + past-due anchor are unchanged, extract only AR Applied Receipts, then run Step 4 and re-run Steps 5–8 so the multiplier re-ranks the worklist and the dashboard/tabs pick up AR_Receipts. Skip Step 0 — the request names the add-on.
• "As of month-end" / "just OU X" / "rebuild" (new data) — re-drive from Step 1 with the new scope, rebuilding the same set of sheets the previous run produced (don't silently drop or add the add-on).

Closed-loop (roadmap, not executed here)

Applying an unidentified/unapplied receipt is deliverable via a Blitz Upload; setting a customer credit hold is a customer-profile update (upload); sending the dunning message is an external step. This skill prioritizes and drafts; a human acts.