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