You Asked
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???
and Chris said...
You should hard code your database link names. It's part of the identifier for the object.
Doing this dynamically leaves you at risk of SQL injection. Allowing people to change the db link names makes it easy for them to run code on any database in your network. Bad idea!
Rating
(4 ratings)
Is this answer out of date? If it is, please let us know via a Comment