IF OBJECT_ID('pj_SMS_Alert','P') IS NOT NULL
DROP PROCEDURE pj_SMS_Alert
GO
CREATE PROCEDURE pj_SMS_Alert
(
	@JobID		nVarchar(50)
)
AS
BEGIN 
	SET NOCOUNT ON
	
	DECLARE @StartTime	Datetime
	DECLARE @Endtime	Datetime
	DECLARE @ErrorTime	Datetime
	DECLARE	@ErrorMsg	nVarchar(max)
	
	
	BEGIN TRY 
	

	
	SELECT @StartTime			= LastRunStartTime,
			@Endtime			= LastRunEndTime
	FROM t_JobConfiguration
	WHERE JobID					=	@JobID

	IF (@StartTime IS NOT NULL AND @Endtime  IS NULL)
	BEGIN
		GOTO EndStatment;
	END
	
	UPDATE t_JobConfiguration
	SET LastRunStartTime	=	GETDATE(),
		LastRunEndTime		=	 NULL
	WHERE JobID			=	@JobID
		   
    DECLARE @AlertMessage			nVarchar(Max)
	DECLARE @EventID				nVarchar(25)
	DECLARE @BankID					BankID
	
	SELECT @BankID			 =	 BankID
	FROM t_SystemBankSetting

	CREATE TABLE #SAT_AlertMsgLog
	(
		OurBranchID                 nVarchar(6)		,                
		AccountID                   nVarchar(20)	,                
		LoanSeries                  SmallInt		,                
		AlertMessage                nVarchar(Max)	,                
		CreatedOn                   Datetime		,       
		EventID                     nVarchar(25)	,                
		ClientID                    nVarchar(20)	,        
		MobileNo                    nVarchar(30)	,
		SanctionAmount              Money			,
		CollectedAmount             Money			,
		CollectionDate              Smalldatetime	,
		AmountPaid					Money			,
		BookedAmount				Money			,
		DisbursedAmount				Money			,
		DisbursementDate			DateTime		,
		DueAmount					Money			,
		DueDate						SmallDateTime	,
		lonePreclosureDate   		SmallDateTime	,
		TrxDate						smalldatetime	,
		TrxBatchID					VARCHAR(8)		,
		ReversedAmount				Money	,
		ReversalDate				smalldatetime						
	)
	
	CREATE TABLE #LoanInstallment
	(
		OurBranchID			nVarchar(10),
		AccountID			nVarchar(25),
		Loanseries			SmallInt,
		WorkingDate			SmallDateTime
	)
	
  -------------------SANCTION ------------- 
	IF EXISTS(SELECT 1 FROM t_SAT_AlertFormat
			  WHERE EventID = 'SANCTION')
	BEGIN
		INSERT INTO #SAT_AlertMsgLog
		(
			OurBranchID  ,                
			AccountID   ,       
			LoanSeries  ,             
			CreatedOn   ,             
			SanctionAmount,
			EventID
		)
		SELECT  
			t_Loan.OurBranchID,
			t_Loan.AccountID,
			t_Loan.LoanSeries,
			GETDATE(),
			t_Loan.SanctionedAmount,
			'SANCTION'
		FROM t_Loan WITH(NOLOCK)
		WHERE t_Loan.SanctionedDate = dbo.f_GetWorkingDate(t_Loan.OurBranchID)
		AND t_loan.OurBranchID NOT IN ('2009','2015','2017','2021','2022','2030','2031','2041','2042','2035','2039','2018','2019','2020','2032','1007','1008','1010','1012','2016','9998','9999')
		AND t_Loan.LoanStatusID IN('A','N','S')      --   A =Active ,N = NPA ,S = Sanctioned Loan 
		AND NOT EXISTS
			(	
				SELECT 1
				FROM t_SAT_AlertMsgLog
				WHERE t_SAT_AlertMsgLog.OurBranchID			=	t_Loan.OurBranchID
					AND t_SAT_AlertMsgLog.AccountID			=	t_Loan.AccountID
					AND t_SAT_AlertMsgLog.LoanSeries		=	t_Loan.LoanSeries
					AND t_SAT_AlertMsgLog.EventID			=	'SANCTION'
			) 	
		     
		UPDATE #SAT_AlertMsgLog 
		SET #SAT_AlertMsgLog.AlertMessage		=	REPLACE (REPLACE(t_SAT_AlertFormat.MessageFormat,'{AMT}',#SAT_AlertMsgLog.SanctionAmount),'{A}', #SAT_AlertMsgLog.AccountID)
		FROM #SAT_AlertMsgLog
		LEFT JOIN  t_SAT_BranchLanguage
		ON t_SAT_BranchLanguage.OurBranchID		=	#SAT_AlertMsgLog.OurBranchID
		LEFT JOIN	t_SAT_AlertFormat
		ON t_SAT_AlertFormat.EventID			=	'SANCTION'
		AND t_SAT_AlertFormat.LanguageID		=	ISNULL(t_SAT_BranchLanguage.LanguageID,'en')
		WHERE #SAT_AlertMsgLog.EventID			=	'SANCTION'
	END
	
   -----------COLLECTION ----------------
   
   IF EXISTS(SELECT 1 FROM t_SAT_AlertFormat
			  WHERE EventID = 'COLLECTION')
	BEGIN   	
		INSERT INTO #SAT_AlertMsgLog
		(
			OurBranchID ,                
			AccountID   ,
			LoanSeries  ,       
			CreatedOn   ,             
			CollectedAmount,                    
			EventID,
			TrxBatchID,
			TrxDate	 
		)
		SELECT                   
			t_Transaction.OurBranchID,
			t_Transaction.AccountID,
			t_Transaction.LoanSeries,
			GETDATE(),
			SUM(t_Transaction.Amount),        
			'COLLECTION',
			t_Transaction.TrxBatchID,
			t_Transaction.TrxDate
		FROM t_Transaction WITH(NOLOCK)
		INNER JOIN t_ProductLoan WITH(NOLOCK)
		ON t_Transaction.ProductID	= t_ProductLoan.ProductID
		AND t_ProductLoan.BankID	= @BankID
		WHERE DeletedOn IS NULL
			AND t_Transaction.OurBranchID NOT IN ('2009','2015','2017','2021','2022','2030','2031','2041','2042','2035','2039','2018','2019','2020','2032','1007','1008','1010','1012','2016','9998','9999')
			AND TrxFlagID = ''
			AND AccountTypeID = 'C'
			AND TrxAmount > 0  ----
			AND T_Transaction.TrxDate = dbo.f_GetWorkingDate(t_Transaction.OurBranchID)
			AND ModuleID IN ('3110','3109','3106')
			--AND ModuleID NOT IN (3096,3116,3117,4548,4555,4556,5092,4545,4547) 
			AND NOT EXISTS(	
								SELECT 1
								FROM t_SAT_AlertMsgLog
								WHERE t_SAT_AlertMsgLog.OurBranchID		=	t_Transaction.OurBranchID
									AND t_SAT_AlertMsgLog.AccountID		=	t_Transaction.AccountID
									AND t_SAT_AlertMsgLog.TrxDate		=	t_Transaction.TrxDate
									AND t_SAT_AlertMsgLog.TrxBatchID	=	t_Transaction.TrxBatchID
									AND t_SAT_AlertMsgLog.EventID		=	'COLLECTION'
									
							) 
		GROUP BY t_Transaction.OurBranchID,
				 t_Transaction.AccountID,
				 t_Transaction.LoanSeries,			
				 t_Transaction.TrxDate,
				 t_Transaction.TrxBatchID					   	
	       
		UPDATE #SAT_AlertMsgLog 
		SET #SAT_AlertMsgLog.AlertMessage	=  REPLACE( REPLACE (REPLACE  (t_SAT_AlertFormat.MessageFormat,'{AMT}',#SAT_AlertMsgLog.CollectedAmount),'{A}', #SAT_AlertMsgLog.AccountID),'{DATE}',CONVERT(nvarchar(10),#SAT_AlertMsgLog.TrxDate,103))
		FROM #SAT_AlertMsgLog
		LEFT JOIN  t_SAT_BranchLanguage
		ON t_SAT_BranchLanguage.OurBranchID	=	 #SAT_AlertMsgLog.OurBranchID
		LEFT JOIN	t_SAT_AlertFormat
		ON t_SAT_AlertFormat.EventID		=	'COLLECTION'
		AND t_SAT_AlertFormat.LanguageID	=	 ISNULL(t_SAT_BranchLanguage.LanguageID,'en')
		WHERE #SAT_AlertMsgLog.EventID		=	'COLLECTION'
    END
    
    -------------------- Preclosure  -------------- 	
	IF EXISTS(SELECT 1 FROM t_SAT_AlertFormat
			  WHERE EventID = 'PRECLOSURE')
	BEGIN
		INSERT INTO #SAT_AlertMsgLog
		(
			OurBranchID ,                
			AccountID   ,       
			LoanSeries  ,             
			CreatedOn   ,             
			AmountPaid ,
			EventID,
			TrxDate,
			TrxBatchID
		)
		SELECT        
			t_loanPreclosure.OurBranchID,
			t_loanPreclosure.AccountID,
			t_loanPreclosure.LoanSeries,
			GETDATE(),
			SUM(t_LoanPreClosureDetail.SettlementAmount),
			'PRECLOSURE',
			 t_loanPreclosure.TrxDate,
			 t_loanPreclosure.TrxBatchID
		FROM t_loanPreclosure WITH(NOLOCK) --me
		INNER JOIN t_LoanPreClosureDetail WITH(NOLOCK) --me
		ON t_loanPreclosure.OurBranchID			= t_LoanPreClosureDetail.OurBranchID
		AND  t_loanPreclosure.AccountID			= t_LoanPreClosureDetail.AccountID
		AND t_loanPreclosure.OurBranchID	NOT	IN ('2009','2015','2017','2021','2022','2030','2031','2041','2042','2035','2039','2018','2019','2020','2032','1007','1008','1010','1012','2016','9998','9999')
		AND  t_loanPreclosure.LoanSeries		= t_LoanPreClosureDetail.LoanSeries
		 AND  t_loanPreclosure.PreCloseRefNo	= t_LoanPreClosureDetail.PreCloseRefNo
		WHERE PreClosureStatusID ='AC'           ---  pp = Pending Posting  AC = Account Closed
		AND t_loanPreclosure.TrxDate = dbo.f_GetWorkingDate(t_loanPreclosure.OurBranchID)
		AND NOT EXISTS(		
							SELECT 1
							FROM t_SAT_AlertMsgLog
							WHERE t_SAT_AlertMsgLog.OurBranchID			 =	t_loanPreclosure.OurBranchID
							 AND t_SAT_AlertMsgLog.AccountID			 =	t_loanPreclosure.AccountID
							 AND t_SAT_AlertMsgLog.LoanSeries			 =	t_loanPreclosure.LoanSeries
							 AND t_SAT_AlertMsgLog.EventID				 =	'PRECLOSURE'
							 AND t_SAT_AlertMsgLog.TrxDate				 =	t_loanPreclosure.TrxDate
							 AND t_SAT_AlertMsgLog.TrxBatchID			 =	t_loanPreclosure.TrxBatchID
					  )			    
		GROUP BY	t_loanPreclosure.OurBranchID,
					t_loanPreclosure.AccountID,
					t_loanPreclosure.LoanSeries,
					t_loanPreclosure.TrxDate,
					t_loanPreclosure.TrxBatchID
				
		UPDATE #SAT_AlertMsgLog 
		SET #SAT_AlertMsgLog.AlertMessage   =  REPLACE (REPLACE (REPLACE  (t_SAT_AlertFormat.MessageFormat,'{AMT}',#SAT_AlertMsgLog.AmountPaid),'{A}', #SAT_AlertMsgLog.AccountID),'{DATE}',CONVERT(nVarchar(10),#SAT_AlertMsgLog.TrxDate,103))
		FROM #SAT_AlertMsgLog
		LEFT JOIN  t_SAT_BranchLanguage
		ON t_SAT_BranchLanguage.OurBranchID	=	 #SAT_AlertMsgLog.OurBranchID
		LEFT JOIN	t_SAT_AlertFormat
		ON t_SAT_AlertFormat.EventID		= 'PRECLOSURE'
		AND t_SAT_AlertFormat.LanguageID	= ISNULL(t_SAT_BranchLanguage.LanguageID,'en')
		WHERE #SAT_AlertMsgLog.EventID		='PRECLOSURE'
	END
		
	  -------------------BOOKING  ----------------  
	IF EXISTS(SELECT 1 FROM t_SAT_AlertFormat
			  WHERE EventID = 'BOOKING')
	BEGIN  
		INSERT INTO #SAT_AlertMsgLog
		(
			OurBranchID  ,                
			AccountID   ,       
			LoanSeries  ,             
			CreatedOn   ,             
			BookedAmount,
			EventID
		)
		SELECT 
			t_WFLoanBooking.OurBranchID,
			t_WFLoanBooking.AccountID,
			t_WFLoanBooking.LoanSeries,
			GETDATE(),
			t_WFLoanBooking.BookedAmount,
			'BOOKING'
		FROM t_WFLoanBooking WITH(NOLOCK)
		WHERE t_WFLoanBooking.BookedDate = dbo.f_GetWorkingDate(t_WFLoanBooking.OurBranchID)
		AND t_WFLoanBooking.OurBranchID	NOT IN ('2009','2015','2017','2021','2022','2030','2031','2041','2042','2035','2039','2018','2019','2020','2032','1007','1008','1010','1012','2016','9998','9999')
			AND NOT EXISTS
			(	
				SELECT 1
				FROM t_SAT_AlertMsgLog WITH(NOLOCK)
				WHERE t_SAT_AlertMsgLog.OurBranchID		=	t_WFLoanBooking.OurBranchID
					AND t_SAT_AlertMsgLog.AccountID		=	t_WFLoanBooking.AccountID
					AND t_SAT_AlertMsgLog.LoanSeries	=	t_WFLoanBooking.LoanSeries
					AND t_SAT_AlertMsgLog.EventID		=	'BOOKING'
			) 
			AND  EXISTS
			(
				SELECT 1
				FROM t_WFLoanApplication WITH(NOLOCK)
				WHERE	t_WFLoanApplication.OurBranchID			=	t_WFLoanBooking.OurBranchID				
					AND t_WFLoanApplication.ApplicationID		=	t_WFLoanBooking.ApplicationID
					AND	t_WFLoanApplication.WFAppStatusID IN('PEN','DIS')	
				
			)				   

		UPDATE #SAT_AlertMsgLog 
		SET #SAT_AlertMsgLog.AlertMessage   =  REPLACE  (t_SAT_AlertFormat.MessageFormat,'{AMT}', #SAT_AlertMsgLog.BookedAmount)
		FROM #SAT_AlertMsgLog
		LEFT JOIN  t_SAT_BranchLanguage
		ON t_SAT_BranchLanguage.OurBranchID	=	#SAT_AlertMsgLog.OurBranchID
		LEFT JOIN	t_SAT_AlertFormat
		ON t_SAT_AlertFormat.EventID		=	'BOOKING'
		AND t_SAT_AlertFormat.LanguageID	=	ISNULL(t_SAT_BranchLanguage.LanguageID,'en')
		WHERE #SAT_AlertMsgLog.EventID		=	'BOOKING'
	END	
		 
	-------------DISBURSEMENT---------------------------------------------------
	IF EXISTS(SELECT 1 FROM t_SAT_AlertFormat
			  WHERE EventID = 'DISBURSEMENT')
	BEGIN
		INSERT INTO #SAT_AlertMsgLog
		(
			OurBranchID			,                 
			AccountID			,               
			LoanSeries			,     
			CreatedOn			,         
			DisbursedAmount		,
			DisbursementDate	,			
			EventID				,
			TrxBatchID			,
			TrxDate												                               
		)
		SELECT 
			 t_Loan.OurBranchID,
			 t_Loan.AccountID,				
			 t_Loan.LoanSeries,
			 GETDATE()	CreatedOn,
			 t_Loan.DisbursedAmount,
			 t_Loan.FirstDisbursementDate,
			 'DISBURSEMENT',
			 t_loandisbdetail.TrxBatchID,
			 t_loandisbdetail.TrxDate
			FROM t_Loan	WITH(NOLOCK) --me
			INNER JOIN	t_loandisbdetail
			ON  t_Loan.OurBranchID					=	t_loandisbdetail.OurBranchID
				AND t_Loan.AccountID				=	t_loandisbdetail.AccountID
				AND t_Loan.LoanSeries				=	t_loandisbdetail.LoanSeries
			WHERE t_Loan.FirstDisbursementDate		=	 dbo.f_GetWorkingDate(t_Loan.OurBranchID)
				AND t_Loan.OurBranchID			NOT	IN ('2009','2015','2017','2021','2022','2030','2031','2041','2042','2035','2039','2018','2019','2020','2032','1007','1008','1010','1012','2016','9998','9999')
				AND t_Loan.LoanStatusID NOT IN ('X')        ----   X = cancelled 
				AND NOT EXISTS (
								SELECT 1
								FROM t_SAT_AlertMsgLog
								WHERE t_SAT_AlertMsgLog.OurBranchID			=	t_Loan.OurBranchID
									AND t_SAT_AlertMsgLog.AccountID			=	t_Loan.AccountID
									AND t_SAT_AlertMsgLog.LoanSeries		=	t_Loan.LoanSeries
									AND t_SAT_AlertMsgLog.EventID			=	'DISBURSEMENT'
									AND t_SAT_AlertMsgLog.TrxBatchID		=	t_loandisbdetail.TrxBatchID
									AND t_SAT_AlertMsgLog.TrxDate			=	t_loandisbdetail.TrxDate
								) 
						
		UPDATE #SAT_AlertMsgLog 
		SET #SAT_AlertMsgLog.AlertMessage   =	REPLACE (REPLACE  (t_SAT_AlertFormat.MessageFormat,'{AMT}',#SAT_AlertMsgLog.DisbursedAmount),'{DATE}',CONVERT(nVarchar(10),#SAT_AlertMsgLog.DisbursementDate,103))
		FROM #SAT_AlertMsgLog
		LEFT JOIN  t_SAT_BranchLanguage
		ON t_SAT_BranchLanguage.OurBranchID	=	#SAT_AlertMsgLog.OurBranchID
		LEFT JOIN	t_SAT_AlertFormat
		ON t_SAT_AlertFormat.EventID		=	'DISBURSEMENT'
		AND t_SAT_AlertFormat.LanguageID	=	ISNULL(t_SAT_BranchLanguage.LanguageID,'en')
		WHERE #SAT_AlertMsgLog.EventID		=	'DISBURSEMENT'	
	END
		----------------- Demand Due (previous)----------------------------------
	--IF EXISTS(SELECT 1 FROM t_SAT_AlertFormat
	--		  WHERE EventID = 'DEMAND')
	--BEGIN
	
	--	INSERT INTO #SAT_AlertMsgLog
	--	(
	--		OurBranchID      ,                
	--		AccountID        ,          
	--		LoanSeries       ,
	--		CreatedOn        ,  
	--		DueAmount		 ,
	--		DueDate			 ,
	--		EventID                                   
	--	)
	--	SELECT           
	--		t_LoanInstallment.OurBranchID	,
	--		t_LoanInstallment.AccountID		,
	--		t_LoanInstallment.Loanseries	,
	--		GETDATE()						,
	--		t_LoanInstallment.InstallmentAmount,
	--		t_LoanInstallment.InstallmentDueDate,
	--		'DEMAND'
	--	FROM  t_LoanInstallment WITH(NOLOCK)	
	--	INNER JOIN t_Loan WITH(NOLOCK) --me
	--	ON  t_Loan.AccountID	= t_LoanInstallment.AccountID
	--	AND t_LoanInstallment.OurBranchID NOT	IN ('2009','2015','2017','2021','2022','2030','2031','2041','2042','2035','2039','2018','2019','2020','2032','1007','1008','1010','1012','2016')
	--	AND t_Loan.OurBranchID	= t_LoanInstallment.OurBranchID
	--	AND t_Loan.LoanSeries	= t_LoanInstallment.LoanSeries 	
	--	WHERE t_LoanInstallment.InstallmentDueDate = dbo.f_GetWorkingDate(t_LoanInstallment.OurBranchID)
	--	AND t_Loan.LoanStatusID IN ('A','N')              --   A =Active ,N = NPA 
	--	AND t_LoanInstallment.PaidStatus IN (0,2)
	--	AND NOT EXISTS (
	--						SELECT 1
	--						FROM t_SAT_AlertMsgLog
	--						WHERE t_SAT_AlertMsgLog.OurBranchID			=	t_Loan.OurBranchID
	--							AND t_SAT_AlertMsgLog.AccountID			=	t_Loan.AccountID
	--							AND t_SAT_AlertMsgLog.LoanSeries		=	t_Loan.LoanSeries
	--							AND t_SAT_AlertMsgLog.EventID			=	'DEMAND'
	--					) 

	--	UPDATE #SAT_AlertMsgLog 
	--	SET #SAT_AlertMsgLog.AlertMessage   = REPLACE( REPLACE  (t_SAT_AlertFormat.MessageFormat,'{AMT}',#SAT_AlertMsgLog.DueAmount),'{DATE}',CONVERT(nVarchar(10),#SAT_AlertMsgLog.DueDate,103))
	--	FROM #SAT_AlertMsgLog
	--	LEFT JOIN  t_SAT_BranchLanguage
	--	ON t_SAT_BranchLanguage.OurBranchID	=	#SAT_AlertMsgLog.OurBranchID
	--	LEFT JOIN	t_SAT_AlertFormat       
	--	ON t_SAT_AlertFormat.EventID		=	'DEMAND'
	--	AND t_SAT_AlertFormat.LanguageID	=	ISNULL(t_SAT_BranchLanguage.LanguageID,'en')
	--	WHERE #SAT_AlertMsgLog.EventID		=	'DEMAND'
	--END
	
			----------------- Demand Due (new)----------------------------------
	IF EXISTS(SELECT 1 FROM t_SAT_AlertFormat
			  WHERE EventID = 'DEMAND')
	BEGIN
		DELETE FROM #LoanInstallment
		
		INSERT INTO #LoanInstallment
		(
			OurBranchID	,		
			AccountID	,		
			Loanseries	,	
			WorkingDate	
		)
		SELECT 	OurBranchID	,		
				AccountID	,		
				Loanseries	,
				dbo.f_GetWorkingDate(t_Loan.OurBranchID)
		FROM t_Loan WITH(NOLOCK)
		WHERE t_Loan.LoanStatusID IN ('A','N')              --   A =Active ,N = NPA 
		AND t_Loan.OurBranchID NOT IN ('2009','2015','2017','2021','2022','2030','2031','2041','2042','2035','2039','2018','2019','2020','2032','1007','1008','1010','1012','2016','9998','9999')
		AND NOT EXISTS (
						SELECT 1
						FROM t_SAT_AlertMsgLog WITH(NOLOCK)
						WHERE t_SAT_AlertMsgLog.OurBranchID			=	t_Loan.OurBranchID
							AND t_SAT_AlertMsgLog.AccountID			=	t_Loan.AccountID
							AND t_SAT_AlertMsgLog.LoanSeries		=	t_Loan.LoanSeries
							AND t_SAT_AlertMsgLog.TrxDate			=	dbo.f_GetWorkingDate(t_Loan.OurBranchID)
							AND t_SAT_AlertMsgLog.EventID			=	'DEMAND'
					)


		INSERT INTO #SAT_AlertMsgLog
		(
			OurBranchID      ,                
			AccountID        ,          
			LoanSeries       ,
			CreatedOn        ,  
			DueAmount		 ,
			TrxDate			 ,
			EventID                                   
		)
		SELECT           
			t_LoanInstallment.OurBranchID	,
			t_LoanInstallment.AccountID		,
			t_LoanInstallment.Loanseries	,
			GETDATE()						,
			SUM(t_LoanInstallment.InstallmentAmount),
			t_LoanInstallment.InstallmentDueDate,
			'DEMAND'
		FROM  t_LoanInstallment WITH(NOLOCK)	
		INNER JOIN #LoanInstallment WITH(NOLOCK) 
		ON #LoanInstallment.OurBranchID	= t_LoanInstallment.OurBranchID
		AND #LoanInstallment.AccountID	= t_LoanInstallment.AccountID
		AND #LoanInstallment.LoanSeries	= t_LoanInstallment.LoanSeries 	
		WHERE t_LoanInstallment.InstallmentDueDate = #LoanInstallment.WorkingDate
		AND t_LoanInstallment.PaidStatus IN (0,2)
		GROUP BY	t_LoanInstallment.OurBranchID,
					t_LoanInstallment.AccountID,					
					t_LoanInstallment.InstallmentDueDate,
					t_LoanInstallment.Loanseries
		/*SELECT           
			t_LoanInstallment.OurBranchID	,
			t_LoanInstallment.AccountID		,
			t_LoanInstallment.Loanseries	,
			GETDATE()						,
			SUM(t_LoanInstallment.InstallmentAmount),
			t_LoanInstallment.InstallmentDueDate,
			'DEMAND'
		FROM  t_LoanInstallment WITH(NOLOCK)	
		INNER JOIN t_Loan WITH(NOLOCK) 
		ON  t_Loan.AccountID	= t_LoanInstallment.AccountID
		AND t_LoanInstallment.OurBranchID NOT	IN ('2009','2015','2017','2021','2022','2030','2031','2041','2042','2035','2039','2018','2019','2020','2032','1007','1008','1010','1012','2016','9998','9999')
		AND t_Loan.OurBranchID	= t_LoanInstallment.OurBranchID
		AND t_Loan.LoanSeries	= t_LoanInstallment.LoanSeries 	
		WHERE t_LoanInstallment.InstallmentDueDate = dbo.f_GetWorkingDate(t_LoanInstallment.OurBranchID)
		AND t_Loan.LoanStatusID IN ('A','N')              --   A =Active ,N = NPA 
		AND t_LoanInstallment.PaidStatus IN (0,2)
		AND NOT EXISTS (
							SELECT 1
							FROM t_SAT_AlertMsgLog WITH(NOLOCK)
							WHERE t_SAT_AlertMsgLog.OurBranchID			=	t_Loan.OurBranchID
								AND t_SAT_AlertMsgLog.AccountID			=	t_Loan.AccountID
								AND t_SAT_AlertMsgLog.LoanSeries		=	t_Loan.LoanSeries
								AND t_SAT_AlertMsgLog.TrxDate			=	t_LoanInstallment.InstallmentDueDate
								AND t_SAT_AlertMsgLog.EventID			=	'DEMAND'
						)
		GROUP BY	t_LoanInstallment.OurBranchID,
					t_LoanInstallment.AccountID,					
					t_LoanInstallment.InstallmentDueDate,
					t_LoanInstallment.Loanseries 
		*/
		UPDATE #SAT_AlertMsgLog 
		SET #SAT_AlertMsgLog.AlertMessage   = REPLACE( REPLACE  (t_SAT_AlertFormat.MessageFormat,'{AMT}',#SAT_AlertMsgLog.DueAmount),'{DATE}',CONVERT(nVarchar(10),#SAT_AlertMsgLog.TrxDate,103))
		FROM #SAT_AlertMsgLog
		LEFT JOIN  t_SAT_BranchLanguage
		ON t_SAT_BranchLanguage.OurBranchID	=	#SAT_AlertMsgLog.OurBranchID
		LEFT JOIN	t_SAT_AlertFormat       
		ON t_SAT_AlertFormat.EventID		=	'DEMAND'
		AND t_SAT_AlertFormat.LanguageID	=	ISNULL(t_SAT_BranchLanguage.LanguageID,'en')
		WHERE #SAT_AlertMsgLog.EventID		=	'DEMAND'
	END
	
			---------------------PRECLOSURE REVERSAL----------------------------

	IF EXISTS(SELECT 1 FROM t_SAT_AlertFormat
			  WHERE EventID = 'PCREV')
			  
		BEGIN
		INSERT INTO #SAT_AlertMsgLog
		(
			OurBranchID ,                
			AccountID   ,       
			LoanSeries  ,             
			CreatedOn   ,             
			ReversedAmount ,
			EventID,
			TrxBatchID,
			TrxDate
		)
		SELECT     
			t_LoanPreClosureReversal.OurBranchID,
			t_LoanPreClosureReversal.AccountID,
			t_LoanPreClosureReversal.LoanSeries,
			GETDATE(),
			t_LoanPreClosureReversal.TotalReversedAmount,
			'PCREV',
			t_LoanPreClosureReversal.TrxBatchID,
			t_LoanPreClosureReversal.TrxDate
		FROM t_LoanPreClosureReversal WITH(NOLOCK) --me
		WHERE t_LoanPreClosureReversal.TrxDate	=	dbo.f_GetWorkingDate(t_LoanPreClosureReversal.OurBranchID)
		AND t_LoanPreClosureReversal.OurBranchID NOT IN ('2009','2015','2017','2021','2022','2030','2031','2041','2042','2035','2039','2018','2019','2020','2032','1007','1008','1010','1012','2016','9998','9999')
		AND NOT EXISTS(		
							SELECT 1
							FROM t_SAT_AlertMsgLog
							WHERE t_SAT_AlertMsgLog.OurBranchID			 =	t_LoanPreClosureReversal.OurBranchID
							 AND t_SAT_AlertMsgLog.AccountID			 =	t_LoanPreClosureReversal.AccountID
							 AND t_SAT_AlertMsgLog.LoanSeries			 =	t_LoanPreClosureReversal.LoanSeries
							 AND t_SAT_AlertMsgLog.TrxDate				 =  t_LoanPreClosureReversal.TrxDate	
					         AND t_SAT_AlertMsgLog.TrxBatchID			 =	t_LoanPreClosureReversal.TrxBatchID 
							 AND t_SAT_AlertMsgLog.EventID				 =	'PCREV'
					    )			    
					
			
		UPDATE #SAT_AlertMsgLog 
		SET #SAT_AlertMsgLog.AlertMessage   =	t_SAT_AlertFormat.MessageFormat
		FROM #SAT_AlertMsgLog
		LEFT JOIN  t_SAT_BranchLanguage
		ON t_SAT_BranchLanguage.OurBranchID	=	#SAT_AlertMsgLog.OurBranchID
		LEFT JOIN	t_SAT_AlertFormat
		ON t_SAT_AlertFormat.EventID		=	'PCREV'
		AND t_SAT_AlertFormat.LanguageID	=	ISNULL(t_SAT_BranchLanguage.LanguageID,'en')
		WHERE #SAT_AlertMsgLog.EventID		=	'PCREV'	
	END			
		
	
	---------------------DISBURSEMENT REVERSAL-------
	IF EXISTS(SELECT 1 FROM t_SAT_AlertFormat
				WHERE EventID = 'DISPREV')
	BEGIN
		INSERT INTO #SAT_AlertMsgLog
			(
				OurBranchID,
				AccountID,
				LoanSeries,
				CreatedOn,
				ReversalDate,
				EventID,
				TrxBatchID,
				TrxDate
			)
		SELECT	
			t_LoanReversal.OurBranchID,
			t_LoanReversal.AccountID,
			t_LoanReversal.LoanSeries,
			GETDATE(),
			t_LoanReversal.ReversalDate,
			'DISPREV',
			t_LoanReversal.TrxBatchID,
			t_LoanReversal.TrxDate
		FROM  t_LoanReversal WITH( NOLOCK)
		WHERE t_LoanReversal.TrxDate	=	dbo.f_GetWorkingDate(t_LoanReversal.OurBranchID) 
		AND	  t_LoanReversal.OurBranchID NOT IN ('2009','2015','2017','2021','2022','2030','2031','2041','2042','2035','2039','2018','2019','2020','2032','1007','1008','1010','1012','2016','9998','9999')
		AND	  t_LoanReversal.ReversalTypeID	IN ('D','L')--(D,L)
		AND	  NOT EXISTS(
							SELECT 1 
							FROM t_SAT_AlertMsgLog	
							WHERE t_SAT_AlertMsgLog.OurBranchID = t_LoanReversal.OurBranchID	
							AND	t_SAT_AlertMsgLog.AccountID		= t_LoanReversal.AccountID		
							AND	t_SAT_AlertMsgLog.LoanSeries	= t_LoanReversal.LoanSeries
							AND t_SAT_AlertMsgLog.TrxDate		= t_LoanReversal.TrxDate	
							AND t_SAT_AlertMsgLog.TrxBatchID	= t_LoanReversal.TrxBatchID		
							AND	t_SAT_AlertMsgLog.EventID		= 'DISPREV'		
						)		
				
	
		UPDATE #SAT_AlertMsgLog 
		SET #SAT_AlertMsgLog.AlertMessage   =	t_SAT_AlertFormat.MessageFormat
		FROM #SAT_AlertMsgLog
		LEFT JOIN  t_SAT_BranchLanguage
		ON t_SAT_BranchLanguage.OurBranchID	=	#SAT_AlertMsgLog.OurBranchID
		LEFT JOIN	t_SAT_AlertFormat
		ON t_SAT_AlertFormat.EventID		=	'DISPREV'
		AND t_SAT_AlertFormat.LanguageID	=	ISNULL(t_SAT_BranchLanguage.LanguageID,'en')
		WHERE #SAT_AlertMsgLog.EventID		=	'DISPREV'			
	END	
	 -----------COLLECTION REVERSAL ----------------
   
	IF EXISTS(SELECT 1 FROM t_SAT_AlertFormat
			  WHERE EventID = 'COLRVE')
	BEGIN   	
		INSERT INTO #SAT_AlertMsgLog
		(
			OurBranchID ,                
			AccountID   ,
			LoanSeries  ,       
			CreatedOn   ,             
			CollectedAmount,
			CollectionDate,
			EventID,
			TrxBatchID,
			TrxDate 
		)
		SELECT                   
			t_Transaction.OurBranchID,
			t_Transaction.AccountID,
			t_Transaction.LoanSeries,
			GETDATE(),
			ABS(SUM(t_Transaction.Amount)),
			t_Transaction.TrxDate,
			'COLRVE',
			t_Transaction.TrxBatchID,
			t_Transaction.TrxDate
		FROM t_Transaction WITH(NOLOCK)
		INNER JOIN t_ProductLoan WITH(NOLOCK)
		ON t_Transaction.ProductID	= t_ProductLoan.ProductID
		AND t_ProductLoan.BankID	= @BankID
		WHERE DeletedOn IS NULL
			AND t_Transaction.OurBranchID NOT IN ('2009','2015','2017','2021','2022','2030','2031','2041','2042','2035','2039','2018','2019','2020','2032','1007','1008','1010','1012','2016','9998','9999')
			AND TrxFlagID = ''
			AND AccountTypeID = 'C'
			AND TrxAmount < 0  ----
			AND T_Transaction.TrxDate = dbo.f_GetWorkingDate(t_Transaction.OurBranchID)
			AND ModuleID IN (3116,3117,3096) 
			AND NOT EXISTS(	
								SELECT 1
								FROM t_SAT_AlertMsgLog
								WHERE t_SAT_AlertMsgLog.OurBranchID		=	t_Transaction.OurBranchID
									AND t_SAT_AlertMsgLog.AccountID		=	t_Transaction.AccountID
									AND t_SAT_AlertMsgLog.TrxDate		=	t_Transaction.TrxDate
									AND t_SAT_AlertMsgLog.TrxBatchID	=	t_Transaction.TrxBatchID
									AND t_SAT_AlertMsgLog.EventID		=	'COLRVE'
									
							) 
		GROUP BY t_Transaction.OurBranchID,
				 t_Transaction.AccountID,
				 t_Transaction.LoanSeries,			
				 t_Transaction.TrxDate,
				 t_Transaction.TrxBatchID					   	
	       
		UPDATE #SAT_AlertMsgLog 
		SET #SAT_AlertMsgLog.AlertMessage   =	t_SAT_AlertFormat.MessageFormat
		FROM #SAT_AlertMsgLog
		LEFT JOIN  t_SAT_BranchLanguage
		ON t_SAT_BranchLanguage.OurBranchID	=	#SAT_AlertMsgLog.OurBranchID
		LEFT JOIN	t_SAT_AlertFormat
		ON t_SAT_AlertFormat.EventID		=	'COLRVE'
		AND t_SAT_AlertFormat.LanguageID	=	ISNULL(t_SAT_BranchLanguage.LanguageID,'en')
		WHERE #SAT_AlertMsgLog.EventID		=	'COLRVE'			
    END
    
       -----------COLLECTION DELETION ----------------
   
   IF EXISTS(SELECT 1 FROM t_SAT_AlertFormat
			  WHERE EventID = 'COLDEL')
	BEGIN   	
		INSERT INTO #SAT_AlertMsgLog
		(
			OurBranchID ,                
			AccountID   ,
			LoanSeries  ,       
			CreatedOn   ,             
			CollectedAmount,                    
			EventID,
			TrxBatchID,
			TrxDate	 
		)
		SELECT    DISTINCT               
			t_Transaction.OurBranchID,
			t_Transaction.AccountID,
			t_Transaction.LoanSeries,
			GETDATE(),
			SUM(t_Transaction.Amount),             
			'COLDEL',
			t_Transaction.TrxBatchID,
			t_Transaction.TrxDate
		FROM t_Transaction WITH(NOLOCK)
		INNER JOIN t_ProductLoan WITH(NOLOCK)
		ON t_Transaction.ProductID	= t_ProductLoan.ProductID
		AND t_ProductLoan.BankID	= @BankID
		WHERE DeletedOn IS NOT NULL
			AND t_Transaction.OurBranchID NOT IN ('2009','2015','2017','2021','2022','2030','2031','2041','2042','2035','2039','2018','2019','2020','2032','1007','1008','1010','1012','2016','9998','9999')
			AND TrxFlagID = ''
			AND AccountTypeID = 'C'
			AND TrxAmount > 0  ----
			AND T_Transaction.TrxDate = dbo.f_GetWorkingDate(t_Transaction.OurBranchID)
			AND ModuleID NOT IN (3096,3116,3117,4548,4555,4556,5092,4545,4547) 
			AND NOT EXISTS(	
								SELECT 1
								FROM t_SAT_AlertMsgLog
								WHERE t_SAT_AlertMsgLog.OurBranchID		=	t_Transaction.OurBranchID
									AND t_SAT_AlertMsgLog.AccountID		=	t_Transaction.AccountID
									AND t_SAT_AlertMsgLog.TrxDate		=	t_Transaction.TrxDate
									AND t_SAT_AlertMsgLog.TrxBatchID	=	t_Transaction.TrxBatchID
									AND t_SAT_AlertMsgLog.EventID		=	'COLDEL'
									
							) 
		GROUP BY t_Transaction.OurBranchID,
				 t_Transaction.AccountID,
				 t_Transaction.LoanSeries,			
				 t_Transaction.TrxDate,
				 t_Transaction.TrxBatchID					   	
	       
		UPDATE #SAT_AlertMsgLog 
		SET #SAT_AlertMsgLog.AlertMessage	=  REPLACE( REPLACE (REPLACE  (t_SAT_AlertFormat.MessageFormat,'{AMT}',#SAT_AlertMsgLog.CollectedAmount),'{A}', #SAT_AlertMsgLog.AccountID),'{DATE}',CONVERT(nvarchar(10),#SAT_AlertMsgLog.TrxDate,103))
		FROM #SAT_AlertMsgLog
		LEFT JOIN  t_SAT_BranchLanguage
		ON t_SAT_BranchLanguage.OurBranchID	=	 #SAT_AlertMsgLog.OurBranchID
		LEFT JOIN	t_SAT_AlertFormat
		ON t_SAT_AlertFormat.EventID		=	'COLDEL'
		AND t_SAT_AlertFormat.LanguageID	=	 ISNULL(t_SAT_BranchLanguage.LanguageID,'en')
		WHERE #SAT_AlertMsgLog.EventID		=	'COLDEL'
    END
    
    ----------------------COMMON -------------------
    UPDATE #SAT_AlertMsgLog
	SET #SAT_AlertMsgLog.ClientID = t_AccountCustomer.ClientID
	FROM t_AccountCustomer WITH(NOLOCK)
	WHERE t_AccountCustomer.OurBranchID = #SAT_AlertMsgLog.OurBranchID
	AND t_AccountCustomer.AccountID		= #SAT_AlertMsgLog.AccountID
	
	UPDATE #SAT_AlertMsgLog
	SET #SAT_AlertMsgLog.AlertMessage	=	REPLACE(REPLACE(#SAT_AlertMsgLog.AlertMessage,'{NAME}',t_Client.Name),'{TITLE}',dbo.fn_GetUserCodeDesc('TitleID',t_UserCodeDetail.SubCodeID)),		
		#SAT_AlertMsgLog.MobileNo		=	t_Client.Mobile
	FROM t_Client
	INNER JOIN t_ClientIndividual
	ON t_ClientIndividual.ClientID		=	t_Client.ClientID
	LEFT  JOIN t_UserCodeDetail
	ON t_ClientIndividual.TitleID		=	t_UserCodeDetail.SubCodeID		
	WHERE t_Client.ClientID				=	#SAT_AlertMsgLog.ClientID
	--AND	t_Client.MobileAlertRequired	=	1
	
	BEGIN TRANSACTION
	INSERT INTO t_NotificationAlert
	(        
		NFTypeID,
		MobileNo,
		Content,
		Subject,
		CreatedOn
	)
	SELECT
		'S',
		MobileNo,
		AlertMessage,
		EventID,
		GETDATE()
	FROM #SAT_AlertMsgLog
	WHERE MobileNo IS NOT NULL 
	
					  
					  
	---
	INSERT INTO t_SAT_AlertMsgLog  
	(        
		OurBranchID					,
		AccountID                   ,
		LoanSeries                  ,
		AlertMessage                ,
		CreatedOn                   ,
		EventID                     ,
		ClientID					,
		MobileNo     ,
		TrxBatchID					,
		TrxDate
	 )
	SELECT
		OurBranchID					,
		AccountID					,
		LoanSeries                  ,
		AlertMessage                ,
		CreatedOn                   ,
		EventID                     ,
		ClientID					,
		MobileNo                    ,
		TrxBatchID					,
		TrxDate
	 FROM #SAT_AlertMsgLog 
	 WHERE MobileNo IS NOT NULL 
	 AND #SAT_AlertMsgLog.EventID IN ('COLLECTION','COLRVE','COLDEL')
	 AND NOT EXISTS ( 
						SELECT 1
						FROM t_SAT_AlertMsgLog WITH(NOLOCK)
						WHERE t_SAT_AlertMsgLog.OurBranchID			=			 #SAT_AlertMsgLog.OurBranchID
							AND t_SAT_AlertMsgLog.AccountID			=			 #SAT_AlertMsgLog.AccountID
							AND t_SAT_AlertMsgLog.EventID			=			 #SAT_AlertMsgLog.EventID   
							AND t_SAT_AlertMsgLog.TrxDate			=			 #SAT_AlertMsgLog.TrxDate
							AND t_SAT_AlertMsgLog.TrxBatchID		=			 #SAT_AlertMsgLog.TrxBatchID  
					  )
					  
	INSERT INTO t_SAT_AlertMsgLog  
	(        
		OurBranchID					,
		AccountID                   ,
		LoanSeries                  ,
		AlertMessage                ,
		CreatedOn                   ,
		EventID                     ,
		ClientID					,
		MobileNo                    ,
		TrxBatchID					,
		TrxDate
	 )
	SELECT
		OurBranchID					,
		AccountID					,
		LoanSeries                  ,
		AlertMessage                ,
		CreatedOn                   ,
		EventID                     ,
		ClientID					,
		MobileNo                    ,
		TrxBatchID					,
		TrxDate
	 FROM #SAT_AlertMsgLog 
	 WHERE MobileNo IS NOT NULL 
	 AND #SAT_AlertMsgLog.EventID IN ('SANCTION','BOOKING')
	 AND NOT EXISTS ( 
						SELECT 1
						FROM t_SAT_AlertMsgLog WITH(NOLOCK)
						WHERE t_SAT_AlertMsgLog.OurBranchID			=			 #SAT_AlertMsgLog.OurBranchID
							AND t_SAT_AlertMsgLog.AccountID			=			 #SAT_AlertMsgLog.AccountID
							AND t_SAT_AlertMsgLog.EventID			=			 #SAT_AlertMsgLog.EventID  
							AND t_SAT_AlertMsgLog.LoanSeries		=			 #SAT_AlertMsgLog.LoanSeries  
					)
					
	INSERT INTO t_SAT_AlertMsgLog  
	(        
		OurBranchID					,
		AccountID                   ,
		LoanSeries                  ,
		AlertMessage                ,
		CreatedOn                   ,
		EventID                     ,
		ClientID					,
		MobileNo                    ,
		TrxBatchID					,
		TrxDate
	 )
	SELECT
		OurBranchID					,
		AccountID					,
		LoanSeries                  ,
		AlertMessage                ,
		CreatedOn                   ,
		EventID                     ,
		ClientID					,
		MobileNo                    ,
		TrxBatchID					,
		TrxDate
	 FROM #SAT_AlertMsgLog 
	 WHERE MobileNo IS NOT NULL 
	 AND #SAT_AlertMsgLog.EventID IN ('DISBURSEMENT','PCREV','DISPREV','PRECLOSURE')
	 AND NOT EXISTS ( 
						SELECT 1
						FROM t_SAT_AlertMsgLog WITH(NOLOCK)
						WHERE t_SAT_AlertMsgLog.OurBranchID			=			 #SAT_AlertMsgLog.OurBranchID
							AND t_SAT_AlertMsgLog.AccountID			=			 #SAT_AlertMsgLog.AccountID
							AND t_SAT_AlertMsgLog.EventID			=			 #SAT_AlertMsgLog.EventID  
							AND t_SAT_AlertMsgLog.LoanSeries		=			 #SAT_AlertMsgLog.LoanSeries
							AND t_SAT_AlertMsgLog.TrxBatchID		=			 #SAT_AlertMsgLog.TrxBatchID  
							AND t_SAT_AlertMsgLog.TrxDate			=			 #SAT_AlertMsgLog.TrxDate    
					)
						
	INSERT INTO t_SAT_AlertMsgLog  
	(        
		OurBranchID					,
		AccountID                   ,
		LoanSeries                  ,
		AlertMessage                ,
		CreatedOn                   ,
		EventID                     ,
		ClientID					,
		MobileNo                    ,
		TrxBatchID					,
		TrxDate
	 )
	SELECT
		OurBranchID					,
		AccountID					,
		LoanSeries                  ,
		AlertMessage                ,
		CreatedOn                   ,
		EventID                     ,
		ClientID					,
		MobileNo                    ,
		TrxBatchID,
		TrxDate
	 FROM #SAT_AlertMsgLog 
	 WHERE MobileNo IS NOT NULL 
	 AND #SAT_AlertMsgLog.EventID ='DEMAND'
	 AND NOT EXISTS ( 
						SELECT 1
						FROM t_SAT_AlertMsgLog WITH(NOLOCK)
						WHERE t_SAT_AlertMsgLog.OurBranchID			=			 #SAT_AlertMsgLog.OurBranchID
							AND t_SAT_AlertMsgLog.AccountID			=			 #SAT_AlertMsgLog.AccountID
							AND t_SAT_AlertMsgLog.LoanSeries		=			 #SAT_AlertMsgLog.LoanSeries
							AND t_SAT_AlertMsgLog.EventID			=			 #SAT_AlertMsgLog.EventID   
							AND t_SAT_AlertMsgLog.TrxDate			=			 #SAT_AlertMsgLog.TrxDate  
					  )				
														 				  
	  
	DROP TABLE #SAT_AlertMsgLog  
	DROP TABLE #LoanInstallment
	
	UPDATE t_JobConfiguration
	SET LastRunEndTime	= GETDATE()
	WHERE JobID			= @JobID

	INSERT INTO t_JobLogDetail
	(
		JobID		,
		StartTime	,
		EndTime		,
		StatusID
	)
	SELECT	@JobID,
			@StartTime,
			@Endtime,
			'S'
			
	EndStatment:
	
	IF @@ERROR = 0 
	BEGIN
		IF @@TRANCOUNT > 0
		BEGIN
		
			COMMIT TRANSACTION
			
		END
	END
	
	END TRY
	
	BEGIN CATCH
	
	SET @ErrorMsg = ERROR_MESSAGE()
	RAISERROR(@ErrorMsg,16,1)
	
	IF @@ERROR > 0 
	BEGIN
		IF @@TRANCOUNT > 0
		BEGIN
		
			ROLLBACK TRANSACTION
			
		END			
	END
		
	END CATCH

	SET NOCOUNT OFF	  			
 END