Here is the SQL Query to get all the open shipment lines and their statuses. This will help shipping manager/shipping user to find out all the open shipment lines, including awaiting to be released, Released to Warehouse, but not yet picked, Staged and Shipped etc., We can get the same info from Manage Shipment lines screen also, but it is better to have the report generating the output in excel for Shipping/Warehouse managers for their analysis and understanding system/supply/resource bottlenecks.
SELECT dha.order_number,
dha.order_type_code,
dha.status_code header_status,
— dla.LINE_NUMBER SO_LINE_NUM,
dla.DISPLAY_LINE_NUMBER SO_LINE_NUM ,
— dla.status_code line_status ,
( dla.display_line_number|| ‘-‘||fl.fulfill_line_number ) fulfillment_line_number,
fl.ordered_qty,
fl.shipped_qty,
(SELECT display_name from DOO_STATUSES_VL where STATUS_CODE = fl.status_code and ORCHESTRATION_APPLICATION_ID = 10008) FULFILL_LINE_STATUS ,
iop.organization_code warehouse,
nd.delivery_Name SHIPMENT,
DECODE(dd.Released_status, ‘B’,’Backordered’ ,’C’,’Shipped’ ,’D’,’Cancelled’ ,’N’,’Not Ready for Release’ ,
‘R’,’Ready to Release’ ,’S’,’Released to Warehouse’ ,’X’,’Not Applicable’ ,’Y’,’Staged’) Shipping_Status,
— nd.STATUS_CODE shipment_status,
— dd.shipped_quantity shipment_shipped_qty,
dd.TP_STATUS_CODE transporation_planning_status
FROM fusion.doo_headers_all dha ,
fusion.doo_lines_all dla ,
fusion.doo_fulfill_lines_all fl ,
fusion.wsh_delivery_details dd ,
fusion.wsh_delivery_assignments da ,
fusion.wsh_new_deliveries nd,
inv_org_parameters iop
WHERE dha.header_id = fl.header_id
AND fl.line_id = dla.line_id
AND fl.status_code NOT IN ( ‘CLOSED’, ‘CANCELED’ )
AND dha.source_order_number = dd.SALES_ORDER_NUMBER
AND fl.fulfill_line_id = dd.source_shipment_id
AND dd.delivery_detail_id = da.delivery_detail_id (+)
AND da.delivery_id = nd.delivery_id (+)
AND nvl(fl.fulfill_org_id, fl.inventory_organization_id) = iop.organization_id
AND iop.organization_code = NVL(:P_WAREHOUSE, iop.organization_code)
Below is the sample output.
