ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 5B14590550511A41E0630100007FD228Y select case when :confirm_all is not null then xxen_upload.action_meaning(xxen_upload.action_update) end action_, case when :confirm_all is not null then xxen_upload.status_meaning(xxen_upload.status_new) end status_, case when :confirm_all is not null then xxen_util.description('U_EXCEL_MSG_VALIDATION_PENDING','XXEN_REPORT_TRANSLATIONS',0) end message_, null modified_columns_, :ship_confirm ship_confirm, x.*, 0 upload_row from ( select mp.organization_code, ooha.order_number, rtrim(oola.line_number||'.'||oola.shipment_number||'.'||oola.option_number||'.'||oola.component_number||'.'||oola.service_number,'.') line_number, hp.party_name customer, hcsua.location ship_to, wnd.name delivery, (select listagg(y.name,', ') within group (order by y.name) from (select distinct wt.name, wdl.delivery_id from wsh_delivery_legs wdl, wsh_trip_stops wts, wsh_trips wt where wdl.pick_up_stop_id=wts.stop_id and wts.trip_id=wt.trip_id) y where wnd.delivery_id=y.delivery_id) trip, mmtt.pick_slip_number pick_slip, mtrh.request_number move_order, xxen_util.meaning(wdt.status,'WMS_TASK_STATUS',700) task_status, msiv.concatenated_segments item, msiv.description item_description, wdd.requested_quantity, mmtt.transaction_uom uom, mmtt.subinventory_code subinventory, milk.concatenated_segments locator, mtlt.lot_number, nvl(mtlt.transaction_quantity,mmtt.transaction_quantity) picked_quantity, wlpn.license_plate_number lpn, mmtt.transfer_subinventory staging_subinventory, milk_st.concatenated_segments staging_locator, mmtt.transaction_temp_id, mmtt.move_order_line_id, wdd.delivery_detail_id, mp.organization_id, mtlt.lot_number allocated_lot from mtl_parameters mp, mtl_material_transactions_temp mmtt, wsh_delivery_details wdd, wsh_delivery_assignments wda, wsh_new_deliveries wnd, oe_order_lines_all oola, oe_order_headers_all ooha, hz_cust_accounts hca, hz_parties hp, hz_cust_site_uses_all hcsua, mtl_transaction_lots_temp mtlt, mtl_txn_request_lines mtrl, mtl_txn_request_headers mtrh, wms_dispatched_tasks wdt, mtl_system_items_vl msiv, mtl_item_locations_kfv milk, mtl_item_locations_kfv milk_st, wms_license_plate_numbers wlpn where 1=1 and mp.organization_id=mmtt.organization_id and mmtt.transaction_source_type_id=2 and mmtt.move_order_line_id=wdd.move_order_line_id and wdd.released_status='S' and wdd.delivery_detail_id=wda.delivery_detail_id(+) and wda.delivery_id=wnd.delivery_id(+) and wdd.source_line_id=oola.line_id and oola.header_id=ooha.header_id and ooha.sold_to_org_id=hca.cust_account_id and hca.party_id=hp.party_id and oola.ship_to_org_id=hcsua.site_use_id(+) and mmtt.transaction_temp_id=mtlt.transaction_temp_id(+) and mmtt.move_order_line_id=mtrl.line_id and mtrl.header_id=mtrh.header_id and mmtt.transaction_temp_id=wdt.transaction_temp_id(+) and mmtt.organization_id=msiv.organization_id and mmtt.inventory_item_id=msiv.inventory_item_id and mmtt.locator_id=milk.inventory_location_id(+) and mmtt.transfer_to_location=milk_st.inventory_location_id(+) and mmtt.transfer_lpn_id=wlpn.lpn_id(+) ) x Initial version U XXEN_WSH_UPLOAD A xxen_wsh_upload.upload_pick xxen_wsh_upload.confirm_pick xxen_wsh_upload.validate_pick( p_organization_code=>:$column$.organization_code, p_item=>:$column$.item, p_allocated_lot=>:$column$.allocated_lot, p_lot_number=>:$column$.lot_number, p_picked_quantity=>:$column$.picked_quantity, p_lpn=>:$column$.lpn ) US WSH Pick Confirm Upload WSH Pick Confirm Upload ======================= Confirms released sales order pick tasks in bulk from Excel: the picked quantity, the subinventory, locator and lot actually picked, the staging location and, in WMS enabled organizations, the LPN the material was packed into. Download the released to warehouse lines of an organization, optionally restricted to an order, delivery, trip, move order (pick slip), customer or item. One row is created per pick allocation, and per allocated lot for lot controlled items. Overwrite the pre-filled allocation where the pick differs, enter the LPN and upload. Each pick task is confirmed through the standard pick confirm API, which moves the material to the staging subinventory and sets the delivery line to Staged/Pick Confirmed. Unknown LPN names are created as new LPNs in the organization. Parameter Confirm All Downloaded Lines pre-flags every downloaded row, so an unchanged pick list can be uploaded as it is. Parameter Ship Confirm additionally ship confirms a delivery as soon as its last released line has been confirmed by the upload. Unpicked quantities of the delivery are backordered. Serial controlled items are not supported. Enginatics Upload 1=1 :confirm_all :ship_confirm 1 10 1=1 mp.organization_code=:organization_code LOV custom Y select oav.organization_code value, oav.organization_name description from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id order by oav.organization_code select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') Y US Organization Code 2 20 1=1 ooha.order_number=:order_number LOV custom Y select ooha.order_number value, hp.party_name||' ('||xxen_util.meaning(ooha.flow_status_code,'FLOW_STATUS',660)||')' description from oe_order_headers_all ooha, hz_cust_accounts hca, hz_parties hp where ooha.header_id in ( select oola.header_id from mtl_parameters mp, mtl_material_transactions_temp mmtt, wsh_delivery_details wdd, oe_order_lines_all oola where mp.organization_code=:$flex$.organization_code and mp.organization_id=mmtt.organization_id and mmtt.transaction_source_type_id=2 and mmtt.move_order_line_id=wdd.move_order_line_id and wdd.released_status='S' and wdd.source_line_id=oola.line_id ) and ooha.sold_to_org_id=hca.cust_account_id and hca.party_id=hp.party_id order by ooha.order_number desc US Order Number 3 30 1=1 wnd.name=:delivery LOV custom Y select wnd.name value, xxen_util.meaning(wnd.status_code,'DELIVERY_STATUS',665) description from wsh_new_deliveries wnd where wnd.delivery_id in ( select wda.delivery_id from mtl_parameters mp, mtl_material_transactions_temp mmtt, wsh_delivery_details wdd, wsh_delivery_assignments wda where mp.organization_code=:$flex$.organization_code and mp.organization_id=mmtt.organization_id and mmtt.transaction_source_type_id=2 and mmtt.move_order_line_id=wdd.move_order_line_id and wdd.released_status='S' and wdd.delivery_detail_id=wda.delivery_detail_id ) order by wnd.name desc US Delivery 4 40 1=1 wnd.delivery_id in (select wdl.delivery_id from wsh_delivery_legs wdl, wsh_trip_stops wts, wsh_trips wt where wdl.pick_up_stop_id=wts.stop_id and wts.trip_id=wt.trip_id and wt.name=:trip) LOV custom Y select wt.name value, xxen_util.meaning(wt.status_code,'TRIP_STATUS',665) description from wsh_trips wt where wt.trip_id in ( select wts.trip_id from wsh_trip_stops wts, wsh_delivery_legs wdl where wts.stop_id=wdl.pick_up_stop_id and wdl.delivery_id in ( select wda.delivery_id from mtl_parameters mp, mtl_material_transactions_temp mmtt, wsh_delivery_details wdd, wsh_delivery_assignments wda where mp.organization_code=:$flex$.organization_code and mp.organization_id=mmtt.organization_id and mmtt.transaction_source_type_id=2 and mmtt.move_order_line_id=wdd.move_order_line_id and wdd.released_status='S' and wdd.delivery_detail_id=wda.delivery_detail_id ) ) order by wt.name desc US Trip 5 50 1=1 mtrh.request_number=:move_order LOV custom Y select mtrh.request_number value, xxen_util.meaning(mtrh.move_order_type,'MOVE_ORDER_TYPE',700) description from mtl_txn_request_headers mtrh where mtrh.header_id in ( select mtrl.header_id from mtl_parameters mp, mtl_material_transactions_temp mmtt, wsh_delivery_details wdd, mtl_txn_request_lines mtrl where mp.organization_code=:$flex$.organization_code and mp.organization_id=mmtt.organization_id and mmtt.transaction_source_type_id=2 and mmtt.move_order_line_id=wdd.move_order_line_id and wdd.released_status='S' and mmtt.move_order_line_id=mtrl.line_id ) order by mtrh.request_number desc US Move Order 6 60 1=1 hp.party_name=:customer LOV custom Y select distinct hp.party_name value, hca.account_number description from hz_cust_accounts hca, hz_parties hp where hca.cust_account_id in ( select ooha.sold_to_org_id from mtl_parameters mp, mtl_material_transactions_temp mmtt, wsh_delivery_details wdd, oe_order_lines_all oola, oe_order_headers_all ooha where mp.organization_code=:$flex$.organization_code and mp.organization_id=mmtt.organization_id and mmtt.transaction_source_type_id=2 and mmtt.move_order_line_id=wdd.move_order_line_id and wdd.released_status='S' and wdd.source_line_id=oola.line_id and oola.header_id=ooha.header_id ) and hca.party_id=hp.party_id order by hp.party_name US Customer 7 70 1=1 msiv.concatenated_segments=:item LOV custom Y Y select distinct msiv.concatenated_segments value, msiv.description from mtl_parameters mp, mtl_material_transactions_temp mmtt, wsh_delivery_details wdd, mtl_system_items_vl msiv where mp.organization_code=:$flex$.organization_code and mp.organization_id=mmtt.organization_id and mmtt.transaction_source_type_id=2 and mmtt.move_order_line_id=wdd.move_order_line_id and wdd.released_status='S' and mmtt.organization_id=msiv.organization_id and mmtt.inventory_item_id=msiv.inventory_item_id order by msiv.concatenated_segments US Item 8 80 :confirm_all LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Confirm All Downloaded Lines Pre-flags every downloaded row for upload, so an unchanged pick list is confirmed as it is. 9 90 :ship_confirm LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Ship Confirm Ship confirms a delivery as soon as its last released line has been confirmed by this upload. Unpicked quantities are backordered. 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 SHIP_CONFIRM Char varchar2 Y 6 ORGANIZATION_CODE Char varchar2 Y 7 ORDER_NUMBER Number number Y 8 LINE_NUMBER Char varchar2 Y 9 CUSTOMER Char varchar2 Y 10 SHIP_TO Char varchar2 Y 11 DELIVERY Char varchar2 Y 12 TRIP Char varchar2 Y 13 PICK_SLIP Number number Y 14 MOVE_ORDER Char varchar2 Y 15 TASK_STATUS Char varchar2 WMS task status. Blank in organizations without Warehouse Management. Y 16 ITEM Char varchar2 Y 17 ITEM_DESCRIPTION Char varchar2 Y 18 REQUESTED_QUANTITY Number number Y 19 UOM Char varchar2 Y 20 SUBINVENTORY LOV custom varchar2 Y select msi.secondary_inventory_name value, msi.description from mtl_parameters mp, mtl_secondary_inventories msi where mp.organization_code=:$column$.organization_code and mp.organization_id=msi.organization_id and nvl(msi.disable_date,sysdate+1)>sysdate order by msi.secondary_inventory_name Pre-filled with the allocated subinventory. Overwrite when the material was picked elsewhere. Must be the same on all rows of a pick task. Y 21 LOCATOR LOV custom varchar2 Y select milk.inventory_location_id id, milk.concatenated_segments value, milk.description from mtl_parameters mp, mtl_item_locations_kfv milk where mp.organization_code=:$column$.organization_code and mp.organization_id=milk.organization_id and milk.subinventory_code=:$column$.subinventory and milk.enabled_flag='Y' and nvl(milk.disable_date,sysdate+1)>sysdate order by milk.concatenated_segments Pre-filled with the allocated locator. 22 LOT_NUMBER LOV custom varchar2 Y select moqd.lot_number value, sum(moqd.transaction_quantity)||' '||msiv.primary_uom_code||case when mln.expiration_date is not null then ', expires '||to_char(mln.expiration_date,'DD-MON-YYYY') end description from mtl_parameters mp, mtl_system_items_vl msiv, mtl_onhand_quantities_detail moqd, mtl_item_locations_kfv milk, 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=moqd.organization_id and msiv.inventory_item_id=moqd.inventory_item_id and moqd.subinventory_code=:$column$.subinventory and moqd.locator_id=milk.inventory_location_id(+) and (:$column$.locator is null or milk.concatenated_segments=:$column$.locator) and moqd.lot_number is not null and moqd.organization_id=mln.organization_id and moqd.inventory_item_id=mln.inventory_item_id and moqd.lot_number=mln.lot_number group by moqd.lot_number, msiv.primary_uom_code, mln.expiration_date order by mln.expiration_date, moqd.lot_number Lot actually picked. Copy a row of the same pick task to pick from an additional lot. 23 PICKED_QUANTITY Number number Pre-filled with the allocated quantity. Reduce for a short pick. On a lot row, 0 removes the lot from the pick. Y 24 LPN LOV custom varchar2 select wlpn.license_plate_number value, xxen_util.meaning(wlpn.lpn_context,'LPN_CONTEXT',700) description from mtl_parameters mp, wms_license_plate_numbers wlpn where mp.organization_code=:$column$.organization_code and mp.organization_id=wlpn.organization_id and wlpn.lpn_context in (5,11) order by wlpn.license_plate_number WMS enabled organizations only. The LPN the material is packed into. A name not yet existing is created as a new LPN. 25 STAGING_SUBINVENTORY LOV custom varchar2 Y select msi.secondary_inventory_name value, msi.description from mtl_parameters mp, mtl_secondary_inventories msi where mp.organization_code=:$column$.organization_code and mp.organization_id=msi.organization_id and nvl(msi.disable_date,sysdate+1)>sysdate order by msi.secondary_inventory_name Staging lane the picked material is dropped at, defaulted from the pick release rule. 26 STAGING_LOCATOR LOV custom varchar2 Y select milk.inventory_location_id id, milk.concatenated_segments value, milk.description from mtl_parameters mp, mtl_item_locations_kfv milk where mp.organization_code=:$column$.organization_code and mp.organization_id=milk.organization_id and milk.subinventory_code=:$column$.staging_subinventory and milk.enabled_flag='Y' and nvl(milk.disable_date,sysdate+1)>sysdate order by milk.concatenated_segments 27 TRANSACTION_TEMP_ID Number number Y 28 MOVE_ORDER_LINE_ID Number number Y 29 DELIVERY_DETAIL_ID Number number Y 30 ORGANIZATION_ID Number number Y 31 ALLOCATED_LOT Char varchar2 Y 32 UPLOAD_ROW Number number Y U 1 status_ out Char STATUS_CODE_ U 2 message_ out Char MESSAGE_ U 3 p_transaction_temp_id in Number TRANSACTION_TEMP_ID U 4 p_organization_id in Number ORGANIZATION_ID U 5 p_delivery_detail_id in Number DELIVERY_DETAIL_ID U 6 p_organization_code in Char ORGANIZATION_CODE U 7 p_item in Char ITEM U 8 p_allocated_lot in Char Y ALLOCATED_LOT U 9 p_subinventory in Char Y SUBINVENTORY U 10 p_locator_id in Number Y LOCATOR U 11 p_lot_number in Char Y LOT_NUMBER U 12 p_picked_quantity in Number Y PICKED_QUANTITY U 13 p_lpn in Char Y LPN U 14 p_staging_subinventory in Char Y STAGING_SUBINVENTORY U 15 p_staging_locator_id in Number Y STAGING_LOCATOR P 1 status_ out Char STATUS_CODE_ P 2 message_ out Char MESSAGE_ P 3 p_transaction_temp_id in Number TRANSACTION_TEMP_ID P 4 p_ship_confirm in Char Y SHIP_CONFIRM Error SQL Order By organization_code, order_number, line_number, transaction_temp_id, lot_number Success SQL select xu.action_, xu.status_, xu.message_, xu.modified_columns_, xu.ship_confirm, xu.organization_code, xu.order_number, xu.line_number, xu.customer, xu.ship_to, xu.delivery, xu.trip, xu.pick_slip, xu.move_order, xu.task_status, xu.item, xu.item_description, xu.requested_quantity, xu.uom, nvl(mmt.subinventory_code,xu.subinventory) subinventory, nvl(y.locator,xu.locator) locator, nvl(mtln.lot_number,xu.lot_number) lot_number, coalesce(-mtln.transaction_quantity,-mmt.transaction_quantity,xu.picked_quantity) picked_quantity, nvl(wlpn.license_plate_number,xu.lpn) lpn, nvl(mmt.transfer_subinventory,xu.staging_subinventory) staging_subinventory, nvl(milk_st.concatenated_segments,xu.staging_locator) staging_locator, xu.transaction_temp_id, xu.move_order_line_id, xu.delivery_detail_id, xu.organization_id, xu.allocated_lot, xu.upload_row from xxen_wsh_pick_confirm_u_5058_u xu, ( select mmt.move_order_line_id, mmt.subinventory_code, milk.concatenated_segments locator, max(mmt.transaction_id) transaction_id from mtl_material_transactions mmt, mtl_item_locations_kfv milk where mmt.move_order_line_id in (select xu0.move_order_line_id from xxen_wsh_pick_confirm_u_5058_u xu0 where xu0.status_code_=xxen_upload.status_success) and mmt.transaction_source_type_id=2 and mmt.transaction_action_id=28 and mmt.transaction_quantity<0 and mmt.locator_id=milk.inventory_location_id(+) group by mmt.move_order_line_id, mmt.subinventory_code, milk.concatenated_segments ) y, mtl_material_transactions mmt, mtl_transaction_lot_numbers mtln, mtl_item_locations_kfv milk_st, wms_license_plate_numbers wlpn where xu.status_code_=xxen_upload.status_success and xu.move_order_line_id=y.move_order_line_id(+) and xu.subinventory=y.subinventory_code(+) and nvl(xu.locator,'#')=nvl(y.locator(+),'#') and y.transaction_id=mmt.transaction_id(+) and mmt.transaction_id=mtln.transaction_id(+) and xu.lot_number=mtln.lot_number(+) and mmt.transfer_locator_id=milk_st.inventory_location_id(+) and mmt.transfer_lpn_id=wlpn.lpn_id(+)