CREATE OR REPLACE PROCEDURE HRIS_LOAN_PAYMENT_DETAILS(
  P_LOAN_REQUEST_ID     NUMBER DEFAULT null
) AS
    V_REPAYMENT_MONTHS                INT;
    V_AMOUNT                           NUMBER;
    V_CHECK_AMOUNT                      NUMBER;
    V_INTEREST_RATE                       NUMBER;
    V_REQUESTED_DATE                 DATE;             
    V_REQUESTED_YEAR                  NUMBER;     
    V_INTEREST_AMOUNT           FLOAT(20);    
    V_PRINCIPLE_AMOUNT          FLOAT(20);    
    V_DAYS                      NUMBER;     
    V_REQUESTED_MONTH                VARCHAR(100); 
    V_REQUESTED_AMOUNT                  FLOAT(20);     
    V_NO_OF_DAYS                 NUMBER;
    V_FROM_DATE                     DATE;
    V_TO_DATE                     DATE;
    V_MONTH_ID                    NUMBER;
    V_ROW_COUNT                    NUMBER;
    V_SNO1                          INT;
    V_SNO2                          INT;
    V_PAID_FLAG                     CHAR(1);
    COUNTER                         NUMBER;
    --V_CALENDAR_TYPE                 CHAR(1);

    BEGIN

    --SELECT VALUE INTO V_CALENDAR_TYPE FROM HRIS_PREFERENCES WHERE KEY = 'CALENDAR_VIEW';

    SELECT 
    REPAYMENT_MONTHS, REQUESTED_AMOUNT, REQUESTED_AMOUNT/REPAYMENT_MONTHS,
    LOAN_DATE, HELR.INTEREST_RATE
    INTO 
    V_REPAYMENT_MONTHS, V_REQUESTED_AMOUNT, V_AMOUNT, 
    V_REQUESTED_DATE, V_INTEREST_RATE 
    FROM HRIS_EMPLOYEE_LOAN_REQUEST HELR
    JOIN HRIS_LOAN_MASTER_SETUP HLMS ON HLMS.LOAN_ID = HELR.LOAN_ID
    WHERE 
    LOAN_REQUEST_ID = P_LOAN_REQUEST_ID;

    SELECT V_REQUESTED_DATE, 
    LAST_DAY(ADD_MONTHS(V_REQUESTED_DATE,0))
    INTO V_FROM_DATE, V_TO_DATE FROM DUMMY;

    SELECT days_between(V_FROM_DATE, V_TO_DATE)+1 INTO V_NO_OF_DAYS FROM DUMMY;

    --FOR PAY DETAILS INSERT
    SELECT COUNT(*) INTO V_ROW_COUNT FROM HRIS_LOAN_PAYMENT_DETAIL WHERE 
    LOAN_REQUEST_ID = P_LOAN_REQUEST_ID;
    IF V_ROW_COUNT = 0
    THEN
    
    FOR COUNTER IN 1..V_REPAYMENT_MONTHS  DO
    
    IF COUNTER = 1 THEN
    SELECT V_REQUESTED_AMOUNT INTO V_PRINCIPLE_AMOUNT FROM DUMMY;
    SELECT (V_REQUESTED_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 = P_LOAN_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),
    P_LOAN_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 IF;
    END;