APPS.BOM_DIAGUNITTEST_TSDATA SQL Statements

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

Line: 18

 sqltxt    VARCHAR2(9999);  -- SQL select statement

Line: 40

select col.column_name from user_synonyms syn, dba_tab_columns col where
col.owner=syn.table_owner AND col.table_name = syn.table_name
and syn.synonym_name = l_table_name and col.owner = l_owner and col.data_type in ( 'VARCHAR2' , 'CHAR');

Line: 45

SELECT count(*)
FROM   mtl_system_items_b
WHERE  inventory_item_id = cp_n_item_id
AND    organization_id   = nvl(cp_n_org_id,organization_id);

Line: 53

JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;

Line: 117

 update those records from the Form.


ACTION:

 In this script''s output for each table,

 ''Column Name'' stands for the name of the offending column

 ''Column Value'' stands for the value of the offending column.

 The rest of the columns are useful in uniquely identifying

 the specific record.



 If records are fetched from any of the tables,

 then the record with the offending column

 needs to be updated so that the columns with

 the trailing spaces are trimmed for this specific record.



(E.g.) If for mtl_system_items_b table,

 a record is fetched with

 Inventory Item Id = 1234

 Organization id   = 100

 Column Name	  = Attribute1

 Column Value	  = ''column with trailing space  ''



 Use file "afchrchk.sql" present in $FND_TOP/sql to trim the trailing spaces.

 This script will ask for the following input.

 1. Table name: Enter the table name fetched above (e.g.)mtl_system_items_b

 2. Column name: Enter the column name fetched above (e.g.) Attribute1

 3. Check for newline characters (Y/N)?: Enter as N

 4. Automatically fix all errors found (Y/N)? Enter as Y



Important: Try the above action plan on a TEST INSTANCE first.

');

Line: 173

	 delete from bom_diag_temp_tab; -- Clear the temporary tables

