eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.IBY_XML_FNDCPT_CREDITCARD_V

Source

View Text - Preformatted


SELECT /*+ leading(TXN CARD) use_nl(CARD SITEU SITE) */ CASE WHEN(txn.payerinstrid IS NULL) AND(txn.instrnumber IS NULL) THEN NULL ELSE xmlelement("PayerCreditCard", CASE WHEN(txn.payerinstrid IS NULL) THEN xmlconcat(xmlelement("CardNumber", iby_transactioncc_pkg.unencrypt_instr_num(txn.trxnmid, iby_utility_pvt.get_view_param('SYS_KEY'))), xmlelement("MaskedCardNumber", iby_creditcard_pkg.mask_card_number(iby_transactioncc_pkg.unencrypt_instr_num(txn.trxnmid, iby_utility_pvt.get_view_param('SYS_KEY')))), xmlelement("CardExpiration", CASE WHEN(core.encrypted = 'Y' AND core.expiry_sec_segment_id IS NOT NULL) THEN (to_char(iby_security_pkg.decrypt_date_field(core.expiry_sec_segment_id, iby_utility_pvt.get_view_param('SYS_KEY')), 'YYYY-MM-DD')) ELSE(to_char(core.instr_expirydate, 'YYYY-MM-DD')) END), xmlforest(iby_utility_pvt.get_view_param('SEC_VAL') AS "SecurityValue"), xmlforest(txn.instrsubtype AS "CardIssuer"), xmlelement("CardHolder", xmlelement("HolderName", CASE WHEN(core.encrypted = 'Y' AND core.chname_sec_segment_id IS NOT NULL) THEN(iby_security_pkg.decrypt_field_vals(core.CHNAME_SEC_SEGMENT_ID, iby_utility_pvt.get_view_param('SYS_KEY'))) ELSE(core.instr_owner_name) END), xmlelement("BillingAddress", xmlelement("AddressLine1", core.instr_owner_address_line1), xmlforest(core.instr_owner_address_line2 AS "AddressLine2", core.instr_owner_address_line3 AS "AddressLine3"), xmlelement("City", core.instr_owner_city), xmlelement("State", core.instr_owner_state_province), xmlelement("Country", core.instr_owner_country), xmlelement("PostalCode", core.instr_owner_postalcode)), xmlforest(core.instr_owner_phone AS "PhoneNumber", core.instr_owner_email AS "EmailAddress")), xmlforest(core.card_subtype_code AS "CardSubtype", decode(core.card_data_level, NULL, NULL, 'LEVEL' || core.card_data_level) AS "CardDataLevel")) ELSE xmlconcat(xmlelement("CardNumber", iby_creditcard_pkg.uncipher_ccnumber(card.instrid, iby_utility_pvt.get_view_param('SYS_KEY'))), xmlelement("MaskedCardNumber", card.masked_cc_number), xmlelement("CardExpiration", CASE WHEN(card.encrypted = 'A' AND card.expiry_sec_segment_id IS NOT NULL) THEN(to_char(iby_security_pkg.decrypt_date_field(card.expiry_sec_segment_id, iby_utility_pvt.get_view_param('SYS_KEY')), 'YYYY-MM-DD')) ELSE(to_char(card.expirydate, 'YYYY-MM-DD')) END), xmlforest(iby_utility_pvt.get_view_param('SEC_VAL') AS "SecurityValue"), xmlforest(txn.instrsubtype AS "CardIssuer"), xmlelement("CardHolder", xmlelement("HolderName", nvl( CASE WHEN(card.encrypted = 'A' AND card.chname_sec_segment_id IS NOT NULL) THEN(iby_creditcard_pkg.decrypt_chname(iby_utility_pvt.get_view_param('SYS_KEY'), card.instrid)) ELSE(card.chname) END, owner.party_name)), xmlelement("BillingAddress", xmlelement("AddressLine1", nvl(address.address1, upg.address1)), xmlforest(nvl(address.address2, upg.address2) AS "AddressLine2", nvl(address.address3, upg.address3) AS "AddressLine3"), xmlelement("City", nvl(address.city, upg.city)), xmlelement("State", nvl(address.state, upg.state)), xmlelement("Country", decode(card.addressid, NULL, bill_addr_territory_code, nvl(address.country, upg.country))), xmlelement("CountryName", terr.territory_short_name), xmlelement("ISO3DigitCountry", terr.iso_territory_code), xmlelement("PostalCode", decode(card.addressid, NULL, billing_addr_postal_code, nvl(address.postal_code, upg.postal_code))), xmlelement("PreFormattedConcatenatedAddress", hz_format_pub.format_address(address.location_id)), xmlelement("PreFormattedMailingAddress", hz_format_pub.format_address(address.location_id, 'POSTAL_ADDR')), xmlelement("AddressName", site.party_site_name))),xmlforest(core.card_subtype_code AS "CardSubtype", DECODE(core.card_data_level, NULL, NULL, 'LEVEL' || core.card_data_level) AS "CardDataLevel")) END) END, txn.trxnmid FROM FND_TERRITORIES_VL TERR,HZ_LOCATIONS ADDRESS,HZ_PARTIES OWNER,HZ_PARTY_SITES SITE,HZ_PARTY_SITE_USES SITEU,IBY_CREDITCARD CARD,IBY_TRXN_CORE CORE,IBY_TRXN_SUMMARIES_ALL TXN,HZ_LOCATIONS UPG WHERE (txn.trxnmid = core.trxnmid(+)) AND (txn.payerinstrid = card.instrid(+)) AND (card.card_owner_id = owner.party_id(+)) AND (card.addressid = siteu.party_site_use_id(+)) AND (siteu.party_site_id = site.party_site_id(+)) AND (site.location_id = address.location_id(+)) AND (card.upgrade_addressid = upg.location_id(+)) AND (address.country = terr.territory_code(+))

