ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 56BB81985AD642B9E0630100007FA016Y select to_char(null) action_, to_char(null) status_, to_char(null) message_, to_char(null) modified_columns_, x.organization_code, x.item, x.item_description, x.data_level, x.revision, x.attribute_group, x.row_number, x.attribute, xxen_ego_upload.attr_display_value(x.extension_id,x.attr_group_id,x.attr_name) value, xxen_ego_upload.attr_uom_value(x.extension_id,x.attr_group_id,x.attr_name) uom, to_char(null) delete_row, x.extension_id, row_number() over (order by x.organization_code, x.item, x.data_level_id, x.revision, x.attribute_group, x.row_number, x.attr_sequence) upload_row from ( select mp.organization_code, msiv.concatenated_segments item, msiv.description item_description, edlv.user_data_level_name data_level, mirb.revision, eagv.attr_group_disp_name attribute_group, case when eagv.multi_row_code='Y' then dense_rank() over (partition by emsieb.inventory_item_id, emsieb.organization_id, emsieb.data_level_id, emsieb.revision_id, emsieb.attr_group_id order by emsieb.extension_id) end row_number, eav.attr_display_name attribute, eav.attr_name, eav.sequence attr_sequence, emsieb.attr_group_id, emsieb.extension_id, emsieb.data_level_id from ego_mtl_sy_items_ext_b emsieb, mtl_parameters mp, mtl_system_items_vl msiv, ego_data_level_vl edlv, mtl_item_revisions_b mirb, ego_attr_groups_v eagv, ego_attrs_v eav where 1=1 and emsieb.organization_id=mp.organization_id and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and emsieb.inventory_item_id=msiv.inventory_item_id and emsieb.organization_id=msiv.organization_id and emsieb.data_level_id=edlv.data_level_id and edlv.data_level_name in ('ITEM_LEVEL','ITEM_ORG','ITEM_REVISION_LEVEL') and emsieb.revision_id=mirb.revision_id(+) and emsieb.attr_group_id=eagv.attr_group_id and exists (select null from ego_obj_ag_assocs_b eoaa, fnd_objects fo where fo.obj_name='EGO_ITEM' and eoaa.object_id=fo.object_id and eoaa.attr_group_id=emsieb.attr_group_id and eoaa.enabled_flag='Y') and eav.attr_group_type=eagv.attr_group_type and eav.attr_group_name=eagv.attr_group_name and eav.application_id=eagv.application_id and eav.enabled_flag='Y' ) x Review corrections: reuse shared INV Organization Code (including master) and INV Item LOVs, typed null framework columns, active item catalog categories only, row tagging and attribute group resolution fixes. U A xxen_ego_upload.upload_item_uda US INV Item User Defined Attribute Upload Item user-defined attribute (UDA) values upload for Oracle Product Information Management (PIM / Advanced Product Catalog). Creates, updates and deletes item user-defined attribute values (attribute groups defined in the item catalog) at the item, item organization and item revision data levels through the Oracle EGO user attributes API. Each upload row carries one attribute value; the rows of one attribute group row instance (same item, data level, attribute group and row number) are processed together. For multi-row attribute groups, enter a Row Number to group the attribute values belonging to one row and include the group's unique key attributes on every row. Set Delete Row to Yes to delete a complete attribute group row. Enter date attribute values in format DD-MON-YYYY, and enter a UOM for numeric attributes that have a unit of measure class. Users need PIM edit privileges on the items (for example through an item owner role); the upload enforces the same item and attribute group security as the PIM pages. Only attribute groups associated to item catalog categories are included. The seeded Detailed Description and Images item features are not user-defined attribute groups and are excluded, and supplier association data levels (item supplier, item supplier site, item supplier site organization) are not supported. Enginatics Upload 1=1 1 10 1=1 :p_upload_mode like '%' || xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 Y 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 mp.organization_code=:organization_code LOV INV Organization Code (including master) 0B22DF18E239D64AE0630100007FBD85 Y select ood.organization_code value, ood.organization_name description from org_organization_definitions ood where ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate order by ood.organization_code US Organization Code 3 30 1=1 msiv.concatenated_segments=:item LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 Y Y select msiv.concatenated_segments value, msiv.description||' ('||mp.organization_code||')' description from mtl_system_items_vl msiv, mtl_parameters mp where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and msiv.organization_id=mp.organization_id order by mp.organization_code, msiv.concatenated_segments US Item 4 40 1=1 msiv.item_catalog_group_id=:item_catalog_group_id LOV custom Y select micgbk.item_catalog_group_id id, micgbk.concatenated_segments value, micgbk.description from mtl_item_catalog_groups_b_kfv micgbk where nvl(micgbk.inactive_date,sysdate)>=sysdate order by 2 US Item Catalog Category 5 50 1=1 eagv.attr_group_disp_name=:attribute_group LOV custom Y select eagv.attr_group_disp_name value, eagv.description from ego_attr_groups_v eagv where eagv.attr_group_type='EGO_ITEMMGMT_GROUP' order by 1 US Attribute Group 6 60 1=1 edlv.user_data_level_name=:data_level LOV custom Y select edlv.user_data_level_name value, null description from ego_data_level_vl edlv where edlv.attr_group_type='EGO_ITEMMGMT_GROUP' and edlv.data_level_name in ('ITEM_LEVEL','ITEM_ORG','ITEM_REVISION_LEVEL') order by 1 US Data Level 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 ORGANIZATION_CODE LOV varchar2 3 INV Organization Code (including master) 0B22DF18E239D64AE0630100007FBD85 Y select ood.organization_code value, ood.organization_name description from org_organization_definitions ood where ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate order by ood.organization_code Y 6 ITEM LOV custom varchar2 Y Y select msiv.concatenated_segments value, msiv.description from mtl_parameters mp, mtl_system_items_vl msiv where mp.organization_code=:$column$.organization_code and mp.organization_id=msiv.organization_id order by 1 Y 7 ITEM_DESCRIPTION Char varchar2 select msiv.description from mtl_parameters mp, mtl_system_items_vl msiv where mp.organization_code=:$column$.organization_code and mp.organization_id=msiv.organization_id and msiv.concatenated_segments=:$column$.item Y 8 DATA_LEVEL LOV custom varchar2 Y select edlv.user_data_level_name value, null description from ego_data_level_vl edlv where edlv.attr_group_type='EGO_ITEMMGMT_GROUP' and edlv.data_level_name in ('ITEM_LEVEL','ITEM_ORG','ITEM_REVISION_LEVEL') order by 1 select edlv.user_data_level_name from ego_data_level_vl edlv where edlv.attr_group_type='EGO_ITEMMGMT_GROUP' and edlv.data_level_name='ITEM_LEVEL' Y 9 REVISION LOV custom varchar2 select mirv.revision value, mirv.description from mtl_parameters mp, mtl_system_items_vl msiv, mtl_item_revisions_vl mirv where mp.organization_code=:$column$.organization_code and mp.organization_id=msiv.organization_id and msiv.concatenated_segments=:$column$.item and msiv.inventory_item_id=mirv.inventory_item_id and msiv.organization_id=mirv.organization_id order by 1 10 ATTRIBUTE_GROUP LOV custom varchar2 Y select eagv.attr_group_disp_name value, eagv.description from ego_obj_ag_assocs_b eoaa, ego_data_level_vl edlv, ego_attr_groups_v eagv where eoaa.object_id=(select fo.object_id from fnd_objects fo where fo.obj_name='EGO_ITEM') and eoaa.enabled_flag='Y' and eoaa.data_level_id=edlv.data_level_id and edlv.attr_group_type='EGO_ITEMMGMT_GROUP' and edlv.user_data_level_name=:$column$.data_level and eoaa.attr_group_id=eagv.attr_group_id and eoaa.classification_code in ( select to_char(micgb.item_catalog_group_id) from mtl_item_catalog_groups_b micgb start with micgb.item_catalog_group_id=(select msiv.item_catalog_group_id from mtl_parameters mp, mtl_system_items_vl msiv where mp.organization_code=:$column$.organization_code and mp.organization_id=msiv.organization_id and msiv.concatenated_segments=:$column$.item) connect by prior micgb.parent_catalog_group_id=micgb.item_catalog_group_id ) order by 1 Y 11 ROW_NUMBER Number number select 'Groups the attribute value rows belonging to one row of a multi-row attribute group. Leave blank for single-row attribute groups.' from dual 12 ATTRIBUTE LOV custom varchar2 Y select eav.attr_display_name value, eav.description from ego_attr_groups_v eagv, ego_attrs_v eav where eagv.attr_group_type='EGO_ITEMMGMT_GROUP' and eagv.attr_group_disp_name=:$column$.attribute_group and eav.attr_group_type=eagv.attr_group_type and eav.attr_group_name=eagv.attr_group_name and eav.application_id=eagv.application_id and eav.enabled_flag='Y' order by eav.sequence Y 13 VALUE LOV custom varchar2 select decode(ffvs.validation_type,'I',ffvv.flex_value,ffvv.flex_value_meaning) value, ffvv.description from ego_attr_groups_v eagv, ego_attrs_v eav, fnd_flex_value_sets ffvs, fnd_flex_values_vl ffvv where eagv.attr_group_type='EGO_ITEMMGMT_GROUP' and eagv.attr_group_disp_name=:$column$.attribute_group and eav.attr_group_type=eagv.attr_group_type and eav.attr_group_name=eagv.attr_group_name and eav.application_id=eagv.application_id and eav.attr_display_name=:$column$.attribute and eav.value_set_id=ffvs.flex_value_set_id and ffvs.validation_type in ('I','X') and ffvs.flex_value_set_id=ffvv.flex_value_set_id and ffvv.enabled_flag='Y' and sysdate between nvl(ffvv.start_date_active,sysdate) and nvl(ffvv.end_date_active,sysdate) order by 1 14 UOM LOV custom varchar2 select muomv.uom_code value, muomv.unit_of_measure_tl description from ego_attr_groups_v eagv, ego_attrs_v eav, mtl_units_of_measure_vl muomv where eagv.attr_group_type='EGO_ITEMMGMT_GROUP' and eagv.attr_group_disp_name=:$column$.attribute_group and eav.attr_group_type=eagv.attr_group_type and eav.attr_group_name=eagv.attr_group_name and eav.application_id=eagv.application_id and eav.attr_display_name=:$column$.attribute and eav.uom_class=muomv.uom_class and nvl(muomv.disable_date,sysdate+1)>sysdate order by 1 15 DELETE_ROW LOV custom varchar2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual where exists ( select null from mtl_parameters mp, mtl_system_items_vl msiv, ego_attr_groups_v eagv, ego_mtl_sy_items_ext_b emsieb where mp.organization_code=:$column$.organization_code and mp.organization_id=msiv.organization_id and msiv.concatenated_segments=:$column$.item and eagv.attr_group_type='EGO_ITEMMGMT_GROUP' and eagv.attr_group_disp_name=:$column$.attribute_group and emsieb.inventory_item_id=msiv.inventory_item_id and emsieb.organization_id=msiv.organization_id and emsieb.attr_group_id=eagv.attr_group_id ) 16 EXTENSION_ID Number number Y Y 17 UPLOAD_ROW Number number Y Error SQL Order By upload_row Success SQL select xu.action_, xu.status_, xu.message_, xu.modified_columns_, xu.organization_code, xu.item, nvl((select msiv.description from mtl_parameters mp, mtl_system_items_vl msiv where xu.organization_code=mp.organization_code and mp.organization_id=msiv.organization_id and xu.item=msiv.concatenated_segments),xu.item_description) item_description, xu.data_level, xu.revision, xu.attribute_group, xu.row_number, xu.attribute, nvl(xxen_ego_upload.success_display_value(xu.organization_code,xu.item,xu.data_level,xu.revision,xu.attribute_group,xu.attribute,xu.extension_id),xu.value) value, nvl(xxen_ego_upload.success_uom_value(xu.organization_code,xu.item,xu.data_level,xu.revision,xu.attribute_group,xu.attribute,xu.extension_id),xu.uom) uom, xu.delete_row, xu.extension_id, xu.upload_row from xxen_inv_item_user_defi_0012_u xu where xu.status_code_=xxen_upload.status_success