Credit Card Sales Order Queries

/* -----List of Orders which are pick released on Sysdate ---------*/

SELECT oeh.header_id, ott.name Order_type,oeh.order_number,oeh.payment_type_code,rt.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 rt,
  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.flow_status_code != 'CLOSED'
   and ool.flow_status_code!='CANCELLED'
   and oeh.order_type_id=ott.transaction_type_id
   and ott.language='US'
   and oeh.payment_term_id=rt.term_id
   and rt.language='US'
   and oeh.header_id=ooh.header_id(+)
   and ooh.hold_source_id=ohs.hold_source_id(+)
   and ohs.hold_id=ohd.hold_id(+)
   and trunc(ool.schedule_ship_date)= to_date('30-APR-14','DD-MON-YY')
    and oeh.PAYMENT_TERM_ID in ( 1021 ,1143);

/*List of Standing Orders along with Credit Card Details on the first open line*/

   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.invoiced_quantity,
  OOLA.TAX_VALUE,
  (OOLA.UNIT_SELLING_PRICE * OOLA.ORDERED_QUANTITY) ITEMTOTALAMOUNT,
  OOLA.FLOW_STATUS_CODE "LineStatus",
  IFTE.TRXN_EXTENSION_ID ,
  ic.ccnumber,
  ic.chname,
  ic.card_owner_id,
  ic.masked_cc_number,
  ic.card_issuer_code,
  ic.expirydate,
  ic.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  IC
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
--AND OOHA.PAYMENT_TYPE_CODE LIKE 'CREDIT_CARD'
AND OP.line_ID(+)        =OOla.line_ID
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      =IC.INSTRID(+)
AND ooha.flow_status_code != 'CLOSED'
 and ooha.PAYMENT_TERM_ID in ( 1021 ,1143)
 and ooha.order_type_id=1010
 and oola.line_id = (select min(l.line_id) from oe_order_lines_all l where l.header_id=ooha.header_id
 and l.flow_status_code not in ('CLOSED','CANCELLED'));

/List of Standing Orders for which credit card details are populated at header level/

SELECT   OOHA.HEADER_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,
  IFTE.TRXN_EXTENSION_ID ,
  ic.ccnumber,
  ic.chname,
  ic.card_owner_id,
  ic.masked_cc_number,
  ic.card_issuer_code,
  ic.expirydate,
  ic.inactive_date
FROM OE_ORDER_HEADERS_ALL OOHA,
  OE_PAYMENTS OP,
  IBY_FNDCPT_TX_EXTENSIONS IFTE,
  IBY_PMT_INSTR_USES_ALL IPUA,
  IBY_CREDITCARD  IC
WHERE OP.header_id(+)        =OOha.header_id
and op.payment_level_code(+)='ORDER'
AND OP.TRXN_EXTENSION_ID=IFTE.TRXN_EXTENSION_ID(+)
AND IFTE.INSTR_ASSIGNMENT_ID=IPUA.INSTRUMENT_PAYMENT_USE_ID(+)
AND IPUA.INSTRUMENT_ID      =IC.INSTRID(+)
AND ooha.flow_status_code not in ('CLOSED' ,'CANCELLED')
 and ooha.PAYMENT_TERM_ID in ( 1021 ,1143)
 and ooha.order_type_id=1010;

XXXXXXXXXXXX0481


/**List of Standng order lines for with credit card details are shown******/

SELECT   OOHA.HEADER_ID,OOHA.ORDER_NUMBER,  OOHA.FLOW_STATUS_CODE "OrderStatus",oola.line_number,oola.flow_status_code,  oola.schedule_ship_date,
  (SELECT hca.account_number
  FROM hz_cust_accounts hca
  WHERE hca.cust_account_id=ooha.sold_to_org_id
  ) CustomerNumber,
  OOHA.PAYMENT_TYPE_CODE,
  ooha.credit_card_number,
  OOLA.ORDERED_ITEM,
  OOLA.ORDERED_QUANTITY,
  OOLA.SHIPPED_QUANTITY,
  oola.invoiced_quantity,
  ic.ccnumber,
  ic.chname,
  ic.card_owner_id,
  ic.masked_cc_number,
  ic.card_issuer_code,
  ic.expirydate,
  ic.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  IC
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
AND OP.line_ID(+)        =OOla.line_ID
AND OP.TRXN_EXTENSION_ID=IFTE.TRXN_EXTENSION_ID(+)
AND IFTE.INSTR_ASSIGNMENT_ID=IPUA.INSTRUMENT_PAYMENT_USE_ID(+)
AND IPUA.INSTRUMENT_ID      =IC.INSTRID(+)
AND ooha.flow_status_code not in ('CLOSED' ,'CANCELLED')
 and ooha.PAYMENT_TERM_ID in ( 1021 ,1143)
 and ooha.order_type_id=1010
 --and trunc(oola.schedule_ship_date)>trunc(sysdate)
--and trunc(oola.schedule_ship_date)= to_date('02-MAY-14','DD-MON-YY')
 and oola.line_id = (select min(l.line_id) from oe_order_lines_all l where l.header_id=ooha.header_id
 and l.flow_status_code not in ('CLOSED','CANCELLED'))
 order by oola.schedule_ship_date asc;

/**********query which we run daily to knows orders on hold***************/

