DECLARE
V_LOAN_ID NUMBER;
V_RESTRITION_ID NUMBER;
BEGIN

FOR LOAN_LIST IN (
select * from SCP0218.HR_PAY_SETUP where pay_code IN (SELECT DISTINCT ADVANCE_TYPE FROM SCP0218.Hr_Advance_Request)
)
LOOP

SELECT NVL(MAX(LOAN_ID),0)+1 INTO V_LOAN_ID FROM HRIS_LOAN_MASTER_SETUP;

INSERT INTO 
HRIS_LOAN_MASTER_SETUP(
LOAN_ID,
LOAN_CODE,
LOAN_NAME,
MIN_AMOUNT,
MAX_AMOUNT,
INTEREST_RATE,
STATUS,
CREATED_DATE,
CREATED_BY,
Repayment_Amount,
Repayment_Period
)
VALUES
(
V_LOAN_ID,
LOAN_LIST.PAY_CODE,
LOAN_LIST.PAY_EDESC,
1,
800000,
3,
'E',
TRUNC(SYSDATE),
1,
1,
1
);

-- INSERT INTO RESTRICTION START

 SELECT NVL(MAX(RESTRICTION_ID),0)+1 INTO V_RESTRITION_ID  FROM HRIS_LOAN_RESTRICTIONS;

Insert into HRIS_LOAN_RESTRICTIONS 
(LOAN_ID,RESTRICTION_TYPE,VALUE,STATUS,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,RESTRICTION_ID) values 
(V_LOAN_ID,'serviceType',null,'E',TRUNC(SYSDATE),12,NULL,12,V_RESTRITION_ID);
Insert into HRIS_LOAN_RESTRICTIONS 
(LOAN_ID,RESTRICTION_TYPE,VALUE,STATUS,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,RESTRICTION_ID) values 
(V_LOAN_ID,'designation','null','E',TRUNC(SYSDATE),12,NULL,12,V_RESTRITION_ID+1);
Insert into HRIS_LOAN_RESTRICTIONS 
(LOAN_ID,RESTRICTION_TYPE,VALUE,STATUS,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,RESTRICTION_ID) values 
(V_LOAN_ID,'position',null,'E',TRUNC(SYSDATE),12,NULL,12,V_RESTRITION_ID+2);
Insert into HRIS_LOAN_RESTRICTIONS 
(LOAN_ID,RESTRICTION_TYPE,VALUE,STATUS,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,RESTRICTION_ID) values 
(V_LOAN_ID,'salaryRange',null,'E',TRUNC(SYSDATE),12,NULL,12,V_RESTRITION_ID+3);
Insert into HRIS_LOAN_RESTRICTIONS 
(LOAN_ID,RESTRICTION_TYPE,VALUE,STATUS,CREATED_DATE,CREATED_BY,MODIFIED_DATE,MODIFIED_BY,RESTRICTION_ID) values 
(V_LOAN_ID,'workingPeriod',null,'E',TRUNC(SYSDATE),12,NULL,12,V_RESTRITION_ID+4);


-- INSERT INTO RESTRICTION END



END LOOP;

END;