CREATE DEFINER=`devadmin`@`%` PROCEDURE `LOAN_AUTHORIZATION`(IN I_COMPANY_ID INT, IN I_LOAN_ID INT(10),
							  IN I_CURR_DATE DATE, IN I_USER_ID VARCHAR(10),
							  IN I_STATUS VARCHAR(1), IN I_REMARKS VARCHAR(500),
							  OUT O_ERROR_FLAG VARCHAR(1), OUT O_ERROR_MSG VARCHAR(100))
BEGIN
DECLARE V_DEAL_ID			INT(10);
DECLARE V_DRF_STATUS			VARCHAR(1);
DECLARE V_LOAN_BRANCH			INT(10);
DECLARE V_LOAN_PRODUCT			VARCHAR(10);
DECLARE V_LOAN_SCHEME			INT(10);
DECLARE V_LOAN_CUSTOMER_ID		INT(10);
DECLARE V_LOAN_CUSTOMER_NAME		VARCHAR(50);
DECLARE V_NPA_FLAG			VARCHAR(10);
DECLARE V_LOAN_AMOUNT			DECIMAL(22,4);
DECLARE V_LOAN_INSTALLMENT_MODE		VARCHAR(1);
DECLARE V_LOAN_REPAYMENT_TYPE		VARCHAR(1);
DECLARE V_ADVANCE_AMOUNT		DECIMAL(22,4);
DECLARE V_TOTAL_INTEREST                DECIMAL(22,4);
DECLARE I_DEPARTMENT			INT(10);
DECLARE V_CHARGE_CODE			INT(10);
DECLARE V_CHARGE_CODE_I			INT(10);
DECLARE V_CHARGE_ID			INT(10);
DECLARE V_CHARGE_TYPE			VARCHAR(1);
DECLARE V_CHARGE_AMOUNT			DECIMAL(22,4) DEFAULT 0;
DECLARE V_CHARGE_AMOUNT_I		DECIMAL(22,4) DEFAULT 0;
DECLARE V_BP_TYPE			VARCHAR(3);
DECLARE V_BP_ID 			INT(10);
DECLARE V_BP_NAME			VARCHAR(50);
DECLARE V_CHARGE_METHOD			VARCHAR(1);
DECLARE V_TAX_RATE1			DECIMAL(7,4) DEFAULT 0;
DECLARE V_TAX_RATE2			DECIMAL(7,4) DEFAULT 0;
DECLARE V_TDS_RATE			DECIMAL(7,4) DEFAULT 0;
DECLARE V_TDS_AMOUNT			DECIMAL(22,4) DEFAULT 0;
DECLARE V_TAX_AMOUNT1			DECIMAL(22,4) DEFAULT 0;
DECLARE V_TAX_AMOUNT2			DECIMAL(22,4) DEFAULT 0;
DECLARE TOTAL_TAX_AMOUNT1 		DECIMAL(22,4) DEFAULT 0;
DECLARE TOTAL_TAX_AMOUNT2 		DECIMAL(22,4) DEFAULT 0;
DECLARE TOTAL_TDS_AMOUNT		DECIMAL(22,4) DEFAULT 0;
DECLARE PREV_BP_TYPE			VARCHAR(3);
DECLARE PREV_BP_ID 			INT(10);
DECLARE V_NAME				VARCHAR(50);
DECLARE V_ASSET_MANUFATURER		INT(10);
DECLARE V_ASSET_SUPPLIER		INT(10);
DECLARE V_ASSET_ID 			INT(10);
DECLARE V_ACCOUNTING_FLAG 		VARCHAR(1) DEFAULT 'N';
DECLARE V_CHARGE_STR			VARCHAR(1000) DEFAULT '';
DECLARE V_VOUCHER_STR			VARCHAR(500);
DECLARE V_CHARGE_AMT			DECIMAL(22,4) DEFAULT 0;
DECLARE V_VOUCHER_NO			VARCHAR(20);
DECLARE V_LINK_LOAN_FLAG		VARCHAR(1) DEFAULT 'N';
DECLARE V_ENABLE_INVOICE_PROCESS 	VARCHAR(1) DEFAULT 'N';
DECLARE V_HOLD_CT                       INT;
DECLARE V_HOUR                          INT;
DECLARE V_DRF_ID                        INT;
DECLARE V_LOAN_MAKER_DATE               DATE;
	DECLARE V_IMD_PAYABLE_AMOUNT		DECIMAL(22,4) DEFAULT 0;
		DECLARE	V_DEAL_APPROVAL_DATE		DATE;
DECLARE V_COUNT		INT(3) DEFAULT 0;
	DECLARE V_KNOCKOFF_ID				INT(10);
	DECLARE V_TOTAL_PAYABLE				DECIMAL(22,4);
	DECLARE V_TOTAL_RECEIVABLE			DECIMAL(22,4);
	DECLARE V_TOTAL_KNOCKOFF_AMOUNT			DECIMAL(22,4);
	DECLARE V_KNOCKOFF_AMOUNT			DECIMAL(22,4);
	DECLARE V_TOTAL_BALANCE_AMOUNT			DECIMAL(22,4);
	DECLARE V_TXNADVICE_ID				INT(10);
   DECLARE V_CHARGE_CODE_ID			INT(10);
	DECLARE V_ADVICE_TYPE				VARCHAR(1);
	DECLARE V_ADVICE_AMOUNT				DECIMAL(22,4);
	DECLARE V_TXN_ADJUSTED_AMOUNT			DECIMAL(22,4);
	DECLARE V_AMOUNT_IN_PROCESS			DECIMAL(22,4);
	DECLARE V_BALANCE_AMOUNT			DECIMAL(22,4);
	DECLARE V_SEQ_NO				INT(3) DEFAULT 0;
DECLARE V_IMD_AMOUNT				DECIMAL(22,4);
DECLARE V_RECOVERY_TYPE			VARCHAR(1);
DECLARE V_LOAN_INSTALLMENT_TYPE		VARCHAR(1);
DECLARE V_LOAN_TENURE			INT(3);
DECLARE V_FREQ_MONTHS			INT(2);
DECLARE V_NO_OF_INSTL			INT(3);
DECLARE V_LOAN_RATE_TYPE 		VARCHAR(1);
DECLARE V_LOAN_FINAL_RATE		DECIMAL(7,4); 
DECLARE V_RV_AMOUNT			DECIMAL(22,4) DEFAULT 0; 
DECLARE V_INSTL_ROUND_TYPE 		VARCHAR(1);
DECLARE V_INSTL_ROUNDING		INT(1);		
DECLARE V_LOAN_ID			INT(10); 
DECLARE V_FROM_INSTL_NO			INT(3); 
DECLARE V_TO_INSTL_NO			INT(3); 
DECLARE V_RECOVERY_PERCENT		DECIMAL(5,2) DEFAULT 0;
DECLARE V_PRINCIPAL_AMOUNT		DECIMAL(22,4) DEFAULT 0;
DECLARE V_INSTALLMENT_AMOUNT		DECIMAL(22,4) DEFAULT 0;
DECLARE V_INSTALLMENT_AMOUNT1		DECIMAL(22,4) DEFAULT 0;
DECLARE V_UPFRONT_INSTL_DIFF 		DECIMAL(22,4) DEFAULT 0;
DECLARE V_TOTAL_RECOVERY		DECIMAL(22,4) DEFAULT 0;
DECLARE V_LOAN_AMOUNT1			DECIMAL(22,4) DEFAULT 0;
DECLARE DONE 		INT(3) DEFAULT 0;
DECLARE V_BP_TYPE_DTL			VARCHAR(5);
DECLARE V_PERCENTAGE			DECIMAL(22,4);
DECLARE V_PARTNER_ID			INT(10);
DECLARE V_LOAN_ATM_PART			DECIMAL(22,4) DEFAULT 0;
DECLARE V_LOAN_INT_PART			DECIMAL(22,4) DEFAULT 0;
DECLARE V_PARTNER_NAME   VARCHAR(300);
DECLARE O_ERRORFLAG	VARCHAR(1);
DECLARE O_ERRORMSG	VARCHAR(100);
	DECLARE V_TAX2_PERCENTAGE		DECIMAL(5,2) DEFAULT 0;
	DECLARE V_TAX3_PERCENTAGE		DECIMAL(5,2) DEFAULT 0;
	DECLARE V_KRISHI_TAX_APPLICABLE_OR_NOT   VARCHAR(1) DEFAULT 'Y';
	DECLARE V_STATE_CODE       	INT(10);
	DECLARE V_PARTY_TYPE		VARCHAR(10);
	DECLARE V_PARTY_CODE 		INT(10);
	DECLARE V_BRANCH_STATE_CODE	 INT(11);
	DECLARE V_CHARGE_DUE_RECEIPT	VARCHAR(1);
	DECLARE V_UNION_TERRITORY	VARCHAR(1);
	DECLARE V_TAX1_CODE , V_TAX2_CODE, V_TAX3_CODE	INT;
	DECLARE V_TAX1_RATE , V_TAX2_RATE, V_TAX3_RATE	DECIMAL(10,2);
	DECLARE V_TAX1_AMOUNT , V_TAX2_AMOUNT, V_TAX3_AMOUNT	DECIMAL(22,4);
	DECLARE V_TAX_RECORD_TYPE	VARCHAR(50);
	DECLARE V_INVOICE_NO, V_COMMON_INVOICE_NO	VARCHAR(16);
	DECLARE V_BILL_OF_SUPPLY_NO	VARCHAR(16);
	DECLARE V_CHARGE_TXNADVICE_ID	INT;
DECLARE V_LOAN_LOAN_AMOUNT	DECIMAL(22,4) DEFAULT 0;
		DECLARE V_KNOCKOFF_RECIEVABLE_AMOUNT,V_KNOCKOFF_PAAYABLE_AMOUNT	DECIMAL(22,4);
DECLARE CHARGES_CUR CURSOR FOR SELECT DEAL_CHARGE_ID, DEAL_CHARGE_CODE, DEAL_CHARGE_TYPE, DEAL_CHARGE_METHOD,
                               DEAL_CHARGE_BP_TYPE, DEAL_CHARGE_BP_ID, 
                               IFNULL(DEAL_CHARGE_FINAL_AMOUNT,0), IFNULL(DEAL_CHARGE_TDS_RATE,0), 
                               IFNULL(DEAL_CHARGE_TAX_RATE1,0), IFNULL(DEAL_CHARGE_TAX_RATE2,0),
                               IFNULL(DEAL_CHARGE_TDS_AMOUNT,0), IFNULL(DEAL_CHARGE_TAX_AMOUNT1,0), IFNULL(DEAL_CHARGE_TAX_AMOUNT2,0)
                          FROM CR_TXNCHARGES_DTL
                         WHERE TXN_TYPE = 'LIM'                  
                           AND TXN_ID = I_LOAN_ID
                           AND IFNULL(DEAL_CHARGE_FINAL_AMOUNT,0) > 0
                           AND DEAL_CHARGE_APPLICATION_STAGE = 'LIM'
                      ORDER BY DEAL_CHARGE_BP_TYPE, DEAL_CHARGE_BP_ID;
DECLARE ASSET_CUR CURSOR FOR SELECT A.ASSET_ID, A.ASSET_MANUFATURER, A.ASSET_SUPPLIER 
                               FROM CR_ASSET_COLLATERAL_M A, CR_LOAN_COLLATERAL_M B
                              WHERE B.LOAN_ID = I_LOAN_ID
                                AND A.ASSET_ID = B.ASSETID 
                                AND A.ASSET_TYPE='ASSET';
DECLARE TXNADVICE_CUR_P CURSOR FOR SELECT TXNADVICE_ID, CHARGE_CODE_ID, ADVICE_TYPE, IFNULL(ADVICE_AMOUNT,0), IFNULL(TXN_ADJUSTED_AMOUNT,0), IFNULL(AMOUNT_IN_PROCESS,0), 
						(IFNULL(ADVICE_AMOUNT,0) - IFNULL(TXN_ADJUSTED_AMOUNT,0) - IFNULL(AMOUNT_IN_PROCESS,0))
					   FROM CR_TXNADVICE_DTL
					  WHERE DEAL_ID = V_DEAL_ID
						 AND LOAN_ID = 0
					    AND ADVICE_TYPE = 'P'
					    AND BP_TYPE = 'CS'
						 AND CHARGE_CODE_ID = 4
					    AND (IFNULL(ADVICE_AMOUNT,0) - IFNULL(TXN_ADJUSTED_AMOUNT,0) - IFNULL(AMOUNT_IN_PROCESS,0)) > 0
					    AND REC_STATUS = 'A'
				       ORDER BY ADVICE_DATE;
DECLARE TXNADVICE_CUR_R CURSOR FOR SELECT TXNADVICE_ID, CHARGE_CODE_ID, ADVICE_TYPE, IFNULL(ADVICE_AMOUNT,0), IFNULL(TXN_ADJUSTED_AMOUNT,0), IFNULL(AMOUNT_IN_PROCESS,0), 
						(IFNULL(ADVICE_AMOUNT,0) - IFNULL(TXN_ADJUSTED_AMOUNT,0) - IFNULL(AMOUNT_IN_PROCESS,0))
					   FROM CR_TXNADVICE_DTL
					  WHERE LOAN_ID = I_LOAN_ID
					    AND ADVICE_TYPE = 'R'
					    AND BP_TYPE = 'CS'
					    AND (IFNULL(ADVICE_AMOUNT,0) - IFNULL(TXN_ADJUSTED_AMOUNT,0) - IFNULL(AMOUNT_IN_PROCESS,0)) > 0
					    AND REC_STATUS = 'A'
					ORDER BY ADVICE_DATE, TXNADVICE_ID;
DECLARE INSTL_PLAN_CUR CURSOR FOR SELECT LOAN_ID, SEQ_NO, FROM_INSTL_NO, TO_INSTL_NO, IFNULL(RECOVERY_PERCENT,0), IFNULL(PRINCIPAL_AMOUNT,0), IFNULL(INSTALLMENT_AMOUNT,0)
                                  FROM CR_INSTALLMENT_PLAN
                                 WHERE LOAN_ID = I_LOAN_ID
                                 ORDER BY LOAN_ID, SEQ_NO;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1;
/*DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  
  SET O_ERROR_FLAG = 'E';
  SET O_ERROR_MSG = 'LOAN AUTHORIZATION---UNKNOWN EXCEPTION IN SQL EXECUTION, CONTACT SYSTEM ADMINISTRATOR...';
END;*/
DECLARE EXIT HANDLER FOR 1205
BEGIN
  
  SET O_ERROR_FLAG = 'E';
  SET O_ERROR_MSG = 'LOAN AUTHORIZATION---LOAN RECORD IS LOCKED BY SOME OTHER USER. TRY AFTER SOME TIME';
