eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BOM_DIAGUNITTEST_BILLHLCHK SQL Statements

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

Line: 18


 sqltxt		VARCHAR2(9999);  -- SQL select statement

Line: 34


JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;

Line: 73


		select 1 into l_org_exists
		from   mtl_parameters
		where  organization_id=l_org_id;

Line: 114


   sqltxt :=	   '  	 select mif1.padded_item_number 	 			"Assembly Item Number",		     '||
		   '	 	mp.organization_code   	 				"Organization Code",		     '||
		   '	 	bsb.alternate_bom_designator 				"Alternate Bom Designator",	     '||
		   '	 	bcb.operation_seq_num	 				"Operation Seq Num",		     '||
		   '	 	bcb.item_num			 			"Item Seq Num",			     '||
		   '   	 	mif2.padded_item_number	 				"Component Item",		     '||
		   '	 	decode(bcb.from_end_item_unit_number,null,''*MISSING'', 				     '||
		   '	 	       bcb.from_end_item_unit_number||'' (Invalid)'') 	"From End Item Unit Number",  	     '||
		   '   	 	bcb.to_end_item_unit_number||'' (Invalid)''  		"To End Item Unit Number",	     '||
		   '	 	bcb.component_quantity	 	    			"Component Quantity",		     '||
		   '	 	to_char(bcb.effectivity_date,''DD-MON-YYYY HH24:MI:SS'') "Effectivity Date",		     '||
		   '	 	to_char(bcb.disable_date,''DD-MON-YYYY HH24:MI:SS'')	"Disable Date",			     '||
		   '	 	bcb.change_notice		 		 	"Change Notice",		     '||
		   '	 	bsb.assembly_item_id	 		 		"Assembly Item Id",		     '||
		   '	 	bsb.organization_id		 		 	"Organization Id",		     '||
		   '	 	bsb.bill_sequence_id	 		 		"Bill Sequence Id",		     '||
		   '	 	bcb.component_sequence_id	 	 		"Component Sequence Id"  	     '||
		   '	 from   bom_components_b bcb, bom_structures_b bsb,	  			     '||
		   '	 	   mtl_item_flexfields mif1, mtl_item_flexfields mif2,		  			     '||
		   '	 	   mtl_parameters mp						  			     '||
		   '	 where  bsb.bill_sequence_id = bcb.bill_sequence_id			  			     '||
		   '	 and    bsb.assembly_item_id = mif1.inventory_item_id			  			     '||
		   '	 and    bsb.organization_id  = mif1.organization_id			  			     '||
		   '	 and    bcb.component_item_id = mif2.inventory_item_id			  			     '||
		   '	 and    bsb.organization_id  = mif2.organization_id			  			     '||
		   '	 and    mif1.organization_id  = mp.organization_id			  			     '||
		   '	 and    mif1.effectivity_control = 2					  			     '||
		   '	 and    (   ( bcb.from_end_item_unit_number is null 						     '||
		   '	 	      or 										     '||
		   '	 		 ( bcb.from_end_item_unit_number is not null 					     '||
		   '	 	   	   and not exists 								     '||
		   '	 	   	        (select 1 from pjm_unit_numbers 					     '||
		   '	 	                 where unit_number = bcb.from_end_item_unit_number)			     '||
		   '	 	         ) 										     '||
		   '	 	     )   										     '||
		   '	         and ( bcb.to_end_item_unit_number is not null 						     '||
		   '	 	       and not exists 									     '||
		   '	 	   	      (select 1 from pjm_unit_numbers 						     '||
		   '	 	               where unit_number = bcb.to_end_item_unit_number) 			     '||
	 	   '	 	      ) 										     '||
		   '	 	)											     ';

