SELECT miln.project_id,
miln.institution_nbr,
miln.institution_nme,
institution.institution_legal_nme,
institution_status_code,
DECODE (miln.project_status_code, 'A', 1, 0)
AS project_reporting_count_code,
miln.project_approval_actual_date,
miln.project_commitment_actual_date,
miln.project_size_usd_amt,
miln.environment_category_code,
miln.region_nme,
miln.regional_dept_nme,
miln.country_nme,
country.ibrd_country_code,
country.ida_member_date,
institution.incorporation_country_code,
miln.dept_alpha_code,
miln.master_project_id,
miln.project_status_nme,
miln.current_stage_code,
miln.project_short_nme,
miln.export_ind,
miln.div_alpha_code,
miln.dept_alpha_code || miln.div_alpha_code AS moc_alpha_code,
miln.special_ops_code,
company_credit_risk_summary_v.loan_risk_rating_nbr,
company_credit_risk_summary_v.loan_granular_rating_code,
company_credit_risk_summary_v.loan_direction_rating_code,
company_credit_risk_summary_v.eqty_risk_rating_nbr,
company_credit_risk_summary_v.eqty_granular_rating_code,
company_credit_risk_summary_v.eqty_direction_rating_code,
lis.project_team_staff_info (miln.project_id, 'I', 'N')
investment_officer_nme,
lis.project_team_staff_info (miln.project_id, '8', 'N')
industry_director_nme,
lis.project_team_staff_info (miln.project_id, '6', 'N')
portfolio_assistant_nme,
lis.project_team_staff_info (miln.project_id, 'B', 'N')
lead_env_soc_specialist_nme,
lis.project_team_staff_info (miln.project_id, 'C', 'N')
credit_officer_nme,
lis.project_team_staff_info (miln.project_id, 'D', 'N')
b_loan_mgmt_officer_nme,
lis.project_team_staff_info (miln.project_id, 'L', 'N') lawyer_nme,
lis.project_team_staff_info (miln.project_id, 'P', 'N')
portfolio_manager_nme,
lis.project_team_staff_info (miln.project_id, 'U', 'N')
insurance_specialist_nme,
lis.project_team_staff_info (miln.project_id, 'V', 'N')
env_specialist_nme,
lis.project_team_staff_info (miln.project_id, 'Y', 'N')
portfolio_officer_nme,
lis.project_team_staff_info (miln.project_id, 'J', 'N')
relationship_manager_nme,
miln.greenfield_code,
miln.sme_type_code,
kfc_sector_v.kfc_sector_code,
kfc_sector_v.kfc_sector_nme AS tertiary_sector_nme,
kfc_sector_v.secondary_sector_nme AS secondary_sector_nme,
kfc_sector_v.primary_sector_nme AS primary_sector_nme,
lis.valuation_summary_v.effective_kfc_ownership_pct,
kfc_companycommonownership_pct AS kfc_cmpy_common_ownership_pct,
SUM (tbv.iocmt_usd_amt + tbv.guarocmt_usd_amt + tbv.crmcmt_usd_amt)
/ 1000
kfc_orig_commit_bal_amt,
SUM (pocmt_usd_amt + pgtocmt_usd_amt) / 1000
AS part_orig_commit_bal_amt,
SUM (guarout_usd_amt + dout_usd_amt + crmdout_usd_amt) / 1000
AS kfc_outst_bal_amt,
(SUM (guarout_usd_amt + dout_usd_amt + crmdout_usd_amt)
+ SUM (guarunds_usd_amt + unds_usd_amt + crmunds_usd_amt))
/ 1000
AS kfc_committed_bal_amt,
SUM (pguarout_usd_amt + pdot_usd_amt) / 1000 AS part_outst_bal_amt,
SUM (pguarund_usd_amt + pund_usd_amt) / 1000 AS part_undisb_bal_amt,
(SUM (tbv.ippy_usd_amt)) / 1000 AS kfc_prepay_bal_amt,
(SUM (tbv.irpy_usd_amt)) / 1000 AS kfc_repay_bal_amt,
(SUM (tbv.wrtoff_usd_amt)) / 1000 AS kfc_wrtoff_bal_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'ET', tbv.dout_usd_amt,
0)))
/ 1000
AS et_kfc_outst_bal_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'ET', tbv.unds_usd_amt,
0)))
/ 1000
AS et_kfc_undisb_bal_amt,
(SUM(DECODE (
NVL (
UPPER (
TRIM (institution_loss_reserve.investment_risk_ind)
),
'N'
),
'N',
(DECODE (RTRIM (tbv.tertiary_product_category_code),
'ET', slr.specific_reserve_usd_amt,
0)),
0
)))
/ 1000
AS et_loss_reserve_bal_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'ET', tbv.dout_usd_amt,
'QE', tbv.dout_usd_amt,
0)))
/ 1000
AS et_qe_kfc_outst_bal_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'ET', tbv.unds_usd_amt,
'QE', tbv.unds_usd_amt,
0)))
/ 1000
AS et_qe_kfc_undisb_bal_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'ET', tbv.idsb_usd_amt,
'QE', tbv.idsb_usd_amt,
0)))
/ 1000
AS et_qe_kfc_disb_bal_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'ET', tbv.ccni_usd_amt,
'QE', tbv.ccni_usd_amt,
0)))
/ 1000
AS et_qe_kfc_cancel_bal_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'ET', tbv.iocmt_usd_amt,
'QE', tbv.iocmt_usd_amt,
0)))
/ 1000
AS et_qe_kfc_orig_commit_bal_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'ET', tbv.icmt_usd_amt,
'QE', tbv.icmt_usd_amt,
0)))
/ 1000
AS et_qe_kfc_net_commit_bal_amt,
SUM (project_fytd_activity.kfc_cash_divnd_fytd_amt) / 1000
AS kfc_cash_divnd_fytd_act_amt,
SUM (project_fytd_activity.et_plus_qe_fyd_disbursed) / 1000
AS et_qe_kfc_fytd_disb_act_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'LN', tbv.dout_usd_amt,
0)))
/ 1000
AS ln_kfc_outs_bal_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'LN', tbv.unds_usd_amt,
0)))
/ 1000
AS ln_kfc_undisb_bal_amt,
(SUM(DECODE (
NVL (
UPPER (
TRIM (institution_loss_reserve.investment_risk_ind)
),
'N'
),
'N',
(DECODE (RTRIM (tbv.tertiary_product_category_code),
'LN', slr.specific_reserve_usd_amt,
0)),
0
)))
/ 1000
AS ln_loss_reserve_bal_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'LN', tbv.dout_usd_amt,
'QL', tbv.dout_usd_amt,
0)))
/ 1000
AS ln_ql_kfc_outst_bal_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'LN', tbv.unds_usd_amt,
'QL', tbv.unds_usd_amt,
0)))
/ 1000
AS ln_ql_kfc_undisb_bal_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'LN', tbv.idsb_usd_amt,
'QL', tbv.idsb_usd_amt,
0)))
/ 1000
AS ln_ql_kfc_disb_bal_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'LN', tbv.ccni_usd_amt,
'QL', tbv.ccni_usd_amt,
0)))
/ 1000
AS ln_ql_kfc_cancel_bal_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'LN', tbv.iocmt_usd_amt,
'QL', tbv.iocmt_usd_amt,
0)))
/ 1000
AS ln_ql_kfc_orig_commit_bal_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'LN', tbv.icmt_usd_amt,
'QL', tbv.icmt_usd_amt,
0)))
/ 1000
AS ln_ql_kfc_net_commit_bal_amt,
SUM (project_fytd_activity.ln_plus_ql_fyd_disbursed) / 1000
AS ln_ql_kfc_fytd_disb_act_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'QE', tbv.dout_usd_amt,
0)))
/ 1000
AS qe_kfc_outst_bal_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'QE', tbv.unds_usd_amt,
0)))
/ 1000
AS qe_kfc_undisb_bal_amt,
(SUM(DECODE (
NVL (
UPPER (
TRIM (institution_loss_reserve.investment_risk_ind)
),
'N'
),
'N',
(DECODE (RTRIM (tbv.tertiary_product_category_code),
'QE', slr.specific_reserve_usd_amt,
0)),
0
)))
/ 1000
AS qe_loss_reserve_bal_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'QL', tbv.dout_usd_amt,
0)))
/ 1000
AS ql_kfc_outst_bal_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'QL', tbv.unds_usd_amt,
0)))
/ 1000
AS ql_kfc_undisb_bal_amt,
(SUM(DECODE (
NVL (
UPPER (
TRIM (institution_loss_reserve.investment_risk_ind)
),
'N'
),
'N',
(DECODE (RTRIM (tbv.tertiary_product_category_code),
'QL', slr.specific_reserve_usd_amt,
0)),
0
)))
/ 1000
AS ql_loss_reserve_bal_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'GT', tbv.guarout_usd_amt + tbv.dout_usd_amt,
0)))
/ 1000
AS gt_kfc_outst_bal_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'GT', tbv.guarunds_usd_amt + tbv.unds_usd_amt,
0)))
/ 1000
AS gt_kfc_undisb_bal_amt,
(SUM(DECODE (
NVL (
UPPER (
TRIM (institution_loss_reserve.investment_risk_ind)
),
'N'
),
'N',
(DECODE (RTRIM (tbv.tertiary_product_category_code),
'GT', slr.specific_reserve_usd_amt,
0)),
0
)))
/ 1000
AS gt_loss_reserve_bal_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'GT', tbv.guarcnc_usd_amt + tbv.ccni_usd_amt,
0)))
/ 1000
AS gt_kfc_cancel_bal_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'GT', tbv.guarocmt_usd_amt + tbv.iocmt_usd_amt,
0)))
/ 1000
AS gt_kfc_orig_commit_bal_amt,
(SUM(DECODE (RTRIM (tbv.tertiary_product_category_code),
'GT', tbv.guarcmt_usd_amt + tbv.icmt_usd_amt,
0)))
/ 1000
AS gt_kfc_net_commit_bal_amt,
SUM (tbv.crmdout_usd_amt) / 1000 AS rm_kfc_outst_bal_amt,
SUM (tbv.crmunds_usd_amt) / 1000 AS rm_kfc_undisb_bal_amt,
SUM (tbv.crmcmt_usd_amt) / 1000 AS rm_kfc_orig_commit_bal_amt,
SUM (tbv.crmcmt_usd_amt - tbv.crmcn_usd_amt) / 1000
AS rm_kfc_net_commit_bal_amt,
SUM (tbv.crmcn_usd_amt) / 1000 AS rm_kfc_cancel_bal_amt,
(SUM(DECODE (
NVL (
UPPER (
TRIM (institution_loss_reserve.investment_risk_ind)
),
'N'
),
'N',
(DECODE (RTRIM (tbv.tertiary_product_category_code),
'RM', slr.specific_reserve_usd_amt,
0)),
0
)))
/ 1000
AS rm_loss_reserve_bal_amt,
MAX (project_amount_due_summary.net_prin_balance_usd_amt)
AS arrears_net_principal_bal_amt,
MAX (project_amount_due_summary.interest_balance_usd_amt)
AS arrears_interest_bal_amt,
MAX(project_amount_due_summary.net_prin_balance_usd_amt
+ project_amount_due_summary.interest_balance_usd_amt)
AS arrears_prin_and_intr_bal_amt,
SUM(DECODE (tranche_info_lookup_v.securitized_ind,
'Y',
pguarout_usd_amt + pdot_usd_amt))
/ 1000
AS part_sec_outst_bal_amt,
SUM(DECODE (tranche_info_lookup_v.securitized_ind,
'N',
pguarout_usd_amt + pdot_usd_amt))
/ 1000
AS part_non_sec_outst_bal_amt,
(CASE
WHEN ( (SUM(DECODE (npl.cy_non_performing_ind,
'Y', npl.cy_npl_principal_usd_amt,
0)))
/ 1000) <> 0
THEN
'P'
ELSE
''
END)
AS principal_npl_code,
SUM (npl.cy_npl_principal_usd_amt) / 1000 AS npl_principal_bal_amt,
SUM (npl.cy_npl_int_usd_amt) / 1000 AS npl_interest_bal_amt,
ods.proj_info_lookup.regional_dept_nme proj_regional_dept_nme,
ods.proj_info_lookup.dept_alpha_code proj_dept_alpha_code,
miln.sector_group_nme sector_group_nme,
miln.summary_date,
SUM (project_fytd_activity.kfc_wrtoff_fytd_amt) / 1000
kfc_wrtoff_fytd_amt,
(SUM (tbv.sbpripyb_usd_amt)) / 1000 AS alp_outst_usd_amt,
miln.south_south_project_ind south_south_project_ind,
miln.existing_sponsor_ind existing_sponsor_ind,
(SUM (tbv.billout_usd_amt)) / 1000 AS billout_usd_amt,
(SUM (tbv.ieqinvst_usd_amt)) / 1000 AS ieqinvst_usd_amt,
cleav.lending_eligibility_code,
cleav.lending_eligibility_sub_code,
SUM(CASE
WHEN UPPER(lis.evaluate_pipeline_role_code (
tbv.Product_Type_Code,
tbv.Product_Sub_Type_Code
)) = 'G'
THEN
tbv.synocmt_usd_amt
ELSE
0
END)
/ 1000
AS agt_ORIG_COMMIT_BAL_AMT,
SUM(CASE
WHEN UPPER(lis.evaluate_pipeline_role_code (
tbv.Product_Type_Code,
tbv.Product_Sub_Type_Code
)) = 'G'
THEN
tbv.synoutst_usd_amt
ELSE
0
END)
/ 1000
AS agt_outst_bal_amt,
SUM(CASE
WHEN UPPER(lis.evaluate_pipeline_role_code (
tbv.Product_Type_Code,
tbv.Product_Sub_Type_Code
)) = 'G'
THEN
tbv.synunds_usd_amt
ELSE
0
END)
/ 1000
AS agt_undisb_bal_amt,
miln.INDUSTRY_SUB_GROUP_ID,
miln.INDUSTRY_SUB_GROUP_NME,
miln.SUBNATIONAL_FINANCE_IND,
miln.INDUSTRY_LVL2_SUB_GROUP_ID,
miln.INDUSTRY_LVL2_SUB_GROUP_NME,
miln.INDUSTRY_LVL3_SUB_GROUP_ID,
miln.INDUSTRY_LVL3_SUB_GROUP_NME,
miln.industry_cluster_nme,
lis.get_Institution_Tier_Detail (miln.institution_nbr,
miln.summary_date,
'INSTITUTION_TIER_CODE')
INSTITUTION_TIER_CODE,
lis.get_Institution_Tier_Detail (miln.institution_nbr,
miln.summary_date,
'INSTITUTION_TIER_NME')
INSTITUTION_TIER_NME,
lis.get_Institution_Tier_Detail (miln.institution_nbr,
miln.summary_date,
'TIER_CALC_METHOD_FLAG')
TIER_CALC_METHOD_FLAG,
CASE
WHEN lis.get_Institution_Tier_Detail (miln.institution_nbr,
miln.summary_date,
'TIER_CALC_METHOD_FLAG') =
'S'
THEN
lis.get_Institution_Tier_Detail (miln.institution_nbr,
miln.summary_date,
'EFFECTIVE_DATE')
ELSE
NULL
END
TIER_CALC_METHOD_DATE,
miln.SHORT_TERM_FINANCE_IND,
miln.SUPER_REGION_CODE,
miln.SUPER_REGION_SHORT_NME,
miln.CLIMATE_CHANGE_IND,
miln.CLIMATE_CHANGE_PCT,
lis.get_Income_Participation_ind (miln.project_id,
miln.summary_date)
Income_Participation_Ind
FROM lis.kfc_sector_v kfc_sector_v,
ods.country country,
ods.country institution_country,
ods.institution institution,
ods.proj_info_lookup_monthly pilm,
lis.tranche_balance tbv,
(SELECT r.project_id,
r.tranche_nbr,
(CASE
WHEN r.tranche_nbr >= 50 THEN t.currency_code
WHEN r.tranche_nbr < 50 THEN r.currency_code
END)
currency_code,
summary_date,
NVL (specific_reserve_usd_amt, 0) specific_reserve_usd_amt,
NVL (specific_reserve_crncy_amt, 0)
specific_reserve_crncy_amt,
NVL (proposed_write_off_usd_amt, 0)
proposed_write_off_usd_amt,
NVL (proposed_write_off_crncy_amt, 0)
proposed_write_off_crncy_amt
FROM ods.specific_loss_reserve r, ods.tranche t
WHERE r.project_id = t.project_id
AND r.tranche_nbr = t.tranche_nbr) slr,
ods.proj_info_lookup proj_info_lookup,
ods.institution_loss_reserve,
lis.company_credit_risk_summary_v,
lis.non_performing_loans_by_trch npl,
lis.tranche_info_lookup_v,
( SELECT ads.project_id,
SUM (ads.net_prin_balance_usd_amt) / 1000
AS net_prin_balance_usd_amt,
SUM (interest_balance_usd_amt) / 1000
AS interest_balance_usd_amt,
process_date summary_date
FROM lis.amount_due_summary ads
WHERE ads.portfolio_report_ind = 'Y'
AND ads.participant_nbr IN (1, 1588)
GROUP BY ads.project_id, process_date) project_amount_due_summary,
( SELECT institution_nbr,
summary_date,
DECODE (MIN (project_status_code), 'A', 'Y', 'N')
institution_status_code
FROM ods.proj_info_lookup_monthly pilm
WHERE miln.project_category_code IN ('F', 'I')
AND miln.project_status_code NOT IN ('X', 'D')
GROUP BY institution_nbr, summary_date) institution_status_code,
lis.equity_valuation_summary valuation_summary_v,
( SELECT institution_nbr,
summary_date,
SUM (TO_NUMBER (feature_value_text))
AS kfc_companycommonownership_pct
FROM (SELECT institution_nbr,
share_type_code,
summary_date,
value_date,
feature_type_code,
feature_value_text,
MAX(value_date)
OVER (
PARTITION BY institution_nbr,
share_type_code,
dt.summary_date
)
max_value_date
FROM ods.valuation_feature vf,
lis.summary_date_table dt
WHERE vf.value_date <= dt.summary_date
AND vf.share_type_code = 'COM')
WHERE value_date = max_value_date
AND LTRIM (feature_value_text, '0123456789.') IS NULL
AND feature_type_code = 'OWN'
GROUP BY institution_nbr, summary_date)
kfc_companycommonownership,
(SELECT abc.project_id,
abc.tranche_nbr,
abc.currency_code,
abc.summary_date,
abc.fiscal_year,
SUM(NVL (ln_plus_ql_fyd_disbursed, 0))
OVER (
PARTITION BY abc.project_id,
abc.tranche_nbr,
abc.currency_code,
abc.fiscal_year
ORDER BY abc.project_id,
abc.tranche_nbr,
abc.currency_code,
abc.summary_date
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
ln_plus_ql_fyd_disbursed,
SUM(NVL (et_plus_qe_fyd_disbursed, 0))
OVER (
PARTITION BY abc.project_id,
abc.tranche_nbr,
abc.currency_code,
abc.fiscal_year
ORDER BY abc.project_id,
abc.tranche_nbr,
abc.currency_code,
abc.summary_date
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
et_plus_qe_fyd_disbursed,
SUM(NVL (kfc_cash_divnd_fytd_amt, 0))
OVER (
PARTITION BY abc.project_id,
abc.tranche_nbr,
abc.currency_code,
abc.fiscal_year
ORDER BY abc.project_id,
abc.tranche_nbr,
abc.currency_code,
abc.summary_date
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
kfc_cash_divnd_fytd_amt,
SUM(NVL (kfc_wrtoff_fytd_amt, 0))
OVER (
PARTITION BY abc.project_id,
abc.tranche_nbr,
abc.currency_code,
abc.fiscal_year
ORDER BY abc.project_id,
abc.tranche_nbr,
abc.currency_code,
abc.summary_date
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
kfc_wrtoff_fytd_amt
FROM (SELECT ics_1.project_id,
ics_1.tranche_nbr,
ics_1.currency_code,
ics_1.summary_date,
CASE
WHEN TO_CHAR (ics_1.summary_date, 'MM') <= 6
THEN
EXTRACT (YEAR FROM ics_1.summary_date)
ELSE
EXTRACT (YEAR FROM ics_1.summary_date) + 1
END
fiscal_year
FROM lis.tranche_activity ics_1
WHERE ics_1.summary_date >= '31-JUL-1999') abc,
( SELECT project_id,
tranche_nbr,
currency_code,
summary_date,
CASE
WHEN TO_CHAR (summary_date, 'MM') <= 6
THEN
EXTRACT (YEAR FROM summary_date)
ELSE
EXTRACT (YEAR FROM summary_date) + 1
END
fiscal_year,
SUM(CASE
WHEN tertiary_product_category_code IN
('LN', 'QL')
AND amount_type_code = 'IDSB'
THEN
usd_amt
ELSE
0
END)
AS ln_plus_ql_fyd_disbursed,
SUM(CASE
WHEN tertiary_product_category_code IN
('ET', 'QE')
AND amount_type_code = 'IDSB'
THEN
usd_amt
ELSE
0
END)
AS et_plus_qe_fyd_disbursed,
SUM(CASE
WHEN amount_type_code IN
('DIVINC', 'OILINCM')
THEN
usd_amt
ELSE
0
END)
AS kfc_cash_divnd_fytd_amt,
SUM(CASE
WHEN amount_type_code = 'WRTOFF'
THEN
usd_amt
ELSE
0
END)
AS kfc_wrtoff_fytd_amt
FROM ( SELECT la.project_id,
la.tranche_nbr,
la.summary_date,
atp.amount_type_code,
ics.tertiary_product_category_code,
la.trans_crncy_code currency_code,
SUM (atrm.action_taken * la.usd_amt)
usd_amt,
SUM (atrm.action_taken * la.crncy_amt)
crncy_amt
FROM (SELECT A.gl_account_nbr,
s.gl_sub_account_nbr,
A.gl_account_code,
s.gl_sub_account_code,
DECODE (
TRIM (B.sub_account_code),
'%',
'',
TRIM (B.sub_account_code)
)
fee_code,
B.source_system_nme,
DECODE (
A.gl_account_code,
s.gl_sub_account_code,
1,
-1
)
* DECODE (B.action_code,
'CR', -1,
1)
action_taken,
B.balance_rule_nbr
FROM ods.gl_account A,
ods.gl_sub_account s,
ods.balance_rule_detail B
WHERE s.gl_account_nbr =
A.gl_account_nbr
AND B.gl_account_nbr =
A.gl_account_nbr
AND B.gl_account_nbr =
s.gl_account_nbr
AND B.gl_sub_account_nbr =
s.gl_sub_account_nbr)
atrm,
ods.ledger_posting_date_summary la,
lis.investment_class_summary ics,
ods.amt_type_rule atr,
ods.amount_type atp
WHERE la.summary_date >= '31-JUL-1999'
AND la.project_id = ics.project_id
AND la.tranche_nbr = ics.tranche_nbr
AND la.summary_date = ics.summary_date
AND NVL (ics.obligation_nbr, 0) = 0
AND atr.investment_level_code = 'T'
AND atr.active_ind = 'Y'
AND atr.balance_rule_nbr =
atrm.balance_rule_nbr
AND atr.source_system_nme =
atrm.source_system_nme
AND la.gl_account_nbr =
atrm.gl_account_nbr
AND la.gl_sub_account_nbr =
atrm.gl_sub_account_nbr
AND la.sub_account_code =
NVL (RPAD (atrm.fee_code, 5),
la.sub_account_code)
AND atp.amount_type_code =
atr.amount_type_code
AND atp.amount_type_code IN
('IDSB',
'OILINCM',
'DIVINC',
'WRTOFF')
GROUP BY la.project_id,
la.tranche_nbr,
la.summary_date,
atp.amount_type_code,
ics.tertiary_product_category_code,
la.trans_crncy_code)
GROUP BY project_id,
tranche_nbr,
currency_code,
summary_date) xyz
WHERE abc.project_id = xyz.project_id(+)
AND abc.tranche_nbr = xyz.tranche_nbr(+)
AND abc.currency_code = xyz.currency_code(+)
AND abc.summary_date = xyz.summary_date(+))
project_fytd_activity,
investment_dm.cntry_lend_eligibility_all_v cleav
WHERE miln.summary_date = lis.valuation_summary_v.summary_date(+)
AND miln.institution_nbr =
lis.valuation_summary_v.institution_nbr(+)
AND miln.summary_date = kfc_companycommonownership.summary_date(+)
AND miln.institution_nbr =
kfc_companycommonownership.institution_nbr(+)
AND tbv.summary_date = project_fytd_activity.summary_date(+)
AND tbv.project_id = project_fytd_activity.project_id(+)
AND tbv.tranche_nbr = project_fytd_activity.tranche_nbr(+)
AND tbv.currency_code = project_fytd_activity.currency_code(+)
AND tbv.summary_date = slr.summary_date(+)
AND tbv.project_id = slr.project_id(+)
AND tbv.tranche_nbr = slr.tranche_nbr(+)
AND tbv.currency_code = slr.currency_code(+)
AND miln.summary_date = project_amount_due_summary.summary_date(+)
AND miln.project_id = project_amount_due_summary.project_id(+)
AND miln.project_id = proj_info_lookup.project_id
AND miln.summary_date = tbv.summary_date(+)
AND miln.project_id = tbv.project_id(+)
AND miln.summary_date = institution_status_code.summary_date
AND miln.institution_nbr = institution_status_code.institution_nbr
AND miln.kfc_sector_code = kfc_sector_v.kfc_sector_code
AND miln.country_code = country.country_code
AND institution.country_code = institution_country.country_code(+)
AND miln.institution_nbr = institution.institution_nbr
AND tranche_info_lookup_v.summary_date = tbv.summary_date
AND tranche_info_lookup_v.project_id = tbv.project_id
AND tranche_info_lookup_v.tranche_nbr = tbv.tranche_nbr
AND miln.summary_date =
company_credit_risk_summary_v.summary_date(+)
AND miln.institution_nbr =
company_credit_risk_summary_v.institution_nbr(+)
AND tbv.summary_date = npl.summary_date(+)
AND tbv.project_id = npl.project_id(+)
AND tbv.tranche_nbr = npl.tranche_nbr(+)
AND tbv.currency_code = npl.currency_code(+)
AND miln.project_category_code IN ('F', 'I')
AND miln.project_status_code NOT IN ('X', 'D')
AND proj_info_lookup.institution_nbr =
institution_loss_reserve.institution_nbr(+)
AND tbv.tranche_nbr >= 0
AND (tbv.project_id, tbv.tranche_nbr) IN
(SELECT project_id, tranche_nbr
FROM ods.tranche
WHERE portfolio_id = 'IF')
AND miln.summary_date = cleav.summary_date(+)
AND miln.country_code = cleav.country_code(+)
GROUP BY miln.project_id,
miln.institution_nbr,
miln.institution_nme,
miln.project_status_code,
miln.project_category_code,
miln.project_reporting_count_ind,
miln.project_approval_actual_date,
miln.project_commitment_actual_date,
miln.project_size_usd_amt,
miln.region_nme,
miln.regional_dept_nme,
miln.country_nme,
miln.master_project_id,
miln.project_status_nme,
miln.current_stage_code,
miln.project_short_nme,
miln.export_ind,
miln.div_alpha_code,
miln.dept_alpha_code || miln.div_alpha_code,
miln.dept_alpha_code,
miln.special_ops_code,
miln.kfc_sector_code,
miln.environment_category_code,
miln.greenfield_code,
miln.sme_type_code,
company_credit_risk_summary_v.loan_risk_rating_nbr,
company_credit_risk_summary_v.loan_granular_rating_code,
company_credit_risk_summary_v.loan_direction_rating_code,
company_credit_risk_summary_v.eqty_risk_rating_nbr,
company_credit_risk_summary_v.eqty_granular_rating_code,
company_credit_risk_summary_v.eqty_direction_rating_code,
kfc_sector_v.kfc_sector_code,
kfc_sector_v.kfc_sector_nme,
kfc_sector_v.secondary_sector_nme,
kfc_sector_v.primary_sector_nme,
institution_status_code,
lis.valuation_summary_v.effective_kfc_ownership_pct,
kfc_companycommonownership_pct,
miln.summary_date,
country.ibrd_country_code,
country.ida_member_date,
institution.institution_legal_nme,
institution.incorporation_country_code,
ods.proj_info_lookup.regional_dept_nme,
ods.proj_info_lookup.dept_alpha_code,
miln.sector_group_nme,
miln.south_south_project_ind,
miln.existing_sponsor_ind,
cleav.lending_eligibility_code,
cleav.lending_eligibility_sub_code,
miln.INDUSTRY_SUB_GROUP_ID,
miln.INDUSTRY_SUB_GROUP_NME,
miln.SUBNATIONAL_FINANCE_IND,
miln.INDUSTRY_LVL2_SUB_GROUP_ID,
miln.INDUSTRY_LVL2_SUB_GROUP_NME,
miln.INDUSTRY_LVL3_SUB_GROUP_ID,
miln.INDUSTRY_LVL3_SUB_GROUP_NME,
miln.industry_cluster_nme,
miln.SHORT_TERM_FINANCE_IND,
miln.SUPER_REGION_CODE,
miln.SUPER_REGION_SHORT_NME,
miln.CLIMATE_CHANGE_IND,
miln.CLIMATE_CHANGE_PCT
ORDER BY miln.project_id;
] 
Blog
Search
Help
Members
Register
Login
Home
