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_1_0_V

Source

View Text - Preformatted


SELECT /*+ use_nl(tang) push_pred(xml_cc) push_pred(orig_xml_cc) push_pred(xml_dc) push_pred(xml_ba_v) push_pred(xml_fc_v) push_pred(xml_payer) */ xmlelement("FundsCaptureOrder", xmlelement("OrderSourceInfo", xmlforest(txn.ecappid AS "ApplicationInternalID", app.application_short_name AS "ApplicationName")), XMLElement("OrderNumber", XMLForest(txn.tangibleid AS "PayeeOrderNumber", txn.logical_group_reference AS "LogicalGroupReference") ), xmlforest(tang.memo AS "PayeeOrderMemo"), xmlforest(tang.refinfo AS "PayeeOrderRefID"), xmlforest(tang.order_medium AS "OrderMedium"), CASE WHEN(NOT txn.initiator_extension_id IS NULL) THEN xmlelement("TrxnRefNumber2", iby_ar_utils.get_trxn_ref_number2(txn.initiator_extension_id)) END, CASE WHEN(NOT txn.trxnmid IS NULL) THEN xmlelement("PayeeBankAccount", CASE WHEN(txn.instrtype = 'BANKACCOUNT') THEN (SELECT xml_fc_v.int_bank_account FROM iby_xml_fc_prba_1_0_v xml_fc_v WHERE(txn.payeeinstrid = xml_fc_v.bank_account_id(+))) ELSE NULL END) 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', 22, 'FULL_REVERSAL', 23, 'PARTIAL_REVERSAL', txn.trxntypeid)), xmlelement("TransactionDate", to_char(txn.updatedate, 'YYYY-MM-DD"T"HH24:MI:SS')), xmlforest(core.tracenumber AS "TraceNumber"), xmlforest(core.authcode AS "AuthCode"), xmlforest(decode(core.date_of_voice_authorization, NULL, 'false', 'true') AS "VoiceAuthFlag"), xmlforest(core.authcode AS "AuthCode"), (SELECT xmlagg(xmlelement("Extend", xmlelement("Name", extend_name), xmlelement("Value", extend_value))) FROM iby_trxn_extensibility x WHERE x.trxnmid = txn.trxnmid)), 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', 22, 'FULL_REVERSAL', 23, 'PARTIAL_REVERSAL', orig_txn.trxntypeid)), xmlelement("TransactionDate", to_char(orig_txn.updatedate, 'YYYY-MM-DD"T"HH24:MI:SS')), xmlforest(orig_core.tracenumber AS "TraceNumber"), 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"), (SELECT xmlagg(xmlelement("Extend", xmlelement("Name", extend_name), xmlelement("Value", extend_value))) FROM iby_trxn_extensibility x WHERE x.trxnmid = orig_txn.trxnmid)) END) END, CASE WHEN(txn.instrtype IN('BANKACCOUNT')) THEN xmlconcat(xmlelement("PayerBankAccount", decode(txn.instrtype, 'BANKACCOUNT', (SELECT bank_account_content FROM iby_xml_fndcpt_bankaccount_v xml_ba_v WHERE txn.payerinstrid = xml_ba_v.instrid AND txn.payment_channel_code <> 'CREDIT_CARD'), NULL)), 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(txn.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')), XMLElement("SeqType",txn.seq_type), CASE WHEN (NVL(txn.instrsubtype,0) <> '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), (SELECT xmlagg(xmlelement("Extend", xmlelement("Name", extend_name), xmlelement("Value", extend_value))) FROM iby_trxn_extensibility x WHERE x.trxnmid = txn.trxnmid)) ) END, CASE WHEN(txn.instrtype = 'DEBITCARD') THEN xmlconcat( (SELECT debit_card FROM iby_xml_fndcpt_debitcard_v xml_dc WHERE(txn.trxnmid = xml_dc.trxnmid)), xmlelement("DebitCardTransaction", xmlelement("ActionType", 'DEPOSIT'), xmlelement("TransactionDate", to_char(txn.updatedate, 'YYYY-MM-DD"T"HH24:MI:SS')), (SELECT xmlagg(xmlelement("Extend", xmlelement("Name", extend_name), xmlelement("Value", extend_value))) FROM iby_trxn_extensibility x WHERE x.trxnmid = txn.trxnmid)), 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), (SELECT xmlagg(xmlelement("Extend", xmlelement("Name", extend_name), xmlelement("Value", extend_value))) FROM iby_trxn_extensibility x WHERE x.trxnmid = orig_txn.trxnmid)) 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, txn.process_profile_code, 'IBY_XML_FNDCPT_ORDER_1_0_V'), iby_fndcpt_extract_gen_pvt.get_mandate_details(txn.debit_authorization_id)), txn.trxnmid, txn.mbatchid, txn.bepkey 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_xml_fndcpt_creditcard_v xml_cc, iby_xml_fndcpt_creditcard_v orig_xml_cc, iby_xml_fndcpt_payer_1_0_v xml_payer, iby_fndcpt_pmt_chnnls_vl pch, iby_xml_fndcpt_payee_1_0_v xml_payee 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, 22, 23) 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, 31)) AND(orig_txn.trxnmid = orig_core.trxnmid(+)) AND(txn.trxnmid = xml_payer.trxnmid) AND(txn.trxnmid = xml_cc.trxnmid) AND(orig_txn.trxnmid = orig_xml_cc.trxnmid) AND(txn.payment_channel_code = pch.payment_channel_code(+)) AND(txn.legal_entity_id = xml_payee.legal_entity_id(+))

