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



GO
PRINT N'Altering [sBiometric].[tShiftMasterCloud]...';


GO
ALTER TABLE [sBiometric].[tShiftMasterCloud]
    ADD [LastOutAlertBasedOnId] INT CONSTRAINT [DF_tShiftMasterCloud_LastOutAlertBasedOnId] DEFAULT ('1') NULL;


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


GO
ALTER TABLE [sCertificate].[tBonafide] ALTER COLUMN [Remark] NVARCHAR (MAX) NULL;


GO
PRINT N'Altering [sCommunication].[tFeedbackTemplate]...';


GO
ALTER TABLE [sCommunication].[tFeedbackTemplate]
    ADD [IsAllAnsRequired] BIT CONSTRAINT [DF_tFeedbackTemplate_IsAllAnsRequired] DEFAULT ('0') NULL;


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


GO
ALTER TABLE [sHrm].[tLeaveTypeGradeSettings]
    ADD [LeaveCarryForwordRuleId] INT CONSTRAINT [DF_tLeaveTypeGradeSettings_LeaveCarryForwordRuleId] DEFAULT ('1') NULL;

GO
PRINT N'Refreshing [sHrm].[vEmployeeColumnName]...';


GO
EXECUTE sp_refreshview N'sHrm.vEmployeeColumnName';


GO
PRINT N'Refreshing [sHrm].[vEmployeeCustomSearch]...';


GO
EXECUTE sp_refreshview N'sHrm.vEmployeeCustomSearch';


GO
PRINT N'Refreshing [sCertificate].[vPrintTemplateBonafideCertificate]...';


GO
EXECUTE sp_refreshview N'sCertificate.vPrintTemplateBonafideCertificate';


GO
PRINT N'Refreshing [sHrm].[vAvailableLeaveEmployeeWise]...';


GO
EXECUTE sp_refreshview N'sHrm.vAvailableLeaveEmployeeWise';

GO


PRINT N'Altering [sCommunication].[vOrgwiseQueryCategory]...';


GO




/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
ALTER VIEW [sCommunication].[vOrgwiseQueryCategory]  
  
AS  
  
SELECT 	
	  QCM.Id,
	  QCM.Title,
	  QCM.OrgGroupId,
	  QCM.IsObsolete,
	  QCO.OrgId,
	  ISNULL(QCM.AssignedToId,1) AS AssignedToId
	  	   
  FROM 
  
	sCommunication.tQueryCategoryMaster QCM
	INNER JOIN sCommunication.tQueryCategoryOrg QCO ON QCO.QueryCategoryId=QCM.Id
	--AND QCM.IsObsolete = 0
GO
PRINT N'Altering [sFee].[vFeeColumnName]...';


GO










/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
ALTER VIEW [sFee].[vFeeColumnName]  
  
AS  
  
SELECT 	
		 --SA.AcademicId,	  
		 --SA.ClassId,
		 --SA.DivisionId,
		 SA.SUId,
		 SA.RollNo,
		 --SA.OrgId,
		 --SA.BatchId,
		 OGBM.Title AS OrgGroupBatch,
		 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,
		 --FA.VariableAmount,
		 --FA.DiscountAmount,
   --      FA.AdditionalAmount,
   --      FA.NotApplicable,
   --      FCM.Number AS FeeCategoryNumber,
   --      FCM.Code AS FeeCategoryCode,
   --      FCM.Title AS FeeCategoryName,
	  --   FCM.OrderIndex AS FeeCategoryOrderIndex,
	  --   FHM.Code AS FeeHeadCode,
	  --   FHM.Title AS FeeHeadTitle,
	  --   FM.Code AS FeeCode,
	  --   FM.Number AS FeeNumber,
	  --   FM.Title AS FeeName,
	  --   FM.OrderIndex AS FeeOrderIndex,
	  --   FS.Amount AS FeeSetupAmount,
	  --   FS.StartDate,
	  --   FS.EndDate,
	     R.ReceiptNumber,
	     R.ReceiptDate,
	     R.ReceiptAmount,
	     R.ChequeNo,
	     R.ChequeDate,
	     R.ChequeClearDate,
	     R.BankName,
	     R.Remark,
	     RBM.Code AS ReceiptBookCode,
	     RBM.Title AS ReceiptBookName,
	     RBM.Address1,
	     RBM.Address2,
	     RBM.BankAccountNumber,
	     --RD.Amount AS ReceiptDetailAmount,
	     PTM.Title AS PayType,
	     U.DisplayTitle AS CreatedUserName, 
	  	 SC.ParentContactNo,
		 R.CreatedDateTime
		    
FROM 
		sStudent.vStudentAcademic SA
		INNER JOIN sSetup.tOrgGroupBatchMaster OGBM ON OGBM.Id=SA.OrgGroupBatchId
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=SA.OrgId
		INNER JOIN sSetup.tBatchMaster BM ON BM.Id=SA.BatchId
		--INNER JOIN sFee.tFeeAllocation FA ON FA.SourceId=SA.AcademicId
		INNER JOIN sFee.tReceipt R ON R.SourceId=SA.AcademicId
		INNER JOIN sFee.tReceiptBookMaster RBM ON RBM.Id=R.ReceiptBookId
		--INNER JOIN sFee.tReceiptDetail RD ON RD.ReceiptId=R.Id
		--INNER JOIN sFee.tFeeSetup FS ON FS.Id=RD.FeeSetupId
		--INNER JOIN sFee.tFeeCategoryMaster FCM ON FCM.Id=FS.FeeCategoryId
		--INNER JOIN sFee.tFeeCategoryClass FCC ON FCC.FeeCategoryId=FCM.Id
		--INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS.FeeId
		--INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id=FM.FeeHeadId
		INNER JOIN sFinanceCommon.tPayTypeMaster PTM ON PTM.Id=R.PayTypeId 
		INNER JOIN sSecurity.tUser U ON U.Id=R.CreatedUserId
		LEFT JOIN sStudent.tStudentCommunication SC ON SC.StudentId=SA.StudentId
GO
PRINT N'Altering [sFee].[vFeeCustomSearch]...';


GO









/******************************************************************************  
 Purpose          : To get Batch Wise  Class
 Created date     : 21 Nov 2013
 Author           : Kundan Patel 
 Copyright©       : Copyright © Sapphire Software Solutions
 ******************************************************************************/  
ALTER VIEW [sFee].[vFeeCustomSearch]  
  
AS  
  
SELECT 	
		 SA.AcademicId,	  
		 SA.ClassId,
		 SA.DivisionId,
		 SA.RollNo,
		 SA.OrgGroupBatchId,
		 SA.OrgId,
		 SA.BatchId,
		 OGBM.Title AS OrgGroupBatch,
		 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,
		 --FA.Id AS FeeAllocationId,
		 --FA.VariableAmount,
		 --FA.DiscountAmount,
   --      FA.AdditionalAmount,
   --      FA.NotApplicable,
   --      FCC.IsCommon,
   --      FCM.Id AS FeeCategoryId,
   --      FCM.Number AS FeeCategoryNumber,
   --      FCM.Code AS FeeCategoryCode,
   --      FCM.Title AS FeeCategoryName,
	  --   FCM.OrderIndex AS FeeCategoryOrderIndex,
	  --   FHM.Id AS FeeHeadId,
	  --   FHM.Code AS FeeHeadCode,
	  --   FHM.Title AS FeeHeadTitle,
	  --   FM.Id AS FeeId,
	  --   FM.Code AS FeeCode,
	  --   FM.Number AS FeeNumber,
	  --   FM.Title AS FeeName,
	  --   FM.OrderIndex AS FeeOrderIndex,
	  --   FS.Id AS FeeSetupId,
	  --   FS.IsVariable,
	  --   FS.Amount AS FeeSetupAmount,
	  --   FS.StartDate,
	  --   FS.EndDate,
	     R.Id AS ReceiptId,
	     R.ReceiptNumber,
	     R.ReceiptDate,
	     R.ReceiptAmount,
	     R.ChequeNo,
	     R.ChequeDate,
	     R.ChequeClearDate,
	     R.BankName,
	     R.Remark,
	     R.IsObsolete,
	     RBM.Id AS FeeReceiptBookId,
	     RBM.Code AS ReceiptBookCode,
	     RBM.Title AS ReceiptBookName,
	     RBM.Address1,
	     RBM.Address2,
	     RBM.BankAccountNumber,
	     --RD.Amount AS ReceiptDetailAmount,
	     PTM.Id AS PayTypeId, 
	     PTM.Title AS PayType,
	     U.Id AS CreatedUserId,
	     U.DisplayTitle AS CreatedUserName,
	     SA.SUId,
	     SA.AdmissionCategoryId,
	     SC.ParentContactNo,
		 R.CreatedDateTime
FROM 
		sStudent.vStudentAcademic SA
		INNER JOIN sSetup.tOrgGroupBatchMaster OGBM ON OGBM.Id=SA.OrgGroupBatchId
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=SA.OrgId
		INNER JOIN sSetup.tBatchMaster BM ON BM.Id=SA.BatchId
		--LEFT JOIN sFee.tFeeAllocation FA ON FA.SourceId=SA.AcademicId
		INNER JOIN sFee.tReceipt R ON R.SourceId=SA.AcademicId
		INNER JOIN sFee.tReceiptBookMaster RBM ON RBM.Id=R.ReceiptBookId
		--INNER JOIN sFee.tReceiptDetail RD ON RD.ReceiptId=R.Id
		--LEFT JOIN sFee.tFeeSetup FS ON FS.Id=RD.FeeSetupId
		--LEFT JOIN sFee.tFeeCategoryMaster FCM ON FCM.Id=FS.FeeCategoryId
		--LEFT JOIN sFee.tFeeCategoryClass FCC ON FCC.FeeCategoryId=FCM.Id
		--INNER JOIN sFee.tFeeMaster FM ON FM.Id=FS.FeeId
		--INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id=FM.FeeHeadId
		INNER JOIN sFinanceCommon.tPayTypeMaster PTM ON PTM.Id=R.PayTypeId 
		LEFT JOIN sSecurity.tUser U ON U.Id=R.CreatedUserId
		LEFT JOIN sStudent.tStudentCommunication SC ON SC.StudentId = SA.StudentId
GO




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
			
	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 [sAlert].[pEmployeeBiometricLastOutTimeGet]...';


GO

ALTER PROCEDURE [sAlert].[pEmployeeBiometricLastOutTimeGet]
	@rTemplate nvarchar(max)='',
	@rSubject nvarchar(100)='',
	@rOrgId int,
	@rOrgGroupId int,
	@rIntervalTime int
 	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY	
		BEGIN TRANSACTION 
			if object_id('tempdb..#tShift') is not null
			BEGIN
				DROP TABLE #tShift
			END
			CREATE TABLE #tShift(
					ShiftCode int NOT NULL,
					OutTimeAlertTime time not NUll
				)
			insert #tShift
			Select 
				ShiftCode, 
				CAST(OutTimeAlertTime AS Time)
				--case When DATEDIFF(MINUTE,CAST(OutTimeAlertTime AS Time),cast([sCommon].[fGetDate]() as Time)) < @rIntervalTime then DATEDIFF(MINUTE,CAST(OutTimeAlertTime AS Time),cast([sCommon].[fGetDate]() as Time)) else @rIntervalTime end 
			  FROM sBiometric.tShiftMasterCloud
			where  ((OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))  
			AND IsOutTimeAlertTime=1
			AND CAST(OutTimeAlertTime AS Time)<cast([sCommon].[fGetDate]() as Time)
			AND CAST(OutTimeAlertTime AS Time) > CAST(DATEADD(minute,-(@rIntervalTime),[sCommon].[fGetDate]()) AS Time)

			declare @lcount int=(SELECT COUNT(ShiftCode) FROM #tShift)
	 
			--select * from #tShift
			if object_id('tempdb..#tTable') is not null
			BEGIN
				DROP TABLE #tTable
			END
			CREATE TABLE #tTable(
						SourceId bigint NOT NULL,
						In_Out_Time [datetime] NOT NULL,
						SourceTypeId int NOT NULL,
						OutTimeAlertTime time NOT NULL,
						CreatedDateTime [datetime] NOT NULL
					)
			if(@lcount>0)
			BEGIN
				insert into #tTable
				select SourceId,MAX(In_Out_Time),SourceTypeId,tempShift.OutTimeAlertTime,Max(CreatedDateTime) 
				from [sBiometric].[tCloudTransactionData] CDT 
				inner join #tShift tempShift ON tempShift.ShiftCode=CDT.ShiftCode
				WHERE Attn_Dt=CONVERT(date,[sCommon].[fGetDate]())  AND SourceTypeId=95 Group by SourceId,SourceTypeId,tempShift.OutTimeAlertTime
			END

			--select * from #tTable

			SELECT 
				EM.MobileNumber AS MobileNo,	
				EM.CompanyEmail AS EmailId,
				replace (
				replace(@rTemplate,'[EmployeeName]',EM.EmployeeName),
				'[TimeStamp]',ISNULL(Convert(varchar,temp.In_Out_Time,108),''))
				 AS MsgText,
				@rSubject AS Subject,
				95 AS ReceiverSourceTypeId,
				EM.EmployeeId AS ReceiverSourceId,
				EM.EmployeeName AS EmployeeName,
				ISNULL(EC.WhatsAppContactNo,'') AS WhatsAppContactNo,
				95 AS SourceTypeId,
				EM.EmployeeId AS SourceId,
				EM.OrgId,
				OM.OrgGroupId,
				OM.DisplayTitle AS OrganizationTitle
			FROM 
				#tTable temp
				Inner join sHrm.vEmployeeCommonDetail EM ON temp.SourceId=EM.EmployeeId
				LEFT JOIN sHrm.tEmployeeCommunications EC ON EC.EmployeeId = EM.EmployeeId
				INNER JOIN sSetup.tOrganizationMaster OM ON EM.OrgID = OM.Id
			WHERE
				temp.OutTimeAlertTime <= CAST([sCommon].[fGetDate]() AS Time)
				AND temp.OutTimeAlertTime > CAST(DATEADD(minute,-(@rIntervalTime),[sCommon].[fGetDate]()) AS Time)
				AND EM.OrgID =@rOrgId

			DROP TABLE #tTable
			DROP TABLE #tShift
		COMMIT TRANSACTION
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
END
GO
PRINT N'Altering [sAlert].[pStudentBiometricLastOutTimeGet]...';


GO


ALTER PROCEDURE [sAlert].[pStudentBiometricLastOutTimeGet]
	@rTemplate nvarchar(max)='',
	@rSubject nvarchar(100)='',
	@rOrgId int,
	@rOrgGroupId int,
	@rIntervalTime int
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY	
		BEGIN TRANSACTION 
			if object_id('tempdb..#tShift') is not null
			BEGIN
				DROP TABLE #tShift
			END
			CREATE TABLE #tShift(
					ShiftCode int NOT NULL,
					OutTimeAlertTime time not NUll,
					LastOutAlertBasedOnId int not NUll
				)

			insert #tShift
			Select 
				ShiftCode,
				 case when LastOutAlertBasedOnId=3 THEN 
				 case When DATEDIFF(MINUTE,CAST(EndTime AS Time),cast([sCommon].[fGetDate]() as Time)) < @rIntervalTime THEN CAST(DATEADD(minute,-(GraceBefore),EndTime) AS Time) else  CAST(DATEADD(minute,-(@rIntervalTime),[sCommon].[fGetDate]()) AS Time) end 
				 else  CAST(OutTimeAlertTime AS Time) end,
				 isnull(LastOutAlertBasedOnId,1)
				--case When DATEDIFF(MINUTE,CAST(@rIntervalTime AS Time),cast([sCommon].[fGetDate]() as Time)) < @rIntervalTime then DATEDIFF(MINUTE,CAST(OutTimeAlertTime AS Time),cast([sCommon].[fGetDate]() as Time)) else @rIntervalTime end 
			FROM sBiometric.tShiftMasterCloud
			where  ((OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))  
			AND IsOutTimeAlertTime=1
			AND (
			 ( (LastOutAlertBasedOnId=1 OR LastOutAlertBasedOnId=2) AND CAST(OutTimeAlertTime AS Time)<cast([sCommon].[fGetDate]() as Time)
			AND CAST(OutTimeAlertTime AS Time) > CAST(DATEADD(minute,-(@rIntervalTime),[sCommon].[fGetDate]()) AS Time)) 
			OR 
			(LastOutAlertBasedOnId=3 AND CAST(DATEADD(minute,-@rIntervalTime,EndTime) AS Time)<cast([sCommon].[fGetDate]() as Time)
			AND CAST(OutTimeAlertTime AS Time) > CAST(DATEADD(minute,-(@rIntervalTime),[sCommon].[fGetDate]()) AS Time))
			)

			--select * from #tShift
			declare @lcount int=(SELECT COUNT(ShiftCode) FROM #tShift)

			if object_id('tempdb..#tTable') is not null
			BEGIN
				DROP TABLE #tTable
			END
			CREATE TABLE #tTable(
				SourceId bigint NOT NULL,
				In_Out_Time [datetime] NOT NULL,
				SourceTypeId int NOT NULL,
				OutTimeAlertTime time NOT NULL,
				CreatedDateTime [datetime] NOT NULL,
				LastOutAlertBasedOnId int NOT NULL,
				ShiftCode int NOT NULL
			)
			if(@lcount>0)
			BEGIN
				insert into #tTable
				select SourceId,MAX(In_Out_Time),SourceTypeId,tempShift.OutTimeAlertTime,Max(CreatedDateTime),tempShift.LastOutAlertBasedOnId,CDT.ShiftCode
				from [sBiometric].[tCloudTransactionData] CDT 
				inner join #tShift tempShift ON tempShift.ShiftCode=CDT.ShiftCode
				WHERE Attn_Dt=CONVERT(date,[sCommon].[fGetDate]())  AND SourceTypeId=2003 Group by SourceId,SourceTypeId,tempShift.OutTimeAlertTime,tempShift.LastOutAlertBasedOnId,CDT.ShiftCode
			End
			--select * from #tTable

			SELECT 
				SC.ParentContactNo AS MobileNo,	
				SC.ParentEmail AS EmailId,
				replace (
				replace(@rTemplate,'[StudentName]',SC.Name),
				'[TimeStamp]',ISNULL(Convert(varchar,temp.In_Out_Time,108),''))
				 AS MsgText,
				@rSubject AS Subject,
				2003 AS ReceiverSourceTypeId,
				SC.StudentId AS ReceiverSourceId,
				SC.Name AS StudentName,
				SC.Code,
				ISNULL(SC.WhatsAppContactNo,'') AS WhatsAppContactNo,
				2003 AS SourceTypeId,
				SC.StudentId AS SourceId,
				SC.OrgId,
				OM.OrgGroupId,
				OM.DisplayTitle AS OrganizationTitle


			FROM 
				#tTable temp
				Inner Join sBiometric.tShiftMasterCloud SMC on SMC.ShiftCode=temp.ShiftCode
				Inner join sStudent.vStudentCommunication SC ON temp.SourceId=SC.StudentId AND SC.IsActiveBatch=1
				INNER JOIN sSetup.tOrganizationMaster OM ON SC.OrgID = OM.Id
			WHERE
				((temp.LastOutAlertBasedOnId=1 ) AND temp.OutTimeAlertTime <= CAST([sCommon].[fGetDate]() AS Time)
				AND temp.OutTimeAlertTime > CAST(DATEADD(minute,-(@rIntervalTime),[sCommon].[fGetDate]()) AS Time)
				
				OR( temp.LastOutAlertBasedOnId=2 AND CAST(DATEADD(minute,-(SMC.GraceBefore),SMC.EndTime) AS Time) <= CAST(temp.In_Out_Time  AS Time)
				AND CAST(temp.In_Out_Time AS Time) < CAST([sCommon].[fGetDate]() AS Time)
				)
				OR 
				(  
					(temp.LastOutAlertBasedOnId=3 
					AND (CAST(temp.In_Out_Time  AS Time)  >= CAST(temp.OutTimeAlertTime AS Time) 
					AND CAST(temp.In_Out_Time  AS Time)  <= CAST([sCommon].[fGetDate]() AS Time)))
					))
				AND SC.OrgID =@rOrgId
				AND SC.StatusId = 1

			DROP TABLE #tTable
			DROP TABLE #tShift
		COMMIT TRANSACTION
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
END
GO
PRINT N'Altering [sAssetManagement].[pAssetAdjustmentGet]...';


GO

ALTER PROCEDURE [sAssetManagement].[pAssetAdjustmentGet]
	@rOrgId [int],
	@rNumber [nvarchar](100) = 0
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
	  SELECT 
			ASSTADJ.AssetAuditDetailId AS AssetAuditDetailId,
			LM.Title AS StoreLocation,
			SIM.Code AS AssetTag,
			SIM.Title AS [Description],
			SIM.ModelNumber AS ModelNumber,
			SM.Title AS OldStatus,
			ASSTADJ.StatusId AS StatusId,
			ASSTADJ.ReasonId AS ReasonId,
			ASSTADJ.Remark AS Remark,
			CM.Title AS Category,
			CM.Id AS CategoryId,
			LM.Id AS LocationId,
			SIM.DepartmentId,
			LM.Title AS LocationCode,
			ODM.Title As Department,
			AA.Number AS AuditNumber,
			SIM.AssetCode AS NewAssetCode		
			
	FROM sAssetManagement.tAssetAdjustmentDetail ASSTADJ	
	INNER JOIN sAssetManagement.tAssetAdjustment ASSTA
	ON 	ASSTADJ.AssetAdjustmentId = ASSTA.Id
	INNER JOIN sAssetManagement.tAssetAuditDetail AAD
	ON ASSTADJ.AssetAuditDetailId = AAD.Id
	INNER JOIN sAssetManagement.tAssetAudit AA
	ON  AAD.AssetAuditId = AA.Id
	INNER JOIN sFixedAsset.vExtLocationMaster LM
	ON  LM.Id = AA.LocationId
	INNER JOIN sCommon.tStatusMaster SM
	ON	SM.Id = AA.StatusId
	INNER JOIN sAssetManagement.tAssetMaster SIM
	ON 	SIM.ID = AAD.AssetId
	INNER JOIN sFixedAsset.tCategoryMaster CM
		ON CM.Id = SIM.CategoryId
	INNER JOIN sSetup.tDepartmentMaster ODM
	ON ODM.Id = SIM.DepartmentID
	--INNER JOIN (SELECT DISTINCT DepartmentId, DepartmentTitle FROM sAssetManagement.vExtAssetDepartment) AD
	--	ON AD.DepartmentId = SIM.DepartmentID
	WHERE
		ASSTA.Number = @rNumber
    AND ((ASSTA.OrgId = @rOrgId) OR @rOrgId=0)
    
	ORDER BY SIM.Code DESC
END
GO
PRINT N'Altering [sBiometric].[pShiftMasterCloudAdd]...';


GO

 
ALTER PROCEDURE [sBiometric].[pShiftMasterCloudAdd]
@rShiftCode int = 0,
@rShiftName nvarchar(50),
@rStartTime DateTime = null,
@rEndTime DateTime = null,
@rGraceBefore int = 0,
@rGraceAfter int = 0,
@rShiftDur nvarchar(16) = NULL,
@rMinHrsHalfDay  nvarchar(16) = null,
@rMinHrsFullDay nvarchar(16) = null,
@rAlterGraceTime int = 0,
@IsOutTimeAlertTime bit = 0,
@OutTimeAlertTime DateTime = null,
@rOrgGroupId int = 0,
@rLastOutAlertBasedOnId int=1

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
	IF(@rShiftcode = 0)	
		BEGIN
			If Exists(select ShiftCode from sBiometric.tShiftMasterCloud where ShiftName=@rShiftName and OrgGroupId = @rOrgGroupId)
				BEGIN
				select -103 as Result
				RETURN
			END 
		END
	ELSE
		BEGIN
			If  Exists(select ShiftCode from sBiometric.tShiftMasterCloud where ShiftName=@rShiftName AND Shiftcode<>@rShiftcode and OrgGroupId = @rOrgGroupId)
			Begin
				select -103 as Result
				RETURN
			End
		END	
	
	 BEGIN TRANSACTION	
	 DECLARE @lNextNumber BIGINT 
	 IF EXISTS (SELECT 1 FROM sBiometric.tShiftMasterCloud)
	 BEGIN
		SELECT @lNextNumber = Max(ISNULL(ShiftCode,0)) + 1 FROM sBiometric.tShiftMasterCloud
	 END
	 ELSE
	 BEGIN
		SET @lNextNumber = 1
	 END
	 
	IF(@rShiftcode = 0)	
	BEGIN
	Insert into sBiometric.tShiftMasterCloud 
     (ShiftCode
     , ShiftName
     , StartTime
     , EndTime
     , GraceBefore
     , GraceAfter
     , ShiftDur
     , MinHrsHalfDay
     , MinHrsFullDay    
     , AlterGraceTime
     , IsOutTimeAlertTime
     , OutTimeAlertTime
     , OrgGroupId
	 ,LastOutAlertBasedOnId)
     Values
     (@lNextNumber
     ,@rShiftName
     ,@rStartTime
     ,@rEndTime
     ,@rGraceBefore
     ,@rGraceAfter
     ,@rShiftDur
     ,@rMinHrsHalfDay
     ,@rMinHrsFullDay
     ,@rAlterGraceTime
     ,@IsOutTimeAlertTime
     ,@OutTimeAlertTime
     ,@rOrgGroupId
     ,@rLastOutAlertBasedOnId
	 )
     
     SELECT @lNextNumber AS Result
	
		--COMMIT TRANSACTION         
	END	 

ELSE
	 BEGIN
	    UPDATE sBiometric.tShiftMasterCloud
	    SET
			ShiftName=@rShiftName,
			StartTime=@rStartTime,
			EndTime=@rEndTime,
			GraceBefore=@rGraceBefore,
			shiftDur=@rShiftDur,
			MinHrsHalfDay=@rMinHrsHalfDay,
			MinHrsFullDay=@rMinHrsFullDay,
			AlterGraceTime=@rAlterGraceTime,
		    IsOutTimeAlertTime = @IsOutTimeAlertTime,
			OutTimeAlertTime = @OutTimeAlertTime,
			LastOutAlertBasedOnId=@rLastOutAlertBasedOnId
		
	      WHERE ShiftCode = @rShiftcode and OrgGroupId = @rOrgGroupId
	      
	      SELECT @lNextNumber AS Result
	
		--COMMIT TRANSACTION
	 END  	

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


GO

ALTER PROCEDURE [sCertificate].[pBonafideAdd]

	@rId bigint
   ,@rAcademicId bigint
   ,@rNumber int
   ,@rBonafideDate date
   ,@rCreatedUserId bigint
   ,@rOrgId int
   ,@rRemark nvarchar(MAX)
   ,@rBonafiedReason nvarchar(200)
   ,@rBatchId int
   ,@rIsNumberBatchWise bit = 0
           	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	--DECLARE @lOrgGroupId smallint
	--SELECT @lOrgGroupId = OrgGroupId from sSetup.tOrganizationMaster Where Id = @rOrgId

	--DECLARE @lIsBonafideCertificateNumberBatchWise bit
	--SELECT @lIsBonafideCertificateNumberBatchWise = ISNULL(CS.ItemValue,CM.ItemValue) From sConfig.tConfigurationMaster CM
	--	    LEFT JOIN sConfig.tConfigurationSetup CS ON CM.Id = CS.ConfigurationId AND SourceTypeId = 2010 AND SourceId = @lOrgGroupId
	--	    WHERE CS.ConfigurationId = 290 

	BEGIN TRY	
	 BEGIN TRANSACTION	
	 
	 If(@rIsNumberBatchWise = 0)
	 BEGIN
		IF(@rNumber = 0)	
		BEGIN
			SELECT @rNumber = ISNULL(MAX(Number),0) + 1 FROM sCertificate.tBonafide WHERE OrgId=@rOrgId
		END	
			
		IF(@rId = 0)	
		BEGIN
			IF EXISTS(SELECT 1 FROM sCertificate.tBonafide WHERE Number=@rNumber AND OrgId=@rOrgId)
			BEGIN
				SELECT -103 AS Result
				RETURN
			END
		END
	 END
	 
	 If(@rIsNumberBatchWise = 1)
	 BEGIN
		IF(@rNumber = 0)	
		BEGIN
			SELECT @rNumber = ISNULL(MAX(Number),0) + 1 FROM sCertificate.tBonafide WHERE OrgId=@rOrgId AND BatchId = @rBatchId
		END	
			
		IF(@rId = 0)	
		BEGIN
			IF EXISTS(SELECT 1 FROM sCertificate.tBonafide WHERE Number=@rNumber AND OrgId=@rOrgId AND BatchId = @rBatchId)
			BEGIN
				SELECT -103 AS Result
				RETURN
			END
		END
	 END
	
	  DECLARE @lNextNumber BIGINT
	  IF(@rId = 0)	
		BEGIN
			EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sCertificate.tBonafide', 0, 1, 1,@outNextNumber = @lNextNumber output
				INSERT INTO sCertificate.tBonafide
				(
					Id
				   ,AcademicId
				   ,Number
				   ,BonafideDate
				   ,OrgId
				   ,CreatedUserId
				   ,CreatedDateTime
				   ,Remark
				   ,BonafiedReason
				   ,BatchId
				)
				VALUES
				(	@lNextNumber
				   ,@rAcademicId
				   ,@rNumber
				   ,@rBonafideDate
				   ,@rOrgId
				   ,@rCreatedUserId
				   ,sCommon.fGetDate()
				   ,@rRemark 
				   ,@rBonafiedReason
				   ,@rBatchId
				)
		END
		
		ELSE
		
		BEGIN
			SET @lNextNumber = @rId
				UPDATE  sCertificate.tBonafide
				SET
					AcademicId=@rAcademicId,
					BonafideDate=@rBonafideDate,
					UpdatedUserId=@rCreatedUserId,
					UpdatedDateTime=sCommon.fGetDate(),
					Remark=@rRemark,
					BonafiedReason=@rBonafiedReason,
					BatchId = @rBatchId
				WHERE Id = @rId
		 END  	
		
		SELECT @lNextNumber AS Result
	
	COMMIT TRANSACTION
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
	
