diff --git a/003.Cashflow Report/original_report_query.sql b/003.Cashflow Report/original_report_query.sql new file mode 100644 index 0000000..af4e3cc --- /dev/null +++ b/003.Cashflow Report/original_report_query.sql @@ -0,0 +1,1449 @@ +/* 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 \ No newline at end of file