Oracle EBS SQLs – Delivery, Back Order Statuses

 

Business Need:

Oracle Applications (EBS) Support, Analysis, Troubleshooting, Reports, Tracking, etc. I am using R12.2.8. But the SQLs will be applicable most of the recent versions.

Order Entry, Shipping, Delivery, pricing, etc. – Analysis and Support.

 

Queries:

Order Number, Line & Delivery Status

-- order lines with delivery status
SELECT OL.HEADER_ID, OL.LINE_ID, OL.LINE_NUMBER, OL.FLOW_STATUS_CODE, OL.ORDERED_QUANTITY, WDL.PICKED_QUANTITY, OL.UNIT_SELLING_PRICE, OL.TAX_VALUE, WDL.RELEASED_STATUS_NAME
FROM OE_ORDER_LINES_ALL OL, APPS.WSH_DELIVERY_DETAILS WDD, WSH_DELIVERABLES_V WDL
WHERE 1=1--OL.FLOW_STATUS_CODE NOT IN ('ENTERED', 'CLOSED')
AND WDL.DELIVERY_DETAIL_ID(+) = WDD.DELIVERY_DETAIL_ID
AND OL.HEADER_ID = WDD.SOURCE_HEADER_ID(+)
AND OL.LINE_ID = WDD.SOURCE_LINE_ID(+)
AND WDL.SOURCE_HEADER_ID(+) = OL.HEADER_ID
--AND WDL.RELEASED_STATUS <> 'B'
AND OL.HEADER_ID = 5154002;                    -- Replace Order Header ID
 

Orders & Deliverable Amounts

-- get auth amount - total of all deliverable amounts of the order
SELECT SUM(LINE_AMOUNTS)
FROM (SELECT GREATEST(0,NVL(WDL.PICKED_QUANTITY, OL.ORDERED_QUANTITY)*OL.UNIT_SELLING_PRICE) LINE_AMOUNTS
    FROM OE_ORDER_LINES_ALL OL, APPS.WSH_DELIVERY_DETAILS WDD, WSH_DELIVERABLES_V WDL
    WHERE OL.FLOW_STATUS_CODE NOT IN ('ENTERED', 'CLOSED')
    AND WDL.DELIVERY_DETAIL_ID(+) = WDD.DELIVERY_DETAIL_ID
    AND OL.HEADER_ID = WDD.SOURCE_HEADER_ID(+)
    AND OL.LINE_ID = WDD.SOURCE_LINE_ID(+)
    AND WDL.RELEASED_STATUS <> 'B'
    AND OL.HEADER_ID = 5154002                 -- Replace Order Header ID
    UNION
    SELECT NVL(WDL.PICKED_QUANTITY, OL.ORDERED_QUANTITY)*OL.TAX_VALUE/OL.ORDERED_QUANTITY TAX_AMOUNTS
    FROM OE_ORDER_LINES_ALL OL, APPS.WSH_DELIVERY_DETAILS WDD, WSH_DELIVERABLES_V WDL
    WHERE OL.FLOW_STATUS_CODE NOT IN ('ENTERED', 'CLOSED')
    AND WDL.DELIVERY_DETAIL_ID(+) = WDD.DELIVERY_DETAIL_ID
    AND OL.HEADER_ID = WDD.SOURCE_HEADER_ID(+)
    AND OL.LINE_ID = WDD.SOURCE_LINE_ID(+)
    AND WDL.RELEASED_STATUS <> 'B'
    AND OL.HEADER_ID = 5154002);               -- Replace Order Header ID
 

  

Keywords:

Oracle EBS, R12, R12.2.8, OM, OE, ONT, WSH, Order Management, Shipping, Ship Confirm, Query, SQL, SQLPLUS