1. Tables and Views for Financials
  2. IBY_XML_FNDCPT_ACCT_1_0_V

IBY_XML_FNDCPT_ACCT_1_0_V

Details

  • Schema: FUSION

  • Object owner: IBY

  • Object type: VIEW

Columns

Name

PAYEE_ACCOUNT

MBATCHID

BEP_ACCOUNT_ID

Query

SQL_Statement

SELECT XMLElement("PayeeAccount", XMLElement("PaymentSystemAccount", XMLElement( "AccountName",keys.key), XMLElement("BatchBookingFlag", nvl(seft.batch_booking_flag, 'N')),

(SELECT account_options

FROM IBY_XML_BEP_ACCT_OPTIONS_V

WHERE 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" ,txn_totals.txn_count), XMLElement("AccountTotals", XMLElement( "AuthorizationsTotal", XMLElement("Value", TXN_TOTALS.auth_total ), XMLElement("Currency", XMLElement("Code",TXN_TOTALS.curr) ) ), XMLElement( "CapturesTotal", XMLElement("Value", TXN_TOTALS.settle_total), XMLElement( "Currency", XMLElement("Code",TXN_TOTALS.curr) ) ), XMLElement("CreditsTotal" , XMLElement("Value", TXN_TOTALS.credit_total ), XMLElement("Currency", XMLElement("Code",TXN_TOTALS.curr) ) ) ),

(SELECT XMLAgg(fndcpt_order)

FROM iby_xml_fc_batch_order_1_0_v xml_order

WHERE xml_order.mbatchid =TXN_TOTALS.mbatchid

AND xml_order.process_profile_code=TXN_TOTALS.process_profile_code

) ) PAYEE_ACCOUNT,

TXN_TOTALS.mbatchid MBATCHID,

keys.bep_account_id BEP_ACCOUNT_ID

FROM IBY_BEPKEYS KEYS,

IBY_PAYEE PAYEE,

(SELECT t.payeeid,

t.bepid,

t.process_profile_code,

t.bepkey,

t.mbatchid,

COUNT(1) txn_count,

SUM(DECODE(t.INSTRTYPE, 'DEBITCARD', 0, 'BANKACCOUNT', 0, DECODE( t.trxntypeid, 2,t.amount, 0))) auth_total,

SUM(DECODE(t.INSTRTYPE, 'DEBITCARD', DECODE(t.trxntypeid, 2,t.amount, 0), 'BANKACCOUNT', DECODE(t.REQTYPE, 'ORAPMTBATCHREQ',t.amount, 0), DECODE( t.trxntypeid, 3,t.amount, 8,t.amount, 0))) settle_total,

SUM(DECODE(t.INSTRTYPE, 'DEBITCARD', 0, 'BANKACCOUNT', DECODE(t.REQTYPE, 'ORAPMTCREDIT',t.amount, 0), DECODE(t.trxntypeid, 5,t.amount, 11,t.amount , 0))) credit_total,

MAX(t.currencynamecode) curr,

MAX(t.instrtype) instrtype

FROM iby_trxn_summaries_all t

GROUP BY t.payeeid,

t.bepid,

t.process_profile_code,

t.bepkey,

t.mbatchid

) TXN_TOTALS,

iby_fndcpt_user_eft_pf_b ueft,

iby_fndcpt_sys_eft_pf_b seft

WHERE keys.ownerid = payee.payeeid

AND keys.ownertype = 'PAYEE'

AND keys.ownerid = TXN_TOTALS.payeeid

AND keys.key = TXN_TOTALS.bepkey

AND keys.bepid = TXN_TOTALS.bepid

AND DECODE(txn_totals.instrtype, 'BANKACCOUNT',TXN_TOTALS.process_profile_code, null) = ueft.user_eft_profile_code(+)

AND ueft.sys_eft_profile_code = seft.sys_eft_profile_code(+)