EBS 11i Compatibility Copy link

Blitz Report ships to EBS 11i (11.5.10.2 / DB 11.2.0.4) from the SAME master content as R12 — every content change must consider 11i.

MANDATORY workflow for every report/upload change or creation

1. Does the report/upload have a 11i twin? (select report_name from xxen_reports_v where report_name='<name>'||' 11i', or ls content/11i/reports content/11i/uploads.) If yes, apply the equivalent change to the twin IN THE SAME TASK — never defer it, never treat it as optional (or explicitly record why it does not apply). If the 11i instance is down and the change cannot be verified there, say so in the task report.
   • Parse-verify caveat: an 11i twin's SQL may legitimately not parse on the development instance, so "framework parse fails on the development instance" is only meaningful COMPARATIVELY — import the unedited original and the edited version both to the development instance and confirm they parse/fail identically, then delete both test imports (xxen_api.delete_report(id,'Y')). Final execution verification happens on the 11i instance.
2. Dual-use content (Enginatics category, no twin, not R12 only) → the change must work on 11i. Verify the changed SQL/LOV/default EXECUTES on the 11i instance (see below). If the change needs an R12-only construct, add a translate_11i_sql rule that produces a valid 11i form, or split off a 11i twin.
3. New content → decide up front: dual-use (verify on 11i), R12 only (with evidence why), or a twin.
4. The upload's PL/SQL package: shared packages must compile on 11i; if the module has a _11I variant, mirror the change there.
5. Every DEFAULT VALUE must resolve to a value that EXISTS on 11i — report parameters, upload columns, template column defaults, template/user parameter defaults. A literal default frozen against the development instance's data executes fine and is silently discarded on 11i, leaving the field blank (see below). Executing the default is not enough; validate its VALUE against the target's LOV on the 11i instance.

E2E testing a backported 11i upload — the coverage contract

The generic scenario matrix (create / update / no-change / invalid LOV / missing required / delete, and the J1–J8 verification steps) in upload-development skill section J applies unchanged. Everything below is the 11i delta; none of it is reachable by the generic matrix alone.

Run every scenario through xxen_upload.submit_upload_process on an 11i instance (connect as described in your environment file (blitz-environment.md)). Calling the upload procedure directly proves nothing: it bypasses the generated block, which is where the alias, sentinel and column-mapping defects live.

Coverage axes — all five, or the test is incomplete

Run the idempotency axis first and trust it least: on 11i a fnd_api.g_miss_* sentinel reaching a plain table handler is STORED, the next download renders it (01-Jan-4712, chr(0)) and re-uploading re-poisons the row. Prove the cycle: create → download → re-upload unchanged → compare byte for byte.

Probes — run each deliberately, they are silent otherwise

Harness facts

• No MOAC on 11i — fnd_client_info.set_org_context(<org_id>), never mo_global.init.
• An API-driven download does NOT evaluate parameter defaults, so every required parameter must be passed explicitly — and that is exactly how a bad default hides. Supplying a value the product should have defaulted is not testing the default; test that path separately.
• Parameters and LOV cells take the DISPLAY value, not the code (Organization, United States — not ORGANIZATION, US).
• Dates are Excel serials. Build the XLSM per upload-development skill (shared strings only, cells sorted by column letter, MODIFIED_COLUMNS_ as Excel column positions, display values in Action/Status/Message).
• The result XLSM is authoritative for per-row Status/Message — not xxen_upload_data.column2.
• Read the request log with xxen_util.concurrent_request_file_url('L',<request_id>) (self-authenticating URL, fetch with curl); XXEN_REPORT_DEBUG='Y' at SITE prints the full generated upload_host block, which is how alias/column-mapping defects are proven.
• A row reported Success is not proof the entity changed — verify the target table.

Validation on 11i — parse is NOT enough

The framework parse gate and template-download tests do NOT execute parameter LOVs, column LOVs, default values, or value-to-id queries; those only run from the run screen / Excel add-in. A report can parse and download perfectly while every LOV errors for the user. "Error reading LOV value" messages in test output are real, never harness artifacts.

