AP Intelligence Dashboard Copy link

🚨 Blitz data comes from br_* WORKSHEET FUNCTIONS of the Blitz Report Excel add-in — write =br_...() into a cell and read the result. They are not a connector, plugin, agent action or MCP tool: never search for one, never conclude "no Oracle EBS data source is connected". #BLITZ_NOT_CONNECTED → ask the user to connect on the Blitz Report ribbon; #NAME? → add-in not loaded.

Self-contained: read no other skill before or during the run. Every step below is ONE workbook operation (in Claude for Excel one execute_office_js call; in Copilot one edit or read action). Every number on the dashboard is an Excel formula over the extracted data — you never compute values yourself and never write computed arrays. The only text you author is the AI commentary (Op 6).

Budget — 6 workbook operations, then one reply

No question before the reply (only exception: Operating Unit missing); no verification reads (the formulas are the verification); no sheets beyond the three named; no deep dives (hold reasons, supplier scoring, cash projection) unless asked afterwards — then route per Actions and follow-ups.

Inputs

Operating Unit (required — ask only if not given; several allowed as A,B). Nothing else: leave Include Unvalidated Invoices and Include Unapproved Invoices blank (blank = include them; ANY value, Yes or No, applies the exclusion), no Pay Through Date, no Template, so the run returns every unpaid invoice.

Op 1–3 — Extract

