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

GO



GO
PRINT N'Altering [sHrm].[tEmployeeDocument]...';


GO
ALTER TABLE [sHrm].[tEmployeeDocument]
    ADD [IsMigrate] BIT CONSTRAINT [DF_tEmployeeDocument_IsMigrate] DEFAULT ('0') NULL;


GO
PRINT N'Altering [sStudent].[tStudentDocument]...';


GO
ALTER TABLE [sStudent].[tStudentDocument]
    ADD [IsMigrate] BIT CONSTRAINT [DF_tStudentDocument_IsMigrate] DEFAULT ('0') NULL;


GO
PRINT N'Creating [sFee].[tLateFeeRuleMaster]...';


GO
CREATE TABLE [sFee].[tLateFeeRuleMaster] (
    [OrgId]      INT            NOT NULL,
    [Id]         INT            NOT NULL,
    [Title]      NVARCHAR (100) NOT NULL,
    [FeeRule]    NVARCHAR (200) NOT NULL,
    [IsObsolete] BIT            NULL
);


GO
PRINT N'Creating PK_tLateFeeRuleMaster...';


GO
ALTER TABLE [sFee].[tLateFeeRuleMaster]
    ADD CONSTRAINT [PK_tLateFeeRuleMaster] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO


PRINT N'Creating [sHrm].[fEmployeeDocumentAllBatchDepartmentwiseget]...';


GO
CREATE FUNCTION [sHrm].[fEmployeeDocumentAllBatchDepartmentwiseget] (@rDocumentId int,@rEmployeeId bigint)
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
BEGIN

 DECLARE @lDepartmentId int
 DECLARE @lEmployeeId bigint

 SELECT @lDepartmentId = DepartmentId,@lEmployeeId = EmployeeId FROM sHrm.vEmployeeCommunication WHERE Id = @rEmployeeId
      DECLARE @rDocumentName Nvarchar(max)
      BEGIN
		SELECT @rDocumentName = ISNULL(STUFF(
			 (SELECT DISTINCT ',' + DocumentName FROM (SELECT DISTINCT SDM.DocumentName +'-OGB-'+cast(UD.OrgGroupBatchId as nvarchar) as DocumentName
			  FROM sCommon.tSubmitDocumentMaster SDM
			  INNER JOIN  sHrm.tEmployeeDocument ED ON SDM.Id=ED.SubmitDocumentMasterId
			  INNER JOIN sCommon.tSubmitDocumentScope DS ON DS.SubmitDocumentId=SDM.Id AND DS.SourceId = @lDepartmentId
			  Inner Join [sCommon].[tUploadDocument] UD On UD.DocumentId=ED.SubmitDocumentMasterId AND UserSourceTypeId=95 AND UserSourceId=ED.EmployeeId AND ( UD.UploadFunctionalityId=13)
			  where ED.EmployeeId=@lEmployeeId AND SDM.SubmitDocumentFunctionalityId = 2 AND ((SDM.Id=@rDocumentId)OR(@rDocumentId=0))

			  UNION ALL

			  SELECT DISTINCT UD.DocumentName  +'-OGB-'+cast(UD.OrgGroupBatchId as nvarchar) as DocumentName
			  FROM sCommon.tSubmitDocumentMaster SDM
			  INNER JOIN  sHrm.tEmployeeDocument ED ON SDM.Id=ED.SubmitDocumentMasterId
			  LEFT JOIN sCommon.tSubmitDocumentScope DS ON DS.SubmitDocumentId=SDM.Id
			  Left Join [sCommon].[tUploadDocument] UD On UD.DocumentId=ED.SubmitDocumentMasterId AND UserSourceTypeId=95 AND UserSourceId=ED.EmployeeId AND ( UD.UploadFunctionalityId=13)
			  where ED.EmployeeId=@lEmployeeId AND SDM.SubmitDocumentFunctionalityId = 2 AND UD.DocumentName is null   AND ((SDM.Id=@rDocumentId)OR(@rDocumentId=0)))
			  AS DocumentName
			  FOR XML PATH (''))
			  , 1, 1, ''),'')             
                                    
      END

     
      RETURN @rDocumentName
END
GO
PRINT N'Altering [sAssessment].[vGeneralExamColumnName]...';


GO




 
/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
ALTER VIEW [sAssessment].[vGeneralExamColumnName]  
  
AS  
  
SELECT 	
		 --SA.AcademicId,	  
		 --SA.ClassId,
		 --SA.DivisionId,
		 SA.RollNo,
		 --SA.OrgId,
		 --SA.BatchId,
		 OM.Title AS Organization,
		 BM.Title AS Batch,
		 --SA.StudentId,
		 SA.Code AS [ID No],
		  SA.Code AS [GR No],
		 SA.Number,
		 SA.FirstName,
		 SA.LastName,
		 SA.Name,
		 --SA.DepartmentId,
		 --SA.StatusId,
		 SA.ClassName,
		 SA.DivisionName,
		 SA.StatusName,
		 R.TotalMarks,
		 R.TotalObtain,
		 R.ResultStatus,
		 R.ResultGrade,
		 R.ResultGradePoint,
		 R.RankNumber,
		 R.Percentage,
		 R.Percentile,
		 R.ResultRemark,
		 R.OtherRemark1,
		 ESGM.Code AS ExamCode,
		 ESGM.Title AS ExamName,
		 --EM.Id AS Id,
	     U.DisplayTitle AS CreatedUserName,
	     SR.ExamSeatNo,
	     SR.TotalDays,
	     SR.PresentDays,
	     SR.StudentHeight,
	     SR.StudentWeight,
	     SR.ResultRemark1,
	     SR.ResultRemark2,
	     SR.MyGoal,
	     SR.MyStrength,
	     SR.MyInterestHobby,
	     SR.Achievements,
	     SR.AreaOfInterest,
	     SR.Responsibility,
	     SR.DentalHygiene,
	     SR.VisionLeft,
	     SR.VisionRight,
	     SR.MyActivity,
	     SR.ClassTeacherRemark,
	     SR.HouseRemark,
	     SR.ResultRemark3,
	     SR.GeneralRemark,
		 CCM.Code As CastCategory,
		 SF.FatherName,
		 SF.MotherName,
	     ACM.Title AS FeeCategory,
		 HM.Title AS House
		 --SO.DateOfBirth
	     
	  	  	   
FROM 
		sStudent.vStudentAcademic SA
	INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=SA.OrgId
	INNER JOIN sSetup.tBatchMaster BM ON BM.Id=SA.BatchId
	INNER JOIN sAssessment.tResult R ON R.AcademicId=SA.AcademicId
	INNER JOIN sAssessment.tExamMaster EM ON EM.Id=R.ExamId
	INNER JOIN sSecurity.tUser U ON U.Id=R.UpdatedUserId
	INNER JOIN sAssessment.tExamSubGroupMaster ESGM ON ESGM.Id = EM.ExamSubGroupId
	LEFT JOIN sAssessment.tStudentRemark SR ON SR.AcademicId=SA.AcademicId
	LEFT JOIN sStudent.tStudentOther SO ON SO.StudentId=SA.StudentId
	LEFT JOIN sCommon.tCastCategoryMaster CCM ON CCM.Id=SO.CastCategoryId
	LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId = SA.StudentId
	LEFT JOIN sStudent.tAdmissionCategoryMaster ACM ON SA.AdmissionCategoryId = ACM.Id
	LEFT JOIN sActivity.tHouseMaster HM ON SO.HouseId = HM.Id
GO
PRINT N'Altering [sAssessment].[vGeneralExamCustomSearch]...';


GO








/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
ALTER VIEW [sAssessment].[vGeneralExamCustomSearch]  
  
AS  
  
SELECT 	
		 SA.AcademicId,	  
		 SA.ClassId,
		 SA.DivisionId,
		 SA.RollNo,
		 SA.OrgId,
		 SA.BatchId,
		 OM.Title AS Organization,
		 BM.Title AS Batch,
		 SA.StudentId,
		 SA.Code,
		 SA.Number,
		 SA.FirstName,
		 SA.LastName,
		 SA.Name,
		 SA.StatusId,
		 SA.ClassName,
		 SA.DivisionName,
		 SA.StatusName,
		 R.Id AS ResultId,
		 R.ExamId AS ExamId,
		 R.TotalMarks,
		 R.TotalObtain,
		 R.ResultStatus,
		 R.ResultGrade,
		 R.ResultGradePoint,
		 R.RankNumber,
		 R.Percentage,
		 R.Percentile,
		 R.ResultRemark,
		 R.OtherRemark1,
		 ESGM.Code AS ExamCode,
		 ESGM.Title AS ExamName,
	     U.Id AS CreatedUserId,
	     U.DisplayTitle AS CreatedUserName,
	     EM.ExamSubGroupId,
	     SR.ExamSeatNo,
	     SR.TotalDays,
	     SR.PresentDays,
	     SR.StudentHeight,
	     SR.StudentWeight,
	     SR.ResultRemark1,
	     SR.ResultRemark2,
	     SR.MyGoal,
	     SR.MyInterestHobby,
	     SR.MyStrength,
	     SR.Achievements,
	     SR.AreaOfInterest,
	     SR.Responsibility,
	  	 SR.DentalHygiene,
	  	 SR.VisionLeft,
	  	 SR.VisionRight  ,
	  	  CM.DepartmentId,
	  	  SR. MyActivity,
	  	  SR.ClassTeacherRemark,
	  	  SR.HouseRemark,
	  	  SR.ResultRemark3,
	  	  SR.GeneralRemark,
		  CCM.Id As CastCategoryId,
		  CCM.Code As CastCategory,

		  SF.FatherName,
		  SF.MotherName,
	      ACM.Title AS FeeCategory,
		  HM.Title AS House
		  --CONVERT(VARCHAR(10), SO.DateOfBirth, 103) AS DateOfBirth
		  --SM.Id AS SubjectId,
		  --SM.Title AS SubjectTitle   
FROM 
		sStudent.vStudentAcademic SA
	 INNER JOIN sSetup.tClassMaster  CM ON CM.Id= SA.ClassId
	 --Add By Vishkha
	 --INNER Join sSetup.tClassSubject CS ON CS.ClassId=CM.Id
	 --INNER JOIN sSetup.tSubjectMaster SM on CS.SubjectId=SM.Id AND SM.OrgId = SA.OrgId
	
	INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=SA.OrgId
	INNER JOIN sSetup.tBatchMaster BM ON BM.Id=SA.BatchId
	INNER JOIN sAssessment.tResult R ON R.AcademicId=SA.AcademicId
	INNER JOIN sAssessment.tExamMaster EM ON EM.Id=R.ExamId
	LEFT JOIN sSecurity.tUser U ON U.Id=R.UpdatedUserId
	INNER JOIN sAssessment.tExamSubGroupMaster ESGM ON ESGM.Id = EM.ExamSubGroupId
	LEFT JOIN sAssessment.tStudentRemark SR ON SR.AcademicId = SA.AcademicId AND SR.ExamId = EM.Id
	LEFT JOIN sStudent.tStudentOther SO ON SO.StudentId=SA.StudentId
	LEFT JOIN sCommon.tCastCategoryMaster CCM ON CCM.Id=SO.CastCategoryId

	LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId = SA.StudentId
	LEFT JOIN sStudent.tAdmissionCategoryMaster ACM ON SA.AdmissionCategoryId = ACM.Id
	LEFT JOIN sActivity.tHouseMaster HM ON SO.HouseId = HM.Id
GO


PRINT N'Altering [sAcademic].[pTopicMasterAdd]...';


GO


ALTER PROCEDURE [sAcademic].[pTopicMasterAdd]
	 
        @rId int,
        @rNumber smallint,
        @rTitle nvarchar(100),
        @rSubjectId int,
        @rClassId int,
        @rOrgId smallint,
        @rCreatedUserId bigint
		--@rIsObsolete bit
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY
	
	IF(@rId = 0)	
	BEGIN
		If Exists(select Id from sAcademic.tTopicMaster where Title=@rTitle AND OrgId=@rOrgId AND ClassId=@rClassId AND SubjectId=@rSubjectId)
			BEGIN
			select -102 as Result
			RETURN
		END 
	
		If Exists(select Id from sAcademic.tTopicMaster where Number=@rNumber AND OrgId=@rOrgId AND ClassId=@rClassId AND SubjectId=@rSubjectId)
			BEGIN
			select -103 as Result
			RETURN
		END 
	END
	ELSE
	BEGIN
		If  Exists(select Id from sAcademic.tTopicMaster where Title=@rTitle AND OrgId=@rOrgId AND ClassId=@rClassId AND SubjectId=@rSubjectId AND Id<>@rId)
		Begin
			select -102 as Result
			RETURN
		End
		If  Exists(select Id from sAcademic.tTopicMaster where Number=@rNumber AND OrgId=@rOrgId AND ClassId=@rClassId AND SubjectId=@rSubjectId AND Id<>@rId)
		Begin
			select -103 as Result
			RETURN
		End

	END
	
	
	
	 BEGIN TRANSACTION	
	 DECLARE @lNextNumber BIGINT 

	IF(@rId = 0)	
	BEGIN
		
	EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sAcademic.tTopicMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
	
	INSERT INTO sAcademic.tTopicMaster
		(
			Id,
			Number,
			Title,
			SubjectId,
			ClassId,
			OrgId,
			CreatedUserId,
			CreatedDateTime
			--IsObsolete
		)
	VALUES
		(
			@lNextNumber,
			@rNumber,
			@rTitle,
			@rSubjectId,
			@rClassId,
			@rOrgId,
			@rCreatedUserId,
			sCommon.fGetDate()
			--@rIsObsolete
		)
		    SELECT 1 AS Result
	END	 

ELSE
	 BEGIN
	 SET @lNextNumber = @rId
	    UPDATE  sAcademic.tTopicMaster
	    SET
			Number=@rNumber,
			Title=@rTitle,
			SubjectId=@rSubjectId,
			ClassId=@rClassId,
			OrgId=@rOrgId,
			UpdatedUserId=@rCreatedUserId,
			UpdatedDateTime=sCommon.fGetDate()
			--IsObsolete = @rIsObsolete
	      
	      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 [sAcademic].[pTopicMasterGet]...';


GO


ALTER PROCEDURE [sAcademic].[pTopicMasterGet]

	 @rId int,
	 @rOrgId smallint,
	 @rClassId int,
	 @rSubjectId int,
	 @rEmployeeId bigint,
	 @rSourceTypeId smallint = 0
	 	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 
	IF(@rSourceTypeId = 0)
	BEGIN
		SELECT 
				DISTINCT TM.Id,
				TM.Number,
				TM.Title,
				TM.OrgId,
				TM.ClassId,
				TM.SubjectId,
				TM.CreatedUserId,
				TM.CreatedDateTime,
				CM.DisplayTitle AS ClassName,
				SM.Title AS SubjectName,
				CM.OrderIndex,
				SM.OrderIndex
				--ISNULL(TM.IsObsolete,0) AS IsObsolete
		FROM 
				sAcademic.tTopicMaster TM
				INNER JOIN sSetup.tClassMaster CM ON CM.Id = TM.ClassId
				INNER JOIN sSetup.tSubjectMaster SM ON SM.Id = TM.SubjectId
				LEFT JOIN sAcademic.vTeacherSubjectDivision vTSD ON vTSD.ClassId = TM.ClassId 			
				--AND vTSD.SubjectId = TM.SubjectId 
				AND vTSD.EmployeeId = @rEmployeeId
		WHERE 
			(TM.OrgId=@rOrgId)
			AND ((TM.Id = @rId) OR (@rId = 0))
			AND ((TM.ClassId = @rClassId) OR (@rClassId = 0))
			AND ((TM.SubjectId = @rSubjectId) OR (@rSubjectId = 0))
			--AND ((vTSD.EmployeeId = @rEmployeeId) OR (vTSD.EmployeeId = 0))
			--ORDER BY CM.OrderIndex
		ORDER BY CM.OrderIndex,SM.OrderIndex,TM.Number ASC
	END
	ELSE IF (@rSourceTypeId = 95)
	BEGIN
		SELECT 
				DISTINCT TM.Id,
				TM.Number,
				TM.Title,
				TM.OrgId,
				TM.ClassId,
				TM.SubjectId,
				TM.CreatedUserId,
				TM.CreatedDateTime,
				CM.DisplayTitle AS ClassName,
				SM.Title AS SubjectName,
				CM.OrderIndex,
				SM.OrderIndex
				--ISNULL(TM.IsObsolete,0) AS IsObsolete
		FROM 
				sAcademic.tTopicMaster TM
				INNER JOIN sSetup.tClassMaster CM ON CM.Id = TM.ClassId
				INNER JOIN sSetup.tSubjectMaster SM ON SM.Id = TM.SubjectId
				INNER JOIN sAcademic.vTeacherSubjectDivision vTSD ON vTSD.ClassId = TM.ClassId AND vTSD.SubjectId = TM.SubjectId 
				AND vTSD.EmployeeId = @rEmployeeId
		WHERE 
			(TM.OrgId=@rOrgId)
			AND ((TM.Id = @rId) OR (@rId = 0))
			AND ((TM.ClassId = @rClassId) OR (@rClassId = 0))
			AND ((TM.SubjectId = @rSubjectId) OR (@rSubjectId = 0))
		ORDER BY CM.OrderIndex,SM.OrderIndex,TM.Number ASC
	END
END
GO
PRINT N'Altering [sAcademic].[pXMLTopicMasterAdd]...';


GO
ALTER PROCEDURE [sAcademic].[pXMLTopicMasterAdd]
	(
		@rXml  nvarchar(MAX),
		@rOrgId int,
		@rClassId int,
		@rSubjectId int,
		@rCreatedUserId bigint 
		
	)
AS

SET NOCOUNT ON

BEGIN

	DECLARE @lInterrorCode INT,@intDoc INT
	DECLARE @lNextNumber BIGINT			
 
	EXECUTE @lInterrorCode = sp_xml_preparedocument @intDoc OUTPUT, @rXml
	
	BEGIN TRY

	CREATE TABLE #tempTable  
	(
		Id bigint identity,
		Number smallint,
		Title nvarchar(500)
	)

	INSERT INTO #tempTable
	(
		Number,
		Title
	)
	SELECT	
			A.Number,
			A.Title
	FROM OPENXML (@intDoc, '/Table/TopicMaster', 1)
	WITH
	(
			Number nvarchar(100) '@Number',
			Title nvarchar(100) '@Title' 
			 
	)A 
	 
	DECLARE @lCount int,@lId int
	SELECT @lCount = COUNT(Id) FROM #tempTable
	SELECT Top 1 @lId=Id FROM #tempTable
	
	WHILE @lId <= @lCount 
	BEGIN

		DECLARE @lNumber smallint, @lTitle nvarchar(500), @lSubjectId Int, @lClassId int, @lOrgId smallint
		
		SELECT @lNumber = Number from #tempTable where  Id = @lId
		SELECT @lTitle = Title from #tempTable where  Id = @lId
		
		If Exists(select Id from sAcademic.tTopicMaster where Title=@lTitle AND OrgId=@rOrgId AND ClassId=@rClassId AND SubjectId=@rSubjectId)
		BEGIN
			select -102 as Result, 'Title Already Exists' AS MSG
			RETURN
		END 
	
		If Exists(select Id from sAcademic.tTopicMaster where Number=@lNumber AND OrgId=@rOrgId AND ClassId=@rClassId AND SubjectId=@rSubjectId)
		BEGIN
			select -103 as Result, 'Number Already Exists' AS MSG
			RETURN
		END 

			EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sAcademic.tTopicMaster', 0, 1, 1,@outNextNumber = @lNextNumber output

			print @lNextNumber

			INSERT INTO sAcademic.tTopicMaster
			(		Id,
					Number,
					Title,
					SubjectId,
					ClassID,
					OrgId,
					CreatedUserId,
					CreatedDateTime 
					
			)
			Values
			(	
					@lNextNumber,
					@lNumber,
					@lTitle,
					@rSubjectId,
					@rClassID,
					@rOrgId,
					@rCreatedUserId,
					sCommon.fGetDate()   
			)

		
	
		SELECT @lNextNumber = MAX(ID) FROM sAcademic.tTopicMaster Where OrgId=@rOrgId
		EXECUTE sCommon.pTableRowIdModify @rOrgId, @rTableTitle = 'sAcademic.tTopicMaster', @rCurrentNumber = @lNextNumber 

		SET @lId=@lId+1
		
	END
	EXEC sp_xml_removedocument @intDoc
	SELECT 1 as Result
	DROP Table #tempTable
	END TRY	

	BEGIN CATCH 
	SELECT -1 AS Result,error_message() AS MSG
	END CATCH
END
GO
PRINT N'Altering [sAdmission].[pRptAdmissionFeeGet]...';


GO

ALTER PROCEDURE [sAdmission].[pRptAdmissionFeeGet]
	
	
	 @rBatchId int,
	 @rClassId int,
	 @rBuilder1 nvarchar(max)='',
	 @rBuilder2 nvarchar(max)='',
	 @rFromDate date,
	 @rToDate date,
	 @rIsDetail bit =0
AS
 SET NOCOUNT ON
			 

BEGIN 
	
	SELECT 
		 AF.FormNo,
		 AF.FirstName+' '+AF.MiddleName+' '+AF.LastName AS Name,
		 AF.ForClassId,
		 AF.BatchId,
		 AF.OrgId,
		 AF.City,
		 AF.FamilyCast,
		 CM.Title AS Class,
		 AF.FamilySubCast,
		 AF.CastCategoryId,
		 --for online and offline display same feename
		 AC.FormFeeName AS FeeName,
		 --ACF.FeeName,
		 --in online entry display amount from pt  and in offline entry amount display from aff
		 CASE WHEN AF.DataEntryModeId = 561 THEN PT.PayAmount ELSE AFF.FeeAmount END  AS FeeAmount,
		 --AFF.FeeAmount,
		 --ACF.FeeNumber,
		 @rIsDetail AS IsDetail,
		 @rBuilder1 AS Builder1,
		 @rBuilder2 AS Builder2,
		 ISNULL(AF.CreatedDateTime,'') AS CreatedDateTime
	 
	 FROM 
		sAdmission.tAdmissionForm AF	
		INNER JOIN sSetup.tClassMaster CM ON AF.ForClassId=CM.Id
		LEFT JOIN sAdmission.tAdmissionFormFee AFF ON AFF.AdmissionFormId = AF.Id
		LEFT JOIN sAdmission.tAdmissionCriteriaFee ACF ON ACF.Id = AFF.AdmissionCriteriaFeeId
		INNER JOIN sAdmission.tAdmissionCriteria AC ON AC.ClassId=CM.Id AND AC.BatchId = @rBatchId
		LEFT JOIN sOp.tPaymentTransaction PT ON AF.Id = PT.PayerSourceId AND PT.TransactionSourceTypeId = 2027 AND PT.TransactionStatusId IN (2,4)
	WHERE 	
		AF.BatchId=@rBatchId
		--AND AF.OrgId=@rOrgId
		AND ((AF.ForClassId=@rClassId) OR (@rClassId=0))	
		AND ((CONVERT(DATE,AF.CreatedDateTime) >= @rFromDate) OR (isnull(@rFromDate,'')=''))
	    AND ((CONVERT(DATE,AF.CreatedDateTime) <= @rToDate) OR (isnull(@rToDate,'')=''))
	    ORDER BY AF.FormNo
END
GO
PRINT N'Altering [sAdmission].[pRptAdmissionReceiptGet]...';


GO
ALTER PROCEDURE [sAdmission].[pRptAdmissionReceiptGet]
	 	 
	 	 @rBatchId int,
	 	 @rOrgId int ,
	 	 @rFormNo int
	 	 
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	SELECT 
			ISNULL(AF.FirstName,'') + ' ' + ISNULL(AF.MiddleName, '') + ' ' + ISNULL(AF.LastName,'') AS StudentName,
			AFF.FatherName AS FatherName,
			AFF.MotherName AS MotherName,
			AF.StreetOne+' ' +AF.StreetTwo+' ' +AF.StreetThree+' ' +AF.City +' ' +AF.State+' '+AF.Country AS StudentAddress,
			AF.ContactNo AS MobileNo,
			AF.FormNo as FormNo,
			0 as ReceiptNumber,
			Convert(date,ISNULL(AF.CreatedDateTime,'')) as ReceiptDate,
			CM.DisplayTitle AS ClassName,
			BM.Title AS BatchName,
			AC.FormFeeAmount as FormFeeAmount,
			AC.FormFeeName AS FormFeeName,
			ACF.FeeNumber ,
			ACF.FeeName,
			ACF.FeeOrder,
			AFFE.FeeAmount,
			ISNULL(AF.Remark,'') AS Remark,
			ISNULL(AF.DateOfBirth,'') AS DateOfBirth,
			ISNULL(AF.FirstName,'') AS StudentFirstName,
			ISNULL(AF.MiddleName, '') AS StudentMiddleName,
			ISNULL(AF.LastName,'') AS StudentLastName
			
	FROM 
			sAdmission.tAdmissionForm AF 
			INNER JOIN sSetup.tClassMaster CM ON CM.Id = AF.ForClassId 
			INNER JOIN sSetup.tBatchMaster BM ON BM.Id = AF.BatchId 
			INNER JOIN sAdmission.tAdmissionCriteria AC ON AC.ClassId=CM.Id AND AC.BatchId = @rBatchId
			LEFT JOIN sAdmission.tAdmissionFormFamilyDetail AFF ON AFF.AdmissionFormId= AF.Id 
			LEFT JOIN sAdmission.tAdmissionFormFee  AFFE ON AFFE.AdmissionFormId = AF.Id
			LEFT JOIN sAdmission.tAdmissionCriteriaFee ACF ON ACF.Id = AFFE.AdmissionCriteriaFeeId
			
	WHERE
			AF.BatchId = @rBatchId
			AND AF.OrgId = @rOrgId
			AND AF.FormNo =@rFormNo
