delimiter //
create PROCEDURE SP_Recon_Limit(IN p_le_short_name varchar(4000),
IN p_br_short_name varchar(4000) 
)
BEGIN
DECLARE v_txn_exists INT default 1;
DECLARE v_parent_facility INT;
DECLARE v_facility_nt facility_nt;
declare c_facility cursor for
select distinct a.ADV_FACI_ID,
a.CURRENCY_ID,
a.OBLIGOR_ID,
a.ADV_FACI_AMOUNT,
a.ADV_FACI_NAME,
case (select 1 from m_obligor where obligor_id = a.obligor_id and reference_id like 'H%') when 1 then 'Y' else 'N' end as IS_DUMMY,
(case when exists(select 1 from t_advance_facility where parent_advf_id = a.ADV_FACI_ID) then 'Y' else 'N' end) as IS_MULTIENTITY
from t_advance_facility a
where a.valid_flag = 'T'
and a.parent_advf_id is null
and a.LEGAL_ENTITY_BRANCH_ID in (select sk_legal_entity_branch_id from r_legal_entity_branch
where LEGAL_ENTITY_ID = (select sk_legal_entity_id from m_legal_entity where legal_entity_shortname = p_le_short_name)
and BRANCH_ID = (select branch_id from m_branch where branch_shortname = p_br_short_name))
and (a.ADV_FACI_STATUS in (6,21,16,26,27,30,43)
or (a.ADV_FACI_STATUS in (9, 8, 11, 7, 24, 25) and a.ADV_FACI_AMOUNT > 0)) prior
a.adv_faci_id = a.parent_advf_id;	
cursor c_sub_facility(f_id in number) is
select ADV_FACI_ID,
CURRENCY_ID,
OBLIGOR_ID,
ADV_FACI_AMOUNT,
ADV_FACI_NAME
from t_advance_facility
where parent_advf_id = f_id;	
begin
delete from dbs_recon_limit;
declare v_facility cursor for c_facility LOOP
BEGIN
SAVEPOINT start_transaction;
select ADV_FACI_ID bulk collect into v_facility_nt from t_advance_facility
start with ADV_FACI_ID = v_facility.ADV_FACI_ID
connect by prior adv_faci_id = parent_advf_id;
select count(*) into v_parent_facility from t_advance_facility
where adv_faci_id = v_facility.ADV_FACI_ID
and PARENT_ADVF_ID is null;
if (v_parent_facility > 0)
then
SP_FACILITY_HEADER(v_facility.ADV_FACI_ID,
v_facility.CURRENCY_ID,
v_facility.OBLIGOR_ID,
v_facility.ADV_FACI_AMOUNT,
v_facility.ADV_FACI_NAME,
v_facility_nt);
end if;	
insert into dbs_recon_limit
SELECT (select reference_id from m_obligor where obligor_id = (select obligor_id from t_advance_facility
where parent_advf_id is null
start with adv_faci_id = v_facility.ADV_FACI_ID
connect by adv_faci_id = prior parent_advf_id) and valid_flag = 'T'), 
v_facility.ADV_FACI_ID,
(select ADV_FACI_NAME from t_advance_facility
where parent_advf_id is null
start with adv_faci_id = v_facility.ADV_FACI_ID
connect by adv_faci_id = prior parent_advf_id), 
(select currency_short_name from m_currency where sk_currency_id = v_facility.CURRENCY_ID and valid_flag = 'T'), 
coalesce(
(case when f.cred_instr_shortname = 'LOAN' THEN
(select j.limit_amt 
from r_adv_faci_cred_instr j
join m_loan_type i on j.loan_type_id = i.sk_loan_type_id
join h_cred lo on lo.loan_type_id = i.sk_loan_type_id
 where j.available_flag = 'T'
 and i.valid_flag = 'T'
 and j.adv_faci_id = (select adv_faci_id from t_advance_facility
where parent_advf_id is null
start with adv_faci_id = h.adv_faci_id
connect by prior parent_advf_id = adv_faci_id)
 and lo.sk_trans_hist_id = a.cred_txn_id
 )
when f.cred_instr_shortname = 'CLOI' THEN
(select k.limit_amt 
from r_adv_faci_cred_instr k
join M_LOI_TYPE l on k.LOI_TYPE_ID = l.SK_ID
join h_cred lto on lto.loi_type_id = l.SK_ID
where k.available_flag = 'T'
and l.valid_flag = 'T'
and k.adv_faci_id = (select adv_faci_id from t_advance_facility
where parent_advf_id is null
start with adv_faci_id = h.adv_faci_id
connect by prior parent_advf_id = adv_faci_id)
and lto.sk_trans_hist_id = a.cred_txn_id
)
when f.cred_instr_shortname = 'SBG' THEN
(select m.limit_amt 
from r_adv_faci_cred_instr m
join M_guarantee n on m.guarantee_ID = n.guarantee_ID
join h_cred o on o.guarantee_type = m.guarantee_ID
where m.available_flag = 'T'
and n.valid_flag = 'T'
and  m.adv_faci_id = (select adv_faci_id from t_advance_facility
where parent_advf_id is null
start with adv_faci_id = h.adv_faci_id
connect by prior parent_advf_id = adv_faci_id)
and o.sk_trans_hist_id = a.cred_txn_id
)
else
(select q.limit_amt 
from r_adv_faci_cred_instr q
join h_cred r on r.CRED_INSTR_TYPE_ID = q.CRED_INSTR_TYPE_ID
where q.adv_faci_id = (select adv_faci_id from t_advance_facility
where parent_advf_id is null
start with adv_faci_id = h.adv_faci_id
connect by prior parent_advf_id = adv_faci_id)
and r.sk_trans_hist_id = a.cred_txn_id
)
end)
, 0)
, 
coalesce(a.exp_value, 0),
(CASE 
WHEN f.cred_instr_shortname = 'LOAN' THEN 
(select LOAN_TYPE_SHORTNAME from m_loan_type mlt, h_cred
where mlt.sk_loan_type_id = loan_type_id
and SK_TRANS_HIST_ID = a.CRED_TXN_ID
and mlt.valid_flag = 'T'
)
WHEN f.cred_instr_shortname = 'CLOI' THEN
(select SHORT_NAME from m_loi_type mloit, h_cred
where mloit.SK_ID = loi_type_id
and SK_TRANS_HIST_ID = a.CRED_TXN_ID
and mloit.valid_flag = 'T'
)
WHEN f.cred_instr_shortname = 'SBG' THEN
(select GUARANTEE_SHORTNAME from m_guarantee mg, h_cred
where mg.guarantee_id = guarantee_type
and SK_TRANS_HIST_ID = a.CRED_TXN_ID
and mg.valid_flag = 'T'
)
ELSE f.cred_instr_shortname 
END), 
(CASE 
WHEN a.exp_status = 1 THEN 'Y'
ELSE 'N'
END),
null,
null
FROM t_eod_rslt_exposure a join h_advance_facility p on a.advance_facility_id = p.SK_ADV_FACI_HIST_ID
join m_cred_instr_type f on a.cit_id = f.cred_instr_type_id
join r_adv_faci_cred_instr h on  f.cred_instr_type_id = h.cred_instr_type_id and h.ADV_FACI_ID = p.original_id
WHERE  h.available_flag = 'T'
and a.cit_id in (select b1.CRED_INSTR_TYPE_ID from m_Cred_instr_grp a1, m_cred_instr_type b1
where a1.sk_cred_instr_grp_id = b1.cred_instr_grp_id
and a1.cred_instr_grp_shortname in ('DC', 'BG', 'FL')
and a1.valid_flag = 'T'
and b1.valid_flag = 'T')
AND a.batch_id IN (SELECT distinct first_value(D.SK_EOD_PROC_HIST_ID) over(partition by D.ADVF_ID order by D.VALUE_DATE desc,D.LAST_UPDATED desc) 
FROM   t_eod_proc_hist D 
WHERE  D.advf_id in (select column_value from table(v_facility_nt)) 
AND D.mtm_successful_flag = 'T' 
AND D.curd_successful_flag = 'T'
AND D.EPH_TYPE not in (4,6));		
if (FOUND_ROWS() = 0)
then
insert into dbs_recon_limit
select (select reference_id from m_obligor where obligor_id = (select obligor_id from t_advance_facility
where parent_advf_id is null
start with adv_faci_id = v_facility.ADV_FACI_ID
connect by adv_faci_id = prior parent_advf_id) and valid_flag = 'T'),
c.afi,
(select ADV_FACI_NAME from t_advance_facility
where parent_advf_id is null
start with adv_faci_id = v_facility.ADV_FACI_ID
connect by adv_faci_id = prior parent_advf_id),
(select currency_short_name from m_currency where sk_currency_id = v_facility.CURRENCY_ID and valid_flag = 'T'),
c.la,
0,
c.cis,
'Y',
null,
null
from (select a.afi,
a.la,
(case when a.typ = 'LOI'
then (select SHORT_NAME from m_loi_type mloit
where mloit.SK_ID = a.loi_type_id) 
when a.typ = 'GID'
then (select GUARANTEE_SHORTNAME from m_guarantee mg
where mg.guarantee_id = a.guarantee_id)
when a.typ = 'LOAN'
then (select LOAN_TYPE_SHORTNAME from m_loan_type mlt
where mlt.sk_loan_type_id = a.loan_type_id)
when a.typ = 'CIT'
then (select cred_instr_shortname from m_cred_instr_type
where cred_instr_type_id = a.cred_instr_type_id)
else 'Not Found' end) as cis
from (select adv_faci_id as afi,
(select ADV_FACI_NAME from t_advance_facility
where adv_faci_id = v_facility.ADV_FACI_ID) as afn,
limit_amt as la,
loi_type_id,
guarantee_id,
loan_type_id,
cred_instr_type_id,
coalesce(cred_instr_group_id,cred_instr_type_id,guarantee_id,loi_type_id,loan_type_id) as id,
(case when cred_instr_group_id is not null
then 'CIG'
when cred_instr_type_id is not null
then 'CIT'
when guarantee_id is not null
then 'GID'
when loi_type_id is not null
then 'LOI'
when loan_type_id is not null
then 'LOAN'
else 'Not Found' end) as typ 
from r_adv_faci_cred_instr
where adv_faci_id = v_facility.ADV_FACI_ID
and available_flag = 'T'
and valid_flag = 'T') a
where a.typ in ('LOAN')) c
where not exists (select 1 from dbs_recon_limit
where facility_id = c.afi
and product_type = c.cis
and liability = 'Y')
order by c.cis;
end if;
SP_LOAN_LIABILITY(v_facility.ADV_FACI_ID,
v_facility.CURRENCY_ID,
v_facility.OBLIGOR_ID,
v_facility.ADV_FACI_NAME,
v_facility.IS_DUMMY,
v_facility.IS_MULTIENTITY);
if (v_facility.IS_DUMMY = 'Y' AND v_facility.IS_MULTIENTITY = 'Y')
then
declare v_sub_facility cursor for c_sub_facility(v_facility.ADV_FACI_ID) LOOP
select ADV_FACI_ID bulk collect into v_facility_nt from t_advance_facility
start with ADV_FACI_ID = v_sub_facility.ADV_FACI_ID
connect by prior adv_faci_id = parent_advf_id;
SP_FACILITY_HEADER(v_sub_facility.ADV_FACI_ID,
v_sub_facility.CURRENCY_ID,
v_sub_facility.OBLIGOR_ID,
v_sub_facility.ADV_FACI_AMOUNT,
v_sub_facility.ADV_FACI_NAME,
v_facility_nt);
insert into dbs_recon_limit
SELECT (select reference_id from m_obligor where obligor_id = v_sub_facility.OBLIGOR_ID and valid_flag = 'T'), 
v_sub_facility.ADV_FACI_ID,
v_sub_facility.ADV_FACI_NAME, 
(select currency_short_name from m_currency where sk_currency_id = v_sub_facility.CURRENCY_ID and valid_flag = 'T'), 
coalesce(
(case when f.cred_instr_shortname = 'LOAN' THEN
(select j.limit_amt 
from r_adv_faci_cred_instr j
join m_loan_type i on j.loan_type_id = i.sk_loan_type_id
join h_cred lo on lo.loan_type_id = i.sk_loan_type_id
where j.available_flag = 'T'
and i.valid_flag = 'T'
and j.adv_faci_id = v_sub_facility.ADV_FACI_ID
and lo.sk_trans_hist_id = a.cred_txn_id
)
when f.cred_instr_shortname = 'CLOI' THEN
(select k.limit_amt 
from r_adv_faci_cred_instr k
join M_LOI_TYPE l on k.LOI_TYPE_ID = l.SK_ID
join h_cred lto on lto.loi_type_id = l.SK_ID
where k.available_flag = 'T'
and l.valid_flag = 'T'
and k.adv_faci_id = v_sub_facility.ADV_FACI_ID
and lto.sk_trans_hist_id = a.cred_txn_id
)
when f.cred_instr_shortname = 'SBG' THEN
(select m.limit_amt 
from r_adv_faci_cred_instr m
join M_guarantee n on m.guarantee_ID = n.guarantee_ID
join h_cred o on o.guarantee_type = m.guarantee_ID
where m.available_flag = 'T'
and n.valid_flag = 'T'
and  m.adv_faci_id = v_sub_facility.ADV_FACI_ID
and o.sk_trans_hist_id = a.cred_txn_id
)
else
(select q.limit_amt 
from r_adv_faci_cred_instr q
join h_cred r on r.CRED_INSTR_TYPE_ID = q.CRED_INSTR_TYPE_ID
where q.adv_faci_id = v_sub_facility.ADV_FACI_ID
and r.sk_trans_hist_id = a.cred_txn_id
)
end)
, 0)
,
coalesce(a.exp_value, 0),
(CASE 
WHEN f.cred_instr_shortname = 'LOAN' THEN 
(select LOAN_TYPE_SHORTNAME from m_loan_type mlt, h_cred
where mlt.sk_loan_type_id = loan_type_id
and SK_TRANS_HIST_ID = a.CRED_TXN_ID
and mlt.valid_flag = 'T'
)
WHEN f.cred_instr_shortname = 'CLOI' THEN
(select SHORT_NAME from m_loi_type mloit, h_cred
where mloit.SK_ID = loi_type_id
and SK_TRANS_HIST_ID = a.CRED_TXN_ID
and mloit.valid_flag = 'T'
)
WHEN f.cred_instr_shortname = 'SBG' THEN
(select GUARANTEE_SHORTNAME from m_guarantee mg, h_cred
where mg.guarantee_id = guarantee_type
and SK_TRANS_HIST_ID = a.CRED_TXN_ID
and mg.valid_flag = 'T'
)
ELSE f.cred_instr_shortname 
END), 
(CASE 
WHEN a.exp_status = 1 THEN 'Y' 
ELSE 'N' 
END),
null,
null
FROM   t_eod_rslt_exposure a join h_advance_facility p on a.advance_facility_id = p.SK_ADV_FACI_HIST_ID
join m_cred_instr_type f on a.cit_id = f.cred_instr_type_id
join r_adv_faci_cred_instr h on  f.cred_instr_type_id = h.cred_instr_type_id and h.ADV_FACI_ID = p.original_id
WHERE  h.available_flag = 'T'
and a.cit_id in (select b1.CRED_INSTR_TYPE_ID from m_Cred_instr_grp a1, m_cred_instr_type b1
where a1.sk_cred_instr_grp_id = b1.cred_instr_grp_id
and a1.cred_instr_grp_shortname in ('DC', 'BG', 'FL')
and a1.valid_flag = 'T'
and b1.valid_flag = 'T')
AND a.batch_id IN (SELECT distinct first_value(D.SK_EOD_PROC_HIST_ID) over(partition by D.ADVF_ID order by D.VALUE_DATE desc,D.LAST_UPDATED desc) 
FROM   t_eod_proc_hist D 
WHERE  D.advf_id in (select column_value from table(v_facility_nt)) 
AND D.mtm_successful_flag = 'T' 
AND D.curd_successful_flag = 'T'
AND D.EPH_TYPE not in (4,6));		
if (FOUND_ROWS() = 0)
then
insert into dbs_recon_limit
select (select reference_id from m_obligor where obligor_id = v_sub_facility.OBLIGOR_ID and valid_flag = 'T'),
c.afi,
v_sub_facility.ADV_FACI_NAME,
(select currency_short_name from m_currency where sk_currency_id = v_sub_facility.CURRENCY_ID and valid_flag = 'T'),
c.la,
0,
c.cis,
'Y',
null,
null
from (select a.afi,
a.la,
(case when a.typ = 'LOI'
then (select SHORT_NAME from m_loi_type mloit
where mloit.SK_ID = a.loi_type_id) 
when a.typ = 'GID'
then (select GUARANTEE_SHORTNAME from m_guarantee mg
where mg.guarantee_id = a.guarantee_id)
when a.typ = 'LOAN'
then (select LOAN_TYPE_SHORTNAME from m_loan_type mlt
where mlt.sk_loan_type_id = a.loan_type_id)
when a.typ = 'CIT'
then (select cred_instr_shortname from m_cred_instr_type
where cred_instr_type_id = a.cred_instr_type_id)
else 'Not Found' end) as cis
from (select adv_faci_id as afi,
(select ADV_FACI_NAME from t_advance_facility
where adv_faci_id = v_sub_facility.ADV_FACI_ID) as afn,
limit_amt as la,
loi_type_id,
guarantee_id,
loan_type_id,
cred_instr_type_id,
coalesce(cred_instr_group_id,cred_instr_type_id,guarantee_id,loi_type_id,loan_type_id) as id,
(case when cred_instr_group_id is not null
then 'CIG'
when cred_instr_type_id is not null
then 'CIT'
when guarantee_id is not null
then 'GID'
when loi_type_id is not null
then 'LOI'
when loan_type_id is not null
then 'LOAN'
else 'Not Found' end) as typ 
from r_adv_faci_cred_instr
where adv_faci_id = v_sub_facility.ADV_FACI_ID
and available_flag = 'T'
and valid_flag = 'T') a
where a.typ in ('LOAN')) c
where not exists (select 1 from dbs_recon_limit
where facility_id = c.afi
and product_type = c.cis
and liability = 'Y')
order by c.cis;
end if;
SP_LOAN_LIABILITY(v_sub_facility.ADV_FACI_ID,
v_sub_facility.CURRENCY_ID,
v_sub_facility.OBLIGOR_ID,
v_sub_facility.ADV_FACI_NAME,
v_facility.IS_DUMMY,
v_facility.IS_MULTIENTITY);
FETCH  INTO;
END WHILE;
CLOSE ;
end if;
declare exit handler for sqlexception begin
rollback to start_transaction;
put_line(concat(ifnull(sqlerrm, '') , ' - ' , IFNULL(v_facility.ADV_FACI_ID, '')));
end;
fetch into;
end;
close;
END LOOP;
update dbs_recon_limit
set syndicated_limit_value = (select SYND_PERC * coalesce(activated_limit_value,0) from t_fro_result
where batch_id = (SELECT distinct first_value(D.SK_EOD_PROC_HIST_ID) over(partition by D.ADVF_ID order by D.VALUE_DATE desc,D.LAST_UPDATED desc) 
FROM   t_eod_proc_hist D 
WHERE  D.advf_id  = facility_id 
AND D.mtm_successful_flag = 'T' 
AND D.curd_successful_flag = 'T'
AND D.EPH_TYPE not in (4,6)) 
and rownum = 1)
, syndicated_perc = (select SYND_PERC from t_fro_result 
where batch_id = (SELECT distinct first_value(D.SK_EOD_PROC_HIST_ID) over(partition by D.ADVF_ID order by D.VALUE_DATE desc,D.LAST_UPDATED desc) 
FROM   t_eod_proc_hist D 
WHERE  D.advf_id = facility_id 
AND D.mtm_successful_flag = 'T' 
AND D.curd_successful_flag = 'T'
AND D.EPH_TYPE not in (4,6)) 
and rownum = 1)
where syndicated_perc is null or
syndicated_limit_value is null;
commit;
open rc_recon_limit for select CIF_NUMBER,
FACILITY_NAME,
LIMIT_CURRENCY,
max(ACTIVATED_LIMIT_VALUE) as ACTIVATED_LIMIT_VALUE,
syndicated_perc,
max(syndicated_limit_value) as syndicated_limit_value,
sum(UTILIZED_VALUE) as UTILIZED_VALUE,
PRODUCT_TYPE,
LIABILITY							
from dbs_recon_limit
group by CIF_NUMBER,
FACILITY_ID,
FACILITY_NAME,
LIMIT_CURRENCY,
syndicated_perc,
PRODUCT_TYPE,
LIABILITY
order by CIF_NUMBER,
FACILITY_ID,
FACILITY_NAME,
LIMIT_CURRENCY,
syndicated_perc,
PRODUCT_TYPE NULLS FIRST;
end SP_Recon_Limit;
//
delimiter;