Line: 162


		   '	 select mif1.padded_item_number 				"Assembly Item Number",		     '||
		   '	 	mp.organization_code   		 			"Organization Code",		     '||
		   '	 	bsb.alternate_bom_designator 				"Alternate Bom Designator",	     '||
		   '	 	bcb.operation_seq_num		 			"Operation Seq Num",		     '||
		   '	    	bcb.item_num						"Item Seq Num",			     '||
		   '	 	mif2.padded_item_number		 			"Component Item",		     '||
		   '	 	decode(bcb.from_end_item_unit_number,null,''*MISSING'',					     '||
		   '	 		bcb.from_end_item_unit_number||'' (Invalid)'') 	"From End Item Unit Number",	     '||
		   '	    	bcb.to_end_item_unit_number 	 			"To End Item Unit Number",	     '||
		   '	 	bcb.component_quantity		 			"Component Quantity",		     '||
		   '	 	to_char(bcb.effectivity_date,''DD-MON-YYYY HH24:MI:SS'') "Effectivity Date",		     '||
		   '	 	to_char(bcb.disable_date,''DD-MON-YYYY HH24:MI:SS'')	"Disable Date",			     '||
		   '	 	bcb.change_notice			 		"Change Notice",		     '||
		   '	 	bsb.assembly_item_id		 			"Assembly Item Id",		     '||
		   '	 	bsb.organization_id			 		"Organization Id",		     '||
		   '	 	bsb.bill_sequence_id		 			"Bill Sequence Id",		     '||
		   '	 	bcb.component_sequence_id	 			"Component Sequence Id"  	     '||
		   '	 	from   bom_components_b bcb, bom_structures_b bsb,			     '||
		   '	 		   mtl_item_flexfields mif1, mtl_item_flexfields mif2,				     '||
		   '	 		   mtl_parameters mp								     '||
		   '	 	where  bsb.bill_sequence_id = bcb.bill_sequence_id					     '||
		   '	 	and    bsb.assembly_item_id = mif1.inventory_item_id					     '||
		   '	 	and    bsb.organization_id  = mif1.organization_id					     '||
		   '	 	and    bcb.component_item_id = mif2.inventory_item_id					     '||
		   '	 	and    bsb.organization_id  = mif2.organization_id					     '||
		   '	 	and    mif1.organization_id  = mp.organization_id					     '||
		   '	 	and    mif1.effectivity_control = 2							     '||
		   '	 	and    (   ( bcb.from_end_item_unit_number is null					     '||
		   '	 		     or  ( bcb.from_end_item_unit_number is not null				     '||
		   '	 	   	           and not exists							     '||
		   '	   	            	   (select 1 from pjm_unit_numbers					     '||
		   '	 	                    where unit_number = bcb.from_end_item_unit_number)			     '||
		   '	 	                   ) 									     '||
		   '	 	 	    )										     '||
		   '	 		    and ( bcb.to_end_item_unit_number is null					     '||
		   '	 			  or 									     '||
		   '	 			  ( bcb.to_end_item_unit_number is not null				     '||
		   '	 	   	            and exists								     '||
		   '	 	   	                (select 1 from pjm_unit_numbers					     '||
		   '	 	                         where unit_number = bcb.to_end_item_unit_number)		     '||
		   '	 	                  )									     '||
		   '	 			)  									     '||
		   '	 		 )										     ';

