View: IBY_XML_FNDCPT_ACCT_1_0_V

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

View Text

SELECT XMLELEMENT("PAYEEACCOUNT"
, XMLELEMENT("PAYMENTSYSTEMACCOUNT"
, XMLELEMENT("ACCOUNTNAME"
, TXN.BEPKEY)
, EXTRACT(XMLAGG(XMLELEMENT("OPTIONSET"
, OPTS.ACCOUNT_OPTIONS))
, 'OPTIONSET[1]/*') )
, CASE WHEN (NOT XML_BANK.INSTRID IS NULL) THEN EXTRACT(XMLAGG(XMLELEMENT("BANKACCOUNT"
, XML_BANK.BANK_ACCOUNT_CONTENT))
, '/BANKACCOUNT[1]') END
, XMLELEMENT("PAYEE"
, XMLELEMENT("NAME"
, PAYEE.NAME)
, XMLELEMENT("ADDRESS"
, XMLELEMENT("ADDRESSLINE1"
, NULL)
, XMLFOREST(NULL AS "ADDRESSLINE2"
, NULL AS "ADDRESSLINE3")
, XMLELEMENT("CITY"
, NULL)
, XMLELEMENT("STATE"
, NULL)
, XMLELEMENT("COUNTRY"
, NULL)
, XMLELEMENT("POSTALCODE"
, NULL) )
, XMLFOREST(DECODE(PAYEE.MCC_CODE
, -1
, NULL
, PAYEE.MCC_CODE) AS "MCC") )
, XMLELEMENT("ORDERCOUNT"
, COUNT(TXN.TRXNMID))
, XMLELEMENT("ACCOUNTTOTALS"
, XMLELEMENT("AUTHORIZATIONSTOTAL"
, XMLELEMENT("VALUE"
, DECODE(TXN.INSTRTYPE
, 'PINLESSDEBITCARD'
, 0
, 'BANKACCOUNT'
, 0
, SUM(DECODE(TXN.TRXNTYPEID
, 2
, TXN.AMOUNT
, 0)) ))
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, MAX(TXN.CURRENCYNAMECODE)) ) )
, XMLELEMENT("CAPTURESTOTAL"
, XMLELEMENT("VALUE"
, DECODE(TXN.INSTRTYPE
, 'PINLESSDEBITCARD'
, SUM(DECODE(TXN.TRXNTYPEID
, 2
, TXN.AMOUNT
, 0))
, 'BANKACCOUNT'
, SUM(DECODE(TXN.REQTYPE
, 'ORAPMTREQ'
, TXN.AMOUNT
, 0))
, SUM(DECODE(TXN.TRXNTYPEID
, 3
, TXN.AMOUNT
, 8
, TXN.AMOUNT
, 0)) ))
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, MAX(TXN.CURRENCYNAMECODE)) ) )
, XMLELEMENT("CREDITSTOTAL"
, XMLELEMENT("VALUE"
, DECODE(TXN.INSTRTYPE
, 'PINLESSDEBITCARD'
, 0
, 'BANKACCOUNT'
, SUM(DECODE(TXN.REQTYPE
, 'ORAPMTCREDIT'
, TXN.AMOUNT
, 0))
, SUM(DECODE(TXN.TRXNTYPEID
, 5
, TXN.AMOUNT
, 11
, TXN.AMOUNT
, 0)) ))
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, MAX(TXN.CURRENCYNAMECODE)) ) ) )
, XMLAGG(XML_ORDER.FNDCPT_ORDER) )
, TXN.MBATCHID
, TXN.PAYEEINSTRID
FROM IBY_TRXN_SUMMARIES_ALL TXN
, IBY_PAYEE PAYEE
, IBY_BEPKEYS KEYS
, IBY_XML_FNDCPT_BANKACCOUNT_V XML_BANK
, IBY_XML_BEP_ACCT_OPTIONS_V OPTS
, IBY_XML_FNDCPT_ORDER_1_0_V XML_ORDER
WHERE (TXN.PAYEEID = PAYEE.PAYEEID)
AND (TXN.PAYEEINSTRID = XML_BANK.INSTRID(+))
AND (TXN.PAYEEID = KEYS.OWNERID)
AND (TXN.BEPKEY = KEYS.KEY)
AND (KEYS.OWNERTYPE = 'PAYEE')
AND (KEYS.BEP_ACCOUNT_ID = OPTS.BEP_ACCOUNT_ID(+))
AND (TXN.TRXNMID = XML_ORDER.TRXNMID) GROUP BY TXN.MBATCHID
, TXN.PAYEEINSTRID
, TXN.INSTRTYPE
, TXN.BEPKEY
, PAYEE.NAME
, PAYEE.MCC_CODE
, OPTS.BEP_ACCOUNT_ID
, XML_BANK.INSTRID

Columns