GO 
INSERT INTO [sSystem].[tVersionUpdate] ([VersionNumber],[VersionId],[Status],[UpdateScript],[StartDateTime],[EndDateTime],[OrgGroupId])
VALUES('20.12.31',201231,'BEGIN','Script',sCommon.fGetDate(),sCommon.fGetDate(),0)
GO 

 
GO
PRINT N'Altering [sCertificate].[tLeaving]...';


GO
ALTER TABLE [sCertificate].[tLeaving]
    ADD [ProofOfDOB] NVARCHAR (200) NULL;


GO
PRINT N'Altering [sOp].[tGatewayProduct]...';


GO
ALTER TABLE [sOp].[tGatewayProduct]
    ADD [RazorpayMID]       NVARCHAR (50) NULL,
        [RazorpayKeyID]     NVARCHAR (50) NULL,
        [RazorpaySecretKey] NVARCHAR (50) NULL;


GO
PRINT N'Altering [sSecurity].[tOrgGroupMobileApp]...';


GO
ALTER TABLE [sSecurity].[tOrgGroupMobileApp]
    ADD [LoginById] SMALLINT NULL;
 

GO

 
GO
PRINT N'Altering [sAlert].[fResultGet]...';


GO
ALTER FUNCTION [sAlert].[fResultGet](@rResultId bigint,@rSubjectId int, @rTypeId tinyint)
RETURNS [nvarchar](MAX) WITH EXECUTE AS CALLER
AS 
BEGIN
DECLARE @lResult  VARCHAR(MAX)

IF(@rTypeId=1)
BEGIN
	(SELECT @lResult  = COALESCE(@lResult ,'') 
			+ CONVERT(VARCHAR(11),CS.SubjectCode,106) + ' - ' +
			(
			CASE  
			WHEN RD.IsPresent IS NULL THEN ''
			WHEN RD.IsPresent = 0 THEN 'Ab , '
			WHEN  E.IsMarksInDecimal = 1 then
			CONVERT(VARCHAR(11),RD.Obtain)
			WHEN E.IsMarksInDecimal = 0 then
			CONVERT(VARCHAR(11),CAST(RD.Obtain AS INT))
			END)
			    + ' , '
	FROM sAssessment.tResult R 
		INNER JOIN sAssessment.tResultDetail RD ON RD.ResultId=R.Id
		INNER JOIN sAssessment.tExamDetail ED ON ED.Id=RD.ExamDetailId
		INNER JOIN sAssessment.tExamMaster E ON R.ExamId = E.Id 
		INNER JOIN sSetup.vClassSubject CS ON CS.ClassSubjectId=ED.ClassSubjectId
		 
	WHERE
		R.Id=@rResultId
		AND ((CS.SubjectId=@rSubjectId) OR @rSubjectId=0)
		)
		
  SET @lResult = LEFT(@lResult, LEN(@lResult) - 1)
END
ELSE
BEGIN
	(SELECT @lResult  = COALESCE(@lResult ,'') 
			+ CONVERT(VARCHAR(11),CS.SubjectCode,106) + ' - ' +
			(
			CASE  
			WHEN RD.IsPresent IS NULL THEN ''
			WHEN RD.IsPresent = 0 THEN 'Ab'
			ELSE
			ISNULL(RD.SubjectGrade,'')
			END)
			    + ' , '
	FROM sAssessment.tResult R 
		INNER JOIN sAssessment.tResultDetail RD ON RD.ResultId=R.Id
		INNER JOIN sAssessment.tExamDetail ED ON ED.Id=RD.ExamDetailId
		INNER JOIN sAssessment.tExamMaster E ON R.ExamId = E.Id 
		INNER JOIN sSetup.vClassSubject CS ON CS.ClassSubjectId=ED.ClassSubjectId
		 
	WHERE
		R.Id=@rResultId
		AND ((CS.SubjectId=@rSubjectId) OR @rSubjectId=0)
		)
		
  SET @lResult = LEFT(@lResult, LEN(@lResult) - 1)
END
    RETURN @lResult 
END
GO
PRINT N'Altering [sAlert].[fResultWithMaxGet]...';


GO

ALTER FUNCTION [sAlert].[fResultWithMaxGet](@rResultId bigint,@rSubjectId int)
RETURNS [nvarchar](MAX) WITH EXECUTE AS CALLER
AS 
BEGIN
DECLARE @lResult  VARCHAR(MAX)
(SELECT @lResult  = COALESCE(@lResult ,'') 
			+ CONVERT(VARCHAR(11),CS.SubjectCode,106) + ' - ' +
			(
			CASE 
			WHEN RD.IsPresent IS NULL THEN ''
			WHEN RD.IsPresent = 0 THEN 'Ab , '
			WHEN E.IsMarksInDecimal = 1 THEN  CONVERT(VARCHAR(11),RD.Obtain)
			else CONVERT(VARCHAR(11),CAST(RD.Obtain AS INT))END)
			+(CASE 
			WHEN RD.IsPresent = 0 THEN ''
			WHEN RD.IsPresent != 0 THEN + ' / ' + CONVERT(VARCHAR(11),Cast(Round(ED.Maximum,0) AS int)) + ' , ' END)
	FROM sAssessment.tResult R 
		INNER JOIN sAssessment.tResultDetail RD ON RD.ResultId=R.Id
		INNER JOIN sAssessment.tExamDetail ED ON ED.Id=RD.ExamDetailId
		INNER JOIN sAssessment.tExamMaster E ON R.ExamId = E.Id 
		INNER JOIN sSetup.vClassSubject CS ON CS.ClassSubjectId=ED.ClassSubjectId
	WHERE
		R.Id=@rResultId
		AND ((CS.SubjectId=@rSubjectId) OR @rSubjectId=0)
		)
		
  SET @lResult = LEFT(@lResult, LEN(@lResult)- 1)
    RETURN @lResult 
END
GO
PRINT N'Creating [sAlert].[fPeriodWiseAttendanceGet]...';


GO
CREATE FUNCTION [sAlert].[fPeriodWiseAttendanceGet](@rAcademicId bigint,@rSubjectId nvarchar(max), @rAttendanceDate date,@OrgId int)
RETURNS [nvarchar](MAX) WITH EXECUTE AS CALLER
AS 
BEGIN

--DECLARE @lResult  VARCHAR(MAX)
--DECLARE @lSubjectName  VARCHAR(MAX)
--(SELECT @lResult  = COALESCE(@lResult ,'') + SM.Code
--			+ CONVERT(VARCHAR(11),SR.PeriodNo,106) + ' - ' +
--			(
--			CASE  
--			WHEN SR.IsPresent = 0 THEN 'Ab'
--			WHEN  SR.IsPresent = 1 then 'P'
--			END)
--			    + ' , '
--    from 
--	sStudent.vStudentCommunication SC
--	INNER JOIN sAttendance.tStudentRegister SR ON SR.AcademicId=SC.AcademicId
--	INNER JOIN sSetup.tSubjectMaster SM ON SR.SubjectId=SM.Id 	
--	WHERE (SC.OrgId=@OrgId) AND SR.AcademicId=@rAcademicId AND SubjectId in (SELECT cast(item as nvarchar(20)) FROM  sCommon.fnsplit(@rSubjectId,',')) AND AttendanceDate=@rAttendanceDate
--		)
		
--  --SELECT @lSubjectName=Code from [sSetup].[tSubjectMaster] Where Id=@rSubjectId
--  SET @lResult = LEFT(@lResult, LEN(@lResult) - 1)
DECLARE @lSubjectName  VARCHAR(MAX)
Declare @v Table 
(
Id int IDENTITY,
SubjectId int
)
INSERT INTO @v
SELECT cast(item as nvarchar(20)) FROM  sCommon.fnsplit(@rSubjectId,',')

--Select * from #tmp

DECLARE @lCountSubjectId int,@lId int,@lSubjectId bigint

SELECT @lCountSubjectId=COUNT(Id) FROM @v
SELECT Top 1 @lId=Id FROM @v
DECLARE @lfinalResult  VARCHAR(MAX)=''
DECLARE @lResult  VARCHAR(MAX)=''
WHILE @lId <= @lCountSubjectId

			BEGIN
		
				SELECT @lSubjectId=SubjectId FROM @v WHERE Id=@lId
			
				SELECT @lSubjectName=Code from [sSetup].[tSubjectMaster] Where Id=@lSubjectId
				SET @lResult= @lSubjectName +':'
				(SELECT @lResult  = COALESCE(@lResult ,'') + ' P '
							+ CONVERT(VARCHAR(11),SR.PeriodNo,106) + ' - ' +
							(
							CASE  
							WHEN SR.IsPresent = 0 THEN 'Ab'
							WHEN  SR.IsPresent = 1 then 'Pr'
							END)
								+ ' , '
					from 
					sStudent.vStudentCommunication SC
					INNER JOIN sAttendance.tStudentRegister SR ON SR.AcademicId=SC.AcademicId
					INNER JOIN [sSetup].[tSubjectMaster] SM ON SR.SubjectId=SM.Id 	
					WHERE (SC.OrgId=9913) AND SR.AcademicId=@rAcademicId AND SubjectId=@lSubjectId AND AttendanceDate=@rAttendanceDate
						)
		
				  
				  SET @lResult = LEFT(@lResult, LEN(@lResult) - 1)

				  --Select @lResult
				  SET @lfinalResult=@lfinalResult + @lResult
				  --Set  @lResult1= '       '+ @lSubjectName +':' + @lResult
				  --Select @lResult1
				SET @lId=@lId+1
			END

--DROP TABLE #tmp

  RETURN  @lfinalResult

END
GO
PRINT N'Creating [sFee].[fFeeAmountSumGet]...';


GO
CREATE FUNCTION [sFee].[fFeeAmountSumGet] (@rAcademicId bigint)
RETURNS decimal(12,0) WITH EXECUTE AS CALLER
AS 
BEGIN
    DECLARE @rTotalFeeAmount decimal(12,0),@lClassId int, @lDivisionId int, @lAdmissionCategoryId int, @rBatchId int, @rOrgId int, @rOrgGroupBatchId int
    SELECT @rOrgGroupBatchId=OrgGroupBatchId,@rOrgId=OrgId,@rBatchId=BatchId,@lClassId=ClassId,@lDivisionId=DivisionId,@lAdmissionCategoryId=AdmissionCategoryId FROM sStudent.vStudentAcademic WHERE AcademicId = @rAcademicId
    
    (
    SELECT 
		@rTotalFeeAmount = SUM(ISNULL(FS.Amount,0.00)+ISNULL(FA.VariableAmount,0.00)+ISNULL(FA.AdditionalAmount,0.00) -ISNULL(FA.DiscountAmount,0.00)-ISNULL(FA.NotApplicableAmount,0.00))
    FROM 
			 sStudent.vStudentAcademic SA
		INNER JOIN 
		(
			SELECT 
				FS1.FeeId,
				FS1.Id,
				ISNULL(FS1.IsOptional,0)AS IsOptional,
				ISNULL(FS1.IsVariable,0)AS IsVariable,
				ISNULL(FS1.Amount,0)AS Amount,
				ISNULL(FS1.StartDate,'1900-01-01')AS StartDate,
				ISNULL(FS1.EndDate,'1900-01-01')AS EndDate,
				FS1.BatchId,
				ISNULL(FS1.ReceiptBookId,0) AS ReceiptBookId,
				SA1.AcademicId,
				FM.Title AS FeeName			 
			FROM sFee.tFeeSetup FS1
			INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.ClassId=FS1.ClassId AND FS1.BatchId = SA1.BatchId 
			AND SA1.AdmissionCategoryId = FS1.AdmissionCategoryId
				AND ((SA1.ClassId=@lClassId) OR (@lClassId=0)) AND ((SA1.DivisionId=@lDivisionId) OR (@lDivisionId=0))
				AND ((SA1.AdmissionCategoryId=@lAdmissionCategoryId) OR (@lAdmissionCategoryId=0))
			INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS1.FeeId
			INNER JOIN sSetup.tBatchMaster BM ON FS1.BatchId = BM.Id AND BM.OrgGroupBatchId = @rOrgGroupBatchId
			WHERE 
			    SA1.OrgGroupBatchId = @rOrgGroupBatchId
				AND	((FS1.ClassId=@lClassId) OR (@lClassId=0))				
				AND	((SA1.OrgId=@rOrgId) OR (@rOrgId=0))
				AND ((FS1.AdmissionCategoryId=@lAdmissionCategoryId) OR (@lAdmissionCategoryId=0))
				AND (FS1.IsOptional=0)
				AND ((FS1.BatchId=@rBatchId) OR (@rBatchId=0))
				AND ((SA1.BatchId=@rBatchId) OR (@rBatchId=0))				
				AND SA1.AcademicId = @rAcademicId
				UNION ALL
				
			SELECT 
				FS1.FeeId,
				FS1.Id,
				ISNULL(FS1.IsOptional,0)AS IsOptional,
				ISNULL(FS1.IsVariable,0)AS IsVariable,
				ISNULL(FS1.Amount,0)AS Amount,
				ISNULL(FS1.StartDate,'1900-01-01')AS StartDate,
				ISNULL(FS1.EndDate,'1900-01-01')AS EndDate,
				FS1.BatchId,
				ISNULL(FS1.ReceiptBookId,0) AS ReceiptBookId,
				SA1.AcademicId,
				FM.Title AS FeeName 
				
			FROM sFee.tFeeSetup FS1
		INNER JOIN sFee.tFeeOptional FO
			ON FO.FeeSetupId=FS1.Id
	    INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS1.FeeId
		INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.AcademicId=FO.SourceId AND SA1.BatchId = FS1.BatchId AND SA1.AdmissionCategoryId = FS1.AdmissionCategoryId
			AND ((SA1.ClassId=@lClassId) OR (@lClassId=0)) AND ((SA1.DivisionId=@lDivisionId) OR (@lDivisionId=0))
			AND ((SA1.AdmissionCategoryId=@lAdmissionCategoryId) OR (@lAdmissionCategoryId=0))
		INNER JOIN sSetup.tBatchMaster BM ON FS1.BatchId = BM.Id AND BM.OrgGroupBatchId = @rOrgGroupBatchId
		WHERE
		        SA1.OrgGroupBatchId = @rOrgGroupBatchId
				AND ((FS1.ClassId=@lClassId) OR (@lClassId=0))
				AND	((SA1.OrgId=@rOrgId) OR (@rOrgId=0))
				AND ((FS1.AdmissionCategoryId=@lAdmissionCategoryId) OR (@lAdmissionCategoryId=0))
				AND (FS1.IsOptional=1)
				AND ((FS1.BatchId=@rBatchId) OR (@rBatchId=0))
				AND ((SA1.BatchId=@rBatchId) OR (@rBatchId=0))				
				AND SA1.AcademicId = @rAcademicId
		) FS ON FS.AcademicId=SA.AcademicId
		LEFT JOIN sFee.tFeeAllocation FA
			ON FA.FeeSetupId=FS.Id AND FA.SourceId=SA.AcademicId AND SA.AcademicId = @rAcademicId

		WHERE	
			SA.AcademicId = @rAcademicId
			AND SA.OrgGroupBatchId = @rOrgGroupBatchId
			AND ((SA.OrgId=@rOrgId) OR (@rOrgId=0)) 
			AND ((SA.BatchId=@rBatchId) OR (@rBatchId=0)) 
			)
			
	RETURN @rTotalFeeAmount
END
GO

 
GO
PRINT N'Altering [dbo].[pEmployeeMasterGet]...';


GO
 
ALTER PROCEDURE [dbo].[pEmployeeMasterGet]
@rOrgId int = 0,
 @rBuilder1 nvarchar(max)='',
 @rBuilder2 nvarchar(max)='',
 @rEmployeeStatusId int=0
AS

BEGIN
	IF EXISTS (SELECT EM.PunchId FROM sHrm.tEmployeeMaster EM INNER JOIN sBiometric.BioDetailMapping BM ON BM.EmpID = EM.Id WHERE EM.PunchId is null)
	BEGIN
	UPDATE EM
	SET
		PunchId = BM.EmpPunchID
	FROM
	    sHrm.tEmployeeMaster EM
		INNER JOIN sBiometric.BioDetailMapping AS BM
		ON BM.EmpID = EM.Id
		WHERE PunchId is null
	END
	
	select *,isnull(FirstName,'') +' '+ isnull(MiddleName,'') as 'FullName'
 from sHrm.tEmployeeMaster
	where 1=1
	and PunchId is not null
	and OrgID = @rOrgId
	AND (StatusId = @rEmployeeStatusId) OR (ISNULL(@rEmployeeStatusId,0)=0) 
	AND ISNULL(IsOtherUser,0) = 0
END
GO
PRINT N'Altering [sAcademic].[pMeetingAccountAdd]...';


GO
 
ALTER PROCEDURE [sAcademic].[pMeetingAccountAdd]
	 
        @rId bigint = 0,
		@rEmployeeId bigint=0,
		@rMeetingProviderId int,
		@rAccountName nvarchar(200)='',
		@rAccountEmail nvarchar(200)='',
		@rAccountPassword nvarchar(200)='',
		@rAPIKey nvarchar(100)='',
		@rAPISecret nvarchar(100)='',
		@rWebAPIKey nvarchar(100)='',
		@rWebAPISecret nvarchar(100)='',
		@rCreatedUserId bigint,
		@rOrgGroupId Smallint,
		@rJWTToken nvarchar(200)=''

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	BEGIN TRY
	 
	 BEGIN TRANSACTION	
	 DECLARE @lNextNumber BIGINT 
	IF EXISTS(SELECT 1 FROM sAcademic.tOnlineMeetingAccount WHERE AccountName = @rAccountName AND Id != @rId AND OrgGroupId = @rOrgGroupId)
		BEGIN
			SELECT -202 As Result
		END	
	ELSE
	BEGIN
	IF(@rId = 0)	
	BEGIN
		
	 
	EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sAcademic.tOnlineMeetingAccount', 0, 1, 1,@outNextNumber = @lNextNumber output
	Print @lNextNumber

	INSERT INTO sAcademic.tOnlineMeetingAccount
		(
			Id
           ,MeetingProviderId
           ,EmployeeId
           ,AccountName
           ,AccountEmailId
           ,AccountPassword
           ,APIKey
           ,APISecret
           ,WebAPIKey
           ,WebAPISecret
           ,CreatedUserId
           ,CreatedDateTime
		   ,OrgGroupId
		   ,JWTToken
		)
	VALUES
		(
			@lNextNumber,
			@rMeetingProviderId,
			@rEmployeeId,
			@rAccountName,
			@rAccountEmail,
			@rAccountPassword,
			@rAPIKey,
			@rAPISecret,
			@rWebAPIKey,
			@rWebAPISecret,
			@rCreatedUserId,
			sCommon.fGetDate(),
			@rOrgGroupId,
			@rJWTToken
		)
		    SELECT @lNextNumber AS Result
	END	 
	ELSE
	 BEGIN
	 SET @lNextNumber = @rId

	    UPDATE sAcademic.tOnlineMeetingAccount
	    SET
        	Id = @rId 
           ,EmployeeId = @rEmployeeId
           ,AccountName = @rAccountName
           ,AccountEmailId = @rAccountEmail
           ,AccountPassword = @rAccountPassword
           ,APIKey = @rAPIKey
           ,APISecret = @rAPISecret
		   ,WebAPIKey = @rWebAPIKey
           ,WebAPISecret = @rWebAPISecret
		   ,UpdatedUserId = @rCreatedUserId
		   ,UpdatedDateTime = sCommon.fGetDate()
		   ,OrgGroupId = @rOrgGroupId
		   ,JWTToken = @rJWTToken
	     WHERE Id = @rId
	      
		DECLARE @tOnlineLearningAccount TABLE  
		(  
		Id INT IDENTITY(1, 1),  
		OnlineLearningId BIGINT
		)   

		INSERT INTO @tOnlineLearningAccount
		SELECT Id
		FROM 
		 sAcademic.tOnlineLearning 
		WHERE MeetingAccountId = @rId AND IsCompleted = 0
		
		DECLARE @lCount int=1
		WHILE(@lCount <= (SELECT count(*) FROM @tOnlineLearningAccount))                
		BEGIN
			UPDATE sAcademic.tOnlineMeetingSettings 
			SET 
			AccountEmailId = OMA.AccountEmailId
		   ,AccountPassword = OMA.AccountPassword
		   ,APIKey = OMA.APIKey
		   ,APISecret = OMA.APISecret
		   ,WebAPIKey = OMA.WebAPIKey
		   ,WebAPISecret = OMA.WebAPISecret
		   ,MeetingProviderId = OMA.MeetingProviderId
		   ,JWTToken = OMA.JWTToken
		   FROM
			sAcademic.tOnlineMeetingAccount OMA
		   WHERE 
			OnlineLearningId = (SELECT OnlineLearningId FROM @tOnlineLearningAccount WHERE Id = @lCount)
			AND OMA.Id = @rId

			SET @lCount = @lCount + 1
		END
	      SELECT -100 AS Result
		--COMMIT TRANSACTION
	 END  	
	END
	COMMIT TRANSACTION		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END
GO
PRINT N'Altering [sAcademic].[pOnlineLearningAllStudentAttendanceAdd]...';


GO
 
ALTER PROCEDURE [sAcademic].[pOnlineLearningAllStudentAttendanceAdd]
	
	@rStrXml nvarchar(MAX),
	@rEmployeeId bigint,
    @rAttendanceTypeId tinyint,
    --@rSubjectId int=0,
    @rRepeatCount tinyint=0,
    @rAttendanceGroupId int=0,
    --@rPeriodNo int=0 ,
	@rAbsentReasonId int=0,
	@rAttendanceRemark nvarchar(10)='',
	@rAttendanceTagId int=0,
	@rCreatedUserId bigint
		
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
SET @rStrXml=REPLACE(@rStrXml,'&','AND')
DECLARE @loDoc INT
EXEC sp_xml_preparedocument @loDoc OUTPUT, @rStrXml
BEGIN
	
	IF EXISTS ( SELECT	
			1			
		FROM OPENXML (@loDoc, '/Table/RowData', 1)
		WITH ( 
				AcademicId bigint,
				AttendanceDate Date,
				IsPresent bit,
				PeriodNo int,
				SubjectId bigint
				
			  ) A 
		INNER JOIN sAttendance.tStudentRegister SR ON SR.AcademicId = A.AcademicId
		AND SR.AttendanceDate = A.AttendanceDate AND SR.SubjectId = A.SubjectId)
		BEGIN
          SELECT -101 AS Result
		END

		ELSE
		BEGIN
		
		INSERT INTO sAttendance.tStudentRegister 
		(
			AcademicId,
			EmployeeId,
			AttendanceTypeId,
			AttendanceDate,
			SubjectId,
			RepeatCount,
			IsPresent,
			AbsentReasonId,
			CreatedUserId,
			CreatedDateTime,
			UpdatedUserId,
			UpdatedDateTime,
			AttendanceTagId,
			PeriodNo,
			AttendanceGroupId,
			AttendanceRemark
			)
		SELECT	
		DISTINCT	
			A.AcademicId,
			@rEmployeeId,
			@rAttendanceTypeId,
		    A.AttendanceDate,
			A.SubjectId,
			ISNULL(@rRepeatCount,0),
			A.IsPresent,
			ISNULL(@rAbsentReasonId,0),
			@rCreatedUserId,
			sCommon.fGetDate(),
			'',
			'',
			@rAttendanceTagId,
			A.PeriodNo,
			@rAttendanceGroupId,
			@rAttendanceRemark
						
		FROM OPENXML (@loDoc, '/Table/RowData', 1)
		WITH ( 
				AcademicId bigint,
				AttendanceDate Date,
				IsPresent bit,
				PeriodNo int,
				SubjectId bigint
				
			  ) A 
		LEFT JOIN sAttendance.tStudentRegister SR ON SR.AcademicId = A.AcademicId
		AND SR.AttendanceDate = A.AttendanceDate AND SR.PeriodNo = A.PeriodNo
		AND SR.SubjectId = A.SubjectId
		WHERE SR.AcademicId IS NULL AND SR.AttendanceDate IS NULL AND SR.PeriodNo IS NULL
		AND SR.SubjectId IS NULL

		Select 1 AS Result
		END
END
GO
PRINT N'Altering [sAssessment].[pCCECSExamScoreAdd]...';


GO
 


ALTER PROCEDURE [sAssessment].[pCCECSExamScoreAdd]
	 
            @rAcademicId bigint
           ,@rExamId int
           ,@rIndicator nvarchar(200)
		   ,@rGrade nvarchar(10)
		   ,@rDescriptiveIndicator nvarchar(max)
		   ,@rCCECSId int
		   ,@rIndicatodIds nvarchar(50)
		   ,@rIsIndicatodIds bit
		
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY
	 BEGIN TRANSACTION	
