Dear Community,
We encounter an issue during the IFS Installation in our upgrade project:
We are upgrading from IFS 8 to 10 following steps are followed for the upgrade.
- Take backup of PROD (Oracle 11g)
- Import this export to the new App10 DB (Oracle 19c)
- Complete the DB upgrade and fix any SYS invalids.
- Run IFS Upgrade on the upgraded DB.
The issue is:
The process gets hanged on the step 'Component DOP is ready'.
A SELECT Statement as per the screenshot attached keeps on running for more than 15hrs and after this is complete, installer does not continue beyond this point.
There are no error logs created as well.
If you have any fresh ideas what we can investigate that would be really appreciate it.
Thank you very much for any ideas or thoughts!
Kind regards,
Balazs
This is the stuck query:
DECLARE
CURSOR get_prjrep_connections IS
SELECT
*
FROM
(
SELECT
pt.activity_seq activity_seq,
'PTR' proj_lu_name,
pt.activity_seq keyref1,
pt.company_id keyref2,
pt.currency_code keyref3,
to_char(pt.account_date, 'YYYYMMDD') keyref4,
'*' keyref5,
'*' keyref6,
'TRUE' system_ctrl_conn,
activity_api.get_description(pt.activity_seq) object_description,
fnd_session_api.get_fnd_user created_by,
sysdate date_created,
sysdate rowversion,
pt.account_date last_transaction_date
FROM
project_transaction_tab pt,
prel_invoice_posting_tab pip,
project_connection_tab p
WHERE
pt.project_transaction_seq = pip.project_transaction_seq
AND pt.invoicability != '2'
AND pip.posting_type IN ( 'PRJI1', 'PRJI2' )
AND pip.prjrep_posting_status != 'ERROR'
AND p.proj_lu_name = 'PTR'
AND pt.activity_seq = p.activity_seq
AND nvl(account_api.get_exclude_proj_followup(pt.company_id, pip.code_a),
'FALSE') = 'FALSE'
GROUP BY
pt.activity_seq,
pt.company_id,
pt.currency_code,
pt.account_date
UNION ALL
SELECT
pt.activity_seq activity_seq,
'PT' proj_lu_name,
pt.activity_seq keyref1,
pt.company_id keyref2,
to_char(pt.account_date, 'YYYYMMDD') keyref3,
'*' keyref4,
'*' keyref5,
'*' keyref6,
'TRUE' system_ctrl_conn,
activity_api.get_description(pt.activity_seq) object_description,
fnd_session_api.get_fnd_user created_by,
sysdate date_created,
sysdate rowversion,
pt.account_date last_transaction_date
FROM
project_transaction_tab pt,
project_trans_posting_tab ptp,
project_connection_tab p
WHERE
pt.project_transaction_seq = ptp.project_transaction_seq
AND p.proj_lu_name IN 'PT'
AND pt.activity_seq = p.activity_seq
AND ptp.prjrep_posting_status != 'ERROR'
AND ptp.posting_type IN ( 'PRJT1', 'PRJT3', 'PRJC1', 'PRJC3', 'PRJT5' )
AND pt.supp_invoice_id IS NULL
AND pt.supp_invoice_no IS NULL
AND pt.report_cost_type IN ( '1', '2' )
AND ptp.company_id = pt.company_id
GROUP BY
pt.activity_seq,
pt.company_id,
pt.account_date
) prjrep
WHERE
NOT EXISTS (
SELECT
1
FROM
project_connection_tab pc
WHERE
pc.activity_seq = prjrep.activity_seq
AND pc.proj_lu_name IN ( 'PT', 'PTR' )
AND pc.proj_lu_name = prjrep.proj_lu_name
AND pc.keyref1 = to_char(prjrep.keyref1)
AND pc.keyref2 = prjrep.keyref2
AND pc.keyref3 = prjrep.keyref3
AND pc.keyref4 = prjrep.keyref4
AND pc.keyref5 = prjrep.keyref5
AND pc.keyref6 = prjrep.keyref6
);
TYPE prjrec_connection_ IS
TABLE OF get_prjrep_connections%rowtype INDEX BY BINARY_INTEGER;
prjrec_connection_tab_ prjrec_connection_;
bulk_limit_ NUMBER := 10000;
start_time_stamp_ DATE;
finish_time_stamp_ DATE;
BEGIN
start_time_stamp_ := sysdate;
OPEN get_prjrep_connections;
LOOP
FETCH get_prjrep_connections
BULK COLLECT INTO prjrec_connection_tab_ LIMIT bulk_limit_;
IF ( prjrec_connection_tab_.count <> 0 ) THEN
FORALL i_ IN prjrec_connection_tab_.first..prjrec_connection_tab_.last
INSERT INTO project_connection_tab (
activity_seq,
proj_lu_name,
keyref1,
keyref2,
keyref3,
keyref4,
keyref5,
keyref6,
connection_seq,
object_description,
system_ctrl_conn,
created_by,
date_created,
rowversion,
last_transaction_date
) VALUES (
prjrec_connection_tab_(i_).activity_seq,
prjrec_connection_tab_(i_).proj_lu_name,
prjrec_connection_tab_(i_).keyref1,
prjrec_connection_tab_(i_).keyref2,
prjrec_connection_tab_(i_).keyref3,
prjrec_connection_tab_(i_).keyref4,
prjrec_connection_tab_(i_).keyref5,
prjrec_connection_tab_(i_).keyref6,
project_connection_seq.NEXTVAL,
prjrec_connection_tab_(i_).object_description,
prjrec_connection_tab_(i_).system_ctrl_conn,
prjrec_connection_tab_(i_).created_by,
prjrec_connection_tab_(i_).date_created,
prjrec_connection_tab_(i_).rowversion,
prjrec_connection_tab_(i_).last_transaction_date
);
COMMIT;
END IF;
EXIT WHEN get_prjrep_connections%notfound;
END LOOP;
CLOSE get_prjrep_connections;
finish_time_stamp_ := sysdate;
--The following code is added to overcome the requirement to run a refresh after an upgrade (for apps10)
--This will create connection details for the project connections without connection details
project_conn_refresh_util_api.refresh_project_connection(start_time_stamp_ => start_time_stamp_, finish_time_stamp_ => finish_time_stamp_
);
COMMIT;
END;