---
name: custom-reports-migration
description: Rules for migrating custom Oracle EBS reports gathered by the Blitz Report Custom Reports Converter into Blitz Reports. Use with the migrate-custom-reports skill.
user-invocable: false
---

# Custom Reports Migration (product reference)

Engagement workflow: `/migrate-custom-reports`. Phase 1 = the customer runs the **Blitz Report Custom Reports Converter** upload (gathers RDF/host/SQL, `.ldt`, BIP `_ds.ldt`/`.rtf`, the custom dependency closure, `<PROG>_assignments.xml`, value-set/lookup ldts, Noetix closures) into a zip that is the gather request's OUTPUT (`download_filename` falls back to `outfile_name`). Phase 2 = classify and build on the conversion instance (instance-agnostic).

## Gathered folder layout (Phase-1 output)

A converter run produces `CONV_<ABBR>_<reqid>_<timestamp>/` containing `manifest.txt`, `gather_summary.txt`, `import_reports.sh`, and one subfolder per selected program:

| File | Meaning | Reliable when… |
|---|---|---|
| `<PROG>.rdf` | Raw Oracle Reports binary (execution method P) | always present for method P |
| `<PROG>_rdf.xml` | rwconverter output (the data-model SQL) | **only if rwconverter succeeded** — absent when it segfaults (see gotcha) |
| `<PROG>.ldt` | Concurrent-program definition: parameters + value sets + defaults | **always** — the authoritative parameter source |
| `<PROG>_ds.ldt` | BIP data-definition registration (template metadata) | BIP reports only — **contains no SQL** |
| `<PROG>_<lang>_<PROG>.rtf` | BIP layout template — `<?FIELD?>` codes = the columns the customer's layout actually outputs | BIP reports only |
| `_xdo_metadata.txt` | BIP template→layout mapping | BIP reports only |
| `<OWNER>.<NAME>.<type>.sql` | Custom dependency DDL (table/view/package/function/index) | present **only when the report has custom DB objects** — a key Outcome-3 signal |

## Classification

Evidence-based, never by name: standard value sets + stock tables + no custom DDL → **Outcome 1** recommend the shipped Blitz Report (no clone by default); partial → **Outcome 2** enhance the existing report; bespoke SQL/custom objects/user security → **Outcome 3** new report via `convert-bip-and-rdf-reports-to-blitz`. Category always `Custom Reports <ABBR>`. `<DESCRIPTION>` names the source program + user name and says what the report does.

## Description — MANDATORY (state the source report + what it does)

Every migrated/built custom report's **`<DESCRIPTION>`** must record **(a) the original report it was migrated from**, as its last two lines `Source: <user concurrent program name>` and `Short Name: <program short name>` (the view name for a custom view; e.g. `Source: Work Order Schedule Detail Report` / `Short Name: XXWOSCHDTL`), and **(b) a brief, plain-language description of what the report is for** (the entity it lists, the key columns/measures, and the main filters). The two lines are the format Blitz Report's own concurrent program import writes, and Blitz Report Custom Reports Migration Tracker links each program to its converted report through the `Short Name:` line, so write them exactly.

- **Placement + format follow `report-development` skill:** the `<DESCRIPTION>` goes **inside `<REPORT_TRANSLATIONS_ROW>`** (next to `<REPORT_NAME>`), not loose in `REPORTS_ROW` (a misplaced one is silently ignored on import → null description). Break it into short paragraphs with blank lines (flush-left), never one run-on block.
- **Shape**: paragraph 1 = what it lists / key columns; paragraph 2 = the main parameters; then the `Source:` and `Short Name:` lines, which `build_xml.py` appends from the spec's `PROGRAM` and `SHORT_NAME`. A report cloned from shipped XML gets the two lines by hand.
- **Applies to Outcome 3 (new build) and any Outcome 1/2 clone.** For a pure Outcome-1 *recommendation* (no report built) there is nothing to describe — the shipped report keeps its own description.
- **Verify after import:** `select dbms_lob.getlength(xrt.description) len, regexp_count(xrt.description,chr(10)) nl from xxen_reports_tl xrt, xxen_reports_v xrv where xrt.report_id=xrv.report_id and xrt.language='US' and xrv.report_name='<name>'` — a NULL length means wrong placement; `nl=0` means it's a run-on block; the text must end with the `Short Name:` line of the source program.

## Data-model SQL

Prefer `_rdf.xml`; `.ldt` is the authoritative parameter source; `.rtf` `<?FIELD?>` codes are the real output columns (keep multiplicity); method-P data is in the RDF, not `_ds.ldt`. Strip trailing `--` comment lines and replace a connector-carrying RDF lexical with `and 1=1` (both raise `ORA-00933`). PL/SQL (method I) programs: reconcile against the elements the package EMITS. An RDF whose data model reads only a temp table filled by its `BeforeReport` trigger converts to one query: the trigger's `insert ... select` becomes an inline view (the data model's `group by` over every column is a `distinct`), aggregated to the group the layout prints; compare the trigger's `if` branches first, they often differ only in `from` order.

- A customer program that reads `xla_transaction_entities_upg` keeps it. The APPS view `xla_transaction_entities` returned no rows for Cost Management receiving entities on the conversion instance, in a plain application session and inside the concurrent request alike, so a converted report that swaps in the view passes every parse gate and returns the header only; the base-table synonym is what the customer's program proved. For RCV_ACCOUNTING_EVENTS entities `source_id_int_1` is the receiving transaction id, so a direct join to `rcv_transactions` is correct.

