/* Long Term Contracts */
SELECT por.description portfolio,
TO_CHAR(cf.value_date, :default_date_format) value_date,
cf.value_date value_date_date,
ct.description type,
cur.code ccy,
cf.cur_uid con_cur,
caft.code_description cf_type,
to_char(ltc.ltc_uid)||'/'||to_char(lts.LTS_UID) contract_no,
TO_CHAR(lts.maturity_date, :default_date_format) maturity,
ltc.description loan_title,
NVL(cf.amount, 0) amount,
NVL(cf.base_amount, 0) base_amount,
DECODE(cf.status,
'1', 'Unconfirmed',
'2', 'Confirmed',
'Changed') status,
cf.cf_uid,
org_cparty.name cparty_name,
fol.description fol_desc
FROM organisations org,
organisation_roles or1,
organisations org_cparty,
organisation_roles orr_cpy,
currencies cur,
contract_types ct,
portfolios por,
long_term_contracts ltc,
long_term_schedules lts,
cash_flow_types caft,
cash_flows cf,
star_site_parameters ssp,
folders fol
WHERE :scenario = 0
AND ssp.current_site = 'Y'
AND TRUNC(cf.value_date) BETWEEN :start_date AND :end_date
-- AND DECODE(:incl_excl_contr_based_on,'CD',TRUNC(ltc.CONTRACT_DATE),TRUNC(ltc.issue_date)) <= :end_date
AND NOT cf.status IN ('5','9')
AND NOT EXISTS (SELECT bbs.bbs_uid
FROM buyback_sales bbs
WHERE cf.bbs_uid = bbs.bbs_uid
AND bbs.ct_uid IN (SELECT ct.ct_uid
FROM contract_types ct,
contract_types ct_repo,
contract_types ct_emm,
star_site_parameters ssp
WHERE (ct.code = ct_repo.code OR ct.code = ct_emm.code)
AND ssp.ct_uid_buy_sell_repo = ct_repo.ct_uid
AND ssp.ct_uid_sm_buyback = ct_emm.ct_uid))
AND lts.lts_uid = cf.lts_uid
AND ltc.ltc_uid = lts.ltc_uid
AND por.por_uid = ltc.por_uid
AND ct.ct_uid = ltc.ct_uid
AND caft.caft_uid = cf.caft_uid
AND cf.caft_uid NOT IN (ssp.caft_uid_pri_not, ssp.caft_uid_fee)
AND cur.cur_uid = cf.cur_uid
AND or1.or_uid = ltc.or_uid_beneficiary
AND org.org_uid = or1.org_uid
AND orr_cpy.or_uid = ltc.or_uid_cparty
AND org_cparty.org_uid = orr_cpy.org_uid
AND por.portfolio_type = 'A'
AND lts.details_complete = DECODE(:details_complete,
'Y', :details_complete,
lts.details_complete)
AND por.contingent = DECODE(:contingent_liabilities,
'Y', por.contingent,
'N')
AND ltc.domestic = DECODE(:domestic,
'Y', 'Y',
'N', 'N',
ltc.domestic)
AND ltc.fol_uid = fol.fol_uid(+)
AND (:exclude_if_contr_after is NULL OR DECODE(:incl_excl_contr_based_on,'CD',TRUNC(ltc.CONTRACT_DATE),TRUNC(ltc.issue_date)) <= :exclude_if_contr_after )
<_where_clause
UNION/* Long Term Schedule Fees */
SELECT por.description portfolio,
TO_CHAR(cf.value_date, :default_date_format) value_date,
cf.value_date value_date_date,
ct.description type,
cur.code ccy,
cf.cur_uid con_cur,
caft.code_description cf_type,
to_char(ltc.ltc_uid)||'/'||to_char(lts.LTS_UID) contract_no,
TO_CHAR(lts.maturity_date, :default_date_format) maturity,
ltc.description loan_title,
NVL(cf.amount, 0) amount,
NVL(cf.base_amount, 0) base_amount,
DECODE(cf.status,
'1', 'Unconfirmed',
'2', 'Confirmed',
'Changed') status,
cf.cf_uid,
org_cparty.name cparty_name,
fol.description fol_desc
FROM organisations org,
organisation_roles or1,
organisations org_cparty,
organisation_roles orr_cpy,
currencies cur,
contract_types ct,
portfolios por,
long_term_contracts ltc,
long_term_schedules lts,
cash_flow_types caft,
fee_schedules fs,
cash_flows cf,
star_site_parameters ssp,
folders fol
WHERE :scenario = 0
AND ssp.current_site = 'Y'
AND TRUNC(cf.value_date) BETWEEN :start_date AND :end_date
-- AND DECODE(:incl_excl_contr_based_on,'CD',TRUNC(ltc.CONTRACT_DATE),TRUNC(ltc.issue_date)) <= :end_date
AND NOT cf.status IN ('5', '9')
AND fs.fs_uid = cf.fs_uid
AND lts.lts_uid = fs.lts_uid
AND ltc.ltc_uid = lts.ltc_uid
AND por.por_uid = ltc.por_uid
AND ct.ct_uid = ltc.ct_uid
AND caft.caft_uid = cf.caft_uid
AND cur.cur_uid = cf.cur_uid
AND or1.or_uid = ltc.or_uid_beneficiary
AND org.org_uid = or1.org_uid
AND orr_cpy.or_uid = ltc.or_uid_cparty
AND org_cparty.org_uid = orr_cpy.org_uid
AND por.portfolio_type = 'A'
AND lts.details_complete = DECODE(:details_complete,
'Y', :details_complete,
lts.details_complete)
AND por.contingent = DECODE(:contingent_liabilities,
'Y', por.contingent,
'N')
AND ltc.domestic = DECODE(:domestic,
'Y', 'Y',
'N', 'N',
ltc.domestic)
AND ltc.fol_uid = fol.fol_uid(+)
AND (:exclude_if_contr_after is NULL OR DECODE(:incl_excl_contr_based_on,'CD',TRUNC(ltc.CONTRACT_DATE),TRUNC(ltc.issue_date)) <= :exclude_if_contr_after )
<_where_clause
UNION/* Long Term Contract Fees */
SELECT por.description portfolio,
TO_CHAR(cf.value_date, :default_date_format) value_date,
cf.value_date value_date_date,
ct.description type,
cur.code ccy,
cf.cur_uid con_cur,
caft.code_description cf_type,
-- to_char(ltc.ltc_uid)||'/'||to_char(lts.LTS_UID) contract_no,
to_char(ltc.ltc_uid) contract_no,
TO_CHAR(ltc.maturity_date, :default_date_format) maturity,
ltc.description loan_title,
NVL(cf.amount, 0) amount,
NVL(cf.base_amount, 0) base_amount,
DECODE(cf.status,
'1', 'Unconfirmed',
'2', 'Confirmed',
'Changed') status,
cf.cf_uid,
org_cparty.name cparty_name,
fol.description fol_desc
FROM organisations org,
organisation_roles or1,
organisations org_cparty,
organisation_roles orr_cpy,
currencies cur,
contract_types ct,
portfolios por,
long_term_contracts ltc,
-- long_term_schedules lts,
cash_flow_types caft,
fee_schedules fs,
cash_flows cf,
star_site_parameters ssp,
folders fol
WHERE :scenario = 0
AND ssp.current_site = 'Y'
AND TRUNC(cf.value_date) BETWEEN :start_date AND :end_date
-- AND DECODE(:incl_excl_contr_based_on,'CD',TRUNC(ltc.CONTRACT_DATE),TRUNC(ltc.issue_date)) <= :end_date
AND NOT cf.status IN ('5', '9')
AND fs.fs_uid = cf.fs_uid
-- AND lts.lts_uid = fs.lts_uid
-- AND ltc.ltc_uid = lts.ltc_uid
AND ltc.ltc_uid = fs.ltc_uid
AND por.por_uid = ltc.por_uid
AND ct.ct_uid = ltc.ct_uid
AND caft.caft_uid = cf.caft_uid
AND cur.cur_uid = cf.cur_uid
AND or1.or_uid = ltc.or_uid_beneficiary
AND org.org_uid = or1.org_uid
AND orr_cpy.or_uid = ltc.or_uid_cparty
AND org_cparty.org_uid = orr_cpy.org_uid
AND por.portfolio_type = 'A'
AND ltc.details_complete = DECODE(:details_complete,
'Y', :details_complete,
ltc.details_complete)
AND por.contingent = DECODE(:contingent_liabilities,
'Y', por.contingent,
'N')
AND ltc.domestic = DECODE(:domestic,
'Y', 'Y',
'N', 'N',
ltc.domestic)
AND ltc.fol_uid = fol.fol_uid(+)
AND (:exclude_if_contr_after is NULL OR DECODE(:incl_excl_contr_based_on,'CD',TRUNC(ltc.CONTRACT_DATE),TRUNC(ltc.issue_date)) <= :exclude_if_contr_after )
<_where_clause
UNION/* Short Term Contracts */
SELECT por.description portfolio,
TO_CHAR(cf.value_date, :default_date_format) value_date,
cf.value_date value_date_date,
ct.description type,
cur.code ccy,
cf.cur_uid con_cur,
caft.code_description cf_type,
to_char(stc.stc_uid) contract_no,
TO_CHAR(stc.maturity_date, :default_date_format) maturity,
stc.title loan_title,
NVL(cf.amount, 0) amount,
NVL(cf.base_amount, 0) base_amount,
DECODE(cf.status,
'1', 'Unconfirmed',
'2', 'Confirmed',
'Changed') status,
cf.cf_uid,
org_cparty.name cparty_name,
fol.description fol_desc
FROM organisations org,
organisation_roles or1,
organisations org_cparty,
organisation_roles orr_cpy,
currencies cur,
contract_types ct,
portfolios por,
short_term_contracts stc,
cash_flow_types caft,
cash_flows cf,
star_site_parameters ssp,
folders fol
WHERE :scenario = 0
AND ssp.current_site = 'Y'
AND TRUNC(cf.value_date) BETWEEN :start_date AND :end_date
-- AND DECODE(:incl_excl_contr_based_on,'CD',TRUNC(stc.CONTRACT_DATE),TRUNC(stc.issue_date)) <= :end_date
AND NOT cf.status IN ('5', '9')
AND caft.caft_uid = cf.caft_uid
AND cf.caft_uid <> ssp.caft_uid_pri_not
AND stc.stc_uid = cf.stc_uid
AND ct.ct_uid != ssp.CT_UID_FXC_SWP
AND por.por_uid = stc.por_uid
AND ct.ct_uid = stc.ct_uid
AND cur.cur_uid = cf.cur_uid
AND or1.or_uid = stc.or_uid_beneficiary
AND org.org_uid = or1.org_uid
AND orr_cpy.or_uid = stc.or_uid_cparty
AND org_cparty.org_uid = orr_cpy.org_uid
AND por.portfolio_type = 'A'
AND por.contingent = DECODE(:contingent_liabilities,
'Y', por.contingent,
'N')
AND stc.domestic = DECODE(:domestic,
'Y', 'Y',
'N', 'N',
stc.domestic)
AND stc.fol_uid = fol.fol_uid(+)
AND (:exclude_if_contr_after is NULL OR DECODE(:incl_excl_contr_based_on,'CD',TRUNC(stc.CONTRACT_DATE),TRUNC(stc.issue_date)) <= :exclude_if_contr_after )
&st_where_clause
UNION /* Short Term Contracts FX Swaps*/
SELECT por.description portfolio,
TO_CHAR(cf.value_date, :default_date_format) value_date,
cf.value_date value_date_date,
ct.description type,
cur.code ccy,
cf.cur_uid con_cur,
caft.code_description cf_type,
to_char(stc.stc_uid) contract_no,
TO_CHAR(stc.maturity_date, :default_date_format) maturity,
stc.title loan_title,
NVL(cf.amount, 0) amount,
NVL(cf.base_amount, 0) base_amount,
DECODE(cf.status,
'1', 'Unconfirmed',
'2', 'Confirmed',
'Changed') status,
cf.cf_uid,
org_cparty.name cparty_name,
fol.description fol_desc
FROM organisations org,
organisation_roles or1,
organisations org_cparty,
organisation_roles orr_cpy,
currencies cur,
contract_types ct,
contract_types ct_fx,
portfolios por,
short_term_contracts stc,
cash_flow_types caft,
cash_flows cf,
star_site_parameters ssp,
folders fol,
(select STC_ROLLED_FROM, STC_uid, maturity_date from short_term_contracts where STC_ROLLED_FROM is not null ) leg2,
(select STC_uid, ISSUE_DATE, contract_date from short_term_contracts where STC_ROLLED_FROM is null ) leg1
WHERE :scenario = 0
AND ssp.current_site = 'Y'
AND TRUNC(cf.value_date) BETWEEN :start_date AND :end_date
-- AND DECODE(:incl_excl_contr_based_on,'CD',TRUNC(stc.CONTRACT_DATE),TRUNC(stc.issue_date)) <= :end_date
AND NOT cf.status IN ('5', '9')
AND caft.caft_uid = cf.caft_uid
AND cf.caft_uid <> ssp.caft_uid_pri_not
AND stc.stc_uid = cf.stc_uid
AND por.por_uid = stc.por_uid
AND ct_fx.ct_uid = ssp.CT_UID_FXC_SWP
AND ct.code = ct_fx.code
AND stc.ct_uid = ct.ct_uid
AND cur.cur_uid = cf.cur_uid
AND or1.or_uid = stc.or_uid_beneficiary
AND org.org_uid = or1.org_uid
AND orr_cpy.or_uid = stc.or_uid_cparty
AND org_cparty.org_uid = orr_cpy.org_uid
AND por.portfolio_type = 'A'
AND por.contingent = DECODE(:contingent_liabilities,
'Y', por.contingent,
'N')
AND stc.domestic = DECODE(:domestic,
'Y', 'Y',
'N', 'N',
stc.domestic)
AND stc.fol_uid = fol.fol_uid(+)
AND (:exclude_if_contr_after is NULL OR DECODE(:incl_excl_contr_based_on,'CD',TRUNC(leg1.CONTRACT_DATE),TRUNC(leg1.issue_date)) <= :exclude_if_contr_after )
AND ( (stc.STC_ROLLED_FROM is null and stc.stc_uid = leg2.stc_rolled_from and stc.stc_uid = leg1.stc_uid)
or stc.STC_ROLLED_FROM is not null and leg1.stc_uid = leg2.stc_rolled_from and stc.stc_uid = leg2.stc_uid )
&st_where_clause
UNION/* Auction Fees */
SELECT por.description portfolio,
TO_CHAR(cf.value_date, :default_date_format) value_date,
cf.value_date value_date_date,
ct.description type,
cur.code ccy,
cf.cur_uid con_cur,
caft.code_description cf_type,
to_char(au.au_uid) contract_no,
TO_CHAR(au.maturity_date, :default_date_format) maturity,
au.title loan_title,
NVL(cf.amount, 0) amount,
NVL(cf.base_amount, 0) base_amount,
DECODE(cf.status,
'1', 'Unconfirmed',
'2', 'Confirmed',
'Changed') status,
cf.cf_uid,
org_cparty.name cparty_name,
fol.description fol_desc
FROM organisations org,
organisation_roles or1,
organisations org_cparty,
organisation_roles or_cpy,
currencies cur,
contract_types ct,
portfolios por,
auctions au,
long_term_contracts ltc,
long_term_schedules lts,
short_term_contracts stc,
cash_flow_types caft,
fee_schedules fs,
cash_flows cf,
star_site_parameters ssp,
folders fol
WHERE :scenario = 0
AND ssp.current_site = 'Y'
AND TRUNC(cf.value_date) BETWEEN :start_date AND :end_date
-- AND DECODE(:incl_excl_contr_based_on,'CD',TRUNC(au.published_date ),TRUNC(au.SETTLEMENT_DATE )) <= :end_date
AND NOT cf.status IN ('5', '9')
AND fs.fs_uid = cf.fs_uid
AND au.au_uid = fs.au_uid
AND ((au.STC_UID = stc.STC_UID AND stc.domestic = DECODE(:domestic,'Y', 'Y','N', 'N',stc.domestic))
OR ((au.LTS_UID = lts.LTS_UID AND lts.LTC_UID = ltc.LTC_UID) AND ltc.domestic = DECODE(:domestic, 'Y', 'Y','N', 'N',ltc.domestic)))
AND por.por_uid = au.por_uid
AND ct.ct_uid = au.ct_uid_auction
AND caft.caft_uid = cf.caft_uid
AND cur.cur_uid = cf.cur_uid
AND or1.or_uid = au.or_uid_beneficiary
AND org.org_uid = or1.org_uid
AND or_cpy.or_uid = NVL(au.or_uid_cparty_contract, DECODE(au.stc_uid,
NULL, (SELECT ltc.or_uid_cparty
FROM long_term_contracts ltc,
long_term_schedules lts
WHERE lts.lts_uid = au.lts_uid
AND ltc.ltc_uid = lts.ltc_uid),
(SELECT or_uid_cparty
FROM short_term_contracts
WHERE stc_uid = au.stc_uid)))
AND org_cparty.org_uid = or_cpy.org_uid
AND por.portfolio_type = 'A'
AND au.auction_complete = DECODE(:details_complete,
'Y', :details_complete,
au.auction_complete)
AND por.contingent = DECODE(:contingent_liabilities,
'Y', por.contingent,
'N')
AND au.fol_uid = fol.fol_uid(+)
AND (:exclude_if_contr_after is NULL OR DECODE(:incl_excl_contr_based_on,'CD',TRUNC(au.published_date ),TRUNC(au.SETTLEMENT_DATE )) <= :exclude_if_contr_after )
&au_where_clause
UNION/* Repo Contracts */
SELECT por.description portfolio,
TO_CHAR(cf.value_date, :default_date_format) value_date,
cf.value_date value_date_date,
ct.description type,
cur.code ccy,
cf.cur_uid con_cur,
caft.code_description cf_type,
to_char(rc.rc_uid) contract_no,
TO_CHAR(rc.maturity_date, :default_date_format) maturity,
rc.title loan_title,
NVL(cf.amount, 0) amount,
NVL(cf.base_amount, 0) base_amount,
DECODE(cf.status,
'1', 'Unconfirmed',
'2', 'Confirmed',
'Changed') status,
cf.cf_uid,
org_cparty.name cparty_name,
fol.description fol_desc
FROM organisations org,
organisation_roles or1,
organisations org_cparty,
organisation_roles or_cpy,
currencies cur,
contract_types ct,
portfolios por,
repo_contracts rc,
cash_flow_types caft,
cash_flows cf,
star_site_parameters ssp,
folders fol
WHERE :scenario = 0
AND ssp.current_site = 'Y'
AND TRUNC(cf.value_date) BETWEEN :start_date AND :end_date
-- AND DECODE(:incl_excl_contr_based_on,'CD',TRUNC(rc.contract_date ),TRUNC(rc.SETTLEMENT_DATE)) <= :end_date
AND NOT cf.status IN ('5', '9')
AND rc.rc_uid = cf.rc_uid
AND por.por_uid = rc.por_uid
AND ct.ct_uid = rc.ct_uid
AND caft.caft_uid = cf.caft_uid
AND cf.caft_uid NOT IN (ssp.caft_uid_pri_not, ssp.caft_uid_fee)
AND cur.cur_uid = cf.cur_uid
AND or1.or_uid = rc.or_benif_uid
AND org.org_uid = or1.org_uid
AND or_cpy.or_uid = rc.or_cparty_uid
AND org_cparty.org_uid = or_cpy.org_uid
AND por.portfolio_type = 'A'
AND por.contingent = DECODE(:contingent_liabilities,
'Y', por.contingent,
'N')
AND nvl(:domestic,'N') != 'Y'
AND rc.fol_uid = fol.fol_uid(+)
AND (:exclude_if_contr_after is NULL OR DECODE(:incl_excl_contr_based_on,'CD',TRUNC(rc.contract_date ),TRUNC(rc.SETTLEMENT_DATE)) <= :exclude_if_contr_after )
&rc_where_clause
UNION/* Repo Contract Fees */
SELECT por.description portfolio,
TO_CHAR(cf.value_date, :default_date_format) value_date,
cf.value_date value_date_date,
ct.description type,
cur.code ccy,
cf.cur_uid con_cur,
caft.code_description cf_type,
to_char(rc.rc_uid) contract_no,
TO_CHAR(rc.maturity_date, :default_date_format) maturity,
rc.title loan_title,
NVL(cf.amount, 0) amount,
NVL(cf.base_amount, 0) base_amount,
DECODE(cf.status,
'1', 'Unconfirmed',
'2', 'Confirmed',
'Changed') status,
cf.cf_uid,
org_cparty.name cparty_name,
fol.description fol_desc
FROM organisations org,
organisation_roles or1,
organisations org_cparty,
organisation_roles or_cpy,
currencies cur,
contract_types ct,
portfolios por,
repo_contracts rc,
cash_flow_types caft,
fee_schedules fs,
cash_flows cf,
star_site_parameters ssp,
folders fol
WHERE :scenario = 0
AND ssp.current_site = 'Y'
AND TRUNC(cf.value_date) BETWEEN :start_date AND :end_date
-- AND DECODE(:incl_excl_contr_based_on,'CD',TRUNC(rc.contract_date ),TRUNC(rc.SETTLEMENT_DATE)) <= :end_date
AND NOT cf.status IN ('5', '9')
AND fs.fs_uid = cf.fs_uid
AND rc.rc_uid = fs.rc_uid
AND por.por_uid = rc.por_uid
AND ct.ct_uid = rc.ct_uid
AND caft.caft_uid = cf.caft_uid
AND cur.cur_uid = cf.cur_uid
AND or1.or_uid = rc.or_benif_uid
AND org.org_uid = or1.org_uid
AND or_cpy.or_uid = rc.or_cparty_uid
AND org_cparty.org_uid = or_cpy.org_uid
AND por.portfolio_type = 'A'
AND por.contingent = DECODE(:contingent_liabilities,
'Y', por.contingent,
'N')
AND nvl(:domestic,'N') != 'Y'
AND rc.fol_uid = fol.fol_uid(+)
AND (:exclude_if_contr_after is NULL OR DECODE(:incl_excl_contr_based_on,'CD',TRUNC(rc.contract_date ),TRUNC(rc.SETTLEMENT_DATE)) <= :exclude_if_contr_after )
&rc_where_clause
UNION/* Buyback Sales Fee Cashflows */
SELECT por.description portfolio,
TO_CHAR(stc.value_date, :default_date_format) value_date,
stc.value_date value_date_date,
ct.description type,
cur.code ccy,
cur.cur_uid con_cur,
caft.code_description cf_type,
to_char(stc.stc_uid) contract_no,
TO_CHAR(stc.maturity_date, :default_date_format) maturity,
stc.title loan_title,
NVL(stc.amount, 0) amount,
NVL(stc.base_amount,0) base_amount,
DECODE(stc.status,
'1', 'Unconfirmed',
'2', 'Confirmed',
'Changed') status,
stc.cf_uid,
org_cparty.name cparty_name,
fol.description fol_desc
FROM organisations org,
organisation_roles or1,
organisations org_cparty,
organisation_roles orr_cpy,
currencies cur,
portfolios por,
contract_types ct,
cash_flow_types caft,
(SELECT bbs.bbs_uid stc_uid,
bbs.title,
cf.cur_uid,
ct.ct_uid,
ct.code,
ct.sub_code,
bbs.contract_date maturity_date,
bbs.or_uid_beneficiary or_uid_beneficiary,
bbs.or_uid_cparty or_uid_cparty,
bbs.por_uid,
cf.caft_uid,
cf.value_date,
cf.amount,
cf.base_amount,
cf.status,
cf.cf_uid,
fol.fol_uid
FROM cash_flows cf,
fee_schedules fs,
buyback_sales bbs,
short_term_contracts stc1,
long_term_schedules lts,
long_term_contracts ltc,
star_site_parameters ssp,
contract_types ct,
folders fol
WHERE ssp.current_site = 'Y'
AND cf.bbs_uid = bbs.bbs_uid
AND fs.bbs_uid = bbs.bbs_uid
AND cf.lts_uid IS NULL
AND TRUNC(cf.value_date) BETWEEN :start_date AND :end_date
-- AND DECODE(:incl_excl_contr_based_on,'CD',TRUNC(bbs.contract_date),TRUNC(bbs.SETTLEMENT_DATE)) <= :end_date
AND cf.status NOT IN ('5','9')
AND ct.ct_uid = ssp.ct_uid_bbs
AND bbs.fol_uid = fol.fol_uid(+)
AND ((bbs.STC_UID = stc1.STC_UID AND stc1.domestic = DECODE(:domestic,'Y', 'Y','N', 'N',stc1.domestic))
OR ((bbs.LTS_UID = lts.LTS_UID AND lts.LTC_UID = ltc.LTC_UID) AND ltc.domestic = DECODE(:domestic, 'Y', 'Y','N', 'N',ltc.domestic)))
AND (:exclude_if_contr_after is NULL OR DECODE(:incl_excl_contr_based_on,'CD',TRUNC(bbs.contract_date),TRUNC(bbs.SETTLEMENT_DATE)) <= :exclude_if_contr_after )
&where_clause_stc
&where_clause_stc_cf
) stc,
folders fol
WHERE :scenario = 0
AND por.por_uid = stc.por_uid
AND cur.cur_uid = stc.cur_uid
AND ct.ct_uid = stc.ct_uid
AND caft.caft_uid = stc.caft_uid
AND or1.or_uid = stc.or_uid_beneficiary
AND org.org_uid = or1.org_uid
AND orr_cpy.or_uid = stc.or_uid_cparty
AND org_cparty.org_uid = orr_cpy.org_uid
AND por.portfolio_type = 'A'
AND por.contingent = DECODE(:contingent_liabilities,
'Y', por.contingent,
'N')
-- AND nvl(:domestic,'N') != 'Y'
AND stc.fol_uid = fol.fol_uid(+)
&where_clause_stc_fs
UNION/* Cash Management Flows */
SELECT por.description portfolio,
TO_CHAR(cmf.date_due, :default_date_format) value_date,
cmf.date_due value_date_date,
ct.description type,
cur.code ccy,
cmf.cur_uid con_cur,
'' cf_type,
to_char(cmf.cmf_uid) contract_no,
TO_CHAR(cmf.date_due, :default_date_format) maturity,
'' loan_title,
NVL(cmf.amount, 0) amount,
NVL(cmf.base_amount, 0) base_amount,
DECODE(cmf.status,
'1', 'Unconfirmed',
'2', 'Confirmed',
'Changed') status,
cmf.cmf_uid cf_uid,
org_cparty.name cparty_name,
null fol_desc
FROM organisations org,
organisation_roles or1,
organisations org_cparty,
organisation_roles orr_cpy,
currencies cur,
contract_types ct,
portfolios por,
cash_management_flows cmf
WHERE :scenario = 0
AND :include_cmf = 'Y'
AND TRUNC(cmf.date_due) BETWEEN :start_date AND :end_date
AND NOT cmf.status IN ('5', '9')
AND por.por_uid = cmf.por_uid
AND ct.ct_uid = cmf.ct_uid
AND cur.cur_uid = cmf.cur_uid
AND or1.or_uid = cmf.or_uid_beneficiary
AND org.org_uid = or1.org_uid
AND orr_cpy.or_uid = NVL(cmf.or_uid_outflow_to, cmf.or_uid_inflow_from)
AND org_cparty.org_uid = orr_cpy.org_uid
AND por.portfolio_type = 'A'
AND por.contingent = DECODE(:contingent_liabilities,
'Y', por.contingent,
'N')
AND nvl(:domestic,'N') != 'Y'
AND (:exclude_if_contr_after is NULL OR cmf.date_due <= :exclude_if_contr_after )
&cmf_where_clause
--------------------DOTUK SYM
UNION
/* Repos and Bond Investments cash flows */
SELECT por.description portfolio,
TO_CHAR(cf.value_date, :default_date_format) value_date,
cf.value_date value_date_date,
ct.description type,
cur.code ccy,
cf.cur_uid con_cur,
caft.code_description cf_type,
to_char(bbs.bbs_uid) contract_no,
TO_CHAR(bbs.contract_date, :default_date_format) maturity,
bbs.title loan_title,
NVL(cf.amount, 0) amount,
NVL(cf.base_amount, 0) base_amount,
DECODE(cf.status,
'1', 'Unconfirmed',
'2', 'Confirmed',
'Changed') status,
cf.cf_uid,
org_cparty.name cparty_name,
fol.description fol_desc
FROM organisations org,
organisation_roles or1,
organisations org_cparty,
organisation_roles orr_cpy,
currencies cur,
contract_types ct,
portfolios por,
buyback_sales bbs,
short_term_contracts stc,
long_term_schedules lts,
long_term_contracts ltc,
cash_flow_types caft,
cash_flows cf,
star_site_parameters ssp,
folders fol
WHERE :scenario = 0
AND ssp.current_site = 'Y'
AND TRUNC(cf.value_date) BETWEEN :start_date AND :end_date
-- AND DECODE(:incl_excl_contr_based_on,'CD',TRUNC(bbs.contract_date),TRUNC(bbs.SETTLEMENT_DATE)) <= :end_date
AND NOT cf.status IN ('5', '9')
AND bbs.ct_uid IN (SELECT ct.ct_uid
FROM contract_types ct,
contract_types ct_repo,
contract_types ct_emm,
star_site_parameters ssp
WHERE (ct.code = ct_repo.code OR ct.code = ct_emm.code)
AND ssp.ct_uid_buy_sell_repo = ct_repo.ct_uid
AND ssp.ct_uid_sm_buyback = ct_emm.ct_uid)
AND cf.bbs_uid = bbs.bbs_uid
AND por.por_uid = bbs.por_uid
AND ct.ct_uid = bbs.ct_uid
AND caft.caft_uid = cf.caft_uid
AND cf.caft_uid NOT IN (ssp.caft_uid_pri_not, ssp.caft_uid_fee)
AND cur.cur_uid = cf.cur_uid
AND or1.or_uid = bbs.or_uid_beneficiary
AND org.org_uid = or1.org_uid
AND orr_cpy.or_uid = bbs.or_uid_cparty
AND org_cparty.org_uid = orr_cpy.org_uid
AND por.portfolio_type = 'A'
AND por.contingent = DECODE(:contingent_liabilities,
'Y', por.contingent,
'N')
--AND nvl(:domestic,'N') != 'Y'
AND ((bbs.STC_UID = stc.STC_UID AND stc.domestic = DECODE(:domestic,'Y', 'Y','N', 'N',stc.domestic))
OR ((bbs.LTS_UID = lts.LTS_UID AND lts.LTC_UID = ltc.LTC_UID) AND ltc.domestic = DECODE(:domestic, 'Y', 'Y','N', 'N',ltc.domestic)))
AND bbs.fol_uid = fol.fol_uid(+)
AND (:exclude_if_contr_after is NULL OR DECODE(:incl_excl_contr_based_on,'CD',TRUNC(bbs.contract_date),TRUNC(bbs.SETTLEMENT_DATE)) <= :exclude_if_contr_after )
&bbs_where_clause
UNION/* Scenarios - Long Term Contracts */
SELECT por.description portfolio,
TO_CHAR(cf.value_date, :default_date_format) value_date,
cf.value_date value_date_date,
ct.description type,
cur.code ccy,
cf.cur_uid con_cur,
caft.code_description cf_type,
to_char(ltc.ltc_uid)||'/'||to_char(lts.LTS_UID) contract_no,
TO_CHAR(lts.maturity_date, :default_date_format) maturity,
ltc.description loan_title,
NVL(sca.nominal_amount, 0) amount,
NVL(sca.base_nominal_amount, 0) base_amount,
DECODE(cf.status,
'1', 'Unconfirmed',
'2', 'Confirmed',
'Changed') status,
cf.cf_uid,
org_cparty.name cparty_name,
fol.description fol_desc
FROM organisations org,
organisation_roles or1,
organisations org_cparty,
organisation_roles orr_cpy,
currencies cur,
contract_types ct,
portfolios por,
long_term_contracts ltc,
long_term_schedules lts,
cash_flow_types caft,
scenario_portfolios spo,
scenarios sce,
scenario_amounts sca,
cash_flows cf,
cash_flow_types caft_int,
cash_flow_types caft_pri,
star_site_parameters ssp,
folders fol
WHERE :scenario <> 0
AND ssp.current_site = 'Y'
AND TRUNC(cf.value_date) BETWEEN :start_date AND :end_date
-- AND DECODE(:incl_excl_contr_based_on,'CD',TRUNC(ltc.CONTRACT_DATE),TRUNC(ltc.issue_date)) <= :end_date
AND NOT cf.status IN ('5','9')
AND NOT EXISTS (SELECT bbs.bbs_uid
FROM buyback_sales bbs
WHERE cf.bbs_uid = bbs.bbs_uid
AND bbs.ct_uid IN (SELECT ct.ct_uid
FROM contract_types ct,
contract_types ct_repo,
contract_types ct_emm,
star_site_parameters ssp
WHERE (ct.code = ct_repo.code OR ct.code = ct_emm.code)
AND ssp.ct_uid_buy_sell_repo = ct_repo.ct_uid
AND ssp.ct_uid_sm_buyback = ct_emm.ct_uid))
AND sca.cf_uid = cf.cf_uid
AND sce.sce_uid = sca.sce_uid
AND spo.sce_uid = sce.sce_uid
AND lts.lts_uid = cf.lts_uid
AND ltc.ltc_uid = lts.ltc_uid
AND por.por_uid = ltc.por_uid
AND spo.por_uid = por.por_uid
AND ct.ct_uid = ltc.ct_uid
AND caft.caft_uid = cf.caft_uid
AND cf.caft_uid NOT IN (ssp.caft_uid_pri_not, ssp.caft_uid_fee)
AND cur.cur_uid = cf.cur_uid
AND or1.or_uid = ltc.or_uid_beneficiary
AND org.org_uid = or1.org_uid
AND orr_cpy.or_uid = ltc.or_uid_cparty
AND org_cparty.org_uid = orr_cpy.org_uid
AND lts.details_complete = DECODE(:details_complete,
'Y', :details_complete,
lts.details_complete)
AND por.contingent = DECODE(:contingent_liabilities,
'Y', por.contingent,
'N')
AND ltc.domestic = DECODE(:domestic,
'Y', 'Y',
'N', 'N',
ltc.domestic)
AND ltc.fol_uid = fol.fol_uid(+)
AND (:exclude_if_contr_after is NULL OR DECODE(:incl_excl_contr_based_on,'CD',TRUNC(ltc.CONTRACT_DATE),TRUNC(ltc.issue_date)) <= :exclude_if_contr_after )
&scenario_clause
<_where_clause
UNION/* Scenarios - Long Term Schedule Fees */
SELECT por.description portfolio,
TO_CHAR(cf.value_date, :default_date_format) value_date,
cf.value_date value_date_date,
ct.description type,
cur.code ccy,
cf.cur_uid con_cur,
caft.code_description cf_type,
to_char(ltc.ltc_uid)||'/'||to_char(lts.LTS_UID) contract_no,
TO_CHAR(lts.maturity_date, :default_date_format) maturity,
ltc.description loan_title,
NVL(sca.nominal_amount, 0) amount,
NVL(sca.base_nominal_amount, 0) base_amount,
DECODE(cf.status,
'1', 'Unconfirmed',
'2', 'Confirmed',
'Changed') status,
cf.cf_uid,
org_cparty.name cparty_name,
fol.description fol_desc
FROM organisations org,
organisation_roles or1,
organisations org_cparty,
organisation_roles orr_cpy,
currencies cur,
contract_types ct,
portfolios por,
long_term_contracts ltc,
long_term_schedules lts,
cash_flow_types caft,
fee_schedules fs,
scenario_portfolios spo,
scenarios sce,
scenario_amounts sca,
cash_flows cf,
star_site_parameters ssp,
folders fol
WHERE :scenario <> 0
AND ssp.current_site = 'Y'
AND TRUNC(cf.value_date) BETWEEN :start_date AND :end_date
-- AND DECODE(:incl_excl_contr_based_on,'CD',TRUNC(ltc.CONTRACT_DATE),TRUNC(ltc.issue_date)) <= :end_date
AND NOT cf.status IN ('5','9')
AND sca.cf_uid = cf.cf_uid
AND sce.sce_uid = sca.sce_uid
AND spo.sce_uid = sce.sce_uid
AND fs.fs_uid = cf.fs_uid
AND lts.lts_uid = fs.lts_uid
AND ltc.ltc_uid = lts.ltc_uid
AND por.por_uid = ltc.por_uid
AND spo.por_uid = por.por_uid
AND ct.ct_uid = ltc.ct_uid
AND caft.caft_uid = cf.caft_uid
AND cur.cur_uid = cf.cur_uid
AND or1.or_uid = ltc.or_uid_beneficiary
AND org.org_uid = or1.org_uid
AND orr_cpy.or_uid = ltc.or_uid_cparty
AND org_cparty.org_uid = orr_cpy.org_uid
AND lts.details_complete = DECODE(:details_complete,
'Y', :details_complete,
lts.details_complete)
AND por.contingent = DECODE(:contingent_liabilities,
'Y', por.contingent,
'N')
AND ltc.domestic = DECODE(:domestic,
'Y', 'Y',
'N', 'N',
ltc.domestic)
AND ltc.fol_uid = fol.fol_uid(+)
AND (:exclude_if_contr_after is NULL OR DECODE(:incl_excl_contr_based_on,'CD',TRUNC(ltc.CONTRACT_DATE),TRUNC(ltc.issue_date)) <= :exclude_if_contr_after )
&scenario_clause
<_where_clause
UNION/* Scenarios - Long Term Contract Fees */
SELECT por.description portfolio,
TO_CHAR(cf.value_date, :default_date_format) value_date,
cf.value_date value_date_date,
ct.description type,
cur.code ccy,
cf.cur_uid con_cur,
caft.code_description cf_type,
to_char(ltc.ltc_uid)||'/'||to_char(lts.LTS_UID) contract_no,
TO_CHAR(lts.maturity_date, :default_date_format) maturity,
ltc.description loan_title,
NVL(sca.nominal_amount, 0) amount,
NVL(sca.base_nominal_amount, 0) base_amount,
DECODE(cf.status,
'1', 'Unconfirmed',
'2', 'Confirmed',
'Changed') status,
cf.cf_uid,
org_cparty.name cparty_name,
fol.description fol_desc
FROM organisations org,
organisation_roles or1,
organisations org_cparty,
organisation_roles orr_cpy,
currencies cur,
contract_types ct,
portfolios por,
long_term_contracts ltc,
long_term_schedules lts,
cash_flow_types caft,
fee_schedules fs,
scenario_portfolios spo,
scenarios sce,
scenario_amounts sca,
cash_flows cf,
star_site_parameters ssp,
folders fol
WHERE :scenario <> 0
AND ssp.current_site = 'Y'
AND TRUNC(cf.value_date) BETWEEN :start_date AND :end_date
-- AND DECODE(:incl_excl_contr_based_on,'CD',TRUNC(ltc.CONTRACT_DATE),TRUNC(ltc.issue_date)) <= :end_date
AND NOT cf.status IN ('5', '9')
AND sca.cf_uid = cf.cf_uid
AND sce.sce_uid = sca.sce_uid
AND spo.sce_uid = sce.sce_uid
AND fs.fs_uid = cf.fs_uid
AND ltc.ltc_uid = fs.ltc_uid
AND ltc.ltc_uid = lts.ltc_uid
AND lts.lts_uid = fs.lts_uid
AND por.por_uid = ltc.por_uid
AND spo.por_uid = por.por_uid
AND ct.ct_uid = ltc.ct_uid
AND caft.caft_uid = cf.caft_uid
AND cur.cur_uid = cf.cur_uid
AND or1.or_uid = ltc.or_uid_beneficiary
AND org.org_uid = or1.org_uid
AND orr_cpy.or_uid = ltc.or_uid_cparty
AND org_cparty.org_uid = orr_cpy.org_uid
AND lts.details_complete = DECODE(:details_complete,
'Y', :details_complete,
lts.details_complete)
AND por.contingent = DECODE(:contingent_liabilities,
'Y', por.contingent,
'N')
AND ltc.domestic = DECODE(:domestic,
'Y', 'Y',
'N', 'N',
ltc.domestic)
AND ltc.fol_uid = fol.fol_uid(+)
AND (:exclude_if_contr_after is NULL OR DECODE(:incl_excl_contr_based_on,'CD',TRUNC(ltc.CONTRACT_DATE),TRUNC(ltc.issue_date)) <= :exclude_if_contr_after )
&scenario_clause
<_where_clause
UNION/* Scenarios - Short Term Contracts */
SELECT por.description portfolio,
TO_CHAR(cf.value_date,:default_date_format) value_date,
cf.value_date value_date_date,
ct.description type,
cur.code ccy,
cf.cur_uid con_cur,
caft.code_description cf_type,
to_char(stc.stc_uid) contract_no,
TO_CHAR(stc.maturity_date,:default_date_format) maturity,
stc.title loan_title,
NVL(sca.nominal_amount, 0) amount,
NVL(sca.base_nominal_amount, 0) base_amount,
DECODE(cf.status,
'1', 'Unconfirmed',
'2', 'Confirmed',
'Changed') status,
cf.cf_uid,
org_cparty.name cparty_name,
fol.description fol_desc
FROM organisations org,
organisation_roles or1,
organisations org_cparty,
organisation_roles orr_cpy,
currencies cur,
contract_types ct,
portfolios por,
short_term_contracts stc,
scenario_portfolios spo,
scenarios sce,
scenario_amounts sca,
cash_flow_types caft,
cash_flows cf,
star_site_parameters ssp,
folders fol
WHERE :scenario <> 0
AND ssp.current_site = 'Y'
AND TRUNC(cf.value_date) BETWEEN :start_date AND :end_date
-- AND DECODE(:incl_excl_contr_based_on,'CD',TRUNC(stc.CONTRACT_DATE),TRUNC(stc.issue_date)) <= :end_date
AND NOT cf.status IN ('5', '9')
AND caft.caft_uid = cf.caft_uid
AND cf.caft_uid <> ssp.caft_uid_pri_not
AND sca.cf_uid = cf.cf_uid
AND sce.sce_uid = sca.sce_uid
AND spo.sce_uid = sce.sce_uid
AND stc.stc_uid = cf.stc_uid
AND por.por_uid = stc.por_uid
AND spo.por_uid = por.por_uid
AND ct.ct_uid = stc.ct_uid
AND ct.ct_uid != ssp.CT_UID_FXC_SWP
AND cur.cur_uid = cf.cur_uid
AND or1.or_uid = stc.or_uid_beneficiary
AND org.org_uid = or1.org_uid
AND orr_cpy.or_uid = stc.or_uid_cparty
AND org_cparty.org_uid = orr_cpy.org_uid
AND por.contingent = DECODE(:contingent_liabilities,
'Y', por.contingent,
'N')
AND stc.domestic = DECODE(:domestic,
'Y', 'Y',
'N', 'N',
stc.domestic)
AND stc.fol_uid = fol.fol_uid(+)
AND (:exclude_if_contr_after is NULL OR DECODE(:incl_excl_contr_based_on,'CD',TRUNC(stc.CONTRACT_DATE),TRUNC(stc.issue_date)) <= :exclude_if_contr_after )
&scenario_clause
&st_where_clause
UNION/* Scenarios - Short Term Contracts FX Swaps*/
SELECT por.description portfolio,
TO_CHAR(cf.value_date,:default_date_format) value_date,
cf.value_date value_date_date,
ct.description type,
cur.code ccy,
cf.cur_uid con_cur,
caft.code_description cf_type,
to_char(stc.stc_uid) contract_no,
TO_CHAR(stc.maturity_date,:default_date_format) maturity,
stc.title loan_title,
NVL(sca.nominal_amount, 0) amount,
NVL(sca.base_nominal_amount, 0) base_amount,
DECODE(cf.status,
'1', 'Unconfirmed',
'2', 'Confirmed',
'Changed') status,
cf.cf_uid,
org_cparty.name cparty_name,
fol.description fol_desc
FROM organisations org,
organisation_roles or1,
organisations org_cparty,
organisation_roles orr_cpy,
currencies cur,
contract_types ct,
contract_types ct_fx,
portfolios por,
short_term_contracts stc,
scenario_portfolios spo,
scenarios sce,
scenario_amounts sca,
cash_flow_types caft,
cash_flows cf,
star_site_parameters ssp,
folders fol,
(select STC_ROLLED_FROM, STC_uid, maturity_date from short_term_contracts where STC_ROLLED_FROM is not null ) leg2,
(select STC_uid, ISSUE_DATE, contract_date from short_term_contracts where STC_ROLLED_FROM is null ) leg1
WHERE :scenario <> 0
AND ssp.current_site = 'Y'
AND TRUNC(cf.value_date) BETWEEN :start_date AND :end_date
-- AND DECODE(:incl_excl_contr_based_on,'CD',TRUNC(stc.CONTRACT_DATE),TRUNC(stc.issue_date)) <= :end_date
AND NOT cf.status IN ('5', '9')
AND caft.caft_uid = cf.caft_uid
AND cf.caft_uid <> ssp.caft_uid_pri_not
AND sca.cf_uid = cf.cf_uid
AND sce.sce_uid = sca.sce_uid
AND spo.sce_uid = sce.sce_uid
AND stc.stc_uid = cf.stc_uid
AND por.por_uid = stc.por_uid
AND spo.por_uid = por.por_uid
AND ct_fx.ct_uid = ssp.CT_UID_FXC_SWP
AND ct.code = ct_fx.code
AND stc.ct_uid = ct.ct_uid
AND cur.cur_uid = cf.cur_uid
AND or1.or_uid = stc.or_uid_beneficiary
AND org.org_uid = or1.org_uid
AND orr_cpy.or_uid = stc.or_uid_cparty
AND org_cparty.org_uid = orr_cpy.org_uid
AND por.contingent = DECODE(:contingent_liabilities,
'Y', por.contingent,
'N')
AND stc.domestic = DECODE(:domestic,
'Y', 'Y',
'N', 'N',
stc.domestic)
AND stc.fol_uid = fol.fol_uid(+)
AND (:exclude_if_contr_after is NULL OR DECODE(:incl_excl_contr_based_on,'CD',TRUNC(leg1.CONTRACT_DATE),TRUNC(leg1.issue_date)) <= :exclude_if_contr_after )
AND ( (stc.STC_ROLLED_FROM is null and stc.stc_uid = leg2.stc_rolled_from and stc.stc_uid = leg1.stc_uid)
or stc.STC_ROLLED_FROM is not null and leg1.stc_uid = leg2.stc_rolled_from and stc.stc_uid = leg2.stc_uid )
&scenario_clause
&st_where_clause
UNION/* Scenarios - Auction Fees */
SELECT por.description portfolio,
TO_CHAR(cf.value_date, :default_date_format) value_date,
cf.value_date value_date_date,
ct.description type,
cur.code ccy,
cf.cur_uid con_cur,
caft.code_description cf_type,
to_char(au.au_uid) contract_no,
TO_CHAR(au.maturity_date, :default_date_format) maturity,
au.title loan_title,
NVL(sca.nominal_amount, 0) amount,
NVL(sca.base_nominal_amount, 0) base_amount,
DECODE(cf.status,
'1', 'Unconfirmed',
'2', 'Confirmed',
'Changed') status,
cf.cf_uid,
org_cparty.name cparty_name,
fol.description fol_desc
FROM organisations org,
organisation_roles or1,
organisations org_cparty,
organisation_roles or_cpy,
currencies cur,
contract_types ct,
portfolios por,
auctions au,
long_term_contracts ltc,
long_term_schedules lts,
short_term_contracts stc,
cash_flow_types caft,
fee_schedules fs,
scenario_portfolios spo,
scenarios sce,
scenario_amounts sca,
cash_flows cf,
star_site_parameters ssp,
folders fol
WHERE :scenario <> 0
AND ssp.current_site = 'Y'
AND TRUNC(cf.value_date) BETWEEN :start_date AND :end_date
-- AND DECODE(:incl_excl_contr_based_on,'CD',TRUNC(au.published_date ),TRUNC(au.SETTLEMENT_DATE )) <= :end_date
AND NOT cf.status IN ('5','9')
AND sca.cf_uid = cf.cf_uid
AND sce.sce_uid = sca.sce_uid
AND spo.sce_uid = sce.sce_uid
AND fs.fs_uid = cf.fs_uid
AND au.au_uid = fs.au_uid
AND por.por_uid = au.por_uid
AND spo.por_uid = por.por_uid
AND ct.ct_uid = au.ct_uid_auction
AND caft.caft_uid = cf.caft_uid
AND cur.cur_uid = cf.cur_uid
AND or1.or_uid = au.or_uid_beneficiary
AND org.org_uid = or1.org_uid
AND or_cpy.or_uid = NVL(au.or_uid_cparty_contract, DECODE(au.stc_uid,
NULL, (SELECT ltc.or_uid_cparty
FROM long_term_contracts ltc,
long_term_schedules lts
WHERE lts.lts_uid = au.lts_uid
AND ltc.ltc_uid = lts.ltc_uid),
(SELECT or_uid_cparty
FROM short_term_contracts
WHERE stc_uid = au.stc_uid)))
AND org_cparty.org_uid = or_cpy.org_uid
AND au.auction_complete = DECODE(:details_complete,
'Y', :details_complete,
au.auction_complete)
AND por.contingent = DECODE(:contingent_liabilities,
'Y', por.contingent,
'N')
AND ((au.STC_UID = stc.STC_UID AND stc.domestic = DECODE(:domestic,'Y', 'Y','N', 'N',stc.domestic))
OR ((au.LTS_UID = lts.LTS_UID AND lts.LTC_UID = ltc.LTC_UID) AND ltc.domestic = DECODE(:domestic, 'Y', 'Y','N', 'N',ltc.domestic)))
-- AND nvl(:domestic,'N') != 'Y'
AND au.fol_uid = fol.fol_uid(+)
AND (:exclude_if_contr_after is NULL OR DECODE(:incl_excl_contr_based_on,'CD',TRUNC(au.published_date ),TRUNC(au.SETTLEMENT_DATE )) <= :exclude_if_contr_after )
&scenario_clause
&au_where_clause
UNION/* Scenarios - Repo Contracts */
SELECT por.description portfolio,
TO_CHAR(cf.value_date, :default_date_format) value_date,
cf.value_date value_date_date,
ct.description type,
cur.code ccy,
cf.cur_uid con_cur,
caft.code_description cf_type,
to_char(rc.rc_uid) contract_no,
TO_CHAR(rc.maturity_date, :default_date_format) maturity,
rc.title loan_title,
NVL(sca.nominal_amount, 0) amount,
NVL(sca.base_nominal_amount, 0) base_amount,
DECODE(cf.status,
'1', 'Unconfirmed',
'2', 'Confirmed',
'Changed') status,
cf.cf_uid,
org_cparty.name cparty_name,
fol.description fol_des
FROM organisations org,
organisation_roles or1,
organisations org_cparty,
organisation_roles or_cpy,
currencies cur,
contract_types ct,
portfolios por,
repo_contracts rc,
cash_flow_types caft,
scenario_portfolios spo,
scenarios sce,
scenario_amounts sca,
cash_flows cf,
star_site_parameters ssp,
folders fol
WHERE :scenario <> 0
AND ssp.current_site = 'Y'
AND TRUNC(cf.value_date) BETWEEN :start_date AND :end_date
-- AND DECODE(:incl_excl_contr_based_on,'CD',TRUNC(rc.contract_date ),TRUNC(rc.SETTLEMENT_DATE)) <= :end_date
AND NOT cf.status IN ('5','9')
AND sca.cf_uid = cf.cf_uid
AND sce.sce_uid = sca.sce_uid
AND spo.sce_uid = sce.sce_uid
AND rc.rc_uid = cf.rc_uid
AND por.por_uid = rc.por_uid
AND spo.por_uid = por.por_uid
AND ct.ct_uid = rc.ct_uid
AND caft.caft_uid = cf.caft_uid
AND cf.caft_uid NOT IN (ssp.caft_uid_pri_not, ssp.caft_uid_fee)
AND cur.cur_uid = cf.cur_uid
AND or1.or_uid = rc.or_benif_uid
AND org.org_uid = or1.org_uid
AND or_cpy.or_uid = rc.or_cparty_uid
AND org_cparty.org_uid = or_cpy.org_uid
AND por.contingent = DECODE(:contingent_liabilities,
'Y', por.contingent,
'N')
AND nvl(:domestic,'N') != 'Y'
AND rc.fol_uid = fol.fol_uid(+)
AND (:exclude_if_contr_after is NULL OR DECODE(:incl_excl_contr_based_on,'CD',TRUNC(rc.contract_date ),TRUNC(rc.SETTLEMENT_DATE)) <= :exclude_if_contr_after )
&scenario_clause
&rc_where_clause
UNION/* Scenarios - Repo Contract Fees */
SELECT por.description portfolio,
TO_CHAR(cf.value_date, :default_date_format) value_date,
cf.value_date value_date_date,
ct.description type,
cur.code ccy,
cf.cur_uid con_cur,
caft.code_description cf_type,
to_char(rc.rc_uid) contract_no,
TO_CHAR(rc.maturity_date, :default_date_format) maturity,
rc.title loan_title,
NVL(sca.nominal_amount, 0) amount,
NVL(sca.base_nominal_amount, 0) base_amount,
DECODE(cf.status,
'1', 'Unconfirmed',
'2', 'Confirmed',
'Changed') status,
cf.cf_uid,
org_cparty.name cparty_name,
fol.description fol_desc
FROM organisations org,
organisation_roles or1,
organisations org_cparty,
organisation_roles or_cpy,
currencies cur,
contract_types ct,
portfolios por,
repo_contracts rc,
cash_flow_types caft,
fee_schedules fs,
scenario_portfolios spo,
scenarios sce,
scenario_amounts sca,
cash_flows cf,
star_site_parameters ssp,
folders fol
WHERE :scenario <> 0
AND ssp.current_site = 'Y'
AND TRUNC(cf.value_date) BETWEEN :start_date AND :end_date
-- AND DECODE(:incl_excl_contr_based_on,'CD',TRUNC(rc.contract_date ),TRUNC(rc.SETTLEMENT_DATE)) <= :end_date
AND NOT cf.status IN ('5', '9')
AND sca.cf_uid = cf.cf_uid
AND sce.sce_uid = sca.sce_uid
AND spo.sce_uid = sce.sce_uid
AND fs.fs_uid = cf.fs_uid
AND rc.rc_uid = fs.rc_uid
AND por.por_uid = rc.por_uid
AND spo.por_uid = por.por_uid
AND ct.ct_uid = rc.ct_uid
AND caft.caft_uid = cf.caft_uid
AND cur.cur_uid = cf.cur_uid
AND or1.or_uid = rc.or_benif_uid
AND org.org_uid = or1.org_uid
AND or_cpy.or_uid = rc.or_cparty_uid
AND org_cparty.org_uid = or_cpy.org_uid
AND por.contingent = DECODE(:contingent_liabilities,
'Y', por.contingent,
'N')
AND nvl(:domestic,'N') != 'Y'
AND rc.fol_uid = fol.fol_uid(+)
AND (:exclude_if_contr_after is NULL OR DECODE(:incl_excl_contr_based_on,'CD',TRUNC(rc.contract_date ),TRUNC(rc.SETTLEMENT_DATE)) <= :exclude_if_contr_after )
&scenario_clause
&rc_where_clause
UNION/* Scenario Buyback Sales Fee Cashflows */
SELECT por.description portfolio,
TO_CHAR(stc.value_date, :default_date_format) value_date,
stc.value_date value_date_date,
ct.description type,
cur.code ccy,
cur.cur_uid con_cur,
caft.code_description cf_type,
to_char(stc.stc_uid) contract_no,
TO_CHAR(stc.maturity_date,:default_date_format) maturity,
stc.title loan_title,
NVL(stc.amount, 0) amount,
NVL(stc.base_amount, 0) base_amount,
DECODE(stc.status,
'1', 'Unconfirmed',
'2', 'Confirmed',
'Changed') status,
stc.cf_uid,
org_cparty.name cparty_name,
fol.description fol_desc
FROM organisations org,
organisation_roles or1,
organisations org_cparty,
organisation_roles orr_cpy,
currencies cur,
portfolios por,
scenario_portfolios spo,
contract_types ct,
cash_flow_types caft,
folders fol,
(SELECT bbs.bbs_uid stc_uid,
bbs.title,
cf.cur_uid,
ct.ct_uid,
ct.code,
ct.sub_code,
bbs.contract_date maturity_date,
bbs.or_uid_beneficiary or_uid_beneficiary,
bbs.or_uid_cparty or_uid_cparty,
bbs.por_uid,
cf.caft_uid,
cf.value_date,
cf.amount,
cf.base_amount,
cf.status,
sce.sce_uid,
cf.cf_uid,
fol.fol_uid
FROM cash_flows cf,
fee_schedules fs,
buyback_sales bbs,
short_term_contracts stc1,
long_term_contracts ltc,
long_term_schedules lts,
star_site_parameters ssp,
contract_types ct,
scenarios sce,
scenario_amounts sca,
folders fol
WHERE ssp.current_site = 'Y'
AND cf.bbs_uid = bbs.bbs_uid
AND fs.bbs_uid = bbs.bbs_uid
AND cf.lts_uid IS NULL
AND sca.cf_uid = cf.cf_uid
AND sce.sce_uid = sca.sce_uid
AND TRUNC(cf.value_date) BETWEEN :start_date AND :end_date
AND ((bbs.STC_UID = stc1.STC_UID AND stc1.domestic = DECODE(:domestic,'Y', 'Y','N', 'N',stc1.domestic))
OR ((bbs.LTS_UID = lts.LTS_UID AND lts.LTC_UID = ltc.LTC_UID) AND ltc.domestic = DECODE(:domestic, 'Y', 'Y','N', 'N',ltc.domestic)))
-- AND DECODE(:incl_excl_contr_based_on,'CD',TRUNC(bbs.contract_date),TRUNC(bbs.SETTLEMENT_DATE)) <= :end_date
AND cf.status NOT IN ('5', '9')
AND ct.ct_uid = ssp.ct_uid_bbs
AND bbs.fol_uid = fol.fol_uid(+)
AND (:exclude_if_contr_after is NULL OR DECODE(:incl_excl_contr_based_on,'CD',TRUNC(bbs.contract_date),TRUNC(bbs.SETTLEMENT_DATE)) <= :exclude_if_contr_after )
&where_clause_stc_cf) stc
WHERE :scenario = 0
AND por.por_uid = stc.por_uid
AND cur.cur_uid = stc.cur_uid
AND ct.ct_uid = stc.ct_uid
AND caft.caft_uid = stc.caft_uid
AND or1.or_uid = stc.or_uid_beneficiary
AND org.org_uid = or1.org_uid
AND orr_cpy.or_uid = stc.or_uid_cparty
AND org_cparty.org_uid = orr_cpy.org_uid
AND spo.sce_uid = stc.sce_uid
AND spo.por_uid = por.por_uid
AND por.contingent = DECODE(:contingent_liabilities,
'Y', por.contingent,
'N')
AND stc.fol_uid = fol.fol_uid(+)
-- AND nvl(:domestic,'N') != 'Y'
&scenario_clause_stc_fs
&where_clause_stc_fs
UNION/* Scenaio - Repos and Bond Investments cash flows */
SELECT por.description portfolio,
TO_CHAR(cf.value_date, :default_date_format) value_date,
cf.value_date value_date_date,
ct.description type,
cur.code ccy,
cf.cur_uid con_cur,
caft.code_description cf_type,
to_char(bbs.bbs_uid) contract_no,
TO_CHAR(bbs.contract_date, :default_date_format) maturity,
bbs.title loan_title,
NVL(sca.nominal_amount, 0) amount,
NVL(sca.base_nominal_amount, 0) base_amount,
DECODE(cf.status,
'1','Unconfirmed',
'2','Confirmed',
'Changed') status,
cf.cf_uid,
org_cparty.name cparty_name,
fol.description fol_desc
FROM organisations org,
organisation_roles or1,
organisations org_cparty,
organisation_roles orr_cpy,
currencies cur,
contract_types ct,
portfolios por,
buyback_sales bbs,
short_term_contracts stc,
long_term_contracts ltc,
long_term_schedules lts,
scenario_portfolios spo,
scenarios sce,
scenario_amounts sca,
cash_flow_types caft,
cash_flows cf,
star_site_parameters ssp,
folders fol
WHERE :scenario <> 0
AND ssp.current_site = 'Y'
AND TRUNC(cf.value_date) BETWEEN :start_date AND :end_date
-- AND DECODE(:incl_excl_contr_based_on,'CD',TRUNC(bbs.contract_date),TRUNC(bbs.SETTLEMENT_DATE)) <= :end_date
AND NOT cf.status IN ('5','9')
AND bbs.ct_uid IN (SELECT ct.ct_uid
FROM contract_types ct,
contract_types ct_repo,
contract_types ct_emm,
star_site_parameters ssp
WHERE (ct.code = ct_repo.code OR ct.code = ct_emm.code)
AND ssp.ct_uid_buy_sell_repo = ct_repo.ct_uid
AND ssp.ct_uid_sm_buyback = ct_emm.ct_uid)
AND sca.cf_uid = cf.cf_uid
AND sce.sce_uid = sca.sce_uid
AND spo.sce_uid = sce.sce_uid
AND spo.por_uid = por.por_uid
AND cf.bbs_uid = bbs.bbs_uid
AND por.por_uid = bbs.por_uid
AND ct.ct_uid = bbs.ct_uid
AND caft.caft_uid = cf.caft_uid
AND cf.caft_uid NOT IN (ssp.caft_uid_pri_not,ssp.caft_uid_fee)
AND cur.cur_uid = cf.cur_uid
AND or1.or_uid = bbs.or_uid_beneficiary
AND org.org_uid = or1.org_uid
AND orr_cpy.or_uid = bbs.or_uid_cparty
AND org_cparty.org_uid = orr_cpy.org_uid
AND por.contingent = DECODE(:contingent_liabilities,
'Y', por.contingent,
'N')
AND bbs.fol_uid = fol.fol_uid(+)
AND ((bbs.STC_UID = stc.STC_UID AND stc.domestic = DECODE(:domestic,'Y', 'Y','N', 'N',stc.domestic))
OR ((bbs.LTS_UID = lts.LTS_UID AND lts.LTC_UID = ltc.LTC_UID) AND ltc.domestic = DECODE(:domestic, 'Y', 'Y','N', 'N',ltc.domestic)))
--AND nvl(:domestic,'N') != 'Y'
AND bbs.fol_uid = fol.fol_uid(+)
AND (:exclude_if_contr_after is NULL OR DECODE(:incl_excl_contr_based_on,'CD',TRUNC(bbs.contract_date),TRUNC(bbs.SETTLEMENT_DATE)) <= :exclude_if_contr_after )
&scenario_clause
&bbs_where_clause
Vesel Karastoyanov