IBY_XML_ONLINE_FCI_1_0

Details

Schema: FUSION
Object owner: IBY
Object type: VIEW

Columns

Name
INSTRUCTION TRXNMID

Name

INSTRUCTION

TRXNMID

Query

SQL_Statement
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 (+))

SQL_Statement

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 (+))