END
GO
PRINT N'Altering [sAssessment].[pExamSettingGet]...';


GO

 
ALTER PROCEDURE [sAssessment].[pExamSettingGet]
	 @rExamId int,
	 @rClassId int,
	 @rExamSettingTypeId int
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 	
DECLARE @lExamIds nvarchar(max)	
CREATE TABLE #AllExams
(
ExamIds int
)
INSERT INTO #AllExams
SELECT EM.Id FROM [sAssessment].[tExamMaster] EM 
		WHERE EM.Id =@rExamId  AND EM.CLassId = @rClassId


INSERT INTO #AllExams
SELECT EM.Id FROM [sAssessment].[tExamMaster] EM 
		INNER JOIN SASSESSMENT.TEXAMSUBGROUPmASTER ESGM ON ESGM.ID = EM.ExamSubGroupId 
		INNER JOIN sAssessment.tExamCombine EC ON EC.SubExamId=EM.Id AND EC.ExamId =@rExamId  AND EM.CLassId = @rClassId


INSERT INTO #AllExams
SELECT EM.Id FROM [sAssessment].[tExamMaster] EM 
		INNER JOIN SASSESSMENT.TEXAMSUBGROUPmASTER ESGM ON ESGM.ID = EM.ExamSubGroupId  
		INNER JOIN sAssessment.tExamCombine EC ON EC.SubExamId=EM.Id AND EC.ExamId in (SELECT SubExamId  FROM sAssessment.tExamCombine WHERE ExamId=@rExamId)
		WHERE EM.ClassId = @rClassId

		--SELECT * FROM #AllExams

SELECT @lExamIds = STUFF((SELECT ','+ CONVERT(varchar,ExamIds)
                FROM #AllExams
              -- WHERE t.prodtype = 'XYZ'
             FOR XML PATH('')), 1, 1, '')

--SELECT @lExamIds

IF(@rExamSettingTypeId = 1)
BEGIN
SELECT 


			EM.Id,
			EM.BatchId,
			ESGM.Code,
			ESGM.Title AS Name,
			ISNULL(IsProcessResult,0) AS IsProcessResult,
			ISNULL(IsNegativeMarks,0) AS IsNegativeMarks,
			ISNULL(IsLockForEdit,0) AS IsLockForEdit,
			ISNULL(IsPublished,0) AS IsPublished,
			ISNULL(IsPublishExamSchedule,0) As IsPublishExamSchedule

   FROM 
			sAssessment.tExamMaster EM
			INNER JOIN sAssessment.tExamSubGroupMaster	ESGM ON ESGM.Id = EM.ExamSubGroupId
			INNER JOIN sAssessment.tExamGroupMaster EGM ON EGM.Id=ESGM.ExamGroupId
			INNER JOIN sSetup.tClassMaster CM on CM.Id=EM.ClassId
   WHERE      
			(ISNULL(EM.Id,0) IN( SELECT cast(item as nvarchar(500)) FROM  sCommon.fnsplit(@lExamIds,',')) OR @lExamIds='') 
            AND EM.ClassId=@rClassId
			order by ESGM.Title
END

ELSE IF(@rExamSettingTypeId = 2)		 
BEGIN
	SELECT 
			EM.Id,
			EM.BatchId,
			ESGM.Code,
			ESGM.Title AS Name,
			EM.IsGrade,
			EM.IsPercentile,
			EM.IsRankNo,
			ISNULL(EM.IsGenerateRankNo,0)As IsGenerateRankNo,
			ISNULL(EM.IsPercentage,0) AS IsPercentage,
			ISNULL(Em.IsRemovePercentageForFail,0) AS IsFailPercentageZero,
			ISNULL(EM.IsSubjectPercentile,0) AS IsSubjectPercentile,
			ISNULL(IsResultStatus,0) AS IsDisplayResultStatus,
			ISNULL(IsAllExamPassRequired,0) As IsAllExamPassRequired,
			ISNULL(IsExcludeAbsentTotal,0) AS IsExcludeAbsentTotal


   FROM 
			sAssessment.tExamMaster EM
			INNER JOIN sAssessment.tExamSubGroupMaster	ESGM ON ESGM.Id = EM.ExamSubGroupId
			INNER JOIN sAssessment.tExamGroupMaster EGM ON EGM.Id=ESGM.ExamGroupId
			INNER JOIN sSetup.tClassMaster CM on CM.Id=EM.ClassId
			--INNER Join sSetup.tClassSubject CS ON EM.ClassId=CS.ClassId				
			--INNER JOIN sSetup.tSubjectMaster SM on   CS.SubjectId=SM.Id					
			--LEFT JOIN sAssessment.tExamDetail ED ON ED.ClassSubjectId = CS.Id AND ED.ExamId=EM.Id
			--LEFT Join sAssessment.tGradeSetMaster GSM ON GSM.Id = ED.GradeSetId
  WHERE      
			(ISNULL(EM.Id,0) IN( SELECT cast(item as nvarchar(500)) FROM  sCommon.fnsplit(@lExamIds,',')) OR @lExamIds='') 
            AND EM.ClassId=@rClassId
			order by ESGM.Title

END
ELSE IF(@rExamSettingTypeId = 3)		 
BEGIN
	SELECT 
			EM.Id,
			EM.BatchId,
			ESGM.Code,
			ESGM.Title AS Name,
			ISNULL(IsDisplayPhoto,0) AS IsDisplayPhoto,
			ISNULL(IsDisplaySchoolName,0) AS IsDisplaySchoolName,
			ISNULL(IsDisplayBirthDate,0) AS IsDisplayBirthDate,
			ISNULL(IsDisplayParentName,0) AS IsDisplayParentName,
			ISNULL(IsDisplayHeightWeight,0) AS IsDisplayHeightWeight,
			ISNULL(IsDisplayGradeString,0) AS IsDisplayGradeString,
			ISNULL(EM.IsDisplayTotalMark,0) AS IsDisplayTotalMark,
			ISNULL(EM.IsDisplayResultIndicator,0) AS IsDisplayResultIndicator,
			ISNULL(IsDisplayHighestDivisionMark,0) AS IsDisplayHighestDivisionMark,
			ISNULL(Convert(bit,IsDisplayHighestDivisionGrade),0) AS IsDisplayHighestDivisionGrade,
			ISNULL(IsDisplayTeacherSign,0) AS IsDisplayTeacherSign,
			ISNULL(IsDisplayPrincipalSign,0) AS IsDisplayPrincipalSign,
			ISNULL(IsDisplayActivityGrade,0) AS IsDisplayActivityGrade
			
   FROM 
			sAssessment.tExamMaster EM
			INNER JOIN sAssessment.tExamSubGroupMaster	ESGM ON ESGM.Id = EM.ExamSubGroupId
			INNER JOIN sAssessment.tExamGroupMaster EGM ON EGM.Id=ESGM.ExamGroupId
			INNER JOIN sSetup.tClassMaster CM on CM.Id=EM.ClassId
			--INNER Join sSetup.tClassSubject CS ON EM.ClassId=CS.ClassId				
			--INNER JOIN sSetup.tSubjectMaster SM on   CS.SubjectId=SM.Id					
			--LEFT JOIN sAssessment.tExamDetail ED ON ED.ClassSubjectId = CS.Id AND ED.ExamId=EM.Id
			--LEFT Join sAssessment.tGradeSetMaster GSM ON GSM.Id = ED.GradeSetId
   WHERE      
			(ISNULL(EM.Id,0) IN( SELECT cast(item as nvarchar(500)) FROM  sCommon.fnsplit(@lExamIds,',')) OR @lExamIds='') 
            AND EM.ClassId=@rClassId
			order by ESGM.Title

END
ELSE
BEGIN
SELECT 
			EM.Id,
			EM.BatchId,
			ESGM.Code,
			ESGM.Title AS Name,
			ISNULL(IsIncludeCoScholastic,0) AS IsIncludeCoScholastic,
			ISNULL(IsCoScholasticGradeIndicator,0) AS IsCoScholasticGradeIndicator,
			ISNULL(IsCoScholasticGradeDesIndicator,0) AS IsCoScholasticGradeDesIndicator,
			ISNULL(IsCoScholasticGrade,0) AS IsCoScholasticGrade
   FROM 
			sAssessment.tExamMaster EM
			INNER JOIN sAssessment.tExamSubGroupMaster	ESGM ON ESGM.Id = EM.ExamSubGroupId
			INNER JOIN sAssessment.tExamGroupMaster EGM ON EGM.Id=ESGM.ExamGroupId
			INNER JOIN sSetup.tClassMaster CM on CM.Id=EM.ClassId
			--INNER Join sSetup.tClassSubject CS ON EM.ClassId=CS.ClassId				
			--INNER JOIN sSetup.tSubjectMaster SM on   CS.SubjectId=SM.Id					
			--LEFT JOIN sAssessment.tExamDetail ED ON ED.ClassSubjectId = CS.Id AND ED.ExamId=EM.Id
			--LEFT Join sAssessment.tGradeSetMaster GSM ON GSM.Id = ED.GradeSetId
   WHERE      
			(ISNULL(EM.Id,0) IN( SELECT cast(item as nvarchar(500)) FROM  sCommon.fnsplit(@lExamIds,',')) OR @lExamIds='') 
            AND EM.ClassId=@rClassId
			order by ESGM.Title
END

DROP TABLE #AllExams
		
END
GO
PRINT N'Altering [sAttendance].[pAttenanceRegisterGet]...';


GO
ALTER PROCEDURE [sAttendance].[pAttenanceRegisterGet]
     @rBuilder1 nvarchar(max)='',
	 @rBuilder2 nvarchar(max)='', 
	 @rAttendanceTypeId smallint,
	 @rOrgId int,
	 @rForMonth smallint,
	 @rForYear smallint,
	 @rClassId int=0,
	 @rDivisionId int=0,
	 @rBatchId int = 0,
	 @rStudentCode nvarchar(20)='',
	 @rDepartmentId int = 0,
	 @rAcademicId bigint = 0,
	 @PageIndex int=0,
	 @rStatus int=0,
	 @rFromStudentCode nvarchar(20)='',
	 @rToStudentCode nvarchar(20)=''
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

--Select * from sAttendance.tTempAttendance

DECLARE @lFirstDate Date
	DECLARE @lResult nvarchar(max)
	DECLARE @li int 
	SET @li=1
	SET @lFirstDate = CONVERT(DATE,(CONVERT(NVARCHAR(4),@rForYear)+'/'+CONVERT(NVARCHAR(2),@rForMonth)+'/01'))	
	
	
DECLARE @lOrgId  int
--DECLARE @lSchoolRemark1  nvarchar(500)
--DECLARE @lSchoolRemark2  nvarchar(500)
--DECLARE @lSchoolRemark3  nvarchar(500)
DECLARE @lSchoolRequiredSignature  nvarchar(500)

SELECT  @lOrgId=RG.OrgId,
--@lSchoolRemark1 = CASE  WHEN RG.OrgId IS NULL  THEN ISNULL(RGM.DefaultRemark1,'')  ELSE RG.SchoolRemark1 END,
--@lSchoolRemark2= CASE  WHEN RG.OrgId IS NULL  THEN ISNULL(RGM.DefaultRemark2,'')  ELSE RG.SchoolRemark2 END,
--@lSchoolRemark3 = CASE  WHEN RG.OrgId IS NULL  THEN ISNULL(RGM.DefaultRemark3,'')  ELSE RG.SchoolRemark3 END,
@lSchoolRequiredSignature= CASE  WHEN RG.OrgId IS NULL  THEN ISNULL(RGM.DefaultRequiredSignature,'')  ELSE RG.SchoolRequiredSignature END 
 
FROM sCommon.tReportRemarkGroupMaster RGM LEFT JOIN sCommon.tReportRemarkGroupOrg RG ON RGM.Id = RG.ReportRemarkGroupId AND RG.OrgId = @rOrgId AND RG.ReportRemarkGroupId=1 where RGM.Id=1
 
-- select @lSchoolRequiredSignature as sig
 	Declare @Days int 
 	select @Days=sCommon.fGetDaysInMonth(@rForMonth,@rForYear)
BEGIN 
	 CREATE Table #AttendanceRegistertempTable
(
	TID int identity(1,1),
	ClassDivisionTitle Nvarchar(500),
	Code nvarchar(500),
	RollNo int,
	AcademicId bigint,
	Name nvarchar(500),
	AttendanceTypeId int,
	ForYear int,
	ForMonth int,
	ForMonthInWord nvarchar(500),
	ClassId int,
	DivisionId int,
	ClassName nvarchar(500),
	DivisionName nvarchar(500),
	IsPresent bit,
	DayPart int,
	LeftDate date,
	ClassOrderIndex int,
	DivisionOrderIndex int,
	FooterSignature nvarchar(500),
	StatusId int,
	AttendanceDate date,
	AttendanceTagId int ,
	EmployeeName nvarchar(500),
	DateOfBirth date,
	Religion nvarchar(500),
	MotherName nvarchar(500),
	FamilyCast nvarchar(100),
	Gender tinyint,
	CastCategory nvarchar(50)

)
Insert INTO #AttendanceRegistertempTable
(	
	ClassDivisionTitle,
	Code ,
	RollNo,
	AcademicId,
	Name,
	AttendanceTypeId,
	ForYear,
	ForMonth,
	ForMonthInWord,
	ClassId,
	DivisionId,
	ClassName,
	DivisionName,
	IsPresent,
	DayPart,
	LeftDate,
	ClassOrderIndex,
	DivisionOrderIndex,
	FooterSignature,
	StatusId,
	AttendanceDate,
	AttendanceTagId,
	EmployeeName,
	DateOfBirth,
	Religion,
	MotherName,
	FamilyCast,
	Gender,
	CastCategory
)
Select	    
	ISNULL(SA.ClassName + '-' + SA.DivisionName,'') AS ClassDivisionTitle,
	SA.Code,
	SA.RollNo,
	SA.AcademicId,
	SA.Name,
	SR.AttendanceTypeId ,
	YEAR(SR.AttendanceDate) AS ForYear,
	MONTH(SR.AttendanceDate) AS ForMonth,
	DATENAME(month, SR.AttendanceDate) AS ForMonthInWord,
	SA.ClassId,
	SA.DivisionId,
	SA.ClassName,Sa.DivisionName,
  
	--(CASE SR.IsPresent WHEN 1 THEN 'P' 
	--	WHEN 0 THEN 'A'
	--ELSE  ' ' END )AS  IsPresent,		
	SR.IsPresent,
	 day(SR.AttendanceDate) AS DayPart,
	 
	 A.LeftDate,
	 CM.OrderIndex AS ClassOrderIndex,
	 DM.OrderIndex As DivisionOrderIndex,
	@lSchoolRequiredSignature AS FooterSignature,
	 SA.StatusId,
	 SR.AttendanceDate As AttendanceDate,
	 SR.AttendanceTagId,
	 EM.FirstName+' ' + EM.LastName AS EmployeeName,
	 SO.DateOfBirth,
	 RE.Title As Religion,
	 Sf.MotherName,
	 SO.FamilyCast,
	 --,
	 --SR.IsPresent
	 SO.GenderId,
	 ISNULL(CCM.Title,'') AS CastCategory
	 
    from
            sStudent.vStudentAcademic SA 
            INNER JOIN sAttendance.tStudentRegister SR ON SR.AcademicId=SA.AcademicId 
            INNER JOIN sStudent.tStudentAdmission A ON SA.StudentId = A.StudentId
            INNER JOIN sSetup.tClassMaster CM ON CM.Id = SA.ClassId
            INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = CM.OrgId
            INNER JOIN sSetup.tDivisionMaster DM ON DM.Id=SA.DivisionId
            LEFT JOIN sHrm.tEmployeeMaster EM ON EM.Id = DM.InchargeEmployeeId 
            INNER JOIN sStudent.tStudentOther SO ON SO.StudentId=SA.StudentId
			LEFT JOIN  sStudent.tStudentFamily SF ON Sf.StudentId=Sa.StudentID
            LEFT JOIN sCommon.tReligionMaster RE On RE.Id=SO.ReligionId
			LEFT JOIN sCommon.tCastCategoryMaster CCM ON CCM.Id=SO.CastCategoryId
            
             WHERE  
	        ((SR.AttendanceTypeId=@rAttendanceTypeId) OR (@rAttendanceTypeId= 0))
	         AND ((SA.OrgId=@rOrgId) OR (@rOrgId= 0))
	          AND ((SA.BatchId=@rBatchId) OR (@rBatchId= 0))
	          AND ((YEAR(SR.AttendanceDate)=@rForYear) OR (@rForYear= 0))
	          AND ((MONTH(SR.AttendanceDate)=@rForMonth) OR (@rForMonth= 0))
	           AND ((SA.ClassId=@rClassId) OR (@rClassId= 0))
	           AND ((SA.Code=@rStudentCode) OR (@rStudentCode=''))
	           AND ((SA.AcademicId=@rAcademicId) OR (@rAcademicId=0))	           
				AND ((SA.DepartmentId=@rDepartmentId) OR (@rDepartmentId=0))
	           AND ((SA.DivisionId=@rDivisionId) OR (@rDivisionId= 0))
	           AND ((SA.StatusId=@rStatus)OR (@rStatus=0))
	           AND ((SA.Code >= @rFromStudentCode) OR (ISNULL(@rFromStudentCode,'')=''))
	           AND ((SA.Code <= @rToStudentCode) OR (ISNULL(@rToStudentCode,'')=''))
	           AND  ISNULL(EM.IsOtherUser,0) = 0 
				
CREATE TABLE #TempDaysTempTable
(
 DayPart int,
 Value nvarchar(20)
 )
WHILE @li<=@Days
BEGIN
INSERT INTO #TempDaysTempTable
(
	DayPart ,
   Value
)

SELECT 
@li AS DayPart,
 [sAttendance].[fWeekHOffGet](@rForYear,@rForMonth,@li,@rBatchId,@rClassId,@rDivisionId,'',@rOrgId) 
SET @li = @li + 1
END

 

SELECT  
			@lFirstDate AS LastDate,
			LeftDate,
            @rBuilder1 AS Builder1,
			@rBuilder2 AS Builder2,
			PivotTable.AcademicId,
			ClassDivisionTitle,isnull(Code,'') as Code,isnull(RollNo,0) as RollNo,
			isnull(Name,'') as Name,isnull(AttendanceTypeId,0) as AttendanceTypeId,ForYear,
			ForMonth,ClassId,ClassName,DivisionId,
			DivisionName,
			ForMonthInWord,
			(ISNULL(TC.TCount,0) - ISNULL(AC.ACount,0)) AS PresentCount,
			ISNULL(ABU.AUCount,0) AS AbsentUpto,
			ISNULL(TC.TCount,0) AS AttendanceCount,
		    ISNULL(AC.ACount,0) As  AbsentCount,
		    ISNULL(PC.PCount,0) As  PresentUpto,	    
		    
		    ClassOrderIndex,
		    DivisionOrderIndex,
		    FooterSignature,
			(ISNULL(TotalPresent,0) + ISNULL(TC.TCount,0)) AS TotalPresent,
			 
			  [sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,1 ,@rBatchId ,@rClassId,@rDivisionId , isnull([1],''),@rOrgId )  AS P1,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,2 ,@rBatchId ,@rClassId,@rDivisionId , isnull([2],''),@rOrgId )  AS P2,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,3 ,@rBatchId ,@rClassId,@rDivisionId , isnull([3],''),@rOrgId )  AS P3,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,4 ,@rBatchId ,@rClassId,@rDivisionId , isnull([4],''),@rOrgId )  AS P4,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,5 ,@rBatchId ,@rClassId,@rDivisionId , isnull([5],''),@rOrgId )  AS P5,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,6 ,@rBatchId ,@rClassId,@rDivisionId , isnull([6],''),@rOrgId ) AS P6,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,7 ,@rBatchId ,@rClassId,@rDivisionId , isnull([7],''),@rOrgId ) AS P7,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,8 ,@rBatchId ,@rClassId,@rDivisionId , isnull([8],''),@rOrgId ) AS P8,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,9 ,@rBatchId ,@rClassId,@rDivisionId , isnull([9],''),@rOrgId ) AS P9,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,10 ,@rBatchId ,@rClassId,@rDivisionId , isnull([10],''),@rOrgId ) AS P10,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,11 ,@rBatchId ,@rClassId,@rDivisionId , isnull([11],''),@rOrgId ) AS P11,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,12 ,@rBatchId ,@rClassId,@rDivisionId , isnull([12],''),@rOrgId ) AS P12,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,13 ,@rBatchId ,@rClassId,@rDivisionId , isnull([13],''),@rOrgId ) AS P13,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,14 ,@rBatchId ,@rClassId,@rDivisionId , isnull([14],''),@rOrgId ) AS P14,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,15 ,@rBatchId ,@rClassId,@rDivisionId , isnull([15],''),@rOrgId ) AS P15,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,16 ,@rBatchId ,@rClassId,@rDivisionId , isnull([16],''),@rOrgId ) AS P16,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,17 ,@rBatchId ,@rClassId,@rDivisionId , isnull([17],''),@rOrgId ) AS P17,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,18 ,@rBatchId ,@rClassId,@rDivisionId , isnull([18],''),@rOrgId ) AS P18,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,19 ,@rBatchId ,@rClassId,@rDivisionId , isnull([19],''),@rOrgId ) AS P19,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,20 ,@rBatchId ,@rClassId,@rDivisionId , isnull([20],''),@rOrgId ) AS P20,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,21 ,@rBatchId ,@rClassId,@rDivisionId , isnull([21],''),@rOrgId ) AS P21,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,22 ,@rBatchId ,@rClassId,@rDivisionId , isnull([22],''),@rOrgId ) AS P22,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,23 ,@rBatchId ,@rClassId,@rDivisionId , isnull([23],''),@rOrgId ) AS P23,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,24 ,@rBatchId ,@rClassId,@rDivisionId , isnull([24],''),@rOrgId ) AS P24,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,25 ,@rBatchId ,@rClassId,@rDivisionId , isnull([25],''),@rOrgId ) AS P25,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,26 ,@rBatchId ,@rClassId,@rDivisionId , isnull([26],''),@rOrgId ) AS P26,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,27 ,@rBatchId ,@rClassId,@rDivisionId , isnull([27],''),@rOrgId ) AS P27,
			[sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,28 ,@rBatchId ,@rClassId,@rDivisionId , isnull([28],''),@rOrgId ) AS P28,
			CASE WHEN @Days>=29 THEN  [sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,29 ,@rBatchId,@rClassId ,@rDivisionId , isnull([29],''),@rOrgId ) end AS P29,
			CASE WHEN @Days>=30 THEN [sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,30 ,@rBatchId ,@rClassId,@rDivisionId , isnull([30],''),@rOrgId ) end AS P30,
			CASE WHEN @Days>=31 THEN [sAttendance].[fWeekHOffGet](@rForYear ,@rForMonth ,31 ,@rBatchId ,@rClassId,@rDivisionId , isnull([31],''),@rOrgId )  end AS P31,
			
	 	--	 isnull([1],'') AS P1,
			--isnull([2],'') AS P2,
			--isnull([3],'') AS P3,
			--isnull([4],'') AS P4,
			--isnull([5],'') AS P5,
			--isnull([6],'') AS P6,
			--isnull([7],'') AS P7,
			--isnull([8],'') AS P8,
			--isnull([9],'') AS P9,
			--isnull([10],'') AS P10,
			--isnull([11],'') AS P11,
			--isnull([12],'') AS P12,
			--isnull([13],'') AS P13,
			--isnull([14],'') AS P14,
			--isnull([15],'') AS P15,
			--isnull([16],'') AS P16,
			--isnull([17],'') AS P17,
			--isnull([18],'') AS P18,
			--isnull([19],'') AS P19,
			--isnull([20],'') AS P20,
			--isnull([21],'') AS P21,
			--isnull([22],'') AS P22,
			--isnull([23],'') AS P23,
			--isnull([24],'') AS P24,
			--isnull([25],'') AS P25,
			--isnull([26],'') AS P26,
			--isnull([27],'') AS P27,
			--isnull([28],'') AS P28,
			--isnull([29],'') AS P29,
			--isnull([30],'') AS P30,
			--isnull([31],'') AS P31, 
			 EmployeeName ,
 
             sCommon.fGetDaysInMonth ( @rForMonth,@rForYear) AS DaysInMonth,
              DateOfBirth,Religion,MotherName,FamilyCast,Gender,
			  CastCategory
