ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 07E85AA7EECD4D53A64ACE0279A144B0Y select null action_, null status_, null message_, null modified_columns_, mp.organization_code, maag.assignment_group_name abc_group, msiv.concatenated_segments item, msiv.description item_description, mac.abc_class_name abc_class, mac.description abc_class_description, null delete_assignment, to_number(null) upload_row from mtl_parameters mp, mtl_abc_assignment_groups maag, mtl_abc_assignments maa, mtl_abc_classes mac, mtl_system_items_vl msiv where 1=1 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 mp.organization_id=maag.organization_id and maag.assignment_group_id=maa.assignment_group_id and maa.abc_class_id=mac.abc_class_id and maa.inventory_item_id=msiv.inventory_item_id and maag.organization_id=msiv.organization_id Initial version U A xxen_inv_upload.upload_abc_assignment US INV Item ABC Assignment Upload Assigns items to ABC classes within an ABC assignment group, changes an item's ABC class, or removes the assignment, as the ABC Assignment Updates window (Inventory > Planning > ABC Codes > ABC Assignments). The ABC group and its classes are set up in the ABC Groups window; an item can be assigned to any class of the group, also when the ABC compile did not include it. 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 mp.organization_code=:organization_code LOV INV Organization Code (including master) 0B22DF18E239D64AE0630100007FBD85 select ood.organization_code value, ood.organization_name description from org_organization_definitions ood where ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') 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 select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') Y US Organization Code 3 30 1=1 maag.assignment_group_name=:abc_group LOV custom select maag.assignment_group_name value, null description from mtl_parameters mp, mtl_abc_assignment_groups maag where mp.organization_code=:$flex$.organization_code and mp.organization_id=maag.organization_id order by maag.assignment_group_name US ABC Group 4 40 1=1 mac.abc_class_name=:abc_class LOV custom select mac.abc_class_name value, mac.description from mtl_parameters mp, mtl_abc_classes mac where mp.organization_code=:$flex$.organization_code and mp.organization_id=mac.organization_id order by mac.abc_class_name US ABC Class 5 50 1=1 msiv.concatenated_segments=:item LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 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 0489C29705C345F69BB4619537CECB58 Assign Items Opens an empty sheet to assign items to an ABC class of an ABC group, for example items the last ABC compile did not include. ENGINATICS 5 ABC_CLASS 6 ABC_CLASS_DESCRIPTION 2 ABC_GROUP 3 ITEM 4 ITEM_DESCRIPTION Y 1 ORGANIZATION_CODE Upload Mode C Y S Site ABC Class Item 867E023047C54A0C8A67995F95E379F4 Default Creates, changes and deletes ABC assignments of items. ENGINATICS 5 ABC_CLASS 6 ABC_CLASS_DESCRIPTION 2 ABC_GROUP 7 DELETE_ASSIGNMENT 3 ITEM 4 ITEM_DESCRIPTION Y 1 ORGANIZATION_CODE Upload Mode C,U S Site 867E023047C54A0C8A67995F95E379F4 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 ORGANIZATION_CODE LOV custom varchar2 select mp.organization_id id, mp.organization_code value, haouv.name description from mtl_parameters mp, hr_all_organization_units_vl haouv where 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 mp.organization_id=haouv.organization_id order by mp.organization_code :$flex$.organization_code Y Y 6 ABC_GROUP LOV custom varchar2 select maag.assignment_group_id id, maag.assignment_group_name value, maag.secondary_inventory description from mtl_parameters mp, mtl_abc_assignment_groups maag where mp.organization_code=:$column$.organization_code and mp.organization_id=maag.organization_id and maag.assignment_group_id in (select maagc.assignment_group_id from mtl_abc_assgn_group_classes maagc) order by maag.assignment_group_name :$flex$.abc_group Y Y 7 ITEM LOV custom varchar2 select msiv.inventory_item_id id, 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 msiv.concatenated_segments Y Y 8 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 9 ABC_CLASS LOV custom varchar2 select mac.abc_class_id id, mac.abc_class_name value, mac.description from mtl_parameters mp, mtl_abc_assignment_groups maag, mtl_abc_assgn_group_classes maagc, mtl_abc_classes mac where mp.organization_code=:$column$.organization_code and mp.organization_id=maag.organization_id and maag.assignment_group_name=:$column$.abc_group and maag.assignment_group_id=maagc.assignment_group_id and maagc.abc_class_id=mac.abc_class_id order by maagc.sequence_number Y 10 ABC_CLASS_DESCRIPTION Char varchar2 select mac.description from mtl_parameters mp, mtl_abc_classes mac where mp.organization_code=:$column$.organization_code and mp.organization_id=mac.organization_id and mac.abc_class_name=:$column$.abc_class Y 11 DELETE_ASSIGNMENT LOV varchar2 Yes 8E2FF36EDEA679D2E0530100007F1FF2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual 12 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_assignment_group_id in Number ABC_GROUP U 5 p_inventory_item_id in Number ITEM U 6 p_abc_class_id in Number Y ABC_CLASS U 7 p_delete_assignment in Char Y DELETE_ASSIGNMENT Error SQL Order By upload_row, organization_code, abc_group, item Success SQL select xu.action_, xu.status_, xu.message_, xu.modified_columns_, mp.organization_code, maag.assignment_group_name abc_group, msiv.concatenated_segments item, msiv.description item_description, nvl((select mac.abc_class_name from mtl_abc_assignments maa, mtl_abc_classes mac where maag.assignment_group_id=maa.assignment_group_id and msiv.inventory_item_id=maa.inventory_item_id and maa.abc_class_id=mac.abc_class_id),xu.abc_class) abc_class, nvl((select mac.description from mtl_abc_assignments maa, mtl_abc_classes mac where maag.assignment_group_id=maa.assignment_group_id and msiv.inventory_item_id=maa.inventory_item_id and maa.abc_class_id=mac.abc_class_id),xu.abc_class_description) abc_class_description, xu.delete_assignment, xu.upload_row from xxen_inv_item_abc_assig_3707_u xu, mtl_parameters mp, mtl_abc_assignment_groups maag, mtl_system_items_vl msiv where xu.status_code_=xxen_upload.status_success and xu.organization_code=mp.organization_code and mp.organization_id=maag.organization_id and xu.abc_group=maag.assignment_group_name and mp.organization_id=msiv.organization_id and xu.item=msiv.concatenated_segments