Item Shipment History Query in Fusion

One of the most important report customer will ask you once you have implemented is shipment history report in which customer will try to fetch the details of items shipped. You can use below query or aggregate it at desired level. Details of the columns this query will return are:
1. Item Number
2. Inventory Org Name
3. Sales Order Number
4. Sales Order Line Number
5. Salesperson Name
6. Shipped Quantity
7. Shipped To Party Number
8. Shipped To Party Name
9. Ship to Site Number
10. Ship To Site Name

So Query to fetch shipment history is:

SELECT item.item_number,
iop.organization_code,
dha.order_number,
dla.line_number,
rsp.person_name sales_person_name,
dfla.shipped_qty qty_shipped,
hz.party_number ship_to_party_number,
hz.party_name ship_to_party_name,
hps.party_site_number ship_to_site ,
hps.party_site_name ship_to_site_name
FROM doo_fulfill_lines_all dfla,
doo_lines_all dla,
doo_headers_all dha,
egp_system_items_b item,
inv_org_parameters iop,
doo_sales_credits sc,
jtf_rs_resource_profiles rsp,
hz_parties HZ,
hz_party_sites HPS
WHERE dfla.actual_ship_date is not null
AND dfla.canceled_flag = ‘N’
AND dfla.item_sub_type_code NOT IN ( ‘ATO-OPTION-CLASS’,’ATO-OPTION’ )
AND dla.line_id = dfla.line_id
AND dha.header_id = dla.header_id
AND item.inventory_item_id = dfla.inventory_item_id
AND item.organization_id = dfla.fulfill_org_id
and iop.organization_id = dfla.fulfill_org_id
AND dha.header_id = sc.header_id (+)
AND sc.salesperson_id = rsp.party_id (+)
AND HPS.party_site_id = DFLA.ship_to_party_site_id
AND HZ.party_id = HPS.party_id

Related Posts:
Query To Get The Shipment Details In Oracle Fusion
Sales Order Query in Oracle Fusion
Sales Order Table Linkage in Oracle Fusion – DOO_HEADERS_ALL

Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.