--,
--AttendanceTagId

 
FROM
(
	Select --TID, 
		ISNULL(ClassName + '-' + DivisionName,'') AS ClassDivisionTitle,
		Code,
		RollNo,
		AcademicId,
		Name,
		AttendanceTypeId ,
		YEAR(ST.AttendanceDate) AS ForYear,
		MONTH(ST.AttendanceDate) AS ForMonth,
		DATENAME(month, ST.AttendanceDate) AS ForMonthInWord,
		ClassId,
		DivisionId,
		ClassName,
		DivisionName,
		(
			CASE  WHEN IsPresent = 1 AND ST.AttendanceTagId = 1 THEN 'LC' 
				  WHEN IsPresent = 1 AND ST.AttendanceTagId = 2 THEN 'EL' 
				  WHEN IsPresent = 1 AND ST.AttendanceTagId = 3 THEN 'N' 
				  WHEN IsPresent = 1 AND ST.AttendanceTagId = 4 THEN 'G' 
			      WHEN IsPresent = 1  THEN 'P' 
				  WHEN IsPresent = 0 THEN 'A'
				  ELSE  DT.Value END 
		)AS  IsPresent,		
		day(ST.AttendanceDate) AS DayPart,
		LeftDate
		,
		ClassOrderIndex,
		DivisionOrderIndex,
		FooterSignature,EmployeeName,
		DateOfBirth,Religion,MotherName,FamilyCast,Gender,
		CastCategory
      -- DO NOT ADD ANY FIELD HERE WITHOUT PERMISSION OF RAJENDRA
	 FROM 
		#AttendanceRegistertempTable ST 
		INNER JOIN #TempDaysTempTable DT ON DT.DayPart = ST.DayPart
	 WHERE
		((LeftDate >= @lFirstDate) OR ISNULL(LeftDate,'') = '')
) AS SourceTable 
 
	           
	        
PIVOT
(	
MIN(IsPresent)
FOR DayPart IN ([0], [1], [2], [3], [4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31])
) AS PivotTable
	LEFT JOIN
	(
		Select  AcademicId AS AcademicId, COUNT(1) AS TCount 
		   FROM  
				#AttendanceRegistertempTable
		    
			GROUP BY  AcademicId
    ) TC ON TC.AcademicId = PivotTable.AcademicId
    
	LEFT JOIN
	(
		Select AcademicId AS AcademicId, COUNT(1) AS ACount 
		   FROM  #AttendanceRegistertempTable
		    WHERE   IsPresent = 0	 		
            
			GROUP BY  AcademicId
    ) AC ON AC.AcademicId = PivotTable.AcademicId
	LEFT JOIN
	(SELECT A.Id AS AcademicId, COUNT(1) AS PCount  FROM sAttendance.tStudentRegister SR 
			INNER JOIN sStudent.tAcademic A ON SR.AcademicId = A.Id 
		   INNER JOIN sStudent.tStudentMaster SA ON A.StudentId = SA.Id
		   INNER JOIN sSetup.tClassMaster CM ON A.ClassId = CM.Id
			WHERE  
			 SR.AttendanceDate < (convert(date,CONVERT(nvarchar(10), @rForYear)+ '/' +  CONVERT(nvarchar(10), @rForMonth) + '/' + '01' ))
			AND SR.IsPresent = 1	    		
            --AND (SA.StatusId = 1)
	        AND ((SR.AttendanceTypeId=@rAttendanceTypeId) OR (@rAttendanceTypeId= 0))
	        AND ((SA.OrgId=@rOrgId) OR (@rOrgId= 0))
	        AND ((A.BatchId=@rBatchId) OR (@rBatchId= 0))	          
	        AND ((A.ClassId=@rClassId) OR (@rClassId= 0))
	        AND ((SA.Code=@rStudentCode) OR (@rStudentCode=''))
	        AND ((A.Id=@rAcademicId) OR (@rAcademicId=0))	           
			AND ((CM.DepartmentId=@rDepartmentId) OR (@rDepartmentId=0))				
	        AND ((A.DivisionId=@rDivisionId) OR (@rDivisionId= 0))
	        AND ((SA.Code>=@rFromStudentCode) OR (ISNULL(@rFromStudentCode,'')=''))
	        AND ((SA.Code<=@rToStudentCode) OR (ISNULL(@rToStudentCode,'')=''))

			GROUP BY A.Id
    ) PC ON PC.AcademicId = PivotTable.AcademicId
		LEFT JOIN
	(SELECT A.Id AS AcademicId, COUNT(1) AS AUCount  FROM sAttendance.tStudentRegister SR 
			INNER JOIN sStudent.tAcademic A ON SR.AcademicId = A.Id 
		   INNER JOIN sStudent.tStudentMaster SA ON A.StudentId = SA.Id
		   INNER JOIN sSetup.tClassMaster CM ON A.ClassId = CM.Id
			WHERE  
			 SR.AttendanceDate < (convert(date,CONVERT(nvarchar(10), @rForYear)+ '/' +  CONVERT(nvarchar(10), @rForMonth) + '/' + '01' ))
			AND SR.IsPresent = 0	    		
            --AND (SA.StatusId = 1)
	        AND ((SR.AttendanceTypeId=@rAttendanceTypeId) OR (@rAttendanceTypeId= 0))
	        AND ((SA.OrgId=@rOrgId) OR (@rOrgId= 0))
	        AND ((A.BatchId=@rBatchId) OR (@rBatchId= 0))	          
	        AND ((A.ClassId=@rClassId) OR (@rClassId= 0))
	        AND ((SA.Code=@rStudentCode) OR (@rStudentCode=''))
	        AND ((A.Id=@rAcademicId) OR (@rAcademicId=0))	           
			AND ((CM.DepartmentId=@rDepartmentId) OR (@rDepartmentId=0))				
	        AND ((A.DivisionId=@rDivisionId) OR (@rDivisionId= 0))
	        AND ((SA.Code>=@rFromStudentCode) OR (ISNULL(@rFromStudentCode,'')=''))
	        AND ((SA.Code<=@rToStudentCode) OR (ISNULL(@rToStudentCode,'')=''))

			GROUP BY A.Id
    ) ABU ON ABU.AcademicId = PivotTable.AcademicId
	LEFT JOIN
	(SELECT A.Id AS AcademicId, COUNT(1) AS TotalPresent  FROM sAttendance.tStudentRegister SR 
			INNER JOIN sStudent.tAcademic A ON SR.AcademicId = A.Id 
		   INNER JOIN sStudent.tStudentMaster SA ON A.StudentId = SA.Id
		   INNER JOIN sSetup.tClassMaster CM ON A.ClassId = CM.Id
			WHERE  
			SR.AttendanceDate < (convert(date,CONVERT(nvarchar(10), @rForYear)+ '/' +  CONVERT(nvarchar(10), @rForMonth) + '/' + '01' ))
	        AND
	        ((SR.AttendanceTypeId=@rAttendanceTypeId) OR (@rAttendanceTypeId= 0))
	        AND ((SA.OrgId=@rOrgId) OR (@rOrgId= 0))
	        AND ((A.BatchId=@rBatchId) OR (@rBatchId= 0))	          
	        AND ((A.ClassId=@rClassId) OR (@rClassId= 0))
	        AND ((SA.Code=@rStudentCode) OR (@rStudentCode=''))
	        AND ((A.Id=@rAcademicId) OR (@rAcademicId=0))	           
			AND ((CM.DepartmentId=@rDepartmentId) OR (@rDepartmentId=0))				
	        AND ((A.DivisionId=@rDivisionId) OR (@rDivisionId= 0))
	        AND ((SA.Code>=@rFromStudentCode) OR (ISNULL(@rFromStudentCode,'')=''))
	        AND ((SA.Code<=@rToStudentCode) OR (ISNULL(@rToStudentCode,'')=''))

			GROUP BY A.Id
    ) AA ON AA.AcademicId = PivotTable.AcademicId
 
    ORDER BY RollNo;
 

select * from #TempDaysTempTable
 				
drop table #AttendanceRegistertempTable	  
drop table #TempDaysTempTable
	


	--SELECT  
	--		Convert(date,'') AS LastDate,
	--		Convert(date,'') AS LeftDate,
 --           '' AS Builder1,
	--		'' AS Builder2,
	--		Convert(bigint,0) AS AcademicId,
	--		'' AS ClassDivisionTitle,
	--		'' AS Code,
	--		0 as RollNo,
	--		'' AS Name,
	--		0 as AttendanceTypeId,
	--		0 as ForYear,
	--		0 as ForMonth,
	--		0 as ClassId,
	--		'' as ClassName,
	--		0 as DivisionId,
	--		'' as DivisionName,
	--		'' as ForMonthInWord,
	--		0 AS PresentCount,
	--		0 AS AbsentUpto,
	--		0 AS AttendanceCount,
	--	    0 As  AbsentCount,
	--	    0 As  PresentUpto,
	--	    0 as ClassOrderIndex,
	--	    0 as DivisionOrderIndex,
	--	    '' as FooterSignature,
	--		0 AS TotalPresent,
	--		'' AS P1,
	--		'' AS P2,
	--		'' AS P3,
	--		'' AS P4,
	--		'' AS P5,
	--		'' AS P6,
	--		'' AS P7,
	--		'' AS P8,
	--		'' AS P9,
	--		'' AS P10,
	--		'' AS P11,
	--		'' AS P12,
	--		'' AS P13,
	--		'' AS P14,
	--		'' AS P15,
	--		'' AS P16,
	--		'' AS P17,
	--		'' AS P18,
	--		'' AS P19,
	--		'' AS P20,
	--		'' AS P21,
	--		'' AS P22,
	--		'' AS P23,
	--		'' AS P24,
	--		'' AS P25,
	--		'' AS P26,
	--		'' AS P27,
	--		'' AS P28,
	--		'' AS P29,
	--		'' AS P30,
	--		'' AS P31,
	--		'' as EmployeeName ,
 --           0 AS DaysInMonth,
	--		Convert(date,'') AS DateOfBirth,
	--		'' as Religion,
	--		'' as MotherName,
	--		'' as FamilyCast,
	--		0 as Gender,
	--		'' as CastCategory



	
END
GO
PRINT N'Altering [sBiometric].[pShiftMasterCloudGet]...';


GO

ALTER PROCEDURE [sBiometric].[pShiftMasterCloudGet]
@rShiftCode int = 0,
@rShiftName nvarchar(50) = '',
@rOrgGroupId int = 0

AS
BEGIN

	select [ShiftCode]
      ,[ShiftName]
      ,[StartTime]
      ,[EndTime]
      ,[GraceBefore]
      ,[GraceAfter]
      ,[ShiftDur]
      ,[MinHrsHalfDay]
      ,[MinHrsFullDay]
      ,[RecessDur]
      ,[AlterGraceTime]
      ,[SMSScheduleTime]
      ,[ShiftGroupCode]
      ,[IsOutTimeAlertTime]
      ,[OutTimeAlertTime]
      ,[OrgGroupId]
      ,isnull([LastOutAlertBasedOnId],1) as LastOutAlertBasedOnId
	   from sBiometric.tShiftMasterCloud
	where 
	((ShiftCode = @rShiftCode) OR (@rShiftCode=0))     
	AND ((ShiftName = @rShiftName) OR (@rShiftName=''))     
	AND ((OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))     
END
GO
PRINT N'Altering [sCertificate].[pRptCharacterCertificateGet]...';


GO
ALTER PROCEDURE [sCertificate].[pRptCharacterCertificateGet]
	 
	 @rBuilder1 nvarchar(max)='',
	 @rBuilder2 nvarchar(max)='', 
	 @rOrgId smallint,
	 @rBatchId int,
	 @rFromNo nvarchar(20)='',
	 @rToNo nvarchar(20)='',
	 @rNumber nvarchar(20)='',
	 @rClassId int=0,
	 @rDivisionId int=0,
	 @rFromDate date='',
     @rToDate date='',
     @rCode nvarchar(20)='',
     @rName nvarchar(20)=''
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN 
DECLARE @lIsOnPrePrinted bit = 0

DECLARE @lConfigValue nvarchar(50)

DECLARE @lIsDisplayImageHeader bit=0
Declare @lIDConfigValue nvarchar(50)='',@lOrgGroupId int=0

Select @lOrgGroupId = OrgGroupId From  sSetup.tOrganizationMaster Where Id = @rOrgId
SELECT @lIDConfigValue =ItemValue From sConfig.tConfigurationSetup
WHERE ConfigurationId = 236 AND SourceId = @lOrgGroupId

