Search Results sys_key

Overview

APPS.IBY_XML_FNDCPT_CREDITCARD_V is a database view in the Oracle E-Business Suite Payments (IBY) module that generates XML representations of payer credit card instruments used in payment transactions. Its primary purpose is to expose credit card data — card numbers, expiration dates, cardholder details, and billing addresses — as structured XMLTYPE documents, typically for outbound integration with payment processors, gateways, or external systems. The view is not a simple projection of a single table; rather, it uses SQL/XML functions such as XMLELEMENT, XMLFOREST, and XMLCONCAT to assemble nested XML fragments under a PayerCreditCard root element.

A distinguishing feature is the use of iby_utility_pvt.get_view_param('SYS_KEY') throughout the definition. This view parameter, commonly searched as sys_key, supplies the encryption key required by the IBY security and credit card packages to decrypt sensitive fields (card number, expiration date, cardholder name) at runtime. This design keeps decryption logic external to the view text and tied to the session's security context.

Underlying Base Objects

The view is defined over several documented base objects, referenced primarily through APPS synonyms:

• IBY_CREDITCARD and IBY_TRXN_CORE — the core credit card instrument and transaction records supplying card and payer data.
• IBY_TRXN_SUMMARIES_ALL — transaction summary data joined into the output.
• IBY_CREDITCARD_PKG, IBY_TRANSACTIONCC_PKG, IBY_SECURITY_PKG, and IBY_UTILITY_PVT — PL/SQL packages that perform decryption (unencrypt_instr_num, uncipher_ccnumber, decrypt_date_field, decrypt_field_vals) and supply view parameters such as SYS_KEY and SEC_VAL.
• HZ_PARTIES, HZ_PARTY_SITES, HZ_PARTY_SITE_USES, HZ_LOCATIONS, and HZ_FORMAT_PUB — TCA party/location objects used to build billing address and cardholder elements.
• FND_TERRITORIES_VL — reference data for country fields.
• XMLTYPE — the Oracle datatype returned by the view.

Key Columns

Because the view returns an XMLTYPE result rather than flat columns, its logical output is the set of XML elements it constructs. Important elements include:

• CardNumber — the decrypted full card number, produced via unencrypt_instr_num / uncipher_ccnumber using the SYS_KEY parameter.
• MaskedCardNumber — a masked rendering from mask_card_number, used where full PAN exposure is undesirable.
• CardExpiration — the expiry date, either decrypted via decrypt_date_field or taken directly from instr_expirydate and formatted as 'YYYY-MM-DD'.
• SecurityValue — the card security code sourced from the SEC_VAL view parameter.
• CardIssuer, CardSubtype, CardDataLevel — instrument classification attributes (e.g., issuer type, card subtype code, and data level as 'LEVEL' concatenated with card_data_level).
• CardHolder — nested element containing HolderName (decrypted when encrypted = 'Y'), BillingAddress (address lines, city, state, country, postal code), PhoneNumber, and EmailAddress.

A conditional at the top of the SQL returns NULL when both payerinstrid and instrnumber are null, so empty instruments yield no XML output.

Common Use Cases and Queries

This view is typically consumed by payment integration programs that require a conformed XML payload of a credit card instrument. Common scenarios include building request messages for a payment gateway, debugging encryption key configuration, or auditing decrypted cardholder/billing data for support purposes.

A representative query retrieving XML for a specific transaction might look like:

• SELECT * FROM apps.iby_xml_fndcpt_creditcard_v WHERE txn_id = :txn_id;

Because the view depends on session-scoped view parameters, callers generally invoke it after the framework has populated SYS_KEY and SEC_VAL via iby_utility_pvt. Where SYS_KEY is unset or incorrect, decryption routines return null or error, which is a frequent root cause of missing CardNumber or CardExpiration values. Note also that the definition references alias sources such as txn and core, indicating internal joins across IBY_TRXN_CORE and IBY_CREDITCARD, so the effective row set reflects each credit card transaction instrument present in the underlying IBY tables.