IF OBJECT_ID('pj_Aris_SMSAlert', 'P') IS NOT NULL
	DROP PROCEDURE pj_Aris_SMSAlert
GO
CREATE  PROCEDURE pj_Aris_SMSAlert
AS
BEGIN 
	SET NOCOUNT ON
	
	DECLARE @StartTime		Datetime
	DECLARE @Endtime		Datetime
	DECLARE @ErrorTime		Datetime
	DECLARE	@ErrorMsg		nVarchar(max)
	DECLARE @WorkingDate	SmallDateTime
	DECLARE @OurBranchID	BranchID
	DECLARE @NoOfHoliday	SmallInt
	
	
	BEGIN TRY 
	
	BEGIN TRANSACTION
		   
    DECLARE @AlertMessage			nVarchar(Max)
	DECLARE @EventID				nVarchar(25)
	DECLARE @BankID					BankID
	
	SELECT @BankID			 =	 BankID
	FROM t_SystemBankSetting
	
	CREATE TABLE #BranchDetails
	(
		OurBranchID			nVarchar(15)
	)

	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)	,		
		CollectionDate              Smalldatetime	,
		AmountPaid					Money			,
		BookedAmount				Money			,
		DisbursedAmount				Money			,
		DisbursementDate			DateTime		,
		DueAmount					Money			,
		DueDate						SmallDateTime	,
		lonePreclosureDate   		SmallDateTime	,
		ReversedAmount				Money	,
		ReversalDate				smalldatetime	,
		ApplicationID				nvarchar(16)	,
		LoanAmount					Money			,
		SLNo						Int				,
		ChequeAmount				Money			,
		DateOfBirth					smalldatetime	,
		Year						Int				,
		CoApplicant					nVarchar(20)	,
		GuarantorMobileNo			nVarchar(20)	,
		CoAppMobileNo               nVarchar(30)	,
		Co_ClientID                 nVarchar(20)	,
		G_ClientID                  nVarchar(20)	,
		ODAmount					Money			,
		TrxDate						smalldatetime	,
		TrxBatchID					VARCHAR(8)		,
		ProcessDate					smalldatetime	
	)
	
	INSERT INTO #BranchDetails
	(OurBranchID)
	SELECT OurBranchID FROM t_SystemBranchSetting
	WHERE BranchStatusID = 'A'	
	
	DECLARE BranchCursor  CURSOR STATIC FOR
	
	SELECT	OurBranchID
	FROM #BranchDetails
	
	OPEN BranchCursor
	
	FETCH NEXT FROM BranchCursor INTO	@OurBranchID
	
	WHILE @@FETCH_STATUS = 0
	BEGIN
	
	SET @WorkingDate = dbo.f_GetWorkingDate(@OurBranchID)
	
	----------DEMAND2----------
	IF EXISTS(SELECT 1 FROM t_Aris_AlertFormat
			  WHERE EventID = 'DEMAND2')
	BEGIN
	
	
		INSERT INTO #SAT_AlertMsgLog
		(
			OurBranchID      ,                
			AccountID        ,          
			LoanSeries       ,
			CreatedOn        ,  
			DueAmount		 ,
			DueDate			 ,
			EventID                                   
		)
		SELECT           
			t_LoanInstallment.OurBranchID	,
			t_LoanInstallment.AccountID		,
			t_LoanInstallment.Loanseries	,
			GETDATE()						,
			SUM(t_LoanInstallment.InstallmentAmount),
			t_LoanInstallment.InstallmentDueDate,
			'DEMAND2'
		FROM  t_LoanInstallment WITH(NOLOCK)	
		INNER JOIN t_Loan WITH(NOLOCK) --me
		ON  t_Loan.AccountID	= t_LoanInstallment.AccountID
		AND t_Loan.OurBranchID	= t_LoanInstallment.OurBranchID
		AND t_Loan.LoanSeries	= t_LoanInstallment.LoanSeries
		WHERE t_Loan.OurBranchID			= @OurBranchID
		AND t_LoanInstallment.OurBranchID	= @OurBranchID 
		AND	t_LoanInstallment.InstallmentDueDate = DATEADD(DAY,2,@WorkingDate)
		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_Aris_AlertMsgLog
							WHERE t_Aris_AlertMsgLog.OurBranchID		=	t_Loan.OurBranchID
								AND t_Aris_AlertMsgLog.AccountID		=	t_Loan.AccountID
								AND t_Aris_AlertMsgLog.LoanSeries		=	t_Loan.LoanSeries
								AND t_Aris_AlertMsgLog.DueDate			=	t_LoanInstallment.InstallmentDueDate
								AND t_Aris_AlertMsgLog.EventID			=	'DEMAND2'
						)
		GROUP BY	t_LoanInstallment.OurBranchID,
					t_LoanInstallment.AccountID,					
					t_LoanInstallment.InstallmentDueDate,
					t_LoanInstallment.Loanseries 
							
		UPDATE #SAT_AlertMsgLog
		SET #SAT_AlertMsgLog.AlertMessage		=(SELECT REPLACE(REPLACE(t_Aris_AlertFormat.MessageFormat,'{AMT}',#SAT_AlertMsgLog.DueAmount),'{DATE}',CONVERT(VARCHAR(12),CAST( #SAT_AlertMsgLog.DueDate AS DATE),109))
													FROM	t_Aris_AlertFormat
													WHERE t_Aris_AlertFormat.EventID	=	'DEMAND2'
													)
		WHERE #SAT_AlertMsgLog.EventID		=	'DEMAND2'
	
	END	
	
	----------DEMAND5----------
	IF EXISTS(SELECT 1 FROM t_Aris_AlertFormat
			  WHERE EventID = 'DEMAND5')
	BEGIN
	
	
		INSERT INTO #SAT_AlertMsgLog
		(
			OurBranchID      ,                
			AccountID        ,          
			LoanSeries       ,
			CreatedOn        ,  
			DueAmount		 ,
			DueDate			 ,
			EventID                                   
		)
		SELECT           
			t_LoanInstallment.OurBranchID	,
			t_LoanInstallment.AccountID		,
			t_LoanInstallment.Loanseries	,
			GETDATE()						,
			SUM(t_LoanInstallment.InstallmentAmount),
			t_LoanInstallment.InstallmentDueDate,
			'DEMAND5'
		FROM  t_LoanInstallment WITH(NOLOCK)	
		INNER JOIN t_Loan WITH(NOLOCK) --me
		ON  t_Loan.AccountID	= t_LoanInstallment.AccountID
		AND t_Loan.OurBranchID	= t_LoanInstallment.OurBranchID
		AND t_Loan.LoanSeries	= t_LoanInstallment.LoanSeries
		WHERE t_Loan.OurBranchID			= @OurBranchID
		AND t_LoanInstallment.OurBranchID	= @OurBranchID 
		AND	t_LoanInstallment.InstallmentDueDate = DATEADD(DAY,5,@WorkingDate)
		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_Aris_AlertMsgLog
							WHERE t_Aris_AlertMsgLog.OurBranchID		=	t_Loan.OurBranchID
								AND t_Aris_AlertMsgLog.AccountID		=	t_Loan.AccountID
								AND t_Aris_AlertMsgLog.LoanSeries		=	t_Loan.LoanSeries
								AND t_Aris_AlertMsgLog.DueDate			=	t_LoanInstallment.InstallmentDueDate
								AND t_Aris_AlertMsgLog.EventID			=	'DEMAND5'
						)
		GROUP BY	t_LoanInstallment.OurBranchID,
					t_LoanInstallment.AccountID,					
					t_LoanInstallment.InstallmentDueDate,
					t_LoanInstallment.Loanseries 
							
		UPDATE #SAT_AlertMsgLog
		SET #SAT_AlertMsgLog.AlertMessage		=(SELECT REPLACE(REPLACE(t_Aris_AlertFormat.MessageFormat,'{AMT}',#SAT_AlertMsgLog.DueAmount),'{DATE}',CONVERT(VARCHAR(12),CAST(#SAT_AlertMsgLog.DueDate AS DATE),109))
													FROM	t_Aris_AlertFormat
													WHERE t_Aris_AlertFormat.EventID	=	'DEMAND5'
													)
		WHERE #SAT_AlertMsgLog.EventID		=	'DEMAND5'
	
	END
	
	---------BOUNCE------
	IF EXISTS(SELECT 1 FROM t_Aris_AlertFormat
			  WHERE EventID = 'BOUNCE')
	BEGIN
		INSERT INTO #SAT_AlertMsgLog
		(
			OurBranchID  ,                
			AccountID  ,
			LoanSeries,
			SLNo,               
			CreatedOn   ,             
			ChequeAmount,
			EventID
		)
		SELECT  
			t_LoanPDC.OurBranchID,
			t_LoanPDC.AccountID,
			t_LoanPDC.LoanSeries,
			t_LoanPDC.SLNo,
			GETDATE(),
			t_LoanPDC.ChequeAmount,
			'Bounce'
		FROM t_LoanPDC WITH(NOLOCK)
		WHERE t_LoanPDC.OurBranchID			=	@OurBranchID
		AND	t_LoanPDC.StatusDate			=	dbo.f_GetWorkingDate(t_LoanPDC.OurBranchID)
		AND	t_LoanPDC.PDCChequeStatusID		=	'RETN'
		AND t_LoanPDC.PDCTypeID				=	'PDC' 
		AND NOT EXISTS
			(	
				SELECT 1
				FROM t_Aris_AlertMsgLog WITH(NOLOCK)
				WHERE t_Aris_AlertMsgLog.OurBranchID		=	t_LoanPDC.OurBranchID
					AND t_Aris_AlertMsgLog.AccountID		=	t_LoanPDC.AccountID
					AND t_Aris_AlertMsgLog.LoanSeries		=	t_LoanPDC.LoanSeries
					AND t_Aris_AlertMsgLog.SLNo				=	t_LoanPDC.SLNo
					AND t_Aris_AlertMsgLog.EventID			=	'BOUNCE'
			) 	
		     
	
		
		UPDATE #SAT_AlertMsgLog
		SET #SAT_AlertMsgLog.AlertMessage		=(SELECT REPLACE(t_Aris_AlertFormat.MessageFormat,'{AMT}',#SAT_AlertMsgLog.ChequeAmount) 
													FROM	t_Aris_AlertFormat
													WHERE t_Aris_AlertFormat.EventID	=	'BOUNCE'
													)
		WHERE #SAT_AlertMsgLog.EventID		=	'BOUNCE'
   END	
   --------------APPREJ--------
   	IF EXISTS(SELECT 1 FROM t_Aris_AlertFormat
			  WHERE EventID = 'APPREJ')
	BEGIN
		INSERT INTO #SAT_AlertMsgLog
		(
			OurBranchID		,                
			ApplicationID	,
			ClientID		,               
			CreatedOn		,             
			EventID
		)
		SELECT  
			t_wfloanapplication.OurBranchID	,
			t_wfloanapplication.ApplicationID,
			t_wfloanapplication.ClientID	,
			GETDATE(),
			'APPREJ'
		FROM t_wfloanapplication WITH(NOLOCK)
		WHERE t_wfloanapplication.OurBranchID	=	@OurBranchID
		AND	t_wfloanapplication.RejectedDate		=	dbo.f_GetWorkingDate(t_wfloanapplication.OurBranchID)
		AND	t_wfloanapplication.WFAppStatusID		=	'REJ' 
		AND NOT EXISTS
		(	
			SELECT 1
			FROM t_Aris_AlertMsgLog WITH(NOLOCK)
			WHERE t_Aris_AlertMsgLog.OurBranchID		=	t_wfloanapplication.OurBranchID
				AND t_Aris_AlertMsgLog.ApplicationID	=	t_wfloanapplication.ApplicationID
				AND t_Aris_AlertMsgLog.EventID			=	'APPREJ'
		) 	
		
		UPDATE #SAT_AlertMsgLog
		SET #SAT_AlertMsgLog.AlertMessage		=(SELECT REPLACE (t_Aris_AlertFormat.MessageFormat,'{AI}', #SAT_AlertMsgLog.ApplicationID)
													FROM	t_Aris_AlertFormat
													WHERE t_Aris_AlertFormat.EventID	=	'APPREJ'
													)
		WHERE #SAT_AlertMsgLog.EventID		=	'APPREJ'
	END	
	
	
	 -------------------APPLICATION ------------- 
	IF EXISTS(SELECT 1 FROM t_Aris_AlertFormat
			  WHERE EventID = 'APPLICATION')
	BEGIN
		INSERT INTO #SAT_AlertMsgLog
		(
			OurBranchID		,                
			ApplicationID   , 
			ClientID		,              
			CreatedOn		,             
			LoanAmount		,
			EventID
		)
		SELECT  
			t_wfloanapplication.OurBranchID		,
			t_wfloanapplication.ApplicationID	,
			t_wfloanapplication.ClientID		,
			GETDATE()							,
			t_wfloanapplication.LoanAmount,
			'APPLICATION'
		FROM t_wfloanapplication WITH(NOLOCK)	
		WHERE t_wfloanapplication.OurBranchID	=	@OurBranchID
		AND	t_wfloanapplication.ApplicationDate = dbo.f_GetWorkingDate(t_wfloanapplication.OurBranchID)
		AND NOT EXISTS
		(	
			SELECT 1
			FROM t_Aris_AlertMsgLog WITH(NOLOCK)
			WHERE t_Aris_AlertMsgLog.OurBranchID			=	t_wfloanapplication.OurBranchID
				AND t_Aris_AlertMsgLog.ApplicationID		=	t_wfloanapplication.ApplicationID
				AND t_Aris_AlertMsgLog.EventID				=	'APPLICATION'
		) 	
		    
		
		UPDATE #SAT_AlertMsgLog
		SET #SAT_AlertMsgLog.AlertMessage		=(SELECT REPLACE (REPLACE(t_Aris_AlertFormat.MessageFormat,'{AMT}',#SAT_AlertMsgLog.LoanAmount),'{AI}', #SAT_AlertMsgLog.ApplicationID) 
													FROM	t_Aris_AlertFormat
													WHERE t_Aris_AlertFormat.EventID	=	'APPLICATION'
													)
		WHERE #SAT_AlertMsgLog.EventID		=	'APPLICATION'
	END
	
	
			 -------------------DOB(out side loop) ------------- 
	IF EXISTS(SELECT 1 FROM t_Aris_AlertFormat
			  WHERE EventID = 'DOB')
	BEGIN
				
		INSERT INTO #SAT_AlertMsgLog
		(	
			OurBranchID,
			ClientID,
			DateOfBirth,
			EventID,
			CreatedOn,
			TrxDate                         
		)
		SELECT 
		    t_client.OurBranchID,     
			t_ClientIndividual.ClientID,
			t_ClientIndividual.DateOfBirth,
			'DOB',
			GETDATE(),
			@WorkingDate
		FROM  t_ClientIndividual WITH(NOLOCK)
		INNER JOIN t_client
		ON 	t_client.ClientID	=	t_ClientIndividual.ClientID	
		WHERE t_client.OurBranchID	=	@OurBranchID
		AND	t_ClientIndividual.DateOfBirth	IS NOT NULL	
		AND DATEPART(D,t_ClientIndividual.DateOfBirth) = DATEPART(D,@WorkingDate)
		AND DATEPART(M,t_ClientIndividual.DateOfBirth) = DATEPART(M,@WorkingDate)
		AND t_client.ClientStatusID		=	'A'
		AND NOT EXISTS (
							SELECT 1
							FROM t_Aris_AlertMsgLog
							WHERE t_Aris_AlertMsgLog.ClientID					=	t_ClientIndividual.ClientID
								AND	DATEPART(YEAR,t_Aris_AlertMsgLog.TrxDate)	=	DATEPART(YEAR,@WorkingDate)
								AND t_Aris_AlertMsgLog.EventID	=	'DOB'		
						)
				
		
		UPDATE #SAT_AlertMsgLog
		SET #SAT_AlertMsgLog.AlertMessage		=(SELECT t_Aris_AlertFormat.MessageFormat 
													FROM	t_Aris_AlertFormat
													WHERE t_Aris_AlertFormat.EventID	=	'DOB'
													)
		WHERE #SAT_AlertMsgLog.EventID		=	'DOB'
		
	END	
		 -------------------DisbEvent------------- 
	
	IF EXISTS(SELECT 1 FROM t_Aris_AlertFormat
			  WHERE EventID = 'DISB')
	BEGIN
		INSERT INTO #SAT_AlertMsgLog
		(
			OurBranchID,
			AccountID,
			ApplicationID,
			DisbursedAmount,
			EventID,
			CreatedOn,
			TrxDate  
		)
		SELECT	
			t_Loan.OurBranchID,
			t_Loan.AccountID,
			t_Loan.ApplicationID,
			t_Loan.DisbursedAmount,
			'DISB',
			GETDATE(),
			@WorkingDate		
		FROM	t_Loan
		WHERE t_Loan.OurBranchID				 =	 @OurBranchID
			AND t_Loan.FirstDisbursementDate	 =	 dbo.f_GetWorkingDate(@OurBranchID)
			AND t_Loan.LoanStatusID = 'A'   
			AND NOT EXISTS
			(	
				SELECT 1
				FROM t_Aris_AlertMsgLog WITH(NOLOCK)
				WHERE t_Aris_AlertMsgLog.OurBranchID			=	t_Loan.OurBranchID
					AND t_Aris_AlertMsgLog.ApplicationID		=	t_Loan.ApplicationID
					AND t_Aris_AlertMsgLog.EventID				=	'DISB'
			) 	

			UPDATE #SAT_AlertMsgLog
			SET DueAmount	= Charge.Amount ,
				DueDate		=	Charge.DueDate		
			FROM #SAT_AlertMsgLog
			INNER JOIN 
			( SELECT t_ChargeDue.OurBranchID,
				     t_ChargeDue.ApplicationID,
					 SUM(CASE 
								WHEN t_ChargeClass.IsChargeIncludeTax = 1
								THEN ISNULL(t_ChargeDue.ChargeAmount, 0)
								ELSE ISNULL(t_ChargeDue.ChargeAmount, 0)+ ISNULL(t_ChargeDue.TaxAmount, 0) + ISNULL(t_ChargeDue.CessAmount, 0)    
								END)  Amount ,
				t_ChargeDue.DueDate
			FROM  t_ChargeDue
			INNER JOIN t_ChargeClass
				ON t_ChargeClass.BankID		= @BankID
				AND t_ChargeClass.ChargeID	= t_ChargeDue.ChargeID
				AND t_ChargeClass.ClassID	= t_ChargeDue.ClassID
				AND t_ChargeClass.CurrencyID = t_ChargeDue.CurrencyID
			WHERE	t_ChargeDue.ChargeDueStatusID			= 'C'
				AND t_ChargeDue.PaymentTypeID				= 'L'
				AND	t_ChargeDue.OurBranchID					= @OurBranchID
				AND t_ChargeDue.ReversedDate				IS NULL
			GROUP BY t_ChargeDue.OurBranchID,
				     t_ChargeDue.ApplicationID,
				     t_ChargeDue.DueDate
			) Charge
			ON Charge.OurBranchID	    = #SAT_AlertMsgLog.OurBranchID
			and Charge.ApplicationID	= #SAT_AlertMsgLog.ApplicationID
			WHERE #SAT_AlertMsgLog.EventID	= 'DISB'
			
			
							
			UPDATE #SAT_AlertMsgLog
			SET #SAT_AlertMsgLog.AlertMessage		=(  SELECT REPLACE(REPLACE(t_Aris_AlertFormat.MessageFormat,'{DAMT}',#SAT_AlertMsgLog.DisbursedAmount),'{CAMT}',#SAT_AlertMsgLog.DueAmount)
														FROM	t_Aris_AlertFormat
														WHERE t_Aris_AlertFormat.EventID	=	'DISB'
													)
			WHERE #SAT_AlertMsgLog.EventID		=	'DISB'
			AND #SAT_AlertMsgLog.OurBranchID	=	@OurBranchID
	
		END
	---------------------CHARGEBOOKING--------------------------------		
			
		IF EXISTS(	SELECT 1 FROM t_Aris_AlertFormat
					WHERE EventID = 'CHARGEBOOKING')
		BEGIN
			INSERT INTO #SAT_AlertMsgLog
			(
				OurBranchID,
				ClientID,
				DueAmount,
				EventID,
				CreatedOn,
				TrxDate
			)
			 SELECT	t_ChargeDue.OurBranchID,
					t_ChargeDue.ClientID,
					 SUM(CASE 
								WHEN t_ChargeClass.IsChargeIncludeTax = 1
								THEN ISNULL(t_ChargeDue.ChargeAmount, 0)
								ELSE ISNULL(t_ChargeDue.ChargeAmount, 0)+ ISNULL(t_ChargeDue.TaxAmount, 0) + ISNULL(t_ChargeDue.CessAmount, 0)    
								END)  Amount ,
					'CHARGEBOOKING',
					GETDATE(),			
					t_ChargeDue.ProcessDate
				FROM  t_ChargeDue WITH(NOLOCK)
				INNER JOIN t_ChargeClass
					ON t_ChargeClass.BankID			= @BankID
					AND t_ChargeClass.ChargeID		= t_ChargeDue.ChargeID
					AND t_ChargeClass.ClassID		= t_ChargeDue.ClassID
					AND t_ChargeClass.CurrencyID	= t_ChargeDue.CurrencyID
				WHERE t_ChargeDue.ProcessDate		= dbo.f_GetWorkingDate(@OurBranchID)
					AND	t_ChargeDue.ChargeID					= 'CHARGEDEPOSIT'	
				--	AND t_ChargeDue.ChargeDueStatusID			= 'C'
				--	AND t_ChargeDue.PaymentTypeID				= 'L'
					AND	t_ChargeDue.OurBranchID					= @OurBranchID
					AND t_ChargeDue.ReversedDate				IS NULL
					AND NOT EXISTS(	SELECT 1
									FROM t_Aris_AlertMsgLog WITH(NOLOCK)
									WHERE t_Aris_AlertMsgLog.OurBranchID	=	t_ChargeDue.OurBranchID	
									AND t_Aris_AlertMsgLog.ClientID			=	t_ChargeDue.ClientID
									AND t_Aris_AlertMsgLog.TrxDate			=   t_ChargeDue.ProcessDate
									AND t_Aris_AlertMsgLog.EventID			=	'CHARGEBOOKING')
				GROUP BY t_ChargeDue.OurBranchID,
						 t_ChargeDue.ClientID,
						 t_ChargeDue.ProcessDate
						 
						 
			UPDATE #SAT_AlertMsgLog
			SET #SAT_AlertMsgLog.AlertMessage		=(  SELECT REPLACE(t_Aris_AlertFormat.MessageFormat,'{AMT}',#SAT_AlertMsgLog.DueAmount)
														FROM	t_Aris_AlertFormat
														WHERE t_Aris_AlertFormat.EventID	=	'CHARGEBOOKING'
													)
			WHERE #SAT_AlertMsgLog.EventID		=	'CHARGEBOOKING'
			AND #SAT_AlertMsgLog.OurBranchID	=	@OurBranchID			 
				
				
		END				
			
		FETCH NEXT FROM BranchCursor INTO	@OurBranchID
		
	END
	
	CLOSE BranchCursor
	DEALLOCATE BranchCursor	
	

	
	    ----------------------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
	
		
		
	UPDATE #SAT_AlertMsgLog
	SET #SAT_AlertMsgLog.Co_ClientID = (SELECT TOP 1 t_ClientRole.ClientID FROM t_ClientRole
										WHERE #SAT_AlertMsgLog.ClientID	= t_ClientRole.MainClientID
										AND t_ClientRole.ClientRoleID = 'C')	
		
		
	UPDATE #SAT_AlertMsgLog
	SET #SAT_AlertMsgLog.G_ClientID = (SELECT TOP 1 t_ClientRole.ClientID FROM t_ClientRole
										WHERE #SAT_AlertMsgLog.ClientID	= t_ClientRole.MainClientID
										AND t_ClientRole.ClientRoleID = 'G')
	UPDATE #SAT_AlertMsgLog
	SET #SAT_AlertMsgLog.GuarantorMobileNo = (SELECT  t_Client.Mobile FROM t_Client
										WHERE #SAT_AlertMsgLog.G_ClientID	= t_Client.ClientID
										)	
	UPDATE #SAT_AlertMsgLog
	SET #SAT_AlertMsgLog.CoAppMobileNo = (SELECT  t_Client.Mobile FROM t_Client
										WHERE #SAT_AlertMsgLog.Co_ClientID	= t_Client.ClientID
										)							
	
	INSERT INTO t_NotificationAlert
	(        
		NFTypeID,
		MobileNo,
		Content,
		Subject,
		CreatedOn
	)
	SELECT
		'S',
		MobileNo,
		AlertMessage,
		EventID,
		GETDATE()
	FROM #SAT_AlertMsgLog
	WHERE MobileNo IS NOT NULL
	AND AlertMessage IS NOT NULL
	
	INSERT INTO t_Aris_AlertMsgLog  
	(        
		OurBranchID,
		AccountID,
		ApplicationID,
		LoanSeries,
		AlertMessage,
		CreatedOn,
		EventID,
		ClientID,
		MobileNo,
		LoanAmount,
		DueAmount,
		SLNo,
		DueDate,
		YEAR,
		TrxDate
	 )
	SELECT
		OurBranchID,
		AccountID,
		ApplicationID,
		LoanSeries,
		AlertMessage,
		CreatedOn,
		EventID,
		ClientID,
		MobileNo,
		LoanAmount,
		DueAmount,
		SLNo,
		DueDate,
		YEAR,
		TrxDate
	 FROM #SAT_AlertMsgLog 
	 WHERE MobileNo IS NOT NULL
	 AND AlertMessage IS NOT NULL

	INSERT INTO t_NotificationAlert
	(        
		NFTypeID,
		MobileNo,
		Content,
		Subject,
		CreatedOn
	)
	SELECT
		'S',
		GuarantorMobileNo,
		AlertMessage,
		EventID,
		GETDATE()
	FROM #SAT_AlertMsgLog
	WHERE GuarantorMobileNo IS NOT NULL 
	AND AlertMessage IS NOT NULL
	
	
	INSERT INTO t_Aris_AlertMsgLog  
	(      
		OurBranchID,
		AccountID,
		ApplicationID,
		LoanSeries,
		AlertMessage,
		CreatedOn,
		EventID,
		ClientID,
		MobileNo,
		LoanAmount,
		DueAmount,
		SLNo,
		DueDate,
		YEAR,
		TrxDate
	 )
	SELECT
		OurBranchID,
		AccountID,
		ApplicationID,
		LoanSeries,
		AlertMessage,
		CreatedOn,
		EventID,
		ClientID,
		GuarantorMobileNo,
		LoanAmount,
		DueAmount,
		SLNo,
		DueDate,
		YEAR,
		TrxDate
	 FROM #SAT_AlertMsgLog 
	 WHERE GuarantorMobileNo IS NOT NULL
	 AND AlertMessage IS NOT NULL
		

	INSERT INTO t_NotificationAlert
	(        
		NFTypeID,
		MobileNo,
		Content,
		Subject,
		CreatedOn
	)
	SELECT
		'S',
		CoAppMobileNo,
		AlertMessage,
		EventID,
		GETDATE()
	FROM #SAT_AlertMsgLog
	WHERE CoAppMobileNo IS NOT NULL 
	AND AlertMessage IS NOT NULL
	
	
	INSERT INTO t_Aris_AlertMsgLog  
	(        
		OurBranchID,
		AccountID,
		ApplicationID,
		LoanSeries,
		AlertMessage,
		CreatedOn,
		EventID,
		ClientID,
		MobileNo,
		LoanAmount,
		DueAmount,
		SLNo,
		DueDate,
		YEAR,
		TrxDate
	 )
	SELECT
		OurBranchID,
		AccountID,
		ApplicationID,
		LoanSeries,
		AlertMessage,
		CreatedOn,
		EventID,
		ClientID,
		CoAppMobileNo,
		LoanAmount,
		DueAmount,
		SLNo,
		DueDate,
		YEAR,
		TrxDate
	 FROM #SAT_AlertMsgLog 
	 WHERE CoAppMobileNo IS NOT NULL
	 AND AlertMessage IS NOT NULL
			
							 				  	  
	DROP TABLE #SAT_AlertMsgLog  
	
	DROP TABLE #BranchDetails
	

			
	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					
GO