END
GO
PRINT N'Altering [sCertificate].[pLeavingBulkGet]...';


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

BEGIN 
	
	SELECT 
			SA.AcademicId,
			ISNULL(SA.RollNo,0) AS RollNo,
			SA.Code,
			SA.Name,
			ISNULL(L.Id,0) AS Id,
			ISNULL(L.LCNumber,0) AS LCNumber,
			ISNULL(L.LCDate,'') AS LCDate,
			ISNULL(L.LCRemark,'')AS LCRemark,
			ISNULL(L.LastExamName,'')AS LastExamName,
			ISNULL(L.LastExamResult,'')AS LastExamResult,
			ISNULL(L.RepeatDetail,'')AS RepeatDetail,
			--ISNULL(L.SubjectStudied,'')AS SubjectStudied,
			ISNULL(L.PromoteToClass,'')AS PromoteToClass,
			ISNULL(L.ExtraCurricularActivities,'')As ExtraCurricularActivities,
			ISNULL(L.NCCScoutDetail,'')AS NCCScoutDetail,
			ISNULL(L.TermsInInstitute,'')As TermsInInstitute,
			ISNULL(L.FeeDetail,'')AS FeeDetail,
			ISNULL(L.FeeConcessionDetail,'')AS FeeConcessionDetail,
			ISNULL(AD.TotalDaysAnnual,0)AS TotalDays,
			ISNULL(AD.PresentDaysAnnual,0)AS PresentDays,
			ISNULL(StuA.ProofOfDOB,'')AS ProofOfDOB,
			CASE WHEN EXISTS(SELECT 1 FROM sSetup.tSubjectMaster S  INNER JOIN sSetup.tClassSubject CS ON  S.Id = CS.SubjectId AND CS.ClassId=@rClassId	
								WHERE S.OrgId=@rOrgId AND CS.IsOptional=0) THEN
			reverse(STUFF(reverse(isnull([sCommon].[fXMLTagsReplaceReverse](STUFF((SELECT ',' + S.Title from sSetup.tSubjectMaster S  
						   INNER JOIN sSetup.tClassSubject CS ON  S.Id = CS.SubjectId AND CS.ClassId=@rClassId	
						   WHERE S.OrgId=@rOrgId AND CS.IsOptional=0 FOR XML PATH('')), 1, 1, '')),'') +',' +
			isnull([sCommon].[fXMLTagsReplaceReverse](STUFF((SELECT ',' + S.Title FROM sAssessment.tOptionalSubject OS 
						   INNER JOIN sSetup.tClassSubject CS ON CS.Id = OS.ClassSubjectId 
						   INNER JOIN sSetup.tSubjectMaster S ON  S.Id = CS.SubjectId 
						   WHERE OS.AcademicId = SA.AcademicId FOR XML PATH('')), 1, 1, '')),'') 
						   ), 1, 1, '')) 
						   
			ELSE

			STUFF(reverse(reverse(isnull([sCommon].[fXMLTagsReplaceReverse](STUFF((SELECT ',' + S.Title from sSetup.tSubjectMaster S  
						   INNER JOIN sSetup.tClassSubject CS ON  S.Id = CS.SubjectId AND CS.ClassId=@rClassId	
						   WHERE S.OrgId=@rOrgId AND CS.IsOptional=0 FOR XML PATH('')), 1, 1, '')),'') +',' +
			isnull([sCommon].[fXMLTagsReplaceReverse](STUFF((SELECT ',' + S.Title FROM sAssessment.tOptionalSubject OS 
						   INNER JOIN sSetup.tClassSubject CS ON CS.Id = OS.ClassSubjectId 
						   INNER JOIN sSetup.tSubjectMaster S ON  S.Id = CS.SubjectId 
						   WHERE OS.AcademicId = SA.AcademicId FOR XML PATH('')), 1, 1, '')),'') 
						   )), 1, 1, '') END AS SubjectStudied,
			(SA.ClassName +'-'+SA.DivisionName)AS ClassDivision


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


