Shipment Details Query needed? Here you go. Shipment Details are required when you are shipping the goods to your customer and relevant details are required in order to send the goods in pre agreed manner such as who should be the receiver on the goods, what should be the billing detail etc. This post will provide you just that. You can expect following columns as the output of the query:
1. Delivery Name
2. Delivery Type
3. Vehicle Number
4. Bill Of Lading Number
5. Packing Slip Number
6. Carrier Name
7. Ship To party name
8. Sold To Party Name
9. Bill To Party Name
10. Ship To Party Site
11. Source Line Type
12. Sales Order Number
13. Sales Order Line Number
14. Source Shipment Number
15. Shipped Quantity
16. Received Quantity
17. Shipping Instruction
18. Bill To Location
Shipment Details Query
SELECT del.delivery_name,
del.delivery_type,
del.vehicle_number,
bill_of_lading_number,
packing_slip_number,
car.carrier_name,
ship_party.party_name ship_to_party_name,
sold_party.party_name sold_to_party_name,
bill_party.party_name bill_party_name,
bill_party_site.party_site_name Bill_to_Party_site,
ship_party_site.party_site_name ship_to_party_site,
deldet.source_line_type,
deldet.sales_order_number,
deldet.sales_order_line_number,
deldet.cust_po_number,
deldet.sales_order_shipment_number,
deldet.source_shipment_number,
deldet.service_level,
deldet.mode_of_transport,
deldet.subinventory,
itm.item_number,
deldet.shipped_quantity,
deldet.requested_quantity,
deldet.shipping_instructions ,
bill_loc.ADDRESS1||CHR(9)||bill_loc.ADDRESS2||CHR(9)||bill_loc.CITY||CHR(9)||bill_loc.POSTAL_CODE BIll_To_Location ,
ship_loc.ADDRESS1||CHR(9)||ship_loc.ADDRESS2||CHR(9)||ship_loc.CITY||CHR(9)||ship_loc.POSTAL_CODE ship_To_Location ,
ship_from_loc.ADDRESS_LINE_1||CHR(9)||ship_from_loc.ADDRESS_LINE_2||CHR(9)||ship_from_loc.TOWN_OR_CITY||CHR(9)||ship_from_loc.POSTAL_CODE ship_From_Location
FROM wsh_new_deliveries del,
wsh_carriers_v car ,
wsh_delivery_assignments DelAssgn,
wsh_delivery_details DelDet,
egp_system_items_b itm ,
inv_org_parameters iop,
hz_parties ship_party,
hz_parties sold_party,
hz_parties bill_party,
hz_locations bill_loc,
hz_locations ship_loc,
hz_party_sites bill_party_site,
hz_party_sites ship_party_site,
hr_locations ship_from_loc
WHERE 1=1
AND car.carrier_id(+) = del.carrier_id
AND DelAssgn.DELIVERY_ID = Del.DELIVERY_ID
AND DelDet.delivery_detail_id = delassgn.delivery_detail_id
AND itm.inventory_item_id = DelDet.Inventory_item_id
AND iop.organization_id = itm.organization_id
AND iop.organization_id = iop.master_organization_id
AND deldet.ship_to_party_id = ship_party.party_id
AND deldet.sold_to_party_id = sold_party.party_id
AND deldet.bill_to_party_id = bill_party.party_id
AND bill_loc.location_id(+) = deldet.bill_to_location_id
AND ship_loc.location_id(+) = deldet.ship_to_location_id
AND bill_party_site.party_site_id= deldet.bill_to_party_site_id
AND ship_party_site.party_site_id= deldet.ship_to_party_site_id
AND ship_from_loc.location_id = DelDet.SHIP_FROM_LOCATION_ID
Brief Details
There are two entities in Fusion. First is Delivery and other is Delivery Detail. You can imagine Delivery as an envelop which in itself has just the header level details. while the more in depth information lies in Delivery Details. But there is a trick. These tables are not directly connected to each other by parent child relationship. Rather one needs to find the connection using delivery assignment table. Table names are:
Delivery : wsh_new_deliveries
Delivery Details : wsh_delivery_details
Delivery Assignment : wsh_delivery_assignments
By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
Related Posts:
Sales Order Query in Oracle Fusion
Oracle Fusion Sales Order Table – DOO_HEADERS_ALL
DOO_LINES_ALL: Sales Order Lines Table
DOO_FULFILL_LINES_ALL: Columns, Indexes & Imp Points
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.
Reference:
support.oracle.com
