CREATE DEFINER=`devadmin`@`%` PROCEDURE `auto_closure_detail_new`(IN I_COMPANY_ID INT,IN I_TERMINATION_TYPE varchar(1),IN I_USER_ID varchar(10),IN I_LOAN_ID INT(10),IN LIMIT1 INT(10),IN LIMIT2 INT(10),OUT O_ERRORFLAG varchar(1),OUT O_ERRORMSG varchar(1000))
BEGIN
DECLARE V_LOAN_ID        INT(20);
DECLARE V_BUSINESS_DATE        DATE;
DECLARE O_BALANCE_PRINCIPAL DECIMAL(22,4);     
DECLARE O_OVERDUE_PRINCIPAL DECIMAL(22,4); 
DECLARE O_OVERDUE_INSTALLMENTS DECIMAL (22,4);      
DECLARE O_UNBILLED_INSTALLMENTS DECIMAL(22,4);     
DECLARE O_OTHER_DUES DECIMAL (22,4);
DECLARE O_GAP_INTEREST DECIMAL (22,4);     
DECLARE O_PREPAY_PENALTY DECIMAL (22,4);
DECLARE O_LOCKIN_PERIOD_INTEREST DECIMAL (22,4);
DECLARE O_OD_AMOUNT DECIMAL (22,4);
DECLARE O_OTHER_PAYABLES DECIMAL(22,4);      
DECLARE O_SECURE_DEPOSIT DECIMAL(22,4); 
DECLARE O_SECURE_DEPOSIT_INTEREST DECIMAL(22,4);
DECLARE O_SECURE_DEPOSIT_TDS DECIMAL(22,4);
DECLARE O_SECURE_DEPOSIT_GAP_INTEREST DECIMAL(22,4); 
DECLARE O_SECURE_DEPOSIT_GAP_TDS DECIMAL(22,4);
DECLARE O_ADVANCE_EMI_REFUNDS DECIMAL(22,4);
DECLARE O_TERMINATION_ID INT(10) ;
DECLARE V_NET_REC_PAY DECIMAL(22,4);
DECLARE V_NET_REC_PAY1 DECIMAL(22,4);
DECLARE V_LOAN_CUSTOMER_ID INT(10) ;
DECLARE V_SCHEME_ID INT(10) ;
DECLARE V_MANUAL_ADVICE_ID INT(10);
DECLARE O_TXNADVICE_ID INT(10);
DECLARE V_AUTO_CLOSE_AMOUNT_LIMIT DECIMAL(22,4);
DECLARE V_LOAN_TERM_COUNT INT(10);
DECLARE V_MANUAL_COUNT INT(10);
DECLARE PROC_PARAM VARCHAR(200);
DECLARE DONE   INT DEFAULT 0;
DECLARE V_PAY_AMOUNT DECIMAL(22,4);
DECLARE V_LOAN_NO VARCHAR(50);
DECLARE V_CHARGE_AMOUNT DECIMAL(22,4);
DECLARE V_INCOME_BOOKING  DECIMAL(22,4);
DECLARE O_REJECTRECORD INT(10);
DECLARE O_UPLOADEDRECORD INT(10);
DECLARE CLOSURE_LOAN_CUR CURSOR FOR SELECT A.LOAN_ID, A.LOAN_CUSTOMER_ID,a.loan_scheme,A.LOAN_NO
                FROM CR_LOAN_DTL A, CR_SCHEME_M B,GCD_CUSTOMER_M G  
                WHERE B.SCHEME_ID=A.LOAN_SCHEME AND G.CUSTOMER_ID=A.LOAN_CUSTOMER_ID  
                AND B.TERMINATION_ALLOWED='Y' AND A.REC_STATUS='A' AND ((A.LOAN_REPAYMENT_TYPE='I' AND A.DISBURSAL_STATUS='F') OR (A.LOAN_REPAYMENT_TYPE<>'I' AND A.DISBURSAL_STATUS='P')) 
                AND A.LOAN_ID NOT IN (SELECT LOAN_ID FROM CR_TERMINATION_DTL WHERE REC_STATUS IN ('P','F','A') ) AND ((A.LOAN_REPAYMENT_TYPE='I' 
                AND A.LOAN_MATURITY_DATE < V_BUSINESS_DATE))
                and LOAN_ID = IF(I_LOAN_ID=0, LOAN_ID, I_LOAN_ID)
                 AND a.loan_scheme in (select scheme_id from cr_closure_master_dtl where rec_status='A') 
                -- and a.LOAN_MATURITY_DATE='2020-03-07'
             -- AND a.loan_scheme in (289,290)
                order by A.LOAN_ID desc limit LIMIT1,LIMIT2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN     
    SET O_ERRORFLAG = 'E';
    SET O_ERRORMSG =  'SOME ERROR IN THE SQL EXECUTION';
    END;
