View: IBY_XML_FNDCPT_CREDITCARD_V

Product:	IBY - Payments
Description:
Implementation/DBA Data:	APPS.IBY_XML_FNDCPT_CREDITCARD_V

View Text

SELECT 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('PAYEE_KEY')))
, XMLELEMENT("CARDEXPIRATION"
, TO_CHAR(CORE.INSTR_EXPIRYDATE
, 'YYYY-MM-DD'))
, XMLFOREST(IBY_UTILITY_PVT.GET_VIEW_PARAM('SEC_VAL') 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"
, 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
, ''))
, 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"
, ADDRESS.ADDRESS1)
, XMLFOREST( ADDRESS.ADDRESS2 AS "ADDRESSLINE2"
, ADDRESS.ADDRESS3 AS "ADDRESSLINE3" )
, XMLELEMENT("CITY"
, ADDRESS.CITY)
, XMLELEMENT("STATE"
, ADDRESS.STATE)
, XMLELEMENT("COUNTRY"
, DECODE(CARD.ADDRESSID
, NULL
, BILL_ADDR_TERRITORY_CODE
, ADDRESS.COUNTRY))
, XMLELEMENT("POSTALCODE"
, DECODE(CARD.ADDRESSID
, NULL
, BILLING_ADDR_POSTAL_CODE
, ADDRESS.POSTAL_CODE)) ) ) ) END ) END
, TXN.TRXNMID
FROM 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
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(+))

Columns