VIEW: APPS.IBY_XML_FNDCPT_DEBITCARD_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("PayerDebitCard", CASE WHEN (txn.payerinstrid IS NULL) THEN XMLConcat( XMLElement("CardNumber",iby_transactioncc_pkg.unencrypt_instr_num(txn.trxnmid,iby_utility_pvt.get_view_param('PAYEE_KEY'))), XMLElement("MaskedCardNumber", iby_creditcard_pkg.mask_card_number(iby_transactioncc_pkg.unencrypt_instr_num(txn.trxnmid,iby_utility_pvt.get_view_param('PAYEE_KEY'))) ), XMLElement("CardExpiration",TO_CHAR(core.instr_expirydate,'YYYY-MM-DD')), XMLForest(NULL AS "SecurityValue"), XMLForest(txn.instrsubtype AS "CardIssuer"), XMLElement("CardHolder", XMLElement("HolderName",core.instr_owner_name), 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" ) ) ELSE XMLConcat( XMLElement("CardNumber",iby_creditcard_pkg.uncipher_ccnumber(card.instrid, iby_utility_pvt.get_view_param('PAYEE_KEY'))), XMLElement("MaskedCardNumber", card.masked_cc_number), XMLElement("CardExpiration",TO_CHAR(card.expirydate,'YYYY-MM-DD')), XMLForest(null AS "SecurityValue"), XMLForest(txn.instrsubtype AS "CardIssuer"), XMLElement("CardHolder", XMLElement("HolderName",NVL(card.chname,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) ) ) ) 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("PAYERDEBITCARD"
, CASE WHEN (TXN.PAYERINSTRID IS NULL) THEN XMLCONCAT( XMLELEMENT("CARDNUMBER"
, IBY_TRANSACTIONCC_PKG.UNENCRYPT_INSTR_NUM(TXN.TRXNMID
, IBY_UTILITY_PVT.GET_VIEW_PARAM('PAYEE_KEY')))
, XMLELEMENT("MASKEDCARDNUMBER"
, IBY_CREDITCARD_PKG.MASK_CARD_NUMBER(IBY_TRANSACTIONCC_PKG.UNENCRYPT_INSTR_NUM(TXN.TRXNMID
, IBY_UTILITY_PVT.GET_VIEW_PARAM('PAYEE_KEY'))) )
, XMLELEMENT("CARDEXPIRATION"
, TO_CHAR(CORE.INSTR_EXPIRYDATE
, 'YYYY-MM-DD'))
, XMLFOREST(NULL AS "SECURITYVALUE")
, XMLFOREST(TXN.INSTRSUBTYPE AS "CARDISSUER")
, XMLELEMENT("CARDHOLDER"
, XMLELEMENT("HOLDERNAME"
, CORE.INSTR_OWNER_NAME)
, 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" ) ) ELSE XMLCONCAT( XMLELEMENT("CARDNUMBER"
, IBY_CREDITCARD_PKG.UNCIPHER_CCNUMBER(CARD.INSTRID
, IBY_UTILITY_PVT.GET_VIEW_PARAM('PAYEE_KEY')))
, XMLELEMENT("MASKEDCARDNUMBER"
, CARD.MASKED_CC_NUMBER)
, XMLELEMENT("CARDEXPIRATION"
, TO_CHAR(CARD.EXPIRYDATE
, 'YYYY-MM-DD'))
, XMLFOREST(NULL AS "SECURITYVALUE")
, XMLFOREST(TXN.INSTRSUBTYPE AS "CARDISSUER")
, XMLELEMENT("CARDHOLDER"
, XMLELEMENT("HOLDERNAME"
, NVL(CARD.CHNAME
, 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) ) ) ) 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(+))