Line: 178

	 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id, organization_id,	'||
 		 	'     char_col1,char_col2)						'||
	 		'  	    SELECT  msib.inventory_item_id 				'||
	 		' 		   ,msib.organization_id				'||
			'		   ,'''||l_varchar_col_rec.column_name||'''	'||
			'		   ,msib.'||l_varchar_col_rec.column_name||'		'||
			'	    FROM   MTL_SYSTEM_ITEMS_B	msib				'||
			'	    WHERE  msib.organization_id  = '||l_org_id||'		'||
			'	    AND    msib.inventory_item_id ='||l_item_id||'		'||
			'	    AND    msib.'||l_varchar_col_rec.column_name||' LIKE ''% ''	'||
			'	    AND	   rownum < '||(row_limit/25);

Line: 197

	sqltxt:='	Select     Inventory_Item_Id 	  "Inventory Item Id",		     '||
		'	 	   Organization_id	  "Organization id",		     '||
		'		   Char_Col1		  "Column Name",		     '||
		'	 	   Char_Col2		  "Column Value"		     '||
		'	From	   bom_diag_temp_tab	  where 1=1			     ';

Line: 218

	 delete from bom_diag_temp_tab; -- Clear the temporary tables

Line: 226

	 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id, organization_id,	'||
 		 	'     char_col3,char_col1,char_col2)					'||
	 		'  	    SELECT  msitl.inventory_item_id	 			'||
	 		' 		   ,msitl.organization_id				'||
			'		   ,msitl.language					'||
			'		   ,'''||l_varchar_col_rec.column_name||'''		'||
			'		   ,msitl.'||l_varchar_col_rec.column_name||'		'||
			'	    FROM   MTL_SYSTEM_ITEMS_TL	msitl				'||
			'	    WHERE  msitl.organization_id  = '||l_org_id||'		'||
			'	    AND    msitl.inventory_item_id ='||l_item_id||'		'||
			'	    AND    msitl.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
			'	    AND	   rownum < '||(row_limit/25);

Line: 242

	 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id, organization_id,	'||
 		 	'     char_col3,char_col1,description)					'||
	 		'  	    SELECT  msitl.inventory_item_id	 			'||
	 		' 		   ,msitl.organization_id				'||
			'		   ,msitl.language					'||
			'		   ,''DESCRIPTION''					'||
			'		   ,msitl.'||l_varchar_col_rec.column_name||'		'||
			'	    FROM   MTL_SYSTEM_ITEMS_TL	msitl				'||
			'	    WHERE  msitl.organization_id  = '||l_org_id||'		'||
			'	    AND    msitl.inventory_item_id ='||l_item_id||'		'||
			'	    AND    msitl.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
			'	    AND	   rownum < '||(row_limit/25);

Line: 257

	 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id, organization_id,	'||
 		 	'     char_col3,char_col1,Long_Description)				'||
	 		'  	    SELECT  msitl.inventory_item_id	 			'||
	 		' 		   ,msitl.organization_id				'||
			'		   ,msitl.language					'||
			'		   ,''LONG_DESCRIPTION''				'||
			'		   ,msitl.'||l_varchar_col_rec.column_name||'		'||
			'	    FROM   MTL_SYSTEM_ITEMS_TL	msitl				'||
			'	    WHERE  msitl.organization_id  = '||l_org_id||'		'||
			'	    AND    msitl.inventory_item_id ='||l_item_id||'		'||
			'	    AND    msitl.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
			'	    AND	   rownum < '||(row_limit/25);

Line: 280

	sqltxt:='	Select     Inventory_Item_Id 	  "Inventory Item Id",		     '||
		'	 	   Organization_id	  "Organization id",		     '||
		'		   Char_col3		  "Language",			     '||
		'		   Char_Col1		  "Column Name",		     '||
		'		   Description		  "Description",		     '||
		'		   Long_Description	  "Long Description"		     '||
		'	From	   bom_diag_temp_tab	  where 1=1			     ';

Line: 303

	 delete from bom_diag_temp_tab; -- Clear the temporary tables

Line: 308

	 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id, organization_id,	'||
 		 	'     char_col3,num_col1,char_col1,char_col2)				'||
	 		'  	    SELECT  mirb.inventory_item_id	 			'||
	 		' 		   ,mirb.organization_id				'||
			'		   ,mirb.revision					'||
			'		   ,mirb.revision_id					'||
			'		   ,'''||l_varchar_col_rec.column_name||'''		'||
			'		   ,mirb.'||l_varchar_col_rec.column_name||'		'||
			'	    FROM   MTL_ITEM_REVISIONS_B	mirb				'||
			'	    WHERE  mirb.organization_id  = '||l_org_id||'		'||
			'	    AND    mirb.inventory_item_id ='||l_item_id||'		'||
			'	    AND    mirb.'||l_varchar_col_rec.column_name||' LIKE ''% ''	'||
			'	    AND	   rownum < '||(row_limit/25);

Line: 329

	sqltxt:='	Select     Inventory_Item_Id 	  "Inventory Item Id",		     '||
		'	 	   Organization_id	  "Organization id",		     '||
		'		   Char_Col3		  "Revision",			     '||
		'		   Num_Col1		  "Revision Id",		     '||
		'		   Char_Col1		  "Column Name",		     '||
		'	 	   Char_Col2		  "Column Value"		     '||
		'	From	   bom_diag_temp_tab	  where 1=1			     ';

Line: 352

	 delete from bom_diag_temp_tab; -- Clear the temporary tables

Line: 357

	 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id, organization_id,	'||
 		 	'     num_col1,char_col3,char_col1,char_col2)				'||
	 		'  	    SELECT  mirtl.inventory_item_id	 			'||
	 		' 		   ,mirtl.organization_id				'||
			'		   ,mirtl.revision_id					'||
			'		   ,mirtl.language					'||
			'		   ,'''||l_varchar_col_rec.column_name||'''		'||
			'		   ,mirtl.'||l_varchar_col_rec.column_name||'		'||
			'	    FROM   MTL_ITEM_REVISIONS_TL	mirtl			'||
			'	    WHERE  mirtl.organization_id  = '||l_org_id||'		'||
			'	    AND    mirtl.inventory_item_id ='||l_item_id||'		'||
			'	    AND    mirtl.'||l_varchar_col_rec.column_name||' LIKE ''% '' '||
			'	    AND	   rownum < '||(row_limit/25);

Line: 378

	sqltxt:='	Select     Inventory_Item_Id 	  "Inventory Item Id",		     '||
		'	 	   Organization_id	  "Organization id",		     '||
		'		   Num_Col1		  "Revision Id",		     '||
		'		   Char_Col3		  "Language",			     '||
		'		   Char_Col1		  "Column Name",		     '||
		'	 	   Char_Col2		  "Column Value"		     '||
		'	From	   bom_diag_temp_tab	  where 1=1			     ';

Line: 401

	 delete from bom_diag_temp_tab; -- Clear the temporary tables

Line: 406

	 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,				'||
 		 	'     char_col3,num_col1,char_col1,char_col2)				'||
	 		'  	    SELECT  mdev.inventory_item_id	 			'||
			'		   ,mdev.element_name					'||
			'		   ,mdev.element_sequence				'||
			'		   ,'''||l_varchar_col_rec.column_name||'''		'||
			'		   ,mdev.'||l_varchar_col_rec.column_name||'		'||
			'	    FROM   MTL_DESCR_ELEMENT_VALUES mdev			'||
			'	    WHERE  mdev.inventory_item_id ='||l_item_id||'		'||
			'	    AND    mdev.'||l_varchar_col_rec.column_name||' LIKE ''% ''	'||
			'	    AND	   rownum < '||(row_limit/25);

Line: 425

	sqltxt:='	Select     Inventory_Item_Id 	  "Inventory Item Id",		     '||
		'		   Char_Col3		  "Element Name",		     '||
		'		   Num_Col1		  "Element Sequence",		     '||
		'		   Char_Col1		  "Column Name",		     '||
		'	 	   Char_Col2		  "Column Value"		     '||
		'	From	   bom_diag_temp_tab	  where 1=1			     ';

Line: 446

	 delete from bom_diag_temp_tab; -- Clear the temporary tables

Line: 451

	 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id,		'||
 		 	'     char_col3,Date_col1,char_col1,char_col2)				'||
	 		'  	    SELECT  mpis.inventory_item_id	 			'||
			'		   ,mpis.Organization_id				'||
			'		   ,mpis.STATUS_CODE					'||
			'		   ,mpis.EFFECTIVE_DATE					'||
			'		   ,'''||l_varchar_col_rec.column_name||'''		'||
			'		   ,mpis.'||l_varchar_col_rec.column_name||'		'||
			'	    FROM   MTL_PENDING_ITEM_STATUS mpis				'||
			'	    WHERE  mpis.inventory_item_id ='||l_item_id||'		'||
			'	    AND    mpis.organization_id  = '||l_org_id||'		'||
			'	    AND    mpis.'||l_varchar_col_rec.column_name||' LIKE ''% ''	'||
			'	    AND	   rownum < '||(row_limit/25);

Line: 472

	sqltxt:='	Select     Inventory_Item_Id 	  "Inventory Item Id",		     '||
		'	 	   Organization_id	  "Organization id",		     '||
		'		   Char_Col3		  "Status Code",		     '||
		'		   to_char(Date_Col1,''DD-MON-YYYY HH24:MI:SS'') "Effectivity Date", '||
		'		   Char_Col1		  "Column Name",		     '||
		'	 	   Char_Col2		  "Column Value"		     '||
		'	From	   bom_diag_temp_tab	  where 1=1			     ';

Line: 495

	 delete from bom_diag_temp_tab; -- Clear the temporary tables

Line: 500

	 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id,		'||
 		 	'     char_col3,char_col4,char_col1,char_col2)				'||
	 		'  	    SELECT  mcr.inventory_item_id	 			'||
			'		   ,mcr.Organization_id					'||
			'		   ,MCR.CROSS_REFERENCE_TYPE				'||
			'		   ,MCR.CROSS_REFERENCE					'||
			'		   ,'''||l_varchar_col_rec.column_name||'''		'||
			'		   ,mcr.'||l_varchar_col_rec.column_name||'		'||
			'	    FROM   MTL_CROSS_REFERENCES_B mcr				'||
			'	    WHERE  mcr.inventory_item_id ='||l_item_id||'		'||
			'	    AND   (  ( mcr.organization_id =  '||l_org_id||' and  org_independent_flag=''N'') '||
			'			or ( mcr.organization_id is null and org_independent_flag=''Y'') )    '||
			'	    AND    mcr.'||l_varchar_col_rec.column_name||' LIKE ''% ''	'||
			'	    AND	   rownum < '||(row_limit/25);

Line: 522

	sqltxt:='	Select     Inventory_Item_Id 	  "Inventory Item Id",		     '||
		'	 	   Organization_id	  "Organization id",		     '||
		'		   Char_Col3		  "Cross Reference Type",	     '||
		'		   Char_Col4		  "Cross Reference",		     '||
		'		   Char_Col1		  "Column Name",		     '||
		'	 	   Char_Col2		  "Column Value"		     '||
		'	From	   bom_diag_temp_tab	  where 1=1			     ';

Line: 544

	 delete from bom_diag_temp_tab; -- Clear the temporary tables

Line: 549

	 sql_stmt :=' Insert into bom_diag_temp_tab (num_col1,		'||
 		 	'     char_col4,char_col1,char_col2)				'||
	 		'  	    SELECT  mcrt.cross_reference_id	 			'||
			'		   ,mcrt.language					'||
			'		   ,'''||l_varchar_col_rec.column_name||'''		'||
			'		   ,mcrt.'||l_varchar_col_rec.column_name||'		'||
			'	    FROM   MTL_CROSS_REFERENCES_B mcrb, MTL_CROSS_REFERENCES_TL mcrt				'||
			'	    WHERE  mcrb.inventory_item_id ='||l_item_id||'		'||
			'	    AND   (  ( mcrb.organization_id =  '||l_org_id||' and  org_independent_flag=''N'') '||
			'			or ( mcrb.organization_id is null and org_independent_flag=''Y'') )    '||
			'	    AND    mcrb.cross_reference_id = mcrt.cross_reference_id	'||
			'	    AND    mcrt.'||l_varchar_col_rec.column_name||' LIKE ''% ''	'||
			'	    AND	   rownum < '||(row_limit/25);

Line: 570

	sqltxt:='	Select  num_col1		  "Cross Reference Id",		     '||
		'		   Char_Col4		  "Language",		     '||
		'		   Char_Col1		  "Column Name",		     '||
		'	 	   Char_Col2		  "Column Value"		     '||
		'	From	   bom_diag_temp_tab	  where 1=1			     ';

Line: 590

	 delete from bom_diag_temp_tab; -- Clear the temporary tables

Line: 595

	 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id,		'||
 		 	'     Num_col1,char_col1,char_col2)					'||
	 		'  	    SELECT  mcix.inventory_item_id	 			'||
			'		   ,mcix.Master_Organization_id				'||
			'		   ,MCIX.CUSTOMER_ITEM_ID				'||
			'		   ,'''||l_varchar_col_rec.column_name||'''		'||
			'		   ,mcix.'||l_varchar_col_rec.column_name||'		'||
			'	    FROM   MTL_CUSTOMER_ITEM_XREFS mcix				'||
			'	    WHERE  mcix.inventory_item_id ='||l_item_id||'		'||
			'	    AND    mcix.master_organization_id =			'||
			'			(select master_organization_id from mtl_parameters  '||
			'			 where organization_id= '||l_org_id||' )	'||
			'	    AND    mcix.'||l_varchar_col_rec.column_name||' LIKE ''% ''	'||
			'	    AND	   rownum < '||(row_limit/25);

Line: 617

	sqltxt:='	Select     Inventory_Item_Id 	  "Inventory Item Id",		     '||
		'	 	   Organization_id	  "Master Organization id",	     '||
		'		   Num_Col1		  "Customer Item Id",		     '||
		'		   Char_Col1		  "Column Name",		     '||
		'	 	   Char_Col2		  "Column Value"		     '||
		'	From	   bom_diag_temp_tab	  where 1=1			     ';

Line: 638

	 delete from bom_diag_temp_tab; -- Clear the temporary tables

Line: 643

	 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id,		'||
 		 	'     Num_col1,char_col3,char_col1,char_col2)				'||
	 		'  	    SELECT  mmpn.inventory_item_id	 			'||
			'		   ,mmpn.Organization_id				'||
			'		   ,MMPN.MANUFACTURER_ID				'||
			'		   ,MMPN.MFG_PART_NUM					'||
			'		   ,'''||l_varchar_col_rec.column_name||'''		'||
			'		   ,mmpn.'||l_varchar_col_rec.column_name||'		'||
			'	    FROM   MTL_MFG_PART_NUMBERS mmpn				'||
			'	    WHERE  mmpn.inventory_item_id ='||l_item_id||'		'||
			'	    AND    mmpn.organization_id =				'||
			'			(select master_organization_id from mtl_parameters  '||
			'			 where organization_id= '||l_org_id||' )	'||
			'	    AND    mmpn.'||l_varchar_col_rec.column_name||' LIKE ''% ''	'||
			'	    AND	   rownum < '||(row_limit/25);

Line: 666

	sqltxt:='	Select     Inventory_Item_Id 	  "Inventory Item Id",		     '||
		'	 	   Organization_id	  "Organization id",		     '||
		'		   Num_Col1		  "Manufacturer Id",		     '||
		'		   Char_Col3		  "Mfg Part Num",		     '||
		'		   Char_Col1		  "Column Name",		     '||
		'	 	   Char_Col2		  "Column Value"		     '||
		'	From	   bom_diag_temp_tab	  where 1=1			     ';

Line: 688

	 delete from bom_diag_temp_tab; -- Clear the temporary tables

Line: 693

	 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id, organization_id,	'||
 		 	'     char_col3,char_col1,char_col2)					'||
	 		'  	    SELECT  mrir.inventory_item_id	 			'||
	 		' 		   ,mrir.organization_id				'||
			'		   ,MRIR.PROCESS_REVISION				'||
			'		   ,'''||l_varchar_col_rec.column_name||'''		'||
			'		   ,mrir.'||l_varchar_col_rec.column_name||'		'||
			'	    FROM   MTL_RTG_ITEM_REVISIONS mrir				'||
			'	    WHERE  mrir.organization_id  = '||l_org_id||'		'||
			'	    AND    mrir.inventory_item_id ='||l_item_id||'		'||
			'	    AND    mrir.'||l_varchar_col_rec.column_name||' LIKE ''% ''	'||
			'	    AND	   rownum < '||(row_limit/25);

Line: 713

	sqltxt:='	Select     Inventory_Item_Id 	  "Inventory Item Id",		     '||
		'	 	   Organization_id	  "Organization id",		     '||
		'		   Char_Col3		  "Process Revision",		     '||
		'		   Char_Col1		  "Column Name",		     '||
		'	 	   Char_Col2		  "Column Value"		     '||
		'	From	   bom_diag_temp_tab	  where 1=1			     ';

Line: 735

	 delete from bom_diag_temp_tab; -- Clear the temporary tables

Line: 740

	 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id,		'||
 		 	'     Num_col1,Num_col2,char_col1,char_col2)				'||
	 		'  	    SELECT  mri.inventory_item_id	 			'||
			'		   ,mri.Organization_id					'||
			'		   ,MRI.RELATED_ITEM_ID					'||
			'		   ,MRI.RELATIONSHIP_TYPE_ID				'||
			'		   ,'''||l_varchar_col_rec.column_name||'''		'||
			'		   ,mri.'||l_varchar_col_rec.column_name||'		'||
			'	    FROM   MTL_RELATED_ITEMS mri				'||
			'	    WHERE  mri.inventory_item_id ='||l_item_id||'		'||
			'	    AND    mri.organization_id =				'||
			'			(select master_organization_id from mtl_parameters  '||
			'			 where organization_id= '||l_org_id||' )	'||
			'	    AND    mri.'||l_varchar_col_rec.column_name||' LIKE ''% ''	'||
			'	    AND	   rownum < '||(row_limit/25);

Line: 763

	sqltxt:='	Select     Inventory_Item_Id 	  "Inventory Item Id",		     '||
		'	 	   Organization_id	  "Organization id",		     '||
		'		   Num_Col1		  "Related Item Id",		     '||
		'		   Num_Col2		  "Relationship Type Id",	     '||
		'		   Char_Col1		  "Column Name",		     '||
		'	 	   Char_Col2		  "Column Value"		     '||
		'	From	   bom_diag_temp_tab	  where 1=1			     ';

Line: 802

	 delete from bom_diag_temp_tab; -- Clear the temporary tables

Line: 807

	 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id,		'||
 		 	'     char_col3,Num_col1,char_col1,char_col2)				'||
	 		'  	    SELECT  bsb.assembly_item_id	 			'||
			'		   ,bsb.Organization_id				'||
			'		   ,bsb.ALTERNATE_BOM_DESIGNATOR			'||
			'		   ,bsb.BILL_SEQUENCE_ID				'||
			'		   ,'''||l_varchar_col_rec.column_name||'''		'||
			'		   ,bsb.'||l_varchar_col_rec.column_name||'		'||
			'	    FROM   BOM_STRUCTURES_B bsb					'||
			'	    WHERE  bsb.assembly_item_id ='||l_item_id||'		'||
			'	    AND    bsb.organization_id  = '||l_org_id||'		'||
			'	    AND    bsb.'||l_varchar_col_rec.column_name||' LIKE ''% ''	'||
			'	    AND	   rownum < '||(row_limit/25);

Line: 828

	sqltxt:='	Select     Inventory_Item_Id 	  "Assembly Item Id",		     '||
		'	 	   Organization_id	  "Organization id",		     '||
		'		   char_Col3		  "ALTERNATE BOM DESIGNATOR",	     '||
		'		   Num_Col1		  "BILL SEQUENCE ID",		     '||
		'		   Char_Col1		  "Column Name",		     '||
		'	 	   Char_Col2		  "Column Value"		     '||
		'	From	   bom_diag_temp_tab	  where 1=1			     ';

Line: 850

	 delete from bom_diag_temp_tab; -- Clear the temporary tables

Line: 855

	 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id,		'||
 		 	'     char_col3,Num_col1,Num_col2,Num_col3,num_col4,char_col1,char_col2)'||
	 		'  	    SELECT  bsb.assembly_item_id	 			'||
			'		   ,bsb.Organization_id				'||
			'		   ,bsb.ALTERNATE_BOM_DESIGNATOR			'||
			'		   ,bsb.BILL_SEQUENCE_ID				'||
			'		   ,BCB.OPERATION_SEQ_NUM				'||
			'		   ,BCB.COMPONENT_ITEM_ID				'||
			'		   ,bcb.component_sequence_id				'||
			'		   ,'''||l_varchar_col_rec.column_name||'''		'||
			'		   ,bcb.'||l_varchar_col_rec.column_name||'		'||
			'	    FROM  BOM_COMPONENTS_B bcb , BOM_STRUCTURES_B bsb	'||
			'	    WHERE  1=1							'||
			'	    AND    bcb.bill_sequence_id = bsb.bill_sequence_id		'||
			'	    AND    bsb.assembly_item_id ='||l_item_id||'		'||
			'	    AND    bsb.organization_id  = '||l_org_id||'		'||
			'	    AND    bcb.'||l_varchar_col_rec.column_name||' LIKE ''% ''	'||
			'	    AND	   rownum < '||(row_limit/25);

Line: 881

	sqltxt:='	Select     Inventory_Item_Id 	  "Assembly Item Id",		     '||
		'	 	   Organization_id	  "Organization id",		     '||
		'		   char_Col3		  "ALTERNATE BOM DESIGNATOR",	     '||
		'		   Num_Col1		  "BILL SEQUENCE ID",		     '||
		'		   Num_Col2		  "OPERATION SEQ NUM",		     '||
		'		   Num_Col3		  "COMPONENT ITEM ID",		     '||
		'		   Num_Col4		  "COMPONENT SEQUENCE ID",	     '||
		'		   Char_Col1		  "Column Name",		     '||
		'	 	   Char_Col2		  "Column Value"		     '||
		'	From	   bom_diag_temp_tab	  where 1=1			     ';

Line: 906

	 delete from bom_diag_temp_tab; -- Clear the temporary tables

Line: 911

	 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id,		'||
 		 	'     char_col3,Num_col1,Num_col2,char_col4,char_col1,char_col2)	'||
	 		'  	    SELECT  bsb.assembly_item_id	 			'||
			'		   ,bsb.Organization_id				'||
			'		   ,bsb.ALTERNATE_BOM_DESIGNATOR			'||
			'		   ,bsb.BILL_SEQUENCE_ID				'||
			'		   ,bcb.component_sequence_id				'||
			'		   ,BRD.COMPONENT_REFERENCE_DESIGNATOR			'||
			'		   ,'''||l_varchar_col_rec.column_name||'''		'||
			'		   ,brd.'||l_varchar_col_rec.column_name||'		'||
			'	    FROM   bom_inventory_components bcb, bom_bill_of_materials bsb, bom_reference_designators brd '||
			'	    WHERE  1=1							'||
			'	    AND    bcb.bill_sequence_id = bsb.bill_sequence_id		'||
			'	    and    brd.component_sequence_id=bcb.component_sequence_id  '||
			'	    AND    bsb.assembly_item_id ='||l_item_id||'		'||
			'	    AND    bsb.organization_id  = '||l_org_id||'		'||
			'	    AND    brd.'||l_varchar_col_rec.column_name||' LIKE ''% ''	'||
			'	    AND	   rownum < '||(row_limit/25);

Line: 937

	sqltxt:='	Select     Inventory_Item_Id 	  "Assembly Item Id",		     '||
		'	 	   Organization_id	  "Organization id",		     '||
		'		   char_Col3		  "ALTERNATE BOM DESIGNATOR",	     '||
		'		   Num_Col1		  "BILL SEQUENCE ID",		     '||
		'		   Num_Col2		  "COMPONENT SEQUENCE ID",	     '||
		'		   Char_Col4		  "COMPONENT REFERENCE DESIGNATOR",  '||
		'		   Char_Col1		  "Column Name",		     '||
		'	 	   Char_Col2		  "Column Value"		     '||
		'	From	   bom_diag_temp_tab	  where 1=1			     ';

Line: 961

	 delete from bom_diag_temp_tab; -- Clear the temporary tables

Line: 966

	 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id,		'||
 		 	'     char_col3,Num_col1,Num_col2,Num_col3,char_col1,char_col2)	'||
	 		'  	    SELECT  bsb.assembly_item_id	 			'||
			'		   ,bsb.Organization_id				'||
			'		   ,bsb.ALTERNATE_BOM_DESIGNATOR			'||
			'		   ,bsb.BILL_SEQUENCE_ID				'||
			'		   ,bcb.component_sequence_id				'||
			'		   ,BSC.SUBSTITUTE_COMPONENT_ID				'||
			'		   ,'''||l_varchar_col_rec.column_name||'''		'||
			'		   ,bsc.'||l_varchar_col_rec.column_name||'		'||
			'	    FROM   bom_inventory_components bcb, bom_bill_of_materials bsb, bom_substitute_components bsc '||
			'	    WHERE  1=1							'||
			'	    AND    bcb.bill_sequence_id = bsb.bill_sequence_id		'||
			'	    and    bsc.component_sequence_id=bcb.component_sequence_id  '||
			'	    AND    bsb.assembly_item_id ='||l_item_id||'		'||
			'	    AND    bsb.organization_id  = '||l_org_id||'		'||
			'	    AND    bsc.'||l_varchar_col_rec.column_name||' LIKE ''% ''	'||
			'	    AND	   rownum < '||(row_limit/25);

Line: 992

	sqltxt:='	Select     Inventory_Item_Id 	  "Assembly Item Id",		     '||
		'	 	   Organization_id	  "Organization id",		     '||
		'		   char_Col3		  "ALTERNATE BOM DESIGNATOR",	     '||
		'		   Num_Col1		  "BILL SEQUENCE ID",		     '||
		'		   Num_Col2		  "COMPONENT SEQUENCE ID",	     '||
		'		   Num_col3		  "SUBSTITUTE COMPONENT ID",	     '||
		'		   Char_Col1		  "Column Name",		     '||
		'	 	   Char_Col2		  "Column Value"		     '||
		'	From	   bom_diag_temp_tab	  where 1=1			     ';

Line: 1020

	 delete from bom_diag_temp_tab; -- Clear the temporary tables

Line: 1025

	 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id,		'||
 		 	'     char_col3,Num_col1,char_col1,char_col2)				'||
	 		'  	    SELECT  bor.assembly_item_id	 			'||
			'		   ,bor.Organization_id					'||
			'		   ,BOR.ALTERNATE_ROUTING_DESIGNATOR			'||
			'		   ,BOR.ROUTING_SEQUENCE_ID				'||
			'		   ,'''||l_varchar_col_rec.column_name||'''		'||
			'		   ,bor.'||l_varchar_col_rec.column_name||'		'||
			'	    FROM   bom_operational_routings bor				'||
			'	    WHERE  1=1							'||
			'	    AND    bor.assembly_item_id ='||l_item_id||'		'||
			'	    AND    bor.organization_id  = '||l_org_id||'		'||
			'	    AND    bor.'||l_varchar_col_rec.column_name||' LIKE ''% ''	'||
			'	    AND	   rownum < '||(row_limit/25);

Line: 1047

	sqltxt:='	Select     Inventory_Item_Id 	  "Assembly Item Id",		     '||
		'	 	   Organization_id	  "Organization id",		     '||
		'		   char_Col3		  "ALTERNATE ROUTING DESIGNATOR",    '||
		'		   Num_Col1		  "ROUTING SEQUENCE ID",	     '||
		'		   Char_Col1		  "Column Name",		     '||
		'	 	   Char_Col2		  "Column Value"		     '||
		'	From	   bom_diag_temp_tab	  where 1=1			     ';

Line: 1069

	 delete from bom_diag_temp_tab; -- Clear the temporary tables

Line: 1074

	 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id,		'||
 		 	'     char_col3,Num_col1,Num_col2,char_col1,char_col2)			'||
	 		'  	    SELECT  bor.assembly_item_id	 			'||
			'		   ,bor.Organization_id					'||
			'		   ,BOR.ALTERNATE_ROUTING_DESIGNATOR			'||
			'		   ,BOR.ROUTING_SEQUENCE_ID				'||
			'		   ,BOS.OPERATION_SEQUENCE_ID				'||
			'		   ,'''||l_varchar_col_rec.column_name||'''		'||
			'		   ,bos.'||l_varchar_col_rec.column_name||'		'||
			'	    FROM    bom_operational_routings bor, bom_operation_sequences bos '||
			'	    WHERE  1=1							'||
			'	    AND	   bos.routing_sequence_id=bor.routing_sequence_id	'||
			'	    AND    bor.assembly_item_id ='||l_item_id||'		'||
			'	    AND    bor.organization_id  = '||l_org_id||'		'||
			'	    AND    bos.'||l_varchar_col_rec.column_name||' LIKE ''% ''	'||
			'	    AND	   rownum < '||(row_limit/25);

Line: 1098

	sqltxt:='	Select     Inventory_Item_Id 	  "Assembly Item Id",		     '||
		'	 	   Organization_id	  "Organization id",		     '||
		'		   char_Col3		  "ALTERNATE ROUTING DESIGNATOR",    '||
		'		   Num_Col1		  "ROUTING SEQUENCE ID",	     '||
		'		   Num_Col2		  "OPERATION SEQUENCE ID",	     '||
		'		   Char_Col1		  "Column Name",		     '||
		'	 	   Char_Col2		  "Column Value"		     '||
		'	From	   bom_diag_temp_tab	  where 1=1			     ';

Line: 1121

	 delete from bom_diag_temp_tab; -- Clear the temporary tables

Line: 1126

	 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id,		'||
 		 	'     char_col3,Num_col1,Num_col2,Num_col3,Num_col4,char_col1,char_col2)'||
	 		'  	    SELECT  bor.assembly_item_id	 			'||
			'		   ,bor.Organization_id					'||
			'		   ,BOR.ALTERNATE_ROUTING_DESIGNATOR			'||
			'		   ,BOR.ROUTING_SEQUENCE_ID				'||
			'		   ,BOS.OPERATION_SEQUENCE_ID				'||
			'		   ,BORE.RESOURCE_SEQ_NUM				'||
			'		   ,BORE.RESOURCE_ID					'||
			'		   ,'''||l_varchar_col_rec.column_name||'''		'||
			'		   ,bore.'||l_varchar_col_rec.column_name||'		'||
			'	    FROM   bom_operational_routings bor, bom_operation_sequences bos,'||
			'		   bom_operation_resources bore				'||
			'	    WHERE   1=1							'||
			'	    AND	   bos.routing_sequence_id=bor.routing_sequence_id	'||
			'	    AND    bore.operation_sequence_id=bos.operation_sequence_id	'||
			'	    AND    bor.assembly_item_id ='||l_item_id||'		'||
			'	    AND    bor.organization_id  = '||l_org_id||'		'||
			'	    AND    bore.'||l_varchar_col_rec.column_name||' LIKE ''% ''	'||
			'	    AND	   rownum < '||(row_limit/25);

Line: 1154

	sqltxt:='	Select     Inventory_Item_Id 	  "Assembly Item Id",		     '||
		'	 	   Organization_id	  "Organization id",		     '||
		'		   char_Col3		  "ALTERNATE ROUTING DESIGNATOR",    '||
		'		   Num_Col1		  "ROUTING SEQUENCE ID",	     '||
		'		   Num_Col2		  "OPERATION SEQUENCE ID",	     '||
		'		   Num_Col3		  "RESOURCE SEQ NUM",		     '||
		'		   Num_Col4		  "RESOURCE ID",		     '||
		'		   Char_Col1		  "Column Name",		     '||
		'	 	   Char_Col2		  "Column Value"		     '||
		'	From	   bom_diag_temp_tab	  where 1=1			     ';

Line: 1179

	 delete from bom_diag_temp_tab; -- Clear the temporary tables

Line: 1184

	 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id,		'||
 		 	'     char_col3,Num_col1,Num_col2,Num_col3,Num_col4,Num_col5,Num_col6,char_col1,char_col2)'||
	 		'  	    SELECT  bor.assembly_item_id	 			'||
			'		   ,bor.Organization_id					'||
			'		   ,BOR.ALTERNATE_ROUTING_DESIGNATOR			'||
			'		   ,BOR.ROUTING_SEQUENCE_ID				'||
			'		   ,BOS.OPERATION_SEQUENCE_ID				'||
			'		   ,BSOR.SUBSTITUTE_GROUP_NUM				'||
			'		   ,BSOR.RESOURCE_ID					'||
			'		   ,BSOR.SCHEDULE_SEQ_NUM				'||
			'		   ,BSOR.REPLACEMENT_GROUP_NUM				'||
			'		   ,'''||l_varchar_col_rec.column_name||'''		'||
			'		   ,bsor.'||l_varchar_col_rec.column_name||'		'||
			'	    FROM   bom_operational_routings bor, bom_operation_sequences bos,'||
			'		   bom_sub_operation_resources bsor			'||
			'	    WHERE   1=1							'||
			'	    AND	   bos.routing_sequence_id=bor.routing_sequence_id	'||
			'	    AND    bsor.operation_sequence_id=bos.operation_sequence_id	'||
			'	    AND    bor.assembly_item_id ='||l_item_id||'		'||
			'	    AND    bor.organization_id  = '||l_org_id||'		'||
			'	    AND    bsor.'||l_varchar_col_rec.column_name||' LIKE ''% ''	'||
			'	    AND	   rownum < '||(row_limit/25);

Line: 1214

	sqltxt:='	Select     Inventory_Item_Id 	  "Assembly Item Id",		     '||
		'	 	   Organization_id	  "Organization id",		     '||
		'		   char_Col3		  "ALTERNATE ROUTING DESIGNATOR",    '||
		'		   Num_Col1		  "ROUTING SEQUENCE ID",	     '||
		'		   Num_Col2		  "OPERATION SEQUENCE ID",	     '||
		'		   Num_Col3		  "SUBSTITUTE GROUP NUM",	     '||
		'		   Num_Col4		  "RESOURCE ID",		     '||
		'		   Num_Col5		  "SCHEDULE SEQ NUM",		     '||
		'		   Num_Col6		  "REPLACEMENT GROUP NUM",	     '||
		'		   Char_Col1		  "Column Name",		     '||
		'	 	   Char_Col2		  "Column Value"		     '||
		'	From	   bom_diag_temp_tab	  where 1=1			     ';

Line: 1242

	 delete from bom_diag_temp_tab; -- Clear the temporary tables

Line: 1247

	 sql_stmt :=' Insert into bom_diag_temp_tab (Inventory_item_id,Organization_id,		'||
 		 	'     char_col3,Num_col1,Num_col2,Num_col3,Num_col4,char_col1,char_col2)'||
	 		'  	    SELECT  bor.assembly_item_id	 			'||
			'		   ,bor.Organization_id					'||
			'		   ,BOR.ALTERNATE_ROUTING_DESIGNATOR			'||
			'		   ,BOR.ROUTING_SEQUENCE_ID				'||
			'		   ,BOS.OPERATION_SEQUENCE_ID				'||
			'		   ,BON.FROM_OP_SEQ_ID					'||
			'		   ,BON.TO_OP_SEQ_ID					'||
			'		   ,'''||l_varchar_col_rec.column_name||'''		'||
			'		   ,bon.'||l_varchar_col_rec.column_name||'		'||
			'	    FROM   bom_operational_routings bor, bom_operation_sequences bos,'||
			'		   bom_operation_networks bon				'||
			'	    WHERE   1=1							'||
			'	    AND    bos.routing_sequence_id=bor.routing_sequence_id	'||
			'	    AND    bon.to_op_seq_id=bos.operation_sequence_id		'||
			'	    AND    bor.assembly_item_id ='||l_item_id||'		'||
			'	    AND    bor.organization_id  = '||l_org_id||'		'||
			'	    AND    bon.'||l_varchar_col_rec.column_name||' LIKE ''% ''	'||
			'	    AND    rownum < '||(row_limit/25);

Line: 1275

	sqltxt:='	Select     Inventory_Item_Id 	  "Assembly Item Id",		     '||
		'	 	   Organization_id	  "Organization id",		     '||
		'		   char_Col3		  "ALTERNATE ROUTING DESIGNATOR",    '||
		'		   Num_Col1		  "ROUTING SEQUENCE ID",	     '||
		'		   Num_Col2		  "OPERATION SEQUENCE ID",	     '||
		'		   Num_Col3		  "FROM OP SEQ ID",		     '||
		'		   Num_Col4		  "TO OP SEQ ID",		     '||
		'		   Char_Col1		  "Column Name",		     '||
		'	 	   Char_Col2		  "Column Value"		     '||
		'	From	   bom_diag_temp_tab	  where 1=1			     ';

Line: 1329

		( E.g. FRM-40654 : Record has been updated.)

		Inputs for fields OrgId and ItemID are mandatory. ';