Query to Get Credit Card Details in Oracle Apps R12 - Orapoint

In this post we will discuss on query to get credit card details with CVV number in Oracle Apps R12, this query will fetch certain columns like customer name, card type, card number, expiry date, owner name, card holder name, valid from, CVV number.

Customer’s credit cards are recorded in table IBY_CREDITCARD. Main columns of this table

1. Addressid refers to the HZ_PARTY_SITE_USES table’s party_site_use_id column.
2. Card Owner Id refers to the HZ_PARTIES table’s party_id column.
3. Card Issuer Code refers to the IBY_CREDITCARD_ISSUERS_B/TL tables’ card_issuer_code.

Query to get Credit Card Details in Oracle Apps R12

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