SELECT @lConfigValue = ItemValue FROM sConfig.tConfigurationSetup WHERE ConfigurationId = 180 AND SourceTypeId = 1016 AND SourceId = @rOrgId
	
	IF (@lConfigValue IS NULL) OR (@lConfigValue = 'Default')
	BEGIN
	SET @lIsOnPrePrinted = 0
	SET @lIsDisplayImageHeader = 0
	END
	ELSE IF (@lConfigValue = 'Blank')
	BEGIN
	SET @lIsOnPrePrinted = 1
	SET @lIsDisplayImageHeader = 0
	END
	ELSE IF (@lConfigValue = 'Image')
	BEGIN
	SET @lIsOnPrePrinted = 0
	SET @lIsDisplayImageHeader = 1
	END
	
	--SELECT @lConfigValue = ItemValue FROM sConfig.tConfigurationSetup WHERE ConfigurationId = 158 AND SourceTypeId = 1016 AND SourceId = @rOrgId
	
	--IF (@lConfigValue IS NULL) OR (@lConfigValue = 'False')
	--SET @lIsDisplayImageHeader = 0
	--ELSE
	--SET @lIsDisplayImageHeader = 1	
	
	SELECT 
			@rBuilder1 AS Builder1,
		    @rBuilder2 AS Builder2,
			C.Id,
			C.AcademicId,
			SM.Code,
		--	SM.FirstName+' '+SM.MiddleName+' '+SM.LastName AS Name,
		   [sStudent].[fStudentNameGet](SM.FirstName , SM.MiddleName, SM.LastName,OM.StudentNameFormatId ) AS Name,
			CM.DisplayTitle+' - '+DM.DisplayTitle AS Class,
			CM.DisplayTitle AS ClassOnly,
			BM.Title AS Batch,
			SO.DateOfBirth,
			ISNULL(SO.BirthDateInWord,'') AS DOBInWords,
			ISNULL(SO.BirthPlace,'') AS BirthPlace,
			ISNULL(SO.BirthTaluka,'') AS Taluka,
			ISNULL(SO.BirthDistrict,'')	AS District,		
			C.characterDate,
			C.Number,
			ISNULL(SO.FamilyCast,'') AS Caste,
			ISNULL(SO.FamilySubCast,'') AS SubCaste,
			ISNULL(OM.CityName,'') AS City,
			ISNULL(OM.StateName,'') AS CityState,
			SO.GenderId,
			SI.StudentPhoto,
			OM.OrgLogo,
			OM.Title AS OrgName,
			OM.StateName+' '+OM.StreetTwo+' '+OM.StreetThree AS OrgAddress,
			OM.PhoneNo,
			SM.StatusId,
			A.RollNo,
			DM.OrderIndex AS DivisionOrder,
			OM.ReportOrgTitle,
			OM.SmallAddress1,
			OM.SmallAddress2,
			OM.RegistrationDetail,
			@lIsOnPrePrinted AS IsOnPrePrinted,
			@lIsDisplayImageHeader AS IsDisplayImageHeader,
			--OI.CertificateHeaderA4  ,
			SA.AdmissionDate,
			SF.FatherName,
			SF.MotherName,
			@lIDConfigValue AS ItemValue,
			OI.CertificateHeaderA4Portrait AS CertificateHeaderA4,
			A.Id,
			SA.LeftDate AS LCDate,
			C.Remark AS Remark,
			ISNULL(EI.EmployeeSign,0x) AS EmployeeSign,
			ISNULL(OM.PrincipalSignature,0x) AS PrincipalSignature,
			SM.FirstName,
			SM.LastName,
			SM.MiddleName,
			SM.SUId As SUID,
			DM.DisplayTitle AS DivisionOnly   --add by dhruvik
	FROM 
			sCertificate.tCharacter C
			INNER JOIN sStudent.tAcademic A ON C.AcademicId=A.Id
			LEFT JOIN sStudent.tStudentImages SI ON SI.StudentId=A.StudentId
			INNER JOIN sStudent.tStudentMaster SM ON SM.Id=A.StudentId
			INNER JOIN sStudent.tStudentOther SO ON SO.StudentId=A.StudentId
			INNER JOIN sSetup.tDivisionMaster DM ON A.DivisionId = DM.Id  
			INNER JOIN sSetup.tClassMaster CM ON CM.Id= DM.ClassId
			INNER JOIN sSetup.tBatchMaster BM ON BM.Id=A.BatchId
			INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=A.OrgId
			LEFT JOIN sSetup.tOrgImages OI ON OI.OrgId = OM.Id
			LEFT JOIN sStudent.tStudentAdmission SA ON SA.StudentId=A.StudentId
			LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId=A.StudentId
			LEFT JOIN sHrm.tEmployeeImages EI ON EI.EmployeeId = DM.InchargeEmployeeId
	WHERE  
			A.OrgId=@rOrgId
		AND	A.BatchId = @rBatchId 
		AND ((A.ClassId=@rClassId) OR (@rClassId=0))
		AND ((A.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		AND ((SM.Code = @rCode) OR (ISNULL(@rCode,'') = ''))
		AND ((SM.FirstName = @rName) OR (ISNULL(@rName,'') = ''))
		AND ((C.Number=@rNumber) OR (@rNumber=''))
		AND ((C.Number >= @rFromNo) OR (@rFromNo=''))
		AND ((C.Number <= @rToNo) OR (@rToNo=''))
		AND ((C.characterDate >= @rFromDate) OR (ISNULL(@rFromDate,'')=''))
		AND ((C.characterDate <= @rToDate) OR (ISNULL(@rToDate,'')=''))
		--Group By C.AcademicId,C.Number,SM.Code,SM.FirstName,SM.LastName,SM.MiddleName,CM.DisplayTitle,BM.Title,SO.DateOfBirth,SO.BirthDateInWord,
		--SO.BirthPlace,SO.BirthTaluka,SO.BirthDistrict,C.characterDate,OM.CityName,OM.StateName,SO.FamilyCast,SO.FamilySubCast,SO.GenderId,SI.StudentPhoto,SM.StatusId,OM.OrgLogo,OM.Title,
		--OM.StreetOne,OM.StreetThree,OM.StreetTwo,OM.PhoneNo
	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 FROM sFee.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

DELETE FROM [sAdmission].[tAdmissionInquiryCriteria] WHERE CAST(LEFT([ClassId], 4) AS int) = @rOrgId
DELETE FROM [sCommon].[tSubmitDocumentScope] WHERE CAST(LEFT([SubmitDocumentId], 4) AS int) = @rOrgId

DELETE FROM sAcademic.tOnlineLearningRecording WHERE CAST(LEFT([OnlineLearningId], 4) AS bigint) = @rOrgId

DELETE FROM [sCommon].[tUserToken] WHERE CAST(LEFT([UserId], 4) AS bigint) = @rOrgId

DELETE FROM [sBiometric].[tEmployeeConfiguration] WHERE CAST(LEFT([EmployeeId], 4) AS bigint) = @rOrgId
DELETE FROM sAlert.tWhatsAppConfiguration WHERE OrgId= @rOrgId
DELETE FROM sFee.tDepositSetup WHERE CAST(LEFT([StudentId], 4) AS bigint) = @rOrgId
DELETE FROM [sAlert].[tWhatsAppLog] WHERE CAST(LEFT([SourceId], 4) AS bigint) = @rOrgId

DELETE FROM sAcademic.tOnlineLearningHistoryLog WHERE CAST(LEFT([BatchId], 4) AS int) = @rOrgId

DELETE FROM [sAlert].[tAlertTrack] WHERE OrgId = @rOrgId
DELETE FROM sTally.tJVPosting WHERE CAST(LEFT([StudentId], 4) AS bigint) = @rOrgId

DELETE FROM [sHrm].[tLeavePeriod] WHERE OrgId = @rOrgId
DELETE FROM [sRecruitment].[tAssignOrganization] WHERE OrgId = @rOrgId

DELETE FROM sAdmission.tAdmissionCustomSearch WHERE OrgId = @rOrgId

DELETE FROM[sCommunication].[tTempQueryCategoryScope] WHERE CAST(LEFT([SourceId], 4) AS bigint) = @rOrgId


DELETE FROM [sStudent].[tOnlineRegistrationStudentAdmission] WHERE CAST(LEFT([StudentId], 4) AS bigint) = @rOrgId
DELETE FROM [sStudent].[tOnlineRegistrationStudentCommunication] WHERE CAST(LEFT([StudentId], 4) AS bigint) = @rOrgId
DELETE FROM [sStudent].[tOnlineRegistrationStudentCustomFieldData] WHERE CAST(LEFT([StudentId], 4) AS bigint) = @rOrgId
DELETE FROM [sStudent].[tOnlineRegistrationStudentDocument] WHERE CAST(LEFT([StudentId], 4) AS bigint) = @rOrgId
DELETE FROM [sStudent].[tOnlineRegistrationStudentFamily] WHERE CAST(LEFT([StudentId], 4) AS bigint) = @rOrgId
DELETE FROM [sStudent].[tOnlineRegistrationStudentImages] WHERE CAST(LEFT([StudentId], 4) AS bigint) = @rOrgId
DELETE FROM [sStudent].[tOnlineRegistrationStudentOther] WHERE CAST(LEFT([StudentId], 4) AS bigint) = @rOrgId
DELETE FROM [sStudent].[tOnlineRegistrationCollegeDetail] WHERE CAST(LEFT([StudentId], 4) AS bigint) = @rOrgId
DELETE FROM [sStudent].[tOnlineRegistrationDisplayField]  WHERE CAST(LEFT([BatchId], 4) AS int) = @rOrgId
DELETE FROM [sStudent].[tOnlineRegistrationCriteriaDocument]  WHERE CAST(LEFT([BatchId], 4) AS int) = @rOrgId
DELETE FROM [sStudent].[tOnlineRegistrationCriteriaForm]  WHERE CAST(LEFT([BatchId], 4) AS int) = @rOrgId
DELETE FROM [sStudent].[tOnlineRegistrationStudentMaster] WHERE OrgId = @rOrgId

DELETE FROM [sBiometric].[tShiftAllocationCloud] WHERE CAST(LEFT([SourceId], 4) AS bigint) = @rOrgId
DELETE FROM [sBiometric].[tCloudTransactionData] WHERE CAST(LEFT([SourceId], 4) AS bigint) = @rOrgId

DELETE FROM [sTransport].[tAssignRouteLog] WHERE CAST(LEFT([SourceId], 4) AS bigint) = @rOrgId
DELETE FROM [sAttendance].[tAttendanceFollowup] WHERE CAST(LEFT([AcademicId], 4) AS bigint) = @rOrgId

DELETE FROM sAssessment.tAssessmentLabelSetup WHERE OrgId = @rOrgId
DELETE FROM [sAssessment].[tStudentExamRemark] WHERE CAST(LEFT([AcademicId], 4) AS bigint) = @rOrgId
DELETE FROM [sBiometric].[tDeviceMasterCloud] WHERE OrgId = @rOrgId

DELETE FROM [sHrm].[tDigitalForm] WHERE OrgId = @rOrgId
DELETE FROM [sHrm].[tDigitalFormField] WHERE CAST(LEFT([DigitalFormId], 4) AS int) = @rOrgId
DELETE FROM [sHrm].[tDigitalFormScope] WHERE CAST(LEFT([DigitalFormId], 4) AS int) = @rOrgId
DELETE FROM [sHrm].[tDigitalFormEmployee] WHERE CAST(LEFT([DigitalFormId], 4) AS int) = @rOrgId
DELETE FROM [sAdmission].[tAdmissionOptionalSubject] WHERE CAST(LEFT([AdmissionFormId], 4) AS bigint) = @rOrgId
DELETE FROM [sStudent].[tOnlineRegistrationStudentOptionalSubject] WHERE CAST(LEFT([OnlineRegistrationStudentId], 4) AS bigint) = @rOrgId
DELETE FROM [sStudent].[tOnlineRegistrationCriteria] WHERE CAST(LEFT([BatchId], 4) AS int) = @rOrgId
DELETE FROM [sStudent].[tOnlineRegistrationCriteriaOptionalSubject] WHERE CAST(LEFT([BatchId], 4) AS int) = @rOrgId
DELETE FROM [sAdmission].[tAdmissionCriteriaOptionalSubject] WHERE CAST(LEFT([BatchId], 4) AS int) = @rOrgId

DELETE FROM [sAlert].[tAlertTrackHistory] WHERE OrgId = @rOrgId
DELETE FROM [sActivity].[tEventInchargeAssign] WHERE CAST(LEFT([SourceId], 4) AS bigint) = @rOrgId
DELETE FROM [sLibrary].[tNewspaperLog] WHERE CAST(LEFT([NewspaperId], 4) AS int) = @rOrgId
DELETE FROM sSetup.tClassContentSetup WHERE CAST(LEFT([ClassId], 4) AS int) = @rOrgId

DELETE FROM[sHrm].[tCertificateJoining] WHERE [OrgId] = @rOrgId
DELETE FROM[sCommunication].[tTempMultiPhotoCategoryScope] WHERE CAST(LEFT([SourceId], 4) AS bigint) = @rOrgId

DELETE FROM[sStudent].[tDigitalFormStudentStatus] WHERE CAST(LEFT([AcademicId], 4) AS bigint) = @rOrgId
DELETE FROM[sHrm].[tDigitalFormEmployeeStatus] WHERE CAST(LEFT([EmployeeId], 4) AS bigint) = @rOrgId
DELETE FROM[sHrm].[tLeaveHierarchyLevel] WHERE CAST(LEFT([LeaveId], 4) AS bigint) = @rOrgId

DELETE FROM [sAudit].[tChargeDeleted] WHERE [OrgId] = @rOrgId
DELETE FROM [sAudit].[tChargeDetailDelete] WHERE CAST(LEFT([ChargeReceiptId], 4) AS bigint) = @rOrgId

DELETE FROM sFee.tLateFeeRuleMaster WHERE OrgId = @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 [sCommunication].[pQueryCategoryDropdownGet]...';


GO

ALTER PROCEDURE [sCommunication].[pQueryCategoryDropdownGet]
	@rOrgGroupBatchId int,
	@rUserSourceId bigint,
	@rUserSourceTypeId int
	 
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 
	DECLARE @lAcademicId bigint,@lStudentId bigint,@lClassId int,@lDivisionId int,@lOrgId int,@lDepartmentId int,@lEmployeeId bigint

	IF @rUserSourceTypeId = 95 -- For Employee Login
	BEGIN
		SELECT 
			@lDepartmentId=EJD.OrganizationDepartmentId,
			@lOrgId = EM.OrgId,
			@lEmployeeId = EM.Id 
		FROM 
			sHrm.tEmployeeMaster EM INNER JOIN sHrm.tEmployeeJobDetail EJD ON EJD.EmployeeId = EM.Id 
		WHERE 
			EM.Id=@rUserSourceId
		
		SELECT Distinct	
			QCM.Id,
			QCM.Title
		FROM 
			sCommunication.tQueryCategoryMaster QCM           
			INNER JOIN 
			(SELECT QCS.QueryCategoryId FROM sCommunication.tQueryCategoryScope QCS
				WHERE (((QCS.SourceTypeId = 2021 AND QCS.SourceId =@lOrgId) OR
				(QCS.SourceTypeId = 2017 AND QCS.SourceId =@lDepartmentId) OR
				(QCS.SourceTypeId = 95 AND QCS.SourceId =@lEmployeeId )) 
				AND ((QCS.AssigneeToId=3) OR (QCS.AssigneeToId IS NULL) OR (ISNULL(QCS.AssigneeToId,0)=0)))) D
				ON D.QueryCategoryId= QCM.Id
			--INNER JOIN sSetup.tOrgGroupBatchMaster OGBM ON OGBM.Id=QCM.OrgGroupBatchId AND OGBM.Id=@rOrgGroupBatchId
			--INNER JOIN sSecurity.tUser U ON U.Id=QCM.CreatedUserId
		WHERE 
			QCM.AssignedToId IN (1,3) 
			AND QCM.IsObsolete = 0

		UNION

		SELECT Distinct	
			QCM.Id,
			QCM.Title
		FROM 
			sCommunication.tQueryCategoryMaster QCM           
			INNER JOIN sCommunication.tQueryCategoryAssignedTo QCA ON QCM.Id = QCA.QueryCategoryId
			AND QCA.SourceTypeId = 95 AND QCA.SourceId =@lEmployeeId
	
		WHERE 
			---QCM.AssignedToId IN (1,3) 
			QCM.IsObsolete = 0

	END
	ELSE IF @rUserSourceTypeId = 2003 -- For Student Login
	BEGIN
		SELECT 
			@lAcademicId = A.Id,
			@lStudentId = A.StudentId,
			@lClassId = A.ClassId, 
			@lDivisionId = A.DivisionId, 
			@lOrgId= A.OrgId, 
			@lDepartmentId=CM.DepartmentId
		FROM sStudent.tAcademic A 
			INNER JOIN sSetup.tBatchMaster BM ON A.BatchId = BM.Id 
			INNER JOIN sSetup.tClassMaster CM ON CM.Id = A.ClassId
		WHERE StudentId = @rUserSourceId AND BM.OrgGroupBatchId = @rOrgGroupBatchId
  
		SELECT 	Distinct
			QCM.Id,
			QCM.Title
		FROM 
			sCommunication.tQueryCategoryMaster QCM
			INNER JOIN (SELECT QCS.QueryCategoryId 
				FROM sCommunication.tQueryCategoryScope QCS
				WHERE (((QCS.SourceTypeId = 2021 AND QCS.SourceId =@lOrgId) OR
				(QCS.SourceTypeId = 2017 AND QCS.SourceId =@lDepartmentId) OR
				(QCS.SourceTypeId = 2016 AND QCS.SourceId =@lClassId) OR
				(QCS.SourceTypeId = 2005 AND QCS.SourceId =@lDivisionId) OR
				(QCS.SourceTypeId = 2003 AND QCS.SourceId =@lStudentId) OR
				(QCS.SourceTypeId = 2001 AND QCS.SourceId =@lAcademicId) )
				)AND ((QCS.AssigneeToId=2) OR (QCS.AssigneeToId IS NULL) OR (ISNULL(QCS.AssigneeToId,0)=0))) D ON D.QueryCategoryId= QCM.Id 
		WHERE
			QCM.AssignedToId IN (1,2) 
			AND QCM.IsObsolete = 0
	END
	ELSE
	--For Other Users
	BEGIN
		SELECT 	
			QCM.Id,
			QCM.Title
		FROM 
			sCommunication.tQueryCategoryMaster QCM
		WHERE 
			QCM.AssignedToId IN (1,2,3)
			AND QCM.IsObsolete = 0
	END
END
GO
PRINT N'Altering [sFee].[pAssignOptionalFeeAdd]...';


GO

 
ALTER PROCEDURE [sFee].[pAssignOptionalFeeAdd]
	 
		   @rFeeSetupId int,
           @rSourceTypeId int,
           @rSourceId bigint,
           @rBatchId int

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY
	
	 BEGIN TRANSACTION	
IF EXISTS (SELECT 1 FROM [sFee].[tFeeSetup] where Id=@rFeeSetupId AND IsOptional=1)
BEGIN
IF NOT EXISTS(SELECT 1 FROM sFee.tFeeOptional WHERE FeeSetupId=@rFeeSetupId AND SourceId=@rSourceId AND SourceTypeId=@rSourceTypeId AND BatchId=@rBatchId )	
BEGIN

	INSERT INTO sFee.tFeeOptional
		(
		   FeeSetupId,
		   SourceTypeId,
		   SourceId,
		   BatchId
		)
	VALUES
		(
			@rFeeSetupId,
			@rSourceTypeId,
			@rSourceId,
			@rBatchId
		)

		DECLARE @lOrgId NVARCHAR(10) = SUBSTRING(CAST(@rBatchId AS NVARCHAR(10)),1,4)

		EXEC sFee.pFeeProcessGenerateFeeOptionalAdd 0,@rFeeSetupId,@lOrgId,@rBatchId,0,0,3,@rSourceId,@rSourceTypeId

		SELECT 1 AS Result
		             
	END	 
	 ELSE
	 BEGIN

	      SELECT 11 AS Result   
	 END 
END
ELSE 
BEGIN

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


GO
ALTER PROCEDURE [sFee].[pFeeSetupAdd]
	 
		@rId int,
        @rClassId int,
        @rAdmissionCategoryId int,
        @rBatchId int,
        @rFeeId int,
        @rIsOptional bit,
        @rIsVariable bit,
        @rAmount decimal(12,2),
        @rStartDate date,
        @rEndDate date,
        @rReceiptBookId int,
        @rCreatedUserId bigint,
		@rUpdatedUserId bigint,
		@rOrgId int,
		@rCheck bit,
		@rIsServiceTax bit,
		@rCollectionGroupId int = 0,
		@rOrgGroupId int=0,
		@rFeeDuration nvarchar(100) ='',
		@rLevelAmount decimal(12,2)=0
		--@rLateFeeRuleId int = 0

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY
	DECLARE @lFeeSetupId int=0
	
	 IF(@rCheck=1 AND @rId>0)
	 BEGIN
	 
	 IF(@rIsOptional = 1 AND @rIsVariable = 1)
	 BEGIN
		  SELECT 0 as Result
		  RETURN
	 END
	 ELSE
	 BEGIN
			IF EXists( SELECT * FROM (
			SELECT AcademicId,	   
					(SELECT ISNULL(SUM(RD.Amount),0)  
					FROM sFee.tReceipt R INNER JOIN sFee.tReceiptDetail RD
					ON	RD.ReceiptId = R.Id
					AND RD.FeeSetupId = @rId
					WHERE R.SourceId = VA.AcademicId AND R.IsObsolete = 0) PaidAmount,		      
					(SELECT ISNULL(VariableAmount,0)-ISNULL(DiscountAmount,0) +
					IsNULL(AdditionalAmount,0) - ISNULL(NotApplicableAmount,0)  
					FROM  sFee.tFeeAllocation A		  
					WHERE A.SourceId = VA.AcademicId AND A.FeeSetupId = @rId  ) AllocationAmount
					--,
					--(SELECT Amount FROM sFee.tFeeSetup S WHERE S.Id = @rId 
					----S.ClassId = VA.AcademicId
					----AND S.AdmissionCategoryId = VA.AdmissionCategoryId
					--) AS FeeSetupAmount	   
			FROM sStudent.vStudentAcademic VA	   
			WHERE ClassId = @rClassId AND AdmissionCategoryId = @rAdmissionCategoryId AND VA.BatchId = @rBatchId
			   ) ST WHERE (@rAmount + ISNULL(ST.AllocationAmount,0) - ISNULL(ST.PaidAmount,0) < 0))
			   
			   select @rId as Result
			   ELSE
			   SELECT 0 as Result
			   RETURN
	 END
	 
		   
		   
		  --WHERE ST.Amount + AllocationAmount - PaidAmount > 0
		  
	 
	 
		    
		  
	 
		      
	 -- SELECT @lFeeSetupId=R.FeeSetupId FROM
	 -- sFee.tFeeSetup FS
		--LEFT JOIN
		--(
		--    SELECT ISNULL(SUM(RD.Amount),0) AS PaidAmount,
		--    RD.FeeSetupId,FA.VariableAmount,FA.AdditionalAmount,FA.DiscountAmount,FA.NotApplicableAmount 
		--    FROM
		--    sFee.tReceipt R INNER JOIN sFee.tReceiptDetail RD ON	RD.ReceiptId = R.Id
		--     LEFT JOIN sFee.tFeeAllocation FA ON FA.FeeSetupId=RD.FeeSetupId AND FA.SourceId=R.SourceId 
		--     AND FA.SourceTypeId=R.SourceTypeId	
		--	WHERE						
		--	 R.IsObsolete = 0 
		--	 GROUP BY RD.FeeSetupId,R.SourceId,R.SourceTypeId,FA.VariableAmount,FA.AdditionalAmount,
		--	 FA.DiscountAmount,FA.NotApplicableAmount
		
		--) R ON R.FeeSetupId = FS.Id 
		--where ISNULL(R.FeeSetupId,0)=@rId 
		--AND (@rAmount +  ISNULL(R.VariableAmount,0) + ISNULL(R.AdditionalAmount,0.00) -  ISNULL(R.DiscountAmount,0)-ISNULL(R.NotApplicableAmount,0) -ISNULL(R.PaidAmount,0))<0
		--BEGIN
		

--	select @lFeeSetupId as Result
		--	RETURN
		--END
		
	END
	 
 IF(@rId = 0)	
BEGIN
	
	DECLARE @lNextNumber BIGINT     
	EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sFee.tFeeSetup', 0, 1, 1,@outNextNumber = @lNextNumber output

	INSERT INTO sFee.tFeeSetup
		(
		   Id,
           ClassId,
           AdmissionCategoryId,
           BatchId,
           FeeId,
           IsOptional,
           IsVariable,
           Amount,
           StartDate,
           EndDate,
           ReceiptBookId,
           CreatedUserId,
           CreatedDateTime,
           IsServiceTax,
           CollectionGroupId,
           FeeDuration,
           LevelAmount
		   --LateFeeRuleId
        )
	VALUES
		(
			@lNextNumber,
			@rClassId,
			@rAdmissionCategoryId,
            @rBatchId,
            @rFeeId,
            @rIsOptional,
            @rIsVariable,
            @rAmount,
            @rStartDate,
            @rEndDate,
            @rReceiptBookId,
            @rCreatedUserId,
			sCommon.fGetDate(),
			@rIsServiceTax,
			@rCollectionGroupId,
			@rFeeDuration,
			@rLevelAmount
			--@rLateFeeRuleId
		)

		SELECT 1 AS Result
	END	 
	 ELSE
	 BEGIN
	 
	 SET @lNextNumber = @rId
	    UPDATE  sFee.tFeeSetup
	    SET
			 --ClassId=@rClassId,
			 --AdmissionCategoryId=@rAdmissionCategoryId,
    --         BatchId=@rBatchId,
             IsOptional=@rIsOptional,
             IsVariable=@rIsVariable,
             Amount=@rAmount,
             StartDate=@rStartDate,
             EndDate=@rEndDate,
             ReceiptBookId=@rReceiptBookId,
             UpdatedUserId=@rUpdatedUserId,
			 UpdatedDateTime=sCommon.fGetDate(),
			 IsServiceTax=@rIsServiceTax,
			 CollectionGroupId=@rCollectionGroupId,
			 FeeDuration=@rFeeDuration,
			 LevelAmount=@rLevelAmount
			 --LateFeeRuleId=@rLateFeeRuleId
	      WHERE (Id = @rId)
	       --AND (FeeId=@rFeeId) 

	      SELECT 11 AS Result   
	 END  
	
	If Not Exists(Select 1 from sSystem.tPortalSync where OrgGroupId=@rOrgGRoupId)
	BEGIN
		Insert INto sSystem.tPortalSync 
		(
			OrgGRoupId,
			FeeChange
		)
		Values
		(
			@rOrgGRoupId,
			1
		)
	END
	Else
	BEGIN
		Update sSystem.tPortalSync 
		Set
			FeeChange=1
		Where OrgGroupId=@rOrgGroupId
		
	END

	EXEC [sFee].[pFeeProcessGenerate] @rOrgId,@rBatchId,@rClassId,NULL,NULL,@rCreatedUserId,@lNextNumber

	END TRY
	
	BEGIN CATCH 
		
		SELECT -1 AS Result
	END CATCH
END
GO
PRINT N'Altering [sFee].[pRptFeePendingDetailListGet]...';


GO
ALTER PROCEDURE [sFee].[pRptFeePendingDetailListGet]  

	@rReportId int=0,  
	@rReceiptBookId int=0,
	@rReceiptBookIds nvarchar(max) = '',
	@rAdmissionCategoryId int=0,  
	@rAdmissionCategoryIds nvarchar(max) = '',
	@rClassId int=0,  
	@rDivisionId int=0,  
	@rUptoDate date='',  
	@rBatchId int=0,  
	@rUserId bigint=0,  
	@rBuilder1 nvarchar(max) = '',  
	@rBuilder2 nvarchar(max) = '',  
	@rIsDetail bit=0,  
	@rOrgId int=0,  
	@rOrgGroupBatchId int=0,  
	@rStatusId int=0,  
	@rCommonBatch nvarchar(100) = '',  
	@rDepartmentId int=0,  
	@rFromRollNo int=0,  
	@rToRollNo int=0,  
	@rCode nvarchar(20)='',  
	@rIsFilter bit=0,  
	@rIsHeader bit=0,    
	@rCreatedUserName nvarchar(20) = '',  
	@rIsReceiptBookGroup bit=0,  
	@rFeeHeadId nvarchar(max) = '',  
	@rIsDisplayOrgGroup bit=0,  
	@rFeeId nvarchar(max) = '',  
	@rFilterId int=0,  
	@rOrgIds  nvarchar(max) = '',  
	@rPayDate date='',  
	@rCollectionGroupId int =0,  
	@rCastCategoryId int = 0,
	@rReportType int=0,
	@rClassStatusId int=0 ,
	@rCollectionGrupId nvarchar(max) ='',
	@rClassIds nvarchar(max) = '',
	@rDivisionIds nvarchar(max) = ''
	 
WITH EXECUTE AS CALLER  
AS  
SET NOCOUNT ON  
BEGIN 
	
	CREATE TABLE #TempIsOptional
	(
		FeeId int,  
		Id int,  
		IsOptional bit,  
		IsVariable bit,  
		Amount decimal(12,2),  
		StartDate date,  
		EndDate date,  
		BatchId int,  
		ReceiptBookId int,  
		AcademicId bigint,  
		FeeName nvarchar(100),  
		FeeHeadId int,
		CollectionGroupId int,
		CollectionGroup nvarchar(max),
		FeeOrderIndex int,
		CollectionGroupOrderIndex int,
		CollectionGroupMasterRemark nvarchar(500),
		FeeDuration nvarchar(100)						--add
	)
	
	INSERT INTO #TempIsOptional
		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.Id AS AcademicId,  
			FM.Title AS FeeName,  
			FM.FeeHeadId,
			ISNULL(CGM.Id,0) AS CollectionGroupId,
		    ISNULL(CGM.Title,'') AS CollectionGroup,
			FM.OrderIndex,
			CGM.OrderIndex  AS CollectionGroupOrderIndex,
			CGM.Remark AS CollectionGroupMasterRemark,
			FS1.FeeDuration AS FeeDuration							--add
		FROM 
			sFee.tFeeSetup FS1  
			INNER JOIN sStudent.tAcademic SA1 ON SA1.ClassId=FS1.ClassId AND FS1.BatchId = SA1.BatchId   
			INNER JOIN sStudent.tStudentMaster SM ON SA1.StudentId = SM.Id      
			AND SA1.AdmissionCategoryId = FS1.AdmissionCategoryId  
			AND ((SA1.ClassId=@rClassId) OR (@rClassId=0)) 
			AND (ISNULL(SA1.ClassId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rClassIds,','))OR @rClassIds='')
			AND ((SA1.DivisionId=@rDivisionId) OR (@rDivisionId=0))
			AND (ISNULL(SA1.DivisionId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rDivisionIds,','))OR @rDivisionIds='')  
			AND ((SA1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=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  
			LEFT JOIN sFee.tCollectionGroupMaster CGM ON FS1.CollectionGroupId=CGM.Id 
			--AND RBM.Id = CGM.ReceiptBookId 
			AND CGM.OrgGroupBatchId = @rOrgGroupBatchId
		WHERE   
			BM.OrgGroupBatchId = @rOrgGroupBatchId  
			AND ((FS1.ClassId=@rClassId) OR (@rClassId=0))      
			AND ((SA1.OrgId=@rOrgId) OR (@rOrgId=0))  
			AND ((FS1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))  
			AND (FS1.AdmissionCategoryId IN(SELECT cast(item as int) FROM  sCommon.fnsplit(@rAdmissionCategoryIds,',')) OR @rAdmissionCategoryIds='')
			AND ((FS1.ReceiptBookId=@rReceiptBookId) OR (@rReceiptBookId=0))  
			AND (FS1.ReceiptBookId IN(SELECT cast(item as int) FROM  sCommon.fnsplit(@rReceiptBookIds,',')) OR @rReceiptBookIds='')
			AND ((FS1.CollectionGroupId=@rCollectionGroupId) OR (@rCollectionGroupId=0))  
			AND (FS1.IsOptional=0)  
			AND ((BM.Title = @rCommonBatch) OR (@rCommonBatch = ''))  
			AND ((FS1.BatchId=@rBatchId) OR (@rBatchId=0))  
			AND ((SA1.BatchId=@rBatchId) OR (@rBatchId=0))      
			AND (ISNULL(FM.FeeHeadId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rFeeHeadId,','))OR @rFeeHeadId='')  
			AND (ISNULL(FM.Id,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rFeeId,','))OR @rFeeId='')  
			AND (ISNULL(SA1.OrgId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rOrgIds,','))OR @rOrgIds='')
			AND (ISNULL(CGM.Id,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rCollectionGrupId,','))OR @rCollectionGrupId='')
			AND (ISNULL(FS1.ClassId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rClassIds,','))OR @rClassIds='') 
			AND (ISNULL(SA1.DivisionId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rDivisionIds,','))OR @rDivisionIds='')  

	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.Id AS AcademicId,  
			FM.Title AS FeeName,  
			FM.FeeHeadId,
			ISNULL(CGM.Id,0) AS CollectionGroupId,
		    ISNULL(CGM.Title,'') AS CollectionGroup,
			FM.OrderIndex,
			CGM.OrderIndex  AS CollectionGroupOrderIndex,
			CGM.Remark AS CollectionGroupMasterRemark,
			FS1.FeeDuration AS FeeDuration					--add
		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.tAcademic SA1 ON SA1.Id=FO.SourceId   
			AND SA1.BatchId = FS1.BatchId AND SA1.AdmissionCategoryId = FS1.AdmissionCategoryId AND SA1.ClassId = FS1.ClassId  
			
			AND ((SA1.ClassId=@rClassId) OR (@rClassId=0)) 
			AND (ISNULL(SA1.ClassId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rClassIds,','))OR @rClassIds='')
			AND ((SA1.DivisionId=@rDivisionId) OR (@rDivisionId=0)) 
			AND (ISNULL(SA1.DivisionId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rDivisionIds,','))OR @rDivisionIds='')  
			AND ((SA1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))  
			INNER JOIN sSetup.tBatchMaster BM ON FS1.BatchId = BM.Id AND BM.OrgGroupBatchId = @rOrgGroupBatchId 
			LEFT JOIN sFee.tCollectionGroupMaster CGM ON FS1.CollectionGroupId=CGM.Id 
			--AND RBM.Id = CGM.ReceiptBookId 
			AND CGM.OrgGroupBatchId = @rOrgGroupBatchId
		WHERE  
			BM.OrgGroupBatchId = @rOrgGroupBatchId  
			AND ((FS1.ClassId=@rClassId) OR (@rClassId=0))  
			AND ((SA1.OrgId=@rOrgId) OR (@rOrgId=0))  
			AND ((FS1.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))  
			AND (FS1.AdmissionCategoryId IN(SELECT cast(item as int) FROM  sCommon.fnsplit(@rAdmissionCategoryIds,',')) OR @rAdmissionCategoryIds='')
			AND ((FS1.ReceiptBookId=@rReceiptBookId) OR (@rReceiptBookId=0))  
			AND (FS1.ReceiptBookId IN(SELECT cast(item as int) FROM  sCommon.fnsplit(@rReceiptBookIds,',')) OR @rReceiptBookIds='')
			AND ((FS1.CollectionGroupId=@rCollectionGroupId) OR (@rCollectionGroupId=0))  
			AND (FS1.IsOptional=1)  
			AND ((BM.Title = @rCommonBatch) OR (@rCommonBatch = ''))  
			AND ((FS1.BatchId=@rBatchId) OR (@rBatchId=0))  
			AND ((SA1.BatchId=@rBatchId) OR (@rBatchId=0))      
			AND (ISNULL(FM.FeeHeadId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rFeeHeadId,','))OR @rFeeHeadId='')  
			AND (ISNULL(FM.Id,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rFeeId,','))OR @rFeeId='')  
			AND (ISNULL(SA1.OrgId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rOrgIds,','))OR @rOrgIds='')  
			AND (ISNULL(CGM.Id,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rCollectionGrupId,','))OR @rCollectionGrupId='')
			AND (ISNULL(FS1.ClassId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rClassIds,','))OR @rClassIds='')
			AND (ISNULL(SA1.DivisionId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rDivisionIds,','))OR @rDivisionIds='')   
	 
	CREATE TABLE #TempReceipt
	(
		PaidAmount decimal(12,2),
		FeeSetupId int,
		SourceId bigint  
	)
	
	INSERT INTO #TempReceipt
	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.tAcademic SA1 ON SA1.Id=R.SourceId  
		INNER JOIN sSetup.tBatchMaster BM ON BM.Id=SA1.BatchId     
		AND (SA1.ClassId=@rClassId OR @rClassId=0)   
		AND (ISNULL(SA1.ClassId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rClassIds,','))OR @rClassIds='')
		AND ((SA1.DivisionId=@rDivisionId) OR (@rDivisionId=0))  
		AND (ISNULL(SA1.DivisionId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rDivisionIds,','))OR @rDivisionIds='') 
		AND (SA1.DivisionId=@rDivisionId OR @rDivisionId=0)  
	WHERE    
		(R.SourceTypeId = 2001)  
		AND ((SA1.OrgId=@rOrgId) OR (@rOrgId=0))  
		AND (R.IsObsolete = 0)   
		AND ((BM.OrgGroupBatchId = @rOrgGroupBatchId))  
		AND (R.OrgGroupBatchId = @rOrgGroupBatchId)  
		AND (R.ReceiptDate <= @rUptoDate) -- added condition after nes issue on 09/08/2016  
		-- OR (@rOrgGroupBatchId = @rOrgGroupBatchId))  
		AND ((R.BatchId=@rBatchId) OR (@rBatchId=0))  
		AND ((R.CreatedUserId=@rUserId) OR (@rUserId=0))  
		AND ((BM.Title = @rCommonBatch) OR (@rCommonBatch = ''))  
		AND (ISNULL(FM.FeeHeadId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rFeeHeadId,','))OR @rFeeHeadId='')  
		AND (ISNULL(FM.Id,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rFeeId,','))OR @rFeeId='')  
		AND (ISNULL(SA1.OrgId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rOrgIds,','))OR @rOrgIds='')
	GROUP BY FeeSetupId,R.SourceId  
	
	CREATE TABLE #TempAssignRoute
	(
		SourceId bigint  
	)
	
	INSERT INTO #TempAssignRoute
	SELECT 
		AT.SourceId 
	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))
	
	IF(@rReportType=1)
	BEGIN	   
		SELECT   
		@rBuilder1 AS Builder1,  
		@rBuilder2 AS Builder2,  
		@rIsDetail AS IsDetail,  
		@rIsFilter As IsFilter,  
		@rIsHeader As IsHeader,  
		@rIsDisplayOrgGroup AS IsDisplayOrgGroup,  
		SA.AcademicId,  
		SA.RollNo,  
		SA.Name,  
		SA.Code,  
		SA.ClassId,  
		SA.DivisionId,  
		SA.ClassName,  
		SA.DivisionName,  
		SA.DivisionOrder,  
		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.FeeName,  
		FHM.Id AS FeeHeadId,  
		FHM.Title AS FeeHeadName,  
		FHM.OrderIndex AS FeeHeadOrderIndex,  
		SA.OrgId,  
		O.Title AS OrgName,  
		O.OrderIndex AS OrgOrderIndex,  
		(CASE WHEN @rIsReceiptBookGroup = 0 THEN 0  
		WHEN @rIsReceiptBookGroup = 1 THEN FS.ReceiptBookId  
		END) AS ReceiptBookId,   
		(CASE WHEN @rIsReceiptBookGroup = 0 THEN 'ALL'  
		WHEN @rIsReceiptBookGroup = 1 THEN ISNULL(RBM.Title,'')  
		END) AS ReceiptBookTitle,  
		(CASE WHEN @rIsReceiptBookGroup = 0 THEN 0  
		WHEN @rIsReceiptBookGroup = 1 THEN ISNULL(RBM.OrderIndex,0)  
		END) AS ReceiptOrderIndex,    
		--FS.ReceiptBookId,  
		ISNULL(RBM.FullTitle,'') AS ReceiptBookFullTitle,
		ISNULL(RBM.Address1,'') AS RecieptBookAddress1,
		IsNULL(RBM.Address2,'') As RecieptBookAddress2,
		IsNULL(RBM.Remark1,'') As Remark1,
		--ISNULL(RBM.OrderIndex,0) AS ReceiptOrderIndex,  
		SA.OrgGroupBatchId,  
		SA.DepartmentId,  
		SA.DepartmentName,  
		ISNULL(SC.ParentContactNo,'') AS ParentContactNo,  
		ISNULL(SO.ReferenceRemark,'') AS ReferenceRemark,  
		@rPayDate AS PayDate ,  
		ISNULL(CCM.MainCastCategoryId,0) AS CastCategoryId,  
		ISNULL(CCM.Title,'') AS CastCategoryName,  
		ISNULL(CCM.OrderIndex,'') AS CastCategoryOrderIndex,
		ISNULL((SELECT ISNULL(Template,'') FROM sCommon.tReportTemplate WHERE ReportId = @rReportId AND OrgId=SA.OrgId),0) AS ReportTemplate  ,
		FS.CollectionGroupId,
		FS.CollectionGroup,
		SA.BatchName,
		ISNULL(SSF.FatherName, '') AS FatherName,
		ISNULL(SSF.FatherContactNo, '') AS FatherContactNo,
		ISNULL(SSF.MotherName, '') AS MotherName,
		ISNULL(SSF.MotherContactNo, '') AS MotherContactNo,
		FS.FeeOrderIndex AS FeeOrderIndex,
		ISNULL(SC.PresentStreetOne,'')+' '+ISNULL(SC.PresentStreetTwo,'')+' '+ISNULL(SC.PresentStreetThree,'')+' '+ISNULL(SC.PresentCity,'')+' '+ISNULL(SC.PresentState,'') AS [Address],
		ISNULL(SC.StudentContactNo,'') AS StudentContactNo,
	 SA.GenderId,
	 SA.FirstName as StudentName,
	 FS.CollectionGroupOrderIndex,
	 SA.SUId AS SUId,
	 FS.CollectionGroupMasterRemark,
	 SA.LastName as LastName,
	 FS.FeeDuration as FeeDuration	--add

	FROM   
		sStudent.vStudentAcademic SA  
		INNER JOIN #TempIsOptional FS ON FS.AcademicId=SA.AcademicId  
		LEFT JOIN sFee.tFeeAllocation FA  
		ON FA.FeeSetupId=FS.Id AND FA.SourceId=SA.AcademicId  
		LEFT JOIN #TempReceipt 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  
		LEFT JOIN sStudent.tStudentCommunication SC   
		ON SC.StudentId = SA.StudentId  
		LEFT JOIN sStudent.tStudentOther SO   
		ON SO.StudentId = SA.StudentId  
		LEFT JOIN sCommon.tCastCategoryMaster CCM   
		ON CCM.Id = SO.CastCategoryId    
		INNER JOIN sFee.tFeeHeadMaster FHM  
		ON FHM.Id = FS.FeeHeadId
		
		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))   
		LEFT JOIN #TempAssignRoute RT ON RT.SourceId = SA.StudentId AND ((SA.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0))
		LEFT JOIN sStudent.tStudentFamily SSF ON SSF.StudentId = SA .StudentId
	WHERE   
		SA.OrgGroupBatchId = @rOrgGroupBatchId  
		AND ((SA.BatchId=@rBatchId) OR (@rBatchId=0))   
		AND (SA.StatusId = @rStatusId OR @rStatusId=0)  
		AND ((FS.StartDate <= @rUptoDate) OR (ISNULL(@rUptoDate,'') = ''))  
		AND ((SA.RollNo >= @rFromRollNo) OR (@rFromRollNo=0))  
		AND ((SA.RollNo <= @rToRollNo) OR (@rToRollNo=0))  
		AND ((SA.Code=@rCode) OR (@rCode=''))  
		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 (  
				(@rFilterId = 0) -- All  
				OR (@rFilterId = 2 AND isnull(AR.SourceId,0)!=0) -- Hostel  
				OR (@rFilterId = 3 AND isnull(RT.SourceId,0)!=0) -- Transport  
				OR ((@rFilterId =1) AND ((isnull(AR.SourceId,0)=0) AND (isnull(RT.SourceId,0)=0)))  
			)  
		AND (ISNULL(SA.OrgId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rOrgIds,','))OR @rOrgIds='')       
		AND ((CCM.Id=@rCastCategoryId) OR (@rCastCategoryId=0)) 
		AND ((SA.ClassStatusId=@rClassStatusId) OR (@rClassStatusId=0))
	Order by SA.RollNo  
	END
	ELSE
	BEGIN
	SELECT   
		@rBuilder1 AS Builder1,  
		@rBuilder2 AS Builder2,  
		@rIsDetail AS IsDetail,  
		@rIsFilter As IsFilter,  
		@rIsHeader As IsHeader,  
		@rIsDisplayOrgGroup AS IsDisplayOrgGroup,  
		SA.AcademicId,  
		SA.RollNo,  
		SA.Name,  
		SA.Code,  
		SA.ClassId,  
		SA.DivisionId,  
		SA.ClassName,  
		SA.DivisionName,  
		SA.DivisionOrder,  
		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(FAS.PaidAmount,0) AS PaidAmount,
		ISNULL(FAS.ToPayAmount,0.00) PendingAmount,
		ISNULL(FA.Remark,'') AS Remark,  
		--FS.FeeName,  
		FM.Title AS FeeName,  
		FHM.Id AS FeeHeadId,  
		FHM.Title AS FeeHeadName,  
		FHM.OrderIndex AS FeeHeadOrderIndex,  
		SA.OrgId,  
		O.Title AS OrgName,  
		O.OrderIndex AS OrgOrderIndex,  
		(CASE WHEN @rIsReceiptBookGroup = 0 THEN 0  
		WHEN @rIsReceiptBookGroup = 1 THEN FS.ReceiptBookId  
		END) AS ReceiptBookId,   
		(CASE WHEN @rIsReceiptBookGroup = 0 THEN 'ALL'  
		WHEN @rIsReceiptBookGroup = 1 THEN ISNULL(RBM.Title,'')  
		END) AS ReceiptBookTitle,  
		(CASE WHEN @rIsReceiptBookGroup = 0 THEN 0  
		WHEN @rIsReceiptBookGroup = 1 THEN ISNULL(RBM.OrderIndex,0)  
		END) AS ReceiptOrderIndex,    
		ISNULL(FS.ReceiptBookId,'0') As ReceiptBookId,  
		ISNULL(RBM.FullTitle,'') AS ReceiptBookFullTitle,
		ISNULL(RBM.Address1,'') AS RecieptBookAddress1,
		IsNULL(RBM.Address2,'') As RecieptBookAddress2,
		IsNULL(RBM.Remark1,'') As Remark1,
		ISNULL(RBM.OrderIndex,0) AS ReceiptOrderIndex,  
		SA.OrgGroupBatchId,  
		SA.DepartmentId,  
		SA.DepartmentName,  
		ISNULL(SC.ParentContactNo,'') AS ParentContactNo,  
		ISNULL(SO.ReferenceRemark,'') AS ReferenceRemark,  
		@rPayDate AS PayDate ,  
		ISNULL(CCM.MainCastCategoryId,0) AS CastCategoryId,  
		ISNULL(CCM.Title,'') AS CastCategoryName,  
		ISNULL(CCM.OrderIndex,'') AS CastCategoryOrderIndex,
		ISNULL((SELECT ISNULL(Template,'') FROM sCommon.tReportTemplate WHERE ReportId = @rReportId AND OrgId=SA.OrgId),0) AS ReportTemplate,
		ISNULL(CGM.Id,0) AS CollectionGroupId,
		ISNULL(CGM.Title,'') AS CollectionGroup,
		SA.BatchName,
		ISNULL(SSF.FatherName, '') AS FatherName,
		ISNULL(SSF.FatherContactNo, '') AS FatherContactNo,
		ISNULL(SSF.MotherName, '') AS MotherName,
		ISNULL(SSF.MotherContactNo, '') AS MotherContactNo,
		FM.OrderIndex AS FeeOrderIndex,
		ISNULL(SC.PresentStreetOne,'')+' '+ISNULL(SC.PresentStreetTwo,'')+' '+ISNULL(SC.PresentStreetThree,'')+' '+ISNULL(SC.PresentCity,'')+' '+ISNULL(SC.PresentState,'') AS [Address],
		ISNULL(SC.StudentContactNo,'') AS StudentContactNo,
		CGM.OrderIndex  AS CollectionGroupOrderIndex,
		SA.SUId AS SUId
	FROM   
		sStudent.vStudentAcademic SA		
		INNER JOIN sFee.tFeeAllocationStudent FAS ON FAS.AcademicId=  SA.AcademicId	
		INNER JOIN sFee.tFeeSetup FS ON FS.ID=FAS.FeeSetupId	
		INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS.FeeId
		INNER JOIN sSetup.tOrganizationMaster O ON SA.OrgId = O.Id
		INNER JOIN sFee.tReceiptBookMaster RBM ON FS.ReceiptBookId=RBM.Id		
		INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id=FM.FeeHeadId
		LEFT JOIN sFee.tFeeAllocation FA ON FA.FeeSetupId=FAS.FeeSetupId AND FA.SourceId=SA.AcademicId  
		LEFT JOIN sStudent.tStudentCommunication SC	ON SC.StudentId = SA.StudentId  
		LEFT JOIN sStudent.tStudentOther SO ON SO.StudentId = SA.StudentId  
		LEFT JOIN sCommon.tCastCategoryMaster CCM ON CCM.Id = SO.CastCategoryId
		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)) 
        LEFT JOIN
		(SELECT AT.SourceId 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 = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0))
		LEFT JOIN sFee.tCollectionGroupMaster CGM ON FS.CollectionGroupId=CGM.Id 
		AND CGM.OrgGroupBatchId = @rOrgGroupBatchId
		LEFT JOIN sStudent.tStudentFamily SSF ON SSF.StudentId = SA .StudentId
	WHERE   
		SA.OrgGroupBatchId = @rOrgGroupBatchId  
		AND ((SA.BatchId=@rBatchId) OR (@rBatchId=0))   
		AND (SA.StatusId = @rStatusId OR @rStatusId=0)  
		AND ((FS.StartDate <= @rUptoDate) OR (ISNULL(@rUptoDate,'') = ''))  
		AND ((SA.RollNo >= @rFromRollNo) OR (@rFromRollNo=0))  
		AND ((SA.RollNo <= @rToRollNo) OR (@rToRollNo=0))  
		AND ((SA.Code=@rCode) OR (@rCode=''))  
		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 ISNULL(FAS.ToPayAmount,0.00) !=0
		AND (  
				(@rFilterId = 0) -- All  
				OR (@rFilterId = 2 AND isnull(AR.SourceId,0)!=0) -- Hostel  
				OR (@rFilterId = 3 AND isnull(RT.SourceId,0)!=0) -- Transport  
				OR ((@rFilterId =1) AND ((isnull(AR.SourceId,0)=0) AND (isnull(RT.SourceId,0)=0)))  
			)  
		AND (ISNULL(SA.OrgId,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rOrgIds,','))OR @rOrgIds='')       
		AND ((CCM.Id=@rCastCategoryId) OR (@rCastCategoryId=0))   
		AND ((SA.ClassStatusId=@rClassStatusId) OR (@rClassStatusId=0))
		AND (ISNULL(CGM.Id,0) IN( SELECT cast(item as int) FROM  sCommon.fnsplit(@rCollectionGrupId,','))OR @rCollectionGrupId='')
	Order by SA.RollNo  
	END
	
	DROP TABLE #TempIsOptional
	DROP TABLE #TempReceipt
	DROP TABLE #TempAssignRoute


	
	--SELECT 
	--    '' as Builder1,  
	--	'' as Builder2,  
	--	Convert(bit,0) as IsDetail,  
	--	Convert(bit,0) as IsFilter,  
	--	Convert(bit,0) as IsHeader,  
	--	Convert(bit,0) as IsDisplayOrgGroup,  
	--	Convert(bigint,0) as AcademicId,
	--	0 As RollNo,  
	--	'' as Name,  
	--	'' As Code,  
	--	0 as ClassId,  
	--	0 as DivisionId,  
	--	'' as ClassName,  
	--	'' as DivisionName,  
	--	0 as DivisionOrder,  
	--	 '' as ClassDivision,  
	--	0 as FeeSetupId,  
	--	Convert(bit,0) as IsVariable,  
	--	 0.00 As Amount,  
	--	'2001-01-30' AS StartDate,  
	--	'2001-01-30' As EndDate,  
	--	0.00 As FAAmount,  
	--	0.00 As DiscountAmount,  
	--	0.00 As AdditionalAmount,  
	--	0.00 As PaidAmount,
	--	0.00 As  PendingAmount,
	--	'' AS Remark,  
	--	--FS.FeeName,  
	--	'' AS FeeName,  
	--	0 AS FeeHeadId,  
	--	'' AS FeeHeadName,  
	--	0 As FeeHeadOrderIndex,  
	--	0 As OrgId,  
	--	'' AS OrgName,  
	--	0 AS OrgOrderIndex,  
	--	0 AS ReceiptBookId,   
	--	'' AS ReceiptBookTitle,  
	--	0 AS ReceiptOrderIndex,    
	--	'' AS ReceiptBookFullTitle,
	--	'' AS RecieptBookAddress1,
	--	'' AS RecieptBookAddress2,
	--	'' As Remark1,
	--	0 As OrgGroupBatchId,  
	--	0 As DepartmentId,  
	--	'' AS DepartmentName,  
	--	'' AS ParentContactNo,  
	--	'' AS ReferenceRemark,  
	--	'2001-01-30' AS PayDate ,  
	--	0 AS CastCategoryId,  
	--	'' AS CastCategoryName,  
	--	0 AS CastCategoryOrderIndex,
	--	'' AS ReportTemplate,
	--	0 AS CollectionGroupId,
	--	'' AS CollectionGroup,
	--	'' AS BatchName,
	--	'' AS FatherName,
	--	 '' AS FatherContactNo,
	--	 '' AS MotherName,
	--	 '' AS MotherContactNo,
	--	0 AS FeeOrderIndex,
	--	'' AS Address,
	--	'' AS StudentContactNo,
	--    '' as StudentName,
	--	0 As GenderId,
	-- 0 AS CollectionGroupOrderIndex,
	-- '' AS SUId,
	-- '' AS CollectionGroupMasterRemark,
	-- '' AS LastName,
	-- '' AS FeeDuration

END
GO
PRINT N'Altering [sHostel].[pHostelAdmissionGet]...';


GO
ALTER PROCEDURE [sHostel].[pHostelAdmissionGet]
          
           @rHostelId int,
           @rOrgId int,
           @rSourceTypeId smallint,
           @rCode nvarchar(20),
           @rFromAllocateDate date,
           @rToAllocateDate date,
           @rToVacantDate date='',
           @rFromVacantDate date='',
           @rSUID nvarchar(50),
           @rOrgGroupBatchId int       
AS
SET NOCOUNT ON

BEGIN
IF (@rSourceTypeId = 95)
	BEGIN 
		SELECT
			OM.DisplayTitle AS OrganizationName,
			'Employee' AS SourceType,
			Emp.Code AS SourceCode, 
			'' AS SUId,
			Emp.EmployeeName AS SourceName,
			Emp.DepartmentName AS ClassDepartmentName,
			HA.SourceTypeId,
			HA.SourceId,
			HA.AllocateDate,
			(CASE WHEN HA.VacantDate = '1900-01-01' THEN ''
			ELSE HA.VacantDate END) AS VacantDate,	
			ISNULL(HA.VacantReason,'') AS VacantReason,
			ISNULL(RM.Title, '') AS RoomName

		FROM 
			sHostel.tHostelAdmission HA 
			INNER JOIN sHrm.vExtEmployeeMaster Emp ON Emp.Id = HA.SourceId
			INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = Emp.OrgId
			LEFT JOIN sHostel.tAssignRoom AR ON AR.SourceId = Emp.EmployeeId AND AR.SourceTypeId = 95 AND AR.OrgGroupBatchId = HA.OrgGroupBatchId 
			LEFT JOIN sHostel.tRoomMaster RM ON RM.HostelId = HA.Hosteld AND RM.Id = AR.RoomId	 

		WHERE 
			(HA.Hosteld=@rHostelId)
			AND ((OM.Id=@rOrgId) OR (@rOrgId=0))
			AND ((Emp.Code LIKE @rCode) OR (ISNULL(@rCode,'')=''))
			AND ((HA.AllocateDate >= @rFromAllocateDate) OR (ISNULL(@rFromAllocateDate,'') = ''))
			AND ((HA.AllocateDate <= @rToAllocateDate) OR (ISNULL(@rToAllocateDate,'') = ''))
			AND ((HA.VacantDate >= @rFromVacantDate) OR (ISNULL(@rFromVacantDate,'') = ''))
			AND ((HA.VacantDate <= @rToVacantDate) OR (ISNULL(@rToVacantDate,'') = ''))
			AND HA.OrgGroupBatchId=@rOrgGroupBatchId
		--ORDER BY  HA.AllocateDate DESC
	END

ELSE IF (@rSourceTypeId = 2003)
	BEGIN
		SELECT
			OM.DisplayTitle AS OrganizationName,
			'Student' AS SourceType,
			SM.Code AS SourceCode, 
			SM.SUId,
			SM.LastName + ' ' + SM.FirstName  AS SourceName,
			sStudent.fStudentCurrentDivisionGet(SM.StudentId) AS ClassDepartmentName,
			HA.SourceTypeId,
			HA.SourceId,
			HA.AllocateDate,
			(CASE WHEN HA.VacantDate = '1900-01-01' THEN ''
			ELSE HA.VacantDate END) AS VacantDate,	
			ISNULL(HA.VacantReason,'') AS VacantReason,
			ISNULL(RM.Title, '') AS RoomName

		FROM 
			sHostel.tHostelAdmission HA
			INNER JOIN sStudent.vStudentAcademic SM ON SM.StudentId = HA.SourceId AND SM.OrgGroupBatchId=@rOrgGroupBatchId
			INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SM.OrgId
			INNER JOIN sSetup.tBatchMaster BM ON BM.OrgGroupBatchId = SM.OrgGroupBatchId AND BM.Id = SM.BatchId 
			LEFT JOIN sHostel.tAssignRoom AR ON AR.SourceId = SM.StudentId AND AR.SourceTypeId = 2003 AND AR.OrgGroupBatchId = HA.OrgGroupBatchId
			LEFT JOIN sHostel.tRoomMaster RM ON RM.HostelId = HA.Hosteld AND RM.Id = AR.RoomId
			
		WHERE 
	
			(HA.Hosteld=@rHostelId)
			AND ((OM.Id=@rOrgId) OR (@rOrgId=0))
			AND ((SM.Code LIKE @rCode) OR (ISNULL(@rCode,'')='')) 
			AND ((HA.AllocateDate >= @rFromAllocateDate) OR (ISNULL(@rFromAllocateDate,'') = ''))
			AND ((HA.AllocateDate <= @rToAllocateDate) OR (ISNULL(@rToAllocateDate,'') = ''))
			AND ((HA.VacantDate >= @rFromVacantDate) OR (ISNULL(@rFromVacantDate,'') = ''))
			AND ((HA.VacantDate <= @rToVacantDate) OR (ISNULL(@rToVacantDate,'') = ''))
			AND ((SM.SUId=@rSUID) OR (ISNULL(@rSUID,'') = ''))
			AND HA.OrgGroupBatchId=@rOrgGroupBatchId
			AND BM.IsInContext = 1
			--order by  HA.AllocateDate desc	
	END

