This content has been marked as final. Show 1 reply
My problem got resolved.
I thought of sharing the solution
First of all the main reason for the cause was we had one more development apex environment and we moved the app from that environment to
prod, it made the user saved ir reports got deleted, even though the app no(200) is same as in prod app no(200).
The way i recovered the orphaned user saved ir reports ,was I need to update the worksheet id in one of the apex meta data tables.
I was able to reproduce the issue in test environment and applied the below fix it worked and later applied the fix in production, we did not see any kind of issues.
Run the below steps as SYSTEM user
-- create a temporary table
create ir_user_old_reports_backup as SELECT * FROM APEX_040000.WWV_FLOW_WORKSHEET_RPTS where FLOW_ID = 200 and
and IS_DEFAULT = 'N' and SESSION_ID is null and STATUS = 'PRIVATE'
and WORKSHEET_ID NOT IN (SELECT ID FROM APEX_040000.WWV_FLOW_WORKSHEETS WHERE FLOW_ID = 200 )
-- Run the below pl/sql block
for c1 in (SELECT id, page_id FROM ir_user_old_reports_backup )
-- Get the new worksheet id to link to orphaned user saved ir reports
for c2 IN (select id from APEX_040000.WWV_FLOW_WORKSHEETS where FLOW_ID = 200 and PAGE_ID = c1.PAGE_ID)
-- update the new worksheet id to link orphaned user saved ir reports
update APEX_040000.WWV_FLOW_WORKSHEET_RPTS set WORKSHEET_ID = c2.id where id = c1.id;
Note: Oracle does not provide any kind of support or assistance if you make any kind of changes to APEX meta data tables.