Execution check for every changed query (LOV, default, value-to-id) on the 11i instance:

declare
l_q clob; l_cnt number;
begin
  select xuc.lov_query into l_q from xxen_upload_columns xuc where ...;
  l_q:=regexp_replace(l_q,':\$(column|flex)\$\.[A-Za-z0-9_#]+(:null)?','null',1,0,'i'); -- flex tokens FIRST (incl. the framework-supported :null suffix)
  l_q:=regexp_replace(l_q,':[A-Za-z0-9_$#]+','null');
  execute immediate 'select count(*) from ('||l_q||') where rownum=0' into l_cnt;
end;

Known false-positive classes of crude bind-nulling (re-test with faithful typed substitution before "fixing"):

• FND value-set tokens :$flex$.x:null — replacing the flex token and :null separately yields the literal nullnull.
• Flattening newlines corrupts -- comments; keep line structure.
• trunc(null) parses as numeric TRUNC → false ORA-00932 against date columns; substitute a typed value (sysdate).
• Sequence NEXTVAL is illegal inside the count-wrapper subquery (ORA-02287) but legal in the framework's execute immediate ... into default evaluation.
• &lexical tokens need a plausible fragment, not null.

Old-client gotchas: set serveroutput on size 1000000 (not unlimited); dbms_output lines under 250 chars; order by <n> beyond the select-list count raises ORA-01785 on 11i.

