The following lines contain the word 'select', 'insert', 'update' or 'delete':
sqltxt VARCHAR2(9999); -- SQL select statement
JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
select 1 into l_org_exists
from mtl_parameters
where organization_id=l_org_id;
sqltxt := ' select mif.item_number "Item Number", mp.organization_code "Organization Code", '||
' mrir.process_revision "Routing Revision", '||
' to_char(mrir.effectivity_date,''DD-MON-YYYY HH24:MI:SS'') "Effectivity Date", '||
' to_char(mrir.implementation_date,''DD-MON-YYYY HH24:MI:SS'') "Implementation Date", '||
' mrir.change_notice "ECO Number", '||
' mif.inventory_item_id "Item Id", mrir.organization_id "Organization Id", '||
' mrir.revised_item_sequence_id "Revised Item Seq Id" '||
' from mtl_item_flexfields mif, mtl_rtg_item_revisions mrir, mtl_parameters mp '||
' where mif.inventory_item_id=mrir.inventory_item_id '||
' and mif.organization_id =mrir.organization_id '||
' and mp.organization_id =mif.organization_id '||
' and mrir.process_revision is not null '||
' and mrir.change_notice is null '||
' and mrir.revised_item_sequence_id is null '||
' and not exists '||
' (select 1 from bom_operational_routings bor '||
' where bor.assembly_item_id=mrir.inventory_item_id '||
' and bor.organization_id =mrir.organization_id ) ';
Please follow below steps to delete these routing revision records.
(1) Take a backup of entire mtl_rtg_item_revisions table.
(2) Use the below script to delete these orphan records.
delete from mtl_rtg_item_revisions
where inventory_item_id in
(select mif.inventory_item_id
from mtl_item_flexfields mif,
mtl_rtg_item_revisions mrir
where mif.inventory_item_id=mrir.inventory_item_id
and mif.organization_id =mrir.organization_id
and mrir.process_revision is not null
and mrir.change_notice is null
and mrir.revised_item_sequence_id is null
and not exists
(select 1 from bom_operational_routings bor
where bor.assembly_item_id=mrir.inventory_item_id
and bor.organization_id =mrir.organization_id )
);
Line: 127
(3) Make sure that the total number of records deleted
are same as the number of records fetched above.
(4) Commit the transaction.
';