/* 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 )                             
   &lt_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 )
   &lt_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 )
   &lt_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
  &lt_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
   &lt_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
   &lt_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