CREATE OR REPLACE PROCEDURE HRIS_LOAN_PAYMENT_SKIP_REVERT(
  P_LOAN_REQUEST_ID     NUMBER,
  P_PAYMENT_ID     NUMBER
) 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;

	SELECT AMOUNT INTO V_CHECK_AMOUNT FROM HRIS_LOAN_PAYMENT_DETAIL
    WHERE LOAN_REQUEST_ID = P_LOAN_REQUEST_ID
    AND PAYMENT_ID = P_PAYMENT_ID;
    SELECT PAID_FLAG INTO V_PAID_FLAG FROM HRIS_LOAN_PAYMENT_DETAIL WHERE 
    PAYMENT_ID = P_PAYMENT_ID;
    IF V_PAID_FLAG = 'N'
    THEN
    IF V_CHECK_AMOUNT <> 0
    THEN
    BEGIN
    SELECT FROM_DATE, TO_DATE, DAYS_BETWEEN(FROM_DATE, TO_DATE) 
    INTO 
    V_FROM_DATE, V_TO_DATE, V_NO_OF_DAYS DEFAULT NULL, NULL, NULL
    FROM HRIS_MONTH_CODE WHERE MONTH_ID = V_MONTH_ID;
    
    END;

    UPDATE HRIS_LOAN_PAYMENT_DETAIL SET AMOUNT = 0 WHERE LOAN_REQUEST_ID = P_LOAN_REQUEST_ID
    AND PAYMENT_ID = P_PAYMENT_ID;

    SELECT SNO+1 INTO V_SNO1 FROM HRIS_LOAN_PAYMENT_DETAIL WHERE LOAN_REQUEST_ID = P_LOAN_REQUEST_ID
    AND PAYMENT_ID = P_PAYMENT_ID;

    SELECT MAX(SNO) INTO V_SNO2 FROM HRIS_LOAN_PAYMENT_DETAIL WHERE LOAN_REQUEST_ID = P_LOAN_REQUEST_ID;

    FOR COUNTER IN V_SNO1 .. V_SNO2 DO

    UPDATE HRIS_LOAN_PAYMENT_DETAIL SET PRINCIPLE_AMOUNT = 
    (SELECT PRINCIPLE_AMOUNT - AMOUNT FROM 
    HRIS_LOAN_PAYMENT_DETAIL WHERE SNO = COUNTER-1
    AND LOAN_REQUEST_ID = P_LOAN_REQUEST_ID) 
    WHERE LOAN_REQUEST_ID = P_LOAN_REQUEST_ID
    AND SNO = COUNTER;

    END FOR;

    SELECT DISTINCT FROM_DATE, TO_DATE
    INTO V_FROM_DATE, V_TO_DATE FROM HRIS_LOAN_PAYMENT_DETAIL
    WHERE 
    SNO = (SELECT MAX(SNO) FROM HRIS_LOAN_PAYMENT_DETAIL
    WHERE LOAN_REQUEST_ID = P_LOAN_REQUEST_ID)
    AND LOAN_REQUEST_ID = P_LOAN_REQUEST_ID;

    SELECT 
    LAST_DAY(ADD_MONTHS(V_FROM_DATE,+1))
    INTO V_TO_DATE 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) INTO V_NO_OF_DAYS FROM DUMMY;

    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,
    (SELECT MAX(SNO)+1 FROM HRIS_LOAN_PAYMENT_DETAIL WHERE LOAN_REQUEST_ID = P_LOAN_REQUEST_ID),
    V_FROM_DATE,
    V_TO_DATE,
    V_AMOUNT,
    null,
    --(SELECT MONTH_NO+1 FROM HRIS_LOAN_PAYMENT_DETAIL WHERE SNO = V_SNO2 AND 
    --LOAN_REQUEST_ID = P_LOAN_REQUEST_ID),
	(SELECT PRINCIPLE_AMOUNT - AMOUNT FROM HRIS_LOAN_PAYMENT_DETAIL WHERE PAYMENT_ID IN 
    (SELECT MAX(PAYMENT_ID) FROM HRIS_LOAN_PAYMENT_DETAIL))*((V_INTEREST_RATE/100)/365*V_NO_OF_DAYS),
    (SELECT PRINCIPLE_AMOUNT - AMOUNT FROM HRIS_LOAN_PAYMENT_DETAIL WHERE PAYMENT_ID IN 
    (SELECT MAX(PAYMENT_ID) FROM HRIS_LOAN_PAYMENT_DETAIL)),
    V_NO_OF_DAYS,
    null,
    null,
    null,
    CURRENT_DATE,
    null,
    'E'
    );

    ELSE
    UPDATE HRIS_LOAN_PAYMENT_DETAIL SET AMOUNT = V_AMOUNT
    WHERE LOAN_REQUEST_ID = P_LOAN_REQUEST_ID
    AND PAYMENT_ID = P_PAYMENT_ID;

    SELECT SNO+1 INTO V_SNO1 FROM HRIS_LOAN_PAYMENT_DETAIL WHERE LOAN_REQUEST_ID = P_LOAN_REQUEST_ID
    AND PAYMENT_ID = P_PAYMENT_ID;

    SELECT MAX(SNO)-1 INTO V_SNO2 FROM HRIS_LOAN_PAYMENT_DETAIL WHERE LOAN_REQUEST_ID = P_LOAN_REQUEST_ID;

    DELETE FROM HRIS_LOAN_PAYMENT_DETAIL WHERE SNO = V_SNO2+1 AND LOAN_REQUEST_ID = P_LOAN_REQUEST_ID;

    FOR COUNTER IN V_SNO1 .. V_SNO2 DO

    UPDATE HRIS_LOAN_PAYMENT_DETAIL SET PRINCIPLE_AMOUNT = 
    (SELECT PRINCIPLE_AMOUNT - AMOUNT FROM 
    HRIS_LOAN_PAYMENT_DETAIL WHERE SNO = COUNTER-1
    AND LOAN_REQUEST_ID = P_LOAN_REQUEST_ID) 
    WHERE LOAN_REQUEST_ID = P_LOAN_REQUEST_ID
    AND SNO = COUNTER; 

    END FOR;

    END IF;
    END IF;
    END;