create or replace procedure P_POP_ILC (P_POL_NO VARCHAR2 DEFAULT NULL)
is

cursor c1 is
select *
from rsds_locn_exposure@dmn_rsk_150
where
RLE_ULM_NO = NVL(P_POL_NO , RLE_ULM_NO);

begin
DELETE FROM IDS_LOCN_CLM@dmn_rsk_150;
commit;

for i in c1 loop
INSERT INTO IDS_LOCN_CLM@dmn_rsk_150
(ILC_ULM_NO,
ILC_DIVN,
ILC_DEPT,
ILC_SRC_BUS,
ILC_CLM_NO,
ILC_RISK_ID,
ILC_LOSS_TYP,
ILC_EVENT_CODE,
ILC_EVENT_DESC,
ILC_LOSSD,
ILC_INTD,
ILC_STATUS,
ILC_PAY_EST_AMT,
ILC_REC_EST_AMT,
ILC_PAY_PAID_AMT,
ILC_REC_AMT,
ILC_PAY_OS_AMT,
ILC_REC_OS_AMT,
ILC_SYS_UPD,
ILC_PAY_RET_EST,
ILC_PAY_RET_PAID,
ILC_PAY_RET_OS,
ILC_PAY_QS_EST,
ILC_PAY_QS_PAID,
ILC_PAY_QS_OS,
ILC_PAY_1S_EST,
ILC_PAY_1S_PAID,
ILC_PAY_1S_OS,
ILC_PAY_2S_EST,ILC_PAY_2S_PAID, ILC_PAY_2S_OS, ILC_PAY_FAC_EST, ILC_PAY_FAC_PAID, ILC_PAY_FAC_OS, ILC_PAY_FAC_OB_EST,
ILC_PAY_FAC_OB_PAID, ILC_PAY_FAC_OB_OS, ILC_REC_RET_EST, ILC_REC_RET_PAID, ILC_REC_RET_OS, ILC_REC_QS_EST,
ILC_REC_QS_PAID, ILC_REC_QS_OS, ILC_REC_1S_EST, ILC_REC_1S_PAID, ILC_REC_1S_OS, ILC_REC_2S_EST, ILC_REC_2S_PAID,
ILC_REC_2S_OS, ILC_REC_FAC_EST, ILC_REC_FAC_PAID, ILC_REC_FAC_OS, ILC_REC_FAC_OB_EST, ILC_REC_FAC_OB_PAID,
ILC_REC_FAC_OB_OS, ILC_XOL1_EST, ILC_XOL1_PAID, ILC_XOL1_OS, ILC_XOL2_EST, ILC_XOL2_PAID, ILC_XOL2_OS,
ILC_XOL3_EST, ILC_XOL3_PAID, ILC_XOL3_OS)
select CLF_ULM_NO,
CLF_DIVN_ID,CLF_DEPT_ID,'DIRECT',CLF_CLC_NO,
CLE_RISK_ID,--(select CLE_RISK_ID from ctds_level_e where cle_clf_sgs_id= clf_sgs_id ),
(select UID_DESC from uds_id_defn where uid_id_typ ='LOSS_TYP' and uid_id =CLE_LOSS_ID and UID_UP_PROD_ID =clf_prod_id) a,
null,null,CLF_CLD,CLF_CID,CLF_STATUS,
--(select clc_status from ctds_level_c where clc_clf_sgs_id =clf_sgs_id and CLC_NO = CLF_CLC_NO)
(select sum(CLE_EST_AMT)from ctds_level_e B where cle_clf_sgs_id= clf_sgs_id AND B.CLE_SGS_ID = A.CLE_SGS_ID AND B.CLE_RISK_ID = A.CLE_RISK_ID and CLE_TYP ='P'and B.CLE_LOSS_ID = A.CLE_LOSS_ID and CLE_CVR_ID <>'*'),
(select sum(CLE_EST_AMT)from ctds_level_e B where cle_clf_sgs_id= clf_sgs_id AND B.CLE_SGS_ID = A.CLE_SGS_ID AND B.CLE_RISK_ID = A.CLE_RISK_ID and CLE_TYP ='R' and CLE_CVR_ID <>'*'),
(select sum(CLS_PAY_AMT) from ctds_level_s where cls_clf_sgs_id =clf_sgs_id and cls_cle_sgs_id = A.cle_sgs_id and CLS_RISK_ID= A.CLE_RISK_ID and CLE_CVR_ID <>'*' ) PAY_PAID_AMT,
null,
(select sum(CLE_OS_AMT) from ctds_level_e B where cle_clf_sgs_id = clf_sgs_id AND B.CLE_SGS_ID = A.CLE_SGS_ID AND B.CLE_RISK_ID = A.CLE_RISK_ID and CLE_TYP ='P' and CLE_CVR_ID <>'*'),
(select sum(CLE_OS_AMT) from ctds_level_e B where cle_clf_sgs_id = clf_sgs_id AND B.CLE_SGS_ID = A.CLE_SGS_ID AND B.CLE_RISK_ID = A.CLE_RISK_ID and CLE_TYP ='R' and CLE_CVR_ID <>'*'),
Null,
--(select SUM(RLM_AMT)from rtds_level_mcr where rlm_clm_no = CLF_CLC_NO and RLM_CLE_SGS_ID = A.CLE_SGS_ID and RLM_LOSS_ID = A.CLE_LOSS_ID AND RLM_SR_NO = A.CLE_SR_NO),
(select SUM(RLM_TTY_RETN_AMT_BC)from rtds_level_mcr where rlm_clm_no = CLF_CLC_NO and RLM_CLE_SGS_ID = A.CLE_SGS_ID and RLM_LOSS_ID =A. CLE_LOSS_ID AND RLM_SR_NO = A.CLE_SR_NO),
(select SUM(RLM_TTY_RETN_AMT_BC)from rtds_level_mcr where rlm_clm_no = CLF_CLC_NO and RLM_CLE_SGS_ID = A.CLE_SGS_ID and RLM_LOSS_ID = A.CLE_LOSS_ID and rlm_rec_typ = 'S' AND RLM_SR_NO = A.CLE_SR_NO),
(select SUM(RLM_TTY_RETN_AMT_BC)from rtds_level_mcr where rlm_clm_no = CLF_CLC_NO and RLM_CLE_SGS_ID = A.CLE_SGS_ID and RLM_LOSS_ID = A.CLE_LOSS_ID and rlm_rec_typ = 'E' AND RLM_SR_NO = A.CLE_SR_NO),
(select SUM(RLM_TTY_AMT_BC)from rtds_level_mcr where rlm_clm_no = CLF_CLC_NO and RLM_CLE_SGS_ID = A.CLE_SGS_ID and RLM_LOSS_ID = A.CLE_LOSS_ID AND RLM_SR_NO = A.CLE_SR_NO ),
(select SUM(RLM_TTY_AMT_BC)from rtds_level_mcr where rlm_clm_no = CLF_CLC_NO and RLM_CLE_SGS_ID = A.CLE_SGS_ID and RLM_LOSS_ID = A.CLE_LOSS_ID and rlm_rec_typ = 'S' AND RLM_SR_NO = A.CLE_SR_NO),
(select SUM(RLM_TTY_AMT_BC)from rtds_level_mcr where rlm_clm_no = CLF_CLC_NO and RLM_CLE_SGS_ID = A.CLE_SGS_ID and RLM_LOSS_ID = A.CLE_LOSS_ID and rlm_rec_typ = 'E' AND RLM_SR_NO = A.CLE_SR_NO),
(select SUM(RLD_RI_AMT) from rtds_level_dcr where rld_mcr_sgs_id in (select rlm_mcr_Sgs_id from rtds_level_mcr where rlm_clm_no = CLF_CLC_NO )
AND RLD_REC_TYP ='E' AND RLD_LVL_NO ='2'),
(select SUM(RLD_RI_AMT) from rtds_level_dcr where rld_mcr_sgs_id in (select rlm_mcr_Sgs_id from rtds_level_mcr where rlm_clm_no = CLF_CLC_NO )
AND RLD_REC_TYP ='S' AND RLD_LVL_NO ='2'),NULL,
(SELECT SUM(FLM_FAC_AMT) FROM FTDS_LEVEL_MCR WHERE FLM_CLM_NO = CLF_CLC_NO AND FLM_CLE_SGS_ID = A.CLE_SGS_ID AND FLM_CVR_ID <> '*' AND FLM_LOSS_ID = A.CLE_LOSS_ID AND FLM_SR_NO = A.CLE_SR_NO AND FLM_SR_NO = A.CLE_SR_NO),
(SELECT SUM(FLM_FAC_AMT) FROM FTDS_LEVEL_MCR WHERE FLM_CLM_NO = CLF_CLC_NO AND FLM_CLE_SGS_ID = A.CLE_SGS_ID AND FLM_CVR_ID <> '*' AND FLM_LOSS_ID = A.CLE_LOSS_ID AND FLM_REC_TYP ='S' AND FLM_SR_NO = A.CLE_SR_NO),
(SELECT SUM(FLM_FAC_AMT) FROM FTDS_LEVEL_MCR WHERE FLM_CLM_NO = CLF_CLC_NO AND FLM_CLE_SGS_ID = A.CLE_SGS_ID AND FLM_CVR_ID <> '*' AND FLM_LOSS_ID = A.CLE_LOSS_ID AND FLM_REC_TYP ='E' AND FLM_SR_NO = A.CLE_SR_NO),
null,null,NULL,null,null,NULL,null,null,NULL,null,null,NULL,null,null,NULL,null,
null,NULL,null,null,NULL,null,null,NULL,null,null,NULL,null,null,NULL,null,null,NULL
from ctds_level_fnol,CTDS_LEVEL_E A
where CLF_ULM_NO = i.RLE_ULM_NO
--CLF_CLC_NO in ('C/55/055/02/1003/18/001/000010','C/55/055/02/1003/18/001/000006','C/55/055/02/1003/18/001/000011')
AND CLF_SGS_ID = CLE_CLF_SGS_ID;
end loop;
UWP_INS_LOCN_CLM@dmn_rsk_150(P_POL_NO);
end;


this is my procedure here the db libk is hardcoded so i have not compile this procedure to other db how to do avoid this hardcoded db link wat to do???