CREATE OR REPLACE PACKAGE Pkg_calc_amount AS

  

  m_Retmsg VARCHAR2(100);

  FUNCTION Get_Due_Principal(p_Org_Code        IN VARCHAR2,
                             p_Loan_Account_No IN VARCHAR2,
                             p_Tranche_No      IN NUMBER DEFAULT NULL)
    RETURN NUMBER;

  FUNCTION Get_Next_Principal_Due(p_Org_Code        IN VARCHAR2,
                                  p_Loan_Account_No IN VARCHAR2)
    RETURN NUMBER;
  FUNCTION Get_Next_Interest_Due(p_Org_Code        IN VARCHAR2,
                                 p_Loan_Account_No IN VARCHAR2) RETURN NUMBER;
  FUNCTION Get_Due_Interest(p_Org_Code        IN VARCHAR2,
                            p_Loan_Account_No IN VARCHAR2,
                            p_Tranche_No      IN NUMBER DEFAULT NULL)
    RETURN NUMBER;

  FUNCTION Get_Due_Installment(p_Org_Code        IN VARCHAR2,
                               p_Loan_Account_No IN VARCHAR2,
                               p_Tranche_No      IN NUMBER DEFAULT NULL)
    RETURN NUMBER;

  FUNCTION Get_Due_Charges(p_Org_Code        IN VARCHAR2,
                           p_Loan_Account_No IN VARCHAR2,
                           p_Tranche_No      IN NUMBER DEFAULT NULL)
    RETURN NUMBER;
  
	FUNCTION Get_debitnote_dues(p_Org_Code        IN VARCHAR2,
                                p_Loan_Account_No IN VARCHAR2,
                                p_Tranche_No      IN NUMBER DEFAULT NULL)

      RETURN NUMBER; 

  FUNCTION Get_Due_Charges_Head(p_Org_Code        IN VARCHAR2,
                                p_Loan_Account_No IN VARCHAR2,
                                p_Tran_Head       IN VARCHAR2,
                                p_Tranche_No      IN NUMBER DEFAULT NULL)
    RETURN NUMBER;

  FUNCTION Get_Due_Subproduct(p_Org_Code        IN VARCHAR2,
                              p_Loan_Account_No IN VARCHAR2,
                              p_Tranche_No      IN NUMBER DEFAULT NULL)
    RETURN NUMBER;

  FUNCTION Get_Total_Due(p_Org_Code        IN VARCHAR2,
                         p_Loan_Account_No IN VARCHAR2) RETURN NUMBER;

  FUNCTION Get_Cust_Total_Due(p_Org_Code        IN VARCHAR2,
                              p_Customer_Number IN VARCHAR2) RETURN NUMBER;

  FUNCTION Get_Cust_Exposure_Amt(p_Org_Code        IN VARCHAR2,
                                 p_Customer_Number IN VARCHAR2) RETURN NUMBER;

  FUNCTION Get_Total_Outstanding(p_Org_Code        IN VARCHAR2,
                                 p_Loan_Account_No IN VARCHAR2,
                                 p_Tranche_No      IN NUMBER DEFAULT NULL)
    RETURN NUMBER;

  FUNCTION Get_Total_Receivables(p_Org_Code        IN VARCHAR2,
                                 p_Loan_Account_No IN VARCHAR2,
                                 p_Tranche_No      IN NUMBER DEFAULT NULL)
    RETURN NUMBER;

  FUNCTION Get_Future_Principal(p_Org_Code        IN VARCHAR2,
                                p_Loan_Account_No IN VARCHAR2,
                                p_Tranche_No      IN NUMBER DEFAULT NULL)
    RETURN NUMBER;

  FUNCTION Get_Outstanding_Future_Prn(p_Org_Code        IN VARCHAR2,
                                      p_Loan_Account_No IN VARCHAR2,
                                      p_Tranche_No      IN NUMBER DEFAULT NULL)
    RETURN NUMBER;

  FUNCTION Get_Outstanding_Principal(p_Org_Code        IN VARCHAR2,
                                     p_Loan_Account_No IN VARCHAR2,
                                     p_Tranche_No      IN NUMBER DEFAULT NULL)
    RETURN NUMBER;

  FUNCTION Get_Principal_Repay(p_Org_Code        IN VARCHAR2,
                               p_Loan_Account_No IN VARCHAR2,
                               p_Tranche_No      IN NUMBER DEFAULT NULL)
    RETURN NUMBER;

  FUNCTION Get_Interest_Repay(p_Org_Code        IN VARCHAR2,
                               p_Loan_Account_No IN VARCHAR2,
                               p_Tranche_No      IN NUMBER DEFAULT NULL)
    RETURN NUMBER;


  FUNCTION Get_Additional_Repay(p_Org_Code        IN VARCHAR2,
                                p_Loan_Account_No IN VARCHAR2,
                                p_Tranche_No      IN NUMBER DEFAULT NULL)
    RETURN NUMBER;

  FUNCTION Get_Charges_Repay(p_Org_Code        IN VARCHAR2,
                             p_Loan_Account_No IN VARCHAR2,
                             p_Tranche_No      IN NUMBER DEFAULT NULL)
    RETURN NUMBER;

  FUNCTION Get_Charges_Repay_Head(p_Org_Code        IN VARCHAR2,
                                  p_Loan_Account_No IN VARCHAR2,
                                  p_Tran_Head       IN VARCHAR2,
                                  p_Charge_Bookid   IN NUMBER,
                                  p_Tranche_No      IN NUMBER DEFAULT NULL,
                                  p_Servicetaxyn    IN VARCHAR DEFAULT 'Y')
    RETURN NUMBER;

  FUNCTION Get_Subproduct_Repay(p_Org_Code        IN VARCHAR2,
                                p_Loan_Account_No IN VARCHAR2,
                                p_Tranche_No      IN NUMBER DEFAULT NULL)
    RETURN NUMBER;
  FUNCTION Get_Subprod_Due(p_Org_Code        IN VARCHAR2,
                           p_Loan_Account_No IN VARCHAR2,
                           p_Tranche_No      IN NUMBER DEFAULT NULL)
    RETURN NUMBER;
  FUNCTION Get_Fut_Subprod_Amt(p_Org_Code        IN VARCHAR2,
                               p_Loan_Account_No IN VARCHAR2,
                               p_Tranche_No      IN NUMBER DEFAULT NULL)
    RETURN NUMBER;

  FUNCTION Get_Excess_Amount(p_Org_Code        IN VARCHAR2,
                             p_Loan_Account_No IN VARCHAR2) RETURN NUMBER;

  FUNCTION Get_Waived_Amount(p_Org_Code        IN VARCHAR2,
                             p_Loan_Account_No IN VARCHAR2,
                             p_Chgbooktran_Id  IN NUMBER DEFAULT NULL)
    RETURN NUMBER;

  FUNCTION Get_Provision_Amount(p_Org_Code        IN VARCHAR2,
                                p_Loan_Account_No IN VARCHAR2) RETURN NUMBER;

  FUNCTION Total_Due_Charges(p_Org_Code        IN VARCHAR2,
                             p_Loan_Account_No IN VARCHAR2,
                             p_Tranche_No      IN NUMBER DEFAULT NULL)
    RETURN NUMBER;

  FUNCTION Get_Outstanding_Subproduct(p_Org_Code        IN VARCHAR2,
                                      p_Loan_Account_No IN VARCHAR2,
                                      p_Ins_Srno        IN NUMBER DEFAULT NULL,
                                      p_Till_Date       IN DATE DEFAULT NULL)
    RETURN NUMBER;
		
  --ADDED BY SURABHI 16/04/2015 FOR ECS PRE-EMI CASES START
  Function Get_Total_PreEmiDue(p_Org_Code        In Varchar2,
                               p_Loan_Account_No In Varchar2,
                               P_BusinessDate    IN Date) Return Number;
 Function Get_Total_Due_noaccrual(p_Org_Code        In Varchar2,
                         p_Loan_Account_No In Varchar2) Return Number ;                               
  ---ADDED BY SURABHI 16/04/2015 FOR ECS PRE-EMI CASES ENDS 