IF NOT EXISTS(SELECT CCECSID FROM sAssessment.tCCECSExamScore WHERE CCECSId=@rCCECSId AND AcademicId=@rAcademicId AND ExamId = @rExamId)	
BEGIN
	INSERT INTO [sAssessment].[tCCECSExamScore]
			   (
			    [CCECSId]
			   ,[ExamId]
			   ,[AcademicId]
			   ,[GradeIndicator]
			   ,[Grade]
			   ,[DescriptiveIndicator]
			   ,[IndicatorIds]
			   )
		 VALUES
			   (
				@rCCECSId
			   ,@rExamId	
			   ,@rAcademicId
			   ,@rIndicator
			   ,@rGrade
			   ,@rDescriptiveIndicator
			   ,@rIndicatodIds
			   )
		   
	END	 
	 ELSE
	 BEGIN
	    UPDATE  [sAssessment].[tCCECSExamScore]
	    SET
			 
	          [AcademicId]=@rAcademicId
	         ,[ExamId] =@rExamId
	         ,GradeIndicator=@rIndicator
			 ,Grade =@rGrade
			 ,DescriptiveIndicator=@rDescriptiveIndicator
			 ,IndicatorIds = @rIndicatodIds
	      WHERE CCECSId=@rCCECSId AND AcademicId=@rAcademicId 
	      AND ExamId = 	   @rExamId
	     
	 END  
	 
	if @rIsIndicatodIds = 1 
	BEGIN 	

		-- Updated by Jash on 21/12/2020 for MantisId :- 0013797
		-- Issue :-  Indicator Title not according to Indicator Code order in CS Exam All Entry screen
		-- Commented Below code and added new code after it


		--Declare @lQuery Nvarchar(max)
		--SET  @lQuery =	'UPDATE A SET A.DescriptiveIndicator = 
 	--					stuff((select '','' + B.Title from sAssessment.tCCECSIndicatorMaster B
		--			WHERE A.CCECSid=B.CCECSId 
		--					and '',''+ (replace(IndicatorIds,''.'','','') )+'','' like ''%,''+CAST(Number as varchar(10))+'',%''				
		--			for xml path('''')),1,1,'''') 
		--	 From sAssessment.tCCECSExamScore A WHERE A.CCECSId='+ CAST(@rCCECSId AS NVARCHAR(50)) +' 
		--	 AND A.AcademicId= '+ CAST (@rAcademicId  AS NVARCHAR(50))+'
		--	  AND A.ExamId ='+ CAST(@rExamId AS NVARCHAR(50))        
        
		--EXECUTE (@lQuery)

		DECLARE @s varchar(20) = ''
		DECLARE @Indicator nvarchar(max) = ''
		SELECT @s = IndicatorIds FROM sAssessment.tCCECSExamScore WHERE AcademicId = @rAcademicId AND ExamId =@rExamId AND CCECSId=@rCCECSId

		while len(@s) > 0
		begin
		  DECLARE @TempIndicator nvarchar(max)
		  SELECT @TempIndicator = (select B.Title from sAssessment.tCCECSIndicatorMaster B  
			 WHERE A.CCECSid=B.CCECSId   
			 AND (Number = (LEFT(@s,charindex('.',@s+'.')-1))))        -- Getting value before first '.'   For ex ('20.21' -> '20')
			From sAssessment.tCCECSExamScore A WHERE A.CCECSId=@rCCECSId   
			AND A.AcademicId= @rAcademicId  
			 AND A.ExamId =@rExamId

			 SET @Indicator = @Indicator + @TempIndicator + ','
			 SET @s = stuff(@s, 1, charindex('.', @s+'.'), '')   -- Setting s to value after first '.' in the current string. For ex ('20.21' -> '21') 
  
		end
		SET @Indicator = left(@Indicator, len(@Indicator)-1)	-- removing last ',' from @Indicator
		UPDATE sAssessment.tCCECSExamScore SET DescriptiveIndicator = @Indicator WHERE AcademicId = @rAcademicId AND ExamId = @rExamId AND CCECSId = @rCCECSId
	END
	  SELECT 1 AS Result
COMMIT TRANSACTION		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END
GO
PRINT N'Altering [sAssessment].[pStudentwiseOptionalSubjectListGet]...';


GO

ALTER PROCEDURE [sAssessment].[pStudentwiseOptionalSubjectListGet]
                  
          @rDivisionId bigint,
          @rClassId bigint,
          @rSubjectId bigint ,
          @rBatchId int,
          @rBuilder1 nvarchar(max)='',
	      @rBuilder2 nvarchar(max)='',
		  @rClassStatusId int=0   ,
		  @rStatusId int=1    
      
AS
SET NOCOUNT ON

BEGIN

SELECT 
SS.AcademicId,
SS.ClassSubjectId,
SS.StudentName,
SS.SubjectId,
SM.Code as SubjectName,
SS.Code,
SS.RollNo,
@rBuilder1 AS ClassName,
@rBuilder2 AS DivisionName,

CASE WHEN isnull(OS.ClassSubjectId,0) <> 0 THEN 'true' ELSE 'false' END AS Selected
FROM
(SELECT
SA.AcademicId,
CS.Id as ClassSubjectId,
SA.Name as StudentName,
CS.SubjectId, 
SA.Code,
SA.RollNo
FROM
 (select * FROM sStudent.vStudentAcademic where DivisionId=@rDivisionId  AND BatchId=@rBatchId AND (ClassStatusId=@rClassStatusId OR @rClassStatusId=0) AND (StatusId=@rStatusId OR @rStatusId=0))SA  
 CROSS JOIN    (select * FROM sSetup.tClassSubject B   WHERE  B.IsOptional = 1 AND B.ClassId = @rClassId  AND  (B.Id= @rSubjectID OR @rSubjectId= 0) 
) AS CS 
 ) AS SS
 INNER JOIN sSetup.tSubjectMaster SM ON SS.SubjectId = SM.Id 
 LEFT JOIN sAssessment.tOptionalSubject OS ON OS.AcademicId = SS.AcademicId AND OS.ClassSubjectId = SS.ClassSubjectId

ORDER BY SS.RollNo,SM.OrderIndex
	   
END
GO
PRINT N'Altering [sAudit].[pActivityLogGet]...';


GO

ALTER PROCEDURE [sAudit].[pActivityLogGet]
	@rOrgGroupId int,
	@rOrgId int,
	@rUserName nvarchar(100),
	@rScreenName nvarchar(100),
	@rActivity nvarchar(500),
	@rFromDate date,
	@rToDate date,
	@rOSTypeId tinyint=0
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
SET @rScreenName = sCommon.fSearchStringGet(@rScreenName)
SET @rActivity = sCommon.fSearchStringGet(@rActivity)
SET @rToDate = DATEADD(day,1,@rToDate)
       SELECT 
            AL.OrgGroupId,
            AL.OrgId,
            OM.Title AS OrganizationName,
            AL.UserId,
			U.DisplayTitle AS UserName,
			U.LoginTitle,
			U.SessionId,
			S.Title AS ScreenName,
			AL.AuditDate,
			(CASE WHEN AL.ScreenId=126
				  THEN AL.Activity		
			      WHEN AL.ScreenId=20878
				  THEN AL.Activity		
				  WHEN AL.ScreenId=30179
				  THEN AL.Activity
				  WHEN AL.ScreenId=20196
				  THEN AL.Activity
				  WHEN AL.ScreenId=30788
				  THEN AL.Activity
				  WHEN AL.ScreenId=30780
				  THEN AL.Activity
			 ELSE SUBSTRING(AL.Activity, CHARINDEX(' ', AL.Activity)+1, LEN(AL.Activity)-(CHARINDEX(' ', AL.Activity)-1))  END)AS  Activity,			
			ISNULL(AL.RecordSourceId,0) AS RecordSourceId,
			ISNULL(AL.RecordDetail,'') AS RecordDetail,
			ISNULL(OS.Title,'') AS OSType
			
       FROM 
		   sAudit.tActivityLog AL
		   INNER JOIN sSecurity.tUser U ON U.Id = AL.UserId
		   INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = AL.OrgId
		   INNER JOIN sSecurity.tScreen S ON S.Id = AL.ScreenId
		   LEFT JOIN sAlert.tOSType OS ON OS.Id = AL.OSTypeId
       WHERE
		   AL.OrgGroupId = @rOrgGroupId
		   AND ((AL.OrgId = @rOrgId) OR (@rOrgId=0))
		   AND ((U.DisplayTitle = @rUserName) OR (@rUserName = ''))
		   AND ((S.Title LIKE @rScreenName) OR (@rScreenName = ''))
		   AND ((AL.Activity LIKE @rActivity) OR (@rActivity = ''))
		   AND (AL.AuditDate >= @rFromDate)
		   AND (AL.AuditDate <= @rToDate)
		   AND ((AL.OSTypeId = @rOSTypeId) OR (@rOSTypeId=0))
       ORDER BY 
			AL.AuditDate DESC
END
GO
PRINT N'Altering [sBiometric].[pTimeSheetListGet]...';


GO
 
 

ALTER PROCEDURE [sBiometric].[pTimeSheetListGet]
  @rEmployeeId bigint=0,  
  @rFromDate DateTime,  
  @rToDate DateTime,  
  @rStatus nvarchar(10) = '0',  
  @rOrgId int = 0  ,
  @rEmployeeName nvarchar(50) = '',
  @rStatusId int = 0,
  @rOrganizationDepartmentId int=0,
  @rOrgGroupDepartmentId int = 0
  
 WITH EXECUTE AS CALLER  
AS  
SET NOCOUNT ON  BEGIN   
  
 CREATE TABLE #TempMonthTrans  
 (  
 Id int Identity,  
 EmployeeId bigint,   
 Attn_Date DateTime,  
 PresentStatus nvarchar(15),  
 FirstIn nvarchar(20),  
 LastOut nvarchar(20),  
 OverTime nvarchar(20),  
 LateBy nvarchar(20),  
 Duration nvarchar(20),   
 EarlyBy nvarchar(20)  
 )  
    Declare @ldate datetime,@lDays int
		declare @Days int
		Declare @Firstdate datetime
		Declare @Lastdate datetime
		DECLARE @lForYear nvarchar(20),@lForMonth nvarchar(20)
		Set @lForYear = Year(@rFromDate)
		
		Set @lForMonth = Month(@rFromDate)
		
		Set @ldate= CONVERT(nvarchar(20), @lForYear)+ '-' +  CONVERT(nvarchar(20), @lForMonth) + '-' + CONVERT(nvarchar(20), 01)
		Select @lDays=day(dateadd(mm,DateDiff(mm, -1, @ldate),0) -1) 
		select @Days=sCommon.fGetDaysInMonth(@lForMonth,@lForYear)
		
		set @Firstdate=CONVERT(nvarchar(20), @lForYear)+ '-' +  CONVERT(nvarchar(20), @lForMonth) + '-' + CONVERT(nvarchar(20), 01)
		set @Lastdate=CONVERT(nvarchar(20), @lForYear)+ '-' +  CONVERT(nvarchar(20), @lForMonth) + '-' + CONVERT(nvarchar(20), @Days)
		
   
 INSERT INTO #TempMonthTrans  
   
 	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 01) as datetime),D01,D01_IN,D01_OUT,D01_O,D01_L,D01_T,D01_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0) 
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 02) as datetime),D02,D02_IN,D02_OUT,D02_O,D02_L,D02_T,D02_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 03) as datetime),D03,D03_IN,D03_OUT,D03_O,D03_L,D03_T,D03_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 04) as datetime),D04,D04_IN,D04_OUT,D04_O,D04_L,D04_T,D04_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 05) as datetime),D05,D05_IN,D05_OUT,D05_O,D05_L,D05_T,D05_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 06) as datetime),D06,D06_IN,D06_OUT,D06_O,D06_L,D06_T,D06_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 07) as datetime),D07,D07_IN,D07_OUT,D07_O,D07_L,D07_T,D07_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 08) as datetime),D08,D08_IN,D08_OUT,D08_O,D08_L,D08_T,D08_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 09) as datetime),D09,D09_IN,D09_OUT,D09_O,D09_L,D09_T,D09_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 10) as datetime),D10,D10_IN,D10_OUT,D10_O,D10_L,D10_T,D10_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 11) as datetime),D11,D11_IN,D11_OUT,D11_O,D11_L,D11_T,D11_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 12) as datetime),D12,D12_IN,D12_OUT,D12_O,D12_L,D12_T,D12_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 13) as datetime),D13,D13_IN,D13_OUT,D13_O,D13_L,D13_T,D13_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 14) as datetime),D14,D14_IN,D14_OUT,D14_O,D14_L,D14_T,D14_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 15) as datetime),D15,D15_IN,D15_OUT,D15_O,D15_L,D15_T,D15_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 16) as datetime),D16,D16_IN,D16_OUT,D16_O,D16_L,D16_T,D16_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 17) as datetime),D17,D17_IN,D17_OUT,D17_O,D17_L,D17_T,D17_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 18) as datetime),D18,D18_IN,D18_OUT,D18_O,D18_L,D18_T,D18_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 19) as datetime),D19,D19_IN,D19_OUT,D19_O,D19_L,D19_T,D19_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 20) as datetime),D20,D20_IN,D20_OUT,D20_O,D20_L,D20_T,D20_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 21) as datetime),D21,D21_IN,D21_OUT,D21_O,D21_L,D21_T,D21_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 22) as datetime),D22,D22_IN,D22_OUT,D22_O,D22_L,D22_T,D22_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 23) as datetime),D23,D23_IN,D23_OUT,D23_O,D23_L,D23_T,D23_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 24) as datetime),D24,D24_IN,D24_OUT,D24_O,D24_L,D24_T,D24_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 25) as datetime),D25,D25_IN,D25_OUT,D25_O,D25_L,D25_T,D25_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 26) as datetime),D26,D26_IN,D26_OUT,D26_O,D26_L,D26_T,D26_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 27) as datetime),D27,D27_IN,D27_OUT,D27_O,D27_L,D27_T,D27_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	UNION ALL

	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 28) as datetime),D28,D28_IN,D28_OUT,D28_O,D28_L,D28_T,D28_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)

	IF  Month(@rFromDate) <> 2 or (((YEAR(@rFromDate) % 4 = 0 AND YEAR(@rFromDate) % 100 <> 0) OR YEAR(@rFromDate) % 400 = 0 And Month(@rFromDate)  =2)) 
	BEGIN
		INSERT INTO #TempMonthTrans
		SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 29) as datetime),D29,D29_IN,D29_OUT,D29_O,D29_L,D29_T,D29_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
		AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)
	END
	
	IF Month(@rFromDate) <> 2
	BEGIN
	INSERT INTO #TempMonthTrans
	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 30) as datetime),D30,D30_IN,D30_OUT,D30_O,D30_L,D30_T,D30_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)
	END
	
	IF Month(@rFromDate) NOT IN (2,4,6,9,11)
	BEGIN
	INSERT INTO #TempMonthTrans
	SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 31) as datetime),D31,D31_IN,D31_OUT,D31_O,D31_L,D31_T,D31_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)
	AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)
	END
	
	


 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 01) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),1,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),1,0,0,@rOrgId) else D01 end as D01,  
 --D01_IN,D01_OUT,D01_O,D01_L,D01_T,D01_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 02) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),2,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),2,0,0,@rOrgId) else D02 end as D02,  
 --D02_IN,D02_OUT,D02_O,D02_L,D02_T,D02_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 03) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),3,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),3,0,0,@rOrgId) else D03 end as D03,  
 --D03_IN,D03_OUT,D03_O,D03_L,D03_T,D03_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 04) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),4,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),4,0,0,@rOrgId) else D04 end as D04,  
 --D04_IN,D04_OUT,D04_O,D04_L,D04_T,D04_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 05) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),5,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),5,0,0,@rOrgId) else D05 end as D05,  
 --D05_IN,D05_OUT,D05_O,D05_L,D05_T,D05_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 06) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),6,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),6,0,0,@rOrgId) else D06 end as D06,  
 --D06_IN,D06_OUT,D06_O,D06_L,D06_T,D06_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 07) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),7,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),7,0,0,@rOrgId) else D07 end as D07,  
 --D07_IN,D07_OUT,D07_O,D07_L,D07_T,D07_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 08) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),8,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),8,0,0,@rOrgId) else D08 end as D08,  
 --D08_IN,D08_OUT,D08_O,D08_L,D08_T,D08_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 09) as datetime), case When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),9,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),9,0,0,@rOrgId) else D09 end as D09,  
 --D09_IN,D09_OUT,D09_O,D09_L,D09_T,D09_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 10) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),10,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),10,0,0,@rOrgId) else D10 end as D10,  
 --D10_IN,D10_OUT,D10_O,D10_L,D10_T,D10_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 11) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),11,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),11,0,0,@rOrgId) else D11 end as D11,  
 --D11_IN,D11_OUT,D11_O,D11_L,D11_T,D11_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 12) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),12,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),12,0,0,@rOrgId) else D12 end as D12,  
 --D12_IN,D12_OUT,D12_O,D12_L,D12_T,D12_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 13) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),13,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),13,0,0,@rOrgId) else D13 end as D13,  
 --D13_IN,D13_OUT,D13_O,D13_L,D13_T,D13_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 14) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),14,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),14,0,0,@rOrgId) else D14 end as D14,  
 --D14_IN,D14_OUT,D14_O,D14_L,D14_T,D14_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 15) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),15,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),15,0,0,@rOrgId) else D15 end as D15,  
 --D15_IN,D15_OUT,D15_O,D15_L,D15_T,D15_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 16) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),16,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),16,0,0,@rOrgId) else D16 end as D16,  
 --D16_IN,D16_OUT,D16_O,D16_L,D16_T,D16_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 17) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),17,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),17,0,0,@rOrgId) else D17 end as D17,  
 --D17_IN,D17_OUT,D17_O,D17_L,D17_T,D17_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 18) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),18,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),18,0,0,@rOrgId) else D18 end as D18,  
 --D18_IN,D18_OUT,D18_O,D18_L,D18_T,D18_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 19) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),19,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),19,0,0,@rOrgId) else D19 end as D19,  
 --D19_IN,D19_OUT,D19_O,D19_L,D19_T,D19_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 20) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),20,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),20,0,0,@rOrgId) else D20 end as D20,  
 --D20_IN,D20_OUT,D20_O,D20_L,D20_T,D20_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 21) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),21,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),21,0,0,@rOrgId) else D21 end as D21,  
 --D21_IN,D21_OUT,D21_O,D21_L,D21_T,D21_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 22) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),22,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),22,0,0,@rOrgId) else D22 end as D22,  
 --D22_IN,D22_OUT,D22_O,D22_L,D22_T,D22_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 23) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),23,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),23,0,0,@rOrgId) else D23 end as D23,  
 --D23_IN,D23_OUT,D23_O,D23_L,D23_T,D23_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 24) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),24,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),24,0,0,@rOrgId) else D24 end as D24,  
 --D24_IN,D24_OUT,D24_O,D24_L,D24_T,D24_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 25) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),25,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),25,0,0,@rOrgId) else D25 end as D25,  
 --D25_IN,D25_OUT,D25_O,D25_L,D25_T,D25_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 26) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),26,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),26,0,0,@rOrgId) else D26 end as D26,  
 --D26_IN,D26_OUT,D26_O,D26_L,D26_T,D26_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 27) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),27,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),27,0,0,@rOrgId) else D27 end as D27,  
 --D27_IN,D27_OUT,D27_O,D27_L,D27_T,D27_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --UNION ALL  
  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 28) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),28,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),28,0,0,@rOrgId) else D28 end as D28,  
 --D28_IN,D28_OUT,D28_O,D28_L,D28_T,D28_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
  
 --IF  Month(@rFromDate) <> 2 or (((YEAR(@rFromDate) % 4 = 0 AND YEAR(@rFromDate) % 100 <> 0) OR YEAR(@rFromDate) % 400 = 0 And Month(@rFromDate)  =2))   
 --BEGIN  
 -- INSERT INTO #TempMonthTrans  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 29) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),29,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),29,0,0,@rOrgId) else D29 end as D29,D29_IN,D29_OUT,D29_O,D29_L,D29_T,D29_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
 --END  
   
 --IF Month(@rFromDate) <> 2  
 --BEGIN  
 --INSERT INTO #TempMonthTrans  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 30) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),30,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),30,0,0,@rOrgId) else D30 end as D30,  
 --D30_IN,D30_OUT,D30_O,D30_L,D30_T,D30_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
 --END  
   
 --IF Month(@rFromDate) NOT IN (2,4,6,9,11)  
 --BEGIN  
 --INSERT INTO #TempMonthTrans  
 --SELECT EmployeeId,cast(rtrim(YEAR(attn_dt) *10000+ Month(attn_dt) *100+ 31) as datetime),CASE When sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),31,0,0,@rOrgId)<>'-' then  
 --sHrm.fWeekHOffGetBio(EmployeeId,YEAR(attn_dt),MONTH(attn_dt),31,0,0,@rOrgId) else D31 end as D31,  
 --D31_IN,D31_OUT,D31_O,D31_L,D31_T,D31_E FROM sBiometric.tMonthTrans WHERE (EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 --AND ATTN_DT >= cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime) AND ATTN_DT <= cast(rtrim(YEAR(@rToDate) *10000+ Month(@rToDate) *100+ 01) as datetime)  
 --END  

 --SELECT TM.*,
 
       
		
 SELECT TM.Id,TM.EmployeeId,TM.Attn_Date,TM.PresentStatus AS OldPresentStatus,OGD.Title AS [Organization Group Department],
 
 CASE When (([sHrm].[fWeekHOffHolidayGet](TM.EmployeeId , YEAR(Attn_Date) ,MONTH(TM.Attn_Date) ,DAY(TM.Attn_Date) ,0 ,ODM.Id ,@rOrgId )='H') AND (TM.FirstIn !='00:00'))  then 'PH'
 When (([sHrm].[fWeekHOffHolidayGet](TM.EmployeeId , YEAR(Attn_Date) ,MONTH(TM.Attn_Date) ,DAY(TM.Attn_Date) ,0 ,ODM.Id ,@rOrgId )='H') AND (TM.FirstIn='00:00')) THEN 'H'  
 WHEN ((TM.PresentStatus='W') AND (TM.FirstIn !='00:00')) THEN 'PW'
 ELSE
 TM.PresentStatus END as PresentStatus,
 TM.FirstIn,TM.LastOut,TM.OverTime,TM.LateBy,TM.Duration,TM.EarlyBy, MT.Tot_Attn,
 MT.Tot_Attn + Tot_WeekOff + Tot_Holiday  AS TotalPresent,Tot_Abs AS TotalAbsent,Tot_WeekOff AS TotalWeekOff,  
 Tot_Holiday AS Totalholiday,Tot_Leave AS TotalLeave,EM.FirstName + ' ' + EM.LastName As ShortName,EM.Code as EmpCode ,   
 [sBiometric].[fEmployeeLeaveTypeGet] (TM.ATTN_DATE,TM.EmployeeId) AS LeaveStatus ,   
 [sBiometric].[fEmployeeLeaveTypePendingGet](TM.ATTN_DATE,TM.EmployeeId) AS LeavePendingStatus,   
 [sBiometric].[fEmployeeTravelGet](TM.ATTN_DATE,TM.EmployeeId) AS EmployeeTravel,  
 ISNULL(ELS.Id,0) AS EmployeeLateStatusId, 
 ISNULL(SM.Id,0) AS StatusId,  
 ISNULL(SM.Title,'') AS Status  
 FROM #TempMonthTrans TM   
 INNER JOIN sBiometric.tMonthTrans MT ON MT.EmployeeId=TM.EmployeeId  
 INNER JOIN sHRM.tEmployeeMaster EM ON EM.Id=TM.EmployeeId  
 LEFT JOIN sBiometric.tEmployeeLateStatus ELS ON ELS.EmployeeId = TM.EmployeeId AND CONVERT(date,TM.Attn_Date) = CONVERT(date,ELS.LateDate)  
 LEFT JOIN sCommon.tStatusMaster SM ON SM.Id = ELS.StatusId  
 --INNER JOIN sHrm.tEmployeeJobDetail EJD ON EJD.EmployeeId = EM.Id AND (TM.ATTN_DATE >= ISNULL(EJD.DateOfJoining,'1900/01/01'))
 INNER JOIN sHrm.tEmployeeJobDetail EJD ON EJD.EmployeeId = EM.Id AND (TM.ATTN_DATE >= ISNULL(cast(rtrim(YEAR(EJD.DateOfJoining) *10000+ Month(EJD.DateOfJoining) *100+ 01) as datetime),'1900/01/01'))
 INNER JOIN sSetup.tDepartmentMaster ODM ON EJD.OrganizationDepartmentId = ODM.Id 
 LEFT JOIN sCommon.tOrgGroupDepartmentMaster OGD ON  EJD.OrgGroupDepartmentId=OGD.Id
 WHERE   
 TM.ATTN_DATE >= @rFromDate AND TM.ATTN_DATE <=@rToDate    
 AND (TM.EmployeeId = @rEmployeeId OR @rEmployeeId = 0)   
 AND MT.Attn_dt=cast(rtrim(YEAR(@rFromDate) *10000+ Month(@rFromDate) *100+ 01) as datetime)
 AND (( @rStatus = 1 AND (TM.Duration < '09:30')) OR ( @rStatus != 1 AND ((PresentStatus = @rStatus OR @rStatus='0'))))
 AND EM.OrgId = @rOrgId 
 AND ((EM.StatusId = @rStatusId) OR (@rStatusId = 0))
 AND((EJD.OrganizationDepartmentId=@rOrganizationDepartmentId) OR (@rOrganizationDepartmentId=0))
 AND ((EJD.OrgGroupDepartmentId = @rOrgGroupDepartmentId) OR (@rOrgGroupDepartmentId=0)) 
 AND (EM.FirstName + ' ' + EM.LastName like '%' + @rEmployeeName + '%' or @rEmployeeName = '')
 AND  ISNULL(EM.IsOtherUser,0) = 0
 AND (EJD.DateOfJoining<= @Lastdate OR ISNULL(EJD.DateOfJoining,'')='')
 AND (EJD.DateOfleaving>= @Firstdate OR ISNULL(EJD.DateOfleaving,'')='')
 --AND (TM.Duration < '09:30')
 Order By TM.EmployeeId,TM.Attn_Date
END
GO
PRINT N'Altering [sCertificate].[pLeavingAdd]...';


GO
ALTER PROCEDURE [sCertificate].[pLeavingAdd]
			
		@rId bigint
       ,@rAcademicId bigint
       ,@rLCNumber int
       ,@rLCDate date
       ,@rLeftDate date
       ,@rLCCopy tinyint
       ,@rLCRemark nvarchar(200)
       ,@rLCProgress nvarchar(200)
       ,@rLCConduct nvarchar(200)
       ,@rLCReason nvarchar(200)
       ,@rLCBookNumber nvarchar(20)
       ,@rCreatedUserId bigint
       ,@rOrgId int
       ,@rToCollege nvarchar(200)
	   ,@rToCollegeAddress nvarchar(200)
	   ,@rLastExamName nvarchar(200)
	   ,@rLastExamResult nvarchar(200)
	   ,@rRepeatDetail nvarchar(100)
	   ,@rSubjectStudied nvarchar(300)
	   ,@rPromoteToClass nvarchar(50)
	   ,@rFeeDetail nvarchar(100)
	   ,@rFeeConcessionDetail nvarchar(100)
	   ,@rExtraCurricularActivities nvarchar(200)
	   ,@rNCCScoutDetail nvarchar(100)
	   ,@rTermsInInstitute nvarchar(500)
	   ,@rBatchId int
	   ,@rIsNumberBatchWise bit = 0
	   ,@rTotalDays int=0
	   ,@rPresentDays int=0
	   ,@rProofOfDOB nvarchar(200)
           	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	BEGIN TRY
	
	 DECLARE @lNextNumber BIGINT
	 DECLARE @lStudentId bigint
	 DECLARE @lClassId bigint
	 DECLARE @lAdmissionDate Date
	 
	 SELECT @lStudentId=StudentId,@lClassId=ClassId FROM sStudent.tAcademic WHERE Id=@rAcademicId
	 UPDATE sStudent.tAcademic SET BatchStatusId = 1 WHERE BatchStatusId is null
	 
	 If(@rIsNumberBatchWise = 1)
	 BEGIN
		IF(@rLCNumber = 0)	
		BEGIN
			SELECT @rLCNumber = ISNULL(MAX(LCNumber),0) + 1 FROM sCertificate.tLeaving WHERE OrgId=@rOrgId AND BatchId = @rBatchId
		END	
	 
		IF(@rId = 0)	
		BEGIN
			IF EXISTS (SELECT Id FROM sCertificate.tLeaving WHERE AcademicId = @rAcademicId AND BatchId = @rBatchId)
			BEGIN
				SELECT -103 AS Result
				RETURN
			END
			
			IF EXISTS(SELECT 1 FROM sCertificate.tLeaving WHERE LCNumber=@rLCNumber AND OrgId=@rOrgId AND BatchId = @rBatchId)
			BEGIN
				SELECT -104 AS Result
				RETURN
			END
		END
	 ELSE
 	 BEGIN
		IF(@rLCNumber = 0)	
		BEGIN
			SELECT @rLCNumber = ISNULL(MAX(LCNumber),0) + 1 FROM sCertificate.tLeaving WHERE OrgId=@rOrgId
		END	
	 
		IF(@rId = 0)	
		BEGIN
			IF EXISTS (SELECT Id FROM sCertificate.tLeaving WHERE AcademicId = @rAcademicId)-- IN(SELECT Id FROM sStudent.tAcademic WHERE StudentId=@lStudentId))
			BEGIN
				SELECT -103 AS Result
				RETURN
			END
			
			IF EXISTS(SELECT 1 FROM sCertificate.tLeaving WHERE LCNumber=@rLCNumber AND OrgId=@rOrgId)
			BEGIN
				SELECT -104 AS Result
				RETURN
			END
		END
	END
			
			IF EXISTS(SELECT 1 FROM sAttendance.tStudentRegister WHERE AttendanceDate>@rLeftDate AND AcademicId=@rAcademicId)
			BEGIN
				SELECT -101 AS Result
				RETURN
			END
			
			SELECT @lAdmissionDate = AdmissionDate FROM sStudent.tStudentAdmission WHERE StudentId=@lStudentId
			IF (@lAdmissionDate > @rLCDate)
			BEGIN
				SELECT -105 AS Result
				RETURN
			END
		END
		ELSE
		BEGIN
			IF EXISTS(SELECT Id FROM sCertificate.tLeaving WHERE AcademicId=@rAcademicId AND Id<>@rId)
			BEGIN
				SELECT -103 AS Result
				RETURN
			END
		END
		
		BEGIN TRANSACTION	
			
		IF(@rId = 0)	
		BEGIN
	
				EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sCertificate.tLeaving', 0, 1, 1,@outNextNumber = @lNextNumber output
				INSERT INTO sCertificate.tLeaving
				(
					Id
				   ,AcademicId
				   ,LCNumber
				   ,LCDate
				   ,LeftDate
				   ,LCCopy
				   ,LCRemark
				   ,LCProgress
				   ,LCConduct
				   ,LCReason
				   ,LCBookNumber
				   ,OrgId
				   ,CreatedUserId
				   ,CreatedDateTime
				   ,ToCollege
				   ,ToCollegeAddress
				   ,LastExamName 
		           ,LastExamResult 
		           ,RepeatDetail 
		           ,SubjectStudied
		           ,PromoteToClass 
		           ,FeeDetail 
		           ,FeeConcessionDetail
		           ,ExtraCurricularActivities
		           ,NCCScoutDetail 
		           ,TermsInInstitute
		           ,BatchId
				   ,TotalDays
				   ,PresentDays
				   ,ProofOfDOB
				)
				VALUES
				(	@lNextNumber
				   ,@rAcademicId
				   ,@rLCNumber
				   ,@rLCDate
				   ,@rLeftDate
				   ,@rLCCopy
				   ,@rLCRemark
				   ,@rLCProgress
				   ,@rLCConduct
				   ,@rLCReason
				   ,@rLCBookNumber
				   ,@rOrgId
				   ,@rCreatedUserId
				   ,sCommon.fGetDate() 
				   ,@rToCollege
				   ,@rToCollegeAddress
				   ,@rLastExamName 
		           ,@rLastExamResult 
		           ,@rRepeatDetail 
		           ,@rSubjectStudied
		           ,@rPromoteToClass 
		           ,@rFeeDetail 
		           ,@rFeeConcessionDetail
		           ,@rExtraCurricularActivities
		           ,@rNCCScoutDetail 
		           ,@rTermsInInstitute
		           ,@rBatchId
				   ,@rTotalDays 
			       ,@rPresentDays 
				   ,@rProofOfDOB
				)	
				SELECT @lNextNumber AS Result 
				 
		END
		
		ELSE
		
		BEGIN
		
			 select 111
			 SET @lNextNumber = @rId
				UPDATE  sCertificate.tLeaving
				SET
					AcademicId=@rAcademicId,
					LCDate=@rLCDate,
					LeftDate=@rLeftDate,
					LCCopy=@rLCCopy,
					LCRemark=@rLCRemark,
					LCProgress=@rLCProgress,
					LCConduct=@rLCConduct,
					LCReason=@rLCReason,
					LCBookNumber=@rLCBookNumber,
					UpdatedUserId=@rCreatedUserId,
					UpdatedDateTime=sCommon.fGetDate(),
					ToCollege=@rToCollege,
				    ToCollegeAddress=@rToCollegeAddress,
				    LastExamName=@rLastExamName 
				    ,LastExamResult=@rLastExamResult 
				    ,RepeatDetail=@rRepeatDetail 
				    ,SubjectStudied=@rSubjectStudied
				    ,PromoteToClass=@rPromoteToClass 
				    ,FeeDetail=@rFeeDetail 
				    ,FeeConcessionDetail=@rFeeConcessionDetail
				    ,ExtraCurricularActivities=@rExtraCurricularActivities
				    ,NCCScoutDetail=@rNCCScoutDetail 
				    ,TermsInInstitute=@rTermsInInstitute
				    ,BatchId = @rBatchId
					,TotalDays = @rTotalDays 
			        ,PresentDays = @rPresentDays 
					,ProofOfDOB = @rProofOfDOB
				WHERE Id = @rId
				
		 
		END 
		
		
				UPDATE sStudent.tStudentAdmission
				SET
				  LeftStandardId=@lClassId,
				  LeftDate = @rLeftDate, 
				  LeftReason = @rLCReason, 
				  LCProgress = @rLCProgress,
				  LCConduct = @rLCConduct, 
				  LCBook = @rLCBookNumber, 
				  LCNumber = @rLCNumber,
				  LCDate = @rLCDate, 
				  LCRemark = @rLCRemark, 
				  LCCopy = @rLCCopy, 
				  LcId = @lNextNumber, 
				  UpdatedUserId = @rCreatedUserId, 
				  UpdatedDateTime =sCommon.fGetDate()
				WHERE StudentId = @lStudentId 
				
				UPDATE sStudent.tStudentMaster
				SET
				  StatusId=3,
				  UpdatedUserId = @rCreatedUserId, 
				  UpdatedDateTime =sCommon.fGetDate()
				WHERE Id = @lStudentId 
				
			  UPDATE sSecurity.tUser
			   SET IsObsolete=1 
			   WHERE SourceTypeId=2003 AND Sourceid=@lStudentId
			   
		 COMMIT TRANSACTION		
		 
		  --Update Batch Status 
			UPDATE A
			SET BatchStatusId = 3 
			FROM sStudent.tAcademic A
			INNER JOIN sStudent.tStudentMaster SM ON A.StudentId = SM.Id
			INNER JOIN sStudent.tStudentAdmission SA ON A.StudentId = SA.StudentId AND A.ClassId = SA.LeftStandardId
			WHERE SM.StatusId = 3 AND SM.Id = @lStudentId
		 --Ends

		   --Update Class Status 
			UPDATE A
			SET ClassStatusId = 3 ,
			 UpdatedUserId = @rCreatedUserId, 
		     UpdatedDateTime =sCommon.fGetDate()
			FROM sStudent.tAcademic A
			WHERE A.Id = @rAcademicId
		 --Ends
		 
		  SELECT @lNextNumber AS Result
	
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
	
END
GO
PRINT N'Altering [sCertificate].[pLeavingBulkAdd]...';


GO

