CREATE DEFINER = `devadmin`@` % ` PROCEDURE `SMALL_TICKET_LOAN_DETAIL_UPLOAD`(IN I_TICKET_ID VARCHAR(10) , OUT uploaded_records VARCHAR(1), OUT rejected_records VARCHAR(1), OUT O_ERROR_FLAG VARCHAR(1), OUT O_ERROR_MSG VARCHAR(9000) ) 
BEGIN
   DECLARE V_UPLOAD_ID INT(10);
DECLARE DONE INT(2);
DECLARE V_TICKET_ID INT(10);
DECLARE V_LOAN_ID VARCHAR(30);
DECLARE V_LOAN_AMT DECIMAL(22, 4);
DECLARE V_TENURE VARCHAR(50) DEFAULT 1;
DECLARE V_ROI VARCHAR(20);
DECLARE V_DUE_DAY VARCHAR(50);
DECLARE V_REPAYMENT_MODE VARCHAR(20);
DECLARE V_REPAYMENT_DATE DATE;
DECLARE V_NEXT_DUE_DAY INT(2);
DECLARE V_NEXT_DUE_DATE DATE;
DECLARE V_MAKER_REMARKS VARCHAR(500);
DECLARE V_TO_INSTALLMENT VARCHAR(3) DEFAULT 1;
DECLARE V_INSTALLMENT_AMOUNT DECIMAL(22, 4);
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_REPAYMENT_TYPE VARCHAR(3);
DECLARE V_LOAN_AMT_temp DECIMAL(22, 4);
DECLARE V_LOAN_ID_temp VARCHAR(30);
DECLARE V_DEAL_UTILIZED_AMOUNT DECIMAL (22, 7);
DECLARE V_DEAL_SANCTION_AMOUNT 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_receivable_amt DECIMAL (22, 7);
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 O_GENERATE_VALUE INT(10);
DECLARE V_NET_AMOUNT DECIMAL (22, 7);
DECLARE V_PMNT_AMOUNT DECIMAL (22, 7);
DECLARE V_DISBURSAL_TEST1_ID VARCHAR(50);
DECLARE V_DISBURSAL_TEST2_ID VARCHAR(50);
DECLARE LOAN_DETAIL_CUR CURSOR FOR 
SELECT
   UPLOAD_ID,
   LOAN_NO,
   IFNULL(LOAN_AMT, ''),
   IFNULL(TENURE, 1),
   IFNULL(ROI, ''),
   IFNULL(REPAYMENT_MODE, ''),
   IFNULL(REPAYMENT_DATE, 0),
   DUE_DAY,
   IFNULL(NEXT_DUE_DATE, 0),
   IFNULL(MAKER_REMARKS, 0.0),
   IFNULL(TO_INSTALLMENT, 1),
   IFNULL(INSTALLMENT_AMOUNT, ''),
   IFNULL(MAKER_ID, ''),
   MAKER_DATE 
FROM
   cr_small_ticket_laon_data 
WHERE
   TICKET_ID = I_TICKET_ID 
   AND IFNULL(Validate_flag, '') NOT IN 
   (
      'Y',
      'N'
   )
;
DECLARE LOAN_DISBURSAL_DETAIL_CUR CURSOR FOR 
SELECT
   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(DIS_DISBURSAL_FLAG, '') 
FROM
   cr_small_ticket_disbursal_data 
WHERE
   TICKET_ID = I_TICKET_ID 
   AND IFNULL(Validate_flag, '') NOT IN 
   (
      'Y',
      'N'
   )
;
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_LOAN_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_DETAIL_CUR;
LOAN_DETAIL_CUR_READ : LOOP -- SELECT '21';
FETCH LOAN_DETAIL_CUR INTO V_UPLOAD_ID,
V_LOAN_NO,
V_LOAN_AMT,
V_TENURE,
V_ROI,
V_REPAYMENT_MODE,
V_REPAYMENT_DATE,
V_DUE_DAY,
V_NEXT_DUE_DATE,
V_MAKER_REMARKS,
V_TO_INSTALLMENT,
V_INSTALLMENT_AMOUNT,
V_MAKER_ID,
V_MAKER_DATE;
IF DONE = 1 
THEN
SET
   DONE = 0;
