select
d.company_code COMPANY,
upper(c.full_name) COMPANY_NAME,
nvl(d.company_account,'NOT ALLOCATED TO A/C') ACCT_NOS,
d.currency_code CCY,
decode(nvl(d.company_account,'NOT ALLOCATED TO A/C')
,'NOT ALLOCATED TO A/C',' '
,'Opening Balance --->') OPEN_BAL2,
decode(nvl(upper(:DAY_WEEK_MONTH2),'WEEK')
,'DAY',d.trx_date
,'WEEK',trunc(d.trx_date,'WW') + 7
,last_day(d.trx_date)) ADATE,
'B' ORD_COLUMN,
round(sum(decode(t.fx_mm_exp_flag
,'FX',d.amount
,0)),0)/:amt_unit2 FX_IN,
round(sum(decode(t.fx_mm_exp_flag
,'MM',d.amount
,0)),0)/:amt_unit2 MM_IN,
round(sum(decode(t.fx_mm_exp_flag
,'EX',d.amount
,0)),0)/:amt_unit2 EXP,
round(sum(d.amount),0)/:amt_unit2 NET_FLOW,
' ' INDIC,
count(d.transaction_number) NUM_TRANS,
XTR_XTRCFACC_XMLP_PKG.open_bal1formula(nvl(d.company_account,'NOT ALLOCATED TO A/C'),d.currency_code,d.company_code) OPEN_BAL1,
XTR_XTRCFACC_XMLP_PKG.OPEN_BAL_p OPEN_BAL,
XTR_XTRCFACC_XMLP_PKG.rolling_bal1formula(:ACCOUNT_BAL) ROLLING_BAL1,
XTR_XTRCFACC_XMLP_PKG.ROLLING_BAL_p ROLLING_BAL
from XTR_CASHFLOWS_V d,
XTR_DEAL_TYPES_V t,
XTR_PARTIES_V c
where d.dda_deal_type = t.deal_type
and d.company_code = c.party_code
and d.amount != 0
and d.dda_deal_type != 'CA'
and nvl(d.multiple_settlements,'N') = 'N'
and d.company_code like nvl(upper(:COMPANY_CODE2),d.company_code)
and d.currency_code like nvl(upper(:CURRENCY2),d.currency_code)
and d.trx_date between :SETTLE_FROM_DATE2 and :SETTLE_TO_DATE2
and nvl(d.company_account,'%') like nvl(upper(:ACCOUNT_NUM2),'%')
and ((d.dda_deal_type != 'FXO' and nvl(upper(:INCL_CCY_OPT2),'Y') = 'N') or
nvl(upper(:INCL_CCY_OPT2),'Y') = 'Y')
and ( (nvl(upper(:EXP_TYPES2), 'ALL') = 'ALL')
or
(upper(:EXP_TYPES2) = 'FIRM' and dda_deal_subtype <> 'INDIC')
or
(upper(:EXP_TYPES2) = 'INDICATIVE' and dda_deal_subtype = 'INDIC'))
and (d.dda_DEAL_TYPE not in('EXP','EXT') or (d.dda_DEAL_TYPE in ('EXP','EXT') &P_1 ) )
and ((d.dda_deal_type != 'FXO' and nvl(upper(:INCL_CCY_OPT2),'Y') = 'N') or
(d.dda_deal_type = 'FXO' and d.type_of_amount = 'PREMIUM' and
nvl(upper(:INCL_CCY_OPT2),'Y') = 'N') or
nvl(upper(:INCL_CCY_OPT2),'Y') = 'Y')
group by d.company_code,c.full_name,d.currency_code,nvl(d.company_account,'NOT ALLOCATED TO A/C'),
decode(nvl(upper(:DAY_WEEK_MONTH2),'WEEK')
,'DAY',d.trx_date
,'WEEK',trunc(d.trx_date,'WW') + 7
,last_day(d.trx_date))
UNION
select
r.company_code COMPANY,
upper(c.full_name) COMPANY_NAME,
nvl(r.principal_account_no,'NOT ALLOCATED TO A/C')
ACCT_NOS,
r.currency CCY,
decode(nvl(r.principal_account_no,'NOT ALLOCATED TO A/C')
,'NOT ALLOCATED TO A/C',' '
,'Opening Balance --->') OPEN_BAL2,
decode(nvl(upper(:DAY_WEEK_MONTH2),'WEEK')
,'DAY',:SETTLE_FROM_DATE2
,'WEEK',trunc(:SETTLE_FROM_DATE2,'WW') + 7
,last_day(:SETTLE_FROM_DATE2)) ADATE,
'A' ORD_COLUMN,
0 FX_IN,
round(sum(decode(r.deal_subtype
,'FUND',(-1)
,1) * r.principal_adjust),0)/:amt_unit2 MM_IN,
0 EXP,
0 NET_FLOW,
'*' INDIC,
count(r.deal_number) NUM_TRANS,
XTR_XTRCFACC_XMLP_PKG.open_bal1formula(nvl(r.principal_account_no,'NOT ALLOCATED TO A/C'),r.currency,r.company_code ) OPEN_BAL1,
XTR_XTRCFACC_XMLP_PKG.OPEN_BAL_p OPEN_BAL,
XTR_XTRCFACC_XMLP_PKG.rolling_bal1formula(:ACCOUNT_BAL) ROLLING_BAL1,
XTR_XTRCFACC_XMLP_PKG.ROLLING_BAL_p ROLLING_BAL
/*FROM_DATE2
,'WEEK',trunc(:SETTLE_FROM_DATE2,'WW') + 7
,last_day(:SETTLE_FROM_DATE2)) ADATE,
'A' ORD_COLUMN,
0 FX_IN,
round(sum(decode(r.deal_subtype
,'FUND',(-1)
,1) * r.principal_adjust),0)/:amt_unit2 MM_IN,
0 EXP,
0 NET_FLOW,
'*' INDIC,
count(r.deal_number) NUM_TRANS*/
from XTR_ROLLOVER_TRANSACTIONS_V r,
XTR_PARTIES_V c
where r.deal_type = 'ONC'
and r.status_code = 'CURRENT'
and r.company_code = c.party_code
and r.company_code like nvl(upper(:COMPANY_CODE2),r.company_code)
and r.currency like nvl(upper(:CURRENCY2),'%')
and r.start_date < :SETTLE_FROM_DATE2
and nvl(r.principal_account_no,'%') like nvl(upper(:ACCOUNT_NUM2),'%')
and nvl(r.principal_adjust,0) != 0
and r.maturity_date is NULL
and r.company_code = c.party_code
group by r.company_code,c.full_name,r.currency,nvl(r.principal_account_no,'NOT ALLOCATED TO A/C'),
decode(nvl(upper(:DAY_WEEK_MONTH2),'WEEK')
,'DAY',:SETTLE_FROM_DATE2
,'WEEK',trunc(:SETTLE_FROM_DATE2,'WW') + 7
,last_day(:SETTLE_FROM_DATE2))
order by 1,3,6,7 |