Business has a common requirement to find out all the open Sales Order Pick Slips (Released to the warehouse, but not yet pick confirmed) in their operation, including sourcing sub-inventory and locator info.
We should able to find out all the open pick slips using Manage Shipment Lines or Confirm Pick Slips UI screens in Inventory Management, but we can’t see the source sub-inventory and locator info in the results (Pick Slip Header level), since sub-inventory and locator info at the pick Slip line level (one level below the pick slip) OR Can’t query the open slips by sourcing sub-inventory and locator. Below query helps to find out all the open pick slips data and filter by source sub-inventory and locator.
SELECT organization_code,
ipsn.pick_slip_number,
ipsn.pick_slip_count,
ipsn.pick_slip_due_date,
ipol.movement_request,
ipol.source_order,
ipol.shipping_priority,
ipol.pick_slip_line,
ipol.item,
ipol.requested_quantity,
ipol.uom,ipol.source_subinventory,
ipol.source_locator,
its.transaction_source_type_name,
ipol.customer,
carrier_name||’-‘||mt.meaning||’-‘||sl.meaning shipping_method,
ipsl.transaction_status
FROM inv_pick_slip_numbers ipsn,
inv_pick_open_lines_v ipol,
inv_pick_slip_lines_v ipsl,
inv_txn_source_types_tl its,
inv_transaction_types_vl itt,
wsh_org_carrier_services wocs,
fnd_lookup_values_vl sl,
fnd_lookup_values_vl mt,
wsh_carriers_v wcv,
inv_org_parameters orgs
WHERE ipol.organization_id = orgs.organization_id
AND ipol.organization_id = wocs.organization_id (+)
AND sl.lookup_type (+) = ‘WSH_SERVICE_LEVELS’
AND sl.lookup_code (+) = service_level
AND mt.lookup_type (+) = ‘WSH_MODE_OF_TRANSPORT’
AND mt.lookup_code (+) = mode_of_transport
AND wocs.carrier_id = wcv.carrier_id (+)
AND ipsl.ship_method_code = wocs.ship_method_code (+)
AND ipol.pick_slip = ipsn.pick_slip_number
AND ipol.pick_slip = ipsl.pick_slip_number
AND ipol.transaction_type = itt.transaction_type_name
AND itt.transaction_source_type_id = its.transaction_source_type_id
AND its.language = ‘US’
AND orgs.organization_code = :p_warehouse
GROUP BY organization_code,
ipsn.pick_slip_number,
ipsn.pick_slip_count,
ipsn.pick_slip_due_date,
ipol.movement_request,
ipol.source_order,
ipol.shipping_priority,
ipol.pick_slip_line,
ipol.item,
ipol.requested_quantity,
ipol.uom,
ipol.source_subinventory,
ipol.source_locator,
ipsl.transaction_status,
its.transaction_source_type_name,
ipol.customer,
carrier_name||’-‘||mt.meaning||’-‘||sl.meaning
ORDER BY organization_code,
pick_slip_due_date,
ipsn.pick_slip_number
Sample Report output:
