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';
SELECT 
  i_loan_no;
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 
  v_loan_no, 
  i_loan_no;
SELECT 
  loan_id INTO v_loan_id 
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';
SELECT 
  v_loan_id, 
  v_loan_no;
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;
SELECT 
  'O_ERROR_FLAG', 
  'DIS_PAYEE_NAME', 
  o_error_flag, 
  dis_payee_name;
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 
  'V_BUSINESS_DATE', 
  v_business_date, 
  o_error_flag;
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';
SELECT 
  v_loan_id, 
  v_stage;
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;