--- name: migrate-custom-reports description: Phase-2 engagement workflow for migrating a customer's gathered custom Oracle EBS reports (the Blitz Report Custom Reports Converter zip output) into Blitz Reports — inventory, extract SQL, classify each report into one of three outcomes (complete map / enhance / new custom build), build/recommend, categorize as "Custom Reports ", and prove each report prints what the original prints (field inventory + value fidelity gate) on the conversion instance. --- ## When to use A customer has run the **Blitz Report Custom Reports Converter** Blitz Upload on their instance and handed over the gathered zip (`CONV___/`). This skill takes that zip and turns each custom report into a recommendation or a built Blitz Report, in a customer-specific category. Read `custom-reports-migration` skill first — it holds the durable taxonomy, the gathered-folder layout, the gotchas, and the category-naming rule this skill depends on. For the per-report RDF/BIP→Blitz mechanics (SQL consolidation, anchors, assignments, parameter binding), delegate to the `convert-bip-and-rdf-reports-to-blitz` skill. ## Arguments `$ARGUMENTS` should contain: - The **customer abbreviation** (e.g. `ABC`) — drives the category name `Custom Reports `. - The **path to the gathered zip OR an unzipped folder** (e.g. `.../CONV_ABC_72146542_20260527_163231.zip` or the extracted folder). A zip is fine — unzip it first (step 0). - Optionally, a single program name to process just one report. ## Instance Run against the **conversion instance** (see `blitz-environment.md` for how to reach it, connect as `apps/$APPS_PWD`, copy files to it and query it). Never use any other instance for a conversion. ## Workflow ### 1. Inventory the gathered package - **Step 0 — if `$ARGUMENTS` points at a `.zip`, unzip it to a working directory first** (e.g. the scratchpad), then operate on the extracted `CONV___/` folder. A folder path is used as-is. The customer typically sends the **gather zip** plus the **converter result Excel** (which lists the programs they selected) — use the Excel to confirm scope. - Read `manifest.txt` and `gather_summary.txt` — list each program, its execution method (P=Reports, I=PL/SQL, Q=SQL*Plus, H=Host, K/J=Java), and which artifacts came through. - Note, per program, whether `_rdf.xml` is present (rwconverter succeeded) and whether any `...sql` custom-dependency files exist. - **Noetix detection:** a `noetix/_noetix_conversion.txt` marker in an item's folder (program type `Noetix View` or `Excel4apps` in the manifest) means this is a **Noetix conversion** — route it through the Noetix path in step 4 (see `custom-reports-migration` skill → "Noetix conversion"). The `noetix/` subfolder holds the recursive `NOETIX_SYS` view/package closure; `_wand.sql` (Excel4apps items) holds the consumer report SQL. - **Two-step extract→report detection:** a PL/SQL **wrapper/data-extract program** gathered alongside a BIP/report child (manifest shows e.g. `XXCANX4RRW|Data Extract|...` next to `XXCANX4RR|BI Publisher|...`), both folders sharing the SAME custom staging-table dependency closure, and the wrapper package spec exposing a conc-program `MAIN` entry + a populate procedure + a submit-child procedure — pair them and plan **ONE combined conversion**, not two (see `custom-reports-migration` skill → "Two-step extract → temp table → report processes"). - **🚨 Request the reference outputs NOW, with the scope confirmation:** one output of each original program from the customer's system (Excel, CSV or the XML/PDF it produces), with the parameters it was run with and the run time. It is the arbiter of the value fidelity gate (step 6): printed columns, order and headings, row grain, and every value the file lets you recompute. A report without one can still be built, but its delivery email says that the figures are unverified until the customer compares them. Our own earlier Blitz output is never a reference. ### 2. Per report — gather the facts (do NOT skip the .ldt or .rtf) - **Parameters & value sets — the AUTHORITATIVE source is `.ldt`, NOT the SQL binds.** Build the full parameter inventory per the rule's **🚨 Parameter fidelity** section (Step P1): for each LDT parameter capture `FORM_LEFT_PROMPT` (→ Blitz name), `FLEX_VALUE_SET_NAME` (→ LOV), `REQUIRED_FLAG`, `DISPLAY_FLAG`, `DEFAULT_TYPE`/`DEFAULT_VALUE`, and **`SRW_PARAM`** (→ the RDF bind/token, the authoritative mapping). **Never derive parameters from the rwconverter SQL bind list — that drops PL/SQL-driven parameters and invents wrong ones.** - **Data-model SQL:** - If `_rdf.xml` present → parse the data-model query from it. Also read its `` / formula columns / triggers — parameters used only there (not in the SELECT binds) are still real parameters (Step P3). - If absent → rwconverter segfaulted (large/complex RDF). Strings-extract the RDF (`strings -n 8 .rdf` or a printable-char split) to **identify** the report and its tables. Do not expect a clean single SQL from a procedural multi-cursor RDF. - **Output columns:** read the `.rtf` `` codes — the columns the customer's layout actually renders — together with the heading printed over or beside each one: the Blitz column must show that heading verbatim (rule "Data-model SQL", headings bullets). (`_ds.ldt` is BIP template registration only — no SQL.) - **🚨 Field inventory — MANDATORY, before writing any SQL:** `python field_inventory.py ` writes `_field_inventory.md`. It reads the `_rdf.xml` (or the `.rex` when rwconverter failed), the data template, the RTF and the custom package bodies, and lists: 1. every printed field and where the original computes it (query column, formula, placeholder, summary, parameter, or the package variable the loop writes, with the lines that assign it); 2. every formula, with the placeholders it assigns, its `select into` count and its exception handlers; 3. everything that decides which rows print (group filters, parent-child links, format triggers that can return FALSE, layout conditions and loops, and the triggers that build lexicals); 4. each query's `union` / `union all` counts and `order by`. The query is only part of the report. Most conversion defects sit in the per-row code after it: package loop code filling cursor literals, formula columns, a group filter hiding a section. The inventory is the conversion's work list. Fill its Blitz expression column as you build, and keep the file with the deliverables. A field with no Blitz expression is an open defect, not a simplification. - **Custom objects:** list the `...sql` dependency files. - **Value sets:** the `.ldt` embeds the full `VALUE_SET` definitions. For the Blitz parameters use **`LOV Oracle` + `LOV_NAME=`** (Step P2) — reuse the existing Oracle value set, do NOT re-author LOV SQL. To test on the conversion instance, load the value sets there (standalone value-set LDT) so `LOV Oracle` resolves. - **🚨 Lists of values — REUSE, in this order: shared Blitz Report LOV → the program's Oracle value set → `LOV custom` LAST (rule Step P2a).** For every list-bearing parameter, query the shared catalogue for the master table it picks from (`xxen_report_parameter_lovs_v` … `dbms_lob.instr(lov_query,'')>0`) and record which shared LOV you considered. The shared LOVs carry **security** you will not reproduce by hand (525 shared LOVs on an R12.1.3 instance: 24 GL-access-set aware, 33 `org_access_view` aware, 13 MOAC aware). **When id-ness forces a custom LOV, re-author only the SELECT LIST and copy the shipped LOV's `where` clause verbatim** — improvising it ships an unrestricted list of every ledger, or *under*-returns by missing ledger-set expansion. ### 3. Classify into one of three outcomes Apply the evidence-based 3-outcome taxonomy from the rule (`custom-reports-migration` skill → "The 3-outcome classification"): - **Outcome 1 — complete map:** stock value sets + stock tables + **no** custom-dependency files ⇒ a standard Oracle report an existing Blitz Report already covers. Identify the matching Blitz Report by the standard program/tables. - **Outcome 2 — partial map:** an existing Blitz Report covers most of it but lacks columns/filters ⇒ propose enhancing it **after user confirmation**; if declined/infeasible, fall through to Outcome 3. - **Outcome 3 — new custom report:** bespoke SQL / custom objects / customer-specific columns / row-security ⇒ build new. Classify by evidence, not by program name (customers rename standard programs). #### 🚨 MANDATORY GATE — run `classify_probe.py` BEFORE writing down any outcome A column-name comparison is **not** a classification. Column overlap tells you the two reports *display* similar things; it says nothing about whether the shipped report can *produce* the customer's rows. The only thing that decides that is the **data sources**. Do not reason about this from memory or from the column list — **run the probe and paste its output into the engagement notes.** ```bash # 1. dump the candidate shipped report's SQL from the BASE TABLE (xxen_reports_v truncates at 32764 bytes) # select xr.sql_text from xxen_reports xr, xxen_reports_v v # where xr.report_id=v.report_id and v.report_name=''; -> shipped.sql # 2. run the probe with EVERY customer prefix you have seen, not just manifest.txt's python ${CLAUDE_PLUGIN_ROOT}/skills/migrate-custom-reports/classify_probe.py \ --custom _extract/_datamodel_1.sql --shipped shipped.sql --prefixes XX,XXC ``` It compares localization objects (`JAI_*`/`JA_IN_*`), customer-owned objects (by prefix), standard EBS objects and union-branch counts, then applies a **deterministic verdict** (exit code 3 = Outcome 3): | signal | verdict | |---|---| | a localization object the shipped report does not reference | **Outcome 3** | | any customer-owned object referenced by the custom SQL | **Outcome 3** (and confirm its DDL was gathered) | | materially more union branches than the shipped report | **Outcome 3** — investigate | | none of the above | Outcome 1/2 candidate — *now* do the column diff | **Do not override the verdict with judgement.** Example: a custom GL detail report called a two-column enhancement of `GL Account Analysis` from column names alone; the probe on that exact pair reports 3 localization tables, 1 customer package and 9-vs-3 union branches — Outcome 3. The probe also prints an **UNCLASSIFIED** list (identifiers matching no known prefix). Check those by hand — an unrecognised customer naming convention is exactly how a customer package goes missing from the gather. ### Build helpers - `field_inventory.py [] [--out ]` writes the field inventory of step 2. It reads `_rdf.xml`, `.rex` (through `rex_parse.py`), data templates, RTF layouts and custom package bodies. - `rex_parse.py ` decodes a `.rex` text export when rwconverter fails, writing each query's SQL and the PL/SQL units next to it. - `vs_parse.py` (run inside a program's folder) prints every gathered value set: validation type, table, id and value columns, where clause. Use it to pick the shared LOV that matches each parameter. - `build_xml.py ` writes the import XML. The spec defines `CATEGORY` and `REPORTS`, a list of report dicts (`NAME`, `DESCRIPTION`, `SQL` file, `GUID`, `ASSIGNMENTS` = the converter's `_assignments.xml`, `PARAMS`, and `SHORT_NAME` and `PROGRAM` of the program the report replaces, written as the description's last lines `Source:` and `Short Name:`), so one call writes the category deliverable with every report and all assignments; a single report at top level still works. A `PARAMS` row takes name, anchor, sql (omit for a `:bind` anchor), type `LOV`/`LOV Oracle`/`LOV custom`/`Date`/`Char`/`Number`, `lov` or `lov_query`, `required`, `default`, `matching`, `hidden`, and `sub` for a further anchor of the parameter above. Shared LOVs are copied with their fallback payload from the shipped report XMLs (`content/r12/reports` in the Blitz Report repository, or the folder named by the environment variable `BLITZ_CONTENT_DIR`, e.g. `xxen_api.export_report` exports of shipped reports using those LOVs); the build stops when a parameter has other than one display row or an `n=n` anchor is not unique in the SQL. Keep the spec and SQL files next to the category file so a re-issue rebuilds from them. - A report cloned from a shipped one (an aging, a trial balance) is built by transforming the shipped XML instead: new GUID, name, description (ending with the `Source:` and `Short Name:` lines), category and assignments, extra columns inserted at the level where their keys are, extra parameters appended. Invoice-level extras go into the detail lexical as `max(x1.col)` so summary mode is untouched. ### 4. Build / recommend per outcome - **Outcome 1:** **recommend the shipped Blitz Report — do not ship a clone by default** (one-version-of-truth; a per-customer copy of a standard report is maintenance debt). To *prove* equivalence, you may build a throwaway clone — `xxen_api.export_report()` → change `GUID`, `REPORT_NAME`, replace `CATEGORY_ASSIGNMENTS` with `Custom Reports ` → `xxen_api.import_xml` → confirm identical SQL + parameter count + behavior → then **delete it** with `xxen_api.delete_report(,'Y')`. Only keep a clone if the customer explicitly insists on an isolated/branded copy. - **🚨 The Outcome-1 DELIVERABLE IS A TEMPLATE, not a report.** "Recommend the shipped report" is not the whole job — the customer's users expect *their* column set, order and layout. Ship one **named template per replaced custom report** (` `, e.g. `ABC Division Wise Trial Balance`) on the shipped report, so switching is a template pick rather than retraining. - **Templates import STANDALONE — never round-trip the whole report to add one.** `xxen_api.export_report` returns an **empty** `` section, so re-importing an exported report would DELETE the shipped templates. Use the template-only shape (`xxen_api.export_template()` produces it): a `REPORTS_ROW` carrying **only** `` (the report's GUID) + ``, with **no `SQL_TEXT`**. That cannot touch the report definition. (A delta *with* other report elements but no `SQL_TEXT` raises `ORA-01407` — see `report-development` skill.) - **Before building a pivot template, check what the shipped report already exposes.** Several shipped reports already pivot generically — e.g. `GL Trial Balance - Detail` has a **Pivot Segment** parameter (`&lp_pivot_segment_name`, resolved from `fnd_id_flex_segments_vl`) plus `PIVOT_SEGMENT_VALUE` / `ACCOUNT_SEGMENT` columns and a shipped `Trial Balance Pivot` template. A custom RDF that hand-codes one column per division/unit maps onto it exactly: put the account on `ROW`, the division on `COLUMN`, the balance on `VALUE`. That turns *N* hardcoded columns into data, so a new division needs no code change. - Pivot template XML: `` with `COLUMN_NAME`, `FIELD_TYPE` (`ROW`/`COLUMN`/`VALUE`/`FILTER`), `DISPLAY_SEQUENCE`, and `AGGREGATION` (`SUM`…) on `VALUE` rows. Every column referenced in `TEMPLATE_PIVOT` must also appear in `TEMPLATE_COLUMNS`. Model on the templates of the shipped `MSC Horizontal Plan` report. - **Verify the pivot actually materialised**, not just that the request completed: the output `.xlsx` must contain `xl/pivotTables/pivotTable1.xml` and `xl/pivotCache/…` (`unzip -l .xlsx`). A run can finish Normal and still emit flat data if the template was not applied. - **Outcome 2:** enhance the existing Blitz Report per the standard report-modification workflow (`development-workflow` skill → "Standard Workflow for Modifying an Existing Report") — baseline, change, redeploy, regression-diff. **Plus a named ` ` template** listing the custom report's columns **in the custom report's own order** (`TEMPLATE_COLUMNS.DISPLAY_SEQUENCE` = the RTF/layout field order), so the enhanced shipped report reproduces the customer's familiar layout. - **Measure the delta before proposing an enhancement.** Diff the custom report's real output columns (the `.rtf` `` list, or the RDF formula-column names) against the shipped report's columns across **all** its templates (`xxen_report_template_columns`), not just one. Column names differ freely (`SOURCE`→`SOURCE_NAME`, `TRAN_NO`→`TRANSACTION_NUMBER`, `PARTY_NAME`→`VENDOR_OR_CUSTOMER_NAME`), so build a name-mapping table. - **🚨 A COLUMN-NAME MATCH IS NOT A CLASSIFICATION. You MUST also compare DATA SOURCES before calling anything Outcome 1 or 2.** Column overlap says the two reports *display* similar things; it says nothing about whether the shipped report can *produce* the customer's rows. Always check, in the shipped report's **`sql_text`**: - **localization coverage** — count `jai_` / `ja_in` / `india` references. Most shipped reports have **none**; - **subledger/source coverage** — which sources the branches actually cover vs the custom report's `UNION ALL` branches; - **customer-owned objects** — any `_*` package/view the custom SQL calls, which the shipped report by definition does not. Example: a custom GL detail report was classified Outcome 2 ("only `PO_NUMBER` + receipt number missing") purely from column names. Checking the SQL showed the shipped `GL Account Analysis` has **0** `jai_` references and 2 union branches, while the custom report has **8** branches (Inventory / Payables / Receivables / **Payables India / Purchasing India / Register India / VAT India**), 4 India Localization tables and **25 calls to a customer package**. It was Outcome 3 all along. - **🚨 Read `sql_text` from the BASE TABLE `xxen_reports`, not `xxen_reports_v`** when measuring — the view truncates the CLOB at 32,764 bytes, which silently makes two different reports look the same length and hides content past the cut. (`GL Account Analysis` is 61,606 bytes; the view showed 32,764.) - **Only add columns that are GENERIC.** A column that is standard EBS data (a PO number, a receipt number) is a legitimate shipped-report enhancement. Customer accounting *policy* baked into the report — provision percentages, entity-specific exception lists, a particular `gl_daily_rates` conversion type, hardcoded division codes — must NOT go into the shipped report. Offer the customer the choice explicitly: **(a)** parameterise it so the shipped report stays neutral, or **(b)** build a separate `Custom Reports ` report. This is a decision for the customer, not a default — ask. When they choose (b), the custom report's `` must carry a caveat pointing users to the shipped report as well, so the two are not mistaken for alternatives. - **Outcome 3:** first create the gathered custom-dependency DDL on the instance (tables may be empty for a parse test), then convert the report via the `convert-bip-and-rdf-reports-to-blitz` skill, category `Custom Reports `. Convert the report, not just its query: the data-model SQL plus every row of the field inventory, with the edge behaviour listed in `references/value-fidelity.md`. That covers what a lookup prints for no row or several, widths, `nvl` defaults, dates as text, row order and grain, serial numbers and report modes. Keep each query's own set operator: `union` where the original has `union`, over the original's key. - **🚨 Dynamic SQL in the source → lexical anchor, never a frozen copy.** A program that assembles part of its SQL at run time (a select list from a lookup or setup table, a FROM/WHERE fragment chosen by a parameter, a hint, an order by) keeps that behaviour: spec entry `{'name': 'Item Columns', 'anchor': '&item_columns', 'sql':