END Pkg_Balances;
/
CREATE OR REPLACE Package Body Pkg_Balances As

	Function Get_Due_Principal(p_Org_Code        In Varchar2,
														 p_Loan_Account_No In Varchar2,
														 p_Tranche_No      In Number Default Null)
		Return Number As
		/* Returns the principal due for the tranche or if tranche
    is null it returns the total princiapl due for the agreeement*/
	
		m_Odprincipal t_Lms_Due_Dtls.f_Due_Amt%Type;
	
	Begin
		Select Nvl(Sum(Nvl(Td.f_Due_Amt, 0)), 0)
			Into m_Odprincipal
			From t_Lms_Due_Dtls Td
		 Where Td.Sz_Org_Code = p_Org_Code
			 And Td.Sz_Loan_Account_No = p_Loan_Account_No
			 And Td.i_Tranche_No =
					 Decode(p_Tranche_No, '', Td.i_Tranche_No, p_Tranche_No)
			 And Td.Sz_Tranhead In ('PRN_AMT', 'FUT_PRN');
	
		Return m_Odprincipal;
	
	Exception
		When Others Then
			Return - 1;
		
	End;

  FUNCTION Get_Next_Principal_Due(p_Org_Code        IN VARCHAR2,
                                  p_Loan_Account_No IN VARCHAR2)
    RETURN NUMBER AS
    /* Returns the principal due for the tranche or if tranche
    is null it returns the total principal due for the agreeement*/
    m_Odprincipal t_Lms_Due_Dtls.f_Due_Amt%TYPE;
  BEGIN
    SELECT SUM(Nvl(Tr.f_Prinamt, 0))
      INTO m_Odprincipal
      FROM t_Lms_Repay_Schedule Tr
     WHERE Tr.Sz_Org_Code = p_Org_Code
       AND Tr.Sz_Loan_Account_No = p_Loan_Account_No
       AND Tr.Dt_Installmentdue =
           Pkg_General_Services.Get_Next_Inst_Duedate(p_Org_Code,
                                                      p_Loan_Account_No,
                                                      NULL,
                                                      (SELECT Pkg_General_Services.Get_Business_Date(p_Org_Code)
                                                         FROM Dual));
    RETURN m_Odprincipal;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN - 1;
  END;
  FUNCTION Get_Next_Interest_Due(p_Org_Code        IN VARCHAR2,
                                 p_Loan_Account_No IN VARCHAR2) RETURN NUMBER AS
    /* Returns the Interest due for the tranche or if tranche
    is null it returns the total interest due for the agreeement*/
    m_Odinterest t_Lms_Due_Dtls.f_Due_Amt%TYPE;
  BEGIN
    SELECT SUM(Nvl(Tr.f_Intamt, 0))
      INTO m_Odinterest
      FROM t_Lms_Repay_Schedule Tr
     WHERE Tr.Sz_Org_Code = p_Org_Code
       AND Tr.Sz_Loan_Account_No = p_Loan_Account_No
       AND Tr.Dt_Installmentdue =
           Pkg_General_Services.Get_Next_Inst_Duedate(p_Org_Code,
                                                      p_Loan_Account_No,
                                                      NULL,
                                                      (SELECT Pkg_General_Services.Get_Business_Date(p_Org_Code)
                                                         FROM Dual));
    RETURN m_Odinterest;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN - 1;
  END;
	Function Get_Due_Interest(p_Org_Code        In Varchar2,
														p_Loan_Account_No In Varchar2,
														p_Tranche_No      In Number Default Null)
		Return Number As
		/* Returns the interest due for the tranche or if tranche
    is null it returns the total interest due for the agreeement*/
	
		m_Odinterest t_Lms_Due_Dtls.f_Due_Amt%Type;
	
	Begin
		Select Nvl(Sum(Nvl(Td.f_Due_Amt, 0)), 0)
			Into m_Odinterest
			From t_Lms_Due_Dtls Td
		 Where Td.Sz_Org_Code = p_Org_Code
			 And Td.Sz_Loan_Account_No = p_Loan_Account_No
			 And Td.i_Tranche_No =
					 Decode(p_Tranche_No, '', Td.i_Tranche_No, p_Tranche_No)
			 And Td.Sz_Tranhead = 'INT_AMT';
	
		Return m_Odinterest;
	
	Exception
		When Others Then
			Return - 1;
		
	End;

	Function Get_Due_Installment(p_Org_Code        In Varchar2,
															 p_Loan_Account_No In Varchar2,
															 p_Tranche_No      In Number Default Null)
		Return Number As
		/* Returns the installment due for the tranche or if tranche
    is null it returns the total due for the agreeement*/
	
		m_Odinstallment t_Lms_Due_Dtls.f_Due_Amt%Type;
	
	Begin
		Select Nvl(Sum(Nvl(Td.f_Due_Amt, 0)), 0)
			Into m_Odinstallment
			From t_Lms_Due_Dtls Td
		 Where Td.Sz_Org_Code = p_Org_Code
			 And Td.Sz_Loan_Account_No = p_Loan_Account_No
			 And Td.i_Tranche_No =
					 Decode(p_Tranche_No, '', Td.i_Tranche_No, p_Tranche_No)
			 And Td.Sz_Trantype In ('INSTALLMENT', 'LOAN_AMOUNT');
	
		Return m_Odinstallment;
	
	Exception
		When Others Then
			Return - 1;
		
	End Get_Due_Installment;
	
	
	
	
	
	

	Function Get_Due_Charges(p_Org_Code        In Varchar2,
													 p_Loan_Account_No In Varchar2,
													 p_Tranche_No      In Number Default Null)
		Return Number As
	
		/* Returns the charges due for the tranche or if tranche
    is null it returns the total charges due for the agreeement*/
	
		m_Duecharges             t_Lms_Due_Dtls.f_Due_Amt%Type;
		m_Duetax                 t_Lms_Due_Dtls.f_Due_Amt%Type;
		m_f_Total_Penal_Computed t_Lms_Tranche_Dtls.f_Total_Penal_Computed%Type;
    CPF_EXCEPTION EXCEPTION;
		M_PENAL_TYPE CHAR;
	Begin
	
		Select Nvl(Sum(Nvl(Td.f_Due_Amt, 0)), 0),
					 Nvl(Sum(Nvl(a.f_Tax_Amt, 0)), 0)
			Into m_Duecharges, m_Duetax
		
			From t_Lms_Due_Dtls Td,
					 (Select Sum(Txd.f_Tax_Amt) f_Tax_Amt, Txd.i_Srno
						
							From t_Lms_Taxdue_Dtls Txd
						 Where Txd.Sz_Loan_Account_No = p_Loan_Account_No
							 And Txd.Sz_Org_Code = p_Org_Code
						 Group By Txd.i_Srno) a
		 Where Td.Sz_Org_Code = p_Org_Code
			 And Td.Sz_Loan_Account_No = p_Loan_Account_No
			 And Nvl(Td.i_Tranche_No, '1') =
					 Decode(p_Tranche_No, '', Nvl(Td.i_Tranche_No, '1'), p_Tranche_No)
			 And Td.Sz_Trantype = 'FEE'
			 And Td.i_Srno = a.i_Srno(+);
		--IF m_Duecharges > 0 THEN --Changed By Mustafa on 01/04
		-- Added By Karthik M for Penal LUMPSUM changes NICHE
		BEGIN
		SELECT NVL(D.C_PENAL_CALC_TYPE, 'P') /*P PERCENT,L -- LUMPSUM */
			INTO M_PENAL_TYPE
			FROM PRD_AMORT_PORTFOLIO_XREF D, T_LMS_PRODUCT_DTLS P
		 WHERE P.SZ_ORG_CODE = P_ORG_CODE
			 AND P.SZ_LOAN_ACCOUNT_NO = p_Loan_Account_No
			 AND D.SZ_PRODUCT_CODE = P.SZ_PRODUCT_CODE;
		EXCEPTION
		WHEN OTHERS THEN
			M_PENAL_TYPE := 'P';
		END;
		IF(M_PENAL_TYPE='P') THEN
		m_f_Total_Penal_Computed := Nvl(Pkg_Penal_Interest.Get_Penal_Computed_Amt(p_Org_Code        => p_Org_Code,
																																							p_Loan_Account_No => p_Loan_Account_No,
																																							p_Tranche_No      => p_Tranche_No,
																																							p_Trantype        => Null, --Changed By Mustafa on 01/04
																																							p_Tranhead        => Null,
																																							p_Businessdate    => Pkg_General_Services.Get_Business_Date(p_Org_Code),
																																							p_Value_Date      => Null),
																		0);
	   ELSIF(M_PENAL_TYPE='L') THEN
		 m_f_Total_Penal_Computed := PKG_PENAL_INTEREST_LUMPSUM.GET_ACC_PENAL_AMT(P_ORG_CODE        => p_Org_Code,
                                                                P_LOAN_ACCOUNT_NO => p_Loan_Account_No,
                                                                P_TRANCHE_NO      => p_Tranche_No,
                                                                P_BUSINESSDATE    => Pkg_General_Services.Get_Business_Date(p_Org_Code),
                                                                P_VALUE_DATE      => NULL);  
		 END IF;
			
		--END IF; --Changed By Mustafa on 01/04
	
      IF m_f_Total_Penal_Computed=-101 OR m_f_Total_Penal_Computed=-102 THEN
              RAISE CPF_EXCEPTION;
             END IF;
		Return m_Duecharges + m_Duetax + Nvl(m_f_Total_Penal_Computed, 0);
	
	Exception
    when CPF_EXCEPTION then 
      return m_f_Total_Penal_Computed;
		When Others Then
			Return - 1;
		
	End;


------Start--Added This Function for issue-77195-------------------------------

 
  
Function Get_debitnote_dues(p_Org_Code   In Varchar2,
						               p_Loan_Account_No In Varchar2,
						               p_Tranche_No      In Number Default Null)
		Return Number As
	
		/* Returns the charges due for the tranche or if tranche
    is null it returns the total charges due for the agreeement*/
	
		m_Duecharges             t_Lms_Due_Dtls.f_Due_Amt%Type;
		m_Duetax                 t_Lms_Due_Dtls.f_Due_Amt%Type;
		m_f_Total_Penal_Computed t_Lms_Tranche_Dtls.f_Total_Penal_Computed%Type;
    CPF_EXCEPTION EXCEPTION;
	Begin
	
		Select Nvl(Sum(Nvl(Td.f_Due_Amt, 0)), 0),
					 Nvl(Sum(Nvl(a.f_Tax_Amt, 0)), 0)
			Into m_Duecharges, m_Duetax
		
			From t_Lms_Due_Dtls Td,
					 (Select Sum(Txd.f_Tax_Amt) f_Tax_Amt, Txd.i_Srno
						
							From t_Lms_Taxdue_Dtls Txd
						 Where Txd.Sz_Loan_Account_No = p_Loan_Account_No
							 And Txd.Sz_Org_Code = p_Org_Code
						 Group By Txd.i_Srno) a
		 Where Td.Sz_Org_Code = p_Org_Code
			 And Td.Sz_Loan_Account_No = p_Loan_Account_No
			 And Nvl(Td.i_Tranche_No, '1') =
					 Decode(p_Tranche_No, '', Nvl(Td.i_Tranche_No, '1'), p_Tranche_No)
			 And Td.Sz_Trantype = 'DEBIT_NOTE'
			 And Td.i_Srno = a.i_Srno(+);
			 
			 	Return m_Duecharges + m_Duetax ;
	
	Exception
    when CPF_EXCEPTION then 
       Return m_Duecharges + m_Duetax ;
		When Others Then
			Return 0;
		
	End;