END;
SET O_ERROR_FLAG = 'S';
SET O_ERROR_MSG = '';
	SELECT ENABLE_INVOICE_PROCESS INTO V_ENABLE_INVOICE_PROCESS
	FROM CR_LOAN_DTL CLD
	JOIN CR_PRODUCT_M CPM ON(CLD.LOAN_PRODUCT=CPM.PRODUCT_ID)
	WHERE CLD.LOAN_ID=I_LOAN_ID;
	
	IF V_ENABLE_INVOICE_PROCESS='Y' AND O_ERROR_FLAG='S' THEN
		SET V_DRF_STATUS='N';
		SET V_DRF_ID=0;
		SELECT DRF_ID INTO V_DRF_ID FROM CR_LOAN_DTL WHERE LOAN_ID=I_LOAN_ID;
		IF DONE = 1 THEN
			SET DONE = 0;   
			SET O_ERROR_FLAG = 'E';
			SET O_ERROR_MSG = 'DRF IS NOT ATTACHED WITH THIS LOAN';
		END IF;
		IF O_ERROR_FLAG = 'S' THEN
			SELECT REC_STATUS INTO V_DRF_STATUS FROM CR_UNPAID_DRF_DTL WHERE DRF_ID=V_DRF_ID;
			IF DONE = 1 THEN
				SET DONE = 0;   
				SET O_ERROR_FLAG = 'E';
				SET O_ERROR_MSG = 'ATTACHED DRF NOT FOUND';
			END IF;
		END IF; 
		IF I_STATUS IN('A','X') AND O_ERROR_FLAG = 'S' AND V_DRF_STATUS='E' THEN
			SET DONE = 0;   
			SET O_ERROR_FLAG = 'E';
			SET O_ERROR_MSG = 'ATTACHED DRF IS EXPIRED, ONLY SEND BACK OPERATION IS ALLOWED';
		END IF; 
	END IF;
	IF I_STATUS = 'A' AND O_ERROR_FLAG = 'S'  THEN
			SELECT LOAN_LOAN_AMOUNT INTO V_LOAN_LOAN_AMOUNT FROM CR_LOAN_DTL WHERE LOAN_ID=I_LOAN_ID;
			SELECT COUNT(1) INTO V_COUNT FROM CR_DEAL_LOAN_DTL WHERE DEAL_ID =(SELECT LOAN_DEAL_ID  FROM CR_LOAN_DTL WHERE LOAN_ID = I_LOAN_ID) AND DEAL_SANCTION_AMOUNT>=(IFNULL(DEAL_UTILIZED_AMOUNT,0)+IFNULL(V_LOAN_LOAN_AMOUNT,0));
			
			
			IF V_COUNT = 0 THEN	
				SET DONE = 0;   
				SET O_ERROR_FLAG = 'E';
				SET O_ERROR_MSG = 'DEAL UTILIZED AMOUNT SHOULD NOT BE GREATER THAN DEAL SANCTION AMOUNT';
			END IF ;
	END IF;
	IF O_ERROR_FLAG = 'S' THEN
		SELECT LOAN_DEAL_ID, LOAN_BRANCH, LOAN_PRODUCT, LOAN_SCHEME, LOAN_CUSTOMER_ID,CUSTOMER_NAME, 
		IFNULL(NPA_FLAG, 'REGULAR'), LOAN_LOAN_AMOUNT, LOAN_INSTALLMENT_MODE, IFNULL(LOAN_REPAYMENT_TYPE, 'I'),A.MAKER_DATE,
		LOAN_INSTALLMENT_TYPE, LOAN_TENURE, GET_FREQ_MONTHS(LOAN_REPAYMENT_FREQ), LOAN_NO_OF_INSTALLMENT, LOAN_RATE_TYPE, LOAN_FINAL_RATE, IFNULL(UPFRONT_ROUNDING_AMOUNT,0)
		INTO V_DEAL_ID, V_LOAN_BRANCH, V_LOAN_PRODUCT, V_LOAN_SCHEME, V_LOAN_CUSTOMER_ID, V_LOAN_CUSTOMER_NAME, 
		V_NPA_FLAG, V_LOAN_AMOUNT, V_LOAN_INSTALLMENT_MODE, V_LOAN_REPAYMENT_TYPE,V_LOAN_MAKER_DATE,
		V_LOAN_INSTALLMENT_TYPE, V_LOAN_TENURE, V_FREQ_MONTHS, V_NO_OF_INSTL, V_LOAN_RATE_TYPE, V_LOAN_FINAL_RATE, V_UPFRONT_INSTL_DIFF
		FROM CR_LOAN_DTL A, GCD_CUSTOMER_M B
		WHERE LOAN_ID = I_LOAN_ID
		AND A.LOAN_CUSTOMER_ID = B.CUSTOMER_ID
		AND A.REC_STATUS = 'F' FOR UPDATE;
		IF DONE = 1 THEN
			SET DONE = 0;   
			SET O_ERROR_FLAG = 'E';
			SET O_ERROR_MSG = 'LOAN DETAILS NOT FOUND';
		END IF;
	END IF;
	IF O_ERROR_FLAG = 'S' THEN
		SELECT INSTL_ROUND_TYPE, INSTL_ROUNDING
		INTO V_INSTL_ROUND_TYPE, V_INSTL_ROUNDING
		FROM CR_PRODUCT_M
		WHERE PRODUCT_ID = V_LOAN_PRODUCT;
		IF DONE = 1 THEN
			SET DONE = 0;   
			SET O_ERROR_FLAG = 'E';
			SET O_ERROR_MSG = 'LOAN PRODUCT NOT FOUND';
		END IF;
	END IF;
	IF O_ERROR_FLAG = 'S' THEN
		SELECT PARAMETER_VALUE INTO I_DEPARTMENT
		FROM PARAMETER_MST
		WHERE PARAMETER_KEY = 'CMDEPARTMENT';
		IF DONE = 1 THEN
			SET DONE = 0;
			SET I_DEPARTMENT = '';
		END IF;
		SELECT COUNT(*) INTO V_COUNT
		FROM CR_SCHEME_ACCOUNTING_DTL
		WHERE SCHEME_ID = V_LOAN_SCHEME
		AND STAGE_ID = 'LIA'
		AND ACCOUNTING_FLAG = 'Y';
		IF V_COUNT > 0 THEN
			SET V_ACCOUNTING_FLAG = 'Y';
		ELSE
			SET V_ACCOUNTING_FLAG = 'N';
		END IF;
		UPDATE CR_LOAN_DTL
		SET REC_STATUS 	  = I_STATUS,
       LOAN_APPROVAL_DATE = I_CURR_DATE,
       DISBURSAL_STATUS   = 'N',
       NPA_FLAG		  = 'REGULAR',
       LOAN_DPD		  = 0,
       LOAN_DPD_STRING    = '',
       LAST_DPD_CALC_DATE = '',
       LAST_PRE_EMI_DATE  = '',
       LAST_ACCRUAL_DATE  = '',
       LAST_LPP_DATE	  = '',
       LAST_BILLING_DATE  = '',
       LOAN_BALANCE_PRINCIPAL  = 0,
       LOAN_OVERDUE_PRINCIPAL  = 0,
       LOAN_RECEIVED_PRINCIPAL = 0,
       LOAN_OVERDUE_INSTL_NUM  = 0,
       LOAN_OVERDUE_AMOUNT     = 0,
       LOAN_BILLED_PRINCIPAL   = 0,
       LOAN_BILLED_INSTL_NUM   = 0,
       LOAN_BILLED_INSTL_AMOUNT = 0,
       LOAN_RECEIVED_INSTL_AMOUNT = 0,
       LOAN_BALANCE_INSTL_AMOUNT = 0,
       LOAN_REMARKS = I_REMARKS ,
       AUTHOR_ID 	  = I_USER_ID,
       AUTHOR_DATE 	  = I_CURR_DATE
 WHERE LOAN_ID = I_LOAN_ID;
UPDATE CR_SD_M
   SET REC_STATUS = I_STATUS,
       AUTHOR_ID = I_USER_ID,
       AUTHOR_DATE = I_CURR_DATE
 WHERE TXN_ID = I_LOAN_ID
   AND TXN_TYPE='LIM';
UPDATE CR_INSTALLMENT_PLAN
   SET REC_STATUS = I_STATUS,
       AUTHOR_ID = I_USER_ID,
       AUTHOR_DATE = I_CURR_DATE
 WHERE LOAN_ID = I_LOAN_ID;
UPDATE CR_REPAYSCH_DTL
   SET REC_STATUS = I_STATUS,
       AUTHOR_ID = I_USER_ID,
       AUTHOR_DATE = I_CURR_DATE
 WHERE LOAN_ID = I_LOAN_ID;
UPDATE CR_LOAN_COLLATERAL_M
   SET REC_STATUS = I_STATUS,
       AUTHOR_ID = I_USER_ID,
       AUTHOR_DATE = I_CURR_DATE
 WHERE LOAN_ID = I_LOAN_ID; 
UPDATE CR_DOCUMENT_DTL
   SET REC_STATUS = I_STATUS
 WHERE TXN_TYPE = 'LIM'
   AND TXNID = I_LOAN_ID
   AND STAGE_ID = 'PRD';
UPDATE CR_TXNCHARGES_DTL
   SET REC_STATUS = I_STATUS
 WHERE TXN_TYPE = 'LIM'
   AND TXN_ID = I_LOAN_ID;
	UPDATE CR_LOAN_CHARGE_PLAN_DTL 
	SET REC_STATUS='A'
	WHERE LOAN_ID=I_LOAN_ID;
IF I_STATUS = 'A' AND IFNULL(O_ERROR_FLAG,'S')='S' THEN 
SELECT COUNT(*) INTO V_COUNT
        FROM BUSINESS_PARTNER_VIEW
       WHERE LOAN_ID = I_LOAN_ID
         AND BP_TYPE = 'CS'
         AND BP_ID = V_LOAN_CUSTOMER_ID;
	IF IFNULL(V_COUNT,0) = 0 AND IFNULL(O_ERROR_FLAG,'S')='S' THEN
	    INSERT INTO BUSINESS_PARTNER_VIEW (DEAL_ID, LOAN_ID, BP_TYPE, BP_ID, BP_NAME) 
	    VALUES(V_DEAL_ID, I_LOAN_ID, 'CS', V_LOAN_CUSTOMER_ID, V_LOAN_CUSTOMER_NAME);
	END IF;
DELETE FROM BUSINESS_PARTNER_VIEW WHERE LOAN_ID = I_LOAN_ID AND BP_TYPE IN ('MF', 'SU');
OPEN ASSET_CUR;
ASSET_CUR_READ : LOOP
   FETCH ASSET_CUR INTO V_ASSET_ID, V_ASSET_MANUFATURER, V_ASSET_SUPPLIER;
   IF DONE = 1 THEN
      SET DONE = 0;
      LEAVE ASSET_CUR_READ;
   END IF;
   IF V_ASSET_MANUFATURER IS NOT NULL THEN
      SELECT DEALER_DESC INTO V_NAME
        FROM CR_DSA_DEALER_M
       WHERE DEALER_ID = V_ASSET_MANUFATURER
         AND BP_TYPE = 'MF'
         AND REC_STATUS = 'A';
      SELECT COUNT(*) INTO V_COUNT
        FROM BUSINESS_PARTNER_VIEW
       WHERE LOAN_ID = I_LOAN_ID
         AND BP_TYPE = 'MF'
         AND BP_ID = V_ASSET_MANUFATURER;
      IF IFNULL(V_COUNT,0) = 0 AND IFNULL(O_ERROR_FLAG,'S')='S' THEN
         INSERT INTO BUSINESS_PARTNER_VIEW (DEAL_ID, LOAN_ID, BP_TYPE, BP_ID, BP_NAME) 
                                    VALUES(V_DEAL_ID, I_LOAN_ID, 'MF', V_ASSET_MANUFATURER, V_NAME);
      END IF;
   END IF;
   IF V_ASSET_SUPPLIER IS NOT NULL THEN
      SELECT DEALER_DESC INTO V_NAME
        FROM CR_DSA_DEALER_M
       WHERE DEALER_ID = V_ASSET_SUPPLIER
         AND BP_TYPE = 'SU'
         AND REC_STATUS = 'A';
      SELECT COUNT(*) INTO V_COUNT
        FROM BUSINESS_PARTNER_VIEW
       WHERE LOAN_ID = I_LOAN_ID
         AND BP_TYPE = 'SU'
         AND BP_ID = V_ASSET_SUPPLIER;
      IF IFNULL(V_COUNT,0) = 0 AND IFNULL(O_ERROR_FLAG,'S')='S' THEN
         INSERT INTO BUSINESS_PARTNER_VIEW (DEAL_ID, LOAN_ID, BP_TYPE, BP_ID, BP_NAME) 
                                     VALUES(V_DEAL_ID, I_LOAN_ID, 'SU', V_ASSET_SUPPLIER, V_NAME);
      END IF;
   END IF;   
END LOOP;
CLOSE ASSET_CUR;
IF I_STATUS = 'A' AND IFNULL(O_ERROR_FLAG,'S')='S' THEN
IF IFNULL(V_LOAN_REPAYMENT_TYPE, 'I') = 'I' THEN
IF IFNULL(V_UPFRONT_INSTL_DIFF,0) > 0 THEN
   SELECT MIN(CHARGE_ID) INTO V_CHARGE_ID
     FROM COM_CHARGES_M
    WHERE CHARGE_CODE = 52
      AND REC_STATUS = 'A'; 
   SELECT COUNT(*) INTO V_COUNT
     FROM CR_TXNCHARGES_DTL A
    WHERE TXN_TYPE 		= 'LIM'
      AND TXN_ID   		= I_LOAN_ID
      AND DEAL_CHARGE_BP_TYPE	= 'CS'
      AND DEAL_CHARGE_BP_ID	= V_LOAN_CUSTOMER_ID
      AND DEAL_CHARGE_CODE	= 52;
   IF IFNULL(V_COUNT,0) > 0 THEN
      UPDATE CR_TXNCHARGES_DTL
         SET DEAL_CHARGE_METHOD			= 'F',
             DEAL_CHARGE_CALCULATED_AMOUNT 	= V_UPFRONT_INSTL_DIFF,
             DEAL_CHARGE_FINAL_AMOUNT 		= V_UPFRONT_INSTL_DIFF,
             DEAL_CHARGE_TDS_AMOUNT 		= 0,
             DEAL_CHARGE_NET_AMOUNT 		= V_UPFRONT_INSTL_DIFF,
             DEAL_CHARGE_TAX_APPLICABLE 	= 'N',
             DEAL_CHARGE_TAX_INCLUSIVE		= 'N',
             DEAL_CHARGE_TAX_AMOUNT1		= 0,
             DEAL_CHARGE_TAX_AMOUNT2 		= 0,
             DEAL_CHARGE_CALCULATED_ON		= '',
             DEAL_CHARGE_SLAB_ON		= '',
             DEAL_CHARGE_TDS_APPLICABLE		= 'N',
             DEAL_CHARGE_TDS_RATE		= 0,
             DEAL_CHARGE_TAX_RATE1		= 0,
             DEAL_CHARGE_TAX_RATE2		= 0,
             DEAL_MIN_CHARGE_METHOD		= 'F',
             DEAL_CHARGE_MIN_CHARGE_AMOUNT	= 0
       WHERE TXN_TYPE 		= 'LIM'
         AND TXN_ID   		= I_LOAN_ID
         AND DEAL_CHARGE_BP_TYPE= 'CS'
         AND DEAL_CHARGE_BP_ID	= V_LOAN_CUSTOMER_ID
         AND DEAL_CHARGE_CODE	= 52;
   ELSE
      INSERT INTO CR_TXNCHARGES_DTL(TXN_TYPE, TXN_ID, DEAL_CHARGE_ID, DEAL_CHARGE_CODE, DEAL_CHARGE_APPLICATION_STAGE,
                                    DEAL_CHARGE_TYPE, DEAL_CHARGE_METHOD, DEAL_CHARGE_BP_TYPE, DEAL_CHARGE_BP_ID, 
                                    DEAL_CHARGE_CALCULATED_AMOUNT, DEAL_CHARGE_FINAL_AMOUNT, DEAL_CHARGE_TDS_AMOUNT, DEAL_CHARGE_NET_AMOUNT, 
                                    DEAL_CHARGE_TAX_APPLICABLE, DEAL_CHARGE_TAX_INCLUSIVE,DEAL_CHARGE_TAX_AMOUNT1, DEAL_CHARGE_TAX_AMOUNT2,REC_STATUS,
                                    DEAL_CHARGE_CALCULATED_ON, DEAL_CHARGE_SLAB_ON, DEAL_CHARGE_TDS_APPLICABLE, DEAL_CHARGE_TDS_RATE,
                                    DEAL_CHARGE_TAX_RATE1, DEAL_CHARGE_TAX_RATE2, DEAL_CHARGE_MIN_CHARGE_AMOUNT, DEAL_MIN_CHARGE_METHOD)
                             VALUES('LIM', I_LOAN_ID, V_CHARGE_ID, 52, 'LIM', 
                                    'R', 'F', 'CS', V_LOAN_CUSTOMER_ID, 
                                    V_UPFRONT_INSTL_DIFF, V_UPFRONT_INSTL_DIFF, 0, V_UPFRONT_INSTL_DIFF, 
                                    'N', 'N',  0, 0,'A',
                                    '', '', 'N', 0, 
                                    0, 0, 0, 'F');
   END IF;