SELECT PARAMETER_VALUE into V_BUSINESS_DATE FROM PARAMETER_MST WHERE PARAMETER_KEY='BUSINESS_DATE';
-- SET V_BUSINESS_DATE='2018-10-06';
-- DELETE FROM AUTO_CLOSURE_LOGG_DTL WHERE MAKER_ID=I_USER_ID;
SET O_ERRORFLAG='S';
SET O_ERRORMSG ='';
set autocommit=0;
    OPEN CLOSURE_LOAN_CUR;
    CLOSURE_LOAN_CUR_READ : LOOP 
SET DONE=0;
    FETCH CLOSURE_LOAN_CUR INTO V_LOAN_ID,V_LOAN_CUSTOMER_ID,V_SCHEME_ID,V_LOAN_NO;
                IF DONE = 1 THEN
        SET DONE = 0;
        LEAVE CLOSURE_LOAN_CUR_READ;
        END IF;
set O_ERRORFLAG='S';
IF IFNULL(O_ERRORFLAG,'E') <> 'E' THEN
        /*SELECT PARAMETER_VALUE into V_AUTO_CLOSE_AMOUNT_LIMIT  FROM PARAMETER_MST WHERE PARAMETER_KEY='AUTO_CLOSE_AMOUNT_LIMIT';*/
        SELECT tolerance_limit,income_booking into V_AUTO_CLOSE_AMOUNT_LIMIT,V_INCOME_BOOKING  FROM cr_closure_master_dtl WHERE scheme_id=V_SCHEME_ID;
            CALL EARLY_CLOSURE_DETAIL(I_COMPANY_ID,V_LOAN_ID,V_BUSINESS_DATE,O_BALANCE_PRINCIPAL,O_OVERDUE_PRINCIPAL 
            ,O_OVERDUE_INSTALLMENTS ,O_UNBILLED_INSTALLMENTS ,O_OTHER_DUES ,O_GAP_INTEREST ,O_PREPAY_PENALTY ,
            O_LOCKIN_PERIOD_INTEREST ,O_OD_AMOUNT ,O_OTHER_PAYABLES,O_SECURE_DEPOSIT ,O_SECURE_DEPOSIT_INTEREST ,
            O_SECURE_DEPOSIT_TDS ,O_SECURE_DEPOSIT_GAP_INTEREST ,O_SECURE_DEPOSIT_GAP_TDS ,O_ADVANCE_EMI_REFUNDS 
            ,O_ERRORFLAG,O_ERRORMSG );
    SET V_NET_REC_PAY = IFNULL(O_BALANCE_PRINCIPAL,0)+IFNULL(O_GAP_INTEREST,0)+IFNULL(O_PREPAY_PENALTY,0)+IFNULL(O_OTHER_DUES,0)+IFNULL(O_LOCKIN_PERIOD_INTEREST,0)+IFNULL(O_OD_AMOUNT,0)+IFNULL(O_OTHER_PAYABLES,0)+IFNULL(O_ADVANCE_EMI_REFUNDS,0)+IFNULL(O_OVERDUE_INSTALLMENTS,0);
    SET V_NET_REC_PAY1=IFNULL(O_BALANCE_PRINCIPAL,0)+IFNULL(O_OVERDUE_INSTALLMENTS,0)+IFNULL(O_GAP_INTEREST,0)+IFNULL(O_UNBILLED_INSTALLMENTS,0)+IFNULL(O_PREPAY_PENALTY,0)+IFNULL(O_OTHER_DUES,0)+IFNULL(O_LOCKIN_PERIOD_INTEREST,0)+IFNULL(O_OD_AMOUNT,0)-IFNULL(O_OTHER_PAYABLES,0)+IFNULL(O_ADVANCE_EMI_REFUNDS,0);
    select ifnull((sum(ifnull(advice_amount,0))-sum(ifnull(txn_adjusted_amount,0))),0) amount INTO V_PAY_AMOUNT from cr_txnadvice_dtl where loan_id=V_LOAN_ID and charge_code_id not in (2) and advice_type='P';
