APPS.BOM_DIAGUNITTEST_RTGHLCHK SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 18

 sqltxt		VARCHAR2(9999);  -- SQL select statement

Line: 32

JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;

Line: 46

		select 1 into l_org_exists
		from   mtl_parameters
		where  organization_id=l_org_id;

Line: 67

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 )					';

Line: 107

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.

';