View Text - HTML Formatted

SELECT /*+ USE_NL(TANG) PUSH_PRED(XML_CC) PUSH_PRED(ORIG_XML_CC) PUSH_PRED(XML_DC) PUSH_PRED(XML_BA_V) PUSH_PRED(XML_FC_V) PUSH_PRED(XML_PAYER) */ XMLELEMENT("FUNDSCAPTUREORDER"
, XMLELEMENT("ORDERSOURCEINFO"
, XMLFOREST(TXN.ECAPPID AS "APPLICATIONINTERNALID"
, APP.APPLICATION_SHORT_NAME AS "APPLICATIONNAME"))
, XMLELEMENT("ORDERNUMBER"
, XMLFOREST(TXN.TANGIBLEID AS "PAYEEORDERNUMBER"
, TXN.LOGICAL_GROUP_REFERENCE AS "LOGICALGROUPREFERENCE") )
, XMLFOREST(TANG.MEMO AS "PAYEEORDERMEMO")
, XMLFOREST(TANG.REFINFO AS "PAYEEORDERREFID")
, XMLFOREST(TANG.ORDER_MEDIUM AS "ORDERMEDIUM")
, CASE WHEN(NOT TXN.INITIATOR_EXTENSION_ID IS NULL) THEN XMLELEMENT("TRXNREFNUMBER2"
, IBY_AR_UTILS.GET_TRXN_REF_NUMBER2(TXN.INITIATOR_EXTENSION_ID)) END
, CASE WHEN(NOT TXN.TRXNMID IS NULL) THEN XMLELEMENT("PAYEEBANKACCOUNT"
, CASE WHEN(TXN.INSTRTYPE = 'BANKACCOUNT') THEN (SELECT XML_FC_V.INT_BANK_ACCOUNT
FROM IBY_XML_FC_PRBA_1_0_V XML_FC_V WHERE(TXN.PAYEEINSTRID = XML_FC_V.BANK_ACCOUNT_ID(+))) ELSE NULL END) 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'
, 22
, 'FULL_REVERSAL'
, 23
, 'PARTIAL_REVERSAL'
, TXN.TRXNTYPEID))
, XMLELEMENT("TRANSACTIONDATE"
, TO_CHAR(TXN.UPDATEDATE
, 'YYYY-MM-DD"T"HH24:MI:SS'))
, XMLFOREST(CORE.TRACENUMBER AS "TRACENUMBER")
, XMLFOREST(CORE.AUTHCODE AS "AUTHCODE")
, XMLFOREST(DECODE(CORE.DATE_OF_VOICE_AUTHORIZATION
, NULL
, 'FALSE'
, 'TRUE') AS "VOICEAUTHFLAG")
, XMLFOREST(CORE.AUTHCODE AS "AUTHCODE")
, (SELECT XMLAGG(XMLELEMENT("EXTEND"
, XMLELEMENT("NAME"
, EXTEND_NAME)
, XMLELEMENT("VALUE"
, EXTEND_VALUE)))
FROM IBY_TRXN_EXTENSIBILITY X
WHERE X.TRXNMID = TXN.TRXNMID))
, 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'
, 22
, 'FULL_REVERSAL'
, 23
, 'PARTIAL_REVERSAL'
, ORIG_TXN.TRXNTYPEID))
, XMLELEMENT("TRANSACTIONDATE"
, TO_CHAR(ORIG_TXN.UPDATEDATE
, 'YYYY-MM-DD"T"HH24:MI:SS'))
, XMLFOREST(ORIG_CORE.TRACENUMBER AS "TRACENUMBER")
, 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")
, (SELECT XMLAGG(XMLELEMENT("EXTEND"
, XMLELEMENT("NAME"
, EXTEND_NAME)
, XMLELEMENT("VALUE"
, EXTEND_VALUE)))
FROM IBY_TRXN_EXTENSIBILITY X
WHERE X.TRXNMID = ORIG_TXN.TRXNMID)) END) END
, CASE WHEN(TXN.INSTRTYPE IN('BANKACCOUNT')) THEN XMLCONCAT(XMLELEMENT("PAYERBANKACCOUNT"
, DECODE(TXN.INSTRTYPE
, 'BANKACCOUNT'
, (SELECT BANK_ACCOUNT_CONTENT
FROM IBY_XML_FNDCPT_BANKACCOUNT_V XML_BA_V
WHERE TXN.PAYERINSTRID = XML_BA_V.INSTRID
AND TXN.PAYMENT_CHANNEL_CODE <> 'CREDIT_CARD')
, NULL))
, 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(TXN.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'))
, XMLELEMENT("SEQTYPE"
, TXN.SEQ_TYPE)
, CASE WHEN (NVL(TXN.INSTRSUBTYPE
, 0) <> '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)
, (SELECT XMLAGG(XMLELEMENT("EXTEND"
, XMLELEMENT("NAME"
, EXTEND_NAME)
, XMLELEMENT("VALUE"
, EXTEND_VALUE)))
FROM IBY_TRXN_EXTENSIBILITY X
WHERE X.TRXNMID = TXN.TRXNMID)) ) END
, CASE WHEN(TXN.INSTRTYPE = 'DEBITCARD') THEN XMLCONCAT( (SELECT DEBIT_CARD
FROM IBY_XML_FNDCPT_DEBITCARD_V XML_DC WHERE(TXN.TRXNMID = XML_DC.TRXNMID))
, XMLELEMENT("DEBITCARDTRANSACTION"
, XMLELEMENT("ACTIONTYPE"
, 'DEPOSIT')
, XMLELEMENT("TRANSACTIONDATE"
, TO_CHAR(TXN.UPDATEDATE
, 'YYYY-MM-DD"T"HH24:MI:SS'))
, (SELECT XMLAGG(XMLELEMENT("EXTEND"
, XMLELEMENT("NAME"
, EXTEND_NAME)
, XMLELEMENT("VALUE"
, EXTEND_VALUE)))
FROM IBY_TRXN_EXTENSIBILITY X
WHERE X.TRXNMID = TXN.TRXNMID))
, 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)
, (SELECT XMLAGG(XMLELEMENT("EXTEND"
, XMLELEMENT("NAME"
, EXTEND_NAME)
, XMLELEMENT("VALUE"
, EXTEND_VALUE)))
FROM IBY_TRXN_EXTENSIBILITY X
WHERE X.TRXNMID = ORIG_TXN.TRXNMID)) 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
, TXN.PROCESS_PROFILE_CODE
, 'IBY_XML_FNDCPT_ORDER_1_0_V')
, IBY_FNDCPT_EXTRACT_GEN_PVT.GET_MANDATE_DETAILS(TXN.DEBIT_AUTHORIZATION_ID))
, TXN.TRXNMID
, TXN.MBATCHID
, TXN.BEPKEY
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_XML_FNDCPT_CREDITCARD_V XML_CC
, IBY_XML_FNDCPT_CREDITCARD_V ORIG_XML_CC
, IBY_XML_FNDCPT_PAYER_1_0_V XML_PAYER
, IBY_FNDCPT_PMT_CHNNLS_VL PCH
, IBY_XML_FNDCPT_PAYEE_1_0_V XML_PAYEE
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
, 22
, 23)
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
, 31)) AND(ORIG_TXN.TRXNMID = ORIG_CORE.TRXNMID(+)) AND(TXN.TRXNMID = XML_PAYER.TRXNMID) AND(TXN.TRXNMID = XML_CC.TRXNMID) AND(ORIG_TXN.TRXNMID = ORIG_XML_CC.TRXNMID) AND(TXN.PAYMENT_CHANNEL_CODE = PCH.PAYMENT_CHANNEL_CODE(+)) AND(TXN.LEGAL_ENTITY_ID = XML_PAYEE.LEGAL_ENTITY_ID(+))