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