Oracle EBS SQLs – Order Hold, Credit Card Details

 
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.
Helpful to analyze Credit Card Orders with Oracle Payments (iPayments / IBY) troubleshooting.
 
Queries:
Order Details – Hold Type, Name, Codes

-- hold types for a given order
SELECT OEH.HEADER_ID, OTT.NAME ORDER_TYPE,OEH.ORDER_NUMBER,OEH.PAYMENT_TYPE_CODE,RTM.NAME PAYMENT_TERM,OOL.LINE_NUMBER,
OOL.ORDERED_ITEM,OOL.SCHEDULE_SHIP_DATE,OOL.FLOW_STATUS_CODE LINE_STATUS,
OHD.NAME,OOH.ORDER_HOLD_ID,OOH.CREATION_DATE,OOH.RELEASED_FLAG
  FROM OE_ORDER_HEADERS_ALL OEH, OE_ORDER_LINES_ALL OOL,OE_TRANSACTION_TYPES_TL OTT,
  RA_TERMS_TL RTM.,
  OE_ORDER_HOLDS_ALL OOH,
  OE_HOLD_SOURCES_ALL OHS,
  OE_HOLD_DEFINITIONS OHD
WHERE 1=1
 AND OEH.HEADER_ID=OOL.HEADER_ID
   AND OEH.HEADER_ID = 5153513
   AND OEH.ORDER_TYPE_ID=OTT.TRANSACTION_TYPE_ID
   AND OEH.PAYMENT_TERM_ID=RTM.TERM_ID
   AND OEH.HEADER_ID=OOH.HEADER_ID(+)
   AND OOH.HOLD_SOURCE_ID=OHS.HOLD_SOURCE_ID(+)
   AND OHS.HOLD_ID=OHD.HOLD_ID(+);

  
Credit Card Details for the Order

-- orders, lines, credit card details (CC tables not used anymore)
SELECT   OOHA.HEADER_ID,OOLA.LINE_NUMBER,OOLA.LINE_ID,OP.LINE_ID,  OOHA.ORDER_NUMBER,
  (SELECT HCA.ACCOUNT_NUMBER   FROM HZ_CUST_ACCOUNTS HCA
  WHERE HCA.CUST_ACCOUNT_ID=OOHA.SOLD_TO_ORG_ID  ) CUSTOMERNUMBER,
  OOHA.FLOW_STATUS_CODE "OrderStatus",  OOHA.PAYMENT_TYPE_CODE,  OOHA.CREDIT_CARD_NUMBER,
  OOLA.ORDERED_ITEM,   OOLA.ORDERED_QUANTITY,  OOLA.SHIPPED_QUANTITY,  OOLA.INVOICRD.D_QUANTITY,
  OOLA.TAX_VALUE,  (OOLA.UNIT_SELLING_PRICRD. * OOLA.ORDERED_QUANTITY) ITEMTOTALAMOUNT,
  OOLA.FLOW_STATUS_CODE "LineStatus",  IFTE.TRXN_EXTENSION_ID ,
  ICRD.CCNUMBER,   ICRD.CHNAME,  ICRD.CARD_OWNER_ID,  ICRD.MASKED_CC_NUMBER,  ICRD.CARD_ISSUER_CODE,  ICRD.EXPIRYDATE,  ICRD.INACTIVE_DATE
FROM OE_ORDER_HEADERS_ALL OOHA,  OE_ORDER_LINES_ALL OOLA,  OE_PAYMENTS OP,  IBY_FNDCPT_TX_EXTENSIONS IFTE,  IBY_PMT_INSTR_USES_ALL IPUA,  IBY_CREDITCARD  ICRD
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
--AND OOHA.PAYMENT_TYPE_CODE LIKE 'CREDIT_CARD'
AND OP.LINE_ID(+)        =OOLA.LINE_ID
AND OOHA.HEADER_ID = 5153513
AND OP.TRXN_EXTENSION_ID=IFTE.TRXN_EXTENSION_ID(+)
  --AND IFTE.TRXN_EXTENSION_ID=ITSA.INITIATOR_EXTENSION_ID
AND IFTE.INSTR_ASSIGNMENT_ID=IPUA.INSTRUMENT_PAYMENT_USE_ID(+)
AND IPUA.INSTRUMENT_ID      =ICRD.INSTRID(+);
 

 
 
 
 
Keywords:
Oracle EBS, R12, R12.2.8, OM, OE, ONT, IBY, Oracle Payments, iPayments, 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.

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

Oracle DB SQLs – Table Data Backup Steps

 

Business Need:

Before changing a record, previous data needs to be backed up in a table for analysis.

Helpful to analyze change done by custom program or user actions on a table data.

 

Queries:

CREATE TABLE OE_OLINES_NOV23A
AS
SELECT * FROM OE_ORDER_LINES_ALL OL WHERE HEADER_ID = 5801517
/
           
ALTER TABLE OE_OLINES_NOV23A ADD (OL_INSERT_DATE DATE DEFAULT SYSDATE)
/
 
INSERT INTO OE_OLINES_NOV23A
SELECT OL.*, SYSDATE FROM OE_ORDER_LINES_ALL OL WHERE HEADER_ID = 5801517;
 
SELECT * FROM OE_OLINES_NOV23A;
 

 

Notes:

1.    These steps are backing up a single order’s lines. The WHERE clause can be substituted for a time window ( CREATION_DATE > SYSDATE-1 ) or Order TYPE ( LINE_TYPE_ID = 2529 ) or any other parameters that needs to be monitored.

 It may be a good idea to have all temp table have a standard format extensions (Eg: %_DATE or  %_BACK) and delete them periodically (After 1 month after creation)

  

Keywords:

Oracle DB, EBS, OM, Order Lines, OE_ORDER_LINES_ALL, Query, SQL, SQLPLUS