The pre-12.2 splice path. xxen_report.max_column_length is 30 on databases before 12.2 and 128 from 12.2 on. On the 30-path the framework cannot embed dependent-LOV/value-to-id SQL with correlated references, so xxen_upload.replace_flex_binds splices sibling :$column$ cell values into the LOV text as literals at row level: varchar/Char/LOV values quote-safely (''||replace(xu.col,'''','''''')||''), dates via fnd_date.date_to_canonical, numbers via nvl(fnd_number.number_to_canonical(xu.col),'null') (never to_char — comma-decimal sessions render 3,5). It is DATABASE-version-driven: every pre-12.2 database is exposed, R12 customers included, and a 19c dev instance never reveals it. Diagnosis when a run aborts in value_to_id (ORA-00907/ORA-00936 from dbms_sql.execute): scan the run's xxen_upload_data column values for quotes/empties and map the hit columns against xxen_upload_dependencies.column_name — only columns referenced as SIBLINGS matter (a column's own value is passed as a bind). Substituting null for a bind cannot detect the empty-cell class (where null is not null parses); only the empty string reproduces it.

all_objects under a read-only account hides packages without EXECUTE grants (tables/views appear via broad SELECT grants, which makes the gap non-obvious). Verify a package's absence via dba_objects before concluding an API does not exist on 11i.

A DEFAULT VALUE can execute perfectly and still be INVALID on 11i

A default can parse, execute and return a value that does not exist in 11i's data; the framework silently discards it and the field renders blank. Nothing errors at download; the failure surfaces at the backend API naming something else entirely. Example: a Created By Module parameter defaulting to the literal TCA_V2_API (validate_from_list='Y', LOV over HZ_CREATED_BY_MODULES, which on 11i holds TCA_V1_API but not TCA_V2_API) → default discarded → the :$flex$.created_by_module column arrives blank → the HZ API rejects every row with "Column created_by_module must have a value". The error names a COLUMN, so it reads like a data-entry problem; hardcoding the value into the test rows masks the defect because an explicitly-set value never exercises the default.

The dangerous class is a hardcoded LITERAL (a query-based default derives from the instance's own data). Four stores: xxen_report_parameters.default_value, xxen_upload_columns.default_value, xxen_report_template_columns.default_value, xxen_report_param_default_vals.default_value (the last two validate against the column's / parameter's own LOV).

Mechanical check — for every literal default whose target has a validated LOV, assert the value is IN that LOV, on the 11i instance:

--per candidate: p_val = the literal default, p_lov = the target's lov_query
l_n:=0;
begin execute immediate 'select 1 from ('||p_lov||') x where x.id=:v and rownum=1' into l_n using p_val;
exception when no_data_found then l_n:=0; when others then l_n:=-1; end;   --value-only LOV has no x.id
if l_n<=0 then
  begin execute immediate 'select 1 from ('||p_lov||') x where x.value=:v and rownum=1' into l_n using p_val;
  exception when no_data_found then l_n:=0; end;
end if;
--l_n<=0 => the default can never be applied on this instance

Calibrate before believing a FAIL — a naive gate is mostly false positives:

Prove the gate both ways before trusting a clean result: feed it a known-bad value (TCA_V2_API → FAIL) and a known-good one (TCA_V1_API → ok).

R12 → 11i substitution catalogue (verified equivalents)

Verify every suspect object/column against the 11i dictionary (all_tab_columns, all_objects, all_arguments) — never assume.

ar_invoice_api_pub.create_invoice accepts only INV and DM transaction types on 11i, so an ON-ACCOUNT credit memo cannot be created there at all. ar_invoice_utils.validate_transaction_type stages AR_INVALID_TRX_TYPE ("Transaction type is invalid with current transaction date") for any header whose type is not in ('INV','DM'); the message names the date but the date is irrelevant, and the row is rejected with the rest of the batch created normally. R12 allows CM there, so the AR Transaction Upload creates on-account credit memos on r12 only -- an applied credit memo works on both, going through ar_credit_memo_api_pub. Do not mirror an on-account credit memo feature into the 11i twin: the code is unreachable.

Nothing on 11i writes the created ids back into the AR invoice GTTs -- ar_trx_header_gt.customer_trx_id and ar_trx_lines_gt.customer_trx_line_id stay null after create_invoice (R12 populates both). Any post-creation step that finds the created transaction through those columns is therefore dead code in an 11i twin, silently: the loop body never runs, or a select into on the null id raises ORA-01403. The twin's existing completion / shipment-detail / sales-credit blocks are in this category.

Read the results workbook, not the request log, when an upload row produces nothing. A row rejected by the invoice api is reported in ar_trx_errors_gt and surfaces as the row's Status and Message in the generated results XLSM; the request log shows only l_return_status: S and no created id, which reads like a code fault.

Fix style in _11I packages: minimal, each fix commented -- 11i:. Whole absent feature → raise_application_error(-20001,'<Feature> is not supported on EBS 11i.') — never half-working code. Missing SELECT column → to_char(null) <alias> keeping the alias.

Concurrent-program parameter lists can differ between 11i and r12, and fnd_request.submit_request binds POSITIONALLY — an r12-shaped argument list silently feeds every value into the wrong parameter from the divergence point on (the args map to the $SRS$.<PROGRAM> DFF segments ordered by column_seq_num). The eventual error (REP-0091: Invalid value for parameter ...) names the parameter that RECEIVED the junk, one slot past the divergence — diff the whole list on BOTH instances:

select fdfcu.column_seq_num, fdfcu.end_user_column_name, fdfcu.srw_param, fdfcu.required_flag, fdfcu.application_column_name
from fnd_descr_flex_column_usages fdfcu
where fdfcu.descriptive_flexfield_name='$SRS$.<PROGRAM>' and fdfcu.enabled_flag='Y' order by fdfcu.column_seq_num;

The Nth row is argumentN. The argument list is a real 11i-vs-r12 difference, so it belongs in the twin (_11I package), not a runtime fnd_release branch — a positional argument cannot be conditionally inserted mid-call. CSTRSCCRP (Supply Chain Cost Rollup) has an extra required P_GROUP_ID on 11i (cst_lists_s.nextval). all_sequences/all_objects hide no-privilege objects from a read-only account — confirm with dba_*.

Database-version traps (also hit R12 on an 11g database)

An /*+ inline */ hint on a hierarchical WITH block referenced twice kills the session on 11.2.0.4

The parse ends the connection (the client sees DPY-4011 / ORA-03113, no ORA error), while 19c parses and runs it. Restructure so that the connect by block is referenced once instead of hinting it. Parse every rewritten report on an R12.1.3 instance on an 11.2 database and an 11i instance before shipping; a 19c run proves nothing here.

standard_hash needs 12.1 — derive deterministic GUIDs from seeded ora_hash

standard_hash does not exist before database 12.1, so shipped code using it fails to compile on 11.2. A deterministic 16-byte GUID available on every release is four to_char(ora_hash(p_text,4294967295,<seed 0..3>),'fm0XXXXXXX') concatenated (xxen_eis.guid). Hash a VARCHAR2: unistr() returns NVARCHAR2, which hashes in the national character set and gives a different value per instance; VARCHAR2 text hashes identically on AL32UTF8 and UTF8 databases.

A VARCHAR2 bind into SQL is capped at 4000 bytes before 12c

Binding is the usual way past the 4000-byte literal cap (ORA-01704), and on 19c a 9,000-byte bind goes through. On 11.2.0.4 it does not: 4000 passes, 4001 raises ORA-01460: unimplemented or unreasonable conversion requested, in every context (where :b is not null, PL/SQL function argument, where col=:b). If the >4000 case must work on every release, split the value into ≤4000-byte chunks and OR one predicate per chunk — xxen_util.contains treats a plain a;b;c list like a <multiple_values>-prefixed one, so chunks need no prefix. Cut each chunk back to the last complete value (instrb(chunk,';',-1)) and verify by rebuilding the chunks byte-for-byte. For a multi-value LOV, 4000 bytes is roughly 360 item codes.

A varying bind set needs dbms_sql, and the predicate must be registered WITH its bind

Native dynamic SQL's USING list is positional and fixed at compile time, so a where clause built from "only the parameters that were entered" cannot bind through open ... for ... using. Use dbms_sql.parse → conditional bind_variable → execute → dbms_sql.to_refcursor (11.1+), which hands back an ordinary sys_refcursor; the cursor must have been executed, must not have had define_column called, and its dbms_sql id is invalid afterwards — never close it. Add the predicate and record its bind in ONE helper: mirroring the same if conditions in a second bind block ships ORA-01008 for the one parameter combination whose branch diverged. Exercise every branch in single and multiple value form. Binding also removes a hard parse per run.

Neutralising an R12-only column in a JOIN PREDICATE returns ZERO rows

Replace the select-list expression with to_char(null) / to_date(null) / to_number(null) keeping the alias. If the column also appears on the left of an outer join, delete the join and its table — never rewrite it as to_number(null)=alias.col(+): with no column on the left there is nothing to null-extend, it degenerates to NULL = col, and the whole result set collapses (identically on 11.2 and 19c). It survives every gate — valid SQL, unchanged column count, and zero rows look like "no data for these parameters". Detection is a text scan over the stored SQL, main SQL AND Success/Error SQL (neutralised independently, so normally both carry it):

regexp_like(line,'(to_number|to_char|to_date)?\(?null\)?[[:space:]]*=[[:space:]]*[a-z0-9_]+\.[a-z0-9_]+[[:space:]]*\(\+\)','i')

Bisect predicates cumulatively when a download returns zero rows — the first predicate that drops the count to 0 is the defect.

listagg(distinct ...) needs 19c, and a two-level correlation fails before it — both parse errors layer behind each other

The 11g suffix cannot cover listagg(distinct) (also fails on 12.x/18c); the MAIN definition must be version-agnostic. Both have the same fix — dedupe and correlate at ONE level:

--fails before 19c (ORA-30482)
(select listagg(distinct ail.tax_classification_code,', ') within group (order by ail.tax_classification_code)
from ap_invoice_lines_all ail where ail.invoice_id=aia.invoice_id and ail.tax_classification_code is not null)
--works everywhere: dedupe INSIDE the view, carry the key out, correlate OUTSIDE it
(select listagg(x.tax_classification_code,', ') within group (order by x.tax_classification_code)
from (select distinct ail.invoice_id, ail.tax_classification_code from ap_invoice_lines_all ail
where ail.tax_classification_code is not null) x
where x.invoice_id=aia.invoice_id)

It is not a performance trade — Oracle pushes the outer predicate into the DISTINCT view, keeps index access, and the rewrite is usually much cheaper. The 11i twin often already carries the correct shape — diff twin vs parent first and copy it.

• The parser reports only the FIRST error — fix and re-parse until clean, using the framework's own parse on an R12.1.3 instance on an 11.2 database as the verdict (it expands &lexical parameters the way a run does, works read-only since the packages are definer-rights, and returns the column count for an arity check):

  xxen_report.initialize_run_param_vals_tmp(l_report_id);
  l_msg:=xxen_report.initialize_parameter_values(l_report_id,'Y',null,'Y');
  if l_msg is null then l_msg:=xxen_report.parse_report_sql_columns(l_report_id); end if;  --'Error parsing SQL.' = broken there

• Proving a rewrite equivalent on real data: compare the values as VARCHAR2 (dbms_lob.substr(x,4000,1)), not with dbms_lob.compare (phantom differences on per-row temporary CLOBs). A real difference may be ORDER, not content — xmlagg without order by follows the plan; compare sorted token sets and add the missing order by.

ORA-01417: a table may be outer joined to at most one other table — pre-12c only, invisible on the development instance

Before 12c the table carrying (+) may reference at most ONE other table across all its join conditions. A normal r12 outer join like

cct_s.cost_type_id=crc.cost_type_id(+) and
mp.organization_id=crc.organization_id(+) and
br.resource_id=crc.resource_id(+)

parses on 19c and raises ORA-01417 on 11.2.0.4 at parse time — main SQL, Success SQL and Error SQL each independently. It is a DATABASE-version behaviour, so it also hits R12 customers on an 11.2 database (an R12.1.3 instance on an 11.2 database); the fix is one dual-use definition, never an 11g/ 11i variant.

Fix: wrap the DRIVING tables in an inline view so the outer-joined entity references exactly one alias. Do not reorder joins, add nvls, or convert to a scalar subquery (measured 15× more buffer gets):

from
(
select <the driving tables' columns, plus every join key the outer join needs>
from <all the inner-joined driving tables>
where <all their restrictions, including the 1=1 anchor>
) x, --11i: before 12c an outer joined table may reference at most one other table, so the driving tables are wrapped
<the outer-joined entity> e
where
x.key1=e.key1(+) and x.key2=e.key2(+) and x.key3=e.key3(+)

• Keep the 1=1/n=n parameter anchor INSIDE the inline view, with the aliases its SQL_TEXT predicates reference.
• Expose the join keys under distinct names (bd_department_id, br_resource_id) and the descriptor columns too, then reference them as x.<name> outside; anything still referencing an inner alias from the outer query is ORA-00904.
• In a Success SQL alias the inline view xu so every existing xu.<upload column> reference keeps resolving.
• e.rowid still works on the outer-joined entity, so an is not null presence test survives the wrap. A second entity outer-joined to only the first is fine outside.
• A KEYS-ONLY inline view is often enough — wrap only enough driving tables that the outer-joined alias sees one ((select pll2.line_location_id, pol2.item_id ... ) k joined on the key), which measures at parity with the original.
• Diagnostic signature on an UPLOAD: the upload succeeds and the RESULT report fails — the request log shows normal processing, then --- ERROR parsing SQL --- with ORA-01417 and the rendered Error SQL UNION ALL Success SQL. The reported line is the SECOND outer-join predicate of the offending alias — count the alias's predicates rather than trusting the quoted line.
• Counting select-list columns to verify a wrap: the content XMLs are CRLF, so a '\nfrom\n' scan never matches — normalise line endings first.

Verify a fix three ways, in this order:

1. Parse both shapes on a real 11.2 instance (an 11i instance, or an R12.1.3 instance on an 11.2 database) — old must raise ORA-01417, new must parse. Substitute a (select … from dual) stand-in for a missing xu view (the error counts join-graph aliases, not table types).
2. Prove row-for-row equivalence on the development instance, where the old shape still parses — both against the same stand-in data covering join hits, outer-join misses AND driving-join misses, MINUS in both directions (0/0).
3. Run the real upload E2E and confirm the result sub-request completes C/C with non-empty output, including at least one Error row so both UNION branches run.

A regex scan is not a substitute for parsing — it is wrong in both directions (aliases in different UNION branches look like violations; function-wrapped or line-split predicates hide real ones). The authoritative sweep: parse every stored SQL (xxen_reports.sql_text for type='U' plus xxen_upload_sqls.sql_text sql_type='Success SQL') on an R12.1.3 instance on an 11.2 database (connect as described in your environment file (blitz-environment.md), alter session set current_schema=apps), binds/lexicals neutralised (regexp_replace(sql,':\$?[A-Za-z0-9_$#.]+','null'), same for &\w+), dbms_sql.parse each; run the identical script on the development instance (19c) and classify only OK on 19c + ORA-01417 on 11.2 as a defect (the neutralisation produces incidental ORA-00933/923/920/907 noise on both, and can never create an outer join). Do not judge an 11i twin by its r12 namesake — separate reports, separate SQL. For the 11i bundle itself an 11i instance as apps is the reference target; under a read-only account without a grant on fnd_global substitute number literals for fnd_global.resp_appl_id/resp_id (they sit in almost every org_access_view predicate) — then an instance with real data is a full parse and execution target. A dual-use upload with no twin fixed on the development instance is fixed on 11i at the same time — check for a twin before assuming 11i-specific work. WITH clause names look like missing tables (ORA-00942) when scanning for absent objects.

Equivalence-testing an 11i twin needs a substitution, because neither instance runs both shapes (the old shape raises ORA-01417 on 11i, the twin's 11i-only objects do not exist on the development instance): replace xxen_gl_ledgers_v with (select gl.name, gl.ledger_id set_of_books_id from gl_ledgers gl) in BOTH shapes and run the MINUS on the development instance.

16 or more analytic listagg over the same window raise ORA-01467: sort key too long before 12.2

Oracle evaluates every analytic function that shares one over (partition by ...) and within group (order by ...) in a single WINDOW SORT, and on 11.2 and 12.1 that sort rejects the 16th listagg whatever the data volume (15 pass; a listagg with a different order by lands in its own sort and does not count). 12.2+ has no such limit, so a 19c instance never shows it.

Write such a merge as aggregate listagg(...) within group (order by ...) with group by on the constant columns instead of select distinct over analytic listaggs; 30 aggregate listaggs run on 11.2. A pre-12.2 database (a 12.2.4 instance, 11.2.0.4) is the only proof; the equivalence proof is the byte-identical output of the two shapes on the development instance.

The 11i apps tier runs SQL*Plus 8.0.6 and an old FNDLOAD

• NLS_LANG=AMERICAN_AMERICA.AL32UTF8 kills the 8.0.6 client outright (ORA-12705: invalid or unknown NLS parameter value specified): the connect fails and nothing runs. UTF8, WE8ISO8859P1, US7ASCII work; the installer's default is American_America.UTF8.
• A concurrent request carries the submitting session's NLS territory into the .prog's NLS_LANG, and an 11i instance's 8.0.6 client rejects territory INDIA: a request submitted from SQLcl on an Indian-locale Windows client fails with ORA-12705 before the report starts (completion text Program exited with status 1). Start a script that submits 11i requests with alter session set nls_territory='AMERICA';.
• set serveroutput on size 1000000 (not unlimited); put_line under 250 chars.

Environment / mechanics (credentials in your environment file (blitz-environment.md))

• Compile with alter session set plsql_ccflags='is_11i:1'; + set define off — also for every later alter package ... compile of an XXEN object: XXEN_UTIL's body branches on $$is_11i, so a recompile without the flag (after a spec change, or a dependency-driven recompile loop) fails with ORA-00942 on an R12-only table and leaves the whole install invalid until it is recompiled with the flag; import via $XXEN_TOP/bin/import_xml apps <file> (derives update_upload_dependencies + update_parameter_dependencies automatically).
• CLOB export from old instances: char-wise base64 chunking truncates silently at multibyte characters. Byte-safe: dbms_lob.converttoblob first, then chunk the BLOB (or utl_file put_raw 'wb').
• 11i Vision data differs from the development instance: org M1=207 does not exist; AR periods, WIP calendar etc. differ — always query for valid test values.