CREATE OR REPLACE PROCEDURE HRIS_LOAN_CASH_PAYMENT_PROC(
  P_PAYMENT_ID     NUMBER
) AS
	V_PAYMENT_DATE DATE;
	V_PRINCIPLE_AMOUNT INT;
    V_REQUEST_ID                INT;
    V_SNO                INT;
    V_REQUESTED_AMOUNT INT;
    V_REMAINING_AMOUNT INT;
    V_DEDUCTED_FROM_SALARY_AMOUNT INT;
    V_REPAYMENT_MONTHS INT;
    COUNTER INT;
    V_TO_DATE DATE;
    V_FROM_DATE DATE;
    V_INTEREST_RATE INT;
    V_NO_OF_DAYS INT;
    V_INTEREST_AMOUNT           FLOAT(20); 
    V_AMOUNT                           NUMBER;
    V_REMAINING_MONTHS INT;
   BEGIN
   
   SELECT PAYMENT_DATE, PRINCIPLE_AMOUNT INTO V_PAYMENT_DATE, V_PRINCIPLE_AMOUNT 
   FROM HRIS_LOAN_CASH_PAYMENT
   WHERE ID = P_PAYMENT_ID;
   
   SELECT LOAN_REQUEST_ID, SNO, ADD_MONTHS(FROM_DATE, 1), ADD_MONTHS(TO_DATE, 1) 
   INTO V_REQUEST_ID, V_SNO, V_FROM_DATE, V_TO_DATE
   FROM HRIS_LOAN_PAYMENT_DETAIL  
   WHERE V_PAYMENT_DATE BETWEEN FROM_DATE AND TO_DATE;
   
   SELECT REQUESTED_AMOUNT, REPAYMENT_MONTHS, INTEREST_RATE 
   INTO V_REQUESTED_AMOUNT, V_REPAYMENT_MONTHS, V_INTEREST_RATE
   FROM HRIS_EMPLOYEE_LOAN_REQUEST
   WHERE LOAN_REQUEST_ID = V_REQUEST_ID;
   
   SELECT SUM(AMOUNT) INTO V_DEDUCTED_FROM_SALARY_AMOUNT FROM HRIS_LOAN_PAYMENT_DETAIL 
   WHERE LOAN_REQUEST_ID = V_REQUEST_ID AND SNO <= V_SNO;
   
   V_REMAINING_MONTHS = V_REPAYMENT_MONTHS - V_SNO;
   
   V_REMAINING_AMOUNT = V_REQUESTED_AMOUNT - (V_DEDUCTED_FROM_SALARY_AMOUNT + V_PRINCIPLE_AMOUNT);
   V_AMOUNT = V_REMAINING_AMOUNT/V_REMAINING_MONTHS;
   
   insert into dummy_table_pratik values('V_REMAINING_AMOUNT', V_REMAINING_AMOUNT);
   insert into dummy_table_pratik values('V_DEDUCTED_FROM_SALARY_AMOUNT', V_DEDUCTED_FROM_SALARY_AMOUNT);
   insert into dummy_table_pratik values('V_REMAINING_MONTHS', V_REMAINING_MONTHS);
   insert into dummy_table_pratik values('V_AMOUNT', V_AMOUNT);
   
   DELETE FROM HRIS_LOAN_PAYMENT_DETAIL WHERE LOAN_REQUEST_ID = V_REQUEST_ID 
   AND SNO > V_SNO;
   
   FOR COUNTER IN (V_SNO+1) .. V_REPAYMENT_MONTHS  DO
   SELECT DAYS_BETWEEN(V_FROM_DATE, V_TO_DATE)+1 INTO V_NO_OF_DAYS DEFAULT NULL FROM DUMMY;
    IF COUNTER = (V_SNO+1) THEN
	V_PRINCIPLE_AMOUNT = V_REMAINING_AMOUNT;
	SELECT (V_PRINCIPLE_AMOUNT*V_INTEREST_RATE/100)/365*V_NO_OF_DAYS INTO V_INTEREST_AMOUNT FROM DUMMY;
	ELSE
	SELECT (PRINCIPLE_AMOUNT - AMOUNT) INTO V_PRINCIPLE_AMOUNT FROM HRIS_LOAN_PAYMENT_DETAIL WHERE PAYMENT_ID = 
	(SELECT MAX(PAYMENT_ID) FROM HRIS_LOAN_PAYMENT_DETAIL WHERE LOAN_REQUEST_ID = V_REQUEST_ID);
	SELECT V_PRINCIPLE_AMOUNT*((V_INTEREST_RATE/100)/365*V_NO_OF_DAYS) INTO V_INTEREST_AMOUNT FROM DUMMY;
	SELECT LAST_DAY(ADD_MONTHS(V_TO_DATE, +1)) INTO V_TO_DATE DEFAULT NULL FROM DUMMY;
	SELECT NEXT_DAY(LAST_DAY(V_FROM_DATE)) INTO V_FROM_DATE FROM DUMMY;
	SELECT DAYS_BETWEEN(V_FROM_DATE, V_TO_DATE)+1 INTO V_NO_OF_DAYS DEFAULT NULL FROM DUMMY;
	END IF;
   
   INSERT INTO HRIS_LOAN_PAYMENT_DETAIL (
    PAYMENT_ID, 
    LOAN_REQUEST_ID, 
    SNO, 
    FROM_DATE, 
    TO_DATE, 
    AMOUNT,  
    MONTH_NO,
    INTEREST_AMOUNT,
    PRINCIPLE_AMOUNT,
    DAYS,
    VOUCHER_NO,
    SHEET_NO,
    REMARKS,
    CREATED_DT,
    CREATED_BY,
    STATUS
    )
    VALUES(
    (SELECT IFNULL(MAX(PAYMENT_ID)+1, 1) FROM HRIS_LOAN_PAYMENT_DETAIL),
    V_REQUEST_ID,
    COUNTER,
    V_FROM_DATE,
    V_TO_DATE,
    V_AMOUNT,
    null,
    V_INTEREST_AMOUNT,
    V_PRINCIPLE_AMOUNT,
    V_NO_OF_DAYS,
    null,
    null,
    null,
    CURRENT_DATE,
    null,
    'E'
    );
   END FOR;
   
   END;