Query of OM - Order Management Table Flow of Oracle Apps EBS R12

 -- Query of OM - Order Management Table Flow of Oracle Apps EBS R12  --

SELECT 'OOH', OOH.HEADER_ID, OOH.ORG_ID, OOH.ORDER_TYPE_ID, OOH.ORDER_NUMBER , OOH.PAYMENT_TERM_ID, OOH.SOLD_FROM_ORG_ID, OOH.SHIP_FROM_ORG_ID,
              OOH.CANCELLED_FLAG , OOH.OPEN_FLAG, OOH.BOOKED_FLAG, OOH.ORDER_CATEGORY_CODE, OOH.FLOW_STATUS_CODE
FROM OE_ORDER_HEADERS_ALL OOH
WHERE  OOH.ORDER_NUMBER = 12130360 --12130359 --12130358  --- HEADER_ID = 104421, 105405

--------------------------------------------------------------

SELECT 'OOL', OOL.LINE_ID, OOL.ORG_ID, OOL.HEADER_ID, OOL.LINE_TYPE_ID, OOL.LINE_NUMBER, OOL.ORDERED_ITEM, OOL.ORDERED_ITEM_ID, OOL.ORDER_QUANTITY_UOM,
              OOL.PRICING_QUANTITY,OOL.CANCELLED_QUANTITY, OOL.SHIPPED_QUANTITY , OOL.ORDERED_QUANTITY , OOL.SHIP_FROM_ORG_ID, OOL.SOLD_FROM_ORG_ID,
              OOL.INVENTORY_ITEM_ID, OOL.UNIT_SELLING_PRICE, OOL.ITEM_TYPE_CODE, OOL.LINE_CATEGORY_CODE, OOL.CANCELLED_FLAG, OOL.OPEN_FLAG, OOL.BOOKED_FLAG,
              OOL.FLOW_STATUS_CODE
FROM OE_ORDER_LINES_ALL OOL
WHERE OOL.HEADER_ID =    106405--105405 --104421    ----LINE_ID = 110743

--------------------------------------------------------------

SELECT 'WDD', WDD.DELIVERY_DETAIL_ID, WDD.SOURCE_HEADER_ID "HEADER_ID", WDD.SOURCE_LINE_ID "LINE_ID", WDD.SOURCE_HEADER_TYPE_ID, WDD.SOURCE_HEADER_TYPE_NAME ,
              WDD.CUSTOMER_ID, WDD.INVENTORY_ITEM_ID, WDD.ITEM_DESCRIPTION, WDD.SHIP_FROM_LOCATION_ID, WDD.ORGANIZATION_ID, WDD.ORG_ID,
              WDD.CANCELLED_QUANTITY, WDD.REQUESTED_QUANTITY, WDD.REQUESTED_QUANTITY_UOM, WDD.RELEASED_STATUS ,WDD.CURRENCY_CODE,
              WDD.INSPECTION_FLAG,  WDD.SOURCE_HEADER_NUMBER "ORDER_NUMBER", WDD.PICKABLE_FLAG, WDD.PICKED_QUANTITY
FROM WSH_DELIVERY_DETAILS WDD
WHERE WDD.SOURCE_HEADER_ID = 106405  --105405   --104421
AND WDD.SOURCE_LINE_ID IN  (111741,111742,111743,111744)  --(110743,110744,110747)

--------------------------------------------------------------


SELECT 'WDA', WDA.DELIVERY_ASSIGNMENT_ID, WDA.DELIVERY_ID, WDA.DELIVERY_DETAIL_ID,WDA.ACTIVE_FLAG, WDA.TYPE 
FROM WSH_DELIVERY_ASSIGNMENTS WDA
WHERE  WDA.DELIVERY_DETAIL_ID IN (107449,107450,107451,107452) --(106457,106458,106459)      --(106449,106450,106451)

----------------------------------------------------------------


-------------JOINING------
--     OE_ORDER_HEADERS_ALL AND OE_ORDER_LINES_ALL OOL  ------