ELSE
	BEGIN
		SELECT
			OM.DisplayTitle AS OrganizationName,
			'Employee' AS SourceType,
			Emp.Code AS SourceCode, 
			'' AS SUId,
			Emp.EmployeeName AS SourceName,
			Emp.DepartmentName AS ClassDepartmentName,
			HA.SourceTypeId,
			HA.SourceId,
			HA.AllocateDate,
			(CASE WHEN HA.VacantDate = '1900-01-01' THEN ''
			ELSE HA.VacantDate END) AS VacantDate,	
			ISNULL(HA.VacantReason,'') AS VacantReason,
			ISNULL(RM.Title, '') AS RoomName

		FROM 
			sHostel.tHostelAdmission HA 
			INNER JOIN sHrm.vExtEmployeeMaster Emp ON Emp.Id = HA.SourceId
			INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = Emp.OrgId
			LEFT JOIN sHostel.tAssignRoom AR ON AR.SourceId = Emp.EmployeeId AND AR.SourceTypeId = 95 AND AR.OrgGroupBatchId = HA.OrgGroupBatchId 
			LEFT JOIN sHostel.tRoomMaster RM ON RM.HostelId = HA.Hosteld AND RM.Id = AR.RoomId	 

		WHERE 
			(HA.Hosteld=@rHostelId)
			AND ((OM.Id=@rOrgId) OR (@rOrgId=0))
			AND ((Emp.Code LIKE @rCode) OR (ISNULL(@rCode,'')=''))
			AND ((HA.AllocateDate >= @rFromAllocateDate) OR (ISNULL(@rFromAllocateDate,'') = ''))
			AND ((HA.AllocateDate <= @rToAllocateDate) OR (ISNULL(@rToAllocateDate,'') = ''))
			AND ((HA.VacantDate >= @rFromVacantDate) OR (ISNULL(@rFromVacantDate,'') = ''))
			AND ((HA.VacantDate <= @rToVacantDate) OR (ISNULL(@rToVacantDate,'') = ''))
			AND HA.OrgGroupBatchId=@rOrgGroupBatchId

			UNION ALL


			SELECT
			OM.DisplayTitle AS OrganizationName,
			'Student' AS SourceType,
			SM.Code AS SourceCode, 
			SM.SUId,
			SM.LastName + ' ' + SM.FirstName  AS SourceName,
			sStudent.fStudentCurrentDivisionGet(SM.StudentId) AS ClassDepartmentName,
			HA.SourceTypeId,
			HA.SourceId,
			HA.AllocateDate,
			(CASE WHEN HA.VacantDate = '1900-01-01' THEN ''
			ELSE HA.VacantDate END) AS VacantDate,	
			ISNULL(HA.VacantReason,'') AS VacantReason,
			ISNULL(RM.Title, '') AS RoomName

		FROM 
			sHostel.tHostelAdmission HA
			INNER JOIN sStudent.vStudentAcademic SM ON SM.StudentId = HA.SourceId AND SM.OrgGroupBatchId=@rOrgGroupBatchId
			INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = SM.OrgId
			INNER JOIN sSetup.tBatchMaster BM ON BM.OrgGroupBatchId = SM.OrgGroupBatchId AND BM.Id = SM.BatchId 
			LEFT JOIN sHostel.tAssignRoom AR ON AR.SourceId = SM.StudentId AND AR.SourceTypeId = 2003 AND AR.OrgGroupBatchId = HA.OrgGroupBatchId
			LEFT JOIN sHostel.tRoomMaster RM ON RM.HostelId = HA.Hosteld AND RM.Id = AR.RoomId
			
		WHERE 
	
			(HA.Hosteld=@rHostelId)
			AND ((OM.Id=@rOrgId) OR (@rOrgId=0))
			AND ((SM.Code LIKE @rCode) OR (ISNULL(@rCode,'')='')) 
			AND ((HA.AllocateDate >= @rFromAllocateDate) OR (ISNULL(@rFromAllocateDate,'') = ''))
			AND ((HA.AllocateDate <= @rToAllocateDate) OR (ISNULL(@rToAllocateDate,'') = ''))
			AND ((HA.VacantDate >= @rFromVacantDate) OR (ISNULL(@rFromVacantDate,'') = ''))
			AND ((HA.VacantDate <= @rToVacantDate) OR (ISNULL(@rToVacantDate,'') = ''))
			AND ((SM.SUId=@rSUID) OR (ISNULL(@rSUID,'') = ''))
			AND HA.OrgGroupBatchId=@rOrgGroupBatchId
			AND BM.IsInContext = 1
			--order by  HA.AllocateDate desc
	END
END
GO
PRINT N'Altering [sHostel].[pHostelStudentGatePassNewGet]...';


GO
ALTER PROCEDURE [sHostel].[pHostelStudentGatePassNewGet]
          
           @rId int,
           @rOrgId int=0,
           @rHostelGatePassReasonId int=0,
           @rFromGatePassDate datetime='',
           @rToGatePassDate datetime='',
           @rEntryModeStatusId smallint=0,
           @rCode nvarchar(20)='',
           @rIsPrintView bit=0,
           @rOrgGroupId int=0,
           @rOrgGroupBatchId int=0,
           @rRoomId int =0,
           @rHostelId int =0
AS
SET NOCOUNT ON
DECLARE @lIsOnPrePrinted bit = 0
DECLARE @lConfigValue nvarchar(50)
BEGIN

--Select @rHostelId
	 SELECT @lConfigValue = ItemValue FROM sConfig.tConfigurationSetup WHERE ConfigurationId = 232 AND SourceTypeId = 2010 AND SourceId = @rOrgGroupId
		IF (@lConfigValue IS NULL) OR (@lConfigValue = 'Default')
		BEGIN
		SET @lIsOnPrePrinted = 0
		END
		ELSE IF (@lConfigValue = 'Blank')
		BEGIN
		SET @lIsOnPrePrinted = 1
	END
	

			SELECT
				HSGP.Id, 
				HSGP.Id AS HostelStudentGatePassId,
				vSA.Code,
				--vSA.FirstName+' '+vSA.LastName AS Name,
				VSA.Name,
				VSA.ClassName+' - '+VSA.DivisionName AS Class,
				HSGP.StudentId,
				HSGP.RelationshipId,		
				RM.Title AS Relationship,
				HSGP.Relation,
				HSGP.VisitorName,
				HSGP.VisitorMobile,
				HSGP.HostelGatePassReasonId,
				HGRM.Title AS HostelGetPassReason,
				HSGP.Remark,
				--HSGP.VisitorPhoto	,


				--CASE  WHEN @rIsPrintView=1 THEN HSGP.VisitorPhoto		
				--ELSE ISNULL(HSGP.VisitorPhoto, CONVERT(VARBINARY(MAX), 0)) END AS VisitorPhoto,
				--ISNULL(SGP.VisitorPhoto, CONVERT(VARBINARY(MAX), 0)) AS VisitorPhoto,
				HSGP.GatePassDate,
				HSGP.GatePassInDate,
				(CASE  WHEN @rIsPrintView=1 THEN 
				ISNULL(HSGP.VisitorPhoto,0x)
				ELSE ISNULL(HSGP.VisitorPhoto, CONVERT(VARBINARY(MAX), 0x)) END) AS VisitorPhoto,
				CONVERT(varchar(15),CAST(HSGP.GatePassTime AS TIME),100) As GatePassTime,
				ISNULL(CONVERT(varchar(15),CAST(HSGP.GatePassInTime AS TIME),100), '12:00AM') As GatePassInTime,

				--ISNULL(HSGP.GatePassTime,'') As GatePassTime,
				HSGP.EntryModeStatusId,
				HSGP.HostelId,
				SM.Title AS EntryModeStatus,
				OM.Id AS OrgId,
				OM.DisplayTitle AS Organization,
				(CASE  WHEN @rIsPrintView=1 THEN 
				ISNULL(SI.StudentPhoto,0x)		
				ELSE ISNULL(SI.StudentPhoto, CONVERT(VARBINARY(MAX), 0x)) END) AS StudentPhoto,
				--ISNULL(SI.StudentPhoto, CONVERT(VARBINARY(MAX), 0)) AS StudentPhoto,
				ISNULL(U.DisplayTitle,'') AS UserName,
				HSGP.OrgGroupBatchId,
				vSA.SUId,
				HM.Title As HostelName,
				HM.ReportOrgTitle AS ReportOrgTitle,
				HM.SmallAddress1 AS SmallAddress1,
				HM.SmallAddress2 AS SmallAddress2,
				@lIsOnPrePrinted AS IsOnPrePrinted,			
				HRM.Id AS RoomId,
				HRM.Title AS RoomTitle

		FROM 
				sStudent.vStudentAcademic vSA
				INNER JOIN sHostel.tHostelStudentGatePass HSGP ON HSGP.StudentId = vSA.StudentId
				LEFT JOIN sSetup.tOrganizationMaster OM ON OM.Id=vSA.OrgId
				LEFT JOIN sHostel.tHostelGatePassReasonMaster HGRM ON HGRM.Id=HSGP.HostelGatePassReasonId
				LEFT JOIN sCommon.tRelationshipMaster RM ON RM.Id=HSGP.RelationshipId
				LEFT JOIN sCommon.tStatusMaster SM ON SM.Id=HSGP.EntryModeStatusId
				LEFT JOIN sStudent.tStudentImages SI ON SI.StudentId=vSA.StudentId
				LEFT JOIN sSecurity.tUser U	ON U.Id=HSGP.CreatedUserId
				LEFT JOIN  sHostel.tAssignRoom AR ON AR.SourceId=HSGP.StudentId AND ((AR.OrgGroupBatchId=@rOrgGroupBatchId) OR (@rOrgGroupBatchId=0))AND (AR.SourceId=HSGP.StudentId)
				LEFT JOIN sHostel.tRoomMaster HRM ON HRM.Id = AR.RoomId
				INNER JOIN sHostel.tHostelMaster HM ON HM.Id = HSGP.HostelId
			
		WHERE 
				OM.OrgGroupId=@rOrgGroupId
				AND HSGP.HostelId = @rHostelId
				AND (vSA.OrgGroupBatchId=@rOrgGroupBatchId) 
				--if student remove from Assign room then it is not display in HostelStudentGatePass
				--AND (AR.SourceId=HSGP.StudentId) 
				AND (HSGP.OrgGroupBatchId=@rOrgGroupBatchId) 
				AND ((HSGP.Id=@rId) OR (@rId=0))
				AND ((vSA.OrgId=@rOrgId) OR (@rOrgId=0))
				AND ((HSGP.GatePassDate>=@rFromGatePassDate) OR (ISNULL(@rFromGatePassDate,'')=''))
				AND ((HSGP.GatePassDate<=@rToGatePassDate) OR (ISNULL(@rToGatePassDate,'')=''))
				AND ((HSGP.EntryModeStatusId=@rEntryModeStatusId) OR (@rEntryModeStatusId=0))
				AND ((vSA.Code=@rCode) OR (ISNULL(@rCode,'')=''))
				AND ((HSGP.HostelGatePassReasonId=@rHostelGatePassReasonId) OR (@rHostelGatePassReasonId=0))
				AND ((HRM.Id = @rRoomId) OR (@rRoomId = 0))
				
				ORDER BY HSGP.GatePassDate,HSGP.GatePassTime DESC
END
GO
PRINT N'Altering [sHrm].[pLeaveHierarchyLevelUpdate]...';


GO

ALTER PROCEDURE [sHrm].[pLeaveHierarchyLevelUpdate]
        @rLeaveHierarchyLevelId bigint,   
		@rStatusId int,
		@rHierarchyLevelCount int,
        @rLeaveId bigint,
        @rRemark nvarchar(500),
        @rEmployeeId bigint,
		@rOrgId int
       
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
	
	BEGIN TRANSACTION

	BEGIN
	DECLARE @lActionByEmployeeId bigint
	SELECT @lActionByEmployeeId = EmployeeManagerId from [sHrm].[tEmployeeJobDetail] WHere EmployeeId = @rEmployeeId AND OrgId = @rOrgId

	UPDATE [sHrm].[tLeaveHierarchyLevel] SET StatusId = @rStatusId , Remark = @rRemark WHERE AutoId = @rLeaveHierarchyLevelId

	DECLARE @lLeaveCount int
	SELECT @lLeaveCount = COUNT(AutoId) FROM [sHrm].[tLeaveHierarchyLevel] WHERE LeaveId = @rLeaveId

	IF(@rStatusId = 550)
	BEGIN
		IF(@rHierarchyLevelCount <> @lLeaveCount)
		BEGIN
			 --IF (@rStatusId = 550)
			 --BEGIN
				INSERT INTO [sHrm].[tLeaveHierarchyLevel]
					   (LeaveId
					   ,StatusId
					   ,Remark
					   ,ActionByEmployeeId
					   ,ActionDatetime)
				 VALUES
					   (@rLeaveId
					   ,549
					   ,''
					   ,@lActionByEmployeeId
					   ,sCommon.fGetDate())

						SELECT 0 AS Result 
			 --END
		END
		ELSE
		BEGIN
			 UPDATE [sHrm].[tLeave] SET StatusId = @rStatusId Where Id = @rLeaveId
			 
			 SELECT @rStatusId AS Result 
			
		END
	END
	ELSE
	BEGIN
		UPDATE [sHrm].[tLeave] SET StatusId = @rStatusId Where Id = @rLeaveId
			--IF(@rHierarchyLevelCount = @lLeaveCount)
			--BEGIN
				DELETE FROM [sHrm].[tLeaveHierarchyLevel] Where LeaveId = @rLeaveId AND ActionByEmployeeId = @lActionByEmployeeId
			--END
		SELECT @rStatusId AS Result 
	END
	
	END 	   
	COMMIT TRANSACTION
	END TRY	
	BEGIN CATCH
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
END
GO
PRINT N'Altering [sMis].[pRptClassCapacityGet]...';


GO
ALTER PROCEDURE [sMis].[pRptClassCapacityGet]
	
		@rBuilder1 nvarchar(max)='',
		@rBuilder2 nvarchar(max)='', 
		--@rBatchId int,
		@rClassId int,
		@rDepartmentId int,
		@rOrgMainGroupId int,
		@rOrgSubGroupId int,
		@rOrgId smallint,
		@rStrOrgMainGroupId nvarchar(max),
		@rStrOrgSubGroupId nvarchar(max),
		@rStrOrgId nvarchar(max),
		@rStrDepartmentId nvarchar(max),
		@rStrClassId nvarchar(max),
		@rOrgGroupId int,
		@rOrgGroupBatchId int,
		@rIsTestMainGroup bit =0,
		@rIsTestSubGroup bit =0,
		@rIsTestOrg bit=0,
		@rIsTestClass bit=0,
		@rIsTestDepartment bit=0,
		@rIsDisplayOrgGroup bit=0,
		@rBatchGroupId int=0,
		@rStatusId int=0,
		@rStudentStatusId int=0,
		@rUptoDate datetime = ''
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN 
	SELECT 
			@rBuilder1 AS Builder1,
			@rBuilder2 AS Builder2,
			@rIsTestMainGroup AS IsTestMainGroup,
			@rIsTestSubGroup AS IsTestSubGroup,
			@rIsTestOrg AS IsTestOrg,
			@rIsTestClass AS IsTestClass,
			@rIsTestDepartment AS IsTestDepartment,
			@rIsDisplayOrgGroup AS IsDisplayOrgGroup,
			CM.Id AS ClassId,
			CM.DisplayTitle AS ClassName,
			CM.OrderIndex,
			CM.DepartmentId,
			DM.DisplayTitle AS DepartmentName,
			OMGM.Id AS OrgMainGroupId,
			OMGM.Title AS OrgMainGroupTitle,
			OSGM.Id AS OrgSubGroupId,
			OSGM.Title AS OrgSubGroupTitle,
			OM.Id AS OrgId,
			OM.DisplayTitle AS OrgDisplayTitle,
			(SELECT COUNT(1) FROM sSetup.tDivisionMaster DM WHERE DM.ClassId=CM.Id) AS DivisionCount,
			ISNULL(CM.Capacity,0) AS Capacity,
			
			(SELECT ISNULL(count(1),0)  FROM sStudent.tAcademic A 
			INNER JOIN sStudent.tStudentMaster SM ON A.StudentId = SM.Id 
			INNER JOIN sSetup.tBatchMaster BM ON A.BatchId = BM.Id
			INNER JOIN sSetup.tBatchGroupMaster BGM ON BGM.Id = BM.BatchGroupId 
			INNER JOIN sStudent.tStudentAdmission  SA on SA.StudentId = SM.Id 
			WHERE A.ClassId = CM.Id 
			AND SA.AdmissionDate <= @rUptoDate
			AND BM.OrgGroupBatchId = @rOrgGroupBatchId
			AND ((BGM.Id = @rBatchGroupId) OR (@rBatchGroupId = 0)) 
			AND ((SM.StatusId = @rStudentStatusId) OR (@rStudentStatusId=0))) 
			AS CurrentStrength,
			
			(SELECT ISNULL(count(1),0) FROM sStudent.tAcademic A 
			INNER JOIN sStudent.tStudentMaster SM ON A.StudentId = SM.Id 
			INNER JOIN sStudent.tStudentAdmission  SA on SA.StudentId = SM.Id AND SA.leftDate <= @rUptoDate
			INNER JOIN sSetup.tBatchMaster BM ON A.BatchId = BM.Id  
			AND BM.OrgId=CM.OrgId AND BM.OrgGroupBatchId=@rOrgGroupBatchId
			WHERE  A.ClassId = CM.Id)
			AS AdmissionLeft,
			
			(SELECT ISNULL(count(1),0) FROM sStudent.tAcademic A 
			INNER JOIN sStudent.tStudentMaster SM ON A.StudentId = SM.Id 
			INNER JOIN sStudent.tStudentAdmission  SA on SA.StudentId = SM.Id AND SA.AdmissionDate = @rUptoDate
			INNER JOIN sSetup.tBatchMaster BM ON A.BatchId = BM.Id  
			AND BM.OrgId=CM.OrgId AND BM.OrgGroupBatchId=@rOrgGroupBatchId AND SM.StatusId = 1
			WHERE  A.ClassId = CM.Id) 
			AS AdmissionGiven,

			(SELECT ISNULL(count(1),0) FROM sStudent.tAcademic A 
			INNER JOIN sStudent.tStudentMaster SM ON A.StudentId = SM.Id 
			INNER JOIN sSetup.tBatchMaster BM ON A.BatchId = BM.Id 
			AND BM.OrderIndex = (SELECT TOP 1 BM.OrderIndex -1 FROM sSetup.tBatchMaster BM	INNER JOIN  sSetup.tOrgGroupBatchMaster ORGM ON BM.OrgGroupBatchId=ORGM.Id
			WHERE BM.OrgId=CM.OrgId 
			AND BM.OrgGroupBatchId=@rOrgGroupBatchId ORDER BY BM.OrderIndex DESC
			) WHERE A.ClassId = CM.Id AND SM.StatusId = 1) 
			AS PreviousStrength
	FROM 
			sSetup.tClassMaster CM
			INNER JOIN sSetup.tDepartmentMaster DM ON DM.Id=CM.DepartmentId
			INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = DM.OrgId
			INNER JOIN sSetup.tOrgSubGroupMaster OSGM ON OSGM.Id = OM.OrgSubGroupId
			INNER JOIN sSetup.tOrgMainGroupMaster OMGM ON OMGM.Id = OSGM.OrgMainGroupId
	WHERE 
			((CM.Id=@rClassId) OR (@rClassId=0))
			AND	((CM.DepartmentId = @rDepartmentId) OR (@rDepartmentId = 0))
			AND ((OMGM.Id=@rOrgMainGroupId) OR (@rOrgMainGroupId=0))
			AND ((OSGM.Id=@rOrgSubGroupId) OR (@rOrgSubGroupId=0))
			AND ((OM.Id=@rOrgId) OR (@rOrgId=0))
			AND ((OM.OrgGroupId = @rOrgGroupId))
			AND (OMGM.Id IN (SELECT cast(item as int) FROM  sCommon.fnsplit(	@rStrOrgMainGroupId,','))OR @rStrOrgMainGroupId='')
			AND (OSGM.Id IN (SELECT cast(item as int) FROM  sCommon.fnsplit(	@rStrOrgSubGroupId,','))OR @rStrOrgSubGroupId='')
			AND (OM.Id IN (SELECT cast (item as int) FROM  sCommon.fnsplit ( @rStrOrgId,','))OR @rStrOrgId='')
			AND (DM.Id IN (SELECT cast(item as int) FROM  sCommon.fnsplit  (@rStrDepartmentId,','))OR @rStrDepartmentId='')
			AND (CM.Id IN (SELECT cast(item as int) FROM  sCommon.fnsplit  (@rStrClassId,','))OR @rStrClassId='')
			ORDER BY CM.OrderIndex ASC
END
GO
PRINT N'Altering [sProcurement].[pStockIssueRequestDetailGet]...';


GO
ALTER PROCEDURE [sProcurement].[pStockIssueRequestDetailGet]
	@rOrgGroupId int,
	@rStockIssueRequestId [int] = 0
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
	 SELECT 
			ISNULL(SIRD.Id,0) As ID,
			ISNULL(SIRD.StockIssueRequestId,0) AS StockIssueRequestId ,
			SIRD.StockItemId,
			ISNULL(SIRD.RequestedQuantity,0) As RequestedQuantity,
			SIRD.Remark,
			SIRD.UpdatedUserID,
			SIRD.UpdatedDateTime,
			SIM.Code ItemCode,
			SIM.Title ItemTitle,
			SU.Code UomCode,
			SU.Title UomTitle,
			ISNULL(SU.Id,0)AS  IssueStockUomId,
			SIM.IsBatchWiseTracking,
			SIM.Code StockItemCode,
			SIM.Title StockItemName,
			SU.Title Uom,
		    '' as Number,
			SIRD.IssuedQuantity,
			SIR.Number,
			SIR.CreatedDateTime as 'ReqDate',
			(
				Select sum( AvailableQuantity)
				FROM sProcurement.tStockBatch SB
				INNER JOIN sIP.tStockItemMaster SIM ON SIM.Id = SB.StockItemID
				INNER JOIN sProcurement.tStockIssueRequestDetail SIRD1 ON SIRD1.StockItemId = SIM.Id
				WHERE ((SIRD1.StockIssueRequestId = @rStockIssueRequestId) OR (@rStockIssueRequestId = 0))
				AND SIRD1.OrgGroupId = @rOrgGroupId  AND SB.StockItemId = SIRD.StockItemId
			) AS AvailableQuantity,
			EM.Code AS ReqEmployeeCode
 
	   FROM 
			sProcurement.tStockIssueRequestDetail SIRD
	   INNER JOIN  sIP.tStockItemMaster SIM
		ON SIM.Id = SIRD.StockItemId
		INNER JOIN sFinanceCommon.tStockUomMaster  SU
		ON SU.Id = SIM.IssueStockUomId
		INNER JOIN sProcurement.tStockIssueRequest SIR ON SIR.ID=SIRD.StockIssueRequestId
		Left Join sCommon.vEmployeeMaster EM ON SIR.CreatedUserId=EM.UserId
		--LEFT JOIN sProcurement.tStockBatch SB ON SB.StockItemID = SIM.Id AND SB.Number!=''
	  WHERE ((SIRD.StockIssueRequestId = @rStockIssueRequestId) OR (@rStockIssueRequestId = 0))
	  AND SIRD.OrgGroupId = @rOrgGroupId
END
GO
PRINT N'Altering [sProcurement].[pStockTransferQueueListing]...';


GO
/******************************************************************************
	Purpose          : To get Pending Records for Transfer/Issue Requests.
	Created date     : 13 May 2012
	Author           : Rajendra Patel
	Copyright©       : Copyright © Sapphire Software
	Last Change Desc : ST.Number is addedremoved
	Test script      : EXECUTE [sProcurement].[pStockTransferQueueListing] @rStatus = 'All', @rFromDate = '5/17/2009', @rToDate = '5/17/2010', @rType = 'All'
*******************************************************************************/
ALTER PROCEDURE [sProcurement].[pStockTransferQueueListing]
	(
	    @rOrgGroupId int,
		@rStatus Nvarchar(30) = '', 
		@rFromDate Nvarchar(100) = '1/1/2009', 
		@rToDate Nvarchar(100) = '1/1/2009', 
		@rType Nvarchar(30) = '',
		@rStoreLocationId int,
		@rToDepartmentId int=0
	)
AS

