Credit Card Sales Order Queries List of Orders which are pick released on Sysdate

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);