SELECT 'OOH', OOH.HEADER_ID, OOH.ORG_ID, OOH.ORDER_TYPE_ID, OOH.ORDER_NUMBER , OOH.PAYMENT_TERM_ID, OOH.SOLD_FROM_ORG_ID, OOH.SHIP_FROM_ORG_ID,
              OOH.CANCELLED_FLAG , OOH.OPEN_FLAG, OOH.BOOKED_FLAG, OOH.ORDER_CATEGORY_CODE, OOH.FLOW_STATUS_CODE,
       'OOL', OOL.LINE_ID, OOL.ORG_ID, OOL.HEADER_ID, OOL.LINE_TYPE_ID, OOL.LINE_NUMBER, OOL.ORDERED_ITEM, OOL.ORDERED_ITEM_ID, OOL.ORDER_QUANTITY_UOM,
              OOL.PRICING_QUANTITY,OOL.CANCELLED_QUANTITY, OOL.SHIPPED_QUANTITY , OOL.ORDERED_QUANTITY , OOL.SHIP_FROM_ORG_ID, OOL.SOLD_FROM_ORG_ID,
              OOL.INVENTORY_ITEM_ID, OOL.UNIT_SELLING_PRICE, OOL.ITEM_TYPE_CODE, OOL.LINE_CATEGORY_CODE, OOL.CANCELLED_FLAG, OOL.OPEN_FLAG, OOL.BOOKED_FLAG,
              OOL.FLOW_STATUS_CODE
FROM   OE_ORDER_HEADERS_ALL OOH,
       OE_ORDER_LINES_ALL OOL
WHERE  OOH.HEADER_ID = OOL.HEADER_ID
AND    OOH.ORDER_NUMBER = 12130359    -- 12130358


-------------JOINING------
--     OE_ORDER_HEADERS_ALL  ----
--     OE_ORDER_LINES_ALL  ------
--     WSH_DELIVERY_DETAILS ----


SELECT 'OOH', OOH.HEADER_ID, OOH.ORG_ID, OOH.ORDER_TYPE_ID, OOH.ORDER_NUMBER , OOH.PAYMENT_TERM_ID, OOH.SOLD_FROM_ORG_ID, OOH.SHIP_FROM_ORG_ID,
              OOH.CANCELLED_FLAG , OOH.OPEN_FLAG, OOH.BOOKED_FLAG, OOH.ORDER_CATEGORY_CODE, OOH.FLOW_STATUS_CODE,
       'OOL', OOL.LINE_ID, OOL.ORG_ID, OOL.HEADER_ID, OOL.LINE_TYPE_ID, OOL.LINE_NUMBER, OOL.ORDERED_ITEM, OOL.ORDERED_ITEM_ID, OOL.ORDER_QUANTITY_UOM,
              OOL.PRICING_QUANTITY,OOL.CANCELLED_QUANTITY, OOL.SHIPPED_QUANTITY , OOL.ORDERED_QUANTITY , OOL.SHIP_FROM_ORG_ID, OOL.SOLD_FROM_ORG_ID,
              OOL.INVENTORY_ITEM_ID, OOL.UNIT_SELLING_PRICE, OOL.ITEM_TYPE_CODE, OOL.LINE_CATEGORY_CODE, OOL.CANCELLED_FLAG, OOL.OPEN_FLAG, OOL.BOOKED_FLAG,
              OOL.FLOW_STATUS_CODE,
       'WDD', WDD.DELIVERY_DETAIL_ID, WDD.SOURCE_HEADER_ID "HEADER_ID", WDD.SOURCE_LINE_ID "LINE_ID", WDD.SOURCE_HEADER_TYPE_ID, WDD.SOURCE_HEADER_TYPE_NAME ,
              WDD.CUSTOMER_ID, WDD.INVENTORY_ITEM_ID, WDD.ITEM_DESCRIPTION, WDD.SHIP_FROM_LOCATION_ID, WDD.ORGANIZATION_ID, WDD.ORG_ID,
              WDD.CANCELLED_QUANTITY, WDD.REQUESTED_QUANTITY, WDD.REQUESTED_QUANTITY_UOM, WDD.RELEASED_STATUS ,WDD.CURRENCY_CODE,
              WDD.INSPECTION_FLAG,  WDD.SOURCE_HEADER_NUMBER "ORDER_NUMBER", WDD.PICKABLE_FLAG, WDD.PICKED_QUANTITY
