Custom Reports Migration Rules Copy link

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:

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).

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 '→' exactly once, which the import decodes back to '. Pre-escaping the LOV text with ' double-escapes (&→&) and the import then stores the literal ' → 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.

Find the candidates for the master table the parameter picks from — run this for EVERY list-bearing parameter, and record the answer:

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:

  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:

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 ROWIDs 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.