ALTER PROCEDURE [sCertificate].[pLeavingBulkAdd]
			
		@rId bigint
       ,@rAcademicId bigint
       ,@rLCNumber int
       ,@rLCDate date
       ,@rLeftDate date
       ,@rLCCopy tinyint
       ,@rLCRemark nvarchar(200)
       ,@rLCProgress nvarchar(200)
       ,@rLCConduct nvarchar(200)
       ,@rLCReason nvarchar(200)
       ,@rLCBookNumber nvarchar(20)
       ,@rCreatedUserId bigint
       ,@rOrgId int
       ,@rToCollege nvarchar(200)
	   ,@rToCollegeAddress nvarchar(200)
	   ,@rLastExamName nvarchar(200)
	   ,@rLastExamResult nvarchar(200)
	   ,@rRepeatDetail nvarchar(100)
	   ,@rSubjectStudied nvarchar(300)
	   ,@rPromoteToClass nvarchar(50)
	   ,@rFeeDetail nvarchar(100)
	   ,@rFeeConcessionDetail nvarchar(100)
	   ,@rExtraCurricularActivities nvarchar(200)
	   ,@rNCCScoutDetail nvarchar(100)
	   ,@rTermsInInstitute nvarchar(500)
	   ,@rBatchId int
	   ,@rIsNumberBatchWise bit = 0
	   ,@rTotalDays int=0
	   ,@rPresentDays int=0
	   ,@rProofOfDOB nvarchar(200)
           	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	BEGIN TRY
	
	 DECLARE @lNextNumber BIGINT
	 DECLARE @lStudentId bigint
	 DECLARE @lClassId bigint
	 DECLARE @lAdmissionDate Date
	 
	 SELECT @lStudentId=StudentId,@lClassId=ClassId FROM sStudent.tAcademic WHERE Id=@rAcademicId
	 UPDATE sStudent.tAcademic SET BatchStatusId = 1 WHERE BatchStatusId is null
	 
	 If(@rIsNumberBatchWise = 1)
	 BEGIN
		IF(@rLCNumber = 0)	
		BEGIN
			SELECT @rLCNumber = ISNULL(MAX(LCNumber),0) + 1 FROM sCertificate.tLeaving WHERE OrgId=@rOrgId AND BatchId = @rBatchId
		END	
	 
		IF(@rId = 0)	
		BEGIN
			IF EXISTS (SELECT Id FROM sCertificate.tLeaving WHERE AcademicId = @rAcademicId AND BatchId = @rBatchId)
			BEGIN
				SELECT -103 AS Result
				RETURN
			END
			
			IF EXISTS(SELECT 1 FROM sCertificate.tLeaving WHERE LCNumber=@rLCNumber AND OrgId=@rOrgId AND BatchId = @rBatchId)
			BEGIN
				SELECT -104 AS Result
				RETURN
			END
		END
	 ELSE
 	 BEGIN
		IF(@rLCNumber = 0)	
		BEGIN
			SELECT @rLCNumber = ISNULL(MAX(LCNumber),0) + 1 FROM sCertificate.tLeaving WHERE OrgId=@rOrgId
		END	
	 
		IF(@rId = 0)	
		BEGIN
			IF EXISTS (SELECT Id FROM sCertificate.tLeaving WHERE AcademicId = @rAcademicId)-- IN(SELECT Id FROM sStudent.tAcademic WHERE StudentId=@lStudentId))
			BEGIN
				SELECT -103 AS Result
				RETURN
			END
			
			IF EXISTS(SELECT 1 FROM sCertificate.tLeaving WHERE LCNumber=@rLCNumber AND OrgId=@rOrgId)
			BEGIN
				SELECT -104 AS Result
				RETURN
			END
		END
	END
			
			IF EXISTS(SELECT 1 FROM sAttendance.tStudentRegister WHERE AttendanceDate>@rLeftDate AND AcademicId=@rAcademicId)
			BEGIN
				SELECT -101 AS Result
				RETURN
			END
			
			SELECT @lAdmissionDate = AdmissionDate FROM sStudent.tStudentAdmission WHERE StudentId=@lStudentId
			IF (@lAdmissionDate > @rLCDate)
			BEGIN
				SELECT -105 AS Result
				RETURN
			END
		END
		ELSE
		BEGIN
			IF EXISTS(SELECT Id FROM sCertificate.tLeaving WHERE AcademicId=@rAcademicId AND Id<>@rId)
			BEGIN
				SELECT -103 AS Result
				RETURN
			END
		END
		
		BEGIN TRANSACTION	
			
		IF(@rId = 0)	
		BEGIN
	
				EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sCertificate.tLeaving', 0, 1, 1,@outNextNumber = @lNextNumber output
				INSERT INTO sCertificate.tLeaving
				(
					Id
				   ,AcademicId
				   ,LCNumber
				   ,LCDate
				   ,LeftDate
				   ,LCCopy
				   ,LCRemark
				   ,LCProgress
				   ,LCConduct
				   ,LCReason
				   ,LCBookNumber
				   ,OrgId
				   ,CreatedUserId
				   ,CreatedDateTime
				   ,ToCollege
				   ,ToCollegeAddress
				   ,LastExamName 
		           ,LastExamResult 
		           ,RepeatDetail 
		           ,SubjectStudied
		           ,PromoteToClass 
		           ,FeeDetail 
		           ,FeeConcessionDetail
		           ,ExtraCurricularActivities
		           ,NCCScoutDetail 
		           ,TermsInInstitute
		           ,BatchId
				   ,TotalDays
				   ,PresentDays
				   ,ProofOfDOB
				   
				)
				VALUES
				(	@lNextNumber
				   ,@rAcademicId
				   ,@rLCNumber
				   ,@rLCDate
				   ,@rLeftDate
				   ,@rLCCopy
				   ,@rLCRemark
				   ,@rLCProgress
				   ,@rLCConduct
				   ,@rLCReason
				   ,@rLCBookNumber
				   ,@rOrgId
				   ,@rCreatedUserId
				   ,sCommon.fGetDate() 
				   ,@rToCollege
				   ,@rToCollegeAddress
				   ,@rLastExamName 
		           ,@rLastExamResult 
		           ,@rRepeatDetail 
		           ,@rSubjectStudied
		           ,@rPromoteToClass 
		           ,@rFeeDetail 
		           ,@rFeeConcessionDetail
		           ,@rExtraCurricularActivities
		           ,@rNCCScoutDetail 
		           ,@rTermsInInstitute
		           ,@rBatchId
				   ,@rTotalDays 
			       ,@rPresentDays 
				   ,@rProofOfDOB
				)	
				SELECT @lNextNumber AS Result 
				 
		END
		
		ELSE
		
		BEGIN
		
			 select 111
			 SET @lNextNumber = @rId
				UPDATE  sCertificate.tLeaving
				SET
					AcademicId=@rAcademicId,
					LCDate=@rLCDate,
					LeftDate=@rLeftDate,
					LCCopy=@rLCCopy,
					LCRemark=@rLCRemark,
					LCProgress=@rLCProgress,
					LCConduct=@rLCConduct,
					LCReason=@rLCReason,
					LCBookNumber=@rLCBookNumber,
					UpdatedUserId=@rCreatedUserId,
					UpdatedDateTime=sCommon.fGetDate(),
					ToCollege=@rToCollege,
				    ToCollegeAddress=@rToCollegeAddress,
				    LastExamName=@rLastExamName 
				    ,LastExamResult=@rLastExamResult 
				    ,RepeatDetail=@rRepeatDetail 
				    ,SubjectStudied=@rSubjectStudied
				    ,PromoteToClass=@rPromoteToClass 
				    ,FeeDetail=@rFeeDetail 
				    ,FeeConcessionDetail=@rFeeConcessionDetail
				    ,ExtraCurricularActivities=@rExtraCurricularActivities
				    ,NCCScoutDetail=@rNCCScoutDetail 
				    ,TermsInInstitute=@rTermsInInstitute
				    ,BatchId = @rBatchId
					,TotalDays = @rTotalDays 
			        ,PresentDays = @rPresentDays 
				WHERE Id = @rId
				
		 
		END 
		
		
				UPDATE sStudent.tStudentAdmission
				SET
				  LeftStandardId=@lClassId,
				  LeftDate = @rLeftDate, 
				  LeftReason = @rLCReason, 
				  LCProgress = @rLCProgress,
				  LCConduct = @rLCConduct, 
				  LCBook = @rLCBookNumber, 
				  LCNumber = @rLCNumber,
				  LCDate = @rLCDate, 
				  LCRemark = @rLCRemark, 
				  LCCopy = @rLCCopy, 
				  LcId = @lNextNumber, 
				  UpdatedUserId = @rCreatedUserId, 
				  UpdatedDateTime =sCommon.fGetDate()
				WHERE StudentId = @lStudentId 
				
				UPDATE sStudent.tStudentMaster
				SET
				  StatusId=3,
				  UpdatedUserId = @rCreatedUserId, 
				  UpdatedDateTime =sCommon.fGetDate()
				WHERE Id = @lStudentId 
				
			  UPDATE sSecurity.tUser
			   SET IsObsolete=1 
			   WHERE SourceTypeId=2003 AND Sourceid=@lStudentId
			   
		 COMMIT TRANSACTION		
		 
		  --Update Batch Status 
			UPDATE A
			SET BatchStatusId = 3 
			FROM sStudent.tAcademic A
			INNER JOIN sStudent.tStudentMaster SM ON A.StudentId = SM.Id
			INNER JOIN sStudent.tStudentAdmission SA ON A.StudentId = SA.StudentId AND A.ClassId = SA.LeftStandardId
			WHERE SM.StatusId = 3 AND SM.Id = @lStudentId
		 --Ends

		   --Update Class Status 
			UPDATE A
			SET ClassStatusId = 3 ,
			 UpdatedUserId = @rCreatedUserId, 
		     UpdatedDateTime =sCommon.fGetDate()
			FROM sStudent.tAcademic A
			WHERE A.Id = @rAcademicId
		 --Ends

		 --Update TotalDays And presentDays
		 IF Exists(Select AcademicId from sStudent.tAcademicDetail Where AcademicId =@rAcademicId)
		 BEGIN
		 UPDATE sStudent.tAcademicDetail 
			SET TotalDaysAnnual=@rTotalDays  ,PresentDaysAnnual = @rPresentDays
			WHERE AcademicId =@rAcademicId
		 END
		 ELSE
		 BEGIN
			INSERT INTO sStudent.tAcademicDetail (AcademicId,TotalDaysAnnual,PresentDaysAnnual)VALUES(@rAcademicId,@rTotalDays,@rPresentDays)
		 END
		  SELECT @lNextNumber AS Result
	
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
	
END
GO
PRINT N'Altering [sCertificate].[pLeavingBulkGet]...';


GO
 

ALTER PROCEDURE [sCertificate].[pLeavingBulkGet]
	 
	 @rClassID int,
	 @rDivisionId int,
	 @rOrgId int,
	 @rBatchId int,
	 @rOrderBy int = 0
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 

BEGIN 
	
	SELECT 
			SA.AcademicId,
			ISNULL(SA.RollNo,0) AS RollNo,
			SA.Code,
			SA.Name,
			ISNULL(L.Id,0) AS Id,
			ISNULL(L.LCNumber,0) AS LCNumber,
			ISNULL(L.LCDate,'') AS LCDate,
			ISNULL(L.LCRemark,'')AS LCRemark,
			ISNULL(L.LastExamName,'')AS LastExamName,
			ISNULL(L.LastExamResult,'')AS LastExamResult,
			ISNULL(L.RepeatDetail,'')AS RepeatDetail,
			ISNULL(L.SubjectStudied,'')AS SubjectStudied,
			ISNULL(L.PromoteToClass,'')AS PromoteToClass,
			ISNULL(L.ExtraCurricularActivities,'')As ExtraCurricularActivities,
			ISNULL(L.NCCScoutDetail,'')AS NCCScoutDetail,
			ISNULL(L.TermsInInstitute,'')As TermsInInstitute,
			ISNULL(L.FeeDetail,'')AS FeeDetail,
			ISNULL(L.FeeConcessionDetail,'')AS FeeConcessionDetail,
			ISNULL(AD.TotalDaysAnnual,0)AS TotalDays,
			ISNULL(AD.PresentDaysAnnual,0)AS PresentDays,
			ISNULL(L.ProofOfDOB,'')AS ProofOfDOB

			
			
  FROM 
			[sStudent].[vStudentAcademic]  SA 
			LEFT OUTER JOIN sCertificate.tLeaving L ON L.AcademicId=SA.AcademicId
			LEFT JOIN sStudent.tAcademicDetail AD ON SA.AcademicId=AD.AcademicId
  WHERE 
			(SA.ClassId=@rClassId OR @rClassId=0)
			AND (SA.DivisionId=@rDivisionId OR @rDivisionId=0)
			AND SA.BatchId=@rBatchId
			AND SA.OrgID=@rOrgId 
			AND L.Id IS NULL
			order by
			CASE @rOrderBy WHEN 2 THEN SA.Code END,
			CASE @rOrderBy WHEN 1 THEN SA.RollNo END,
			CASE @rOrderBy WHEN 0 THEN SA.RollNo END
END
GO
PRINT N'Altering [sCertificate].[pLeavingGet]...';


GO
 
ALTER PROCEDURE [sCertificate].[pLeavingGet]
	 
	 @rId bigint = 0,
	 @rCode nvarchar(20),
	 @rName nvarchar(200),
	 @rFromNo int,
	 @rToNo int,
	 @rClassId int,
	 @rDivisionId int,
	 @rFromDate date,
	 @rToDate date,
	 @rOrgId smallint,
	 @rBatchId int,
	 @rIsSearch bit = 1
	 	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 

BEGIN 
	 IF @rIsSearch = 1
		BEGIN
			SET @rCode = sCommon.fSearchStringGet(@rCode)
			SET @rName = sCommon.fSearchStringGet(@rName)
			
		END
	
	SELECT 
	
			L.Id,
			L.AcademicId,
			SA.Code,
			SA.Name,
			SA.ClassName+'-'+SA.DivisionName as ClassDivision,
			L.LCNumber,
			L.LCDate,
			L.LeftDate,
			L.LCCopy,
			L.LCRemark,
			L.LCProgress,
			L.LCConduct,
			L.LCReason,
			L.LCBookNumber,
			L.ToCollege,
			L.ToCollegeAddress
			,L.LastExamName 
		    ,L.LastExamResult 
		    ,L.RepeatDetail 
		    ,L.SubjectStudied
		    
		    ,L.PromoteToClass 
		    ,L.FeeDetail 
		    ,L.FeeConcessionDetail
		    ,L.ExtraCurricularActivities
		    ,L.NCCScoutDetail 
		    ,L.TermsInInstitute
			,ISNULL(L.TotalDays,0)AS TotalDays
			,ISNULL(L.PresentDays,0) AS PresentDays
			,L.ProofOfDOB

		
  FROM 
			sCertificate.tLeaving L
			INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId=L.AcademicId
			AND SA.OrgId=@rOrgId AND SA.BatchId=@rBatchId
			
  WHERE 
			((L.Id = @rId) OR (@rId = 0))
			AND ((SA.ClassId = @rClassId) OR (@rClassId = 0))
			AND ((SA.DivisionId =@rDivisionId) OR (@rDivisionId = 0))
			AND ((SA.Code like @rCode) OR (ISNULL(@rCode,'') = ''))
			AND ((SA.Name like @rName) OR (ISNULL(@rName,'') = ''))
			AND ((L.LCDate >= @rFromDate) OR (ISNULL(@rFromDate,'')=''))
			AND ((L.LCDate <= @rToDate) OR (ISNULL(@rToDate,'')=''))
			AND ((L.LCNumber >= @rFromNo) OR (@rFromNo = 0))
			AND ((L.LCNumber <= @rToNo) OR (@rToNo = 0))
		
			ORDER BY L.LCNumber
END
GO
PRINT N'Altering [sCommon].[pOrganizationSetupRemove]...';


GO
 
ALTER PROCEDURE [sCommon].[pOrganizationSetupRemove]
	 
	 @rOrgId bigint
	 
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
  --  BEGIN TRY
	
	 --BEGIN TRANSACTION
	 
	 --EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
    
