Query to Fetch Credit Card Details and CVV of a customer in oracle-R12

Leave a Comment

Query to fetch credit card details and secured CVV number

select hca.cust_account_id,
       hca.party_id,
       hcas.party_site_id,
       hca.account_number "CUSTOMER",
       CreditCardEO.CARD_ISSUER_CODE "CARD TYPE",
       CreditCardEO.CCNUMBER "CARD NUMBER",
       CreditCardEO.EXPIRYDATE "EXP DATE",
       hp.party_name "OWNER NAME",
       hcsu.location,
       CreditCardEO.DESCRIPTION "CARDHOLDER NAME",
       CreditCardEO.INVALIDATION_REASON,
       trunc(ipiu.start_date) "VALID FROM",
       (select iss.SEGMENT_CIPHER_TEXT
          from apps.iby_fndcpt_tx_extensions extn,
               IBY.IBY_SECURITY_SEGMENTS     iss
         where 1 = 1
           and extn.instr_assignment_id = ipiu.instrument_payment_use_id
           and extn.INSTR_CODE_SEC_SEGMENT_ID = iss.SEC_SEGMENT_ID
           and rownum = 1) CVV
  from apps.hz_cust_accounts_all   hca,
       apps.hz_cust_acct_sites_all hcas,
       apps.hz_cust_site_uses_all  hcsu,
       apps.HZ_PARTY_SITE_USES     hpsu,
       apps.IBY_CREDITCARD         CreditCardEO,
       apps.IBY_PMT_INSTR_USES_ALL ipiu,
       apps.hz_parties             hp
 where 1 = 1
   and hca.party_id = hp.party_id
   and hca.cust_account_id = hcas.cust_account_id
   and hcas.cust_acct_site_id = hcsu.cust_acct_site_id
   and hcas.party_site_id = hpsu.party_site_id
   and hpsu.SITE_USE_TYPE = 'CC_BILLING'
   AND CreditCardEO.INSTRID = ipiu.instrument_id
   AND ipiu.INSTRUMENT_TYPE = 'CREDITCARD'
   AND CreditCardEO.CARD_OWNER_ID = hp.party_id
   and hcsu.site_use_code = 'BILL_TO'
   and hcsu.org_id = 'required_operating_unit_id' -- pass operting unit id
 order by customer, CreditCardEO.DESCRIPTION



{*Note : Before sharing cvv number,first check with proper requirement  from security point of view                and policy of your organization and client}.



If You Enjoyed This, Take 5 Seconds To Share It

0 comments:

Post a Comment