1. Tables and Views for Financials
  2. IBY_XML_FNDCPT_DEBITCARD_V

IBY_XML_FNDCPT_DEBITCARD_V

Details

  • Schema: FUSION

  • Object owner: IBY

  • Object type: VIEW

Columns

Name

DEBIT_CARD

TRXNMID

Query

SQL_Statement

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('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",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('SYS_KEY'))), XMLElement("MaskedCardNumber", card.masked_cc_number), XMLElement("CardExpiration", TO_CHAR(NVL2 (card.expiry_sec_segment_id, IBY_SECURITY_PKG.DECRYPT_DATE_FIELD(card.expiry_sec_segment_id, IBY_UTILITY_PVT.GET_VIEW_PARAM('SYS_KEY')), card.expirydate), 'YYYY-MM-DD')), XMLForest(NULL AS "SecurityValue"), XMLForest(txn.instrsubtype AS "CardIssuer"), XMLElement("CardHolder", XMLElement("HolderName", NVL2(card.chname_sec_segment_id,iby_creditcard_pkg.decrypt_chname(IBY_UTILITY_PVT.GET_VIEW_PARAM('SYS_KEY'),card.instrid),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", terrt.territory_short_name), XMLElement("ISO3DigitCountry", terrb.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) ) ),

CASE WHEN card.attribute_category IS NOT NULL THEN XMLElement("DescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'IBY_CREDITCARD', card.instrid, NULL)) END

)

END )

END,

txn.trxnmid

FROM FND_TERRITORIES_TL TERRT, FND_TERRITORIES_B TERRB,

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 = terrb.territory_code(+))

AND (terrb.territory_code = terrt.territory_code(+))

AND (terrt.language(+) = USERENV('LANG'))