--            select V_NET_REC_PAY,    V_NET_REC_PAY1,V_PAY_AMOUNT,O_OTHER_PAYABLES;
            IF IFNULL(O_ERRORFLAG,'E') <> 'E' AND (ifnull(V_NET_REC_PAY,0)-ifnull(V_PAY_AMOUNT,0))>= 0 and IFNULL(O_OTHER_PAYABLES,0)=0 THEN
            
                SELECT COUNT(1) into V_LOAN_TERM_COUNT FROM CR_TERMINATION_DTL WHERE LOAN_ID=V_LOAN_ID AND REC_STATUS IN ('P','F');
                    IF IFNULL(V_LOAN_TERM_COUNT,0)=0 THEN 
                        IF IFNULL(V_NET_REC_PAY,0)>=0 AND IFNULL(V_NET_REC_PAY,0)<=IFNULL(V_AUTO_CLOSE_AMOUNT_LIMIT,0) THEN
                        CALL EARLY_CLOSURE_SAVE(V_LOAN_ID,'0','Maturity Closure',V_BUSINESS_DATE,I_TERMINATION_TYPE,
                        O_BALANCE_PRINCIPAL,O_SECURE_DEPOSIT,O_OVERDUE_INSTALLMENTS,O_SECURE_DEPOSIT_INTEREST,O_GAP_INTEREST,
                        O_SECURE_DEPOSIT_TDS,O_UNBILLED_INSTALLMENTS,O_SECURE_DEPOSIT_GAP_INTEREST,0.0,O_LOCKIN_PERIOD_INTEREST,O_OD_AMOUNT,
                        O_SECURE_DEPOSIT_GAP_TDS,O_OTHER_DUES,O_OTHER_PAYABLES,0.0,O_OVERDUE_PRINCIPAL,V_NET_REC_PAY,O_ADVANCE_EMI_REFUNDS,
                        'P',I_USER_ID,V_BUSINESS_DATE,O_TERMINATION_ID,O_ERRORFLAG,O_ERRORMSG);
                        
                            call WAIVE_OFF_ALLOCATOR('Y',O_TERMINATION_ID,I_USER_ID,V_BUSINESS_DATE,O_ERRORFLAG,O_ERRORMSG);
                            commit;                            
                        
                        IF IFNULL(O_ERRORFLAG,'E') <> 'E' THEN  
                        update cr_termination_dtl set rec_status='F' where loan_id=V_LOAN_ID and termination_id=O_TERMINATION_ID;
                        CALL EARLY_CLOSURE_AUTHOR(I_COMPANY_ID,V_LOAN_ID,V_BUSINESS_DATE,I_USER_ID,'A','AUTO APPROVED',O_ERRORFLAG,O_ERRORMSG);
                                IF (IFNULL(O_ERRORFLAG,'E') <> 'E') THEN
                                    commit;
                                else
                                    delete from cr_termination_dtl where loan_id=V_LOAN_ID and termination_id=O_TERMINATION_ID;
                        commit;
                        END IF;    
                    END IF;
                    END IF;
                    END IF;
                    IF IFNULL(V_LOAN_TERM_COUNT,0)>0 THEN 
                        SET O_ERRORFLAG='E';
                        SET O_ERRORMSG ='LOAN IN PROCESSING STATE(PENDING/FORWARDED)';
                    END IF ;
            ELSEIF (ifnull(V_NET_REC_PAY1,0)< 0) then                            
                            SET V_CHARGE_AMOUNT=abs(V_NET_REC_PAY1);
                            IF ((IFNULL(V_CHARGE_AMOUNT,0))<=IFNULL(V_INCOME_BOOKING,0)) and ((IFNULL(V_CHARGE_AMOUNT,0)))>0 THEN
                                
                                INSERT INTO CR_MANUAL_ADVICE_DTL (TXN_TYPE, TXN_ID, BPTYPE, BPID, MANUAL_ADVICE_DATE, CHARGE_CODE, 
                                     ORG_CHARGE_AMOUNT, ORG_TAX_AMOUNT1, ORG_TAX_AMOUNT2, ORG_TDS_AMOUNT, ADVICE_AMOUNT,
                                     TAX_RATE1, TAX_RATE2, TDS_RATE, ADVICE_TYPE, TXN_ADJUSTED_AMOUNT, AMOUNT_IN_PROCESS, 
                                     TDS_APPLICABLE, TAX_APPLICABLE, TAX_INCLUSIVE, MAKER_REMARKS, REMARKS, REC_STATUS,
                                     MAKER_ID, MAKER_DATE, AUTHOR_ID, AUTHOR_DATE)
                                VALUES ('LIM', V_LOAN_ID, 'CS', V_LOAN_CUSTOMER_ID,STR_TO_DATE(V_BUSINESS_DATE,'%Y-%m-%d') , '53',
                                (V_CHARGE_AMOUNT), 0, 0, 0, (V_CHARGE_AMOUNT),
                                     0, 0, 0, 'R', 0, 0, 
                                     'N', 'N', 'N', 'Auto Credit Period Int', 'Auto Credit Period Int', 'F',
                                     I_USER_ID, V_BUSINESS_DATE, I_USER_ID, V_BUSINESS_DATE);
                                SELECT  LAST_INSERT_ID() INTO V_MANUAL_ADVICE_ID;
                                CALL MANUAL_ADVICE_AUTHOR (1, V_MANUAL_ADVICE_ID, V_BUSINESS_DATE, I_USER_ID, 'A', 'Auto Credit Period Int', O_ERRORFLAG, O_ERRORMSG);commit;
                         
                                CALL EARLY_CLOSURE_DETAIL(I_COMPANY_ID,V_LOAN_ID,V_BUSINESS_DATE,O_BALANCE_PRINCIPAL,O_OVERDUE_PRINCIPAL 
                                ,O_OVERDUE_INSTALLMENTS ,O_UNBILLED_INSTALLMENTS ,O_OTHER_DUES ,O_GAP_INTEREST ,O_PREPAY_PENALTY ,
                                O_LOCKIN_PERIOD_INTEREST ,O_OD_AMOUNT ,O_OTHER_PAYABLES,O_SECURE_DEPOSIT ,O_SECURE_DEPOSIT_INTEREST ,
                                O_SECURE_DEPOSIT_TDS ,O_SECURE_DEPOSIT_GAP_INTEREST ,O_SECURE_DEPOSIT_GAP_TDS ,O_ADVANCE_EMI_REFUNDS 
                                ,O_ERRORFLAG,O_ERRORMSG );
                            SET V_NET_REC_PAY = IFNULL(O_BALANCE_PRINCIPAL,0)+IFNULL(O_OVERDUE_INSTALLMENTS,0)+IFNULL(O_GAP_INTEREST,0)+IFNULL(O_UNBILLED_INSTALLMENTS,0)+ifnull(O_PREPAY_PENALTY,0)+ifnull(O_OTHER_DUES,0)+ifnull(O_LOCKIN_PERIOD_INTEREST,0)+ifnull(O_OD_AMOUNT,0)
                                    -(ifnull(O_OTHER_PAYABLES,0)+ifnull(O_ADVANCE_EMI_REFUNDS,0));
                                    IF V_NET_REC_PAY=0 THEN
                                        SET V_NET_REC_PAY=O_OTHER_PAYABLES;
                                    END IF;
                                CALL EARLY_CLOSURE_SAVE(V_LOAN_ID,'0','Maturity Closure',V_BUSINESS_DATE,I_TERMINATION_TYPE,
                                O_BALANCE_PRINCIPAL,O_SECURE_DEPOSIT,O_OVERDUE_INSTALLMENTS,O_SECURE_DEPOSIT_INTEREST,O_GAP_INTEREST,
                                O_SECURE_DEPOSIT_TDS,O_UNBILLED_INSTALLMENTS,O_SECURE_DEPOSIT_GAP_INTEREST,0.0,O_LOCKIN_PERIOD_INTEREST,O_OD_AMOUNT,
                                O_SECURE_DEPOSIT_GAP_TDS,O_OTHER_DUES,O_OTHER_PAYABLES,0.0,O_OVERDUE_PRINCIPAL,V_NET_REC_PAY,O_ADVANCE_EMI_REFUNDS,
                                'P',I_USER_ID,V_BUSINESS_DATE,O_TERMINATION_ID,O_ERRORFLAG,O_ERRORMSG);
                                IF IFNULL(O_ERRORFLAG,'E') <> 'E' THEN  
                                -- select 'EARLY_CLOSURE_AUTHOR',V_LOAN_ID;
                                update cr_termination_dtl set rec_status='F' where loan_id=V_LOAN_ID and termination_id=O_TERMINATION_ID;
                                CALL EARLY_CLOSURE_AUTHOR(I_COMPANY_ID,V_LOAN_ID,V_BUSINESS_DATE,I_USER_ID,'A','AUTO APPROVED',O_ERRORFLAG,O_ERRORMSG);
                                IF (IFNULL(O_ERRORFLAG,'E') <> 'E') THEN
                                    commit;
                                else
                                    delete from cr_termination_dtl where loan_id=V_LOAN_ID and termination_id=O_TERMINATION_ID;
                                    commit;
                                END IF;                    
                        END IF;    
                            END IF;
                    
            END IF;
                                            
        IF IFNULL(V_NET_REC_PAY,0)>IFNULL(V_AUTO_CLOSE_AMOUNT_LIMIT,0) THEN                                           
            SET O_ERRORFLAG='E';
            SET O_ERRORMSG ='MANUAL ADVICE- NET REC/PAY AMOUNT GREATER THAN AUTO LOAN CLOSE LIMIT';
        ELSEIF IFNULL(V_NET_REC_PAY1,0)>IFNULL(V_AUTO_CLOSE_AMOUNT_LIMIT,0) THEN                                           
            SET O_ERRORFLAG='E';
            SET O_ERRORMSG ='MANUAL ADVICE- NET REC/PAY AMOUNT GREATER THAN AUTO LOAN CLOSE LIMIT';
        END IF;
        
        
    END IF;
set O_ERRORFLAG='S';
    END LOOP; 
    CLOSE CLOSURE_LOAN_CUR;
    IF O_ERRORFLAG = 'E'  THEN
    SET PROC_PARAM=CONCAT('PROCEDURE_PARAMETER: ',I_COMPANY_ID,',',0,',',V_BUSINESS_DATE);
    SET O_ERRORMSG=CONCAT('AUTO CLOSURE DETAIL-',O_ERRORMSG);
    END IF;
END