END IF;
SELECT SUM(IFNULL(INSTL_AMOUNT,0) + IFNULL(OTHER_CHARGES,0)) INTO V_ADVANCE_AMOUNT
  FROM CR_REPAYSCH_DTL
 WHERE LOAN_ID = I_LOAN_ID
   AND ((ADV_FLAG = 'Y') OR (V_LOAN_INSTALLMENT_MODE = 'A' AND INSTL_NO = 1))
   AND REC_STATUS = 'A';
IF IFNULL(V_ADVANCE_AMOUNT,0) > 0 THEN
   SELECT MIN(CHARGE_ID) INTO V_CHARGE_ID
     FROM COM_CHARGES_M
    WHERE CHARGE_CODE = 27
      AND REC_STATUS = 'A'; 
   SELECT COUNT(*) INTO V_COUNT
     FROM CR_TXNCHARGES_DTL A
    WHERE TXN_TYPE 		= 'LIM'
      AND TXN_ID   		= I_LOAN_ID
      AND DEAL_CHARGE_BP_TYPE	= 'CS'
      AND DEAL_CHARGE_BP_ID	= V_LOAN_CUSTOMER_ID
      AND DEAL_CHARGE_CODE	= 27;
   IF IFNULL(V_COUNT,0) > 0 AND IFNULL(O_ERROR_FLAG,'S')='S' THEN
      UPDATE CR_TXNCHARGES_DTL
         SET DEAL_CHARGE_METHOD			= 'F',
             DEAL_CHARGE_CALCULATED_AMOUNT 	= V_ADVANCE_AMOUNT,
             DEAL_CHARGE_FINAL_AMOUNT 		= V_ADVANCE_AMOUNT,
             DEAL_CHARGE_TDS_AMOUNT 		= 0,
             DEAL_CHARGE_NET_AMOUNT 		= V_ADVANCE_AMOUNT,
             DEAL_CHARGE_TAX_APPLICABLE 	= 'N',
             DEAL_CHARGE_TAX_INCLUSIVE		= 'N',
             DEAL_CHARGE_TAX_AMOUNT1		= 0,
             DEAL_CHARGE_TAX_AMOUNT2 		= 0,
             DEAL_CHARGE_CALCULATED_ON		= '',
             DEAL_CHARGE_SLAB_ON		= '',
             DEAL_CHARGE_TDS_APPLICABLE		= 'N',
             DEAL_CHARGE_TDS_RATE		= 0,
             DEAL_CHARGE_TAX_RATE1		= 0,
             DEAL_CHARGE_TAX_RATE2		= 0,
             DEAL_MIN_CHARGE_METHOD		= 'F',
             DEAL_CHARGE_MIN_CHARGE_AMOUNT	= 0
       WHERE TXN_TYPE 		= 'LIM'
         AND TXN_ID   		= I_LOAN_ID
         AND DEAL_CHARGE_BP_TYPE= 'CS'
         AND DEAL_CHARGE_BP_ID	= V_LOAN_CUSTOMER_ID
         AND DEAL_CHARGE_CODE	= 27;
   ELSEIF IFNULL(O_ERROR_FLAG,'S')='S' THEN
      INSERT INTO CR_TXNCHARGES_DTL(TXN_TYPE, TXN_ID, DEAL_CHARGE_ID, DEAL_CHARGE_CODE, DEAL_CHARGE_APPLICATION_STAGE,
                                    DEAL_CHARGE_TYPE, DEAL_CHARGE_METHOD, DEAL_CHARGE_BP_TYPE, DEAL_CHARGE_BP_ID, 
                                    DEAL_CHARGE_CALCULATED_AMOUNT, DEAL_CHARGE_FINAL_AMOUNT, DEAL_CHARGE_TDS_AMOUNT, DEAL_CHARGE_NET_AMOUNT, 
                                    DEAL_CHARGE_TAX_APPLICABLE, DEAL_CHARGE_TAX_INCLUSIVE,DEAL_CHARGE_TAX_AMOUNT1, DEAL_CHARGE_TAX_AMOUNT2,REC_STATUS,
                                    DEAL_CHARGE_CALCULATED_ON, DEAL_CHARGE_SLAB_ON, DEAL_CHARGE_TDS_APPLICABLE, DEAL_CHARGE_TDS_RATE,
                                    DEAL_CHARGE_TAX_RATE1, DEAL_CHARGE_TAX_RATE2, DEAL_CHARGE_MIN_CHARGE_AMOUNT, DEAL_MIN_CHARGE_METHOD)
                             VALUES('LIM', I_LOAN_ID, V_CHARGE_ID, 27, 'LIM', 
                                    'R', 'F', 'CS', V_LOAN_CUSTOMER_ID, 
                                    V_ADVANCE_AMOUNT, V_ADVANCE_AMOUNT, 0, V_ADVANCE_AMOUNT, 
                                    'N', 'N',  0, 0,'A',
                                    '', '', 'N', 0, 
                                    0, 0, 0, 'F');
   END IF;
END IF;
END IF;
   IF IFNULL(V_ACCOUNTING_FLAG,'N') = 'Y'  AND IFNULL(O_ERROR_FLAG,'S')='S' THEN
	
	SELECT FUNDING_TYPE INTO V_BP_TYPE_DTL FROM CR_LOAN_DTL WHERE LOAN_ID=I_LOAN_ID;
	IF V_BP_TYPE_DTL='CB' OR V_BP_TYPE_DTL='CP' THEN
	      SET V_CHARGE_CODE = 43;
	      SELECT SUM(PERCENTAGE) INTO V_PERCENTAGE FROM CR_BUSINESS_PARTNER_DTL WHERE LOAN_ID=I_LOAN_ID AND SELF_FLAG<>'Y';
	      SET V_LOAN_ATM_PART=((V_LOAN_AMOUNT*V_PERCENTAGE)/100);
	      SET V_CHARGE_AMT = V_LOAN_ATM_PART;
	      SET V_CHARGE_STR = CONCAT(V_CHARGE_STR, CAST(V_CHARGE_CODE AS CHAR),';', CAST(V_CHARGE_AMT AS CHAR), '|');
	END IF;
      SET V_CHARGE_STR = '';
      SET V_CHARGE_CODE = 2;
      SET V_CHARGE_AMOUNT = V_LOAN_AMOUNT;
      SET V_CHARGE_STR = CONCAT(V_CHARGE_STR, CAST(V_CHARGE_CODE AS CHAR),';', CAST(V_CHARGE_AMOUNT AS CHAR), '|');
      SELECT IFNULL(SUM(IFNULL(INT_COMP,0)),0) + IFNULL(SUM(IFNULL(EXCESS_INT,0)),0) 
        INTO V_TOTAL_INTEREST
        FROM CR_REPAYSCH_DTL
       WHERE LOAN_ID = I_LOAN_ID;
	-- SELECT V_TOTAL_INTEREST;
      IF DONE = 1 THEN
         SET DONE = 0;
         SET V_TOTAL_INTEREST = 0;
      END IF;
	
	
	
      SET V_CHARGE_CODE = 41;
      SET V_CHARGE_AMT = V_TOTAL_INTEREST;
      SET V_CHARGE_STR = CONCAT(V_CHARGE_STR, CAST(V_CHARGE_CODE AS CHAR),';', CAST(V_CHARGE_AMT AS CHAR), '|');
     
     
     CALL GENERATEVOUCHER(I_COMPANY_ID, V_LOAN_BRANCH, 'CMS',
                           V_LOAN_SCHEME, I_DEPARTMENT, '',
                           'LIA', V_NPA_FLAG, 0, 
                           'CS', V_LOAN_CUSTOMER_ID, V_LOAN_CUSTOMER_NAME,
                           I_CURR_DATE, I_CURR_DATE,
                           I_LOAN_ID,  V_CHARGE_STR,
                           I_USER_ID, V_VOUCHER_STR, O_ERROR_FLAG, O_ERROR_MSG);
      IF IFNULL(V_VOUCHER_STR, '') <> '' AND IFNULL(O_ERROR_FLAG,'S')='S' THEN
         VOUCHER_STR_LOOP: LOOP
         IF INSTR(V_VOUCHER_STR,'|') = 0 THEN
            LEAVE VOUCHER_STR_LOOP;
         END IF;
         SET V_VOUCHER_NO 	= SUBSTRING_INDEX(V_VOUCHER_STR, '|', 1);
         SET V_VOUCHER_STR 	= SUBSTR(V_VOUCHER_STR,INSTR(V_VOUCHER_STR,'|') + 1);
         INSERT INTO CR_LOAN_VOUCHER_DTL(LOAN_ID, TXN_TYPE, TXN_ID, TXN_DATE, VALUE_DATE,
                                         COMPANY_ID, BRANCH_ID, PRODUCT_ID, DEPARTMENT_ID,
                                         STAGE_ID, NPA_STAGE, BP_TYPE, BP_ID, VOUCHER_NO)
                                  VALUES(I_LOAN_ID, 'LIA', I_LOAN_ID,  I_CURR_DATE, I_CURR_DATE,
                                         I_COMPANY_ID, V_LOAN_BRANCH, V_LOAN_SCHEME, I_DEPARTMENT,
                                         'LIA', V_NPA_FLAG,  'CS', V_LOAN_CUSTOMER_ID, V_VOUCHER_NO);
         END LOOP;
      END IF;
      SET V_CHARGE_STR = '';
   END IF;
SET PREV_BP_TYPE = ''; 
SET PREV_BP_ID   = 0;
SET V_CHARGE_STR = '';
SET TOTAL_TAX_AMOUNT1 = 0;
SET TOTAL_TAX_AMOUNT2 = 0;
SET TOTAL_TDS_AMOUNT = 0;
SET DONE = 0;
			SET V_INVOICE_NO='';
			SET V_BILL_OF_SUPPLY_NO='';
OPEN CHARGES_CUR;
CHARGES_CUR_READ : LOOP
   SET DONE =0;	
   FETCH CHARGES_CUR INTO V_CHARGE_ID, V_CHARGE_CODE, V_CHARGE_TYPE, V_CHARGE_METHOD,
                          V_BP_TYPE, V_BP_ID, V_CHARGE_AMOUNT, V_TDS_RATE, V_TAX_RATE1, V_TAX_RATE2,
                          V_TDS_AMOUNT, V_TAX_AMOUNT1, V_TAX_AMOUNT2;
/*SELECT 'INSIDE CHARGECUR', V_CHARGE_ID, V_CHARGE_CODE, V_CHARGE_TYPE, V_CHARGE_METHOD,
                          V_BP_TYPE, V_BP_ID, V_CHARGE_AMOUNT, V_TDS_RATE, V_TAX_RATE1, V_TAX_RATE2,
                          V_TDS_AMOUNT, V_TAX_AMOUNT1, V_TAX_AMOUNT2, DONE;*/
   IF DONE = 1 THEN
       SET DONE = 0;
       LEAVE CHARGES_CUR_READ;
   END IF;