GO
 
 
ALTER PROCEDURE [sCommon].[pGeneralMasterWithValueAdd]
	@rTableName [nvarchar](100),
	@rCodeField [nvarchar](100),
	@rRemarkField [nvarchar](100),
	@rCode [nvarchar](50),
	@rRemark [nvarchar](100),
	@rIsObsolete [nvarchar](10),
	@rCreatedUserId [bigint],
	@rIsStatic1to100 [bit],
	@rOrgId int,
	@rVisibilityField nvarchar(100),
	@rVisibilityId int
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON 
BEGIN

	--DECLARE @lSelectClause Nvarchar(2000)
	--DECLARE @lNextNumber BIGINT
	--EXECUTE @lNextNumber = sCommon.pTableRowIdGet @rTableName, 0, 1, 1
	DECLARE @lSelectClause Nvarchar(2000)
	DECLARE @lIsShow Nchar(1)
	DECLARE @lNextNumber BigInt
	DECLARE @lSchemaName Nvarchar(50)
	DECLARE @lTableName	Nvarchar(100)
	DECLARE @lSeed int
	
	SET @lSeed = (CASE WHEN @rIsStatic1to100 = 1 THEN 101 ELSE 1 END)
	EXECUTE  sCommon.pTableRowIdGet  @rOrgId,@rTableName, 0, @lSeed, 1,@outNextNumber = @lNextNumber output
	SET @lNextNumber = @lNextNumber
	SET @lIsShow = '0'
	SET @lSchemaName = SUBSTRING(@rTableName, 1, CHARINDEX('.', @rTableName, 1) - 1)
	SET @lTableName =  SUBSTRING(@rTableName, CHARINDEX('.', @rTableName, 1) + 1, LEN(@rTableName))
     
	--SELECT @lSchemaName, @lTableName
	
	SET @lSelectClause =     'IF EXISTS(SELECT ' + @rRemarkField + ' FROM ' + @rTableName + 
										' WHERE ' +  @rRemarkField + '	= ''' + @rRemark + ''' AND 
												' +  @rVisibilityField + '	= ' + CONVERT(NVARCHAR(20), @rVisibilityId) + ') 
     							BEGIN
									SELECT -102 AS Result
									RETURN
								END ' + 
							   'INSERT INTO ' + @rTableName 
								  + '(Id, ' + @rCodeField 
										   + ', ' + @rRemarkField 
										   + ', ' + ' UpdatedUserId ' 
										   + ', ' + ' UpdatedDateTime ' 
										   + ', ' + ' CreatedUserId ' 
										   + ', ' + ' CreatedDateTime ' 
                                           + ', ' + 'IsObsolete' +                                         
                                           --+' , ' +  'StoreLocationId' +
                                          ', ' + @rVisibilityField +
									      
									 ')
                               VALUES(' + CAST(@lNextNumber AS NVARCHAR(15)) + ', N''' + @rCode + ''', N''' 
							     		   + @rRemark +  ''', '''
                                           + CONVERT(NVARCHAR(25), @rCreatedUserId) + ''', ''' 
										   + CONVERT(NVARCHAR(30), sCommon.fGetDate()) + ''', '''
                                           + CONVERT(NVARCHAR(25), @rCreatedUserId) + ''', ''' 
										   + CONVERT(NVARCHAR(30), sCommon.fGetDate()) + ''', ''' + @rIsObsolete + ''','''										   
										   + CONVERT(NVARCHAR(20), @rVisibilityId) 
										 
								 + ''')
								 
								SELECT 1 AS Result'			
	
							 	
							 	
		PRINT @lSelectClause
	EXECUTE sp_executesql @lSelectClause

END
GO
PRINT N'Altering [sCommon].[pMasterDataAdd]...';


GO
ALTER  PROCEDURE [sCommon].[pMasterDataAdd]
	 
        
		@rTitle nvarchar(100)='',
		@rCode nvarchar(50)='',
		@rIsObsolete bit=0,
		@rCreatedUserId bigint,
		@rOrgGroupId smallint,
		@rOrgId int,
		@rTable_Name int=0,
		@rDependId int=0,
		@rNo int=0
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY

	 DECLARE @lNextNumber BIGINT 
	 DECLARE @lReligionId INT 
			 
		IF(@rTable_Name=1)
		BEGIN
		
			If Exists(select Id from sFinanceCommon.tBankMaster where Title=@rTitle AND OrgGroupId=@rOrgGroupId)
				BEGIN
				select -103 as Result
				RETURN
	 		END 
	 		EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sFinanceCommon.tBankMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
			INSERT INTO sFinanceCommon.tBankMaster
			(
			
				Id,
				Code,
				Title,
				 IsObsolete,
				 OrgGroupId,
				CreatedUserId,
				UpdateduserId,
				CreatedDateTime,
				UpdatedDateTime 
			)
		VALUES
			(
				@lNextNumber,
				@rCode,
				@rTitle,
				 @rIsObsolete,
				 @rOrgGroupId,
				@rCreatedUserId,
				@rCreatedUserId,
				sCommon.fGetDate() ,
				sCommon.fGetDate() 
			)
			END
			
			IF(@rTable_Name=2)
		BEGIN
		
			If Exists(select Id from sCommon.tReligionMaster where Title=@rTitle AND OrgGroupId=@rOrgGroupId)
				BEGIN
				select -103 as Result
				RETURN
	 		END 
	 		EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sCommon.tReligionMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
			INSERT INTO sCommon.tReligionMaster
			(
			
				Id,
				Code,
				Title,
				 IsObsolete,
				 OrgGroupId,
				CreatedUserId,
				UpdateduserId,
				CreatedDateTime,
				UpdatedDateTime 
			)
		VALUES
			(
				@lNextNumber,
				@rCode,
				@rTitle,
				 @rIsObsolete,
				 @rOrgGroupId,
				@rCreatedUserId,
				@rCreatedUserId,
				sCommon.fGetDate() ,
				sCommon.fGetDate() 
			)
			END
			
			IF(@rTable_Name=3)
		BEGIN
		
			If Exists(select Id from sCommon.tOccupationMaster where Title=@rTitle AND OrgGroupId=@rOrgGroupId)
				BEGIN
				select -103 as Result
				RETURN
	 		END 
	 		EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sCommon.tOccupationMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
			INSERT INTO sCommon.tOccupationMaster
			(
			
				Id,
				Code,
				Title,
				 IsObsolete,
				 OrgGroupId,
				CreatedUserId,
				UpdateduserId,
				CreatedDateTime,
				UpdatedDateTime 
			)
		VALUES
			(
				@lNextNumber,
				@rCode,
				@rTitle,
				 @rIsObsolete,
				 @rOrgGroupId,
				@rCreatedUserId,
				@rCreatedUserId,
				sCommon.fGetDate() ,
				sCommon.fGetDate() 
			)
			END
			
			
			IF(@rTable_Name=4)
		BEGIN
		
			If Exists(select Id from sCommon.tMotherTongueMaster where Title=@rTitle AND OrgGroupId=@rOrgGroupId)
				BEGIN
				select -103 as Result
				RETURN
	 		END 
	 		EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sCommon.tMotherTongueMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
			INSERT INTO sCommon.tMotherTongueMaster
			(
			
				Id,
				Code,
				Title,
				 IsObsolete,
				 OrgGroupId,
				CreatedUserId,
				UpdateduserId,
				CreatedDateTime,
				UpdatedDateTime 
			)
		VALUES
			(
				@lNextNumber,
				@rCode,
				@rTitle,
				 @rIsObsolete,
				 @rOrgGroupId,
				@rCreatedUserId,
				@rCreatedUserId,
				sCommon.fGetDate() ,
				sCommon.fGetDate() 
			)
			END
			
		IF(@rTable_Name=5)
		BEGIN
		
			If Exists(select Id from sCommon.tPreviousSchoolMaster where Title=@rTitle AND OrgGroupId=@rOrgGroupId)
				BEGIN
				select -103 as Result
				RETURN
	 		END 
	 		EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sCommon.tPreviousSchoolMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
			INSERT INTO sCommon.tPreviousSchoolMaster
			(
			
				Id,
				Code,
				Title,
				 IsObsolete,
				 OrgGroupId,
				CreatedUserId,
				UpdateduserId,
				CreatedDateTime,
				UpdatedDateTime 
			)
		VALUES
			(
				@lNextNumber,
				@rCode,
				@rTitle,
				 @rIsObsolete,
				 @rOrgGroupId,
				@rCreatedUserId,
				@rCreatedUserId,
				sCommon.fGetDate() ,
				sCommon.fGetDate() 
			)
			END
			
			IF(@rTable_Name=6)
		BEGIN
		
			If Exists(select Id from sHrm.tQualificationMaster where Title=@rTitle AND OrgGroupId=@rOrgGroupId)
				BEGIN
				select -103 as Result
				RETURN
	 		END 
	 		EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sHrm.tQualificationMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
			INSERT INTO sHrm.tQualificationMaster
			(
			
				Id,
				Code,
				Title,
				 IsObsolete,
				 OrgGroupId,
				CreatedUserId,
				UpdateduserId,
				CreatedDateTime,
				UpdatedDateTime 
			)
		VALUES
			(
				@lNextNumber,
				@rCode,
				@rTitle,
				 @rIsObsolete,
				 @rOrgGroupId,
				@rCreatedUserId,
				@rCreatedUserId,
				sCommon.fGetDate() ,
				sCommon.fGetDate() 
			)
			END
			
				IF(@rTable_Name=7)
		BEGIN
		
			If Exists(select Id from sCommon.tCountryMaster where Title=@rTitle AND OrgGroupId=@rOrgGroupId)
				BEGIN
				select -103 as Result
				RETURN
	 		END 
	 		EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sCommon.tCountryMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
			INSERT INTO sCommon.tCountryMaster
			(
			
				Id,
				Title,
				Nationality,
			
				 IsObsolete,
				 OrgGroupId,
				CreatedUserId,
				UpdateduserId,
				CreatedDateTime,
				UpdatedDateTime ,
				IsShow
			)
		VALUES
			(
				@lNextNumber,
				
				@rTitle,
				@rCode,
				 @rIsObsolete,
				 @rOrgGroupId,
				@rCreatedUserId,
				@rCreatedUserId,
				sCommon.fGetDate() ,
				sCommon.fGetDate() ,
				1
			)
			END
			
				IF(@rTable_Name=8)
		BEGIN
			If Exists(select Id from sCommon.tStateMaster where Title=@rTitle AND CountryId=@rDependId AND OrgGroupId=@rOrgGroupId)
				BEGIN
				select -103 as Result
				RETURN
	 		END 
	 		EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sCommon.tStateMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
			INSERT INTO sCommon.tStateMaster
			(
			
				Id,
				Title,
				CountryId,
			
				 IsObsolete,
				 OrgGroupId,
				CreatedUserId,
				UpdateduserId,
				CreatedDateTime,
				UpdatedDateTime ,
				IsShow
			)
		VALUES
			(
				@lNextNumber,
				
				@rTitle,
				@rDependId,
				 @rIsObsolete,
				 @rOrgGroupId,
				@rCreatedUserId,
				@rCreatedUserId,
				sCommon.fGetDate() ,
				sCommon.fGetDate() ,
				1
			)
			END
			
			
				IF(@rTable_Name=9)
		BEGIN
				If Exists(select Id from sCommon.tCityMaster where Title=@rTitle AND StateId =@rDependId AND OrgGroupId=@rOrgGroupId)
				BEGIN
				select -103 as Result
				RETURN
	 		END 
	 		EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sCommon.tCityMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
			INSERT INTO sCommon.tCityMaster
			(
			
				Id,
				Title,
				StateId,
			
				 IsObsolete,
				 OrgGroupId,
				CreatedUserId,
				UpdateduserId,
				CreatedDateTime,
				UpdatedDateTime ,
				IsShow
			)
		VALUES
			(
				@lNextNumber,
				
				@rTitle,
				@rDependId,
				 @rIsObsolete,
				 @rOrgGroupId,
				@rCreatedUserId,
				@rCreatedUserId,
				sCommon.fGetDate() ,
				sCommon.fGetDate() ,
				1
			)
			END
			
				IF(@rTable_Name=10)
		BEGIN
				If Exists(select Id from sCommon.tHobbyMaster where Title=@rTitle AND OrgGroupId=@rOrgGroupId)
				BEGIN
				select -103 as Result
				RETURN
	 		END 
	 		EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sCommon.tHobbyMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
			INSERT INTO sCommon.tHobbyMaster
			(
			
				Id,
				Title,
				Code,
				 IsObsolete,
				 OrgGroupId,
				CreatedUserId,
				UpdateduserId,
				CreatedDateTime,
				UpdatedDateTime 
			 
			)
		VALUES
			(
				@lNextNumber,
				
				@rTitle,
				@rCode,
				 @rIsObsolete,
				 @rOrgGroupId,
				@rCreatedUserId,
				@rCreatedUserId,
				sCommon.fGetDate() ,
				sCommon.fGetDate() 
				 
			)
			END
			
		-- IF(@rTable_Name=11)
		--BEGIN
		--		If Exists(select Id from sCommon.tHostelMaster where Title=@rTitle AND OrgGroupId=@rOrgGroupId)
		--		BEGIN
		--		select -103 as Result
		--		RETURN
	 --		END 
	 --		EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sCommon.tHostelMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
		--	INSERT INTO sCommon.tHostelMaster
		--	(
			
		--		Id,
		--		Title,
		--		Code,
		--		 IsObsolete,
		--		 OrgGroupId,
		--		CreatedUserId,
		--		UpdateduserId,
		--		CreatedDateTime,
		--		UpdatedDateTime 
			 
		--	)
		--VALUES
		--	(
		--		@lNextNumber,
				
		--		@rTitle,
		--		@rCode,
		--		 @rIsObsolete,
		--		 @rOrgGroupId,
		--		@rCreatedUserId,
		--		@rCreatedUserId,
		--		sCommon.fGetDate() ,
		--		sCommon.fGetDate() 
				 
		--	)
		--	END
		 
		 	
		 IF(@rTable_Name=11)
		BEGIN
				If Exists(select Id from sCommon.tAreaMaster where Title=@rTitle AND CityId =@rDependId AND OrgGroupId=@rOrgGroupId)
				BEGIN
				select -103 as Result
				RETURN
	 		END 
	 		EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sCommon.tAreaMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
			INSERT INTO sCommon.tAreaMaster
			(
			
				Id,
				Title,
				CityId,
				 IsObsolete,
				 OrgGroupId,
				CreatedUserId,
				UpdateduserId,
				CreatedDateTime,
				UpdatedDateTime,
				IsShow
			 
			)
		VALUES
			(
				@lNextNumber,
				
				@rTitle,
				@rDependId,
				 @rIsObsolete,
				 @rOrgGroupId,
				@rCreatedUserId,
				@rCreatedUserId,
				sCommon.fGetDate() ,
				sCommon.fGetDate() ,
				1
				
				 
			)
			END
		 
		  IF(@rTable_Name=12)
		BEGIN
				If Exists(select Id from sCommon.tFamiliyCastMaster where FamiliyCast=@rTitle AND CastCategoryId=@rDependId AND OrgGroupId=@rOrgGroupId)	--add CastCategoryId
				BEGIN
				select -103 as Result
				RETURN
	 		END 
	 		
	  
	 		EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sCommon.tFamiliyCastMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
			INSERT INTO sCommon.tFamiliyCastMaster
			(
			
				Id,
				FamiliyCast, 
				 FamiliySubCast, 
				 OrgGroupId,
				 IsObsolete,
				CreatedUserId,
				UpdateduserId,
				CreatedDateTime,
				UpdatedDateTime,
				CastCategoryId,		--added
				ReligionId			--added
			 
			 
			)
		VALUES
			(
				@lNextNumber,
				@rTitle, 
				@rCode,
				 @rOrgGroupId,
				 @rIsObsolete,
				@rCreatedUserId,
				@rCreatedUserId,
				sCommon.fGetDate() ,
				sCommon.fGetDate(),
				@rDependId,			--added
				-1					--added
				 
				
				 
			)
			END
			
			
			
			 IF(@rTable_Name=13	)
		BEGIN
		If Exists(select Id from sSetup.tSubjectMaster where Code=@rCode AND OrgId=@rOrgId)			--add if condition
			BEGIN
			select -102 as Result
			RETURN
		END
				If Exists(select Id from sSetup.tSubjectMaster where Title=@rTitle   AND OrgId=@rOrgId)
				BEGIN
				select -103 as Result
				RETURN
				END
				If Exists(select Id from sSetup.tSubjectMaster where Number=@rNo and OrgId=@rOrgId)		--add if condition
			BEGIN
			select -104 as Result
			RETURN
	 		END 
	 		
	  
	 		EXECUTE sCommon.pTableRowIdGet @rOrgId, 'sSetup.tSubjectMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
			INSERT INTO sSetup.tSubjectMaster
			(
			
				Id,
				Code, 
				 Number, 
				 Title,
				 IsObsolete,
				 OrgId,
				 OrderIndex,
				CreatedUserId,
				UpdateduserId,
				CreatedDateTime,
				UpdatedDateTime 
			 
			 
			)
		VALUES
			(
				@lNextNumber,
				@rCode,
				@rNo,
				@rTitle, 
				 @rIsObsolete,
				 @rOrgId,
				 @rNo,
				@rCreatedUserId,
				@rCreatedUserId,
				sCommon.fGetDate() ,
				sCommon.fGetDate()  
				 
				
				 
			)
			END
			
			
			SELECT 1 AS Result
			
			
		 
 
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END
GO
PRINT N'Altering [sCommunication].[pAssignedQueryListGet]...';


GO
ALTER PROCEDURE [sCommunication].[pAssignedQueryListGet]
	 
	 @rOrgGroupId smallint,
	 @rOrgGroupBatchId int,
	 @rSourceId bigint,
	 @rSourceTypeId smallint,
	 @rStatusId int,
	 @rFromDate datetime = NULL,
	 @rToDate datetime = NULL
	
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
		
BEGIN 
	SELECT 
			 Q.Id AS QueryId,
			 Q.Title AS QueryTitle,
			 Q.Remark AS QueryRemark,
			 SM.Id AS QueryStatusId,
			 SM.Title AS QueryStatus,
			 QCM.Title AS QueryCategoryTitle,
			 QCA.QueryCategoryId,
			 (CONVERT(VARCHAR(25),Q.CreatedDateTime, 100)) AS QueryCreatedDateTime,
			 (CASE WHEN U.SourceTypeId = 95 THEN 	'Employee'
			 WHEN U.SourceTypeId = 2003 THEN	'Student' END ) AS SourceType,
			 (CASE WHEN U.SourceTypeId = 95 THEN (SELECT EmployeeName FROM sHrm.vEmployeeCommonDetail WHERE EmployeeId= U.SourceId )	 
			 WHEN U.SourceTypeId = 2003 THEN (SELECT Name FROM sStudent.vStudentAcademic WHERE StudentId= U.SourceId AND OrgGroupBatchId = @rOrgGroupBatchId)  END ) AS SourceName,
			  (CASE WHEN U.SourceTypeId = 95 THEN (SELECT DepartmentName FROM sHrm.vEmployeeCommonDetail WHERE EmployeeId= U.SourceId )	 
			 WHEN U.SourceTypeId = 2003 THEN (SELECT ClassName +' - ' + DivisionName FROM sStudent.vStudentAcademic WHERE StudentId= U.SourceId AND OrgGroupBatchId = @rOrgGroupBatchId)  END ) AS ClasDivOrDept,
			 (CASE WHEN U.SourceTypeId = 95 THEN (SELECT OM.Title FROM sHrm.vEmployeeCommonDetail vECD INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = vECD.OrgID WHERE vECD.EmployeeId= U.SourceId )	 
			 WHEN U.SourceTypeId = 2003 THEN (SELECT OrganizationName FROM sStudent.vStudentAcademic WHERE StudentId= U.SourceId AND OrgGroupBatchId = @rOrgGroupBatchId)  END ) AS OrgTitle,
			 ISNULL(Q.Number,0) AS QueryNumber
	FROM 
				sCommunication.tQueryCategoryAssignedTo QCA
			INNER JOIN sCommunication.tQuery Q ON Q.QueryCategoryId = QCA.QueryCategoryId AND (Q.StatusId=@rStatusId)
			INNER JOIN sSecurity.tUser U ON U.Id = Q.CreatedUserId
			INNER JOIN sCommunication.tQueryCategoryMaster QCM ON QCM.Id=QCA.QueryCategoryId AND (QCM.OrgGroupId = @rOrgGroupId)	
			INNER JOIN sCommon.tStatusMaster SM ON SM.Id = Q.StatusId
	WHERE 
			(QCA.SourceTypeId = @rSourceTypeId) 
			AND (QCA.SourceId = @rSourceId) 
			AND ((Q.CreatedDateTime >= @rFromDate) OR (ISNULL(@rFromDate,'')=''))
			AND ((Q.CreatedDateTime <= @rToDate) OR (ISNULL(@rToDate,'')=''))
			AND (Q.OrgGroupBatchId = @rOrgGroupBatchId OR @rOrgGroupBatchId = 0)
	ORDER BY Q.CreatedDateTime Asc	
END
GO
PRINT N'Altering [sCommunication].[pBehaviourTypeMasterGet]...';


GO

ALTER PROCEDURE [sCommunication].[pBehaviourTypeMasterGet]
	 
	 @rId int,
	 @rTitle nvarchar(100),
	 @rOrgGroupId smallint,
	 @rBehaviourIndicatorId int = 0
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN 
	  --BEGIN
			--SET @rTitle = sCommon.fSearchStringGet(@rTitle)
	  --END
	 
	SELECT 
	BTM.Id,
	BTM.Title,
	BTM.Remark,
	BTM.BehaviourIndicatorId,
	BIM.Title AS BehaviourIndicatorTitle,
	BTM.OrgGroupId,
	BTM.IsObsolete,
	BTM.CreatedUserId,
	BIM.Title AS BehaviourType
  FROM 
        sCommunication.tBehaviourTypeMaster BTM
         INNER JOIN sCommunication.tBehaviourIndicatorMaster BIM
         ON BTM.BehaviourIndicatorId=BIM.Id
  WHERE 
      ((BTM.Id = @rId) OR (@rId = 0))
      AND  ((BTM.Title like @rTitle) OR (ISNULL(@rTitle,'') = '')) 
	  AND BTM.OrgGroupId = @rOrgGroupId	
	  AND ((BIM.Id = @rBehaviourIndicatorId) OR (@rBehaviourIndicatorId = 0))
	  
END
GO
PRINT N'Altering [sCommunication].[pFeedbackDataGet]...';


GO
ALTER PROCEDURE [sCommunication].[pFeedbackDataGet]
	  
        --@rFeedbackId bigint=0 ,
        @rOrgId int=0,
        @rBatchId int,
        @rOrgGroupBatchId int ,
        @rSourceId bigint=0
		
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	--Declare @lSourceTypeId smallint,@lFeedbackId  bigint=0 
	--SELECT @lFeedbackId=FB.Id FROM sCommunication.tFeedback FB
 --   INNER JOIN sCommunication.tFeedbackAssign FA ON Fa.FeedbackId=FB.Id 
 --   WHERE OrgGroupBatchId=@rOrgGroupBatchId and IsClose=0 and IsPublish=1  AND AssignToSourceId =@rSourceId ORDER BY Title
 
	--Select @lSourceTypeId=ForSourceTypeId from sCommunication.tFeedbackFor where FeedbackId=@lFeedbackId
	
	Select 
		 (CASE ForSourceTypeId WHen  95
		 THEN
			(Select 
			 FirstName +' ' + MiddleName+' ' + LastName
			From sHrm.tEmployeeMaster Where  Id=FF.ForSourceId And FF.ForSourceTypeId=95 And  OrgId=@rOrgId AND ISNULL(IsOtherUser,0) = 0  )
		 WHEN  2001
		 THEN
			(Select
			 FirstName +' ' + MiddleName+' ' + LastName
			From sStudent.tAcademic A
			INNER JOIN  sStudent.tStudentMaster SM ON SM.Id=A.StudentID
			Where A.Id=FF.ForSourceId ANd FF.ForSourceTypeId=2001)
		 Else
			FF.ForName
		 END) AS Name, 
		 
		 (CASE ForSourceTypeId WHen  95
		 THEN
			(Select 
			 Code
			From sHrm.tEmployeeMaster Where  Id=FF.ForSourceId And FF.ForSourceTypeId=95 And  OrgId=@rOrgId AND ISNULL(IsOtherUser,0) = 0  )
		 WHEN  2001
		 THEN
			(Select
			 SM.Code
			From sStudent.tAcademic A
			INNER JOIN  sStudent.tStudentMaster SM ON SM.Id=A.StudentID
			Where A.Id=FF.ForSourceId ANd FF.ForSourceTypeId=2001)
		 Else
			FF.ForName
		 END) AS Code, 
		 
		 (CASE ForSourceTypeId WHen  95
		 THEN
			(Select 
			 Id
			From sHrm.tEmployeeMaster Where  Id=FF.ForSourceId And FF.ForSourceTypeId=95 And  OrgId=@rOrgId AND ISNULL(IsOtherUser,0) = 0  )
		 WHEN  2001
		 THEN
			(Select
			 A.Id
			From sStudent.tAcademic A
			INNER JOIN  sStudent.tStudentMaster SM ON SM.Id=A.StudentID
			Where A.Id=FF.ForSourceId ANd FF.ForSourceTypeId=2001)
		 Else
			0  
		 END) AS Id, 
		 FF.Id AS FeedbackForId,
		FB.Id AS FeedbackId,FB.Title ,
		FB.FeedbackTemplateId
	 FROM sCommunication.tFeedbackFor FF 
	 INNER JOIN sCommunication.tFeedback FB ON FB.Id=FF.FeedbackId
	 INNER JOIN sCommunication.tFeedbackAssign FA ON Fa.FeedbackId=FB.Id
	 Where FB.OrgGroupBatchId=@rOrgGroupBatchId and IsClose=0 and IsPublish=1  AND AssignToSourceId =@rSourceId 
	 
	--If @lSourceTypeId=2001
	--BEGIN
	--	SELECT FB.Id AS FeedbackId,FB.Title,A.Id,Code+' - '+FirstName+' '+MiddleName+' '+LastName AS name 
	--	 FROM sCommunication.tFeedback FB
	--	--INNER JOIN sCommunication.tFeedbackAssign FA ON Fa.FeedbackId=FB.Id 
	--	INNER JOIN sCommunication.tFeedbackAssign FA ON Fa.FeedbackId=FB.Id
	--	INNER JOIN sCommunication.tFeedbackFor FO ON FO.FeedbackId=FB.ID
	--	INNER JOIN sStudent.tAcademic A  on FO.ForSourceId=A.Id
	--	INNER JOIN sStudent.tStudentMaster SM ON SM.Id=A.StudentId
	--	Where SM.OrgId=@rOrgId   And A.BatchId=@rBatchId
	--	And FB.OrgGroupBatchId=@rOrgGroupBatchId and IsClose=0 and IsPublish=1  AND AssignToSourceId =@rSourceId 
	
	-- END
	
	--else if  @lSourceTypeId=95
	--BEGIN
	--	SELECT FB.Id AS FeedbackId,FB.Title,EM.Id,Code+' - '+FirstName+' '+MiddleName+' '+LastName AS name 
	--	 FROM sCommunication.tFeedback FB
	--	--INNER JOIN sCommunication.tFeedbackAssign FA ON Fa.FeedbackId=FB.Id 
	--	INNER JOIN sCommunication.tFeedbackAssign FA ON Fa.FeedbackId=FB.Id
	--	INNER JOIN sCommunication.tFeedbackFor FO ON FO.FeedbackId=FB.ID
	--	INNER JOIN sHrm.tEmployeeMaster EM  on FO.ForSourceId=EM.Id
	--	Where EM.OrgId=@rOrgId  and  OrgGroupBatchId=@rOrgGroupBatchId And IsClose=0 and IsPublish=1 and AssignToSourceId =@rSourceId
	--END
	
	--else
	--BEGIN
		 
	--	SELECT FB.Id AS FeedbackId,FB.Title,FO.ForName AS name,0 AS Id
	--	 FROM sCommunication.tFeedback FB
	--	INNER JOIN sCommunication.tFeedbackAssign FA ON Fa.FeedbackId=FB.Id 
	--	LEFT JOIN sCommunication.tFeedbackFor FO ON FO.FeedbackId=FB.ID  
	--	where   OrgGroupBatchId=@rOrgGroupBatchId and IsClose=0 and IsPublish=1  
	--	AND ISNULL(AssignToSourceId,0) =0 AND ISNULL(ASSIgntosourcetypeid,0)=0
		  
	--END  
END
GO
PRINT N'Altering [sCommunication].[pFeedbackResponseGet]...';


GO
ALTER PROCEDURE [sCommunication].[pFeedbackResponseGet]
	 
	 
	 
	 @rUserSourceId bigint,
	 @rUserSourceTypeId int ,
	 @rOrgGroupBatchId int,
	 @rOrgGroupId smallint,
	 @rFeedbackId bigint ,
	  @rFeedbackForId bigint
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 

BEGIN 
	 SELECT	
		FQ.Id As TempateQuestionId,
					FQ.QuestionNumber,
					FQ.Question ,
					FQ.FeedbackAnswerTypeID,			 
					ISNULL(FQR. FeedbackAnswerTypeDetailId,0) AS FeedbackAnswerTypeDetailId,
					FR.SourceId ,
					ISNULL(FQR.FeedbackAnswerDescription,'') AS FeedbackAnswerDescription,
					FR.Id 
					--FB.FeedbackTemplateId
		from sCommunication.tFeedback FB 
		INNER JOIN	sCommunication.tFeedbackTemplateQuestion FQ ON FQ.FeedbackTemplateId = FB.FeedbackTemplateId
		LEFT JOIN sCommunication.tFeedbackResponse FR ON FR.FeedbackId = FB.Id AND FR.FeedbackForId =@rFeedbackForId  AND  SourceId = @rUserSourceId 		
				    AND SourceTypeId=@rUserSourceTypeId 
		Left Join sCommunication.tFeedbackQuestionResponse FQR ON  FQR.FeedBackResponseId = FR.Id 
		AND FQR.FeedbackTemplateQuestionID = FQ.Id
		--AND FQR.FeedBackResponseId = 1001000000001
		--LEFT JOIN sCommunication.tFeedbackAnswerTypeDetail FA ON FA.ID=FQR.FeedbackAnswerTypeDetailId
		WHERE FB.Id = @rFeedbackId ANd OrgGroupBatchId=@rOrgGroupBatchId
	 
		 END
GO
PRINT N'Altering [sCommunication].[pFeedbackTemplateAdd]...';


GO
ALTER PROCEDURE [sCommunication].[pFeedbackTemplateAdd]
	 
	 	@rOrgGroupId smallint,
        @rId bigint=0,
        @rCode nvarchar(200),
        @rTitle nvarchar(200), 
		@rCreatedUserId bigint ,
		@rOrgGroupBatchId int ,
		@rIsAllAnsRequired bit = 0
		
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY
	 DECLARE @lNextNumber BIGINT 

	 
	IF(@rId = 0)	
	BEGIN
		 
		if exists(Select 1 from sCommunication.tFeedbackTemplate where Code=@rCode AND OrgGroupId=@rOrgGroupId)
	BEGIn
		Select -101 As Result
		Return
	END
	
	If exists(Select 1 from sCommunication.tFeedbackTemplate where Title =@rTitle AND OrgGroupId=@rOrgGroupId)
	BEGIN
		Select -102 As Result
		Return
	END
	
	EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sCommunication.tFeedbackTemplate', 0, 1, 1,@outNextNumber = @lNextNumber output
	
	INSERT INTO sCommunication.tFeedbackTemplate
		(
			Id,
			Code,
			Title, 
			OrgGroupId,
			CreatedUserId,
			CreatedDateTime,
			IsAllAnsRequired 
			 
		)
	VALUES
		(
			@lNextNumber,
			@rCode,
			@rTitle, 
			@rOrgGroupId,
			@rCreatedUserId,
			sCommon.fGetDate(),
			@rIsAllAnsRequired 
		)
		    SELECT @lNextNumber AS Result
	END	 

ELSE
	 BEGIN
		if exists(Select 1 from sCommunication.tFeedbackTemplate where Code=@rCode AND OrgGroupId=@rOrgGroupId AND Id<>@rId)
	BEGIn
		Select -101 As Result
		Return
	END
	
	If exists(Select 1 from sCommunication.tFeedbackTemplate where Title =@rTitle AND OrgGroupId=@rOrgGroupId AND ID<>@rId)
	BEGIN
		Select -102 As Result
		Return
	END
	
	 if exists(Select 1 from sCommunication.tFeedback where OrgGroupBatchId =@rOrgGroupBatchId and FeedbackTemplateId=@rId)
	 BEGIN
		select -111 As Result
		return
	 
	 END
	
	    UPDATE  sCommunication.tFeedbackTemplate
	    SET
			Code=@rCode,
			Title=@rTitle, 
			UpdatedUserId=@rCreatedUserId,
			UpdatedDateTime=sCommon.fGetDate(),
			IsAllAnsRequired  = @rIsAllAnsRequired
	      
	      WHERE Id = @rId
	      SELECT @rId AS Result
	 END  	

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


GO
ALTER PROCEDURE [sCommunication].[pFeedbackTemplateGet]
	 
	 	 
        @rCode nvarchar(200),
        @rTitle nvarchar(200),
        @rId bigint=0,
        @rOrgGroupId int
		
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	Select 
		 Id,
		 Code,
		 Title, 
		 OrgGroupId,
		CreatedUserId,
		CreatedDateTime,
		ISNULL(IsAllAnsRequired,0) AS IsAllAnsRequired
		
	from sCommunication.tFeedbackTemplate  
	Where (( Code=@rCode) OR (ISNULL(@rCode,'')=''))
	AND (( Title=@rTitle) OR (ISNULL(@rTitle,'')=''))
	AND (( Id=@rId) OR (@rId=0))
	AND OrgGroupId=@rOrgGroupId
		
END
GO
PRINT N'Altering [sCommunication].[pMyBookGet]...';


GO
ALTER PROCEDURE [sCommunication].[pMyBookGet]
	 
	 @rReaderSourceTypeId smallint,
	 @rReaderSourceId bigint,
	 @rBatchId int,
	 @rStatusId int=0,
	 @rLibraryId int=0
	 	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN 



	select 
		BL.Id AS BookLogId,
		BM.Title AS BookTitle,
		BL.LogDate AS IssueDate,
		--BL.IsCurrentStatus AS IsReturnPending,
		BL.StatusId AS StatusId,
		--SM.Title As StatusName,
		(CASE WHEN (ISNULL(BL.ReturnDate,'')!='' OR BL.BookActionStatusId=1) THEN 'Return'
			 WHEN BL.BookActionStatusId=2 THEN 'Release'
			 ELSE SM.Title END) AS StatusName,
		CASE WHEN BL.IsCurrentStatus = 1 THEN 'Pending' ELSE 'Return' END AS IsReturnPending,
		BL.DueDate,
		BL.LibraryId ,
		ISNULL(BM.Author,'')AS Author
	--CASE WHEN BL.IsCurrentStatus = 0 THEN BL.DueDate ELSE '' END AS DueDate

	  from 
		  sLibrary.tBookLog BL 
		  INNER JOIN sLibrary.tBookMaster BM
		  ON BL.ResourceSourceId = BM.Id AND BL.ResourceSourceTypeId = 2009  
		  LEFT JOIN sCommon.tStatusMaster SM ON SM.Id = BL.StatusId AND SM.statusTypeId = 106
	  WHERE 
		  --BL.statusId = 523
		  BL.ReaderSourceId = @rReaderSourceId
		  AND BL.ReaderSourceTypeId = @rReaderSourceTypeId
		  AND (BL.StatusId = @rStatusId OR @rStatusId=0)
		  AND (BL.LibraryId= @rLibraryId OR @rLibraryId=0)
		  AND (SM.Id!=521 OR ISNULL(BL.BookActionStatusId,0)!=0)
		  AND (BL.BatchId = @rBatchId OR @rBatchId = 0)
	  
	  ORDER BY BL.LogDate DESC,BL.CreatedDateTime DESC

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) 
			AND 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].[pAssignOtherFeeGet]...';


GO
ALTER PROCEDURE [sFee].[pAssignOtherFeeGet]
	 
		@rOrgId int,
		@rBatchId int,
		@rAdmissionCategoryId int,
		@rDepartmentId int,
		@rClassId int,
		@rDivisionId int,
		@rFilterId tinyint,
		@rOrgGroupBatchId int,
		@rCode nvarchar(max)=''
		 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 

BEGIN 		
		
	SELECT 
			SA.AcademicId AS SourceId,
			SA.ClassName +' - '+SA.DivisionName AS Class,
			SA.Code,
			SA.Name,
			SA.RollNo
	FROM 
			sStudent.vStudentAcademic SA
			LEFT JOIN sHostel.tAssignRoom AR ON  AR.SourceId=SA.StudentId AND AR.SourceTypeId=2003 AND ((SA.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0)) AND AR.OrgGroupBatchId=SA.OrgGroupBatchId 
			--LEFT JOIN sTransport.tAssignRoute AT ON  AT.SourceId=SA.StudentId AND AT.SourceTypeId=2003 AND ((SA.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0))
			LEFT JOIN (
				select AT.SourceId,BRDM.Id,AT.SourceTypeId,isnull(BRM.id,0) as PickupDestinationRoutId,isnull(BRM2.id,0) as DropDestinationRoutId From sTransport.tAssignRoute AT 
				Left JOIN sTransport.tBusRouteDestinationMaster BRDM ON BRDM.Id=AT.PickupDestinationId
				Left JOIN sTransport.tBusRouteMaster BRM On BRm.id=BRDM.BusRouteId  --AnD BRM.OrgGroupBatchId=@rOrgGroupBatchId
				LEFT JOIN sTransport.tBusRouteDestinationMaster BRDM2 ON BRDM2.Id=AT.DropDestinationId	
			    LEFT JOIN sTransport.tBusRouteMaster BRM2 On BRM2.Id=BRDM2.BusRouteId  --AND BRM2.OrgGroupBatchId=@rOrgGroupBatchId
				where  BRM.OrgGroupBatchId=@rOrgGroupBatchId or BRM2.OrgGroupBatchId=@rOrgGroupBatchId
			) A ON  A.SourceId=SA.StudentId AND A.SourceTypeId=2003 AND ((SA.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0)) 
	WHERE 
		(SA.OrgId=@rOrgId) 
		AND (SA.BatchId=@rBatchId)
		AND ((SA.AdmissionCategoryId=@rAdmissionCategoryId) OR (@rAdmissionCategoryId=0))
		AND ((SA.DepartmentId=@rDepartmentId) OR (@rDepartmentId=0))
		AND ((SA.ClassId=@rClassId) OR (@rClassId=0))
		AND ((SA.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		AND (SA.StatusId=1)
		AND (
		(@rFilterId = 0) -- All
		OR (@rFilterId = 2 AND ISNULL(AR.SourceId,0)!=0) -- Hostel
		--OR (@rFilterId = 3 AND ISNULL(AT.SourceId,0)!=0) -- Transport
		OR (@rFilterId = 3 AND ISNULL(A.SourceId,0)!=0) -- Transport
		--OR ((@rFilterId =1) AND ((ISNULL(AR.SourceId,0)=0) AND (ISNULL(AT.SourceId,0)=0))) -- School
		OR ((@rFilterId =1) AND ((ISNULL(AR.SourceId,0)=0) AND (ISNULL(A.SourceId,0)=0))) -- School
		)
		AND (ISNULL(SA.Code,0) IN( SELECT cast(item as nvarchar(20)) FROM  sCommon.fnsplit(@rCode,',')) OR @rCode='') 
		ORDER BY SA.ClassOrderIndex, SA.DivisionOrder, SA.RollNo
END
GO
PRINT N'Altering [sFee].[pFeeAllocationViewGet]...';


GO
ALTER PROCEDURE [sFee].[pFeeAllocationViewGet]
	 
		
		@rSourceId bigint ,
		@rSourceTypeId int,
		@rBatchId int
		--@rStudentId bigint =0
		
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
		
BEGIN 	
	--if (@rStudentId <> 0)
	--SELECT @rSourceId = Id FROM sStudent.tAcademic WHERE StudentId = @rStudentId AND BatchId = @rBatchId
		DECLARE @lClassId int
		DECLARE @lAdmissionCategoryId int
		SELECT @lClassId=ClassId,@lAdmissionCategoryId=AdmissionCategoryId FROM sStudent.vStudentAcademic WHERE AcademicId=@rSourceId

SELECT 
			FM.Title AS FeeName,
			FS.Id AS FeeSetupId,
			ISNULL(FS.IsOptional,0)AS IsOptional,
			ISNULL(FS.IsVariable,0)AS IsVariable,
			--CASE WHEN FA.VariableAmount > 0 THEN FA.VariableAmount ELSE ISNULL(FS.Amount,0) END AS Amount,
			ISNULL(FS.Amount,0) AS Amount,
			ISNULL(FS.StartDate,'1900-01-01')AS StartDate,
			ISNULL(FS.EndDate,'1900-01-01')AS EndDate,
			ISNULL(FA.VariableAmount,0.00)AS FAAmount,
			ISNULL(FA.DiscountAmount,0.00)AS DiscountAmount,
			ISNULL(FA.AdditionalAmount,0.00)AS AdditionalAmount,
			ISNULL(R.PaidAmount,0) AS PaidAmount,
			ISNULL(FS.Amount,0.00)+ISNULL(FA.VariableAmount,0.00)+ISNULL(FA.AdditionalAmount,0.00) -ISNULL(FA.DiscountAmount,0.00)-ISNULL(FA.NotApplicableAmount,0.00)-ISNULL(R.PaidAmount,0.00)AS PendingAmount,
			ISNULL(FA.NotApplicableAmount,0.00) AS NotApplicableAmount,
			ISNULL(FA.Remark,'') AS Remark,
			FM.OrderIndex,
			0 AS ReceiptNumber,
			'1900-01-01'AS ReceiptDate,
			ISNULL(FS.ReceiptBookId,0) AS ReceiptBookId,
			FS.CollectionGroupId,
			FM.FeeHeadId
			
  FROM 
			sFee.tFeeMaster FM
		INNER JOIN 
		(
			SELECT 
				FS1.FeeId,
				FS1.Id,
				ISNULL(FS1.IsOptional,0)AS IsOptional,
				ISNULL(FS1.IsVariable,0)AS IsVariable,
				ISNULL(FS1.Amount,0)AS Amount,
				ISNULL(FS1.StartDate,'1900-01-01')AS StartDate,
				ISNULL(FS1.EndDate,'1900-01-01')AS EndDate,
				FS1.BatchId,
				ISNULL(FS1.ReceiptBookId,0) AS ReceiptBookId,
				ISNULL(FS1.CollectionGroupId,0) AS CollectionGroupId
				 
			FROM sFee.tFeeSetup FS1
			   
				WHERE FS1.ClassId=@lClassId AND FS1.AdmissionCategoryId=@lAdmissionCategoryId AND IsOptional=0
				AND FS1.BatchId = @rBatchId
				UNION ALL
				
			SELECT 
				FS1.FeeId,
				FS1.Id,
				ISNULL(FS1.IsOptional,0)AS IsOptional,
				ISNULL(FS1.IsVariable,0)AS IsVariable,
				ISNULL(FS1.Amount,0)AS Amount,
				ISNULL(FS1.StartDate,'1900-01-01')AS StartDate,
				ISNULL(FS1.EndDate,'1900-01-01')AS EndDate,
				FS1.BatchId,
				ISNULL(FS1.ReceiptBookId,0) AS ReceiptBookId,
				ISNULL(FS1.CollectionGroupId,0) AS CollectionGroupId 
				 
			FROM sFee.tFeeSetup FS1
			 
		INNER JOIN sFee.tFeeOptional FO
			ON FO.FeeSetupId=FS1.Id AND (FO.SourceId=@rSourceId) AND(FO.SourceTypeId=@rSourceTypeId)
			WHERE
			 FS1.BatchId = @rBatchId
			AND	FS1.ClassId=@lClassId AND FS1.AdmissionCategoryId=@lAdmissionCategoryId AND IsOptional=1
		) FS ON FS.FeeId=FM.Id
		LEFT JOIN sFee.tFeeAllocation FA
			ON FA.FeeSetupId=FS.Id AND (FA.SourceId=@rSourceId) AND(FA.SourceTypeId=@rSourceTypeId)
		LEFT JOIN
		(
		    SELECT ISNULL(SUM(RD.Amount),0) AS PaidAmount,RD.FeeSetupId
		    FROM
		    sFee.tReceipt R INNER JOIN sFee.tReceiptDetail RD ON	RD.ReceiptId = R.Id 		
			WHERE						
			 R.SourceId= @rSourceId
			AND ((R.SourceTypeId = @rSourceTypeId) OR (@rSourceTypeId=0))
			AND R.IsObsolete = 0 
			AND R.BatchId = @rBatchId
			 GROUP BY FeeSetupId
		) R ON R.FeeSetupId = FS.Id	
WHERE 
	FS.BatchId=@rBatchId
ORDER BY FS.StartDate ASC,FM.OrderIndex ASC

END
GO
PRINT N'Altering [sHrm].[pLeaveApproveValidate]...';


GO

 
ALTER PROCEDURE [sHrm].[pLeaveApproveValidate]

        @rId bigint,
        @rEmployeeId bigint,
        @rFromDate date,
        @rLeaveTypeId int,
        @rNoOfLeave decimal(5,2),
        @rBatchId int,
        @rOrgId int
        
AS
SET NOCOUNT ON

BEGIN
	IF EXISTS (SELECT 1 FROM  sHrm.tLeaveTypeMaster WHERE Id = @rLeaveTypeId AND isLwp = 0)
	BEGIN
	   DECLARE @lMainLeaveTypeId tinyint
	   SET @lMainLeaveTypeId =0
	   SELECT @lMainLeaveTypeId = ISNULL(MainLeaveTypeId,0) FROM sHrm.tLeaveTypeMaster WHERE Id = @rLeaveTypeId
	   
	   DECLARE @lMaxInMonth decimal(5,2)
	   DECLARE @lNumberOfLeave decimal(5,2)
	   SET @lMaxInMonth =0
	   SET @lNumberOfLeave =0
	   
	   DECLARE @lFirstDate date
	   DECLARE @lLastDate date
	   
	   SET @lFirstDate = DATEADD(mm, DATEDIFF(mm, 0, @rFromDate), 0)
	   SET @lLastDate = DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, @rFromDate) + 1, 0))
	   
	   SELECT @lMaxInMonth = ISNULL(LB.MaxInMonth,0) FROM sHrm.tEmployeeJobDetail EJD 
	   INNER JOIN sHrm.tLeaveBalance LB ON LB.EmployeeId=EJD.EmployeeId AND LB.BatchId = @rBatchId
	   WHERE EJD.OrgId = @rOrgId AND LB.LeaveTypeId=@rLeaveTypeId AND EJD.EmployeeId=@rEmployeeId
	   
	   -- For Employee Specific 08/11/2017 - by Ramesh Gurjar
	   IF(@lMaxInMonth=0)
	   BEGIN
			SELECT @lMaxInMonth = ISNULL(LTGS.MaxInMonth,0) FROM sHrm.tEmployeeJobDetail EJD 
			INNER JOIN sHrm.tLeaveTypeGradeSettings LTGS ON EJD.EmployeeGradeId=LTGS.EmployeeGradeId 
			WHERE LTGS.OrgId = @rOrgId 
			AND LTGS.LeaveTypeId=@rLeaveTypeId 
			AND  EJD.EmployeeId=@rEmployeeId
	   END
	   
	   --BEGIN LEAVE CARY FORWARD VALIDATION
		DECLARE @lLeaveCarryForwordRuleId int=1
		DECLARE @lMonthDiffrence int
		Declare @lDateofJoining date = (SELECT DateOfJoining FROM sHrm.tEmployeeJobDetail WHERE EmployeeId = @rEmployeeId)
		 SELECT @lLeaveCarryForwordRuleId=ISNULL(LTGS.LeaveCarryForwordRuleId,1) FROM sHrm.tEmployeeJobDetail EJD
		 INNER JOIN sHrm.tLeaveTypeGradeSettings LTGS ON EJD.EmployeeGradeId=LTGS.EmployeeGradeId 
		 WHERE LTGS.OrgId =@rOrgId
		 AND LTGS.LeaveTypeId=@rLeaveTypeId
		 AND  EJD.EmployeeId=@rEmployeeId

		 
		 if(@lLeaveCarryForwordRuleId=1)
		 Begin
		  set @lMaxInMonth=@lMaxInMonth
		 END
		 else if(@lLeaveCarryForwordRuleId=2)
		 Begin
			 select @lMaxInMonth= Case When @lDateofJoining<LeaveYearFromDate Then @lMaxInMonth*(DATEDIFF(month,LeaveYearFromDate,@rFromDate)+1) else @lMaxInMonth*(DATEDIFF(month,LeaveYearFromDate,@lDateofJoining)+1) end 
			 ,@lFirstDate=Case When @lDateofJoining<LeaveYearFromDate Then LeaveYearFromDate else @lDateofJoining END from [sSetup].[tBatchMaster] WHere Id=@rBatchId
		 END
		
	   --END LEAVE CARY FORWARD VALIDATION
	   


	   SELECT  @lNumberOfLeave= ISNULL(SUM(L.NoOfLeave),0) 
	   FROM     
	   sHrm.tLeave L  
	   WHERE  
	   L.EmployeeId=@rEmployeeId 
	   AND (L.FromDate>=@lFirstDate) AND (L.FromDate<=@lLastDate)
	   AND L.LeaveTypeId=@rLeaveTypeId AND L.StatusId = 550
	   GROUP BY L.EmployeeId
	   
	   IF (@lMaxInMonth < (@lNumberOfLeave+@rNoOfLeave) AND @lMainLeaveTypeId!=3)
	   BEGIN
			SELECT -105  AS RESULT
			RETURN
	   END
	   ELSE
	   BEGIN
			SELECT 1
	   END	
   END
   ELSE 
   SELECT 1
END
GO
PRINT N'Altering [sHrm].[pLeaveTypeGradeSeetingListGet]...';


GO

ALTER PROCEDURE [sHrm].[pLeaveTypeGradeSeetingListGet]
    @rEmployeeGradeId int = 0,
    @rOrgId int
AS
SET NOCOUNT ON

BEGIN
       
       SELECT 
				 LTM.Id AS LeaveTypeId,
				 LTM.Title AS LeaveType,
				 ISNULL(LTGS.EmployeeGradeId,0) AS EmployeeGradeId,
				 ISNULL(LTGS.NoOfLeave,'') AS NoOFLeave,
				 ISNULL(LTGS.MaxInMonth,0) AS MaxInMonth,
				 LTGS.LeaveTypeId ,
				 Isnull(LTGS.LeaveCarryForwordRuleId,1) AS LeaveCarryForwordRuleId
       FROM 
			   sHrm.tLeaveTypeMaster LTM 
		   LEFT JOIN sHrm.tLeaveTypeGradeSettings LTGS ON LTGS.LeaveTypeId = LTM.Id AND EmployeeGradeId = @rEmployeeGradeId
       WHERE 
			   LTM.IsObsolete = 0
			AND LTM.OrgId = @rOrgId   AND IsLWP = 0
END
GO
PRINT N'Altering [sHrm].[pLeaveTypeGradeSettingAdd]...';


GO
ALTER PROCEDURE [sHrm].[pLeaveTypeGradeSettingAdd]
           
           @rLeaveTypeId int,
           @rEmployeeGradeId int,
           @rNoOfLeave int,
           @rMaxInMonth decimal(5,2),          
           @rOrgId smallint,
		   @rLeaveCarryForwordRuleId int=1
        
AS
SET NOCOUNT ON

BEGIN

	BEGIN TRY
	
	BEGIN TRANSACTION

	DECLARE @lNextNumber bigint	  
	
	IF EXISTS(SELECT 1 FROM sHrm.tLeaveTypeGradeSettings WHere LeaveTypeId = @rLeaveTypeId AND OrgId = @rOrgId AND EmployeeGradeId = @rEmployeeGradeId)
	DELETE FROM sHrm.tLeaveTypeGradeSettings WHere LeaveTypeId = @rLeaveTypeId AND OrgId = @rOrgId AND EmployeeGradeId = @rEmployeeGradeId
	
	BEGIN		     
		   INSERT INTO [sHrm].[tLeaveTypeGradeSettings]
           (
            [OrgId]
           ,[LeaveTypeId]
           ,[EmployeeGradeId]
           ,[NoOfLeave]
           ,[MaxInMonth]
		   ,[LeaveCarryForwordRuleId]
           )
     VALUES
           (
            @rOrgId
           ,@rLeaveTypeId
           ,@rEmployeeGradeId
           ,@rNoOfLeave
           ,@rMaxInMonth
		   ,@rLeaveCarryForwordRuleId
           )		 
          END
	   SELECT 1 AS Result	   
	   COMMIT TRANSACTION
	   		
	END TRY	
	BEGIN CATCH
		ROLLBACK TRANSACTION
		SELECT -1 AS RESULT
	END CATCH
	
END
GO
PRINT N'Altering [sHrm].[pRptJoiningCertificateGet]...';


GO

ALTER PROCEDURE [sHrm].[pRptJoiningCertificateGet]  
 
  @rEmployeeId [bigint]= 0, 
  @rBuilder1 nvarchar(max)='',  
  @rBuilder2 nvarchar(max)='',    
  @rOrgId smallint,  
  @rCode nvarchar(20)='',  
  @rFromNo int=0,  
  @rToNo int=0,  
  @rFromDate date='',  
  @rToDate date='',  
  @rName nvarchar(20)=''

WITH EXECUTE AS CALLER  
AS  
SET NOCOUNT ON  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  
  
BEGIN  
DECLARE @lIsOnPrePrinted bit = 0  
  
DECLARE @lConfigValue nvarchar(50)  
DECLARE @lIsDisplayImageHeader bit=0  
  
SELECT @lConfigValue = ItemValue FROM sConfig.tConfigurationSetup WHERE ConfigurationId = 182 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  
   @rBuilder1 AS Builder1,  
   @rBuilder2 AS Builder2,  
   EM.Id AS EmployeeId,
   CJ.Number,
   EM.EmployeeName,
   EM.FirstName,  
   EM.MiddleName,  
   EM.LastName,      
   EM.Code,    
   EM.ShortName,  
   EM.OrgID,  
   EM.DepartmentId,  
   EM.DepartmentName,  
   EM.DesignationId,  
   EM.Designation,  
   EM.EmployeePhoto, 
   EM.DateOfJoining, 
   @lIsOnPrePrinted AS IsOnPrePrinted,  
   @lIsDisplayImageHeader AS IsDisplayImageHeader,  
   OI.CertificateHeaderA4 ,  
   CJ.Remark,  
   ISNULL(CJ.CertificateDate,'') AS CertificateDate,  
   EM.MaritalStatusId,  
   CJ.PayScale,  
   CJ.AgreementPeriod,
   CJ.CertificateRemark,
   OM.Title As OrgTitle,
   CJ.CreatedDateTime As CreatedDateTime,
   U.DisplayTitle As UserDisplayName,
   EDM.Title AS EmployeeDesignation,
   OM.DisplayTitle As ShortTitle,
   DM.Title AS DepartmentName1,
   EM.MobileNumber,
   --EC.PresentStreetOne+'-'+EC.PresentStreetTwo+ '-' +EC.PresentStreetThree AS LocalAddress,
    EC.PresentStreetOne  + CASE WHEN EC.PresentStreetOne = '' OR (EC.PresentStreetTwo = '' AND EC.PresentStreetThree = '') Then '' ELSE '-' END 
   		    + EC.PresentStreetTwo + CASE WHEN EC.PresentStreetTwo = '' OR EC.PresentStreetThree = '' Then '' ELSE '-' END	
			+ EC.PresentStreetThree AS LocalAddress,
   --EC.PresentCity+ '-'+EC.PresentState+'-'+EC.PresentZipCode+'-'+EC.PresentContactNo As LocalAddress1,
	EC.PresentCity  + CASE WHEN EC.PresentCity = '' OR (EC.PresentState = '' AND EC.PresentZipCode = '' AND EC.PresentContactNo = '') Then '' ELSE '-' END 
		+ EC.PresentState + CASE WHEN EC.PresentState = '' OR (EC.PresentZipCode = '' AND EC.PresentContactNo = '') Then '' ELSE '-' END	
		+ EC.PresentZipCode + CASE WHEN EC.PresentZipCode = '' OR EC.PresentContactNo = '' Then '' ELSE '-' END
		+ EC.PresentContactNo AS LocalAddress1,
   ISNULL(EDM1.Title,'Admin') AS UserDesignation,
   --Replace(Replace(RTrim(Ltrim(EC.PresentCity +' '+ EC.PresentState +' '+ EC.PresentZipCode)),'  ',' '),' ','-') AS LocalAddress2
   EC.PresentCity  + CASE WHEN EC.PresentCity = '' OR (EC.PresentState = '' AND EC.PresentZipCode = '') Then '' ELSE '-' END 
   		    + EC.PresentState + CASE WHEN EC.PresentState = '' OR EC.PresentZipCode = '' Then '' ELSE '-' END	
			+ EC.PresentZipCode AS LocalAddress2,
	ISNULL(CJ.DateOfJoining,'') As CertificateDateOfJoining,
	EM.GenderId

 FROM   
        
	  sHrm.tCertificateJoining CJ   
      INNER JOIN [sHrm].[vExtEmployeeMaster] EM  ON EM.EmployeeId=CJ.EmployeeId 
      INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=EM.OrgId   
      INNER JOIN  sSecurity.tUser U ON U.Id=CJ.CreatedUserId 
	  --AND U.SourceTypeId = 95
      INNER JOIN  sHrm.tEmployeeJobDetail EJD ON EJD.EmployeeId=EM.Id
	  LEFT JOIN  sHrm.tEmployeeDesignationMaster EDM ON EDM.Id=EJD.EmployeeDesignationId  --added
	  INNER JOIN  sSetup.tDepartmentMaster DM ON DM.Id=CJ.DepartmentId
	  LEFT JOIN  sHrm.tEmployeeCommunications EC ON EC.EmployeeId=CJ.EmployeeId  --added
	  LEFT JOIN sSetup.tOrgImages OI ON OI.OrgId = OM.Id 
      LEFT JOIN  sHrm.tEmployeeJobDetail EJD1 ON EJD1.EmployeeId=U.SourceId
	  LEFT JOIN  sHrm.tEmployeeDesignationMaster EDM1 ON EDM1.Id=EJD1.EmployeeDesignationId
 WHERE    
 
    ((EM.Code LIKE @rCode)OR (@rCode = '' ))     
    AND ((EM.EmployeeId = @rEmployeeId) OR (@rEmployeeId = 0))    
    AND ((EM.EmployeeName like @rName) OR (ISNULL(@rName,'') = ''))
    AND ((CJ.Number >= @rFromNo) OR (ISNULL(@rFromNo,0)=0))  
    AND ((CJ.Number <= @rToNo) OR (ISNULL(@rToNo,0)=0))  
    AND ((CJ.CertificateDate >= @rFromDate) OR (ISNULL(@rFromDate,'')=''))  
    AND ((CJ.CertificateDate <= @rToDate) OR (ISNULL(@rToDate,'')=''))    
    AND  EM.OrgId = @rOrgId
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 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
 
	   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].[ppImportStudent_Update_Dynamicly]...';


GO
ALTER proc [sStudent].[ppImportStudent_Update_Dynamicly]
@rOrgId int,
@rCreatedUserId bigint,
@rCode nvarchar(20) = '', 
@rFirstName nvarchar(100) = '',
@rMiddleName nvarchar(100) = '',
@rLastName nvarchar(100) = '',
@rGender nvarchar(20) = '',
@rAdmissionStandard nvarchar(100) = '',
@rAdmissionCategory nvarchar(100) = '',
@rAdmissionYear nvarchar(10) = '',
@rAdmissionDate datetime = '',
@rLeftStandard nvarchar(100) = '',
@rLeftDate datetime = '',
@rLeftYear nvarchar(10) = '',
@rLeftReason nvarchar(200) = '',
@rPreviousSchool nvarchar(200) = '',
@rDateOfBirth datetime = '',
@rBirthPlace nvarchar(150) = '',
@rBloodGroup nvarchar(20) = '',
@rBirthTaluka nvarchar(150) = '',
@rBirthDistrict nvarchar(150) = '',
@rFamilySubCast nvarchar(100) = '',
@rFamilyCast nvarchar(100) = '',
@rCastCatagory nvarchar(50) = '',
@rMotherTongue nvarchar(100) = '',
@rNationality nvarchar(50) = '',
@rReligion nvarchar(50) = '',
@rHobby nvarchar(50) = '',
@rFatherName nvarchar(100)='',
@rFatherContactNo nvarchar(50)='',
@rFatherDateOfBirth datetime = '',
@rFatherOccupation nvarchar(50)='',
@rFatherQualification nvarchar(50)='',
@rFatherMail nvarchar(50)='',
@rFatherOfficeAddress nvarchar(500)='',
@rFatherIncome int=0,
@rMotherDateOfBirth datetime = '',
@rMothername nvarchar(100)='',
@rMotherContactNo nvarchar(50)='',
@rMotherOccupation nvarchar(50)='',
@rMotherQualification nvarchar(50)='',
@rMotherMail nvarchar(50)='',
@rMotherOfficeAddress nvarchar(100)='',
@rMotherIncome int=0,
@rRelationshipName nvarchar(100)='',
@rRelativeName nvarchar(100)='',
@rRelativeDateOfBirth datetime='',
@rRelativeOccupation nvarchar(50)='',
@rRelativeQualification	nvarchar(50)='',
@rRelativeContactNo	nvarchar(50)='',
@rRelativeMail nvarchar(50)='',
@rRelativeOfficeAddress nvarchar(500)='',
@rRelativeIncome int=0,
@rPresentStreetOne nvarchar(150)='',
@rPresentStreetTwo nvarchar(150)='',
@rPresentStreetThree nvarchar(150)='',
@rPresentCity nvarchar(50)='',
@rPresentZipCode nvarchar(20)='',
@rPresentState nvarchar(50)='',
@rPresentCountry nvarchar(50)='',
@rPresentContactNo nvarchar(20)='', 
@rParentEmail nvarchar(50)='',
@rStudentContactNo  nvarchar(50)='',
@rStudentEmail nvarchar(50)='', 
@rAccountNumber nvarchar(20)='',
@rBankName nvarchar(100)=0,
@rBranch nvarchar(100)='', 
@rBankIFSCCode  nvarchar(20)='',
@rAdhaarNumber nvarchar(20)='',
@rValidate varchar(250)='' ,
@rOrgGroupId int,
@rBirthState  nvarchar(150)='',
@rUIDNumber nvarchar(50)='', 
@rBirthCountry nvarchar(150)='',
@rFatherOfficeContactNo nvarchar(50)='',
@rMotherOfficeContactNo varchar(50)='',
@rWhatsAppContactNo nvarchar(20)=''

as
begin	
	IF(@rAdmissionDate='') 
	SET @rAdmissionDate = NULL
	IF(@rDateOfBirth='') 
	SET @rDateOfBirth = NULL
	IF(@rFatherDateOfBirth='') 
	SET @rFatherDateOfBirth = NULL
	IF(@rMotherDateOfBirth='') 
	SET @rMotherDateOfBirth = NULL
	IF(@rRelativeDateOfBirth='') 
	SET @rRelativeDateOfBirth = NULL
	
	
	declare @StudentId bigint = 0
	select @StudentId = Id from sStudent.tStudentMaster where Code = @rCode and OrgID = @rOrgId
	select @StudentId = isnull(@StudentId,0)
	
	declare @genderid int,@AdmissionStandardid int,@LeftStandardid int,@nationalityid int,@religionid int, 
	 @castcategoryid int,@admissioncategoryid int,@bloodgroupid int 
	
	select @rGender = case when upper(@rGender) = 'M' then 'Male'
	when  upper(@rGender) = 'MALE' then 'Male'
	when upper(@rGender) ='FEMALE' then 'Female'
	when upper(@rGender) = 'F' then 'Female' when upper(@rGender) = 'N' then 'Not Known' else 'Not Known' end
	 
	select @genderid = isnull(Id,0) from sCommon.tGenderMaster where Title = @rGender
	
	select @AdmissionStandardid = isnull(Id,0) from sSetup.tClassMaster where Title = @rAdmissionStandard and OrgId = @rOrgId
 
	select @LeftStandardid = isnull(Id,0) from sSetup.tClassMaster where Title = @rLeftStandard and OrgId = @rOrgId
	
	select @nationalityid = isnull(Id,0) from sCommon.tCountryMaster where Nationality = @rNationality and OrgGroupId = @rOrgGroupId
	 
	 select @religionid = isnull(Id,0) from sCommon.tReligionMaster where Title = @rReligion  and OrgGroupId = @rOrgGroupId
	 
	  select @castcategoryid = isnull(Id,0) from sCommon.tCastCategoryMaster where Title = @rcastcatagory and OrgGroupId = @rOrgGroupId
	  
	  select @admissioncategoryid = isnull(Id,0) from sStudent.tAdmissionCategoryMaster where Title = @radmissioncategory and OrgGroupId = @rOrgGroupId
	 
	 select @bloodgroupid = isnull(Id,0) from sCommon.tBloodGroupMaster  where Title = @rBloodGroup  
	  
	select @genderid = isnull(@genderid,0),@AdmissionStandardid = isnull(@AdmissionStandardid,0),@LeftStandardid = isnull(@LeftStandardid,0),
	@nationalityid = isnull(@nationalityid,0),@religionid = isnull(@religionid,0),@castcategoryid = isnull(@castcategoryid,0),
	@admissioncategoryid = isnull(@admissioncategoryid,0),@bloodgroupid = isnull(@bloodgroupid,0) 
	
	declare @lNextNumber bigint,@Title varchar(100)=''
	
	 
	 
	if @rFirstName <> ''
	select @rFirstName = [sSetup].[fPortalXMLTagsReplaceReverse](@rFirstName)
	if @rMiddleName <> ''
	select @rMiddleName = [sSetup].[fPortalXMLTagsReplaceReverse](@rMiddleName)
	if @rLastName <> ''
	select @rLastName = [sSetup].[fPortalXMLTagsReplaceReverse](@rLastName)
	 if @rAdmissionDate <> ''
	select @rAdmissionDate = [sSetup].[fPortalXMLTagsReplaceReverse](@rAdmissionDate)
	 if @rAdmissionYear <> ''
	select @rAdmissionYear = [sSetup].[fPortalXMLTagsReplaceReverse](@rAdmissionYear)
	 if @rLeftDate <> ''
	select @rLeftDate = [sSetup].[fPortalXMLTagsReplaceReverse](@rLeftDate)
	if @rLeftYear <> ''
	select @rLeftYear= [sSetup].[fPortalXMLTagsReplaceReverse](@rLeftYear)
	if @rLeftReason <> ''
	select @rLeftReason= [sSetup].[fPortalXMLTagsReplaceReverse](@rLeftReason)
	if @rPreviousSchool <> ''
	select @rPreviousSchool= [sSetup].[fPortalXMLTagsReplaceReverse](@rPreviousSchool)
	if @rDateOfBirth <> ''
	select @rDateOfBirth= [sSetup].[fPortalXMLTagsReplaceReverse](@rDateOfBirth)
	if @rBirthPlace <> ''
	select @rBirthPlace= [sSetup].[fPortalXMLTagsReplaceReverse](@rBirthPlace)
	if @rBirthTaluka<> ''
	select @rBirthTaluka= [sSetup].[fPortalXMLTagsReplaceReverse](@rBirthTaluka)
	if @rBirthDistrict <> ''
	select @rBirthDistrict= [sSetup].[fPortalXMLTagsReplaceReverse](@rBirthDistrict)
	if @rFamilyCast <> ''
	select @rFamilyCast= [sSetup].[fPortalXMLTagsReplaceReverse](@rFamilyCast)
	if @rFamilySubCast <> ''
	select @rFamilySubCast= [sSetup].[fPortalXMLTagsReplaceReverse](@rFamilySubCast)
	if @rMotherTongue <> ''
	select @rMotherTongue= [sSetup].[fPortalXMLTagsReplaceReverse](@rMotherTongue)
	if @rHobby <> ''
	select @rHobby= [sSetup].[fPortalXMLTagsReplaceReverse](@rHobby)
	if @rFatherName<> ''
	select @rFatherName= [sSetup].[fPortalXMLTagsReplaceReverse](@rFatherName)
	if @rFatherContactNo<> ''
	select @rFatherContactNo= [sSetup].[fPortalXMLTagsReplaceReverse](@rFatherContactNo)
	if @rFatherDateOfBirth<> ''
	select @rFatherDateOfBirth= [sSetup].[fPortalXMLTagsReplaceReverse](@rFatherDateOfBirth)
	if @rFatherOccupation<> ''
	select @rFatherOccupation= [sSetup].[fPortalXMLTagsReplaceReverse](@rFatherOccupation)
	if @rFatherQualification<> ''
	select @rFatherQualification= [sSetup].[fPortalXMLTagsReplaceReverse](@rFatherQualification)
	if @rFatherMail<> ''
	select @rFatherMail= [sSetup].[fPortalXMLTagsReplaceReverse](@rFatherMail)
	if @rFatherOfficeAddress<> ''
	select @rFatherOfficeAddress= [sSetup].[fPortalXMLTagsReplaceReverse](@rFatherOfficeAddress)
	if @rFatherIncome<> ''
	select @rFatherIncome= [sSetup].[fPortalXMLTagsReplaceReverse](@rFatherIncome)
	if @rMothername<> ''
	select @rMothername= [sSetup].[fPortalXMLTagsReplaceReverse](@rMothername)
	if @rMotherContactNo<> ''
	select @rMotherContactNo= [sSetup].[fPortalXMLTagsReplaceReverse](@rMotherContactNo)
	if @rMotherDateOfBirth<> ''
	select @rMotherDateOfBirth= [sSetup].[fPortalXMLTagsReplaceReverse](@rMotherDateOfBirth)
	if @rMotherOccupation<> ''
	select @rMotherOccupation= [sSetup].[fPortalXMLTagsReplaceReverse](@rMotherOccupation)
	if @rMotherQualification <> ''
	select @rMotherQualification= [sSetup].[fPortalXMLTagsReplaceReverse](@rMotherQualification)
	if @rMotherMail<> ''
	select @rMotherMail= [sSetup].[fPortalXMLTagsReplaceReverse](@rMotherMail)
	if @rMotherOfficeAddress<> ''
	select @rMotherOfficeAddress= [sSetup].[fPortalXMLTagsReplaceReverse](@rMotherOfficeAddress)
	if @rMotherIncome<> ''
	select @rMotherIncome= [sSetup].[fPortalXMLTagsReplaceReverse](@rMotherIncome)
	if @rRelationshipName <> ''
	select @rRelationshipName= [sSetup].[fPortalXMLTagsReplaceReverse](@rRelationshipName)
	if @rRelativeName <> ''
	select @rRelativeName= [sSetup].[fPortalXMLTagsReplaceReverse](@rRelativeName)
	if @rRelativeDateOfBirth <> ''
	select @rRelativeDateOfBirth= [sSetup].[fPortalXMLTagsReplaceReverse](@rRelativeDateOfBirth)
	if @rRelativeOccupation <> ''
	select @rRelativeOccupation= [sSetup].[fPortalXMLTagsReplaceReverse](@rRelativeOccupation)
	if @rRelativeQualification <> ''
	select @rRelativeQualification= [sSetup].[fPortalXMLTagsReplaceReverse](@rRelativeQualification)
	if @rRelativeContactNo<> ''
	select @rRelativeContactNo= [sSetup].[fPortalXMLTagsReplaceReverse](@rRelativeContactNo)
	if @rRelativeMail<> ''
	select @rRelativeMail= [sSetup].[fPortalXMLTagsReplaceReverse](@rRelativeMail)
	if @rRelativeOfficeAddress <> ''
	select @rRelativeOfficeAddress= [sSetup].[fPortalXMLTagsReplaceReverse](@rRelativeOfficeAddress)
	if @rRelativeIncome <> ''
	select @rRelativeIncome= [sSetup].[fPortalXMLTagsReplaceReverse](@rRelativeIncome)
	 
	if @rPresentStreetOne <> ''
	select @rPresentStreetOne = [sSetup].[fPortalXMLTagsReplaceReverse](@rPresentStreetOne)
	if @rPresentStreetTwo <> ''
	select @rPresentStreetTwo = [sSetup].[fPortalXMLTagsReplaceReverse](@rPresentStreetTwo)
	if @rPresentStreetThree <> ''
	select @rPresentStreetThree = [sSetup].[fPortalXMLTagsReplaceReverse](@rPresentStreetThree)
	 
	if @rPresentZipCode <> ''
	select @rPresentZipCode = [sSetup].[fPortalXMLTagsReplaceReverse](@rPresentZipCode)
	if @rPresentState <> ''
	select @rPresentState = [sSetup].[fPortalXMLTagsReplaceReverse](@rPresentState)
	if @rPresentCountry <> ''
	select @rPresentCountry = [sSetup].[fPortalXMLTagsReplaceReverse](@rPresentCountry)
	if @rPresentCity <> ''
	select @rPresentCity = [sSetup].[fPortalXMLTagsReplaceReverse](@rPresentCity)
	if @rPresentContactNo <> ''
	select @rPresentContactNo = [sSetup].[fPortalXMLTagsReplaceReverse](@rPresentContactNo)
	if @rPresentState <> ''
	select @rPresentState = [sSetup].[fPortalXMLTagsReplaceReverse](@rPresentState)
	if @rAccountNumber <> ''
	select @rAccountNumber = [sSetup].[fPortalXMLTagsReplaceReverse](@rAccountNumber)
	if @rBankName <> ''
	select @rBankName = [sSetup].[fPortalXMLTagsReplaceReverse](@rBankName)
	if @rBranch <> ''
	select @rBranch = [sSetup].[fPortalXMLTagsReplaceReverse](@rBranch)
	if @rParentEmail<> ''
	select @rParentEmail = [sSetup].[fPortalXMLTagsReplaceReverse](@rParentEmail)
	if @rUIDNumber <> ''
	select @rUIDNumber = [sSetup].[fPortalXMLTagsReplaceReverse](@rUIDNumber)
	if @rAdhaarNumber <> ''
	select @rAdhaarNumber = [sSetup].[fPortalXMLTagsReplaceReverse](@rAdhaarNumber)
	  if @rStudentContactNo <> ''
	select @rStudentContactNo = [sSetup].[fPortalXMLTagsReplaceReverse](@rStudentContactNo)
	if @rStudentEmail <> ''
	select @rStudentEmail= [sSetup].[fPortalXMLTagsReplaceReverse](@rStudentEmail)
	if @rBankIFSCCode <> ''
	select @rBankIFSCCode = [sSetup].[fPortalXMLTagsReplaceReverse](@rBankIFSCCode)
	if @rBirthState<> ''
	select @rBirthState= [sSetup].[fPortalXMLTagsReplaceReverse](@rBirthState)
	if @rBirthCountry<> ''
	select @rBirthCountry= [sSetup].[fPortalXMLTagsReplaceReverse](@rBirthCountry)
	if @rFatherOfficeContactNo<> ''
	select @rFatherOfficeContactNo= [sSetup].[fPortalXMLTagsReplaceReverse](@rFatherOfficeContactNo)
	if @rMotherOfficeContactNo<> ''
	select @rMotherOfficeContactNo= [sSetup].[fPortalXMLTagsReplaceReverse](@rMotherOfficeContactNo)
	if @rWhatsAppContactNo<> ''
	select @rWhatsAppContactNo= [sSetup].[fPortalXMLTagsReplaceReverse](@rWhatsAppContactNo)
	
	begin

		UPDATE sStudent.tStudentMaster
		SET 
		FirstName = case when isnull(@rFirstName,'') <> '' then @rFirstName else FirstName end
		,MiddleName   = case when isnull(@rMiddleName,'') <> '' then @rMiddleName else MiddleName end
		,LastName     = case when isnull(@rLastName,'') <> '' then @rLastName else LastName end
		--,StatusId     = case when isnull(@statusid,0) <> 0 then @statusid else StatusId end
		 ,UpdatedUserId = @rCreatedUserId
		,UpdatedDateTime = sCommon.fGetDate()
		WHERE Id = @StudentId and OrgId = @rOrgId
		
		IF NOT EXISTS(Select * from sStudent.tStudentAdmission where StudentId= @StudentId) 
		BEGIN
			INSERT INTO sStudent.tStudentAdmission(StudentId,CreatedUserId,CreatedDateTime) Values (@StudentId, @rCreatedUserId, sCommon.fGetDate())
		END

		SELECT @rLeftDate,@LeftStandardid			
		UPDATE sStudent.tStudentAdmission
		SET 
			AdmissionStandardId = case when isnull(@AdmissionStandardid,0) <> 0 then @AdmissionStandardid else AdmissionStandardId end,	 
			AdmissionYear = case when isnull(@rAdmissionYear,0) <> 0 then @rAdmissionYear else AdmissionYear end,
			
			AdmissionDate = case when isnull(@rAdmissionDate,'') <> '' then @rAdmissionDate else AdmissionDate end,
			LeftStandardId =	case when isnull(@LeftStandardid,'') <> '' then @LeftStandardid else LeftStandardId end,
			LeftDate = case when isnull(@rLeftDate,'') <> '' then @rLeftDate else LeftDate end,	 
			LeftYear = case when isnull(@rLeftYear,0) <> 0 then @rLeftYear else LeftYear end,
			LeftReason = case when isnull(@rLeftReason,'') <> '' then @rLeftReason else LeftReason end,
			PreviousSchool = case when isnull(@rPreviousSchool,'') <> '' then @rPreviousSchool else PreviousSchool end,
			UpdatedUserId = @rCreatedUserId,
			UpdatedDateTime = sCommon.fGetDate()			   
		WHERE StudentId = @StudentId 
	 

		IF NOT EXISTS(Select * from sStudent.tStudentOther where StudentId= @StudentId) 
		BEGIN
			INSERT INTO sStudent.tStudentOther (StudentId, CreatedUserId, CreatedDateTime) Values (@StudentId, @rCreatedUserId, sCommon.fGetDate())
		END

		Update sStudent.tStudentOther
		set  
		GenderId = case when isnull(@genderid,0) <> 0 then @genderid else GenderId  end,
		ReligionId =  case when isnull(@religionId,0) <> 0 then @religionId else ReligionId  end,
		 BloodGroupId =  case when isnull(@bloodgroupid,0) <> 0 then @bloodgroupid else BloodGroupId  end,
		DateOfBirth =  case when isnull(@rDateOfBirth,'') <> '' then @rDateOfBirth else DateOfBirth  end,
		BirthPlace = case when isnull(@rBirthPlace,'') <> '' then @rBirthPlace else BirthPlace  end,
		BirthTaluka = case when isnull(@rBirthTaluka,'') <> '' then @rBirthTaluka else BirthTaluka  end,
			BirthDistrict = case when isnull(@rBirthDistrict,'') <> '' then @rBirthDistrict else BirthDistrict  end,
		 UIDNumber =  case when isnull(@rUIDNumber,'') <> '' then @rUIDNumber else UIDNumber  end,
		UpdatedUserId = @rCreatedUserId,
		UpdatedDateTime = sCommon.fGetDate(),
		FamilyCast =case when isnull(@rFamilyCast,'') <> '' then @rFamilyCast else FamilyCast  end,
		AdhaarNumber =case when isnull(@rAdhaarNumber,'') <> '' then @rAdhaarNumber else AdhaarNumber  end,
		FamilySubCast = case when isnull(@rFamilySubCast,'') <> '' then @rFamilySubCast else FamilySubCast  end,
		MotherTongue = case when isnull(@rMotherTongue,'') <>'' then @rMotherTongue else MotherTongue  end,
		NationalityId = case when isnull(@NationalityId,0) <> 0 then @NationalityId else NationalityId  end,
		 Hobby = case when isnull(@rHobby,'') <> '' then @rHobby else Hobby  end,
		CastCategoryId =case when isnull(@castcategoryid,0) <> 0 then @castcategoryid else CastCategoryId  end,
			BirthState = case when isnull(@rBirthState,'') <> '' then @rBirthState else BirthState  end,
				BirthCountry = case when isnull(@rBirthCountry,'') <> '' then @rBirthCountry else BirthCountry  end
		where StudentId = @StudentId 
		
	
		IF NOT EXISTS(Select * from sStudent.tStudentFamily where StudentId= @StudentId) 
		BEGIN
			INSERT INTO sStudent.tStudentFamily (StudentId, CreatedUserId, CreatedDateTime) Values (@StudentId, @rCreatedUserId, sCommon.fGetDate())
		END

  		UPDATE sStudent.tStudentFamily
		SET 
	      
		   FatherName = case when isnull(@rFatherName,'') <> '' then @rFatherName else FatherName end 
		  ,FatherDateOfBirth =  case when isnull(@rFatherDateOfBirth,'') <> '' then @rFatherDateOfBirth else FatherDateOfBirth end 
		  ,FatherOccupation = case when isnull(@rFatherOccupation,'') <> '' then @rFatherOccupation else FatherOccupation end 
		  ,FatherQualification = case when isnull(@rFatherQualification,'') <> '' then @rFatherQualification else FatherQualification end 
		  ,FatherContactNo =  case when isnull(@rFatherContactNo,'') <> '' then @rFatherContactNo else FatherContactNo end 
		  ,FatherMail =  case when isnull(@rFatherMail,'') <> '' then @rFatherMail else FatherMail end 
		  ,FatherOfficeAddress = case when isnull(@rFatherOfficeAddress,'') <> '' then @rFatherOfficeAddress else FatherOfficeAddress end 
		  ,FatherIncome = case when isnull(@rFatherIncome,'') <> '' then @rFatherIncome else FatherIncome end 
		  ,MotherName = case when isnull(@rMotherName,'') <> '' then @rMotherName else MotherName end  
		  ,MotherDateOfBirth =  case when isnull(@rMotherDateOfBirth,'') <> '' then @rMotherDateOfBirth else MotherDateOfBirth  end   
		  ,MotherOccupation =  case when isnull(@rMotherOccupation,'') <> '' then @rMotherOccupation else MotherOccupation  end   
		  ,MotherQualification =  case when isnull(@rMotherQualification,'') <> '' then @rMotherQualification else MotherQualification  end      
		   ,MotherContactNo = case when isnull(@rMotherContactNo,'') <> '' then @rMotherContactNo else MotherContactNo end
		   ,MotherMail = case when isnull(@rMotherMail,'') <> '' then @rMotherMail else MotherMail end
		   ,MotherOfficeAddress = case when isnull(@rMotherOfficeAddress,'') <> '' then @rMotherOfficeAddress else MotherOfficeAddress end
		   ,MotherIncome = case when isnull(@rMotherIncome,'') <> '' then @rMotherIncome else MotherIncome end
		   ,RelationshipName = case when isnull(@rRelationshipName,'') <> '' then @rRelationshipName else RelationshipName end
		   ,RelativeName = case when isnull(@rRelativeName,'') <> '' then @rRelativeName else RelativeName end
			,RelativeDateOfBirth = case when isnull(@rRelativeDateOfBirth,'') <> '' then @rRelativeDateOfBirth else RelativeDateOfBirth end 
			,RelativeOccupation = case when isnull(@rRelativeOccupation,'') <> '' then @rRelativeOccupation else RelativeOccupation end 
			,RelativeQualification = case when isnull(@rRelativeQualification,'') <> '' then @rRelativeQualification else RelativeQualification end 
			,RelativeContactNo = case when isnull(@rRelativeContactNo,'') <> '' then @rRelativeContactNo else RelativeContactNo end 
			,RelativeMail = case when isnull(@rRelativeMail,'') <> '' then @rRelativeMail else RelativeMail end 
			,RelativeOfficeAddress = case when isnull(@rRelativeOfficeAddress,'') <> '' then @rRelativeOfficeAddress else RelativeOfficeAddress end 
			,RelativeIncome = case when isnull(@rRelativeIncome,'') <> '' then @rRelativeIncome else RelativeIncome end 
			,FatherOfficeContactNo = case when isnull(@rFatherOfficeContactNo,'') <> '' then @rFatherOfficeContactNo else FatherOfficeContactNo end 
			,MotherOfficeContactNo = case when isnull(@rMotherOfficeContactNo,'') <> '' then @rMotherOfficeContactNo else MotherOfficeContactNo end 
		  ,[UpdatedUserId] = @rCreatedUserId
		  ,[UpdatedDateTime] =  sCommon.fGetDate() 
		WHERE StudentId = @StudentId
			

		IF NOT EXISTS(Select * from sStudent.tStudentCommunication where StudentId= @StudentId) 
		BEGIN
			INSERT INTO sStudent.tStudentCommunication (StudentId, CreatedUserId, CreatedDateTime) Values (@StudentId, @rCreatedUserId, sCommon.fGetDate())
		END

  		UPDATE sStudent.tStudentCommunication
		SET 
	      
		   [PresentStreetOne] = case when isnull(@rPresentStreetOne,'') <> '' then @rPresentStreetOne else [PresentStreetOne] end 
		  ,[PresentStreetTwo] =  case when isnull(@rPresentStreetTwo,'') <> '' then @rPresentStreetTwo else [PresentStreetTwo] end 
		  ,[PresentStreetThree] = case when isnull(@rPresentStreetThree,'') <> '' then @rPresentStreetThree else [PresentStreetThree] end 
		  ,[PresentCountry] = case when isnull(@rPresentCountry,'') <> '' then @rPresentCountry else [PresentCountry] end 
		  ,[PresentState] =  case when isnull(@rPresentState,'') <> '' then @rPresentState else [PresentState] end 
		  ,[PresentCity] =  case when isnull(@rPresentCity,'') <> '' then @rPresentCity else [PresentCity] end 
		  ,PresentZipCode = case when isnull(@rPresentZipCode,'') <> '' then @rPresentZipCode else PresentZipCode end 
		  ,PresentContactNo = case when isnull(@rPresentContactNo,'') <> '' then @rPresentContactNo else PresentContactNo end 
		  ,ParentEmail = case when isnull(@rParentEmail,'') <> '' then @rParentEmail else ParentEmail end  
		  ,StudentContactNo =  case when isnull(@rStudentContactNo,'') <> '' then @rStudentContactNo else StudentContactNo  end   
		  ,StudentEmail =  case when isnull(@rStudentEmail,'') <> '' then @rStudentEmail else StudentEmail  end   
		   ,[UpdatedUserId] = @rCreatedUserId
		  ,[UpdatedDateTime] =  sCommon.fGetDate() 
		  ,WhatsAppContactNo =  case when isnull(@rWhatsAppContactNo,'') <> '' then @rWhatsAppContactNo else WhatsAppContactNo  end   
		WHERE StudentId = @StudentId
		

		IF NOT EXISTS(Select * from sStudent.tStudentAdditional where StudentId= @StudentId) 
		BEGIN
			INSERT INTO sStudent.tStudentAdditional (StudentId) Values (@StudentId)
		END

		UPDATE sStudent.tStudentAdditional
		SET
			BankBranch = case when isnull(@rBranch,'') <> '' then @rBranch else BankBranch end 
		  ,BankAccountNumber = case when isnull(@rAccountNumber,'') <> '' then @rAccountNumber else BankAccountNumber end 
		  ,BankName = case when isnull(@rBankName,'') <> '' then @rBankName else BankName end 
		  ,BankIFSCCode = case when isnull(@rBankIFSCCode,'') <> '' then @rBankIFSCCode else BankIFSCCode end 
		  
		where StudentId=@StudentId
		
	end	
end
GO
PRINT N'Altering [sStudent].[pRptStudentIcardGet]...';


GO
ALTER PROCEDURE [sStudent].[pRptStudentIcardGet]  
  @rBuilder1 nvarchar(max)='',  
  @rBuilder2 nvarchar(max)='',   
  @rOrgId smallint,  
  @rBatchId int,  
  @rStrCode nvarchar(max),  
  @rClassId int,  
  @rDivisionId int,  
  @rStudentId bigint,  
  @rFromRollNo int,  
  @rToRollNo int,  
  @rStatusId int,  
  @rDateOfBirth int,  
  @rDepartmentId int,  
  @rHouseGroupId int=0,  
  @rIsShowBarcode bit=0,  
  @rOrgGroupBatchId int,
  @rHouseId int=0  
 WITH EXECUTE AS CALLER  
AS  
SET NOCOUNT ON  
      
  
BEGIN   
  
DECLARE @lIcardBorder nvarchar(10)  
DECLARE @lSUIdBarcode nvarchar(50)  
--DECLARE @lIsBarcodeBasedOnSUId bit = 0  
DECLARE @lOrgGroupId int = 0  
Declare @lIDConfigValue nvarchar(50)=''  
SELECT @lOrgGroupId=OrgGroupId FROM sSetup.tOrganizationMaster WHERE Id = @rOrgId  
  
SELECT @lIDConfigValue =ItemValue From sConfig.tConfigurationSetup  
WHERE ConfigurationId = 236 AND SourceId = @lOrgGroupId  
  
SELECT @lIcardBorder = ItemValue FROM sConfig.tConfigurationSetup WHERE configurationId = 130 AND SourceTypeId = 1016 AND SourceId = @rOrgId --ItemKey = 'sConfig.tConfigurationSetup'   
SELECT @lSUIdBarcode = ItemValue FROM sConfig.tConfigurationSetup WHERE ConfigurationId = 168 AND SourceTypeId = 2010 AND SourceId = @lOrgGroupId  
   
 --IF (@lSUIdBarcode IS NULL) OR (@lSUIdBarcode = 'False')  
 --SET @lIsBarcodeBasedOnSUId = 0  
 --ELSE  
 --SET @lIsBarcodeBasedOnSUId = 1   
   
 SELECT      
   SD.AcademicId,  
   SD.StudentId,   
   SD.ClassId,  
   SD.DivisionId,  
   SD.ClassName,  
   SD.DivisionName,  
   SD.ClassDivision,  
   SD.Batch,  
   SD.Code,  
   SD.Number AS StudentNumber,  
   SD.RegistrationNumber,   
   SD.RollNo,      
   SD.FirstName,  
   SD.MiddleName,     
   SD.LastName,  
   SF.FatherName,
   [sStudent].[fStudentNameGet](SD.FirstName , SD.MiddleName, SD.LastName,OM.StudentNameFormatId ) AS Name,   
   --SD.FirstName + ' ' + SD.MiddleName + ' ' + SD.LastName As Name,     
   SD.StatusId,  
   SD.DateOfBirth,    
   SD.BloodGroup,  
   ISNULL(SD.PresentStreetone,'') AS PresentStreetone,  
   ISNULL(SD.PresentStreetTwo,'') AS PresentStreetTwo,  
   ISNULL(SD.PresentStreetThree,'') AS PresentStreetThree,  
      ISNULL(SD.PresentCity,'') AS PresentCity, 
	  ISNULL(SD.PresentState,'') AS PresentState, 
      ISNULL(SD.PresentZipCode,'') AS PresentZipCode,  
   SD.PresentContactNo,         
   SD.ParentContactNo AS ParentContactNo,  
   SD.StudentContactNo,     
   SD.StudentPhoto,  
   SD.AdmissionCategory,   
   SD.DivisionOrder,    
   ISNULL(SD.FamilyCast,'')AS FamilyCast, 
   ISNull(SD.AdhaarNumber,'')As AdhaarNumber,
   OM.Title AS OrgName,   
   ISNULL(HM.Title,'') AS HouseName,   
   OM.PrincipalSignature,  
   ISNULL(ICO.ICardOrgTitle,'') AS ICardOrgTitle,  
   ISNULL(ICO.ICardAddress1,'') AS ICardAddress1,  
   ISNULL(ICO.ICardAddress2,'') AS ICardAddress2,  
   ISNULL(ICO.ICardAddress3,'') AS ICardAddress3,     
   OM.OrgLogo AS ICardLogo,  
     
   --ISNULL(ICO.ICardBackground,0) AS ICardBackground,   
   --ISNULL(ICO.ICardBackBackground,0) AS ICardBackBackground,   
     
    ICO.ICardBackground  AS ICardBackground,   
    ICO.ICardBackBackground  AS ICardBackBackground,        
       
   ISNULL(ICO.LabelColor,'Black') AS LabelColor,  
   ISNULL(ICO.DetailColor,'Black') AS DetailColor,  
   ISNULL(ICO.HeaderColor,'Black') AS HeaderColor,  
   ISNULL(SF.FatherName,'') AS FatherName,  
   ISNULL(SF.FatherContactNo,'') AS FatherContactNo,  
   ISNULL(SF.MotherName,'') AS MotherName,  
   ISNULL(SF.MotherContactNo,'') AS MotherContactNo,  
   ISNULL(RT.RouteName,'') AS RouteName,  
   ISNULL(RT.PickupPoint,'') AS PickupPoint,  
   ISNULL(RT.DropPoint,'') AS DropPoint,  
     
   @lIcardBorder AS IcardBorder,  
   HM.ColorCode,  
   OM.OrgLogo,  
   OM.SmallAddress1,  
   --@lSUIdBarcode AS IsBarcodeBasedOnSUId,  
   CASE WHEN @lSUIdBarcode = 'True' THEN SD.SUId  ELSE SD.Code END AS BarcodeNumber,  
    ISNULL(SD.EmergencyStreetOne,'') AS EmergencyStreetOne,  
          ISNULL(SD.EmergencyStreetTwo,'') AS EmergencyStreetTwo,  
         ISNULL(SD.EmergencyStreetThree,'') AS EmergencyStreetThree,  
         ISNULL(SD.EmergencyCountry,'') AS EmergencyCountry,  
         ISNULL(SD.EmergencyState,'') As EmergencyState,  
         ISNULL(SD.EmergencyCity,'') AS EmergencyCity,  
         ISNULL(SD.EmergencyZipCode,'') as EmergencyZipCode,  
         SD.EmergencyContactNo AS EmergencyContactNo,  
   ISNULL(SD.SUId,'') As SUId,  
   ISNULL(RT.RouteNumber,0) AS RouteNumber,  
   @rIsShowBarcode As IsShowBarcode,  
   SD.Name,  
   SM.Title AS Stream,  
   SD.AdmissionDate,  
   ISNULL(DM.Title,'') AS DepartmentName,  
   SD.FatherPhoto,  
   SD.MotherPhoto,  
   SD.RelativePhoto,  
   RT.Transport AS TransPortMode,  
   @lIDConfigValue AS ItemValue,  
   RT.RouteCode,
   ISNULL(LM.Title,'') AS Medium ,
   ISNULL(HA.Hosteld,'') AS HostelId,
   SD.Gender,
   SI.QRCode
   ,  (Select ToDate from sSetup.tBatchMaster where Id = @rBatchId) as LastBatchDate,

   --STUFF(SELECT  ', ' + Title FROM sActivity.tClubMaster CM INNER JOIN sActivity.tAssignClub AC ON AC.AcademicId=SD.AcademicId FOR XML PATH ('') ),0,0,',') AS Club

    STUFF(
         (SELECT DISTINCT  ', ' + Title 
          FROM sActivity.tClubMaster CM INNER JOIN sActivity.tAssignClub AC ON SD.AcademicId  = AC.AcademicId AND CM.OrgGroupId = LEFT(SD.OrgGroupBatchId,4)
		  
          FOR XML PATH (''))
          , 1, 1, '')  AS Club,

	ISNULL(TM.Title,'') AS StudentTransportMode,
	ISNULL(RT.VehicleName,'') AS VehicleName,
	ISNULL(SO.BirthPlace,'') AS BirthPlace
   --CM.Title as Club 

 FROM   
   sStudent.vStudentAllDetail SD  
   INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=SD.OrgId  
   --INNER JOIN sStudent.tStudentOther SO ON SD.StudentId = So.StudentId      
   LEFT JOIN sActivity.tHouseMaster HM ON SD.HouseId = HM.Id   
   LEFT JOIN sActivity.tHouseGroupMaster HGM ON HGM.Id=HM.HouseGroupId  
   LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId = SD.StudentId 
   LEFT JOIN sHostel.tHostelAdmission HA ON HA.SourceId= SD.StudentId 
   LEFT JOIN  sStudent.tStudentImages SI ON SD.StudentId = SI.StudentId
   LEFT JOIN  
   (SELECT TM.Title AS Transport,  AT.SourceId,ISNULL(BRM.RouteCode,0) AS RouteCode,BRM.Title As RouteName,BRDM.Title As PickupPoint,ISNULL(BRM.RouteNumber,0) AS RouteNumber,
		ISNULL(BRDM1.Title,'')As DropPoint, VM.Title AS VehicleName
    FROM 
		sTransport.tAssignRoute AT   
		INNER JOIN sTransport.tBusRouteDestinationMaster BRDM ON BRDM.Id=AT.PickupDestinationId
		INNER JOIN sTransport.tBusRouteMaster BRM ON BRM.Id=BRDM.BusRouteId AND  AT.SourceTypeId=2003 AND ((BRM.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0))
		INNER JOIN sTransport.tTransportMaster TM ON TM.Id=BRM.TransportId
		LEFT JOIN sTransport.tBusRouteDestinationMaster BRDM1 ON BRDM1.Id=AT.DropDestinationId AND BRM.Id=BRDM1.BusRouteId
		LEFT JOIN sTransport.tVehicleMaster VM ON VM.Id = BRM.VehicleId
   ) RT  
   ON RT.SourceId = SD.StudentId AND ((SD.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId = 0))   
   --LEFT JOIN sTransport.tAssignRoute AR ON AR.SourceId =SD.StudentId   
   --LEFT JOIN sTransport.tBusRouteDestinationMaster BRDM ON BRDM.Id =AR.PickupDestinationId   
   --LEFT JOIN sTransport.tBusRouteMaster BRM ON BRM.Id =BRDM.BusRouteId  AND(( BRM.OrgGroupBatchId=@rOrgGroupBatchId) OR (@rOrgGroupBatchId=0))  
   LEFT JOIN sStudent.tICardOrg ICO ON SD.OrgId = ICO.OrgId  
   LEFT JOIN sSetup.tStreamMaster SM ON SM.Id=SD.StreamId  
   LEFT JOIN sSetup.tDepartmentMaster DM ON DM.Id=SD.DepartmentId  
   LEFT JOIN sStudent.tStudentOther SO ON SD.StudentId = SO.StudentId  
   LEFT JOIN sCommon.tTransportModeMaster TM ON so.TransportModeId = TM.Id  
   LEFT JOIN sSetup.tLanguageMaster LM ON OM.LanguageId = LM.Id

   
   
   --LEFT JOIN sSetup.tOrganizationConfiguration OC ON OM.Id = OC.OrgId   
     
 WHERE   
  SD.OrgId=@rOrgId  
  AND SD.BatchId = @rBatchId   
  AND (ISNULL(SD.Code,0) IN( SELECT cast(item as nvarchar(20)) FROM  sCommon.fnsplit(@rStrCode,',')) OR @rStrCode='')   
  AND ((SD.ClassId=@rClassId) OR (@rClassId=0))  
  AND ((SD.DivisionId=@rDivisionId) OR (@rDivisionId=0))  
  AND ((SD.StudentId=@rStudentId) OR (@rStudentId=0))  
  AND ((SD.RollNo >= @rFromRollNo) OR (@rFromRollNo=0))  
  AND ((SD.RollNo <= @rToRollNo) OR (@rToRollNo=0))  
  AND ((SD.StatusId=@rStatusId)OR(@rStatusId=0))  
  AND ((SD.DepartmentId=@rDepartmentId) OR (@rDepartmentId=0))  
  AND ((HGM.Id=@rHouseGroupId)OR(@rHouseGroupId=0)) 
  AND ((HM.Id=@rHouseId) OR (Isnull(@rHouseId,0)=0)) 
  AND ((SD.OrgGroupBatchId = @rOrgGroupBatchId) OR (@rOrgGroupBatchId=0))
END
GO
PRINT N'Altering [sStudent].[pRptStudentRegisterWithAddressGet]...';


GO


ALTER PROCEDURE [sStudent].[pRptStudentRegisterWithAddressGet]
	 
	 @rBuilder1 nvarchar(max)='',
	 @rBuilder2 nvarchar(max)='', 
	 @rOrgId smallint,
	 @rBatchId int,
	 @rStrCode nvarchar(max),
	 @rClassId int,
	 @rDivisionId int,
	 @rStudentId bigint,
	 @rFromRollNo int,
	 @rToRollNo int,
	 @rStatusId int,
	 @rClassStatusId int,
	 @rDateOfBirth int,
	 @rDepartmentId int
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 

BEGIN 

	SELECT 
		 
			@rBuilder1 AS Builder1,
			@rBuilder2 AS Builder2,
			SD.RollNo,
			SD.Code,
			SD.FirstName,
			SD.MiddleName,			
			SD.LastName,
			SD.Name,	
			SD.DateOfBirth,
			SD.Religion,
			SD.FamilyCast,
			CCM.Title AS FamilySubCast,
			SF.MotherName,
			SF.FatherName,
			SD.ParentContactNo AS ContactNo,
			SF.FatherOccupation,
			SD.PresentStreetOne + ' ' + SD.PresentStreetTwo +' ' +SD.PresentStreetThree AS Address,
			
			SD.AcademicId,
			SD.StudentId, 
			SD.ClassId,
			SD.DivisionId,
			SD.ClassDivision,
			  
			SD.ClassName,
			SD.DivisionName,
			 
			OM.Title AS OrgName,
			SD.DivisionOrder,
			SD.Number AS StudentNumber,
			SD.Batch,                       
			
			SD.ClassStatusId,
			SD.ClassStatusName,
			ISNULL(SD.AdmissionDate,'') As AdmissionDate
			
      
	FROM 
			sStudent.vStudentAllDetail SD
			INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=SD.OrgId
			LEFT JOIN sStudent.tStudentFamily SF ON SF.StudentId=SD.StudentId
			--INNER  JOIN sStudent.tStudentCommunication SC ON SC.StudentId = SD.StudentId
			--INNER JOIN sStudent.tStudentOther SO ON SO.StudentId = SD.StudentId
			LEFT JOIN sCommon.tCastCategoryMaster CCM ON CCM.Id = SD.CastCategoryId
			INNER JOIN sSetup.tBatchClass BC ON BC.ClassID =  SD.ClassId and BC.BatchId= @rBatchId 
			
	WHERE 
			SD.OrgId=@rOrgId
		AND	SD.BatchId = @rBatchId 
		AND (ISNULL(SD.Code,0) IN( SELECT cast(item as nvarchar(20)) FROM  sCommon.fnsplit(@rStrCode,',')) OR @rStrCode='') 
		AND ((SD.ClassId=@rClassId) OR (@rClassId=0))
		AND ((SD.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		AND ((SD.StudentId=@rStudentId) OR (@rStudentId=0))
		AND ((SD.RollNo >= @rFromRollNo) OR (@rFromRollNo=0))
		AND ((SD.RollNo <= @rToRollNo) OR (@rToRollNo=0))
		AND ((SD.StatusId=@rStatusId)OR(@rStatusId=0))
		AND ((SD.ClassStatusId=@rClassStatusId)OR(@rClassStatusId=0))
		AND ((MONTH(SD.DateOfBirth)=@rDateOfBirth OR @rDateOfBirth=0))
	    AND ((SD.DepartmentId=@rDepartmentId) OR (@rDepartmentId=0))
END
GO
PRINT N'Altering [sStudent].[pValidateImportAdmissionInquiry]...';


GO

ALTER PROCEDURE [sStudent].[pValidateImportAdmissionInquiry]

	@rOrgGroupId int,
    @rOrgId int,
	@rXmldoc [nvarchar](max)
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
DECLARE @lCount INT
DECLARE @lInterrorCode INT,
		@intDoc INT,
        
        @lClass nvarchar(100),
        @lInquiryDate nvarchar(15),
		@lInquiryStatus nvarchar(50),
        @lDOB nvarchar(15),             
        @1ReturnCode int = 1,
        @1ValidInquiryDate nvarchar(max)='',
        @1ValidDOB nvarchar(max) = '' 
       
       
EXECUTE @lInterrorCode = sp_xml_preparedocument @intDoc OUTPUT, @rXmlDoc
 
CREATE TABLE #tTable1 
 (
		Id bigint identity,
        FirstName nvarchar(100),
        MiddleName nvarchar(100),
        LastName nvarchar(100),
		Class nvarchar(100),
		
		InquiryDate nvarchar(15), 
        InquiryStatus nvarchar(50),
        DOB nvarchar(15),
        Gender nvarchar(10),
		InquiryMode nvarchar(50)  
	 
 
  )
  INSERT INTO #tTable1
	(
	    
	    FirstName,
	    MiddleName,
	    LastName,
	    Class,
	    InquiryDate,
	    InquiryStatus,
	    DOB,
	    Gender,
		InquiryMode
	    
    ) 
   SELECT
       
	    FirstName,
	    MiddleName,
	    LastName,
	    Class,
	    InquiryDate,
	    InquiryStatus,
	    DOB,
	    Gender,
		InquiryMode
	  
   
   FROM OPENXML (@intDoc, '/Table/ImportStudent', 1)
   With
   (
        
        FirstName nvarchar(100) '@FirstName',
        MiddleName  nvarchar(100) '@MiddleName',
        LastName nvarchar(100) '@LastName',
		Class nvarchar(100) '@Class',
		InquiryDate nvarchar(15) '@InquiryDate',
		InquiryStatus nvarchar(50) '@InquiryStatus',
		DOB nvarchar(15) '@DOB',
		Gender nvarchar(10) '@Gender' ,
		InquiryMode nvarchar(50) '@InquiryMode'
   )   
EXEC sp_xml_removedocument @intDoc
		
SELECT @lCount =  COUNT(Id) FROM #tTable1
 
WHILE @lCount > 0 
  BEGIN
  
	SELECT         
		@lInquiryDate = InquiryDate 
			FROM #tTable1 WHERE Id = @lCount
	IF(@lInquiryDate = '')
	BEGIN    
		SELECT 'Inquiry date Should not be empty'   AS StudentCode
	END
    ELSE IF(ISDATE(@lInquiryDate) = 0 AND @lInquiryDate!='')
	BEGIN    
		SET @1ValidInquiryDate = @1ValidInquiryDate + @lInquiryDate+','
	END

	SELECT         
		@lDOB = DOB 
			FROM #tTable1 WHERE Id = @lCount
	IF(ISDATE(@lDOB) = 0 AND @lDOB!='')
	BEGIN
		SET @1ValidDOB=@1ValidDOB +   @lDOB+','
	END
     SET @lCount = @lCount - 1	
   END	
    
   
   SELECT @lCount =  COUNT(Id) FROM #tTable1
 
	WHILE @lCount > 0 
		BEGIN
			SELECT         
			 @lClass = Class 
			FROM #tTable1 WHERE Id = @lCount

			IF NOT EXISTS (SELECT 1 FROM  
					 sSetup.tClassMaster CM  
						WHERE CM.Title COLLATE DATABASE_DEFAULT =@lClass COLLATE DATABASE_DEFAULT  )	
			BEGIN
				
				SELECT 'Enter proper value for Class ' + @lClass   AS StudentCode
				RETURN
			END
			SET @lCount = @lCount - 1	
		END
   	
		   
		 
		  IF EXISTS(SELECT 1 FROM #tTable1 WHERE LastName IS NULL OR LastName COLLATE DATABASE_DEFAULT ='' COLLATE DATABASE_DEFAULT)
		BEGIN
			SELECT 'Surname Should not be empty' AS StudentCode
		END
		ELSE IF EXISTS(SELECT 1 fROM #tTable1 WHERE FirstName IS NULL OR FirstName COLLATE DATABASE_DEFAULT ='' COLLATE DATABASE_DEFAULT)
		BEGIN
			SELECT 'First Name Should not be empty' AS StudentCode
		END
		ELSE IF EXISTS(SELECT 1 fROM #tTable1 WHERE MiddleName IS NULL OR MiddleName COLLATE DATABASE_DEFAULT ='' COLLATE DATABASE_DEFAULT)
		BEGIN
			SELECT 'Middle Name Should not be empty' AS StudentCode
		END
		ELSE IF EXISTS(SELECT 1 fROM #tTable1 WHERE Class IS NULL OR Class COLLATE DATABASE_DEFAULT ='' COLLATE DATABASE_DEFAULT)
		BEGIN
			SELECT 'Class Should not be empty' AS StudentCode
		END
		 
	 
		ELSE IF EXISTS(SELECT 1 fROM #tTable1 WHERE Gender IS NULL OR Gender COLLATE DATABASE_DEFAULT ='' COLLATE DATABASE_DEFAULT)
		BEGIN
			SELECT 'Gender Should not be empty' AS StudentCode
		END
 
		ELSE IF EXISTS(SELECT 1 fROM #tTable1 WHERE Class  COLLATE DATABASE_DEFAULT NOT IN(SELECT Title FROM sSetup.tClassMaster WHERE OrgId=@rOrgId))
		BEGIN
			SELECT 'Enter proper value for Class as per Institute Setup > Class Master' AS StudentCode
		END				
		 
		ELSE IF EXISTS(SELECT 1 fROM #tTable1 WHERE Gender COLLATE DATABASE_DEFAULT !='' COLLATE DATABASE_DEFAULT AND Gender NOT IN(SELECT Title FROM sCommon.tGenderMaster))
		BEGIN
			SELECT 'Enter proper value / Check spelling for gender ( Male / Female )' AS StudentCode
		END
		--ELSE IF EXISTS(SELECT 1 fROM #tTable1 WHERE AdmissionCategory!='' AND AdmissionCategory NOT IN(SELECT Title FROM sStudent.tAdmissionCategoryMaster))
		--BEGIN
		--	SELECT 'Enter proper value for Cast Category (Indian,NRI,General)' AS StudentCode
		--END
			 
		Else if Exists(Select 1 from #tTable1 where InquiryStatus COLLATE DATABASE_DEFAULT !='' COLLATE DATABASE_DEFAULT AND InquiryStatus NOT IN (Select Title from sAdmission.tInquiryStatusMaster))
		Begin
			Select 'Enter proper Value or check Spelling for Inquiry Status' AS StudentCode
		End
		ELSE IF EXISTS(SELECT 1 fROM #tTable1 WHERE InquiryStatus IS NULL OR InquiryStatus COLLATE DATABASE_DEFAULT ='' COLLATE DATABASE_DEFAULT)
		BEGIN
			SELECT 'Inquiry Status Should not be empty ' AS StudentCode
		END
		ELSE IF NOT EXISTS(SELECT 1 FROM #tTable1 WHERE InquiryMode IN(SELECT Title FROM sAdmission.tInquiryModeMaster ) OR InquiryMode='')
		BEGIN
			SELECT 'Inquiry Mode is not proper as per Inquiry Mode Master' AS StudentCode
		END

		ELSE
			
		BEGIN
			IF((@1ValidInquiryDate IS NULL OR @1ValidInquiryDate = '') AND (@1ValidDOB IS NULL OR @1ValidDOB = ''))
		BEGIN
			SELECT '' AS StudentCode
		END
		
		ELSE IF (@1ValidInquiryDate IS NOT NULL AND @1ValidInquiryDate != '')
			BEGIN
			SET @1ValidInquiryDate = 'Wrong Inquiry Date : ' + LEFT(@1ValidInquiryDate, LEN(@1ValidInquiryDate)-1)
			Select @1ValidInquiryDate AS StudentCode
			END

		ELSE IF (@1ValidDOB IS NOT NULL AND @1ValidDOB != '')
			BEGIN
			SET @1ValidDOB = 'Wrong DOB : ' + LEFT(@1ValidDOB, LEN(@1ValidDOB)-1)
			Select @1ValidDOB AS StudentCode
			END
		
		END
		 
END
GO
PRINT N'Altering [sStudent].[pXMLStudentImportAdd]...';


GO

 
ALTER PROCEDURE [sStudent].[pXMLStudentImportAdd]
	
	@rStudentImpXml [nvarchar](max),
	@rToOrgId int,
	@rToBatchId int,
	@rToClassId int,
	@rToDivisionId int,
	@rAdmissionDate date,
	@rCreatedUserId bigint,
	@rIsStudentOldCodeWithOrgId bit=0
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

DECLARE @loDoc INT
EXEC sp_xml_preparedocument @loDoc OUTPUT, @rStudentImpXml
BEGIN

	
	DECLARE @lCount INT
	DECLARE @lNextNumber BIGINT 
	Declare @lExistCode nvarchar(max)
	DECLARE @MaxCode AS bigint
	
 
 
	IF EXISTS(SELECT SM.Code
	from
	(
			SELECT StudentId FROM
			OPENXML (@loDoc, '/Table/StudentImportDetail', 1)
			WITH (
					StudentId bigint)	
	)E
	INNER JOIN sStudent.tStudentMaster SM ON SM.ReferenceSourceId=E.StudentId AND SM.OrgId=@rToOrgId	
	)
	BEGIN
		SELECT @lExistCode  = COALESCE(@lExistCode ,'') + 'Old : '+ CONVERT(VARCHAR(15),E.Code) +' - ' + 'New : '+  SM.Code + ' , ' 
		from
		(
				SELECT StudentId,Code FROM
				OPENXML (@loDoc, '/Table/StudentImportDetail', 1)
				WITH (
						StudentId bigint,
						Code nvarchar(20)
						)
		)E
		INNER JOIN sStudent.tStudentMaster SM ON SM.ReferenceSourceId=E.StudentId AND SM.OrgId=@rToOrgId	
		SELECT @lExistCode
		RETURN
	END
	
	--BEGIN TRY
	--BEGIN TRANSACTION

	EXECUTE sCommon.pTableRowIdGet @rToOrgId, 'sStudent.tStudentMaster', 0, 1, 1,@outNextNumber = @lNextNumber output
	
	SELECT @MaxCode = MAX(Number) FROM sStudent.tStudentMaster WHERE OrgId = @rToOrgId
	--SET @MaxCode = ISNULL(@MaxCode,0) + 1
	
	IF(@rIsStudentOldCodeWithOrgId = 0)
	BEGIN
	SELECT 
			@lNextNumber + ROW_NUMBER() OVER(ORDER BY E.StudentId)AS NewStudentId,
			E.StudentId,
			ISNULL(@MaxCode,0) + ROW_NUMBER() OVER(ORDER BY E.StudentId) AS Code,			
			AdmissionCategoryId
	INTO 
			sStudent.tTempStudentId
	from
	(
			SELECT StudentId,AdmissionCategoryId,Code FROM
			OPENXML (@loDoc, '/Table/StudentImportDetail', 1)
			WITH (
					StudentId bigint,
					AdmissionCategoryId int,
					Code nvarchar(20)
					)	
	)E
	END
	ELSE IF (@rIsStudentOldCodeWithOrgId = 1)
	BEGIN
	SELECT 
			@lNextNumber + ROW_NUMBER() OVER(ORDER BY E.StudentId)AS NewStudentId,
			E.StudentId,
			CONVERT(NVARCHAR(4),LEFT(E.StudentId,4)) + E.Code AS Code,
			AdmissionCategoryId
	INTO 
			sStudent.tTempStudentId
	from
	(
			SELECT StudentId,AdmissionCategoryId,Code FROM
			OPENXML (@loDoc, '/Table/StudentImportDetail', 1)
			WITH (
					StudentId bigint,
					AdmissionCategoryId int,
					Code nvarchar(20)
					)	
	)E
	END

	SELECT @lCount = COUNT(1) FROM OPENXML (@loDoc, '/Table/StudentImportDetail', 1)
				 WITH ( Id int )
	SET @lNextNumber  = @lCount + @lNextNumber 
	
	EXECUTE sCommon.pTableRowIdModify @rToOrgId, @rTableTitle = 'sStudent.tStudentMaster', @rCurrentNumber = @lNextNumber 
		 print 'Academic'
	--Insert Into Student Master
	INSERT INTO sStudent.tStudentMaster
	(
			Id,
			Code,
			Number,
			FirstName,
			MiddleName,
			LastName,
			OrgId,
			StatusId,
			RecordStatusId,
			CreatedUserId,
			CreatedDateTime,
			ReferenceSourceId,
			AdmissionCategoryId,
			RegistrationNumber,
			IsBlackList,
			BlackListReason,
			SUId
		
	)
	SELECT 
			TS.NewStudentId,
			TS.Code,
			CASE WHEN ISNUMERIC(TS.Code) <> 0 THEN TS.Code ELSE TS.NewStudentId END,
			SM.FirstName,
			SM.MiddleName,
			SM.LastName,
			@rToOrgId,
			1,
			502,
			@rCreatedUserId,
			sCommon.fGetDate(),
			TS.StudentId,
			SM.AdmissionCategoryId,
			SM.RegistrationNumber,
			SM.IsBlackList,
			SM.BlackListReason,
			SM.SUId
	FROM
			sStudent.tTempStudentId TS	
		INNER JOIN sStudent.tStudentMaster SM ON SM.ID=TS.StudentId
		
	--Insert Into Academic
		
	EXECUTE sCommon.pTableRowIdGet @rToOrgId, 'sStudent.tAcademic', 0, 1, 1,@outNextNumber = @lNextNumber output


 print 'Academic'
	INSERT INTO sStudent.tAcademic
	(
			Id,
			StudentId,
			ClassId,
			BatchId,
			DivisionId,
			RollNo,
			OrgId,
			CreatedUserId,
			CreatedDateTime,
			AdmissionCategoryId,
			ClassStatusId
	)
	SELECT
			@lNextNumber+ ROW_NUMBER() OVER(ORDER BY StudentId),
			NewStudentId,
			@rToClassId,
			@rToBatchId,
			@rToDivisionId,
			0,
			@rToOrgId,
			@rCreatedUserId,
			sCommon.fGetDate(),
			AdmissionCategoryId,
			1
	FROM 
			sStudent.tTempStudentId

print 'Academic1'		
	SELECT @lCount = COUNT(1) FROM OPENXML (@loDoc, '/Table/StudentImportDetail', 1)
			 WITH ( Id int )
	SET @lNextNumber  = @lCount + @lNextNumber 
	
	EXECUTE sCommon.pTableRowIdModify @rToOrgId, @rTableTitle = 'sStudent.tAcademic', @rCurrentNumber = @lNextNumber 
	
	--Insert Into Student Other
	INSERT INTO sStudent.tStudentOther
	(
		   [StudentId]
		  ,[UIDNumber]
		  ,[DateOfBirth]
		  ,[BirthDateInWord]
		  ,[TransportModeId]
		  ,[GenderId]
		  ,[BloodGroupId]
		  ,[BirthPlace]
		  ,[BirthTaluka]
		  ,[BirthDistrict]
		  ,[FamilyCast]
		  ,[FamilySubCast]
		  ,[CastCategoryId]
		  ,[ReligionId]
		  ,[HouseId]
		  ,[MotherTongueId]
		  ,[NationalityId]
		  ,[Hobby]		   
		  ,[CreatedUserId]
		  ,[CreatedDateTime]
		  ,ReferenceRemark
		  ,AdhaarNumber
		  ,MotherTongue
		  ,QuotaId
		  ,BirthState
		  ,BirthCountry
	)
	SELECT 
		   TS.NewStudentId
		  ,SO.[UIDNumber]
		  ,SO.[DateOfBirth]
		  ,SO.[BirthDateInWord]
		  ,SO.[TransportModeId]
		  ,SO.[GenderId]
		  ,SO.[BloodGroupId]
		  ,SO.[BirthPlace]
		  ,SO.[BirthTaluka]
		  ,SO.[BirthDistrict]
		  ,SO.[FamilyCast]
		  ,SO.[FamilySubCast]
		  ,SO.[CastCategoryId]
		  ,SO.ReligionId		   
		  ,NULL
		  ,so.MotherTongueId
		  ,So.NationalityId		 
		  ,SO.[Hobby] 
		  ,@rCreatedUserId
		  ,sCommon.fGetDate()
		  ,ReferenceRemark
		  ,AdhaarNumber
		  ,MotherTongue
		  ,QuotaId
		  ,BirthState
		  ,BirthCountry
	FROM
			sStudent.tTempStudentId TS	
		INNER JOIN sStudent.tStudentOther SO ON SO.StudentId=TS.StudentId
	
	--Insert Into Student Admission
	INSERT INTO sStudent.tStudentAdmission
	(
		   [StudentId]
		  ,[PreviousStudentId]
		  ,[AdmissionStandardId]
		  ,[AdmissionDate]
		  ,[AdmissionYear]
		  ,[BatchId]
		  ,[CreatedUserId]
		  ,[CreatedDateTime]
		  ,PreviousSchool
	)
	SELECT 
		   TS.NewStudentId
		  ,TS.StudentId
		  ,@rToClassId
		  ,@rAdmissionDate
		  ,YEAR(GETDATE())
		  ,@rToBatchId
		  ,@rCreatedUserId
		  ,sCommon.fGetDate()
		  ,PreviousSchool
	FROM
			sStudent.tTempStudentId TS	
		INNER JOIN sStudent.tStudentAdmission SA ON SA.StudentId=TS.StudentId
	
	
INSERT INTO [sStudent].[tStudentCommunication]
           ([StudentId]
           ,[PresentStreetOne]
           ,[PresentStreetTwo]
           ,[PresentStreetThree]
           ,[PresentCountry]
           ,[PresentState]
           ,[PresentCity]
           ,[PresentZipCode]
           ,[PresentContactNo]
           ,[PermanentStreetOne]
           ,[PermanentStreetTwo]
           ,[PermanentStreetThree]
           ,[PermanentCountry]
           ,[PermanentState]
           ,[PermanentCity]
           ,[PermanentZipCode]
           ,[PermanentContactNo]
           ,[EmergencyStreetOne]
           ,[EmergencyStreetTwo]
           ,[EmergencyStreetThree]
           ,[EmergencyCountry]
           ,[EmergencyState]
           ,[EmergencyCity]
           ,[EmergencyZipCode]
           ,[EmergencyContactNo]
           ,[ParentContactNo]
           ,[ParentEmail]
           ,[StudentContactNo]
           ,[StudentEmail]
           ,[CreatedUserId]
           ,[CreatedDateTime]  
		   ,PresentDistanceFromSchool
		   ,WhatsAppContactNo
		   )
 
 SELECT 
			TS.NewStudentId
           ,[PresentStreetOne]
           ,[PresentStreetTwo]
           ,[PresentStreetThree]
           ,[PresentCountry]
           ,[PresentState]
           ,[PresentCity]
           ,[PresentZipCode]
           ,[PresentContactNo]
           ,[PermanentStreetOne]
           ,[PermanentStreetTwo]
           ,[PermanentStreetThree]
           ,[PermanentCountry]
           ,[PermanentState]
           ,[PermanentCity]
           ,[PermanentZipCode]
           ,[PermanentContactNo]
           ,[EmergencyStreetOne]
           ,[EmergencyStreetTwo]
           ,[EmergencyStreetThree]
           ,[EmergencyCountry]
           ,[EmergencyState]
           ,[EmergencyCity]
           ,[EmergencyZipCode]
           ,[EmergencyContactNo]
           ,[ParentContactNo]
           ,[ParentEmail]
           ,[StudentContactNo]
           ,[StudentEmail]
           ,@rCreatedUserId
           ,sCommon.fGetDate()
		   ,PresentDistanceFromSchool
		   ,WhatsAppContactNo	
	FROM
			sStudent.tTempStudentId TS	
		INNER JOIN [sStudent].[tStudentCommunication] SA ON SA.StudentId=TS.StudentId          
	
	
	
	INSERT INTO  [sStudent].[tStudentFamily]
           ([StudentId]
           ,[FatherName]
           ,[FatherDateOfBirth]
           ,[FatherOccupation]
           ,[FatherQualification]
           ,[FatherContactNo]
           ,[FatherMail]
           ,[FatherOfficeAddress]
           ,[FatherIncome]
           ,[MotherName]
           ,[MotherDateOfBirth]
           ,[MotherOccupation]
           ,[MotherQualification]
           ,[MotherContactNo]
           ,[MotherMail]
           ,[MotherOfficeAddress]
           ,[MotherIncome]
           ,[RelationshipName]
           ,[RelativeName]
           ,[RelativeDateOfBirth]
           ,[RelativeOccupation]
           ,[RelativeQualification]
           ,[RelativeContactNo]
           ,[RelativeMail]
           ,[RelativeOfficeAddress]
           ,[RelativeIncome]
           ,[CreatedUserId]
           ,[CreatedDateTime]
           ,[FatherPhoto]
           ,[MotherPhoto]
           ,[RelativePhoto]
           ,[MarriageAnniversaryDate]
		   ,LiveWithId
		   ,IsFatherExStudent
		   ,FatherPassOutYear
		   ,IsMotherExStudent
		   ,MotherPassOutYear
		   ,IsRelativeExStudent
		   ,RelativePassOutYear)
     SELECT    
			TS.NewStudentId
           ,[FatherName]
           ,[FatherDateOfBirth]
           ,[FatherOccupation]
           ,[FatherQualification]
           ,[FatherContactNo]
           ,[FatherMail]
           ,[FatherOfficeAddress]
           ,[FatherIncome]
           ,[MotherName]
           ,[MotherDateOfBirth]
           ,[MotherOccupation]
           ,[MotherQualification]
           ,[MotherContactNo]
           ,[MotherMail]
           ,[MotherOfficeAddress]
           ,[MotherIncome]
           ,[RelationshipName]
           ,[RelativeName]
           ,[RelativeDateOfBirth]
           ,[RelativeOccupation]
           ,[RelativeQualification]
           ,[RelativeContactNo]
           ,[RelativeMail]
           ,[RelativeOfficeAddress]
           ,[RelativeIncome]
           ,@rCreatedUserId
           ,sCommon.fGetDate()
           ,[FatherPhoto]
           ,[MotherPhoto]
           ,[RelativePhoto]
           ,[MarriageAnniversaryDate]
		   ,LiveWithId
		   ,IsFatherExStudent
		   ,FatherPassOutYear
		   ,IsMotherExStudent
		   ,MotherPassOutYear
		   ,IsRelativeExStudent
		   ,RelativePassOutYear
   	FROM
			sStudent.tTempStudentId TS	
		INNER JOIN sStudent.tStudentFamily SA ON SA.StudentId=TS.StudentId    


INSERT INTO  [sStudent].[tStudentHealth]
           ([StudentId]
           ,[IdentificationMark1]
           ,[IdentificationMark2]
           ,[IsPysicalDeformity]
           ,[PysicalDeformityRemark]
           ,[IsMajorIllness]
           ,[MajorIllnessRemark]
           ,[IsAllergy]
           ,[AllergyRemark]
           ,[IsPastHistory]
           ,[PastHistoryRemark]
           ,[FamilyDoctorName]
           ,[FamilyDoctorContactNo]
           ,[UpdatedUserId]
           ,[UpdatedDateTime])
SELECT

			TS.NewStudentId
           ,[IdentificationMark1]
           ,[IdentificationMark2]
           ,[IsPysicalDeformity]
           ,[PysicalDeformityRemark]
           ,[IsMajorIllness]
           ,[MajorIllnessRemark]
           ,[IsAllergy]
           ,[AllergyRemark]
           ,[IsPastHistory]
           ,[PastHistoryRemark]
           ,[FamilyDoctorName]
           ,[FamilyDoctorContactNo]
           ,@rCreatedUserId
           ,sCommon.fGetDate()

   	FROM
			sStudent.tTempStudentId TS	
		INNER JOIN sStudent.tStudentHealth SA ON SA.StudentId=TS.StudentId 
		
		
		INSERT INTO  [sStudent].[tStudentImages]
           ([StudentId]
           ,[StudentPhoto]
           ,[FatherPhoto]
           ,[MotherPhoto]
           ,[RelativePhoto]
           ,[UpdatedDateTime])
           
           SELECT           
            TS.NewStudentId
           ,[StudentPhoto]
           ,[FatherPhoto]
           ,[MotherPhoto]
           ,[RelativePhoto]
           ,sCommon.fGetDate()
         	FROM
			sStudent.tTempStudentId TS	
		INNER JOIN sStudent.tStudentImages SA ON SA.StudentId=TS.StudentId    
		
		
		
		INSERT INTO [sStudent].[tStudentAdditional]
           (StudentId,
			BankAccountNumber,
			BankName,
			BankBranch,
			BankIFSCCode,
			Class9BoardRegistrationNumber,
			Class11BoardRegistrationNumber,
			PassportNumber,
			PassportType,
			PassportCountryCode,
			PassportIssueDate,
			PassportExpiryDate
           )
           
           SELECT           
            TS.NewStudentId,
            BankAccountNumber,
            BankName,
			BankBranch,
			BankIFSCCode,
			Class9BoardRegistrationNumber,
			Class11BoardRegistrationNumber,
			PassportNumber,
			PassportType,
			PassportCountryCode,
			PassportIssueDate,
			PassportExpiryDate
         	FROM
			sStudent.tTempStudentId TS	
		INNER JOIN sStudent.tStudentAdditional SA ON SA.StudentId=TS.StudentId        
	    

		---Studen Sibling Detail-
			
		INSERT INTO [sStudent].[tStudentSibling]
           (StudentId,
			SiblingName1,
			SiblingInstitue1,
			SiblingClass1,
			SiblingAge1,
			SiblingRelation1,
			SiblingName2,
			SiblingInstitue2,
			SiblingClass2,
			SiblingAge2,
			SiblingRelation2,
			SiblingName3,
			SiblingInstitue3,
			SiblingClass3,
			SiblingAge3,
			SiblingRelation3

           )
           
           SELECT           
            TS.NewStudentId,
            SiblingName1,
			SiblingInstitue1,
			SiblingClass1,
			SiblingAge1,
			SiblingRelation1,
			SiblingName2,
			SiblingInstitue2,
			SiblingClass2,
			SiblingAge2,
			SiblingRelation2,
			SiblingName3,
			SiblingInstitue3,
			SiblingClass3,
			SiblingAge3,
			SiblingRelation3
         	FROM
			sStudent.tTempStudentId TS	
		INNER JOIN [sStudent].[tStudentSibling] SA ON SA.StudentId=TS.StudentId        

		INSERT INTO  [sStudent].[tCollegeDetail]
            (StudentId,
			AdmissionNumber,
			IsTransfer,
			TransferFrom,
			PassingYear,
			IsTFWS,
			IsCertificateHolder,
			CertificateName,
			SSCBoard,
			SSCSchool,
			SSCPassingYear,
			SSCObtainMarks,
			SSCOutOfMarks,
			SSCPercentage,
			SSCMathMarks,
			SSCMathOutOfMarks,
			HSCBoard,
			HSCSchool,
			HSCPassingYear,
			HSCObtainMarks,
			HSCOutOfMarks,
			HSCPercentage,
			HSCPhysicsMarks,
			HSCPhysicsOutOfMarks,
			HSCChemistryMarks,
			HSCChemistryOutOfMarks,
			HSCMathMarks,
			HSCMathOutOfMarks,
			HSCBiologyMarks,
			HSCBiologyOutOfMarks,
			PCMMarks,
			PCBMarks,
			JEERollNo,
			JEEMeritNo,
			JEEMeritMarks,
			JEEOutOfMarks,
			DiplomaBoard,
			DiplomaCollege,
			DiplomaPassingYear,
			DiplomaObtainMarks,
			DiplomaOutOfMarks,
			DiplomaPercentage,
			ApplicationNo,
			AdmissionRound,
			MaritalStatusId
)
SELECT

			TS.NewStudentId,
            AdmissionNumber,
			IsTransfer,
			TransferFrom,
			PassingYear,
			IsTFWS,
			IsCertificateHolder,
			CertificateName,
			SSCBoard,
			SSCSchool,
			SSCPassingYear,
			SSCObtainMarks,
			SSCOutOfMarks,
			SSCPercentage,
			SSCMathMarks,
			SSCMathOutOfMarks,
			HSCBoard,
			HSCSchool,
			HSCPassingYear,
			HSCObtainMarks,
			HSCOutOfMarks,
			HSCPercentage,
			HSCPhysicsMarks,
			HSCPhysicsOutOfMarks,
			HSCChemistryMarks,
			HSCChemistryOutOfMarks,
			HSCMathMarks,
			HSCMathOutOfMarks,
			HSCBiologyMarks,
			HSCBiologyOutOfMarks,
			PCMMarks,
			PCBMarks,
			JEERollNo,
			JEEMeritNo,
			JEEMeritMarks,
			JEEOutOfMarks,
			DiplomaBoard,
			DiplomaCollege,
			DiplomaPassingYear,
			DiplomaObtainMarks,
			DiplomaOutOfMarks,
			DiplomaPercentage,
			ApplicationNo,
			AdmissionRound,
			MaritalStatusId

   	FROM
			sStudent.tTempStudentId TS	
		INNER JOIN sStudent.tCollegeDetail SA ON SA.StudentId=TS.StudentId 

		-- -Student Custom Tag-
		INSERT INTO [sStudent].[tStudentCustomTag]
           (StudentId,CustomTagId
           )
SELECT

			TS.NewStudentId,CustomTagId
       
   	FROM
			sStudent.tTempStudentId TS	
		INNER JOIN sStudent.tStudentCustomTag SA ON SA.StudentId=TS.StudentId 
		

	    -- for update hostel 
	    -- Start
		--UPDATE A SET A.SourceId = T.NewStudentId 
		--FROM sHostel.tAssignRoom A JOIN sStudent.tTempStudentId T ON A.SourceId = T.StudentId AND A.SourceTypeId = 2003
		
		--UPDATE A SET A.SourceId = T.NewStudentId 
		--FROM sHostel.tAssignRoomAsset A JOIN sStudent.tTempStudentId T ON A.SourceId = T.StudentId AND A.SourceTypeId = 2003
		
		--UPDATE A SET A.StudentId = T.NewStudentId 
		--FROM sHostel.tAttendanceRegister A JOIN sStudent.tTempStudentId T ON A.StudentId = T.StudentId
		
		--UPDATE A SET A.SourceId = T.NewStudentId 
		--FROM sHostel.tChangeRoom A JOIN sStudent.tTempStudentId T ON A.SourceId = T.StudentId AND A.SourceTypeId = 2003
		
		--UPDATE A SET A.SourceId = T.NewStudentId 
		--FROM sHostel.tHostelAdmission A JOIN sStudent.tTempStudentId T ON A.SourceId = T.StudentId AND A.SourceTypeId = 2003
		
		--UPDATE A SET A.StudentId = T.NewStudentId 
		--FROM sHostel.tHostelDepositAdd A JOIN sStudent.tTempStudentId T ON A.StudentId = T.StudentId
		
		--UPDATE A SET A.StudentId = T.NewStudentId 
		--FROM sHostel.tHostelDepositAdjustment A JOIN sStudent.tTempStudentId T ON A.StudentId = T.StudentId
		
		--UPDATE A SET A.StudentId = T.NewStudentId 
		--FROM sHostel.tHostelDepositRefund A JOIN sStudent.tTempStudentId T ON A.StudentId = T.StudentId
		
		--UPDATE A SET A.StudentId = T.NewStudentId 
		--FROM sHostel.tStudentGatePass A JOIN sStudent.tTempStudentId T ON A.StudentId = T.StudentId
		
		--UPDATE A SET A.WhomToMeetSourceId = T.NewStudentId 
		--FROM sHostel.tVisitorGatePass A JOIN sStudent.tTempStudentId T ON A.WhomToMeetSourceId = T.StudentId AND WhomToMeetSourceTypeId = 2003
		-- End
		
		
		-- for update transport
		-- Start
		--UPDATE A SET A.SourceId = T.NewStudentId 
		--FROM sTransport.tAssignRoute A JOIN sStudent.tTempStudentId T ON A.SourceId = T.StudentId AND A.SourceTypeId = 2003
		
		--UPDATE A SET A.SourceId = T.NewStudentId 
		--FROM sTransport.tAttendanceRegister A JOIN sStudent.tTempStudentId T ON A.SourceId = T.StudentId AND A.SourceTypeId = 2003
		-- End
		
		-- for update Library
		-- Start
		UPDATE A SET A.ReaderSourceId = T.NewStudentId 
		FROM sLibrary.tBookLog A JOIN sStudent.tTempStudentId T ON A.ReaderSourceId = T.StudentId AND A.ReaderSourceTypeId = 2003
		
		UPDATE A SET A.SourceId = T.NewStudentId 
		FROM sLibrary.tLibraryAttendance A JOIN sStudent.tTempStudentId T 
		ON A.SourceId = T.StudentId AND A.SourceTypeId = 2003
		-- End
		
		-- for update Canteen
		-- Start
		UPDATE A SET A.SourceId = T.NewStudentId 
		FROM sCanteen.tCanteenIssue A JOIN sStudent.tTempStudentId T 
		ON A.SourceId = T.StudentId AND A.SourceTypeId = 2003
		
		-- End
	
	DROP TABLE sStudent.tTempStudentId
	
    SELECT 1 AS Result
	
	--COMMIT TRANSACTION
		
	--END TRY
	
	--BEGIN CATCH 
	--	ROLLBACK TRANSACTION
	--	SELECT Error_message()
	--	SELECT -1 AS Result
	--END CATCH	
		
END
GO
PRINT N'Altering [sTally].[pFeeLedgerMappingGet]...';


GO

ALTER PROCEDURE [sTally].[pFeeLedgerMappingGet]

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

BEGIN 
Declare @lOrgGroupId Int
	Declare @lPostingTYpe nvarchar(100)
	
	SELECT @lOrgGroupId = OrgGroupId from sSetup.tOrgGroupBatchMaster Where Id=@rOrgGroupBatchId

	SELECT @lPostingTYpe = CASE WHEN CS.ItemValue IS NULL THEN C.ItemValue ELSE CS.ItemValue END  
	FROM sConfig.tConfigurationMaster C INNER JOIN sConfig.tConfigurationLevelMaster CM ON CM.Id = C.ConfigurationLevelId
	LEFT JOIN sConfig.tConfigurationSetup CS ON CS.ConfigurationId = C.Id AND CS.SOURCEID=@lOrgGroupId
	WHERE  C.ItemKey = 'Tally.PostingType'
    	
	if @lPostingTYpe = 'OrgMainGroupWise' 
	BEGIN
		SELECT Distinct
			   OM.TITLE AS FeeName,
			   OM.Id AS FeeId,
			    FLM.LedgerName AS LedgerName,
			   RBC.CompanyName 
	    FROM sSetup.tORganizationMAster OM 
		INNER JOIN sSetup.tOrgSubGroupMaster OSM ON OSM.Id = OM.OrgSubGRoupID
		INNER JOIN sTally.tReceiptBookCompanyMapping RBC ON RBC.ReceiptBookId = OSM.Id AND RBC.OrgGroupBatchId = @rOrgGroupBatchId
		LEFT JOIN sTally.tFeeLedgerMapping FLM ON FLM.FeeId= OM.Id
		
		--Where RBC.ReceiptBookId = @rReceiptBookId 
	END
	ELSE IF @lPostingTYpe = 'FeeHeadAndReceiptBookWise' OR @lPostingTYpe = 'FeeHeadWiseReceipt_AllDetail'
	BEGIN
		SELECT Distinct
				   FHM.TITLE AS FeeName,
				   FHM.Id AS FeeId,
				   FLM.LedgerName,
				   RBC.CompanyName 
			FROM sFee.tFeeSetup FS
			INNER JOIN sSetup.tBatchMaster BM ON BM.Id = FS.BatchId AND BM.OrgGroupBatchId = @rOrgGroupBatchId
			INNER JOIN sFee.tFeeMaster FM ON FM.Id = FS.FeeId
			INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id = FM.FeeHeadId
			INNER JOIN sTally.tReceiptBookCompanyMapping RBC ON RBC.ReceiptBookId = FS.ReceiptBookId AND RBC.OrgGroupBatchId = @rOrgGroupBatchId
			LEFT JOIN sTally.tFeeLedgerMapping FLM ON FLM.FeeId= FHM.Id AND FLM.ReceiptBookId = FS.ReceiptBookId
			Where FS.ReceiptBookId = @rReceiptBookId 
	END
	ELSE 
	
	BEGIN
		
		SELECT Distinct
			   FHM.TITLE AS FeeName,
			   FHM.Id AS FeeId,
			   FLM.LedgerName,
			   RBC.CompanyName 
	    FROM sFee.tFeeSetup FS
		INNER JOIN sSetup.tBatchMaster BM ON BM.Id = FS.BatchId AND BM.OrgGroupBatchId = @rOrgGroupBatchId
		INNER JOIN sFee.tFeeMaster FM ON FM.Id = FS.FeeId
		INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id = FM.FeeHeadId
		INNER JOIN sTally.tReceiptBookCompanyMapping RBC ON RBC.ReceiptBookId = FS.ReceiptBookId AND RBC.OrgGroupBatchId = @rOrgGroupBatchId
		LEFT JOIN sTally.tFeeLedgerMapping FLM ON FLM.FeeId= FHM.Id 
		Where FS.ReceiptBookId = @rReceiptBookId 
	END
END
GO
PRINT N'Altering [sTally].[pReceiptPostingGet]...';


GO

ALTER PROCEDURE [sTally].[pReceiptPostingGet]

	 @rOrgGroupBatchId int,
	 @rReceiptBookId int,
	 @rReceiptDate Date,
	 @rToReceiptDate Date,
	 @rPayTypeId int = 0,
	 @rCustomNarration nvarchar(max)='',
	 @rIsCustomNarration bit=0
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 
	
	DECLARE @lTallyPostingType nVarchar(100)
	DECLARE @lOrgGroupId INT
	
	SELECT @lOrgGroupId = OrgGroupId from sSetup.tOrgGroupBatchMaster Where Id=@rOrgGroupBatchId
	
	SELECT @lTallyPostingType = CASE WHEN CS.ItemValue IS NULL THEN C.ItemValue ELSE CS.ItemValue END 
		FROM sConfig.tConfigurationMaster C
        INNER JOIN sConfig.tConfigurationLevelMaster CM ON CM.Id = C.ConfigurationLevelId
        LEFT JOIN sConfig.tConfigurationSetup CS ON CS.ConfigurationId = C.Id AND CS.SOURCEID= @lOrgGroupId
        WHERE  C.ItemKey = 'Tally.PostingType'
        
		
        IF @lTallyPostingType = 'FeeHeadWise' 
        BEGIN
			   SELECT	ReceiptDate,
						ISNULL(FHM.Title,'') AS FeeName,
						PM.Title AS PayType,
						Sum(FRD.Amount) AS Amount,
						Case when @rIsCustomNarration=1 then STUFF((SELECT ','  +Replace(replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					replace(
																					@rCustomNarration,'[Class]',isnull(SM1.ClassName,'')
																					),'[Division]',Isnull(SM1.DivisionName,'')
																					),'[StudentsName]',isnull(SM1.Name,'')
																					), '[RollNumber]',isnull(convert(nvarchar(200),SM1.RollNo),'0')
																					),'[Batch]',isnull(SM1.BatchName,'')
																					), '[ReceiptNumber]', isnull(Convert(nvarchar(100),FR1.ReceiptNumber),'')
																					), '[ReceiptDate]',isnull(Convert(nvarchar(100),FR1.ReceiptDate),'')
																					), '[ReceiptAmount]',isnull(Convert(nvarchar(100),FR1.ReceiptAmount),'')
																					), '[ChequeNo]',isnull(Convert(nvarchar(100),FR1.ChequeNo),'')
																					),'[BankName]',isnull(FR1.BankName,'')
																					),'[FeeName]',isnull(FM1.Title,'')
																					), '[FeeAmount]',isnull(Convert(nvarchar(100),Sum(FRD.Amount)),'0.00')
																					),'[FeeHeadName]',ISNULL(FHM.Title,'')
																					), '[FeeHeadAmount]',isnull(Convert(nvarchar(100),Sum(FRD.Amount)),'0.00')
																					), '[CollectionGroupName]',isNull(CGM1.Title,'')
																					),'[ReceiptFromNumber]',isnull(Convert(nvarchar(10),(Select Min(ReceiptNumber) from sFee.tReceipt where   FR.ReceiptDate =ReceiptDate)),'')
																					), '[ReceiptToNumber]',isnull(Convert(nvarchar(10),(Select Max(ReceiptNumber) from sFee.tReceipt Where FR.ReceiptDate = ReceiptDate)),''))
						from sFee.tReceipt FR1  INNER JOIN sFee.tReceiptDetail FRD1 ON FR1.Id = FRD1.ReceiptId
						INNER JOIN sFee.tFeeSetup FS1 ON FS1.Id = FRD1.FeeSetupId 
						INNER JOIN sStudent.vStudentAcademic SM1 ON SM1.ACademicId = FR1.SourceID AND SM1.OrgGRoupBatchId = FR1.OrgGRoupBatchId 						
						INNER JOIN sFee.tFeeMaster FM1 ON FM1.Id = FS1.FeeId --AND FRD.ReceiptId=FRD1.ReceiptId
						LEFT JOIN sFee.tCollectionGroupMaster CGM1 ON CGM1.Id = FS1.CollectionGroupId
						INNER JOIN sFee.tFeeHeadMaster FHM1 ON FHM1.Id = FM1.FeeHeadId AND FHM1.Id=FHM.Id where  FR.ReceiptDate=FR1.ReceiptDate FOR XML PATH('')),1,1,'') else
						 'Receipt generated for Batch : ' + (Select Title from sSetup.tOrgGroupBatchMaster Where Id= @rOrgGroupBatchId) +
						' Receipt Date : ' +  Convert(nvarchar(10),ReceiptDate) + ' and Receipt Number From ' + Convert(nvarchar(10),(Select Min(ReceiptNumber) from sFee.tReceipt where   FR.ReceiptDate =ReceiptDate AND IsObsolete = 0 )) + ' -  ' + 
						Convert(nvarchar(10),(Select Max(ReceiptNumber) from sFee.tReceipt Where FR.ReceiptDate = ReceiptDate AND IsObsolete = 0)) end as Narration,						
						FR.PayTypeId AS PayTypeId, 
						FHM.Id AS FeeId,
						0 AS ReceiptId
				FROM sFee.tReceipt FR 
					INNER JOIN sFee.tReceiptDetail FRD ON FR.Id = FRD.ReceiptId
					INNER JOIN sFee.tFeeSetup FS ON FS.Id = FRD.FeeSetupId
					INNER JOIN sFee.tFeeMaster FM ON FM.Id = FS.FeeId
					INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id = FM.FeeHeadId
					INNER JOIN sFinanceCommon.tPayTypeMaster PM ON PM.Id = FR.PayTypeId
					WHERE	FR.ReceiptBookId = @rReceiptBookId 
							AND FR.IsObsolete = 0 
							AND FR.OrgGroupBatchId = @rOrgGroupBatchId
							AND FR.ReceiptDate >= @rReceiptDate AND FR.ReceiptDate <= @rToReceiptDate
							AND (FR.PayTypeId = @rPayTypeId OR @rPayTypeId = 0)
				group by ReceiptDate,FHM.Title,PM.Title,FHM.Id,FR.PayTypeId
				Order By ReceiptDate Desc
			END				
       ELSE IF @lTallyPostingType = 'FeeHeadWiseCustom' 
        BEGIN
			   SELECT	ReceiptDate,
						FR.Id AS FeeName,
						PM.Title AS PayType,
						Sum(FRD.Amount) AS Amount,
						Case when @rIsCustomNarration=1 then  STUFF((SELECT ','  +Replace(replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					replace(
																					@rCustomNarration,'[Class]',isnull(SM1.ClassName,'')
																					),'[Division]',Isnull(SM1.DivisionName,'')
																					),'[StudentsName]',isnull(SM1.Name,'')
																					), '[RollNumber]',isnull(convert(nvarchar(200),SM1.RollNo),'0')
																					),'[Batch]',isnull(SM1.BatchName,'')
																					), '[ReceiptNumber]', isnull(Convert(nvarchar(100),FR1.ReceiptNumber),'')
																					), '[ReceiptDate]',isnull(Convert(nvarchar(100),FR1.ReceiptDate),'')
																					), '[ReceiptAmount]',isnull(Convert(nvarchar(100),FR1.ReceiptAmount),'')
																					), '[ChequeNo]',isnull(Convert(nvarchar(100),FR1.ChequeNo),'')
																					),'[BankName]',isnull(FR1.BankName,'')
																					),'[FeeName]',isnull(FM1.Title,'')
																					), '[FeeAmount]',isnull(Convert(nvarchar(100),Sum(FRD.Amount)),'0.00')
																					),'[FeeHeadName]',ISNULL(FHM1.Title,'')
																					), '[FeeHeadAmount]',isnull(Convert(nvarchar(100),Sum(FRD.Amount)),'0.00')
																					), '[CollectionGroupName]',isNull(CGM1.Title,'')
																					),'[ReceiptFromNumber]',isnull(Convert(nvarchar(10),(Select Min(ReceiptNumber) from sFee.tReceipt where   FR.ReceiptDate =ReceiptDate AND IsObsolete = 0)),'')
																					), '[ReceiptToNumber]',isnull(Convert(nvarchar(10),(Select Max(ReceiptNumber) from sFee.tReceipt Where FR.ReceiptDate = ReceiptDate AND IsObsolete = 0)),''))
						from sFee.tReceipt FR1  INNER JOIN sFee.tReceiptDetail FRD1 ON FR1.Id = FRD1.ReceiptId
						INNER JOIN sFee.tFeeSetup FS1 ON FS1.Id = FRD1.FeeSetupId 
						INNER JOIN sStudent.vStudentAcademic SM1 ON SM1.ACademicId = FR1.SourceID AND SM1.OrgGRoupBatchId = FR1.OrgGRoupBatchId 						
						INNER JOIN sFee.tFeeMaster FM1 ON FM1.Id = FS1.FeeId --AND FRD.ReceiptId=FRD1.ReceiptId
						LEFT JOIN sFee.tCollectionGroupMaster CGM1 ON CGM1.Id = FS1.CollectionGroupId
						INNER JOIN sFee.tFeeHeadMaster FHM1 ON FHM1.Id = FM1.FeeHeadId  where   FR.ReceiptDate=FR1.ReceiptDate AND FR.Id=FR1.Id AND FR1.IsObsolete = 0  FOR XML PATH('')),1,1,'') else
						SM.Name + '(' + ClassName + ':' + DivisionName + ') ' + ' # Cheque No. :' + ISNULL(FR.ChequeNo,'') + ' BankName :' + ISNULL(FR.BankName,'') end as Narration,						
						FR.PayTypeId AS PayTypeId, 
						0 AS FeeId,
						FR.Id AS ReceiptId
				FROM sFee.tReceipt FR 
					INNER JOIN sFee.tReceiptDetail FRD ON FR.Id = FRD.ReceiptId
					INNER JOIN sStudent.vStudentAcademic SM ON SM.ACademicId = FR.SourceID AND SM.OrgGRoupBatchId = FR.OrgGRoupBatchId 
					INNER JOIN sFee.tFeeSetup FS ON FS.Id = FRD.FeeSetupId
					INNER JOIN sFee.tFeeMaster FM ON FM.Id = FS.FeeId
					INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id = FM.FeeHeadId
					INNER JOIN sFinanceCommon.tPayTypeMaster PM ON PM.Id = FR.PayTypeId
					INNER JOIN sSetup.tOrganizationMaster OM ON  OM.Id=SM.OrgId
					LEFT JOIN sSetup.tOrgSubGroupMaster OSM ON OSM.id=OM.OrgSubGroupId

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

       ELSE IF @lTallyPostingType = 'OrgMainGroupWise' 
        BEGIN
			   SELECT	ReceiptDate,
						SM.AcademicId AS FeeName,
						PM.Title AS PayType,
						Sum(FRD.Amount) AS Amount,
						Case when @rIsCustomNarration=1 then  STUFF((SELECT ','  +Replace(replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					replace(
																					@rCustomNarration,'[Class]',isnull(SM1.ClassName,'')
																					),'[Division]',Isnull(SM1.DivisionName,'')
																					),'[StudentsName]',isnull(SM1.Name,'')
																					), '[RollNumber]',isnull(convert(nvarchar(200),SM1.RollNo),'0')
																					),'[Batch]',isnull(SM1.BatchName,'')
																					), '[ReceiptNumber]', isnull(Convert(nvarchar(100),FR1.ReceiptNumber),'')
																					), '[ReceiptDate]',isnull(Convert(nvarchar(100),FR1.ReceiptDate),'')
																					), '[ReceiptAmount]',isnull(Convert(nvarchar(100),FR1.ReceiptAmount),'')
																					), '[ChequeNo]',isnull(Convert(nvarchar(100),FR1.ChequeNo),'')
																					),'[BankName]',isnull(FR1.BankName,'')
																					),'[FeeName]',isnull(FM1.Title,'')
																					), '[FeeAmount]',isnull(Convert(nvarchar(100),Sum(FRD.Amount)),'0.00')
																					),'[FeeHeadName]',ISNULL(FHM1.Title,'')
																					), '[FeeHeadAmount]',isnull(Convert(nvarchar(100),Sum(FRD.Amount)),'0.00')
																					), '[CollectionGroupName]',isNull(CGM1.Title,'')
																					),'[ReceiptFromNumber]',isnull(Convert(nvarchar(10),FR1.ReceiptNumber),'')
																					), '[ReceiptToNumber]',isnull(Convert(nvarchar(10),FR1.ReceiptNumber),''))
						from sFee.tReceipt FR1  INNER JOIN sFee.tReceiptDetail FRD1 ON FR1.Id = FRD1.ReceiptId
						INNER JOIN sFee.tFeeSetup FS1 ON FS1.Id = FRD1.FeeSetupId 
						INNER JOIN sStudent.vStudentAcademic SM1 ON SM1.ACademicId = FR1.SourceID AND SM1.OrgGRoupBatchId = FR1.OrgGRoupBatchId 						
						INNER JOIN sFee.tFeeMaster FM1 ON FM1.Id = FS1.FeeId --AND FRD.ReceiptId=FRD1.ReceiptId
						LEFT JOIN sFee.tCollectionGroupMaster CGM1 ON CGM1.Id = FS1.CollectionGroupId
						INNER JOIN sFinanceCommon.tPayTypeMaster PM1 ON PM1.Id = FR1.PayTypeId
						INNER JOIN sFee.tFeeHeadMaster FHM1 ON FHM1.Id = FM1.FeeHeadId  where  FR.ReceiptDate=FR1.ReceiptDate AND PM.Title=PM1.Title AND SM.AcademicId=SM1.AcademicId AND  FR.PayTypeId=FR1.PayTypeId AND FR1.IsObsolete = 0  FOR XML PATH('')),1,1,'') else
						SM.Name + '(' + ClassName + ':' + DivisionName + ') ' + ' # Cheque No. :' + ISNULL(FR.ChequeNo,'') + ' BankName :' + ISNULL(FR.BankName,'') end as Narration,						
						FR.PayTypeId AS PayTypeId, 
						FR.Id AS FeeId,
						SM.AcademicId AS ReceiptId
				FROM sFee.tReceipt FR 
					INNER JOIN sFee.tReceiptDetail FRD ON FR.Id = FRD.ReceiptId
					INNER JOIN sStudent.vStudentAcademic SM ON SM.ACademicId = FR.SourceID AND SM.OrgGRoupBatchId = FR.OrgGRoupBatchId 
					INNER JOIN sFee.tFeeSetup FS ON FS.Id = FRD.FeeSetupId
					INNER JOIN sFee.tFeeMaster FM ON FM.Id = FS.FeeId
					INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id = FM.FeeHeadId
					INNER JOIN sFinanceCommon.tPayTypeMaster PM ON PM.Id = FR.PayTypeId
					INNER JOIN sSEtup.tOrganizationMaster OM ON OM.OrgSubGRoupID =  @rReceiptBookId AND OM.ID = SM.OrgId					
					WHERE	FR.IsObsolete = 0 
							AND FR.OrgGroupBatchId = @rOrgGroupBatchId
							AND FR.ReceiptDate >= @rReceiptDate AND FR.ReceiptDate <= @rToReceiptDate
							AND FR.PayTypeID = 2
							AND (FR.PayTypeId = @rPayTypeId OR @rPayTypeId = 0)
				group by ReceiptDate,PM.Title,SM.AcademicId ,FR.PayTypeId,FR.Id,SM.Name + '(' + ClassName + ':' + DivisionName + ') ' + ' # Cheque No. :' + ISNULL(FR.ChequeNo,'') + ' BankName :' + ISNULL(FR.BankName,'')
				Order By FR.Id,ReceiptDate Desc
			END	
	ELSE IF @lTallyPostingType = 'ReceiptWise' 
			BEGIN	
				SELECT	ReceiptDate,						
						ISNULL(SLM.LedgerName,'') AS FeeName,
						PM.Title AS PayType,						
						FR.ReceiptAmount AS Amount,
						Case when @rIsCustomNarration=1 then  STUFF((SELECT ','+  +Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					replace(
																					@rCustomNarration,'[Class]',isnull(SM1.ClassName,'')
																					),'[Division]',Isnull(SM1.DivisionName,'')
																					),'[StudentsName]',isnull(SM1.Name,'')
																					), '[RollNumber]',isnull(convert(nvarchar(200),SM1.RollNo),'0')
																					),'[Batch]',isnull(SM1.BatchName,'')
																					), '[ReceiptNumber]', isnull(Convert(nvarchar(100),FR1.ReceiptNumber),'')
																					), '[ReceiptDate]',isnull(Convert(nvarchar(100),FR1.ReceiptDate),'')
																					), '[ReceiptAmount]',isnull(Convert(nvarchar(100),FR1.ReceiptAmount),'')
																					), '[ChequeNo]',isnull(Convert(nvarchar(100),FR1.ChequeNo),'')
																					),'[BankName]',isnull(FR1.BankName,'')
																					),'[FeeName]',isnull(FM1.Title,'')
																					), '[FeeAmount]',isnull(Convert(nvarchar(100),Sum(FRD.Amount)),'0.00')
																					),'[FeeHeadName]',ISNULL(FHM1.Title,'')
																					), '[FeeHeadAmount]',isnull(Convert(nvarchar(100),Sum(FRD.Amount)),'0.00')
																					), '[CollectionGroupName]',isNull(CGM1.Title,'')
																					),'[ReceiptFromNumber]',isnull(Convert(nvarchar(10),FR1.ReceiptNumber),'')
																					), '[ReceiptToNumber]',isnull(Convert(nvarchar(10),FR1.ReceiptNumber),''))
						from sFee.tReceipt FR1  INNER JOIN sFee.tReceiptDetail FRD1 ON FR1.Id = FRD1.ReceiptId
						INNER JOIN sFee.tFeeSetup FS1 ON FS1.Id = FRD1.FeeSetupId 
						INNER JOIN sStudent.vStudentAcademic SM1 ON SM1.ACademicId = FR1.SourceID AND SM1.OrgGRoupBatchId = FR1.OrgGRoupBatchId 						
						INNER JOIN sFee.tFeeMaster FM1 ON FM1.Id = FS1.FeeId --AND FRD.ReceiptId=FRD1.ReceiptId
						LEFT JOIN sFee.tCollectionGroupMaster CGM1 ON CGM1.Id = FS1.CollectionGroupId
						INNER JOIN sFinanceCommon.tPayTypeMaster PM1 ON PM1.Id = FR1.PayTypeId
						INNER JOIN sFee.tFeeHeadMaster FHM1 ON FHM1.Id = FM1.FeeHeadId
						LEFT JOIN sTally.tStudentLedgerMapping SLM1 ON SLM1.StudentId = SM1.StudentId AND SLM1.ReceiptBookid = FR1.ReceiptBookId AND SLM.LedgerName=SLM1.LedgerName where  FR1.ReceiptDate=FR.ReceiptDate   AND FR1.PayTypeId=FR.PayTypeId AND  A.StudentId=SM1.StudentId AND FR.ReceiptAmount=FR1.ReceiptAmount AND FR1.IsObsolete = 0  FOR XML PATH('')),1,1,'') else
						case When FR.PayTypeId = 1 then
						'Std-' + ClassName + ':' + DivisionName + ' RollNo :' + convert(nvarchar(20),RollNo) + ' ' + BatchName + '-->' +
						STUFF((SELECT ','  + convert(nvarchar(20),isnull(FRD1.Amount,'')) + '-' + isnull(FM1.Title ,'') 
						from sFee.tReceipt FR1  INNER JOIN sFee.tReceiptDetail FRD1 ON FR1.Id = FRD1.ReceiptId
						INNER JOIN sFee.tFeeSetup FS1 ON FS1.Id = FRD1.FeeSetupId 						
						INNER JOIN sFee.tFeeMaster FM1 ON FM1.Id = FS1.FeeId AND FRD.ReceiptId=FRD1.ReceiptId  FOR XML PATH('')),1,1,'') 
						when FR.PayTypeId <> 1 then 
						'Std-' + ClassName + ':' + DivisionName + ' RollNo :' + convert(nvarchar(20),RollNo) + ' ' + BatchName + '-->' +
						STUFF((SELECT ','  + convert(nvarchar(20),isnull(FRD1.Amount,'')) + '-' + isnull(FM1.Title ,'') 
						from sFee.tReceipt FR1  INNER JOIN sFee.tReceiptDetail FRD1 ON FR1.Id = FRD1.ReceiptId
						INNER JOIN sFee.tFeeSetup FS1 ON FS1.Id = FRD1.FeeSetupId 						
						INNER JOIN sFee.tFeeMaster FM1 ON FM1.Id = FS1.FeeId AND FRD.ReceiptId=FRD1.ReceiptId  FOR XML PATH('')),1,1,'') + '  Cheque No. :' + ISNULL(FR.ChequeNo,'') + ' BankName :' + ISNULL(FR.BankName,'')  ENd
						end AS Narration,
						--A.Name+', '+ClassName + '-' + DivisionName+', '+A.OrganizationName+', ('+BatchName+')'
						--AS Narration,					
						FR.PayTypeId AS PayTypeId,									
						A.StudentId AS FeeId,
						FRD.ReceiptId					 
				FROM sFee.tReceipt FR 
					INNER JOIN sFee.tReceiptDetail FRD ON FR.Id = FRD.ReceiptId
					INNER JOIN sFee.tFeeSetup FS ON FS.Id = FRD.FeeSetupId
					INNER JOIN sFee.tFeeMaster FM ON FM.Id = FS.FeeId
					INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id = FM.FeeHeadId
					INNER JOIN sFinanceCommon.tPayTypeMaster PM ON PM.Id = FR.PayTypeId
					INNER JOIN sStudent.vStudentAcademic A ON A.AcademicId = FR.SourceId AND A.OrgGRoupBatchId = FR.OrgGRoupBatchId
					LEFT JOIN sTally.tStudentLedgerMapping SLM ON SLM.StudentId = A.StudentId AND SLM.ReceiptBookid = FR.ReceiptBookId
					WHERE							
							FR.ReceiptBookId = @rReceiptBookId 
							AND FR.IsObsolete = 0 
							AND FR.OrgGroupBatchId = @rOrgGroupBatchId
							AND FR.ReceiptDate >= @rReceiptDate	And FR.ReceiptDate <= @rToReceiptDate
							AND (FR.PayTypeId = @rPayTypeId OR @rPayTypeId = 0)
				group by 
				ReceiptDate,PM.Title,
				SLM.LedgerName,FR.PayTypeId,A.StudentId,
				FRD.ReceiptId,Isnull(FR.ChequeNo,''),ISNull(FR.BankName,''),
				--A.Name+', '+ClassName + '-' + DivisionName+', '+A.OrganizationName+', ('+BatchName+')',
				'Std-' + ClassName + ':' + DivisionName + ' RollNo :' + convert(nvarchar(20),RollNo) + ' ' + BatchName,
				FR.ReceiptAmount			
				Order By ReceiptDate desc
			END		
			
	ELSE IF @lTallyPostingType = 'FeeHeadAndReceiptBookWise' 
			BEGIN	
				SELECT	ReceiptDate,						
						ISNULL(SLM.LedgerName,'') AS FeeName,
						PM.Title AS PayType,						
						FR.ReceiptAmount AS Amount,
						Case when @rIsCustomNarration=1 then STUFF((SELECT ','  +Replace(replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					replace(
																					@rCustomNarration,'[Class]',isnull(SM1.ClassName,'')
																					),'[Division]',Isnull(SM1.DivisionName,'')
																					),'[StudentsName]',isnull(SM1.Name,'')
																					), '[RollNumber]',isnull(convert(nvarchar(200),SM1.RollNo),'0')
																					),'[Batch]',isnull(SM1.BatchName,'')
																					), '[ReceiptNumber]', isnull(Convert(nvarchar(100),FR1.ReceiptNumber),'')
																					), '[ReceiptDate]',isnull(Convert(nvarchar(100),FR1.ReceiptDate),'')
																					), '[ReceiptAmount]',isnull(Convert(nvarchar(100),FR1.ReceiptAmount),'')
																					), '[ChequeNo]',isnull(Convert(nvarchar(100),FR1.ChequeNo),'')
																					),'[BankName]',isnull(FR1.BankName,'')
																					),'[FeeName]',isnull(FM1.Title,'')
																					), '[FeeAmount]',isnull(Convert(nvarchar(100),Sum(FRD.Amount)),'0.00')
																					),'[FeeHeadName]',ISNULL(FHM1.Title,'')
																					), '[FeeHeadAmount]',isnull(Convert(nvarchar(100),Sum(FRD.Amount)),'0.00')
																					), '[CollectionGroupName]',isNull(CGM1.Title,'')
																					),'[ReceiptFromNumber]',isnull(Convert(nvarchar(10),FR1.ReceiptNumber),'')
																					), '[ReceiptToNumber]',isnull(Convert(nvarchar(10),FR1.ReceiptNumber),''))
						from sFee.tReceipt FR1  INNER JOIN sFee.tReceiptDetail FRD1 ON FR1.Id = FRD1.ReceiptId
						INNER JOIN sFee.tFeeSetup FS1 ON FS1.Id = FRD1.FeeSetupId 
						INNER JOIN sStudent.vStudentAcademic SM1 ON SM1.ACademicId = FR1.SourceID AND SM1.OrgGRoupBatchId = FR1.OrgGRoupBatchId 						
						INNER JOIN sFee.tFeeMaster FM1 ON FM1.Id = FS1.FeeId --AND FRD.ReceiptId=FRD1.ReceiptId
						LEFT JOIN sFee.tCollectionGroupMaster CGM1 ON CGM1.Id = FS1.CollectionGroupId
						INNER JOIN sFinanceCommon.tPayTypeMaster PM1 ON PM1.Id = FR1.PayTypeId
						INNER JOIN sFee.tFeeHeadMaster FHM1 ON FHM1.Id = FM1.FeeHeadId 
						LEFT JOIN sTally.tStudentLedgerMapping SLM1 ON SLM1.StudentId = SM1.StudentId AND SLM1.ReceiptBookid = FR1.ReceiptBookId AND SLM.LedgerName=SLM1.LedgerName where  FR1.ReceiptDate=FR.ReceiptDate AND PM1.Title=PM.Title  AND FR1.PayTypeId=FR.PayTypeId AND A.StudentId=SM1.StudentId AND FR.ReceiptAmount=FR1.ReceiptAmount AND FR1.IsObsolete = 0  FOR XML PATH('')),1,1,'') else
						case When FR.PayTypeId = 1 then
						'Receiptno-'  + CONVERT(nvarchar,isnull(FR.ReceiptNumber,'')) + 
						STUFF((SELECT ','  + convert(nvarchar(20),isnull(FM1.title,'')) 
						from sFee.tReceipt FR1  INNER JOIN sFee.tReceiptDetail FRD1 ON FR1.Id = FRD1.ReceiptId And FRD1.ReceiptId = FRD.ReceiptId 
						INNER JOIN sFee.tFeeSetup FS1 ON FS1.Id = FRD1.FeeSetupId 						
						INNER JOIN sFee.tFeeMaster FM1 ON FM1.Id = FS1.FeeId AND FRD.ReceiptId=FRD1.ReceiptId  FOR XML PATH('')),1,1,'') 
						when FR.PayTypeId <> 1 then 
						'Receiptno-'  +  CONVERT(nvarchar,isnull(FR.ReceiptNumber,'')) +
						STUFF((SELECT ','  + convert(nvarchar(20),isnull(FM1.title,'')) 
						from sFee.tReceipt FR1  INNER JOIN sFee.tReceiptDetail FRD1 ON FR1.Id = FRD1.ReceiptId And FRD1.ReceiptId = FRD.ReceiptId 
						INNER JOIN sFee.tFeeSetup FS1 ON FS1.Id = FRD1.FeeSetupId 						
						INNER JOIN sFee.tFeeMaster FM1 ON FM1.Id = FS1.FeeId AND FRD.ReceiptId=FRD1.ReceiptId  FOR XML PATH('')),1,1,'') 
						+ '  Cheque No. :' + ISNULL(FR.ChequeNo,'') + ' BankName :' + ISNULL(FR.BankName,'')  ENd
						end AS Narration,
						FR.PayTypeId AS PayTypeId,									
						A.StudentId AS FeeId,
						FRD.ReceiptId		
									 
				FROM sFee.tReceipt FR 
					INNER JOIN sFee.tReceiptDetail FRD ON FR.Id = FRD.ReceiptId
					INNER JOIN sFee.tFeeSetup FS ON FS.Id = FRD.FeeSetupId
					INNER JOIN sFee.tFeeMaster FM ON FM.Id = FS.FeeId
					INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id = FM.FeeHeadId
					INNER JOIN sFinanceCommon.tPayTypeMaster PM ON PM.Id = FR.PayTypeId
					INNER JOIN sStudent.vStudentAcademic A ON A.AcademicId = FR.SourceId AND A.OrgGRoupBatchId = FR.OrgGRoupBatchId
					LEFT JOIN sTally.tStudentLedgerMapping SLM ON SLM.StudentId = A.StudentId AND SLM.ReceiptBookid = FR.ReceiptBookId
					WHERE							
							FR.ReceiptBookId = @rReceiptBookId 
							AND FR.IsObsolete = 0 
							AND FR.OrgGroupBatchId = @rOrgGroupBatchId
							AND FR.ReceiptDate >= @rReceiptDate	And FR.ReceiptDate <= @rToReceiptDate
							AND (FR.PayTypeId = @rPayTypeId OR @rPayTypeId = 0)
				group by 
				ReceiptDate,PM.Title,
				SLM.LedgerName,FR.PayTypeId,A.StudentId,
				FRD.ReceiptId,
				Isnull(FR.ChequeNo,''),ISNull(FR.BankName,''),
				'Std-' + ClassName + ':' + DivisionName + ' RollNo :' + convert(nvarchar(20),RollNo) + ' ' + BatchName,FR.ReceiptAmount,FR.ReceiptNumber
				--,FHM.Title
				
				Order By ReceiptDate desc	
			END	
	ELSE IF @lTallyPostingType = 'FeeHeadWiseReceipt_AllDetail' 
        BEGIN
			   SELECT	ReceiptDate,
						FR.Id AS FeeName,
						PM.Title AS PayType,
						Sum(FRD.Amount) AS Amount,
						Case when @rIsCustomNarration=1 then  STUFF((SELECT ','  +Replace(replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					Replace(
																					replace(
																					replace(
																					@rCustomNarration,'[Class]',isnull(SM1.ClassName,'')
																					),'[Division]',Isnull(SM1.DivisionName,'')
																					),'[StudentsName]',isnull(SM1.Name,'')
																					), '[RollNumber]',isnull(convert(nvarchar(200),SM1.RollNo),'0')
																					),'[Batch]',isnull(SM1.BatchName,'')
																					), '[ReceiptNumber]', isnull(Convert(nvarchar(100),FR1.ReceiptNumber),'')
																					), '[ReceiptDate]',isnull(Convert(nvarchar(100),FR1.ReceiptDate),'')
																					), '[ReceiptAmount]',isnull(Convert(nvarchar(100),FR1.ReceiptAmount),'')
																					), '[ChequeNo]',isnull(Convert(nvarchar(100),FR1.ChequeNo),'')
																					),'[BankName]',isnull(FR1.BankName,'')
																					),'[FeeName]',isnull(FM1.Title,'')
																					), '[FeeAmount]',isnull(Convert(nvarchar(100),Sum(FRD.Amount)),'0.00')
																					),'[FeeHeadName]',ISNULL(FHM1.Title,'')
																					), '[FeeHeadAmount]',isnull(Convert(nvarchar(100),Sum(FRD.Amount)),'0.00')
																					), '[CollectionGroupName]',isNull(CGM1.Title,'')
																					),'[ReceiptFromNumber]',isnull(Convert(nvarchar(10),(Select Min(ReceiptNumber) from sFee.tReceipt where   FR.ReceiptDate =ReceiptDate AND IsObsolete = 0)),'')
																					), '[ReceiptToNumber]',isnull(Convert(nvarchar(10),(Select Max(ReceiptNumber) from sFee.tReceipt Where FR.ReceiptDate = ReceiptDate AND IsObsolete = 0)),''))
						from sFee.tReceipt FR1  INNER JOIN sFee.tReceiptDetail FRD1 ON FR1.Id = FRD1.ReceiptId
						INNER JOIN sFee.tFeeSetup FS1 ON FS1.Id = FRD1.FeeSetupId 
						INNER JOIN sStudent.vStudentAcademic SM1 ON SM1.ACademicId = FR1.SourceID AND SM1.OrgGRoupBatchId = FR1.OrgGRoupBatchId 						
						INNER JOIN sFee.tFeeMaster FM1 ON FM1.Id = FS1.FeeId --AND FRD.ReceiptId=FRD1.ReceiptId
						LEFT JOIN sFee.tCollectionGroupMaster CGM1 ON CGM1.Id = FS1.CollectionGroupId
						INNER JOIN sFee.tFeeHeadMaster FHM1 ON FHM1.Id = FM1.FeeHeadId  where   FR.ReceiptDate=FR1.ReceiptDate AND FR.Id=FR1.Id AND FR1.IsObsolete = 0  FOR XML PATH('')),1,1,'') else
						case When FR.PayTypeId = 1 then
						'ReceiptNo-'  + CONVERT(nvarchar,isnull(FR.ReceiptNumber,'')) + '('+BatchName+')'
						when FR.PayTypeId <> 1 then 
						'Receiptno-'  +  CONVERT(nvarchar,isnull(FR.ReceiptNumber,'')) + '('+BatchName+')'
						+ '  Cheque No. :' + ISNULL(FR.ChequeNo,'') + ' BankName :' + ISNULL(FR.BankName,'')  ENd 
						END as Narration,						
						FR.PayTypeId AS PayTypeId, 
						FR.Id  AS FeeId,
						FR.Id AS ReceiptId
				FROM sFee.tReceipt FR 
					INNER JOIN sFee.tReceiptDetail FRD ON FR.Id = FRD.ReceiptId
					INNER JOIN sStudent.vStudentAcademic SM ON SM.ACademicId = FR.SourceID AND SM.OrgGRoupBatchId = FR.OrgGRoupBatchId 
					INNER JOIN sFee.tFeeSetup FS ON FS.Id = FRD.FeeSetupId
					INNER JOIN sFee.tFeeMaster FM ON FM.Id = FS.FeeId
					INNER JOIN sFee.tFeeHeadMaster FHM ON FHM.Id = FM.FeeHeadId
					INNER JOIN sFinanceCommon.tPayTypeMaster PM ON PM.Id = FR.PayTypeId
					INNER JOIN sSetup.tOrganizationMaster OM ON  OM.Id=SM.OrgId
					LEFT JOIN sSetup.tOrgSubGroupMaster OSM ON OSM.id=OM.OrgSubGroupId

					WHERE	FR.ReceiptBookId= @rReceiptBookId 
							AND FR.IsObsolete = 0 
							AND FR.OrgGroupBatchId = @rOrgGroupBatchId
							AND FR.ReceiptDate >= @rReceiptDate AND FR.ReceiptDate <= @rToReceiptDate
							AND (FR.PayTypeId = @rPayTypeId OR @rPayTypeId = 0)
				group by ReceiptDate,PM.Title,FR.PayTypeId,FR.Id,isnull(FR.ReceiptNumber,'') , BatchName,ISNULL(FR.BankName,''),ISNULL(FR.ChequeNo,'')
				Order By FR.Id,ReceiptDate Desc
			END			
			
END
GO
PRINT N'Altering [sTally].[pReceiptTallyPostingAdd]...';


GO
ALTER PROCEDURE [sTally].[pReceiptTallyPostingAdd]	 
      @rXmlDoc nvarchar(MAX),     
      @rReceiptId Int,
      @rOrgGroupBatchId Int,
      @rVoucherNumber Int    
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

SET @rXmlDoc=REPLACE(@rXmlDoc,'&','AND')
DECLARE @lInterrorCode INT,
			@intDoc INT
		
	BEGIN TRY
	 BEGIN TRANSACTION	
		
	EXECUTE @lInterrorCode = sp_xml_preparedocument @intDoc OUTPUT, @rXmlDoc
	
	
			
		
	
	UPDATE sTally.tReceiptPosting
	SET
		TransactionDate=A1.ReceiptDate,
		ReceiptBookId=@rReceiptId,
		StudentId=A1.FeeId,
		OrgGroupBatchId=@rOrgGroupBatchId,
		PayTypeId=A1.PayTypeId,		
		Amount = A1.Amount,
		Narration = A1.Narration
	FROM
	(
		SELECT	
			FeeId,
			PayTypeId,
			Amount,
			Narration,
			ReceiptDate			
			FROM OPENXML (@intDoc, '/Table/ReceiptTallyPosting', 1)
			WITH
			(
				FeeId bigint '@FeeId',
				PayTypeId int '@PayTypeId',
				Amount decimal(12,2) '@Amount',
				Narration nvarchar(500) '@Narration',
				ReceiptDate Date '@ReceiptDate'	
			))A1 INNER JOIN sTally.tReceiptPosting RP ON RP.StudentId = A1.FeeId AND RP.OrgGroupBatchId=@rOrgGroupBatchId
			AND RP.ReceiptBookId=@rReceiptId 
			AND RP.TransactionDate= A1.ReceiptDate --AND RP.Narration = A1.Narration
	
	

	INSERT INTO sTally.tReceiptPosting
		(
		TransactionDate,
		ReceiptBookId,
		FeeId,
		StudentId,
		OrgGroupBatchId,
		PayTypeId,		
		VoucherNumber,
		Amount,
		Narration
		)
	SELECT
		A.ReceiptDate,
		@rReceiptId,
		0,
		A.FeeId,
		@rOrgGroupBatchId,
		A.PayTypeId,
		@rVoucherNumber  + ROW_NUMBER() OVER(ORDER BY ReceiptBookId),
		A.Amount,
		A.Narration
		
	FROM OPENXML (@intDoc, '/Table/ReceiptTallyPosting', 1)
	WITH
	(
		FeeId bigint '@FeeId',
		PayTypeId int '@PayTypeId',
		Amount decimal(12,2) '@Amount',
		Narration nvarchar(500) '@Narration',
		ReceiptDate Date '@ReceiptDate'			
	)A LEFT JOIN sTally.tReceiptPosting RP ON RP.StudentId = A.FeeId AND RP.OrgGroupBatchId=@rOrgGroupBatchId
	AND RP.ReceiptBookId=@rReceiptId 
	AND RP.TransactionDate= A.ReceiptDate	
	WHERE RP.StudentId IS NULL
	
	SELECT 101 AS RESULT
	
	COMMIT TRANSACTION		
	END TRY

	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END
GO
PRINT N'Creating [sAdmission].[pRptAdmissionInquiryListGet]...';


GO

CREATE PROCEDURE [sAdmission].[pRptAdmissionInquiryListGet]
	
	 @rBuilder1 nvarchar(max)='',
	 @rBuilder2 nvarchar(max)='',
	 @rClassId int = 0,
	 @rOrgId int = 0,
	 @rDepartmentId int = 0,
	 @rFromDate datetime='',
	 @rToDate datetime='',
	 @rFromMeritMarks decimal(10,2) = 0,
	 @rToMeritMarks decimal(10,2)=0,
	 @rInquiryStatusId int =0,
	 @rBatchId int = 0 
	 	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 

BEGIN 	

	SELECT 
	
			@rBuilder1 AS Builder1,
			@rBuilder2 AS Builder2,
			AI.Id InquiryId,
			ISNULL(AI.Number,0) AS InquiryNumber,
			AI.ForClassId AS ClassId,
			ISNULL(CM.Title,'') AS ClassName,
			AI.FirstName,
			AI.MiddleName,
			AI.LastName,
			[sStudent].[fStudentNameGet](AI.FirstName , ISNULL(AI.MiddleName,'') , AI.LastName,OM.StudentNameFormatId ) AS StudentName,
			ISNULL(AI.InquiryDate,'') AS AdmissionInquiryDate,
			ISNULL(AI.FatherContactNo,'') AS FatherContactNo,
			ISNULL(AI.MotherContactNo,'') AS MotherContactNo,
			SM.Title AS InquiryStatus

  FROM 
            sAdmission.tAdmissionInquiry AI
            INNER JOIN sAdmission.tInquiryStatusMaster SM ON SM.Id=AI.InquiryStatusId 
			INNER JOIN sSetup.tClassMaster CM ON CM.Id=AI.ForClassId
			INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=AI.OrgId
  WHERE  
	((AI.InquiryDate>= @rFromDate) OR (ISNULL(@rFromDate,'')=''))
      AND ((AI.InquiryDate <= @rToDate) OR (ISNULL(@rToDate,'')=''))
	  AND ((AI.ForClassId=@rClassId) OR (@rClassId=0)) 
	  AND ((SM.Id=@rInquiryStatusId) OR (@rInquiryStatusId=0))
	  AND (AI.OrgId=@rOrgId OR @rOrgId=0)
	  AND ((CM.DepartmentId=@rDepartmentId) or (@rDepartmentId = 0))
	  AND ((AI.MeritMarks >= @rFromMeritMarks) OR (@rFromMeritMarks=0))
	  AND ((AI.MeritMarks <= @rToMeritMarks) OR (@rToMeritMarks=0))	
	  AND ((AI.ForBatchId=@rBatchId) OR (@rBatchId=0))		    
END
GO
PRINT N'Creating [sAlert].[pIssueBookAllGet]...';


GO
CREATE PROCEDURE [sAlert].[pIssueBookAllGet]
	 
	 @rSourceTypeId smallint,
	 @rFromDate date,
	 @rToDate date,
	 @rOrgId smallint,
	 @rBatchId int,
	 @rDepartmentId int = 0,
	 @rClassId int = 0,
	 @rDivisionId int = 0,
	 @rTemplate nvarchar(max),	 	
	 @rMailSubject nvarchar(max)=''
	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			
BEGIN 	
		SELECT distinct
			SC.OrgGroupId,
			SC.OrgId,
			SC.AcademicId,
			SC.Code,
			SC.Name AS Name,
			SC.ParentContactNo AS MobileNo,	
			SC.ParentEmail AS EmailId,
			SC.WhatsAppContactNo,
			replace (
			replace (
			replace(@rTemplate,'[Name]',SC.Name),			
			'[BookCode - BookName - IssueDate]',(SELECT STUFF ((SELECT distinct ',' +' ' + BM1.Code +' - '+BM1.Title+' - '+ CONVERT(nvarchar(100), BL1.LogDate, 103)
					From sLibrary.tBookLog BL1
					INNER JOIN sLibrary.tBookMaster BM1 ON BM1.Id=BL1.ResourceSourceId
					INNER JOIN sStudent.vStudentCommunication SC1 ON SC1.StudentId = BL1.ReaderSourceId AND SC1.BatchId = BL1.BatchId  AND BL1.ReaderSourceTypeId=2003
					INNER JOIN sSetup.tBatchMaster BMM1 ON BMM1.Id = BL1.BatchId 
					INNER JOIN sSetup.tOrganizationMaster OM1 ON OM1.Id = BL1.OrgId
					INNER JOIN sLibrary.tLibraryMaster LM1 ON LM.Id=BL1.LibraryId
					WHERE
							(BL1.ReaderSourceTypeId=@rSourceTypeId OR @rSourceTypeId=0)
						AND ((BL1.LogDate >= @rFromDate) OR (ISNULL(@rFromDate,'')=''))
						AND ((BL1.LogDate <= @rToDate) OR (ISNULL(@rToDate,'')=''))
						AND (BL1.OrgId = @rOrgId)
						AND (BL1.BatchId = @rBatchId)
						AND (BL1.ReaderSourceTypeId != 0)
						AND ((SC1.DepartmentId=@rDepartmentId) OR (@rDepartmentId=0))
						AND ((SC1.ClassId=@rClassId) OR (@rClassId=0))
						AND ((SC1.DivisionId=@rDivisionId) OR (@rDivisionId=0))
						AND BL1.IsCurrentStatus=1 
						AND BL1.ReaderSourceId=BL.ReaderSourceId	
						AND (SC.StatusId=1) AND SC.IsBlockAllAlert=0	
                    FOR XML PATH('')),1,1,''))),
			'[LibraryName]',LM.Title)
			 AS Template,
			replace (
			replace (
			replace(@rTemplate,'[Name]',SC.Name),			
			'[BookCode - BookName - IssueDate]',(SELECT STUFF ((SELECT distinct ',' +' ' + BM1.Code +' - '+BM1.Title+' - '+ CONVERT(nvarchar(100), BL1.LogDate, 103)
					From sLibrary.tBookLog BL1
					INNER JOIN sLibrary.tBookMaster BM1 ON BM1.Id=BL1.ResourceSourceId AND BL1.ReaderSourceTypeId=2003
					AND BL1.ReaderSourceId=BL.ReaderSourceId  AND BL1.ReaderSourceId = SC.StudentId AND BL1.BatchId =SC.BatchId  
					AND BMM.Id = BL1.BatchId  AND BL1.OrgId = OM.Id AND  BL1.LibraryId =LM.Id
					AND (SC.StatusId=1) AND SC.IsBlockAllAlert=0
					AND BL1.IsCurrentStatus=1 		
                    FOR XML PATH('')),1,1,'') )),
			'[LibraryName]',LM.Title)
			 AS MsgText,
			'2003' AS SourceTypeId,
			SC.StudentId AS SourceId,
			@rMailSubject AS Subject,
			2003 AS ReceiverSourceTypeId,
			SC.StudentId AS ReceiverSourceId,
			LM.Title AS LibraryName
	From 
		sLibrary.tBookLog BL
		INNER JOIN sLibrary.tBookMaster BM ON BM.Id=BL.ResourceSourceId
		INNER JOIN sStudent.vStudentCommunication SC ON SC.StudentId = BL.ReaderSourceId AND SC.BatchId = BL.BatchId  AND BL.ReaderSourceTypeId=2003
		INNER JOIN sSetup.tBatchMaster BMM ON BMM.Id = BL.BatchId 
		INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id = BL.OrgId
		INNER JOIN sCommon.tStatusMaster SM ON SM.Id=BL.StatusId
		INNER JOIN sLibrary.tLibraryMaster LM ON LM.Id=BL.LibraryId
	WHERE
		(BL.ReaderSourceTypeId=@rSourceTypeId OR @rSourceTypeId=0)
		AND ((BL.LogDate >= @rFromDate) OR (ISNULL(@rFromDate,'')=''))
		AND ((BL.LogDate <= @rToDate) OR (ISNULL(@rToDate,'')=''))
				AND (BL.OrgId = @rOrgId OR @rOrgId=0)
		AND (BL.BatchId = @rBatchId OR @rBatchId = 0)
		AND (BL.ReaderSourceTypeId != 0)
		AND ((SC.DepartmentId=@rDepartmentId) OR (@rDepartmentId=0))
		AND ((SC.ClassId=@rClassId) OR (@rClassId=0))
		AND ((SC.DivisionId=@rDivisionId) OR (@rDivisionId=0))
		AND (SC.StatusId=1) AND SC.IsBlockAllAlert=0	
		AND BL.IsCurrentStatus=1 

	UNION 

	SELECT distinct
			OM.OrgGroupId,
			EC.OrgId,
			EC.EmployeeId As AcademicId,
			EC.Code,
			EC.EmployeeName AS Name,
			EC.MobileNumber AS MobileNo,	
			EC.CompanyEmail AS EmailId,
			EC.WhatsAppContactNo,
			replace (
			replace (
			replace(@rTemplate,'[Name]',EC.EmployeeName),			
			'[BookCode - BookName - IssueDate]',(SELECT STUFF ((SELECT distinct ',' +' ' + BM1.Code +' - '+BM1.Title+' - '+ CONVERT(nvarchar(100), BL1.LogDate, 103)
						From sLibrary.tBookLog BL1
							INNER JOIN sLibrary.tBookMaster BM1 ON BM1.Id=BL1.ResourceSourceId AND BL1.ReaderSourceTypeId=95
							AND BL1.ReaderSourceId=BL.ReaderSourceId  AND BL1.ReaderSourceId = EC.EmployeeId 
							AND BL1.OrgId = OM.Id AND  BL1.LibraryId =LM.Id AND ODM.Id = EC.DepartmentId
							AND ISNULL(EC.IsOtherUser,0) = 0  AND EC.StatusId = 7
							AND BL1.IsCurrentStatus=1
                    FOR XML PATH('')),1,1,'') )),
			'[LibraryName]',LM.Title)
			 AS Template,
			replace (
			replace (
			replace(@rTemplate,'[Name]',EC.EmployeeName),			
			'[BookCode - BookName - IssueDate]',(SELECT STUFF ((SELECT distinct ',' +' ' + BM1.Code +' - '+BM1.Title+' - '+ CONVERT(nvarchar(100), BL1.LogDate, 103)
						From sLibrary.tBookLog BL1
							INNER JOIN sLibrary.tBookMaster BM1 ON BM1.Id=BL1.ResourceSourceId AND BL1.ReaderSourceTypeId=95
							AND BL1.ReaderSourceId=BL.ReaderSourceId  AND BL1.ReaderSourceId = EC.EmployeeId 
							AND BL1.OrgId = OM.Id AND  BL1.LibraryId =LM.Id AND ODM.Id = EC.DepartmentId
							AND ISNULL(EC.IsOtherUser,0) = 0  AND EC.StatusId = 7
							AND BL1.IsCurrentStatus=1	
                    FOR XML PATH('')),1,1,'') )),
			'[LibraryName]',LM.Title)
			 AS MsgText,
			'95' AS SourceTypeId,
			EC.EmployeeId AS SourceId,
			@rMailSubject AS Subject,
			95 AS ReceiverSourceTypeId,
			EC.EmployeeId AS ReceiverSourceId,
			LM.Title AS LibraryName
	         
	FROM 
		sLibrary.tBookLog BL
		INNER JOIN sLibrary.tBookMaster BM ON BM.Id=BL.ResourceSourceId
		INNER JOIN sHrm.vEmployeeCommunication EC ON EC.EmployeeId=BL.ReaderSourceId AND BL.ReaderSourceTypeId=95 
		INNER JOIN sSetup.tDepartmentMaster ODM ON ODM.Id = EC.DepartmentId
		INNER JOIN [sSetup].[tOrganizationMaster] OM ON OM.Id = EC.OrgId
		INNER JOIN sCommon.tStatusMaster SM ON SM.Id=BL.StatusId
		INNER JOIN sLibrary.tLibraryMaster LM ON LM.Id=BL.LibraryId

	WHERE 

		 (BL.OrgId = @rOrgId OR @rOrgId=0)
		AND (BL.BatchId = @rBatchId OR @rBatchId = 0)
		AND ((EC.DepartmentId=@rDepartmentId) OR (@rDepartmentId=0))
		AND ((BL.LogDate >= @rFromDate) OR (ISNULL(@rFromDate,'')=''))
		AND ((BL.LogDate <= @rToDate) OR (ISNULL(@rToDate,'')=''))
		AND (BL.ReaderSourceTypeId=@rSourceTypeId OR @rSourceTypeId=0)
		AND ISNULL(EC.IsOtherUser,0) = 0  AND EC.StatusId = 7	
		AND BL.IsCurrentStatus=1 
		 
END
GO
PRINT N'Creating [sAssessment].[pExamSettingGet]...';


GO

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

BEGIN 		
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
			--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       
            EM.Id=@rExamId
			order by ESGM.Title Desc
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,
			EM.IsPercentage,
			ISNULL(Em.IsRemovePercentageForFail,0) AS IsFailPercentageZero,
			EM.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       
            EM.Id=@rExamId
			order by ESGM.Title Desc

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(IsDisplayHighestDivisionGrade,0) AS IsDisplayHighestDivisionGrade,
			ISNULL(IsDisplayTeacherSign,0) AS IsDisplayTeacherSign,
			ISNULL(IsDisplayPrincipalSign,0) AS IsDisplayPrincipalSign,
			ISNULL(IsDisplayActivityGrade,0) AS IsDisplayActivityGrade
			--ISNULL(EM.DetailAdjustment,0) AS DetailAdjustment,
			--ISNULL(EM.FooterAdjustment,0) AS FooterAdjustment,
			--ISNULL(EM.ExamMaximum,0) AS ExamMaximum
   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       
            EM.Id=@rExamId
			order by ESGM.Title Desc

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       
            EM.Id=@rExamId
			order by ESGM.Title Desc
END


		
END
GO
PRINT N'Creating [sIp].[pImportStockItemCategory]...';


GO
CREATE PROCEDURE [sIp].[pImportStockItemCategory]
	  
	@rStrXml nvarchar(max),
	@rStoreLocationId int,
	@rScreenId int,
	@rOrgId int,
	@rCreatedUserId Bigint
		
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

DECLARE @lInterrorCode INT,@intDoc INT
EXECUTE @lInterrorCode = sp_xml_preparedocument @intDoc OUTPUT, @rStrXml

BEGIN
		
	BEGIN TRY

	CREATE TABLE #tempTable  
	(
		Id bigint identity,
		Code nvarchar(100),
		Name nvarchar(100)
	)

	INSERT INTO #tempTable
	(
		Code,
		Name
	)

	SELECT  A.Code,A.Name
			FROM OPENXML (@intDoc, '/Table/ImportStockMainMasterItem', 1)
	WITH
	(
		Code nvarchar(100) '@Code',
		Name nvarchar(100) '@Name'			
	) A
	
	EXEC sp_xml_removedocument @intDoc

	DECLARE @lCode nvarchar(100),@lTitle nvarchar(100)

	DECLARE @lCount int,@lId int

	SELECT @lCount = COUNT(Id) FROM #tempTable
	SELECT Top 1 @lId=Id FROM #tempTable

	WHILE @lId <= @lCount 
	BEGIN
		select @lCode = Code from #tempTable where Id = @lId
		select @lTitle = Name from #tempTable where Id = @lId
		
		IF Exists (SELECT Id FROM sIp.tStockItemCategoryMaster WHERE Code = @lCode AND StoreLocationId  = @rStoreLocationId)
		BEGIN
			Select -102 AS Result, 'Code Already Exists ' + @lCode AS Msg
			RETURN
		END

		IF EXISTS(SELECT Id FROM sIp.tStockItemCategoryMaster WHERE Title = @lTitle AND StoreLocationId  = @rStoreLocationId)
		BEGIN
		 Select -103 AS Result, 'Name Already Exists ' + @lTitle AS Msg
		 RETURN
		END

		SET @lId=@lId+1
	END
		

	Declare @lIsStatic1to100 bit, @lNextNumber BigInt, @lSeed int

	SET @lIsStatic1to100 = (SELECT IsStatic1to100 FROM sCommon.tCommonMaster AS tCM  LEFT JOIN sSecurity.tScreen SS ON ((tCM.ScreenId = SS.Id) AND (SS.IsObsolete = 0)) WHERE (ScreenId = @rScreenId))
	
	SET @lSeed = (CASE WHEN @lIsStatic1to100 = 1 THEN 101 ELSE 1 END)

	DECLARE @lCount1 int,@lId1 int
	SELECT @lCount1 = COUNT(Id) FROM #tempTable
	SELECT Top 1 @lId1=Id FROM #tempTable

	WHILE @lId1 <= @lCount1 
	BEGIN
		EXECUTE  sCommon.pTableRowIdGet  @rOrgId, 'sIp.tStockItemCategoryMaster', 0, @lSeed, 1,@outNextNumber = @lNextNumber output
		SET @lNextNumber = @lNextNumber

		select @lCode = Code from #tempTable where Id = @lId1
		select @lTitle = Name from #tempTable where Id = @lId1
		INSERT INTO sIp.tStockItemCategoryMaster
			(Id, Code, Title, IsObsolete, CreatedUserId,  CreatedDateTime, UpdatedUserId, UpdatedDateTime, StoreLocationId)
		VALUES
			(@lNextNumber, @lCode, @lTitle, 0, @rCreatedUserId, sCommon.fGetDate(), @rCreatedUserId, sCommon.fGetDate(), @rStoreLocationId)

		SET @lId1=@lId1+1
	END

	Select 1 AS Result, '' AS Msg

	DROP Table #tempTable
	END TRY

	BEGIN CATCH 
	SELECT -1 AS Result, error_message() AS Msg
	END CATCH	
END
GO
PRINT N'Creating [sIp].[pImportStockItemSubGroup]...';


GO
CREATE PROCEDURE [sIp].[pImportStockItemSubGroup]
	  
	@rStrXml nvarchar(max),
	@rStoreLocationId int,
	@rOrgGroupId int,
	@rCreatedUserId Bigint
		
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

DECLARE @lInterrorCode INT,@intDoc INT
EXECUTE @lInterrorCode = sp_xml_preparedocument @intDoc OUTPUT, @rStrXml

BEGIN
		
	BEGIN TRY

	CREATE TABLE #tempTable  
	(
		Id bigint identity,
		Code nvarchar(100),
		Name nvarchar(100),
		ItemMainGroup nvarchar(100)
	)

	INSERT INTO #tempTable
	(
		Code,
		Name,
		ItemMainGroup
	)

	SELECT  A.Code,A.Name,A.ItemMainGroup
			FROM OPENXML (@intDoc, '/Table/ImportStockMainMasterItem', 1)
	WITH
	(
		Code nvarchar(100) '@Code',
		Name nvarchar(100) '@Name',
		ItemMainGroup nvarchar(100) '@ItemMainGroup'		
	) A
	
	EXEC sp_xml_removedocument @intDoc

	DECLARE @lCode nvarchar(100),@lTitle nvarchar(100),@lItemMainGroup nvarchar(100)

	DECLARE @lCount int,@lId int

	SELECT @lCount = COUNT(Id) FROM #tempTable
	SELECT Top 1 @lId=Id FROM #tempTable

	WHILE @lId <= @lCount 
	BEGIN
		select @lCode = Code from #tempTable where Id = @lId
		select @lTitle = Name from #tempTable where Id = @lId
		SELECT @lItemMainGroup= ItemMainGroup from #tempTable where Id = @lId

		IF Exists (SELECT Id FROM sIp.tItemSubGroupMaster WHERE Code = @lCode AND StoreLocationId  = @rStoreLocationId)
		BEGIN
			Select -102 AS Result, 'Code Already Exists ' + @lCode AS Msg
			RETURN
		END

		IF EXISTS(SELECT Id FROM sIp.tItemSubGroupMaster WHERE Title = @lTitle AND StoreLocationId  = @rStoreLocationId)
		BEGIN
		 Select -103 AS Result, 'Name Already Exists ' + @lTitle AS Msg
		 RETURN
		END

		IF NOT EXISTS(SELECT Id FROM sIp.tItemMainGroupMaster WHERE Title = @lItemMainGroup AND StoreLocationId  = @rStoreLocationId)
		BEGIN
		 Select -100 AS Result, 'Item Main Group Not Exists ' + @lItemMainGroup AS Msg
		 RETURN
		END

		SET @lId=@lId+1
	END
		

	DECLARE @lCount1 int,@lId1 int
	SELECT @lCount1 = COUNT(Id) FROM #tempTable
	SELECT Top 1 @lId1=Id FROM #tempTable

	WHILE @lId1 <= @lCount1 
	BEGIN
		DECLARE @lNextNumber INT,@lItemMainGroupId int
		EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sIp.tItemSubGroupMaster', 0, 1, 1,@outNextNumber = @lNextNumber output

		select @lCode = Code from #tempTable where Id = @lId1
		select @lTitle = Name from #tempTable where Id = @lId1
		SELECT @lItemMainGroup= ItemMainGroup from #tempTable where Id = @lId1

		SET @lItemMainGroupId =(Select Id from sIp.tItemMainGroupMaster WHERE Title = @lItemMainGroup AND StoreLocationId  = @rStoreLocationId)
		
		INSERT INTO sIp.tItemSubGroupMaster
		(
			Id,
			Code,
			Title,
			ItemMainGroupId,			
			IsObsolete,
			CreatedUserId,
			CreatedDateTime,
			UpdatedUserId,
			UpdatedDateTime,
			OrgGroupId,
			StoreLocationId
		)
	VALUES
		(
			@lNextNumber,
			@lCode,
			@lTitle,
			@lItemMainGroupId,			
			0,
			@rCreatedUserId,
			sCommon.fGetDate(),
			@rCreatedUserId,
			sCommon.fGetDate(),
			@rOrgGroupId,
			@rStoreLocationId
		)

		SET @lId1=@lId1+1
	END

	Select 1 AS Result, '' AS Msg

	DROP Table #tempTable
	END TRY

	BEGIN CATCH 
	SELECT -1 AS Result, error_message() AS Msg
	END CATCH	
END
GO
PRINT N'Creating [sIp].[pImportStockMainGroupMaster]...';


GO
CREATE PROCEDURE [sIp].[pImportStockMainGroupMaster]
	  
	@rStrXml nvarchar(max),
	@rStoreLocationId int,
	@rScreenId int,
	@rOrgId int,
	@rCreatedUserId Bigint
		
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

DECLARE @lInterrorCode INT,@intDoc INT
EXECUTE @lInterrorCode = sp_xml_preparedocument @intDoc OUTPUT, @rStrXml

BEGIN
		
	BEGIN TRY

	CREATE TABLE #tempTable  
	(
		Id bigint identity,
		Code nvarchar(100),
		Name nvarchar(100)
	)

	INSERT INTO #tempTable
	(
		Code,
		Name
	)

	SELECT  A.Code,A.Name
			FROM OPENXML (@intDoc, '/Table/ImportStockMainMasterItem', 1)
	WITH
	(
		Code nvarchar(100) '@Code',
		Name nvarchar(100) '@Name'			
	) A
	
	EXEC sp_xml_removedocument @intDoc

	
	DECLARE @lCode nvarchar(100),@lTitle nvarchar(100)

	DECLARE @lCount int,@lId int

	SELECT @lCount = COUNT(Id) FROM #tempTable
	SELECT Top 1 @lId=Id FROM #tempTable

	WHILE @lId <= @lCount 
	BEGIN
		select @lCode = Code from #tempTable where Id = @lId
		select @lTitle = Name from #tempTable where Id = @lId
		
		IF Exists (SELECT Id FROM sIp.tItemMainGroupMaster WHERE Code = @lCode AND StoreLocationId  = @rStoreLocationId)
		BEGIN
			Select -102 AS Result, 'Code Already Exists ' + @lCode AS Msg
			RETURN
		END

		IF EXISTS(SELECT Id FROM sIp.tItemMainGroupMaster WHERE Title = @lTitle AND StoreLocationId  = @rStoreLocationId)
		BEGIN
		 Select -103 AS Result, 'Name Already Exists ' + @lTitle AS Msg
		 RETURN
		END

		SET @lId=@lId+1
	END
		

	Declare @lIsStatic1to100 bit, @lNextNumber BigInt, @lSeed int

	SET @lIsStatic1to100 = (SELECT IsStatic1to100 FROM sCommon.tCommonMaster AS tCM  LEFT JOIN sSecurity.tScreen SS ON ((tCM.ScreenId = SS.Id) AND (SS.IsObsolete = 0)) WHERE (ScreenId = @rScreenId))
	
	SET @lSeed = (CASE WHEN @lIsStatic1to100 = 1 THEN 101 ELSE 1 END)

	DECLARE @lCount1 int,@lId1 int
	SELECT @lCount1 = COUNT(Id) FROM #tempTable
	SELECT Top 1 @lId1=Id FROM #tempTable

	WHILE @lId1 <= @lCount1 
	BEGIN
		EXECUTE  sCommon.pTableRowIdGet  @rOrgId, 'sIp.tItemMainGroupMaster', 0, @lSeed, 1,@outNextNumber = @lNextNumber output
		SET @lNextNumber = @lNextNumber

		select @lCode = Code from #tempTable where Id = @lId1
		select @lTitle = Name from #tempTable where Id = @lId1
		INSERT INTO sIp.tItemMainGroupMaster
			(Id, Code, Title, CreatedUserId,  CreatedDateTime, IsObsolete, StoreLocationId)
		VALUES
			(@lNextNumber, @lCode, @lTitle, @rCreatedUserId, sCommon.fGetDate(), 0,@rStoreLocationId)

		SET @lId1=@lId1+1
	END

	Select 1 AS Result, '' AS Msg

	DROP Table #tempTable
	END TRY

	BEGIN CATCH 
	SELECT -1 AS Result, error_message() AS Msg
	END CATCH	
END
GO


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



ALTER TABLE sAccounting.tLedgerGroupMaster
DROP CONSTRAINT PK_tLedgerGroupMaster;   


GO  
ALTER TABLE sAccounting.tLedgerMaster
DROP CONSTRAINT PK_tLedgerMaster;   
GO