LEAVE LOAN_DETAIL_CUR_READ;
END
IF;
SELECT
   COUNT(1),
   LOAN_ID INTO V_COUNT,
   V_LOAN_ID 
FROM
   CR_LOAN_DTL 
WHERE
   LOAN_NO = V_LOAN_NO 
   AND rec_status = 'P';
SELECT
   DATE(PARAMETER_VALUE) INTO V_BUSINESS_DATE 
FROM
   PARAMETER_MST 
WHERE
   PARAMETER_KEY = 'BUSINESS_DATE';
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
   SELECT
      COUNT(1),
      DEAL_UTILIZED_AMOUNT,
      DEAL_SANCTION_AMOUNT INTO V_COUNT,
      V_DEAL_UTILIZED_AMOUNT,
      V_DEAL_SANCTION_AMOUNT 
   FROM
      cr_loan_dtl 
      INNER JOIN
         CR_DEAL_LOAN_DTL 
         ON(Cr_loan_dtl.loan_deal_id = CR_DEAL_LOAN_DTL.deal_id) 
   WHERE
      -- DEAL_ID =(SELECT LOAN_DEAL_ID FROM CR_LOAN_DTL WHERE LOAN_ID = V_LOAN_ID)
      Cr_loan_dtl.LOAN_ID = V_LOAN_ID 
      AND DEAL_SANCTION_AMOUNT >= 
      (
         IFNULL(DEAL_UTILIZED_AMOUNT, 0) + IFNULL(V_LOAN_AMT, 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;
-- SELECT COUNT(1) INTO V_COUNT FROM cr_loan_dtl WHERE LOAN_ID=V_LOAN_ID AND rec_status='P';
IF IFNULL(O_ERROR_FLAG, 'E') = 'S' 
THEN
   IF V_COUNT = 1 
THEN
   UPDATE
      cr_loan_dtl 
   SET
      LOAN_LOAN_AMOUNT = V_LOAN_AMT, LOAN_TENURE = V_TENURE, LOAN_FINAL_RATE = V_ROI, LOAN_REPAYMENT_MODE = V_REPAYMENT_MODE, LOAN_REPAY_EFF_DATE = V_REPAYMENT_DATE, LOAN_DUE_DAY = IFNULL(V_DUE_DAY, LOAN_DUE_DAY), NEXT_DUE_DATE = V_NEXT_DUE_DATE, LOAN_MAKER_REMARKS = V_MAKER_REMARKS, LOAN_NO_OF_INSTALLMENT = V_TO_INSTALLMENT 
   WHERE
      loan_id = V_LOAN_ID 
      AND REC_STATUS = 'P';
SELECT
   LOAN_LOAN_AMOUNT,
   loan_id,
   loan_repayment_type,
   loan_product INTO V_LOAN_AMT_temp,
   V_LOAN_ID_temp,
   V_REPAYMENT_TYPE,
   V_product 
FROM
   cr_loan_dtl 
WHERE
   LOAN_ID = V_LOAN_ID limit 1;
ELSE
SET
   DONE = 0;
SET
   O_ERROR_FLAG = 'E';
SET
   O_ERROR_MSG = 'LOAN DOES NOT EXIST';
END
IF;
IF IFNULL(O_ERROR_FLAG, 'E') = 'S' 
THEN
   /*SELECT DEAL_REPAYMENT_TYPE INTO V_REPAYMENT_TYPE FROM cr_deal_loan_dtl 
            WHERE deal_id=(SELECT loan_deal_id FROM cr_loan_dtl WHERE LOAN_ID=V_LOAN_ID) LIMIT 1;
         */
   IF(IFNULL(V_REPAYMENT_TYPE, 'N') = 'I')
THEN
   DELETE
   FROM
      CR_INSTALLMENT_PLAN 
   WHERE
      LOAN_ID = V_LOAN_ID;
INSERT INTO
   cr_installment_plan (LOAN_ID, FROM_INSTL_NO, TO_INSTL_NO, RECOVERY_PERCENT, PRINCIPAL_AMOUNT, INSTALLMENT_AMOUNT, REC_STATUS, SEQ_NO, RECOVERY_TYPE, MAKER_ID, MAKER_DATE) 
VALUES
   (
      V_LOAN_ID, '1', V_TO_INSTALLMENT, '0', V_LOAN_AMT, V_INSTALLMENT_AMOUNT, 'A', '1', 'F', V_MAKER_ID, V_BUSINESS_DATE
   )
;
ELSE
SET
   V_IPSTATUS = 'N';
SET
   V_RPSTATUS = 'N';
SET
   V_DISBSTATUS = 'N';
END
IF;
IF IFNULL(O_ERROR_FLAG, 'E') = 'S' 
THEN
   CALL GENERATE_REPAYMENT_SCHEDULE('LIM', V_LOAN_ID, O_ERROR_FLAG, O_ERROR_MSG);
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 (v_stage IN 
(
   'LIA',
   'DIA',
   'DIM'
)
) 
THEN
   UPDATE
      cr_loan_dtl 
   SET
      rec_status = 'F', maker_id = V_MAKER_ID, maker_date = V_MAKER_DATE 
   WHERE
      LOAN_ID = V_LOAN_ID;
COMMIT;
SELECT
   LOAN_LOAN_AMOUNT,
   loan_id INTO V_LOAN_AMT_temp,
   V_LOAN_ID_temp 
FROM
   cr_loan_dtl 
WHERE
   LOAN_ID = V_LOAN_ID;
-- SELECT V_LOAN_AMT_temp,'v_loan_amt',V_LOAN_ID_temp;
CALL `Loan_Authorization`(1, V_LOAN_ID, V_MAKER_DATE, V_MAKER_ID, 'A', 'LOAN AUTO APPROVAL', O_ERROR_FLAG, O_ERROR_MSG);
COMMIT;
END
IF;
END
IF;
END
IF;
IF IFNULL(O_ERROR_FLAG, 'E') = 'S' 
THEN
   IF (V_STAGE IN 
   (
      'DIM', 'DIA'
   )
) 
THEN
   CALL `SMALL_TICKET_DISBURSAL_DETAIL_UPLOAD`(I_TICKET_ID, V_LOAN_NO, O_GENERATE_VALUE, O_ERROR_FLAG, O_ERROR_MSG);
SET
   V_GENERATE_VALUE = O_GENERATE_VALUE;
SELECT
   NET_AMOUNT,
   PMNT_AMOUNT INTO V_NET_AMOUNT,
   V_PMNT_AMOUNT 
FROM
   cr_loan_disbursal_dtl_temp 
WHERE
   loan_id = V_LOAN_ID 
   AND REC_STATUS = 'P';
IF V_PMNT_AMOUNT > V_NET_AMOUNT 
THEN
SET
   O_ERROR_FLAG = 'E';
SET
   O_ERROR_MSG = CONCAT('PAYMENT AMOUNTF SHOULD NOT BE GREATER THAN NET AMOUNT : ', V_NET_AMOUNT);
END
IF;
IF IFNULL(O_ERROR_FLAG, 'E') = 'S' 
THEN
   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_ACCOUNT, DEPOSIT_BANK_ID, DEPOSIT_BRANCH_ID, DEPOSIT_MICR_CODE, DEPOSIT_IFSC_CODE) 
      SELECT
         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 
      FROM
         cr_loan_disbursal_dtl_temp 
      WHERE
         loan_id = V_LOAN_ID 
         AND REC_STATUS = 'P';
END
IF;
SELECT
   COUNT(1) INTO V_COUNT 
FROM
   cr_loan_disbursal_dtl 
WHERE
   loan_id = V_LOAN_ID 
   AND REC_STATUS = 'P';
--  select LOAN_DISBURSAL_ID into V_DISBURSAL_TEST1_ID from cr_loan_disbursal_dtl_temp	where loan_id=V_LOAN_ID limit 1;
-- select LOAN_DISBURSAL_ID into V_DISBURSAL_TEST2_ID from cr_loan_disbursal_dtl where loan_id=V_LOAN_ID limit 1;
IF V_COUNT > 0 
THEN
   -- update cr_loan_disbursal_dtl set rec_status='F' where loan_id=V_LOAN_ID;
   DELETE
   FROM
      cr_loan_disbursal_dtl_temp 
   WHERE
      DISBURSAL_BATCH_ID = V_GENERATE_VALUE;
END
IF;
-- select LAST_INSERT_ID() into V_LAST_INSERT_ID;
-- UPDATE  GENERATE_SEQUENCE_TBL SET VALUE=V_GENERATE_VALUE WHERE SEQ_KEY='DISBURSAL_BATCH_ID';
-- UPDATE CR_RESCH_INSTALLMENT_PLAN SET LOAN_DISBURSAL_ID=V_DISBURSAL_TEST2_ID WHERE LOAN_DISBURSAL_ID_TEMP =V_DISBURSAL_TEST1_ID  AND LOAN_ID=V_LOAN_ID;
--  UPDATE CR_LOAN_CHARGE_PLAN_DTL SET LOAN_DISBURSAL_ID=V_DISBURSAL_TEST2_ID WHERE LOAN_DISBURSAL_ID_TEMP =V_DISBURSAL_TEST1_ID  AND LOAN_ID=V_LOAN_ID;
IF IFNULL(O_ERROR_FLAG, 'E') = 'S' 
AND 
(
   V_STAGE IN 
   (
      'DIA'
   )
)
THEN
   UPDATE
      cr_loan_disbursal_dtl 
   SET
      rec_status = 'F' 
   WHERE
      loan_id = V_LOAN_ID;
SELECT
   V_LOAN_ID,
   (
      SELECT
         rec_status 
      FROM
         cr_loan_disbursal_dtl 
      WHERE
         loan_id = V_LOAN_ID
   )
   rec_status,
   V_BUSINESS_DATE,
   V_MAKER_ID;
-- COMMIT;
CALL `DISBURSAL_AUTHORIZATION`(1, V_LOAN_ID, V_GENERATE_VALUE, V_BUSINESS_DATE, V_MAKER_ID, 'A', 'LOAN AUTO DISBURSE', O_ERROR_FLAG, O_ERROR_MSG);
-- SELECT COUNT(1) INTO V_COUNT FROM CR_INSTRUMENT_DTL WHERE TXNID = V_LOAN_ID AND REC_STATUS = 'A' AND INSTRUMENT_TYPE ='P';
-- select 'DISBURSAL_AUTHORIZATION done',O_ERROR_FLAG;
END
IF;
END
IF;
END
IF;
-- select O_ERROR_FLAG,V_UPLOAD_ID,'V_UPLOAD_ID';
IF IFNULL(O_ERROR_FLAG, 'E') = 'S' 
THEN
   UPDATE
      cr_small_ticket_laon_data 
   SET
      validate_flag = 'Y', error_code = '', error_msg = '' 
   WHERE
      TICKET_ID = I_TICKET_ID 
      AND UPLOAD_ID = V_UPLOAD_ID;
COMMIT;
ELSE
   -- ROLLBACK;
   UPDATE
      cr_small_ticket_laon_data 
   SET
      error_code = 'E', error_msg = O_ERROR_MSG, validate_flag = 'N' 
   WHERE
      TICKET_ID = I_TICKET_ID 
      AND UPLOAD_ID = V_UPLOAD_ID;
COMMIT;
   SET
      O_ERROR_FLAG = 'S';
   SET
      O_ERROR_MSG = '';
END
IF;
END
LOOP;
CLOSE LOAN_DETAIL_CUR;
SELECT
   COUNT(1) INTO UPLOADED_RECORDS 
FROM
   cr_small_ticket_laon_data 
WHERE
   VALIDATE_FLAG = 'Y' 
   AND TICKET_ID = I_TICKET_ID;
SELECT
   COUNT(1) INTO REJECTED_RECORDS 
FROM
   cr_small_ticket_laon_data 
WHERE
   ERROR_CODE = 'E' 
   AND TICKET_ID = I_TICKET_ID;
-- SELECT UPLOADED_RECORDS,REJECTED_RECORDS;
END