View Text - HTML Formatted

SELECT /*+ LEADING(TXN CARD) USE_NL(CARD SITEU SITE) */ CASE WHEN(TXN.PAYERINSTRID IS NULL) AND(TXN.INSTRNUMBER IS NULL) THEN NULL ELSE XMLELEMENT("PAYERCREDITCARD"
, CASE WHEN(TXN.PAYERINSTRID IS NULL) THEN XMLCONCAT(XMLELEMENT("CARDNUMBER"
, IBY_TRANSACTIONCC_PKG.UNENCRYPT_INSTR_NUM(TXN.TRXNMID
, IBY_UTILITY_PVT.GET_VIEW_PARAM('SYS_KEY')))
, XMLELEMENT("MASKEDCARDNUMBER"
, IBY_CREDITCARD_PKG.MASK_CARD_NUMBER(IBY_TRANSACTIONCC_PKG.UNENCRYPT_INSTR_NUM(TXN.TRXNMID
, IBY_UTILITY_PVT.GET_VIEW_PARAM('SYS_KEY'))))
, XMLELEMENT("CARDEXPIRATION"
, CASE WHEN(CORE.ENCRYPTED = 'Y'
AND CORE.EXPIRY_SEC_SEGMENT_ID IS NOT NULL) THEN (TO_CHAR(IBY_SECURITY_PKG.DECRYPT_DATE_FIELD(CORE.EXPIRY_SEC_SEGMENT_ID
, IBY_UTILITY_PVT.GET_VIEW_PARAM('SYS_KEY'))
, 'YYYY-MM-DD')) ELSE(TO_CHAR(CORE.INSTR_EXPIRYDATE
, 'YYYY-MM-DD')) END)
, XMLFOREST(IBY_UTILITY_PVT.GET_VIEW_PARAM('SEC_VAL') AS "SECURITYVALUE")
, XMLFOREST(TXN.INSTRSUBTYPE AS "CARDISSUER")
, XMLELEMENT("CARDHOLDER"
, XMLELEMENT("HOLDERNAME"
, CASE WHEN(CORE.ENCRYPTED = 'Y'
AND CORE.CHNAME_SEC_SEGMENT_ID IS NOT NULL) THEN(IBY_SECURITY_PKG.DECRYPT_FIELD_VALS(CORE.CHNAME_SEC_SEGMENT_ID
, IBY_UTILITY_PVT.GET_VIEW_PARAM('SYS_KEY'))) ELSE(CORE.INSTR_OWNER_NAME) END)
, XMLELEMENT("BILLINGADDRESS"
, XMLELEMENT("ADDRESSLINE1"
, CORE.INSTR_OWNER_ADDRESS_LINE1)
, XMLFOREST(CORE.INSTR_OWNER_ADDRESS_LINE2 AS "ADDRESSLINE2"
, CORE.INSTR_OWNER_ADDRESS_LINE3 AS "ADDRESSLINE3")
, XMLELEMENT("CITY"
, CORE.INSTR_OWNER_CITY)
, XMLELEMENT("STATE"
, CORE.INSTR_OWNER_STATE_PROVINCE)
, XMLELEMENT("COUNTRY"
, CORE.INSTR_OWNER_COUNTRY)
, XMLELEMENT("POSTALCODE"
, CORE.INSTR_OWNER_POSTALCODE))
, XMLFOREST(CORE.INSTR_OWNER_PHONE AS "PHONENUMBER"
, CORE.INSTR_OWNER_EMAIL AS "EMAILADDRESS"))
, XMLFOREST(CORE.CARD_SUBTYPE_CODE AS "CARDSUBTYPE"
, DECODE(CORE.CARD_DATA_LEVEL
, NULL
, NULL
, 'LEVEL' || CORE.CARD_DATA_LEVEL) AS "CARDDATALEVEL")) ELSE XMLCONCAT(XMLELEMENT("CARDNUMBER"
, IBY_CREDITCARD_PKG.UNCIPHER_CCNUMBER(CARD.INSTRID
, IBY_UTILITY_PVT.GET_VIEW_PARAM('SYS_KEY')))
, XMLELEMENT("MASKEDCARDNUMBER"
, CARD.MASKED_CC_NUMBER)
, XMLELEMENT("CARDEXPIRATION"
, CASE WHEN(CARD.ENCRYPTED = 'A'
AND CARD.EXPIRY_SEC_SEGMENT_ID IS NOT NULL) THEN(TO_CHAR(IBY_SECURITY_PKG.DECRYPT_DATE_FIELD(CARD.EXPIRY_SEC_SEGMENT_ID
, IBY_UTILITY_PVT.GET_VIEW_PARAM('SYS_KEY'))
, 'YYYY-MM-DD')) ELSE(TO_CHAR(CARD.EXPIRYDATE
, 'YYYY-MM-DD')) END)
, XMLFOREST(IBY_UTILITY_PVT.GET_VIEW_PARAM('SEC_VAL') AS "SECURITYVALUE")
, XMLFOREST(TXN.INSTRSUBTYPE AS "CARDISSUER")
, XMLELEMENT("CARDHOLDER"
, XMLELEMENT("HOLDERNAME"
, NVL( CASE WHEN(CARD.ENCRYPTED = 'A'
AND CARD.CHNAME_SEC_SEGMENT_ID IS NOT NULL) THEN(IBY_CREDITCARD_PKG.DECRYPT_CHNAME(IBY_UTILITY_PVT.GET_VIEW_PARAM('SYS_KEY')
, CARD.INSTRID)) ELSE(CARD.CHNAME) END
, OWNER.PARTY_NAME))
, XMLELEMENT("BILLINGADDRESS"
, XMLELEMENT("ADDRESSLINE1"
, NVL(ADDRESS.ADDRESS1
, UPG.ADDRESS1))
, XMLFOREST(NVL(ADDRESS.ADDRESS2
, UPG.ADDRESS2) AS "ADDRESSLINE2"
, NVL(ADDRESS.ADDRESS3
, UPG.ADDRESS3) AS "ADDRESSLINE3")
, XMLELEMENT("CITY"
, NVL(ADDRESS.CITY
, UPG.CITY))
, XMLELEMENT("STATE"
, NVL(ADDRESS.STATE
, UPG.STATE))
, XMLELEMENT("COUNTRY"
, DECODE(CARD.ADDRESSID
, NULL
, BILL_ADDR_TERRITORY_CODE
, NVL(ADDRESS.COUNTRY
, UPG.COUNTRY)))
, XMLELEMENT("COUNTRYNAME"
, TERR.TERRITORY_SHORT_NAME)
, XMLELEMENT("ISO3DIGITCOUNTRY"
, TERR.ISO_TERRITORY_CODE)
, XMLELEMENT("POSTALCODE"
, DECODE(CARD.ADDRESSID
, NULL
, BILLING_ADDR_POSTAL_CODE
, NVL(ADDRESS.POSTAL_CODE
, UPG.POSTAL_CODE)))
, XMLELEMENT("PREFORMATTEDCONCATENATEDADDRESS"
, HZ_FORMAT_PUB.FORMAT_ADDRESS(ADDRESS.LOCATION_ID))
, XMLELEMENT("PREFORMATTEDMAILINGADDRESS"
, HZ_FORMAT_PUB.FORMAT_ADDRESS(ADDRESS.LOCATION_ID
, 'POSTAL_ADDR'))
, XMLELEMENT("ADDRESSNAME"
, SITE.PARTY_SITE_NAME)))
, XMLFOREST(CORE.CARD_SUBTYPE_CODE AS "CARDSUBTYPE"
, DECODE(CORE.CARD_DATA_LEVEL
, NULL
, NULL
, 'LEVEL' || CORE.CARD_DATA_LEVEL) AS "CARDDATALEVEL")) END) END
, TXN.TRXNMID
FROM FND_TERRITORIES_VL TERR
, HZ_LOCATIONS ADDRESS
, HZ_PARTIES OWNER
, HZ_PARTY_SITES SITE
, HZ_PARTY_SITE_USES SITEU
, IBY_CREDITCARD CARD
, IBY_TRXN_CORE CORE
, IBY_TRXN_SUMMARIES_ALL TXN
, HZ_LOCATIONS UPG
WHERE (TXN.TRXNMID = CORE.TRXNMID(+))
AND (TXN.PAYERINSTRID = CARD.INSTRID(+))
AND (CARD.CARD_OWNER_ID = OWNER.PARTY_ID(+))
AND (CARD.ADDRESSID = SITEU.PARTY_SITE_USE_ID(+))
AND (SITEU.PARTY_SITE_ID = SITE.PARTY_SITE_ID(+))
AND (SITE.LOCATION_ID = ADDRESS.LOCATION_ID(+))
AND (CARD.UPGRADE_ADDRESSID = UPG.LOCATION_ID(+))
AND (ADDRESS.COUNTRY = TERR.TERRITORY_CODE(+))