SET NOCOUNT ON
BEGIN
IF @rStatus = 'Pending'
BEGIN
	IF @rType = 'Tran'
		BEGIN
		   SELECT 
			   SR.Number AS [RequestNo],  
			   SR.CreatedDateTime AS [RequestDate],  
			   SR.RequestedByName AS [RequestedBy], 
			   'Tran'AS [Type], 
			   LM.Title AS [Location],
			   0 AS DepartmentId,
			   ST.Number AS [TransferNo],
			   'Pending' AS [Status], 
			   SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.TransferredQuantity, 0)) AS [PendingQty],
			   SR.Id [ReqId]
			   --ST.Id [TransferId]		   
		   FROM  
				sProcurement.tStockTransferRequest SR
					INNER JOIN sProcurement.tStockTransferRequestDetail SRD
					ON SR.Id = SRD.StockTransferRequestId
					LEFT OUTER JOIN sProcurement.tStockTransfer ST
					ON ST.StockTransferRequestId = SR.id
					LEFT JOIN sProcurement.tStockTransferDetail STD
					ON ST.Id = STD.StockTransferId
					INNER JOIN sIp.tStoreLocationMaster LM 
					ON SR.ToStoreLocationId = LM.Id
					WHERE 
					--SR.OrgGroupId = @rOrgGroupId AND 
					SR.FromStoreLocationId =  @rStoreLocationId
					
					GROUP BY 
						SR.Number, SR.CreatedDateTime, SR.RequestedByName, LM.Title, ST.Number, SR.Id, ST.Id 
					HAVING 
						SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.TransferredQuantity, 0)) > 0
						AND SR.CreatedDateTime BETWEEN @rFromDate AND @rToDate
		END
	IF @rType = 'Issue'
		BEGIN
		   SELECT 
			   SR.Number AS [RequestNo],  
			   SR.CreatedDateTime AS [RequestDate],  
			   SR.RequestedByName AS [RequestedBy], 
			   'Issue'AS [Type], 
			   ISNULL(LM.Title,'') AS [Location], 
			   ISNULL( LM.Id,0) AS DepartmentId,
			   ST.Number AS [TransferNo],
			   'Pending' AS [Status], 
			   SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.IssuedQuantity, 0)) AS PendingQty, 
			   SR.Id [ReqId]
			   --ST.Id [TransferId]
		   FROM  
				sProcurement.tStockIssueRequest SR
					INNER JOIN sProcurement.tStockIssueRequestDetail SRD
					ON SR.Id = SRD.StockIssueRequestId
					LEFT OUTER JOIN sProcurement.tStockIssue ST
					ON ST.StockIssueRequestId = SR.id
					LEFT JOIN sProcurement.tStockIssueDetail STD
					ON ST.Id = STD.StockIssueId
					LEFT JOIN sSetup.tDepartmentMaster LM 
					ON SR.ToDepartmentId = LM.Id
					WHERE
					 --SR.OrgGroupId = @rOrgGroupId AND 
					 SR.FromStoreLocationId =  @rStoreLocationId
					AND (SR.ToDepartmentId= @rToDepartmentId OR @rToDepartmentId =0 )
					GROUP BY 
						SR.Number, SR.CreatedDateTime, SR.RequestedByName, LM.Title,LM.Id, ST.Number, SR.Id, ST.Id 
					HAVING 
						SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.IssuedQuantity, 0)) > 0
						AND SR.CreatedDateTime BETWEEN @rFromDate AND @rToDate
		END
	IF @rType = 'All'
		BEGIN
		SELECT 
			   SR.Number AS [RequestNo],  
			   SR.CreatedDateTime AS [RequestDate],  
			   SR.RequestedByName AS [RequestedBy], 
			   'Tran'AS [Type], 
			   LM.Title AS [Location], 
			   0 AS DepartmentId,
			   ST.Number AS [TransferNo],
			   'Pending' AS [Status],
			   SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.TransferredQuantity, 0)) AS PendingQty,
			   SR.Id [ReqId]
			   --ST.Id [TransferId]
			   FROM  
					sProcurement.tStockTransferRequest SR
						INNER JOIN sProcurement.tStockTransferRequestDetail SRD
						ON SR.Id = SRD.StockTransferRequestId
						LEFT OUTER JOIN sProcurement.tStockTransfer ST
						ON ST.StockTransferRequestId = SR.id
						LEFT JOIN sProcurement.tStockTransferDetail STD
						ON ST.Id = STD.StockTransferId
						INNER JOIN sIp.tStoreLocationMaster LM 
						ON SR.ToStoreLocationId = LM.Id
						WHERE 
						--SR.OrgGroupId = @rOrgGroupId AND
						 SR.FromStoreLocationId =  @rStoreLocationId 
						GROUP BY 
							SR.Number, SR.CreatedDateTime, SR.RequestedByName, LM.Title, ST.Number, SR.Id, ST.Id
						HAVING 
							SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.TransferredQuantity, 0)) > 0
							AND SR.CreatedDateTime BETWEEN @rFromDate AND @rToDate
			UNION ALL
			   SELECT 
			   SR.Number AS [RequestNo],  
			   SR.CreatedDateTime AS [RequestDate],  
			   SR.RequestedByName AS [RequestedBy], 
			   'Issue'AS [Type], 
			   ISNULL(LM.Title,'') AS [Location], 
			   ISNULL( LM.Id,0) AS DepartmentId,
			   ST.Number AS [TransferNo],
			   'Pending' AS [Status], 
			   SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.IssuedQuantity, 0)) AS PendingQty,
			   SR.Id [ReqId]
			  -- ST.Id [TransferId]
		   FROM  
				sProcurement.tStockIssueRequest SR
					INNER JOIN sProcurement.tStockIssueRequestDetail SRD
					ON SR.Id = SRD.StockIssueRequestId
					LEFT JOIN sProcurement.tStockIssue ST
					ON ST.StockIssueRequestId = SR.id
					LEFT JOIN sProcurement.tStockIssueDetail STD
					ON ST.Id = STD.StockIssueId
					LEFT JOIN sSetup.tDepartmentMaster LM 
					ON SR.ToDepartmentId = LM.Id
					WHERE 
					--SR.OrgGroupId = @rOrgGroupId AND
					 SR.FromStoreLocationId =  @rStoreLocationId
						 AND (SR.ToDepartmentId= @rToDepartmentId OR @rToDepartmentId =0 )
					GROUP BY 
						SR.Number, SR.CreatedDateTime, SR.RequestedByName, LM.Title,LM.Id, ST.Number, SR.Id, ST.Id
					HAVING 
						SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.IssuedQuantity, 0)) > 0
						AND SR.CreatedDateTime BETWEEN @rFromDate AND @rToDate
						Order by SR.Number
		END			
	END
	
	
IF @rStatus = 'Completed'
BEGIN
	IF @rType = 'Tran'
		BEGIN
		   SELECT 
			   SR.Number AS [RequestNo],  
			   SR.CreatedDateTime AS [RequestDate],  
			   SR.RequestedByName AS [RequestedBy], 
			   'Tran'AS [Type], 
			   LM.Title AS [Location],
			   0 AS DepartmentId,
			   ST.Number AS [TransferNo],
			   'Completed' AS [Status], 
			   SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.TransferredQuantity, 0)) AS [PendingQty], 
			   SR.Id [ReqId]
			   --ST.Id [TransferId]		   
		   FROM
				sProcurement.tStockTransferRequest SR
					INNER JOIN sProcurement.tStockTransferRequestDetail SRD
					ON SR.Id = SRD.StockTransferRequestId
					INNER JOIN sProcurement.tStockTransfer ST
					ON ST.StockTransferRequestId = SR.id
					INNER JOIN sProcurement.tStockTransferDetail STD
					ON ST.Id = STD.StockTransferId
					INNER JOIN sIp.tStoreLocationMaster LM 
					ON SR.ToStoreLocationId = LM.Id
					WHERE 
					--SR.OrgGroupId = @rOrgGroupId AND 
					SR.FromStoreLocationId =  @rStoreLocationId
					GROUP BY 
						SR.Number, SR.CreatedDateTime, SR.RequestedByName, LM.Title, ST.Number, SR.Id, ST.Id 
					HAVING 
						SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.TransferredQuantity, 0)) <= 0
						AND SR.CreatedDateTime BETWEEN @rFromDate AND @rToDate
						Order by  SR.Id,SR.Number
		END
	IF @rType = 'Issue'
		BEGIN
		   SELECT 
			   SR.Number AS [RequestNo],  
			   SR.CreatedDateTime AS [RequestDate],  
			   SR.RequestedByName AS [RequestedBy], 
			   'Issue'AS [Type], 
			   ISNULL(LM.Title,'') AS [Location], 
			   0 AS DepartmentId,
			   ST.Number AS [TransferNo],
			   'Completed' AS [Status], 
			   SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.IssuedQuantity, 0)) AS PendingQty, 
			   SR.Id [ReqId]
			   --ST.Id [TransferId]
		   FROM  
				sProcurement.tStockIssueRequest SR
					INNER JOIN sProcurement.tStockIssueRequestDetail SRD
					ON SR.Id = SRD.StockIssueRequestId
					INNER JOIN sProcurement.tStockIssue ST
					ON ST.StockIssueRequestId = SR.id
					INNER JOIN sProcurement.tStockIssueDetail STD
					ON ST.Id = STD.StockIssueId
					LEFT JOIN sSetup.tDepartmentMaster LM 
					ON SR.ToDepartmentId = LM.Id
					WHERE 
					--SR.OrgGroupId = @rOrgGroupId AND
					 SR.FromStoreLocationId =  @rStoreLocationId 
						 AND (SR.ToDepartmentId= @rToDepartmentId OR @rToDepartmentId =0 )
					GROUP BY 
						SR.Number, SR.CreatedDateTime, SR.RequestedByName, LM.Title, ST.Number, SR.Id, ST.Id 
					HAVING 
						SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.IssuedQuantity, 0)) <= 0
						AND SR.CreatedDateTime BETWEEN @rFromDate AND @rToDate
						Order by  SR.Id,SR.Number
		END
	IF @rType = 'All'
		BEGIN
		SELECT 
			   SR.Number AS [RequestNo],  
			   SR.CreatedDateTime AS [RequestDate],  
			   SR.RequestedByName AS [RequestedBy], 
			   'Tran'AS [Type], 
			   LM.Title AS [Location], 
			   0 AS DepartmentId,
			   ST.Number AS [TransferNo],
			   'Completed' AS [Status],
			   SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.TransferredQuantity, 0)) AS PendingQty,
			   SR.Id [ReqId]
			   --ST.Id [TransferId]
			   FROM  
					sProcurement.tStockTransferRequest SR
						INNER JOIN sProcurement.tStockTransferRequestDetail SRD
						ON SR.Id = SRD.StockTransferRequestId
						INNER JOIN sProcurement.tStockTransfer ST
						ON ST.StockTransferRequestId = SR.id
						INNER JOIN sProcurement.tStockTransferDetail STD
						ON ST.Id = STD.StockTransferId
						INNER JOIN sIp.tStoreLocationMaster LM 
						ON SR.ToStoreLocationId = LM.Id
						WHERE 
						--SR.OrgGroupId = @rOrgGroupId AND 
						SR.FromStoreLocationId =  @rStoreLocationId  
						GROUP BY 
							SR.Number, SR.CreatedDateTime, SR.RequestedByName, LM.Title, ST.Number, SR.Id, ST.Id
						HAVING 
							SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.TransferredQuantity, 0)) <= 0
							AND SR.CreatedDateTime BETWEEN @rFromDate AND @rToDate
			UNION ALL
			   SELECT 
			   SR.Number AS [RequestNo],  
			   SR.CreatedDateTime AS [RequestDate],  
			   SR.RequestedByName AS [RequestedBy], 
			   'Issue'AS [Type], 
			   ISNULL(LM.Title,'') AS [Location], 
			   ISNULL( LM.Id,0) AS DepartmentId,
			   ST.Number AS [TransferNo],
			   'Completed' AS [Status], 
			   SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.IssuedQuantity, 0)) AS PendingQty,
			   SR.Id [ReqId]
			  -- ST.Id [TransferId]
		   FROM  
				sProcurement.tStockIssueRequest SR
					INNER JOIN sProcurement.tStockIssueRequestDetail SRD
					ON SR.Id = SRD.StockIssueRequestId
					INNER JOIN sProcurement.tStockIssue ST
					ON ST.StockIssueRequestId = SR.id
					INNER JOIN sProcurement.tStockIssueDetail STD
					ON ST.Id = STD.StockIssueId
					LEFT JOIN sSetup.tDepartmentMaster LM 					
					ON SR.ToDepartmentId = LM.Id
					WHERE 
					--SR.OrgGroupId = @rOrgGroupId AND 
					SR.FromStoreLocationId =  @rStoreLocationId
					GROUP BY 
						SR.Number, SR.CreatedDateTime, SR.RequestedByName, LM.Title,LM.Id, ST.Number, SR.Id, ST.Id
					HAVING 
						SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.IssuedQuantity, 0)) <= 0
						AND SR.CreatedDateTime BETWEEN @rFromDate AND @rToDate
						Order by  SR.Id,SR.Number
		END			
	END
	
IF @rStatus = 'All'
BEGIN
	IF @rType = 'Tran'
		BEGIN
		   SELECT 
			   SR.Number AS [RequestNo],  
			   SR.CreatedDateTime AS [RequestDate],  
			   SR.RequestedByName AS [RequestedBy], 
			   'Tran'AS [Type], 
			   LM.Title AS [Location],
			   0 AS DepartmentId,
			   ST.Number AS [TransferNo],
			   'Pending' AS [Status], 
			   SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.TransferredQuantity, 0)) AS [PendingQty],
			   SR.Id [ReqId]
			   --ST.Id [TransferId]		   
		   FROM  
				sProcurement.tStockTransferRequest SR
					INNER JOIN sProcurement.tStockTransferRequestDetail SRD
					ON SR.Id = SRD.StockTransferRequestId
					LEFT OUTER JOIN sProcurement.tStockTransfer ST
					ON ST.StockTransferRequestId = SR.id
					LEFT JOIN sProcurement.tStockTransferDetail STD
					ON ST.Id = STD.StockTransferId
					INNER JOIN sIp.tStoreLocationMaster LM 
					ON SR.ToStoreLocationId = LM.Id
						WHERE 
						--SR.OrgGroupId = @rOrgGroupId AND 
						SR.FromStoreLocationId =  @rStoreLocationId 
					GROUP BY 
						SR.Number, SR.CreatedDateTime, SR.RequestedByName, LM.Title, ST.Number, SR.Id, ST.Id 
					HAVING 
						SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.TransferredQuantity, 0)) > 0
						AND SR.CreatedDateTime BETWEEN @rFromDate AND @rToDate		
		UNION ALL
		SELECT 
			   SR.Number AS [RequestNo],  
			   SR.CreatedDateTime AS [RequestDate],  
			   SR.RequestedByName AS [RequestedBy], 
			   'Tran'AS [Type], 
			   LM.Title AS [Location],
			   0 AS DepartmentId,
			   ST.Number AS [TransferNo],
			   'Completed' AS [Status], 
			   SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.TransferredQuantity, 0)) AS [PendingQty], 
			   SR.Id [ReqId]
			   --ST.Id [TransferId]		   
		   FROM
				sProcurement.tStockTransferRequest SR
					INNER JOIN sProcurement.tStockTransferRequestDetail SRD
					ON SR.Id = SRD.StockTransferRequestId
					INNER JOIN sProcurement.tStockTransfer ST
					ON ST.StockTransferRequestId = SR.id
					INNER JOIN sProcurement.tStockTransferDetail STD
					ON ST.Id = STD.StockTransferId
					INNER JOIN sIp.tStoreLocationMaster LM 
					ON SR.ToStoreLocationId = LM.Id
						WHERE 
						--SR.OrgGroupId = @rOrgGroupId AND 
						SR.FromStoreLocationId =  @rStoreLocationId
					GROUP BY 
						SR.Number, SR.CreatedDateTime, SR.RequestedByName, LM.Title, ST.Number, SR.Id, ST.Id 
					HAVING 
						SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.TransferredQuantity, 0)) <= 0
						AND SR.CreatedDateTime BETWEEN @rFromDate AND @rToDate
						Order by  SR.Id,SR.Number
		END
	IF @rType = 'Issue'
		BEGIN
		   SELECT 
			   SR.Number AS [RequestNo],  
			   SR.CreatedDateTime AS [RequestDate],  
			   SR.RequestedByName AS [RequestedBy], 
			   'Issue'AS [Type], 
			   ISNULL(LM.Title,'') AS [Location], 
			   ISNULL( LM.Id,0) AS DepartmentId,
			   ST.Number AS [TransferNo],
			   'Pending' AS [Status], 
			   SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.IssuedQuantity, 0)) AS PendingQty, 
			   SR.Id [ReqId]
			   --ST.Id [TransferId]
		   FROM  
				sProcurement.tStockIssueRequest SR
					INNER JOIN sProcurement.tStockIssueRequestDetail SRD
					ON SR.Id = SRD.StockIssueRequestId
					LEFT OUTER JOIN sProcurement.tStockIssue ST
					ON ST.StockIssueRequestId = SR.id
					LEFT JOIN sProcurement.tStockIssueDetail STD
					ON ST.Id = STD.StockIssueId
					LEFT JOIN sSetup.tDepartmentMaster LM 
					ON SR.ToDepartmentId = LM.Id
						WHERE 
						--SR.OrgGroupId = @rOrgGroupId AND 
						SR.FromStoreLocationId =  @rStoreLocationId 
						 AND (SR.ToDepartmentId= @rToDepartmentId OR @rToDepartmentId =0 )
					GROUP BY 
						SR.Number, SR.CreatedDateTime, SR.RequestedByName, LM.Title,LM.Id, ST.Number, SR.Id, ST.Id 
					HAVING 
						SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.IssuedQuantity, 0)) > 0
						AND SR.CreatedDateTime BETWEEN @rFromDate AND @rToDate
			UNION ALL
			
			SELECT 
			   SR.Number AS [RequestNo],  
			   SR.CreatedDateTime AS [RequestDate],  
			   SR.RequestedByName AS [RequestedBy], 
			   'Issue'AS [Type], 
			   ISNULL(LM.Title,'') AS [Location],
			   ISNULL( LM.Id,0) AS DepartmentId, 
			   ST.Number AS [TransferNo],
			   'Completed' AS [Status], 
			   SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.IssuedQuantity, 0)) AS PendingQty, 
			   SR.Id [ReqId]
			   --ST.Id [TransferId]
		   FROM  
				sProcurement.tStockIssueRequest SR
					INNER JOIN sProcurement.tStockIssueRequestDetail SRD
					ON SR.Id = SRD.StockIssueRequestId
					INNER JOIN sProcurement.tStockIssue ST
					ON ST.StockIssueRequestId = SR.id
					INNER JOIN sProcurement.tStockIssueDetail STD
					ON ST.Id = STD.StockIssueId
					LEFT JOIN sSetup.tDepartmentMaster LM 
					ON SR.ToDepartmentId = LM.Id
						WHERE 
						--SR.OrgGroupId = @rOrgGroupId AND 
						SR.FromStoreLocationId =  @rStoreLocationId
						 AND (SR.ToDepartmentId= @rToDepartmentId OR @rToDepartmentId =0)
					GROUP BY 
						SR.Number, SR.CreatedDateTime, SR.RequestedByName, LM.Title,LM.Id, ST.Number, SR.Id, ST.Id 
					HAVING 
						SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.IssuedQuantity, 0)) <= 0
						AND SR.CreatedDateTime BETWEEN @rFromDate AND @rToDate
						Order by  SR.Id,SR.Number
		END
		IF @rType = 'All'
		BEGIN
		SELECT 
			   SR.Number AS [RequestNo],  
			   SR.CreatedDateTime AS [RequestDate],  
			   SR.RequestedByName AS [RequestedBy], 
			   'Tran'AS [Type], 
			   LM.Title AS [Location],
			   0 AS DepartmentId, 
			   ST.Number AS [TransferNo],
			   'Pending' AS [Status],
			   SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.TransferredQuantity, 0)) AS PendingQty,
			   SR.Id [ReqId]
			   --ST.Id [TransferId]
			   FROM  
					sProcurement.tStockTransferRequest SR
						INNER JOIN sProcurement.tStockTransferRequestDetail SRD
						ON SR.Id = SRD.StockTransferRequestId
						LEFT OUTER JOIN sProcurement.tStockTransfer ST
						ON ST.StockTransferRequestId = SR.id
						LEFT JOIN sProcurement.tStockTransferDetail STD
						ON ST.Id = STD.StockTransferId
						INNER JOIN sIp.tStoreLocationMaster LM 
						ON SR.ToStoreLocationId = LM.Id
							WHERE 
							--SR.OrgGroupId = @rOrgGroupId AND 
							SR.FromStoreLocationId =  @rStoreLocationId
						GROUP BY 
							SR.Number, SR.CreatedDateTime, SR.RequestedByName, LM.Title, ST.Number, SR.Id, ST.Id
						HAVING 
							SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.TransferredQuantity, 0)) > 0
							AND SR.CreatedDateTime BETWEEN @rFromDate AND @rToDate
			UNION ALL
			   SELECT 
			   SR.Number AS [RequestNo],  
			   SR.CreatedDateTime AS [RequestDate],  
			   SR.RequestedByName AS [RequestedBy], 
			   'Issue'AS [Type], 
			   ISNULL(LM.Title,'') AS [Location], 
			   ISNULL( LM.Id,0) AS DepartmentId,
			   ST.Number AS [TransferNo],
			   'Pending' AS [Status], 
			   SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.IssuedQuantity, 0)) AS PendingQty,
			   SR.Id [ReqId]
			  -- ST.Id [TransferId]
		   FROM  
				sProcurement.tStockIssueRequest SR
					INNER JOIN sProcurement.tStockIssueRequestDetail SRD
					ON SR.Id = SRD.StockIssueRequestId
					LEFT JOIN sProcurement.tStockIssue ST
					ON ST.StockIssueRequestId = SR.id
					LEFT JOIN sProcurement.tStockIssueDetail STD
					ON ST.Id = STD.StockIssueId
					LEFT JOIN sSetup.tDepartmentMaster LM 
					ON SR.ToDepartmentId = LM.Id
						WHERE 
						--SR.OrgGroupId = @rOrgGroupId AND 
						SR.FromStoreLocationId =  @rStoreLocationId 
						 AND (SR.ToDepartmentId= @rToDepartmentId OR @rToDepartmentId =0 )
					GROUP BY 
						SR.Number, SR.CreatedDateTime, SR.RequestedByName, LM.Title,LM.Id, ST.Number, SR.Id, ST.Id
					HAVING 
						SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.IssuedQuantity, 0)) > 0
						AND SR.CreatedDateTime BETWEEN @rFromDate AND @rToDate
			UNION ALL
			SELECT 
			   SR.Number AS [RequestNo],  
			   SR.CreatedDateTime AS [RequestDate],  
			   SR.RequestedByName AS [RequestedBy], 
			   'Tran'AS [Type], 
			   LM.Title AS [Location], 
			   0 AS DepartmentId,
			   ST.Number AS [TransferNo],
			   'Completed' AS [Status],
			   SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.TransferredQuantity, 0)) AS PendingQty,
			   SR.Id [ReqId]
			   --ST.Id [TransferId]
			   FROM  
					sProcurement.tStockTransferRequest SR
						INNER JOIN sProcurement.tStockTransferRequestDetail SRD
						ON SR.Id = SRD.StockTransferRequestId
						INNER JOIN sProcurement.tStockTransfer ST
						ON ST.StockTransferRequestId = SR.id
						INNER JOIN sProcurement.tStockTransferDetail STD
						ON ST.Id = STD.StockTransferId
						INNER JOIN sIp.tStoreLocationMaster LM 
						ON SR.ToStoreLocationId = LM.Id
							WHERE 
							--SR.OrgGroupId = @rOrgGroupId AND 
							SR.FromStoreLocationId =  @rStoreLocationId
						GROUP BY 
							SR.Number, SR.CreatedDateTime, SR.RequestedByName, LM.Title, ST.Number, SR.Id, ST.Id
						HAVING 
							SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.TransferredQuantity, 0)) <= 0
							AND SR.CreatedDateTime BETWEEN @rFromDate AND @rToDate
			UNION ALL
			   SELECT 
			   SR.Number AS [RequestNo],  
			   SR.CreatedDateTime AS [RequestDate],  
			   SR.RequestedByName AS [RequestedBy], 
			   'Issue'AS [Type], 
			   ISNULL(LM.Title,'') AS [Location], 
			   ISNULL( LM.Id,0) AS DepartmentId,
			   ST.Number AS [TransferNo],
			   'Completed' AS [Status], 
			   SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.IssuedQuantity, 0)) AS PendingQty,
			   SR.Id [ReqId]
			  -- ST.Id [TransferId]
		   FROM  
				sProcurement.tStockIssueRequest SR
					INNER JOIN sProcurement.tStockIssueRequestDetail SRD
					ON SR.Id = SRD.StockIssueRequestId
					INNER JOIN sProcurement.tStockIssue ST
					ON ST.StockIssueRequestId = SR.id
					INNER JOIN sProcurement.tStockIssueDetail STD
					ON ST.Id = STD.StockIssueId
					LEFT JOIN sSetup.tDepartmentMaster LM 
					ON SR.ToDepartmentId = LM.Id
						WHERE 
						--SR.OrgGroupId = @rOrgGroupId AND 
						SR.FromStoreLocationId =  @rStoreLocationId
						 AND (SR.ToDepartmentId= @rToDepartmentId OR @rToDepartmentId =0 )
					GROUP BY 
						SR.Number, SR.CreatedDateTime, SR.RequestedByName, LM.Title,LM.Id, ST.Number, SR.Id, ST.Id
					HAVING 
						SUM(ISNULL(SRD.RequestedQuantity, 0)) - SUM(ISNULL(STD.IssuedQuantity, 0)) <= 0
						AND SR.CreatedDateTime BETWEEN @rFromDate AND @rToDate
						Order by  SR.Id,SR.Number
		END		
		END
END
GO
PRINT N'Altering [sStudent].[pStudentVerificationGet]...';


GO
ALTER PROCEDURE [sStudent].[pStudentVerificationGet]
	 
	@rClassId int=0,
	@rDivisionId int=0,
	@rRollNo int=0,
	@rOrgId int,
	--@rNumber bigint,
	@rCode nvarchar(20) = '',
	@rStatusId smallint=0,
	@rFirstName nvarchar(100)='',
	@rLastName nvarchar(100)='',
	@rBatchId int = 0,
	@rOrgGroupBatchId int = 0,
	@rDepartmentId int,
	@rOrgGroupId smallint,
	@rToRollNo int=0,
	@rFromCode nvarchar(20)='',
	@rToCode nvarchar(20)='',
	@rSpecificCode nvarchar(max)='',	
	@rOrderByTypeId int  = 0
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON			 

