[Home] [Help]
View: IBY_XML_ONLINE_FCI_1_0_V
View Text
SELECT XMLELEMENT("FUNDSCAPTUREINSTRUCTION"
, XMLELEMENT("INSTRUCTIONINFO"
, XMLELEMENT("INSTRUCTIONINTERNALID"
, TXN.TRXNMID)
, XMLELEMENT("INSTRUCTIONNAME"
, TXN.TANGIBLEID)
, XMLELEMENT("INSTRUCTIONCREATIONDATE"
, TO_CHAR(TXN.CREATION_DATE
, 'YYYY-MM-DD"T"HH24:MI:SS'))
, XMLELEMENT("INSTRUCTIONSENTDATE"
, TO_CHAR(SYSDATE
, 'YYYY-MM-DD"T"HH24:MI:SS'))
, XMLELEMENT("INSTRUCTIONSTATUS"
, XMLELEMENT("CODE"
, '')
, XMLELEMENT("MEANING"
, '') ) )
, XMLELEMENT("INSTRUCTIONSEQUENCE"
, XMLELEMENT("SEQUENCENAME"
, 'IBY_TRXNSUMM_MID_S')
, XMLELEMENT("LASTVALUE"
, TXN.TRXNMID) )
, XMLELEMENT("INSTRUCTIONTOTALS"
, XMLELEMENT("PAYEEACCOUNTCOUNT"
, 1) )
, XMLELEMENT("PAYEEACCOUNT"
, XMLELEMENT("PAYMENTSYSTEMACCOUNT"
, XMLELEMENT("ACCOUNTNAME"
, TXN.BEPKEY)
, OPTS.ACCOUNT_OPTIONS )
, CASE WHEN (NOT XML_PAYEE_BANK.INSTRID IS NULL) THEN XMLELEMENT("BANKACCOUNT"
, XML_PAYEE_BANK.BANK_ACCOUNT_CONTENT) END
, XMLELEMENT("PAYEE"
, XMLELEMENT("NAME"
, NVL(PAYEE.DBA_NAME
, PAYEE.NAME))
, XMLELEMENT("ADDRESS"
, XMLELEMENT("ADDRESSLINE1"
, NULL)
, XMLFOREST(NULL AS "ADDRESSLINE2"
, NULL AS "ADDRESSLINE3")
, XMLELEMENT("CITY"
, PAYEE.DBA_CITY)
, XMLELEMENT("STATE"
, PAYEE.DBA_STATE_PROVINCE)
, XMLELEMENT("COUNTRY"
, PAYEE.DBA_COUNTRY)
, XMLELEMENT("POSTALCODE"
, NULL) )
, XMLFOREST(DECODE(PAYEE.MCC_CODE
, -1
, NULL
, PAYEE.MCC_CODE) AS "MCC") )
, XMLELEMENT("ORDERCOUNT"
, 1)
, XMLELEMENT("ACCOUNTTOTALS"
, XMLELEMENT("AUTHORIZATIONSTOTAL"
, XMLELEMENT("VALUE"
, DECODE(TXN.TRXNTYPEID
, 2
, TXN.AMOUNT
, 0))
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, TXN.CURRENCYNAMECODE) ) )
, XMLELEMENT("CAPTURESTOTAL"
, XMLELEMENT("VALUE"
, DECODE(TXN.TRXNTYPEID
, 3
, TXN.AMOUNT
, 8
, TXN.AMOUNT
, 0))
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, TXN.CURRENCYNAMECODE) ) )
, XMLELEMENT("CREDITSTOTAL"
, XMLELEMENT("VALUE"
, DECODE(TXN.TRXNTYPEID
, 5
, TXN.AMOUNT
, 11
, TXN.AMOUNT
, 0))
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, TXN.CURRENCYNAMECODE) ) ) )
, XML_ORDER.FNDCPT_ORDER ) )
, TXN.TRXNMID
FROM IBY_TRXN_SUMMARIES_ALL TXN
, IBY_PAYEE PAYEE
, IBY_BEPKEYS KEYS
, IBY_XML_FNDCPT_BANKACCOUNT_V XML_PAYEE_BANK
, IBY_XML_BEP_ACCT_OPTIONS_V OPTS
, IBY_XML_FNDCPT_ORDER_1_0_V XML_ORDER
WHERE (TXN.TRXNMID = XML_ORDER.TRXNMID)
AND (TXN.PAYEEINSTRID = XML_PAYEE_BANK.INSTRID(+))
AND (TXN.PAYEEID = PAYEE.PAYEEID)
AND (TXN.PAYEEID = KEYS.OWNERID)
AND (TXN.BEPKEY = KEYS.KEY)
AND (KEYS.OWNERTYPE = 'PAYEE')
AND (KEYS.BEP_ACCOUNT_ID = OPTS.BEP_ACCOUNT_ID (+))
Columns
APPS.IBY_XML_ONLINE_FCI_1_0_V