--  SELECT 'INSIDE CHARGECUR000', V_CHARGE_CODE, DONE,PREV_BP_TYPE,V_BP_TYPE,V_BP_ID,PREV_BP_ID;   
   IF V_ACCOUNTING_FLAG = 'Y' AND  PREV_BP_TYPE <> '' AND (V_BP_TYPE <> PREV_BP_TYPE OR V_BP_ID <> PREV_BP_ID) THEN
							
							
							
							
							
							
 -- SELECT 111;
      IF PREV_BP_TYPE = 'CS' THEN
         SET V_BP_NAME = V_LOAN_CUSTOMER_NAME;
      ELSE
         SELECT DEALER_DESC INTO V_BP_NAME
           FROM CR_DSA_DEALER_M
          WHERE BP_TYPE = PREV_BP_TYPE
            AND DEALER_ID = PREV_BP_ID;
 -- SELECT V_BP_NAME;
         IF DONE = 1 THEN
            SET DONE = 0;
            SET V_BP_NAME = '';
         END IF;
      END IF;
 -- SELECT '222';
					SET V_INVOICE_NO='';
					SET V_BILL_OF_SUPPLY_NO='';
      CALL GENERATEVOUCHER(I_COMPANY_ID, V_LOAN_BRANCH, 'CMS',
                           V_LOAN_SCHEME, I_DEPARTMENT, '',
                           'LIA', V_NPA_FLAG, 0, 
                           PREV_BP_TYPE, PREV_BP_ID, V_BP_NAME,
                           I_CURR_DATE, I_CURR_DATE,
                           I_LOAN_ID,  V_CHARGE_STR,
                           I_USER_ID, V_VOUCHER_STR, O_ERROR_FLAG, O_ERROR_MSG);
							
      IF IFNULL(V_VOUCHER_STR, '') <> '' AND IFNULL(O_ERROR_FLAG,'S')='S' THEN
         VOUCHER_STR_LOOP: LOOP
         IF INSTR(V_VOUCHER_STR,'|') = 0 THEN
            LEAVE VOUCHER_STR_LOOP;
         END IF;
         SET V_VOUCHER_NO 	= SUBSTRING_INDEX(V_VOUCHER_STR, '|', 1);
         SET V_VOUCHER_STR 	= SUBSTR(V_VOUCHER_STR,INSTR(V_VOUCHER_STR,'|') + 1);
         INSERT INTO CR_LOAN_VOUCHER_DTL(LOAN_ID, TXN_TYPE, TXN_ID, TXN_DATE, VALUE_DATE,
                                         COMPANY_ID, BRANCH_ID, PRODUCT_ID, DEPARTMENT_ID,
                                         STAGE_ID, NPA_STAGE, BP_TYPE, BP_ID, VOUCHER_NO)
                                  VALUES(I_LOAN_ID, 'LIA', I_LOAN_ID,  I_CURR_DATE, I_CURR_DATE,
                                         I_COMPANY_ID, V_LOAN_BRANCH, V_LOAN_SCHEME, I_DEPARTMENT,
                                         'LIA', V_NPA_FLAG,  V_BP_TYPE, V_BP_ID, V_VOUCHER_NO);
         END LOOP;
      END IF;
      SET V_CHARGE_STR = '';
      SET TOTAL_TAX_AMOUNT1 = 0;
      SET TOTAL_TAX_AMOUNT2 = 0;
      SET TOTAL_TDS_AMOUNT = 0;
   END IF;
	IF IFNULL(O_ERROR_FLAG,'S')='S' THEN
		SET V_CHARGE_TXNADVICE_ID =0;
   		INSERT INTO CR_TXNADVICE_DTL (DEAL_ID, LOAN_ID, TXN_TYPE, TXN_ID, BP_TYPE, BP_ID, ADVICE_DATE, ADVICE_TYPE,
                                 CHARGE_CODE_ID, ORG_CHARGE_AMOUNT, TAX_RATE1, TAX_RATE2, TDS_RATE, ORG_TAX_AMOUNT1,
                                 ORG_TAX_AMOUNT2, ORG_TDS_AMOUNT, ORG_ADVICE_AMOUNT, WAIVE_OFF_AMOUNT, CHARGE_AMOUNT,
                                 TAX_AMOUNT1, TAX_AMOUNT2, TDS_AMOUNT, ADVICE_AMOUNT, TXN_ADJUSTED_AMOUNT,
                                 AMOUNT_IN_PROCESS, REC_STATUS, MAKER_ID, MAKER_DATE, AUTHOR_ID, AUTHOR_DATE) 
		         VALUES (V_DEAL_ID, I_LOAN_ID, 'LIM', I_LOAN_ID,  V_BP_TYPE, V_BP_ID, I_CURR_DATE, V_CHARGE_TYPE,
		                 V_CHARGE_CODE, (V_CHARGE_AMOUNT - V_TAX_AMOUNT1 - V_TAX_AMOUNT2), V_TAX_RATE1, V_TAX_RATE2, V_TDS_RATE, V_TAX_AMOUNT1,
			         V_TAX_AMOUNT2, V_TDS_AMOUNT, V_CHARGE_AMOUNT, 0, (V_CHARGE_AMOUNT - V_TAX_AMOUNT1 - V_TAX_AMOUNT2),
			         V_TAX_AMOUNT1, V_TAX_AMOUNT2, V_TDS_AMOUNT, V_CHARGE_AMOUNT, 0,
			         0, 'A', I_USER_ID, I_CURR_DATE, I_USER_ID, I_CURR_DATE);
			SELECT LAST_INSERT_ID() INTO V_CHARGE_TXNADVICE_ID;
  					SET V_TAX1_CODE =0;
					 SET V_TAX1_RATE=0;
					 SET V_TAX1_AMOUNT=0;
					 SET V_TAX2_CODE =0;
					 SET V_TAX2_RATE=0;
					 SET V_TAX2_AMOUNT=0;
					 SET V_TAX3_CODE =0;
					 SET V_TAX3_RATE=0;
					 SET V_TAX3_AMOUNT=0;
   							SET V_CHARGE_STR = CONCAT(V_CHARGE_STR, CAST(V_CHARGE_CODE AS CHAR),';', CAST((V_CHARGE_AMOUNT - V_TAX_AMOUNT1 - V_TAX_AMOUNT2) AS CHAR), '|');
					-- SELECT '1',V_CHARGE_STR;	
					SET V_BRANCH_STATE_CODE = GET_BRANCH_STATE(I_LOAN_ID,V_DEAL_ID);
					-- SELECT '2',V_BRANCH_STATE_CODE;
					SET V_PARTY_CODE = 0;
					SET V_STATE_CODE = GET_STATE('CMS',I_LOAN_ID,'LOAN',V_BP_TYPE,V_BP_ID,V_PARTY_CODE);
					-- SELECT 'V_STATE_CODE',V_STATE_CODE;
					SET  V_CHARGE_CODE_I = GET_TAX_CHARGE_CODE1(V_CHARGE_CODE,V_BRANCH_STATE_CODE,V_STATE_CODE,I_CURR_DATE); 
					SET  V_CHARGE_AMOUNT_I =IFNULL(V_TAX_AMOUNT1, 0); 
					SET V_CHARGE_STR = CONCAT(V_CHARGE_STR, CAST(V_CHARGE_CODE_I AS CHAR),';', CAST((V_CHARGE_AMOUNT_I) AS CHAR), '|');
					-- SELECT 'charge_code1',V_CHARGE_STR;
					SET V_TAX1_CODE=V_CHARGE_CODE_I;
					SET V_TAX1_RATE=V_TAX_RATE1;
					SET V_TAX1_AMOUNT=V_CHARGE_AMOUNT_I;
					IF V_TAX_AMOUNT2 > 0 THEN
						SET V_TAX3_PERCENTAGE = 0;
						SET V_TAX2_PERCENTAGE = 0;
						SELECT IFNULL(TAX2_PERCENTAGE,0),IFNULL(TAX3_PERCENTAGE,0) INTO V_TAX2_PERCENTAGE,V_TAX3_PERCENTAGE FROM com_tax_rate_mapping WHERE RATE=V_TAX_RATE2 AND EFFECTIVE_DATE<=I_CURR_DATE ORDER BY EFFECTIVE_DATE DESC LIMIT 1;
						IF DONE = 1 THEN
							SET DONE = 0;
							SET V_KRISHI_TAX_APPLICABLE_OR_NOT='N';
						END IF;
						IF IFNULL(V_TAX3_PERCENTAGE,0) > 0 THEN
							SET V_KRISHI_TAX_APPLICABLE_OR_NOT='Y';
						ELSE
							SET V_KRISHI_TAX_APPLICABLE_OR_NOT='N';
						END IF;
						IF V_TAX_AMOUNT2>0 AND IFNULL(GET_TAX_CHARGE_CODE3(V_CHARGE_CODE),'')<>'' AND IFNULL(V_KRISHI_TAX_APPLICABLE_OR_NOT,'N')='Y' THEN 
								
							SET  V_CHARGE_CODE_I = GET_TAX_CHARGE_CODE2(V_CHARGE_CODE,V_BRANCH_STATE_CODE,V_STATE_CODE,I_CURR_DATE); 
							SET  V_CHARGE_AMOUNT_I =IFNULL(V_TAX_AMOUNT2, 0)*V_TAX2_PERCENTAGE/100;
							SET V_CHARGE_STR = CONCAT(V_CHARGE_STR, CAST(V_CHARGE_CODE_I AS CHAR),';', CAST((V_CHARGE_AMOUNT_I) AS CHAR), '|');
							-- SELECT 'charge_code2',V_CHARGE_STR;
							SET V_TAX2_CODE=V_CHARGE_CODE_I;
							SET V_TAX2_RATE=V_TAX_RATE2 *V_TAX2_PERCENTAGE/100;
							SET V_TAX2_AMOUNT=V_CHARGE_AMOUNT_I;
							SET  V_CHARGE_CODE_I = GET_TAX_CHARGE_CODE3(V_CHARGE_CODE); 
							SET  V_CHARGE_AMOUNT_I =IFNULL(V_TAX_AMOUNT2, 0)*V_TAX3_PERCENTAGE/100;
							SET V_TAX3_CODE=V_CHARGE_CODE_I;
							SET V_TAX3_RATE=V_TAX_RATE2*V_TAX3_PERCENTAGE/100;
							SET V_TAX3_AMOUNT=V_CHARGE_AMOUNT_I;
							SET V_CHARGE_STR = CONCAT(V_CHARGE_STR, CAST(V_CHARGE_CODE_I AS CHAR),';', CAST((V_CHARGE_AMOUNT_I) AS CHAR), '|');
							-- SELECT 'charge_code3',V_CHARGE_STR;
						ELSE
							SET  V_CHARGE_CODE_I = GET_TAX_CHARGE_CODE2(V_CHARGE_CODE,V_BRANCH_STATE_CODE,V_STATE_CODE,I_CURR_DATE); 
							SET  V_CHARGE_AMOUNT_I =IFNULL(V_TAX_AMOUNT2, 0);
							SET V_TAX2_CODE=V_CHARGE_CODE_I;
							SET V_TAX2_RATE=V_TAX_RATE2 ;
							SET V_TAX2_AMOUNT=V_CHARGE_AMOUNT_I;
							
							SET V_TAX3_CODE=0;
							SET V_TAX3_RATE=0 ;
							SET V_TAX3_AMOUNT=0;
							
							SET V_CHARGE_STR = CONCAT(V_CHARGE_STR, CAST(V_CHARGE_CODE_I AS CHAR),';', CAST((V_CHARGE_AMOUNT_I) AS CHAR), '|');
						-- 	SELECT 'charge_code22',V_CHARGE_STR;
						END IF;
					END IF;
					SELECT DUE_RECEIPT_BASIS INTO V_CHARGE_DUE_RECEIPT FROM com_charge_code_m WHERE charge_code=V_CHARGE_CODE;
					SET V_CHARGE_DUE_RECEIPT = IFNULL(V_CHARGE_DUE_RECEIPT,'D');
					IF V_CHARGE_DUE_RECEIPT='D' THEN
					--	SELECT '111',V_TAX1_AMOUNT,V_TAX2_AMOUNT,V_INVOICE_NO,V_BILL_OF_SUPPLY_NO;
						IF IFNULL(V_TAX1_AMOUNT,0)+IFNULL(V_TAX2_AMOUNT,0) <> 0 THEN
							
							IF V_INVOICE_NO ='' THEN
								CALL GENERATEINVOICESEQUENCE(V_BRANCH_STATE_CODE,I_CURR_DATE,'TAX_INVOICE_NO',V_INVOICE_NO,O_ERROR_FLAG, O_ERROR_MSG);
							END IF;
							SET V_COMMON_INVOICE_NO=V_INVOICE_NO;
							SET V_TAX_RECORD_TYPE = 'TAX INVOICE';
						ELSE
							IF V_BILL_OF_SUPPLY_NO ='' THEN
								CALL GENERATEINVOICESEQUENCE(V_BRANCH_STATE_CODE,I_CURR_DATE,'SUPPLY_INVOICE_NO',V_BILL_OF_SUPPLY_NO,O_ERROR_FLAG, O_ERROR_MSG);
							-- SELECT O_ERROR_FLAG, O_ERROR_MSG;
							END IF;
							SET V_COMMON_INVOICE_NO=V_BILL_OF_SUPPLY_NO;
							SET V_TAX_RECORD_TYPE = 'BILL OF SUPPLY';
						END IF;
						-- SELECT V_BILL_OF_SUPPLY_NO,V_INVOICE_NO,O_ERROR_FLAG, O_ERROR_MSG;
						SET V_TAX2_CODE =IFNULL(V_TAX2_CODE,0);
						SELECT com_state_m.UNIONTERRITORY INTO V_UNION_TERRITORY FROM com_state_m WHERE com_state_m.STATE_ID=V_BRANCH_STATE_CODE;
						SET V_UNION_TERRITORY= IFNULL(V_UNION_TERRITORY,'N');
						-- SELECT V_UNION_TERRITORY,V_TAX1_CODE, V_TAX2_CODE,V_TAX1_AMOUNT,V_TAX2_RATE,V_TAX2_AMOUNT;
						INSERT INTO cr_tax_details (TXN_TYPE,
									INVOICE_NO,TXN_DATE,VALUE_DATE,
									TXN_ID,LOAN_DEAL,LOAN_DEAL_ID,BPTYPE,BPID,
									CHARGE_CODE_ID,CHARGE_AMOUNT,
									CGST_RATE,
									CGST_AMOUNT,
									SGCT_RATE,
									SGCT_AMOUNT,
									IGST_RATE,
									IGST_AMOUNT,
									UGST_RATE,
									UGST_AMOUNT,
									VOUCHER_STR,
									RECORD_TYPE,
									Branch_State,
									CUSTOMER_STATE,
									BRANCH_ID,
									TXNADVICEID)
							VALUES('LIA',
								V_COMMON_INVOICE_NO,I_CURR_DATE,I_CURR_DATE,
								I_LOAN_ID,'LOAN',I_LOAN_ID,V_BP_TYPE, V_BP_ID,
								V_CHARGE_CODE,(V_CHARGE_AMOUNT - V_TAX_AMOUNT1 - V_TAX_AMOUNT2),
								IF (V_TAX1_CODE<>V_TAX2_CODE,  V_TAX1_RATE,0),
								IF (V_TAX1_CODE<>V_TAX2_CODE , V_TAX1_AMOUNT,0),
								IF (V_TAX1_CODE<>V_TAX2_CODE,  IF(V_UNION_TERRITORY='N',V_TAX2_RATE,0),0),
								IF (V_TAX1_CODE<>V_TAX2_CODE,  IF(V_UNION_TERRITORY='N',V_TAX2_AMOUNT,0),0),
								IF (V_TAX1_CODE=V_TAX2_CODE,  V_TAX1_RATE+V_TAX2_RATE , 0),
								IF (V_TAX1_CODE=V_TAX2_CODE,  V_TAX1_AMOUNT+V_TAX2_AMOUNT , 0),
								IF (V_TAX1_CODE<>V_TAX2_CODE,  IF(V_UNION_TERRITORY='Y',V_TAX2_RATE,0),0),
								IF (V_TAX1_CODE<>V_TAX2_CODE,   IF(V_UNION_TERRITORY='Y',V_TAX2_AMOUNT,0),0),
								'',
								V_TAX_RECORD_TYPE,
								V_BRANCH_STATE_CODE,
								V_STATE_CODE,
								V_LOAN_BRANCH,	
								V_CHARGE_TXNADVICE_ID
								);
						
						
					END IF;   
	
 
							
							
							
							
							SET PREV_BP_TYPE = V_BP_TYPE; 
							SET PREV_BP_ID   = V_BP_ID;
						END IF;
					END LOOP CHARGES_CUR_READ;
					CLOSE CHARGES_CUR;
	   
					IF IFNULL(V_ACCOUNTING_FLAG,'N') = 'Y' AND  IFNULL(PREV_BP_TYPE,'') <> '' THEN
						
						
						
						
						
						
      IF V_BP_TYPE = 'CS' THEN
         SET V_BP_NAME = V_LOAN_CUSTOMER_NAME;
      ELSE
         SELECT DEALER_DESC INTO V_BP_NAME
           FROM CR_DSA_DEALER_M
          WHERE BP_TYPE = PREV_BP_TYPE
            AND DEALER_ID = PREV_BP_ID;
      END IF;
      CALL GENERATEVOUCHER(I_COMPANY_ID, V_LOAN_BRANCH, 'CMS',
                           V_LOAN_SCHEME, I_DEPARTMENT, '',
                           'LIA', V_NPA_FLAG, 0, 
                           PREV_BP_TYPE, PREV_BP_ID, V_BP_NAME,
                           I_CURR_DATE, I_CURR_DATE,
                           I_LOAN_ID,  V_CHARGE_STR,
                           I_USER_ID, V_VOUCHER_STR, O_ERROR_FLAG, O_ERROR_MSG);
						
      IF IFNULL(V_VOUCHER_STR, '') <> '' AND IFNULL(O_ERROR_FLAG,'S')='S' THEN
         VOUCHER_STR_LOOP: LOOP
         IF INSTR(V_VOUCHER_STR,'|') = 0 THEN
            LEAVE VOUCHER_STR_LOOP;
         END IF;
         SET V_VOUCHER_NO 	= SUBSTRING_INDEX(V_VOUCHER_STR, '|', 1);
         SET V_VOUCHER_STR 	= SUBSTR(V_VOUCHER_STR,INSTR(V_VOUCHER_STR,'|') + 1);
         INSERT INTO CR_LOAN_VOUCHER_DTL(LOAN_ID, TXN_TYPE, TXN_ID, TXN_DATE, VALUE_DATE,
                                         COMPANY_ID, BRANCH_ID, PRODUCT_ID, DEPARTMENT_ID,
                                         STAGE_ID, NPA_STAGE, BP_TYPE, BP_ID, VOUCHER_NO)
                                  VALUES(I_LOAN_ID, 'LIA', I_LOAN_ID,  I_CURR_DATE, I_CURR_DATE,
                                         I_COMPANY_ID, V_LOAN_BRANCH, V_LOAN_SCHEME, I_DEPARTMENT,
                                         'LIA', V_NPA_FLAG,  V_BP_TYPE, V_BP_ID, V_VOUCHER_NO);
         END LOOP;
      END IF;
      SET V_CHARGE_STR = '';
      SET TOTAL_TAX_AMOUNT1 = 0;
      SET TOTAL_TAX_AMOUNT2 = 0;
      SET TOTAL_TDS_AMOUNT = 0;
   END IF;
