1. Tables and Views for Financials
  2. IBY_XML_FNDCPT_CREDITCARD_V

IBY_XML_FNDCPT_CREDITCARD_V

Details

  • Schema: FUSION

  • Object owner: IBY

  • Object type: VIEW

Columns

Name

CREDIT_CARD

TRXNMID

Query

SQL_Statement

SELECT /*+ leading(TXN CARD) use_nl(CARD SITE) */

CASE

WHEN (txn.payerinstrid IS NULL)

THEN NULL

ELSE XMLElement("PayerCreditCard",

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("CardDescription", card.description),

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(iby_utility_pvt.get_view_param('SEC_VAL') AS "SecurityValue"), XMLForest(txn.instrsubtype AS "CardIssuer"),

XMLForest( core.card_subtype_code AS "CardSubtype", DECODE(core.card_data_level, NULL, NULL, 'LEVEL' || core.card_data_level) AS "CardDataLevel" ),

XMLElement("CardSecurity",card.encrypted),

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("HolderFirstName", iby_creditcard_pkg.Get_Holder_FirstName(card.instrid, iby_utility_pvt.get_view_param('SYS_KEY'))),

XMLElement("HolderLastName", iby_creditcard_pkg.Get_Holder_LastName(card.instrid, iby_utility_pvt.get_view_param('SYS_KEY'))),

XMLElement("BillingAddress", XMLElement("AddressLine1", address.address1),

XMLForest( address.address2 AS "AddressLine2", address.address3 AS "AddressLine3" ), XMLElement("City", address.city),

XMLElement("State", DECODE(address.country,'CA',address.province,address.state)), XMLElement("Country",DECODE(card.addressid, NULL,bill_addr_territory_code, address.country)),

XMLElement("CountryName",terrt.territory_short_name), XMLElement("ISO3DigitCountry", terrb.iso_territory_code),

XMLElement("PostalCode",DECODE( card.addressid, NULL,billing_addr_postal_code, address.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) ),

XMLElement("Email", IBY_FNDCPT_EXTRACT_GEN_PVT.Get_CC_Owner_Email(card.card_owner_id,txn.trxnmid)) ),

CASE

WHEN card.attribute_category IS NOT NULL

THEN XMLElement("DescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'IBY_CREDITCARD', card.instrid, NULL))

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 u,

IBY_CREDITCARD CARD,

IBY_TRXN_CORE CORE,

IBY_TRXN_SUMMARIES_ALL TXN

WHERE txn.trxnmid = core.trxnmid(+)

AND txn.payerinstrid = card.instrid(+)

AND card.card_owner_id = owner.party_id(+)

AND card.addressid = u.party_site_use_id(+)

AND u.party_site_id = site.party_site_id(+)

AND site.location_id = address.location_id(+)

AND address.country = terrb.territory_code(+)

AND terrb.territory_code = terrt.territory_code(+)

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