Line: 212


		   '	 select mif1.padded_item_number 	 		"Assembly Item Number",	 		     '||
		   '	 	mp.organization_code   	 			"Organization Code",		 	     '||
		   '	 	bsb.alternate_bom_designator 			"Alternate Bom Designator",	 	     '||
		   '	 	bcb.operation_seq_num	 			"Operation Seq Num",		 	     '||
		   '	 	bcb.item_num			 		"Item Seq Num",		 		     '||
		   '	 	mif2.padded_item_number	 			"Component Item",		 	     '||
		   '	 	bcb.from_end_item_unit_number 			"From End Item Unit Number",	 	     '||
		   '	 	bcb.to_end_item_unit_number||'' (Invalid)'' 	"To End Item Unit Number",	 	     '||
		   '	 	bcb.component_quantity	 			"Component Quantity",		 	     '||
		   '	 	to_char(bcb.effectivity_date,''DD-MON-YYYY HH24:MI:SS'')"Effectivity Date",		     '||
		   '	 	to_char(bcb.disable_date,''DD-MON-YYYY HH24:MI:SS'')	"Disable Date",		 		'||
		   '	 	bcb.change_notice		 		"Change Notice",		 	     '||
		   '	 	bsb.assembly_item_id				"Assembly Item Id",		 	     '||
		   '	 	bsb.organization_id		 		"Organization Id",		 	     '||
		   '	 	bsb.bill_sequence_id	 			"Bill Sequence Id",		 	     '||
		   '	 	bcb.component_sequence_id	 		"Component Sequence Id"  	 	     '||
		   '	 from   bom_components_b bcb, bom_structures_b bsb,	 			     '||
		   '	 	   mtl_item_flexfields mif1, mtl_item_flexfields mif2,		 			     '||
		   '	 	   mtl_parameters mp						 			     '||
		   '	 where  bsb.bill_sequence_id = bcb.bill_sequence_id			 			     '||
		   '	 and    bsb.assembly_item_id = mif1.inventory_item_id			 			     '||
		   '	 and    bsb.organization_id  = mif1.organization_id			 			     '||
		   '	 and    bcb.component_item_id = mif2.inventory_item_id			 			     '||
		   '	 and    bsb.organization_id  = mif2.organization_id			 			     '||
		   '	 and    mif1.organization_id  = mp.organization_id			 			     '||
		   '	 and    mif1.effectivity_control = 2					 			     '||
		   '	 and    (   ( bcb.to_end_item_unit_number is not null						     '||
		   '	 	      and not exists									     '||
		   '	 	          (select 1 from pjm_unit_numbers						     '||
		   '	 	           where unit_number = bcb.to_end_item_unit_number)				     '||
		   '	 	    )											     '||
		   '	 	    and ( bcb.from_end_item_unit_number is not null					     '||
		   '	 	          and exists									     '||
		   '	 	              (select 1 from pjm_unit_numbers						     '||
		   '	 	               where unit_number = bcb.from_end_item_unit_number)			     '||
		   '	 	        )										     '||
		   '	        )											     ';

Line: 309


sqltxt:=	'	select	mif.padded_item_number		"Assembly Item",					   '||
		'		mp.organization_code		"Organization Code",					   '||
		'		bsb.alternate_bom_designator	"Alternate Designator",					   '||
		'		decode(bsb.assembly_type,1,''Manufacturing Bill'',2,''Engineering Bill'')  "Assembly Type",'||
		'		to_char(bsb.implementation_date,''DD-MON-YYYY HH24:MI:SS'')	"Implementation Date",	   '||
		'		bsb.pending_from_ecn		"Pending From ECN",			  		   '||
		'		bsb.assembly_item_id		"Assembly Item Id" ,					   '||
		'		bsb.organization_id		"Organization Id",					   '||
		'		bsb.bill_sequence_id		"Bill Sequence Id",					   '||
		'		bsb.common_bill_sequence_id	"Common Bill Sequence Id"				   '||
		'	from    bom_structures_b bsb, mtl_item_flexfields mif, mtl_parameters mp			   '||
		'	where	mif.inventory_item_id =  bsb.assembly_item_id						   '||
		'	and	mif.organization_id   =  bsb.organization_id						   '||
		'	and	bsb.organization_id  =  mp.organization_id						   '||
		'	and 	bsb.implementation_date is null							   ';

Line: 352


		
(2) Use the below update statement to correct these bills.
	update bom_structures_b
	set    implementation_date = creation_date
	where  implementation_date is null;

Line: 357

		(3) Make sure that the total number of records updated
		
are same as the number of records fetched above.
		
(4) Commit the transaction.
		
';