SELECT ooh.header_id,oeh.header_id, ott.name Order_type,oeh.order_number,oeh.payment_type_code,rt.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
,ic.ccnumber,
  ic.chname,
  ic.card_owner_id,
  ic.masked_cc_number,
  ic.card_issuer_code,
  ic.expirydate,
  ic.inactive_date
  FROM oe_order_headers_all oeh, oe_order_lines_all ool,oe_transaction_types_tl ott,
  ra_terms_tl rt,
  oe_order_holds_all ooh,
  oe_hold_sources_all ohs,
  oe_hold_definitions ohd,
  OE_PAYMENTS OP,
  IBY_FNDCPT_TX_EXTENSIONS IFTE,
  IBY_PMT_INSTR_USES_ALL IPUA,
  IBY_CREDITCARD  IC
WHERE 1=1
 and oeh.header_id=ool.header_id
   AND oeh.flow_status_code != 'CLOSED'
   and ool.flow_status_code!='CANCELLED'
   and oeh.order_type_id=ott.transaction_type_id
   and ott.language='US'
   and oeh.payment_term_id=rt.term_id
   and rt.language='US'
   and ool.header_id=ooh.header_id(+)
   and ool.line_id=nvl(ooh.line_id(+),ool.line_id)
 --  and ool.line_id=ooh.line_id(+)
   and ooh.hold_source_id=ohs.hold_source_id(+)
   and ohs.hold_id=ohd.hold_id(+)
   AND OP.line_ID(+)        =Ool.line_ID
AND OP.TRXN_EXTENSION_ID=IFTE.TRXN_EXTENSION_ID(+)
--and op.payment_level_code(+)='ORDER'
  --AND IFTE.TRXN_EXTENSION_ID=ITSA.INITIATOR_EXTENSION_ID
AND IFTE.INSTR_ASSIGNMENT_ID=IPUA.INSTRUMENT_PAYMENT_USE_ID(+)
AND IPUA.INSTRUMENT_ID      =IC.INSTRID(+)
   and trunc(ool.schedule_ship_date)= to_date('08-MAY-14','DD-MON-YY')
    and oeh.PAYMENT_TERM_ID in ( 1021 ,1143);

 SELECT cra.receipt_number AS "Receipt Number",
         rm.name AS "Receipt Method",
         cra.amount AS "Amount",
         TO_CHAR (cra.receipt_date, 'DD-MON-YYYY') AS "Receipt Date",
         (SELECT rstat.meaning
            FROM applsys.fnd_lookup_values rstat
           WHERE     rstat.lookup_code = crh.status
                 AND rstat.lookup_type = 'RECEIPT_CREATION_STATUS'
                 AND rstat.language = 'US')
            AS "Receipt Status",
         TO_CHAR (crh.gl_date, 'DD-MON-YYYY') AS "GL Date",
         tsa.tangibleid AS "PSON",
         (SELECT treq.meaning
            FROM applsys.fnd_lookup_values treq
           WHERE     treq.lookup_code = tsa.reqtype
                 AND treq.lookup_type = 'IBY_TRXN_REQTYPES'
                 AND treq.language = 'US')
            AS "IBY Request Type",
         (SELECT ttype.meaning || ' | ' || ttype.description
            FROM applsys.fnd_lookup_values ttype
           WHERE     ttype.lookup_code = tsa.trxntypeid
                 AND ttype.lookup_type = 'IBY_TRXNTYPES'
                 AND ttype.language = 'US')
            AS "IBY Transaction Type",
         tsa.status AS "IBY Status Code",
         (SELECT istat.meaning || ' | ' || istat.description
            FROM applsys.fnd_lookup_values istat
           WHERE     istat.lookup_code = tsa.status
                 AND istat.lookup_type = 'IBY_TRANSACTION_STATUS'
                 AND istat.language = 'US')
            AS "IBY Status Description",
         tsa.bepcode AS "Payment System Error Code",
         tsa.bepmessage AS "Payment System Error Message"
    FROM ar.ar_cash_receipts_all cra
         INNER JOIN ar.ar_cash_receipt_history_all crh
            ON cra.cash_receipt_id = crh.cash_receipt_id
         INNER JOIN iby.iby_fndcpt_tx_operations fto
            ON cra.payment_trxn_extension_id = fto.trxn_extension_id
         INNER JOIN iby.iby_trxn_summaries_all tsa
            ON fto.transactionid = tsa.transactionid
         INNER JOIN ar.ar_receipt_methods rm
            ON cra.receipt_method_id = rm.receipt_method_id
   WHERE     1 = 1
                AND  crh.status NOT IN ('CLEARED', 'REVERSED')
         -- AND  cra.receipt_number                     = '11068970'--:Receipt_Number
         AND rm.name='CardConnect US'
         AND crh.current_record_flag = 'Y'
         AND cra.receipt_date >='01-OCT-2016'
         AND tsa.status<>0
         AND tsa.updatedate = (SELECT MAX (tsa2.updatedate)
                                 FROM iby.iby_trxn_summaries_all tsa2
                                WHERE tsa2.transactionid = tsa.transactionid)
ORDER BY cra.receipt_date DESC, cra.receipt_number;

References:
http://sairamgoudmalla.blogspot.com/2014/05/credit-card-sales-order-queries.html