ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E804FC9D05634C6EB60B2D9B184A119FY select null action_, null status_, null message_, to_number(null) request_id_, null modified_columns_, mp.organization_code, msiv.concatenated_segments item, msiv.description item_description, mln.lot_number, mln.parent_lot_number parent_lot, xxen_util.meaning(msiv.shelf_life_code,'MTL_SHELF_LIFE',700) shelf_life_control, mln.origination_date, mln.expiration_date, mln.expiration_action_code expiration_action, mln.expiration_action_date, mln.retest_date, mln.maturity_date, mln.hold_date, to_number(null) upload_row from mtl_parameters mp, mtl_system_items_vl msiv, mtl_lot_numbers mln 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=msiv.organization_id and msiv.organization_id=mln.organization_id and msiv.inventory_item_id=mln.inventory_item_id and :p_upload_mode like '%'||xxen_upload.action_update and 1=1 Initial version. Updates the dates of existing lots (mtl_lot_numbers) through the public API inv_lot_api_pub.update_inv_lot, replicating the Lot Numbers form (INVTDILT). U A xxen_inv_upload.upload_lot US INV Lot Upload Updates the expiration and other dates of existing lots, as in the Lot Numbers form (Inventory > On-hand, Availability > Lots). Download existing lots with Upload Mode Create, Update, or use Create for an empty sheet and paste Organization Code, Item, Lot Number and the new dates. Every row updates an existing lot; new lots are created by receipt transactions. Expiration Date, Expiration Action Date and Retest Date can be changed only for items under shelf life control, and the Expiration Date of such an item cannot be cleared. Changing the Origination Date does not recalculate the other dates. Clearing the Expiration Action Date also clears the Expiration Action. Enginatics Upload 1=1 :p_upload_mode 1 10 :p_upload_mode 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 mln.lot_number=:lot_number LOV INV Lot Number 2450E07B1B9F64E9E0630100007FCFA7 Y select distinct mln.lot_number value, mln.description from mtl_parameters mp, mtl_system_items_vl msiv, mtl_lot_numbers mln where mp.organization_id = msiv.organization_id and mln.organization_id = msiv.organization_id and mln.inventory_item_id = msiv.inventory_item_id and (:$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 (:$flex$.item is null or xxen_util.contains(:$flex$.item,msiv.concatenated_segments) = 'Y') and (:$flex$.item_from is null or msiv.concatenated_segments >= :$flex$.item_from) and (:$flex$.item_to is null or msiv.concatenated_segments <= :$flex$.item_to) order by value, description US Lot Number 5 50 1=1 mln.expiration_date>=:expiration_date_from Date US Expiration Date From 6 60 1=1 mln.expiration_date<:expiration_date_to+1 Date :$flex$.expiration_date_from US Expiration Date To 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 REQUEST_ID_ Number number Y 5 MODIFIED_COLUMNS_ Char varchar2 Y 6 ORGANIZATION_CODE LOV custom varchar2 Y 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 select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') Y Y 7 ITEM LOV custom varchar2 Y Y 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 and msiv.lot_control_code=2 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 LOT_NUMBER LOV custom varchar2 Y select mln.lot_number value, mln.description from mtl_parameters mp, mtl_system_items_vl msiv, mtl_lot_numbers mln where mp.organization_code=:$column$.organization_code and mp.organization_id=msiv.organization_id and msiv.concatenated_segments=:$column$.item and msiv.organization_id=mln.organization_id and msiv.inventory_item_id=mln.inventory_item_id order by mln.lot_number Y Y 10 PARENT_LOT Char varchar2 select mln.parent_lot_number from mtl_parameters mp, mtl_system_items_vl msiv, mtl_lot_numbers mln where mp.organization_code=:$column$.organization_code and mp.organization_id=msiv.organization_id and msiv.concatenated_segments=:$column$.item and msiv.organization_id=mln.organization_id and msiv.inventory_item_id=mln.inventory_item_id and mln.lot_number=:$column$.lot_number Y 11 SHELF_LIFE_CONTROL Char varchar2 select xxen_util.meaning(msiv.shelf_life_code,'MTL_SHELF_LIFE',700) 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 12 ORIGINATION_DATE Date date 13 EXPIRATION_DATE Date date 14 EXPIRATION_ACTION LOV custom varchar2 Y select mav.action_code value, mav.description from mtl_actions_vl mav where mav.disable_flag='N' order by mav.action_code 15 EXPIRATION_ACTION_DATE Date date 16 RETEST_DATE Date date 17 MATURITY_DATE Date date 18 HOLD_DATE Date date 19 UPLOAD_ROW Number number Y Error SQL Order By upload_row, organization_code, item, lot_number Success SQL select xu.action_, xu.status_, xu.message_, xu.request_id_, xu.modified_columns_, nvl(x.organization_code,xu.organization_code) organization_code, nvl(x.item,xu.item) item, nvl(x.item_description,xu.item_description) item_description, nvl(x.lot_number,xu.lot_number) lot_number, x.parent_lot, x.shelf_life_control, x.origination_date, x.expiration_date, x.expiration_action, x.expiration_action_date, x.retest_date, x.maturity_date, x.hold_date, xu.upload_row from xxen_inv_lot_upload_5707_u xu, ( select mp.organization_code, msiv.concatenated_segments item, msiv.description item_description, mln.lot_number, mln.parent_lot_number parent_lot, xxen_util.meaning(msiv.shelf_life_code,'MTL_SHELF_LIFE',700) shelf_life_control, mln.origination_date, mln.expiration_date, mln.expiration_action_code expiration_action, mln.expiration_action_date, mln.retest_date, mln.maturity_date, mln.hold_date from mtl_parameters mp, mtl_system_items_vl msiv, mtl_lot_numbers mln where mp.organization_id=msiv.organization_id and msiv.organization_id=mln.organization_id and msiv.inventory_item_id=mln.inventory_item_id ) x where xu.status_code_=xxen_upload.status_success and xu.organization_code=x.organization_code(+) and xu.item=x.item(+) and xu.lot_number=x.lot_number(+)