FROM   OE_ORDER_HEADERS_ALL OOH,
       OE_ORDER_LINES_ALL OOL,
       WSH_DELIVERY_DETAILS WDD
WHERE  OOH.HEADER_ID = OOL.HEADER_ID
AND    OOH.HEADER_ID = WDD.SOURCE_HEADER_ID
AND    OOL.LINE_ID   = WDD.SOURCE_LINE_ID
AND    OOH.ORDER_NUMBER = 12130359   --12130358

----------------------------------------------------

-------------JOINING-------------------------
----     OE_ORDER_HEADERS_ALL          ----
----     OE_ORDER_LINES_ALL            ----
----    WSH_DELIVERY_DETAILS           ----
----    WSH_DELIVERY_ASSIGNMENTS       ----


SELECT 'OOH', OOH.HEADER_ID, OOH.ORG_ID, OOH.ORDER_TYPE_ID, OOH.ORDER_NUMBER , OOH.PAYMENT_TERM_ID, OOH.SOLD_FROM_ORG_ID, OOH.SHIP_FROM_ORG_ID,
              OOH.CANCELLED_FLAG , OOH.OPEN_FLAG, OOH.BOOKED_FLAG, OOH.ORDER_CATEGORY_CODE, OOH.FLOW_STATUS_CODE,
       'OOL', OOL.LINE_ID, OOL.ORG_ID, OOL.HEADER_ID, OOL.LINE_TYPE_ID, OOL.LINE_NUMBER, OOL.ORDERED_ITEM, OOL.ORDERED_ITEM_ID, OOL.ORDER_QUANTITY_UOM,
              OOL.PRICING_QUANTITY,OOL.CANCELLED_QUANTITY, OOL.SHIPPED_QUANTITY , OOL.ORDERED_QUANTITY , OOL.SHIP_FROM_ORG_ID, OOL.SOLD_FROM_ORG_ID,
              OOL.INVENTORY_ITEM_ID, OOL.UNIT_SELLING_PRICE, OOL.ITEM_TYPE_CODE, OOL.LINE_CATEGORY_CODE, OOL.CANCELLED_FLAG, OOL.OPEN_FLAG, OOL.BOOKED_FLAG,
              OOL.FLOW_STATUS_CODE,
       'WDD', WDD.DELIVERY_DETAIL_ID, WDD.SOURCE_HEADER_ID "HEADER_ID", WDD.SOURCE_LINE_ID "LINE_ID", WDD.SOURCE_HEADER_TYPE_ID, WDD.SOURCE_HEADER_TYPE_NAME ,
              WDD.CUSTOMER_ID, WDD.INVENTORY_ITEM_ID, WDD.ITEM_DESCRIPTION, WDD.SHIP_FROM_LOCATION_ID, WDD.ORGANIZATION_ID, WDD.ORG_ID,
              WDD.CANCELLED_QUANTITY, WDD.REQUESTED_QUANTITY, WDD.REQUESTED_QUANTITY_UOM, WDD.RELEASED_STATUS ,WDD.CURRENCY_CODE,
              WDD.INSPECTION_FLAG,  WDD.SOURCE_HEADER_NUMBER "ORDER_NUMBER", WDD.PICKABLE_FLAG, WDD.PICKED_QUANTITY,
       'WDA', WDA.DELIVERY_ASSIGNMENT_ID, WDA.DELIVERY_ID, WDA.DELIVERY_DETAIL_ID,WDA.ACTIVE_FLAG, WDA.TYPE 
FROM   OE_ORDER_HEADERS_ALL OOH,
       OE_ORDER_LINES_ALL OOL,
       WSH_DELIVERY_DETAILS WDD,
       WSH_DELIVERY_ASSIGNMENTS WDA
WHERE  OOH.HEADER_ID = OOL.HEADER_ID
AND    OOH.HEADER_ID = WDD.SOURCE_HEADER_ID
AND    OOL.LINE_ID   = WDD.SOURCE_LINE_ID
AND    WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
AND    OOH.ORDER_NUMBER =  12130359 --12130358