------End--Added This Function for issue-77195-------------------------------

	Function Get_Due_Charges_Head(p_Org_Code        In Varchar2,
																p_Loan_Account_No In Varchar2,
																p_Tran_Head       In Varchar2,
																p_Tranche_No      In Number Default Null)
		Return Number As
	
		/* Returns the charges due for the head*/
	
		m_Duecharges t_Lms_Due_Dtls.f_Due_Amt%Type;
	
	Begin
	
		/* SELECT Nvl(SUM(Nvl(Td.f_Due_Amt, 0)), 0)
     INTO m_Duecharges
     FROM t_Lms_Due_Dtls Td
    WHERE Td.Sz_Org_Code = p_Org_Code
      AND Td.Sz_Loan_Account_No = p_Loan_Account_No
      AND Td.Sz_Tranhead = p_Tran_Head
      AND Nvl(Td.i_Tranche_No, '1') =
          Decode(p_Tranche_No, '', Nvl(Td.i_Tranche_No, '1'), p_Tranche_No)
     AND Td.Sz_Trantype IN ('FEE', 'TAX'); --AND Td.Sz_Trantype = 'CHARGES';*/
	
		Select Nvl(Sum(Nvl(Td.f_Due_Amt, 0)), 0) +
					 Nvl(Sum(Nvl(a.f_Tax_Amt, 0)), 0)
			Into m_Duecharges
			From t_Lms_Due_Dtls Td,
					 (Select Sum(Txd.f_Tax_Amt) f_Tax_Amt, Txd.i_Srno
						
							From t_Lms_Taxdue_Dtls Txd
						 Where Txd.Sz_Loan_Account_No = p_Loan_Account_No
							 And Txd.Sz_Org_Code = p_Org_Code
						 Group By Txd.i_Srno) a
		 Where Td.Sz_Org_Code = p_Org_Code
			 And Td.Sz_Loan_Account_No = p_Loan_Account_No
			 And Td.Sz_Tranhead = p_Tran_Head
			 And Nvl(Td.i_Tranche_No, '1') =
					 Decode(p_Tranche_No, '', Nvl(Td.i_Tranche_No, '1'), p_Tranche_No)
			 And Td.Sz_Trantype = 'FEE'
			 And Td.i_Srno = a.i_Srno(+);
	
		Return m_Duecharges;
	
	Exception
		When Others Then
			Return - 1;
		
	End Get_Due_Charges_Head;

	Function Get_Due_Subproduct(p_Org_Code        In Varchar2,
															p_Loan_Account_No In Varchar2,
															p_Tranche_No      In Number Default Null)
		Return Number As
	
		/* Returns the sub-product due for the tranche or if tranche
    is null it returns the total sub-product due for the agreeement*/
	
		m_Duefee t_Lms_Due_Dtls.f_Due_Amt%Type;
		m_Duetax t_Lms_Due_Dtls.f_Due_Amt%Type;
	Begin
	
		Select Nvl(Sum(Nvl(Td.f_Due_Amt, 0)), 0),
					 Nvl(Sum(Nvl(a.f_Tax_Amt, 0)), 0)
			Into m_Duefee, m_Duetax
		
			From t_Lms_Due_Dtls Td,
					 (Select Sum(Txd.f_Tax_Amt) f_Tax_Amt, Txd.i_Srno
						
							From t_Lms_Taxdue_Dtls Txd
						 Where Txd.Sz_Loan_Account_No = p_Loan_Account_No
							 And Txd.Sz_Org_Code = p_Org_Code
						 Group By Txd.i_Srno) a
		 Where Td.Sz_Org_Code = p_Org_Code
			 And Td.Sz_Loan_Account_No = p_Loan_Account_No
			 And Nvl(Td.i_Tranche_No, '1') =
					 Decode(p_Tranche_No, '', Nvl(Td.i_Tranche_No, '1'), p_Tranche_No)
			 And Td.Sz_Trantype = 'SUB_PRODUCT'
			 And Td.i_Srno = a.i_Srno(+);
	
		Return m_Duefee + m_Duetax;
	
	Exception
		When Others Then
			Return - 1;
		
	End Get_Due_Subproduct;

	Function Get_Total_Due(p_Org_Code        In Varchar2,
												 p_Loan_Account_No In Varchar2) Return Number As
	
		/* Returns the total outstanding amount excluding future principal for the tranche or if tranche
    is null it returns the total outstanding for the agreeement*/
	
		m_Totaldue               t_Lms_Due_Dtls.f_Due_Amt%Type;
		m_f_Total_Penal_Computed t_Lms_Due_Dtls.f_Due_Amt%Type;
    CPF_EXCEPTION exception;
		M_PENAL_TYPE CHAR;
	Begin
	
		/* SELECT Nvl(SUM(Nvl(Td.f_Due_Amt, 0)), 0)
     INTO m_Totaldue
     FROM t_Lms_Due_Dtls Td
    WHERE Td.Sz_Org_Code = p_Org_Code
     AND Td.Sz_Loan_Account_No = p_Loan_Account_No;*/
		--AND Td.Sz_Tranhead <> 'FUT_PRN';
	
		Select Nvl(Sum(Nvl(Td.f_Due_Amt, 0)), 0) +
					 Nvl(Sum(Nvl(a.f_Tax_Amt, 0)), 0)
			Into m_Totaldue
			From t_Lms_Due_Dtls Td,
					 (Select Sum(Txd.f_Tax_Amt) f_Tax_Amt, Txd.i_Srno
						
							From t_Lms_Taxdue_Dtls Txd
						 Where Txd.Sz_Loan_Account_No = p_Loan_Account_No
							 And Txd.Sz_Org_Code = p_Org_Code
						 Group By Txd.i_Srno) a
		 Where Td.Sz_Org_Code = p_Org_Code
			 And Td.Sz_Loan_Account_No = p_Loan_Account_No
			 And Td.i_Srno = a.i_Srno(+);
	
		If m_Totaldue > 0 Then
		-- Added By Karthik M for Penal LUMPSUM changes NICHE
			BEGIN
			SELECT NVL(D.C_PENAL_CALC_TYPE, 'P') /*P PERCENT,L -- LUMPSUM */
				INTO M_PENAL_TYPE
				FROM PRD_AMORT_PORTFOLIO_XREF D, T_LMS_PRODUCT_DTLS P
			 WHERE P.SZ_ORG_CODE = P_ORG_CODE
				 AND P.SZ_LOAN_ACCOUNT_NO = p_Loan_Account_No
				 AND D.SZ_PRODUCT_CODE = P.SZ_PRODUCT_CODE;
			EXCEPTION
			WHEN OTHERS THEN
				M_PENAL_TYPE := 'P';
			END;
			IF(M_PENAL_TYPE='P') THEN
			m_f_Total_Penal_Computed := Pkg_Penal_Interest.Get_Penal_Computed_Amt(p_Org_Code        => p_Org_Code,
																																						p_Loan_Account_No => p_Loan_Account_No,
																																						p_Tranche_No      => Null,
																																						p_Businessdate    => Pkg_General_Services.Get_Business_Date(p_Org_Code));
      ELSIF(M_PENAL_TYPE='L') THEN
			m_f_Total_Penal_Computed := PKG_PENAL_INTEREST_LUMPSUM.GET_ACC_PENAL_AMT(P_ORG_CODE        => p_Org_Code,
                                                                P_LOAN_ACCOUNT_NO => p_Loan_Account_No,
                                                                P_TRANCHE_NO      => 1,
                                                                P_BUSINESSDATE    => Pkg_General_Services.Get_Business_Date(p_Org_Code),
                                                                P_VALUE_DATE      => NULL);  
			END IF;																																						
       IF m_f_Total_Penal_Computed=-101 OR m_f_Total_Penal_Computed=-102 THEN
              RAISE CPF_EXCEPTION;
		End If;
		End If;
		Return Nvl(m_Totaldue, 0) + Nvl(m_f_Total_Penal_Computed, 0);
	
	Exception
    when CPF_EXCEPTION then
      return m_f_Total_Penal_Computed;
		When Others Then
			Return - 1;
		
	End Get_Total_Due;

	Function Get_Cust_Total_Due(p_Org_Code        In Varchar2,
															p_Customer_Number In Varchar2) Return Number As
	
		Cursor Cur_Loan_Acnt Is
			Select Lr.Sz_Loan_Account_No
				From t_Lms_Loan_Account La, t_Lms_Loan_References Lr
			
			 Where Lr.Sz_Org_Code = p_Org_Code
				 And Lr.Sz_Customer_No = p_Customer_Number
				 And Lr.c_Typeofapplicant = 'B'
				 And Lr.Sz_Org_Code = La.Sz_Org_Code
				 And Lr.Sz_Loan_Account_No = La.Sz_Loan_Account_No
				 And La.c_Account_Status = 'L';
	
		m_Totaldue               t_Lms_Due_Dtls.f_Due_Amt%Type;
		m_Totaldueamt            t_Lms_Due_Dtls.f_Due_Amt%Type := 0;
	Begin
	
		For Rec_Loan_Acnt In Cur_Loan_Acnt Loop
			--Changed By Mustafa on 01/04
			/*     SELECT Nvl(SUM(Nvl(Td.f_Due_Amt, 0)), 0) +
            Nvl(SUM(Nvl(a.f_Tax_Amt, 0)), 0)
       INTO m_Totaldue
       FROM t_Lms_Due_Dtls Td,
            (SELECT SUM(Txd.f_Tax_Amt) f_Tax_Amt, Txd.i_Srno
               FROM t_Lms_Taxdue_Dtls Txd
              WHERE Txd.Sz_Loan_Account_No =
                    Rec_Loan_Acnt.Sz_Loan_Account_No
                AND Txd.Sz_Org_Code = p_Org_Code
              GROUP BY Txd.i_Srno) a
      WHERE Td.Sz_Org_Code = p_Org_Code
        AND Td.Sz_Loan_Account_No = Rec_Loan_Acnt.Sz_Loan_Account_No
       AND Td.i_Srno = a.i_Srno(+);*/
			m_Totaldue := Get_Total_Due(p_Org_Code,
																	Rec_Loan_Acnt.Sz_Loan_Account_No);
		
			/* IF m_Totaldue > 0 THEN
      m_f_Total_Penal_Computed := Pkg_Penal_Interest.Get_Penal_Computed_Amt(p_Org_Code        => p_Org_Code,
                                                                            p_Loan_Account_No => Rec_Loan_Acnt.Sz_Loan_Account_No,
                                                                            p_Tranche_No      => NULL,
                                                                            p_Businessdate    => Pkg_General_Services.Get_Business_Date(p_Org_Code));
      ELSE
        m_f_Total_Penal_Computed := 0;
      END IF;*/
		
			m_Totaldueamt := m_Totaldueamt + m_Totaldue;
		
		End Loop;
	
		Return m_Totaldueamt;
	
	Exception
		When Others Then
			Return - 1;
		
	End Get_Cust_Total_Due;

	Function Get_Cust_Exposure_Amt(p_Org_Code        In Varchar2,
																 p_Customer_Number In Varchar2) Return Number As
	
		Cursor Cur_Loan_Acnt Is
			Select Lr.Sz_Loan_Account_No
				From t_Lms_Loan_Account La, t_Lms_Loan_References Lr
			
			 Where Lr.Sz_Org_Code = p_Org_Code
				 And Lr.Sz_Customer_No = p_Customer_Number
				 And Lr.c_Typeofapplicant = 'B'
				 And Lr.Sz_Org_Code = La.Sz_Org_Code
				 And Lr.Sz_Loan_Account_No = La.Sz_Loan_Account_No
				 And La.c_Account_Status = 'L';
	
		m_Totaldue    t_Lms_Due_Dtls.f_Due_Amt%Type := 0;
		m_Totaldueamt t_Lms_Due_Dtls.f_Due_Amt%Type := 0;
	
	Begin
	
		For Rec_Loan_Acnt In Cur_Loan_Acnt Loop
		
			m_Totaldue := Get_Total_Receivables(p_Org_Code,
																					Rec_Loan_Acnt.Sz_Loan_Account_No,
																					Null);
		
			m_Totaldueamt := Nvl(m_Totaldueamt, 0) + Nvl(m_Totaldue, 0);
		
		End Loop;
	
		Return m_Totaldueamt;
	
	Exception
		When Others Then
			Return - 1;
		
	End Get_Cust_Exposure_Amt;

	Function Get_Total_Outstanding(p_Org_Code        In Varchar2,
																 p_Loan_Account_No In Varchar2,
																 p_Tranche_No      In Number Default Null)
		Return Number As
	
		/* Returns the total outstanding amount including future principal for the tranche or if tranche
    is null it returns the total outstanding for the agreeement*/
	
		m_Totaloutstanding       t_Lms_Due_Dtls.f_Due_Amt%Type;
		m_f_Total_Penal_Computed t_Lms_Due_Dtls.f_Due_Amt%Type;
    CPF_EXCEPTION EXCEPTION;
		M_PENAL_TYPE CHAR;
	Begin
		/* SELECT Nvl(SUM(Nvl(Td.f_Due_Amt, 0)), 0)
     INTO m_Totaloutstanding
     FROM t_Lms_Due_Dtls Td
    WHERE Td.Sz_Org_Code = p_Org_Code
      AND Td.Sz_Loan_Account_No = p_Loan_Account_No
     AND Nvl(Td.i_Tranche_No, '1') =
         Decode(p_Tranche_No, '', Nvl(Td.i_Tranche_No, '1'), p_Tranche_No);*/
	
		Select Nvl(Sum(Nvl(Td.f_Due_Amt, 0)), 0) +
					 Nvl(Sum(Nvl(a.f_Tax_Amt, 0)), 0)
			Into m_Totaloutstanding
			From t_Lms_Due_Dtls Td,
					 (Select Sum(Txd.f_Tax_Amt) f_Tax_Amt, Txd.i_Srno
						
							From t_Lms_Taxdue_Dtls Txd
						 Where Txd.Sz_Loan_Account_No = p_Loan_Account_No
							 And Txd.Sz_Org_Code = p_Org_Code
						 Group By Txd.i_Srno) a
		 Where Td.Sz_Org_Code = p_Org_Code
			 And Td.Sz_Loan_Account_No = p_Loan_Account_No
			 And Td.i_Srno = a.i_Srno(+)
			 And Nvl(Td.i_Tranche_No, '1') =
					 Decode(p_Tranche_No, '', Nvl(Td.i_Tranche_No, '1'), p_Tranche_No);
	
		If m_Totaloutstanding > 0 Then
		
			/*m_f_Total_Penal_Computed := Pkg_Penal_Interest.Get_Penal_Computed_Amt(p_Org_Code        => p_Org_Code,
																																						p_Loan_Account_No => p_Loan_Account_No,
																																						p_Tranche_No      => p_Tranche_No,
																																						p_Businessdate    => Pkg_General_Services.Get_Business_Date(p_Org_Code));*/
     -- Added By Karthik M for Penal LUMPSUM changes NICHE
      BEGIN
      SELECT NVL(D.C_PENAL_CALC_TYPE, 'P') /*P PERCENT,L -- LUMPSUM */
        INTO M_PENAL_TYPE
        FROM PRD_AMORT_PORTFOLIO_XREF D, T_LMS_PRODUCT_DTLS P
       WHERE P.SZ_ORG_CODE = P_ORG_CODE
         AND P.SZ_LOAN_ACCOUNT_NO = p_Loan_Account_No
         AND D.SZ_PRODUCT_CODE = P.SZ_PRODUCT_CODE;
      EXCEPTION
      WHEN OTHERS THEN
        M_PENAL_TYPE := 'P';
      END;
      IF(M_PENAL_TYPE='P') THEN
      m_f_Total_Penal_Computed := Pkg_Penal_Interest.Get_Penal_Computed_Amt(p_Org_Code        => p_Org_Code,
                                                                            p_Loan_Account_No => p_Loan_Account_No,
                                                                            p_Tranche_No      => Null,
                                                                            p_Businessdate    => Pkg_General_Services.Get_Business_Date(p_Org_Code));
      ELSIF(M_PENAL_TYPE='L') THEN
      m_f_Total_Penal_Computed := PKG_PENAL_INTEREST_LUMPSUM.GET_ACC_PENAL_AMT(P_ORG_CODE        => p_Org_Code,
                                                                P_LOAN_ACCOUNT_NO => p_Loan_Account_No,
                                                                P_TRANCHE_NO      => 1,
                                                                P_BUSINESSDATE    => Pkg_General_Services.Get_Business_Date(p_Org_Code),
                                                                P_VALUE_DATE      => NULL);  
      END IF;                                                                                 																																						
		
          IF m_f_Total_Penal_Computed=-101 OR m_f_Total_Penal_Computed=-102 THEN
              RAISE CPF_EXCEPTION;
		End If;
		End If;
	
		Return m_Totaloutstanding + Nvl(m_f_Total_Penal_Computed, 0);
	
	Exception
    when CPF_EXCEPTION then
      return m_f_Total_Penal_Computed;
		When Others Then
			Return - 1;
		
	End;

	Function Get_Total_Receivables(p_Org_Code        In Varchar2,
																 p_Loan_Account_No In Varchar2,
																 p_Tranche_No      In Number Default Null)
		Return Number As
	
		/* Returns the total receivable amount which includes outstanding
    principal,outstanding insurance amount, dues for the tranche*/
	
		m_Totaloutstanding       t_Lms_Due_Dtls.f_Due_Amt%Type;
		m_f_Total_Penal_Computed t_Lms_Due_Dtls.f_Due_Amt%Type;
    CPF_EXCEPTION exception;
		M_PENAL_TYPE CHAR;		
	Begin
	
		Select (Nvl(Sum(Nvl(Td.f_Due_Amt, 0)), 0) +
					 Nvl(Sum(Nvl(a.f_Tax_Amt, 0)), 0) +
					 Nvl(Pkg_Balances.Get_Future_Principal(p_Org_Code,
																									p_Loan_Account_No),
								0) + Nvl(Pkg_Balances.Get_Outstanding_Subproduct(p_Org_Code,
																																	p_Loan_Account_No),
													0)) -
					 Nvl(Pkg_Balances.Get_Excess_Amount(p_Org_Code, p_Loan_Account_No),
							 0)
			Into m_Totaloutstanding
			From t_Lms_Due_Dtls Td,
					 (Select Sum(Txd.f_Tax_Amt) f_Tax_Amt, Txd.i_Srno
						
							From t_Lms_Taxdue_Dtls Txd
						 Where Txd.Sz_Loan_Account_No = p_Loan_Account_No
							 And Txd.Sz_Org_Code = p_Org_Code
						 Group By Txd.i_Srno) a
		 Where Td.Sz_Org_Code = p_Org_Code
			 And Td.Sz_Loan_Account_No = p_Loan_Account_No
			 And Td.i_Srno = a.i_Srno(+)
			 And Nvl(Td.i_Tranche_No, '1') =
					 Decode(p_Tranche_No, '', Nvl(Td.i_Tranche_No, '1'), p_Tranche_No);
	
		If m_Totaloutstanding > 0 Then
		
			/*m_f_Total_Penal_Computed := Pkg_Penal_Interest.Get_Penal_Computed_Amt(p_Org_Code        => p_Org_Code,
																																						p_Loan_Account_No => p_Loan_Account_No,
																																						p_Tranche_No      => p_Tranche_No,
																																						p_Businessdate    => Pkg_General_Services.Get_Business_Date(p_Org_Code));*/
       																																						
		 BEGIN
      SELECT NVL(D.C_PENAL_CALC_TYPE, 'P') /*P PERCENT,L -- LUMPSUM */
        INTO M_PENAL_TYPE
        FROM PRD_AMORT_PORTFOLIO_XREF D, T_LMS_PRODUCT_DTLS P
       WHERE P.SZ_ORG_CODE = P_ORG_CODE
         AND P.SZ_LOAN_ACCOUNT_NO = p_Loan_Account_No
         AND D.SZ_PRODUCT_CODE = P.SZ_PRODUCT_CODE;
      EXCEPTION
      WHEN OTHERS THEN
        M_PENAL_TYPE := 'P';
      END;
      IF(M_PENAL_TYPE='P') THEN
      m_f_Total_Penal_Computed := Pkg_Penal_Interest.Get_Penal_Computed_Amt(p_Org_Code        => p_Org_Code,
                                                                            p_Loan_Account_No => p_Loan_Account_No,
                                                                            p_Tranche_No      => Null,
                                                                            p_Businessdate    => Pkg_General_Services.Get_Business_Date(p_Org_Code));
      ELSIF(M_PENAL_TYPE='L') THEN
      m_f_Total_Penal_Computed := PKG_PENAL_INTEREST_LUMPSUM.GET_ACC_PENAL_AMT(P_ORG_CODE        => p_Org_Code,
                                                                P_LOAN_ACCOUNT_NO => p_Loan_Account_No,
                                                                P_TRANCHE_NO      => 1,
                                                                P_BUSINESSDATE    => Pkg_General_Services.Get_Business_Date(p_Org_Code),
                                                                P_VALUE_DATE      => NULL);  
      END IF;           
			
              IF m_f_Total_Penal_Computed=-101 OR m_f_Total_Penal_Computed=-102 THEN
              RAISE CPF_EXCEPTION;
		End If;
		End If;
	
		Return m_Totaloutstanding + Nvl(m_f_Total_Penal_Computed, 0);
	
	Exception
     when CPF_EXCEPTION then
       return m_f_Total_Penal_Computed;
		When Others Then
			Return - 1;
		
	End;

	Function Get_Outstanding_Future_Prn(p_Org_Code        In Varchar2,
																			p_Loan_Account_No In Varchar2,
																			p_Tranche_No      In Number Default Null)
		Return Number As
		/* Returns the future principal due for the tranche or if tranche
    is null it returns the total future principal due for the agreeement*/
	
		m_Duefutprinamt t_Lms_Due_Dtls.f_Due_Amt%Type;
		m_Futprinamt    t_Lms_Due_Dtls.f_Due_Amt%Type;
	
	Begin
	
		Select Nvl(Sum(Nvl(Td.f_Due_Amt, 0)), 0)
			Into m_Duefutprinamt
			From t_Lms_Due_Dtls Td
		 Where Td.Sz_Org_Code = p_Org_Code
			 And Td.Sz_Loan_Account_No = p_Loan_Account_No
			 And Td.i_Tranche_No =
					 Decode(p_Tranche_No, '', Td.i_Tranche_No, p_Tranche_No)
			 And Td.Sz_Tranhead = 'FUT_PRN';
	
		Select Nvl(Sum(Trn.f_Futpri_Amt), 0) f_Futpri_Amt
			Into m_Futprinamt
			From t_Lms_Tranche_Dtls Trn
		 Where Trn.Sz_Org_Code = p_Org_Code
			 And Trn.Sz_Loan_Account_No = p_Loan_Account_No
			 And Trn.i_Tranche_No =
					 Decode(p_Tranche_No, '', Trn.i_Tranche_No, p_Tranche_No);
	
		Return m_Futprinamt + m_Duefutprinamt;
	
	Exception
		When Others Then
			Return - 1;
		
	End;

	Function Get_Future_Principal(p_Org_Code        In Varchar2,
																p_Loan_Account_No In Varchar2,
																p_Tranche_No      In Number Default Null)
		Return Number As
		/* Returns the future principal due for the tranche or if tranche
    is null it returns the total future principal due for the agreeement*/
	
		m_Futprinamt t_Lms_Due_Dtls.f_Due_Amt%Type;
	
	Begin
	
		Select Nvl(Sum(Trn.f_Futpri_Amt), 0) f_Futpri_Amt
			Into m_Futprinamt
			From t_Lms_Tranche_Dtls Trn
		 Where Trn.Sz_Org_Code = p_Org_Code
			 And Trn.Sz_Loan_Account_No = p_Loan_Account_No
			 And Trn.i_Tranche_No =
					 Decode(p_Tranche_No, '', Trn.i_Tranche_No, p_Tranche_No);
	
		Return m_Futprinamt;
	
	Exception
		When Others Then
			Return - 1;
		
	End;

	Function Get_Outstanding_Principal(p_Org_Code        In Varchar2,
																		 p_Loan_Account_No In Varchar2,
																		 p_Tranche_No      In Number Default Null)
		Return Number As
		/* Returns the total principal due for the tranche or if tranche
    is null it returns the total principal due for the agreeement*/
		m_Futprinamt t_Lms_Due_Dtls.f_Due_Amt%Type;
		m_Dueprinamt t_Lms_Due_Dtls.f_Due_Amt%Type;
	
	Begin
		Select Nvl(Sum(Trn.f_Futpri_Amt), 0) f_Futpri_Amt
			Into m_Futprinamt
			From t_Lms_Tranche_Dtls Trn
		 Where Trn.Sz_Org_Code = p_Org_Code
			 And Trn.Sz_Loan_Account_No = p_Loan_Account_No
			 And Trn.i_Tranche_No =
					 Decode(p_Tranche_No, '', Trn.i_Tranche_No, p_Tranche_No);
	
		Select Nvl(Sum(Nvl(Td.f_Due_Amt, 0)), 0)
			Into m_Dueprinamt
			From t_Lms_Due_Dtls Td
		 Where Td.Sz_Org_Code = p_Org_Code
			 And Td.Sz_Loan_Account_No = p_Loan_Account_No
			 And Td.i_Tranche_No =
					 Decode(p_Tranche_No, '', Td.i_Tranche_No, p_Tranche_No)
			 And Td.Sz_Tranhead In ('PRN_AMT', 'FUT_PRN');
	
		Return m_Dueprinamt + m_Futprinamt;
	
	Exception
		When Others Then
			Return - 1;
		
	End;

	Function Get_Principal_Repay(p_Org_Code        In Varchar2,
															 p_Loan_Account_No In Varchar2,
															 p_Tranche_No      In Number Default Null)
		Return Number As
	
		/* Returns the principal repaid for the tranche or if tranche
    is null it returns the total principal repaid for the agreeement*/
	
		m_Principal t_Lms_Payment_Dtls.f_Amount%Type;
	
	Begin
	
		Select /*Nvl(Sum(Nvl(Pd.f_Amount, 0)) - Sum(Nvl(Pd.f_Rev_Amount, 0)), 0)*/
           SUM(CASE
                 WHEN NVL(PH.C_PAYENTRY_REV_YN, 'N') = 'Y' OR
                      NVL(PH.C_PAY_REVERSE_YN, 'N') = 'Y' THEN
                  -1
                 ELSE
                  1
               END * PD.F_AMOUNT)
			Into m_Principal
			From t_Lms_Payment_Dtls   Pd,
					 t_Lms_Payment_Header Ph,
					 t_Lms_Instruments    i
		 Where Ph.Sz_Org_Code = p_Org_Code
			 And Ph.Sz_Org_Code = Pd.Sz_Org_Code
			 And Ph.i_Tran_Id = Pd.i_Tran_Id
			 And Ph.i_Payment_Srno = Pd.i_Payment_Srno
			 And Ph.Sz_Loan_Account_No = p_Loan_Account_No
			 And Pd.Sz_Tranhead = 'PRN_AMT'
			 And Pd.i_Tranche_No =
					 Decode(p_Tranche_No, '', Pd.i_Tranche_No, p_Tranche_No)
			 And i.Sz_Org_Code = Ph.Sz_Org_Code
			 And i.i_Tran_Id = Pd.i_Tran_Id
			 And i.c_Payment_Status = 'Y';
	
		Return(m_Principal);
	
	Exception
		When Others Then
			Return - 1;
		
	End Get_Principal_Repay;

	Function Get_Interest_Repay(p_Org_Code        In Varchar2,
															p_Loan_Account_No In Varchar2,
															p_Tranche_No      In Number Default Null)
		Return Number As
		/* Returns the principal repaid for the tranche or if tranche
    is null it returns the total principal repaid for the agreeement*/
		m_Principal t_Lms_Payment_Dtls.f_Amount%Type;
	Begin
		Select /*Nvl(Sum(Nvl(Pd.f_Amount, 0)) - Sum(Nvl(Pd.f_Rev_Amount, 0)), 0)*/
           SUM(CASE
                 WHEN NVL(PH.C_PAYENTRY_REV_YN, 'N') = 'Y' OR
                      NVL(PH.C_PAY_REVERSE_YN, 'N') = 'Y' THEN
                  -1
                 ELSE
                  1
               END * PD.F_AMOUNT)
			Into m_Principal
			From t_Lms_Payment_Dtls   Pd,
					 t_Lms_Payment_Header Ph,
					 t_Lms_Instruments    i
		 Where Ph.Sz_Org_Code = p_Org_Code
			 And Ph.Sz_Org_Code = Pd.Sz_Org_Code
			 And Ph.i_Tran_Id = Pd.i_Tran_Id
			 And Ph.i_Payment_Srno = Pd.i_Payment_Srno
			 And Ph.Sz_Loan_Account_No = p_Loan_Account_No
			 And Pd.Sz_Tranhead = 'INT_AMT'
			 And Pd.i_Tranche_No =
					 Decode(p_Tranche_No, '', Pd.i_Tranche_No, p_Tranche_No)
			 And i.Sz_Org_Code = Ph.Sz_Org_Code
			 And i.i_Tran_Id = Pd.i_Tran_Id
			 And i.c_Payment_Status = 'Y';
		Return(m_Principal);
	Exception
		When Others Then
			Return - 1;
	End Get_Interest_Repay;

	Function Get_Additional_Repay(p_Org_Code        In Varchar2,
																p_Loan_Account_No In Varchar2,
																p_Tranche_No      In Number Default Null)
		Return Number As
	
		/* Returns the future principal repaid for the tranche or if
    tranche is null it returns the total future principal repaid for
    the agreeement*/
	
		m_Addrepay t_Lms_Payment_Dtls.f_Amount%Type;
	
	Begin
	
		Select /*Nvl(Sum(Nvl(Pd.f_Amount, 0)) - Sum(Nvl(Pd.f_Rev_Amount, 0)), 0)*/
           SUM(CASE
                 WHEN NVL(PH.C_PAYENTRY_REV_YN, 'N') = 'Y' OR
                      NVL(PH.C_PAY_REVERSE_YN, 'N') = 'Y' THEN
                  -1
                 ELSE
                  1
               END * PD.F_AMOUNT)
			Into m_Addrepay
			From t_Lms_Payment_Dtls   Pd,
					 t_Lms_Payment_Header Ph,
					 t_Lms_Instruments    i
		 Where Ph.Sz_Org_Code = p_Org_Code
			 And Ph.Sz_Org_Code = Pd.Sz_Org_Code
			 And Ph.i_Tran_Id = Pd.i_Tran_Id
			 And Ph.i_Payment_Srno = Pd.i_Payment_Srno
			 And Ph.Sz_Loan_Account_No = p_Loan_Account_No
			 And Pd.Sz_Tranhead = 'FUT_PRN'
			 And Pd.i_Tranche_No =
					 Decode(p_Tranche_No, '', Pd.i_Tranche_No, p_Tranche_No)
			 And i.Sz_Org_Code = Ph.Sz_Org_Code
			 And i.i_Tran_Id = Pd.i_Tran_Id
			 And i.c_Payment_Status = 'Y';
	
		Return(m_Addrepay);
	
	Exception
		When Others Then
			Return - 1;
		
	End Get_Additional_Repay;

	Function Get_Charges_Repay(p_Org_Code        In Varchar2,
														 p_Loan_Account_No In Varchar2,
														 p_Tranche_No      In Number Default Null)
		Return Number As
	
		/* Returns the charges paid for the tranche or if tranche
    is null it returns the total charges paid for the agreeement*/
	
		m_Chargerepay t_Lms_Payment_Dtls.f_Amount%Type;
	
	Begin
	
		Select /*Nvl(Sum(Nvl(Pd.f_Amount, 0)) - Sum(Nvl(Pd.f_Rev_Amount, 0)), 0)*/
           SUM(CASE
                 WHEN NVL(PH.C_PAYENTRY_REV_YN, 'N') = 'Y' OR
                      NVL(PH.C_PAY_REVERSE_YN, 'N') = 'Y' THEN
                  -1
                 ELSE
                  1
               END * PD.F_AMOUNT)
			Into m_Chargerepay
			From t_Lms_Payment_Dtls   Pd,
					 t_Lms_Payment_Header Ph,
					 t_Lms_Instruments    i
		 Where Ph.Sz_Org_Code = p_Org_Code
			 And Ph.i_Tran_Id = Pd.i_Tran_Id
			 And Ph.i_Payment_Srno = Pd.i_Payment_Srno
			 And Ph.Sz_Org_Code = Pd.Sz_Org_Code
			 And Ph.Sz_Loan_Account_No = p_Loan_Account_No
			 And Pd.Sz_Trantype In ('FEE', 'TAX') --AND Pd.Sz_Trantype = 'CHARGES'
			 And Nvl(Pd.i_Tranche_No, '1') =
					 Decode(p_Tranche_No, '', Nvl(Pd.i_Tranche_No, '1'), p_Tranche_No)
			 And i.Sz_Org_Code = Ph.Sz_Org_Code
			 And i.i_Tran_Id = Pd.i_Tran_Id
			 And i.c_Payment_Status = 'Y';
	
		Return(m_Chargerepay);
	
	Exception
		When Others Then
			Return - 1;
		
	End Get_Charges_Repay;

	Function Get_Charges_Repay_Head(p_Org_Code        In Varchar2,
																	p_Loan_Account_No In Varchar2,
																	p_Tran_Head       In Varchar2,
																	p_Charge_Bookid   In Number,
																	p_Tranche_No      In Number Default Null,
																	p_Servicetaxyn    In Varchar Default 'Y')
		Return Number As
	
		/* Returns the charges paid for the head*/
	
		m_Chargerepay t_Lms_Payment_Dtls.f_Amount%Type;
	
	Begin
	
		If p_Servicetaxyn = 'Y' Then
			Select /*Nvl(Sum(Nvl(Pd.f_Amount, 0)) - Sum(Nvl(Pd.f_Rev_Amount, 0)), 0)*/
             SUM(CASE
                 WHEN NVL(PH.C_PAYENTRY_REV_YN, 'N') = 'Y' OR
                      NVL(PH.C_PAY_REVERSE_YN, 'N') = 'Y' THEN
                  -1
                 ELSE
                  1
               END * PD.F_AMOUNT)
				Into m_Chargerepay
				From t_Lms_Payment_Dtls   Pd,
						 t_Lms_Payment_Header Ph,
						 t_Lms_Instruments    i
			 Where Ph.Sz_Org_Code = p_Org_Code
				 And Ph.i_Tran_Id = Pd.i_Tran_Id
				 And Ph.i_Payment_Srno = Pd.i_Payment_Srno
				 And Ph.Sz_Org_Code = Pd.Sz_Org_Code
				 And Ph.Sz_Loan_Account_No = p_Loan_Account_No
				 And Pd.i_Reference_No = p_Charge_Bookid
				 And Pd.Sz_Trantype In ('FEE', 'TAX')
				 And Pd.Sz_Tranhead = p_Tran_Head
				 And Nvl(Pd.i_Tranche_No, '1') =
						 Decode(p_Tranche_No,
										'',
										Nvl(Pd.i_Tranche_No, '1'),
										p_Tranche_No)
				 And i.Sz_Org_Code = Ph.Sz_Org_Code
				 And i.i_Tran_Id = Pd.i_Tran_Id
				 And i.c_Payment_Status = 'Y';
		Else
			Select /*Sum(Nvl(Pd.f_Amount, 0)) - Sum(Nvl(Pd.f_Rev_Amount, 0))*/
             SUM(CASE
                 WHEN NVL(PH.C_PAYENTRY_REV_YN, 'N') = 'Y' OR
                      NVL(PH.C_PAY_REVERSE_YN, 'N') = 'Y' THEN
                  -1
                 ELSE
                  1
               END * PD.F_AMOUNT)
				Into m_Chargerepay
				From t_Lms_Payment_Dtls   Pd,
						 t_Lms_Payment_Header Ph,
						 t_Lms_Instruments    i
			 Where Ph.Sz_Org_Code = p_Org_Code
				 And Ph.Sz_Org_Code = Pd.Sz_Org_Code
				 And Ph.i_Tran_Id = Pd.i_Tran_Id
				 And Ph.i_Payment_Srno = Pd.i_Payment_Srno
				 And Ph.Sz_Loan_Account_No = p_Loan_Account_No
				 And Pd.Sz_Tranhead = p_Tran_Head
				 And Pd.i_Reference_No = p_Charge_Bookid
				 And Pd.Sz_Trantype = 'FEE'
				 And Nvl(Pd.i_Tranche_No, '1') =
						 Decode(p_Tranche_No,
										'',
										Nvl(Pd.i_Tranche_No, '1'),
										p_Tranche_No)
				 And i.Sz_Org_Code = Ph.Sz_Org_Code
				 And i.i_Tran_Id = Pd.i_Tran_Id
				 And i.c_Payment_Status = 'Y';
		End If;
	
		Return(m_Chargerepay);
	
	Exception
		When Others Then
			Return - 1;
		
	End Get_Charges_Repay_Head;

	Function Get_Subproduct_Repay(p_Org_Code        In Varchar2,
																p_Loan_Account_No In Varchar2,
																p_Tranche_No      In Number Default Null)
		Return Number As
	
		/* Returns the subprouct repaid for the tranche or if tranche
    is null it returns the total subproduct repaid for the agreeement*/
	
		m_Subprdctrepay t_Lms_Payment_Dtls.f_Amount%Type;
	
	Begin
	
		Select /*Nvl(Sum(Nvl(Pd.f_Amount, 0)) - Sum(Nvl(Pd.f_Rev_Amount, 0)), 0)*/
           SUM(CASE
                 WHEN NVL(PH.C_PAYENTRY_REV_YN, 'N') = 'Y' OR
                      NVL(PH.C_PAY_REVERSE_YN, 'N') = 'Y' THEN
                  -1
                 ELSE
                  1
               END * PD.F_AMOUNT)
			Into m_Subprdctrepay
			From t_Lms_Payment_Dtls   Pd,
					 t_Lms_Payment_Header Ph,
					 t_Lms_Instruments    i
		 Where Ph.Sz_Org_Code = p_Org_Code
			 And Ph.Sz_Org_Code = Pd.Sz_Org_Code
			 And Ph.i_Tran_Id = Pd.i_Tran_Id
			 And Ph.i_Payment_Srno = Pd.i_Payment_Srno
			 And Ph.Sz_Loan_Account_No = p_Loan_Account_No
			 And Pd.Sz_Trantype = 'SUB_PRODUCT'
			 And Pd.i_Tranche_No =
					 Decode(p_Tranche_No, '', Pd.i_Tranche_No, p_Tranche_No)
			 And i.Sz_Org_Code = Ph.Sz_Org_Code
			 And i.i_Tran_Id = Pd.i_Tran_Id
			 And i.c_Payment_Status = 'Y';
	
		Return(m_Subprdctrepay);
	
	Exception
		When Others Then
			Return - 1;
		
	End Get_Subproduct_Repay;

  FUNCTION Get_Subprod_Due(p_Org_Code        IN VARCHAR2,
                           p_Loan_Account_No IN VARCHAR2,
                           p_Tranche_No      IN NUMBER DEFAULT NULL)
    RETURN NUMBER AS
    m_Subprod_Due t_Lms_Payment_Dtls.f_Amount%TYPE;
  BEGIN
    SELECT Nvl(SUM(f_Due_Amt), 0) f_Due_Amt
      INTO m_Subprod_Due
      FROM t_Lms_Due_Dtls Dd
     WHERE Dd.Sz_Org_Code = p_Org_Code
       AND Dd.Sz_Loan_Account_No = p_Loan_Account_No
       AND Dd.i_Tranche_No =
           Decode(p_Tranche_No, '', Dd.i_Tranche_No, p_Tranche_No)
       AND Dd.Sz_Trantype = 'SUB_PRODUCT';
    RETURN(m_Subprod_Due);
  EXCEPTION
    WHEN OTHERS THEN
      RETURN - 1;
  END Get_Subprod_Due;
  FUNCTION Get_Fut_Subprod_Amt(p_Org_Code        IN VARCHAR2,
                               p_Loan_Account_No IN VARCHAR2,
                               p_Tranche_No      IN NUMBER DEFAULT NULL)
    RETURN NUMBER AS
    m_Fut_Subprd_Due t_Lms_Payment_Dtls.f_Amount%TYPE;
  BEGIN
    SELECT Nvl(SUM(Ind.f_Premium_Amt -
                   (SELECT Nvl(SUM(Ins.f_Insurance_Amt), 0) f_Insurance_Amt
                      FROM t_Lms_Insurance_Schedule Ins
                     WHERE Ins.Sz_Org_Code = Ind.Sz_Org_Code
                       AND Ins.Sz_Loan_Account_No = Ind.Sz_Loan_Account_No
                       AND Ins.i_Insurance_Srno = Ind.i_Insurance_Srno)),
               0) f_Fut_Insurance
      INTO m_Fut_Subprd_Due
      FROM t_Lms_Insurance_Dtls Ind
     WHERE Ind.Sz_Org_Code = p_Org_Code
       AND Ind.Sz_Loan_Account_No = p_Loan_Account_No
       AND Ind.i_Tranche_No =
           Decode(p_Tranche_No, '', Ind.i_Tranche_No, p_Tranche_No)
       AND Ind.Sz_Mode_Recovery = 'INSTAMT';
    RETURN(m_Fut_Subprd_Due);
  EXCEPTION
    WHEN OTHERS THEN
      RETURN 0;
  END Get_Fut_Subprod_Amt;
	Function Get_Excess_Amount(p_Org_Code        In Varchar2,
														 p_Loan_Account_No In Varchar2) Return Number As
	
		/* Returns the excess amount for the agreeement*/
	
		m_Excessamount t_Lms_Loan_Account.f_Excess_Amt%Type;
	
	Begin
		Select Nvl(Ta.f_Excess_Amt, 0)
			Into m_Excessamount
			From t_Lms_Loan_Account Ta
		 Where Ta.Sz_Org_Code = p_Org_Code
			 And Ta.Sz_Loan_Account_No = p_Loan_Account_No;
	
		Return m_Excessamount;
	
	Exception
		When Others Then
			Return - 1;
		
	End;

	Function Get_Waived_Amount(p_Org_Code        In Varchar2,
														 p_Loan_Account_No In Varchar2,
														 p_Chgbooktran_Id  In Number Default Null)
		Return Number As
	
		/* Returns the waived amount for the agreeement*/
	
		m_Waivedamount t_Lms_Chg_Waiver_Dtls.f_Waived_Amt%Type;
	
	Begin
	
		Select Nvl(Sum(Nvl(Tw.f_Waived_Amt, 0)), 0)
			Into m_Waivedamount
			From t_Lms_Chg_Waiver_Dtls Tw
		 Where Tw.Sz_Org_Code = p_Org_Code
			 And Tw.Sz_Loan_Account_No = p_Loan_Account_No
			 And Tw.i_Chgbooktran_Id =
					 Decode(p_Chgbooktran_Id,
									'',
									Tw.i_Chgbooktran_Id,
									p_Chgbooktran_Id);
	
		Return m_Waivedamount;
	
	Exception
		When Others Then
			Return - 0;
		
	End;

	Function Get_Provision_Amount(p_Org_Code        In Varchar2,
																p_Loan_Account_No In Varchar2) Return Number As
	
		/* Returns the provision amount for the agreeement*/
	
		m_Provision_Amt t_Lms_Provision_Dtls.f_Prov_Amt%Type;
	
	Begin
	
		Select /*SUM(Nvl(Pd.f_Sec_Prov_Amt, 0) *
                           Decode(Pd.c_Reverse_Flag, 'Y', -1, 1)) +
                       SUM(Nvl(Pd.f_Non_Sec_Prov_Amt, 0) *
                           Decode(Pd.c_Reverse_Flag, 'Y', -1, 1)) +
                       SUM(Nvl(Pd.f_Spe_Prov_Amt, 0) *
                           Decode(Pd.c_Reverse_Flag, 'Y', -1, 1))*/
		 Sum(Nvl(Pd.f_Prov_Amt, 0) * Decode(Pd.c_Reverse_Flag, 'Y', -1, 1)) f_Provision_Amt
			Into m_Provision_Amt
			From t_Lms_Provision_Dtls Pd
		 Where Pd.Sz_Org_Code = p_Org_Code
			 And Pd.Sz_Loan_Account_No = p_Loan_Account_No;
	
		Return m_Provision_Amt;
	
	Exception
		When Others Then
		
			Return 0;
	End Get_Provision_Amount;

	Function Total_Due_Charges(p_Org_Code        In Varchar2,
														 p_Loan_Account_No In Varchar2,
														 p_Tranche_No      In Number Default Null)
		Return Number Is
		m_Total_Charges t_Lms_Due_Dtls.f_Due_Amt%Type;
		m_Due_Charges   t_Lms_Due_Dtls.f_Due_Amt%Type;
	Begin
	
		If p_Tranche_No Is Not Null Then
		
			Select Nvl(Sum(Nvl(Td.f_Due_Amt, 0)), 0)
				Into m_Total_Charges
				From t_Lms_Due_Dtls Td
			 Where Td.Sz_Org_Code = p_Org_Code
				 And Td.Sz_Loan_Account_No = p_Loan_Account_No
				 And Td.i_Tranche_No = p_Tranche_No
				 And Td.Sz_Trantype In ('SUB_PRODUCT');
		
		Elsif p_Tranche_No Is Null Then
		
			Select Nvl(Sum(Nvl(Td.f_Due_Amt, 0)), 0)
				Into m_Total_Charges
				From t_Lms_Due_Dtls Td
			 Where Td.Sz_Org_Code = p_Org_Code
				 And Td.Sz_Loan_Account_No = p_Loan_Account_No
						--  AND Td.i_Tranche_No IS NULL
				 And Td.Sz_Trantype In ('SUB_PRODUCT');
		
		End If;
	
		m_Due_Charges := Nvl(Get_Due_Charges(p_Org_Code        => p_Org_Code,
																				 p_Loan_Account_No => p_Loan_Account_No,
																				 p_Tranche_No      => p_Tranche_No),
												 0);
		Return m_Total_Charges + m_Due_Charges;
	End;

	Function Get_Outstanding_Subproduct(p_Org_Code        In Varchar2,
																			p_Loan_Account_No In Varchar2,
																			p_Ins_Srno        In Number Default Null,
																			p_Till_Date       In Date Default Null)
		Return Number Is
	
		m_Insurance_Amt t_Lms_Insurance_Schedule.f_Insurance_Amt%Type := 0;
		m_Add_Query     Varchar2(2000);
		m_Add_Query2    Varchar2(2000);
		m_Add_Query4    Varchar2(2000);
		m_Add_Query5    Varchar2(2000);
		m_Query         Varchar2(2000);
		m_Tot_Premium   t_Lms_Insurance_Dtls.f_Premium_Amt%Type := 0;
		m_Amt_Corrected t_Lms_Insurance_Dtls.f_Amt_Corrected%Type := 0; -- Added by Sandeep on 04.03.2010
	
	Begin
		
			If p_Till_Date Is Not Null Then
				m_Add_Query := ' AND Ins.DT_INSTALLMENTDUE<=''' || p_Till_Date || '''';
			End If;
		m_Add_Query4 := ' AND I.SZ_MODE_RECOVERY = ''' || 'INSTAMT' || '''';
		m_Add_Query5 := ' AND NVL(I.c_Infoonly,''' || 'N' || ''') = ''' || 'N' || '''';
	
			If p_Ins_Srno Is Not Null Then
				m_Add_Query2 := ' AND Ins.i_Insurance_Srno = ''' || p_Ins_Srno || '''';
			End If;
		
			Begin
		
				m_Query := 'SELECT Nvl(SUM(Ins.f_Insurance_Amt), 0)
		   FROM t_Lms_Insurance_Schedule Ins,t_Lms_Insurance_Dtls i
		WHERE ins.sz_org_code = i.sz_org_code
		AND   ins.sz_loan_account_no = i.sz_loan_account_no
		AND   ins.i_insurance_srno = i.i_insurance_srno
		and Ins.Sz_Org_Code = ''' || p_Org_Code || '''' ||
									 ' AND Ins.Sz_Loan_Account_No = ''' || p_Loan_Account_No || '''' ||
								 m_Add_Query2 || m_Add_Query || m_Add_Query4 ||
								 m_Add_Query5;
			
				Execute Immediate m_Query
					Into m_Insurance_Amt;
			
			Exception
				When No_Data_Found Then
			
					m_Insurance_Amt := 0;
			End;
		
		If p_Ins_Srno Is Not Null Then
			Begin
				Select Nvl(Sum(i.f_Premium_Amt), 0), Nvl(Sum(i.f_Amt_Corrected), 0)
						Into m_Tot_Premium, m_Amt_Corrected


						From t_Lms_Insurance_Dtls i
					 Where i.Sz_Org_Code = p_Org_Code
						 And i.Sz_Loan_Account_No = p_Loan_Account_No
						 And i.i_Insurance_Srno = p_Ins_Srno
					 And Nvl(c_Infoonly, 'N') = 'N'
					 And i.Sz_Mode_Recovery = 'INSTAMT';
			Exception
				When No_Data_Found Then
					m_Tot_Premium   := 0;
					m_Amt_Corrected := 0;
			End;
		Else
			Select Nvl(Sum(i.f_Premium_Amt), 0), Nvl(Sum(i.f_Amt_Corrected), 0)

			
				Into m_Tot_Premium, m_Amt_Corrected

			
				From t_Lms_Insurance_Dtls i
			 Where i.Sz_Org_Code = p_Org_Code
				 And i.Sz_Loan_Account_No = p_Loan_Account_No
			AND Nvl(c_Infoonly, 'N') = 'N'
			AND i.Sz_Mode_Recovery = 'INSTAMT';
		End If;
	
				-- Added by Sandeep on 09.04.2010
				m_Insurance_Amt := Nvl(m_Tot_Premium, 0) - Nvl(m_Insurance_Amt, 0) -
													 Nvl(m_Amt_Corrected, 0); -- Modified by Sandeep on 04.03.2010
		m_Insurance_Amt := m_Insurance_Amt;
	
		Return m_Insurance_Amt;
	
	End;

  --ADDED BY SURABHI 16/04/2015 FOR ECS PRE-EMI CASES START
  Function Get_Total_PreEmiDue(p_Org_Code        In Varchar2,
                               p_Loan_Account_No In Varchar2,
                               P_BusinessDate    IN Date) Return Number As
    /* Returns the total outstanding amount excluding future principal for the tranche or if tranche
    is null it returns the total outstanding for the agreeement*/
    m_Totaldue               t_Lms_Due_Dtls.f_Due_Amt%Type;
    m_f_Total_Penal_Computed t_Lms_Due_Dtls.f_Due_Amt%Type;
    m_BusinessDate           Date := nvl(P_BusinessDate,
                                         Pkg_General_Services.Get_Business_Date(p_Org_Code));
    CPF_EXCEPTION exception;
		M_PENAL_TYPE CHAR;
  Begin
    /* SELECT Nvl(SUM(Nvl(Td.f_Due_Amt, 0)), 0)
     INTO m_Totaldue
     FROM t_Lms_Due_Dtls Td
    WHERE Td.Sz_Org_Code = p_Org_Code
     AND Td.Sz_Loan_Account_No = p_Loan_Account_No;*/
    --AND Td.Sz_Tranhead <> 'FUT_PRN';
    Select Nvl(Sum(Nvl(Td.f_Due_Amt, 0)), 0) +
           Nvl(Sum(Nvl(a.f_Tax_Amt, 0)), 0)
      Into m_Totaldue
      From t_Lms_Due_Dtls Td,
           (Select Sum(Txd.f_Tax_Amt) f_Tax_Amt, Txd.i_Srno
              From t_Lms_Taxdue_Dtls Txd
             Where Txd.Sz_Loan_Account_No = p_Loan_Account_No
               And Txd.Sz_Org_Code = p_Org_Code
             Group By Txd.i_Srno) a
     Where Td.Sz_Org_Code = p_Org_Code
       And Td.Sz_Loan_Account_No = p_Loan_Account_No
       And Td.i_Srno = a.i_Srno(+);
    If m_Totaldue > 0 Then
     /* m_f_Total_Penal_Computed := Pkg_Penal_Interest.Get_Penal_Computed_Amt(p_Org_Code        => p_Org_Code,
                                                                            p_Loan_Account_No => p_Loan_Account_No,
                                                                            p_Tranche_No      => Null,
                                                                            p_Businessdate    => m_BusinessDate);*/
      BEGIN
      SELECT NVL(D.C_PENAL_CALC_TYPE, 'P') /*P PERCENT,L -- LUMPSUM */
        INTO M_PENAL_TYPE
        FROM PRD_AMORT_PORTFOLIO_XREF D, T_LMS_PRODUCT_DTLS P
       WHERE P.SZ_ORG_CODE = P_ORG_CODE
         AND P.SZ_LOAN_ACCOUNT_NO = p_Loan_Account_No
         AND D.SZ_PRODUCT_CODE = P.SZ_PRODUCT_CODE;
      EXCEPTION
      WHEN OTHERS THEN
        M_PENAL_TYPE := 'P';
      END;
      IF(M_PENAL_TYPE='P') THEN
      m_f_Total_Penal_Computed := Pkg_Penal_Interest.Get_Penal_Computed_Amt(p_Org_Code        => p_Org_Code,
                                                                            p_Loan_Account_No => p_Loan_Account_No,
                                                                            p_Tranche_No      => Null,
                                                                            p_Businessdate    => Pkg_General_Services.Get_Business_Date(p_Org_Code));
      ELSIF(M_PENAL_TYPE='L') THEN
      m_f_Total_Penal_Computed := PKG_PENAL_INTEREST_LUMPSUM.GET_ACC_PENAL_AMT(P_ORG_CODE        => p_Org_Code,
                                                                P_LOAN_ACCOUNT_NO => p_Loan_Account_No,
                                                                P_TRANCHE_NO      => 1,
                                                                P_BUSINESSDATE    => Pkg_General_Services.Get_Business_Date(p_Org_Code),
                                                                P_VALUE_DATE      => NULL);  
      END IF;   																																						
      IF m_f_Total_Penal_Computed = -101 OR m_f_Total_Penal_Computed = -102 THEN
        RAISE CPF_EXCEPTION;
      End If;
    End If;
    Return Nvl(m_Totaldue, 0) + Nvl(m_f_Total_Penal_Computed, 0);
  Exception
    when CPF_EXCEPTION then
      return m_f_Total_Penal_Computed;
    When Others Then
      Return - 1;
  End Get_Total_PreEmiDue;
    Function Get_Total_Due_noaccrual(p_Org_Code        In Varchar2,
                         p_Loan_Account_No In Varchar2) Return Number As
    /* Returns the total outstanding amount excluding future principal for the tranche or if tranche
    is null it returns the total outstanding for the agreeement*/
    m_Totaldue               t_Lms_Due_Dtls.f_Due_Amt%Type;
    CPF_EXCEPTION exception;
  Begin
    /* SELECT Nvl(SUM(Nvl(Td.f_Due_Amt, 0)), 0)
     INTO m_Totaldue
     FROM t_Lms_Due_Dtls Td
    WHERE Td.Sz_Org_Code = p_Org_Code
     AND Td.Sz_Loan_Account_No = p_Loan_Account_No;*/
    --AND Td.Sz_Tranhead <> 'FUT_PRN';
    Select Nvl(Sum(Nvl(Td.f_Due_Amt, 0)), 0) +
           Nvl(Sum(Nvl(a.f_Tax_Amt, 0)), 0)
      Into m_Totaldue
      From t_Lms_Due_Dtls Td,
           (Select Sum(Txd.f_Tax_Amt) f_Tax_Amt, Txd.i_Srno
              From t_Lms_Taxdue_Dtls Txd
             Where Txd.Sz_Loan_Account_No = p_Loan_Account_No
               And Txd.Sz_Org_Code = p_Org_Code
             Group By Txd.i_Srno) a
     Where Td.Sz_Org_Code = p_Org_Code
       And Td.Sz_Loan_Account_No = p_Loan_Account_No
       And Td.i_Srno = a.i_Srno(+);
    Return Nvl(m_Totaldue, 0) ;
  Exception
    When Others Then
      Return - 1;
  End Get_Total_Due_noaccrual;
---ADDED BY SURABHI 16/04/2015 FOR ECS PRE-EMI CASES ENDS 
End Pkg_calc_amount 
/