DELETE FROM sAccounting.tExpense WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAccounting.tExpenseGroupMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAccounting.tReimbursement WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAccounting.tReimbursementDetail WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAccounting.tReimbursementTypeMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sActivity.tActivity WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sActivity.tActivityParticipant WHERE CAST(LEFT(ActivityId, 4) AS INT) = @rOrgId
DELETE FROM sActivity.tAssignClub WHERE CAST(LEFT(ClubId, 4) AS INT) = @rOrgId
DELETE FROM sActivity.tEventAssign WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sActivity.tEventEnrollment WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sActivity.tEventSchedule WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sActivity.tHealthCheckup WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sActivity.tHealthCheckupAssign WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sActivity.tHealthCheckupDetail WHERE CAST(LEFT(HealthCheckupId, 4) AS INT) = @rOrgId
DELETE FROM sActivity.tHealthCheckupParameter WHERE CAST(LEFT(HealthCheckupId, 4) AS INT) = @rOrgId
DELETE FROM sActivity.tHealthCheckupStudent WHERE CAST(LEFT(HealthCheckupId, 4) AS INT) = @rOrgId
DELETE FROM sActivity.tHealthParameterGroupMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sActivity.tHealthParameterList WHERE CAST(LEFT(HealthParameterId, 4) AS INT) = @rOrgId
DELETE FROM sActivity.tHealthParameterMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sActivity.tHealthTrack WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sActivity.tHouseMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sActivity.tPlacement WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sActivity.tPlacementInvite WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sActivity.tStudentImmunizationStatus WHERE CAST(LEFT(ImmunizationChartId, 4) AS INT) = @rOrgId
DELETE FROM sAdmission.tAdmissionForm WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sAdmission.tAdmissionFormIssue WHERE OrgId = @rOrgId
DELETE FROM sAdmission.tAdmissionInquiry WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sAlert.tAlertConfiguration WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAlert.tAlertLog WHERE OrgId = @rOrgId
DELETE FROM sAlert.tAlertQueue WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sAlert.tAlertQueueDetail WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sAlert.tCustomTemplateMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAlert.tDepartmentRecipient WHERE CAST(LEFT(DepartmentId, 4) AS INT) = @rOrgId
DELETE FROM sAlert.tEmailConfiguration WHERE CAST(LEFT(OrgId, 4) AS SMALLINT) = @rOrgId
DELETE FROM sAlert.tEventTemplate WHERE CAST(LEFT(OrgId, 4) AS SMALLINT) = @rOrgId
DELETE FROM sAlert.tExternalRecipientMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAlert.tSMSConfiguration WHERE CAST(LEFT(OrgId, 4) AS SMALLINT) = @rOrgId
DELETE FROM sAlert.tTemplateMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tCCECSClass WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tCCECSGroupMaster WHERE OrgId = @rOrgId
DELETE FROM sAssessment.tCCECSIndicatorGrade WHERE CAST(LEFT(CCECSId, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tCCECSIndicatorMaster WHERE OrgId = @rOrgId
DELETE FROM sAssessment.tCCECSMaster WHERE OrgId = @rOrgId
DELETE FROM sAssessment.tCCECSScore WHERE CAST(LEFT(CCECSId, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tCCEExamMapping WHERE CAST(LEFT(BatchId, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tCCEExamSetting WHERE CAST(LEFT(ClassId, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tCCEFAGroupMaster WHERE OrgId = @rOrgId
DELETE FROM sAssessment.tCCEFAIndicatorMaster WHERE OrgId = @rOrgId
DELETE FROM sAssessment.tCCEFAMaster WHERE OrgId = @rOrgId
DELETE FROM sAssessment.tCCEFAScore WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sAssessment.tCCEFASubject WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tCCEResult WHERE AcademicId = @rOrgId
DELETE FROM sAssessment.tCCEResultDetail WHERE AcademicId = @rOrgId
DELETE FROM sAssessment.tExamCombine WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tExamDetail WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tExamGroupMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tExamMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tExamStatus WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sAssessment.tExamsubgroupmaster WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sAssessment.tGeneralExamCustomSearch WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sAssessment.tGradeSetDetail WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tGradeSetMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tLevelSetDetail WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tLevelSetMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tOptionalSubject WHERE CAST(LEFT(AcademicId, 4) AS BIGINT) = @rOrgId
DELETE FROM sAssessment.tRankSetDetail WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tRankSetMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tResult WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sAssessment.tResultDetail WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sAssetManagement.tAssetAdjustment WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAssetManagement.tAssetAdjustmentDetail WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAssetManagement.tAssetAudit WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAssetManagement.tAssetAuditDetail WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAssetManagement.tAssetMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAssetManagement.tDepartmentLocation WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAssetManagement.tMaintenence WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAssetManagement.tMovement WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAssetManagement.tMovementDetail WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAssetManagement.tRecordManualEvent WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAttendance.tApplicableAttendanceType WHERE OrgId = @rOrgId
DELETE FROM sAttendance.tMonthlyRegister WHERE CAST(LEFT(AcademicId, 4) AS BIGINT) = @rOrgId
DELETE FROM sAttendance.tMonthlySetup WHERE CAST(LEFT(ClassId, 4) AS INT) = @rOrgId
DELETE FROM sAttendance.tStudentRegister WHERE CAST(LEFT(AcademicId, 4) AS BIGINT) = @rOrgId
DELETE FROM sAttendance.tStudentSetup WHERE CAST(LEFT(AcademicId, 4) AS BIGINT) = @rOrgId
DELETE FROM sAttendance.tSubjectBatch WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAttendance.tSubjectBatchStudent WHERE CAST(LEFT(AcademicId, 4) AS BIGINT) = @rOrgId
DELETE FROM sAudit.tAudit WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sAudit.tAuditLog WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sAudit.tAuditLogTransaction WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAudit.tAuditTable WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAudit.tAuditTableField WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAudit.tAuditTransaction WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sBi.tAction WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sBi.tActionStatusMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sBi.tActionTypeMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sBi.tAudit WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sBi.tAuditLocation WHERE CAST(LEFT(AuditId, 4) AS BIGINT) = @rOrgId
DELETE FROM sBi.tAuditReasonMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sBi.tAuditStatusMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sBi.tAuditTypeMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sBi.tDocument WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sBi.tDocumentTypeMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sBi.tFinding WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sBi.tFindingTypeMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sBi.tInspection WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sBi.tQuestionAnswerMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sBi.tQuestionAnswerTypeMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sBi.tQuestionMainGroupMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sBi.tQuestionMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sBi.tQuestionnairesMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sBi.tQuestionResponse WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sBi.tQuestionSubGroupMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sBi.tSeverityMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sBiometric.tPunchMaster WHERE CAST(LEFT(OrgId, 4) AS SMALLINT) = @rOrgId
DELETE FROM sCertificate.tBonafide WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sCertificate.tCharacter WHERE OrgId = @rOrgId
DELETE FROM sCertificate.tFirstTrial WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sCertificate.tLeaving WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sCommon.tAgeGroupMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sCommon.tClinicMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId


DELETE FROM sCommon.tDurationMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sCommon.tExceptions WHERE CAST(LEFT(OrgId, 4) AS SMALLINT) = @rOrgId
DELETE FROM sCommon.tFileUpload WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sCommon.tFirstAidMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sCommon.tFunctionMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sCommon.tLocationMaster WHERE OrgId = @rOrgId
DELETE FROM sCommon.tOrganizationDepartmentMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sCommon.tPrefixSetup WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sCommon.tPublicHolidayMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sCommon.tPublicHolidayTypeMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sCommon.tReasonMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sCommon.tReportFormatGroupOrg WHERE OrgId = @rOrgId
 
DELETE FROM sCommunication.tAssignment WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sCommunication.tAssignmentScope WHERE CAST(LEFT(AssignmentId, 4) AS BIGINT) = @rOrgId
DELETE FROM sCommunication.tAssignmentView WHERE CAST(LEFT(AssignmentId, 4) AS BIGINT) = @rOrgId
DELETE FROM sCommunication.tAssignTask WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sCommunication.tBehaviour WHERE CAST(LEFT(AcademicId, 4) AS BIGINT) = @rOrgId
DELETE FROM sCommunication.tDiscussion WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sCommunication.tDiscussionComment WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sCommunication.tDiscussionGroup WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sCommunication.tDiscussionGroupMember WHERE CAST(LEFT(DiscussionGroupId, 4) AS BIGINT) = @rOrgId
DELETE FROM sCommunication.tPhotoCategoryOrg WHERE OrgId = @rOrgId
DELETE FROM sCommunication.tQueryCategoryOrg WHERE OrgId = @rOrgId
DELETE FROM sCommunication.tUploadBookLog WHERE CAST(LEFT(BookLogId, 4) AS BIGINT) = @rOrgId
DELETE FROM sCommunication.tUploadResult WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sConfig.tColorLegendDetail WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sConfig.tConfigurationSetup WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId AND SourceTypeId <> 2010
DELETE FROM sConfig.tCultureConfiguration WHERE OrgId = @rOrgId
DELETE FROM sConfig.tImport WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sConfig.tLocalDeviceSetting WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sConfig.tMachineMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sConfig.tMachinePrinterMapping WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sConfig.tPrinterMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sEExam.tTestAssign WHERE CAST(LEFT(TestId, 4) AS int) = @rOrgId
DELETE FROM sEExam.tTestMaster WHERE OrgId = @rOrgId
DELETE FROM sEExam.tTestParticipant WHERE CAST(LEFT(TestId, 4) AS BIGINT) = @rOrgId
DELETE FROM sEExam.tTestParticipantAnswer WHERE CAST(LEFT(TestId, 4) AS INT) = @rOrgId
DELETE FROM sEExam.tTestQuestion WHERE CAST(LEFT(TestId, 4) AS INT) = @rOrgId
DELETE FROM sFee.tChargeReceipt WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sFee.tChargeReceiptDetail WHERE CAST(LEFT(ChargeReceiptId, 4) AS BIGINT) = @rOrgId
DELETE FROM sFee.tFeeAllocation WHERE CAST(LEFT(SourceId, 4) AS BIGINT) = @rOrgId
DELETE FROM sFee.tFeeCustomSearch WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sFee.tFeeOptional WHERE CAST(LEFT(SourceId, 4) AS BIGINT) = @rOrgId
DELETE FROM sFee.tFeeSetup WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sFee.tReceipt WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sFee.tReceiptDetail WHERE CAST(LEFT(ReceiptId, 4) AS BIGINT) = @rOrgId
DELETE FROM sFixedAsset.tCategoryMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sFixedAsset.tLocationMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sFrontOffice.tVisitorLog WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sGSEB.tActivityIndicatorScore WHERE CAST(LEFT(ActivityId, 4) AS INT) = @rOrgId
DELETE FROM sGSEB.tAnusuchiB WHERE CAST(LEFT(AcademicId, 4) AS BIGINT) = @rOrgId
DELETE FROM sGSEB.tClassActivity WHERE CAST(LEFT(ActivityId, 4) AS INT) = @rOrgId
DELETE FROM sGSEB.tExamSetting WHERE CAST(LEFT(ClassId, 4) AS INT) = @rOrgId
DELETE FROM sGSEB.tGSEBExamMapping WHERE CAST(LEFT(BatchId, 4) AS INT) = @rOrgId
DELETE FROM sGSEB.tHetuMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sGSEB.tAnusuchiB WHERE CAST(LEFT(AcademicId, 4) AS BIGINT) = @rOrgId
DELETE FROM sGSEB.tResult WHERE CAST(LEFT(AcademicId, 4) AS BIGINT) = @rOrgId
DELETE FROM sGSEB.tResultDetail WHERE CAST(LEFT(AcademicId, 4) AS BIGINT) = @rOrgId
DELETE FROM sGSEB.tResultDetailOptional WHERE CAST(LEFT(AcademicId, 4) AS BIGINT) = @rOrgId
DELETE FROM sGSEB.tSubjectIndicatorMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sHostel.tAssignRoom WHERE CAST(LEFT(RoomId, 4) AS INT) = @rOrgId
DELETE FROM sHostel.tHostelWarden WHERE CAST(LEFT(HostelId, 4) AS INT) = @rOrgId
DELETE FROM sHrm.tEmployeeAttendance WHERE CAST(LEFT(EmployeeId, 4) AS BIGINT) = @rOrgId
DELETE FROM sHrm.tEmployeeCommunications WHERE CAST(LEFT(EmployeeId, 4) AS BIGINT) = @rOrgId
DELETE FROM sHrm.tEmployeeCustomSearch WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sHrm.tEmployeeDesignationMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sHrm.tEmployeeFamilyDetails WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sHrm.tEmployeeGradeMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sHrm.tEmployeeImages WHERE CAST(LEFT(EmployeeId, 4) AS BIGINT) = @rOrgId
DELETE FROM sHrm.tEmployeeJobDetail WHERE CAST(LEFT(EmployeeId, 4) AS BIGINT) = @rOrgId
DELETE FROM sHrm.tEmployeeMaster WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sHrm.tEmployeePersonalDetail WHERE CAST(LEFT(EmployeeId, 4) AS BIGINT) = @rOrgId
DELETE FROM sHrm.tEmployeePreviousEmployment WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sHrm.tEmployeeStatutoryAndOther WHERE CAST(LEFT(EmployeeId, 4) AS BIGINT) = @rOrgId
DELETE FROM sHrm.tEmployeeTypeMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sHrm.tGuestMaster WHERE OrgId = @rOrgId
DELETE FROM sHrm.tLeave WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sHrm.tLeaveTypeGradeSettings WHERE CAST(LEFT(OrgId, 4) AS SMALLINT) = @rOrgId
DELETE FROM sHrm.tLeaveTypeMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sHrm.tWorkingDays WHERE CAST(LEFT(BatchId, 4) AS INT) = @rOrgId
DELETE FROM sIp.tBinMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sIp.tItemStandardPriceLog WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sIp.tStockItemBin WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sIp.tStockItemPlanning WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sIp.tStockItemPurchaseUom WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sIp.tWeightedAverageCostLog WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sLibrary.tAssignOrganization WHERE OrgId = @rOrgId
DELETE FROM sLibrary.tBookLog WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sLibrary.tBookLogPenalty WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sLibrary.tBooksForClass WHERE CAST(LEFT(ClassId, 4) AS INT) = @rOrgId
DELETE FROM sLibrary.tIssueSetting WHERE CAST(LEFT(SourceId, 4) AS BIGINT) = @rOrgId
DELETE FROM sOmr.tAnswerKeySetup WHERE CAST(LEFT(ExamDetailId, 4) AS INT) = @rOrgId
DELETE FROM sOmr.tExamSetup WHERE CAST(LEFT(ExamDetailId, 4) AS INT) = @rOrgId
DELETE FROM sOmr.tScanHistory WHERE CAST(LEFT(AcademicId, 4) AS BIGINT) = @rOrgId
DELETE FROM sOmr.tSetting WHERE CAST(LEFT(OrgId, 4) AS SMALLINT) = @rOrgId
DELETE FROM sOmr.tSheetCallibration WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sOmr.tTempAnsKeyAnalysis WHERE CAST(LEFT(ExamDetailId, 4) AS INT) = @rOrgId
DELETE FROM sOrderEntry.tIssueReturn WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sOrderEntry.tIssueReturnDetail WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sOrderEntry.tOrderEntry WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sOrderEntry.tOrderEntryBatch WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sOrderEntry.tServiceItemTypeMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tFirstAidEmoloyee WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sProcurement.tFirstAidEmployeeDetail WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sProcurement.tFirstAidInspection WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sProcurement.tFirstAidInspectionDetail WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sProcurement.tGoodsReceiptNote WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tGoodsReceiptNoteBatch WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tGoodsReceiptNoteDetail WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tGoodsReceiptNoteReturn WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tGoodsReceiptNoteReturnBatch WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tGoodsReceiptNoteReturnDetail WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tPurchaseOrder WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sProcurement.tPurchaseOrderDetail WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sProcurement.tStockAdjustment WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockAdjustmentBatch WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockAdjustmentDetail WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockAudit WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockAuditDetail WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockBatch WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockFreezeAdjustment WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockFreezeAdjustmentDetail WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockInward WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockInwardBatch WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockInwardDetail WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockInwardRequest WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockInwardRequestDetail WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockInwardReturn WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockInwardReturnBatch WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockIssue WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockIssueBatch WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockIssueDetail WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockIssueRequest WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockIssueRequestDetail WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockItemFreeze WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sProcurement.tStockTransfer WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockTransferBatch WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockTransferDetail WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockTransferRequest WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockTransferRequestDetail WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sProcurement.tStockVerification WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sProcurement.tStockVerificationApproval WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sSecurity.tConfiguration WHERE CAST(LEFT(OrgId, 4) AS SMALLINT) = @rOrgId
DELETE FROM sSecurity.tDivisionPermission WHERE CAST(LEFT(UserId, 4) AS BIGINT) = @rOrgId
DELETE FROM sSecurity.tLdapUser WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sSecurity.tUser WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sSecurity.tUserAccess  WHERE CAST(LEFT(UserId, 4) AS int) = @rOrgId
DELETE FROM sSecurity.tUserFavoriteScreen WHERE CAST(LEFT(UserId, 4) AS BIGINT) = @rOrgId
DELETE FROM sSecurity.tUserLocation WHERE CAST(LEFT(StoreLocationId, 4) AS INT) = @rOrgId
DELETE FROM sSecurity.tUserLog WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sSecurity.tUserOrganization WHERE CAST(LEFT(OrgId, 4) AS SMALLINT) = @rOrgId
DELETE FROM sSecurity.tUserPreference WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sSecurity.tUserProfile WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sSecurity.tUserRole WHERE CAST(LEFT(UserId, 4) AS INT) = @rOrgId
DELETE FROM sSetup.tBatchClass WHERE CAST(LEFT(ClassId, 4) AS INT) = @rOrgId
DELETE FROM sSetup.tBatchMaster WHERE OrgId = @rOrgId
DELETE FROM sSetup.tClassMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sSetup.tClassSubject WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sSetup.tDepartmentMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sSetup.tDivisionMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
 
DELETE FROM sSetup.tReportConfiguration WHERE OrgId = @rOrgId
DELETE FROM sSetup.tReportSetting WHERE CAST(LEFT(OrgId, 4) AS SMALLINT) = @rOrgId
DELETE FROM sSetup.tSubjectMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sSetup.tTermMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sStudent.tAcademic WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sStudent.tAcademicDetail WHERE CAST(LEFT(AcademicId, 4) AS BIGINT) = @rOrgId
DELETE FROM sStudent.tStudentAdmission WHERE CAST(LEFT(StudentId, 4) AS BIGINT) = @rOrgId
DELETE FROM sStudent.tStudentCommunication WHERE CAST(LEFT(StudentId, 4) AS BIGINT) = @rOrgId
DELETE FROM sStudent.tStudentCustomSearch WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sStudent.tStudentFamily WHERE CAST(LEFT(StudentId, 4) AS BIGINT) = @rOrgId
DELETE FROM sStudent.tStudentHealth WHERE CAST(LEFT(StudentId, 4) AS BIGINT) = @rOrgId
DELETE FROM sStudent.tStudentImages WHERE CAST(LEFT(StudentId, 4) AS BIGINT) = @rOrgId
DELETE FROM sStudent.tStudentMaster WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sStudent.tStudentOther WHERE CAST(LEFT(StudentId, 4) AS BIGINT) = @rOrgId
DELETE FROM sSupport.tContactMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sSupport.tContactTypeMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sSupport.tImageMaster WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sSupport.tImageTypeMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sSupport.tLinkMaster WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sSupport.tVideoMaster WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sSupport.tVideoTypeMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sTax.tTaxRateSetup WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sTimetable.tDivisionWorkLoad WHERE CAST(LEFT(DivisionId, 4) AS INT) = @rOrgId
DELETE FROM sTimetable.tProxySetting WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sTimetable.tTeacherWorkLoad WHERE CAST(LEFT(DivisionId, 4) AS INT) = @rOrgId
DELETE FROM sTimetable.tTimeSetup WHERE CAST(LEFT(BatchId, 4) AS INT) = @rOrgId
DELETE FROM sTimetable.tTimeTable WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sTimetable.tTimeTableSchema WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sTransport.tAssignRoute WHERE CAST(LEFT(SourceId, 4) AS BIGINT) = @rOrgId
DELETE FROM sAssessment.tCCECSExamScore WHERE CAST(LEFT(ExamId, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tCCEFAExamScore WHERE CAST(LEFT(ExamId, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tExamCombineTerm WHERE CAST(LEFT(ExamId, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tGradeSujbectExamScore WHERE CAST(LEFT(ExamId, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tStudentRemark WHERE CAST(LEFT(ExamId, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tSubjectIndicatorMaster WHERE OrgId = @rOrgId
DELETE FROM sAssessment.tTempAllExam
DELETE FROM sAssessment.tTempCoScholasticExam

DELETE FROM sFee.tAccountMapping WHERE CAST(LEFT(FeeSetupId, 4) AS INT) = @rOrgId
DELETE FROM sFee.tDepositAdd WHERE CAST(LEFT(StudentId, 4) AS BIGINT) = @rOrgId
DELETE FROM sFee.tDepositAdjustment WHERE CAST(LEFT(StudentId, 4) AS BIGINT) = @rOrgId
DELETE FROM sFee.tDepositBalance WHERE CAST(LEFT(ClassId, 4) AS INT) = @rOrgId
DELETE FROM sFee.tDepositRefund WHERE CAST(LEFT(StudentId, 4) AS BIGINT) = @rOrgId
DELETE FROM sScholarship.tScholarshipEligibleStudent WHERE CAST(LEFT(AcademicId, 4) AS BIGINT) = @rOrgId
DELETE FROM sScholarship.tScholarshipMaster WHERE CAST(LEFT(BatchId, 4) AS BIGINT) = @rOrgId
DELETE FROM sScholarship.tScholarshipReceive WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sScholarship.tScholarshipStudentPaid WHERE CAST(LEFT(AcademicId, 4) AS BIGINT) = @rOrgId
DELETE FROM sSetup.tYearChangeDivisionMapping WHERE CAST(LEFT(OldDivisionId, 4) AS INT) = @rOrgId
DELETE FROM sAcademic.tClasswork WHERE CAST(LEFT(BatchId, 4) AS INT) = @rOrgId
DELETE FROM sAcademic.tLessonPlan WHERE CAST(LEFT(BatchId, 4) AS INT) = @rOrgId
DELETE FROM sAcademic.tLessonPlanResource WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sAcademic.tTopicMaster WHERE OrgId = @rOrgId
DELETE FROM sCertificate.tPrintTemplate WHERE OrgId = @rOrgId
DELETE FROM sCertificate.tPrintTemplateStudent WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sHostel.tStudentGatePass WHERE CAST(LEFT(StudentId, 4) AS INT) = @rOrgId
DELETE FROM sFee.tFeeHeadOrgwise WHERE OrgId = @rOrgId
DELETE FROM sFee.tReceiptBookOrgwise WHERE OrgId = @rOrgId

DELETE FROM sAcademic.tTeacherSubject WHERE CAST(LEFT(EmployeeId, 4) AS INT) = @rOrgId
DELETE FROM sAcademic.tTeacherSubjectDivision WHERE CAST(LEFT(EmployeeId, 4) AS INT) = @rOrgId
DELETE FROM sAdmission.tAdmissionCriteriaMaster WHERE OrgId = @rOrgId
--DELETE FROM sAdmission.tAdmissionCriteriaStudent WHERE CAST(LEFT(AdmissionSetupId, 4) AS INT) = @rOrgId
DELETE FROM sAdmission.tAdmissionFormSelected WHERE CAST(LEFT(AdmissionFormId, 4) AS INT) = @rOrgId
DELETE FROM sAdmission.tAdmissionSetup WHERE CAST(LEFT(BatchId, 4) AS INT) = @rOrgId
DELETE FROM sAttendance.tFilterCriteria WHERE OrgId = @rOrgId
DELETE FROM sAttendance.tFilterCriteriaDetail WHERE CAST(LEFT(FilterCriteriaId, 4) AS INT) = @rOrgId
DELETE FROM sAttendance.tYearlyRegister WHERE CAST(LEFT(AcademicId, 4) AS INT) = @rOrgId
DELETE FROM sFee.tWinfeeDetail WHERE CAST(LEFT(AcademicId, 4) AS INT) = @rOrgId
DELETE FROM sHrm.tEmployeeAttendanceRegister WHERE CAST(LEFT(EmployeeId, 4) AS INT) = @rOrgId
DELETE FROM sHrm.tEmployeeICardOrg WHERE OrgId = @rOrgId
DELETE FROM sSecurity.tUserDashlet WHERE CAST(LEFT(UserId, 4) AS INT) = @rOrgId
DELETE FROM sStudent.tICardOrg WHERE OrgId = @rOrgId

DELETE FROM sAdmission.tAdmissionSetupStudent WHERE CAST(LEFT(AdmissionSetupId, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tCCECSForClass WHERE CAST(LEFT(ClassId, 4) AS INT) = @rOrgId
DELETE FROM sAudit.tActivityLog WHERE CAST(LEFT(OrgId, 4) AS INT)  = @rOrgId
DELETE FROM sAudit.tReceiptDelete WHERE BatchId = @rOrgId
DELETE FROM sAudit.tReceiptDetailDelete WHERE CAST(LEFT(ReceiptId, 4) AS INT) = @rOrgId

DELETE FROM sCommunication.tViewResult WHERE CAST(LEFT(AcademicId, 4) AS INT) = @rOrgId
DELETE FROM sCommunication.tViewResultDetail WHERE CAST(LEFT(ExamId, 4) AS INT) = @rOrgId
DELETE FROM sSetup.tOrgInfo WHERE OrgId = @rOrgId

DELETE FROM sStudent.tStudentCustomTag WHERE CAST(LEFT(StudentId, 4) AS INT) = @rOrgId

DELETE FROM sTimetable.tAssignOrg WHERE OrgId = @rOrgId

DELETE FROM sAdmission.tAdmissionCriteria WHERE CAST(LEFT(BatchId, 4) AS INT) = @rOrgId
DELETE FROM sAdmission.tAdmissionFormFamilyDetail WHERE CAST(LEFT(AdmissionFormId, 4) AS BIGINT) = @rOrgId
DELETE FROM sAdmission.tAdmissionFormParentAcademic WHERE CAST(LEFT(AdmissionFormId, 4) AS BIGINT) = @rOrgId
DELETE FROM sAdmission.tAdmissionFormReference WHERE CAST(LEFT(AdmissionFormId, 4) AS BIGINT) = @rOrgId
DELETE FROM sAdmission.tAdmissionFormSibling WHERE CAST(LEFT(AdmissionFormId, 4) AS BIGINT) = @rOrgId
DELETE FROM sAdmission.tAdmissionFormStudentAcademic WHERE CAST(LEFT(AdmissionFormId, 4) AS BIGINT) = @rOrgId
DELETE FROM sAssessment.tCCESubjectActivityMaster WHERE OrgId = @rOrgId
DELETE FROM sAssessment.tCCESubjectSubActivityExamScore WHERE CAST(LEFT(ExamId, 4) AS BIGINT) = @rOrgId
DELETE FROM sAssessment.tCCESubjectSubActivityMaster WHERE OrgId = @rOrgId
DELETE FROM sAssessment.tResultAnalysis WHERE CAST(LEFT(ExamId, 4) AS BIGINT) = @rOrgId
DELETE FROM sAssessment.tResultAnalysisDetail WHERE CAST(LEFT(ExamId, 4) AS BIGINT) = @rOrgId
DELETE FROM sCommon.tReportOrg WHERE ScopeSourceId = @rOrgId AND ScopeSourceTypeId = 2021
DELETE FROM sAssessment.tCCESubjectSubActivityIndicatorMaster WHERE  OrgId = @rOrgId
DELETE FROM sAssessment.tResultAnalysisDivision WHERE CAST(LEFT(ExamId, 4) AS BIGINT) = @rOrgId
DELETE FROM sAssessment.tResultAnalysisDivisionDetail WHERE CAST(LEFT(ExamId, 4) AS BIGINT) = @rOrgId
DELETE FROM sFrontOffice.tStudentGatePass WHERE OrgId = @rOrgId
DELETE FROM sGoogle.tOrgCalander WHERE OrgId = @rOrgId
DELETE FROM sGoogle.tOrgDrive WHERE OrgId = @rOrgId
DELETE FROM sGoogle.tOrgYouTube WHERE OrgId = @rOrgId
DELETE FROM sHrm.tLeaveBalance WHERE CAST(LEFT(BatchId, 4) AS BIGINT) = @rOrgId
DELETE FROM sHrm.tLeaveBalanceAdd WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sTimetable.tAssignTeacher WHERE CAST(LEFT(EmployeeId, 4) AS BIGINT) = @rOrgId
DELETE FROM sTimetable.tDivision WHERE CAST(LEFT(OrgId, 4) AS INT) = @rOrgId
DELETE FROM sTimetable.tSubject WHERE CAST(LEFT(OrgId, 4) AS INT) = @rOrgId
DELETE FROM sTimetable.tSubjectTimeOff WHERE CAST(LEFT(SubjectId, 4) AS INT) = @rOrgId
DELETE FROM sTimetable.tTeacher WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sHrm.tEmployeeQualification WHERE OrgId = @rOrgId
DELETE FROM sSecurity.tUserDashboardNotification WHERE CAST(LEFT(UserId, 4) AS BIGINT) = @rOrgId
DELETE FROM sTransport.tAssignTransportOrg WHERE OrgId = @rOrgId

--DELETE FROM sFee.tOnlinePayment WHERE  CAST(LEFT(BatchId, 4) AS INT) = @rOrgId
--DELETE FROM sFee.tOnlinePaymentDetail WHERE  CAST(LEFT(OnlinePaymentId, 4) AS BIGINT) = @rOrgId
DELETE FROM sFee.tReceiptRefund WHERE  CAST(LEFT(BatchId, 4) AS INT) = @rOrgId
DELETE FROM sFee.tReceiptRefundDetail WHERE  CAST(LEFT(ReceiptRefundId, 4) AS BIGINT) = @rOrgId
DELETE FROM sIp.tUniformIssue WHERE  CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
DELETE FROM sIp.tUniformIssue WHERE  CAST(LEFT(AcademicId, 4) AS BIGINT) = @rOrgId
DELETE FROM sIp.tUniformItemForClass WHERE  CAST(LEFT(ClassId, 4) AS INT) = @rOrgId
DELETE FROM sIp.tUniformOrder WHERE  CAST(LEFT(AcademicId, 4) AS BIGINT) = @rOrgId
DELETE FROM sIp.tUniformOrderDetail WHERE  CAST(LEFT(UniformOrderId, 4) AS BIGINT) = @rOrgId
 
DELETE FROM sFee.tFeeInvoice WHERE CAST(LEFT(BatchId, 4) AS INT) = @rOrgId
DELETE FROM sFee.tFeeInvoiceDetail WHERE CAST(LEFT(FeeInvoiceId, 4) AS INT) = @rOrgId
DELETE FROM sFee.tOnlineFee WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sFee.tOnlineFeeDetail WHERE CAST(LEFT(OnlineFeeId, 4) AS INT) = @rOrgId
DELETE FROM sHrm.tCertificateEmployment WHERE CAST(LEFT(EmployeeId, 4) AS INT) = @rOrgId
DELETE FROM sOp.tPaymentTransaction WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sSetup.tOrgImages WHERE OrgId = @rOrgId
DELETE FROM sStudent.tStudentadditional WHERE CAST(LEFT(StudentId, 4) AS INT) = @rOrgId
DELETE FROM sStudent.tStudentBatchStatus WHERE CAST(LEFT(AcademicId, 4) AS INT) = @rOrgId

DELETE FROM sCommon.tPrefixConfig WHERE OrgId = @rOrgId
DELETE FROM sConfig.tDisplayFieldOrg WHERE OrgId = @rOrgId
DELETE FROM sHrm.tCertificateExperience WHERE OrgId = @rOrgId
DELETE FROM sHrm.tCertificateRelieving WHERE OrgId = @rOrgId
DELETE FROM sRFID.tAttendance WHERE OrgId = @rOrgId
DELETE FROM sRFID.tRegistration WHERE CAST(LEFT(RegistrtionId, 4) AS INT) = @rOrgId
DELETE FROM sSetup.tBatchClassLecture WHERE CAST(LEFT(BatchId, 4) AS INT) = @rOrgId
DELETE FROM sStudent.tStudentSibling WHERE CAST(LEFT(StudentId, 4) AS INT) = @rOrgId
DELETE FROM sStudent.tTranscriptAssessment WHERE CAST(LEFT(AcademicId, 4) AS INT) = @rOrgId
DELETE FROM sTally.tStudentLedgerMapping WHERE CAST(LEFT(StudentId, 4) AS INT) = @rOrgId

/* -- Date : 13/06/2016 -- */
DELETE FROM sActivity.tEventAttendance WHERE CAST(LEFT(SourceId, 4) AS INT) = @rOrgId
DELETE FROM sCanteen.tAssignOrganization WHERE OrgId = @rOrgId
DELETE FROM sCommunication.tRegisteredUsers WHERE OrgId = @rOrgId
DELETE FROM sStudent.tCollegeDetail WHERE CAST(LEFT(StudentId, 4) AS INT) = @rOrgId

-- 15.7.2016
DELETE FROM sFrontOffice.tInwardRegister WHERE OrgId = @rOrgId
DELETE FROM sFrontOffice.tOutwardRegister WHERE OrgId = @rOrgId
DELETE FROM sSetup.tSubjectGroupMaster WHERE OrgId = @rOrgId

/* -- Date : 12/08/2016 -- */
DELETE FROM sAssessment.tExamCombineSubject WHERE CAST(LEFT(ExamId, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tResultDetailOther WHERE CAST(LEFT(ExamId, 4) AS INT) = @rOrgId
DELETE FROM sSetup.tDivisionInchargeEmployee WHERE CAST(LEFT(DivisionId, 4) AS INT) = @rOrgId
DELETE FROM sStudent.tEnrollmentStatusOrg WHERE OrgId = @rOrgId
DELETE FROM sStudent.tEnrollmentStatusStudent WHERE CAST(LEFT(AcademicId, 4) AS INT) = @rOrgId
DELETE FROM sStudent.tLinkSibling WHERE CAST(LEFT(StudentId, 4) AS INT) = @rOrgId

/* -- Date : 17/09/2016 -- */

DELETE FROM sAssessment.tTempAllExamSubjectActivity WHERE CAST(LEFT(AcademicId, 4) AS INT) = @rOrgId
DELETE FROM sAttendance.tTempAttendance WHERE CAST(LEFT(AcademicId, 4) AS INT) = @rOrgId

/* -- Date : 05/10/2016 -- */

DELETE FROM sAdmission.tAdmissionInquiryFollowup WHERE CAST(LEFT(Id, 4) AS BIGINT) = @rOrgId
--DELETE FROM sAssessment.tStaticCCECSGroupMaster WHERE OrgId = @rOrgId
--DELETE FROM sAssessment.tStaticCCECSMaster WHERE OrgId = @rOrgId

/* -- Date : 20/10/2016 -- */

DELETE FROM sAssessment.tOptionalCCECS WHERE CAST(LEFT(AcademicId, 4) AS BIGINT) = @rOrgId

/* -- Date : 24/11/2016 -- */
DELETE FROM sBiometric.tMonthTrans WHERE CAST(LEFT(EmployeeId, 4) AS BIGINT) = @rOrgId

/* -- Date : 23/12/2016 -- */
DELETE FROM sAdmission.tAdmissionFormReceipt WHERE CAST(LEFT(BatchId, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tCCEExamStatus WHERE CAST(LEFT(BatchId, 4) AS INT) = @rOrgId
DELETE FROM sAttendance.tAttendanceGroup WHERE CAST(LEFT(BatchId, 4) AS INT) = @rOrgId
DELETE FROM sAttendance.tAttendanceGroupStudent WHERE CAST(LEFT(AcademicId, 4) AS BIGINT) = @rOrgId
DELETE FROM sFee.tAccountPosting WHERE CAST(LEFT(AccountId, 4) AS INT) = @rOrgId
DELETE FROM sHrm.tLeaveBalanceAddRequest WHERE CAST(LEFT(BatchId, 4) AS INT) = @rOrgId

/* -- Date : 14/02/2017 -- */

DELETE FROM sCRM.tChannelPartnerMaster WHERE OrgId = @rOrgId
DELETE FROM sCRM.tInstituteGroupMaster WHERE OrgId = @rOrgId
DELETE FROM sCRM.tInstituteMaster WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sSetup.tOrganizationOtherDetail WHERE OrgId = @rOrgId

DELETE FROM sAppraisal.tConduct WHERE CAST(LEFT(BatchId, 4) AS INT) = @rOrgId
DELETE FROM sAssessment.tAllBatchClassGroup WHERE OrgId= @rOrgId
DELETE FROM sAssessment.tAllBatchResult WHERE CAST(LEFT(StudentId, 4) AS BIGINT) = @rOrgId
DELETE FROM sAssessment.tAllBatchSubject WHERE OrgId= @rOrgId
DELETE FROM sHrm.tEmployeeTravel WHERE OrgId= @rOrgId


DELETE FROM sLoan.tLoanDetail WHERE OrgId= @rOrgId
DELETE FROM sLoan.tLoanEMI WHERE CAST(LEFT(LoanDetailId, 4) AS BIGINT) = @rOrgId
DELETE FROM sLoan.tLoanPayrollMapping WHERE OrgId= @rOrgId
DELETE FROM sLoan.tLoanTypeMaster WHERE OrgId= @rOrgId
DELETE FROM sPayroll.tPayrollCustomSearch WHERE OrgId= @rOrgId
DELETE FROM sErp.tChannelMaster WHERE OrgId = @rOrgId

DELETE FROM sAccounting.tExpenseDetail WHERE CAST(LEFT(ExpenseId, 4) AS int) = @rOrgId
DELETE FROM sAccounting.tExpenseSubGroupMaster WHERE OrgId= @rOrgId
DELETE FROM sAppraisal.tAppraisalTypeMaster WHERE CAST(LEFT(BatchId, 4) AS int) = @rOrgId
DELETE FROM sAppraisal.tBusinessHeadsRatingStatus WHERE CAST(LEFT(AppraisalTypeId, 4) AS int) = @rOrgId
DELETE FROM sAppraisal.tKPAEmployee WHERE CAST(LEFT(BatchId, 4) AS int) = @rOrgId
DELETE FROM sAppraisal.tKPAEmployeeDetail WHERE CAST(LEFT(KPAEmployeeId, 4) AS bigint) = @rOrgId
DELETE FROM sAppraisal.tKPAEmployeeRating WHERE CAST(LEFT(KPAEmployeeId, 4) AS bigint) = @rOrgId
DELETE FROM sAppraisal.tKPAEmployeeRatingDetail WHERE CAST(LEFT(KPAEmployeeRatingId, 4) AS bigint) = @rOrgId
DELETE FROM sPoliciesForms.tFolderFile WHERE OrgId = @rOrgId
DELETE FROM sPoliciesForms.tFolderFilePermission WHERE CAST(LEFT(FolderFileId, 4) AS int) = @rOrgId


DELETE FROM sFee.tFeeMainGroupOptionalFee WHERE OrgId = @rOrgId
DELETE FROM sFee.tFeeStructureDetail WHERE CAST(LEFT(FeeStructureId, 4) AS int) = @rOrgId
DELETE FROM sFee.tFeeStructureMaster WHERE OrgId = @rOrgId
DELETE FROM sFrontOffice.tEmployeeGatePass WHERE OrgId = @rOrgId
DELETE FROM sHostel.tHostelStudentGatePass WHERE CAST(LEFT(StudentId, 4) AS bigint) = @rOrgId
DELETE FROM sHrm.tLeaveBalanceLog WHERE CAST(LEFT(EmployeeId, 4) AS bigint) = @rOrgId
DELETE FROM sTransport.tAttendanceRegister WHERE CAST(LEFT(SourceId, 4) AS bigint) = @rOrgId

DELETE FROM sAssessment.tCCESubjectActivityExamScore WHERE CAST(LEFT(AcademicId, 4) AS bigint) = @rOrgId
DELETE FROM sAssessment.tTempAll_Exam WHERE CAST(LEFT(AcademicId, 4) AS bigint) = @rOrgId
DELETE FROM sCanteen.tAttendanceForBreak WHERE CAST(LEFT(SourceId, 4) AS bigint) = @rOrgId
DELETE FROM sCanteen.tRegisterForBreak WHERE CAST(LEFT(SourceId, 4) AS bigint) = @rOrgId
DELETE FROM sCertificate.tTransfer WHERE OrgId = @rOrgId
DELETE FROM sCanteen.tRegisterForBreak WHERE CAST(LEFT(SourceId, 4) AS bigint) = @rOrgId

DELETE FROM sErp.tLeadFollowup WHERE CAST(LEFT(LeadId, 4) AS int) = @rOrgId
DELETE FROM sErp.tLeadMaster WHERE OrgId = @rOrgId


DELETE FROM sAdmission.tAdmissionHSCDetail WHERE CAST(LEFT(AdmissionFormId, 4) AS bigint) = @rOrgId
DELETE FROM sBiometric.tEmployeeCheckIn WHERE CAST(LEFT(EmployeeId, 4) AS bigint) = @rOrgId

DELETE FROM sBiometric.tEmployeeLateStatus WHERE CAST(LEFT(EmployeeId, 4) AS bigint) = @rOrgId

DELETE FROM sCommon.tSubmitDocumentMaster WHERE OrgId = @rOrgId
DELETE FROM sStudent.tStudentDocument WHERE CAST(LEFT(SubmitDocumentMasterId, 4) AS int) = @rOrgId
DELETE FROM sTimetable.tAnnualSubjectWorkLoad WHERE CAST(LEFT(BatchId, 4) AS int) = @rOrgId

DELETE FROM sFee.tReceiptDeleted WHERE CAST(LEFT(BatchId, 4) AS int) = @rOrgId
DELETE FROM sFee.tReceiptDetailDeleted WHERE CAST(LEFT(ReceiptId, 4) AS bigint) = @rOrgId

DELETE FROM sAttendance.tStudentLeaveRequest WHERE CAST(LEFT(AcademicId, 4) AS bigint) = @rOrgId
DELETE FROM sInfrastructure.tResourceMaster WHERE CAST(LEFT(Id, 4) AS int) = @rOrgId
DELETE FROM sInfrastructure.tResourceRequest WHERE CAST(LEFT(Id, 4) AS bigint) = @rOrgId

DELETE FROM sAlumni.tRegistration WHERE OrgId = @rOrgId
DELETE FROM sBiometric.tMonthlyStatusUpdate WHERE CAST(LEFT(EmployeeId, 4) AS bigint) = @rOrgId
DELETE FROM sErp.tDSR WHERE OrgId = @rOrgId
DELETE FROM sErp.tDSRWorkTypeMaster WHERE OrgId = @rOrgId
DELETE FROM sErp.tProjectMaster WHERE OrgId = @rOrgId
DELETE FROM sFee.tFeeFollowup WHERE CAST(LEFT(AcademicId, 4) AS bigint) = @rOrgId
 
DELETE FROM sActivity.tWeekOffSetting WHERE CAST(LEFT(SourceId, 4) AS bigint) = @rOrgId
DELETE FROM sAssessment.tAllBatchResultCoScholestic WHERE CAST(LEFT(StudentId, 4) AS bigint) = @rOrgId
DELETE FROM sHrm.tEmployeeDocument WHERE CAST(LEFT(EmployeeId, 4) AS bigint) = @rOrgId
DELETE FROM sStudent.tChangeIdLog WHERE CAST(LEFT(StudentId, 4) AS bigint) = @rOrgId
DELETE FROM sAlert.tAssociateRecipient WHERE CAST(LEFT(ForUserId, 4) AS bigint) = @rOrgId

DELETE FROM sAcademic.tPreLessonPlan WHERE CAST(LEFT(BatchId, 4) AS int) = @rOrgId
DELETE FROM sAssessment.tAllBatchResultGrade WHERE CAST(LEFT(StudentId, 4) AS bigint) = @rOrgId

DELETE FROM sVts.tvtsClassMaster WHERE CAST(LEFT(ClassId, 4) AS int) = @rOrgId
DELETE FROM sVts.tvtsOrganizationMaster WHERE OrgId = @rOrgId
DELETE FROM sVts.tvtsStudent WHERE CAST(LEFT(StudentId, 4) AS bigint) = @rOrgId
DELETE FROM sErp.tLeadChannelPartnerMaster WHERE  OrgId = @rOrgId


DELETE FROM sAlumni.tStudentMapping WHERE CAST(LEFT(StudentId, 4) AS bigint) = @rOrgId
DELETE FROM sErp.tUserFeedback WHERE CAST(LEFT(UserId, 4) AS bigint) = @rOrgId
DELETE FROM sFee.tFeeExpense WHERE CAST(LEFT(AcademicId, 4) AS bigint) = @rOrgId


DELETE FROM sAdmission.tAdmissionCriteriaForm WHERE CAST(LEFT(BatchId, 4) AS int) = @rOrgId
DELETE FROM sAdmission.tAdmissionFormField WHERE CAST(LEFT(AdmissionFormId, 4) AS bigint) = @rOrgId
DELETE FROM sAssessment.tGradeSetClassSubjectDetail WHERE CAST(LEFT(Id, 4) AS int) = @rOrgId
DELETE FROM sAssessment.tGradeSetClassSubjectMaster WHERE OrgId = @rOrgId
DELETE FROM sBiometric.BioDetailMapping WHERE OrgId = @rOrgId
DELETE FROM sBiometric.tLog WHERE OrgId = @rOrgId

DELETE FROM sCertificate.tOtherCertificate WHERE OrgId = @rOrgId
DELETE FROM sWorkflow.tApprovalAction WHERE CAST(LEFT(ActionUserId, 4) AS bigint) = @rOrgId
DELETE FROM sWorkflow.tApprovalRequest WHERE CAST(LEFT(BatchId, 4) AS int) = @rOrgId
DELETE FROM sWorkflow.tFunctionalityApprovingAuthority WHERE OrgId = @rOrgId
DELETE FROM sWorkflow.tFunctionalityApprovingDepartment WHERE OrgId = @rOrgId

DELETE FROM sBiometric.tProcess WHERE CAST(LEFT(SourceId, 4) AS bigint) = @rOrgId
DELETE FROM sAssessment.tConfiguration WHERE OrgId = @rOrgId

DELETE FROM sAcademic.tHomeworkCheck WHERE CAST(LEFT(AcademicId, 4) AS bigint) = @rOrgId

DELETE FROM sAdmission.tAdmissionCriteriaFee WHERE CAST(LEFT(BatchId, 4) AS int) = @rOrgId
DELETE FROM sAdmission.tAdmissionFormFee WHERE CAST(LEFT(AdmissionFormId, 4) AS bigint) = @rOrgId


DELETE FROM sFee.tFeeOpening WHERE CAST(LEFT(AcademicId, 4) AS bigint) = @rOrgId
DELETE FROM sTransport.tChangeRouteLog WHERE CAST(LEFT(SourceId, 4) AS bigint) = @rOrgId

DELETE FROM sAcademic.tClassworkConfig WHERE OrgId = @rOrgId
DELETE FROM sBiometric.tPunchData WHERE CAST(LEFT(StudentID, 4) AS bigint) = @rOrgId
DELETE FROM sErp.tCallLog WHERE CAST(LEFT(EmployeeId, 4) AS bigint) = @rOrgId
DELETE FROM sErp.tMarketingActivityPlan WHERE CAST(LEFT(EmployeeId, 4) AS bigint) = @rOrgId
--DELETE FROM tMarketingActivityStatus WHERE CAST(LEFT(EmployeeId, 4) AS bigint) = @rOrgId
DELETE FROM sErp.tTripLog WHERE CAST(LEFT(EmployeeId, 4) AS bigint) = @rOrgId


DELETE FROM sAudit.HistoryLog WHERE CAST(LEFT(SourceId, 4) AS bigint) = @rOrgId
DELETE FROM sAudit.tOperationLog WHERE CAST(LEFT(SourceId, 4) AS int) = @rOrgId

--DELETE FROM sErp.tCustomerTicket WHERE OrgId= @rOrgId
DELETE FROM sPayroll.tPayProcessEmployeeRemark WHERE CAST(LEFT(EmployeeId, 4) AS int) = @rOrgId


/*==============================*/

DELETE FROM sSetup.tOrganizationMaster WHERE Id = @rOrgId
DELETE FROM sCommon.tTableRowId WHERE OrgId = @rOrgId


DELETE FROM sAlert.tAlertLogHistory WHERE OrgId= @rOrgId
DELETE FROM sAlert.tNotificationLogHistory WHERE CAST(LEFT(CreatedUserId, 4) AS bigint) = @rOrgId
DELETE FROM sHrm.tBatchEmployeeCommunications WHERE CAST(LEFT(EmployeeId, 4) AS bigint) = @rOrgId
DELETE FROM sHrm.tBatchEmployeeFamilyDetails WHERE CAST(LEFT(EmployeeId, 4) AS bigint) = @rOrgId
DELETE FROM sHrm.tBatchEmployeeJobDetail WHERE OrgId= @rOrgId
DELETE FROM sHrm.tBatchEmployeeMaster WHERE OrgId= @rOrgId
DELETE FROM sHrm.tBatchEmployeePersonalDetail WHERE OrgId= @rOrgId
DELETE FROM sHrm.tBatchEmployeeStatutoryAndOther WHERE CAST(LEFT(EmployeeId, 4) AS bigint) = @rOrgId
DELETE FROM sSecurity.tUserLogHistory WHERE OrgId= @rOrgId
DELETE FROM sStudent.tAcademicStudent WHERE CAST(LEFT(AcademicId, 4) AS bigint) = @rOrgId
DELETE FROM sStudent.tAcademicStudentAdditional WHERE CAST(LEFT(AcademicId, 4) AS bigint) = @rOrgId
DELETE FROM sStudent.tAcademicStudentCommunication WHERE CAST(LEFT(AcademicId, 4) AS bigint) = @rOrgId
DELETE FROM sStudent.tAcademicStudentFamily WHERE CAST(LEFT(AcademicId, 4) AS bigint) = @rOrgId
DELETE FROM sStudent.tAcademicStudentHealth WHERE CAST(LEFT(AcademicId, 4) AS bigint) = @rOrgId
DELETE FROM sStudent.tAcademicStudentOther WHERE CAST(LEFT(AcademicId, 4) AS bigint) = @rOrgId
DELETE FROM sStudent.tStudentCustomFieldData WHERE CAST(LEFT(StudentId, 4) AS bigint) = @rOrgId
DELETE FROM sStudent.tStudentMasterDelete WHERE OrgId= @rOrgId

DELETE FROM sAudit.ChangeLog WHERE CAST(LEFT(SourceId, 4) AS bigint) = @rOrgId
DELETE FROM sHrm.tCertificateIncrement WHERE OrgId= @rOrgId
DELETE FROM sAudit.ChangeLog WHERE CAST(LEFT(ScreenId, 4) AS bigint) = @rOrgId

DELETE FROM sAcademic.tClassworkView WHERE CAST(LEFT(AcademicId, 4) AS bigint) = @rOrgId
DELETE FROM sBiometric.tPunchCount WHERE CAST(LEFT(SourceId, 4) AS bigint) = @rOrgId

DELETE FROM sFrontOffice.tOTPLog WHERE CAST(LEFT(CreatedUserId, 4) AS bigint) = @rOrgId
DELETE FROM sHrm.tLeaveDeduction WHERE CAST(LEFT(EmployeeId, 4) AS bigint) = @rOrgId


DELETE FROM sAdmission.tAdmissionCriteriaDocument WHERE CAST(LEFT(BatchId, 4) AS int) = @rOrgId
DELETE FROM sAdmission.tAdmissionStudentDocument WHERE CAST(LEFT(AdmissionFormId, 4) AS bigint) = @rOrgId
DELETE FROM sTimetable.tTimeTableTime WHERE CAST(LEFT(DivisionId, 4) AS int) = @rOrgId
DELETE FROM sHrm.tEmployeeWorkingDays WHERE CAST(LEFT(BatchId, 4) AS int) = @rOrgId

DELETE FROM sErp.tContactBookMaster WHERE OrgId= @rOrgId
DELETE FROM sErp.tEmployeeERPDetail WHERE CAST(LEFT(EmployeeId, 4) AS bigint) = @rOrgId
DELETE FROM sFrontOffice.tLostFoundRegisterScope WHERE CAST(LEFT(SourceId, 4) AS int) = @rOrgId
DELETE FROM sHrm.tEmployeeChangeHistory WHERE CAST(LEFT(EmployeeId, 4) AS bigint) = @rOrgId
DELETE FROM sCommon.tReportTemplate WHERE OrgId= @rOrgId
DELETE FROM sTransport.tBusRouteAssignedTo WHERE CAST(LEFT(SourceId, 4) AS bigint) = @rOrgId
DELETE FROM sWorkflow.tStudentDataChange WHERE CAST(LEFT(SourceId, 4) AS bigint) = @rOrgId

DELETE FROM sAcademic.tOnlineLearning WHERE CAST(LEFT(BatchId, 4) AS int) = @rOrgId
DELETE FROM sAcademic.tOnlineLearningAssignment WHERE CAST(LEFT(OnlineLearningId, 4) AS bigint) = @rOrgId
DELETE FROM sAcademic.tOnlineLearningAssignmentSubmit WHERE CAST(LEFT(OnlineLearningId, 4) AS bigint) = @rOrgId
DELETE FROM sAcademic.tOnlineLearningAttendance WHERE CAST(LEFT(OnlineLearningId, 4) AS bigint) = @rOrgId
DELETE FROM sAcademic.tOnlineLearningAttendanceLog WHERE CAST(LEFT(OnlineLearningId, 4) AS bigint) = @rOrgId
DELETE FROM sAcademic.tOnlineLearningReference WHERE CAST(LEFT(OnlineLearningId, 4) AS bigint) = @rOrgId
DELETE FROM sAcademic.tOnlineLearningResource WHERE CAST(LEFT(OnlineLearningId, 4) AS bigint) = @rOrgId
DELETE FROM sAcademic.tOnlineLearningScope WHERE CAST(LEFT(OnlineLearningId, 4) AS bigint) = @rOrgId
DELETE FROM sAcademic.tTempOnlineLearningScope WHERE CAST(LEFT(SourceId, 4) AS bigint) = @rOrgId
DELETE FROM sCommunication.tChat WHERE CAST(LEFT(FromSourceId, 4) AS bigint) = @rOrgId
DELETE FROM sCommunication.tTempChatGroupScope WHERE CAST(LEFT(MemberSourceId, 4) AS bigint) = @rOrgId


DELETE FROM sAcademic.tOnlineMeetingSettings WHERE CAST(LEFT(OnlineLearningId, 4) AS bigint) = @rOrgId
DELETE FROM sStudent.tDigitalForm WHERE OrgId= @rOrgId
DELETE FROM sStudent.tDigitalFormScope WHERE CAST(LEFT(DigitalFormId, 4) AS int) = @rOrgId
DELETE FROM sStudent.tDigitalFormField WHERE CAST(LEFT(DigitalFormId, 4) AS int) = @rOrgId
DELETE FROM sStudent.tDigitalFormStudent WHERE CAST(LEFT(DigitalFormId, 4) AS int) = @rOrgId

DELETE FROM sAcademic.tOnlineLearningView WHERE CAST(LEFT(AcademicId, 4) AS bigint) = @rOrgId

DELETE FROM sOp.tPaymentTransactionHistory WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROMsFee.tOnlineFeeHistory WHERE CAST(LEFT(Id, 4) AS INT) = @rOrgId
DELETE FROM sFee.tOnlineFeeDetailHistory WHERE CAST(LEFT(OnlineFeeId, 4) AS INT) = @rOrgId
DELETE FROM sCommunication.tStudentDocumentUpload WHERE CAST(LEFT(BatchId, 4) AS INT) = @rOrgId

DELETE FROM sFee.tFeeAllocationHistory WHERE CAST(LEFT(SourceId, 4) AS bigint) = @rOrgId

DELETE FROM sCommon.tUploadDocument WHERE CAST(LEFT(CreatedUserId, 4) AS bigint) = @rOrgId

DELETE FROM sFee.tFeeAllocationStudent WHERE CAST(LEFT(AcademicId, 4) AS bigint) = @rOrgId
DELETE FROM sFee.tFeeClassBatchStatus WHERE CAST(LEFT(BatchId, 4) AS int) = @rOrgId
DELETE FROM sHrm.tEmployeeBatch WHERE CAST(LEFT([EmployeeId], 4) AS bigint) = @rOrgId
DELETE FROM sCommunication.tEmployeeDocumentUpload WHERE OrgId = @rOrgId

DELETE FROM [sHrm].[tEmployeeCustomFieldData] WHERE CAST(LEFT([EmployeeId], 4) AS bigint) = @rOrgId

DELETE FROM [sCommunication].[tDailyThoughtAssign] WHERE OrgId= @rOrgId
DELETE FROM [sFee].[tEMandate] WHERE CAST(LEFT(Id, 4) AS bigint) = @rOrgId



--EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

	SELECT 1 AS Result
 
-- COMMIT TRANSACTION
		
--	END TRY
	
--	BEGIN CATCH 
--		ROLLBACK TRANSACTION
--		SELECT -1 AS Result
		
--	END CATCH
END
GO
PRINT N'Altering [sEExam].[pQuestionListGet]...';


GO
 

ALTER PROCEDURE [sEExam].[pQuestionListGet]
	 
	 @rId bigint = 0,
	 @rTopicId int,
	 @rClassSubjectId int,
	 @rClassId int,
	 @rDifficultyLevelId int,
	 @rDataBankId int,
	 @rQuestionTypeId int=0

	 
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON 

BEGIN 	 
	
	SELECT 
		
		QM.Id,
		QM.TopicId,
		DLM.Id AS DifficultyLevelId,
		ISNULL(QM.QuestionParagraphId,0)AS QuestionParagraphId,
		QM.QuestionDescription  AS QuestionDescription,
		--''''+QM.QuestionDescription+''''  AS QuestionDescription,
		ISNULL(QM.QuestionDescription2,'')AS QuestionDescription2,
		ISNULL(QP.QuestionParagraphDescription,'') AS QuestionParegraphDescription,	
		ISNULL(QP.QuestionParagraphDescription2,'') AS QuestionParegraphDescription2,												  
		QM.Marks,
		QM.IsObsolete,
		QM.OrderIndex,
		DLM.Title AS DifficultyLevel,
		CM.Title AS Class,
		VCS.Title AS SubjectName,
		TM.Title AS Topic,
		ISNULL(QM.GroupQuestionId,0) AS QuestionGroupId,
		ISNULL(QM.EntryModeId,0) AS EntryModeId,
		CASE WHEN QuestionParagraphId<>0 then 'With Para' else '' end AS WithPara,
		QM.Id,
		ISNULL(QM.QuestionTypeId,0)AS QuestionTypeId,
		SM.Title AS QuestionTypeTitle
		      
	FROM 
			sEExam.tQuestionMaster QM
		INNER JOIN sEExam.tDataTopicMaster TM
			ON QM.TopicId= TM.Id
		INNER JOIN sEExam.tDataSubjectMaster VCS
			ON TM.DataSubjectId=VCS.Id
		INNER JOIN sEExam.tDifficultyLevelMaster DLM
			ON DLM.Id=QM.DifficultyLevelId
		INNER JOIN sSetup.tClassGroupMaster CM ON CM.Id = TM.ClassGroupId	
		--INNER JOIN sSetup.vAffiliationClassGroup CM
		--	ON CM.Id=TM.ClassGroupId 
		LEFT JOIN sEExam.tQuestionParagraph QP ON Qp.Id = QM.QuestionParagraphId
		INNER JOIN sCommon.tStatusMaster SM ON SM.Id = QM.QuestionTypeId AND SM.StatusTypeId = 658
             
	WHERE  
			((QM.Id = @rId) OR (@rId = 0))
		 AND((QM.DataBankId = @rDataBankId) OR (@rDataBankId = 0))
		 AND((QM.TopicId = @rTopicId) OR (@rTopicId = 0))
         AND((VCS.Id = @rClassSubjectId) OR (@rClassSubjectId = 0)) 
         AND((TM.ClassGroupId = @rClassId) OR (@rClassId = 0)) 
         AND((DLM.Id = @rDifficultyLevelId) OR (@rDifficultyLevelId = 0)) 
		 AND ((ISNULL(QM.QuestionTypeId,727) = @rQuestionTypeId) OR (@rQuestionTypeId = 0))
         --AND CM.AffiliationId = 3
      
        Order By QM.TopicId,OrderIndex,QP.Id
	
		
END
GO
PRINT N'Altering [sEExam].[pQuestionMasterAdd]...';


GO


ALTER PROCEDURE [sEExam].[pQuestionMasterAdd]
	 
	@rId bigint,
	@rTopicId int,
	@rDifficultyLevelId tinyint,
	@rQuestionParagraphId bigint,
	@rQuestionDescription nvarchar(max),
	@rQuestionDescription2 nvarchar(max),
	@rMarks decimal(6,2),
	@rIsObsolete bit,
	@rOrderIndex smallint,	
	@rCreatedUserId bigint,
	@rDataBankId int,
	@rOrgGroupId int,
	@rEntryModeId smallint,
	@rQuestionTypeId int
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY
	
BEGIN TRANSACTION	
IF(@rQuestionTypeId = 0)
BEGIN
	SET @rQuestionTypeId=727;
END
IF(@rId = 0)	
BEGIN

	DECLARE @lNextNumber BIGINT   
	DECLARE @1NextOrderIndex int  
	
	EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sEExam.tQuestionMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
	IF EXISTS(SELECT OrderIndex From sEExam.tQuestionMaster Where TopicId = @rTopicId AND OrderIndex=@rOrderIndex)
	BEGIN
	 Select -101 as Result
	 return;
	END
	IF(@rOrderIndex = 0)
	BEGIN
    Select @rOrderIndex = ISNULL(Max(OrderIndex),0) From sEExam.tQuestionMaster Where TopicId = @rTopicId
    SET @rOrderIndex = @rOrderIndex + 1;
    END
    
	INSERT INTO sEExam.tQuestionMaster
		(
			Id,
			TopicId,
			DifficultyLevelId,
			QuestionParagraphId,
			QuestionDescription,
			QuestionDescription2,
			Marks,
			IsObsolete,
			OrderIndex,			
			CreatedUserId,
			CreatedDateTime,
			DataBankId,
			EntryModeId,
			QuestionTypeId
		)
	VALUES
		(
			@lNextNumber,
			@rTopicId,
			@rDifficultyLevelId,
			@rQuestionParagraphId,
			@rQuestionDescription,
			@rQuestionDescription2,
			@rMarks,
			@rIsObsolete,
			@rOrderIndex,			
			@rCreatedUserId,
			sCommon.fGetDate(),
			@rDataBankId,
			@rEntryModeId,
			@rQuestionTypeId
		)
		   SELECT @lNextNumber AS Result          
	END	 
	 ELSE
	 SET @lNextNumber = @rId 
	 
	    UPDATE  sEExam.tQuestionMaster
	    SET
			TopicId=@rTopicId,
			DifficultyLevelId=@rDifficultyLevelId,
			QuestionParagraphId=@rQuestionParagraphId,
			QuestionDescription=@rQuestionDescription,
			QuestionDescription2=@rQuestionDescription2,
			Marks=@rMarks,
			IsObsolete=@rIsObsolete,
			OrderIndex=@rOrderIndex,
			EntryModeId= @rEntryModeId,
			UpdatedUserId=@rCreatedUserId,
			UpdatedDateTime=sCommon.fGetDate() ,
			QuestionTypeId=@rQuestionTypeId
			 
	      WHERE Id = @rId 	   
	 	 
		  SELECT @lNextNumber AS Result  
		COMMIT TRANSACTION
		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END
GO
PRINT N'Altering [sEExam].[pTakeTestGet]...';


GO

ALTER PROCEDURE [sEExam].[pTakeTestGet]

	 @rTestParticipantId bigint 
	 
           
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 

BEGIN 
--DECLARE @lScoremark decimal(6,2)
--SET @lScoremark =(SELECT SUM(TPAT.ScoredMark) FROM sEExam.tTestParticipantAnswer TPAT WHERE TPAT.TestParticipantId=9999000000567)
	  
	SELECT
		
		ISNULL(TP.Id,0) AS TestParticipantId, 
		TP.TestId,
		TP.SourceId,
		TP.SourceTypeId,
		QM.Id AS QuestionId,
		QO.Id AS QuestionOptionId,		
		QM.QuestionDescription AS QuestionDescription,
		ISNULL(QM.QuestionDescription2,'') AS QuestionDescription2,
		QO.ChoiceDescription AS ChoiceDescription,
		ISNULL(QO.ChoiceDescription2,'') AS ChoiceDescription2,		
		QM.Marks AS QuestionMarks,
		QO.IsKey,
				
		ISNULL(Qp.QuestionParagraphDescription,'') AS QuestionParagraphDescription ,
		TM.TestDuration,
		ISNULL(TM.IsAutoFinishOnTime,0) AS IsAutoFinishOnTime,
		ISNULL(TPA.QuestionOptionId,0) AS AnweredQuestionOptionId,
		ISNULL(TPA.AnswerStatusId,0)AS AnswerStatusId,
		ISNULL(TPA.OrderIndex,0)AS OrderIndex,		
		CASE
		WHEN (TPA.QuestionOptionId = QO.Id AND QO.IsKey = 1 ) THEN 'Right'
		WHEN (TPA.QuestionOptionId = QO.Id AND QO.IsKey = 0) THEN 'Wrong'
		WHEN (QO.IsKey = 1) THEN 'Right' 
		ELSE '' END AS QuestionStatus,
		QM.QuestionTypeId
		 
FROM 
		sEExam.tTestParticipant TP 
		INNER JOIN sEExam.tTestMaster TM ON TP.TestId = TM.Id
		INNER JOIN sEExam.tTestQuestion TQ ON  TQ.TestId = TP.TestId
		INNER JOIN sEExam.tQuestionMaster QM ON TQ.QuestionId = QM.Id 		
		INNER JOIN sEExam.tQuestionOption QO ON QO.QuestionId=QM.Id 
	    LEFT JOIN sEExam.tTestParticipantAnswer TPA ON TP.Id = TPA.TestParticipantId AND TPA.QuestionId = TQ.QuestionId AND TPA.TestId = TQ.TestId	  
		LEFT JOIN sEExam.tQuestionParagraph QP ON Qp.Id = QM.QuestionParagraphId
		
WHERE  

        TP.Id = @rTestParticipantId
         
		--TPA.TestId=@rTestId AND 
		--SourceId = @rSourceId AND SourceTypeId = @rSourceTypeId 
		
	ORDER BY TQ.OrderIndex ASC,ISNULL(Qp.QuestionParagraphDescription,'') ASC
		
END
GO
PRINT N'Altering [sEExam].[pTestAssignGet]...';


GO


ALTER PROCEDURE [sEExam].[pTestAssignGet]

			--@rClassId int,
			@rDivisionId int,
			@rTestId int,
			@rBatchId int,			 
			@rSourceTypeID int,
			@rOrgId int,
			@rClassId int
           
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON  
BEGIN    

    SELECT  
			--VSA.AcademicId,
			VSA.RollNo,
			VSA.StudentId AS SourceId,
			VSA.Name AS StudentName,			
			'Student' AS ParticipantType,
			2003 AS SourceTypeId,
			VSA.DivisionId, 
			VSA.Code,	   
			CASE WHEN ISNULL(A.TestId,0) != 0 THEN 1 ELSE 0 END AS IsSelected,
			ISNULL(A.TestStatusId,0) AS TestStatusId,
			--VSA.DivisionName AS DivisionName
			DM.Code AS DivisionName    
    
    FROM 
			 sStudent.vStudentAcademic VSA
			 LEFT JOIN sEExam.tTestAssign A ON A.SourceId = VSA.StudentId AND A.SourceTypeId = 2003 AND A.TestId = @rTestId
			 LEFT JOIN sCommon.tStatusMaster SM ON SM.Id = A.TestStatusID
			 LEFT JOIN sSetup.tDivisionMaster DM ON VSA.DivisionId=DM.Id
	WHERE 
		--VSA.DivisionId=@rDivisionId
	 --   AND 
	    VSA.BatchId=@rBatchId  
		AND VSA.StatusId=1
		 
    --ORDER BY VSA.RollNo ASC 
	
--UNION ALL
  
--      SELECT 
--          0 AS RollNo,
--          EM.EmployeeId AS  SourceId,
--          EM.EmployeeName AS StudentName,
--	      'Employee' AS ParticipantType,
--		  95 AS SourceTypeId,
--		  0 AS DivisionId,
--          EM.Code,
--		  CASE WHEN ISNULL(A.TestId,0) != 0 THEN 1 ELSE 0 END AS IsSelected,
--          ISNULL(A.TestStatusId,0) AS TestStatusId,
--          EM.DepartmentName AS DivisionName
         
--      FROM sHrm.vEmployeeCommonDetail EM LEFT JOIN sEExam.tTestAssign A ON A.SourceId = EM.EmployeeId AND A.SourceTypeId = 95 AND A.TestId = @rTestId
--      LEFT JOIN sCommon.tStatusMaster SM ON SM.Id = A.TestStatusID 
	        
--      WHERE OrgID = @rOrgId
  
 UNION ALL
     SELECT 
          0 AS RollNo,
          DM.Id AS  SourceId,
          DM.Title AS StudentName,
		  'Division' AS ParticipantType,
		  2005 AS SourceTypeId,
		  DM.Id AS DivisonId, 
          DM.Code,
		  CASE WHEN ISNULL(A.TestId,0) != 0 THEN 1 ELSE 0 END AS IsSelected,
          ISNULL(A.TestStatusId,0) AS TestStatusId,
          --DM.Title AS DivisionName    
		  DM.Code AS DivisionName    
         
      FROM sSetup.tDivisionMaster DM  LEFT JOIN sEExam.tTestAssign A ON A.SourceId = DM.Id AND A.TestId = @rTestId
      LEFT JOIN sCommon.tStatusMaster SM ON SM.Id = A.TestStatusID 
      
      WHERE 
          ClassId = @rClassId AND DM.IsObsolete = 0 

		  ORDER By Code
END
GO
PRINT N'Altering [sEExam].[pTestMasterGet]...';


GO
 

ALTER PROCEDURE [sEExam].[pTestMasterGet]
	 
	 @rId int = 0,
	 --@rAffiliationId int,
	 @rTitle nvarchar(100),
	 @rCode nvarchar(20),
	 @rDataSubjectId int,
	 @rClassGroupId int,
	 @rDataBankId int,
	 @rOrgId int,
	 @rClassId int,
	 @rClassSubjectId int,
	 @rTestTemplateId int,
	 @rIsObsolete bit,
	 @rBatchId int,
	 @rFromDate date = '',
	 @rToDate date = '',
	 @rTestTypeId int=0
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON		 

BEGIN 
	  BEGIN	  
			SET @rTitle = sCommon.fSearchStringGet(@rTitle)
			SET @rCode = sCommon.fSearchStringGet(@rCode)
	  END
	SELECT 
		TM.Id,
		TM.Code,
		TM.Title,
		TM.TestDate,
		TM.DataSubjectId,
		TM.FromTime,
		TM.ToTime,
		TM.IsDisplayResultOnComplete,
		tm.IsSMSResultOnComplete,
		TM.IsAllowReview,
		TM.IsAutoPostResultToExam,
		TM.IsObsolete,
		TM.TotalMarks,
		DSM.Title AS DataSubjectTitle,
		TM.ClassGroupId,
		CGM.Title AS ClassGroupTitle,
		TM.DataBankId,
		DBM.Title AS DataBank,
		TM.ClassId,
		TM.ClassSubjectId,
		TM.TestTemplateId,
		CM.Title AS ClassName,
		CS.SubjectName AS ClassSubjectName,	
		'' AS TestTemplateName,
		ISNULL(AssignedSourceTypeId,0) AS AssignSourceTypeId,
		ISNULL(TestDuration,0) AS TestDuration,
		ISNULL(IsTestLock,0) AS IsTestLock,
		ISNULL(TestQuestion,0) AS TestQuestion,
		ISNULL(TM.IsMarkInDecimal,0) AS IsMarkInDecimal,
		ISNULL(TM.IsAutoFinishOnTime,0) AS IsAutoFinishOnTime,
		ISNULL(TM.TestStartupRemark,'') AS TestStartupRemark,
		ISNULL(TM.AlertDuration,0) AS AlertDuration,
		ISNULL(NegativeMarkPerAnswer,0) AS NegativeMarkPerAnswer,
		ISNULL(TM.FromDate,'')AS FromDate,
		ISNULL(TM.ToDate,'')AS ToDate,
		ISNULL(TM.TestTypeId,725) AS TestTypeId,
		ISNULL(TM.AssignEmployeeId,0) AS AssignEmployeeId,
		(CASE WHEN TM.TestTypeId=726 THEN 'Subjective' ELSE 'Objective' END) AS TestType
		--TM.IsMarkInDecimal
									   
	    --VBC.ClassName      
  FROM 
        sEExam.tTestMaster TM
        INNER JOIN  sEExam.tDataSubjectMaster DSM
        ON DSM.Id = TM.DataSubjectId
        INNER JOIN sEExam.tDataBankMaster DBM ON DBM.Id = TM.DataBankId
        INNER JOIN sSetup.tClassGroupMaster CGM ON CGM.Id = TM.ClassGroupId
        INNER JOIN sSetup.tClassMaster CM ON CM.Id = TM.ClassId
        INNER JOIN [sSetup].[vClassSubject] CS ON CS.ClassSubjectId = TM.ClassSubjectId  
        
  WHERE  
		   ((TM.Id = @rId) OR (@rId = 0))
      AND  ((TM.OrgId = @rOrgId) OR (@rOrgId = 0))
      AND  ((TM.Title like @rTitle) OR (ISNULL(@rTitle,'') = '')) 
      AND  ((TM.Code like @rCode) OR (ISNULL(@rCode,'') = '')) 
      AND  (TM.ClassGroupId = @rClassGroupId OR @rClassGroupId = 0) 
      AND  (TM.DataSubjectId = @rDataSubjectId OR @rDataSubjectId = 0)
      AND  (TM.ClassId = @rClassId OR @rClassId = 0)
      AND  (TM.ClassSubjectId = @rClassSubjectId OR @rClassSubjectId = 0)
      AND  (TM.TestTemplateId = @rTestTemplateId OR @rTestTemplateId = 0)
      AND  (TM.DataBankId = @rDataBankId OR @rDataBankId = 0)
      AND  (TM.IsObsolete = @rIsObsolete)
      AND  TM.BatchId = @rBatchId
	  AND ((TM.TestDate >= @rFromDate) OR (ISNULL(@rFromDate,'')=''))
	  AND ((TM.TestDate <= @rToDate) OR (ISNULL(@rToDate,'')=''))
	  AND  (ISNULL(TM.TestTypeId,725) = @rTestTypeId OR @rTestTypeId = 0)

	ORDER BY TM.TestDate DESC
      
END
GO
PRINT N'Altering [sEExam].[pTestParticipantAdd]...';


GO
ALTER PROCEDURE [sEExam].[pTestParticipantAdd]
	  
	@rId bigint,
	@rTestId int,
	@rSourceId bigint,
	@rSourceTypeId int, 
	@rTestDate date=NULL,
	@rStartTime time(7)=NULL,
	@rEndTime time(7)=NULL,
	@rTestStatusId smallint=NULL,
	@rTotalMarks decimal(6,2)=NULL,
	@rScoredMarks decimal(6,2)=NULL,
	@rTotalQuestions int=NULL,
	@rRightQuestions int=NULL,
	@rWrongQuestions int=NULL,
	@rNotAttemptedQuestions int=NULL,
	@rSkippQuestion int=NULL,
	@rOrgId int,
	@rMode smallint,
	@rBatchId int
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	IF(@rId = 0)
	BEGIN 
	DECLARE @lNextNumber bigint     
	EXECUTE sCommon.pTableRowIdGet  @rOrgId, 'sEExam.tTestParticipant', 0, 1, 1,@outNextNumber = @lNextNumber output
	END 

	BEGIN TRY
	
	 BEGIN TRANSACTION	
	 
	
 IF(@rSourceTypeId = 2005)
   BEGIN   
		 DELETE TP FROM sEExam.tTestParticipant TP INNER JOIN sStudent.vStudentAcademic VSA 
		 ON TP.SourceId = VSA.StudentId 
		 Where DivisionId = @rSourceId  AND TestId = @rTestId AND VSA.BatchId=@rBatchId
		 

		  --Delete Participant Answer
			DELETE TPA FROM sEExam.tTestParticipantAnswer TPA 
			INNER JOIN sEExam.tTestParticipant TP ON TPA.TestParticipantId=TP.Id
			INNER JOIN sStudent.vStudentAcademic VSA ON TP.SourceId = VSA.StudentId 
			WHERE TPA.TestId= @rTestId  
			AND VSA.DivisionId = @rSourceId  AND VSA.BatchId = @rBatchId

		    --IF(@rId = 0)
		     BEGIN
			 INSERT INTO sEExam.tTestParticipant
			 SELECT @rTestId,2003,StudentId,@rTestDate,@rStartTime,@rEndTime,543,
			 @rTotalMarks,@rScoredMarks,@rTotalQuestions,@rRightQuestions,@rWrongQuestions,@rNotAttemptedQuestions,@rSkippQuestion,@lNextNumber + ROW_NUMBER() over(order by StudentId desc),''
			 FROM sStudent.vStudentAcademic VSA Where DivisionId = @rSourceId AND VSA.BatchId=@rBatchId
			  
			 SELECT @lNextNumber = MAX(Id) from sEExam.tTestParticipant where Id like  CONVERT(nvarchar(50),@rOrgId) + '%'
             EXECUTE sCommon.pTableRowIdModify  @rOrgId,'sEExam.tTestParticipant',@lNextNumber,0,0,0,@lNextNumber output 
			  
			 UPDATE  TA 
			 SET TA.TestStatusId = 543
			 FROM sEExam.tTestAssign TA INNER JOIN sStudent.vStudentAcademic VSA 
			 ON TA.SourceId = VSA.StudentId 
			 Where DivisionId = @rSourceId AND TA.TestId = @rTestId  AND VSA.BatchId=@rBatchId
			 END
           
         SELECT 1 AS Result 
   END 
    --EXECUTE sCommon.pTableRowIdModify   @rOrgId,'sEExam.tTestParticipant',@lNextNumber,0,0,0,@lNextNumber output 
   
ELSE
	 
   IF NOT EXISTS(SELECT 1 FROM sEExam.tTestParticipant Where  TestId = @rTestId 
   AND SourceId = @rSourceId AND SourceTypeId = @rSourceTypeId)	
   BEGIN
  
	INSERT INTO sEExam.tTestParticipant
		(
			Id,
			TestId,
			SourceId,
			SourceTypeId,
			TestDate,
			StartTime,
			EndTime,
			TestStatusId,
			TotalMarks,
			ScoredMarks,
			TotalQuestions,
			RightQuestions,
			WrongQuestions,
			NotAttemptedQuestions,
			SkippQuestions			
		)
	VALUES
		(
			@lNextNumber,
			@rTestId,
			@rSourceId,
			@rSourceTypeId,
			@rTestDate,
			CONVERT(CHAR(7),@rStartTime,108),
			--@rStartTime,
			@rEndTime,
			@rTestStatusId,
			@rTotalMarks,
			@rScoredMarks,
			@rTotalQuestions,
			@rRightQuestions,
			@rWrongQuestions,
			@rNotAttemptedQuestions,
			@rSkippQuestion
			
		)    
	--UPDATE sEExam.tTestParticipant SET TestStatusId = 1 WHERE SourceId = @rSourceId AND SourceTypeId = @rSourceTypeId AND TestId = @rTestId
	 --1 For Activated ,0 For Deactivated
	--SELECT 1 AS Result  
	END
	 ELSE
	 BEGIN
	 --SET @lNextNumber = @rId
	 IF(@rMode=1)
	 BEGIN
	  
	    UPDATE sEExam.tTestParticipant
	    SET
	     TestDate=@rTestDate,
	     StartTime=@rStartTime,
	     TestStatusId=@rTestStatusId,
	     TotalMarks=@rTotalMarks
	     WHERE Id = @rId
 	     --TestId = @rTestId AND SourceId = @rSourceId AND SourceTypeId = @rSourceTypeId
	     --SELECT 11 AS Result 	
	 END
	 ELSE IF(@rMode=2)
	 BEGIN
	 
	 UPDATE sEExam.tTestParticipant
	    SET
	     
	     --EndTime=@rEndTime,
	     TestStatusId=@rTestStatusId,
	     ScoredMarks=@rScoredMarks,
	     TotalQuestions=@rTotalQuestions,
	     RightQuestions=@rRightQuestions,
	     WrongQuestions=@rWrongQuestions,
	     NotAttemptedQuestions=@rNotAttemptedQuestions,
	     SkippQuestions = @rSkippQuestion
	      
	     WHERE Id = @rId 
	     --TestId = @rTestId AND SourceId = @rSourceId AND SourceTypeId = @rSourceTypeId
	     --SELECT 11 AS Result 
	  END    
	 END
	 
	 UPDATE sEExam.tTestParticipant SET TestStatusId = @rTestStatusId WHERE SourceId = @rSourceId AND SourceTypeId = @rSourceTypeId AND TestId = @rTestId
	 --UPDATE sEExam.tTestParticipant SET TestStatusId = @rTestStatusId , ScoredMarks = 0 ,RightQuestions =0 , WrongQuestions =0 ,SkippQuestions =0 ,NotAttemptedQuestions =0 WHERE SourceId = @rSourceId AND SourceTypeId = @rSourceTypeId AND TestId = @rTestId
	 UPDATE sEExam.tTestAssign SET TestStatusId = @rTestStatusId Where SourceId = @rSourceId AND SourceTypeId = @rSourceTypeId AND TestId = @rTestId

	 
    --Delete Participant Answer
		DELETE TPA FROM sEExam.tTestParticipantAnswer TPA 
		INNER JOIN sEExam.tTestParticipant TP ON TPA.TestParticipantId=TP.Id
		INNER JOIN sStudent.vStudentAcademic VSA ON TP.SourceId = VSA.StudentId 
		WHERE TPA.TestId= @rTestId  
		AND TP.SourceId = @rSourceId  AND TP.SourceTypeId = @rSourceTypeId

	 SELECT 1 AS Result
	 	
	COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
END
GO
PRINT N'Altering [sFee].[pFeeFollowupGet]...';


GO
 
ALTER PROCEDURE [sFee].[pFeeFollowupGet]
	
	@rId bigint = 0,
	@rFromDate date='',
	@rToDate date='',
	@rOrgId Smallint=0,
	@rOrgGroupBatchId int = 0,
	@rReceiptBookId int=0,
	@rCode nvarchar(20)='',
	@rFromNextFollowupDate date = '',
	@rToNextFollowupDate date = ''
	 	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 

BEGIN 
	SELECT 
		FF.Id,
		vSA.Code,
		FF.ReceiptBookId,
		ISNULL(FF.FollowupDate,'') AS  FollowupDate,
		FF.FollowupRemark,
		FF.HandledBy,
		vSA.Name,
		ISNULL(FF.NextFollowupDate,'') AS NextFollowupDate,
		vSA.OrgId,
		OM.DisplayTitle AS OrgName,
		vSA.RollNo,
		vSA.ParentContactNo,
		ClassDivision,
		DM.OrderIndex  AS DivisionOrderIndex,
		vSA.AcademicId
	FROM 
		sFee.tFeeFollowup FF
		INNER JOIN sStudent.vStudentAllDetail vSA ON  vSA.AcademicId = FF.AcademicId
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = vSA.OrgId
		INNER JOIN sSetup.tDivisionMaster DM ON DM.Id = vSA.DivisionId
	WHERE  
		(FF.Id = @rId OR @rId = 0)
		AND (FF.FollowupDate>= @rFromDate OR ISNULL(@rFromDate,'')='')
		AND (FF.FollowupDate<= @rToDate OR ISNULL(@rToDate,'')='')
		AND (vSA.OrgId=@rOrgId OR @rOrgId=0)
		AND (FF.ReceiptBookId = @rReceiptBookId OR @rReceiptBookId = 0)
		AND (vSA.OrgGroupBatchId=@rOrgGroupBatchId OR @rOrgGroupBatchId=0)
		AND ((vSA.Code=@rCode) OR (ISNULL(@rCode,'')=''))
		AND (FF.NextFollowupDate>= @rFromNextFollowupDate OR ISNULL(@rFromNextFollowupDate,'')='')
		AND (FF.NextFollowupDate<= @rToNextFollowupDate OR ISNULL(@rToNextFollowupDate,'')='')
	ORDER BY FF.FollowupDate  
END
GO
PRINT N'Altering [sFee].[pFeePendingListGet]...';


GO
 
ALTER PROCEDURE [sFee].[pFeePendingListGet]
		
		@rOrgGroupBatchId int=0,
		@rAdmissionCategoryId int=0,
		@rClassId int=0,
		@rDivisionId int=0,
		@rCode nvarchar(20)='',
		@rFirstName nvarchar(100)='',
		@rLastName nvarchar(100) = '',
		@rReceiptBookId int=0,
		@rOrgId int=0,
		@rBatchId int=0,
		@rDepartmentId int=0,
		@rSUId nvarchar(20) = '',
		@rUptoDate date='',
		@rFromNextFollowupDate date='',
		@rToNextFollowupDate date=''
		
		
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
		BEGIN
			SET @rFirstName = sCommon.fSearchStringGet(@rFirstName)
			SET @rLastName = sCommon.fSearchStringGet(@rLastName)
		END		 
BEGIN 	
	
	DECLARE @p AS TempPendingFee
	
	INSERT INTO @p 
	SELECT 
		SA.AcademicId,
		SA.RollNo,
		SA.Name,
		SA.Code,
		SA.ClassId,
		SA.DivisionId,
		SA.ClassName,
		SA.DivisionName,
		SA.ClassName+' - '+SA.DivisionName AS ClassDivision,
		FS.Id AS FeeSetupId,		
		ISNULL(FS.IsVariable,0)AS IsVariable,
		ISNULL(FS.Amount,0)AS Amount,
		ISNULL(FS.StartDate,'1900-01-01')AS StartDate,
		ISNULL(FS.EndDate,'1900-01-01')AS EndDate,
		ISNULL(FA.VariableAmount,0.00)AS FAAmount,
		ISNULL(FA.DiscountAmount,0.00)AS DiscountAmount,
		ISNULL(FA.AdditionalAmount,0.00)AS AdditionalAmount,
		ISNULL(R.PaidAmount,0) AS PaidAmount,
		ISNULL(FS.Amount,0.00)+ISNULL(FA.VariableAmount,0.00)+ISNULL(FA.AdditionalAmount,0.00) -ISNULL(FA.DiscountAmount,0.00)-ISNULL(FA.NotApplicableAmount,0.00)-ISNULL(R.PaidAmount,0.00)AS PendingAmount,
		ISNULL(FA.Remark,'') AS Remark,
		FS.ReceiptBookId,
		FS.FeeName,
		FS.FeeCode,
		SA.OrgId,
		O.Title AS OrgName,
		O.OrderIndex AS OrgOrderIndex,
		ISNULL(RBM.Title,'') AS ReceiptBookTitle,
		ISNULL(RBM.OrderIndex,0) AS ReceiptOrderIndex,
		SA.BatchId,
		BM.Title AS BatchName,
		'',
		'',
		''	
  FROM 
		sStudent.vStudentAcademic SA
		INNER JOIN 
		(
			SELECT 
				FS1.FeeId,
				FS1.Id,
				ISNULL(FS1.IsOptional,0)AS IsOptional,
				ISNULL(FS1.IsVariable,0)AS IsVariable,
				ISNULL(FS1.Amount,0)AS Amount,
				ISNULL(FS1.StartDate,'1900-01-01')AS StartDate,
				ISNULL(FS1.EndDate,'1900-01-01')AS EndDate,
				FS1.BatchId,
				ISNULL(FS1.ReceiptBookId,0) AS ReceiptBookId,
				SA1.AcademicId,
				FM.Title AS FeeName,
				FM.Code AS FeeCode
			FROM 
				sFee.tFeeSetup FS1
				INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.ClassId=FS1.ClassId AND FS1.BatchId = SA1.BatchId 
				AND SA1.AdmissionCategoryId = FS1.AdmissionCategoryId				
				AND ((SA1.ClassId=@rClassId) OR (@rClassId=0)) AND ((SA1.DivisionId=@rDivisionId) OR (@rDivisionId=0))
				AND ((SA1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))
				INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS1.FeeId
			WHERE 
			    ((SA1.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId=0))
				AND	((FS1.ClassId=@rClassId) OR (@rClassId=0))
				AND	((SA1.OrgId=@rOrgId) OR (@rOrgId=0))
				AND ((FS1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))
				AND ((FS1.ReceiptBookId=@rReceiptBookId) OR (@rReceiptBookId=0))
				AND (FS1.IsOptional=0)
				AND ((FS1.BatchId=@rBatchId) OR (@rBatchId=0))
				AND ((SA1.BatchId=@rBatchId) OR (@rBatchId=0))
				
			UNION ALL
				
			SELECT 
				FS1.FeeId,
				FS1.Id,
				ISNULL(FS1.IsOptional,0)AS IsOptional,
				ISNULL(FS1.IsVariable,0)AS IsVariable,
				ISNULL(FS1.Amount,0)AS Amount,
				ISNULL(FS1.StartDate,'1900-01-01')AS StartDate,
				ISNULL(FS1.EndDate,'1900-01-01')AS EndDate,
				FS1.BatchId,
				ISNULL(FS1.ReceiptBookId,0) AS ReceiptBookId,
				SA1.AcademicId,
				FM.Title AS FeeName,
				FM.Code AS FeeCode
			FROM 
				sFee.tFeeSetup FS1
				INNER JOIN sFee.tFeeOptional FO ON FO.FeeSetupId=FS1.Id
				INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS1.FeeId
				INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.AcademicId=FO.SourceId AND SA1.BatchId = FS1.BatchId AND SA1.AdmissionCategoryId = FS1.AdmissionCategoryId
				AND ((SA1.ClassId=@rClassId) OR (@rClassId=0)) AND ((SA1.DivisionId=@rDivisionId) OR (@rDivisionId=0))
				AND ((SA1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))
			WHERE
		        ((SA1.OrgGroupBatchId = @rOrgGroupBatchId)  OR (@rOrgGroupBatchId=0))
				AND ((FS1.ClassId=@rClassId) OR (@rClassId=0))
				AND	((SA1.OrgId=@rOrgId) OR (@rOrgId=0))
				AND ((FS1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))
				AND ((FS1.ReceiptBookId=@rReceiptBookId) OR (@rReceiptBookId=0))
				AND (FS1.IsOptional=1)
				AND ((FS1.BatchId=@rBatchId) OR (@rBatchId=0))
				AND ((SA1.BatchId=@rBatchId) OR (@rBatchId=0))
		) FS ON FS.AcademicId=SA.AcademicId
		LEFT JOIN sFee.tFeeAllocation FA ON FA.FeeSetupId=FS.Id AND FA.SourceId=SA.AcademicId
		LEFT JOIN
		(
		    SELECT 
					ISNULL(SUM(RD.Amount),0) AS PaidAmount,RD.FeeSetupId ,R.SourceId
		    FROM
				sFee.tReceipt R 
				INNER JOIN sFee.tReceiptDetail RD ON RD.ReceiptId = R.Id 
				INNER JOIN sFee.tFeeSetup FS ON RD.FeeSetupId = FS.Id
				INNER JOIN sFee.tFeeMaster FM ON FS.FeeId = FM.Id
				INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.AcademicId=R.SourceId				
				AND (SA1.ClassId=@rClassId OR @rClassId=0) AND ((SA1.DivisionId=@rDivisionId) OR (@rDivisionId=0))
				AND (SA1.DivisionId=@rDivisionId OR @rDivisionId=0)
				
			WHERE		
				(R.SourceTypeId = 2001)
				AND ((SA1.OrgId=@rOrgId) OR (@rOrgId=0))
				AND (R.IsObsolete = 0) 
				AND ((SA1.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId=0))
				AND ((R.BatchId=@rBatchId) OR (@rBatchId=0))
				GROUP BY FeeSetupId,R.SourceId
		) R ON R.FeeSetupId = FS.Id AND SA.AcademicId = R.SourceId
		 INNER JOIN sSetup.tOrganizationMaster O ON SA.OrgId = O.Id
		 INNER JOIN sFee.tReceiptBookMaster RBM ON FS.ReceiptBookId=RBM.Id
		 INNER JOIN sSetup.tBatchMaster BM ON BM.Id=SA.BatchId	
		 LEFT JOIN sHostel.tAssignRoom AR ON  AR.SourceId=SA.StudentId AND AR.SourceTypeId=2003 AND ((SA.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0))  AND ((AR.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0) AND (SA.OrgGroupBatchId = AR.OrgGroupBatchId)) 
         LEFT JOIN
		(
			SELECT 
				AT.SourceId,
				B.OrgGroupBatchId 
			FROM 
				sTransport.tAssignRoute AT 
				INNER JOIN sTransport.tBusRouteDestinationMaster BM ON BM.Id=AT.PickupDestinationId
				INNER JOIN sTransport.tBusRouteMaster B ON B.Id=BM.BusRouteId AND  AT.SourceTypeId=2003 AND ((B.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0))
		) RT ON RT.SourceId = SA.StudentId 
		AND ((SA.OrgGroupBatchId = RT.OrgGroupBatchId))
		AND ((SA.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0)) 
	WHERE	
			((SA.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId=0))
			AND ((SA.BatchId=@rBatchId) OR (@rBatchId=0)) 
			AND ((SA.Code=@rCode) OR (@rCode=''))
			AND ((SA.Code=@rSUId) OR (@rSUId=''))
			AND ((SA.FirstName like @rFirstName) OR(ISNULL(@rFirstName,'')=''))
			AND ((SA.LastName like @rLastName) OR (ISNULL(@rLastName,'')=''))
			AND ((SA.DepartmentId=@rDepartmentId) OR (@rDepartmentId=0))
			AND (ISNULL(FS.Amount,0.00)+ISNULL(FA.VariableAmount,0.00)+ISNULL(FA.AdditionalAmount,0.00) -ISNULL(FA.DiscountAmount,0.00) -ISNULL(FA.NotApplicableAmount,0.00)-ISNULL(R.PaidAmount,0.00) != 0)			
			AND SA.	StatusId = 1
			AND ((FS.StartDate <= @rUptoDate) OR (ISNULL(@rUptoDate,'') = ''))
			
			ORDER BY SA.RollNo
 
			SELECT
				Temp.OrgId,			 
				Temp.ReceiptBookId,
				Temp.AcademicId,
				Temp.DivisionId,
				Temp.ClassDivision,
				Temp.RollNo, 
				Temp.Name,
				Temp.Code,
				Temp.ReceiptBookTitle,
				Temp.OrgName,
				SUM(Temp.PendingAmount) AS PendingAmount, 
				[sFee].[fPendingFeeNamesGet](Temp.AcademicId,Temp.ReceiptBookId,	@p) AS PendingFeeName,
				Temp.ReceiptOrderIndex,
				Temp.BatchId,
				Temp.BatchName,
				'' AS SUId,
				FF.NextFollowupDate,
				ISNULL(SC.IsStopApplicationAccess,0) as IsStopApplicationAccess,
				ISNULL(SC.StudentCustomAlert,'') as StudentCustomAlert,
				ISNULL(SC.ParentContactNo,'')AS ParentContactNo
			FROM @p Temp
			LEFT JOIN 
			(
				SELECT AcademicId,ReceiptBookId,NextFollowupDate 
				FROM 
				(
				SELECT AcademicId,ReceiptBookId,NextFollowupDate, 
				ROW_NUMBER() OVER(PARTITION BY AcademicId,ReceiptBookId ORDER BY NextFollowupDate DESC) AS RowNumber
				FROM sFee.tFeeFollowup FF 
				) AS T
				WHERE RowNumber = 1 
			) FF ON FF.AcademicId = Temp.AcademicId AND FF.ReceiptBookId = Temp.ReceiptBookId
			INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId = Temp.AcademicId AND SA.OrgId = Temp.OrgId
			LEFT JOIN sStudent.tStudentCommunication SC ON SC.StudentId = SA.StudentId 
		WHERE 
			((FF.NextFollowupDate >= @rFromNextFollowupDate) OR (ISNULL(@rFromNextFollowupDate,'') = ''))
			AND ((FF.NextFollowupDate <= @rToNextFollowupDate) OR (ISNULL(@rToNextFollowupDate,'') = ''))
		GROUP BY 
			Temp.OrgId,Temp.AcademicId,Temp.ReceiptBookId,Temp.DivisionId,Temp.BatchId,
			Temp.ClassDivision,Temp.RollNo, Temp.Name,Temp.Code,Temp.ReceiptBookTitle,
			Temp.OrgName,Temp.ReceiptOrderIndex,Temp.BatchName,FF.NextFollowupDate,
			 SC.IsStopApplicationAccess,SC.StudentCustomAlert,SC.ParentContactNo
			ORDER BY Temp.AcademicId
END
GO
PRINT N'Altering [sFee].[pMyFeeGet]...';


GO
  
-- exec [sFee].[pMyFeeGet] 1201000000102,2001,120100001,0,'CollectionGroup'  
ALTER PROCEDURE [sFee].[pMyFeeGet]    
      
  @rSourceId bigint ,  --1201000000102  
  @rSourceTypeId int,  --2001  
  @rBatchId int,  --120100001  
  @rStudentId bigint =0,  
  @rDisplay varchar(20)=''  
      
 WITH EXECUTE AS CALLER    
AS    
SET NOCOUNT ON    BEGIN      
 if (@rStudentId <> 0)    
 SELECT @rSourceId = Id, @rSourceTypeId = 2001 FROM sStudent.tAcademic WHERE StudentId = @rStudentId AND BatchId = @rBatchId    
  DECLARE @lClassId int    
  DECLARE @lAdmissionCategoryId int    
  SELECT @lClassId=ClassId,@lAdmissionCategoryId=AdmissionCategoryId FROM sStudent.vStudentAcademic WHERE AcademicId=@rSourceId    
    
  -----------------------------------------------------------------------------------------------------------------------------------------  
  
  create table #TempTable  
  (id int identity(1,1),Feeid bigint,FeeName nvarchar(50),FeeSetupId bigint,isOptional tinyint,IsVariable tinyint,Amount decimal(12,2),  
  StartDate Date,EndDate Date,FAAmount decimal(12,2),DiscountAmount decimal(12,2),AdditionalAmount decimal(12,2),PaidAmount decimal(12,2),PendingAmount decimal(12,2),  
  NotApplicableAmount decimal(12,2),Remark Varchar(Max),OrderIndex int,ReceiptNumber int,ReceiptDate Date,ReceiptBookId bigint,CollectionGroupId Bigint,  
  Col_StartDate Date,Col_EndDate Date,CollectionGroup varchar(200),FeeHeadid Bigint,FeeHead nvarchar(200),FeeDuration Varchar(100))  
  
  
  Insert into #TempTable  
  
SELECT     
   FM.Id AS FeeId,    
   FM.Title AS FeeName,    
   FS.Id AS FeeSetupId,    
   ISNULL(FS.IsOptional,0)AS IsOptional,    
   ISNULL(FS.IsVariable,0)AS IsVariable,    
   ISNULL(FS.Amount,0)AS Amount,    
   ISNULL(FS.StartDate,'1900-01-01')AS StartDate,    
   ISNULL(FS.EndDate,'1900-01-01')AS EndDate,    
   ISNULL(FA.VariableAmount,0.00)AS FAAmount,    
   ISNULL(FA.DiscountAmount,0.00)AS DiscountAmount,    
   ISNULL(FA.AdditionalAmount,0.00)AS AdditionalAmount,    
   ISNULL(R.PaidAmount,0) AS PaidAmount,    
   ISNULL(FS.Amount,0.00)+ISNULL(FA.VariableAmount,0.00)+ISNULL(FA.AdditionalAmount,0.00) -ISNULL(FA.DiscountAmount,0.00)-ISNULL(FA.NotApplicableAmount,0.00)-ISNULL(R.PaidAmount,0.00)AS PendingAmount,    
   ISNULL(FA.NotApplicableAmount,0.00) AS NotApplicableAmount,    
   ISNULL(FA.Remark,'') AS Remark,    
   FM.OrderIndex,    
   0 AS ReceiptNumber,    
   '1900-01-01'AS ReceiptDate,    
   ISNULL(FS.ReceiptBookId,0) AS ReceiptBookId,    
   ISNULL(FS.CollectionGroupId,0) AS CollectionGroupId,  
   ISNULL(CGM.StartDate,'') as Col_StartDate,  
 ISNULL(CGM.EndDate,'') as Col_EndDate,  
   ISNULL(CGM.Title,'') AS CollectionGroup,    
   FM.FeeHeadId,    
   FHM.Title AS FeeHead,    
   FS.FeeDuration    
  FROM     
   sFee.tFeeMaster FM    
  INNER JOIN     
  (    
   SELECT     
    FS1.FeeId,    
    FS1.Id,    
    ISNULL(FS1.IsOptional,0)AS IsOptional,    
    ISNULL(FS1.IsVariable,0)AS IsVariable,    
    ISNULL(FS1.Amount,0)AS Amount,    
    ISNULL(FS1.StartDate,'1900-01-01')AS StartDate,    
    ISNULL(FS1.EndDate,'1900-01-01')AS EndDate,    
    FS1.BatchId,    
    ISNULL(FS1.ReceiptBookId,0) AS ReceiptBookId,    
    ISNULL(FS1.CollectionGroupId,0) AS CollectionGroupId,  
    ISNULL(FS1.FeeDuration,'')  AS FeeDuration    
   FROM sFee.tFeeSetup FS1    
          
    WHERE FS1.ClassId=@lClassId AND FS1.AdmissionCategoryId=@lAdmissionCategoryId AND IsOptional=0    
    AND FS1.BatchId = @rBatchId    
    UNION ALL    
        
   SELECT     
    FS1.FeeId,    
    FS1.Id,    
    ISNULL(FS1.IsOptional,0)AS IsOptional,    
    ISNULL(FS1.IsVariable,0)AS IsVariable,    
    ISNULL(FS1.Amount,0)AS Amount,    
    ISNULL(FS1.StartDate,'1900-01-01')AS StartDate,    
    ISNULL(FS1.EndDate,'1900-01-01')AS EndDate,    
    FS1.BatchId,    
    ISNULL(FS1.ReceiptBookId,0) AS ReceiptBookId,    
    ISNULL(FS1.CollectionGroupId,0) AS CollectionGroupId ,   
    ISNULL(FS1.FeeDuration,'') AS FeeDuration    
         
   FROM sFee.tFeeSetup FS1    
        
  INNER JOIN sFee.tFeeOptional FO    
   ON FO.FeeSetupId=FS1.Id AND (FO.SourceId=@rSourceId) AND(FO.SourceTypeId=@rSourceTypeId)    
   WHERE    
    FS1.BatchId = @rBatchId    
   AND FS1.ClassId=@lClassId AND FS1.AdmissionCategoryId=@lAdmissionCategoryId AND IsOptional=1    
  ) FS ON FS.FeeId=FM.Id    
  INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id = FM.FeeHeadId    
  LEFT JOIN sFee.tCollectionGroupMaster CGM ON CGM.Id = FS.CollectionGroupId    
  LEFT JOIN sFee.tFeeAllocation FA    
   ON FA.FeeSetupId=FS.Id AND (FA.SourceId=@rSourceId) AND(FA.SourceTypeId=@rSourceTypeId)    
  LEFT JOIN    
  (    
      SELECT ISNULL(SUM(RD.Amount),0) AS PaidAmount,RD.FeeSetupId    
      FROM    
      sFee.tReceipt R INNER JOIN sFee.tReceiptDetail RD ON RD.ReceiptId = R.Id       
   WHERE          
    R.SourceId= @rSourceId    
   AND ((R.SourceTypeId = @rSourceTypeId) OR (@rSourceTypeId=0))    
   AND R.IsObsolete = 0     
   AND R.BatchId = @rBatchId    
    GROUP BY FeeSetupId    
  ) R ON R.FeeSetupId = FS.Id     
WHERE     
 FS.BatchId=@rBatchId    
ORDER BY FS.StartDate ASC,FM.OrderIndex ASC   
   
 --------------------------------------------------------------------------------------------  
 -- Get Group Wise Data ---  
  
 IF(@rDisplay = 'Fee Head')  
 BEGIN  
 Print @rDisplay + '1'  
   
 Create Table #temp_head  
 (id int identity(1,1),FeeHeadid Bigint,FeeHead nvarchar(200),Amount decimal(12,2),StartDate Date,EndDate Date,FAAmount decimal(12,2),  
 DiscountAmount decimal(12,2),AdditionalAmount decimal(12,2),PaidAmount decimal(12,2),PendingAmount decimal(12,2),  
 NotApplicableAmount decimal(12,2))  
  
 Insert into #temp_head  
 select FeeHeadid,FeeHead,sum(Amount) as Amount,min(StartDate) as StartDate,Max(EndDate) as EndDate,sum(FAAmount) as FAAmount,  
 sum(DiscountAmount) as DiscountAmount,sum(AdditionalAmount) as AdditionalAmount,sum(PaidAmount) as PaidAmount,  
 sum(PendingAmount) as PendingAmount,sum(NotApplicableAmount) as NotApplicableAmount--,Remark,ReceiptDate,ReceiptBookId,CollectionGroupID,CollectionGroup  
 from #TempTable  
 group by Feeheadid,FeeHead  
  
 Declare @i int  
  
 while(@i<= (Select Count(*) from #temp_head))  
 BEGIN  
 Update #temp_head set StartDate = (Select min(StartDate) from #tempTable A Where A.FeeHeadid = #temp_head.FeeHeadid) where id=@i  
 Update #temp_head set EndDate = (Select Max(EndDate) from #tempTable A Where A.FeeHeadid = #temp_head.FeeHeadid) where id=@i  
 PRINT 1  
 END  
  
 select FeeHeadid as Feeid,FeeHead as FeeName,PendingAmount,'' as IsVariable,  
 '' as FeeSetupId,'' as Remark,'' as ReceiptNumber,'' as ReceiptDate,'' as CollectionGroupId,FeeHeadid,0 as IsOptional,  
 (case when FAAmount>0 then FAAmount else Amount + AdditionalAmount - DiscountAmount - NotApplicableAmount End) as Amount ,  
 StartDate,EndDate,FAAmount,DiscountAmount,AdditionalAmount,PaidAmount,PendingAmount,NotApplicableAmount,'' as FeeDuration from #temp_head  
   
 END  
  
 ELSE IF(@rDisplay = 'Collection Group')  
 BEGIN  
  
 select CollectionGroupID as Feeid,CollectionGroup as FeeName, sum(PendingAmount) as PendingAmount,CollectionGroupID as FeeHeadid,  
 '' as IsVariable,'' as FeeSetupId,'' as Remark,'' as ReceiptNumber,'' as ReceiptDate,'' as CollectionGroupId,0 as IsOptional,  
 (case when sum(FAAmount)>0 then sum(FAAmount) else sum(Amount) + sum(AdditionalAmount) - sum(DiscountAmount) - sum(NotApplicableAmount) End) as Amount ,  
 Col_startDate as StartDate,Col_EndDate as EndDate,sum(FAAmount) as FAAmount,sum(DiscountAmount) as DiscountAmount,  
 sum(AdditionalAmount) as AdditionalAmount,sum(PaidAmount) as PaidAmount,sum(PendingAmount) as PendingAmount,  
 sum(NotApplicableAmount) as NotApplicableAmount,'' as FeeDuration from #TempTable  
 group by CollectionGroupID,CollectionGroup,Col_startDate,Col_EndDate  
  
 END  
  
 ELSE  
 BEGIN  
  
 select CollectionGroupID as Feeid,FeeName,PendingAmount,   
 '' as IsVariable,'' as FeeSetupId,'' as Remark,'' as ReceiptNumber,'' as ReceiptDate,'' as CollectionGroupId,0 as IsOptional,  
 CollectionGroupID as FeeHeadid,  
 (FAAmount + Amount + AdditionalAmount) - (DiscountAmount + NotApplicableAmount) as Amount ,  
 StartDate,EndDate,FAAmount,DiscountAmount, AdditionalAmount,PaidAmount,PendingAmount,NotApplicableAmount,'' as FeeDuration from #TempTable  
  
 END  
  
END
GO
PRINT N'Altering [sFee].[pRptFeeReceiptDetailsGet]...';


GO
 
 
ALTER PROCEDURE [sFee].[pRptFeeReceiptDetailsGet]
			
			
			@rReportId int=0,
			@rReceiptId bigint = 0,
			@rOrgGroupId int = 0,
			@rReceiptBookId int = 0,
			@rFromReceiptNo int=0,
			@rToReceiptNo int=0,
			@rOrgId int = 0,
			@rBatchId int = 0,
			@rReceiptDate Date='',
			@rClassId int = 0,
			@rOrgIds nvarchar(max) = '',
			@rReceiptPrintGroupId nvarchar(100)=''
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	
BEGIN 	
	
	DECLARE @lOrgGroupId int = 0
	DECLARE @lAmountInWord nvarchar(500) = ''
	DECLARE @lReceiptAmount Decimal(12,2) = 0.0
	DECLARE @lCurrencyCode nvarchar(20) = ''
	DECLARE @lCurrencyTitle nvarchar(100) = ''
	Declare @lIDConfigValue nvarchar(50)='',@lOrgGroup_Id int=0
	Select @lOrgGroup_Id = OrgGroupId From  sSetup.tOrganizationMaster Where Id = @rOrgId
	 
	SELECT @lIDConfigValue =ItemValue From sConfig.tConfigurationSetup
	WHERE ConfigurationId = 236 AND SourceId = @rOrgGroupId
	
	IF(@rOrgGroupId=0)
	BEGIN
		SELECT @lOrgGroupId = OrgGroupId ,@lReceiptAmount = R.ReceiptAmount  FROM sFee.tReceipt R 
		INNER JOIN sSetup.tOrgGroupBatchMaster OB ON R.OrgGroupBatchId = OB.Id 
		WHERE R.Id = @rReceiptId

		SELECT  @lAmountInWord = sFinanceCommon.fNumberToWordsWithDecimal(ISNULL(@lReceiptAmount,0.00),1,2,@lOrgGroupId)
		SELECT  @lCurrencyCode = [sFinanceCommon].[fDefaultCurrencyCodeGet](@lOrgGroupId) 
		SELECT  @lCurrencyTitle = [sFinanceCommon].[fDefaultCurrencyTitleGet](@lOrgGroupId) 
	END
	ELSE
	BEGIN
		SELECT  @lCurrencyCode = [sFinanceCommon].[fDefaultCurrencyCodeGet](@rOrgGroupId) 
	END

	SELECT 
			FRD.ReceiptId,
			FR.ReceiptNumber,
			FR.ReceiptDate,
			SA.AcademicId,
			--SA.FirstName+' '+SA.MiddleName+' '+SA.LastName AS Name,
			SA.Name AS Name,
			SA.Code,
			SA.RollNo,
			SA.ClassId,
			SA.DivisionId,
			SA.ClassName,
			SA.DivisionName,
			SA.ClassName+' - '+SA.DivisionName AS ClassDivision,
			FM.FeeHeadId,
			FHM.Title AS FeeHead,
			FM.Id AS FeeId,
			FM.Title AS FeeName,
			FRD.Amount AS PaidAmount,
			PTM.Id AS PayTypeId,
			PTM.Title AS PayType,
			
			--changes by Jash on 06/11/2020--%
			CASE WHEN ISNULL(FR.ChequeNo,'') != '' THEN FR.ChequeNo ELSE CONVERT(nvarchar(20),ISNULL(FR.PaymentTransactionId,''))  END AS ChequeNo,
			--CASE WHEN ISNULL(FR.ChequeNo,'') != '' THEN FR.ChequeNo
			--	WHEN ISNULL(PT.BankTransactionId,'') = '' THEN  CONVERT(nvarchar(20),FR.PaymentTransactionId)
			--	ELSE CONVERT(nvarchar(20),FR.PaymentTransactionId) +' - '+ CONVERT(nvarchar(20),PT.BankTransactionId) END AS ChequeNo,
			--%


			--ISNULL(FR.ChequeNo,'') AS ChequeNo,
			ISNULL(FR.BankName,'') AS BankName,
			ISNULL(RBM.Address1,'') AS Address1,
			ISNULL(RBM.Address2,'') AS Address2,
			ISNULL(RBM.FullTitle,'') AS FullTitle,
			ISNULL(FR.TotalReceiptAmount,0.00) AS TotalReceiptAmount,
			ISNULL(FR.ReceiptAmount,0.00) AS ReceiptAmount,			
			
			(CASE WHEN @rOrgGroupId = 0 -- For Single Receipt
					THEN @lAmountInWord 
				  WHEN @rOrgGroupId != 0 -- For Bulk Receipt
					THEN sFinanceCommon.fNumberToWordsWithDecimal(ISNULL(FR.ReceiptAmount,0.00),1,2,@rOrgGroupId)
			END) AS ReceiptAmountInWords,
			
			ISNULL(FR.DiscountAmount,0.00) AS DiscountAmount,
			FR.Remark,
			(CASE WHEN  U.SourceTypeId = 2003 
			  THEN -- Navrachana
				'Online Generated'
				ELSE ISNULL(U.DisplayTitle,'') END) AS UserName,
			--ISNULL(U.DisplayTitle,'') AS UserName,
			OM.OrgLogo,
			RBM.Remark1,
			RBM.Remark2,			 
			ISNULL(RBM.IsOnPrePrinted,0) AS IsOnPrePrinted,
			ISNULL(RBM.IsSupressLogo,0) AS IsSupressLogo,
			ISNULL(RBM.IsDisplaySchoolName,0) AS IsDisplaySchoolName,
			RBM.ReportId,
			OM.Title AS OrganizationName,
			--(CASE  WHEN (RBM.ReportId = 20775 OR RBM.ReportId = 20777 OR RBM.ReportId = 20874 OR RBM.ReportId =20427 OR RBM.ReportId =20890 OR RBM.ReportId =20901 OR RBM.ReportId =20902) AND ISNULL(RBM.IsMonthDetailOnly,0) = 0  THEN 
			--	[sFee].[fFeeHeadMonthDetailGet](FR.ID,FHM.Id) -- Fee name in detail	
				  
			  
			(CASE WHEN  RBM.ReportId = 20576
			      THEN -- HM patel report
				  [sFee].[fReceiptMonthDetailGet](FR.ID,0)
			      WHEN RBM.ReportId = 30457 
			      THEN
				  [sFee].[fReceiptMonthDetailGet](FR.ID,1)		
			      WHEN ISNULL(RBM.IsMonthDetailOnly,0) = 1  AND ISNULL(RBM.IsFeeInvoiceHeadwise,0) = 1
			      THEN				
				  [sFee].[fFeeHeadMonthNameGet](FR.ID,FHM.Id) -- Head wise Month name in detail
				  WHEN ISNULL(RBM.IsMonthDetailOnly,0) = 1  AND ISNULL(RBM.IsFeeInvoiceHeadwise,0) = 0
			      THEN				
				  [sFee].[fFeeMonthNameGet](FR.ID,FM.Id) -- Fee wise Month name in detail
			ELSE '' END) 
			AS MonthDetail,    

 
			--FS.FeeDuration AS FeeDuration,
			
			Stuff((SELECT  N', ' +  FS1.FeeDuration FROM 
			sFee.tReceipt FR1				
			INNER JOIN sFee.tReceiptDetail FRD1	ON FR1.Id=FRD1.ReceiptId AND FR1.SourceTypeId=2001				
			INNER JOIN sFee.tFeeSetup FS1 ON FRD1.FeeSetupId = FS1.Id
			INNER JOIN sFee.tFeeMaster FM1 ON FS1.FeeId=FM1.Id						
			INNER JOIN sFee.tFeeHeadMaster FHM1 ON FHM1.Id=FM1.FeeHeadId 
			INNER JOIN sStudent.vStudentAcademic SA1 ON SA1.AcademicId = FR1.SourceId
			--Where FM1.FeeHeadId = FM.FeeHeadId AND FS.ClassId = FS1.ClassId AND FR1.Id = @rReceiptId
			Where FM1.Id = FM.Id AND FS.ClassId = FS1.ClassId AND FR1.Id = @rReceiptId
			AND (ISNULL(SA1.OrgId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rOrgIds,','))OR @rOrgIds='')
			 Order By FS.StartDate
			FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'' ) AS FeeDuration,
			
			
			DM.Title AS DepartmentName,
			FM.OrderIndex AS FeeOrderIndex,
			FHM.OrderIndex AS FeeHeadOrderIndex,
			FR.IsObsolete AS IsCancelReceipt,
			@lCurrencyCode AS CurrencyCode,
			@lCurrencyTitle AS CurrencyTitle,
			BM.Title AS Batch,
			FR.ChequeDate AS ChequeDate,
			ISNULL(RBM.IsMonthDetailOnly,0) AS IsMonthDetailOnly,
	        ISNULL(RBM.IsFeeInvoiceHeadwise,0) AS IsFeeInvoiceHeadwise,
	        ISNULL(RBM.GroupTitle,'') AS GroupTitle,
	        ISNULL(RBM.IsSuppressFeeDetail,0) AS IsSuppressFeeDetail,
	        ISNULL(CGM.Title,'') AS CollectionGroupName,
	        ISNULL(FI.InvoiceNumber,0) AS InvoiceNumber,
	        SA.SUID,
	        CASE
				 WHEN RBM.IsOnPrePrinted=1       
				 THEN 2
				 ELSE  ISNULL(RBM.HeaderDisplayId,1)
				END AS HeaderDisplayId,
	        RBM.ReceiptBookHeader,
	        RBM.ServiceTaxPercentage,
	        ISNULL(FR.AmountAfterDiscount,0) AS AmountAfterDiscount,
	        (CASE WHEN  FR.CGSTPercentage IS NULL THEN RBM.CGSTPercentage ELSE FR.CGSTPercentage END) AS CGSTPercentage,
	        (CASE WHEN  FR.SGSTPercentage IS NULL THEN RBM.SGSTPercentage ELSE FR.SGSTPercentage END) AS SGSTPercentage,
	        ISNULL(FR.CGSTAmount,0) AS CGSTAmount,
	        ISNULL(FR.SGSTAmount,0) AS SGSTAmount,
	        ISNULL(OOM.GSTINNo,'') AS GSTINNo,
	        ISNULL(OOM.HSNSACCode,'') AS HSNSACCode,
	        @lIDConfigValue AS ItemValue ,
	        SF.FatherName,
	        SF.MotherName,
	        ISNULL(FA.DiscountAmount,0) AS ExemptionAmount,
			ISNULL(FA.Remark,'') AS ExemptionRemark,
			EI.EmployeeSign,
			ISNULL(SC.PresentCity,'') AS PresentCity,

			(ISNULL((CASE WHEN SC.PresentStreetOne IS NOT NULL AND SC.PresentStreetOne!='' THEN ''+ SC.PresentStreetOne END),'')+
			ISNULL((CASE WHEN SC.PresentStreetTwo IS NOT NULL AND SC.PresentStreetTwo!=''  THEN ', '+ SC.PresentStreetTwo END),'')+
			ISNULL((CASE WHEN SC.PresentStreetThree IS NOT NULL AND SC.PresentStreetThree!=''  THEN ', '+ SC.PresentStreetThree END),'')+
			ISNULL((CASE WHEN SC.PresentCity IS NOT NULL AND SC.PresentCity!=''  THEN ', ' + SC.PresentCity  END),'')+
			ISNULL((CASE WHEN SC.PresentState IS NOT NULL AND SC.PresentState!=''  THEN  ', '+ SC.PresentState END),'')+
			ISNULL((CASE WHEN SC.PresentZipCode IS NOT NULL AND SC.PresentZipCode!=''  THEN  ', '+ SC.PresentZipCode  END),''))
			 AS PresentAdress,
			--ISNULL(SC.PresentStreetOne +','+ SC.PresentStreetTwo +','+ SC.PresentStreetThree +','+ SC.PresentCity +','+ SC.PresentState +'-'+ SC.PresentZipCode ,'') AS PresentAdress,
			SC.ParentContactNo,
			ISNULL(RBM.IsDisplayPendingFeeAmount,0) AS IsDisplayPendingFeeAmount,
			(CASE WHEN  ISNULL(RBM.IsDisplayPendingFeeAmount,0) = 0 THEN 0 ELSE ISNULL([sFee].[fPendingFeeAmountReceiptBookwiseGet](FR.SourceId,0),0) END) AS PendingFeeAmount,
			SA.FirstName,
			SA.LastName,
			ISNULL([sFee].[fFeeAmountSumGet](FR.SourceId),0) AS FeeAmount
	FROM 
	
			sFee.tReceipt FR				
			INNER JOIN sFee.tReceiptDetail FRD ON FR.Id=FRD.ReceiptId AND FR.SourceTypeId=2001				
			INNER JOIN sFee.tFeeSetup FS ON FRD.FeeSetupId = FS.Id				
			INNER JOIN sFee.tFeeMaster FM ON FS.FeeId=FM.Id						
			INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id=FM.FeeHeadId
			INNER JOIN sStudent.vStudentAcademic SA ON SA.AcademicId = FR.SourceId
			INNER JOIN sStudent.tStudentCommunication SC ON SC.StudentId = SA.StudentId
			INNER JOIn sFinanceCommon.tPayTypeMaster PTM ON PTM.Id=FR.PayTypeId
			LEFT JOIN sFee.tFeeAllocation FA ON FA.FeeSetupId=FS.Id AND FA.SourceId=SA.AcademicId
			INNER JOIN sFee.tReceiptBookMaster RBM  ON RBM.Id=FR.ReceiptBookId
			LEFT JOIN sSecurity.tUser U ON U.Id=FR.CreatedUserId
			INNER JOIN sSetup.tDepartmentMaster DM ON SA.DepartmentId =DM.Id
			LEFT JOIN sSetup.tOrganizationMaster OM ON OM.Id=DM.OrgId
			LEFT JOIN sSetup.tOrganizationOtherDetail OOM ON OM.Id=OOM.OrgId	
			INNER JOIN sSetup.tBatchMaster BM ON BM.Id = FR.BatchId 
			LEFT JOIN sFee.tCollectionGroupMaster CGM ON CGM.Id=FR.CollectionGroupId 
			LEFT JOIN sFee.tFeeInvoice FI ON FI.Id=FR.FeeInvoiceId
			LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId = SA.StudentId
			LEFT JOIN sHrm.tEmployeeImages EI ON EI.EmployeeId = U.SourceId AND U.SourceTypeId=95
			LEFT JOIN sOp.tPaymentTransaction PT ON PT.Id = FR.PaymentTransactionId
	WHERE 
			((FR.Id=@rReceiptId) OR (@rReceiptId = 0))
			AND ((RBM.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId = 0))
			AND ((FR.ReceiptBookId = @rReceiptBookId) OR (@rReceiptBookId = 0))
			AND ((FR.ReceiptNumber >= @rFromReceiptNo) OR (@rFromReceiptNo = 0))
			AND ((FR.ReceiptNumber <= @rToReceiptNo) OR (@rToReceiptNo = 0))
			AND ((FR.BatchId = @rBatchId) OR (@rBatchId = 0))
			AND ((BM.OrgId = @rOrgId) OR (@rOrgId = 0))
			AND ((FR.ReceiptDate = @rReceiptDate) OR (ISNULL(@rReceiptDate,'') = ''))
			AND ((SA.ClassId = @rClassId) OR (@rClassId = 0))
			AND (ISNULL(SA.OrgId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rOrgIds,','))OR @rOrgIds='')			
			AND ((ISNULL(FR.ReceiptPrintGroupId,'')=@rReceiptPrintGroupId) OR (ISNULL(@rReceiptPrintGroupId,'') = ''))
    ORDER BY 
            FS.StartDate,FHM.OrderIndex,FM.OrderIndex ASC 
END
GO
PRINT N'Altering [sFee].[pStudentLedgerGet]...';


GO

   
ALTER PROCEDURE [sFee].[pStudentLedgerGet]  
   
  @rCode  nvarchar(20)='',  
  @rSUId nvarchar(20)='',  
  @rDepositHeadId int,  
  @rOrgId int,  
  @rBatchId int=0,  
  @rAcademicId bigint=0  
WITH EXECUTE AS CALLER  
AS  
SET NOCOUNT ON   
BEGIN   
 SELECT A.DepositDate ,  
        A.Credit,  
        A.Debit,  
  A.ExpenseType,  
  A.TransactionType,  
  (CASE WHEN A.Debit = 0 THEN 'Credited' ELSE 'Debited' END)AS PayType  
         FROM(  
   SELECT   
   DA.DepositDate AS DepositDate,  
   ISnull (DA.Amount,0) AS Credit,  
      0 AS Debit,  
   '' AS ExpenseType,  
   'Deposit Add' AS TransactionType  ,
   DA.CreatedDateTime
   FROM sStudent.vStudentAcademic  VA  
   INNER JOIN sFee.tDepositAdd DA  ON VA.AcademicId = DA.AcademicId   
   Where   
   ((VA.Code = @rCode) OR (ISNULL(@rCode,'') = ''))  
   AND ((VA.SUId like @rSUId) OR (ISNULL(@rSUId,'')=''))  
   AND (DA.DepositHeadId = @rDepositHeadId)   
   AND ((VA.OrgId = @rOrgId) OR (ISNULL(@rOrgId,0) = 0))   
   AND VA.StudentId= @rAcademicId  
   ---AND VA.BatchId = @rBatchId  
   UNION ALL   
   SELECT   
   DAJ.AdjustmentDate As DepositDate,  
   0  AS Credit,  
   ISnull (DAJ.Amount,0)  AS Debit,  
   ISNULL(ETM.Title,'')AS ExpenseType,  
   ('Expense'+' '+ISNULL(ETM.Title,'')) AS TransactionType  ,
   DAJ.CreatedDateTime
   FROM sStudent.vStudentAcademic  VA  
     INNER JOIN sFee.tDepositAdjustment DAJ ON DAJ.AcademicId = VA.AcademicId  
     INNER JOIN sFee.tFeeDepositExpenseTypeMaster ETM ON ETM.Id =DAJ.FeeDepositExpenseTypeId  
   Where   
   ((VA.Code = @rCode) OR (ISNULL(@rCode,'') = ''))  
   AND ((VA.SUId like @rSUId) OR (ISNULL(@rSUId,'')=''))  
   AND (DAJ.DepositHeadId = @rDepositHeadId)   
   AND ((VA.OrgId = @rOrgId) OR (ISNULL(@rOrgId,0) = 0))   
   --AND VA.BatchId = @rBatchId  
   AND VA.StudentId = @rAcademicId  
   UNION ALL   
   SELECT   
   DR.RefundDate As DepositDate,  
   0  AS Credit,  
   ISnull (DR.RefundAmount,0)  AS Debit,  
   '' AS ExpenseType,  
   'Deposit Refund' AS TransactionType ,
    DR.CreatedDateTime
   FROM sStudent.vStudentAcademic  VA  
     INNER JOIN sFee.tDepositRefund DR ON DR.AcademicId = VA.AcademicId  
   Where   
   ((VA.Code = @rCode) OR (ISNULL(@rCode,'') = ''))  
   AND ((VA.SUId like @rSUId) OR (ISNULL(@rSUId,'')=''))  
   AND (DR.DepositHeadId = @rDepositHeadId)   
   AND ((VA.OrgId = @rOrgId) OR (ISNULL(@rOrgId,0) = 0))   
     -- AND VA.BatchId = @rBatchId  
      AND VA.StudentId = @rAcademicId  
   )A   
   Order BY A.DepositDate,A.CreatedDateTime
     
END
GO
PRINT N'Altering [sOp].[pGatewayProductAdd]...';


GO
 
ALTER PROCEDURE [sOp].[pGatewayProductAdd]
	 
        @rId int,
	    @rGatewayConfigurationId int,
		@rTitle nvarchar(150),
		@rGatewayURL nvarchar(1000),
		@rServerPort nvarchar(50),
		@rLoginId nvarchar(50),
		@rLoginPassword nvarchar(50),
		@rProductId nvarchar(100),
		@rOrgGroupId int,
		@rCCAvenueMerchantID nvarchar(50)='',
		@rCCAvenueAccessCode nvarchar(100)='',
		@rCCAvenueWorkingKey nvarchar(100)='',
		@rCCAvenueWhitelistURL nvarchar(500)='',
		@rCCAvenueSubAccountId nvarchar(100)='',
		
		@rAtomReqHashKey nvarchar(100)='',
		@rAtomRespHashKey nvarchar(100)='',
		@rPaytmMID nvarchar(50)='',
		@rPaytmMerchantKey nvarchar(50)='',
		@rPaytmChannelId nvarchar(20)='',
		@rPaytmIndustryTypeId nvarchar(20)='',
		@rPaytmWebsite nvarchar(20)='',
		@rPaytmStatusCheckURL nvarchar(500)='',
		@rPayUMoneyMERCHANTID nvarchar(20)='',
		@rPayUMoneyMERCHANTKEY nvarchar(20)='',
		@rPayUMoneySALT nvarchar(20)='',
		@rPayUMoneyHASHSEQUENCE nvarchar(200)='',
		@rPayUMoneyAUTHHEADER nvarchar(50)='',
		@rBillDeskMerchantID nvarchar(50)='',
		@rBillDeskChecksumKey nvarchar(50)='',
		@rBillDeskSecurityID nvarchar(50)='',
		@rBillDeskUniqueIdentifier nvarchar(50)='',
		@rEazypayMerchantId nvarchar(50)='',
		@rEazypayAESKey nvarchar(50)='',
		@rAxisMerchantId nvarchar(50)='',
		@rAxisAPIKey nvarchar(50)='',
		@rAxisResponseKey nvarchar(50)='',
		@rRazorpayMID nvarchar(50)='',
		@rRazorpayKeyID nvarchar(50)='',
		@rRazorpaySecretKey nvarchar(50)=''


		
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY

	IF(@rId = 0)	
	BEGIN
		IF EXISTS(SELECT Id FROM [sOp].[tGatewayProduct] WHERE GatewayConfigurationId=@rGatewayConfigurationId AND Title=@rTitle AND ProductId = @rProductId)
		BEGIN
			SELECT -101 AS Result
			RETURN
		END 
	END
	ELSE
	BEGIN
		IF  EXISTS(SELECT Id FROM [sOp].[tGatewayProduct] WHERE GatewayConfigurationId=@rGatewayConfigurationId AND Title=@rTitle AND ProductId = @rProductId AND Id<>@rId)
		BEGIN
			SELECT -101 AS Result
			RETURN
		END
	END	

	BEGIN TRANSACTION	
	DECLARE @lNextNumber INT     
	IF(@rId = 0)	
	BEGIN

	EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sOp.tGatewayProduct', 0, 1, 1,@outNextNumber = @lNextNumber output
	
	INSERT INTO [sOp].[tGatewayProduct]
		(
			[Id],
			[GatewayConfigurationId],
			[Title],
			[GatewayURL],
			[ServerPort],
			[LoginId],
			[LoginPassword],
			[ProductId],
			[CCAvenueMerchantID],
			[CCAvenueAccessCode],
			[CCAvenueWorkingKey],
			[CCAvenueWhitelistURL],
			[CCAvenueSubAccountId],
			[AtomReqHashKey],
			[AtomRespHashKey],
			[PaytmMID],
			[PaytmMerchantKey],
			[PaytmChannelId],
			[PaytmIndustryTypeId],
			[PaytmWebsite],
			[PaytmStatusCheckURL],
			[PayUMoneyMERCHANTID],
			[PayUMoneyMERCHANTKEY],
			[PayUMoneySALT],
			[PayUMoneyHASHSEQUENCE],
			[PayUMoneyAUTHHEADER],
			[BillDeskMerchantID],
			[BillDeskChecksumKey],
			[BillDeskSecurityID],
			[BillDeskUniqueIdentifier],
			[EazypayMerchantId],
			[EazypayAESKey],
			[AxisMerchantId],
			[AxisAPIKey],
			[AxisResponseKey],
			[RazorpayMID],
			[RazorpayKeyID],
			[RazorpaySecretKey]
		)
	VALUES
		(
			@lNextNumber,
			@rGatewayConfigurationId,
			@rTitle,
			@rGatewayURL,
			@rServerPort,
			@rLoginId,
			@rLoginPassword,
			@rProductId,
			@rCCAvenueMerchantID,
			@rCCAvenueAccessCode,
			@rCCAvenueWorkingKey,
			@rCCAvenueWhitelistURL,
			@rCCAvenueSubAccountId,
			@rAtomReqHashKey,
			@rAtomRespHashKey,
			@rPaytmMID,
			@rPaytmMerchantKey,
			@rPaytmChannelId,
			@rPaytmIndustryTypeId,
			@rPaytmWebsite,
			@rPaytmStatusCheckURL,
			@rPayUMoneyMERCHANTID,
			@rPayUMoneyMERCHANTKEY,
			@rPayUMoneySALT,
			@rPayUMoneyHASHSEQUENCE,
			@rPayUMoneyAUTHHEADER,
			@rBillDeskMerchantID,
			@rBillDeskChecksumKey,
			@rBillDeskSecurityID,
			@rBillDeskUniqueIdentifier,
			@rEazypayMerchantId,
			@rEazypayAESKey,
			@rAxisMerchantId,
			@rAxisAPIKey,
			@rAxisResponseKey,
			@rRazorpayMID,
			@rRazorpayKeyID,
			@rRazorpaySecretKey
		)
		    SELECT 1 AS Result
	END	 
ELSE
	 BEGIN
		SET @lNextNumber = @rId
	    UPDATE  [sOp].[tGatewayProduct]
	    SET
			[GatewayConfigurationId]=@rGatewayConfigurationId,
			[Title]=@rTitle,
			[GatewayURL]=@rGatewayURL,
			[ServerPort]=@rServerPort,
			[LoginId]=@rLoginId,
			[LoginPassword]=@rLoginPassword,
			[ProductId]=@rProductId,
			[CCAvenueMerchantID]=@rCCAvenueMerchantID,
			[CCAvenueAccessCode]=@rCCAvenueAccessCode,
			[CCAvenueWorkingKey]=@rCCAvenueWorkingKey,
			[CCAvenueWhitelistURL]=@rCCAvenueWhitelistURL,
			[CCAvenueSubAccountId]=@rCCAvenueSubAccountId,
			[AtomReqHashKey]=@rAtomReqHashKey,
			[AtomRespHashKey]=@rAtomRespHashKey,
			[PaytmMID]=@rPaytmMID,
			[PaytmMerchantKey]=@rPaytmMerchantKey,
			[PaytmChannelId]=@rPaytmChannelId,
			[PaytmIndustryTypeId]=@rPaytmIndustryTypeId,
			[PaytmWebsite]=@rPaytmWebsite,
			[PaytmStatusCheckURL]=@rPaytmStatusCheckURL,
			[PayUMoneyMERCHANTID]=@rPayUMoneyMERCHANTID,
			[PayUMoneyMERCHANTKEY]=@rPayUMoneyMERCHANTKEY,
			[PayUMoneySALT]=@rPayUMoneySALT,
			[PayUMoneyHASHSEQUENCE]=@rPayUMoneyHASHSEQUENCE,
			[PayUMoneyAUTHHEADER]=@rPayUMoneyAUTHHEADER,
			[BillDeskMerchantID]=@rBillDeskMerchantID,
			[BillDeskChecksumKey]=@rBillDeskChecksumKey,
			[BillDeskSecurityID]=@rBillDeskSecurityID,
			[BillDeskUniqueIdentifier]=@rBillDeskUniqueIdentifier,
			[EazypayMerchantId]=@rEazypayMerchantId,
			[EazypayAESKey]=@rEazypayAESKey,
			[AxisMerchantId]=@rAxisMerchantId,
			[AxisAPIKey]=@rAxisAPIKey,
			[AxisResponseKey]=@rAxisResponseKey,
			[RazorpayMID]=@rRazorpayMID,
			[RazorpayKeyID]=@rRazorpayKeyID,
			[RazorpaySecretKey]=@rRazorpaySecretKey

	    WHERE Id = @rId
	    SELECT 11 AS Result
	 END  	

	COMMIT TRANSACTION		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
END
GO
PRINT N'Altering [sOp].[pGatewayProductGet]...';


GO
 
 ALTER PROCEDURE [sOp].[pGatewayProductGet]

	 @rId int,
	 @rGatewayConfigurationId int,
	 @rTitle nvarchar(150),
	 @rOrgGroupId int
	 	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 
	BEGIN
			SET @rTitle = sCommon.fSearchStringGet(@rTitle)
	END
	
	SELECT 
			GP.Id,
			GP.GatewayConfigurationId,
			PGM.Title AS PaymentGatewayName,
			GP.Title AS GatewayProductName,
			ISNULL(GP.GatewayURL,'') AS GatewayURL,
			ISNULL(GP.ServerPort,'') AS ServerPort,
			ISNULL(GP.LoginId,'') AS LoginId,
			ISNULL(GP.LoginPassword,'') AS LoginPassword,
			ISNULL(GP.ProductId,'') AS ProductId,
			
			ISNULL(GP.CCAvenueMerchantID,'') AS CCAvenueMerchantID,
			ISNULL(GP.CCAvenueAccessCode,'') AS CCAvenueAccessCode,
			ISNULL(GP.CCAvenueWorkingKey,'') AS CCAvenueWorkingKey,
			ISNULL(GP.CCAvenueWhitelistURL,'') AS CCAvenueWhitelistURL,
			ISNULL(GP.CCAvenueSubAccountId,'') AS CCAvenueSubAccountId,
			
			ISNULL(GP.AtomReqHashKey,'') AS AtomReqHashKey,
			ISNULL(GP.AtomRespHashKey,'') AS AtomRespHashKey,
			ISNULL(GP.PaytmMID,'') AS PaytmMID,
			ISNULL(GP.PaytmMerchantKey,'') AS PaytmMerchantKey,
			ISNULL(GP.PaytmChannelId,'') AS PaytmChannelId,
			ISNULL(GP.PaytmIndustryTypeId,'') AS PaytmIndustryTypeId,
			ISNULL(GP.PaytmWebsite,'') AS PaytmWebsite,
			ISNULL(GP.PaytmStatusCheckURL,'') AS PaytmStatusCheckURL,
			ISNULL(GP.PayUMoneyMERCHANTID,'') AS PayUMoneyMERCHANTID,
			ISNULL(GP.PayUMoneyMERCHANTKEY,'') AS PayUMoneyMERCHANTKEY,
			ISNULL(GP.PayUMoneySALT,'') AS PayUMoneySALT,
			ISNULL(GP.PayUMoneyHASHSEQUENCE,'') AS PayUMoneyHASHSEQUENCE,
			ISNULL(GP.PayUMoneyAUTHHEADER,'') AS PayUMoneyAUTHHEADER,
			
			ISNULL(GP.BillDeskMerchantID,'') AS BillDeskMerchantID,
			ISNULL(GP.BillDeskChecksumKey,'') AS BillDeskChecksumKey,
			ISNULL(GP.BillDeskSecurityID,'') AS BillDeskSecurityID,
			ISNULL(GP.BillDeskUniqueIdentifier,'') AS BillDeskUniqueIdentifier,
			ISNULL(GP.EazypayMerchantId,'') AS EazypayMerchantId,
			ISNULL(GP.EazypayAESKey,'') AS EazypayAESKey,

			ISNULL(GP.AxisMerchantId,'') AS AxisMerchantId,
			ISNULL(GP.AxisAPIKey,'') AS AxisAPIKey,
			ISNULL(GP.AxisResponseKey,'') AS AxisResponseKey,

			ISNULL(GP.RazorpayMID,'') AS RazorpayMID,
			ISNULL(GP.RazorpayKeyID,'') AS RazorpayKeyID,
			ISNULL(GP.RazorpaySecretKey,'') AS RazorpaySecretKey
	FROM 
			[sOp].[tGatewayProduct] GP
			INNER JOIN [sOp].[tGatewayConfiguration] GC ON GC.Id = GP.GatewayConfigurationId
			INNER JOIN [sOp].[tPaymentGatewayMaster] PGM ON PGM.Id = GC.PaymentGatewayId
	WHERE 
			(GC.OrgGroupId = @rOrgGroupId)
			AND	((GP.Id = @rId) OR (@rId = 0))
			AND	((GP.GatewayConfigurationId = @rGatewayConfigurationId) OR (@rGatewayConfigurationId = 0))
			AND ((GP.Title like @rTitle) OR (ISNULL(@rTitle,'') = ''))
			AND ISNULL(GC.IsObsolete,0) = 0
END
GO
PRINT N'Altering [sPayroll].[pEmployeePayrollListGet]...';


GO
 
ALTER PROCEDURE [sPayroll].[pEmployeePayrollListGet]

	@rPayrollId int,
	@rPayrollGroupId int,
	@rForYear int,
	@rForMonth int
 
AS
SET NOCOUNT ON

BEGIN
		SELECT 
			EM.Code AS Code,
			ISNULL(EM.FirstName, '') + ' ' + ISNULL(EM.MiddleName, '') + ' ' + ISNULL(EM.LastName, '') AS Name,
			ISNULL(ETM.Title,'') As EmployeeType,
			OM.DisplayTitle AS OrganizationName,
			ODM.Title AS OrganizationDepartmentName,
			PGM.Title AS PayrollGroupName,
			ISNULL(AT.TotalWorkingDays,0) AS WorkingDays, 
			ISNULL(AT.LWP,0) AS LWP,
			(ISNULL(AT.TotalWorkingDays,0) - ISNULL(AT.LWP,0) ) AS PresentDays
		FROM 
			sHrm.tEmployeeMaster EM
			INNER JOIN sHrm.tEmployeeJobDetail EJD ON EJD.EmployeeId = EM.Id
			INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=EM.OrgId
			INNER JOIN sHrm.tEmployeeStatutoryAndOther ESO ON ESO.EmployeeId = EM.Id  
			INNER JOIN sPayroll.tPayrollGroupMaster PGM ON ESO.PayrollGroupId = PGM.ID
			INNER JOIN sSetup.tDepartmentMaster ODM ON ODM.Id = EJD.OrganizationDepartmentId
			LEFT JOIN sHrm.tEmployeeTypeMaster ETM ON ETM.Id = EJD.EmployeeTypeId			
			INNER JOIN sHrm.tEmployeeAttendance AT ON AT.EmployeeId=EM.Id
			INNER JOIN sSetup.tBatchMaster BM ON AT.BatchId = BM.Id	AND BM.IsActive = 1
		WHERE 
			ESO.PayrollId = @rPayrollId 
			AND ((ESO.PayrollGroupId=@rPayrollGroupId) OR (@rPayrollGroupId=0))
			AND AT.ForYear = @rForYear AND AT.ForMonth = @rForMonth
			AND ISNULL(EM.IsOtherUser,0) = 0 
		ORDER BY EM.OrgId,EM.Number,EM.Code
END
GO
PRINT N'Altering [sPayroll].[pPaySetupElementWiseEmployeeGet]...';


GO

ALTER PROCEDURE [sPayroll].[pPaySetupElementWiseEmployeeGet]
	 @rSourceTypeId smallint,
	 @rSourceId bigint,
	 @rPayrollId int,
	 @rPayElementId int,
	 @rPayrollYearId int=0,
	 @rPayrollGroupId int = 0
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN 
    SELECT  
		ISNULL(PS.Id,0) AS PaySetupId,
		EM.EmployeeId,
		EM.EmployeeName,
		EM.Code,
		EM.DepartmentName,
		PS.Amount,
		ISNULL(PE.SortOrder,0) AS ProcessOrder,
		ISNULL(PS.PayElementId,0) AS PayElementId,
		ISNULL(PS.DefaultAmount,0) AS DefaultAmount 
	FROM sHrm.vEmployeeCommonDetail EM
		INNER JOIN sHrm.tEmployeeStatutoryAndOther ESO ON EM.EmployeeId = ESO.EmployeeId	 
		LEFT JOIN sPayroll.tPaySetup PS ON PS.SourceId = EM.EmployeeId  
		AND SourceTypeId = @rSourceTypeId 
		AND PS.PayElementId = @rPayElementId
		AND PS.PayRollId = ESO.PayrollId
		AND PS.PayRollYearId = @rPayrollYearId
		INNER JOIN sPayroll.tPaySetup PS1 ON PS1.SourceId = ESO.PayrollGroupId AND PS1.SourceTypeId = 2020
		LEFT JOIN sPayroll.tPayElement PE ON PS1.PayElementId = PE.ID 
	WHERE  ESO.PayrollId = @rPayrollId 
		AND (ESO.PayrollGroupId = @rPayrollGroupId OR @rPayrollGroupId=0)
		AND EM.EmployeeStatusId=7
		AND ISNULL(EM.IsOtherUser,0)=0 
		AND PE.ID=@rPayElementId
	ORDER BY EM.OrgId,EM.Number
END
GO
PRINT N'Altering [sSecurity].[pOrgGroupMobileAppAdd]...';


GO
 
ALTER PROCEDURE [sSecurity].[pOrgGroupMobileAppAdd]
	  @rOrgGroupId int,
	 @rCode nvarchar(20), 
	 @rTitle nvarchar(100), 
	 @rApiUrl nvarchar(200), 
	 @rIsMobileSiteAccess bit,
	 @rIsStudentPortalAccess bit,
	 @rActivationDate date 	,
	 @rPackageName nvarchar(100),
	 @rSMSProviderId tinyint,
	 @rBundleId nvarchar(100),
	 @rLoginById smallint = 0
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY
	
	IF NOT EXISTS(SELECT 1 FROM sSecurity.tOrgGroupMobileApp Where OrgGroupId = @rOrgGroupId )
	BEGIN
		If Exists(select OrgGroupId from sSecurity.tOrgGroupMobileApp where Code=@rCode)
			BEGIN
			select -102 as Result
			RETURN
		END 
	
		If Exists(select OrgGroupId from sSecurity.tOrgGroupMobileApp where Title=@rTitle)
			BEGIN
			select -103 as Result
			RETURN
		END 
	END
	ELSE
	BEGIN
		If Exists(select OrgGroupId from sSecurity.tOrgGroupMobileApp where Code=@rCode AND OrgGroupId <> @rOrgGroupId)
		Begin
			select -102 as Result
			RETURN
		End
		If  Exists(select OrgGroupId from sSecurity.tOrgGroupMobileApp where Title=@rTitle  AND OrgGroupId <> @rOrgGroupId)
		Begin
			select -103 as Result
			RETURN
		End

	END
	
	 BEGIN TRANSACTION	
	 DECLARE @lNextNumber BIGINT 

IF NOT EXISTS(SELECT 1 FROM sSecurity.tOrgGroupMobileApp Where OrgGroupId = @rOrgGroupId )
	BEGIN
		
	INSERT INTO sSecurity.tOrgGroupMobileApp
		(
		OrgGroupId,
		Code,
		Title,
		ApiUrl,
		IsMobileSiteAccess,
		IsStudentPortalAccess,
		ActivationDate,
		PackageName,
		SMSProviderId,
		BundleId,
		LoginById 
		)
	VALUES
		(
			 @rOrgGroupId, 
			 @rCode ,
			 @rTitle ,
			 @rApiUrl,
			 @rIsMobileSiteAccess,
			 @rIsStudentPortalAccess ,
			 @rActivationDate,
			 @rPackageName,
			 @rSMSProviderId,
			 @rBundleId,
			 @rLoginById
		)
		    SELECT 1 AS Result
	END	 

ELSE
	 BEGIN
		
	    UPDATE sSecurity.tOrgGroupMobileApp
	    SET 
				--OrgGroupId,
				Code=@rCode,
				Title=@rTitle,
				ApiUrl=@rApiUrl,
				IsMobileSiteAccess=@rIsMobileSiteAccess,
				IsStudentPortalAccess=@rIsStudentPortalAccess,
				ActivationDate=@rActivationDate,
				PackageName = @rPackageName,
			    SMSProviderId = @rSMSProviderId,
			    BundleId = @rBundleId,
				LoginById = @rLoginById
	      WHERE 	OrgGroupId = @rOrgGroupId
		
	      SELECT 11 AS Result
	 END  	

	COMMIT TRANSACTION		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END
GO
PRINT N'Altering [sSecurity].[pOrgGroupMobileAppGet]...';


GO
 
ALTER PROCEDURE  [sSecurity].[pOrgGroupMobileAppGet]

	 @rOrgGroupId int,
	 @rOrgId smallint,
	 @rCode nvarchar(20), 
	 @rTitle nvarchar(100)
	 
	
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 
		BEGIN
		SET @rTitle = sCommon.fSearchStringGet(@rTitle)
		SET @rCode = sCommon.fSearchStringGet(@rCode)
	END
	
	SELECT 
			OGM.OrgGroupId,
			OGM.Code,
			OGM.Title,
			OGM.ApiUrl,
			OGM.IsMobileSiteAccess,
			OGM.IsStudentPortalAccess,
			OGM.ActivationDate,
			CM.Title AS CustomerName,
			ISNULL(OGM.PackageName,'') As PackageName,
			ISNULL(OGM.SMSProviderId,0) As SMSProviderId,
			ISNULL(OGM.BundleId,'') As BundleId,
			ISNULL(OGM.LoginById,0) AS LoginById
	FROM 
	sSecurity.tOrgGroupMobileApp OGM
		LEFT JOIN sErp.tCustomerMaster CM ON  CM.OrgGroupId=OGM.OrgGroupId
			
	WHERE 
	
			((OGM.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId= 0))
				AND((CM.OrgId = @rOrgId)OR (@rOrgId=0))
				AND ((OGM.Title like @rTitle) OR (ISNULL(@rTitle,'') = ''))
		AND ((OGM.Code like @rCode) OR (ISNULL(@rCode,'') = ''))
    order by OGM.ActivationDate desc
END
GO
PRINT N'Altering [sSecurity].[pPendingEmployeePasswordGet]...';


GO
 
ALTER PROCEDURE [sSecurity].[pPendingEmployeePasswordGet]
	
	@rOrgId smallint,
	@rDepartmentId int,
	@rIsPendingLogin bit,
	@rIsCreatedLogin bit,
	@rEmployeeGroupId int = 0,
	@rStatusId int = 0,
	@rUserId BigInt
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
	SELECT 
			ISNULL(U.Id,0) AS Id,
			EC.EmployeeId,
			EC.Code,
			EC.EmployeeName AS Name,
			ISNULL(EC.MobileNumber,'') AS MobileNo,
			ISNULL(EC.CompanyEmail,'') AS EmailId,
			U.LoginTitle AS UserName,
			U.LoginPassword,
			ISNULL(R.Id,0) AS RoleId,
			ISNULL(R.Title,'') AS Role,
			(CASE WHEN A.UserId IS NULL THEN 'Not Register' ELSE 'Register' END) AS UserRegisterStatus
	FROM 
			sHrm.vEmployeeCommunication EC
	LEFT JOIN sSecurity.tUser U ON U.SourceId=EC.EmployeeId AND U.SourceTypeId=95
	LEFT JOIN sSecurity.tUserRole UR ON UR.UserId=U.Id AND UR.IsPrimary = 1 AND UR.IsObsolete = 0
	LEFT JOIN sSecurity.tRole R ON UR.RoleId=R.Id
	LEFT JOIN 
			(
				SELECT DISTINCT UserId FROM sCommunication.tRegisteredUsers
			) AS A ON A.UserId = U.Id
	INNER JOIN sSetup.vDepartment VD ON VD.Id = EC.DepartmentId AND VD.OrgId = EC.OrgID
	
	WHERE 
		((EC.DepartmentId=@rDepartmentId) OR (@rDepartmentId=0))
		AND	((EC.EmployeeGroupId=@rEmployeeGroupId) OR (@rEmployeeGroupId=0))
		AND	(EC.OrgId = @rOrgId)
		AND (EC.IsObsolete=0)
		AND ((U.LoginPassWord is Null AND @rIsPendingLogin=1)
		OR (U.LoginPassWord is NOT Null AND @rIsCreatedLogin=1))
		AND ((EC.StatusId=@rStatusId) OR (@rStatusId=0))
		AND VD.UserId = @rUserId
		AND VD.IsObsolete = 0
	Order By 
		EC.Number
END
GO
PRINT N'Altering [sSupport].[pContactTypeMasterAdd]...';


GO

ALTER PROCEDURE [sSupport].[pContactTypeMasterAdd]
	 
	@rId int,
	@rCode nvarchar(100),
	@rTitle nvarchar(100),	
	@rCreatedUserId bigint,
	@rUpdatedUserId bigint,
	@rIsObsolete bit,
	@rOrgId int
	
	
	
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY
	
	
		IF(@rId = 0)	
	BEGIN
		If Exists(select Id from sSupport.tContactTypeMaster where Code=@rCode AND OrgId=@rOrgId)
			BEGIN
			select -103 as Result
			RETURN
		END 
 
	END
	ELSE
	BEGIN
 
		If  Exists(select Id from sSupport.tContactTypeMaster where Code=@rCode AND Id<>@rId AND OrgId=@rOrgId)
		Begin
			select -103 as Result
			RETURN
		End

	END
	
	
	
	
	 BEGIN TRANSACTION	
 IF(@rId = 0)	
BEGIN

	DECLARE @lNextNumber INT     
	EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sSupport.tContactTypeMaster', 0, 1, 1,@outNextNumber = @lNextNumber output


	INSERT INTO sSupport.tContactTypeMaster
		(
			Id,
			Code,
			Title,
			CreatedUserId,
			CreatedDateTime,
			IsObsolete,
			OrgId
			
		)
	VALUES
		(
			@lNextNumber,
			@rCode,
			@rTitle,
			@rCreatedUserId,
			sCommon.fGetDate(),
			@rIsObsolete,
			@rOrgId
			
		)
		 SELECT 1 AS RESULT            
	END	 
	 ELSE
	 BEGIN
	 SET @lNextNumber = @rId
	    UPDATE sSupport.tContactTypeMaster
	    SET
	      Code=@rCode,
	      Title=@rTitle,
	      IsObsolete=@rIsObsolete,
	      UpdatedUserId=@rUpdatedUserId,
	      UpdatedDateTime= sCommon.fGetDate(),
	      OrgId=@rOrgId
	     
	      
	      
	      WHERE Id = @rId 	   
	 END  
		SELECT @lNextNumber AS Result
	
		COMMIT TRANSACTION
		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END
GO
PRINT N'Altering [sTally].[pReceiptPostingGet]...';


GO
 
ALTER PROCEDURE [sTally].[pReceiptPostingGet]

	 @rOrgGroupBatchId int,
	 @rReceiptBookId int,
	 @rReceiptDate Date,
	 @rToReceiptDate Date
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 
	
	DECLARE @lTallyPostingType nVarchar(100)
	DECLARE @lOrgGroupId INT
	
	SELECT @lOrgGroupId = OrgGroupId from sSetup.tOrgGroupBatchMaster Where Id=@rOrgGroupBatchId
	
	SELECT @lTallyPostingType = CASE WHEN CS.ItemValue IS NULL THEN C.ItemValue ELSE CS.ItemValue END 
		FROM sConfig.tConfigurationMaster C
        INNER JOIN sConfig.tConfigurationLevelMaster CM ON CM.Id = C.ConfigurationLevelId
        LEFT JOIN sConfig.tConfigurationSetup CS ON CS.ConfigurationId = C.Id AND CS.SOURCEID= @lOrgGroupId
        WHERE  C.ItemKey = 'Tally.PostingType'
        
		
        IF @lTallyPostingType = 'FeeHeadWise' 
        BEGIN
			   SELECT	ReceiptDate,
						ISNULL(FHM.Title,'') AS FeeName,
						PM.Title AS PayType,
						Sum(FRD.Amount) AS Amount,
						'' as Narration,						
						FR.PayTypeId AS PayTypeId, 
						FHM.Id AS FeeId,
						0 AS ReceiptId
				FROM sFee.tReceipt FR 
					INNER JOIN sFee.tReceiptDetail FRD ON FR.Id = FRD.ReceiptId
					INNER JOIN sFee.tFeeSetup FS ON FS.Id = FRD.FeeSetupId
					INNER JOIN sFee.tFeeMaster FM ON FM.Id = FS.FeeId
					INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id = FM.FeeHeadId
					INNER JOIN sFinanceCommon.tPayTypeMaster PM ON PM.Id = FR.PayTypeId
					WHERE	FR.ReceiptBookId = @rReceiptBookId 
							AND FR.IsObsolete = 0 
							AND FR.OrgGroupBatchId = @rOrgGroupBatchId
							AND FR.ReceiptDate >= @rReceiptDate AND FR.ReceiptDate <= @rToReceiptDate
				group by ReceiptDate,FHM.Title,PM.Title,FHM.Id,FR.PayTypeId
				Order By ReceiptDate Desc
			END				
       ELSE IF @lTallyPostingType = 'FeeHeadWiseCustom' 
        BEGIN
			   SELECT	ReceiptDate,
						FR.Id AS FeeName,
						PM.Title AS PayType,
						Sum(FRD.Amount) AS Amount,
						SM.Name + '(' + ClassName + ':' + DivisionName + ') ' + ' # Cheque No. :' + ISNULL(FR.ChequeNo,'') + ' BankName :' + ISNULL(FR.BankName,'')  as Narration,						
						FR.PayTypeId AS PayTypeId, 
						0 AS FeeId,
						FR.Id AS ReceiptId
				FROM sFee.tReceipt FR 
					INNER JOIN sFee.tReceiptDetail FRD ON FR.Id = FRD.ReceiptId
					INNER JOIN sStudent.vStudentAcademic SM ON SM.ACademicId = FR.SourceID AND SM.OrgGRoupBatchId = FR.OrgGRoupBatchId 
					INNER JOIN sFee.tFeeSetup FS ON FS.Id = FRD.FeeSetupId
					INNER JOIN sFee.tFeeMaster FM ON FM.Id = FS.FeeId
					INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id = FM.FeeHeadId
					INNER JOIN sFinanceCommon.tPayTypeMaster PM ON PM.Id = FR.PayTypeId
					INNER JOIN sSetup.tOrganizationMaster OM ON  OM.Id=SM.OrgId
					LEFT JOIN sSetup.tOrgSubGroupMaster OSM ON OSM.id=OM.OrgSubGroupId

					WHERE	FR.ReceiptBookId= @rReceiptBookId 
							AND FR.IsObsolete = 0 
							AND FR.OrgGroupBatchId = @rOrgGroupBatchId
							AND FR.ReceiptDate >= @rReceiptDate AND FR.ReceiptDate <= @rToReceiptDate
				group by ReceiptDate,PM.Title,FR.PayTypeId,FR.Id,SM.Name + '(' + ClassName + ':' + DivisionName + ') ' + ' # Cheque No. :' + ISNULL(FR.ChequeNo,'') + ' BankName :' + ISNULL(FR.BankName,'')
				Order By FR.Id,ReceiptDate Desc
			END	

       ELSE IF @lTallyPostingType = 'OrgMainGroupWise' 
        BEGIN
			   SELECT	ReceiptDate,
						SM.AcademicId AS FeeName,
						PM.Title AS PayType,
						Sum(FRD.Amount) AS Amount,
						SM.Name + '(' + ClassName + ':' + DivisionName + ') ' + ' # Cheque No. :' + ISNULL(FR.ChequeNo,'') + ' BankName :' + ISNULL(FR.BankName,'')  as Narration,						
						FR.PayTypeId AS PayTypeId, 
						FR.Id AS FeeId,
						SM.AcademicId AS ReceiptId
				FROM sFee.tReceipt FR 
					INNER JOIN sFee.tReceiptDetail FRD ON FR.Id = FRD.ReceiptId
					INNER JOIN sStudent.vStudentAcademic SM ON SM.ACademicId = FR.SourceID AND SM.OrgGRoupBatchId = FR.OrgGRoupBatchId 
					INNER JOIN sFee.tFeeSetup FS ON FS.Id = FRD.FeeSetupId
					INNER JOIN sFee.tFeeMaster FM ON FM.Id = FS.FeeId
					INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id = FM.FeeHeadId
					INNER JOIN sFinanceCommon.tPayTypeMaster PM ON PM.Id = FR.PayTypeId
					INNER JOIN sSEtup.tOrganizationMaster OM ON OM.OrgSubGRoupID =  @rReceiptBookId AND OM.ID = SM.OrgId					
					WHERE	FR.IsObsolete = 0 
							AND FR.OrgGroupBatchId = @rOrgGroupBatchId
							AND FR.ReceiptDate >= @rReceiptDate AND FR.ReceiptDate <= @rToReceiptDate
							AND FR.PayTypeID = 2
				group by ReceiptDate,PM.Title,SM.AcademicId ,FR.PayTypeId,FR.Id,SM.Name + '(' + ClassName + ':' + DivisionName + ') ' + ' # Cheque No. :' + ISNULL(FR.ChequeNo,'') + ' BankName :' + ISNULL(FR.BankName,'')
				Order By FR.Id,ReceiptDate Desc
			END	
	ELSE IF @lTallyPostingType = 'ReceiptWise' 
			BEGIN	
				SELECT	ReceiptDate,						
						ISNULL(SLM.LedgerName,'') AS FeeName,
						PM.Title AS PayType,						
						FR.ReceiptAmount AS Amount,
						case When FR.PayTypeId = 1 then
						'Std-' + ClassName + ':' + DivisionName + ' RollNo :' + convert(nvarchar(20),RollNo) + ' ' + BatchName + '-->' +
						STUFF((SELECT ','  + convert(nvarchar(20),isnull(FRD1.Amount,'')) + '-' + isnull(FM1.Title ,'') 
						from sFee.tReceipt FR1  INNER JOIN sFee.tReceiptDetail FRD1 ON FR1.Id = FRD1.ReceiptId
						INNER JOIN sFee.tFeeSetup FS1 ON FS1.Id = FRD1.FeeSetupId 						
						INNER JOIN sFee.tFeeMaster FM1 ON FM1.Id = FS1.FeeId AND FRD.ReceiptId=FRD1.ReceiptId  FOR XML PATH('')),1,1,'') 
						when FR.PayTypeId <> 1 then 
						'Std-' + ClassName + ':' + DivisionName + ' RollNo :' + convert(nvarchar(20),RollNo) + ' ' + BatchName + '-->' +
						STUFF((SELECT ','  + convert(nvarchar(20),isnull(FRD1.Amount,'')) + '-' + isnull(FM1.Title ,'') 
						from sFee.tReceipt FR1  INNER JOIN sFee.tReceiptDetail FRD1 ON FR1.Id = FRD1.ReceiptId
						INNER JOIN sFee.tFeeSetup FS1 ON FS1.Id = FRD1.FeeSetupId 						
						INNER JOIN sFee.tFeeMaster FM1 ON FM1.Id = FS1.FeeId AND FRD.ReceiptId=FRD1.ReceiptId  FOR XML PATH('')),1,1,'') + '  Cheque No. :' + ISNULL(FR.ChequeNo,'') + ' BankName :' + ISNULL(FR.BankName,'')  ENd
						AS Narration,					
						FR.PayTypeId AS PayTypeId,									
						A.StudentId AS FeeId,
						FRD.ReceiptId					 
				FROM sFee.tReceipt FR 
					INNER JOIN sFee.tReceiptDetail FRD ON FR.Id = FRD.ReceiptId
					INNER JOIN sFee.tFeeSetup FS ON FS.Id = FRD.FeeSetupId
					INNER JOIN sFee.tFeeMaster FM ON FM.Id = FS.FeeId
					INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id = FM.FeeHeadId
					INNER JOIN sFinanceCommon.tPayTypeMaster PM ON PM.Id = FR.PayTypeId
					INNER JOIN sStudent.vStudentAcademic A ON A.AcademicId = FR.SourceId AND A.OrgGRoupBatchId = FR.OrgGRoupBatchId
					LEFT JOIN sTally.tStudentLedgerMapping SLM ON SLM.StudentId = A.StudentId AND SLM.ReceiptBookid = FR.ReceiptBookId
					WHERE							
							FR.ReceiptBookId = @rReceiptBookId 
							AND FR.IsObsolete = 0 
							AND FR.OrgGroupBatchId = @rOrgGroupBatchId
							AND FR.ReceiptDate >= @rReceiptDate	And FR.ReceiptDate <= @rToReceiptDate					
				group by 
				ReceiptDate,PM.Title,
				SLM.LedgerName,FR.PayTypeId,A.StudentId,
				FRD.ReceiptId,Isnull(FR.ChequeNo,''),ISNull(FR.BankName,''),
				'Std-' + ClassName + ':' + DivisionName + ' RollNo :' + convert(nvarchar(20),RollNo) + ' ' + BatchName,FR.ReceiptAmount			
				Order By ReceiptDate desc	
			END			
			
END
GO
PRINT N'Creating [sAlert].[pSubjectwiseStudentAttendanceStatusGet]...';


GO

CREATE PROCEDURE [sAlert].[pSubjectwiseStudentAttendanceStatusGet]
	 	 
	 	 @rBatchId int,
	 	 @rClassId int,
	 	 @rDivisionId int,
	 	 @rOrgId int,
	 	 @rTemplate nvarchar(max),
	 	 @rAttendanceTypeId tinyint,
	 	 @rSubjectId int,
	 	 @rFromDate date,
	 	 @rToDate date,
	 	 @rIsPresent bit,
	 	 @rMailSubject nvarchar(500)=''
	 	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

--SET @rToDate = dateadd(d,1,@rToDate)
	CREATE TABLE #TMP 
	(
	AcademicId bigint,
	Code nvarchar(20),
	Name nvarchar(300),
	MobileNo nvarchar(50),
	EmailId nvarchar(50),
	SubjectId bigint,
	AttendanceDate Date
	)
	INSERT INTO #TMP
	Select 
			SC.AcademicId,
			SC.Code,
			SC.Name,
			SC.ParentContactNo AS MobileNo,	
			SC.ParentEmail AS EmailId,
			SR.SubjectId,
			SR.AttendanceDate
			--SR.PeriodNo
			--ISNULL(SC.StudentContactNo,'') AS StudentContactNo,
			--ISNULL(SC.StudentEmail,'') AS StudentEmail,
			--ISNULL(SC.FatherContactNo,'') AS FatherContactNo,
			--ISNULL(SC.FatherMail,'') AS FatherMail,
			--ISNULL(SC.MotherContactNo,'') AS MotherContactNo,
			--ISNULL(SC.MotherMail,'') AS MotherMail,
			--CASE WHEN @rIsPresent=0 THEN
			--replace (replace (
			--replace(replace(replace(@rTemplate,'[Name]',SC.Name),
			--'[Class]',SC.ClassName),
			--'[Division]',SC.DivisionName),
			--'[AbsentDates]',SR.AttendanceDate),
			--'[AbsentReason]',ISNULL(AR.Title,''))
			--ELSE
			--replace (
			--replace(replace(replace(@rTemplate,'[Name]',SC.Name),
			--'[Class]',SC.ClassName),
			--'[Division]',SC.DivisionName),
			--'[PresentDates]',SR.AttendanceDate)
			--END
			-- AS MsgText,
			--@rMailSubject AS Subject,
			--SC.AcademicId AS SourceId,
			--2001 AS SourceTypeId,
			--SC.OrgId,
			--SC.OrgGroupId,
			--SC.AcademicId AS ReceiverSourceId,
			--2001 AS ReceiverSourceTypeId
			
	From 
		sStudent.vStudentCommunication SC
	INNER JOIN sAttendance.tStudentRegister SR ON SR.AcademicId=SC.AcademicId
	LEFT JOIN sAttendance.tAbsentReasonMaster AR ON SR.AbsentReasonId = AR.Id
	INNER JOIN [sSetup].[tSubjectMaster] SM ON SR.SubjectId=SM.Id 
			
	WHERE
		((SC.ClassId=@rClassId) OR (@rClassId=0))
		AND ((SC.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		AND (SC.OrgId=@rOrgId)
		AND (SC.BatchId=@rBatchId)
		AND (SC.StatusId=1)
		AND ((SR.AttendanceTypeId=@rAttendanceTypeId) OR (@rAttendanceTypeId=0))
		AND ((SR.SubjectId=@rSubjectId) OR (@rSubjectId=0))
		AND ((SR.AttendanceDate >= @rFromDate) OR (ISNULL(@rFromDate,'')=''))
		AND ((SR.AttendanceDate <= @rToDate) OR (ISNULL(@rToDate,'')=''))
			--AND (SR.IsPresent = @rIsPresent)
		AND	SC.AcademicId NOT IN (SELECT AcademicId FROM sAttendance.tStudentLeaveRequest WHERE LeaveFromDate = @rFromDate AND LeaveToDate=@rToDate)
		AND SC.IsBlockAttendanceAlert=0 AND SC.IsBlockAllAlert=0
		GROUP BY SC.AcademicId,SC.Code,SC.Name,SC.ParentContactNo,	SC.ParentEmail,SR.SubjectId,SR.AttendanceDate
   --ORDER BY SC.ClassOrderIndex,SC.DivisionOrderIndex,SC.Rollno
   --Select * from #TMP
   Select 
   			AcademicId,
			Code,
			Name,
			MobileNo,	
			EmailId,
			AttendanceDate,
			STUFF(
                 (SELECT ',' + CONVERT(VARCHAR,tmp1.SubjectId) FROM #TMP tmp1
				  WHERE tmp1.AcademicId=tmp.AcademicId 
				  FOR XML PATH ('')), 1, 1, ''
               ),
			   			
			--replace (
			--replace (
			--replace (
			--replace (
			replace (
			replace(@rTemplate,'[Name]',tmp.Name),
			--'[Class]',ClassName),
			--'[Division]',SC.DivisionName),
			--'[AbsentDates]',SR.AttendanceDate),
			'[SubjectLectureStatusReason]', CHAR(13) +[sAlert].[fPeriodWiseAttendanceGet](tmp.AcademicId,STUFF(
                 (SELECT ',' + CONVERT(VARCHAR,tmp1.SubjectId) FROM #TMP tmp1
				  WHERE tmp1.AcademicId=tmp.AcademicId 
				  FOR XML PATH ('')), 1, 1, ''
               ),AttendanceDate,@rOrgId))
			--'[AbsentReason]',ISNULL(AR.Title,''))
			AS MsgText,
			[sAlert].[fPeriodWiseAttendanceGet](tmp.AcademicId,STUFF(
                 (SELECT ',' + CONVERT(VARCHAR,tmp1.SubjectId) FROM #TMP tmp1
				  WHERE tmp1.AcademicId=tmp.AcademicId 
				  FOR XML PATH ('')), 1, 1, ''
               ),AttendanceDate,@rOrgId)
		
    from #TMP tmp
	GROUP BY AcademicId,Code,Name,MobileNo,EmailId,AttendanceDate

	DROP TABLE #TMP
END
GO
PRINT N'Creating [sFee].[pEmandateRequestAdd]...';


GO
 
 
CREATE PROCEDURE [sFee].[pEmandateRequestAdd]
			
			@rAcademicId BIGINT=0,
			@rCheckSum NVARCHAR(500),
			@rMsgId NVARCHAR(50),
			@rCustomer_Name NVARCHAR(40),
			@rCustomer_MobileNo NVARCHAR(34),
			@rCustomer_EmailId NVARCHAR(50),
			@rCustomer_AccountNo NVARCHAR(35),
			@rCustomer_StartDate NVARCHAR(10),
			@rCustomer_ExpiryDate NVARCHAR(10),
			@rCustomer_DebitAmount DECIMAL(15,2) = 0,
			@rCustomer_MaxAmount DECIMAL(15,2) = 0,
			@rCustomer_DebitFrequency NVARCHAR(4),
			@rCustomer_InstructedMemberId NVARCHAR(11),
			@rShort_code NVARCHAR(10),
			@rCustomer_SequenceType NVARCHAR(4),
			@rMerchant_Category_Code NVARCHAR(4),
			@rCustomer_Reference1 NVARCHAR(50),
			@rCustomer_Reference2 NVARCHAR(50),
			@rChannel NVARCHAR(6),
			@rUtilCode NVARCHAR(18),
			@rFiller1 NVARCHAR(50),
			@rFiller2 NVARCHAR(50),
			@rFiller3 NVARCHAR(50),
			@rFiller4 NVARCHAR(50),
			@rFiller5 NVARCHAR(50),
			@rFiller6 NVARCHAR(50),
			@rFiller7 NVARCHAR(50),
			@rFiller8 NVARCHAR(50),
			@rFiller9 NVARCHAR(50),
			@rFiller10 NVARCHAR(50),
			@rCreatedUserId BIGINT,
			@rOrgId int
			

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
		BEGIN TRY
		BEGIN TRANSACTION	

	DECLARE @lNextNumber BIGINT     
	EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sFee.tEMandate', 0, 1, 1,@outNextNumber = @lNextNumber output

	INSERT INTO sFee.tEMandate
			(
				Id
			   ,AcademicId
			   ,CheckSum
			   ,Msgid
			   ,Customer_Name
			   ,Customer_Mobile
			   ,Customer_EmailId
			   ,Customer_AccountNo
			   ,Customer_StartDate
			   ,Customer_ExpiryDate
			   ,Customer_DebitAmount
			   ,Customer_MaxAmount
			   ,Customer_DebitFrequency
			   ,Customer_InstructedMemberId
			   ,Short_code
			   ,Customer_SequenceType
			   ,Merchant_Category_Code
			   ,Customer_Reference1
			   ,Customer_Reference2
			   ,Channel
			   ,UtilCode
			   ,Filler1
			   ,Filler2
			   ,Filler3
			   ,Filler4
			   ,Filler5
			   ,Filler6
			   ,Filler7
			   ,Filler8
			   ,Filler9
			   ,Filler10
			   ,CreatedUserId
			   ,CreatedDateTime
			)
		VALUES
			(
				@lNextNumber,
				@rAcademicId,
				@rCheckSum,
				@rMsgId,
				@rCustomer_Name,
				@rCustomer_MobileNo,
				@rCustomer_EmailId,
				@rCustomer_AccountNo,
				@rCustomer_StartDate,
				@rCustomer_ExpiryDate,
				@rCustomer_DebitAmount,
				@rCustomer_MaxAmount,
				@rCustomer_DebitFrequency,
				@rCustomer_InstructedMemberId,
				@rShort_code,
				@rCustomer_SequenceType,
				@rMerchant_Category_Code,
				@rCustomer_Reference1,
				@rCustomer_Reference2,
				@rChannel,
				@rUtilCode,
				@rFiller1,
				@rFiller2,
				@rFiller3,
				@rFiller4,
				@rFiller5,
				@rFiller6,
				@rFiller7,
				@rFiller8,
				@rFiller9,
				@rFiller10,
				@rCreatedUserId,
				sCommon.fGetDate()
			)

	SELECT @lNextNumber AS Result
	
	COMMIT TRANSACTION
	END TRY
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
END
GO
PRINT N'Creating [sFee].[pEMandateTransactionGet]...';


GO
 
CREATE PROCEDURE [sFee].[pEMandateTransactionGet]
	@rAcademicId bigint = 0,
	@rFromDate nvarchar(10),
	@rToDate nvarchar(10),
	@rOrgGroupId smallint=0,
	@rOrgId int=0,
	@rBatchId int=0,
	@rReferenceNo bigint=0
AS
SET NOCOUNT ON

BEGIN
	 SELECT  
		ISNULL(EM.ResponseStatus,'Pending') as ResponseStatus,
		EM.Customer_DebitAmount,
		EM.Customer_MaxAmount,
		EM.Customer_StartDate,
		EM.Customer_ExpiryDate,
		CONVERT(nvarchar(20),SM.Code) AS Code,
		SM.FirstName + ' ' + SM.LastName AS Name,
		CM.DisplayTitle +' - '+ DM.DisplayTitle AS ClassName,
		EM.Id AS EMandateId
	
	FROM 
		sFee.tEmandate EM
		INNER JOIN sStudent.tAcademic A ON A.Id = EM.AcademicId
		INNER JOIN sStudent.tStudentMaster SM ON SM.Id = A.StudentId
		INNER JOIN sSetup.tClassMaster CM ON CM.Id = A.ClassId
		INNER JOIN sSetup.tDivisionMaster DM ON DM.Id = A.DivisionId
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = CM.OrgId
		INNER JOIN sSetup.tBatchMaster BM ON BM.Id = A.BatchId
		
	WHERE 
		 ((EM.Customer_StartDate >= @rFromDate) OR (ISNULL(@rFromDate,'')=''))
		 AND ((EM.Customer_StartDate <= @rToDate) OR (ISNULL(@rToDate,'')=''))
		 AND ((EM.AcademicId = @rAcademicId) OR (@rAcademicId=0))
		 AND OM.OrgGroupId = @rOrgGroupId
		 AND OM.Id = @rOrgId
		 AND BM.Id = @rBatchId
		 AND (EM.Id= @rReferenceNo OR @rReferenceNo=0)
		 ORDER BY EM.Customer_StartDate DESC
END
GO
  

 
GO
INSERT INTO [sSystem].[tVersionUpdate] ([VersionNumber],[VersionId],[Status],[UpdateScript],[StartDateTime],[EndDateTime],[OrgGroupId])
VALUES('20.12.31',201231,'END','Script',sCommon.fGetDate(),sCommon.fGetDate(),0)
GO