--- name: eis-migration description: Reviewing and finishing EIS eXpress Reporting reports converted to Blitz Reports. Use when working on EIS-converted reports. user-invocable: false --- # EIS eXpress Reporting (XXEIS) to Blitz Report migration ## Architecture - Migrated reports get category `EIS eXpress` plus the EIS application name, nothing else. Decision: no category option in the importer; a category is renamed afterwards if needed. ## EIS runtime rules the importer mirrors - Main object source: `eis_rs_reports.paste_view_sql` when not null, else `eis_rs_views.view_sql` when `object_type='SQL'`, else the database object by name with the owner resolved from the dictionary (`view_owner`, then APPS, then XXEIS: EIS registers views under XXEIS that exist only in APPS). The database view text is authoritative; its `view_sql` is a stale snapshot, used only when the view is missing. - Every component row of `eis_rs_report_views` is joined, used by a column or not. Joins are `eis_rs_view_component_joins` of (main view, component) with `enabled` null or `Y`, rendered ` `; `value1` carries the `(+)`; the alias is `eis_rs_view_components.alias_name`. - Columns: `enabled_flag='N'` is not selected but calculations may reference it; `display_flag='N'` IS selected, so it sets the grain of DISTINCT and GROUP BY reports. `group_function` counts only with `group_by_flag='Y'`. - Conditions: skip headers with `enabled='N'` and details with neither item nor value. A SIMPLE condition is omitted when its parameter is empty; an ADVANCED template (`1#$# and 2#$#`) turns an empty-parameter detail into `1=1` and is omitted when all are empty; FREE_TEXT always applies, an empty parameter as `''`. `value2*` counts only for BETWEEN. The operator comes from the condition detail, not `eis_rs_report_parameters.operator_name`. - EIS substitutes parameters as literals everywhere (conditions, free text, calculations, sorts, triggers), referenced as `:` with spaces; dates as `'DD-MON-YYYY'`; `:SYSTEM.PROCESS_ID` is the run's `eis_rs_processes.process_id`. LIKE appends `%`; no `trunc` is added to date columns. Strip comments before scanning for binds (paste SQL has commented-out binds); `:MI`/`:SS` in date masks are not binds. - Getters: zero-argument `.get_`, `eis_get_` or `g_` read a package global a report trigger sets: `set_(...)`, `eis_set_(...)` (named arguments by `dba_arguments` position), `g_:=` or `.:=`. Some read another global or transform it (`getter`: `get_ledger_name` reads `g_ledger`, `get_age_div` is 1 for zero or null), a few have a fixed value when no trigger sets them (`unset_value`). - EIS returns nothing where a getter has no setter in its report, or `get_lookup_meaning` has no application. The importer then takes the one parameter the other reports' setters assign, when the report has it, and the lookup type's only application, and adds a migration note. ## Importer shapes and rewrites - Flatten a single query block without set operators, WITH, DISTINCT, aggregation, analytics, ROWNUM, CONNECT BY, ANSI joins, hints or `select *`: `.` becomes the view's select expression, view aliases colliding with report or component aliases are renamed `_v`, unqualified view columns are qualified against the view's own tables. Otherwise Wrap, `from () `, pruned to the referenced columns unless DISTINCT, GROUP BY or set operators. Reference for a SYNONYM or TABLE. - Flatten falls back to Wrap only when Flatten does not parse and Wrap does on the generating instance, so the shape depends on where the import runs. - Rewrites, token-aware, in source, calculations, conditions, free text, sorts, LOV queries and SQL defaults: - `exists (select 1 from xxeis.eis_org_access_v where organization_id=)` to ` in (select oav.organization_id from org_access_view oav where )`; any other use to that inline view. - `eis_gl_security_pkg.validate_access` to `gl_security_pkg.validate_access` (named `sob_id=>` becomes `p_ledger_id=>`). - `eis_gl_security_pkg.validate_segment_value('SEGMENTn',.flex_value)='TRUE'` (EIS GL segment LOVs) to `(nvl(,'N')='N' or xxen_util.flex_value_accessible(...,.flex_value_set_id,.flex_value)='Y')`, the flag being the highest `security_enabled_flag` of the GL segments in that column on that value set. Decision: EIS's extra access set segment check is dropped. The generated LOVs need a build with the 4-argument function. - EIS functions with a standard equivalent become that equivalent from the table in `template` (`#n` is the nth argument): lookups, user names, names by id, account strings, transaction source numbers, WIP quantities, GL period numbers, item categories where the category set allows one category per item. Where EIS reads an org-striped view (`po_headers`, `ap_tax_codes`) the `_all` equivalent also shows other operating units' rows. `decode_vset(.segmentN,...)` becomes `xxen_util.segment_description`. - A scalar subquery equivalent is not used as an outer join operand (`ORA-01799`), in a group by it becomes the call's non-literal arguments (`ORA-22818`), its aliases are renamed when an argument uses them, and it is skipped when an unqualified argument column exists in its tables. - XXEIS-owned objects referenced unqualified in XXEIS view text get `xxeis.`; synonym `EIS_RS_UTILITY` is package `XXEIS.EIS_RSC_UTILITY`, which has no APPS synonym. XXEIS views other than `EIS_RS_%` are inlined to depth 5, XXEIS synonyms of other schemas' objects become their target, `xxeis.xx_` becomes `xx_` once an APPS package of that name exists. - Decision: any other XXEIS object stays qualified `xxeis.` and sets tier `XXEIS Code`; the report works while XXEIS exists. - Remove the EIS Copyright column (also from GROUP BY) and the flexfield columns between the `--descr#flexfield#start/end`, `--kff#start/end` and `--gl#accountff#start/end` markers. - Tiers: `Clean`, `Rewritten` (a rewrite above reaches the main SQL or a parameter predicate), `XXEIS Code`, `Trigger` (needs the trigger package), `Not Supported` (no source SQL). - DISTINCT and GROUP BY reports sort by column alias; GROUP BY reports drop sorts on aggregated columns (EIS ignores them) and never take over the view's ORDER BY. Conditions and sorts on vanished view columns are dropped with a note. ## Columns, parameters and LOVs - Column alias: the plain lowercase name when `xxen_report.column_translation` gives back the EIS display name, else the quoted display name. - Parameters keep the EIS names with generated binds of at most 30 bytes, typed after the database type of the filtered column, never EIS `data_type`; unused ones are left out, hidden ones get a negative display sequence. A condition parameter sits on an `n=n` anchor; one used only by free text or a calculation is a bind anchor. - Decision: date "to" conditions are `col<:to+1` (a superset of EIS `<=`), LIKE stays `col like :b||'%'`. A date "to" gets a default only where EIS had one: EIS users and schedules run with only a From date, so never add `last_day(:$flex$.)`. - Defaults: a CONSTANT loses its quotes, an in-list `'a','b'` becomes a multi-value default, `select f() from dual` becomes `f()`. - LOVs: `select x. value, x. description from () x`, value-set LOVs as `LOV Oracle`. EIS LOV names repeat with different queries: identical duplicates share one LOV, a differing one gets its lowest LOV id appended. `eis_rs_dependant_param_maps` (`parameter_id` child, `dependant_param_id` parent, `column_name` of the child's LOV) becomes a `:$flex$` restriction in the child's LOV (`xxen_util.contains` for a multi-value parent). - DFF: `&dff_columns` only in Flatten reports, for the aliases in a populated `--descr#flexfield` block (`add_dff_flag` is unreliable) whose `.rowid` and attribute columns parse; prefixes unique per alias. - GL: `##` becomes `.segmentN`, `#DESCR` `xxen_util.segment_description` (parent segment for a dependent value set). `show_gl_segments` columns become a `Show GL Segments` parameter on a `&gl_segments` lexical over the responsibility's charts of accounts, using the view's GL block alias, a code combination component or an added outer join on the wrapped view's `code_combination_id`; without one the column is only flagged. ## Trigger reports - Setter-only triggers feed the getter rewrite; cleanup-only after-triggers of such a report are dropped, and so are triggers calling `get_column_updt`, whose EIS headings the `USAGE_PERIOD_nn` columns take. A re-import of a report no longer tier `Trigger` drops its package. Any other trigger makes the report tier `Trigger` with the generated package `XXEN_EIS_R` as `DB_PACKAGE` (`trigger_package_ddl` scripts it). - It relies on the `DB_PACKAGE` rules in `report-development` skill. `beforereport` moves the parameter variables into a private `run` record, sets public `eis_process_id` from `xxeis.eis_rs_processes_s.nextval` only when the SQL or a trigger reads `:SYSTEM.PROCESS_ID` (shared output tables such as `eis_rs_common_outputs` are keyed on it), and calls each EIS trigger as a local procedure, since a `return;` in an EIS trigger ends only that trigger. - Trigger references are package-qualified (`xxen_eis_r.run.`) so a column or local of the trigger cannot capture them. - `:SYSTEM.PROCESS_ID` becomes `:eis_process_id`, bound from the package; it and each sequence bind carry a hidden `Number` parameter without value. Parameters the triggers read are bound directly, so their conditions become static `(:b is null or ...)`. - The import warning shows only the first `user_errors` line of a trigger package; on a dev instance that is usually a missing customer object hiding other errors, so read them all. ## Identity and re-import - GUIDs derive from names (`xxen_eis.guid`, see `ebs-11i-compatibility` skill): report `'EIS eXpress report '||name`, templates from report and pivot name, LOVs `'EIS eXpress LOV '||name`. EIS report ids are instance-local (a clone diverges from production): never derive identity from them. - The description ends with the line `EIS Report ID: `, which the analysis reports match anywhere in the description, preferring the Blitz report named like the EIS report, else the oldest. The id and the package names belong to the generating instance: ship the package script from the same run as the XML. - `import_xml` forks a report whose name changed on an existing GUID, so a re-import keeps the first import's Blitz name, and an EIS rename creates a new Blitz report. - The batch skips existing GUIDs unless `Replace Existing`, which overwrites hand edits of parameters, categories and assignments but not hand-made templates. ## Migration process - Scope: enabled reports run within a window, seeded or not, never application 85000 (EIS administration); on a clone, anchor usage windows at the clone date. A blank `Executed within Days` imports every such report, used or not. - Run `Blitz Report EIS Import` at the customer, or on a replica refreshed just before: shape, DFF, getter and category-set decisions and value-set LOVs (stored by `value_set_id`) depend on the generating instance. Assignments come only from a run at the customer or from EIS Report Security loaded through `Blitz Report Assignment Upload`; responsibilities users run EIS under need the `XXEN_REPORTS` function. - Afterwards check EIS Import Validation and recreate the schedules EIS Schedules lists. ## Known limitations - Parameters used in static SQL (free text, multi-detail advanced conditions, calculations) or read by triggers take one value; the report description lists them. - GL account parameters with `expand_gl_parameter='Y'` stay one parameter on the account-string column; EIS expanded them per segment. - 19c raises `ORA-00932: expected NUMBER got DATE` at parse when an OR contains `trunc()` of a date column of an outer-joined MOAC view (`po_releases`); `no_expand` does not help. Fix such reports by hand: compare the raw date, or join the `_all` table. - `xxen_util.user_name` appends the user description unless profile `XXEN_REPORT_SHOW_USER_DESCRIPTION` is `N`, so rewritten user-name columns differ from EIS. - EIS compares literals, so a parameter whose EIS type disagrees with its column (a date parameter on a VARCHAR2 column) works there; Blitz types it after the column, since a typed bind would convert the column. ## Validation technique - On a read-only customer clone validate by parse only, in a time-limited read-only transaction: `select * from () where 1=0`, generated on the dev instance (`test_sql` fills in every parameter predicate and binds typed nulls). Never `count(*)`, which leaves the inline view's select list and ORDER BY unparsed; `where 1=0` does not prevent every execution (TEMP can run out). - Never run on a read-only instance: PL/SQL, `fnd_global.apps_initialize`, `eis_rsc_process_reports.get_view_sql` or `xxeis.eis_gl_security_pkg.init_segval` (both write rows). EIS `validate_access` returns FALSE for every row until `init_segval` ran in the session, so compare the rewrite only on a writable replica. - The installed EIS release is `xxeis.eis_rs_version_control`: one row per module and patch, the engine as `module='Base engine'` (`version_number`, `patch_number`, `release_date`, `applied_date`). `EIS Overview` reports it. - Older EIS releases lack `eis_rs_report_views` (one view per report in `eis_rs_reports.view_id`, components only through the columns' and parameters' `view_component_id`), `eis_rs_report_columns.display_flag`, `eis_rs_lovs.value_set_id`, `eis_rs_report_security.report_set_id`, `eis_rs_view_columns.calculation_column` and the FTP distribution columns. `XXEN_EIS` does not compile there (the compile lists only the first missing column), and of the analysis reports only `EIS Overview`, `EIS Usage Summary`, `EIS Users`, `EIS Execution Summary` and `EIS View Dependencies` run.