[Home] [Help]
View: IBY_XML_FNDCPT_ORDER_1_0_V
View Text
SELECT XMLELEMENT("FUNDSCAPTUREORDER"
, XMLELEMENT("ORDERSOURCEINFO"
, XMLFOREST(TXN.ECAPPID AS "APPLICATIONINTERNALID"
, APP.APPLICATION_SHORT_NAME AS "APPLICATIONNAME") )
, XMLELEMENT("PAYMENTMETHOD"
, TXN.PAYMENT_CHANNEL_CODE)
, XMLELEMENT("ORDERNUMBER"
, XMLELEMENT("PAYEEORDERNUMBER"
, TXN.TANGIBLEID) )
, XMLFOREST(TANG.MEMO AS "PAYEEORDERMEMO")
, XMLFOREST(TANG.REFINFO AS "PAYEEORDERREFID")
, XMLFOREST(TANG.ORDER_MEDIUM AS "ORDERMEDIUM")
, XMLELEMENT("ORDERAMOUNT"
, XMLELEMENT("VALUE"
, TXN.AMOUNT)
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, TXN.CURRENCYNAMECODE) ) )
, 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("AUTHORIZATIONMETHOD"
, TANG.EFT_AUTH_METHOD)
, XMLELEMENT("DELIVERYMETHOD"
, NULL)
, XMLELEMENT("SETTLEMENTCUSTOMERREFERENCE"
, TXN.SETTLEMENT_CUSTOMER_REFERENCE)
, XMLELEMENT("SETTLEMENTFACTORED"
, TXN.FACTORED_FLAG)
, 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("TRANSFERTYPE"
, DECODE(TXN.INSTRSUBTYPE
, 'ACH'
, 'ACH'
, 'BR'))
, EXTEND.EXTEND_LIST ) ) END
, CASE WHEN (TXN.INSTRTYPE = 'PINLESSDEBITCARD') 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
, IBY_AR_UTILS.GET_DOCUMENT_RECEIVABLE(TXN.TANGIBLEID
, TXN.TRXNTYPEID
, NVL(CORE.CARD_DATA_LEVEL
, ORIG_CORE.CARD_DATA_LEVEL)
, TXN.INSTRTYPE ))
, 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_INSTRHOLDER HOLDER
, IBY_XML_FNDCPT_CREDITCARD_V XML_CC
, IBY_XML_FNDCPT_CREDITCARD_V ORIG_XML_CC
, IBY_XML_FNDCPT_BANKACCOUNT_V XML_BANK
, 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
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)
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 (CARD.INSTRID = HOLDER.INSTRID(+))
AND (ORIG_TXN.TRXNMID = ORIG_CORE.TRXNMID(+))
AND (TXN.TRXNMID = XML_CC.TRXNMID)
AND (TXN.TRXNMID = XML_DC.TRXNMID)
AND (TXN.PAYERINSTRID = XML_BANK.INSTRID(+))
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
Columns
| Name |
| FNDCPT_ORDER |
| TRXNMID |
APPS.IBY_XML_FNDCPT_ORDER_1_0_V