The following lines contain the word 'select', 'insert', 'update' or 'delete':
sqltxt VARCHAR2(9999); -- SQL select statement
JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
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;
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.
';
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'' '||
' ) '||
' ) ';
select category_set_id,count(*)
from mtl_item_categories
where category_set_id = :cat_set_id
group by category_set_id ;
select category_set_id,count(*)
from mtl_category_set_valid_cats
where category_set_id = :cat_set_id
group by category_set_id ;
select category_set_id,count(*)
from cst_cost_updates
where category_set_id = :cat_set_id
group by category_set_id ;
select category_set_id,count(*)
from cst_cost_type_history
where category_set_id = :cat_set_id
group by category_set_id ;
select category_set_id,count(*)
from mtl_default_category_sets
where category_set_id =:cat_set_id
group by category_set_id ;
select category_set_id,count(*)
from cst_ap_variance_batches
where category_set_id = :cat_set_id
group by category_set_id ;
select category_set_id,count(*)
from cst_item_overhead_defaults
where category_set_id = :cat_set_id
group by category_set_id ;
select category_set_id,count(*)
from cst_sc_rollup_history
where category_set_id = :cat_set_id
group by category_set_id ;
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'');
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 ;