IF IFNULL(O_ERROR_FLAG, 'S') = 'S' THEN
   SELECT IFNULL(SUM(IFNULL(ADVICE_AMOUNT,0) - IFNULL(TXN_ADJUSTED_AMOUNT,0) - IFNULL(AMOUNT_IN_PROCESS,0)), 0) INTO V_TOTAL_PAYABLE
	  FROM CR_TXNADVICE_DTL
	 WHERE DEAL_ID = V_DEAL_ID
		AND LOAN_ID = 0
	   AND ADVICE_TYPE = 'P'
	   AND BP_TYPE = 'CS'
      AND CHARGE_CODE_ID = 4
	   AND (IFNULL(ADVICE_AMOUNT,0) - IFNULL(TXN_ADJUSTED_AMOUNT,0) - IFNULL(AMOUNT_IN_PROCESS,0)) > 0
	   AND REC_STATUS = 'A';
	IF V_TOTAL_PAYABLE > 0 THEN
	   SELECT IFNULL(SUM(IFNULL(ADVICE_AMOUNT,0) - IFNULL(TXN_ADJUSTED_AMOUNT,0) - IFNULL(AMOUNT_IN_PROCESS,0)), 0) INTO V_TOTAL_RECEIVABLE
	     FROM CR_TXNADVICE_DTL
		 WHERE LOAN_ID = I_LOAN_ID
		   AND ADVICE_TYPE = 'R'
		   AND BP_TYPE = 'CS'
		   AND (IFNULL(ADVICE_AMOUNT,0) - IFNULL(TXN_ADJUSTED_AMOUNT,0) - IFNULL(AMOUNT_IN_PROCESS,0)) > 0
		   AND REC_STATUS = 'A';
	END IF;
   SET V_TOTAL_KNOCKOFF_AMOUNT = LEAST(IFNULL(V_TOTAL_PAYABLE,0), IFNULL(V_TOTAL_RECEIVABLE,0));
   IF V_TOTAL_KNOCKOFF_AMOUNT > 0 AND IFNULL(O_ERROR_FLAG,'S')='S' THEN
   INSERT INTO CR_KNOCKOFF_M (LOAN_ID, BP_TYPE, BP_ID, KNOCKOFF_DATE, KNOCKOFF_AMOUNT_RECEIVABLE,
										KNOCKOFF_AMOUNT_PAYABLE, REC_STATUS, CALLED_FROM, REMARKS,
										MAKER_ID, MAKER_DATE, AUTHOR_ID, AUTHOR_DATE)
								VALUES(I_LOAN_ID, 'CS', V_LOAN_CUSTOMER_ID, I_CURR_DATE, V_TOTAL_KNOCKOFF_AMOUNT,
										V_TOTAL_KNOCKOFF_AMOUNT, 'F', 'LIA', 'AUTO KNOCK OFF IMD',	
										I_USER_ID, I_CURR_DATE, I_USER_ID, I_CURR_DATE);
	SELECT DISTINCT LAST_INSERT_ID() INTO V_KNOCKOFF_ID;
	SET V_SEQ_NO= 1;
	SET V_ADVICE_TYPE = 'P';
	SET V_BP_TYPE = 'CS';
	SET V_TOTAL_BALANCE_AMOUNT = V_TOTAL_KNOCKOFF_AMOUNT;
		
	OPEN TXNADVICE_CUR_P;
	TXNADVICE_CUR_P_READ : LOOP
		FETCH TXNADVICE_CUR_P INTO V_TXNADVICE_ID, V_CHARGE_CODE_ID, V_ADVICE_TYPE, V_ADVICE_AMOUNT, V_TXN_ADJUSTED_AMOUNT, V_AMOUNT_IN_PROCESS, V_BALANCE_AMOUNT;
		IF DONE = 1 THEN
		    SET DONE = 0;
		    LEAVE TXNADVICE_CUR_P_READ;
		END IF;
		
		SET V_KNOCKOFF_AMOUNT = LEAST(V_BALANCE_AMOUNT, V_TOTAL_BALANCE_AMOUNT);
		 
		UPDATE CR_TXNADVICE_DTL SET AMOUNT_IN_PROCESS = IFNULL(AMOUNT_IN_PROCESS, 0) + IFNULL(V_KNOCKOFF_AMOUNT,0)
		 WHERE TXNADVICE_ID = V_TXNADVICE_ID;
		INSERT INTO CR_KNOCKOFF_DTL (SEQ_NO, KNOCKOFF_ID, TXN_ADVICE_ID, ADVICE_TYPE, ADVICE_AMOUNT,
												BALANCE_AMOUNT, KNOCKOFF_AMOUNT, AMOUNT_IN_PROCESS, REC_STATUS,
												MAKER_ID, MAKER_DATE, AUTHOR_ID, AUTHOR_DATE)
									VALUES (V_SEQ_NO, V_KNOCKOFF_ID, V_TXNADVICE_ID, V_ADVICE_TYPE, V_ADVICE_AMOUNT,
												V_BALANCE_AMOUNT, V_KNOCKOFF_AMOUNT, V_AMOUNT_IN_PROCESS, 'F',
												I_USER_ID, I_CURR_DATE, I_USER_ID, I_CURR_DATE);
		SET V_SEQ_NO =  V_SEQ_NO + 1;
		SET V_TOTAL_BALANCE_AMOUNT = V_TOTAL_BALANCE_AMOUNT - V_KNOCKOFF_AMOUNT;
		IF V_TOTAL_BALANCE_AMOUNT <= 0 THEN
		   LEAVE TXNADVICE_CUR_P_READ;
		END IF;
	END LOOP;
	CLOSE TXNADVICE_CUR_P;
   SET V_ADVICE_TYPE = 'R';
   SET V_BP_TYPE = 'CS';
   SET V_TOTAL_BALANCE_AMOUNT = V_TOTAL_KNOCKOFF_AMOUNT;
	OPEN TXNADVICE_CUR_R;
	TXNADVICE_CUR_R_READ : LOOP
		FETCH TXNADVICE_CUR_R INTO V_TXNADVICE_ID, V_CHARGE_CODE_ID, V_ADVICE_TYPE, V_ADVICE_AMOUNT, V_TXN_ADJUSTED_AMOUNT, V_AMOUNT_IN_PROCESS, V_BALANCE_AMOUNT;
		IF DONE = 1 THEN
		    SET DONE = 0;
		    LEAVE TXNADVICE_CUR_R_READ;
		END IF;
		
      SELECT IFNULL(SUM(IFNULL(IMD_AMOUNT,0)),0) INTO V_IMD_AMOUNT
        FROM CR_IMD_DTL
       WHERE DEAL_ID = V_DEAL_ID
         AND DEAL_CHARGE_CODE = V_CHARGE_CODE_ID
			AND REC_STATUS = 'A';
      IF V_IMD_AMOUNT > 0 AND IFNULL(O_ERROR_FLAG,'S')='S' THEN
		SET V_KNOCKOFF_AMOUNT = LEAST(V_BALANCE_AMOUNT, V_TOTAL_BALANCE_AMOUNT, V_IMD_AMOUNT);
		 
		UPDATE CR_TXNADVICE_DTL SET AMOUNT_IN_PROCESS = IFNULL(AMOUNT_IN_PROCESS, 0) + IFNULL(V_KNOCKOFF_AMOUNT,0)
		 WHERE TXNADVICE_ID = V_TXNADVICE_ID;
		INSERT INTO CR_KNOCKOFF_DTL (SEQ_NO, KNOCKOFF_ID, TXN_ADVICE_ID, ADVICE_TYPE, ADVICE_AMOUNT,
												BALANCE_AMOUNT, KNOCKOFF_AMOUNT, AMOUNT_IN_PROCESS, REC_STATUS,
												MAKER_ID, MAKER_DATE, AUTHOR_ID, AUTHOR_DATE)
									VALUES (V_SEQ_NO, V_KNOCKOFF_ID, V_TXNADVICE_ID, V_ADVICE_TYPE, V_ADVICE_AMOUNT,
												V_BALANCE_AMOUNT, V_KNOCKOFF_AMOUNT, V_AMOUNT_IN_PROCESS, 'F',
												I_USER_ID, I_CURR_DATE, I_USER_ID, I_CURR_DATE);
		SET V_SEQ_NO =  V_SEQ_NO + 1;
		SET V_TOTAL_BALANCE_AMOUNT = V_TOTAL_BALANCE_AMOUNT - V_KNOCKOFF_AMOUNT;
      END IF;
		IF V_TOTAL_BALANCE_AMOUNT <= 0 THEN
		   LEAVE TXNADVICE_CUR_R_READ;
		END IF;
	END LOOP;
	CLOSE TXNADVICE_CUR_R;
	UPDATE CR_TXNADVICE_DTL SET LOAN_ID=I_LOAN_ID,BP_ID= V_LOAN_CUSTOMER_ID WHERE DEAL_ID=V_DEAL_ID AND CHARGE_CODE_ID=4;	
	IF O_ERROR_FLAG = 'S' AND V_TOTAL_KNOCKOFF_AMOUNT > 0 THEN
		CALL KNOCK_OFF_AUTHOR(I_COMPANY_ID, V_KNOCKOFF_ID, I_CURR_DATE, I_USER_ID,'A', 'AUTO KNOCK OFF',O_ERROR_FLAG, O_ERROR_MSG);
								
	END IF;     
   END IF;
END IF;
END IF;
END IF;
SELECT PARAMETER_VALUE INTO V_LINK_LOAN_FLAG FROM PARAMETER_MST WHERE PARAMETER_KEY='LINK_LOAN_FLAG';
IF DONE=1 THEN
	SET DONE=0;
END IF;
 IF I_STATUS = 'A' AND IFNULL(O_ERROR_FLAG,'S')='S' AND IFNULL(V_LINK_LOAN_FLAG,'N')='Y' THEN 
	CALL LOAN_LINKED_WIH_CUSTOMER(I_LOAN_ID,I_USER_ID,I_CURR_DATE,O_ERROR_FLAG,O_ERROR_MSG);
 END IF;				