- In a PL/SQL cursor an identifier that is both a column of the queried table or view and a procedure parameter resolves to the column: `x.p_type = P_TYPE` over a view that has a `p_type` column is always true and filters nothing, so every branch of the view prints. Read each such predicate for name capture before converting it to a filter; in `v$sql` the ported cursor shows real parameters as binds and a captured name as the column.
- Which BI Publisher layout prints is decided by the template registrations in `<PROG>_ds.ldt` (start and end dates, the default template), not by the file names: a layout whose end date has passed is not converted, and a `_MAIN` file with sub-templates can be an abandoned test. One layout can hold several tables behind `if:` conditions on a parameter; that is one report whose parameter selects the column list through a lexical.
- Dynamic SQL in the source converts to a lexical anchor, never to a frozen copy of what the SQL happened to be on the day of the gather. Whenever the program assembles a fragment at run time - a select list read from a lookup or a setup table, a FROM or WHERE fragment chosen by a parameter, a hint, an order by - the Blitz Report keeps that behaviour: a hidden Char parameter (or the visible parameter that drives the choice) with anchor `&<name>` at the fragment's position and a SQL_TEXT that returns the fragment. For a column list the SQL_TEXT returns one line per column, `<expression> <alias>` with a comma on every line but the last (`row_number() over (order by ... desc)>1`), ordered as the program orders; a constant default (the lookup type) gives the parameter a value on every run and can be referenced as `<parameter_value>`. The engine fetches every row of that select into a clob and substitutes it, so the `listagg` 4000-byte limit does not apply (`dynamic_sql_text`). The FROM aliases must be the ones the stored expressions reference, and the column alias is the code the program printed as the heading, double-quoted (`'"'||flvv.lookup_code||'"'`; a lookup code is at most 30 bytes). Decision by the product owner: a customer's runtime-driven column list is delivered dynamic, even when a static list would pass every test.
- Every output column shows the heading the original printed, in its exact case and punctuation: the RTF header cell or the label beside the field, the header line a PL/SQL program writes, the lookup code a dynamic program prints. Blitz Report shows a double-quoted alias verbatim; any other alias renders as `initcap` with underscores as spaces, or through the global column translations (`xxen_report.column_translation`), which differ between versions. Keep a plain alias only where its plain rendering already equals the heading; otherwise alias the top-level select column as the quoted heading (`x.debit_inr_amt "Debit Amount (INR)"`) and leave the inner queries on plain names. A template names a quoted column by the bare heading. Never ship an RDF formula, placeholder or summary name (`CF_`, `CP_`, `CS_`); `build_xml.py` refuses them.
- Heading limits: an alias is at most 30 bytes, so abbreviate a longer heading minimally; a heading printed twice needs a suffix, because Excel headers must be unique; an all-upper-case heading that is a plain identifier folds to the unquoted name even when quoted, so Blitz Report initcaps or translates it. Write such a heading with its underscores as spaces (`"ITEM CODE"` for `ITEM_CODE`), which keeps the printed capitals; a single upper-case word (`TDS`, `STATUS`) cannot be kept and shows as `Tds`/`Status` - list those for the customer. A single-level query whose `order by` names a select alias must follow the rename. A heading built from parameter values (`'0-'||p_age1||' QTY'`) is a hidden lexical whose SQL_TEXT binds those parameters and returns the aliased columns; `dynamic_sql_text` binds from the run's bind values. Verify after import by rendering every parsed column through `xxen_report.column_translation` and comparing with the printed list.
- A BI Publisher cross-tab (`horizontal-break-table`, `for-each-group@column`) converts to an embedded tabular pivot template (`PIVOT_LAYOUT: 'T'`, `REPEAT_LABELS: 'Y'` in `build_xml.py`): the printed line columns as row fields, the grouping column as the column field, the amount as a value. Every row field must hold one value per printed line, or the pivot splits the line: a data-template column that varies by the cross-tab column (the layout prints only the first row's value) is made per line first. Excel places row fields left of the column field and shows an empty row field as `(blank)`; say so to the customer. A layout's `<?position()?>` is `row_number() over (<the report's order by>)`; with a top-level `distinct`, use `group by` instead so the numbering is applied after duplicates are removed.
- Do not use a dynamic column translation rule for customer-specific headings: the importer processes rules before reports and skips a report-restricted rule whose report does not exist yet, and `delete_report` removes the rule's report association, after which the rule applies to every report.
- Lexical gates: `xxen_api.create_run_id_` applies no defaults, so a scripted run must pass the hidden parameter explicitly (`"Item Columns=<lookup type>"`) or the anchor is stripped and the run fails with `ORA-00936`; the design-time parse needs `xxen_report.initialize_run_param_vals_tmp(id)` then `initialize_parameter_values(id,'Y')` before `parse_report_sql_columns`, as the form does - a bare parse strips the unresolved anchor and reports `Error parsing SQL.`. Run the lexical's own select standalone first and count its lines against the source.

## Parameter fidelity — the authoritative process (MANDATORY, NEVER shortcut)

**The data-model SQL bind list is NOT the parameter source. The concurrent-program `.ldt` is.** Building parameters from the RDF/rwconverter SQL binds is the single biggest conversion failure mode — it silently drops parameters, invents wrong ones, and ships with no LOVs. Typical defect: an LDT with 8 properly-prompted, value-set-backed parameters (incl. a *required* `Stock Type`) converted into 8 bare bind parameters with wrong names, no LOVs, no required flags — because the build read the SQL binds instead of the LDT. **Follow every step below, in order, every time. Do not assume. Do not skip the verification gate.**

### Step P1 — Build the parameter inventory from the LDT (authoritative)
Parse the concurrent-program `.ldt`'s parameter entities. For **each** parameter capture all of:
- `FORM_LEFT_PROMPT` → the Blitz parameter **name** (e.g. `Stock Type`, `Item Category`) — NOT the bind token, NOT a guess.
- `FLEX_VALUE_SET_NAME` → the value set (its LOV).
- `REQUIRED_FLAG` → Blitz `REQUIRED`.
- `DISPLAY_FLAG = 'N'` → hidden parameter (negative `DISPLAY_SEQUENCE`).
- `DEFAULT_TYPE`/`DEFAULT_VALUE` → the default (e.g. a hidden `USER_ID` defaulting to `fnd_global.user_id`, never a hardcoded `1`).
- **`SRW_PARAM`** → the RDF user-parameter / bind **token**. THIS is the authoritative mapping between the concurrent-program parameter and the RDF, not the bind name guessed from the SQL.

### Step P2 — Decide the parameter TYPE from the value set's `validation_type` (do NOT blanket-convert to `LOV Oracle`)
Each parameter's value set has a `validation_type` (`select validation_type, format_type from fnd_flex_value_sets where flex_value_set_name='<name>'`) that decides the Blitz parameter type. **Reuse the value set as `LOV Oracle` ONLY when it actually has a list/SQL behind it; otherwise use a plain type by format (or a custom LOV if a pick-list is wanted).**

| `validation_type` | Has a usable LOV? | Blitz parameter |
|---|---|---|
| `I` Independent, `D` Dependent, `T`/`Y` Translatable, **`F` Table** | Yes (value list / table query) | **`PARAMETER_TYPE_DSP = LOV Oracle` + `LOV_NAME = <value-set name>`** — reuse it (no `LOV_GUID`, no `LOV_QUERY_DSP`); Blitz resolves the value set at runtime |
| `N` None (format only) | No | **plain type by `format_type`** (`C`→Char, `N`→Number, `X`/date→Date), **or a `LOV custom`** if a pick-list is wanted |
| `P` Pair / `R` Range Pair | No (from/to validation mechanism, no list) | **plain type by `format_type`** — the From/To pair is already modelled as the two Blitz parameters |

**Putting `LOV Oracle` on an `N`/`P` value set is the ORA-00903 trap.** Such a set has no list, so the run-screen value validation builds `select x.value,x.id from () x where x.value=:bind` (empty `from () x`) → **ORA-00903 "invalid table name"** the moment a value (incl. a default) is validated. Symptom: *"Error validating value 'X' for parameter '...'. ORA-00903 ... from () x"*. Decide the type from `validation_type` up front.

- **`F` = Table is a real LOV — RETAIN it, don't replace it.** The common mistake is treating `F` (and "special") sets as "no list". `F` is table-validated (has a `fnd_flex_validation_tables` row), so `LOV Oracle` reuses the original pick-list (e.g. `WIP_SRS_DISCRETE_JOBS_OPT` → discrete-job names; resolved at runtime so `xxen_report_parameters_v.lov_query` is null — that's expected, not a failure). Confirm table-backed: `select count(*) from fnd_flex_validation_tables ffvt, fnd_flex_value_sets ffvs where ffvt.flex_value_set_id=ffvs.flex_value_set_id and ffvs.flex_value_set_name='<name>'` > 0.
- **`LOV custom` when the list isn't portable.** For a customer-custom value set that won't exist on the conversion/validation instance (or to drop a cross-instance dependency), a portable `LOV custom` is fine — match the SQL's value semantics (e.g. a report comparing the literal `'Yes'`/`'No'` → `select 'Yes' id,'Yes' value,null description from dual union all select 'No' id,'No' value,null description from dual`).
- **A `LOV custom` that replaces an Independent value set copies its `VSET_VALUE`/`DESCRIPTION` pairs verbatim from the `_vs_<name>_.ldt` — id = the flex value, label = its description — and every id is then matched against the literals the SQL compares the bind to (`decode(:currency,'FC',…)`).** Never write the labels from memory: a list whose ids are right but whose labels are swapped (`'FC'` labelled Invoice Currency) passes every parse and run check and reaches the customer as "values not reflected" / "parameters not matched". Prove it by running the report once per list value and checking the amounts change as the value set's descriptions say.
- **`LOV custom` quoting — author with REAL single quotes.** The XML escaper turns `'`→`&apos;` exactly once, which the import decodes back to `'`. **Pre-escaping the LOV text with `&apos;` double-escapes** (`&`→`&amp;`) and the import then stores the **literal `&apos;`** → the LOV won't compile. **Verify by EXECUTING the STORED LOV** (`execute immediate 'select count(*) from ('||p.lov_query||') x'` over `xxen_report_parameters_v p`), never a hand-typed copy.
- **A literal DEFAULT_VALUE on a custom-LOV param must be one of its VALUES** (the run screen validates `x.value=:default`). For an id≠value LOV a literal default is the display *value* (e.g. Drawer defaults to `All jobs`, not `A`); an evaluated default — a bare function call such as `fnd_profile.value('ORG_ID')`, no `select … from dual` needed — returns the *id* and is validated as such (`report-development` skill "Parameter LOV Defaults").

