List of Bugs, Issues and Solutions Fixed in 12.2.7 & 12.2.8 Release

1.1 Fixed in 12.2.7 Release
These issues have been fixed in 12.2.7 RUP, 12.2.0 CUP8 or associated patches.
Bug No
Upgrade Stage
Upgrade Script
Other References
Issue
Solution
Fixed In
12.2.0 CUP
Both
aprevupd.sql
Long running
In particular SQLs:
axtd94rppwqyd insert into AP_UPG_ DISTRIBUTIONS_BACKUP …
br1hq563h9gfu update ap_inv_dists_source set reversal_flag = null, parent_reversal_id = null where invoice_distribution_id in …
(this is the last update ap_inv_dists_source SQL in the script).
Also any of other update ap_inv_dists_source statements (except 1st, 9th and 12th)
Modified one off script was provided in Bug 22264629.
But, as more customers were reporting issue, the Bug 23210291 was opened to provide a fix in standard code.
SQL to “insert into AP_UPG_ DISTRIBUTIONS_BACKUP” has been restructured and split up with more specific hints.
More specific hints added to most SQLs “update ap_inv_dists_source …”
Patch 23210291:R12.AP.C Included in 12.2.0 CUP8
11.5.10 to 12.2.0 CUP
asoatoci.sql
File was being run although it has been replaced by ASOUTOCI.SQL.
Stubbed out.
Patch 25063540:R12.ASO.C is included in 12.2.0 CUP8.
11.5.10 to 12.2.0 CUP
oklbpapu.sql
Long running. SQLs :
a3a31vj108dy4 SELECT … FROM OKL_CNSLD_AP_INVS_ALL CIN ,AP_INVOICES_ALL APINV
2w73jxazgpdn9 UPDATE /*+ index(APINV, AP_INVOICES_N6)*/ AP_INVOICES_ALL APINV
The script was using AD parallel incorrectly.
Changed structure of script so that selection by rowid is in the main c_apinv_csr loop rather than enclosed SQL. Also added hints to this cursor and changed bulkcollect cursor limit so that it includes all data in enclosed SQL (limit = 500000) rather than batch size.
Patch 24416066R12.OKL.C. Included in 12.2.0 CUP8.
11.5.10 to 12.2.0 CUP
oklbpdis.sql
Long running SQLs
SELECT tap.id tap_id ,tap.khr_id khr_id, tpl.id FROM okl_trx_ap_invs_all_b tap , okl_txl_ap_inv_lns_all_b tpl
UPDATE okl_txl_ap_inv_lns_all_b tpl
Script was not using AD parallel correctly.
Changed structure of script to selection by rowid is in main c_tap_sel loop rather than enclosed SQL.
Changed bulkcollect cursor limit so that it includes all data in enclosed SQL (limit = 500000) rather than batch size.
Patch 23854627:R12.OKL.C.
Included in 12.2.0 CUP8
11.5.10 to 12.2.0 CUP
oklbpdis3.sql
Long running SQLs
Cursor c_cin_csr
SELECT …. FROM okl_trx_ap_invs_all_b tap, okl_ext_pay_invs_all_b xpi, okl_xtl_pay_invs_all_b xlp, AP_INVOICES_ALL apinv …
INSERT INTO okl_cnsld_ap_invs_all …
Changed bulkcollect cursor limit so that it includes all data in enclosed SQL (limit = 500000) rather than batch size.
Changed structure of SQL in cursor c_cin_csr to use WITH clause for repeated SQL.
Patch 24363789:R12.OKL.C
Included in 12.2.0 CUP8
11.5.10 to 12.2.0 CUP
oklfkeyseq.sql
oklupgaccs.sql
Long running SQL:
UPDATE (SELECT /*+ LEADING(ASR) */ …. FROM OKL_ACCT_SOURCES_ALL ASR, …
Originally reported on 12.1.3 in Bug 24316903 
In Bug 24784652 the equivalent SQL (in OKLUPGACCS.sql) was long running. The leading and rowid hints had been put on the wrong table (asr).
Patch 24433010: Comment out this SQL in OKLFKEYSEQ.SQL.
Create new file (OKLUPGACCS.sql) using AD Parallel to run this SQL.
Patch 24784652: corrected hints (put on table upg).
Patch 24433010:R12.OKL.C
Included in 12.2.0 CUP8
Patch 24784652:R12.OKL.C,
Included in 12.2.0 CUP8
11.5.10 to 12.2.0 CUP
oklslaupg.sql
Long running due to SQL
b6ufpfddh652h INSERT INTO OKL_XLA_ACC_DATA_GT ….
Added leading(tcn) and cardinality(tcn,1) hints to encourage CBO to lead from table with rowid range scan and favour index/nested loop access.
Patch 24448683:R12.OKL.C
Included in 12.2.0 CUP8
11.5.10 to 12.2.0 CUP
okltxupg.sql
Long running SQLs.
Also a SQL Trace call.
Added rowid hint to SELECT part of INSERT SQLs (where rowid hint was not present previously).
Removed SQL Trace call.
Patch 24655291:R12.OKL.C.
Included in 12.2.0 CUP8
11.5.10 to 12.2.0 CUP
okssrepv1.sql
Long running due to SQL
Update /*+ rowid(lines) */ Okc_k_lines_b lines ….
Added hints:
nl_sj index(REL OKC_CONTRACT_REL_OBJS_N2)
to exists sub-query.
Patch 24570119:R12.OKS.C.
Included in 12.2.0 CUP8
12.2.n RUP
pat148.odf
pat149.odf
Long running:
Creating indexes, that should not be created at 12.2.n RUP level (but at 12.2.0 CUP level)
Patch 25658515:R12.PA.C includes the stubbed out replacements for xdfs (pa_cost_distribution_lines_n18.xdf, pa_cost_distribution_lines_n20.xdf, pa_expenditure_items_n35.xdf,
pa_expenditure_items_n36.xdf, pa_expenditure_items_n37.xdf, pa_expenditure_items_n38.xdf)
Patch 21523445:R12.PA.C contains full definitions in above files.
Patch 21523445:R12.PA.C Included in 12.2.0 CUP8
Patch 25658515:R12.PA.C Included in 12.2.7 RUP
12.2.0 CUP
pybthcon.sql
Long running due to SQL: cursor csr_input_values
SELECT …. FROM pay_element_types_f pet, pay_input_values_f piv WHERE ….
Altered so that uses simpler and more efficient SQL (in cursor csr_input_values0) when element_type_id is not null.
Bug 23586928 suggests purging old batches before upgrade :
1)delete from pay_batch_lines where batch_id in (select batch_id from
pay_batch_headers where BATCH_STATUS=’T’)
2)delete from pay_batch_headers where BATCH_STATUS=’T’
Patch 24293108:R12.PAY.C.
Included in 12.2.0 CUP8
12.2.0 CUP both
xlaaxuar_rcpt.sql
Long running SQLS:
1kw0fth2xjhyd MERGE … INTO XLA_AE_LINES XAL …
789t5a9f8vmx8 INSERT INTO XLA_DISTRIBUTION_LINKS …
Added hints.
Patch 25459089:R12.XLA.C
Included in 12.2.0 CUP8
12.2.0 CUP and 12.2.n RUP
cstpost importaad.sql other xxxpost importaad.sql scripts, where xxx is the application short name.
xlainaad.pkb
Long running cstpostimportaad.sql (and other xxxpostimportaad.sql scripts) due to SQLs:
UPDATE xla_product_rules_b prd SET compile_status_code = ‘R’ ….
UPDATE xla_product_rules_b prd SET compile_status_code = ‘N’ ….
Add leading hints to sub query in each SQL to ensure correct table order.
Patch 23540832:R12.XLA.C
Included in 12.2.7 RUP
Included in 12.2.0 CUP8 via Patch 23753553:R12.XLA.C
1.2 Fixed in 12.2.8 Release
Bug No
Upgrade Stage
Upgrade Script
Other References
Issue
Solution
Fixed In
12.2.0 CUP
adgrants.sql
Long running SQL
SELECT count(1) from dba_tab_privs where …..
Due to changes in view definition for dba_tab_privs in 12c
Added predicate for owner.
Change also made to adgrants_nt.sql
Fix in Patch 26612557:R12.AD.C.
Fix is also in Patch 27098139:R12.AD.C
Patch 27098139:R12.AD.C
Included in “ORACLE APPLICATIONS RELEASE 12.2: CPU PATCH FOR OCT 2017 Patch 26574498:12.2.0)” and later.
Also included in CONSOLIDATED PATCH ON TOP OF AD DELTA 10 Patch 27254132:R12.AD.C
12.2.n RUP
Online Patching
adzdmvb.pls
Downtime patching should use build deferred for MVs.
Changed to do this.
Patch 26720905:R12.AD.C
Included in CONSOLIDATED PATCH ON TOP OF AD DELTA 10 Patch 27254132:R12.AD.C
OPE
adzdprep.sql
adzdprpb.pls
Long running SQL (cursor C_COLUMNS in procedure ad_zd_prep.fix_columns)
fqwkb7v0xbwrf SELECT … FROM DBA_TABLES ATAB, DBA_TAB_COLUMNS ACOL WHERE ATAB.OWNER IN (SELECT … FROM SYSTEM. FND_ORACLE_USERID WHERE READ_ONLY_FLAG IN (‘A’, ‘B’, ‘E’, ‘U’, ‘C’)) …
Due to Cartesian join on X$KSPPCV which is referred in the dba_tables view.
Execute query for one owner at a time in loop to avoid Cartesian join.
Patch 26321002:R12.AD.C
Included in ORACLE APPLICATIONS DBA CONSOLIDATED UPGRADE PATCH 9 (CUP9) FOR R12.2.0 Patch 26924705:R12.AD.C
1220 CUP
afatch.odf
affdlredef.sql
afatch.odf was running long. It was using ALTER TABLE to convert long to clob.
Replaced with affdlredef.sql, which uses dbms_redefinition package (and forcing parallel execution at session level) to convert long to clob.
There is a further change to add alter session commands to disable parallel, otherwise SQLs in subsequent scripts (e.g. hrdrptrg.sql) get executed in parallel and can cause contention.
Patch 19504977:R12.FND.C
Included in 12.2.0 CUP9
12.2.0 CUP
asocolmg.sql
File was executing unnecessarily
This was because of file.sql.6 fix (remove hardcoded schema) (Patch 23019551:R12.ASO.C)
Added Checkfile equivalence tuple to restrict execution in 12.1.3 to 12.2 upgrade.
Patch 26304638:R12.ASO.C
Included in 12.2.0 CUP9
Included in “R12.1 and 12.2 Oracle E-Business Suite Preinstall Patches Report [Video] (Document 1448102.2)”
12.2.n RUP
b16312259.sql
File should be stubbed out for 12.2.n as indexes are now obsolete.
Stubbed out.
The script could be skipped as well.
Patch 26361142:R12.ICX.D
Included in 12.2.0 CUP9 and 12.2.8 RUP
Included in “R12.1 and 12.2 Oracle E-Business Suite Preinstall Patches Report [Video] (Document 1448102.2)”
12.2.n RUP
mtl_system _items_b _apply.sql
mtl_system_items_b_apply.sql was updating table in serial.
Stubbed out MTL_SYSTEM_ITEMS _B_APPLY.sql
Changed trigger definition in MTL_SYSTEM_ITEMS_B_X1.sql to specify dbdrv: sql ad patch/115/sql AD_ZD_TABLE_APPLY.sql (rather than MTL_SYSTEM_ ITEMS_B_APPLY.sql) so that gets processed by main 4CET (acet) code.
Patch 26362144:R12.ITM.D
Included in 12.2.8 RUP
12.2.n RUP
oe_int_ items_mv.xdf
oe_int_items_mv.xdf conatined long running creation of MV.
Serial. Not parallelized.
Parallel hints and clause added.
Patch 26746269 is correction to this and includes fixes to OEITORDB.pls to enable parallel DML.
Patch 26746269:R12.ONT.C
Included in 12.2.8 RUP
12.2.0 CUP Both
ont00068.sql
See bug. File was executing unnecessarily
This was because of file.sql.6 fix (remove hardcoded schema) (Patch 25821025:R12.ONT.C).
Added Checkfile equivalence tuple to restrict execution in 12.1.3 to 12.2 upgrade.
The CFE tuple fix in Patch 26304545 was not sufficient, so superceded with Patch 26861982.
Patch 26861982:R12.ONT.C
Included in 12.2.0 CUP9
11.5.10 to 12.2.0 CUP
ozfupter.sql
Long running due to SQL:
Cursor c_get_terr_name
SELECT … FROM jtf_terr_all JT, ozf_terr_levels_all JTT …
Join between tables JT and JTT is missing.
Added join.
Patch 26147440:R12.OZF.C
Included in 12.2.0 CUP9
Included in “R12.1 and 12.2 Oracle E-Business Suite Preinstall Patches Report [Video] (Document 1448102.2)”
12.2.n RUP
ozfuptrxtype .sql
ozfuptrxtype.sql long running due to SQL
SELECT … FROM ozf_claims_all cla, ozf_settlement_docs_all csd …
Not using any parallel method (e.g. AD Parallel or Parallel SQL).
Split the SQL cursor that drives the update (in loop) into 6 different cursors. Made the SQLs parallel and added leading and use_hash hints as well. Changed the loop to bulk collect and forall.
Patch 27159910:R12.OZF.C
Included in 12.2.0 CUP9
Included in “R12.1 and 12.2 Oracle E-Business Suite Preinstall Patches Report [Video] (Document 1448102.2)”
12.2.n RUP
Online Patching Validate
phase.pm
ADOP -validate is consuming large amount of memory due to infinite loop.
See Bug 24591000
AD Critical Patch 24591000:R12.AD.C
Included in CONSOLIDATED PATCH ON TOP OF AD DELTA 10 Patch 27254132:R12.AD.C
12.2.0 CUP both
qprunbld.sql
qpxpsrcb.pls
Long running SQL:
SELECT ‘Y’ FROM QP_LIST_LINES WHERE ((BREAK_UOM_CONTEXT = :B2 AND BREAK_UOM_ATTRIBUTE = :B1 ) OR (ACCUM_CONTEXT = :B2 AND ACCUM_ATTRIBUTE = :B1)) AND ROWNUM < 2
Inefficient execution plan (FTS), due to OR statement being evaluated on filter and separate conditions not using an index.
Added hint /*+ use_concat */
Patch 27019691:R12.QP.C
Included in 12.2.0 CUP9
12.2.0 CUP both
xlaaxuar.sql
Long running. Serial execution of one SQL (sub query)
Note that
1. There was a restructure of the code in Bug 22489152
2. Several bugs previously corrected code / added hints Bug 19191011, Bug 21128151, Bug 22842858.
These are still relevant and have been carried forward into Patch 27019025 (and 12.2.0 CUP8).
Add full hint to parallel hint
Patch 27019025:R12.XLA.C
Included in 12.2.0 CUP9
Included in “R12.1 and 12.2 Oracle E-Business Suite Preinstall Patches Report [Video] (Document 1448102.2)”
12.2.0 CUP both
wfdspifx.sql
Long running SQL:
update WF_USER_ ROLE_ASSIGNMENTS … where wura.ROLE_ORIG_SYSTEM_ID between :start_id AND :end_id
Inefficient execution plan (includes a FTS scan on WF_USER_ ROLE_ASSIGNMENTS).
There could also be contention.
This will be an issue for any customer with large number of rows on WF_USER_ ROLE_ASSIGNMENTS
Note that there were previous fixes in 12.2.0 CUP6 :
Patch 20655260:R12.OWF.C (Bug 19515151) use DBMS_PARALLEL_EXECUTE. run_task to execute chunks.
Patch 20894328:R12.OWF.C to enable parallelism (it was defaulting to 0 and running in serial).
Create index on WF_USER_ ROLE_ASSIGNMENTS( ROLE_ORIG_SYSTEM_ID)
Note that it should be local (as table is partitioned and SQL explicitly specifies the partition (UMX)).
Additionally, the number of rows in table WF_USER_ ROLE_ASSIGNMENTS can be reduced by purging adhoc roles.
It can also be purged with the “Purge Obsolete Workflow Runtime Data” concurrent program.
See “How To Reduce The Size Of Tables WF_LOCAL_ROLES, WF_LOCAL_USER_ROLES, And WF_USER_ ROLE_ASSIGNMENTS ? (Document 1454205.1)”
See Oracle Workflow Administrator’s Guide and Oracle Workflow API Reference.
Workaround.
11.5.10 to 12.2.0 CUP
wmsoptup.sql
Long running SQL:
UPDATE wms_op_plan_instances_arch wopia …
Due to unnecessary FTS on wms_op_plan_instances_arch.
Will be an issue for customer with high volume on this table.
Create an index on WMS_OP_OPERTN _INSTANCES_ARCH( OP_PLAN_INSTANCE_ID)
Possibly append OPERATION_SEQUENCE to make even more efficient.
and maybe SOURCE_TASK_ID, OPERATION_TYPE_ID to avoid visit to the table.
Workaround
11.5.10 to 12.2.0 CUP
xlabalupg.sql
Long running.
Runs serial in phase upg+99, alongside a handful of other jobs, and not fully parallelized (parallel DML/SQL).
Will be an issue if customer has high volume on XLA, especially balances.
Can be skipped and run post upgrade.
Can be skipped and run post upgrade.
 
  • July 3, 2019 | 55 views
  • Comments