ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 450BCFECBE544A5DA09EF2ECA3B2097DY select null action_, null status_, null message_, null modified_columns_, mcsv.category_set_name category_set, mcsv.description, mcsv.structure_name flex_structure, mcsv.control_level_disp controlled_at, mcbk_d.concatenated_segments default_category, xxen_util.yes(mcsv.mult_item_cat_assign_flag) allow_multiple_assignments, xxen_util.yes(mcsv.validate_flag) enforce_valid_categories, mcbk_v.concatenated_segments valid_category, null delete_valid_category, to_number(null) upload_row from mtl_category_sets_v mcsv, mtl_categories_b_kfv mcbk_d, mtl_category_set_valid_cats mcsvc, mtl_categories_b_kfv mcbk_v where 1=1 and nvl(mcsv.hierarchy_enabled,'N')='N' and mcsv.default_category_id=mcbk_d.category_id(+) and mcsv.category_set_id=mcsvc.category_set_id(+) and mcsvc.category_id=mcbk_v.category_id(+) Initial version U A xxen_inv_upload.upload_category_set US INV Category Set Upload Creates and updates item category sets and their list of valid categories, as the Category Sets window (Setup > Items > Categories > Category Sets). Each row carries the category set and one valid category. Add a valid category with a new row for the category set, and remove one with Delete Valid Category. Category sets with a category hierarchy (Product Information Management catalogs) are maintained in the catalog pages and are not included. The flex structure of a category set can only be changed while it has no item assignments or valid categories. Enginatics Upload 1=1 1 10 1=1 :p_upload_mode like '%'||xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create||','||xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C,U Y US Upload Mode 2 20 1=1 mcsv.category_set_name=:category_set LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name US Category Set 3 30 1=1 mcsv.structure_name=:flex_structure LOV custom select fifsv.id_flex_structure_name value, fifsv.description from fnd_id_flex_structures_vl fifsv where fifsv.application_id=401 and fifsv.id_flex_code='MCAT' and fifsv.enabled_flag='Y' order by fifsv.id_flex_structure_name US Flex Structure 0BC9DFC392E441D68DFE253F01D76CA3 Create Category Sets Opens an empty sheet to create new category sets with their valid categories, one row per valid category. ENGINATICS 6 ALLOW_MULTIPLE_ASSIGNMENTS 1 CATEGORY_SET 4 CONTROLLED_AT 5 DEFAULT_CATEGORY 2 DESCRIPTION Y 7 ENFORCE_VALID_CATEGORIES 3 FLEX_STRUCTURE 8 VALID_CATEGORY Upload Mode C Y S Site Category Set Flex Structure B446A05FF5D743ADBEBB4EDBBF468AA4 Default Creates and updates category sets and their valid categories. ENGINATICS 6 ALLOW_MULTIPLE_ASSIGNMENTS 1 CATEGORY_SET 4 CONTROLLED_AT 5 DEFAULT_CATEGORY 9 DELETE_VALID_CATEGORY 2 DESCRIPTION Y 7 ENFORCE_VALID_CATEGORIES 3 FLEX_STRUCTURE 8 VALID_CATEGORY Upload Mode C,U S Site B446A05FF5D743ADBEBB4EDBBF468AA4 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 CATEGORY_SET LOV custom varchar2 Y select mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where nvl(mcsv.hierarchy_enabled,'N')='N' order by mcsv.category_set_name :$flex$.category_set Y Y 6 DESCRIPTION Char varchar2 Y 7 FLEX_STRUCTURE LOV custom varchar2 select fifsv.id_flex_num id, fifsv.id_flex_structure_name value, fifsv.description from fnd_id_flex_structures_vl fifsv where fifsv.application_id=401 and fifsv.id_flex_code='MCAT' and fifsv.enabled_flag='Y' order by fifsv.id_flex_structure_name :$flex$.flex_structure Y 8 CONTROLLED_AT LOV custom varchar2 select ml.lookup_code id, ml.meaning value, null description from mfg_lookups ml where ml.lookup_type='ITEM_CONTROL_LEVEL_GUI' order by ml.lookup_code Y 9 DEFAULT_CATEGORY LOV custom varchar2 select mcbk.category_id id, mcbk.concatenated_segments value, mcv.description from fnd_id_flex_structures_vl fifsv, mtl_categories_b_kfv mcbk, mtl_categories_vl mcv where fifsv.application_id=401 and fifsv.id_flex_code='MCAT' and fifsv.id_flex_structure_name=:$column$.flex_structure and fifsv.id_flex_num=mcbk.structure_id and mcbk.category_id=mcv.category_id order by mcbk.concatenated_segments Y 10 ALLOW_MULTIPLE_ASSIGNMENTS LOV varchar2 Yes 8E2FF36EDEA679D2E0530100007F1FF2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual 11 ENFORCE_VALID_CATEGORIES LOV varchar2 Yes 8E2FF36EDEA679D2E0530100007F1FF2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual 12 VALID_CATEGORY LOV custom varchar2 select mcbk.category_id id, mcbk.concatenated_segments value, mcv.description from fnd_id_flex_structures_vl fifsv, mtl_categories_b_kfv mcbk, mtl_categories_vl mcv where fifsv.application_id=401 and fifsv.id_flex_code='MCAT' and fifsv.id_flex_structure_name=:$column$.flex_structure and fifsv.id_flex_num=mcbk.structure_id and mcbk.category_id=mcv.category_id order by mcbk.concatenated_segments 13 DELETE_VALID_CATEGORY LOV varchar2 Yes 8E2FF36EDEA679D2E0530100007F1FF2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual 14 UPLOAD_ROW Number number Y U 1 action_ in Char ACTION_CODE_ U 2 status_ out Char STATUS_CODE_ U 3 message_ out Char MESSAGE_ U 4 p_category_set in Char CATEGORY_SET U 5 p_description in Char Y DESCRIPTION U 6 p_structure_id in Number Y FLEX_STRUCTURE U 7 p_control_level in Number Y CONTROLLED_AT U 8 p_default_category_id in Number Y DEFAULT_CATEGORY U 9 p_allow_multiple_assignments in Char Y ALLOW_MULTIPLE_ASSIGNMENTS U 10 p_enforce_valid_categories in Char Y ENFORCE_VALID_CATEGORIES U 11 p_valid_category_id in Number Y VALID_CATEGORY U 12 p_delete_valid_category in Char Y DELETE_VALID_CATEGORY Error SQL Order By upload_row, category_set, valid_category Success SQL select xu.action_, xu.status_, xu.message_, xu.modified_columns_, mcsv.category_set_name category_set, mcsv.description, mcsv.structure_name flex_structure, mcsv.control_level_disp controlled_at, mcbk_d.concatenated_segments default_category, xxen_util.yes(mcsv.mult_item_cat_assign_flag) allow_multiple_assignments, xxen_util.yes(mcsv.validate_flag) enforce_valid_categories, nvl((select mcbk_v.concatenated_segments from mtl_category_set_valid_cats mcsvc, mtl_categories_b_kfv mcbk_v where mcsv.category_set_id=mcsvc.category_set_id and mcsvc.category_id=mcbk_v.category_id and mcbk_v.concatenated_segments=xu.valid_category),xu.valid_category) valid_category, xu.delete_valid_category, xu.upload_row from xxen_inv_category_set_u_4239_u xu, mtl_category_sets_v mcsv, mtl_categories_b_kfv mcbk_d where xu.status_code_=xxen_upload.status_success and xu.category_set=mcsv.category_set_name and mcsv.default_category_id=mcbk_d.category_id(+)