CREATE OR REPLACE PROCEDURE prc_update_monthend(p_Org_Code In Varchar2,
                                                     p_bussdate In date) AS
  
  m_delinquency_str varchar2(4000);
  m_Return_Msg      VARCHAR2(500) := 'T';
  m_Loan_Account    t_Lms_Loan_Account.Sz_Loan_Account_No%Type;

  CURSOR cur_eom_data IS
  
    with schqry as
     (SELECT SZ_LOAN_ACCOUNT_NO,
             SUM(PAIDEMI) PAIDEMI,
             SUM(OVERDUEEMI) OVERDUEEMI,
             SUM(UNDUEEMI) UNDUEEMI
        FROM (SELECT TA.SZ_LOAN_ACCOUNT_NO,
                     (CASE
                       WHEN TS.DT_INSTALLMENTDUE < TA.DT_FIRST_DUE THEN
                        1
                       ELSE
                        0
                     END) PAIDEMI,
                     (CASE
                       WHEN TS.DT_INSTALLMENTDUE >= TA.DT_FIRST_DUE AND
                            TS.DT_INSTALLMENTDUE <= p_bussdate THEN
                        1
                       ELSE
                        0
                     END) OVERDUEEMI,
                     (CASE
                       WHEN p_bussdate > TA.DT_FIRST_DUE THEN
                        1
                       ELSE
                        0
                     END) UNDUEEMI
                FROM T_LMS_LOAN_ACCOUNT TA, T_LMS_REPAY_SCHEDULE TS
               WHERE TA.SZ_LOAN_ACCOUNT_NO = TS.SZ_LOAN_ACCOUNT_NO
                 AND TA.C_ACCOUNT_STATUS = 'L')
       GROUP BY SZ_LOAN_ACCOUNT_NO)
    SELECT t.sz_loan_account_no acc_no,
           Pkg_Balances.Get_Total_Outstanding(t.sz_org_code,
                                              t.sz_loan_account_no) Total_outstanding,
           Pkg_Balances.Get_Due_Principal(t.sz_org_code,
                                          t.sz_loan_account_no) overdue_principal,
           Pkg_Balances.Get_Due_Interest(t.sz_org_code,
                                         t.sz_loan_account_no) overdue_interest,
           Pkg_Balances.Get_Future_Principal(t.sz_org_code,
                                             t.sz_loan_account_no) future_principal,
           Pkg_General_Services.Get_Dpd(t.sz_org_code,
                                        t.sz_loan_account_no,
                                        NULL,
                                        p_bussdate) dpd,
           PAIDEMI,
           OVERDUEEMI,
           UNDUEEMI,
           T.Dt_Previous_Inst_Due
    
      FROM t_lms_loan_account t, schqry
     WHERE t.sz_org_code = p_Org_Code
       AND t.c_account_status = 'L'
       and t.sz_loan_account_no(+) = schqry.sz_loan_account_no;

  TYPE typ_cur IS TABLE OF cur_eom_data%ROWTYPE;
  rec_payment typ_cur := typ_cur();

BEGIN
  IF (last_day(p_bussdate) = p_bussdate) THEN
    OPEN cur_eom_data;
    LOOP
      FETCH cur_eom_data BULK COLLECT
        INTO rec_payment LIMIT 10000;
    
      FOR indx IN 1 .. rec_payment.count LOOP
      
        BEGIN
          SELECT t.sz_delinquency_str
            INTO m_delinquency_str
            FROM t_lms_month_end_loan_data t
           WHERE t.sz_loan_account_no = rec_payment(indx).acc_no
             AND t.dt_businessdate = add_months(p_bussdate, -1);
        EXCEPTION
          WHEN others THEN
            m_delinquency_str := '';
        END;
        IF rec_payment(indx).dt_previous_inst_due IS NOT NULL THEN
          IF (m_delinquency_str is null) THEN
            m_delinquency_str := rec_payment(indx).dpd;
          ELSE
            m_delinquency_str := to_char(rec_payment(indx).dpd) || chr(45) ||
                                 m_delinquency_str;
          END IF;
        ELSE
          m_delinquency_str := NULL;
        END IF;
        INSERT INTO t_lms_month_end_loan_data
          (SZ_ORG_CODE,
           SZ_LOAN_ACCOUNT_NO,
           I_DPD,
           SZ_DELINQUENCY_STR,
           I_NO_OF_PAID_EMI,
           I_NO_OF_OVERDUE_EMI,
           I_NO_OF_UNPAID_EMI,
           F_OUTSTANDING_AMT,
           F_OVERDUE_INTEREST,
           F_OVERDUE_PRINCIPAL,
           DT_BUSINESSDATE,
           SZ_USER_ID,
           F_FUTURE_PRINCIPAL)
        VALUES
          (p_Org_Code,
           rec_payment(indx).acc_no,
           rec_payment(indx).dpd,
           m_delinquency_str,
           rec_payment(indx).paidemi,
           rec_payment(indx).overdueemi,
           rec_payment(indx).undueemi,
           rec_payment(indx).Total_outstanding,
           rec_payment(indx).overdue_interest,
           rec_payment(indx).overdue_principal,
           p_bussdate,
           'EOD',
           rec_payment(indx).future_principal);
        COMMIT;

        update t_lms_loan_account d
           set d.sz_delinquency_str = m_delinquency_str
         where d.sz_loan_account_no = rec_payment(indx).acc_no;
      END LOOP;
      EXIT WHEN cur_eom_data%NOTFOUND;
    END LOOP;
  END IF;
  commit;
EXCEPTION
  WHEN OTHERS THEN
    m_Return_Msg := Substr(SQLERRM, 1, 500);
    Pkg_Eod_Process.Eod_Error_Log(p_Orgcode            => p_Org_Code,
                                  p_Dtbusidate         => p_bussdate,
                                  p_Iprocess           => 'prc_update_monthend_data',
                                  p_Sz_Loan_Account_No => '',
                                  p_Szerrordesc        => m_Return_Msg,
                                  p_Szuserid           => 'EOD',
                                  p_Dtupdtimestamp     => SYSDATE);
    COMMIT;
END;
/
