VIEW: APPS.IBY_XML_FNDCPT_ACCT_1_0_V

Source

View Text - Preformatted

SELECT XMLElement("PayeeAccount", XMLElement("PaymentSystemAccount", XMLElement("AccountName",totals.bepkey), ( SELECT XMLAgg( XMLElement("AccountOption", XMLElement("Name", account_option_code), XMLElement("Value", CASE WHEN(vals.val_sec_segment_id IS NULL) THEN vals.account_option_value ELSE iby_security_pkg.decrypt_field_vals(vals.val_sec_segment_id, iby_utility_pvt.get_view_param('SYS_KEY')) END))) FROM iby_bep_acct_opt_vals vals WHERE vals.bep_account_id = keys.bep_account_id ) ), XMLElement("Payee", XMLElement("Name",payee.name), XMLForest(DECODE(payee.mcc_code, -1,null, payee.mcc_code) AS "MCC") ), XMLElement("OrderCount", totals.order_count), XMLElement("AccountTotals", XMLElement("AuthorizationsTotal", XMLElement("Value", totals.auth_total), XMLElement("Currency", XMLElement("Code", totals.currency) ) ), XMLElement("CapturesTotal", XMLElement("Value", totals.capt_total), XMLElement("Currency", XMLElement("Code", totals.currency) ) ), XMLElement("CreditsTotal", XMLElement("Value", totals.credit_total), XMLElement("Currency", XMLElement("Code",totals.currency) ) ) ), ( SELECT XMLAgg(xml_order.fndcpt_order) FROM iby_xml_fndcpt_order_1_0_v xml_order WHERE xml_order.mbatchid= batch.mbatchid AND xml_order.bepkey=totals.bepkey ) ), batch.mbatchid, totals.bepkey FROM iby_batches_all batch, iby_bepkeys keys, iby_payee payee, ( SELECT mbatchid, bepkey, COUNT(trxnmid) order_count, MAX(currencynamecode) currency, SUM( DECODE( DECODE( instrtype, 'PURCHASECARD','CREDITCARD', instrtype ), 'PINLESSDEBITCARD',0, 'BANKACCOUNT',0, DECODE( trxntypeid, 2,amount, 0) )) auth_total, SUM( DECODE( DECODE( instrtype, 'PURCHASECARD','CREDITCARD', instrtype ), 'PINLESSDEBITCARD', DECODE(trxntypeid, 2,amount, 0), 'BANKACCOUNT', DECODE(reqtype, 'ORAPMTBATCHREQ',amount, 0), DECODE(trxntypeid, 3,amount, 8,amount, 0) )) capt_total, SUM( DECODE( DECODE( instrtype, 'PURCHASECARD','CREDITCARD', instrtype ), 'PINLESSDEBITCARD', 0, 'BANKACCOUNT', DECODE(reqtype, 'ORAPMTCREDIT',amount, 0), DECODE(trxntypeid, 5,amount,11,amount, 0) )) credit_total FROM iby_trxn_summaries_all group by mbatchid, bepkey ) totals WHERE (batch.mpayeeid = payee.mpayeeid) AND (payee.payeeid = keys.ownerid) AND (keys.ownertype = 'PAYEE') AND (batch.mbatchid = totals.mbatchid) AND totals.bepkey = keys.key

View Text - HTML Formatted

