eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.IBY_XML_FNDCPT_ORDER_PN_1_0_V

Source

View Text - Preformatted


SELECT XMLElement("FundsCaptureOrder", XMLElement("OrderSourceInfo", XMLForest(txn.ecappid as "ApplicationInternalID", app.application_short_name as "ApplicationName") ), XMLElement("OrderNumber", XMLElement("PayeeOrderNumber",txn.tangibleid) ), XMLForest(tang.memo AS "PayeeOrderMemo"), XMLForest(tang.refinfo AS "PayeeOrderRefID"), XMLForest(tang.order_medium AS "OrderMedium"), CASE WHEN (NOT xml_payee_ba.trxnmid IS NULL)THEN XMLElement("PayeeBankAccount",xml_payee_ba.bank_account_content) END, XMLElement("OrderAmount", XMLElement("Value",txn.amount), XMLElement("Currency", XMLElement("Code",txn.currencynamecode) ) ), XMLElement("PayeeLegalEntity",xml_payee.xml_payee), XMLElement("Payer", xml_payer.payer), CASE WHEN (txn.instrtype IN ('CREDITCARD','PURCHASECARD')) OR (txn.instrtype IS NULL) THEN XMLConcat( xml_cc.credit_card, orig_xml_cc.credit_card, XMLElement("CreditCardTransaction", XMLElement("ActionType", DECODE(txn.trxntypeid, 2,'AUTH', 3,'AUTH_CAPTURE', 8,'CAPTURE', 9,'CAPTURE', 5,'CREDIT', 10,'CREDIT', 11,'CREDIT', 13, 'VOID', 4,'VOID', 7, 'VOID', 14, 'VOID', 17, 'VOID', 18, 'VOID', 19, 'VOID', txn.trxntypeid ) ), XMLElement("TransactionDate",TO_CHAR(txn.updatedate,'YYYY-MM-DD"T"HH24:MI:SS')), XMLForest(core.tracenumber AS "TraceNumber"), CASE WHEN (core.pos_trxn_flag = 'Y') THEN XMLElement("POSData", XMLElement("ReaderCapability",core.pos_reader_capability_code), XMLElement("EntryMode",core.pos_entry_method_code), XMLElement("CardIdMethod",core.pos_id_method_code), XMLElement("AuthSource",core.pos_auth_source_code), XMLElement("ReaderData",core.reader_data) ) END, XMLForest(core.authcode AS "AuthCode"), XMLForest(DECODE(core.date_of_voice_authorization, null, 'false', 'true') AS "VoiceAuthFlag" ), XMLForest(core.authcode AS "AuthCode"), extend.extend_list ), CASE WHEN (orig_txn.trxnmid!=txn.trxnmid) THEN XMLElement("OriginalCCTransaction", XMLElement("ActionType", DECODE(orig_txn.trxntypeid, 2,'AUTH', 3,'AUTH_CAPTURE', 8,'CAPTURE', 9,'CAPTURE', 5,'CREDIT', 10,'CREDIT', 11,'CREDIT', orig_txn.trxntypeid ) ), XMLElement("TransactionDate",TO_CHAR(orig_txn.updatedate,'YYYY-MM-DD"T"HH24:MI:SS')), XMLForest(orig_core.tracenumber AS "TraceNumber"), CASE WHEN (orig_core.pos_trxn_flag = 'Y') THEN XMLElement("POSData", XMLElement("ReaderCapability",orig_core.pos_reader_capability_code), XMLElement("EntryMode",orig_core.pos_entry_method_code), XMLElement("CardIdMethod",orig_core.pos_id_method_code), XMLElement("AuthSource",orig_core.pos_auth_source_code), XMLElement("ReaderData",orig_core.reader_data) ) END, XMLForest(orig_core.authcode AS "AuthCode"), XMLElement("VoiceAuthFlag",DECODE(orig_core.date_of_voice_authorization, null, 'false', 'true')), XMLElement("Amount", XMLElement("Value",orig_txn.amount), XMLElement("Currency", XMLElement("Code",orig_txn.currencynamecode) ) ), XMLForest( orig_core.avscode AS "AVSCode", orig_core.referencecode AS "ReferenceCode", orig_core.cvv2result AS "SecurityValueCheck" ), XMLForest(orig_txn.bepcode AS "PaymentSystemCode"), orig_extend.extend_list ) END ) END, CASE WHEN (txn.instrtype IN ('BANKACCOUNT')) THEN XMLConcat(XMLElement("PayerBankAccount", xml_bank.bank_account_content), XMLElement("BankAccountTransaction", XMLElement("ActionType",DECODE(txn.reqtype, 'ORAPMTCREDIT','CREDIT', 'DEBIT') ), XMLElement("TransactionDate",TO_CHAR(txn.updatedate,'YYYY-MM-DD"T"HH24:MI:SS')), XMLElement("SettlementDueDate",TO_CHAR(ar.settlement_due_date,'YYYY-MM-DD"T"HH24:MI:SS')), XMLElement("AuthorizationMethod",tang.eft_auth_method), XMLElement("DeliveryMethod",txn.debit_advice_delivery_method), XMLElement("TransferType",DECODE(txn.instrsubtype,'ACH', 'ACH', 'BR')), XMLElement("SettlementCustomerReference", txn.settlement_customer_reference), XMLElement("SettlementFactored",DECODE(txn.factored_flag, 'Y','true', 'false')), CASE WHEN (txn.instrsubtype <> 'ACH') THEN XMLElement("BillReceivableData", XMLElement("MaturityDate", TO_CHAR(txn.br_maturity_date,'YYYY-MM-DD"T"HH24:MI:SS')), XMLElement("BRType", txn.instrsubtype)) END, XMLElement("BankChargeBearer", XMLElement("Code",txn.bank_charge_bearer_code), XMLElement("Meaning",null) ), XMLElement("DebitAuthorization", XMLElement("DebitAuthRefCode",txn.debit_auth_reference), XMLElement("DebitAuthMethod",txn.debit_auth_method), XMLElement("DebitAuthGranted",DECODE(txn.debit_auth_flag, 'Y','true', 'false')) ), XMLElement("DebitNotification", XMLElement("DeliveryMethod",txn.debit_advice_delivery_method), XMLElement("EmailAddress",txn.debit_advice_email), XMLElement("FaxNumber",txn.debit_advice_fax) ), XMLElement("DebitBankInstruction",txn.dirdeb_instruction_code), extend.extend_list ) ) END, CASE WHEN (txn.instrtype = 'DEBITCARD') THEN XMLConcat( xml_dc.debit_card, XMLElement("DebitCardTransaction", XMLElement("ActionType",'DEPOSIT'), XMLElement("TransactionDate",TO_CHAR(txn.updatedate,'YYYY-MM-DD"T"HH24:MI:SS')), extend.extend_list ), CASE WHEN (NOT orig_txn.trxnmid IS NULL) THEN XMLElement("OriginalDCTransaction", XMLElement("ActionType",'DEPOSIT'), XMLElement("TransactionDate",TO_CHAR(orig_txn.updatedate,'YYYY-MM-DD"T"HH24:MI:SS')), XMLForest(orig_core.proc_tracenumber AS "TraceNumber"), XMLForest(orig_core.authcode AS "AuthCode"), XMLForest(orig_txn.bepcode AS "PaymentSystemCode"), XMLElement("DebitNetworkCode",orig_core.debit_network_code), orig_extend.extend_list ) END ) END, XMLElement("PaymentMethod", XMLElement("PaymentMethodInternalID", pch.payment_channel_code), XMLElement("PaymentMethodName", pch.payment_channel_name), XMLElement("PaymentMethodFormatValue", pch.format_value), CASE WHEN NOT (pch.attribute_category IS NULL) THEN XMLElement("DescriptiveFlexField", IBY_EXTRACTGEN_PVT.Get_Dffs( 'IBY_FNDCPT_PMT_CHNNLS_B', NULL, pch.payment_channel_code)) END ), iby_ar_utils.get_document_receivable(txn.initiator_extension_id, txn.trxntypeid, NVL(core.card_data_level,orig_core.card_data_level), txn.instrtype, 'IBY_XML_FNDCPT_ORDER_PN_1_0_V' )), txn.trxnmid FROM iby_trxn_summaries_all txn, fnd_application app, iby_trxn_core core, iby_tangible tang, iby_trxn_summaries_all orig_txn, iby_trxn_core orig_core, iby_creditcard card, iby_xml_fndcpt_creditcard_v xml_cc, iby_xml_fndcpt_creditcard_v orig_xml_cc, ( select decode(iby_ba_sum.instrtype, 'BANKACCOUNT', bank_account_content, null) bank_account_content, trxnmid from iby_xml_fndcpt_bankaccount_v xml_ba_v, iby_trxn_summaries_all iby_ba_sum where iby_ba_sum.payerinstrid=xml_ba_v.instrid) xml_bank, ( select decode(iby_ba_sum.instrtype, 'BANKACCOUNT', int_bank_account, null) bank_account_content, trxnmid from iby_xml_fc_prba_1_0_v xml_ba_v, iby_trxn_summaries_all iby_ba_sum where iby_ba_sum.payeeinstrid=xml_ba_v.bank_account_id) xml_payee_ba, (SELECT tangible_id, settlement_due_date FROM ar_funds_capture_orders_v) ar, iby_xml_fndcpt_debitcard_v xml_dc, iby_xml_trxn_extendlist_v extend, iby_xml_trxn_extendlist_v orig_extend, iby_xml_fndcpt_payer_1_0_v xml_payer, iby_fndcpt_pmt_chnnls_vl pch, iby_xml_fndcpt_payee_1_0_v xml_payee, ce_security_profiles_gt ce_sp WHERE (txn.trxnmid = core.trxnmid(+)) AND (txn.mtangibleid = tang.mtangibleid) AND (txn.ecappid = app.application_id(+)) AND (txn.transactionid = orig_txn.transactionid(+)) AND ( (txn.TRXNTYPEID = 4 AND orig_txn.trxntypeid = 2) OR (txn.TRXNTYPEID IS NULL AND orig_txn.reqtype ='ORAPMTREQ') OR (txn.TRXNTYPEID = 7 AND orig_txn.trxntypeid = 3) OR (txn.TRXNTYPEID IN (13, 14) AND orig_txn.trxntypeid = 8) OR (txn.TRXNTYPEID IN (17, 18) AND orig_txn.trxntypeid = 5) OR (txn.TRXNTYPEID = 19 AND orig_txn.trxntypeid = 11) OR (txn.TRXNTYPEID IN (5, 6, 8, 9, 10, 100) AND orig_txn.reqtype = 'ORAPMTREQ') OR (txn.TRXNTYPEID =11) OR (txn.TRXNTYPEID IN (2, 3, 20) AND orig_txn.reqtype ='ORAPMTREQ') ) AND (orig_txn.status IN ( 0, 9, 11, 111)) AND (txn.payerinstrid = card.instrid(+)) AND (orig_txn.trxnmid = orig_core.trxnmid(+)) AND (txn.trxnmid = xml_cc.trxnmid) AND (txn.trxnmid = xml_dc.trxnmid) AND (txn.trxnmid = xml_bank.trxnmid(+)) AND (orig_txn.trxnmid = orig_xml_cc.trxnmid) AND (txn.trxnmid = extend.trxnmid(+)) AND (orig_txn.trxnmid = orig_extend.trxnmid(+)) AND (txn.trxnmid=xml_payer.trxnmid) AND (txn.trxnmid = xml_payee_ba.trxnmid(+)) AND (txn.payment_channel_code = pch.payment_channel_code(+)) AND (txn.legal_entity_id = xml_payee.legal_entity_id(+)) AND (txn.tangibleid = ar.tangible_id(+)) AND txn.org_id = ce_sp.organization_id AND txn.org_type = ce_sp.organization_type

