Query to get delivery number in oracle apps

Query to get delivery number in oracle apps

In this post , I am going to share the sql query to extract the sales order delivery number in oracle apps. delivery number is basically the actual shipment which we do send against the customer sales order in oracle apps. here below i will share the sql query which helps to extract the delivery number/ shipments created against the sales order in oracle apps. Please find below the detail SQL query  to delivery number in oracle apps.

7 Important table for query to delivery number in oracle apps

1.oe_order_headers_all ooh,

2.ar_customers ac,

3.wsh_delivery_details wdd,

4.oe_order_lines_all ool,

5.wsh_delivery_assignments wda,

6.hz_cust_accounts hca,

7.mtl_system_items msi

Detail SQL Query to get delivery number in oracle apps

SELECT distinct

ooh.order_number,

ac.customer_name,

ooh.org_id,

ooh.ORDERED_DATE,

ooh.FLOW_STATUS_CODE SO_Status,

ool.line_number,

msi.SEGMENT1 Item_Name,

ool.ordered_quantity,

wdd.shipped_quantity,

wda.delivery_id shipment_number,

ool.UNIT_SELLING_price*ool.ordered_quantity line_total

from apps.oe_order_headers_all ooh,

apps.ar_customers ac,

apps.wsh_delivery_details wdd,

apps.oe_order_lines_all ool,

apps.wsh_delivery_assignments wda,

apps.hz_cust_accounts hca,

apps.mtl_system_items msi

where

ooh.header_id=ool.header_id

and ooh.sold_to_org_id=hca.cust_account_id

and ooh.header_id=wdd.source_header_id(+)

and ooh.order_number=:P_ORDER_NUMBER

and ool.line_id=wdd.source_line_id(+)

and hca.cust_account_id=ac.customer_id

and msi.INVENTORY_ITEM_ID=ool.INVENTORY_ITEM_ID

and msi.ORGANIZATION_ID=ool.SHIP_FROM_ORG_ID

and wda.delivery_detail_id=wdd.delivery_detail_id

and ooh.org_id=:P_ORG_ID

order by ool.line_number