### Step P2a — Before authoring ANY `LOV custom`, search the shared Blitz Report LOV catalogue

**Reuse ladder, highest first. A `LOV custom` is the LAST resort, never the default.**

| # | Option | How |
|---|---|---|
| 1 | **A shared Blitz Report LOV** | `PARAMETER_TYPE_DSP=LOV` + `LOV_NAME` + `LOV_GUID` (`content/lovs.xml` / `xxen_report_parameter_lovs_v`) |
| 2 | The source program's own Oracle value set | `LOV Oracle` + `LOV_NAME=<value-set name>` (Step P2) |
| 3 | `LOV custom` | only when neither fits — and then the security rule below is mandatory |

Find the candidates for the master table the parameter picks from — run this for EVERY list-bearing parameter, and record the answer:
```sql
select xrplv.lov_name, xrplv.guid,
case when regexp_like(dbms_lob.substr(xrplv.lov_query,4000,1),'[[:space:],]id[[:space:],]','i') then 'has id' else 'name only' end idness,
case when dbms_lob.instr(xrplv.lov_query,'gl_access_set')+dbms_lob.instr(xrplv.lov_query,'org_access_view')+dbms_lob.instr(xrplv.lov_query,'mo_glob_org_access_tmp')>0 then 'security aware' else 'plain' end security
from xxen_report_parameter_lovs_v xrplv
where xrplv.lov_query is not null and
dbms_lob.instr(xrplv.lov_query,'<master table, e.g. gl_ledgers>')>0
order by xrplv.lov_name;
```

**Why reuse outranks convenience: the shared LOVs carry SECURITY and edge cases you will not reproduce by hand.** Dozens of the shared LOVs are GL-access-set, `org_access_view` or MOAC aware. A converted report is replacing an Oracle report whose *value set or form* applied that security; a hand-written `select ... from <master table>` silently drops it. **A converted report's LOV must never be a bare unfiltered select over a security-scoped master table** (`gl_ledgers`, `hr_operating_units`, `mtl_parameters`, `hr_all_organization_units`, …).

**When id-ness forces a custom LOV, LIFT THE SHIPPED LOV'S `where` CLAUSE VERBATIM — never improvise the security predicate.** The shared LOV is often name-only (rule: never add an id column to it — see the P4 LOV gate), so a parameter binding a numeric `*_id` legitimately needs its own LOV. That is a reason to re-author the **select list**, never the **filter**.
- **Worked example, and the exact trap.** A converted report binds `:ledger_id`, so the name-only shared **`GL Ledger`** LOV could not be reused. The custom LOV shipped as a bare `select gl.ledger_id id, gl.name value, gl.description description from gl_ledgers gl order by gl.name` — **all 101 ledgers**, no access-set restriction, while the parameter's own `DEFAULT_VALUE` was already `gl_access_set_security_pkg.get_default_ledger_id(...)`. Customer-reported: the list and the default disagreed.
- **The improvised fix was ALSO wrong, and worse than the bug.** A hand-written `gl.ledger_id in (select gasnav.ledger_id from gl_access_set_norm_assign_v gasnav where gasnav.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasnav.access_privilege_code in ('R','B'))` **under-returns**: an access set may grant a **LEDGER SET**, and `gl_access_set_norm_assign_v` does NOT expand it to its member ledgers. Measured: access sets 1160/1161 returned **1 ledger instead of 5**, 1180/1225 **1 instead of 3**. Under-returning blocks a user from a ledger they are entitled to — a worse failure than listing too many. The `access_privilege_code` filter was additionally a no-op (all 115 assignments were `B`) and would itself kill the expanded rows, which have no `_v` row at all.
- **The correct predicate is the shipped `GL Ledger` one, copied unchanged:**
  ```sql
  gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+))
  ```
  Note it uses the **base** table and the outer-join expansion, and carries no privilege filter. Verified after the correction: 5/5/3/3 for the ledger-set access sets, 3 for a 3-ledger set, 1 for a single-ledger set, default always present in the list.
- **Generalise it:** the shipped LOV is the specification. Read its `lov_query`, take its `where` clause as-is, and change only the select list. If you find yourself writing security SQL from first principles during a conversion, you are about to ship one of these.

