APPS.BOM_DIAGUNITTEST_CATHLCHK SQL Statements

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

Line: 18

 sqltxt      VARCHAR2(9999);  -- SQL select statement

Line: 36

JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;

Line: 70

sqltxt := ' Select * from (	'||
	  '	select mcsv.category_set_name "Category Set Name",						'||
	  '	decode(mcsv.control_level,1,''Master'',2,''Org'')	"Control Level",			'||
	  '	mp.organization_code "Organization Code",							'||
	  '	mif.padded_item_number "Item Number",count(*) "Count of categories assigned",			'||
	  '	mic.category_set_id "Category Set Id",								'||
	  '	mic.organization_id "Organization Id" ,mic.inventory_item_id "Item Id"				'||
	  '	from mtl_item_categories mic, mtl_category_sets_v mcsv,						'||
	  '	     mtl_parameters mp, mtl_item_flexfields mif							'||
	  '	where mif.inventory_item_id = mic.inventory_item_id						'||
	  '	and   mif.organization_id   = mic.organization_id						'||
	  '	and   mic.organization_id   = mp.organization_id						'||
	  '	and   mcsv.category_set_id  = mic.category_set_id						'||
	  '	and   mcsv.mult_item_cat_assign_flag = ''N''							'||
    	  '	group by mcsv.category_set_name,mcsv.control_level, mp.organization_code,mif.padded_item_number,'||
	  '	         mic.category_set_id,mic.organization_id,mic.inventory_item_id				'||
	  '	having count(*) > 1										'||
	  '	order by mcsv.category_set_name,mp.organization_code,mif.padded_item_number			'||
	  '	) where rownum <  '||row_limit;

Line: 116

 5) Delete the unwanted item category assignments.

 6) Save the changes to the Item.



 Case 2: For Master controlled Category Sets.

 1) Open (N) Inventory > Master Items form.

 2) Query for an Item fetched above.

 3) Go to (M) Tools > Categories.

 4) Query for the corresponding Category Set containing multiple assignments.

    (i.e.) This item will be assigned to multiple categories

	under this category set which is not allowed.

 5) Delete the unwanted item category assignments.

 6) Save the changes to the Item.

';

Line: 142

sqltxt := '	  select  mcsv.category_set_name	"CATEGORY SET NAME",		  '||
	  '	  mcsv.description			"CATEGORY SET DESCRIPTION",	  '||
	  '	  fifsv.id_flex_structure_code		"ID FLEX STRUCTURE CODE",	  '||
	  '	  fifsv.id_flex_structure_name		"ID FLEX STRUCTURE NAME",	  '||
	  '	  fifsv.description			"ID FLEX STRUCTURE DESCRIPTION",  '||
	  '	  mcsv.category_set_id			"CATEGORY SET ID",		  '||
	  '	  mcsv.structure_id			"STRUCTURE ID",			  '||
	  '	  mcsv.default_category_id		"DEFAULT CATEGORY ID",		  '||
	  '	  mcsv.validate_flag			"VALIDATE FLAG",		  '||
	  '	  mcsv.control_level			"CONTROL LEVEL",		  '||
	  '	  mcsv.mult_item_cat_assign_flag	"MULT ITEM CAT ASSIGN FLAG"	  '||
	  '	  from   mtl_category_sets_vl mcsv, fnd_id_flex_structures_vl fifsv	  '||
	  '	  where  mcsv.structure_id=fifsv.id_flex_num				  '||
	  '	  and    fifsv.id_flex_code = ''MCAT''					  '||
	  '	  and    (  ( mcsv.category_set_id =5 					  '||
	  '		        and fifsv.id_flex_structure_code  <> ''SALES_CATEGORIES'' '||
	  '			  )							  '||
	  '			or							  '||
	  '			  ( mcsv.category_set_id in (11,12) 			  '||
	  '		        and fifsv.id_flex_structure_code <> ''CARTONIZATION''	  '||
	  '			  )							  '||
	  '		   )								  ';

Line: 191

        select category_set_id,count(*)
        from mtl_item_categories
        where category_set_id = :cat_set_id
        group by category_set_id ;

Line: 196

        select category_set_id,count(*)
        from mtl_category_set_valid_cats
        where category_set_id = :cat_set_id
        group by category_set_id ;

Line: 201

        select category_set_id,count(*)
        from cst_cost_updates
        where category_set_id = :cat_set_id
        group by category_set_id ;

Line: 206

        select category_set_id,count(*)
        from cst_cost_type_history
        where category_set_id  = :cat_set_id
        group by category_set_id ;

Line: 211

        select category_set_id,count(*)
        from mtl_default_category_sets
        where category_set_id =:cat_set_id
        group by category_set_id ;

Line: 216

        select category_set_id,count(*)
        from cst_ap_variance_batches
        where category_set_id = :cat_set_id
        group by category_set_id ;

Line: 221

        select category_set_id,count(*)
        from cst_item_overhead_defaults
        where category_set_id = :cat_set_id
        group by category_set_id ;

Line: 226

        select category_set_id,count(*)
        from cst_sc_rollup_history
        where category_set_id = :cat_set_id
        group by category_set_id ;

Line: 236

	select  id_flex_structure_code "Structure Code",
		id_flex_num "New Structure Id"
	from    fnd_id_flex_structures_vl
	where   id_flex_code = ''MCAT''
	and     id_flex_structure_code
		in (''SALES_CATEGORIES'',''CARTONIZATION'');

Line: 243

	5) Use the below update statement to correct the seeded category sets.
	
   The bind variables
	
   cat_set_id stands for the category_set_id of the corrupted seeded category set.
	
   old_structure_id stands for the incorrect structure_id
	
   currently associated with this category set.
	
   new_structure_id stands for the New structure Id
	
   retrieved through the select statement in step 4.
	

	update mtl_category_sets_b
	set    structure_id = :new_structure_id
	where  category_set_id = :cat_set_id
	and    structure_id = :old_structure_id ;