IF OBJECT_ID('pj_ESFB_LOCSMS_Alert','P')IS NOT NULL
BEGIN 
	DROP PROCEDURE pj_ESFB_LOCSMS_Alert
END
GO 
CREATE PROCEDURE pj_ESFB_LOCSMS_Alert
(
	@JobID		nVarchar(50) = NULL
)
AS
BEGIN 
	SET NOCOUNT ON
	
	DECLARE @StartTime	Datetime
	DECLARE @Endtime	Datetime
	DECLARE @ErrorTime	Datetime
	DECLARE	@ErrorMsg	nVarchar(max)
	
	CREATE TABLE #BranchDate
	(
		OurBranchID		nVarchar(10) ,
		NthWorkingDate	SmallDateTime ,
		WorkingDate		SmallDateTime ,
		BranchName		nVarchar(100),
		CityName		nVarchar(300),
		LanguageID      nVarchar(3)
	)
	
	CREATE TABLE #ESB_AlertMsgLog
	(
		OurBranchID			nVarchar(6)	,
		ApplicationID		nVarchar(20),
		TrxDate				SmallDateTime,
		EventID				nVarchar(25),
		StageID				nVarchar(25),
		SMSKey				nVarchar(max),
		ClientID			nVarchar(20),
		MobileNo			nVarchar(30),
		LanguageID			nVarchar(3),
		ApplicationFileNo	nVarchar(20),
		MemberID			SmallInt,
		Name				nVarchar(300),
		AlertMessage		nVarchar(Max)	,
		Address				nVarchar(Max),
		BranchName			nVarchar(100),
		CreatedOn           Datetime,
		BankAccountNo		nVarchar(25)	,
		MeetingTime			SmallDateTime,
		LoanAmount			Numeric(18,2)
		
		
	)
	
		INSERT INTO #BranchDate
		SELECT t_SystemBranchStatus.OurbranchID,dbo.f_GetNextNthWorkingDate(t_SystemBranchStatus.OurBranchID,SODDATE,3),SODDATE,t_SystemBranchSetting.BranchName,dbo.fn_GetUserCodedesc('CITYID',t_SystemBranchSetting.CityID)
		,t_SystemBranchSetting.LanguageID
		FROM t_SystemBranchStatus
		INNER JOIN t_SystemBranchSetting
		ON t_SystemBranchStatus.OurBranchID	=	t_SystemBranchSetting.OurBranchID
		AND t_SystemBranchSetting.BranchStatusID='A'
	
		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
		
		BEGIN TRY 

		----LOAN SANCTION
		IF EXISTS(SELECT 1 FROM t_ESB_AlertFormat
				  WHERE EventID = 'LOANSANC')
		BEGIN
			
				INSERT INTO #ESB_AlertMsgLog
				(
					OurBranchID,
					ApplicationFileNo,
					MemberID,
					LoanAmount,
					MobileNo,
					TrxDate,
					SMSKey,
					EventID,
					LanguageID
				)
				SELECT GC.OurBranchID,
				       GC.ApplicationFileNo,
					   GC.MemberID,
					   LoanAmount,
					   GC.MobileNo,
					   GETDATE(),
					   GC.OurBranchID + GC.ApplicationFileNo + CAST(GC.MemberID AS nvarchar(10)),
					   'LOANSANC',
					   #BranchDate.LanguageID  
				FROM t_GLOSClient GC WITH(NOLOCK)
				INNER JOIN t_GLOSApplication GA  WITH(NOLOCK)
				ON GA.OurBranchID				= GC.OurBranchID
				AND GA.ApplicationFileNo		= GC.ApplicationFileNo
				AND GA.GLOSApplicationStatusID	= 'IPC'
				INNER JOIN #BranchDate
				ON #BranchDate.OurBranchID			  =	GC.OurBranchID
				WHERE ISNULL(GC.IsMobileVerified,0)	  = 1 
				AND GC.GLOSMemberStatusID			  = 'A'
				AND EXISTS ( SELECT 1
							 FROM t_GLOSActivityLog AL  WITH(NOLOCK)
							 WHERE AL.OurBranchID			= GC.OurBranchID
							 AND AL.ApplicationFileNo		= GC.ApplicationFileNo
							 AND AL.GLOSProcessActivityID	= 'CCNV'
							 AND ISNULL(AL.ActivityStatusID,'') = 'COMP'
						   )
				AND NOT EXISTS(SELECT 1 FROM t_ESB_AlertMsgLog  WITH(NOLOCK)
						WHERE t_ESB_AlertMsgLog.OurBranchID	=  GC.OurBranchID
						AND t_ESB_AlertMsgLog.SMSKey		=  GC.OurBranchID + GC.ApplicationFileNo + CAST(GC.MemberID AS nvarchar(10))
						AND t_ESB_AlertMsgLog.EventID		= 'LOANSANC'
					--	AND t_ESB_AlertMsgLog.LanguageID	=  #BranchDate.LanguageID
						)

				UPDATE #ESB_AlertMsgLog
				SET LoanAmount = t_GLOSClientLoan.LoanAmount
				FROM t_GLOSClientLoan
				WHERE #ESB_AlertMsgLog.OurBranchID		= t_GLOSClientLoan.OurBranchID
				AND  #ESB_AlertMsgLog.ApplicationFileNo = t_GLOSClientLoan.ApplicationFileNo
				AND #ESB_AlertMsgLog.MemberID			= t_GLOSClientLoan.MemberID
				AND t_GLOSClientLoan.RecordStatusID     = 'A'



				UPDATE 	#ESB_AlertMsgLog
				SET #ESB_AlertMsgLog.AlertMessage	=	REPLACE(REPLACE (REPLACE(t_ESB_AlertFormat.MessageFormat,'{application}',#ESB_AlertMsgLog.ApplicationFileNo),'{Amount}', ISNULL(CAST(#ESB_AlertMsgLog.LoanAmount AS NVARCHAR(20)),'0')),'{Date}',CONVERT(nvarchar(10),ISNULL(#ESB_AlertMsgLog.TrxDate,''),103))
				FROM #ESB_AlertMsgLog
				INNER JOIN t_ESB_AlertFormat 
				ON #ESB_AlertMsgLog.EventID			=  t_ESB_AlertFormat.EventID
				AND #ESB_AlertMsgLog.LanguageID		=  t_ESB_AlertFormat.LanguageID
				AND t_ESB_AlertFormat.EventID		= 'LOANSANC'


		END

		

		--branch Address

		IF EXISTS(SELECT 1 FROM t_ESB_AlertFormat
					WHERE EventID = 'BRANCHADD')
		BEGIN
		
			INSERT INTO #ESB_AlertMsgLog
			(
				OurBranchID		,
				TrxDate			,
				EventID			,
				ClientID		,
				SMSKey			,
				LanguageID      ,	
				Address			,
				MobileNo		,
				BranchName
			)
			SELECT t_SystemBranchSetting.OurBranchID,
					#BranchDate.WorkingDate,
					'BRANCHADD',
					t_Client.ClientID,
					ISNULL(t_SystemBranchSetting.OurBranchID,'')+ISNULL(t_Client.ClientID,'')+CONVERT (VARCHAR,ISNULL(#BranchDate.WorkingDate,''),112),
					#BranchDate.LanguageID,
					'Address1:'+ ISNULL(t_SystemBranchSetting.Address1,'')+' '+'Address2:'+ ISNULL(t_SystemBranchSetting.Address2,'')+' '+ISNULL(t_SystemBranchSetting.ZipCode,'')+' '+'City:'+ ISNULL(#BranchDate.CityName,'')+' '+'Mobile:'+
					 ISNULL(t_SystemBranchSetting.Mobile,'')+' '+'Fax:'+ ISNULL(t_SystemBranchSetting.Fax,'')+' '+'EMailID:'+ ISNULL(t_SystemBranchSetting.EMailID,''),
					t_Client.Mobile,
					t_SystemBranchSetting.BranchName
			FROM t_SystemBranchSetting
			INNER JOIN #BranchDate
			ON #BranchDate.OurBranchID		=	t_SystemBranchSetting.OurBranchID
			INNER JOIN t_Client
			ON t_Client.OurBranchID			= #BranchDate.OurBranchID	
			AND t_Client.ClientStatusID		= 'A'
			AND t_Client.IsMobileVerified	= 1
			AND t_Client.OurBranchID		= '11104'
			WHERE  EXISTS(SELECT 1 FROM t_ESB_BranchAddressHis
							WHERE t_SystemBranchSetting.OurBranchID			=	t_ESB_BranchAddressHis.OurBranchID
							AND (	ISNULL(t_SystemBranchSetting.BranchName,'')			<>	ISNULL(t_ESB_BranchAddressHis.BranchName,'')
									OR ISNULL(t_SystemBranchSetting.Address1,'')		<>	ISNULL(t_ESB_BranchAddressHis.Address1,'')
									OR ISNULL(t_SystemBranchSetting.Address2,'')		<>	ISNULL(t_ESB_BranchAddressHis.Address2,'')
									OR ISNULL(t_SystemBranchSetting.CityID,'')			<>	ISNULL(t_ESB_BranchAddressHis.CityID,'')
									OR ISNULL(t_SystemBranchSetting.CountryID,'')		<>	ISNULL(t_ESB_BranchAddressHis.CountryID,'')
									OR ISNULL(t_SystemBranchSetting.ZipCode,'')			<>	ISNULL(t_ESB_BranchAddressHis.ZipCode,'')
									OR ISNULL(t_SystemBranchSetting.Phone1,'')			<>	ISNULL(t_ESB_BranchAddressHis.Phone1,'')
									OR ISNULL(t_SystemBranchSetting.Phone2,'')			<>	ISNULL(t_ESB_BranchAddressHis.Phone2,'')
									OR ISNULL(t_SystemBranchSetting.Mobile,'')			<>	ISNULL(t_ESB_BranchAddressHis.Mobile,'')
									OR ISNULL(t_SystemBranchSetting.Fax,'')				<>	ISNULL(t_ESB_BranchAddressHis.Fax,'')
									OR ISNULL(t_SystemBranchSetting.EMailID,'')			<>	ISNULL(t_ESB_BranchAddressHis.EMailID,'')
									OR ISNULL(t_SystemBranchSetting.StateID,'')			<>	ISNULL(t_ESB_BranchAddressHis.StateID,'')	
											) 	)
				AND NOT EXISTS( SELECT 1 FROM t_ESB_AlertMsgLog
								WHERE t_ESB_AlertMsgLog.OurBranchID	= t_SystemBranchSetting.OurBranchID
								AND t_ESB_AlertMsgLog.SMSKey		= ISNULL(t_SystemBranchSetting.OurBranchID,'')+ISNULL(t_Client.ClientID,'')+CONVERT (VARCHAR,ISNULL(#BranchDate.WorkingDate,''),112)
								AND t_ESB_AlertMsgLog.EventID		= 'BRANCHADD'
								AND t_ESB_AlertMsgLog.LanguageID	=  dbo.f_GetUserLanguageID(t_SystemBranchSetting.CreatedBy))									
		
			UPDATE  t_ESB_BranchAddressHis
			SET t_ESB_BranchAddressHis.BranchName		=	t_SystemBranchSetting.BranchName	,	
				t_ESB_BranchAddressHis.Address1			=	t_SystemBranchSetting.Address1	,
				t_ESB_BranchAddressHis.Address2			=	t_SystemBranchSetting.Address2	,
				t_ESB_BranchAddressHis.CityID			=	t_SystemBranchSetting.CityID,		
				t_ESB_BranchAddressHis.CountryID		=	t_SystemBranchSetting.CountryID,
				t_ESB_BranchAddressHis.ZipCode			=	t_SystemBranchSetting.ZipCode,		
				t_ESB_BranchAddressHis.Phone1			=	t_SystemBranchSetting.Phone1,		
				t_ESB_BranchAddressHis.Phone2			=	t_SystemBranchSetting.Phone2,		
				t_ESB_BranchAddressHis.Mobile			=	t_SystemBranchSetting.Mobile,		
				t_ESB_BranchAddressHis.Fax				=	t_SystemBranchSetting.Fax,			
				t_ESB_BranchAddressHis.EMailID			=	t_SystemBranchSetting.EMailID,
				t_ESB_BranchAddressHis.StateID			=	t_SystemBranchSetting.StateID	
			FROM t_ESB_BranchAddressHis
			INNER JOIN t_SystemBranchSetting
			ON t_SystemBranchSetting.OurBranchID	=	t_ESB_BranchAddressHis.OurBranchID
			WHERE EXISTS(SELECT 1 FROM  #ESB_AlertMsgLog
							WHERE #ESB_AlertMsgLog.OurBranchID	=	t_ESB_BranchAddressHis.OurBranchID
							AND #ESB_AlertMsgLog.EventID		=	'BRANCHADD')
						
						
			IF NOT EXISTS(SELECT 1 FROM t_SystemBranchSetting
							WHERE NOT EXISTS(SELECT 1 FROM t_ESB_BranchAddressHis 
												WHERE t_SystemBranchSetting.OurBranchID	=	t_ESB_BranchAddressHis.OurBranchID ) )	
			BEGIN 
				INSERT INTO t_ESB_BranchAddressHis
				(
					OurBranchID,
					BranchName,
					Address1,
					Address2,
					CityID,
					CountryID,
					ZipCode,
					Phone1,
					Phone2,
					Mobile,
					Fax,
					EMailID,
					StateID
				)
				SELECT OurBranchID,
					BranchName,
					Address1,
					Address2,
					CityID,
					CountryID,
					ZipCode,
					Phone1,
					Phone2,
					Mobile,
					Fax,
					EMailID,
					StateID
				FROM  t_SystemBranchSetting
				WHERE NOT EXISTS(SELECT 1 FROM t_ESB_BranchAddressHis 
									WHERE t_SystemBranchSetting.OurBranchID	=	t_ESB_BranchAddressHis.OurBranchID )	

			
			
			END		

			UPDATE 	#ESB_AlertMsgLog
			SET #ESB_AlertMsgLog.AlertMessage	=	REPLACE(REPLACE(REPLACE(t_ESB_AlertFormat.MessageFormat,'{BranchName}',ISNULL(#ESB_AlertMsgLog.BranchName,'')),'{Address}',ISNULL(#ESB_AlertMsgLog.Address,'')),'{Date}',CONVERT(nvarchar(10),ISNULL(GETDATE(),''),103))



			FROM #ESB_AlertMsgLog
			INNER JOIN t_ESB_AlertFormat
			ON #ESB_AlertMsgLog.EventID			=	t_ESB_AlertFormat.EventID
			AND #ESB_AlertMsgLog.LanguageID		=	t_ESB_AlertFormat.LanguageID
			AND t_ESB_AlertFormat.EventID		=   'BRANCHADD'
	   END

	  ---Rejection
	  IF EXISTS(SELECT 1 FROM t_ESB_AlertFormat
				  WHERE EventID = 'REJECTION')
	   BEGIN
		
		INSERT INTO #ESB_AlertMsgLog
		(
			OurBranchID		
			,ApplicationFileNo	
			,MemberID		
			,TrxDate			
			,EventID		
			,SMSKey			
			,LanguageID      
			--,MobileNo
		)
		SELECT  GC.OurBranchID,
		        GC.ApplicationFileNo,
				GC.MemberID,
				GETDATE(),
				'REJECTION',
				GC.OurBranchID + GC.ApplicationFileNo + CAST(GC.MemberID AS NVARCHAR(10)),
				#BranchDate.LanguageID
				--,GC.MobileNo
		FROM t_GLOSClient GC
		INNER JOIN t_GLOSApplication GA  WITH(NOLOCK)
		ON GA.OurBranchID				= GC.OurBranchID
		AND GA.ApplicationFileNo		= GC.ApplicationFileNo
		INNER JOIN #BranchDate
		ON #BranchDate.OurBranchID	=	GC.OurBranchID
		WHERE ( GC.GLOSMemberStatusID = 'D' OR GA.GLOSApplicationStatusID = 'REJ')
		AND GA.GLOSProcessStageID   >'20APP'
		AND NOT EXISTS(SELECT 1 FROM t_ESB_AlertMsgLog  WITH(NOLOCK)
						WHERE t_ESB_AlertMsgLog.OurBranchID	=  GC.OurBranchID
						AND t_ESB_AlertMsgLog.SMSKey		=  GC.OurBranchID + GC.ApplicationFileNo + CAST(GC.MemberID AS nvarchar(10))
						AND t_ESB_AlertMsgLog.EventID		= 'REJECTION'
					--	AND t_ESB_AlertMsgLog.LanguageID	=  #BranchDate.LanguageID
						)

		UNION 

		SELECT WF.OurBranchID,
		       WF.ApplicationID,
			   0,
			   GETDATE(),
			   'REJECTION',
				WF.OurBranchID + WF.ApplicationID,
				#BranchDate.LanguageID
		FROM t_WFLoanApplication WF
		INNER JOIN #BranchDate
		ON #BranchDate.OurBranchID	= WF.OurBranchID
		WHERE WF.WFAppStatusID      = 'REJ'
		AND NOT EXISTS(SELECT 1 FROM t_ESB_AlertMsgLog  WITH(NOLOCK)
						WHERE t_ESB_AlertMsgLog.OurBranchID	=  WF.OurBranchID
						AND t_ESB_AlertMsgLog.SMSKey		=  WF.OurBranchID + WF.ApplicationID
						AND t_ESB_AlertMsgLog.EventID		= 'REJECTION'
					--	AND t_ESB_AlertMsgLog.LanguageID	=  #BranchDate.LanguageID
						)

		UPDATE 	#ESB_AlertMsgLog
		SET #ESB_AlertMsgLog.AlertMessage	=	REPLACE(REPLACE (REPLACE(t_ESB_AlertFormat.MessageFormat,'{application}',#ESB_AlertMsgLog.ApplicationFileNo),'{Amount}', ISNULL(CAST(#ESB_AlertMsgLog.LoanAmount AS NVARCHAR(20)),'0')),'{Date}',CONVERT(nvarchar(10),ISNULL(#ESB_AlertMsgLog.TrxDate,''),103))
		FROM #ESB_AlertMsgLog
		INNER JOIN t_ESB_AlertFormat 
		ON #ESB_AlertMsgLog.EventID			=  t_ESB_AlertFormat.EventID
		AND #ESB_AlertMsgLog.LanguageID		=  t_ESB_AlertFormat.LanguageID
		AND t_ESB_AlertFormat.EventID		= 'REJECTION'


	   END 
						
		
		--CUSTOMER ONBOARD
		IF EXISTS(SELECT 1 FROM t_ESB_AlertFormat
					WHERE EventID = 'CUSONBOARD')
		BEGIN
			INSERT INTO #ESB_AlertMsgLog
			(
				OurBranchID			,
				ApplicationFileNo	,
				MemberID			,
				TrxDate				,
				EventID				,
				SMSKey				,
				LanguageID      	,
				StageID				,
				Mobileno			,
				LoanAmount
			)
			SELECT t_GLOSActivityLog.OurBranchID,
					t_GLOSActivityLog.ApplicationFileNo,
					t_GLOSClient.MemberID,
					t_GLOSActivityLog.StatusOn,
					'CUSONBOARD',
					t_GLOSClient.OurBranchID+t_GLOSClient.ApplicationFileNo+CAST(t_GLOSClient.MemberID AS nVarchar(10))+t_GLOSActivityLog.GLOSProcessStageID+t_GLOSActivityLog.GLOSProcessActivityID+CONVERT(VARCHAR,t_GLOSActivityLog.StatusOn,112),
					#BranchDate.LanguageID,
					t_GLOSActivityLog.GLOSProcessStageID,
					t_GLOSClient.MobileNo,
					t_GLOSClientLoan.LoanAmount
			FROM t_GLOSActivityLog WITH(NOLOCK)
			INNER JOIN #BranchDate
			ON t_GLOSActivityLog.OurBranchID	=	#BranchDate.OurBranchID
			INNER JOIN t_GLOSClient WITH(NOLOCK)
			ON t_GLOSActivityLog.OurBranchID		=	t_GLOSClient.OurBranchID
			AND t_GLOSActivityLog.ApplicationFileNo	=	t_GLOSClient.ApplicationFileNo
			--INNER JOIN t_GLOSClientAddress WITH(NOLOCK)
			--ON t_GLOSClientAddress.OurBranchID			=	t_GLOSClient.OurBranchID
			--AND t_GLOSClientAddress.ApplicationFileNo	=	t_GLOSClient.ApplicationFileNo
			--AND t_GLOSClientAddress.MemberID			=	t_GLOSClient.MemberID
			--AND t_GLOSClientAddress.IsMailingAddress	=	1
			INNER JOIN t_GLOSClientLoan WITH(NOLOCK)
			ON t_GLOSClientLoan.OurBranchID			=	t_GLOSClient.OurBranchID
			AND t_GLOSClientLoan.ApplicationFileNo	=	t_GLOSClient.ApplicationFileNo
			AND t_GLOSClientLoan.MemberID			=	t_GLOSClient.MemberID
			AND t_GLOSClientLoan.RecordStatusID		=	'A'
			WHERE t_GLOSActivityLog.StatusDate	=	#BranchDate.WorkingDate
			AND t_GLOSActivityLog.GLOSProcessStageID	=	'20APP'
			AND	t_GLOSActivityLog.GLOSProcessActivityID	=	'DAEN'
			AND	t_GLOSActivityLog.ActivityStatusID		=	'COMP'
			AND t_GLOSClient.GLOSMemberStatusID			=	'A'
			AND NOT EXISTS (SELECT 1 FROM t_ESB_AlertMsgLog
							WHERE t_ESB_AlertMsgLog.OurBranchID		=	t_GLOSActivityLog.OurBranchID
							AND t_ESB_AlertMsgLog.SMSKey			=	t_GLOSClient.OurBranchID+t_GLOSClient.ApplicationFileNo+CAST(t_GLOSClient.MemberID AS nVarchar(10))+t_GLOSActivityLog.GLOSProcessStageID+t_GLOSActivityLog.GLOSProcessActivityID+CONVERT(VARCHAR,t_GLOSActivityLog.StatusOn,112)
							AND t_ESB_AlertMsgLog.EventID			=	'CUSONBOARD'
							--AND t_ESB_AlertMsgLog.LanguageID		=	#BranchDate.LanguageID
							)


			UPDATE 	#ESB_AlertMsgLog
			SET #ESB_AlertMsgLog.AlertMessage	= REPLACE (REPLACE(t_ESB_AlertFormat.MessageFormat,'{LoanAmount}',#ESB_AlertMsgLog.LoanAmount), '{Date}',CONVERT(nvarchar(10),ISNULL(#ESB_AlertMsgLog.TrxDate,''),103))
			FROM #ESB_AlertMsgLog
			INNER JOIN t_ESB_AlertFormat 
			ON #ESB_AlertMsgLog.EventID			=  t_ESB_AlertFormat.EventID
			AND #ESB_AlertMsgLog.LanguageID		=  t_ESB_AlertFormat.LanguageID
			AND t_ESB_AlertFormat.EventID		= 'CUSONBOARD'
		END
		
			
		BEGIN TRAN

			UPDATE #ESB_AlertMsgLog
			SET #ESB_AlertMsgLog.MobileNo				= t_GLOSClient.MobileNo
			FROM t_GLOSClient
			WHERE t_GLOSClient.OurBranchID				= #ESB_AlertMsgLog.OurBranchID
			AND  t_GLOSClient.ApplicationFileNo			= #ESB_AlertMsgLog.ApplicationFileNo
			AND  t_GLOSClient.MemberID					= #ESB_AlertMsgLog.MemberID
			--AND  t_GLOSClient.IsMailingAddress		= 1
			AND  t_GLOSClient.IsMobileVerified			= 1
			AND	 #ESB_AlertMsgLog.EventID				<>   'BRANCHADD'
		   
			UPDATE #ESB_AlertMsgLog
			SET #ESB_AlertMsgLog.AlertMessage			=	REPLACE(#ESB_AlertMsgLog.AlertMessage,'{NAME}',t_Client.Name),
				#ESB_AlertMsgLog.MobileNo				=	t_Client.Mobile
			FROM t_Client	
			WHERE t_Client.ClientID						=	#ESB_AlertMsgLog.ClientID
			AND t_Client.IsMobileVerified				=  1
			AND	 #ESB_AlertMsgLog.EventID				=   'BRANCHADD'


		   INSERT INTO t_NotificationAlert  
		   (          
			NFTypeID,  
			MobileNo,  
			Content,  
			Subject,  
			CreatedOn,  
			LanguageID,
			SMSStatusTimestamp  
		   )   
		   SELECT  
				'S',  
				MobileNo,  
				AlertMessage,  
				EventID,  
				GETDATE(),  
				ISNULL(#ESB_AlertMsgLog.LanguageID,'EN'),
				GETDATE()
			FROM #ESB_AlertMsgLog	
			WHERE MobileNo IS NOT NULL
			AND AlertMessage IS NOT NULL
			AND NOT EXISTS(SELECT 1 FROM t_ESB_AlertMsgLog 
						WHERE	t_ESB_AlertMsgLog.OurbranchID	=	#ESB_AlertMsgLog.OurbranchID	
							AND	t_ESB_AlertMsgLog.SMSKey		=	#ESB_AlertMsgLog.SMSKey 
							AND t_ESB_AlertMsgLog.EventID		=	#ESB_AlertMsgLog.EventID
							--AND t_ESB_AlertMsgLog.LanguageID	=	#ESB_AlertMsgLog.LanguageID
							)
							
							
		INSERT INTO t_ESB_AlertMsgLog	
		(
			OurbranchID,
			MobileNo,
			SMSDate,
			SMSKey,
			EventID,
			LanguageID
		)
		SELECT OurbranchID,
				MobileNo,
				TrxDate,
				SMSKey,
				EventID,
				LanguageID
		FROM #ESB_AlertMsgLog		
		WHERE MobileNo IS NOT NULL
		AND AlertMessage IS NOT NULL
		AND NOT EXISTS(SELECT 1 FROM t_ESB_AlertMsgLog 
						WHERE	t_ESB_AlertMsgLog.OurbranchID	=	#ESB_AlertMsgLog.OurbranchID	
							AND	t_ESB_AlertMsgLog.SMSKey		=	#ESB_AlertMsgLog.SMSKey 
							AND t_ESB_AlertMsgLog.EventID		=	#ESB_AlertMsgLog.EventID
							--AND t_ESB_AlertMsgLog.LanguageID	=	#ESB_AlertMsgLog.LanguageID
							)
			
		UPDATE t_JobConfiguration
		SET LastRunEndTime	= GETDATE()
		WHERE JobID			= @JobID

		INSERT INTO t_JobLogDetail
		(
			JobID		,
			StartTime	,
			EndTime		,
			StatusID
		)
		SELECT	@JobID,
				@StartTime,
				@Endtime,
				'S'
				
		
		
		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
	
	EndStatment:
		
		IF @@ERROR = 0 
		BEGIN
			IF @@TRANCOUNT > 0
			BEGIN
			
				COMMIT TRANSACTION
				
			END
		END
	
	
	IF @@ERROR > 0 
	BEGIN
		IF @@TRANCOUNT > 0
		BEGIN
		
			ROLLBACK TRANSACTION
			
		END			
	END
		
	
	DROP TABLE #ESB_AlertMsgLog
	DROP TABLE #BranchDate
	
	SET NOCOUNT OFF	  			
END
GO