### Step P3 — Reconcile each parameter against how it's actually used in the report
**Principle (per the Blitz developer guide): prefer a `1=1` WHERE-anchor with the predicate in `SQL_TEXT` over embedding the value as a `:bind` in the main SQL — wherever possible.** Convert every optional/independent filter to `1=1` + `SQL_TEXT`; keep a bare `:bind` anchor ONLY for non-filter logic that must stay inline (see the exception below). For every LDT parameter, find how its `SRW_PARAM` token is consumed and wire the Blitz anchor accordingly:
- **Used as a `:bind` for an OPTIONAL equality filter in the SELECT's WHERE** → **prefer a `1=1` WHERE-anchor with the predicate in `SQL_TEXT`**, not the bind embedded in the main SQL. RDFs almost always express optional filters as a self-referencing no-op bind, e.g. `and nvl(mc.segment3,0) = nvl(:P_SEGMENT3, nvl(mc.segment3,0))`. **Convert that to the Blitz idiom**: put a single `1=1` anchor at that spot in the SQL and move the predicate to the parameter as `SQL_TEXT = nvl(mc.segment3,0)=:P_SEGMENT3` (the **bare** predicate — NEVER prefix `and`/`or`; the framework ANDs it at the anchor — see `report-development` skill "WHERE Clause Anchors"). The framework then injects the condition only when the user supplies a value, and the verbose `nvl(...)=nvl(:bind,nvl(...))` no-op trick disappears. Multiple optional filters share the one `1=1` anchor (they AND together). Keep a parameter as a **bare `:bind` anchor (no SQL_TEXT)** only when the bind is used in non-filter logic that must stay inline — e.g. `:user_id` inside `(:user_id=1 and ...) or (:user_id=2 and ...)` row-security, or a bind referenced inside a `CASE`/formula column. Match the LOV id/value semantics (see `report-development` skill "LOV Binding").
  - **`1=1` anchor must be unique in the SQL** (the framework injects at every literal `1=1`); rewrite any incidental `1=1` inside a subquery to a real predicate. Single- vs multi-value: `nvl(col,0)=:bind` is single-value; for multi-select use the unwrapped `col=:bind` so the framework auto-expands to `IN (...)` (`report-development` skill "Multiple Values").
  - **Worked example:** Organization + the 4 category segments were converted from embedded `nvl(mc.segmentN,0)=nvl(:P_SEGMENTN,nvl(mc.segmentN,0))` binds to `1=1` anchors with `SQL_TEXT = nvl(mc.segmentN,0)=:P_SEGMENTN` / `d.organization_id=:p_inv_org_id`. `User ID` and `Decode/Encode Type` stayed bind anchors (row-security + CASE column).
  - **The self-referencing no-op also comes in a `DECODE` form:** `nvl(col,x) = DECODE(:bind,'Y',y,nvl(col,x))` is the same pattern as the `nvl` no-op — a Yes/No parameter whose 'Y' restricts and whose other values no-op. Convert to a **matching-value** parameter: `LOV Oracle` + `LOV_NAME=Yes_No`, `MATCHING_VALUE=Y`, `1=1` anchor, `SQL_TEXT` = the simplified restriction (`nvl(xipcq.asst_sub_inv,2)=1` ⇔ `xipcq.asst_sub_inv=1`); selecting No matches no row and injects nothing = the DECODE fallback.
  - **The no-op's treatment of NULL is part of the row set.** `col=nvl(:p,col)` with an empty parameter is `col=col`, which drops every row whose column is null: convert it to the predicate `col=:p` plus a static `col is not null` in the main SQL, unless the column is declared not null. An orphan bind in that form (no LDT parameter) is just `col is not null`. With a stand-in on the column side (`nvl(col,'X')=nvl(:p,nvl(col,'X'))`, or a dynamically appended `and nvl(col,1)=<value>`) the predicate keeps the stand-in, `nvl(col,'X')=:p`, so a value equal to the stand-in still matches the empty rows as it did in the original; `upper()` stays on the side the original has it.
  - **A free-text parameter on an upper-case value set is upper-cased.** A format-only value set with `UPPERCASE_ONLY_FLAG = "Y"` converted what the user typed in the submit form to upper case; a Blitz `Char` parameter passes the text as typed, so wrap the bind in `upper()`.
  - **A legitimately-retained bind anchor still needs a pick-list.** When a bind stays inline because it feeds SELECT-list logic (currency lookup, CASE/formula column), the parameter must NOT be a raw numeric-id entry (coding-standards #46 — users never type ids): give it an **id/value LOV** so users pick the display value while the bind receives the id. When the SQL compares the bind to a numeric literal or id column (e.g. `when :ledger_id=2223`), the LOV MUST pass the **id** — a name-only shared LOV would bind the name into a numeric comparison (wrong result / ORA-01722) → a custom id/value LOV is required. **Re-author only the SELECT LIST; take the shipped LOV's `where` clause verbatim (Step P2a).** A bare `select gl.ledger_id id, gl.name value, gl.description description from gl_ledgers gl` lists every ledger with no GL access-set restriction. The correct form keeps that select list and adds the shipped `GL Ledger` predicate unchanged.
- **Used only in RDF PL/SQL** (formula columns, before-report trigger, lexical `&param`) and **absent from the SELECT bind list** → you MUST read the RDF PL/SQL (rwconverter XML `<programUnits>`/`<formula>`/triggers, or `rdf-analyzer`) to learn what it filters, then express that as a Blitz `&lexical` or WHERE-clause `SQL_TEXT`. **A parameter missing from the SELECT binds is NOT an absent parameter — it is a PL/SQL-driven one. Never drop it.** (a `Stock Type`/`P_RTYPE` or `Decode/Encode Type`/`P_DE_TYPE` parameter used only in formula columns is exactly this case.)
- **Orphan binds in the SELECT with no LDT parameter** (e.g. `:p_org_id`, a commented-out `--...:p_item`) → do NOT create a parameter for them. Resolve to their RDF default, drop the dead predicate, or confirm intent. Never expose an internal/dead RDF bind as a user parameter.

### Step P4 — Verification gate (a build is NOT done until ALL pass)
"It parses and runs wide-open" is **NOT** acceptance — a bind-only, LOV-less parameter parses and runs perfectly while being completely wrong. Before declaring the conversion complete, produce and check a **parameter-by-parameter table** proving fidelity to the LDT:

| Check | Pass condition |
|---|---|
| Count | Blitz parameter count = LDT parameter count (hidden included) |
| Names | Every Blitz name = the LDT `FORM_LEFT_PROMPT` (no invented/generic names) |
| LOVs | Parameter TYPE matches the value set's `validation_type` (P2): list/table sets (`I`/`D`/`T`/`F`) → `LOV Oracle` + correct `LOV_NAME`, and **confirm the LOV passes the framework-wrapper gate below, not a bare row count**; `N`/`P` sets → plain type (or `LOV custom`), **never `LOV Oracle`** (else ORA-00903 `from () x`). Custom LOVs verified through the framework wrapper below (not a bare `select count(*)`) |
| Required | Every LDT `REQUIRED_FLAG='Y'` parameter is `REQUIRED` in Blitz |
| Hidden/defaults | `DISPLAY_FLAG='N'` → hidden; defaults match (e.g. `fnd_global.user_id`, not a literal) |
| Wiring | Every parameter is reconciled per Step P3 (bind / SQL_TEXT / lexical) — none left as a naked bind that doesn't filter |
| **Bind-anchor gate (mechanical — run it, don't eyeball)** | `select xrpv.parameter_name, xrpv.anchor from xxen_report_parameters_v xrpv where xrpv.report_id=xxen_report.report_id('<name>') and xrpv.anchor like ':%'` — EVERY returned row needs a written P3-exception justification: the bind's ONLY uses in the main SQL are SELECT-list/CASE/row-security expressions. Automatic FAIL when the bind token sits in a WHERE filter predicate (plain equality, LIKE, or a self-referencing `nvl`/`DECODE` no-op), or when a retained numeric-id bind parameter has no id/value LOV. Bind-anchor filters have shipped before — the fidelity table is produced BEFORE declaring done, not after the user complains |
| **LOV gate (mechanical — but CALIBRATE it before believing a failure)** | Run each LOV through `select x.value, x.id from (<lov>) x`, NOT a bare `select count(*) from (<lov>)` — the bare count hides a genuine id/bind mismatch. **A raw `x.id` failure is NOT by itself a defect, and "every LOV needs an id column" is WRONG** — the framework describes the LOV's select list to detect id-presence and wraps accordingly, so a **name-only** LOV is fully supported. Proven on the conversion instance: the shipped shared LOVs `HR Operating Unit`, `GL Ledger`, `FND User Name`, `AR Customer Name` and `INV Organization Code` **all** fail the raw `x.id` wrapper and pass value-only, and `report-development` skill documents them as name-only by design. So **calibrate the gate against 3-5 known-good shipped LOVs first**; if they fail too, your harness is over-strict and its hits are false positives (a typical uncalibrated run: 17 hits, 16 of them false). **Never "fix" a name-only LOV by adding an id column** — with an id present the bind receives the **id** instead of the display value (see `report-development` skill "LOV Binding"), which silently breaks every SQL_TEXT that compares a name column. The real defect is a **mismatch**: the LOV's id-ness vs what the consuming SQL_TEXT/`:$flex$` actually binds — a name-only LOV feeding a numeric `*_id` column (ORA-01722, or 0 rows), or an id LOV compared against a name column. Judge each hit by reading its SQL_TEXT, not by the wrapper's exit code. Substitute a real parent value for each `:$flex$.<parent>` when testing dependent LOVs: `xxen_api.lov_query_exec_nosort` reads parents from the session's run-parameter table and returns 0 rows for a dependent LOV in a bare session, so execute the stored `lov_query` with the parent substituted. Org-access-aware shared LOVs (`INV Organization Code`) return rows only under a responsibility with organization access, never as System Administrator. |
| **Shared-LOV reuse gate (mechanical — list every custom LOV and justify it)** | `select xrpv.parameter_name, dbms_lob.substr(xrpv.lov_query,300,1) from xxen_report_parameters_v xrpv where xrpv.report_id=xxen_report.report_id('<name>') and xrpv.lov_query is not null;` — every row returned is a **hand-written** LOV (a `LOV Oracle` value set and a shared-LOV reference both store null here, so the query lists exactly the ones needing justification). For each: name the shared Blitz LOV considered and why it was rejected (Step P2a), the only valid reason being id-ness or genuine non-portability. **Automatic FAIL:** the LOV selects from a security-scoped master table (`gl_ledgers`, `hr_operating_units`, `hr_all_organization_units`, `mtl_parameters`, …) with no security predicate, or with a predicate improvised instead of copied from the shipped LOV. |
| **Output-column reconciliation (BIP/RDF/PL-SQL completeness)** | Diff the built column set against the AUTHORITATIVE rendered output — the RTF `<?FIELD?>` codes for BIP, the SELECT list for RDF, and **for a PL/SQL (method I) program the elements the package EMITS** (its `'<TAG>'` XML literals or `fnd_file.put_line` column list) — **preserving multiplicity and order (never dedup the field list)**. Counts must match and every rendered field must map to exactly one Blitz column; **produce the per-column mapping table and count it — a "core subset" delivery is only acceptable when the dropped columns are LISTED and the customer has agreed to the reduced scope in writing**. A layout can render the SAME field code in two columns under different headers" and "(Latest Revision)"); a de-duplicated field scan drops the second column and ships a report one column short. Skipping this gate for PL/SQL programs ships registers missing most of their output elements (e.g. every tax column of a GST register) — found by the customer, not the build |
| Anchor SQL_TEXT not blank | No `1=1`/`&lexical`-anchor parameter has empty SQL_TEXT (`anchor like '%=%' or '&%'` ⇒ SQL_TEXT must be non-empty) |
| Assignments | The report has `<ASSIGNMENTS>` (top-level, per `report-development` skill) so a responsibility can see it — consumed from the gathered `<PROG>_assignments.xml`; `xxen_report_assignments_v` returns the expected rows |
| Description | `<DESCRIPTION>` (inside `REPORT_TRANSLATIONS_ROW`) names the **source Oracle report** (`<PROG>` + user name) AND briefly says what the report does; broken into paragraphs (not run-on). Verify it imported non-null with line breaks |
| **E2E with real values** | Run through the Blitz **frontend run screen** (or with **real filter values set**, never wide-open) and confirm the filters actually restrict the output |

If any row fails, the conversion is not done — fix and re-verify. **Never report a conversion as complete on a wide-open parse+run.**

### Mechanical rules for generating the parameter XML (each of these shipped a real defect first)

- A `LOV custom` query returns `value` and `description` columns, `id` optional (`null description` when there is nothing to show). The run form reads every custom list through `select x.value, x.id, x.description from (select null id, x.value, x.description from (<query>) x) x where rownum<3`, so a value-only list opens with `Error reading LOV value ... ORA-00904: "X"."DESCRIPTION": invalid identifier` on every run screen while `select count(*) from (<query>)` gates pass. `build_xml.py` refuses a query without both aliases; the instance gate is `select x.value, x.description from (<query>) x where rownum<3` for every `lov_query` of the category.
- An LDT SQL default (`DEFAULT_TYPE = "S"`, `select <expr> from dual`) may drop the `select` only when `<expr>` is something `xxen_report.is_function` evaluates (`fnd_global.`, `fnd_profile.`, `xxen_util.`, `xxen_report*.`, `sys_context(`, `:$flex$.`, a standard SQL function, `sysdate` on a Date, `case ... end`); anything else, `mo_global.get_current_org_id` included, is stored as literal text. On a Number parameter every run screen then stops with `Error validating value '<expr>' for parameter '<name>' of type Number ... ORA-06502`, and the add-in, which echoes the literal back as the parameter value, fails the run with `ORA-06502: character to number conversion error`. `build_xml.py` refuses such a default. Gate: print `initialize_parameter_values`' message on its own (reusing its variable for the parse result hides it), and call `xxen_webservices.report_metadata` plus `report_param_lov` for the independent lists with the metadata defaults as binds, which is what the add-in does when the run screen opens.
- A single operating unit program (`MULTI_ORG_CATEGORY = "S"` in the LDT) took its operating unit from the submit form, and `mo_global.get_current_org_id` / `fnd_global.org_id` inside it returned that unit. A Blitz Report without a concurrent program link runs in multi-org mode, where `mo_global.get_current_org_id` is null for a responsibility with several operating units and `fnd_global.org_id` is -1 without MO: Operating Unit, so a hidden parameter defaulted from either returns no rows there. Convert it to a visible required id/value list with the shipped HR Operating Unit where clause (`mo_glob_org_access_tmp`) and default `fnd_profile.value('DEFAULT_ORG_ID')`; the engine fills a required list that has a single row, so a single operating unit responsibility needs no default.

Whether you hand-author the XML or generate it, these four decide correctness and none is visible to a framework parse:

- **A `--PARAM` predicate can be lifted into an `n=n` anchor only when it sits at the SAME bracket depth as the anchors — not "depth 0".** When the report is wrapped as `select ... from ( <branches> ) x`, the anchors live at depth 1 and so do the liftable predicates; a depth-0 test then finds *nothing* liftable and every parameter degenerates to a `:bind` anchor — silently turning optional filters into always-applied ones and failing the Step-P4 bind-anchor gate. Compute the anchor's depth first, then lift only predicates whose **every** occurrence is at that depth. A predicate deeper than the anchor is inside a correlated subquery and must stay inline behind a `:bind` (the P3 exception). Verify all anchors sit at one depth and abort if they don't.
- **The visible row (DISPLAY_SEQUENCE + LOV + default + required) belongs on the FIRST branch that actually carries a predicate for that parameter — never hardcoded to branch 1.** In a multi-branch report a parameter used only in, say, branches 6 and 7 otherwise gets sub-rows only and **no display row at all**: it never appears on the run screen and `xxen_api.set_parameter_value_` fails with `ORA-20001: Parameter "X" not found`. Audit before importing — every distinct parameter name must have exactly one row containing `<DISPLAY_SEQUENCE>`.
- **A plain-typed parameter (Date/Number/Char) must NOT be given an empty `LOV_QUERY_DSP`.** The framework builds `select x.value, x.id from () x` → `ORA-00903 invalid table name` at run time (the same trap as putting `LOV Oracle` on an `N`/`P` value set, Step P2). Emit `PARAMETER_TYPE_DSP` alone and no LOV element.
- **A bind that lands in an `org_id`/`*_id` NUMBER column needs an id/value LOV.** An OU LOV written as `hou.name value` with no `id` binds the *name* into `org_id` → `ORA-01722: invalid number`. The failure can hide: if other predicates filter every row first, the run completes "Normal" with **0 rows** instead of erroring, so a 0-row result is never proof of correctness — check the bind against the column type.

- **Assignments are CONSUMED from the gathered `<PROG>_assignments.xml` — never hand-listed, and every row keeps its OWN `APPLICATION_SHORT_NAME`.** The gathered file is the authoritative set (the source program's real request groups). Hand-typing a few group names into a config and hardcoding one application drops most of the access and points the rest at the wrong application — the reports then appear for almost nobody. A customer's real set typically spans several applications (the custom one plus `JA`/`SQLAP`/`AR`/`JE`/`SQLGL`).
  - **Gate it OFFLINE against the gathered file — the instance-side count is NOT a substitute.** On a conversion instance the customer's request groups do not exist, so the import legitimately reports **0 assignments** and can tell you nothing about whether the XML was right. A zero there is *unverifiable*, not *passed*; treating it as passed is what let the above ship. Diff the built XML's `<ASSIGNMENTS_ROW>` set against the gathered file (level_value + application_short_name, count exact, no extras) with no DB at all — reference implementation `verify_assignments.py` in the `migrate-custom-reports` skill.
  - **The level code in a gathered assignments file may be the OBSOLETE single letter — always normalise it, never trust it.** The converter hardcoded `G` while the product switched to full words, and an unrecognised code is **silently skipped on import** — so on any customer at that build or later the converter's own gathered file imports **zero rows with no error**. **The converter is fixed to emit `Request Group`**, but every package gathered before that fix still carries `G`, and nothing reprocesses them. Map the gathered code through the legacy table below and emit what the TARGET instance's build expects.

**E2E harness quirks (bare `xxen_api` API, not report defects):** `set_parameter_value_` stores the value **raw**, so pass what the *stored* column expects — a **`DD-MON-YYYY`** date (canonical `YYYY/MM/DD HH24:MI:SS` raises `ORA-01861` at bind time), and for an id/value LOV the **id**, not the display value. A run that reaches "Parameters and binds" in the log and then fails under `FND_DATE` is this, not the report.

**A 0-row E2E on the conversion instance is usually a missing customer object, and you can prove which one:** re-run the SQL with that single join relaxed and count. On one report this isolated an ungathered customer FND lookup (inner-joined to the ledger name) — 0 rows as shipped, 45,197 with the join relaxed. That is a complete, honest E2E result: the report is correct and the dependency is the customer's to supply.

**The two mechanical gates above (LOV framework-wrapper, output-column reconciliation) exist because parse-only validation is blind to them; both defect classes have shipped and were caught by the customer, not the build.** (1) A Period LOV missing its `id` column parsed fine and even returned rows to a bare count, but failed the framework's `select x.value, x.id` wrapper, so the report could not be run at all. (2) The Pending PO report was one column short because the RTF renders `CP_LINE_PRICE` twice and the field-code scan had deduplicated it. Neither is a parse failure — `xxen_report.parse_report_sql_columns` never opens a LOV nor compares the column set to the source, so a clean framework parse proves neither. When the conversion instance lacks the customer's master data (so a real frontend run is impossible), these two gates — plus, ideally, a **column-and-value diff against one real Oracle output the customer supplies** — are the substitute; run them before declaring done.

**Four more classes that shipped in converted batches and were caught by customer UAT, each now a mandatory check:**
- **A visible parameter with NO type and NO LOV is a shipped trap** — a "Ledger" parameter with neither (`PARAMETER_TYPE_DSP` absent) lets the run screen accept free text, so the user's ledger NAME goes into the numeric `ate.ledger_id` bind → ORA-01722 on every run. Same class: a "Supplier Site" LOV with no `id` column while its SQL_TEXT binds `vendor_site_id`. Both are exactly what the P4 bind-anchor + LOV framework-wrapper gates test — run those gates over EVERY parameter of EVERY report in the batch, not just the ones being actively worked on.
- **A STUB of a customer function must never guess the return type — normalize the consuming union positions to varchar2 instead.** A stub declared `return date` for a customer function that really returns a character value makes every run die with ORA-01790 (union datatype mismatch) on their instance while parsing clean on ours. Fix pattern: wrap every select-list call of an unknown-signature customer function in `to_char(...)` (legal for both date and varchar2 returns) and align the other union branches' expressions at those positions to text — the report then cannot depend on the real signatures at all.
- **Report SQL is a CLOB and is NOT capped at 32,767 bytes — do NOT flag a long SQL as a defect**. Blitz Report supports report SQL beyond 32k: the engine reads the base CLOB, and a long-SQL report parses and runs normally. A `null` coming back from a varchar2-bound path in your own ad-hoc probe is an artifact of that probe, not a product limit. When a multi-branch union needs per-branch columns, **wrap the union in `select x.*, <new columns> from (<union>) x`** anyway — not for size, but so the additions are written ONCE and correlate via the union's exposed id columns (this also gives per-branch fallback semantics for free: a branch that exposes NULL ids makes that source's subquery return null).
- **Scalar lookups keyed on non-unique customer data need `max()`** — a bank-code lookup joining `ce_bank_accounts` by account name+number, which is not unique on the customer's data, raises ORA-01427 at run time. The original RDF swallowed the duplicate inside a formula-column exception handler, so the custom report "worked" — Blitz surfaces it. When converting an RDF formula column with `WHEN OTHERS RETURN NULL/0` around a `select into`, aggregate the converted subquery (`max()`/`sum()`) so multi-row data degrades the same way the original did. Apply `max()` also to a cursor lookup without a handler whose key is unique only within one operating unit (`order_number` alone): a report without an operating-unit filter raises ORA-01427 as soon as the number exists in two operating units.

## Gathering gaps

- A method-P program whose folder holds only the `.ldt` plus value sets (`1 file(s) written` counts only what `write_program_files` writes; the `.rdf` copy is the shell's) is usually a program whose EXECUTABLE belongs to another application than the program: the converter resolves `APP_TOP` from the concurrent program's application basepath (`fcp.application_id`), while the `.rdf` lives under the executable's application top (`fcp.executable_application_id`), so the gather logs `RDF file ... not found`. Compare `BEGIN PROGRAM "<X>" "<app>"` with `BEGIN EXECUTABLE "<X>" "<app>"` in the LDT; ask the customer for `$<EXEC_APP>_TOP/reports/US/<X>.rdf` and leave the tracker row blocked; never classify from the parameters alone.
- `classify_probe.py` reports a customer-owned object only for a `<prefix>*` table or a bare `<prefix>*(` function call; a customer dependency accessed some other way (a synonym under a standard prefix, an object referenced only from a trigger) still needs the gathered `.sql` list read by hand. It does read a **schema-qualified** `from <prefix>.<table>` — an earlier version did not and silently returned "Outcome 1/2 candidate" for a report whose whole data source was a customer table, so re-check that behaviour after any change to `FROM_RE`.
- **`xxen_report_sql_columns` is a SESSION-duration GTT, so the output-column gate must parse and count in ONE session.** Reading it in a fresh session returns zero rows, which reads exactly like a parse failure on a report that parsed fine. Put `xxen_report.parse_report_sql_columns(<id>)` and the `select ... from xxen_report_sql_columns where id=<id>` in the same script (a `commit` between them is harmless).
- **Strip `fnd_date.canonical_to_date()` from every converted date predicate.** The RDF received its dates as canonical strings; a Blitz `Date` parameter binds a real DATE, and `canonical_to_date(<date>)` returns NULL, so every predicate using it is silently never true and the report completes Normal with zero rows. Same family as the RDF's `p_<x>` PL/SQL variables left unbound in a gathered package body — grep the converted SQL for `canonical_to_date` and for bare `p_[a-z_]+` (no colon) before the first run.
- **A value set's LDT carries its security grants, and FNDLOAD aborts the whole file on the first responsibility the conversion instance lacks** (`ORA-20002: LDR.GET_RESP(P) is not able to find ... RESP KEY`), leaving the value set absent and the report's `LOV Oracle` degraded. Drop the `VSET_SECURITY_USAGE` / `VSET_SECURITY_LINE` / `VSET_SECURITY_RULE` blocks before loading; the values and the table definition are what the LOV gate needs.
- **A value set's validation TABLE is never gathered, so its `LOV Oracle` silently degrades on the conversion instance.** `import_xml` stores a `LOV Oracle` parameter as a bare `LOV` with no name when the named value set is absent, which reads exactly like a build defect (and is the "visible parameter with no LOV" trap) while the delivered XML is correct. Load the gathered `<PROG>_vs_<NAME>_.ldt` with FNDLOAD (`afffload.lct`), create a stub for the table named in its `APPLICATION_TABLE_NAME` (shape comes from `VALUE_COLUMN_NAME`/`ID_COLUMN_NAME`), then DELETE and re-import the report and re-read `parameter_type_dsp` — only then does the gate mean anything. Request the real objects from the customer in the same round.
- **A value set whose validation table is registered under the customer application fails FNDLOAD on the conversion instance** (`ORA-20002: LDR.GET_APP(SHORT_NAME, P) is not able to find APP SHORT NAME:'XXCUST'`): rewrite `TABLE_APPLICATION_SHORT_NAME = "XXCUST"` to `"FND"` in the copy you load. Test accommodation only; the delivered XML names the value set, not its application.
- An LDT parameter with `ENABLED_FLAG=N` is invisible in the original and is not converted; list it in the mapping. Rendered fields that echo parameter values in the layout header (organization name, the from/to dates) are not report columns either, and must not turn their binds into `:bind` anchors.
- **Never use a `model` clause in report SQL.** `dbms_sql.parse` accepts it, but the framework's `describe_columns3` fails with `ORA-00984: column not allowed here`, so the report parses to a single error row. A PL/SQL loop that accumulates receipts until the running total is positive, allocates `least(total, remaining)` and resets is the same as, for cumulative receipts `C` newest first and the running maximum `M` of the previous rows floored at 0, `greatest(0,least(C,onhand)-least(M,onhand))` per row, with `onhand-least(max(C),onhand)` left unexplained — two analytic layers, no recursion.
- A `1=1` anchor works inside a `connect by ... start with (org,item) in (select ... where 1=1 ...)` subquery: the framework injects textually, so the top-item filters of a BOM explosion belong there. A program that calls `bompxinq.exploder_userexit` is converted by reproducing `BOMPBXIN.bom_exploder` (read it on the instance): a recursive `with` over `bom_bill_of_materials` x `bom_inventory_components` (primary bill, `common_bill_sequence_id`, `implementation_date is not null`), depth `least(50, bom_parameters.maximum_bom_level)`, `eco_for_production=1` components skipped, no explosion of a model or option class under a standard item, Explode Option 1 below the first level only for components whose effectivity overlaps the parent's, and extended quantity `component_quantity*decode(basis_type,null,<parent extended quantity>,1)/decode(component_yield_factor,0,1,component_yield_factor)` in plain NUMBER arithmetic. Never `exp(sum(ln()))`: it is inexact, so a `union`/`distinct` on the result misses duplicates, and `ln(0)` raises ORA-01428 on any zero quantity. A leaf is a component exploded nowhere in the top item's explosion, not `connect_by_isleaf`. Prove it against the exploder itself: it writes the session-private `BOM_SMALL_EXPL_TEMP`, so run it and roll back.
- A template `COLUMN_NAME` must be the report's SQL alias; a name copied from the RTF field (`DOCUMENT_NUMBER` for `document_seq_value`) is silently dropped from the output. When evaluating defaults in the E2E harness, skip parameters whose default references `:$flex$` (binding the literal text `null` raised ORA-01722 only once rows reached that predicate), and set hidden operating-unit parameters explicitly: `fnd_global.org_id` is -1 in a bare `apps_initialize` session.
- rwconverter segfaults on some RDFs (no `<PROG>_rdf.xml`, ten retries do not help): ask the customer for the `.rex` text export of the report (Reports Builder File > Convert, report definition to text) and run `${CLAUDE_PLUGIN_ROOT}/skills/migrate-custom-reports/rex_parse.py <folder> <PROG>` - it writes every query's SQL and every PL/SQL unit (formula columns, triggers, after-parameter-form) next to the `.rex`. Queries cloned per sort/group variant collapse into one Blitz SQL; `FND FLEXSQL`/`FLEXIDVAL` user exits become `mtl_categories_b_kfv.concatenated_segments` predicates and columns.
- The dependency closure is PREFIX-driven; the prefix field takes a delimited list (`XX,XXC`) — ask for every prefix, and cross-check gathered SQL for object references without a `.sql` file. Objects named only in a value set's validation table are never collected, whatever the prefix: read `TABLE_NAME` in the `_vs_*.ldt` files and ask for those objects separately.
- `.ldt` from a newer EBS may not FNDLOAD on an older conversion instance.
- Wrapper + child (temp table + BIP child) programs convert to ONE report via `<DB_PACKAGE>` before/after-report triggers (`xxen_xdo.before_report` writes bind-anchor values into same-named package variables; `afterreport` cleans up). Warm per-row lookup caches set-based in `beforereport`.
- Noetix views: closure gathered into `noetix/`; transform catalogue (`#` wrapper views → base tables, ACL maps → `hr_operating_units`/`gl_ledgers`/MOAC, `xxk_gl_acct` → `gcck` + `&segment_columns`, `noetix_env_pkg.get_language` → `userenv('lang')`, drop `A$*`); parameters designed from the standard library.

## Value fidelity (the acceptance of every delivery)

- A converted report, and every correction of one, is delivered only after the value fidelity gate of the `migrate-custom-reports` skill (step 6, `references/value-fidelity.md`) passes. The parse, run, row-count, parameter and heading gates prove the report is well-formed, not that its values are the original's.
- Request one output of each original program from the customer's system, with its parameters, at engagement start. It is the arbiter for printed columns, headings, row grain and every value it lets you recompute. Our own earlier Blitz output is never a reference.
- A correction implements the original's logic for the field and cites the source line it follows. Never choose a date basis, a rate or a set operator by analogy with another report.
- A defect class found in one report is searched for in every report of the category before the round goes out.
- Before the first delivery, a second agent audits each report against the original's files (`references/fidelity-audit-prompt.md`), and every DIFFERENT or MISSING item is resolved before the file is sent.
- Without a reference output from the customer, run the original itself on the conversion instance where its tables have data: an RDF with `appsrwrun.sh` on the gathered `.rdf` (XML output), its RTF through the BI Publisher engine on that XML; a PL/SQL program as its entry procedure copied verbatim into a harness procedure that captures the output lines. Where the instance has no data, both sides read the same seeded stand-in rows. A port generated by script from the source lines proves more than a retyped one.
- A standard table of the conversion instance can lack columns the customer's release has (`jai_tax_det_factors.eway_bill_num` on 12.1.3): read it through a stand-in view in the instance copy of the XML only, the delivered XML keeps the table.

## Performance of a converted report (checked before delivery, not after the customer's first run)

- Measure a converted or changed report with a full fetch of every output column, old against new over the same window. A `count(*)` lets Oracle skip the select-list subqueries, so it hides exactly the per-row lookups a like-for-like conversion adds.
- A converted union of many branches that filled a temp table branch by branch must carry the run-screen filters INSIDE every branch, as the original's insert statements did: an outer `1=1` over the union is not pushed into branches that aggregate (`group by`, `distinct`) or join through inline views, so those scan their full source (a 34-branch GL drill-down took 20 minutes for 2,399 lines). The framework replaces every occurrence of an `n=n` anchor, so put the same `1=1` in each branch's where clause (inside the aggregating inline view where the filter columns live) and write the predicates on the branch aliases; `build_xml.py` takes the intended count as `REPEATED_ANCHOR: {'1=1': n}`.
- Before delivery, read the `#VALUE_SETS` definition of EVERY `LOV Oracle` parameter of the batch and replace any list that scans a transactional table or an ungathered customer view per open (`distinct` over `mtl_item_categories`, an on-hand view, a PO print view): the run screen executes a required list twice on open and once per list open. Take the values from the setup table instead (`mtl_categories_b` for the category set, `po_document_types_all_vl`, the shipped `INV Item (master)` list). A list over a setup table (categories, value sets, suppliers, organizations, FA books) is fine.
- Never carry over a customer value set whose list is a `distinct` over `gl_code_combinations` with a description function per row (`gl_flexfields_pkg.get_description_sql`): the run screen executes a required list parameter's query twice on open and once per list open, minutes each on 600,000 combinations. Replace it with a segment value list over the ledger's chart of accounts through `xxen_util.kff_segment_lov` (the select list of the shipped `GL Segment<n>` lists), keyed the same way the report resolves its ledger.
- A report that computes balances through correlated scalar sub-queries per row (an aging: applications and adjustments per bucket, 262 of them) is rewritten before delivery: enumerate the distinct sub-query shapes (normalise whitespace and count them), compute each as one aggregate per key (`group by` payment schedule / transaction / receipt, the as-of date inside), outer-join the aggregates and substitute the column into the original wrapper (`nvl(-1*...,0)`, `abs(...)`, bare `sum` stays nullable). Drive an as-of aging from open schedules (`aps.gl_date_closed>:p_as_of_date`); a schedule closed by GL date on or before the as-of date has a zero balance in any arithmetic that dates applications by `nvl(gl_date,apply_date)`. Prove it with an order-insensitive row diff and column sums against the previous version, not by reading the plan.
- A report whose selection is wide by design (a whole operating unit and category as of a date) reads `mtl_material_transactions` with one full scan hash joined to the listed items and lots, never per organization (N5) or per item (N1) through an index: without bind peeking the date range is estimated at 5% of the table, so the optimizer picks the index path, which takes hours on a customer table of ~50M rows while the full scan takes minutes; a small dev instance cannot show the difference. When an optional parameter makes the selection narrow (an item), switch the hint with a hidden lexical inside the hint comment, `/*+ leading(i mmt mtln) &mmt_access */` with SQL_TEXT `select nvl2(:p_item_code,'<index path hints>','<full scan hints>') from dual`; the engine substitutes a lexical anchor anywhere in the text, comments included.
- An `xmlagg` over a `select distinct` of a whole table inside a per-row scalar sub-query is aggregated once (group by the correlation key) and outer-joined; a `select distinct ... rownum=1` lookup drops the `distinct`.
- E2E harness scripts that several runs share must use per-run temp file names: two concurrent `_e2e.py` runs that shared `/tmp/_e2e_wait.sql` polled each other's request and downloaded the wrong output.

## UAT round bookkeeping

- A conversion, and every later correction, replicates what the original computes outside its main query, not only the query: RDF formula columns, placeholders assigned inside formulas, summaries a formula or the layout reads, format triggers that hide rows, group filters (a filter on a parent group suppresses every child query linked to it), BeforeReport/AfterPForm lexicals and derived binds, BI Publisher data triggers, and for a PL/SQL (execution method I) program every variable the loop writes to the output. A cursor column selected as a literal (`0 basic`, `NULL batch_source_name`) is a placeholder the loop fills later; shipping the literal leaves the column blank or zero for that branch. The field inventory (`${CLAUDE_PLUGIN_ROOT}/skills/migrate-custom-reports/field_inventory.py`) lists all of these per program; a row with no Blitz expression is an open defect, not a simplification.
- `UNION` in the original de-duplicates over its whole select list, including ids and `ROWID`s the layout never prints. Keep `UNION` where our branches carry an equivalent key; where they do not, add the original's key columns in an inline view and drop them in the outer select. Never switch between `UNION` and `UNION ALL` by analogy with another report: decide from the original's select list, because `UNION ALL` double counts join fan-out that the original collapses, and a narrower key merges rows the original keeps.