View Text - HTML Formatted

SELECT XMLELEMENT("FUNDSCAPTUREORDER"
, XMLELEMENT("ORDERSOURCEINFO"
, XMLFOREST(TXN.ECAPPID AS "APPLICATIONINTERNALID"
, APP.APPLICATION_SHORT_NAME AS "APPLICATIONNAME") )
, XMLELEMENT("ORDERNUMBER"
, XMLELEMENT("PAYEEORDERNUMBER"
, TXN.TANGIBLEID) )
, XMLFOREST(TANG.MEMO AS "PAYEEORDERMEMO")
, XMLFOREST(TANG.REFINFO AS "PAYEEORDERREFID")
, XMLFOREST(TANG.ORDER_MEDIUM AS "ORDERMEDIUM")
, CASE WHEN (NOT XML_PAYEE_BA.TRXNMID IS NULL)THEN XMLELEMENT("PAYEEBANKACCOUNT"
, XML_PAYEE_BA.BANK_ACCOUNT_CONTENT) END
, XMLELEMENT("ORDERAMOUNT"
, XMLELEMENT("VALUE"
, TXN.AMOUNT)
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, TXN.CURRENCYNAMECODE) ) )
, XMLELEMENT("PAYEELEGALENTITY"
, XML_PAYEE.XML_PAYEE)
, XMLELEMENT("PAYER"
, XML_PAYER.PAYER)
, CASE WHEN (TXN.INSTRTYPE IN ('CREDITCARD'
, 'PURCHASECARD')) OR (TXN.INSTRTYPE IS NULL) THEN XMLCONCAT( XML_CC.CREDIT_CARD
, ORIG_XML_CC.CREDIT_CARD
, XMLELEMENT("CREDITCARDTRANSACTION"
, XMLELEMENT("ACTIONTYPE"
, DECODE(TXN.TRXNTYPEID
, 2
, 'AUTH'
, 3
, 'AUTH_CAPTURE'
, 8
, 'CAPTURE'
, 9
, 'CAPTURE'
, 5
, 'CREDIT'
, 10
, 'CREDIT'
, 11
, 'CREDIT'
, 13
, 'VOID'
, 4
, 'VOID'
, 7
, 'VOID'
, 14
, 'VOID'
, 17
, 'VOID'
, 18
, 'VOID'
, 19
, 'VOID'
, TXN.TRXNTYPEID ) )
, XMLELEMENT("TRANSACTIONDATE"
, TO_CHAR(TXN.UPDATEDATE
, 'YYYY-MM-DD"T"HH24:MI:SS'))
, XMLFOREST(CORE.TRACENUMBER AS "TRACENUMBER")
, CASE WHEN (CORE.POS_TRXN_FLAG = 'Y') THEN XMLELEMENT("POSDATA"
, XMLELEMENT("READERCAPABILITY"
, CORE.POS_READER_CAPABILITY_CODE)
, XMLELEMENT("ENTRYMODE"
, CORE.POS_ENTRY_METHOD_CODE)
, XMLELEMENT("CARDIDMETHOD"
, CORE.POS_ID_METHOD_CODE)
, XMLELEMENT("AUTHSOURCE"
, CORE.POS_AUTH_SOURCE_CODE)
, XMLELEMENT("READERDATA"
, CORE.READER_DATA) ) END
, XMLFOREST(CORE.AUTHCODE AS "AUTHCODE")
, XMLFOREST(DECODE(CORE.DATE_OF_VOICE_AUTHORIZATION
, NULL
, 'FALSE'
, 'TRUE') AS "VOICEAUTHFLAG" )
, XMLFOREST(CORE.AUTHCODE AS "AUTHCODE")
, EXTEND.EXTEND_LIST )
, CASE WHEN (ORIG_TXN.TRXNMID!=TXN.TRXNMID) THEN XMLELEMENT("ORIGINALCCTRANSACTION"
, XMLELEMENT("ACTIONTYPE"
, DECODE(ORIG_TXN.TRXNTYPEID
, 2
, 'AUTH'
, 3
, 'AUTH_CAPTURE'
, 8
, 'CAPTURE'
, 9
, 'CAPTURE'
, 5
, 'CREDIT'
, 10
, 'CREDIT'
, 11
, 'CREDIT'
, ORIG_TXN.TRXNTYPEID ) )
, XMLELEMENT("TRANSACTIONDATE"
, TO_CHAR(ORIG_TXN.UPDATEDATE
, 'YYYY-MM-DD"T"HH24:MI:SS'))
, XMLFOREST(ORIG_CORE.TRACENUMBER AS "TRACENUMBER")
, CASE WHEN (ORIG_CORE.POS_TRXN_FLAG = 'Y') THEN XMLELEMENT("POSDATA"
, XMLELEMENT("READERCAPABILITY"
, ORIG_CORE.POS_READER_CAPABILITY_CODE)
, XMLELEMENT("ENTRYMODE"
, ORIG_CORE.POS_ENTRY_METHOD_CODE)
, XMLELEMENT("CARDIDMETHOD"
, ORIG_CORE.POS_ID_METHOD_CODE)
, XMLELEMENT("AUTHSOURCE"
, ORIG_CORE.POS_AUTH_SOURCE_CODE)
, XMLELEMENT("READERDATA"
, ORIG_CORE.READER_DATA) ) END
, XMLFOREST(ORIG_CORE.AUTHCODE AS "AUTHCODE")
, XMLELEMENT("VOICEAUTHFLAG"
, DECODE(ORIG_CORE.DATE_OF_VOICE_AUTHORIZATION
, NULL
, 'FALSE'
, 'TRUE'))
, XMLELEMENT("AMOUNT"
, XMLELEMENT("VALUE"
, ORIG_TXN.AMOUNT)
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, ORIG_TXN.CURRENCYNAMECODE) ) )
, XMLFOREST( ORIG_CORE.AVSCODE AS "AVSCODE"
, ORIG_CORE.REFERENCECODE AS "REFERENCECODE"
, ORIG_CORE.CVV2RESULT AS "SECURITYVALUECHECK" )
, XMLFOREST(ORIG_TXN.BEPCODE AS "PAYMENTSYSTEMCODE")
, ORIG_EXTEND.EXTEND_LIST ) END ) END
, CASE WHEN (TXN.INSTRTYPE IN ('BANKACCOUNT')) THEN XMLCONCAT(XMLELEMENT("PAYERBANKACCOUNT"
, XML_BANK.BANK_ACCOUNT_CONTENT)
, XMLELEMENT("BANKACCOUNTTRANSACTION"
, XMLELEMENT("ACTIONTYPE"
, DECODE(TXN.REQTYPE
, 'ORAPMTCREDIT'
, 'CREDIT'
, 'DEBIT') )
, XMLELEMENT("TRANSACTIONDATE"
, TO_CHAR(TXN.UPDATEDATE
, 'YYYY-MM-DD"T"HH24:MI:SS'))
, XMLELEMENT("SETTLEMENTDUEDATE"
, TO_CHAR(AR.SETTLEMENT_DUE_DATE
, 'YYYY-MM-DD"T"HH24:MI:SS'))
, XMLELEMENT("AUTHORIZATIONMETHOD"
, TANG.EFT_AUTH_METHOD)
, XMLELEMENT("DELIVERYMETHOD"
, TXN.DEBIT_ADVICE_DELIVERY_METHOD)
, XMLELEMENT("TRANSFERTYPE"
, DECODE(TXN.INSTRSUBTYPE
, 'ACH'
, 'ACH'
, 'BR'))
, XMLELEMENT("SETTLEMENTCUSTOMERREFERENCE"
, TXN.SETTLEMENT_CUSTOMER_REFERENCE)
, XMLELEMENT("SETTLEMENTFACTORED"
, DECODE(TXN.FACTORED_FLAG
, 'Y'
, 'TRUE'
, 'FALSE'))
, CASE WHEN (TXN.INSTRSUBTYPE <> 'ACH') THEN XMLELEMENT("BILLRECEIVABLEDATA"
, XMLELEMENT("MATURITYDATE"
, TO_CHAR(TXN.BR_MATURITY_DATE
, 'YYYY-MM-DD"T"HH24:MI:SS'))
, XMLELEMENT("BRTYPE"
, TXN.INSTRSUBTYPE)) END
, XMLELEMENT("BANKCHARGEBEARER"
, XMLELEMENT("CODE"
, TXN.BANK_CHARGE_BEARER_CODE)
, XMLELEMENT("MEANING"
, NULL) )
, XMLELEMENT("DEBITAUTHORIZATION"
, XMLELEMENT("DEBITAUTHREFCODE"
, TXN.DEBIT_AUTH_REFERENCE)
, XMLELEMENT("DEBITAUTHMETHOD"
, TXN.DEBIT_AUTH_METHOD)
, XMLELEMENT("DEBITAUTHGRANTED"
, DECODE(TXN.DEBIT_AUTH_FLAG
, 'Y'
, 'TRUE'
, 'FALSE')) )
, XMLELEMENT("DEBITNOTIFICATION"
, XMLELEMENT("DELIVERYMETHOD"
, TXN.DEBIT_ADVICE_DELIVERY_METHOD)
, XMLELEMENT("EMAILADDRESS"
, TXN.DEBIT_ADVICE_EMAIL)
, XMLELEMENT("FAXNUMBER"
, TXN.DEBIT_ADVICE_FAX) )
, XMLELEMENT("DEBITBANKINSTRUCTION"
, TXN.DIRDEB_INSTRUCTION_CODE)
, EXTEND.EXTEND_LIST ) ) END
, CASE WHEN (TXN.INSTRTYPE = 'DEBITCARD') THEN XMLCONCAT( XML_DC.DEBIT_CARD
, XMLELEMENT("DEBITCARDTRANSACTION"
, XMLELEMENT("ACTIONTYPE"
, 'DEPOSIT')
, XMLELEMENT("TRANSACTIONDATE"
, TO_CHAR(TXN.UPDATEDATE
, 'YYYY-MM-DD"T"HH24:MI:SS'))
, EXTEND.EXTEND_LIST )
, CASE WHEN (NOT ORIG_TXN.TRXNMID IS NULL) THEN XMLELEMENT("ORIGINALDCTRANSACTION"
, XMLELEMENT("ACTIONTYPE"
, 'DEPOSIT')
, XMLELEMENT("TRANSACTIONDATE"
, TO_CHAR(ORIG_TXN.UPDATEDATE
, 'YYYY-MM-DD"T"HH24:MI:SS'))
, XMLFOREST(ORIG_CORE.PROC_TRACENUMBER AS "TRACENUMBER")
, XMLFOREST(ORIG_CORE.AUTHCODE AS "AUTHCODE")
, XMLFOREST(ORIG_TXN.BEPCODE AS "PAYMENTSYSTEMCODE")
, XMLELEMENT("DEBITNETWORKCODE"
, ORIG_CORE.DEBIT_NETWORK_CODE)
, ORIG_EXTEND.EXTEND_LIST ) END ) END
, XMLELEMENT("PAYMENTMETHOD"
, XMLELEMENT("PAYMENTMETHODINTERNALID"
, PCH.PAYMENT_CHANNEL_CODE)
, XMLELEMENT("PAYMENTMETHODNAME"
, PCH.PAYMENT_CHANNEL_NAME)
, XMLELEMENT("PAYMENTMETHODFORMATVALUE"
, PCH.FORMAT_VALUE)
, CASE WHEN NOT (PCH.ATTRIBUTE_CATEGORY IS NULL) THEN XMLELEMENT("DESCRIPTIVEFLEXFIELD"
, IBY_EXTRACTGEN_PVT.GET_DFFS( 'IBY_FNDCPT_PMT_CHNNLS_B'
, NULL
, PCH.PAYMENT_CHANNEL_CODE)) END )
, IBY_AR_UTILS.GET_DOCUMENT_RECEIVABLE(TXN.INITIATOR_EXTENSION_ID
, TXN.TRXNTYPEID
, NVL(CORE.CARD_DATA_LEVEL
, ORIG_CORE.CARD_DATA_LEVEL)
, TXN.INSTRTYPE
, 'IBY_XML_FNDCPT_ORDER_PN_1_0_V' ))
, TXN.TRXNMID
FROM IBY_TRXN_SUMMARIES_ALL TXN
, FND_APPLICATION APP
, IBY_TRXN_CORE CORE
, IBY_TANGIBLE TANG
, IBY_TRXN_SUMMARIES_ALL ORIG_TXN
, IBY_TRXN_CORE ORIG_CORE
, IBY_CREDITCARD CARD
, IBY_XML_FNDCPT_CREDITCARD_V XML_CC
, IBY_XML_FNDCPT_CREDITCARD_V ORIG_XML_CC
, ( SELECT DECODE(IBY_BA_SUM.INSTRTYPE
, 'BANKACCOUNT'
, BANK_ACCOUNT_CONTENT
, NULL) BANK_ACCOUNT_CONTENT
, TRXNMID
FROM IBY_XML_FNDCPT_BANKACCOUNT_V XML_BA_V
, IBY_TRXN_SUMMARIES_ALL IBY_BA_SUM
WHERE IBY_BA_SUM.PAYERINSTRID=XML_BA_V.INSTRID) XML_BANK
, ( SELECT DECODE(IBY_BA_SUM.INSTRTYPE
, 'BANKACCOUNT'
, INT_BANK_ACCOUNT
, NULL) BANK_ACCOUNT_CONTENT
, TRXNMID
FROM IBY_XML_FC_PRBA_1_0_V XML_BA_V
, IBY_TRXN_SUMMARIES_ALL IBY_BA_SUM
WHERE IBY_BA_SUM.PAYEEINSTRID=XML_BA_V.BANK_ACCOUNT_ID) XML_PAYEE_BA
, (SELECT TANGIBLE_ID
, SETTLEMENT_DUE_DATE
FROM AR_FUNDS_CAPTURE_ORDERS_V) AR
, IBY_XML_FNDCPT_DEBITCARD_V XML_DC
, IBY_XML_TRXN_EXTENDLIST_V EXTEND
, IBY_XML_TRXN_EXTENDLIST_V ORIG_EXTEND
, IBY_XML_FNDCPT_PAYER_1_0_V XML_PAYER
, IBY_FNDCPT_PMT_CHNNLS_VL PCH
, IBY_XML_FNDCPT_PAYEE_1_0_V XML_PAYEE
, CE_SECURITY_PROFILES_GT CE_SP
WHERE (TXN.TRXNMID = CORE.TRXNMID(+))
AND (TXN.MTANGIBLEID = TANG.MTANGIBLEID)
AND (TXN.ECAPPID = APP.APPLICATION_ID(+))
AND (TXN.TRANSACTIONID = ORIG_TXN.TRANSACTIONID(+))
AND ( (TXN.TRXNTYPEID = 4
AND ORIG_TXN.TRXNTYPEID = 2) OR (TXN.TRXNTYPEID IS NULL
AND ORIG_TXN.REQTYPE ='ORAPMTREQ') OR (TXN.TRXNTYPEID = 7
AND ORIG_TXN.TRXNTYPEID = 3) OR (TXN.TRXNTYPEID IN (13
, 14)
AND ORIG_TXN.TRXNTYPEID = 8) OR (TXN.TRXNTYPEID IN (17
, 18)
AND ORIG_TXN.TRXNTYPEID = 5) OR (TXN.TRXNTYPEID = 19
AND ORIG_TXN.TRXNTYPEID = 11) OR (TXN.TRXNTYPEID IN (5
, 6
, 8
, 9
, 10
, 100)
AND ORIG_TXN.REQTYPE = 'ORAPMTREQ') OR (TXN.TRXNTYPEID =11) OR (TXN.TRXNTYPEID IN (2
, 3
, 20)
AND ORIG_TXN.REQTYPE ='ORAPMTREQ') )
AND (ORIG_TXN.STATUS IN ( 0
, 9
, 11
, 111))
AND (TXN.PAYERINSTRID = CARD.INSTRID(+))
AND (ORIG_TXN.TRXNMID = ORIG_CORE.TRXNMID(+))
AND (TXN.TRXNMID = XML_CC.TRXNMID)
AND (TXN.TRXNMID = XML_DC.TRXNMID)
AND (TXN.TRXNMID = XML_BANK.TRXNMID(+))
AND (ORIG_TXN.TRXNMID = ORIG_XML_CC.TRXNMID)
AND (TXN.TRXNMID = EXTEND.TRXNMID(+))
AND (ORIG_TXN.TRXNMID = ORIG_EXTEND.TRXNMID(+))
AND (TXN.TRXNMID=XML_PAYER.TRXNMID)
AND (TXN.TRXNMID = XML_PAYEE_BA.TRXNMID(+))
AND (TXN.PAYMENT_CHANNEL_CODE = PCH.PAYMENT_CHANNEL_CODE(+))
AND (TXN.LEGAL_ENTITY_ID = XML_PAYEE.LEGAL_ENTITY_ID(+))
AND (TXN.TANGIBLEID = AR.TANGIBLE_ID(+))
AND TXN.ORG_ID = CE_SP.ORGANIZATION_ID
AND TXN.ORG_TYPE = CE_SP.ORGANIZATION_TYPE