END IF;
IF I_STATUS = 'A' AND IFNULL(O_ERROR_FLAG,'S')='S' THEN
	SET V_HOLD_CT=0;
	SELECT COUNT(1) INTO V_HOLD_CT FROM STAGE_HOLDING_TIME_DTL WHERE DEAL_ID=V_DEAL_ID;
        IF (IFNULL(V_HOLD_CT,0)=0) THEN
		INSERT INTO STAGE_HOLDING_TIME_DTL(DEAL_ID,DC,QC,BSA,CVC,FAC,FFC,FVC,FVI,POC,TCC,UNC,LOAN,DISBURSAL,RAL,CBL)
                VALUES(V_DEAL_ID,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
	END IF;
	SET V_HOUR=0;
        SET V_HOUR=TIMESTAMPDIFF(MINUTE,V_LOAN_MAKER_DATE,I_CURR_DATE);
	SET V_HOUR=IFNULL(V_HOUR,0);
        UPDATE STAGE_HOLDING_TIME_DTL SET LOAN=LOAN+(V_HOUR) WHERE DEAL_ID=V_DEAL_ID;
	
	/*UPDATE CR_DEAL_LOAN_DTL SET DEAL_UTILIZED_AMOUNT=IFNULL(DEAL_UTILIZED_AMOUNT,0)+
	(SELECT LOAN_LOAN_AMOUNT FROM CR_LOAN_DTL WHERE LOAN_ID=I_LOAN_ID) 
	WHERE DEAL_LOAN_ID=(SELECT LOAN_DEAL_LOAN_ID FROM CR_LOAN_DTL WHERE LOAN_ID=I_LOAN_ID);*/
	
	update cr_deal_loan_dtl  DL inner join cr_loan_dtl CLD on(DL.DEAL_LOAN_ID=CLD.LOAN_DEAL_LOAN_ID)
	SET DL.DEAL_UTILIZED_AMOUNT=IFNULL(DL.DEAL_UTILIZED_AMOUNT,0)+CLD.LOAN_LOAN_AMOUNT
	WHERE LOAN_ID=I_LOAN_ID;
				
	
	
END IF;

/*UPDATE GCD_CUSTOMER_M SET CUSTOMER_STATUS = 'A' WHERE CUSTOMER_ID IN 
(SELECT GCD_ID FROM CR_LOAN_CUSTOMER_ROLE WHERE LOAN_ID = I_LOAN_ID);
*/

UPDATE GCD_CUSTOMER_M m 
INNER JOIN CR_LOAN_CUSTOMER_ROLE r ON r.GCD_ID=m.CUSTOMER_ID
SET m.CUSTOMER_STATUS = 'A' WHERE r.LOAN_ID = I_LOAN_ID;

IF V_ENABLE_INVOICE_PROCESS='Y' AND O_ERROR_FLAG='S' THEN
	IF I_STATUS = 'X' AND O_ERROR_FLAG = 'S' THEN
		UPDATE CR_UNPAID_DRF_DTL SET PAID_STATUS='N' WHERE DRF_ID=V_DRF_ID;
		UPDATE CR_ASSET_COLLATERAL_M SET DRF_ID=NULL,UNPAID_INVOICE_ID=NULL WHERE DRF_ID=V_DRF_ID;
		UPDATE CR_LOAN_COLLATERAL_M SET DRF_ID=NULL,UNPAID_INVOICE_ID=NULL WHERE DRF_ID=V_DRF_ID;
		UPDATE CR_ASSET_INVOICE_DTL SET DRF_ID=NULL,UNPAID_INVOICE_ID=NULL WHERE DRF_ID=V_DRF_ID;
	END IF; 
	IF I_STATUS = 'A' AND O_ERROR_FLAG = 'S' THEN
		UPDATE CR_UNPAID_DRF_DTL CUDD
		JOIN CR_UNPAID_INVOICE_DTL CUID ON(CUID.DRF_ID=CUDD.DRF_ID)
		JOIN CR_LOAN_DTL CLD ON(CLD.DRF_ID=CUDD.DRF_ID)
		SET CUDD.PAID_STATUS='U',CUDD.REC_STATUS='U',CUDD.DRF_STATUS='U',CUID.REC_STATUS='U'
		WHERE CLD.LOAN_ID=I_LOAN_ID;
	END IF; 
END IF;
IF O_ERROR_FLAG = 'E' THEN
	ROLLBACK;
END IF;
IF O_ERROR_FLAG = 'S' THEN
	SELECT FUNDING_TYPE INTO V_BP_TYPE_DTL FROM CR_LOAN_DTL WHERE LOAN_ID=I_LOAN_ID;
	SET V_COUNT=0;
	SELECT COUNT(1) INTO V_COUNT FROM CR_BUSINESS_PARTNER_DTL A
	JOIN COM_PARTNER_DETAILS_M B ON A.PARTNER_ID=B.PARTNER_ID
	WHERE LOAN_ID=I_LOAN_ID AND IFNULL(A.SELF_FLAG,'')='N';
	IF IFNULL(V_COUNT,0) > 0 THEN
	INSERT INTO BUSINESS_PARTNER_VIEW  
	SELECT V_DEAL_ID,I_LOAN_ID,'CP',IFNULL(A.PARTNER_ID,''),IFNULL(B.NAME,'') FROM CR_BUSINESS_PARTNER_DTL A
	JOIN COM_PARTNER_DETAILS_M B ON A.PARTNER_ID=B.PARTNER_ID
	WHERE LOAN_ID=I_LOAN_ID AND IFNULL(A.SELF_FLAG,'')='N';
	END IF;	
	
END IF;
END


---


CREATE DEFINER=`devadmin`@`%` PROCEDURE `SMALL_TICKET_DISBURSAL_DETAIL_UPLOAD`(IN I_TICKET_ID VARCHAR(10),IN I_LOAN_NO VARCHAR(30),OUT O_GENERATE_VALUE INT(10),OUT O_ERROR_FLAG VARCHAR(1)
										,OUT O_ERROR_MSG VARCHAR(9000))
BEGIN
	DECLARE      	DONE                    	INT(2);
	DECLARE		V_LOAN_ID			VARCHAR(30);
	DECLARE		V_MAKER_DATE			DATE;
	DECLARE		V_MAKER_ID			VARCHAR(30);
	DECLARE		V_BUSINESS_DATE			DATE;
	DECLARE 	V_COUNT				INT(3);
	DECLARE		V_LOAN_NO			VARCHAR(20);
	DECLARE		V_product			VARCHAR(20);
	DECLARE		v_stage				VARCHAR(50);
	DECLARE 	V_IPSTATUS			VARCHAR(10);
	DECLARE 	V_RPSTATUS			VARCHAR(10);
	DECLARE 	V_DISBSTATUS			VARCHAR(10);
	DECLARE		V_LOAN_ID_temp			VARCHAR(30);
	DECLARE 	P_DISBURSAL_DATE 		VARCHAR(10);
	DECLARE 	P_DISBURSAL_AMOUNT 		DECIMAL (22,7);
	DECLARE 	P_LOAN_REPAY_EFF_DATE 		VARCHAR(20);
	DECLARE 	v_parameter_value 		VARCHAR(1);
	DECLARE 	O_CURR_PEMI  			DECIMAL (22,7);
	DECLARE 	O_NEXT_MONTH_PEMI  		DECIMAL (22,7);
	DECLARE     	P_PAY_TO     			VARCHAR(50);
	DECLARE     	V_GENERATE_VALUE    		INT(10);
	DECLARE     	DISB_count    			INT(10);
	DECLARE		DISBURSEDAMOUNT			DECIMAL (22,7);
	DECLARE        	mbd_disbursedAmount    		DECIMAL(22,4) DEFAULT 0.0;
	DECLARE		V_LAST_INSERT_ID		INT(10);
	DECLARE		UPDATEBATCHID			INT(10);
	DECLARE		DIS_DISBURSAL_DATE		VARCHAR(10);
	DECLARE		DIS_MAKER_REMARKS		VARCHAR(500);
	DECLARE		DIS_AUTHOR_REMARKS		VARCHAR(500);
	DECLARE		DIS_DISBURSAL_TO		VARCHAR(3);
	DECLARE		DIS_CUSTOMER_ID			INT(10);
	DECLARE		DIS_DISBURSAL_AMOUNT		DECIMAL(22,4);
	DECLARE		DIS_ADJUST_TOTAL_PAYABLE	DECIMAL(22,4);
	DECLARE		DIS_ADJUST_TOTAL_RECEIVABLE	DECIMAL(22,4);
	DECLARE		DIS_NET_AMOUNT			DECIMAL(22,4);
	DECLARE		DIS_FINAL_DISBURSAL		VARCHAR(1);
	DECLARE		DIS_LOAN_CURTAILMENT		VARCHAR(1);
	DECLARE		DIS_PAY_TO			VARCHAR(20);
	DECLARE		DIS_PAYEE_NAME			VARCHAR(100);
	DECLARE		DIS_PAYMENT_MODE		VARCHAR(3);
	DECLARE		DIS_PAYMENT_DATE		VARCHAR(10);
	DECLARE		DIS_INSTRUMENT_NO		VARCHAR(20);
	DECLARE		DIS_INSTRUMENT_DATE		VARCHAR(10);
	DECLARE		DIS_BANK_ACCOUNT		VARCHAR(20);
	DECLARE		DIS_BANK_ID			INT(10);
	DECLARE		DIS_BRANCH_ID			INT(10);
	DECLARE		DIS_MICR_CODE			VARCHAR(20);
	DECLARE		DIS_IFSC_CODE			VARCHAR(20);
	DECLARE		DIS_PAYMENT_AMOUNT		DECIMAL(22,4);
	DECLARE		DIS_TDS_AMOUNT			DECIMAL(22,4);
	DECLARE		DIS_REMARKS			VARCHAR(500);
	DECLARE		DIS_PAYMENT_FLAG		VARCHAR(1);
	DECLARE		DIS_DISBURSAL_FLAG		VARCHAR(1);
	DECLARE		V_DEAL_ID			VARCHAR(30);
	DECLARE 	v_receivable_amt 		DECIMAL (22,7);
	DECLARE		V_PAYABLE_AMT			DECIMAL (22,7);
	DECLARE		DIS_DEPOSIT_BANK_ACCOUNT 	VARCHAR(22);
	DECLARE		DIS_DEPOSIT_BANK_ID 		VARCHAR(22);
	DECLARE		DIS_DEPOSIT_BRANCH_ID 		VARCHAR(20);
	DECLARE		DIS_DEPOSIT_MICR_CODE 		VARCHAR(20);
	DECLARE		DIS_DEPOSIT_IFSC_CODE 		VARCHAR(20);
	DECLARE		DIS_DEPOSIT_BANK_ACCOUNT1 	VARCHAR(22);
	DECLARE		DIS_DEPOSIT_BANK_ID1 		VARCHAR(22);
	DECLARE		DIS_DEPOSIT_BRANCH_ID1 		VARCHAR(20);
	DECLARE		DIS_DEPOSIT_MICR_CODE1 		VARCHAR(20);
	DECLARE		DIS_DEPOSIT_IFSC_CODE1 		VARCHAR(20);
	DECLARE 	V_BUSINESS_TYPE 		VARCHAR(50);
	DECLARE		V_NET_AMOUNT			DECIMAL (22,7);
	DECLARE LOAN_DISBURSAL_DETAIL_CUR CURSOR FOR SELECT IFNULL(LOAN_NO,'0'),IFNULL(DISBURSAL_DATE,''),IFNULL(MAKER_REMARKS,''),IFNULL(AUTHOR_REMARKS,''),IFNULL(DISBURSAL_TO,''),
					IFNULL(CUSTOMER_ID,''),IFNULL(DISBURSAL_AMOUNT,0.0),IFNULL(ADJUST_TOTAL_PAYABLE,''),IFNULL(ADJUST_TOTAL_RECEIVABLE,''),
					IFNULL(NET_AMOUNT,0.0),IFNULL(FINAL_DISBURSAL,''),IFNULL(LOAN_CURTAILMENT,''),IFNULL(PAY_TO,''),IFNULL(PAYEE_NAME,''),
					IFNULL(PAYMENT_MODE,''),IFNULL(PAYMENT_DATE,''),IFNULL(INSTRUMENT_NO,''),IFNULL(INSTRUMENT_DATE,''),
					IFNULL(BANK_ACCOUNT,''),IFNULL(BANK_ID,''),IFNULL(BRANCH_ID,''),IFNULL(MICR_CODE,''),IFNULL(IFSC_CODE,''),
					IFNULL(PAYMENT_AMOUNT,0.0),IFNULL(TDS_AMOUNT,0.0),IFNULL(REMARKS,''),IFNULL(PAYMENT_FLAG,''),
					IFNULL(DISBURSAL_FLAG,''), IFNULL(DEPOSIT_BANK_ACCOUNT,0),IFNULL(DEPOSIT_BANK_ID,0),IFNULL(DEPOSIT_BRANCH_ID,0),
					IFNULL(DEPOSIT_IFSC_CODE,0),IFNULL(DEPOSIT_MICR_CODE,0)
					FROM cr_small_ticket_disbursal_data WHERE TICKET_ID =I_TICKET_ID AND LOAN_NO=I_LOAN_NO;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = 1;
	/*DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		SET O_ERROR_FLAG = 'E';
		SET O_ERROR_MSG = 'SMALL_TICKET_DISBURSAL_DETAIL_UPLOAD---There is some error in SQL execution';
	END;*/
	DECLARE EXIT HANDLER FOR 1205
	BEGIN
		SET O_ERROR_FLAG = 'E';
		SET O_ERROR_MSG = 'SMALL_TICKET_LOAN_DETAIL_UPLOAD---Record is locked by some other user. try after some time';
	END;
	SET O_ERROR_FLAG = 'S';
	SET AUTOCOMMIT=0;
	SET V_IPSTATUS='Y';
	SET V_RPSTATUS='Y';
	SET V_DISBSTATUS='Y';
	
	OPEN LOAN_DISBURSAL_DETAIL_CUR;
	LOAN_DISBURSAL_DETAIL_CUR_READ : LOOP
		FETCH LOAN_DISBURSAL_DETAIL_CUR INTO V_LOAN_NO,DIS_DISBURSAL_DATE,DIS_MAKER_REMARKS,DIS_AUTHOR_REMARKS,DIS_DISBURSAL_TO,DIS_CUSTOMER_ID,
			DIS_DISBURSAL_AMOUNT,DIS_ADJUST_TOTAL_PAYABLE,DIS_ADJUST_TOTAL_RECEIVABLE,DIS_NET_AMOUNT,DIS_FINAL_DISBURSAL,
			DIS_LOAN_CURTAILMENT,DIS_PAY_TO,DIS_PAYEE_NAME,DIS_PAYMENT_MODE,DIS_PAYMENT_DATE,DIS_INSTRUMENT_NO,DIS_INSTRUMENT_DATE,
			DIS_BANK_ACCOUNT,DIS_BANK_ID,DIS_BRANCH_ID,DIS_MICR_CODE,DIS_IFSC_CODE,DIS_PAYMENT_AMOUNT,DIS_TDS_AMOUNT,DIS_REMARKS,
			DIS_PAYMENT_FLAG,DIS_DISBURSAL_FLAG,DIS_DEPOSIT_BANK_ACCOUNT1,DIS_DEPOSIT_BANK_ID1,DIS_DEPOSIT_BRANCH_ID1,DIS_DEPOSIT_IFSC_CODE1,DIS_DEPOSIT_MICR_CODE1;
			
		SET V_COUNT =0;	
					
		IF DONE = 1 THEN
			SET DONE = 0;
			LEAVE LOAN_DISBURSAL_DETAIL_CUR_READ;
		END IF;
		
		SELECT LOAN_ID,loan_product INTO V_LOAN_ID,V_product FROM CR_LOAN_DTL WHERE LOAN_NO=V_LOAN_NO AND rec_status='A';
		SELECT DATE(PARAMETER_VALUE) INTO V_BUSINESS_DATE FROM PARAMETER_MST WHERE PARAMETER_KEY='BUSINESS_DATE';
		
		IF DONE = 1 THEN
			SET DONE = 0;
			LEAVE LOAN_DISBURSAL_DETAIL_CUR_READ;
		END IF;
		IF  IFNULL(O_ERROR_FLAG, 'E') = 'S' THEN
			IF  (V_LOAN_ID IS NULL)  THEN
				SET O_ERROR_FLAG = 'E';
				SET O_ERROR_MSG = 'LOAN NUMBER IS EMPTY';
			END IF;
		END IF;
    
		IF  IFNULL(O_ERROR_FLAG, 'E') = 'S' THEN
			IF  DIS_DISBURSAL_TO IN ('AN') THEN
				SET O_ERROR_FLAG = 'E';
				SET O_ERROR_MSG = ' Please Select Anchor Name';
			END IF;
		END IF;
		IF O_ERROR_FLAG = 'S' AND DIS_PAYMENT_MODE NOT IN ('Q', 'D', 'R', 'N','S','I','F') THEN
			SET O_ERROR_FLAG = 'E';
			SET O_ERROR_MSG = 'INVALID PAYMENT MODE.';
		END IF;
		
		-- SELECT loan_product INTO V_product FROM cr_loan_dtl WHERE loan_id=V_LOAN_ID;
		SELECT LOAN_STAGE INTO v_stage FROM CR_LOS_WORKFLOW_M WHERE product_id=V_product AND rec_status='A';
	
		IF IFNULL(O_ERROR_FLAG, 'E') = 'S' THEN
			IF (V_STAGE IN ('DIM','DIA')) THEN
				SELECT DISBURSAL_DATE,DISBURSAL_AMOUNT,LOAN_REPAY_EFF_DATE ,SMALL.PAY_TO
				INTO P_DISBURSAL_DATE,P_DISBURSAL_AMOUNT,P_LOAN_REPAY_EFF_DATE,P_PAY_TO FROM
				CR_SMALL_TICKET_DISBURSAL_DATA SMALL
				-- JOIN CR_DEAL_DTL D ON D.DEAL_ID=(SELECT loan_deal_id FROM cr_loan_dtl WHERE loan_no=SMALL.LOAN_NO)
				INNER JOIN CR_LOAN_DTL L ON L.LOAN_NO =SMALL.LOAN_NO 
				WHERE SMALL.TICKET_ID = I_TICKET_ID  AND L.REC_STATUS='A' AND l.loan_id = V_LOAN_ID;
				IF IFNULL(DIS_DISBURSAL_TO,'CS')='SU' THEN
				    SELECT IFNULL(SUM((ADVICE_AMOUNT-(TXN_ADJUSTED_AMOUNT+AMOUNT_IN_PROCESS))),0) INTO V_RECEIVABLE_AMT FROM CR_TXNADVICE_DTL
				    WHERE REC_STATUS IN('A','F')  AND (ADVICE_AMOUNT-(TXN_ADJUSTED_AMOUNT))>0  AND ADVICE_TYPE='R'
				    AND LOAN_ID=V_LOAN_ID AND IFNULL(CHARGE_CODE_ID,0)<>8 AND BP_TYPE ='SU';
				    SELECT SUM((ADVICE_AMOUNT-(TXN_ADJUSTED_AMOUNT+AMOUNT_IN_PROCESS))) INTO V_PAYABLE_AMT FROM cr_txnadvice_dtl  
				    WHERE REC_STATUS IN('A','F')  AND  (ADVICE_AMOUNT-(TXN_ADJUSTED_AMOUNT))>0  AND ADVICE_TYPE='P' AND LOAN_ID=V_LOAN_ID
				    AND BP_TYPE ='SU';
				END IF;
				IF IFNULL(DIS_DISBURSAL_TO,'CS')='CS' THEN
				    SELECT IFNULL(SUM((ADVICE_AMOUNT-(TXN_ADJUSTED_AMOUNT+AMOUNT_IN_PROCESS))),0) INTO V_RECEIVABLE_AMT FROM CR_TXNADVICE_DTL
				    WHERE REC_STATUS IN('A','F')  AND (ADVICE_AMOUNT-(TXN_ADJUSTED_AMOUNT))>0  AND ADVICE_TYPE='R'
				    AND LOAN_ID=V_LOAN_ID AND IFNULL(CHARGE_CODE_ID,0)<>8 AND BP_TYPE ='CS';
				    SELECT SUM((ADVICE_AMOUNT-(TXN_ADJUSTED_AMOUNT+AMOUNT_IN_PROCESS))) INTO V_PAYABLE_AMT FROM cr_txnadvice_dtl  WHERE 
				    REC_STATUS IN('A','F') AND (ADVICE_AMOUNT-(TXN_ADJUSTED_AMOUNT))>0  AND ADVICE_TYPE='P' AND LOAN_ID=V_LOAN_ID AND 
				    BP_TYPE ='CS';
				END IF;
				IF IFNULL(DIS_DISBURSAL_TO,'CS')='MF' THEN
				    SELECT SUM((ADVICE_AMOUNT-(TXN_ADJUSTED_AMOUNT+AMOUNT_IN_PROCESS))) INTO V_PAYABLE_AMT FROM cr_txnadvice_dtl  
				    WHERE REC_STATUS IN('A','F')  AND  (ADVICE_AMOUNT-(TXN_ADJUSTED_AMOUNT))>0  AND ADVICE_TYPE='P' AND LOAN_ID=V_LOAN_ID 
				    AND BP_TYPE ='MF';
				    SELECT SUM((ADVICE_AMOUNT-(TXN_ADJUSTED_AMOUNT+AMOUNT_IN_PROCESS))) INTO V_RECEIVABLE_AMT FROM cr_txnadvice_dtl  
				    WHERE REC_STATUS IN('A','F')  AND  (ADVICE_AMOUNT-(TXN_ADJUSTED_AMOUNT))>0  AND ADVICE_TYPE='R' AND LOAN_ID=V_LOAN_ID 
				    AND IFNULL(CHARGE_CODE_ID,0)<>8  AND BP_TYPE ='MF';
				END IF;
			--	SELECT V_PAYABLE_AMT, DIS_ADJUST_TOTAL_PAYABLE;
			--	SELECT V_RECEIVABLE_AMT, DIS_ADJUST_TOTAL_RECEIVABLE;
				IF  ROUND(IFNULL(DIS_ADJUST_TOTAL_PAYABLE,'0'))>ROUND(IFNULL(V_PAYABLE_AMT,'0')) THEN					
					SET O_ERROR_FLAG = 'E';
					SET O_ERROR_MSG = 'Adjust Total Payable Could Not Be Greater Than Total payable';
				END IF;
				IF  IFNULL(DIS_ADJUST_TOTAL_RECEIVABLE,'0')>IFNULL(V_RECEIVABLE_AMT,'0') THEN					
					SET O_ERROR_FLAG = 'E';
					SET O_ERROR_MSG = 'Adjust Total Receivable Could Not Be Greater Than Total Receivable';
				END IF;
				IF IFNULL(O_ERROR_FLAG, 'E') = 'S' THEN
					SELECT Parameter_value INTO v_parameter_value FROM parameter_mst WHERE parameter_key ='NET_AMOUNT_ADJUST_RECEIALBE_FLAG';
					IF IFNULL(v_parameter_value,'N')='N' THEN
						SET v_receivable_amt=0.0;
					END IF;
					CALL PRE_EMI_CALC_AT_DISBURSAL(P_DISBURSAL_AMOUNT,P_DISBURSAL_DATE,V_LOAN_ID,P_DISBURSAL_DATE,P_LOAN_REPAY_EFF_DATE,
					'SYSTEM','F',O_CURR_PEMI,O_NEXT_MONTH_PEMI,O_ERROR_FLAG,O_ERROR_MSG);
					-- SELECT O_ERROR_FLAG,O_ERROR_MSG,O_CURR_PEMI,O_NEXT_MONTH_PEMI;
					SELECT VALUE INTO V_GENERATE_VALUE FROM generate_sequence_tbl  WHERE SEQ_KEY='DISBURSAL_BATCH_ID' FOR UPDATE;
					-- SELECT '1';
					SET V_GENERATE_VALUE=V_GENERATE_VALUE+1;
					
					-- if v_business_type<> 'LC' then
					SET DISB_count=1;
					-- SELECT 'tej';
					IF IFNULL(P_PAY_TO,'CS')='CS' THEN 
						-- SELECT 'tej';
						IF DIS_DEPOSIT_BANK_ID1=0 AND DIS_DEPOSIT_BRANCH_ID1=0 AND DIS_DEPOSIT_BANK_ACCOUNT1=0 THEN 
							SELECT b.BANK_ACCOUNT,b.BANK_ID,b.BANK_BRANCH_ID,d.BRANCH_MICR_CODE,d.BRANCH_IFCS_CODE INTO 
							DIS_DEPOSIT_BANK_ACCOUNT,DIS_DEPOSIT_BANK_ID,DIS_DEPOSIT_BRANCH_ID,DIS_DEPOSIT_MICR_CODE,
							DIS_DEPOSIT_IFSC_CODE
							FROM business_partner_view v LEFT JOIN cr_loan_dtl deal ON v.LOAN_ID=deal.LOAN_ID 
							LEFT JOIN cust_bank_details_m b ON deal.LOAN_CUSTOMER_ID=b.CUSTOMER_ID 
							LEFT JOIN COM_BANKBRANCH_M d ON d.BANK_BRANCH_ID=b.BANK_BRANCH_ID
							WHERE  deal.loan_id=V_LOAN_ID AND v.BP_TYPE='CS' LIMIT 1; 
						ELSE
							SET DIS_DEPOSIT_BANK_ACCOUNT=DIS_DEPOSIT_BANK_ACCOUNT1;
							SET DIS_DEPOSIT_BANK_ID=DIS_DEPOSIT_BANK_ID1;
							SET DIS_DEPOSIT_BRANCH_ID=DIS_DEPOSIT_BRANCH_ID1;
							SET DIS_DEPOSIT_MICR_CODE=DIS_DEPOSIT_MICR_CODE1;
							SET DIS_DEPOSIT_IFSC_CODE=DIS_DEPOSIT_IFSC_CODE1;							
						END IF;
						
					--	SELECT DIS_DEPOSIT_BANK_ACCOUNT,DIS_DEPOSIT_BANK_ID,DIS_DEPOSIT_BRANCH_ID,DIS_DEPOSIT_MICR_CODE,
					--	DIS_DEPOSIT_IFSC_CODE;
						
						SET V_NET_AMOUNT=((IFNULL(DIS_DISBURSAL_AMOUNT,'0')+IFNULL(DIS_ADJUST_TOTAL_PAYABLE,'0'))-IFNULL(DIS_ADJUST_TOTAL_RECEIVABLE,'0'));
						-- SELECT V_NET_AMOUNT;
					/*	SELECT COUNT(1) INTO V_COUNT
						FROM CR_SMALL_TICKET_DISBURSAL_DATA SMALL
						-- JOIN CR_DEAL_DTL D ON D.DEAL_ID=(SELECT loan_deal_id FROM cr_loan_dtl WHERE loan_no=SMALL.LOAN_NO)
						INNER JOIN CR_LOAN_DTL L ON L.LOAN_NO =SMALL.LOAN_NO
						INNER JOIN gcd_customer_m ON gcd_customer_m.customer_id=L.loan_customer_id
						WHERE SMALL.TICKET_ID = I_TICKET_ID  AND L.REC_STATUS='A' AND SMALL.LOAN_NO=V_LOAN_NO;
						*/ -- SELECT v_count,'aqib';
						-- select ifnull(advice_amount,0.0)-ifnull(txn_adjusted_amount,0.0) into disbursedAmount  from cr_txnadvice_dtl 
						-- where loan_id = v_loan_id and charge_code_id =27 and ifnull(advice_amount,0.0)-ifnull(txn_adjusted_amount,0.0)>0;
						INSERT INTO cr_loan_disbursal_dtl_temp(LOAN_ID,disbursal_no,disbursal_date,disbursal_to,disbursal_to_id,DISBURSAL_AMOUNT,
						ADJUST_TOTAL_PAYABLE,short_amount_adjusted,NET_AMOUNT,disbursal_flag,loan_curtail,PAY_TO,PAYEE_NAME,pmnt_mode,
						pmnt_date,instrument_no,instrument_date,bank_account,bank_id,branch_id,micr_code,ifsc_code,pmnt_amount,tds_amount,pmnt_remark,
						remarks,PAYMENT_FLAG,MAKER_ID,MAKER_DATE,REC_STATUS,DISBURSAL_BATCH_ID,REPAY_EFF_DATE,LOAN_DUE_DAY,NEXT_DUE_DATE,cur_month_emi,
						next_month_emi,disbursal_description,DEPOSIT_BANK_ACCOUNT,DEPOSIT_BANK_ID,DEPOSIT_BRANCH_ID,DEPOSIT_MICR_CODE,DEPOSIT_IFSC_CODE)
						SELECT L.LOAN_ID,DISB_count,SMALL.DISBURSAL_DATE,'CS',loan_customer_id,DISBURSAL_AMOUNT,
						ADJUST_TOTAL_PAYABLE, ADJUST_TOTAL_RECEIVABLE,IFNULL(V_NET_AMOUNT,'0'),DISBURSAL_FLAG,IF(l.loan_repayment_type='N','N',LOAN_CURTAILMENT),SMALL.PAY_TO,
						PAYEE_NAME,PAYMENT_MODE,PAYMENT_DATE,INSTRUMENT_NO,INSTRUMENT_DATE,BANK_ACCOUNT,BANK_ID,BRANCH_ID,MICR_CODE,IFSC_CODE,PAYMENT_AMOUNT,TDS_AMOUNT,IF(IFNULL(REMARKS,'')='','OK',REMARKS)REMARKS,
						IF(IFNULL(AUTHOR_REMARKS,'')='','OK',AUTHOR_REMARKS)REMARKS,IF((SELECT IFNULL(autodisbursal,'N') FROM cr_scheme_m WHERE scheme_id=l.loan_scheme)='Y','P','Y'),D.MAKER_ID,D.MAKER_DATE,'P',V_GENERATE_VALUE,L.LOAN_REPAY_EFF_DATE,
						IF(l.loan_repayment_type='N',NULL,L.LOAN_DUE_DAY),IF(l.loan_repayment_type='N',NULL,L.NEXT_DUE_DATE),O_CURR_PEMI,O_NEXT_MONTH_PEMI,SMALL.MAKER_REMARKS,
						DIS_DEPOSIT_BANK_ACCOUNT,DIS_DEPOSIT_BANK_ID,DIS_DEPOSIT_BRANCH_ID,DIS_DEPOSIT_MICR_CODE,DIS_DEPOSIT_IFSC_CODE
						FROM CR_SMALL_TICKET_DISBURSAL_DATA SMALL
						
						 JOIN CR_LOAN_DTL L ON L.LOAN_NO =SMALL.LOAN_NO
						JOIN CR_DEAL_DTL D ON D.DEAL_ID=L.loan_deal_id
						LEFT JOIN gcd_customer_m ON gcd_customer_m.customer_id=L.loan_customer_id
						WHERE SMALL.TICKET_ID = I_TICKET_ID  AND L.REC_STATUS='A' AND SMALL.LOAN_NO=V_LOAN_NO;
						-- SELECT 'disb';
						SET DISB_count=DISB_count+1;
						SET O_GENERATE_VALUE=V_GENERATE_VALUE;
						-- SELECT '2',I_LOAN_NO,V_COUNT;
					END IF;
					IF IFNULL(P_PAY_TO,'CS')='SU' THEN 
						SET V_COUNT=0;
						-- select count(1) into V_COUNT from cr_dsa_dealer_m where BP_TYPE=DIS_DISBURSAL_TO and a.DEALER_ID=DIS_CUSTOMER_ID;
						SELECT COUNT(BP_ID) INTO V_COUNT FROM business_partner_view a
						LEFT JOIN cr_dsa_dealer_m b ON b.dealer_id=a.bp_id  WHERE 1=1  AND LOAN_ID=V_LOAN_ID AND a.BP_TYPE=P_PAY_TO AND bp_id=DIS_CUSTOMER_ID;
						
						SET V_NET_AMOUNT=((IFNULL(DIS_DISBURSAL_AMOUNT,'0')+IFNULL(DIS_ADJUST_TOTAL_PAYABLE,'0'))-IFNULL(DIS_ADJUST_TOTAL_RECEIVABLE,'0'));
						SELECT V_NET_AMOUNT;
						-- select ifnull(advice_amount,0.0)-ifnull(txn_adjusted_amount,0.0) into disbursedAmount  from cr_txnadvice_dtl 
						-- where loan_id = v_loan_id and charge_code_id =27 and ifnull(advice_amount,0.0)-ifnull(txn_adjusted_amount,0.0)>0;
						SELECT V_COUNT,V_GENERATE_VALUE,V_LOAN_ID;
						IF(V_COUNT >0) THEN 
							SELECT V_NET_AMOUNT,V_GENERATE_VALUE,V_LOAN_ID,I_TICKET_ID,DISB_count,O_CURR_PEMI,O_NEXT_MONTH_PEMI,DIS_CUSTOMER_ID;
							INSERT INTO cr_loan_disbursal_dtl(LOAN_ID,disbursal_no,disbursal_date,disbursal_to,disbursal_to_id,DISBURSAL_AMOUNT,
							ADJUST_TOTAL_PAYABLE,short_amount_adjusted,NET_AMOUNT,disbursal_flag,loan_curtail,PAY_TO,PAYEE_NAME,pmnt_mode,
							pmnt_date,instrument_no,instrument_date,bank_account,bank_id,branch_id,micr_code,ifsc_code,pmnt_amount,tds_amount,
							pmnt_remark,remarks,PAYMENT_FLAG,MAKER_ID,MAKER_DATE,REC_STATUS,DISBURSAL_BATCH_ID,REPAY_EFF_DATE,LOAN_DUE_DAY,
							NEXT_DUE_DATE,cur_month_emi,next_month_emi,disbursal_description,DEPOSIT_BANK_ID,DEPOSIT_BRANCH_ID,
							DEPOSIT_IFSC_CODE,DEPOSIT_BANK_ACCOUNT,DEPOSIT_MICR_CODE)
							SELECT L.LOAN_ID,1,SMALL.DISBURSAL_DATE,DISBURSAL_TO,loan_customer_id,DISBURSAL_AMOUNT,
							ADJUST_TOTAL_PAYABLE,ADJUST_TOTAL_RECEIVABLE,IFNULL(V_NET_AMOUNT,'0'),
							DISBURSAL_FLAG,IF(l.loan_repayment_type='N','N',LOAN_CURTAILMENT),SMALL.PAY_TO,gcd_customer_m.customer_name PAYEE_NAME,
							PAYMENT_MODE,PAYMENT_DATE,INSTRUMENT_NO,INSTRUMENT_DATE,SMALL.BANK_ACCOUNT,SMALL.BANK_ID,SMALL.BRANCH_ID,
							IF(IFNULL(SMALL.MICR_CODE,'')='','0',SMALL.MICR_CODE)MICR_CODE,
							IF(IFNULL(SMALL.IFSC_CODE,'')='','0',SMALL.IFSC_CODE)IFSC_CODE, PAYMENT_AMOUNT,TDS_AMOUNT,
							IF(IFNULL(REMARKS,'')='','OK',REMARKS)pmnt_remark,IF(IFNULL(AUTHOR_REMARKS,'')='','OK',AUTHOR_REMARKS)REMARKS,
							PAYMENT_FLAG,D.MAKER_ID,D.MAKER_DATE,'P'
							,V_GENERATE_VALUE,L.LOAN_REPAY_EFF_DATE,IF(l.loan_repayment_type='N',NULL,L.LOAN_DUE_DAY),
							IF(l.loan_repayment_type='N',NULL,L.NEXT_DUE_DATE)
							,O_CURR_PEMI,O_NEXT_MONTH_PEMI,SMALL.MAKER_REMARKS,
							IF(DISBURSAL_TO='SU',cr_dsa_dealer_m.bank_id,cust_bank_details_m.bank_id),
							IF(DISBURSAL_TO='SU',cr_dsa_dealer_m.bank_branch_id,cust_bank_details_m.bank_branch_id),
							IF(DISBURSAL_TO='SU',cr_dsa_dealer_m.branch_ifcs_code,cust_bank_details_m.branch_ifcs_code),
							IF(DISBURSAL_TO='SU',cr_dsa_dealer_m.bank_account,cust_bank_details_m.bank_account),
							IF(DISBURSAL_TO='SU',cr_dsa_dealer_m.branch_micr_code,cust_bank_details_m.branch_micr_code)
							FROM CR_SMALL_TICKET_DISBURSAL_DATA SMALL
							JOIN CR_DEAL_DTL D ON D.DEAL_ID=(SELECT loan_deal_id FROM cr_loan_dtl WHERE loan_id=V_LOAN_ID)
							LEFT JOIN CR_LOAN_DTL L ON D.DEAL_ID =L.LOAN_DEAL_ID
							LEFT JOIN gcd_customer_m ON gcd_customer_m.customer_id=L.loan_customer_id
							LEFT JOIN (SELECT customer_id,bank_id,bank_branch_id,branch_ifcs_code, bank_account,branch_micr_code FROM cust_bank_details_m 
							GROUP BY customer_id ) cust_bank_details_m ON cust_bank_details_m.customer_id=gcd_customer_m.customer_id
							LEFT JOIN cr_dsa_dealer_m ON cr_dsa_dealer_m.dealer_id=DIS_CUSTOMER_ID
							WHERE D.DEAL_ID=(SELECT loan_deal_id FROM cr_loan_dtl WHERE loan_id=V_LOAN_ID) 
							AND SMALL.TICKET_ID = I_TICKET_ID AND L.REC_STATUS='A' AND SMALL.LOAN_NO=V_LOAN_NO;
							SET DISB_count=DISB_count+1;
							SET O_GENERATE_VALUE=V_GENERATE_VALUE;
						SELECT I_LOAN_NO;
						ELSE
							SET O_ERROR_FLAG = 'E';
							SET O_ERROR_MSG = 'CUSTOMER ID IS WRONG FOR THIS SUPPLIER';
						END IF;
						
						
					END IF;
					IF IFNULL(P_PAY_TO,'CS')='MF' THEN 
						SET V_COUNT=0;
						-- select count(1) into V_COUNT from cr_dsa_dealer_m where BP_TYPE=DIS_DISBURSAL_TO and a.DEALER_ID=DIS_CUSTOMER_ID;
						SELECT COUNT(BP_ID) INTO V_COUNT FROM business_partner_view a
						LEFT JOIN cr_dsa_dealer_m b ON b.dealer_id=a.bp_id  WHERE 1=1  AND LOAN_ID=V_LOAN_ID AND a.BP_TYPE=P_PAY_TO AND bp_id=DIS_CUSTOMER_ID;
						
						SET V_NET_AMOUNT=((IFNULL(DIS_DISBURSAL_AMOUNT,'0')+IFNULL(DIS_ADJUST_TOTAL_PAYABLE,'0'))-IFNULL(DIS_ADJUST_TOTAL_RECEIVABLE,'0'));
						SELECT V_NET_AMOUNT;
						-- select ifnull(advice_amount,0.0)-ifnull(txn_adjusted_amount,0.0) into disbursedAmount  from cr_txnadvice_dtl 
						-- where loan_id = v_loan_id and charge_code_id =27 and ifnull(advice_amount,0.0)-ifnull(txn_adjusted_amount,0.0)>0;
						SELECT V_COUNT;
						IF(V_COUNT >0) THEN 
							
							INSERT INTO cr_loan_disbursal_dtl_temp(LOAN_ID,disbursal_no,disbursal_date,disbursal_to,disbursal_to_id,DISBURSAL_AMOUNT,
							ADJUST_TOTAL_PAYABLE,short_amount_adjusted,NET_AMOUNT,disbursal_flag,loan_curtail,PAY_TO,PAYEE_NAME,pmnt_mode,
							pmnt_date,instrument_no,instrument_date,bank_account,bank_id,branch_id,micr_code,ifsc_code,pmnt_amount,tds_amount,
							pmnt_remark,remarks,PAYMENT_FLAG,MAKER_ID,MAKER_DATE,REC_STATUS,DISBURSAL_BATCH_ID,REPAY_EFF_DATE,LOAN_DUE_DAY,
							NEXT_DUE_DATE,cur_month_emi,next_month_emi,disbursal_description,DEPOSIT_BANK_ID,DEPOSIT_BRANCH_ID,
							DEPOSIT_IFSC_CODE,DEPOSIT_BANK_ACCOUNT,DEPOSIT_MICR_CODE)
							SELECT L.LOAN_ID,1,SMALL.DISBURSAL_DATE,DISBURSAL_TO,loan_customer_id,DISBURSAL_AMOUNT,
							ADJUST_TOTAL_PAYABLE,ADJUST_TOTAL_RECEIVABLE,IFNULL(V_NET_AMOUNT,'0'),
							DISBURSAL_FLAG,IF(l.loan_repayment_type='N','N',LOAN_CURTAILMENT),SMALL.PAY_TO,gcd_customer_m.customer_name PAYEE_NAME,
							PAYMENT_MODE,PAYMENT_DATE,INSTRUMENT_NO,INSTRUMENT_DATE,SMALL.BANK_ACCOUNT,SMALL.BANK_ID,SMALL.BRANCH_ID,
							IF(IFNULL(SMALL.MICR_CODE,'')='','0',SMALL.MICR_CODE)MICR_CODE,
							IF(IFNULL(SMALL.IFSC_CODE,'')='','0',SMALL.IFSC_CODE)IFSC_CODE, PAYMENT_AMOUNT,TDS_AMOUNT,
							IF(IFNULL(REMARKS,'')='','OK',REMARKS)pmnt_remark,IF(IFNULL(AUTHOR_REMARKS,'')='','OK',AUTHOR_REMARKS)REMARKS,
							PAYMENT_FLAG,D.MAKER_ID,D.MAKER_DATE,'P'
							,V_GENERATE_VALUE,L.LOAN_REPAY_EFF_DATE,IF(l.loan_repayment_type='N',NULL,L.LOAN_DUE_DAY),
							IF(l.loan_repayment_type='N',NULL,L.NEXT_DUE_DATE)
							,O_CURR_PEMI,O_NEXT_MONTH_PEMI,SMALL.MAKER_REMARKS,
							IF(DISBURSAL_TO='MF',cr_dsa_dealer_m.bank_id,cust_bank_details_m.bank_id),
							IF(DISBURSAL_TO='MF',cr_dsa_dealer_m.bank_branch_id,cust_bank_details_m.bank_branch_id),
							IF(DISBURSAL_TO='MF',cr_dsa_dealer_m.branch_ifcs_code,cust_bank_details_m.branch_ifcs_code),
							IF(DISBURSAL_TO='MF',cr_dsa_dealer_m.bank_account,cust_bank_details_m.bank_account),
							IF(DISBURSAL_TO='MF',cr_dsa_dealer_m.branch_micr_code,cust_bank_details_m.branch_micr_code)
							FROM CR_SMALL_TICKET_DISBURSAL_DATA SMALL
							JOIN CR_DEAL_DTL D ON D.DEAL_ID=(SELECT loan_deal_id FROM cr_loan_dtl WHERE loan_id=V_LOAN_ID)
							LEFT JOIN CR_LOAN_DTL L ON D.DEAL_ID =L.LOAN_DEAL_ID
							LEFT JOIN gcd_customer_m ON gcd_customer_m.customer_id=L.loan_customer_id
							LEFT JOIN (SELECT customer_id,bank_id,bank_branch_id,branch_ifcs_code, bank_account,branch_micr_code FROM cust_bank_details_m 
							GROUP BY customer_id ) cust_bank_details_m ON cust_bank_details_m.customer_id=gcd_customer_m.customer_id
							LEFT JOIN cr_dsa_dealer_m ON cr_dsa_dealer_m.dealer_id=DIS_CUSTOMER_ID
							WHERE D.DEAL_ID=(SELECT loan_deal_id FROM cr_loan_dtl WHERE loan_id=V_LOAN_ID) 
							AND SMALL.TICKET_ID = I_TICKET_ID AND L.REC_STATUS='A' AND SMALL.LOAN_NO=V_LOAN_NO;
							SET DISB_count=DISB_count+1;
							SET O_GENERATE_VALUE=V_GENERATE_VALUE;
						ELSE
							SET O_ERROR_FLAG = 'E';
							SET O_ERROR_MSG = CONCAT('CUSTOMER ID IS WRONG FOR THIS ','- ',PAY_TO);
						END IF;
						IF IFNULL(P_PAY_TO,'CS')='OTH' THEN 
							SELECT 'tej';
							
							SET V_NET_AMOUNT=((IFNULL(DIS_DISBURSAL_AMOUNT,'0')+IFNULL(DIS_ADJUST_TOTAL_PAYABLE,'0'))-IFNULL(DIS_ADJUST_TOTAL_RECEIVABLE,'0'));
							SELECT V_NET_AMOUNT;
							-- select ifnull(advice_amount,0.0)-ifnull(txn_adjusted_amount,0.0) into disbursedAmount  from cr_txnadvice_dtl 
							-- where loan_id = v_loan_id and charge_code_id =27 and ifnull(advice_amount,0.0)-ifnull(txn_adjusted_amount,0.0)>0;
							INSERT INTO cr_loan_disbursal_dtl_temp(LOAN_ID,disbursal_no,disbursal_date,disbursal_to,disbursal_to_id,DISBURSAL_AMOUNT,
							ADJUST_TOTAL_PAYABLE,short_amount_adjusted,NET_AMOUNT,disbursal_flag,loan_curtail,PAY_TO,PAYEE_NAME,pmnt_mode,
							pmnt_date,instrument_no,instrument_date,bank_account,bank_id,branch_id,micr_code,ifsc_code,pmnt_amount,tds_amount,pmnt_remark,
							remarks,PAYMENT_FLAG,MAKER_ID,MAKER_DATE,REC_STATUS,DISBURSAL_BATCH_ID,REPAY_EFF_DATE,LOAN_DUE_DAY,NEXT_DUE_DATE,cur_month_emi,
							next_month_emi,disbursal_description,DEPOSIT_BANK_ACCOUNT,DEPOSIT_BANK_ID,DEPOSIT_BRANCH_ID,DEPOSIT_MICR_CODE,DEPOSIT_IFSC_CODE)
							SELECT L.LOAN_ID,DISB_count,SMALL.DISBURSAL_DATE,'OTH',loan_customer_id,DISBURSAL_AMOUNT,
							ADJUST_TOTAL_PAYABLE, ADJUST_TOTAL_RECEIVABLE,IFNULL(V_NET_AMOUNT,'0'),DISBURSAL_FLAG,IF(l.loan_repayment_type='N','N',LOAN_CURTAILMENT),SMALL.PAY_TO,
							PAYEE_NAME,PAYMENT_MODE,PAYMENT_DATE,INSTRUMENT_NO,INSTRUMENT_DATE,BANK_ACCOUNT,BANK_ID,BRANCH_ID,MICR_CODE,IFSC_CODE,PAYMENT_AMOUNT,TDS_AMOUNT,IF(IFNULL(REMARKS,'')='','OK',REMARKS)REMARKS,
							IF(IFNULL(AUTHOR_REMARKS,'')='','OK',AUTHOR_REMARKS)REMARKS,PAYMENT_FLAG,D.MAKER_ID,D.MAKER_DATE,'P',V_GENERATE_VALUE,L.LOAN_REPAY_EFF_DATE,
							IF(l.loan_repayment_type='N',NULL,L.LOAN_DUE_DAY),IF(l.loan_repayment_type='N',NULL,L.NEXT_DUE_DATE),O_CURR_PEMI,O_NEXT_MONTH_PEMI,SMALL.MAKER_REMARKS,
							DIS_DEPOSIT_BANK_ACCOUNT,DIS_DEPOSIT_BANK_ID,DIS_DEPOSIT_BRANCH_ID,DIS_DEPOSIT_MICR_CODE,DIS_DEPOSIT_IFSC_CODE
							FROM CR_SMALL_TICKET_DISBURSAL_DATA SMALL
							JOIN CR_DEAL_DTL D ON D.DEAL_ID=(SELECT loan_deal_id FROM cr_loan_dtl WHERE loan_no=SMALL.LOAN_NO)
							LEFT JOIN CR_LOAN_DTL L ON D.DEAL_ID =L.LOAN_DEAL_ID
							LEFT JOIN gcd_customer_m ON gcd_customer_m.customer_id=L.loan_customer_id
							WHERE SMALL.TICKET_ID = I_TICKET_ID  AND L.REC_STATUS='A' AND SMALL.LOAN_NO=V_LOAN_NO;
							
							SET DISB_count=DISB_count+1;
							SET O_GENERATE_VALUE=V_GENERATE_VALUE;
							-- SELECT '2';
						END IF;
						
					END IF;
UPDATE  GENERATE_SEQUENCE_TBL SET VALUE=V_GENERATE_VALUE WHERE SEQ_KEY='DISBURSAL_BATCH_ID';
					-- END IF;
				END IF;
			END IF;
		END IF;
	END LOOP;
	CLOSE LOAN_DISBURSAL_DETAIL_CUR;
    END

---