SELECT XMLELEMENT("PAYEEACCOUNT"
, XMLELEMENT("PAYMENTSYSTEMACCOUNT"
, XMLELEMENT("ACCOUNTNAME"
, TOTALS.BEPKEY)
, ( SELECT XMLAGG( XMLELEMENT("ACCOUNTOPTION"
, XMLELEMENT("NAME"
, ACCOUNT_OPTION_CODE)
, XMLELEMENT("VALUE"
, CASE WHEN(VALS.VAL_SEC_SEGMENT_ID IS NULL) THEN VALS.ACCOUNT_OPTION_VALUE ELSE IBY_SECURITY_PKG.DECRYPT_FIELD_VALS(VALS.VAL_SEC_SEGMENT_ID
, IBY_UTILITY_PVT.GET_VIEW_PARAM('SYS_KEY')) END)))
FROM IBY_BEP_ACCT_OPT_VALS VALS
WHERE VALS.BEP_ACCOUNT_ID = KEYS.BEP_ACCOUNT_ID ) )
, XMLELEMENT("PAYEE"
, XMLELEMENT("NAME"
, PAYEE.NAME)
, XMLFOREST(DECODE(PAYEE.MCC_CODE
, -1
, NULL
, PAYEE.MCC_CODE) AS "MCC") )
, XMLELEMENT("ORDERCOUNT"
, TOTALS.ORDER_COUNT)
, XMLELEMENT("ACCOUNTTOTALS"
, XMLELEMENT("AUTHORIZATIONSTOTAL"
, XMLELEMENT("VALUE"
, TOTALS.AUTH_TOTAL)
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, TOTALS.CURRENCY) ) )
, XMLELEMENT("CAPTURESTOTAL"
, XMLELEMENT("VALUE"
, TOTALS.CAPT_TOTAL)
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, TOTALS.CURRENCY) ) )
, XMLELEMENT("CREDITSTOTAL"
, XMLELEMENT("VALUE"
, TOTALS.CREDIT_TOTAL)
, XMLELEMENT("CURRENCY"
, XMLELEMENT("CODE"
, TOTALS.CURRENCY) ) ) )
, ( SELECT XMLAGG(XML_ORDER.FNDCPT_ORDER)
FROM IBY_XML_FNDCPT_ORDER_1_0_V XML_ORDER
WHERE XML_ORDER.MBATCHID= BATCH.MBATCHID
AND XML_ORDER.BEPKEY=TOTALS.BEPKEY ) )
, BATCH.MBATCHID
, TOTALS.BEPKEY
FROM IBY_BATCHES_ALL BATCH
, IBY_BEPKEYS KEYS
, IBY_PAYEE PAYEE
, ( SELECT MBATCHID
, BEPKEY
, COUNT(TRXNMID) ORDER_COUNT
, MAX(CURRENCYNAMECODE) CURRENCY
, SUM( DECODE( DECODE( INSTRTYPE
, 'PURCHASECARD'
, 'CREDITCARD'
, INSTRTYPE )
, 'PINLESSDEBITCARD'
, 0
, 'BANKACCOUNT'
, 0
, DECODE( TRXNTYPEID
, 2
, AMOUNT
, 0) )) AUTH_TOTAL
, SUM( DECODE( DECODE( INSTRTYPE
, 'PURCHASECARD'
, 'CREDITCARD'
, INSTRTYPE )
, 'PINLESSDEBITCARD'
, DECODE(TRXNTYPEID
, 2
, AMOUNT
, 0)
, 'BANKACCOUNT'
, DECODE(REQTYPE
, 'ORAPMTBATCHREQ'
, AMOUNT
, 0)
, DECODE(TRXNTYPEID
, 3
, AMOUNT
, 8
, AMOUNT
, 0) )) CAPT_TOTAL
, SUM( DECODE( DECODE( INSTRTYPE
, 'PURCHASECARD'
, 'CREDITCARD'
, INSTRTYPE )
, 'PINLESSDEBITCARD'
, 0
, 'BANKACCOUNT'
, DECODE(REQTYPE
, 'ORAPMTCREDIT'
, AMOUNT
, 0)
, DECODE(TRXNTYPEID
, 5
, AMOUNT
, 11
, AMOUNT
, 0) )) CREDIT_TOTAL
FROM IBY_TRXN_SUMMARIES_ALL GROUP BY MBATCHID
, BEPKEY ) TOTALS
WHERE (BATCH.MPAYEEID = PAYEE.MPAYEEID)
AND (PAYEE.PAYEEID = KEYS.OWNERID)
AND (KEYS.OWNERTYPE = 'PAYEE')
AND (BATCH.MBATCHID = TOTALS.MBATCHID)
AND TOTALS.BEPKEY = KEYS.KEY