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.

Inventory, Products, Items, Onhand Quantity, etc. – Analysis and Support.

 

Queries:

Inventory/Warehouse Items/Products, Counts

-- qry sql - mtl onhand inventory items, counts
SELECT   ITEM.INVENTORY_ITEM_ID AS ITEM_ID, ITEM.SEGMENT1 ITEM_CODE, ITEM.DESCRIPTION ITEM_DESCRIPTION,
    NVL(SUM(ON_HAND.ON_HAND),0) ON_HAND
FROM MTL_SYSTEM_ITEMS_B ITEM, MTL_ONHAND_TOTAL_MWB_V ON_HAND
WHERE ITEM.ORGANIZATION_ID = 340  --ORG ID
AND ITEM.WEB_STATUS = 'PUBLISHED'
AND ITEM.ORDERABLE_ON_WEB_FLAG = 'Y'
AND ITEM.SEGMENT1 LIKE '3%'
AND ITEM.ORGANIZATION_ID = ON_HAND.ORGANIZATION_ID(+)  --ORG ID
AND ITEM.INVENTORY_ITEM_ID = ON_HAND.INVENTORY_ITEM_ID(+)
--AND item.inventory_item_id = 4231
--AND on_hand.subinventory_code = 'SEA'  --sub inventory
--AND item.segment1 = 'XXXXX' ---item name
GROUP BY ITEM.INVENTORY_ITEM_ID, ITEM.SEGMENT1, ITEM.DESCRIPTION
ORDER BY 4 DESC;
 

 

Product Counts, Organization/Warehouse Code

-- onhand by item status, inventory org code
SELECT   item.organization_id,
    (select ood.organization_code from org_organization_definitions ood where ood.organization_id = item.organization_id) as org_code,
    INVENTORY_ITEM_STATUS_CODE, count(distinct  item.inventory_item_id) as item_count, SUM (on_hand.on_hand) on_hand
    FROM mtl_system_items_b item, mtl_onhand_total_mwb_v on_hand
   WHERE item.organization_id in (23, 290, 25, 270, 28, 24, 34, 102, 103, 106, 107, 111, 112, 115, 136, 196, 36)  --ORG ID
   --AND exists (select 1 from mtl_system_items_b item4 where item4.organization_id = 332 and item4.inventory_item_id = item.inventory_item_id )
  AND item.organization_id = on_hand.organization_id
  AND item.inventory_item_id = on_hand.inventory_item_id
  --AND item.inventory_item_id = 4231
     --AND on_hand.subinventory_code = 'SEA'  --sub inventory
     --AND item.segment1 = 'XXXXX' ---item name
GROUP BY item.organization_id, INVENTORY_ITEM_STATUS_CODE;

 

 

 

Inventory Item Counts by Item Status

-- item counts by status
SELECT INVENTORY_ITEM_STATUS_CODE, (select ood.organization_code from org_organization_definitions ood where ood.organization_id = msi.organization_id) as org_code
    , ORGANIZATION_ID, COUNT(*) as total_items, sum(decode(INVENTORY_ITEM_STATUS_CODE, 'Active', 1, 0)) as active_items
FROM APPS.MTL_SYSTEM_ITEMS_VL msi
where organization_id in (28, 332)
--and inventory_item_id in (1613, 1645, 3443248, 47961, 3443228, 1389, 5151, 3443235, 7041, 3098, 1244, 9334, 7589, 3443225, 7701, 3443227)
GROUP BY ORGANIZATION_ID, INVENTORY_ITEM_STATUS_CODE
ORDER BY ORGANIZATION_ID ASC;
 
 

  

Keywords:

Oracle EBS, R12, R12.2.8, OM, OE, ONT, WSH, INV, Inventory, Warehouse, Onhand, Demand, Availability, Order Management, Shipping, Ship Confirm, Query, SQL, SQLPLUS

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