VIEW: APPS.IBY_XML_ONLINE_FCI_1_0_V

Source

View Text - Preformatted

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 ), XMLElement("Payee", XMLElement("Name",NVL(payee.dba_name,payee.name)), 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_bep_acct_options_v opts, iby_xml_fndcpt_order_1_0_v xml_order WHERE (txn.trxnmid = xml_order.trxnmid) 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 (+))

View Text - HTML Formatted

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 )
, XMLELEMENT("PAYEE"
, XMLELEMENT("NAME"
, NVL(PAYEE.DBA_NAME
, PAYEE.NAME))
, 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_BEP_ACCT_OPTIONS_V OPTS
, IBY_XML_FNDCPT_ORDER_1_0_V XML_ORDER
WHERE (TXN.TRXNMID = XML_ORDER.TRXNMID)
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 (+))