[EBS]Sales Order Join Delivery

 

SELECT

OOH.ORG_ID,

OOH.ORDER_NUMBER,

OOH.ORDERED_DATE,

OOH.ORDER_TYPE_ID,

OOL.LINE_ID "ORDER_LINE_ID",

OOL.LINE_NUMBER || '.' || OOL.SHIPMENT_NUMBER "ORDER_LINE",

OOL.INVENTORY_ITEM_ID "ITEM_ID",

OOL.ORDERED_ITEM "ITEM_NUMBER",

OOL.ORDERED_QUANTITY "QUANTITY",

OOL.ORDER_QUANTITY_UOM "UOM",

OOL.UNIT_SELLING_PRICE "UNIT_PRICE",

OOH.TRANSACTIONAL_CURR_CODE "CURRENCY",

trunc(OOL.SCHEDULE_SHIP_DATE) "DEMAND_DATE",

OOH.SOLD_TO_ORG_ID "CUSTOMER_ID",

OOH.SHIP_TO_ORG_ID,

OOH.INVOICE_TO_ORG_ID,

WND.name

FROM

APPS.OE_ORDER_HEADERS_ALL OOH

INNER JOIN APPS.OE_ORDER_LINES_ALL OOL ON

OOL.HEADER_ID = OOH.HEADER_ID

INNER JOIN wsh_delivery_details WDD ON

OOL.header_id=WDD.source_header_id

AND OOL.line_id=WDD.source_line_id

INNER JOIN wsh_delivery_assignments WDA ON

WDD.delivery_detail_id=WDA.delivery_detail_id

INNER JOIN wsh_new_deliveries WND ON

WND.delivery_id = WDA.delivery_id

WHERE

1 = 1

AND OOH.order_number = '12345'

AND WND.name='DD102201130'

;