BEGIN 	 

	BEGIN
		SET @rCode= sCommon.fSearchStringGet(@rCode)
		SET @rFirstName = sCommon.fSearchStringGet(@rFirstName)
		SET @rLastName = sCommon.fSearchStringGet(@rLastName)
	END
 
	SELECT 
			SA.AcademicId,
			SA.StudentId,
			SA.ClassId,
			Sa.MiddleName,
			ISNULL(SA.AdmissionDate,'')As AdmissionDate,
			SA.PreviousSchool,
			ISNULL(SA.DateOfBirth,'') As DateOfBirth,
			SA.BirthPlace,
			SA.Gender,
			SA.FamilyCast,
			SA.CastCatagory AS CasteCategory,
			SA.MotherTongue,
			SA.Nationality,
			Sa.FatherName,
			SA.FatherContactNo,
			SA.Mothername,
			SA.MotherContactNo,
			SA.PresentStreetOne,
			SA.PresentStreetTwo,
			SA.PresentStreetThree,
			ISNULL(SA.StudentContactNo,'')As StudentContactNo,
			SA.UIDNumber,
			SA.AdhaarNumber,
			SA.BankBranch,
			SA.BankAccountNumber,
			SA.BankName,
			SA.BankIFSCCode,
			SA.DepartmentId,
			SA.FirstName,
			SA.LastName,
			SA.Name,
			SA.Code,
			SA.ADMISSIONStandardId, 
			SA.DivisionId,
			SA.DivisionName,
			ISNULL(SA.Number,0) AS Number,
			SA.RollNo,
			SA.StatusId,
			--SA.StatusName,
			SA.OrgId,
			--SA.Age,
			ISNULL(SA.AdmissionCategoryId,0) AS AdmissionCategoryId,
			SA.Religion,
			SA.StudentEmail,
			SA.Name,
			(select  Title  from sSetup.tClassMaster where OrgId=@rOrgId AND id=SA.ADMISSIONStandardId  ) As AdmissionStandard,
			SA.ClassName,
			SA.BirthState,
			SA.BirthCountry,
			SA.BirthTaluka,
			SA.BirthDistrict,
			SA.BankAccountHolderName
  FROM 
            sStudent.vStudentVerification SA  
  WHERE 
			((SA.ClassId=@rClassId)OR(@rClassId=0))
			AND((SA.DivisionId=@rDivisionId)OR(@rDivisionId=0))
			AND((SA.Code like @rCode) OR (ISNULL(@rCode,'') = '')) 
			AND((SA.Code >= @rFromCode) AND (SA.Code <= @rToCode) OR (ISNULL(@rFromCode,'') = '' OR ISNULL(@rToCode,'') = '')) 
			AND(SA.Code IN (SELECT CONVERT(nvarchar,item) from sCommon.fnSplit(@rSpecificCode,',')) OR ISNULL(@rSpecificCode,'') = '') 
			AND((SA.FirstName like @rFirstName) OR (ISNULL(@rFirstName,'') = '')) 
			AND((SA.LastName like @rLastName) OR (ISNULL(@rLastName,'') = '')) 
			AND((SA.StatusId=@rStatusId)OR(@rStatusId=0))	
			AND(SA.OrgId=@rOrgId OR @rOrgId = 0)
			AND (OrgGroupBatchId = @rOrgGroupBatchId	or @rOrgGroupBatchId = 0)	
			AND(SA.BatchId=@rBatchId or @rBatchId = 0)
			AND(SA.DepartmentId=@rDepartmentId or @rDepartmentId = 0)
			and ((sa.RollNO>=@rRollNO)OR(@rRollNo=0))
			and ((sa.RollNO<=@rToRollNo)OR(@rToRollNo=0))
			--AND((SA.Number=@rNumber)OR(@rNumber=0))	

--ORDER BY SA.ClassId,SA.DivisionId,SA.RollNo

ORDER by CASE WHEN @rOrderByTypeId = 1 then SA.FirstName 
			  WHEN @rOrderByTypeId = 2 then SA.LastName END, 
		CASE 
			  WHEN @rOrderByTypeId = 3 then SA.RollNo
			  WHEN @rOrderByTypeId = 4 then (CASE WHEN ISNUMERIC(SA.Code) = 1 THEN CAST(SA.Code AS bigINT)  ELSE 99999999999999999999  END) END,
		CASE WHEN @rOrderByTypeId = 5 then SA.AdmissionDate END  
			  ,SA.ClassId, SA.DivisionId,SA.RollNo
 
END
GO
PRINT N'Creating [sAssessment].[pExamSettingAdd]...';


GO

CREATE PROCEDURE [sAssessment].[pExamSettingAdd]
	(
		--@rExamSettingXml nvarchar(MAX),
		--@rOrgId int

		@rExamSettingXml nvarchar(max),
		@rExamSettingTypeId int,
		@rCreatedUserId bigint
	)
AS

SET NOCOUNT ON

BEGIN
SET @rExamSettingXml=REPLACE(@rExamSettingXml,'&','AND')
DECLARE @lInterrorCode INT,
			@intDoc INT
DECLARE @lNextNumber BIGINT			
BEGIN TRY
BEGIN TRANSACTION	
	EXECUTE @lInterrorCode = sp_xml_preparedocument @intDoc OUTPUT, @rExamSettingXml
	
	IF(@rExamSettingTypeId = 1)
	BEGIN
		UPDATE sAssessment.tExamMaster
		SET 
			IsProcessResult = E.IsProcessResult,
			IsNegativeMarks = E.IsNegativeMarks,
			IsLockForEdit = E.IsLockForEdit,
			IsPublished = E.IsPublished,
			IsPublishExamSchedule = E.IsPublishExamSchedule,
			UpdatedUserId = @rCreatedUserId,
			UpdatedDateTime = sCommon.fGetDate()
		FROM (
		SELECT
			ExamId,
			IsProcessResult,
			IsNegativeMarks,
			IsLockForEdit,
			IsPublished,
			IsPublishExamSchedule
			FROM OPENXML (@intDoc, '/Table/ExamSetting', 1)
		WITH
		(
			ExamId bigint,
			IsProcessResult bit ,
			IsNegativeMarks bit ,
			IsLockForEdit bit,
			IsPublished bit,
			IsPublishExamSchedule bit
		))E 
		Where sAssessment.tExamMaster.Id = E.ExamId
	END
	
	ELSE IF(@rExamSettingTypeId = 2)
	BEGIN
		UPDATE sAssessment.tExamMaster
		SET 
			IsGrade = E.IsGrade,
			IsPercentile = E.IsPercentile,
			IsRankNo = E.IsRankNo,
			IsGenerateRankNo = E.IsGenerateRankNo,
			IsPercentage = E.IsPercentage,
			IsRemovePercentageForFail = E.IsFailPercentageZero,
			IsSubjectPercentile = E.IsSubjectPercentile,
			IsResultStatus = E.IsDisplayResultStatus,
			IsAllExamPassRequired = E.IsAllExamPassRequired,
			IsExcludeAbsentTotal = E.IsExcludeAbsentTotal,
			UpdatedUserId = @rCreatedUserId,
			UpdatedDateTime = sCommon.fGetDate()
		FROM (
		SELECT
			ExamId,
			IsGrade,
			IsPercentile,
			IsRankNo,
			IsGenerateRankNo,
			IsPercentage,
			IsFailPercentageZero,
			IsSubjectPercentile,
			IsDisplayResultStatus,
			IsAllExamPassRequired,
			IsExcludeAbsentTotal
			FROM OPENXML (@intDoc, '/Table/ExamSetting', 1)
		WITH
		(
			ExamId bigint,
			IsGrade bit,
			IsPercentile bit,
			IsRankNo bit,
			IsGenerateRankNo bit,
			IsPercentage bit,
			IsFailPercentageZero bit,
			IsSubjectPercentile bit,
			IsDisplayResultStatus bit,
			IsAllExamPassRequired bit,
			IsExcludeAbsentTotal bit
		))E 
		Where sAssessment.tExamMaster.Id = E.ExamId
	END
	
	ELSE IF(@rExamSettingTypeId = 3)
	BEGIN
		UPDATE sAssessment.tExamMaster
			SET 
				IsDisplayPhoto = E.IsDisplayPhoto,
				IsDisplaySchoolName = E.IsDisplaySchoolName,
				IsDisplayBirthDate = E.IsDisplayBirthDate,
				IsDisplayParentName = E.IsDisplayParentName,
				IsDisplayHeightWeight = E.IsDisplayHeightWeight,
				IsDisplayGradeString = E.IsDisplayGradeString,
				IsDisplayTotalMark = E.IsDisplayTotalMark,
				IsDisplayResultIndicator = E.IsDisplayResultIndicator,
				IsDisplayHighestDivisionMark = E.IsDisplayHighestDivisionMark,
				IsDisplayHighestDivisionGrade = E.IsDisplayHighestDivisionGrade,
				IsDisplayTeacherSign = E.IsDisplayTeacherSign,
				IsDisplayPrincipalSign = E.IsDisplayPrincipalSign,
				IsDisplayActivityGrade = E.IsDisplayActivityGrade,
				UpdatedUserId = @rCreatedUserId,
				UpdatedDateTime = sCommon.fGetDate()
			FROM (
			SELECT
				ExamId,
				IsDisplayPhoto ,
				IsDisplaySchoolName ,
				IsDisplayBirthDate ,
				IsDisplayParentName ,
				IsDisplayHeightWeight ,
				IsDisplayGradeString ,
				IsDisplayTotalMark ,
				IsDisplayResultIndicator ,
				IsDisplayHighestDivisionMark ,
				IsDisplayHighestDivisionGrade ,
				IsDisplayTeacherSign ,
				IsDisplayPrincipalSign ,
				IsDisplayActivityGrade 
				FROM OPENXML (@intDoc, '/Table/ExamSetting', 1)
			WITH
			(
				ExamId bigint,
				IsDisplayPhoto bit,
				IsDisplaySchoolName bit,
				IsDisplayBirthDate bit,
				IsDisplayParentName bit,
				IsDisplayHeightWeight bit,
				IsDisplayGradeString bit,
				IsDisplayTotalMark bit,
				IsDisplayResultIndicator bit,
				IsDisplayHighestDivisionMark bit,
				IsDisplayHighestDivisionGrade bit,
				IsDisplayTeacherSign bit,
				IsDisplayPrincipalSign bit,
				IsDisplayActivityGrade bit
			))E 
			Where sAssessment.tExamMaster.Id = E.ExamId
	END
		
	ELSE
	BEGIN
		UPDATE sAssessment.tExamMaster
			SET 
				IsIncludeCoScholastic = E.IsIncludeCoScholastic,
				IsCoScholasticGradeIndicator = E.IsCoScholasticGradeIndicator,
				IsCoScholasticGradeDesIndicator = E.IsCoScholasticGradeDesIndicator,
				IsCoScholasticGrade = E.IsCoScholasticGrade,
				UpdatedUserId = @rCreatedUserId,
				UpdatedDateTime = sCommon.fGetDate()
			FROM (
			SELECT
				ExamId,
				IsIncludeCoScholastic ,
				IsCoScholasticGradeIndicator ,
				IsCoScholasticGradeDesIndicator ,
				IsCoScholasticGrade 
				FROM OPENXML (@intDoc, '/Table/ExamSetting', 1)
			WITH
			(
				ExamId bigint,
				IsIncludeCoScholastic bit,
				IsCoScholasticGradeIndicator bit,
				IsCoScholasticGradeDesIndicator bit,
				IsCoScholasticGrade bit
			))E 
			Where sAssessment.tExamMaster.Id = E.ExamId

	END

	EXEC sp_xml_removedocument @intDoc
	COMMIT TRANSACTION
	SELECT 1
	END TRY
	BEGIN CATCH
	ROLLBACK TRANSACTION
	SELECT -1
	END CATCH

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


GO
CREATE PROCEDURE [sFee].[pLateFeeRuleMasterAdd]
	    @rOrgId int,
        @rId int,
       -- @rCode nvarchar(20),
        @rTitle nvarchar(100),
		@rFeeRule nvarchar(100),
        @rIsObsolete bit
       
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY
	
	IF(@rId = 0)	
	BEGIN
		--IF EXISTS (SELECT Id FROM sFee.tLateFeeRuleMaster WHERE Code=@rCode AND OrgGroupBatchId=@rOrgGroupBatchId AND ReceiptBookId=@rReceiptBookId)
		--	BEGIN
		--	SELECT -101 as Result
		--	RETURN
		--END 
	
		IF EXISTS (SELECT Id FROM sFee.tLateFeeRuleMaster WHERE Title=@rTitle AND OrgId=@rOrgId)
			BEGIN
			SELECT -102 as Result
			RETURN
		END 

			IF EXISTS (SELECT Id FROM sFee.tLateFeeRuleMaster WHERE FeeRule=@rFeeRule AND OrgId=@rOrgId)
			BEGIN
			SELECT -103 as Result
			RETURN
		END 
	END
	ELSE
	BEGIN
		--IF EXISTS (SELECT Id FROM sFee.LateFeeRuleMaster WHERE  Code=@rCode AND OrgGroupBatchId=@rOrgGroupBatchId AND ReceiptBookId=@rReceiptBookId AND Id<>@rId)
		--BEGIN
		--	SELECT -101 as Result
		--	RETURN
		--END	
		IF EXISTS (SELECT Id FROM sFee.tLateFeeRuleMaster WHERE  Title=@rTitle AND OrgId=@rOrgId AND Id<>@rId)
		BEGIN
			SELECT -102 as Result
			RETURN
		END 

		IF EXISTS (SELECT Id FROM sFee.tLateFeeRuleMaster WHERE FeeRule=@rFeeRule AND OrgId=@rOrgId AND Id<>@rId)
			BEGIN
			SELECT -103 as Result
			RETURN
       END
	END
	
	
	 BEGIN TRANSACTION	
	 DECLARE @lNextNumber BIGINT 

	IF(@rId = 0)	
	BEGIN
		
	EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sFee.tLateFeeRuleMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
	
	
	INSERT INTO sFee.tLateFeeRuleMaster
		(
			OrgId,
			Id,
			--Code,
			Title,
			FeeRule,
			IsObsolete	
		)
	VALUES
		(
		    @rOrgId,
			@lNextNumber,
			--@rCode,
			@rTitle,
			@rFeeRule,
			@rIsObsolete
			
		)
		    SELECT 1 AS Result
	END	 

ELSE
	 BEGIN
	 SET @lNextNumber = @rId
	    UPDATE  sFee.tLateFeeRuleMaster
			--Code=@rCode,
			SET Title=@rTitle,
		    	FeeRule=@rFeeRule,
			IsObsolete=@rIsObsolete
	      WHERE Id = @rId
	      SELECT 11 AS Result
	 END  	
	--if not exists(Select 1 from sSystem.tPortalSync where OrgGroupId=@rOrgGroupId)
	--BEGIN
	--	INsert Into sSystem.tPortalSync
	--	(
	--		OrgGroupId,
	--		FeeChange
	--	)
	--	Values
	--	(
	--		@rOrgGroupId,1
	--	)
	--END
	--ELse
	--BEGIN
	--	Update sSystem.tPortalSync
	--	Set
	--		FeeChange=1
	--	Where OrgGroupId=@rOrgGroupId
	
	--END
	COMMIT TRANSACTION		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result 
	END CATCH	
		
END
GO
PRINT N'Creating [sFee].[pLateFeeRuleMasterGet]...';


GO
Create PROCEDURE [sFee].[pLateFeeRuleMasterGet]
	 
	 @rOrgId int,
	 @rId int,
	 --@rCode nvarchar(20),
	 @rTitle nvarchar(100),
	 @rFeeRule nvarchar(200)
	 	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 
	
	BEGIN
			--SET @rCode = sCommon.fSearchStringGet(@rCode)
			SET @rTitle = sCommon.fSearchStringGet(@rTitle)
	END
	
	SELECT 
			Id,
			--CGM.Code,
			Title,
			IsObsolete,
			OrgId,
			ISNULL(FeeRule,'') AS FeeRule
			
	FROM 
			sFee.tLateFeeRuleMaster 		
	WHERE 
			(OrgId = @rOrgId)
			AND	((Id = @rId) OR (@rId = 0))
			--AND ((CGM.Code like @rCode) OR (ISNULL(@rCode,'') = ''))
			AND ((Title like @rTitle) OR (ISNULL(@rTitle,'') = ''))
			
		
END
GO
PRINT N'Creating [sHrm].[pEmployeeAllBAtchDocumentSubmitGet]...';


GO
CREATE PROCEDURE [sHrm].[pEmployeeAllBAtchDocumentSubmitGet]
	 
        @rEmployeeId bigint,
		@rOrgId int
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
 
	BEGIN
			SELECT Isnull(D.Id,0) AS DocumentId,ISNULL(D.DocumentName,'') AS DocumentName,
			CASE WHEN  S.SubmitDocumentMasterId Is null And UD.DocumentId is null THEN 'False' ELSE 'True'END AS IsCheck
			, ISNULL(UD.DocumentName,'')  As FileName,
			isnull(Ud.OrgGroupBatchId,0) AS OrgGroupBatchId,ISNULL(S.IsMigrate,0) as IsMigrate
		 	FROM sCommon.tSubmitDocumentMaster D
			LEFT Join [sHrm].[tEmployeeDocument]  S ON S.SubmitDocumentMasterId=D.id AND S.EmployeeId=@rEmployeeId
			Left Join [sCommon].[tUploadDocument] UD On UD.DocumentId=S.SubmitDocumentMasterId AND UserSourceTypeId=95 AND UserSourceId=S.EmployeeId AND ( UD.UploadFunctionalityId=13)
			WHERE OrgId=@rOrgId AND SubmitDocumentFunctionalityId='2'
END
GO
PRINT N'Creating [sHrm].[pEmployeeDocumentAllBatchDepartmentWiseGet]...';


GO

Create PROCEDURE [sHrm].[pEmployeeDocumentAllBatchDepartmentWiseGet]

	@rOrgId smallint,
	@rCode nvarchar(20) = '',
	@rFirstName nvarchar(100)='',
	@rLastName nvarchar(100)='',
	@rStatusId int = 0,
	@rDepartmentId int = 0,
	@rUserId bigint=0,
    @rMultiCode nvarchar(max)='',
	@rDocumentId int=0

AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
		BEGIN
			SET @rCode= sCommon.fSearchStringGet(@rCode)
			SET @rFirstName = sCommon.fSearchStringGet(@rFirstName)
			SET @rLastName = sCommon.fSearchStringGet(@rLastName)
		END

		SELECT 
				EC.EmployeeId AS Id,
				EC.Code AS Code,
				ISNULL(EC.EmployeeName, '') AS Name,
				ISNULL(SM.Id,0) AS StatusId,
				ISNULL(SM.Title,'') AS Status,
				EC.DepartmentId,
				EC.DepartmentName,
				OM.DisplayTitle,
				OM.Id As OrgId,
				ISNULL([sHrm].[fEmployeeDocumentAllBatchDepartmentwiseget](@rDocumentId,EC.Id),'') AS DocumentName

		FROM 
				sHrm.vEmployeeCommunication EC
			INNER JOIN sCommon.tStatusMaster SM ON SM.Id = EC.StatusId AND SM.StatusTypeId = 4
			INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = EC.OrgId
			INNER JOIN sSecurity.tUserAccess U ON U.SourceId = EC.DepartmentId AND U.SourceTypeId=2017
		WHERE 
				((EC.OrgId = @rOrgId) OR (@rOrgId=0))
			AND ((EC.Code Like @rCode + '%') OR (ISNULL(@rCode,'') = ''))
			AND ((EC.FirstName Like @rFirstName  + '%') OR (@rFirstName = ''))	 			 
			AND ((EC.LastName Like @rLastName + '%')OR(@rLastName = ''))
			AND ((EC.DepartmentId = @rDepartmentId) OR (@rDepartmentId = 0))	
			AND ((EC.StatusId = @rStatusId) OR (@rStatusId = 0))	
			AND U.UserId = @rUserId
			AND (ISNULL(EC.Code,0) IN( SELECT cast(item as nvarchar(20)) FROM  sCommon.fnsplit(@rMultiCode,',')) OR ISNULL(@rMultiCode,'')='')
			
			ORDER BY EC.Number
	END TRY
	BEGIN CATCH
		SELECT -1 As Result,ERROR_LINE(),ERROR_MESSAGE()
	END CATCH
END
GO
PRINT N'Creating [sHrm].[pEmployeeStudentDocumentMigrationAdd]...';


GO
CREATE PROCEDURE [sHrm].[pEmployeeStudentDocumentMigrationAdd]
        @rUserSourceId bigint,
		@rUserSourceTypeId smallint,
		@DocumentId int,
		@DocumentName nvarchar(150)
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN 	
 
	BEGIN TRY
	
	 BEGIN TRANSACTION	
	 
		if(@rUserSourceTypeId=95)
		Begin
		INSERT INTO sHrm.tEmployeeDocument
		(
			EmployeeId, 
			SubmitDocumentMasterId,
			DocumentName,
			IsMigrate
		)
		VALUES   (@rUserSourceId,@DocumentId,@DocumentName,1)  
		END
		if(@rUserSourceTypeId=2003)
		BEGIN
		INSERT INTO sStudent.tStudentDocument
			(
				StudentId, 
				SubmitDocumentMasterId,
				DocumentName,
				IsMigrate
			)
			VALUES   (@rUserSourceId,@DocumentId,@DocumentName,1)   
		END 
	SELECT 1 AS Result
	
	COMMIT TRANSACTION
	
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
		
END
GO
PRINT N'Creating [sStudent].[pStudentAllBatchDocumentSubmitGet]...';


GO

CREATE PROCEDURE [sStudent].[pStudentAllBatchDocumentSubmitGet]
	 
        @rStudentId bigint,
		@rOrgId int
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
 
	BEGIN
			SELECT DISTINCT Isnull(D.Id,0) AS DocumentId,ISNULL(D.DocumentName,'') AS DocumentName,
			CASE WHEN  S.SubmitDocumentMasterId Is null and UD.DocumentId is null THEN 'False' ELSE 'True'END AS IsCheck,
			 ISNULL(UD.DocumentName,'') As FileName,
			isnull(Ud.OrgGroupBatchId,0) AS OrgGroupBatchId,ISNULL(S.IsMigrate,0) as IsMigrate
		 	FROM sCommon.tSubmitDocumentMaster D
			INNER JOIN sCommon.tSubmitDocumentScope DS ON DS.SubmitDocumentId = D.Id
			INNER JOIN sStudent.tAcademic A ON A.ClassId = DS.SourceId AND A.StudentId = @rStudentId
			INNER JOIN sSetup.tBatchMaster BM ON BM.Id = A.BatchId AND BM.IsActive = 1
			LEFT Join sStudent.tStudentDocument  S ON S.SubmitDocumentMasterId=D.id AND S.StudentId=@rStudentId
			Left Join [sCommon].[tUploadDocument] UD On UD.DocumentId=D.id AND UD.UploadFunctionalityId=11 AND UserSourceTypeId=2003 AND UserSourceId=S.StudentId AND UserSourceId=@rStudentId
			WHERE D.OrgId=@rOrgId AND SubmitDocumentFunctionalityId='1'

			UNION 

			SELECT DISTINCT Isnull(D.Id,0) AS DocumentId,ISNULL(D.DocumentName,'') AS DocumentName,
			CASE WHEN  S.SubmitDocumentMasterId Is null and UD.DocumentId is null THEN 'False' ELSE 'True'END AS IsCheck,
			 ISNULL(UD.DocumentName,'') As FileName,
			isnull(Ud.OrgGroupBatchId,0) AS OrgGroupBatchId,ISNULL(S.IsMigrate,0) as IsMigrate
		 	FROM sCommon.tSubmitDocumentMaster D
			LEFT JOIN sCommon.tSubmitDocumentScope DS ON DS.SubmitDocumentId = D.Id AND DS.SubmitDocumentId is null
			LEFT JOIN sStudent.tAcademic A ON A.ClassId = DS.SourceId AND A.StudentId = @rStudentId
			LEFT JOIN sSetup.tBatchMaster BM ON BM.Id = A.BatchId AND BM.IsActive = 1
			LEFT Join sStudent.tStudentDocument  S ON S.SubmitDocumentMasterId=D.id AND S.StudentId=@rStudentId
			Left Join [sCommon].[tUploadDocument] UD On UD.DocumentId=D.id AND UD.UploadFunctionalityId=11 AND UserSourceTypeId=2003 AND UserSourceId=S.StudentId AND UserSourceId=@rStudentId
			WHERE D.OrgId=@rOrgId AND SubmitDocumentFunctionalityId='1' --AND DS.SubmitDocumentId is null


END
GO

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