1. Get-or-create blitz_ (never activate it). In blitz_!A1 write =br_run_report_external("AP Cash Requirement","Operating Unit=<OU>") and read it back — expect BLITZ_RUNNING: .... Only on #BLITZ_REPORT_NOT_FOUND search with =br_report_list_external("cash requirement") and re-run with the exact name.
2. Overwrite blitz_!A1 with the text run triggered (a recalculated run formula starts a duplicate run), write =_blitz_run_status in blitz_!A3, read it.
3. While it says RUNNING (or #NAME? = not published yet): one quiet read per turn, nothing else touching the workbook — the add-in writes the result sheet in the background and any other edit collides with it. Stop at DONE: <n> rows on sheet '<name>' or ERROR: <message>; ERROR while a populated sheet exists means the data is good — note it, never re-run. Zero rows → "no unpaid invoices for <OU>", stop. Take the result sheet's NAME from the status string, never from a sheet listing.

Result sheet layout is fixed: row 1 banner, row 2 headers, row 3 onward data. One row per payment schedule (an instalment invoice appears once per instalment). Amounts are the report's Functional Currency Amount — ledger currency, net of any discount still available; an invoice with no exchange rate has a blank amount (counted, not summed). The report excludes scheduled-payment holds and supplier-site payment holds; invoice holds are included and carried in Holds Count.

Op 4 — AP_Raw

Rename the result sheet to AP_Raw. From row 2 locate by header text — compare case-insensitively with spaces and underscores equivalent (a build can render DAYS_TO_DUE): Invoice Num {in}, Trading Partner {sp}, Due Date {du}, Days To Due {dd}, Functional Currency Amount {fa}, Functional Currency Code {fc}, Approved {ap}, Holds Count {hc}, Invoice Status {is}; last data row {N}. Add three columns after the last header (header in row 2, formula in row 3, filled down to {N}) — substitute the letters:

Days To Due is negative when overdue. Approved is the translated Yes/No (approved, manually approved or approval not required = Yes) — hence the first-letter test, which also holds for No/Nein/Non. Holds Count is blank when the invoice has no open hold and can arrive as TEXT ("1"), so test blank, never >0. {last} = the last column letter after these three.

Apply the autofilter as sheet.autoFilter.apply(<the row-2 header range>) — a range has no autoFilter() method. It is cosmetic: if it fails, skip it and carry on.

Op 5 — AP_Detail then AP_Intelligence

Range shorthands below (all on AP_Raw, rows 3..{N}): fa = AP_Raw!{fa}3:{fa}{N}, likewise dd, af, oh, sp, du, db, in, hc. {x#} = column NUMBER of {x}.

AP_Detail — five blocks, one formula each

Block rows {r} = 1, 30, 59, 88, 117 (25 result rows each). Per block: A{r} title, row {r}+1 headers Invoice | Supplier | Due Date | Days To Due | Amount | Approved | Holds | Invoice Status, A{r+2} the formula. Columns picked are {in#},{sp#},{du#},{dd#},{fa#},{ap#},{hc#},{is#}: =IFERROR(TAKE(SORTBY(FILTER(CHOOSECOLS(AP_Raw!$A$3:${last}${N},{in#},{sp#},{du#},{dd#},{fa#},{ap#},{hc#},{is#}),<cond>),FILTER(fa,<cond>),-1),25),"None")

Formats per block (one call per contiguous range): C{r+2}:C{r+26} dd-mmm-yyyy, E{r+2}:E{r+26} #,##0.00; bold the header rows; widen columns. A date is a serial NUMBER — pass it through and let the format render it; never convert or "correct" one.

AP_Intelligence — create as the FIRST sheet

Fixed layout so the commentary and the reply can reference the cells. fmt(x) below stands for IF(ABS(x)>=1000000,TEXT(x/1000000,"#,##0.0")&"M",IF(ABS(x)>=1000,TEXT(x/1000,"#,##0.0")&"K",TEXT(x,"#,##0")))&" "&$H$1 — expand it inline wherever it appears.

• A1 AP Intelligence - <OU> (bold, large); H1 =AP_Raw!{fc}3 (ledger currency, feeds fmt); A2 ="Live from Oracle EBS via the AP Cash Requirement report | unpaid, uncancelled invoices | amounts in "&$H$1.
• KPI table — A10:E10 KPI | Amount | Invoices | Definition | AI commentary, then rows 11–15 (A = label, B = amount, C = count, D = definition, E left empty for Op 6):

Rows 16–20 stay empty: they are the slots for follow-up KPI questions.

• AI Intelligence Summary — A4 AI INTELLIGENCE SUMMARY (bold). Helper sentences in N5:N7 with their amounts in O5:O7, so the three actions rank themselves by value: N5 =IF(C11=0,"No unpaid invoice carries an open hold.","Review "&fmt(B11)&" on "&C11&" invoices with open holds - confirm each hold is still valid or release it; largest: "&IFERROR(AP_Detail!B3,"n/a")&" ("&IFERROR(fmt(AP_Detail!E3),"n/a")&")."), O5 =B11 N6 =IF(C13=0,"Nothing is overdue by more than 30 days.","Resolve "&fmt(B13)&" overdue by more than 30 days on "&C13&" invoices - oldest due "&IFERROR(TEXT(MINIFS(du,dd,"<-30"),"dd-mmm-yyyy"),"n/a")&", largest supplier "&IFERROR(AP_Detail!B32,"n/a")&"."), O6 =B13 N7 =IF(C15=0,"No unapproved invoice is due within 15 days or overdue.","Approve or reject "&fmt(B15)&" on "&C15&" unapproved invoices due within 15 days or already overdue - nothing is payable until approved."), O7 =B15 A5 ="1. "&INDEX(SORTBY($N$5:$N$7,$O$5:$O$7,-1),1), A6 ="2. "&INDEX(SORTBY($N$5:$N$7,$O$5:$O$7,-1),2), A7 ="3. "&INDEX(SORTBY($N$5:$N$7,$O$5:$O$7,-1),3). A8 =IF(ROWS(fa)-COUNT(fa)>0,ROWS(fa)-COUNT(fa)&" invoices have no exchange rate and are counted but not summed","").
• Due buckets — A23:D23 Due Bucket | Amount | Invoices | % of Total; A24 ={"Overdue 30+";"Overdue 1-30";"Due 0-7";"Due 8-15";"Due 16-30";"Due 31+"} (a formula, never typed labels); B24 =SUMIF(db,A24#,fa); C24 =COUNTIF(db,A24#); D24 =B24#/$B$12.
• Top suppliers — A32:D32 Supplier | Outstanding | Invoices | Overdue 30+; A33 =TAKE(SORTBY(UNIQUE(sp),SUMIF(sp,UNIQUE(sp),fa),-1),10); B33 =SUMIF(sp,A33#,fa); C33 =COUNTIF(sp,A33#); D33 =SUMIFS(fa,sp,A33#,dd,"<-30").
• Formats — one call per CONTIGUOUS range, never a comma-separated address: #,##0.00 on B11:B20, on B24:B29, on B33:B42, on D33:D42; #,##0 on C11:C20, on C24:C29, on C33:C42; 0.0% on D24:D29; wrap text on E11:E20. Bold rows 4, 10, 23, 32; column widths A 60, B 18, C 12, D 55, E 90. One clustered column chart on A23:B29 titled Outstanding by due bucket, placed right of the tables. Delete blitz_. Activate AP_Intelligence.
• End the operation by reading AP_Intelligence!A5:C15 and the top three rows of each detail block (AP_Detail!A3:H5, A32:H34, A61:H63, A90:H92, A119:H121) — the material for Op 6 and the reply.

Op 6 — AI commentary

Write E11:E15 in one block: two or three sentences per KPI in the user's language — what the number is, why it matters, and the concrete next step — naming the largest supplier or invoice from the detail rows you read. Say plainly when a KPI is zero (e.g. "no approved invoices fall due in the next seven days, so the near-term payment pipeline is empty"). Never restate the definition column. Quote amounts as they appear (already in the ledger currency); never invent a number you did not read.

Reply

Four to six lines in the user's language — report and sheet names are fine, function names, blitz_ and status names are never mentioned: the three ranked actions with their amounts, the total outstanding, the no-rate note if not empty, then the offers, chosen by what is non-zero:

• C11 > 0: "Want me to prepare the release of the invoice holds for your review? I'll pre-fill the release upload — nothing goes to Oracle without your confirmation." (→ br-ap-hold-release), and "or first a hold-by-hold view with reasons, aging and anomalies" (→ br-ap-holds-analysis).
• C14 > 0 or C13 > 0: "I can plan the payment run for the approved invoices" (→ br-ap-payment-optimizer).
• Always: "Ask me any payables question and I'll add it as a KPI card on the dashboard." On yes, continue with that skill (Claude: read_skill("<name>"); Copilot routes to it); each reuses the workbook and re-extracts only what it needs.

Follow-up questions → new KPI cards

A question the extracted data can answer becomes a card in the next free row of A16:E20 (then A21 onward, shifting nothing else): A = the question as asked, B = =SUMIFS(fa,<criteria>), C = =COUNTIFS(<criteria>), D = the criteria in words, E = one sentence of commentary after reading B:C. Add its drill-down as a further block on AP_Detail at {r} = 146, 175, ... with the same formula shape. Available criteria columns: Days To Due / Due Date (timing), Approved Flag, On Hold, Invoice Status, Trading Partner, Invoice Currency Code, Pay Group, Payment Method, Payment Priority, Invoice Date, Functional Currency Amount (thresholds). Examples:

• "unapproved invoices over 10,000 due next month" → af,"N",dd,">"&(EOMONTH(TODAY(),0)-TODAY()),dd,"<="&(EOMONTH(TODAY(),1)-TODAY()),fa,">10000"
• "how much do we owe <supplier>" → sp,"<supplier>" (use the exact Trading Partner text from AP_Raw).

A question the data cannot answer is routed, never guessed: hold names, reasons and who placed them → br-ap-holds-analysis; the PO and receipt matched to an invoice → br-run-reports with AP Invoices and Lines and Invoice Number=<n>; Display Level=Lines and Distributions (columns Po Number, Receipt Number); payments already made → AP Invoice Payments with Invoice Number=<n>; supplier master data → AP Suppliers. Say which report you are running and why.

Actions and follow-ups

• Release a hold → br-ap-hold-release. Its download lists only user-releasable holds: a system variance hold (Line Variance, Dist Variance) never appears, because Oracle re-applies it on the next validation until the invoice is corrected. If the user asks to release one, say so and point to the fix — correct the lines or distributions in the Invoice Workbench and revalidate — instead of attempting a release.
• Refresh / update the numbers → read A16:E20 (the follow-up cards) first, delete AP_Raw, AP_Detail and AP_Intelligence, run Ops 1–6 again and re-create the follow-up cards from the questions you read.
• A different Operating Unit → the same, with the new value; never merge two runs into one sheet.

Rules

• Formulas only; never type computed numbers or bucket labels into cells; the bucket list is a formula array so 8-15-style text cannot turn into a date.
• Get-or-create sheets (Claude: getItemOrNullObject + load("isNullObject"), never getItem in try/catch; load a range, sync, read that same variable). Copilot: one edit action per Op.
• Never activate blitz_; delete it in Op 5. Never re-run a report that produced a data sheet.
• Every range address is ONE contiguous block. A comma-separated multi-area address (B11,B24) is rejected outright and the cell-by-cell recovery that follows is what turns a fast run into a slow one. Issue one call per block; several blocks in the same operation are free.
• A rejected write is retried ONCE, on that one range, and never cell-by-cell. If the retry fails too, leave that range unformatted, finish the pass, and say so in the reply.
• Dynamic-array formulas (FILTER, SORTBY, UNIQUE, TAKE, CHOOSECOLS, MINIFS) need Microsoft 365 Excel; on an older Excel every block shows #NAME? — say so and stop rather than rewriting them.