CREATE SCHEMA [sEMeeting];

GO
CREATE SCHEMA [sPurchase];


GO
CREATE SCHEMA [sWf];
  
GO
--IF EXISTS (select top 1 1 from [sPurchase].[tGoodsReceivedNoteDetail])
--    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT

--GO


--IF EXISTS (select top 1 1 from [sPurchase].[tInvoiceGenerateDetail])
--    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT

--GO


--IF EXISTS (select top 1 1 from [sPurchase].[tQualityCheckDetail])
--    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT

--GO


--IF EXISTS (select top 1 1 from [sPurchase].[tSalesReturnDetail])
--    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT

--GO


--IF EXISTS (select top 1 1 from [sPurchase].[tDocumentManagement])
--    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT


--GO
--ALTER TABLE [sPurchase].[tSalesReturn] DROP CONSTRAINT [DF__tSalesRet__IsObs__544A41E7];

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'pGoodsReceivedNoteAdd')  
DROP PROCEDURE [sPurchase].[pGoodsReceivedNoteAdd]
GO
DROP TYPE [sPurchase].[GoodsReceivedNoteDetailType];
GO

CREATE TYPE [sPurchase].[GoodsReceivedNoteDetailType] AS TABLE (
    [Id]                    BIGINT          NOT NULL,
    [PurchaseOrderDetailId] BIGINT          NOT NULL,
    [StockItemId]           BIGINT          NOT NULL,
    [POQty]                 DECIMAL (12, 2) NOT NULL,
    [ReceivedQty]           DECIMAL (12, 2) NOT NULL,
    [PreviousGRNQty]        DECIMAL (12, 2) NULL,
    [UnitPrice]             DECIMAL (12, 2) NOT NULL,
    [Remark]                NVARCHAR (300)  NULL);

GO

CREATE PROCEDURE [sPurchase].[pGoodsReceivedNoteAdd]
		@rId bigint,
		@rPurchaseRequestId bigint,
		@rPurchaseOrderId bigint,
		@rVendorId int,
		@rPODueDate datetime,	
		@rRequestedBy nvarchar(100),
		@rOrgGroupId smallint,
		@rLocationId int,
		@rStatusId smallint,	
		@rBillToAddress nvarchar(1000),
		@rUserId bigint,
		@rDetailList sPurchase.GoodsReceivedNoteDetailType READONLY
 
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
	BEGIN TRANSACTION

		If(@rId > 0)
		Begin
			Update sPurchase.tGoodsReceivedNote
			set 
				StatusId = @rStatusId,
				PurchaseRequestId = @rPurchaseRequestId,
				PurchaseOrderId = @rPurchaseOrderId,
				VendorId = @rVendorId,
				PODueDate = @rPODueDate,
				BillToAddress = @rBillToAddress,
				UpdatedUserId = @rUserId,		
				UpdatedDateTime = GetUtcDate()
			where Id = @rId and IsObsolete = 0

			delete from sPurchase.tGoodsReceivedNoteDetail
			where GoodsReceivedNoteId = @rId

			DECLARE @lNextNumberGRN INT	 
			EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sPurchase.tGoodsReceivedNoteDetail',@lNextNumber = @lNextNumberGRN output
			set @lNextNumberGRN=@lNextNumberGRN-1
			INSERT INTO sPurchase.tGoodsReceivedNoteDetail
			(
				Id,
				OrgGroupId,
				GoodsReceivedNoteId,
				PurchaseOrderDetailId,
				StockItemId,
				POQty,
				ReceivedQty,
				PreviousGRNQty,
				UnitPrice,
				Remark,
				CreatedUserId,
				CreatedDateTime
			)
			select	@lNextNumberGRN+Id,
					@rOrgGroupId,
					@rId,
					PurchaseOrderDetailId,
					StockItemId,
					POQty,
					ReceivedQty,
					PreviousGRNQty,
					UnitPrice,
					Remark,
					@rUserId, 
					GETUTCDATE() 
			from @rDetailList
			set @lNextNumberGRN=@lNextNumberGRN+(select Count(Id) from @rDetailList)
			EXECUTE sCommon.pTableRowIdModify @rOrgGroupId, @rTableTitle = 'sPurchase.tGoodsReceivedNoteDetail', @rCurrentNumber = @lNextNumberGRN

			Select @rId
		End
		Else
		Begin
			DECLARE @lNextNumber INT	 
			EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sPurchase.tGoodsReceivedNote',@lNextNumber = @lNextNumber output	
			--------------select @lNextNumber
			--DECLARE @loNextNumber nVarchar(20)
			--EXECUTE sCommon.pPrefixSetupNextNumberGet @rOrgId = @rOrgGroupId, @rPrefixFunctionalityId = 1005,@rStoreLocationId = @rLocationId,
			--------------@rNextNumber = @loNextNumber OUTPUT

	 		INSERT INTO sPurchase.tGoodsReceivedNote
			(
				Id,
				GRNNumber,
				PurchaseRequestId,
				PurchaseOrderId,
				PODueDate,
				RequestedBy, 
				OrgGroupId,
				LocationId, 			 
				StatusId,
				VendorId,
				BillToAddress,
				CreatedUserId,		
				CreatedDateTime
	
			)
			VALUES
			(
				@lNextNumber,
				@lNextNumber,
				@rPurchaseRequestId,
				@rPurchaseOrderId,
				@rPODueDate,
				@rRequestedBy,	 
				@rOrgGroupId,
				@rLocationId, 		 
				@rStatusId,
				@rVendorId,
				@rBillToAddress,
				@rUserId,		
				GETUTCDATE()
			)
 
			set @rId = @lNextNumber  
			
			DECLARE @lNextNumberGRND INT	 
			EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sPurchase.tGoodsReceivedNoteDetail',@lNextNumber = @lNextNumberGRND output	
			set @lNextNumberGRND=@lNextNumberGRND-1

	 		INSERT INTO sPurchase.tGoodsReceivedNoteDetail
			(
				Id,
				OrgGroupId,
				GoodsReceivedNoteId,
				PurchaseOrderDetailId,
				StockItemId,
				POQty,
				ReceivedQty,
				PreviousGRNQty,
				UnitPrice,
				Remark,
				CreatedUserId,
				CreatedDateTime
			)
			select	@lNextNumberGRND+Id,
					@rOrgGroupId,
					@rId,
					PurchaseOrderDetailId,
					StockItemId,
					POQty,
					ReceivedQty,
					PreviousGRNQty,
					UnitPrice,
					Remark,
					@rUserId, 
					GETUTCDATE() 
			from @rDetailList
						
			set @lNextNumberGRND=@lNextNumberGRND+(select Count(Id) from @rDetailList)
			EXECUTE sCommon.pTableRowIdModify @rOrgGroupId, @rTableTitle = 'sPurchase.tGoodsReceivedNoteDetail', @rCurrentNumber = @lNextNumberGRND

			select @rId

			Declare @typeId smallint=0
			Declare @Level smallint=0
			SELECT @typeId=WfTypeMasterId, @Level=WfLevel FROM sWf.tWfSetup WHERE WfFunctionalityId = 4
			IF(@typeId = 1)
			BEGIN
				exec [sWf].[pWfApprovalAdd] 0,@rId,2043,@rUserId,1,'',0,0,4,@rOrgGroupId
			END
			ELSE IF(@typeId = 2)
			Begin
				exec [sWf].[pWfApprovalAdd] 0,@rId,2043,@rUserId,2,'',1,@Level,4,@rOrgGroupId
			End
			ELSE IF(@typeId = 3)
			BEGIN
				exec [sWf].[pWfApprovalAdd] 0,@rId,2043,@rUserId,3,'',0,0,4,@rOrgGroupId
			END
			--select 1;
		End
	COMMIT TRAN -- Transaction Success!	
	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error

    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  

    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH
END

GO
--DROP PROCEDURE [sPurchase].[pInvoiceGenerateAdd];

--GO
--DROP PROCEDURE [sPurchase].[pQualityCheckAdd];


--GO
--DROP PROCEDURE [sPurchase].[pQuotationRequestAdd];


--GO
--DROP PROCEDURE [sPurchase].[pPurchaseOrderAdd];


--GO
--DROP PROCEDURE [sPurchase].[pPurchaseRequestAdd];

--GO
--DROP PROCEDURE [sPurchase].[pSalesReturnAdd];


--GO
--DROP TABLE [sPurchase].[tDocumentManagement];

--GO
--DROP PROCEDURE [sWf].[pWfApprovingAuthorityAdd];


--GO
--DROP PROCEDURE [sWf].[pWfApprovingAuthorityGet];

--GO
----DROP TYPE [sPurchase].[GoodsReceivedNoteDetailType];

--GO
--DROP TYPE [sPurchase].[InvoiceGenerateDetailType];


--GO
--DROP TYPE [sPurchase].[PurchaseOrderDetailType];


--GO
--DROP TYPE [sPurchase].[PurchaseRequestDetailType];


--GO
--DROP TYPE [sPurchase].[QualityCheckDetailType];


--GO
--DROP TYPE [sPurchase].[QuotationRequestDetailType];

--GO
--DROP TYPE [sPurchase].[SalesReturnDetailType];


--GO
--PRINT N'Creating Schema [sChatbot]...';

CREATE TYPE [sPurchase].[InvoiceGenerateDetailType] AS TABLE (
    [Id]                        BIGINT          NOT NULL,
    [InvoiceGenerateId]         BIGINT          NOT NULL,
    [GoodsReceivedNoteDetailId] BIGINT          NOT NULL,
    [StockItemId]               BIGINT          NOT NULL,
    [POQty]                     DECIMAL (12, 2) NOT NULL,
    [ReceivedQty]               DECIMAL (12, 2) NOT NULL,
    [AcceptedQty]               DECIMAL (12, 2) NOT NULL,
    [RejectedQty]               DECIMAL (12, 2) NULL,
    [UnitPrice]                 DECIMAL (12, 2) NOT NULL,
    [ReasonForRejection]        NVARCHAR (300)  NULL);

GO
CREATE TYPE [sPurchase].[PurchaseOrderDetailType] AS TABLE (
    [Id]                      BIGINT          NOT NULL,
    [PurchaseRequestDetailId] BIGINT          NOT NULL,
    [StockItemId]             BIGINT          NOT NULL,
    [TotalQty]                DECIMAL (12, 2) NOT NULL,
    [Remark]                  NVARCHAR (300)  NULL);


GO
CREATE TYPE [sPurchase].[PurchaseRequestDetailType] AS TABLE (
    [Id]           BIGINT          NOT NULL,
    [StockItemId]  BIGINT          NOT NULL,
    [RequestedQty] DECIMAL (12, 2) NOT NULL,
    [Remark]       NVARCHAR (300)  NULL);

GO
CREATE TYPE [sPurchase].[QualityCheckDetailType] AS TABLE (
    [Id]                        BIGINT          NOT NULL,
    [GoodsReceivedNoteDetailId] BIGINT          NOT NULL,
    [StockItemId]               BIGINT          NOT NULL,
    [POQty]                     DECIMAL (12, 2) NOT NULL,
    [ReceivedQty]               DECIMAL (12, 2) NOT NULL,
    [AcceptedQty]               DECIMAL (12, 2) NOT NULL,
    [RejectedQty]               DECIMAL (12, 2) NULL,
    [ReasonForRejection]        NVARCHAR (300)  NULL);

GO
CREATE TYPE [sPurchase].[QuotationRequestDetailType] AS TABLE (
    [Id]                      BIGINT          NOT NULL,
    [PurchaseRequestDetailId] BIGINT          NULL,
    [StockItemId]             BIGINT          NOT NULL,
    [RequestedQty]            DECIMAL (12, 2) NOT NULL,
    [Remark]                  NVARCHAR (300)  NULL,
    [TargetPrice]             DECIMAL (12, 2) NOT NULL);

GO
CREATE TYPE [sPurchase].[SalesReturnDetailType] AS TABLE (
    [Id]                        BIGINT          NOT NULL,
    [GoodsReceivedNoteDetailId] BIGINT          NOT NULL,
    [StockItemId]               BIGINT          NOT NULL,
    [POQty]                     DECIMAL (12, 2) NOT NULL,
    [ReceivedQty]               DECIMAL (12, 2) NOT NULL,
    [RejectedQty]               DECIMAL (12, 2) NULL,
    [CreditNoteIssueDate]       DATETIME        NULL,
    [ReasonForRejection]        NVARCHAR (300)  NULL);

GO
CREATE TYPE [sPurchase].[DocumentType] AS TABLE (
    [UploadFunctionalityId] SMALLINT        NOT NULL,
    [UserSourceTypeId]      SMALLINT        NULL,
    [UserSourceId]          BIGINT          NULL,
    [DocumentId]            BIGINT          NOT NULL,
    [DocumentName]          NVARCHAR (200)  NOT NULL,
    [CreatedUserId]         BIGINT          NOT NULL,
    [CreatedDateTime]       DATETIME        NOT NULL,
    [DocumentSize]          DECIMAL (12, 2) NULL,
    [OrgGroupBatchId]       INT             NULL);


GO

GO
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [sPurchase].[tmp_ms_xx_tGoodsReceivedNoteDetail] (
    [Id]                    BIGINT          NOT NULL,
    [OrgGroupId]            SMALLINT        NOT NULL,
    [GoodsReceivedNoteId]   BIGINT          NOT NULL,
    [PurchaseOrderDetailId] BIGINT          NOT NULL,
    [StockItemId]           BIGINT          NOT NULL,
    [POQty]                 DECIMAL (12, 2) NOT NULL,
    [ReceivedQty]           DECIMAL (12, 2) NOT NULL,
    [PreviousGRNQty]        DECIMAL (12, 2) NULL,
    [UnitPrice]             DECIMAL (12, 2) NOT NULL,
    [Remark]                NVARCHAR (300)  NULL,
    [CreatedUserId]         BIGINT          NULL,
    [CreatedDateTime]       DATETIME        NOT NULL,
    [UpdatedUserId]         BIGINT          NULL,
    [UpdatedDateTime]       DATETIME        NULL,
    CONSTRAINT [tmp_ms_xx_constraint_PK_sPurchase]].[tGoodsReceivedNoteDetails1] PRIMARY KEY CLUSTERED ([Id] ASC)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [sPurchase].[tGoodsReceivedNoteDetail])
    BEGIN
        INSERT INTO [sPurchase].[tmp_ms_xx_tGoodsReceivedNoteDetail] ([Id], [OrgGroupId], [GoodsReceivedNoteId], [StockItemId], [POQty], [ReceivedQty], [PreviousGRNQty], [UnitPrice], [Remark], [CreatedUserId], [CreatedDateTime], [UpdatedUserId], [UpdatedDateTime])
        SELECT   [Id],
                 [OrgGroupId],
                 [GoodsReceivedNoteId],
                 [StockItemId],
                 [POQty],
                 [ReceivedQty],
                 [PreviousGRNQty],
                 [UnitPrice],
                 [Remark],
                 [CreatedUserId],
                 [CreatedDateTime],
                 [UpdatedUserId],
                 [UpdatedDateTime]
        FROM     [sPurchase].[tGoodsReceivedNoteDetail]
        ORDER BY [Id] ASC;
    END

DROP TABLE [sPurchase].[tGoodsReceivedNoteDetail];

EXECUTE sp_rename N'[sPurchase].[tmp_ms_xx_tGoodsReceivedNoteDetail]', N'tGoodsReceivedNoteDetail';

EXECUTE sp_rename N'[sPurchase].[tmp_ms_xx_constraint_PK_sPurchase]].[tGoodsReceivedNoteDetails1]', N'PK_sPurchase].[tGoodsReceivedNoteDetails', N'OBJECT';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


GO
PRINT N'Altering Table [sPurchase].[tInvoiceGenerateDetail]...';


GO
ALTER TABLE [sPurchase].[tInvoiceGenerateDetail] DROP COLUMN [CategoryId], COLUMN [SubCategoryId], COLUMN [UnitTypeId];


GO
ALTER TABLE [sPurchase].[tInvoiceGenerateDetail] ALTER COLUMN [RejectedQty] DECIMAL (12, 2) NULL;


GO
PRINT N'Altering Table [sPurchase].[tQualityCheckDetail]...';


GO
ALTER TABLE [sPurchase].[tQualityCheckDetail] DROP COLUMN [CategoryId], COLUMN [SubCategoryId], COLUMN [UnitTypeId];


GO
PRINT N'Altering Table [sPurchase].[tQuotationRequest]...';


GO
ALTER TABLE [sPurchase].[tQuotationRequest] ALTER COLUMN [PurchaseRequestId] BIGINT NULL;


GO
PRINT N'Altering Table [sPurchase].[tQuotationRequestDetail]...';


GO
ALTER TABLE [sPurchase].[tQuotationRequestDetail] ALTER COLUMN [PurchaseRequestDetailId] BIGINT NULL;


GO
PRINT N'Starting rebuilding table [sPurchase].[tSalesReturn]...';


GO
BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;

CREATE TABLE [sPurchase].[tmp_ms_xx_tSalesReturn] (
    [Id]                  BIGINT         NOT NULL,
    [OrgGroupId]          SMALLINT       NOT NULL,
    [LocationId]          INT            NOT NULL,
    [IsObsolete]          BIT            DEFAULT ((0)) NULL,
    [GoodsReceivedNoteId] BIGINT         NULL,
    [PurchaseOrderId]     BIGINT         NULL,
    [VendorId]            INT            NULL,
    [RequestedBy]         NVARCHAR (100) NOT NULL,
    [SRNumber]            NVARCHAR (20)  NOT NULL,
    [StatusId]            SMALLINT       NOT NULL,
    [CreatedUserId]       BIGINT         NULL,
    [CreatedDateTime]     DATETIME       NOT NULL,
    [UpdatedUserId]       BIGINT         NULL,
    [UpdatedDateTime]     DATETIME       NULL,
    [DeletedDateTime]     DATETIME       NULL,
    CONSTRAINT [tmp_ms_xx_constraint_PK_sPurchase]].[tSalesReturn1] PRIMARY KEY CLUSTERED ([Id] ASC)
);

IF EXISTS (SELECT TOP 1 1 
           FROM   [sPurchase].[tSalesReturn])
    BEGIN
        INSERT INTO [sPurchase].[tmp_ms_xx_tSalesReturn] ([Id], [OrgGroupId], [LocationId], [IsObsolete], [GoodsReceivedNoteId], [PurchaseOrderId], [RequestedBy], [SRNumber], [StatusId], [CreatedUserId], [CreatedDateTime], [UpdatedUserId], [UpdatedDateTime], [DeletedDateTime])
        SELECT   [Id],
                 [OrgGroupId],
                 [LocationId],
                 [IsObsolete],
                 [GoodsReceivedNoteId],
                 [PurchaseOrderId],
                 [RequestedBy],
                 [SRNumber],
                 [StatusId],
                 [CreatedUserId],
                 [CreatedDateTime],
                 [UpdatedUserId],
                 [UpdatedDateTime],
                 [DeletedDateTime]
        FROM     [sPurchase].[tSalesReturn]
        ORDER BY [Id] ASC;
    END

DROP TABLE [sPurchase].[tSalesReturn];

EXECUTE sp_rename N'[sPurchase].[tmp_ms_xx_tSalesReturn]', N'tSalesReturn';

EXECUTE sp_rename N'[sPurchase].[tmp_ms_xx_constraint_PK_sPurchase]].[tSalesReturn1]', N'PK_sPurchase].[tSalesReturn', N'OBJECT';

COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


GO
PRINT N'Altering Table [sPurchase].[tSalesReturnDetail]...';


GO
ALTER TABLE [sPurchase].[tSalesReturnDetail] DROP COLUMN [CategoryId], COLUMN [SubCategoryId], COLUMN [UnitTypeId];


GO
PRINT N'Creating Table [sChatbot].[tChatbotAuditLog]...';

GO

CREATE TABLE [sPurchase].[tPurchaseReportTemplate] (
    [Id]            BIGINT         IDENTITY (1, 1) NOT NULL,
    [Template]      NVARCHAR (MAX) NOT NULL,
    [CreatedBy]     BIGINT         NOT NULL,
    [CreatedByDate] DATETIME       NULL,
    [IsObsolete]    BIT            NULL,
    CONSTRAINT [PK_PurchaseReportTemplate] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO

PRINT N'Altering Procedure [sPurchase].[pGoodsReceivedNoteGet]...';


GO


ALTER PROCEDURE [sPurchase].[pGoodsReceivedNoteGet]
	  @rId int
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
		select  GRN.Id,
				GRN.GRNNumber                               ,
				GRN.OrgGroupId,
				GRN.LocationId,
				GRN.PurchaseRequestId,
				GRN.PurchaseOrderId,
				GRN.VendorId,
				GRN.RequestedBy,
				GRN.BillToAddress,
				GRN.StatusId,
				GRN.PODueDate,
				ISNULL(SM.Title,'') as Status,
				VM.Title AS VendorName,
			    PR.Number As PurchaseRequestNumber,
			    PO.RefNumber as PurchaseOrderNumber  
		from   [sPurchase].[tGoodsReceivedNote] GRN(nolock) 
			   Left JOIN sProcurement.tVendorMaster  VM on GRN.VendorId = VM.ID
			   Left Join [sWf].[tWfStatusMaster] SM ON SM.Id = GRN.StatusId
			   Left Join [sPurchase].[tPurchaseRequest] PR ON PR.ID = GRN.PurchaseRequestId
			   Left Join [sPurchase].[tPurchaseOrder] PO On PO.Id = GRN.PurchaseOrderId
		where  GRN.Id=@rId AND GRN.IsObsolete = 0

		select GRND.Id,
			   GRND.GoodsReceivedNoteId,
			   GRND.PurchaseOrderDetailId,
			   GRND.OrgGroupId,
			   SUMa.Id AS UnitTypeId,
			   GRND.StockItemId,
			   GRND.POQty,
			   GRND.ReceivedQty,
			   GRND.PreviousGRNQty,
			   GRND.UnitPrice,
			   GRND.Remark, 
			   SIM.Code	AS StockItemCode,
			   SIM.Title AS StockItemName,
			   SUMa.Code AS UOM,
			   SCM.Id as CategoryId,
			   SCM.Title as CategoryName,
			   0 as SubCategoryId
		from   [sPurchase].[tGoodsReceivedNoteDetail] GRND (nolock)
			   Inner JOIN	sPurchase.tGoodsReceivedNote GRN	ON GRN.Id = GRND.GoodsReceivedNoteId AND GRN.IsObsolete = 0
			   Inner JOIN	sIp.tStockItemMaster SIM	ON SIM.Id = GRND.StockItemId 
			   Inner JOIN  sFinanceCommon.tStockUomMaster SUMa ON SUMa.Id = SIM.IssueStockUomId
			   LEFT JOIN [sIp].[tStockItemCategoryMaster] SCM ON SCM.Id = SIM.StockItemCategoryId
		where  GRND.GoodsReceivedNoteId=@rId
	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH
END
GO
PRINT N'Altering Procedure [sPurchase].[pGoodsReceivedNoteSRGet]...';


GO


ALTER PROCEDURE [sPurchase].[pGoodsReceivedNoteSRGet]
	  @rId int
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
		select  GRN.Id as GoodsReceivedNoteId,
				GRN.GRNNumber,
				GRN.OrgGroupId,
				GRN.LocationId,
				GRN.PurchaseRequestId,
				GRN.PurchaseOrderId,
				GRN.VendorId,
				GRN.RequestedBy,
				GRN.BillToAddress,
				GRN.StatusId,
				GRN.PODueDate,
				PO.RefNumber as PurchaseOrderNumber,
				ISNULL(SM.Title,'') as Status,
				VM.Title AS VendorName 
		from   [sPurchase].[tGoodsReceivedNote] GRN(nolock) 
			   INNER JOIN sPurchase.tQualityCheck QC on QC.GoodsReceivedNoteId = GRN.Id
			   INNER JOIN sPurchase.tPurchaseOrder PO on PO.Id = GRN.PurchaseOrderId
			   LEFT JOIN  sProcurement.tVendorMaster  VM on GRN.VendorId = VM.ID
			   LEFT Join  sWf.tWfStatusMaster SM ON SM.Id = GRN.StatusId
		where  GRN.Id=@rId AND GRN.IsObsolete = 0

		select GRND.Id as GoodsReceivedNoteDetailId,
			   GRND.GoodsReceivedNoteId,
			   GRND.PurchaseOrderDetailId,
			   GRND.OrgGroupId,
			   SUMa.Id AS UnitTypeId,
			   GRND.StockItemId,
			   GRND.POQty,
			   GRND.ReceivedQty,
			   GRND.PreviousGRNQty,
			   GRND.UnitPrice,
			   GRND.Remark, 
			   QCD.RejectedQty,
			   QCD.ReasonForRejection,
			   SIM.Code	AS StockItemCode,
			   SIM.Title AS StockItemName,
			   SUMa.Code AS UOM,
			   SCM.Id as CategoryId,
			   SCM.Title as CategoryName,
			   0 as SubCategoryId
		from   [sPurchase].[tGoodsReceivedNoteDetail] GRND (nolock)
			   INNER JOIN	sPurchase.tGoodsReceivedNote GRN	ON GRN.Id = GRND.GoodsReceivedNoteId AND GRN.IsObsolete = 0
			   INNER JOIN sPurchase.tQualityCheck QC on QC.GoodsReceivedNoteId = GRN.Id
			   INNER JOIN   sPurchase.tQualityCheckDetail QCD on QCD.QualityCheckId = QC.Id AND QCD.GoodsReceivedNoteDetailId = GRND.ID
			   INNER JOIN	sIp.tStockItemMaster SIM	ON SIM.Id = GRND.StockItemId 
			   INNER JOIN   sFinanceCommon.tStockUomMaster SUMa ON SUMa.Id = SIM.IssueStockUomId
			   LEFT JOIN [sIp].[tStockItemCategoryMaster] SCM ON SCM.Id = SIM.StockItemCategoryId
		where  GRND.GoodsReceivedNoteId=@rId AND QCD.RejectedQty > 0
	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH
END
GO
PRINT N'Creating Procedure [sPurchase].[pInvoiceGenerateAdd]...';


GO



CREATE PROCEDURE [sPurchase].[pInvoiceGenerateAdd]
		@rId bigint,
		@rGoodsReceivedNoteId bigint,
		@rVendorId bigint,
		@rRequestedBy nvarchar(100),
		@rOrgGroupId smallint,
		@rLocationId int,
		@rStatusId smallint,
		@rInvoiceDate datetime,
		@rInvoiceDueDate datetime,
		@rBillToAddress nvarchar(1000),
		@rShipToAddress nvarchar(1000),
		@rRemarks nvarchar(500),	
		@rUserId bigint,
		@rDetailList sPurchase.InvoiceGenerateDetailType READONLY
 
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
	BEGIN TRANSACTION

		If(@rId > 0)
		Begin
			Update sPurchase.tInvoiceGenerate
			set 
				StatusId = @rStatusId,
				GoodsReceivedNoteId = @rGoodsReceivedNoteId,
				VendorId = @rVendorId,
				InvoiceDate = @rInvoiceDate,
				InvoiceDueDate = @rInvoiceDueDate,
				BillToAddress = @rBillToAddress,
				ShipToAddress = @rShipToAddress,
				Remarks = @rRemarks,
				UpdatedUserId = @rUserId,		
				UpdatedDateTime = GetUtcDate()
			where Id = @rId and IsObsolete = 0

			delete from sPurchase.tInvoiceGenerateDetail
			where InvoiceGenerateId = @rId

			DECLARE @lNextNumberIGD INT	 
			EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sPurchase.tInvoiceGenerateDetail',@lNextNumber = @lNextNumberIGD output
			set @lNextNumberIGD=@lNextNumberIGD-1
			INSERT INTO sPurchase.tInvoiceGenerateDetail
			(
				Id,
				OrgGroupId,
				InvoiceGenerateId,
				GoodsReceivedNoteDetailId,
				StockItemId,
				POQty,
				ReceivedQty,
				AcceptedQty,
				RejectedQty,
				UnitPrice,
				ReasonForRejection,
				CreatedUserId,
				CreatedDateTime
			)
			select	@lNextNumberIGD+Id,
					@rOrgGroupId,
					@rId,
					GoodsReceivedNoteDetailId,
					StockItemId,
					POQty,
					ReceivedQty,
					AcceptedQty,
					RejectedQty,
					UnitPrice,
					ReasonForRejection,
					@rUserId, 
					GETUTCDATE() 
			from @rDetailList
			set @lNextNumberIGD=@lNextNumberIGD+(select Count(Id) from @rDetailList)
			EXECUTE sCommon.pTableRowIdModify @rOrgGroupId, @rTableTitle = 'sPurchase.tInvoiceGenerateDetail', @rCurrentNumber = @lNextNumberIGD
			
			Select @rId
		End
		Else
		Begin
			DECLARE @lNextNumber INT 
			EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sPurchase.tInvoiceGenerate',@lNextNumber = @lNextNumber output	
			--select @lNextNumber
			--DECLARE @loNextNumber nVarchar(20)
			--EXECUTE sCommon.pPrefixSetupNextNumberGet @rOrgId = @rOrgGroupId, @rPrefixFunctionalityId = 1005,@rStoreLocationId = @rLocationId,
			--@rNextNumber = @loNextNumber OUTPUT

	 		INSERT INTO sPurchase.tInvoiceGenerate
			(
				Id,
				INVNumber,
				GoodsReceivedNoteId,
				VendorId,
				RequestedBy, 
				OrgGroupId,
				LocationId, 			 
				StatusId,
				InvoiceDate,
				InvoiceDueDate,
				BillToAddress,
				ShipToAddress,
				Remarks,
				CreatedUserId,		
				CreatedDateTime
	
			)
			VALUES
			(
				@lNextNumber,
				@lNextNumber,
				@rGoodsReceivedNoteId,
				@rVendorId,
				@rRequestedBy,	 
				@rOrgGroupId,
				@rLocationId, 		 
				@rStatusId,
				@rInvoiceDate,
				@rInvoiceDueDate,
				@rBillToAddress,
				@rShipToAddress,
				@rRemarks,
				@rUserId,		
				GETUTCDATE()
			)
 
			set @rId = @lNextNumber  
			
			DECLARE @lNextNumberIGDD INT= 10000 + 	(select count (ID) from sPurchase.tInvoiceGenerateDetail)	 
			EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sPurchase.tInvoiceGenerateDetail',@lNextNumber = @lNextNumberIGDD output	
			set @lNextNumberIGDD=@lNextNumberIGDD-1

	 		INSERT INTO sPurchase.tInvoiceGenerateDetail
			(
				Id,
				OrgGroupId,
				InvoiceGenerateId,
				GoodsReceivedNoteDetailId,
				StockItemId,
				POQty,
				ReceivedQty,
				AcceptedQty,
				RejectedQty,
				UnitPrice,
				ReasonForRejection,
				CreatedUserId,
				CreatedDateTime
			)
			select	@lNextNumberIGDD+Id,
					@rOrgGroupId,
					@rId,
					GoodsReceivedNoteDetailId,
					StockItemId,
					POQty,
					ReceivedQty,
					AcceptedQty,
					RejectedQty,
					UnitPrice,
					ReasonForRejection,
					@rUserId, 
					GETUTCDATE() 
			from @rDetailList
						
			set @lNextNumberIGDD=@lNextNumberIGDD+(select Count(Id) from @rDetailList)
			EXECUTE sCommon.pTableRowIdModify @rOrgGroupId, @rTableTitle = 'sPurchase.tInvoiceGenerateDetail', @rCurrentNumber = @lNextNumberIGDD
			select @rId
		End
	COMMIT TRAN -- Transaction Success!	
	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error

    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  

    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH
END
GO
PRINT N'Altering Procedure [sPurchase].[pInvoiceGenerateGet]...';


GO


ALTER PROCEDURE [sPurchase].[pInvoiceGenerateGet]
	  @rId int
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
		select  IG.Id,
				IG.INVNumber,
				IG.OrgGroupId,
				IG.LocationId,
				IG.GoodsReceivedNoteId,
				IG.RequestedBy,
				IG.VendorId,
				IG.InvoiceDate,
				IG.InvoiceDueDate,
				IG.BillToAddress,
				IG.ShipToAddress,
				IG.Remarks,
				ISNULL(GRN.GRNNumber,'') as GRNNumber,
				IG.StatusId,
				ISNULL(SM.Title,'') as Status,
				ISNULL(VM.Title,'') AS VendorName,
				ISNULL(VM.Code,'') AS VendorCode
		from   [sPurchase].[tInvoiceGenerate] IG(nolock) 
		Left Join [sPurchase].[tGoodsReceivedNote] GRN ON GRN.Id = IG.GoodsReceivedNoteID
		Left JOIN sProcurement.tVendorMaster  VM on IG.VendorId = VM.ID
		Left Join [sWf].[tWfStatusMaster] SM ON SM.Id = IG.StatusId
		where  IG.Id=@rId AND IG.IsObsolete = 0

		select IGD.Id,
			   IGD.InvoiceGenerateId,
			   IGD.GoodsReceivedNoteDetailId,
			   IGD.OrgGroupId,
			   SUMa.Id AS UnitTypeId,
			   IGD.StockItemId,
			   IGD.POQty,
			   IGD.ReceivedQty,
			   IGD.AcceptedQty,
			   IGD.RejectedQty, 
			   IGD.UnitPrice,
			   IGD.ReasonForRejection,
			   SCM.Id as CategoryId,
			   SCM.Title as CategoryName,
			   0 as SubCategoryId,
			   SIM.Code				AS StockItemCode,
			   SIM.Title				AS StockItemName,
			   SUMa.Code				AS UOM
		from   [sPurchase].[tInvoiceGenerateDetail] IGD (nolock)
			   Inner JOIN	sPurchase.tInvoiceGenerate IG	ON IG.Id = IGD.InvoiceGenerateId AND IG.IsObsolete = 0
			   Inner JOIN	sIp.tStockItemMaster SIM	ON SIM.Id = IGD.StockItemId 
			   LEFT JOIN [sIp].[tStockItemCategoryMaster] SCM ON SCM.Id = SIM.StockItemCategoryId
			   Inner JOIN  sFinanceCommon.tStockUomMaster SUMa ON SUMa.Id = SIM.IssueStockUomId
		where  IGD.InvoiceGenerateId=@rId

	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH
END
GO
PRINT N'Altering Procedure [sPurchase].[pGoodsReceivedNoteSearchSRGet]...';


GO

--exec [sPurchase].[pGoodsReceivedNoteSearchSRGet] null,991100001,null,9911,'2023-01-01','2023-12-01',1,10,null
ALTER PROCEDURE [sPurchase].[pGoodsReceivedNoteSearchSRGet]

	@rNumber nvarchar(30) = '',
	@rLocationId int,
	@rName nvarchar = '',
	@rOrgGroupId int,
	@rStartDate DateTime = null,
	@rEndDate DateTime = null,
	@rPageNo int,
	@rPageSize int,
	@rStatusId smallint = null

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY


		 DECLARE @TotalRecords BIGINT;
		-- Get the total number of records
		SELECT @TotalRecords = COUNT(*) FROM sPurchase.tGoodsReceivedNote GRN (nolock)
			    INNER JOIN   sPurchase.tQualityCheck QC on QC.GoodsReceivedNoteId = GRN.Id
			    INNER JOIN   sPurchase.tQualityCheckDetail QCD on QCD.QualityCheckId = QC.Id

		WHERE 
				((GRN.GRNNumber like @rNumber+'%') OR (ISNULL(@rNumber,'') = ''))
   				AND ((GRN.RequestedBy like @rName+'%') OR (ISNULL(@rName,'') = ''))
   				--AND ((SLM.Id = @rStoreLocationId) OR (@rStoreLocationId = 0))
   				AND ((GRN.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))
				AND ((GRN.StatusId = @rStatusId) OR (ISNULL(@rStatusId,0)=0))
				AND GRN.IsObsolete = 0 AND QCD.RejectedQty > 0
				AND ((GRN.CreatedDateTime >= @rStartDate) OR (ISNULL(@rStartDate,'')=''))
				AND((GRN.CreatedDateTime <= @rEndDate) OR (ISNULL(@rEndDate,'')=''))


		Select t.* From (
			Select ROW_NUMBER() Over (
			Order by GRN.Id desc  
			) AS 'RowNum',GRN.Id,
				GRN.GRNNumber as Number,
				GRN.RequestedBy, 
				GRN.LocationId, 
				GRN.PODueDate,
				ISNULL(SM.Title,'') as Status


        FROM		
				sPurchase.tGoodsReceivedNote GRN (nolock)
				Left Join [sWf].[tWfStatusMaster] SM on GRN.StatusId = SM.Id
			    INNER JOIN   sPurchase.tQualityCheck QC on QC.GoodsReceivedNoteId = GRN.Id
			    INNER JOIN   sPurchase.tQualityCheckDetail QCD on QCD.QualityCheckId = QC.Id


	    WHERE 
				((GRN.GRNNumber like @rNumber+'%') OR (ISNULL(@rNumber,'') = ''))
   				AND ((GRN.RequestedBy like @rName+'%') OR (ISNULL(@rName,'') = ''))
   				--AND ((SLM.Id = @rStoreLocationId) OR (@rStoreLocationId = 0))
   				AND ((GRN.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))
				AND ((GRN.StatusId = @rStatusId) OR (ISNULL(@rStatusId,0)=0))
				AND GRN.IsObsolete = 0 AND QCD.RejectedQty > 0
				AND ((GRN.CreatedDateTime >= @rStartDate) OR (ISNULL(@rStartDate,'')=''))
				AND((GRN.CreatedDateTime <= @rEndDate) OR (ISNULL(@rEndDate,'')=''))
					)t Where t.RowNum Between
		        ((@rPageNo-1)*@rPageSize +1) AND (@rPageNo*@rpageSize)

        SELECT @TotalRecords as TotalRecords

	 END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH  		  
END
GO
PRINT N'Creating Procedure [sPurchase].[pQualityCheckAdd]...';


GO




CREATE PROCEDURE [sPurchase].[pQualityCheckAdd]
		@rId bigint,
		@rGoodsReceivedNoteId bigint,
		@rPurchaseOrderId bigint,
		@rRequestedBy nvarchar(100),
		@rOrgGroupId smallint,
		@rLocationId int,
		@rStatusId smallint,	
		@rUserId bigint,
		@rDetailList sPurchase.QualityCheckDetailType READONLY
 
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
	BEGIN TRANSACTION

		If(@rId > 0)
		Begin
			Update sPurchase.tQualityCheck
			set 
				StatusId = @rStatusId,
				GoodsReceivedNoteId = @rGoodsReceivedNoteId,
				PurchaseOrderId = @rPurchaseOrderId,
				UpdatedUserId = @rUserId,		
				UpdatedDateTime = GetUtcDate()
			where Id = @rId and IsObsolete = 0

			delete from sPurchase.tQualityCheckDetail
			where QualityCheckId = @rId

			DECLARE @lNextNumberQCD INT	 
			EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sPurchase.tQualityCheckDetail',@lNextNumber = @lNextNumberQCD output
			set @lNextNumberQCD=@lNextNumberQCD-1
			INSERT INTO sPurchase.tQualityCheckDetail
			(
				Id,
				OrgGroupId,
				QualityCheckId,
				GoodsReceivedNoteDetailId,
				StockItemId,
				POQty,
				ReceivedQty,
				AcceptedQty,
				RejectedQty,
				ReasonForRejection,
				CreatedUserId,
				CreatedDateTime
			)
			select	@lNextNumberQCD+Id,
					@rOrgGroupId,
					@rId,
					GoodsReceivedNoteDetailId,
					StockItemId,
					POQty,
					ReceivedQty,
					AcceptedQty,
					RejectedQty,
					ReasonForRejection,
					@rUserId, 
					GETUTCDATE() 
			from @rDetailList
			set @lNextNumberQCD=@lNextNumberQCD+(select Count(Id) from @rDetailList)
			EXECUTE sCommon.pTableRowIdModify @rOrgGroupId, @rTableTitle = 'sPurchase.tQualityCheckDetail', @rCurrentNumber = @lNextNumberQCD

			select @rId
		End
		Else
		Begin
			DECLARE @lNextNumber INT	 
			EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sPurchase.tQualityCheck',@lNextNumber = @lNextNumber output	
			---------------select @lNextNumber
			--DECLARE @loNextNumber nVarchar(20)
			--EXECUTE sCommon.pPrefixSetupNextNumberGet @rOrgId = @rOrgGroupId, @rPrefixFunctionalityId = 1005,@rStoreLocationId = @rLocationId,
			---------------@rNextNumber = @loNextNumber OUTPUT

	 		INSERT INTO sPurchase.tQualityCheck
			(
				Id,
				QCNumber,
				GoodsReceivedNoteId,
				PurchaseOrderId,
				RequestedBy, 
				OrgGroupId,
				LocationId, 			 
				StatusId,
				CreatedUserId,		
				CreatedDateTime
	
			)
			VALUES
			(
				@lNextNumber,
				@lNextNumber,
				@rGoodsReceivedNoteId,
				@rPurchaseOrderId,
				@rRequestedBy,	 
				@rOrgGroupId,
				@rLocationId, 		 
				@rStatusId,
				@rUserId,		
				GETUTCDATE()
			)
 
			set @rId = @lNextNumber  
			
			DECLARE @lNextNumberQCDD INT	 
			EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sPurchase.tQualityCheckDetail',@lNextNumber = @lNextNumberQCDD output	
			set @lNextNumberQCDD=@lNextNumberQCDD-1

	 		INSERT INTO sPurchase.tQualityCheckDetail
			(
				Id,
				OrgGroupId,
				QualityCheckId,
				GoodsReceivedNoteDetailId,
				StockItemId,
				POQty,
				ReceivedQty,
				AcceptedQty,
				RejectedQty,
				ReasonForRejection,
				CreatedUserId,
				CreatedDateTime
			)
			select	@lNextNumberQCDD+Id,
					@rOrgGroupId,
					@rId,
					GoodsReceivedNoteDetailId,
					StockItemId,
					POQty,
					ReceivedQty,
					AcceptedQty,
					RejectedQty,
					ReasonForRejection,
					@rUserId, 
					GETUTCDATE() 
			from @rDetailList
						
			set @lNextNumberQCDD=@lNextNumberQCDD+(select Count(Id) from @rDetailList)
			EXECUTE sCommon.pTableRowIdModify @rOrgGroupId, @rTableTitle = 'sPurchase.tQualityCheckDetail', @rCurrentNumber = @lNextNumberQCDD
			
			select @rId
			--select 1;
		End
	COMMIT TRAN -- Transaction Success!	
	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error

    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  

    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH
END
GO
PRINT N'Altering Procedure [sPurchase].[pQualityCheckGet]...';


GO


ALTER PROCEDURE [sPurchase].[pQualityCheckGet]
	  @rId int
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
		select  QC.Id,
				QC.QCNumber,
				QC.OrgGroupId,
				QC.LocationId,
				QC.GoodsReceivedNoteId,
				QC.PurchaseOrderId,
				QC.RequestedBy,
				ISNULL(GRN.GRNNumber,'') as GRNNumber,
				QC.StatusId,
				ISNULL(SM.Title,'') as Status
		from   [sPurchase].[tQualityCheck] QC(nolock) 
		Left Join [sPurchase].[tGoodsReceivedNote] GRN ON GRN.Id = QC.GoodsReceivedNoteID
		Left Join [sWf].[tWfStatusMaster] SM ON SM.Id = QC.StatusId
		where  QC.Id=@rId AND QC.IsObsolete = 0

		select QCD.Id,
			   QCD.QualityCheckId,
			   QCD.GoodsReceivedNoteDetailId,
			   QCD.OrgGroupId,
			   SUMa.Id AS UnitTypeId,
			   QCD.StockItemId,
			   QCD.POQty,
			   QCD.ReceivedQty,
			   QCD.AcceptedQty,
			   QCD.RejectedQty,
			   QCD.ReasonForRejection, 
			   SCM.Id as CategoryId,
			   SCM.Title as CategoryName,
			   0 as SubCategoryId,
			   SIM.Code				AS StockItemCode,
			   SIM.Title				AS StockItemName,
			   SUMa.Code				AS UOM
		from   [sPurchase].[tQualityCheckDetail] QCD (nolock)
			   Inner JOIN	sPurchase.tQualityCheck QC	ON QC.Id = QCD.QualityCheckId AND QC.IsObsolete = 0
			   Inner JOIN	sIp.tStockItemMaster SIM	ON SIM.Id = QCD.StockItemId 
			   LEFT JOIN [sIp].[tStockItemCategoryMaster] SCM ON SCM.Id = SIM.StockItemCategoryId
			   Inner JOIN  sFinanceCommon.tStockUomMaster SUMa ON SUMa.Id = SIM.IssueStockUomId
		where  QCD.QualityCheckId=@rId


	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH
END
GO
PRINT N'Altering Procedure [sPurchase].[pQuotationRequestGet]...';


GO


ALTER PROCEDURE [sPurchase].[pQuotationRequestGet]
	  @rId int
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
		select QR.Id,
			   QR.Number,
			   QR.PurchaseRequestId,
			   QR.Name,
			   QR.StatusId,
			   QR.VendorId,
			   QR.LocationId,
			   QR.BranchId,
			   QR.OrgGroupId,
			   QR.RFQDate,
			   QR.Description,
			   QR.StartDate,
			   QR.EndDate,
			   QR.BidOpen,
			   QR.BidClose,
			   QR.PersonContact,
			   QR.Email,
			   QR.SendToVendor,
			   QR.VendorId,
			   ISNULL(SM.Title,'') as Status,
			   VM.Code AS VendorCode,
			   VM.Title AS VendorName,
			   PR.Number as PurchaseNumber
		from   [sPurchase].[tQuotationRequest] QR(nolock) 
			   Left JOIN sProcurement.tVendorMaster  VM on QR.VendorId = VM.ID
			   Left Join [sWf].[tWfStatusMaster] SM ON SM.Id = QR.StatusId
			   Left Join [sPurchase].[tPurchaseRequest] PR on PR.Id = QR.PurchaseRequestId

		where  QR.Id=@rId AND QR.IsObsolete = 0

		select QRD.Id,
			   QRD.QuotationRequestId,
			   QRD.PurchaseRequestDetailId,
			   QRD.OrgGroupId,
			   QRD.StockItemId,
			   QRD.RequestedQty,
			   QRD.TargetPrice,
			   QRD.Remark, 
			   SIM.Code	AS StockItemCode,
			   SIM.Title AS StockItemName,
			   SCM.Title AS CategoryName,
			   SUMa.Code AS UOM
		from   [sPurchase].[tQuotationRequestDetail] QRD (nolock)
			   Inner JOIN	sPurchase.tQuotationRequest QR	ON QR.Id = QRD.QuotationRequestId AND QR.IsObsolete = 0
			   Inner JOIN	sIp.tStockItemMaster SIM	ON SIM.Id = QRD.StockItemId 
			   LEFT JOIN [sIp].[tStockItemCategoryMaster] SCM ON SCM.Id = SIM.StockItemCategoryId
			   Inner JOIN  sFinanceCommon.tStockUomMaster SUMa ON SUMa.Id = SIM.IssueStockUomId
		where  QRD.QuotationRequestId=@rId


	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH

END
GO
PRINT N'Altering Procedure [sPurchase].[pQuotationRequestSearchGet]...';


GO

--exec [sPurchase].[pQuotationRequestSearchGet] @rLocationId=991100001,@rOrgGroupId=9911,@rStartDate='2023-01-01',@rEndDate='2023-12-01',@rPageNo=1,@rPageSize=10
ALTER PROCEDURE [sPurchase].[pQuotationRequestSearchGet]

	@rNumber nvarchar(30) = '',
	@rLocationId int,
	@rName nvarchar = '',
	@rOrgGroupId smallint,
	@rStartDate DateTime = null,
	@rEndDate DateTime = null,
	@rPageNo int,
	@rPageSize int,
	@rStatusId smallint = 0

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY

        DECLARE @TotalRecords BIGINT;
		-- Get the total number of records
		SELECT @TotalRecords = COUNT(*) FROM sPurchase.tQuotationRequest QR (nolock)

		WHERE 
				((QR.Number like @rNumber+'%') OR (ISNULL(@rNumber,'') = ''))
   				AND ((QR.Name like @rName+'%') OR (ISNULL(@rName,'') = ''))
   				--AND ((SLM.Id = @rStoreLocationId) OR (@rStoreLocationId = 0))
   				AND ((QR.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))
				AND ((QR.StatusId = @rStatusId) OR (ISNULL(@rStatusId,0)=0))
				AND QR.IsObsolete = 0
				AND ((QR.CreatedDateTime >= @rStartDate) OR (ISNULL(@rStartDate,'')=''))
				AND((QR.CreatedDateTime <= @rEndDate) OR (ISNULL(@rEndDate,'')=''))


		Select t.* From (
			Select ROW_NUMBER() Over (
			Order by QR.Id desc  
			) AS 'RowNum',QR.Id, 
				QR.Number,
				QR.Name as RequestedBy,
				QR.LocationId,
				ISNULL(SM.Title,'') as Status

        FROM		
				sPurchase.tQuotationRequest QR (nolock)
				Left Join [sWf].[tWfStatusMaster] SM on QR.StatusId = SM.Id

	    
		WHERE 
				((QR.Number like @rNumber+'%') OR (ISNULL(@rNumber,'') = ''))
   				AND ((QR.Name like @rName+'%') OR (ISNULL(@rName,'') = ''))
   				--AND ((SLM.Id = @rStoreLocationId) OR (@rStoreLocationId = 0))
   				AND ((QR.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))
				AND ((QR.StatusId = @rStatusId) OR (ISNULL(@rStatusId,0)=0))
				AND QR.IsObsolete = 0
				AND ((QR.CreatedDateTime >= @rStartDate) OR (ISNULL(@rStartDate,'')=''))
				AND((QR.CreatedDateTime <= @rEndDate) OR (ISNULL(@rEndDate,'')=''))
					)t Where t.RowNum Between
		        ((@rPageNo-1)*@rPageSize +1) AND (@rPageNo*@rpageSize)

        SELECT @TotalRecords as TotalRecords

	 END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH  		  
END
GO
PRINT N'Creating Procedure [sPurchase].[pPurchaseOrderAdd]...';


GO







CREATE PROCEDURE [sPurchase].[pPurchaseOrderAdd]
		@rId bigint,
		@rPurchaseRequestId bigint,
		@rPODate datetime,	
		@rRequestedBy nvarchar(100),
		@rOrgGroupId smallint,
		@rLocationId int,
		@rStatusId smallint,	
		@rVendorId int,
		@rBillToAddress nvarchar(1000),
		@rShipToAddress nvarchar(1000),	
		@rUserId bigint,
		@rRequiredBy datetime,
		@rDetailList sPurchase.PurchaseOrderDetailType READONLY
 
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
	BEGIN TRANSACTION

		If(@rId > 0)
		Begin
			Update sPurchase.tPurchaseOrder
			set 
				StatusId = @rStatusId,
				VendorId = @rVendorId,
				RequiredBy = @rRequiredBy,
				BillToAddress = @rBillToAddress,
				ShipToAddress = @rShipToAddress,
				UpdatedUserId = @rUserId,		
				UpdatedDateTime = GetUtcDate()
			where Id = @rId and IsObsolete = 0

			delete from sPurchase.tPurchaseOrderDetail
			where PurchaseOrderId = @rId

			DECLARE @lNextNumberPOD INT	 
			EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sPurchase.tPurchaseOrderDetail',@lNextNumber = @lNextNumberPOD output
			set @lNextNumberPOD=@lNextNumberPOD-1
			INSERT INTO sPurchase.tPurchaseOrderDetail
			(
				Id,
				OrgGroupId,
				PurchaseOrderId,
				PurchaseRequestDetailId,
				StockItemId,
				TotalQty,
				Remark,
				CreatedUserId,
				CreatedDateTime
			)
			select	@lNextNumberPOD+Id,
					@rOrgGroupId,
					@rId,
					PurchaseRequestDetailId,
					StockItemId,
					TotalQty,
					Remark, 
					@rUserId, 
					GETUTCDATE() 
			from @rDetailList
			set @lNextNumberPOD=@lNextNumberPOD+(select Count(Id) from @rDetailList)
			EXECUTE sCommon.pTableRowIdModify @rOrgGroupId, @rTableTitle = 'sPurchase.tPurchaseOrderDetail', @rCurrentNumber = @lNextNumberPOD

			Select @rId
		End
		Else
		Begin
			DECLARE @lNextNumber INT	 
			EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sPurchase.tPurchaseOrder',@lNextNumber = @lNextNumber output	
			---------------select @lNextNumber
			--DECLARE @loNextNumber nVarchar(20)
			--EXECUTE sCommon.pPrefixSetupNextNumberGet @rOrgId = @rOrgGroupId, @rPrefixFunctionalityId = 1005,@rStoreLocationId = @rLocationId,
			---------------@rNextNumber = @loNextNumber OUTPUT

	 		INSERT INTO sPurchase.tPurchaseOrder
			(
				Id,
				RefNumber,
				PurchaseRequestId,
				PODate,
				RequestedBy, 
				OrgGroupId,
				LocationId, 			 
				StatusId,
				VendorId,
				RequiredBy,
				BillToAddress,
				ShipToAddress,
				CreatedUserId,		
				CreatedDateTime
	
			)
			VALUES
			(
				@lNextNumber,
				@lNextNumber,
				@rPurchaseRequestId,
				@rPODate,
				@rRequestedBy,	 
				@rOrgGroupId,
				@rLocationId, 		 
				@rStatusId,
				@rVendorId,
				@rRequiredBy,
				@rBillToAddress,
				@rShipToAddress,
				@rUserId,		
				GETUTCDATE()
			)
 
			set @rId = @lNextNumber  
			
			DECLARE @lNextNumberPO INT	 
			EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sPurchase.tPurchaseOrderDetail',@lNextNumber = @lNextNumberPO output	
			set @lNextNumberPO=@lNextNumberPO-1

	 		INSERT INTO sPurchase.tPurchaseOrderDetail
			(
				Id,
				OrgGroupId,
				PurchaseOrderId,
				PurchaseRequestDetailId,
				StockItemId,
				TotalQty,
				Remark,
				CreatedUserId,
				CreatedDateTime
			)
			select	@lNextNumberPO+Id,
					@rOrgGroupId,
					@rId,
					PurchaseRequestDetailId,
					StockItemId,
					TotalQty,
					Remark, 
					@rUserId, 
					GETUTCDATE() 
			from @rDetailList
						
			set @lNextNumberPO=@lNextNumberPO+(select Count(Id) from @rDetailList)
			EXECUTE sCommon.pTableRowIdModify @rOrgGroupId, @rTableTitle = 'sPurchase.tPurchaseOrderDetail', @rCurrentNumber = @lNextNumberPO

			Select @rId

			Declare @typeId smallint=0
			Declare @Level smallint=0
			SELECT @typeId=WfTypeMasterId, @Level=WfLevel FROM sWf.tWfSetup WHERE WfFunctionalityId = 3
			IF(@typeId = 1)--parallel
			BEGIN
				exec [sWf].[pWfApprovalAdd] 0,@rId,2042,@rUserId,1,'',0,0,3,@rOrgGroupId
			END
			ELSE IF(@typeId = 2)--sequencial
			Begin
				exec [sWf].[pWfApprovalAdd] 0,@rId,2042,@rUserId,2,'',1,@Level,3,@rOrgGroupId
			End
			ELSE IF(@typeId = 3)--auto approved
			BEGIN
				exec [sWf].[pWfApprovalAdd] 0,@rId,2042,@rUserId,3,'',0,0,3,@rOrgGroupId
			END
			--select 1;
		End
	COMMIT TRAN -- Transaction Success!	
	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error

    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  

    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH
END
GO
PRINT N'Creating Procedure [sPurchase].[pPurchaseRequestAdd]...';


GO


CREATE PROCEDURE [sPurchase].[pPurchaseRequestAdd]
		@rId bigint,
		@rRequestDate datetime,
		@rResourceId bigint,
		@rRequestedBy nvarchar(100),
		@rOrgGroupId smallint,
		@rBranchId int,
		@rLocationId int,
		@rStatusId smallint,	
		@rVendorId int,
		@rUserId bigint,	
		@rRequiredBy datetime,	
		@rDetailList sPurchase.PurchaseRequestDetailType READONLY

 
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
	BEGIN TRANSACTION

		If(@rId > 0)
		Begin
			Update sPurchase.tPurchaseRequest
			set 
				StatusId = @rStatusId,
				UpdatedUserId = @rUserId,		
				UpdatedDateTime = GetUtcDate()
			where Id = @rId and IsObsolete = 0

			delete from sPurchase.tPurchaseRequestDetail
			where PurchaseRequestId = @rId

			DECLARE @lNextNumberPRQA INT	 
			EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sPurchase.tPurchaseRequestDetail',@lNextNumber = @lNextNumberPRQA output
			set @lNextNumberPRQA=@lNextNumberPRQA-1
			INSERT INTO sPurchase.tPurchaseRequestDetail
			(
				ID,
				OrgGroupId,
				PurchaseRequestId,
				StockItemId,
				RequestedQty,
				Remark,
				CreatedUserId,
				CreatedDateTime
			)
			select	@lNextNumberPRQA+Id,
					@rOrgGroupId,
					@rId, 
					StockItemId,
					RequestedQty,
					Remark, 
					@rUserId, 
					GETUTCDATE() 
			from @rDetailList
			set @lNextNumberPRQA=@lNextNumberPRQA+(select Count(Id) from @rDetailList)
			EXECUTE sCommon.pTableRowIdModify @rOrgGroupId, @rTableTitle = 'sPurchase.tPurchaseRequestDetail', @rCurrentNumber = @lNextNumberPRQA

			Select @rId
		End
		Else
		Begin
			DECLARE @lNextNumber INT	 
			EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sPurchase.tPurchaseRequest',@lNextNumber = @lNextNumber output	
			--select @lNextNumber
			--DECLARE @loNextNumber nVarchar(20)
			--EXECUTE sCommon.pPrefixSetupNextNumberGet @rOrgId = @rOrgGroupId, @rPrefixFunctionalityId = 1005,@rStoreLocationId = @rLocationId,
			--@rNextNumber = @loNextNumber OUTPUT
	 		INSERT INTO sPurchase.tPurchaseRequest
			(
				Id,
				Number,
				RequestDate,
				ResourceId,
				RequestedBy, 
				OrgGroupId,
				BranchId,
				LocationId, 			 
				StatusId,
				VendorId,
				RequiredBy,
				CreatedUserId,		
				CreatedDateTime
			)
			VALUES
			(
				@lNextNumber,
				@lNextNumber,
				@rRequestDate,
				@rResourceId,
				@rRequestedBy,	 
				@rOrgGroupId,
				@rBranchId,
				@rLocationId, 		 
				@rStatusId,
				@rVendorId,
				@rRequiredBy,
				@rUserId,		
				GETUTCDATE()
			)
 
			set @rId = @lNextNumber  
			DECLARE @lNextNumberPRQ INT	 
			EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sPurchase.tPurchaseRequestDetail',@lNextNumber = @lNextNumberPRQ output	
			set @lNextNumberPRQ=@lNextNumberPRQ-1

	 		INSERT INTO sPurchase.tPurchaseRequestDetail
			(
				Id,
				OrgGroupId,
				PurchaseRequestId,
				StockItemId,
				RequestedQty,
				Remark,
				CreatedUserId,
				CreatedDateTime
			)
			select	@lNextNumberPRQ+Id,
					@rOrgGroupId,
					@rId, 
					StockItemId,
					RequestedQty,
					Remark, 
					@rUserId, 
					GETUTCDATE() 
			from @rDetailList
			set @lNextNumberPRQ=@lNextNumberPRQ+(select Count(Id) from @rDetailList)
			EXECUTE sCommon.pTableRowIdModify @rOrgGroupId, @rTableTitle = 'sPurchase.tPurchaseRequestDetail', @rCurrentNumber = @lNextNumberPRQ

			
			select @rId

			Declare @typeId smallint=0
			Declare @Level smallint=0
			SELECT @typeId=WfTypeMasterId, @Level=WfLevel FROM sWf.tWfSetup WHERE WfFunctionalityId = 1
			IF(@typeId = 1)--parallel
			BEGIN
				exec [sWf].[pWfApprovalAdd] 0,@rId,2040,@rUserId,1,'',0,0,1,@rOrgGroupId
			END
			ELSE IF(@typeId = 2)--sequencial
			Begin
				exec [sWf].[pWfApprovalAdd] 0,@rId,2040,@rUserId,2,'',1,@Level,1,@rOrgGroupId
			End
			ELSE IF(@typeId = 3)--auto approved
			BEGIN
				exec [sWf].[pWfApprovalAdd] 0,@rId,2040,@rUserId,3,'',0,0,1,@rOrgGroupId
			END
			
		End
	COMMIT TRAN -- Transaction Success!	
	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error

    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  

    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH
END
GO
PRINT N'Creating Procedure [sPurchase].[pSalesReturnAdd]...';


GO




CREATE PROCEDURE [sPurchase].[pSalesReturnAdd]
		@rId bigint,
		@rGoodsReceivedNoteId bigint,
		@rPurchaseOrderId bigint,
		@rVendorId int,
		@rRequestedBy nvarchar(100),
		@rOrgGroupId smallint,
		@rLocationId int,
		@rStatusId smallint,	
		@rUserId bigint,
		@rDetailList sPurchase.SalesReturnDetailType READONLY
 
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
	BEGIN TRANSACTION

		If(@rId > 0)
		Begin
			Update sPurchase.tSalesReturn
			set 
				StatusId = @rStatusId,
				GoodsReceivedNoteId = @rGoodsReceivedNoteId,
				PurchaseOrderId = @rPurchaseOrderId,
				VendorId = @rVendorId,
				UpdatedUserId = @rUserId,		
				UpdatedDateTime = GetUtcDate()
			where Id = @rId and IsObsolete = 0

			delete from sPurchase.tSalesReturnDetail
			where SalesReturnId = @rId

			DECLARE @lNextNumberSRD INT	 
			EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sPurchase.tSalesReturnDetail',@lNextNumber = @lNextNumberSRD output
			set @lNextNumberSRD=@lNextNumberSRD-1
			INSERT INTO sPurchase.tSalesReturnDetail
			(
				Id,
				OrgGroupId,
				SalesReturnId,
				GoodsReceivedNoteDetailId,
				StockItemId,
				POQty,
				ReceivedQty,
				RejectedQty,
				CreditNoteIssueDate,
				ReasonForRejection,
				CreatedUserId,
				CreatedDateTime
			)
			select	@lNextNumberSRD+Id,
					@rOrgGroupId,
					@rId,
					GoodsReceivedNoteDetailId,
					StockItemId,
					POQty,
					ReceivedQty,
					RejectedQty,
					CreditNoteIssueDate,
					ReasonForRejection,
					@rUserId, 
					GETUTCDATE() 
			from @rDetailList
			set @lNextNumberSRD=@lNextNumberSRD+(select Count(Id) from @rDetailList)
			EXECUTE sCommon.pTableRowIdModify @rOrgGroupId, @rTableTitle = 'sPurchase.tSalesReturnDetail', @rCurrentNumber = @lNextNumberSRD
			
			select @rId
		End
		Else
		Begin
			DECLARE @lNextNumber INT	 
			EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sPurchase.tSalesReturn',@lNextNumber = @lNextNumber output	
			-------------select @lNextNumber
			--DECLARE @loNextNumber nVarchar(20)
			--EXECUTE sCommon.pPrefixSetupNextNumberGet @rOrgId = @rOrgGroupId, @rPrefixFunctionalityId = 1005,@rStoreLocationId = @rLocationId,
			------------@rNextNumber = @loNextNumber OUTPUT

	 		INSERT INTO sPurchase.tSalesReturn
			(
				Id,
				SRNumber,
				GoodsReceivedNoteId,
				PurchaseOrderId,
				VendorId,
				RequestedBy, 
				OrgGroupId,
				LocationId, 			 
				StatusId,
				CreatedUserId,		
				CreatedDateTime
	
			)
			VALUES
			(
				@lNextNumber,
				@lNextNumber,
				@rGoodsReceivedNoteId,
				@rPurchaseOrderId,
				@rVendorId,
				@rRequestedBy,	 
				@rOrgGroupId,
				@rLocationId, 		 
				@rStatusId,
				@rUserId,		
				GETUTCDATE()
			)
 
			set @rId = @lNextNumber  
			
			DECLARE @lNextNumberSRDD INT	 
			EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sPurchase.tSalesReturnDetail',@lNextNumber = @lNextNumberSRDD output	
			set @lNextNumberSRDD=@lNextNumberSRDD-1

	 		INSERT INTO sPurchase.tSalesReturnDetail
			(
				Id,
				OrgGroupId,
				SalesReturnId,
				GoodsReceivedNoteDetailId,
				StockItemId,
				POQty,
				ReceivedQty,
				RejectedQty,
				CreditNoteIssueDate,
				ReasonForRejection,
				CreatedUserId,
				CreatedDateTime
			)
			select	@lNextNumberSRDD+Id,
					@rOrgGroupId,
					@rId,
					GoodsReceivedNoteDetailId,
					StockItemId,
					POQty,
					ReceivedQty,
					RejectedQty,
					CreditNoteIssueDate,
					ReasonForRejection,
					@rUserId, 
					GETUTCDATE() 
			from @rDetailList
						
			set @lNextNumberSRDD=@lNextNumberSRDD+(select Count(Id) from @rDetailList)
			EXECUTE sCommon.pTableRowIdModify @rOrgGroupId, @rTableTitle = 'sPurchase.tSalesReturnDetail', @rCurrentNumber = @lNextNumberSRDD
			
			select @rId

			Declare @typeId smallint=0
			Declare @Level smallint=0
			SELECT @typeId=WfTypeMasterId, @Level=WfLevel FROM sWf.tWfSetup WHERE WfFunctionalityId = 5
			IF(@typeId = 1)--parallel
			BEGIN
				exec [sWf].[pWfApprovalAdd] 0,@rId,2044,@rUserId,1,'',0,0,5,@rOrgGroupId
			END
			ELSE IF(@typeId = 2)--sequencial
			Begin
				exec [sWf].[pWfApprovalAdd] 0,@rId,2044,@rUserId,2,'',1,@Level,5,@rOrgGroupId
			End
			ELSE IF(@typeId = 3)--auto approved
			BEGIN
				exec [sWf].[pWfApprovalAdd] 0,@rId,2044,@rUserId,3,'',0,0,5,@rOrgGroupId
			END
			--select 1;
		End
	COMMIT TRAN -- Transaction Success!	
	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error

    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  

    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH
END
GO
PRINT N'Altering Procedure [sPurchase].[pSalesReturnGet]...';


GO

ALTER PROCEDURE [sPurchase].[pSalesReturnGet]
	  @rId int
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
		select  SR.Id,
				SR.SRNumber,
				SR.OrgGroupId,
				SR.LocationId,
				SR.GoodsReceivedNoteId,
				SR.PurchaseOrderId,
				SR.VendorId,
				SR.RequestedBy,
				ISNULL(GRN.GRNNumber,'') as GRNNumber,
				SR.StatusId,
				ISNULL(SM.Title,'') as Status,
				VM.Title AS VendorName,
				PO.RefNumber as PurchaseOrderNumber
		from   [sPurchase].[tSalesReturn] SR(nolock) 
		Left Join [sPurchase].[tGoodsReceivedNote] GRN ON GRN.Id = SR.GoodsReceivedNoteID
		left JOIN sPurchase.tPurchaseOrder PO on PO.Id = SR.PurchaseOrderId
		Left Join [sWf].[tWfStatusMaster] SM ON SM.Id = SR.StatusId
		LEFT JOIN  sProcurement.tVendorMaster  VM on SR.VendorId = VM.ID
		where  SR.Id=@rId AND SR.IsObsolete = 0

		select SRD.Id,
			   SRD.SalesReturnId,
			   SRD.GoodsReceivedNoteDetailId,
			   SRD.OrgGroupId,
			   SRD.StockItemId,
			   SRD.POQty,
			   SRD.ReceivedQty,
			   SRD.RejectedQty,
			   SUMa.Id AS UnitTypeId,
			   SRD.CreditNoteIssueDate,
			   SRD.ReasonForRejection, 
			   SIM.Code	AS StockItemCode,
			   SIM.Title AS StockItemName,
			   SUMa.Code AS UOM,
			   SCM.Id as CategoryId,
			   SCM.Title as CategoryName,
			   0 as SubCategoryId
		from   [sPurchase].[tSalesReturnDetail] SRD (nolock)
			   Inner JOIN	sPurchase.tSalesReturn SR	ON SR.Id = SRD.SalesReturnId AND SR.IsObsolete = 0
			   Inner JOIN	sIp.tStockItemMaster SIM	ON SIM.Id = SRD.StockItemId 
			   LEFT JOIN [sIp].[tStockItemCategoryMaster] SCM ON SCM.Id = SIM.StockItemCategoryId
			   Inner JOIN  sFinanceCommon.tStockUomMaster SUMa ON SUMa.Id = SIM.IssueStockUomId
		where  SRD.SalesReturnId=@rId

	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH
END
GO
PRINT N'Altering Procedure [sPurchase].[pSalesReturnSearchGet]...';


GO


ALTER PROCEDURE [sPurchase].[pSalesReturnSearchGet]

	@rNumber nvarchar(30) = '',
	@rLocationId int,
	@rName nvarchar = '',
	@rOrgGroupId int,
	@rStartDate DateTime = null,
	@rEndDate DateTime = null,
    @rPageNo int,
	@rPageSize int,
	@rStatusId smallint = null

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY


		If @rNumber = NULL SET @rNumber=''
		If @rName = NULL SET @rName=''
		If @rStartDate = '' SET @rStartDate=NULL
		If @rEndDate = '' SET @rEndDate=NULL
		If @rStatusId is NULL SET @rStatusId=0

		DECLARE @TotalRecords BIGINT;
		-- Get the total number of records
		SELECT @TotalRecords = COUNT(*) FROM sPurchase.tSalesReturn SR (nolock)
				Left Join [sWf].[tWfStatusMaster] SM on SR.StatusId = SM.Id

        WHERE 
				((SR.SRNumber like @rNumber+'%') OR (@rNumber = ''))
   				AND ((SR.RequestedBy like @rName+'%') OR (@rName = ''))
   				--AND ((SLM.Id = @rStoreLocationId) OR (@rStoreLocationId = 0))
   				AND ((SR.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))
				AND ((SR.StatusId = @rStatusId) OR (@rStatusId=0))
				AND SR.IsObsolete = 0
				AND ((SR.CreatedDateTime >= @rStartDate) OR (ISNULL(@rStartDate,'')=''))
				AND((SR.CreatedDateTime <= @rEndDate) OR (ISNULL(@rEndDate,'')=''))

        Select t.* From (
			Select ROW_NUMBER() Over (
			Order by SR.Id desc  
			) AS 'RowNum',SR.Id,
				SR.SRNumber as Number,
				SR.RequestedBy, 
				SR.LocationId,
				ISNULL(SM.Title,'') as Status

        FROM		
				sPurchase.tSalesReturn SR (nolock)
				Left Join [sWf].[tWfStatusMaster] SM on SR.StatusId = SM.Id

	    WHERE 
				((SR.SRNumber like @rNumber+'%') OR (@rNumber = ''))
   				AND ((SR.RequestedBy like @rName+'%') OR (@rName = ''))
   				--AND ((SLM.Id = @rStoreLocationId) OR (@rStoreLocationId = 0))
   				AND ((SR.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))
				AND ((SR.StatusId = @rStatusId) OR (@rStatusId=0))
				AND SR.IsObsolete = 0
				AND ((SR.CreatedDateTime >= @rStartDate) OR (ISNULL(@rStartDate,'')=''))
				AND((SR.CreatedDateTime <= @rEndDate) OR (ISNULL(@rEndDate,'')=''))
					)t Where t.RowNum Between
		        ((@rPageNo-1)*@rPageSize +1) AND (@rPageNo*@rpageSize)

        SELECT @TotalRecords as TotalRecords
	
	 END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH  		  
END
GO
PRINT N'Altering Procedure [sAcademic].[pTaskConsolidatedDashboardGet]...';


GO

 
ALTER PROCEDURE [sEMeeting].[pOnlineMeetingAdd]
	 
        @rId bigint = 0,
		@rOrgGroupId smallint,
		@rMeetingTypeId smallint,
		@rHostEmployeeId bigint,
		@rStartDate datetime,
		@rStartTime datetime,
		@rEndDate datetime,
		@rEndTime datetime,
		@rTitle nvarchar(100) = '',
		@rDescription nvarchar(max) = '',
		@rIsDisplayMeetingId bit = 0,
		@rMeetingId nvarchar(50) = '',
		@rMeetingPass nvarchar(50) = '',
		@rIsReference bit = 0,
		@rIsResource bit=0,
		@rIsCompleted bit = 0,
		@rIsMeetingStarted bit = 0,
		@rMeetingAccountId int = 0,
		@rIsMeetingCreated bit=0,
		@rIsObsolete bit = 0,
		@rCreatedUserId bigint,
		@rIsOnParticipantVideo bit = 0,
		@rIsOnParticipantAudio bit = 0,
		@rIsOnHostVideo bit = 0,
		@rIsOnHostAudio bit = 0,
		@rIsOnParticipantScreenShare bit = 0,
		@rIsOnHostscreenShare bit = 0,
		@rAssigneeId tinyint,
		@rIsTrackAttendance bit = 0,
		@rOnlineMeetingScopeList sEMeeting.OnlineMeetingScopeDetailType READONLY,
		@rOnlineMeetingReferenceList sEMeeting.OnlineMeetingReferenceDetailType READONLY
			
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY
	 
	 BEGIN TRANSACTION	
	 DECLARE @lNextNumber BIGINT 

	 DECLARE @lAccountEmailId NVARCHAR(200),@lAccountPassword NVARCHAR(200),@lAPIKey NVARCHAR(100),@lAPISecret NVARCHAR(100),@lWebAPIKey NVARCHAR(100),
	         @lWebAPISecret NVARCHAR(100),@lMeetingProviderId NVARCHAR(100),@lJWTToken NVARCHAR(MAX),@lOAuthAccountId NVARCHAR(MAX), @lOAuthClientId NVARCHAR(MAX),@lOAuthClientSecret NVARCHAR(MAX)

	SELECT @lAccountEmailId = AccountEmailId,@lAccountPassword = AccountPassword,@lAPIKey = APIKey,@lAPISecret = APISecret,@lWebAPIKey = WebAPIKey,@lWebAPISecret = WebAPISecret,@lMeetingProviderId = MeetingProviderId,
	@lJWTToken = JWTToken, @lOAuthAccountId = OAuthAccountId , @lOAuthClientId = OAuthClientId , @lOAuthClientSecret = OAuthClientSecret
	    FROM sAcademic.tOnlineMeetingAccount
		WHERE Id = @rMeetingAccountId

	IF(@rId = 0)	
	BEGIN
		
	EXECUTE sCommon.pTableRowIdGet @rOrgGroupId, 'sEMeeting.tOnlineMeeting', 0, 1, 1,@outNextNumber = @lNextNumber output
	Print @lNextNumber
	INSERT INTO sEMeeting.tOnlineMeeting
		(
			Id,
			OrgGroupId,
			MeetingTypeId,
			HostEmployeeId,
			StartDate,
			StartTime,
			EndDate,
			EndTime,
			Title,
			Description,
			IsDisplayMeetingId,
			MeetingId,
			MeetingPassword,
			IsReference,
			IsResource,
			IsCompleted,
			IsMeetingStarted,
			MeetingAccountId,
			IsMeetingCreated,
			IsObsolete,
			AssigneeId,
			IsTrackAttendance,
			CreatedUserId,
			CreatedDateTime
		)
	VALUES
		(
			@lNextNumber,
			@rOrgGroupId,
			@rMeetingTypeId,
			@rHostEmployeeId,
			@rStartDate,
			@rStartTime,
			@rEndDate, 
			@rEndTime,
			@rTitle,
			@rDescription,
			@rIsDisplayMeetingId,
			@rMeetingId,
			@rMeetingPass,
			@rIsReference,
			@rIsResource,
			@rIsCompleted,
			@rIsMeetingStarted,
			@rMeetingAccountId,
			@rIsMeetingCreated,
			@rIsObsolete,
			@rAssigneeId,
			@rIsTrackAttendance,
			@rCreatedUserId,
			GetUtcDate()
		)
		    
	INSERT INTO sEMeeting.tOnlineMeetingScope
			(
			OnlineMeetingId,
			ScopeSourceTypeId,
			ScopeSourceId,
			IsObsolete,
			AssigneeId,
			CreatedUserId,
			CreatedDateTime
			)
			SELECT 
			@lNextNumber,
			ScopeSourceTypeId,
			ScopeSourceId,
			@rIsObsolete,
			@rAssigneeId,
			@rCreatedUserId,
			GetUtcDate()
			from @rOnlineMeetingScopeList

	IF (@rIsReference = 'true')
	BEGIN
	INSERT INTO sEMeeting.tOnlineMeetingReference
	(
	OnlineMeetingId,
	ReferencePath
	)
	SELECT
	@lNextNumber,
	ReferencePath
	from @rOnlineMeetingReferenceList
		
	END

	INSERT INTO sEMeeting.tOnlineMeetingSettings
		(
			OnlineMeetingId,
			IsOnParticipantVideo,
			IsOnParticipantAudio,
			IsOnHostVideo,
			IsOnHostAudio,
			IsOnParticipantScreenShare,
			IsOnHostscreenShare,
			AccountEmailId,
			AccountPassword,
			APIKey,
			APISecret,
			WebAPIKey,
			WebAPISecret,
			MeetingProviderId,
			JWTToken,
			OAuthAccountId,
			OAuthClientId,
			OAuthClientSecret
		)
		VALUES
		(
			@lNextNumber,
			@rIsOnParticipantVideo,
			@rIsOnParticipantAudio,
			@rIsOnHostVideo,
			@rIsOnHostAudio,
			@rIsOnParticipantScreenShare,
			@rIsOnHostscreenShare,
			@lAccountEmailId,
			@lAccountPassword,
			@lAPIKey,
			@lAPISecret,
			@lWebAPIKey,
			@lWebAPISecret,
			@lMeetingProviderId,
			@lJWTToken,
			@lOAuthAccountId,
			@lOAuthClientId,
			@lOAuthClientSecret
		)

	SELECT @lNextNumber as Result
	END	 

ELSE
	 BEGIN
	    UPDATE sEMeeting.tOnlineMeeting
	    SET
		   OrgGroupId = @rOrgGroupId,
		   MeetingTypeId = @rMeetingTypeId,
		   HostEmployeeId = @rHostEmployeeId,
		   StartDate = @rStartDate,
		   StartTime = @rStartTime,
		   EndDate = @rEndDate,
		   EndTime = @rEndTime,
		   Title = @rTitle,
		   Description = @rDescription,
		   IsDisplayMeetingId = @rIsDisplayMeetingId,
		   MeetingId = @rMeetingId,
		   MeetingPassword = @rMeetingPass,
		   IsReference = @rIsReference,
		   IsResource = @rIsResource,
		   IsCompleted = @rIsCompleted,
		   IsMeetingStarted = @rIsMeetingStarted,
		   MeetingAccountId = @rMeetingAccountId,
		   IsMeetingCreated = @rIsMeetingCreated,
		   IsObsolete = @rIsObsolete,
		   UpdatedUserId = @rCreatedUserId,
		   UpdatedDateTime = GetUtcDate()

	      WHERE Id = @rId
		  
		delete from sEMeeting.tOnlineMeetingScope where OnlineMeetingId = @rId

		INSERT INTO sEMeeting.tOnlineMeetingScope
			(
			OnlineMeetingId,
			ScopeSourceTypeId,
			ScopeSourceId,
			IsObsolete,
			CreatedUserId,
			CreatedDateTime
			)
			SELECT 
			@rId,
			ScopeSourceTypeId,
			ScopeSourceId,
			@rIsObsolete,
			@rCreatedUserId,
			GetUtcDate()
			from @rOnlineMeetingScopeList
		
		delete from sEMeeting.tOnlineMeetingReference where OnlineMeetingId = @rId

		IF (@rIsReference = 'true')
		BEGIN
			INSERT INTO sEMeeting.tOnlineMeetingReference
			(
			OnlineMeetingId,
			ReferencePath
			)
			SELECT
			@rId,
			ReferencePath
			from @rOnlineMeetingReferenceList
		END

	    SELECT 11 AS Result
	
	 END  	

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


GO
 
ALTER PROCEDURE [sEMeeting].[pOnlineMeetingGet]
	 @rId bigint = 0,
	 @rTitle nvarchar(100) ='',
	 @rFromDate date = '',
	 @rToDate date = '',
	 @rEmployeeId bigint = 0,
	 @rMeetingTypeId smallint = 0,
	 @rIsCompleted int = 0,
	 @rAssigneeId tinyint,
	 @rUserId bigint = 0,
	 @rOnlineMeetingAccountId int = 0
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 
BEGIN TRY

	if(@rId = 0)
	BEGIN 
	SELECT 
			OM.Id,
			EM.FirstName +' '+ EM.LastName as Teacher,
			DM.Title as MeetingType,
			CONVERT(NVARCHAR,OM.StartDate,103)+' '+CONVERT(NVARCHAR(5),OM.StartTime,108) as StartDate,
			CONVERT(NVARCHAR,OM.EndDate,103)+' '+CONVERT(NVARCHAR(5),OM.EndTime,108) as EndDate,
			OM.IsCompleted as StatusId,
		    ISNULL(OM.Title,'') as Title,
			OM.StartDate AS TeacherStartDate,
			OM.EndDate AS TeacherEndDate,
			OM.AssigneeId,
			(SELECT
			  (
			  STUFF((SELECT ',' + a.Code
			  FROM
			  (
			  SELECT Code FROM sEMeeting.tOnlineMeetingScope OMS INNER JOIN sSetup.tClassMaster CM ON OMS.ScopeSourceId = CM.Id Where  OnlineMeetingId = OM.Id AND OMS.ScopeSourceTypeId = 2016
			  UNION ALL
			  SELECT Code FROM sEMeeting.tOnlineMeetingScope OMS INNER JOIN sSetup.tDivisionMaster DM ON OMS.ScopeSourceId = DM.Id Where  OnlineMeetingId = OM.Id AND OMS.ScopeSourceTypeId = 2005
			  ) a
			  FOR XML PATH ('')), 1, 1, '') ) ) as Assignee 
	  FROM 
			sEMeeting.tOnlineMeeting OM
			
			INNER JOIN sHrm.tEmployeeMaster EM ON EM.Id = OM.HostEmployeeId 
			INNER JOIN sCommon.tDataMaster DM ON DM.Id = OM.MeetingTypeId AND DM.DataTypeId = 3
			INNER JOIN sCommon.tAssigneeMaster AM ON AM.Id=OM.AssigneeId
	  WHERE 
  
				((OM.Title like @rTitle) OR (ISNULL(@rTitle,'') = ''))
			AND ((OM.StartDate >= @rFromDate) OR (ISNULL(@rFromDate,'')=''))
			AND ((OM.StartDate <= @rToDate) OR (ISNULL(@rToDate,'')=''))
			AND ((OM.HostEmployeeId = @rEmployeeId) OR (@rEmployeeId = 0))
			AND ((OM.MeetingTypeId = @rMeetingTypeId) OR (@rMeetingTypeId = 0))
			AND ((CONVERT(int,OM.IsCompleted)=@rIsCompleted) OR (@rIsCompleted = 0))
			AND (OM.AssigneeId IN (1,2,3) AND @rAssigneeId=1)  OR
			(OM.AssigneeId = @rAssigneeId AND @rAssigneeId IN (2,3))
			ORDER BY
			
			OM.StartDate , OM.StartTime asc
	SELECT
			 OMS.OnlineMeetingId,
			 OMS.ScopeSourceTypeId,
			 OMS.ScopeSourceId,
			 OM.DisplayTitle AS Source,
			 'Organization' AS SourceType,
			 '' AS ROllNo,
             '' AS Code,
             '' AS ClassDepartment,
             OM.DisplayTitle AS OrgName,
			 OMS.AssigneeId
			FROM sEMeeting.tOnlineMeetingScope OMS
			INNER JOIN sSetup.tOrganizationMaster OM
				ON OMS.ScopeSourceId=OM.Id
			LEFT JOIN sCommon.tAssigneeMaster AM 
				ON AM.Id = OMS.AssigneeId
			WHERE OMS.OnlineMeetingId=@rId AND OMS.ScopeSourceTypeId=2021

			UNION ALL

			SELECT
			 OMS.OnlineMeetingId,
			 OMS.ScopeSourceTypeId,
			 OMS.ScopeSourceId,
			 DM.DisplayTitle AS Source,
			 'Department' AS SourceType,
			 '' AS ROllNo,
             '' AS Code,
             DM.Title AS ClassDepartment,
             OM.DisplayTitle AS OrgName,
			 OMS.AssigneeId
			FROM sEMeeting.tOnlineMeetingScope OMS
			INNER JOIN sSetup.tDepartmentMaster DM
				ON OMS.ScopeSourceId=DM.Id
			INNER JOIN sSetup.tOrganizationMaster OM
				ON DM.OrgId=OM.Id 
			LEFT JOIN sCommon.tAssigneeMaster AM 
				ON AM.Id = OMS.AssigneeId
			WHERE OMS.OnlineMeetingId=@rId AND OMS.ScopeSourceTypeId=2017

			UNION ALL

			SELECT
			 OMS.OnlineMeetingId,
			 OMS.ScopeSourceTypeId,
			 OMS.ScopeSourceId,
			 CM.DisplayTitle AS Source,
			 'Class' AS SourceType,
			 '' AS ROllNo,
             '' AS Code,
             DMast.Title AS ClassDepartment,
             OM.DisplayTitle AS OrgName,
			 OMS.AssigneeId
			FROM sEMeeting.tOnlineMeetingScope OMS
			INNER JOIN  sSetup.tClassMaster CM
				ON OMS.ScopeSourceId=CM.Id
			INNER JOIN sSetup.tDepartmentMaster DMast
				ON CM.DepartmentId =DMast.Id		
			INNER JOIN sSetup.tOrganizationMaster OM
				ON DMast.OrgId=OM.Id
			LEFT JOIN sCommon.tAssigneeMaster AM 
				ON AM.Id = OMS.AssigneeId
			WHERE OMS.OnlineMeetingId=@rId AND OMS.ScopeSourceTypeId=2016

			UNION ALL

			SELECT
			 OMS.OnlineMeetingId,
			 OMS.ScopeSourceTypeId,
			 OMS.ScopeSourceId,
			 DM.Code AS Source,
			 'Division' AS SourceType,
			 '' AS ROllNo,
             '' AS Code,
             DMast.Title AS ClassDepartment,
             OM.DisplayTitle AS OrgName,
			 OMS.AssigneeId
			FROM sEMeeting.tOnlineMeetingScope OMS
			INNER JOIN   sSetup.tDivisionMaster DM
				ON OMS.ScopeSourceId=DM.Id
			INNER JOIN 	sSetup.tClassMaster CM
				ON DM.ClassId=CM.Id	
			INNER JOIN sSetup.tDepartmentMaster DMast
				ON CM.DepartmentId =DMast.Id
			INNER JOIN sSetup.tOrganizationMaster OM
				ON DMast.OrgId=OM.Id
			LEFT JOIN sCommon.tAssigneeMaster AM 
				ON AM.Id = OMS.AssigneeId
				WHERE OMS.OnlineMeetingId=@rId AND OMS.ScopeSourceTypeId=2005

			UNION ALL
			
			SELECT
			 OMS.OnlineMeetingId,
			 OMS.ScopeSourceTypeId,
			 OMS.ScopeSourceId,
			 [sStudent].[fStudentNameGet](SM.FirstName , SM.MiddleName, SM.LastName,OM.StudentNameFormatId ) AS Source,
			 'Student' AS SourceType,
			 SA.ROllNo,
             SA.Code,
             SA.ClassName+' - '+SA.DivisionName AS ClassDepartment,
             OM.DisplayTitle AS OrgName,
			 OMS.AssigneeId
			FROM sEMeeting.tOnlineMeetingScope OMS
		    INNER JOIN sStudent.tStudentMaster SM
				ON OMS.ScopeSourceId=SM.Id
			INNER JOIN  sStudent.vStudentAcademic SA
				ON OMS.ScopeSourceId=SA.StudentId AND SA.IsActiveBatch=1 AND SA.StatusId=1		
			INNER JOIN sSetup.tOrganizationMaster OM
				ON SM.OrgId = OM.Id
			LEFT JOIN sCommon.tAssigneeMaster AM 
				ON AM.Id = OMS.AssigneeId
			WHERE OMS.OnlineMeetingId=@rId AND OMS.ScopeSourceTypeId=2003
			
			UNION ALL
			
			SELECT
			 OMS.OnlineMeetingId,
			 OMS.ScopeSourceTypeId,
			 OMS.ScopeSourceId,
			 SA.Name AS Source,
			 'Student' AS SourceType,
			 SA.ROllNo,
             SA.Code,
             SA.ClassName+' - '+SA.DivisionName AS ClassDepartment,
             OM.DisplayTitle AS OrgName,
			 OMS.AssigneeId
			FROM sEMeeting.tOnlineMeetingScope OMS
		   	INNER JOIN  sStudent.vStudentAcademic SA
				ON OMS.ScopeSourceId=SA.AcademicId AND SA.IsActiveBatch=1 AND SA.StatusId=1
			INNER JOIN sSetup.tOrganizationMaster OM
				ON SA.OrgId = OM.Id	
			LEFT JOIN sCommon.tAssigneeMaster AM 
				ON AM.Id = OMS.AssigneeId
			WHERE OMS.OnlineMeetingId=@rId AND OMS.ScopeSourceTypeId=2001
			
			UNION ALL
			
			SELECT
			 OMS.OnlineMeetingId,
			 OMS.ScopeSourceTypeId,
			 OMS.ScopeSourceId,
			 vEC.FirstName +' '+ vEC.LastName AS Source,
			 'Employee' AS SourceType,
			 '' AS ROllNo,
             vEC.Code,
             vEC.DepartmentName AS ClassDepartment,
             OM.DisplayTitle AS OrgName,
			 OMS.AssigneeId
			FROM sEMeeting.tOnlineMeetingScope OMS
			INNER JOIN sHrm.vEmployeeCommunication vEC 
				ON OMS.ScopeSourceId=vEC.EmployeeId AND vEC.StatusId=7
			INNER JOIN sSetup.tOrganizationMaster OM
				ON vEC.OrgId = OM.Id	
			LEFT JOIN sCommon.tAssigneeMaster AM 
				ON AM.Id = OMS.AssigneeId
			WHERE OMS.OnlineMeetingId=@rId AND OMS.ScopeSourceTypeId=95
	SELECT
		OnlineMeetingId, 
		ReferencePath 
		from sEMeeting.tOnlineMeetingReference OMR
		where OMR.OnlineMeetingId = @rId
	END
	ELSE
	BEGIN
		SELECT 
			OM.Id,
			EM.EmployeeName as Teacher,
			Em.EmployeeId as TeacherId,
			DM.Title as MeetingType,
			DM.Id as MeetingTypeId,
			CONVERT(NVARCHAR,OM.StartDate) as StartDate,
			CONVERT(NVARCHAR,OM.StartTime,108) as StartTime,
			CONVERT(NVARCHAR,OM.EndDate) as EndDate,
			CONVERT(NVARCHAR,OM.EndTime,108) as EndTime,
			OM.IsCompleted as StatusId,
			OM.Title as MeetingTitle,
			OM.Description,
			OM.IsDisplayMeetingId,
			OM.MeetingId,
			OM.MeetingPassword,
			ISNULL(OM.IsReference,0) as IsReference,
			ISNULL(OM.IsResource,0) as IsResource,
			ISNULL(OM.IsCompleted,0) as IsCompleted,
			ISNULL(OM.IsMeetingStarted,0) as IsMeetingStarted,
			ISNULL(OMS.IsOnParticipantVideo,0) AS IsOnParticipantVideo,
			ISNULL(OMS.IsOnParticipantAudio,0) AS IsOnParticipantAudio,
			ISNULL(OMS.IsOnHostVideo,0) AS IsOnHostVideo,
			ISNULL(OMS.IsOnHostAudio,0) AS IsOnHostAudio,
			ISNULL(OMS.IsOnParticipantScreenShare,0) AS IsOnParticipantScreenShare,
			ISNULL(OMS.IsOnHostscreenShare,0) AS IsOnHostscreenShare,
			ISNULL(OM.MeetingAccountId,0) AS MeetingAccountId,
			ISNULL(OM.IsMeetingCreated,0) AS IsMeetingCreated,
			ISNULL(OM.IsTrackAttendance,0) AS IsTrackAttendance,
			ISNULL(OMS.MeetingProviderId,0) As MeetingProviderId,
			OM.AssigneeId,
			OM.StartDate AS TeacherStartDate,
			OM.EndDate AS TeacherEndDate
	  FROM 
			sEMeeting.tOnlineMeeting OM
			
			INNER JOIN sHrm.vExtEmployee EM ON EM.EmployeeId = OM.HostEmployeeId 
			INNER JOIN sCommon.tDataMaster DM ON DM.Id = OM.MeetingTypeId AND DM.DataTypeId = 3
			LEFT JOIN sEMeeting.tOnlineMeetingSettings OMS ON OMS.OnlineMeetingId = OM.Id
			INNER JOIN sCommon.tAssigneeMaster AM ON AM.Id=OM.AssigneeId
	  WHERE 
			(OM.Id = @rId)
	  ORDER BY
			OM.StartDate , OM.StartTime asc
		SELECT
			 OMS.OnlineMeetingId,
			 OMS.ScopeSourceTypeId,
			 OMS.ScopeSourceId,
			 OM.DisplayTitle AS Source,
			 'Organization' AS SourceType,
			 '' AS ROllNo,
             '' AS Code,
             '' AS ClassDepartment,
             OM.DisplayTitle AS OrgName,
			 OMS.AssigneeId
			FROM sEMeeting.tOnlineMeetingScope OMS
			INNER JOIN sSetup.tOrganizationMaster OM
				ON OMS.ScopeSourceId=OM.Id
			LEFT JOIN sCommon.tAssigneeMaster AM 
				ON AM.Id = OMS.AssigneeId
			WHERE OMS.OnlineMeetingId=@rId AND OMS.ScopeSourceTypeId=2021

			UNION ALL

			SELECT
			 OMS.OnlineMeetingId,
			 OMS.ScopeSourceTypeId,
			 OMS.ScopeSourceId,
			 DM.DisplayTitle AS Source,
			 'Department' AS SourceType,
			 '' AS ROllNo,
             '' AS Code,
             DM.Title AS ClassDepartment,
             OM.DisplayTitle AS OrgName,
			 OMS.AssigneeId
			FROM sEMeeting.tOnlineMeetingScope OMS
			INNER JOIN sSetup.tDepartmentMaster DM
				ON OMS.ScopeSourceId=DM.Id
			INNER JOIN sSetup.tOrganizationMaster OM
				ON DM.OrgId=OM.Id
			LEFT JOIN sCommon.tAssigneeMaster AM 
				ON AM.Id = OMS.AssigneeId
			WHERE OMS.OnlineMeetingId=@rId AND OMS.ScopeSourceTypeId=2017

			UNION ALL

			SELECT
			 OMS.OnlineMeetingId,
			 OMS.ScopeSourceTypeId,
			 OMS.ScopeSourceId,
			 CM.DisplayTitle AS Source,
			 'Class' AS SourceType,
			 '' AS ROllNo,
             '' AS Code,
             DMast.Title AS ClassDepartment,
             OM.DisplayTitle AS OrgName,
			 OMS.AssigneeId
			FROM sEMeeting.tOnlineMeetingScope OMS
			INNER JOIN  sSetup.tClassMaster CM
				ON OMS.ScopeSourceId=CM.Id
			INNER JOIN sSetup.tDepartmentMaster DMast
				ON CM.DepartmentId =DMast.Id		
			INNER JOIN sSetup.tOrganizationMaster OM
				ON DMast.OrgId=OM.Id
				LEFT JOIN sCommon.tAssigneeMaster AM 
				ON AM.Id = OMS.AssigneeId
			WHERE OMS.OnlineMeetingId=@rId AND OMS.ScopeSourceTypeId=2016

			UNION ALL

			SELECT
			 OMS.OnlineMeetingId,
			 OMS.ScopeSourceTypeId,
			 OMS.ScopeSourceId,
			 DM.Code AS Source,
			 'Division' AS SourceType,
			 '' AS ROllNo,
             '' AS Code,
             DMast.Title AS ClassDepartment,
             OM.DisplayTitle AS OrgName,
			 OMS.AssigneeId
			FROM sEMeeting.tOnlineMeetingScope OMS
			INNER JOIN   sSetup.tDivisionMaster DM
				ON OMS.ScopeSourceId=DM.Id
			INNER JOIN 	sSetup.tClassMaster CM
				ON DM.ClassId=CM.Id	
			INNER JOIN sSetup.tDepartmentMaster DMast
				ON CM.DepartmentId =DMast.Id
			INNER JOIN sSetup.tOrganizationMaster OM
				ON DMast.OrgId=OM.Id	
				LEFT JOIN sCommon.tAssigneeMaster AM 
				ON AM.Id = OMS.AssigneeId
				WHERE OMS.OnlineMeetingId=@rId AND OMS.ScopeSourceTypeId=2005

			UNION ALL
			
			SELECT
			 OMS.OnlineMeetingId,
			 OMS.ScopeSourceTypeId,
			 OMS.ScopeSourceId,
			 [sStudent].[fStudentNameGet](SM.FirstName , SM.MiddleName, SM.LastName,OM.StudentNameFormatId ) AS Source,
			 'Student' AS SourceType,
			 SA.ROllNo,
             SA.Code,
             SA.ClassName+' - '+SA.DivisionName AS ClassDepartment,
             OM.DisplayTitle AS OrgName,
			 OMS.AssigneeId
			FROM sEMeeting.tOnlineMeetingScope OMS
		    INNER JOIN sStudent.tStudentMaster SM
				ON OMS.ScopeSourceId=SM.Id
			INNER JOIN  sStudent.vStudentAcademic SA
				ON OMS.ScopeSourceId=SA.StudentId AND SA.IsActiveBatch=1 AND SA.StatusId=1		
			INNER JOIN sSetup.tOrganizationMaster OM
				ON SM.OrgId = OM.Id
				LEFT JOIN sCommon.tAssigneeMaster AM 
				ON AM.Id = OMS.AssigneeId
			WHERE OMS.OnlineMeetingId=@rId AND OMS.ScopeSourceTypeId=2003
			
			UNION ALL
			
			SELECT
			 OMS.OnlineMeetingId,
			 OMS.ScopeSourceTypeId,
			 OMS.ScopeSourceId,
			 SA.Name AS Source,
			 'Student' AS SourceType,
			 SA.ROllNo,
             SA.Code,
             SA.ClassName+' - '+SA.DivisionName AS ClassDepartment,
             OM.DisplayTitle AS OrgName,
			 OMS.AssigneeId
			FROM sEMeeting.tOnlineMeetingScope OMS
		   	INNER JOIN  sStudent.vStudentAcademic SA
				ON OMS.ScopeSourceId=SA.AcademicId AND SA.IsActiveBatch=1 AND SA.StatusId=1
			INNER JOIN sSetup.tOrganizationMaster OM
				ON SA.OrgId = OM.Id	
				LEFT JOIN sCommon.tAssigneeMaster AM 
				ON AM.Id = OMS.AssigneeId
			WHERE OMS.OnlineMeetingId=@rId AND OMS.ScopeSourceTypeId=2001
			
			UNION ALL
			
			SELECT
			 OMS.OnlineMeetingId,
			 OMS.ScopeSourceTypeId,
			 OMS.ScopeSourceId,
			 vEC.FirstName +' '+ vEC.LastName AS Source,
			 'Employee' AS SourceType,
			 '' AS ROllNo,
             vEC.Code,
             vEC.DepartmentName AS ClassDepartment,
             OM.DisplayTitle AS OrgName,
			 OMS.AssigneeId
			FROM sEMeeting.tOnlineMeetingScope OMS
			INNER JOIN sHrm.vEmployeeCommunication vEC 
				ON OMS.ScopeSourceId=vEC.EmployeeId AND vEC.StatusId=7
			INNER JOIN sSetup.tOrganizationMaster OM
				ON vEC.OrgId = OM.Id	
			LEFT JOIN sCommon.tAssigneeMaster AM 
				ON AM.Id = OMS.AssigneeId
			WHERE OMS.OnlineMeetingId=@rId AND OMS.ScopeSourceTypeId=95
		SELECT
		OnlineMeetingId, 
		ReferencePath 
		from sEMeeting.tOnlineMeetingReference OMR
		where OMR.OnlineMeetingId = @rId
	END
END TRY
BEGIN CATCH 
		SELECT -1 AS Result
END CATCH
END
GO
PRINT N'Altering Procedure [sEMeeting].[pOnlineMeetingTeacherGet]...';


GO

ALTER PROCEDURE [sEMeeting].[pOnlineMeetingTeacherGet]
	 
	 @rSourceId bigint,
	 @rSourceTypeId int,
	 @rOrgGroupId int,
	 @rUserId bigint,
	 @rIsOnlineLearningAdmin bit,
	 @rBatchId int
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 
BEGIN
	BEGIN TRY
	If (@rSourceTypeId = 95)
		BEGIN
		if(@rIsOnlineLearningAdmin = 1)
			BEGIN
			SELECT EM.EmployeeId as Id, 
			[sHrm].[fEmployeeNameGet](EM.FirstName , EM.MiddleName, EM.LastName,ISNULL(OrgM.EmployeeNameFormatId,1)) AS Title 
			FROM sHrm.vExtEmployee EM 
			INNER JOIN sSetup.vUserOrganization OM ON OM.Id = EM.OrgId INNER JOIN sSetup.tOrganizationMaster OrgM 
			ON OrgM.Id=EM.OrgId 
			WHERE ISNULL(IsOtherUser,0) = 0 AND  StatusId =7  AND EmployeeGroupId = 1 AND OM.OrgGroupId = @rOrgGroupId AND OM.UserId = @rUserId
			ORDER BY Title
			END
		else
			BEGIN
			SELECT EM.EmployeeId as Id, 
			[sHrm].[fEmployeeNameGet](EM.FirstName , EM.MiddleName, EM.LastName,ISNULL(OrgM.EmployeeNameFormatId,1)) AS Title 
			FROM sHrm.vExtEmployee EM 
			INNER JOIN sSetup.tOrganizationMaster OrgM 
			ON OrgM.Id=EM.OrgId 
			WHERE ISNULL(EM.IsOtherUser,0) = 0 AND  EM.StatusId =7  AND EM.EmployeeGroupId = 1 AND  EM.EmployeeId = @rSourceId 
			ORDER BY EmployeeName
			END
		END
	else
		BEGIN
		SELECT DISTINCT EM.Id,ISNULL(EM.FirstName,'') +' ' + ISNULL(EM.MiddleName,'') +' ' + ISNULL(EM.LastName ,'') As Title FROM [sHrm].[tEmployeeMaster] EM
		 INNER JOIN [sAcademic].[tOnlineLearning] OL ON OL.TeacherEmployeeId=EM.Id WHERE OL.BatchId = @rBatchId
		END
	END TRY

	BEGIN CATCH 
		SELECT -1 AS Result
	END CATCH
END
GO
PRINT N'Altering Procedure [sEMeeting].[pOnlineMeetingTeacherStudentListGet]...';


GO

 
ALTER PROCEDURE [sEMeeting].[pOnlineMeetingTeacherStudentListGet]
	 @rId bigint = 0,	 
	 @rScopeSourceId bigint,
	 @rScopeSourceTypeId int,
	 @rMeetingTypeId int = 0,
	 @rStartDate date = '',
	 @rEndDate date = '',
	 @rIsCompleted int= 0,
	 @rTitle nvarchar(100) ='',
	 @rOrgGroupBatchId int
		 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 

BEGIN 

	if (@rId = 0)
		BEGIN
	  DECLARE @lAcademicId bigint,@lStudentId bigint,@lClassId int,@lDivisionId int,@lOrgId int,@lDepartmentId int,@lEmployeeId bigint,
	 @lClassName nvarchar(100)


	 SELECT @lAcademicId = A.Id,@lStudentId = A.StudentId,@lClassId = A.ClassId, @lDivisionId = A.DivisionId, @lOrgId= A.OrgId, @lDepartmentId=CM.DepartmentId,
		@lClassName=CM.Title,@lCLassId=CM.Id
     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 A.Id = @rScopeSourceId AND BM.OrgGroupBatchId = @rOrgGroupBatchId

	 If (@rScopeSourceTypeId = 2003)
	 BEGIN
	
	  SELECT 
			OM.Id,
			DM.Title AS MeetingType,
			ISNULL(EM.FirstName,'') +' ' + ISNULL(EM.MiddleName,'') +' ' + ISNULL(EM.LastName ,'') AS Teacher,
			CONVERT(NVARCHAR,OM.StartDate,103)+' '+CONVERT(NVARCHAR(5),OM.StartTime,108) as StartDate,
			CONVERT(NVARCHAR,OM.EndDate,103)+' '+CONVERT(NVARCHAR(5),OM.EndTime,108) as EndDate,
			(CASE WHEN OM.IsCompleted=0 THEN 'Pending' WHEN OM.IsCompleted=1 THEN 'Completed'  WHEN OM.IsCompleted=721 THEN 'Cancel' END) AS Status,
			ISNULL(OM.Title,'') AS Title
			
		FROM 
				sEMeeting.tOnlineMeeting OM
				INNER JOIN sHrm.tEmployeeMaster EM ON EM.Id = OM.HostEmployeeId 
				INNER JOIN sCommon.tDataMaster DM ON DM.Id = OM.MeetingTypeId AND DM.DataTypeId = 3
				INNER JOIN sEMeeting.tOnlineMeetingScope OMS ON OMS.OnlineMeetingId = OM.Id
				INNER JOIN (SELECT OMS.OnlineMeetingId FROM sEMeeting.tOnlineMeetingScope OMS
				WHERE (
				( 
				(OMS.ScopeSourceTypeId = 2021 AND OMS.ScopeSourceId =@lOrgId AND OMS.AssigneeId = 2) 
				OR
				(OMS.ScopeSourceTypeId = 2017 AND OMS.ScopeSourceId =@lDepartmentId AND OMS.AssigneeId = 2) OR
				(OMS.ScopeSourceTypeId = 2016 AND OMS.ScopeSourceId =@lClassId AND OMS.AssigneeId = 2) OR
				(OMS.ScopeSourceTypeId = 2005 AND OMS.ScopeSourceId =@lDivisionId AND OMS.AssigneeId = 2) OR
				(OMS.ScopeSourceTypeId = 2003 AND OMS.ScopeSourceId =@lStudentId AND OMS.AssigneeId = 2) OR
				(OMS.ScopeSourceTypeId = 2001 AND OMS.ScopeSourceId =@lAcademicId AND OMS.AssigneeId = 2)
				 ))
				) D
				ON D.OnlineMeetingId= OM.Id

			WHERE
				((OM.Title like '%' +@rTitle+ '%') OR (ISNULL(@rTitle,'')=''))
				AND ((OM.EndDate >= CASE WHEN OM.MeetingTypeId = 1 THEN CONVERT(varchar,GETDATE(),23) ELSE '1900-01-01' END)) 
				AND ((OM.StartDate <= CASE WHEN OM.MeetingTypeId = 1 THEN CONVERT(varchar,GETDATE(),23) ELSE '9999-01-01' END))
				AND ((OM.StartTime <= CASE WHEN OM.MeetingTypeId = 1 AND OM.StartDate = CONVERT(varchar,GETDATE(),23) THEN CONVERT(varchar,GETDATE(),14) ELSE OM.StartTime END)) 
				AND ((OM.EndTime >= CASE WHEN OM.MeetingTypeId = 1  AND OM.EndDate = CONVERT(varchar,GETDATE(),23)THEN CONVERT(varchar,GETDATE(),14) ELSE OM.EndTime END))
				AND ((OM.StartDate >= @rStartDate) OR (ISNULL(@rStartDate,'')=''))
				AND ((OM.StartDate <= @rEndDate) OR (ISNULL(@rEndDate,'')=''))
				AND ((OM.MeetingTypeId = @rMeetingTypeId)OR (ISNULL(@rMeetingTypeId,0)=0))
				AND ((Convert(int,OM.IsCompleted)=@rIsCompleted) OR (@rIsCompleted = 0))
				
				ORDER BY OM.IsCompleted,OM.StartDate,OM.StartTime,OM.EndDate
			END
		
	IF (@rScopeSourceTypeId = 95)
	BEGIN 
	DECLARE @lOrgId1 int,@lDepartmentId1 int,@lEmployeeId1 bigint
	 SELECT @lOrgId1 = EC.OrgId, @lDepartmentId1 =EC.DepartmentId, @lEmployeeId1 = EC.EmployeeId
     FROM sHrm.vEmployeeCommunication EC
	 WHERE EC.Id = @rScopeSourceId
		SELECT 
			OM.Id,
			DM.Title AS MeetingType,
			CONVERT(NVARCHAR,OM.StartDate,103)+' '+CONVERT(NVARCHAR(5),OM.StartTime,108) as StartDate,
			CONVERT(NVARCHAR,OM.EndDate,103)+' '+CONVERT(NVARCHAR(5),OM.EndTime,108) as EndDate,
			(CASE WHEN OM.IsCompleted=0 THEN 'Pending' WHEN OM.IsCompleted=1 THEN 'Completed'  WHEN OM.IsCompleted=721 THEN 'Cancel' END) AS Status,
			ISNULL(OM.Title,'') As Title,
			OM.StartDate AS TeacherStartDateTime,
			OM.EndDate AS TeacherEndDateTime
			FROM 
				sEMeeting.tOnlineMeeting OM
				INNER JOIN sHrm.tEmployeeMaster EM ON EM.Id = OM.HostEmployeeId
				INNER JOIN sCommon.tDataMaster DM ON DM.Id = OM.MeetingTypeId AND DM.DataTypeId = 3
				INNER JOIN (SELECT OMS.OnlineMeetingId FROM sEMeeting.tOnlineMeetingScope OMS
				WHERE (
				( 
				(OMS.ScopeSourceTypeId = 2021 AND OMS.ScopeSourceId =@lOrgId1 AND OMS.AssigneeId = 3) 
				OR
				(OMS.ScopeSourceTypeId = 2017 AND OMS.ScopeSourceId =@lDepartmentId1 AND OMS.AssigneeId = 3)
				OR
				(OMS.ScopeSourceTypeId = 95 AND OMS.ScopeSourceId =@lEmployeeId1 AND OMS.AssigneeId = 3)
				 ))
				) D
				ON D.OnlineMeetingId= OM.Id OR (OM.HostEmployeeId = @rScopeSourceId)
			WHERE
				((OM.MeetingTypeId = @rMeetingTypeId) OR (@rMeetingTypeId = 0))
				AND ((OM.EndDate >= CASE WHEN OM.MeetingTypeId = 1 THEN CONVERT(varchar,GETDATE(),23) ELSE '1900-01-01' END)) 
				AND ((OM.StartDate <= CASE WHEN OM.MeetingTypeId = 1 THEN CONVERT(varchar,GETDATE(),23) ELSE '9999-01-01' END))
				AND ((OM.StartTime <= CASE WHEN OM.MeetingTypeId = 1 AND OM.StartDate = CONVERT(varchar,GETDATE(),23) THEN CONVERT(varchar,GETDATE(),14) ELSE OM.StartTime END)) 
				AND ((OM.EndTime >= CASE WHEN OM.MeetingTypeId = 1 AND OM.EndDate = CONVERT(varchar,GETDATE(),23) THEN CONVERT(varchar,GETDATE(),14) ELSE OM.EndTime END))
				AND ((OM.StartDate >= @rStartDate) OR (ISNULL(@rStartDate,'')=''))
				AND ((OM.StartDate <= @rEndDate) OR (ISNULL(@rEndDate,'')=''))
				AND ((Convert(int,OM.IsCompleted)=@rIsCompleted) OR (@rIsCompleted = 0))
				AND ((OM.Title like '%' +@rTitle+ '%') OR (ISNULL(@rTitle,'')=''))
				ORDER BY OM.IsCompleted,OM.StartDate,OM.StartTime,OM.EndDate
		END
		END
	ELSE
	IF (@rScopeSourceTypeId = 2003)
	BEGIN 

	 DECLARE @lAcademicId2 bigint,@lStudentId2 bigint,@lClassId2 int,@lDivisionId2 int,@lOrgId2 int,@lDepartmentId2 int,@lEmployeeId2 bigint,
	 @lClassName2 nvarchar(100)


	 SELECT @lAcademicId = A.Id,@lStudentId = A.StudentId,@lClassId = A.ClassId, @lDivisionId = A.DivisionId, @lOrgId= A.OrgId, @lDepartmentId=CM.DepartmentId,
	 @lClassName=CM.Title,@lCLassId=CM.Id
     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 A.Id = @rScopeSourceId AND BM.OrgGroupBatchId = @rOrgGroupBatchId



	SELECT
			OM.Id,
			DM.Title AS MeetingType,
			ISNULL(EM.FirstName,'') +' ' + ISNULL(EM.MiddleName,'') +' ' + ISNULL(EM.LastName ,'')  AS Teacher,
			CONVERT(NVARCHAR,OM.StartDate,103)+' '+CONVERT(NVARCHAR(5),OM.StartTime,108) as StartTime,
			CONVERT(NVARCHAR,OM.EndDate,103)+' '+CONVERT(NVARCHAR(5),OM.EndTime,108) as EndTime,
			(CASE WHEN OM.IsCompleted=0 THEN 'Pending' ELSE 'Completed' END) AS Status,
			OM.MeetingTypeId AS MeetingTypeId,
			OM.IsDisplayMeetingId AS IsDisplayMeetingId,
			OM.MeetingId AS MeetingId,
			OM.MeetingPassword AS MeetingPassword,
			ISNULL(OM.IsMeetingStarted,'') AS IsMeetingStarted,
			OM.IsCompleted AS IsCompleted,
			ISNULL(OM.Description,'')AS Description,
			ISNULL(OM.Title,'') AS Title,
			ISNULL(OM.IsMeetingStarted,'') AS IsMeetingStarted,
			OM.IsMeetingCreated AS IsMeetingCreated,
			OM.IsReference,
			OM.IsResource
          FROM 
				sEMeeting.tOnlineMeeting OM
				
				INNER JOIN sHrm.tEmployeeMaster EM ON EM.Id = OM.HostEmployeeId 
				INNER JOIN sCommon.tDataMaster DM ON DM.Id = OM.MeetingTypeId AND DM.DataTypeId = 3
				INNER JOIN (SELECT OMS.OnlineMeetingId FROM sEMeeting.tOnlineMeetingScope OMS
				WHERE (
				( 
				(OMS.ScopeSourceTypeId = 2021 AND OMS.ScopeSourceId =@lOrgId AND OMS.AssigneeId = 2) 
				OR
				(OMS.ScopeSourceTypeId = 2017 AND OMS.ScopeSourceId =@lDepartmentId2 AND OMS.AssigneeId = 2) OR
				(OMS.ScopeSourceTypeId = 2016 AND OMS.ScopeSourceId =@lClassId2 AND OMS.AssigneeId = 2) OR
				(OMS.ScopeSourceTypeId = 2005 AND OMS.ScopeSourceId =@lDivisionId2 AND OMS.AssigneeId = 2) OR
				(OMS.ScopeSourceTypeId = 2003 AND OMS.ScopeSourceId =@lStudentId2 AND OMS.AssigneeId = 2) OR
				(OMS.ScopeSourceTypeId = 2001 AND OMS.ScopeSourceId =@lAcademicId2 AND OMS.AssigneeId = 2)
				 ))
				) D
				ON D.OnlineMeetingId= OM.Id

            
            WHERE
				 OM.Id = @rId

				ORDER BY OM.IsCompleted,OM.StartDate,OM.EndDate
		END
		
		IF(@rScopeSourceTypeId = 95)
		BEGIN
		SELECT 
			OM.Id,
			DM.Title AS MeetingType,
			ISNULL(EM.FirstName,'') +' ' + ISNULL(EM.MiddleName,'') +' ' + ISNULL(EM.LastName ,'')  AS Teacher,
			CONVERT(NVARCHAR,OM.StartDate,103)+' '+CONVERT(NVARCHAR(5),OM.StartTime,108) as StartTime,
			CONVERT(NVARCHAR,OM.EndDate,103)+' '+CONVERT(NVARCHAR(5),OM.EndTime,108) as EndTime,
			(CASE WHEN OM.IsCompleted=0 THEN 'Pending' ELSE 'Completed' END) AS Status,
			OM.MeetingTypeId AS MeetingTypeId,
			OM.IsDisplayMeetingId AS IsDisplayMeetingId,
			OM.MeetingId AS MeetingId,
			OM.MeetingPassword AS MeetingPassword,
			OM.IsCompleted AS IsCompleted,
			ISNULL(OM.Title,'') AS Title,
			ISNULL(OM.Description,'')AS Description,
			ISNULL(OM.IsMeetingStarted,'') AS IsMeetingStarted,
			OM.IsMeetingCreated AS IsMeetingCreated,
			OM.IsTrackAttendance AS IsTrackAttendance,
			OM.IsReference,
			OM.IsResource,
			OM.HostEmployeeId
		FROM 
				sEMeeting.tOnlineMeeting OM
				INNER JOIN sHrm.tEmployeeMaster EM ON EM.Id = OM.HostEmployeeId
				INNER JOIN sCommon.tDataMaster DM ON DM.Id = OM.MeetingTypeId AND DM.DataTypeId = 3
				INNER JOIN sEMeeting.tOnlineMeetingScope OMS ON OMS.OnlineMeetingId = OM.Id
		WHERE
			 OM.Id = @rId
			 AND (OM.HostEmployeeId = @rScopeSourceId OR OMS.ScopeSourceId = @rScopeSourceId AND OMS.ScopeSourceTypeId = @rScopeSourceTypeId)
			ORDER BY OM.CreatedDateTime
		SELECT
			 OMS.OnlineMeetingId,
			 OMS.ScopeSourceTypeId,
			 OMS.ScopeSourceId,
			 OM.DisplayTitle AS Source,
			 'Organization' AS SourceType,
			 '' AS ROllNo,
             '' AS Code,
             '' AS ClassDepartment,
             OM.DisplayTitle AS OrgName,
			 OMS.AssigneeId
			FROM sEMeeting.tOnlineMeetingScope OMS
			INNER JOIN sSetup.tOrganizationMaster OM
				ON OMS.ScopeSourceId=OM.Id
			LEFT JOIN sCommon.tAssigneeMaster AM 
				ON AM.Id = OMS.AssigneeId
			WHERE OMS.OnlineMeetingId=@rId AND OMS.ScopeSourceTypeId=2021

			UNION ALL

			SELECT
			 OMS.OnlineMeetingId,
			 OMS.ScopeSourceTypeId,
			 OMS.ScopeSourceId,
			 DM.DisplayTitle AS Source,
			 'Department' AS SourceType,
			 '' AS ROllNo,
             '' AS Code,
             DM.Title AS ClassDepartment,
             OM.DisplayTitle AS OrgName,
			 OMS.AssigneeId
			FROM sEMeeting.tOnlineMeetingScope OMS
			INNER JOIN sSetup.tDepartmentMaster DM
				ON OMS.ScopeSourceId=DM.Id
			INNER JOIN sSetup.tOrganizationMaster OM
				ON DM.OrgId=OM.Id
			LEFT JOIN sCommon.tAssigneeMaster AM 
				ON AM.Id = OMS.AssigneeId 
			WHERE OMS.OnlineMeetingId=@rId AND OMS.ScopeSourceTypeId=2017

			UNION ALL

			SELECT
			 OMS.OnlineMeetingId,
			 OMS.ScopeSourceTypeId,
			 OMS.ScopeSourceId,
			 CM.DisplayTitle AS Source,
			 'Class' AS SourceType,
			 '' AS ROllNo,
             '' AS Code,
             DMast.Title AS ClassDepartment,
             OM.DisplayTitle AS OrgName,
			 OMS.AssigneeId
			FROM sEMeeting.tOnlineMeetingScope OMS
			INNER JOIN  sSetup.tClassMaster CM
				ON OMS.ScopeSourceId=CM.Id
			INNER JOIN sSetup.tDepartmentMaster DMast
				ON CM.DepartmentId =DMast.Id		
			INNER JOIN sSetup.tOrganizationMaster OM
				ON DMast.OrgId=OM.Id
			LEFT JOIN sCommon.tAssigneeMaster AM 
				ON AM.Id = OMS.AssigneeId
			WHERE OMS.OnlineMeetingId=@rId AND OMS.ScopeSourceTypeId=2016

			UNION ALL

			SELECT
			 OMS.OnlineMeetingId,
			 OMS.ScopeSourceTypeId,
			 OMS.ScopeSourceId,
			 DM.Code AS Source,
			 'Division' AS SourceType,
			 '' AS ROllNo,
             '' AS Code,
             DMast.Title AS ClassDepartment,
             OM.DisplayTitle AS OrgName,
			 OMS.AssigneeId
			FROM sEMeeting.tOnlineMeetingScope OMS
			INNER JOIN   sSetup.tDivisionMaster DM
				ON OMS.ScopeSourceId=DM.Id
			INNER JOIN 	sSetup.tClassMaster CM
				ON DM.ClassId=CM.Id	
			INNER JOIN sSetup.tDepartmentMaster DMast
				ON CM.DepartmentId =DMast.Id
			INNER JOIN sSetup.tOrganizationMaster OM
				ON DMast.OrgId=OM.Id
			LEFT JOIN sCommon.tAssigneeMaster AM 
				ON AM.Id = OMS.AssigneeId	
				WHERE OMS.OnlineMeetingId=@rId AND OMS.ScopeSourceTypeId=2005

			UNION ALL
			
			SELECT
			 OMS.OnlineMeetingId,
			 OMS.ScopeSourceTypeId,
			 OMS.ScopeSourceId,
			 [sStudent].[fStudentNameGet](SM.FirstName , SM.MiddleName, SM.LastName,OM.StudentNameFormatId ) AS Source,
			 'Student' AS SourceType,
			 SA.ROllNo,
             SA.Code,
             SA.ClassName+' - '+SA.DivisionName AS ClassDepartment,
             OM.DisplayTitle AS OrgName,
			 OMS.AssigneeId
			FROM sEMeeting.tOnlineMeetingScope OMS
		    INNER JOIN sStudent.tStudentMaster SM
				ON OMS.ScopeSourceId=SM.Id
			INNER JOIN  sStudent.vStudentAcademic SA
				ON OMS.ScopeSourceId=SA.StudentId AND SA.IsActiveBatch=1 AND SA.StatusId=1		
			INNER JOIN sSetup.tOrganizationMaster OM
				ON SM.OrgId = OM.Id
			LEFT JOIN sCommon.tAssigneeMaster AM 
				ON AM.Id = OMS.AssigneeId
			WHERE OMS.OnlineMeetingId=@rId AND OMS.ScopeSourceTypeId=2003
			
			UNION ALL
			
			SELECT
			 OMS.OnlineMeetingId,
			 OMS.ScopeSourceTypeId,
			 OMS.ScopeSourceId,
			 SA.Name AS Source,
			 'Student' AS SourceType,
			 SA.ROllNo,
             SA.Code,
             SA.ClassName+' - '+SA.DivisionName AS ClassDepartment,
             OM.DisplayTitle AS OrgName,
			 OMS.AssigneeId
			FROM sEMeeting.tOnlineMeetingScope OMS
		   	INNER JOIN  sStudent.vStudentAcademic SA
				ON OMS.ScopeSourceId=SA.AcademicId AND SA.IsActiveBatch=1 AND SA.StatusId=1
			INNER JOIN sSetup.tOrganizationMaster OM
				ON SA.OrgId = OM.Id
			LEFT JOIN sCommon.tAssigneeMaster AM 
				ON AM.Id = OMS.AssigneeId	
			WHERE OMS.OnlineMeetingId=@rId AND OMS.ScopeSourceTypeId=2001
			
			UNION ALL
			
			SELECT
			 OMS.OnlineMeetingId,
			 OMS.ScopeSourceTypeId,
			 OMS.ScopeSourceId,
			 vEC.FirstName +' '+ vEC.LastName AS Source,
			 'Employee' AS SourceType,
			 '' AS ROllNo,
             vEC.Code,
             vEC.DepartmentName AS ClassDepartment,
             OM.DisplayTitle AS OrgName,
			 OMS.AssigneeId
			FROM sEMeeting.tOnlineMeetingScope OMS
			INNER JOIN sHrm.vEmployeeCommunication vEC 
				ON OMS.ScopeSourceId=vEC.EmployeeId AND vEC.StatusId=7
			INNER JOIN sSetup.tOrganizationMaster OM
				ON vEC.OrgId = OM.Id
			LEFT JOIN sCommon.tAssigneeMaster AM 
				ON AM.Id = OMS.AssigneeId
			WHERE OMS.OnlineMeetingId=@rId AND OMS.ScopeSourceTypeId=95

		SELECT
		OnlineMeetingId, 
		ReferencePath 
		from sEMeeting.tOnlineMeetingReference OMR
		where OMR.OnlineMeetingId = @rId
			END
		
END
GO

--exec [sPurchase].[pGoodsReceivedNoteSearchGet] null,991100001,null,9911,'2023-01-01','2023-12-01',1,10,null
ALTER PROCEDURE [sPurchase].[pGoodsReceivedNoteSearchGet]

	@rNumber nvarchar(30) = '',
	@rLocationId int,
	@rName nvarchar = '',
	@rOrgGroupId int,
	@rStartDate DateTime = null,
	@rEndDate DateTime = null,
	@rPageNo int,
	@rPageSize int,
	@rStatusId smallint = null

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY


	    DECLARE @TotalRecords BIGINT;
			-- Get the total number of records
		SELECT @TotalRecords = COUNT(*) FROM sPurchase.tGoodsReceivedNote GRN(nolock)



		WHERE 
				((GRN.GRNNumber like @rNumber+'%') OR (ISNULL(@rNumber,'') = ''))
   				AND ((GRN.RequestedBy like @rName+'%') OR (ISNULL(@rName,'') = ''))
   				--AND ((SLM.Id = @rStoreLocationId) OR (@rStoreLocationId = 0))
   				AND ((GRN.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))
				AND ((GRN.StatusId = @rStatusId) OR (ISNULL(@rStatusId,0)=0))
				AND GRN.IsObsolete = 0
				AND ((GRN.CreatedDateTime >= @rStartDate) OR (ISNULL(@rStartDate,'')=''))
				AND((GRN.CreatedDateTime <= @rEndDate) OR (ISNULL(@rEndDate,'')=''))

	    Select t.* From (
			Select ROW_NUMBER() Over (
			Order by GRN.Id desc  
			) AS 'RowNum',GRN.Id,
				GRN.PurchaseRequestId,
				GRN.PurchaseOrderId,
				GRN.VendorId,
				GRN.GRNNumber as Number,
				GRN.RequestedBy, 
				GRN.LocationId, 
				GRN.PODueDate,
				ISNULL(SM.Title,'') as Status,
				VM.Code AS VendorCode,
			    VM.Title AS VendorName,
				PR.Number as PurchaseNumber,
				PO.RefNumber as PurchaseOrderNumber

	    FROM		
				sPurchase.tGoodsReceivedNote GRN (nolock)
				Left Join [sWf].[tWfStatusMaster] SM on GRN.StatusId = SM.Id
				Left JOIN sProcurement.tVendorMaster  VM on GRN.VendorId = VM.ID
				Left Join [sPurchase].[tPurchaseRequest] PR on PR.Id = GRN.PurchaseRequestId
				Left Join [sPurchase].[tPurchaseOrder] PO on PO.Id = GRN.PurchaseOrderId
		WHERE 
				((GRN.GRNNumber like @rNumber+'%') OR (ISNULL(@rNumber,'') = ''))
   				AND ((GRN.RequestedBy like @rName+'%') OR (ISNULL(@rName,'') = ''))
   				--AND ((SLM.Id = @rStoreLocationId) OR (@rStoreLocationId = 0))
   				AND ((GRN.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))
				AND ((GRN.StatusId = @rStatusId) OR (ISNULL(@rStatusId,0)=0))
				AND GRN.IsObsolete = 0
				AND ((GRN.CreatedDateTime >= @rStartDate) OR (ISNULL(@rStartDate,'')=''))
				AND((GRN.CreatedDateTime <= @rEndDate) OR (ISNULL(@rEndDate,'')=''))
			)t Where t.RowNum Between
		((@rPageNo-1)*@rPageSize +1) AND (@rPageNo*@rpageSize)

		SELECT @TotalRecords as TotalRecords

	 END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH  		  
END
GO
PRINT N'Altering Procedure [sPurchase].[pInvoiceGenerateSearchGet]...';


GO

--exec [sPurchase].[pInvoiceGenerateSearchGet] '',991100001,'',9911,'2023-01-01','2023-12-01',1,10,null
ALTER PROCEDURE [sPurchase].[pInvoiceGenerateSearchGet]

	@rNumber nvarchar(30) = '',
	@rLocationId int,
	@rName nvarchar = '',
	@rOrgGroupId int,
	@rStartDate DateTime = null,
	@rEndDate DateTime = null,
	@rPageNo int,
	@rPageSize int,
	@rStatusId smallint = 0

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY

		DECLARE @TotalRecords BIGINT;
		-- Get the total number of records
		SELECT @TotalRecords = COUNT(*) FROM sPurchase.tInvoiceGenerate IG (nolock)

		WHERE 
				((IG.INVNumber like @rNumber+'%') OR (ISNULL(@rNumber,'') = ''))
   				AND ((IG.RequestedBy like @rName+'%') OR (ISNULL(@rName,'') = ''))
   				--AND ((SLM.Id = @rStoreLocationId) OR (@rStoreLocationId = 0))
   				AND ((IG.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))
				AND ((IG.StatusId = @rStatusId) OR (ISNULL(@rStatusId,0)=0))
				AND ((IG.CreatedDateTime >= @rStartDate) OR (ISNULL(@rStartDate,'')=''))
				AND((IG.CreatedDateTime <= @rEndDate) OR (ISNULL(@rEndDate,'')=''))
				AND IG.IsObsolete = 0


	    
        Select t.* From (
			Select ROW_NUMBER() Over (
			Order by IG.Id desc  
			) AS 'RowNum',
			    IG.Id,
				IG.INVNumber as Number,
				IG.RequestedBy, 
				IG.LocationId,
				ISNULL(SM.Title,'') as Status

        FROM		
				sPurchase.tInvoiceGenerate IG (nolock)
				Left Join [sWf].[tWfStatusMaster] SM on IG.StatusId = SM.Id



        WHERE 
				((IG.INVNumber like @rNumber+'%') OR (ISNULL(@rNumber,'') = ''))
   				AND ((IG.RequestedBy like @rName+'%') OR (ISNULL(@rName,'') = ''))
   				--AND ((SLM.Id = @rStoreLocationId) OR (@rStoreLocationId = 0))
   				AND ((IG.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))
				AND ((IG.StatusId = @rStatusId) OR (ISNULL(@rStatusId,0)=0))
				AND ((IG.CreatedDateTime >= @rStartDate) OR (ISNULL(@rStartDate,'')=''))
				AND((IG.CreatedDateTime <= @rEndDate) OR (ISNULL(@rEndDate,'')=''))
				AND IG.IsObsolete = 0
				)t Where t.RowNum Between
		        ((@rPageNo-1)*@rPageSize +1) AND (@rPageNo*@rpageSize)

        SELECT @TotalRecords as TotalRecords
	 END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH  		  
END
GO
PRINT N'Altering Procedure [sPurchase].[pPurchaseOrderApprovedGet]...';


GO


ALTER PROCEDURE [sPurchase].[pPurchaseOrderApprovedGet]
	  @rId int
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
		select PO.Id as PurchaseOrderId,
			   PO.RefNumber as PurchaseOrderNumber,
			   PO.PurchaseRequestId,
			   PO.RequestedBy,
			   PO.StatusId,
			   PO.VendorId,
			   PO.LocationId,
			   PO.OrgGroupId,
			   PO.PODate as PODueDate,
			   PO.BillToAddress,
			   PO.ShipToAddress,
			   PO.RequiredBy,
			   ISNULL(SM.Title,'') as Status,
			   VM.Code AS VendorCode,
			   VM.Title AS VendorName,
			   PR.Number As PurchaseRequestNumber 
		from   [sPurchase].[tPurchaseOrder] PO(nolock) 
			   Left Join [sPurchase].[tPurchaseRequest] PR ON PR.ID = PO.PurchaseRequestId
			   Left JOIN sProcurement.tVendorMaster  VM on PO.VendorId = VM.ID 
			   Left Join [sWf].[tWfStatusMaster] SM ON SM.Id = PO.StatusId
		where  PO.Id=@rId AND PO.IsObsolete = 0 AND PO.StatusId = 3

		Declare @PreviousGrnQty int,@UnitPrice int
		Select @PreviousGrnQty = Sum(GRND.ReceivedQty)
		from [sPurchase].[tGoodsReceivedNote] GRN 
		Left Join sPurchase.tGoodsReceivedNoteDetail GRND ON GRND.GoodsReceivedNoteId = GRN.Id
		where GRN.PurchaseOrderId = @rId

		select POD.Id as PurchaseOrderDetailId,
			   POD.PurchaseOrderId,
			   POD.OrgGroupId,
			   POD.PurchaseRequestDetailId,
			   POD.StockItemId,
			   POD.TotalQty as POQty,
			   POD.Remark, 
			   SUMa.Id AS UnitTypeId,
			   SIM.Code				AS StockItemCode,
			   SIM.Title				AS StockItemName,
			   SUMa.Code				AS UOM,
			   SCM.Id as CategoryId,
			   SCM.Title as CategoryName,
			   0 as SubCategoryId,
			   Cast((POD.TotalQty - @PreviousGrnQty) as decimal) as ReceivedQty ,
			   Cast(@PreviousGrnQty as decimal) as PreviousGRNQty,
			   QRD.TargetPrice as UnitPrice
		from   [sPurchase].[tPurchaseOrderDetail] POD (nolock)
			   Inner JOIN	sPurchase.tPurchaseOrder PO	ON PO.Id = POD.PurchaseOrderId AND PO.IsObsolete = 0 AND PO.StatusId = 3
			   Inner JOIN	sIp.tStockItemMaster SIM	ON SIM.Id = POD.StockItemId 
			   Inner JOIN  sFinanceCommon.tStockUomMaster SUMa ON SUMa.Id = SIM.IssueStockUomId
			   LEFT JOIN [sIp].[tStockItemCategoryMaster] SCM ON SCM.Id = SIM.StockItemCategoryId
			   Left Join [sPurchase].[tPurchaseRequest] PR ON PR.Id = PO.PurchaseRequestId AND PR.StatusId = 3
			   Left Join [sPurchase].[tQuotationRequest] QR ON QR.PurchaseRequestId = PR.Id
			   Left Join [sPurchase].[tQuotationRequestDetail] QRD ON QRD.PurchaseRequestDetailId = POD.PurchaseRequestDetailId
			   And QRD.StockItemId = POD.StockItemId AND QRD.QuotationRequestId = QR.Id
		where  POD.PurchaseOrderId=@rId     

	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH

END
GO
PRINT N'Altering Procedure [sPurchase].[pPurchaseOrderApprovedSearchGet]...';


GO


--exec [sPurchase].[pPurchaseOrderApprovedSearchGet] '',991100001,'',9911,'2023-01-01','2023-12-01',1,10,null
ALTER PROCEDURE [sPurchase].[pPurchaseOrderApprovedSearchGet]

	@rNumber nvarchar(30) = '',
	@rLocationId int,
	@rName nvarchar = '',
	@rOrgGroupId int,
	@rStartDate DateTime = null,
	@rEndDate DateTime = null,
	@rPageNo int,
	@rPageSize int,
	@rStatusId smallint = 0

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY


		DECLARE @TotalRecords BIGINT;
		-- Get the total number of records
		SELECT @TotalRecords = COUNT(*) FROM sPurchase.tPurchaseOrder PO (nolock)

		WHERE 
				((PO.RefNumber like @rNumber+'%') OR (ISNULL(@rNumber,'') = ''))
   				AND ((PO.RequestedBy like @rName+'%') OR (ISNULL(@rName,'') = ''))
   				--AND ((SLM.Id = @rStoreLocationId) OR (@rStoreLocationId = 0))
   				AND ((PO.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))
				AND ((PO.StatusId = @rStatusId) OR (ISNULL(@rStatusId,0)=0))
				AND PO.IsObsolete = 0 and PO.StatusId = 3
				AND ((PO.CreatedDateTime >= @rStartDate) OR (ISNULL(@rStartDate,'')=''))
				AND((PO.CreatedDateTime <= @rEndDate) OR (ISNULL(@rEndDate,'')=''))


	    Select t.* From (
			Select ROW_NUMBER() Over (
			Order by PO.Id desc  
			) AS 'RowNum',
				PO.Id, 
				PO.RefNumber as Number,
				PO.RequestedBy, 
				PO.LocationId, 
				PO.PODate,
				ISNULL(SM.Title,'') as Status
		FROM		
				sPurchase.tPurchaseOrder PO (nolock)
				Left Join [sWf].[tWfStatusMaster] SM on PO.StatusId = SM.Id
		WHERE 
				((PO.RefNumber like @rNumber+'%') OR (ISNULL(@rNumber,'') = ''))
   				AND ((PO.RequestedBy like @rName+'%') OR (ISNULL(@rName,'') = ''))
   				--AND ((SLM.Id = @rStoreLocationId) OR (@rStoreLocationId = 0))
   				AND ((PO.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))
				AND ((PO.StatusId = @rStatusId) OR (ISNULL(@rStatusId,0)=0))
				AND PO.IsObsolete = 0 and PO.StatusId = 3
				AND ((PO.CreatedDateTime >= @rStartDate) OR (ISNULL(@rStartDate,'')=''))
				AND((PO.CreatedDateTime <= @rEndDate) OR (ISNULL(@rEndDate,'')=''))
			 )t Where t.RowNum Between
		        ((@rPageNo-1)*@rPageSize +1) AND (@rPageNo*@rpageSize)

        SELECT @TotalRecords as TotalRecords

	 
	 END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH  		  
END
GO
PRINT N'Altering Procedure [sPurchase].[pPurchaseOrderGet]...';


GO


ALTER PROCEDURE [sPurchase].[pPurchaseOrderGet]
	  @rId int
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
		select PO.Id,
			   PO.RefNumber,
			   PO.PurchaseRequestId,
			   PR.Number as PurchaseRequestNumber,
			   PO.RequestedBy,
			   PO.StatusId,
			   PO.VendorId,
			   PO.LocationId,
			   PO.OrgGroupId,
			   PO.PODate,
			   PO.BillToAddress,
			   PO.ShipToAddress,
			   PO.RequiredBy,
			   ISNULL(SM.Title,'') as Status,
			   VM.Code AS VendorCode,
			   VM.Title AS VendorName 
		from   [sPurchase].[tPurchaseOrder] PO(nolock) 
			   Left JOIN sProcurement.tVendorMaster  VM on PO.VendorId = VM.ID
			   Left Join [sWf].[tWfStatusMaster] SM ON SM.Id = PO.StatusId
			   Left Join [sPurchase].[tPurchaseRequest] PR on PR.Id = PO.PurchaseRequestId
		where  PO.Id = @rId AND PO.IsObsolete = 0

		select POD.Id,
			   POD.PurchaseOrderId,
			   POD.PurchaseRequestDetailId,
			   POD.OrgGroupId,
			   POD.StockItemId,
			   POD.TotalQty,
			   POD.Remark, 
			   SIM.Code	AS StockItemCode,
			   SIM.Title AS StockItemName,
			   SCM.Title AS CategoryName,
			   SUMa.Id AS UnitTypeId,
			   SUMa.Code AS UOM
		from   [sPurchase].[tPurchaseOrderDetail] POD (nolock)
			   Inner JOIN	sPurchase.tPurchaseOrder PO	ON PO.Id = POD.PurchaseOrderId AND PO.IsObsolete = 0
			   Inner JOIN	sIp.tStockItemMaster SIM	ON SIM.Id = POD.StockItemId 
			   LEFT JOIN [sIp].[tStockItemCategoryMaster] SCM ON SCM.Id = SIM.StockItemCategoryId
			   Inner JOIN  sFinanceCommon.tStockUomMaster SUMa ON SUMa.Id = SIM.IssueStockUomId
		where  POD.PurchaseOrderId = @rId

		
	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH

END
GO
PRINT N'Altering Procedure [sPurchase].[pPurchaseOrderSearchGet]...';


GO



ALTER PROCEDURE [sPurchase].[pPurchaseOrderSearchGet]

	@rNumber nvarchar(30) = '',
	@rLocationId int,
	@rName nvarchar = '',
	@rOrgGroupId int,
	@rStartDate DateTime = null,
	@rEndDate DateTime = null,
	@rPageNo int,
	@rPageSize int,
	@rStatusId smallint = null

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY


		If @rNumber = NULL SET @rNumber=''
		If @rName = NULL SET @rName=''
		If @rStartDate = '' SET @rStartDate=NULL
		If @rEndDate = '' SET @rEndDate=NULL
		If @rStatusId is NULL SET @rStatusId=0

		DECLARE @TotalRecords BIGINT;
		-- Get the total number of records
		SELECT @TotalRecords = COUNT(*) FROM sPurchase.tPurchaseOrder PO (nolock)

		WHERE 
				((PO.RefNumber like @rNumber+'%') OR (@rNumber = ''))
   				AND ((PO.RequestedBy like @rName+'%') OR (@rName = ''))
   				--AND ((SLM.Id = @rStoreLocationId) OR (@rStoreLocationId = 0))
   				AND ((PO.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))
				AND ((PO.StatusId = @rStatusId) OR (@rStatusId=0))
				AND PO.IsObsolete = 0
				AND ((PO.CreatedDateTime >= @rStartDate) OR (ISNULL(@rStartDate,'')=''))
				AND((PO.CreatedDateTime <= @rEndDate) OR (ISNULL(@rEndDate,'')=''))

	    Select t.* From (
			Select ROW_NUMBER() Over (
			Order by PO.Id desc  
			) AS 'RowNum',PO.Id,
				PO.PurchaseRequestId,
				PO.RefNumber as Number,
				PO.RequestedBy, 
				PO.LocationId, 
				PO.PODate,
				ISNULL(SM.Title,'') as Status
				
		 FROM		
				sPurchase.tPurchaseOrder PO (nolock)
				Left Join [sWf].[tWfStatusMaster] SM on PO.StatusId = SM.Id

         
		 WHERE 
				((PO.RefNumber like @rNumber+'%') OR (@rNumber = ''))
   				AND ((PO.RequestedBy like @rName+'%') OR (@rName = ''))
   				--AND ((SLM.Id = @rStoreLocationId) OR (@rStoreLocationId = 0))
   				AND ((PO.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))
				AND ((PO.StatusId = @rStatusId) OR (@rStatusId=0))
				AND PO.IsObsolete = 0
				AND ((PO.CreatedDateTime >= @rStartDate) OR (ISNULL(@rStartDate,'')=''))
				AND((PO.CreatedDateTime <= @rEndDate) OR (ISNULL(@rEndDate,'')=''))
				)t Where t.RowNum Between
		        ((@rPageNo-1)*@rPageSize +1) AND (@rPageNo*@rpageSize)

        SELECT @TotalRecords as TotalRecords
	 END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH  		  
END
GO
PRINT N'Altering Procedure [sPurchase].[pPurchaseRequestApprovedGet]...';


GO

--exec [sPurchase].[pPurchaseRequestApprovedGet] 991100002
ALTER PROCEDURE [sPurchase].[pPurchaseRequestApprovedGet]
	  @rId int
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
		select PR.Id,
			   PR.Number,
			   PR.RequestedBy,
			   PR.StatusId,
			   QR.VendorId,
			   PR.LocationId,
			   PR.BranchId,
			   PR.OrgGroupId,
			   PR.ResourceId,
			   PR.RequestDate,
			   PR.RequiredBy,
			   ISNULL(SM.Title,'') AS Status,
			   VM.Code AS VendorCode,
			   VM.Title AS VendorName 
		from   [sPurchase].[tPurchaseRequest] PR (nolock)
			   Inner Join [sPurchase].[tQuotationRequest] QR on QR.PurchaseRequestId = PR.Id
			   Left Join [sWf].[tWfStatusMaster] SM ON SM.Id = PR.StatusId
			   Left Join sProcurement.tVendorMaster  VM ON PR.VendorId = VM.ID
		where  PR.Id=@rId AND PR.IsObsolete = 0 AND PR.StatusId = 3 AND QR.StatusId = 3

		select PRD.Id,
			   PRD.PurchaseRequestId,
			   PRD.OrgGroupId,
			   PRD.StockItemId,
			   PRD.RequestedQty,
			   PRD.Remark, 
			   SIM.Code	AS StockItemCode,
			   SCM.Title AS CategoryName,
			   SIM.Title AS StockItemName,
			   SUMa.Code AS UOM
		from   [sPurchase].[tPurchaseRequestDetail] PRD (nolock)
			   Inner JOIN	sPurchase.tPurchaseRequest PR	ON PR.Id = PRD.PurchaseRequestId AND PR.IsObsolete = 0 AND PR.StatusId = 3
			   Inner JOIN	sIp.tStockItemMaster SIM	ON SIM.Id = PRD.StockItemId 
			   LEFT JOIN [sIp].[tStockItemCategoryMaster] SCM ON SCM.Id = SIM.StockItemCategoryId
			   Inner JOIN  sFinanceCommon.tStockUomMaster SUMa ON SUMa.Id = SIM.IssueStockUomId
		where  PurchaseRequestId=@rId
	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH

END
GO
PRINT N'Altering Procedure [sPurchase].[pPurchaseRequestApprovedSearchGet]...';


GO

--exec [sPurchase].[pPurchaseRequestApprovedSearchGet] '',991100001,'',9911,'2023-01-01','2023-12-01',1,10,null
ALTER PROCEDURE [sPurchase].[pPurchaseRequestApprovedSearchGet]

	@rNumber nvarchar(30) = '',
	@rLocationId int,
	@rName nvarchar = '',
	@rOrgGroupId int,
	@rStartDate DateTime = null,
	@rEndDate DateTime = null,
	@rPageNo int,
	@rPageSize int,
	@rStatusId smallint = 0

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY

		DECLARE @TotalRecords BIGINT;
		-- Get the total number of records
		SELECT @TotalRecords = COUNT(*) FROM sPurchase.tPurchaseRequest PR (nolock)
		Inner Join [sPurchase].[tQuotationRequest] QR on QR.PurchaseRequestId = PR.Id

		WHERE 
				((PR.Number like @rNumber+'%') OR (ISNULL(@rNumber,'') = ''))
   				AND ((PR.RequestedBy like @rName+'%') OR (ISNULL(@rName,'') = ''))
   				--AND ((SLM.Id = @rStoreLocationId) OR (@rStoreLocationId = 0))
   				AND ((PR.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))
				AND ((PR.StatusId = @rStatusId) OR (ISNULL(@rStatusId,0)=0))
				AND PR.IsObsolete = 0 and PR.StatusId = 3 and QR.StatusId = 3
				AND ((PR.CreatedDateTime >= @rStartDate) OR (ISNULL(@rStartDate,'')=''))
				AND((PR.CreatedDateTime <= @rEndDate) OR (ISNULL(@rEndDate,'')=''))


		Select t.* From (
			Select ROW_NUMBER() Over (
			Order by PR.Id desc  
			) AS 'RowNum',PR.Id, 
				PR.Number,
				PR.RequestedBy, 
				PR.LocationId, 
				PR.RequestDate,
				ISNULL(SM.Title,'') as Status

	    FROM		
				sPurchase.tPurchaseRequest PR (nolock)
				Inner Join [sPurchase].[tQuotationRequest] QR on QR.PurchaseRequestId = PR.Id
				Left Join [sWf].[tWfStatusMaster] SM on PR.StatusId = SM.Id


        WHERE 
				((PR.Number like @rNumber+'%') OR (ISNULL(@rNumber,'') = ''))
   				AND ((PR.RequestedBy like @rName+'%') OR (ISNULL(@rName,'') = ''))
   				--AND ((SLM.Id = @rStoreLocationId) OR (@rStoreLocationId = 0))
   				AND ((PR.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))
				AND ((PR.StatusId = @rStatusId) OR (ISNULL(@rStatusId,0)=0))
				AND PR.IsObsolete = 0 and PR.StatusId = 3 and QR.StatusId = 3
				AND ((PR.CreatedDateTime >= @rStartDate) OR (ISNULL(@rStartDate,'')=''))
				AND((PR.CreatedDateTime <= @rEndDate) OR (ISNULL(@rEndDate,'')='')))t Where t.RowNum Between
		        ((@rPageNo-1)*@rPageSize +1) AND (@rPageNo*@rpageSize)

        SELECT @TotalRecords as TotalRecords
	 END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH  		  
END
GO
PRINT N'Altering Procedure [sPurchase].[pPurchaseRequestGet]...';


GO


ALTER PROCEDURE [sPurchase].[pPurchaseRequestGet]
	  @rId bigint
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
		select  PR.Id,
				PR.Number,
				PR.RequestedBy,
				PR.StatusId,
				PR.VendorId,
				PR.LocationId,
				PR.BranchId,
				PR.OrgGroupId,
				PR.ResourceId,
				PR.RequestDate,
				PR.RequiredBy,
				ISNULL(SM.Title,'') as Status,
				VM.Code AS VendorCode,
				VM.Title AS VendorName
		from    [sPurchase].[tPurchaseRequest] PR (nolock) 
			    Left Join sProcurement.tVendorMaster  VM on PR.VendorId = VM.ID
				Left Join [sWf].[tWfStatusMaster] SM ON SM.Id = PR.StatusId
		where   PR.Id=@rId AND PR.IsObsolete = 0

		select PRD.Id,
			   PRD.PurchaseRequestId,
			   PRD.OrgGroupId,
			   PRD.StockItemId,
			   PRD.RequestedQty,
			   PRD.Remark, 
			   SIM.Code	AS StockItemCode,
			   SCM.Title AS CategoryName,
			   SIM.Title AS StockItemName,
			   SUMa.Code AS UOM
		from   [sPurchase].[tPurchaseRequestDetail] PRD (nolock)
			   Inner JOIN	sPurchase.tPurchaseRequest PR	ON PR.Id = PRD.PurchaseRequestId AND PR.IsObsolete = 0
			   Inner JOIN	sIp.tStockItemMaster SIM	ON SIM.Id = PRD.StockItemId 
			   LEFT JOIN [sIp].[tStockItemCategoryMaster] SCM ON SCM.Id = SIM.StockItemCategoryId
			   Inner JOIN  sFinanceCommon.tStockUomMaster SUMa ON SUMa.Id = SIM.IssueStockUomId
		where  PurchaseRequestId=@rId

	
	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH

END
GO
PRINT N'Altering Procedure [sPurchase].[pPurchaseRequestSearchGet]...';


GO

--exec [sPurchase].[pPurchaseRequestSearchGet] null,991100001,null,9911,null,null,1,10,null
ALTER PROCEDURE [sPurchase].[pPurchaseRequestSearchGet]

	@rNumber nvarchar(30) = '',
	@rLocationId int,
	@rName nvarchar  = '',
	@rOrgGroupId int,
	@rStartDate DateTime = null,
	@rEndDate DateTime = null,
	@rPageNo int,
	@rPageSize int,
	@rStatusId smallint = 0

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY


		If @rNumber = NULL SET @rNumber=''
		If @rName = NULL SET @rName=''
		If @rStartDate = '' SET @rStartDate=NULL
		If @rEndDate = '' SET @rEndDate=NULL
		If @rStatusId is NULL SET @rStatusId=0

		DECLARE @TotalRecords BIGINT;
		-- Get the total number of records
		SELECT @TotalRecords = COUNT(*) FROM sPurchase.tPurchaseRequest PR (nolock)

		WHERE 
				((PR.Number like @rNumber+'%') OR (ISNULL(@rNumber,'') = ''))
   				AND ((PR.RequestedBy like @rName+'%') OR (ISNULL(@rName,'') = ''))
   				--AND ((SLM.Id = @rStoreLocationId) OR (@rStoreLocationId = 0))
   				AND ((PR.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))
   				AND ((PR.StatusId = @rStatusId) OR (ISNULL(@rStatusId,0)=0))
				AND PR.IsObsolete = 0
				AND ((PR.CreatedDateTime >= @rStartDate) OR (ISNULL(@rStartDate,'')=''))
				AND((PR.CreatedDateTime <= @rEndDate) OR (ISNULL(@rEndDate,'')=''))
			

	    Select t.* From (
			Select ROW_NUMBER() Over (
			Order by PR.Id desc  
			) AS 'RowNum',PR.Id, 
				PR.Number,
				PR.RequestedBy, 
				PR.LocationId, 
				PR.RequestDate,
				ISNULL(SM.Title,'') as Status

        FROM		
				sPurchase.tPurchaseRequest PR (nolock)
				Left Join [sWf].[tWfStatusMaster] SM on PR.StatusId = SM.Id

        WHERE 
				((PR.Number like @rNumber+'%') OR (ISNULL(@rNumber,'') = ''))
   				AND ((PR.RequestedBy like @rName+'%') OR (ISNULL(@rName,'') = ''))
   				--AND ((SLM.Id = @rStoreLocationId) OR (@rStoreLocationId = 0))
   				AND ((PR.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))
   				AND ((PR.StatusId = @rStatusId) OR (ISNULL(@rStatusId,0)=0))
				AND PR.IsObsolete = 0
				AND ((PR.CreatedDateTime >= @rStartDate) OR (ISNULL(@rStartDate,'')=''))
				AND((PR.CreatedDateTime <= @rEndDate) OR (ISNULL(@rEndDate,'')=''))
					)t Where t.RowNum Between
		        ((@rPageNo-1)*@rPageSize +1) AND (@rPageNo*@rpageSize)

        SELECT @TotalRecords as TotalRecords
	 END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH  		  
END
GO
PRINT N'Altering Procedure [sPurchase].[pQualityCheckSearchGet]...';


GO

--exec [sPurchase].[pQualityCheckSearchGet] null,991100001,null,9911,null,null,1,10,null
ALTER PROCEDURE [sPurchase].[pQualityCheckSearchGet]

	@rNumber nvarchar(30) = '',
	@rLocationId int,
	@rName nvarchar = '',
	@rOrgGroupId int,
	@rStartDate DateTime = null,
	@rEndDate DateTime = null,
	@rPageNo int,
	@rPageSize int,
	@rStatusId smallint = 0

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY


		If @rNumber = NULL SET @rNumber=''
		If @rName = NULL SET @rName=''
		If @rStartDate = '' SET @rStartDate=NULL
		If @rEndDate = '' SET @rEndDate=NULL
		If @rStatusId is NULL SET @rStatusId=0

		DECLARE @TotalRecords BIGINT;
		-- Get the total number of records
		SELECT @TotalRecords = COUNT(*) FROM sPurchase.tQualityCheck QC (nolock)

		WHERE 
				((QC.QCNumber like @rNumber+'%') OR (@rNumber = ''))
   				AND ((QC.RequestedBy like @rName+'%') OR (@rName = ''))
   				--AND ((SLM.Id = @rStoreLocationId) OR (@rStoreLocationId = 0))
   				AND ((QC.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))
				AND ((QC.StatusId = @rStatusId) OR (@rStatusId=0))
				AND QC.IsObsolete = 0
				AND ((QC.CreatedDateTime >= @rStartDate) OR (ISNULL(@rStartDate,'')=''))
				AND((QC.CreatedDateTime <= @rEndDate) OR (ISNULL(@rEndDate,'')=''))

		Select t.* From (
			Select ROW_NUMBER() Over (
			Order by QC.Id desc  
			) AS 'RowNum',QC.Id,
				QC.QCNumber as Number,
				QC.RequestedBy, 
				QC.LocationId,
				ISNULL(SM.Title,'') as Status

        FROM		
				sPurchase.tQualityCheck QC (nolock)
				Left Join [sWf].[tWfStatusMaster] SM on QC.StatusId = SM.Id


	    WHERE 
				((QC.QCNumber like @rNumber+'%') OR (@rNumber = ''))
   				AND ((QC.RequestedBy like @rName+'%') OR (@rName = ''))
   				--AND ((SLM.Id = @rStoreLocationId) OR (@rStoreLocationId = 0))
   				AND ((QC.OrgGroupId = @rOrgGroupId) OR (@rOrgGroupId=0))
				AND ((QC.StatusId = @rStatusId) OR (@rStatusId=0))
				AND QC.IsObsolete = 0
				AND ((QC.CreatedDateTime >= @rStartDate) OR (ISNULL(@rStartDate,'')=''))
				AND((QC.CreatedDateTime <= @rEndDate) OR (ISNULL(@rEndDate,'')=''))
					)t Where t.RowNum Between
		        ((@rPageNo-1)*@rPageSize +1) AND (@rPageNo*@rpageSize)

        SELECT @TotalRecords as TotalRecords
	 END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH  		  
END
GO
PRINT N'Altering Procedure [sSecurity].[pAppPermissionGet]...';

GO

CREATE PROCEDURE sEMeeting.GetStudentProfile 
	@racademicId bigint,
	@rbatchId int
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	SELECT A.OrgId, BatchId, StudentId, A.Id AS AcademicId, SM.Code, A.RollNo, (FirstName +' '+MiddleName+' '+LastName) AS StudentName,
                            SM.FirstName, SM.MiddleName, SM.LastName, A.ClassId, A.DivisionId, (CM.DisplayTitle +'-'+ DM.DisplayTitle) AS ClassDivision,
                            DPM.Id AS DepartmentId, DPM.DisplayTitle AS DepartmentName, SM.SUID, B.Title as BatchName
                             FROM sStudent.tAcademic A INNER JOIN sStudent.tStudentMaster SM ON A.StudentId=SM.Id
                            INNER JOIN sSetup.tClassMaster CM ON CM.Id=A.ClassId INNER JOIN sSetup.tDivisionMaster DM ON DM.Id = A.DivisionId
                            INNER JOIN sSetup.tDepartmentMaster DPM ON DPM.Id = CM.DepartmentId
                            INNER JOIN sSetup.tBatchMaster B ON b.Id = A.BatchId
							WHERE A.Id=@racademicId AND BatchId = @rbatchId



END
GO
PRINT N'Creating Procedure [sEMeeting].[pAttachmentLogAdd]...';


GO

CREATE PROCEDURE [sEMeeting].[pAttachmentLogAdd]
		@rMessageId nvarchar(max),
		@rAttachment nvarchar(max)
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY	
			INSERT INTO sAlert.tAlertAttachmentLog VALUES (@rMessageId, @rAttachment)
		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
	END CATCH
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pBatchLockGet]...';


GO

CREATE PROC [sEMeeting].[pBatchLockGet]
	@rBatchId int

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	BEGIN TRY

	Select ISNULL(IsLock,0) as IsLock FROM sSetup.tBatchMaster WHERE Id = @rBatchId

	END TRY

	BEGIN CATCH
	SELECT -1 as Result
	END CATCH

END
GO
PRINT N'Creating Procedure [sEMeeting].[pbatchmasterGet]...';


GO

CREATE PROCEDURE [sEMeeting].[pbatchmasterGet]
	@rOrgId smallint,
	@rOrgGroupBatchId int

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY
			
			SELECT Id as BatchId FROM [sSetup].[tBatchMaster] WHERE OrgId = @rOrgId AND OrgGroupBatchId = @rOrgGroupBatchId ORDER BY OrderIndex DESC

		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pCancelMeeting]...';


GO

CREATE PROCEDURE [sEMeeting].[pCancelMeeting]
	@rId bigint

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY
			
			IF EXISTS (SELECT 1 FROM sEMeeting.tOnlineMeeting WHERE IsCompleted = 550 AND Id = @rId)
			BEGIN
				SELECT -1 as Result
			END
			IF EXISTS (SELECT 1 FROM sEMeeting.tOnlineMeeting WHERE StartDate >= GetUtcDate() AND Id = @rId)
			BEGIN
				SELECT -102 as Result
			END
			ELSE
			BEGIN
				Update sEMeeting.tOnlineMeeting SET IsCompleted = 551 WHERE StartDate < GetUtcDate() AND IsCompleted = 0 AND Id =@rId
				SELECT 1 as Result
			END
		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -101 AS Result
	END CATCH
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pCreateSMSStatus]...';


GO

CREATE PROCEDURE [sEMeeting].[pCreateSMSStatus]
		@rResultMessage nvarchar(max)
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY
			Insert Into sAlert.tSMSStatus VALUES (CONVERT(DateTime,GetUtcDate(),103), @rResultMessage)
		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pEmployeeMasterIdGet]...';


GO

CREATE PROCEDURE [sEMeeting].[pEmployeeMasterIdGet]
		@rMobileNotificationCode nvarchar(max),
		@rOrgId int
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY
			SELECT Id FROM sHrm.tEmployeeMaster WHERE Code = @rMobileNotificationCode AND OrgId= @rOrgId AND  ISNULL(IsOtherUser,0) = 0 
		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pEventSMSTemplateIdGet]...';


GO

CREATE PROCEDURE [sEMeeting].[pEventSMSTemplateIdGet]
		@rOrgId int,
		@rEventId int
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY
			Select SMSTemplateID FROM sAlert.tEventTemplate WHERE EventId = @rOrgId AND OrgId=@rOrgId AND AlertModeId = 2
		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pEventTemplateGet]...';


GO

CREATE PROCEDURE [sEMeeting].[pEventTemplateGet]
		@rOrgId int
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY
			SELECT EventId,Template FROM sAlert.tEventTemplate WHERE IsAutoSend = 1 AND OrgId =@rOrgId AND EventId = 93 AND AlertModeId in (3)
		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pMeetingAccountNameGet]...';


GO

CREATE PROCEDURE [sEMeeting].[pMeetingAccountNameGet]
		@rEmployeeId bigint
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
	BEGIN TRY
	SELECT OMA.Id,
	OMA.AccountName as Title
	FROM sAcademic.tOnlineMeetingAccount OMA 
	WHERE EmployeeId = @rEmployeeId 
	AND ISNULL(OMA.IsObsolete,0) = 0
	END TRY
	BEGIN CATCH 
		SELECT -1 AS Result
	END CATCH	
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pMeetingTypeMasterDelete]...';


GO
 
CREATE PROCEDURE [sEMeeting].[pMeetingTypeMasterDelete]
	 @rId smallint

	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 
BEGIN TRY

	IF EXISTS (SELECT 1 from sEMeeting.tMeetingTypeMaster where AutoId = @rId AND IsObsolete = 0)
	BEGIN 
	Update sEMeeting.tMeetingTypeMaster
	SET 
		IsObsolete = 1
	WHERE AutoId = @rId
	SELECT 1 As Result
	END
END TRY
BEGIN CATCH 
		SELECT -1 AS Result
END CATCH
END
GO
PRINT N'Creating Procedure [sEMeeting].[pMeetingTypeMasterDetailGet]...';


GO
 
CREATE PROCEDURE [sEMeeting].[pMeetingTypeMasterDetailGet]
	 @rId smallint = 0

	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 
BEGIN TRY

	If(@rId = 0)
	BEGIN 
		SELECT AutoId, Title, Code, OrderIndex, Description
			from [sEMeeting].[tMeetingTypeMaster] where IsObsolete = 0
	END
	ELSE
	BEGIN
		SELECT AutoId, Title, Code, OrderIndex, Description
			from [sEMeeting].[tMeetingTypeMaster] where AutoId = @rId AND IsObsolete = 0
	END
END TRY
BEGIN CATCH 
		SELECT -1 AS Result
END CATCH
END
GO
PRINT N'Creating Procedure [sEMeeting].[pMenuGet]...';


GO

CREATE PROCEDURE sEMeeting.pMenuGet
 @rUserId [bigint],    
 @rMenuId int = 0,    
 @rResourceTypeId int= 0,    
 @rIsCoreMenu bit = 0     
      
WITH EXECUTE AS CALLER    
AS    
SET NOCOUNT ON    
    
BEGIN    
 IF(@rIsCoreMenu=0)    
 BEGIN    
 IF EXISTS(SELECT 1 FROM sSecurity.tPermissionUser WHERE UserId = @rUserId)    
  BEGIN    
   SELECT    
    MenuID,    
    ModuleId,    
    ParentMenuID,    
    MenuTitle,      
    ScreenTitle,       
    PageURL,    
    ScreenId,    
    Permission,    
    OrderNumber,      
    TaskId,      
    PageURL  AS PagePath,    
    IconPath    
        
   FROM    
     (SELECT Menu.Id AS MenuID,     
    ISNULL(Menu.ParentMenuID, 0) AS ParentMenuID,     
    Menu.Title AS MenuTitle,       
    ISNULL(Screen.Title, '') AS ScreenTitle,        
    ISNULL(Screen.Page, '') AS PageURL,    
    ISNULL(Screen.ScreenGroupId,0) AS ScreenGroupId,    
    ISNULL(Menu.ScreenId, 0) AS ScreenId,    
    ISNULL(PermissionValue, 0) AS Permission,    
    ISNULL(OrderNumber, 0) AS OrderNumber,    
    ISNULL(Screen.TaskId,0) AS TaskId,    
    ISNULL(Screen.IconPath,'') As IconPath,     
    ISNULL(T.ResourceTypeId,0) AS ResourceTypeId,    
    ISNULL(T.ModuleId,0) AS ModuleId    
       
       
   FROM sSecurity.tMenu Menu     
   LEFT OUTER JOIN sSecurity.tScreen Screen    
    ON Menu.ScreenId = Screen.Id      
   LEFT OUTER JOIN (     
       SELECT     
        TaskId,     
        SUM(Permission & 2) PermissionValue          
       FROM     
        sSecurity.tPermissionUser P    
        INNER JOIN sSecurity.tTask T ON T.Id = P.TaskId    
        --INNER JOIN sSecuirty.tOrganizationTask OT ON OT.TaskId = T.Id AND OT.OrgGroupId = P.OrgGroupId     
        INNER JOIN sSecurity.tModulePermission MP ON MP.ModuleId = T.ModuleId AND MP.OrgGroupId = P.OrgGroupId    
       WHERE    
        T.IsObsolete = 0 AND P.UserId = @rUserId    
       GROUP BY     
        TaskId) TBL2    
       ON     
        Screen.TaskId = TBL2.TaskId     
         LEFT OUTER JOIN sSecurity.tTask  T ON T.Id =  Screen.TaskId AND ResourceTypeId = @rResourceTypeId    
          
       WHERE     
       (Menu.IsObsolete = 0)  --For ScreenGroupId Master Page    
       AND ISNULL(Screen.IsObsolete, 0) = 0)     
       MenuTable      
       --INNER JOIN sSecurity.tTask  T ON T.Id =  MenuTable.TaskId     
   WHERE     
    MenuTable.ScreenId = 0 OR MenuTable.Permission >= 2     
    AND     
    ((MenuTable.ResourceTypeId = @rResourceTypeId) OR (@rResourceTypeId = 0))    
    AND ((MenuTable.MenuID = @rMenuID)OR(@rMenuID = 0))    
        
   ORDER BY     
    ParentMenuID    
  END    
 ELSE     
  BEGIN    
   SELECT    
    MenuID,    
    ModuleId,    
    ParentMenuID,    
    MenuTitle,      
    ScreenTitle,       
    PageURL,    
    ScreenId,    
    Permission,    
    OrderNumber,      
    TaskId,      
     PageURL  AS PagePath,    
     IconPath    
        
   FROM    
     (SELECT Menu.Id AS MenuID,     
    ISNULL(Menu.ParentMenuID, 0) AS ParentMenuID,     
    Menu.Title AS MenuTitle,       
    ISNULL(Screen.Title, '') AS ScreenTitle,        
    ISNULL(Screen.Page, '') AS PageURL,    
    ISNULL(Screen.ScreenGroupId,0) AS ScreenGroupId,    
    ISNULL(Menu.ScreenId, 0) AS ScreenId,    
    ISNULL(PermissionValue, 0) AS Permission,    
    ISNULL(OrderNumber, 0) AS OrderNumber,    
    ISNULL(Screen.TaskId,0) AS TaskId,    
    ISNULL(Screen.IconPath,'') As IconPath,     
    ISNULL(T.ResourceTypeId,0) AS ResourceTypeId,    
    ISNULL(T.ModuleId,0) AS ModuleId    
       
       
   FROM sSecurity.tMenu Menu     
   LEFT OUTER JOIN sSecurity.tScreen Screen    
    ON Menu.ScreenId = Screen.Id      
   LEFT OUTER JOIN (     
       SELECT     
        TaskId,     
        SUM(Permission & 2) PermissionValue          
       FROM     
        sSecurity.tPermission P    
        INNER JOIN sSecurity.tTask T ON T.Id = P.TaskId    
        --INNER JOIN sSecuirty.tOrganizationTask OT ON OT.TaskId = T.Id AND OT.OrgGroupId = P.OrgGroupId     
        INNER JOIN sSecurity.tModulePermission MP ON MP.ModuleId = T.ModuleId AND MP.OrgGroupId = P.OrgGroupId    
        INNER JOIN    
        (    
        SELECT RoleId FROM sSecurity.tUserRole UR WHERE  UserId = @rUserId AND IsObsolete = 0    
        ) B ON B.RoleId = P.RoleId    
       WHERE    
        T.IsObsolete = 0    
       GROUP BY     
        TaskId) TBL2    
       ON     
        Screen.TaskId = TBL2.TaskId     
         LEFT OUTER JOIN sSecurity.tTask  T ON T.Id =  Screen.TaskId AND ResourceTypeId = @rResourceTypeId    
          
       WHERE     
       (Menu.IsObsolete = 0)  --For ScreenGroupId Master Page    
       AND ISNULL(Screen.IsObsolete, 0) = 0)     
       MenuTable      
       --INNER JOIN sSecurity.tTask  T ON T.Id =  MenuTable.TaskId     
   WHERE     
    MenuTable.ScreenId = 0 OR MenuTable.Permission >= 2     
    AND     
    ((MenuTable.ResourceTypeId = @rResourceTypeId) OR (@rResourceTypeId = 0))    
    AND ((MenuTable.MenuID = @rMenuID)OR(@rMenuID = 0))    
        
   ORDER BY     
    ParentMenuID    
  END    
 END    
 ELSE    
 BEGIN     
 SELECT    
   MenuID,    
   ModuleId,    
   ParentMenuID,    
   MenuTitle,      
   ScreenTitle,       
   PageURL,    
   ScreenId,    
   Permission,    
   OrderNumber,      
   TaskId,      
   PageURL  AS PagePath,    
   MenuIsObsolete,    
   Icon      
  FROM    
  (    

SELECT    
   ISNULL(Menu.Id,0) AS MenuID,     
   ISNULL(Menu.ParentMenuID, 0) AS ParentMenuID,     
   ISNULL(Menu.Title,'') AS MenuTitle,       
   ISNULL(Screen.Title, '') AS ScreenTitle,        
   ISNULL(Screen.Page, '') AS PageURL,    
   ISNULL(Screen.ScreenGroupId,0) AS ScreenGroupId,    
   ISNULL(Screen.Id, 0) AS ScreenId,    
   ISNULL(PermissionValue, 0) AS Permission,    
   ISNULL(OrderNumber, 0) AS OrderNumber,    
   ISNULL(Screen.TaskId,0) AS TaskId,    
   ISNULL(T.ResourceTypeId,0) AS ResourceTypeId,    
   ISNULL(T.ModuleId,0) AS ModuleId,    
   ISNULL(Menu.IsObsolete,1) AS MenuIsObsolete,    
   ISNULL(Screen.IconPath,'') AS Icon    
  FROM     
  sSecurity.tMenu Menu     
  LEFT OUTER JOIN sSecurity.tScreen Screen ON Menu.ScreenId = Screen.Id        
  LEFT OUTER JOIN     
  (    
  SELECT     
   P.TaskId,     
   SUM(OT.AvailablePermission & 2) PermissionValue          
  FROM     
   sSecurity.tPermission P    
   INNER JOIN sSecurity.tTask T ON T.Id = P.TaskId  AND T.IsObsolete = 0  
   INNER JOIN sSecurity.tModulePermission MP ON MP.ModuleId = T.ModuleId      
   INNER JOIN sCommon.tModuleMaster MM ON MP.ModuleId = MM.Id And  MM.IsObsolete = 0    
   INNER JOIN sSecurity.tOrganizationTask OT ON OT.TaskId = T.Id        
   INNER JOIN (SELECT RoleId FROM sSecurity.tUserRole WHERE  UserId = @rUserId AND IsObsolete = 0) UR ON UR.RoleId = P.RoleId     
  GROUP BY P.TaskId    
  ) TBL2 ON Screen.TaskId = TBL2.TaskId     
  LEFT OUTER JOIN sSecurity.tTask  T ON T.Id =  Screen.TaskId AND ResourceTypeId = @rResourceTypeId    
  WHERE (Menu.IsObsolete = 0)    
  AND ISNULL(Screen.IsObsolete, 0) = 0    
  AND (Menu.Id  in (19184027,2411))   
  --Or ParentMenuID  in (24,2711))     
  --AND (
  ----Menu.Id IN (19184027)  
  ----OR ParentMenuID IN (55)
  --)  
  

  UNION
  

  SELECT     
  
   ISNULL(Menu.Id, 0) AS MenuID,     
   ISNULL(Menu.Id, 0) AS ParentMenuID,     
   ISNULL(Screen1.Title,'') AS MenuTitle,       
   ISNULL(Screen1.Title, '') AS ScreenTitle,        
   ISNULL(Screen1.Page, '')  AS PageURL,    
   ISNULL(Screen1.ScreenGroupId,0) AS ScreenGroupId,    
   ISNULL(Screen1.Id, 0) AS ScreenId,    
   ISNULL(PermissionValue, 0) AS Permission,    
   ISNULL(OrderNumber, 0) AS OrderNumber,    
   ISNULL(Screen1.TaskId,0) AS TaskId,    
   ISNULL(T.ResourceTypeId,0) AS ResourceTypeId,    
   ISNULL(T.ModuleId,0) AS ModuleId,    
   ISNULL(Menu.IsObsolete,1) AS MenuIsObsolete,    
   ISNULL(Screen1.IconPath,'') AS Icon    
  FROM     
   sSecurity.tMenu Menu     
   LEFT OUTER JOIN sSecurity.tScreen Screen ON Menu.ScreenId = Screen.Id        
   LEFT OUTER JOIN sSecurity.tScreen Screen1 ON Screen1.ScreenGroupId = Screen.ScreenGroupId     
   LEFT OUTER JOIN sSecurity.tTask  T ON T.Id =  Screen1.TaskId    
   LEFT OUTER JOIN (     
  SELECT     
   P.TaskId,     
   SUM(OT.AvailablePermission & 2) PermissionValue          
  FROM     
   sSecurity.tPermission P    
   INNER JOIN sSecurity.tTask T ON T.Id = P.TaskId   AND T.IsObsolete = 0  
   INNER JOIN sSecurity.tModulePermission MP ON MP.ModuleId = T.ModuleId        
   INNER JOIN sCommon.tModuleMaster MM ON MP.ModuleId = MM.Id And  MM.IsObsolete = 0    
   INNER JOIN sSecurity.tOrganizationTask OT ON OT.TaskId = T.Id       
   INNER JOIN (SELECT RoleId FROM sSecurity.tUserRole WHERE  UserId = @rUserId AND IsObsolete = 0) UR ON UR.RoleId = P.RoleId    
            
  GROUP BY P.TaskId    
  ) TBL2 ON Screen1.TaskId = TBL2.TaskId    
  WHERE (Menu.IsObsolete = 0) AND ISNULL(Screen1.IsObsolete, 0) = 0    
  --AND (Menu.ParentMenuID  in (24,2711)   
  --or Menu.Id  in (24,2711))   
    
  AND Screen1.Id IN (31295 , 31293,31304)

  ) MenuTable      
  WHERE     
  --(MenuTable.ScreenId = 0 OR MenuTable.Permission >= 2)  
  --AND   
  ((MenuTable.ResourceTypeId = @rResourceTypeId) OR (@rResourceTypeId = 0))    
  AND ((MenuTable.MenuID = @rMenuID)OR(@rMenuID = 0))    
  AND MenuIsObsolete = 0    
  AND MenuTable.MenuId IN (19184027,2411)   
  AND ModuleId IN (95,235)
  ORDER BY ParentMenuID  
 END    
END
GO
PRINT N'Creating Procedure [sEMeeting].[pMobileNotitficationTitleGet]...';


GO

CREATE PROCEDURE [sEMeeting].[pMobileNotitficationTitleGet]
		@rEventId int
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY	
			SELECT Title FROM sAlert.tEventMaster WHERE Id= @rEventId
		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pNotitficationLogIdGet]...';


GO

CREATE PROCEDURE [sEMeeting].[pNotitficationLogIdGet]
		@rFromId bigint,
		@rToId bigint
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY	
			SELECT Id AS NotificationId FROM sAlert.tNotificationLog WHERE (Id>=@rFromId) AND (Id<=@rToId)
		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pOnlineMeetingAccountDetailsGet]...';


GO

CREATE PROCEDURE [sEMeeting].[pOnlineMeetingAccountDetailsGet]
	@rMeetingAccountId int

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
	BEGIN TRY
		SELECT OAuthAccountId,OAuthClientId,OAuthClientSecret,AccountEmailId,JWTToken FROM sAcademic.tOnlineMeetingAccount WHERE Id = @rMeetingAccountId AND ISNULL(IsObsolete,0) = 0
	END TRY
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pOnlineMeetingAccounts]...';


GO

CREATE PROCEDURE [sEMeeting].[pOnlineMeetingAccounts]
	@rOnlineMeetingAccountId bigint

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY
			SELECT STUFF((SELECT ','+ CONVERT(varchar,OM.Id) FROM sEMeeting.tOnlineMeeting OM WHERE MeetingAccountId =  @rOnlineMeetingAccountId AND IsCompleted = 0								FOR XML PATH('')), 1, 1, '') AS Ids
		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pOnlineMeetingAssigneeGet]...';


GO

CREATE PROCEDURE [sEMeeting].[pOnlineMeetingAssigneeGet]
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY
			SELECT Id,
			Title 
			FROM sCommon.tAssigneeMaster
			WHERE IsObsolete=0 ORDER BY Id
		END TRY
		BEGIN CATCH 
		SELECT -1 AS Result
		END CATCH
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pOnlineMeetingAttendanceAdd]...';


GO


CREATE PROCEDURE [sEMeeting].[pOnlineMeetingAttendanceAdd]
	 @rOnlineMeetingId bigint ,
	 @rSourceId bigint,
	 @rSourceTypeId int,
	 @rAttendanceType bit
		 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 
	 
	 IF(@rAttendanceType= 1) 
	   BEGIN
	   IF NOT EXISTS (SELECT 1 FROM sEMeeting.tOnlineMeetingAttendance WHERE OnlineMeetingId = @rOnlineMeetingId AND ParticipantSourceId = @rSourceId AND ParticipantSourceTypeId = @rSourceTypeId)
			BEGIN
			INSERT  INTO sEMeeting.tOnlineMeetingAttendance
			(OnlineMeetingId,ParticipantSourceTypeId, ParticipantSourceId, IsPresent ,FirstInDateTime, IsLoggedIn)
			VALUES 
			(@rOnlineMeetingId, @rSourceTypeId, @rSourceId,1,GetDate(), 1)
			END
		SELECT 1 AS Result	
		END
		
		ELSE IF (@rAttendanceType= 0)
		BEGIN
		IF EXISTS (SELECT 1 FROM sEMeeting.tOnlineMeetingAttendance WHERE OnlineMeetingId = @rOnlineMeetingId AND ParticipantSourceId = @rSourceId AND ParticipantSourceTypeId = @rSourceTypeId)
			BEGIN
			UPDATE sEMeeting.tOnlineMeetingAttendance 
			SET LastOutDateTime = GetDate(),
			IsLoggedIn = 0
			Where ParticipantSourceId= @rSourceId AND OnlineMeetingId= @rOnlineMeetingId And ParticipantSourceTypeId = @rSourceTypeId
			SELECT 11 AS Result
			END
			
		ELSE 
		SELECT -1 AS Result
	
END
END
GO
PRINT N'Creating Procedure [sEMeeting].[pOnlineMeetingAttendanceGet]...';


GO


CREATE PROCEDURE [sEMeeting].[pOnlineMeetingAttendanceGet]
	@rOnlineMeetingId bigint
 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN 
	
	CREATE TABLE #ScopeSourceTypeId
	(
		Id int IDENTITY,
		ScopeSourceTypeId bigint
	)
	CREATE TABLE #StudentData
	(
	Class nvarchar(200),
	Code nvarchar(200),
	RollNo bigint,
	StudentName nvarchar(200),
	IsPresent nvarchar(100),
	ClassID bigint,
	DivisionId bigint,
	AcademicId bigint,
	IsLoggedIn nvarchar(100),
	FirstInDateTime datetime,
	LastOutDateTime datetime,
	IsPresentAbsent nvarchar(100)
	)

	INSERT INTO #ScopeSourceTypeId
	Select Distinct  ScopeSourceTypeId from [sEMeeting].[tOnlineMeetingScope] Where OnlineMeetingId=@rOnlineMeetingId

DECLARE @lCountScopeSourceTypeId int,@lScopeSourceTypeId int,@lId int

SELECT @lCountScopeSourceTypeId=COUNT(ScopeSourceTypeId) FROM #ScopeSourceTypeId
	SELECT Top 1 @lId=Id FROM #ScopeSourceTypeId

	WHILE @lId <= @lCountScopeSourceTypeId
			BEGIN
			SELECT @lScopeSourceTypeId=ScopeSourceTypeId FROM #ScopeSourceTypeId WHERE Id=@lId
	--AcademicId
	IF(@lScopeSourceTypeId=2001)
	BEGIN
	INSERT INTO #StudentData
	SELECT 
			SA.ClassName+'-'+SA.DivisionName AS Class,
			SA.Code,
			SA.RollNo,
			SA.FirstName +' '+SA.MiddleName +' '+SA.LastName As StudentName ,
			ISNULL((SELECT (CASE WHEN OMA.IsPresent=1 THEN 'P' ELSE 'N' END)  FROM sEMeeting.tOnlineMeetingAttendance OMA 
			WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId),'N')AS IsPresent,
			SA.ClassID,
			SA.DivisionId,
			SA.AcademicId,
			ISNULL((SELECT (CASE WHEN OMA.IsLoggedIn=1 THEN 'ON' ELSE 'OFF' END)  FROM sEMeeting.tOnlineMeetingAttendance OMA 
			WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId),'OFF')AS IsLoggedIn,
			ISNULL((SELECT  OMA.FirstInDateTime FROM sEMeeting.tOnlineMeetingAttendance OMA 
			WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId),OM.StartDate)AS FirstInDateTime,
			(SELECT  OMA.LastOutDateTime FROM sEMeeting.tOnlineMeetingAttendance OMA WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId ) as LastOutDateTime,
			ISNULL((SELECT  OMA.IsPresent  FROM sEMeeting.tOnlineMeetingAttendance OMA 
			WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId),0)AS IsPresentAbsent
			
  FROM		sStudent.vStudentAcademic SA
			INNER JOIN sEMeeting.tOnlineMeetingScope OMS ON SA.AcademicId=OMS.ScopeSourceId
			INNER JOIN [sEMeeting].[tOnlineMeeting] OM ON OM.Id=OMS.OnlineMeetingId
			WHERE 
			OMS.OnlineMeetingId=@rOnlineMeetingId
			AND SA.StatusId = 1
			GROUP BY
			SA.StudentId,SA.FirstName ,SA.MiddleName,SA.LastName,
			SA.ClassName,SA.Code,SA.RollNo,SA.RollNo,
			SA.DivisionName,SA.ClassID,
			SA.DivisionId,SA.AcademicId,OM.StartDate
			ORDER BY SA.ClassID,SA.DivisionId,SA.RollNo
	END
	--ClassId
	ELSE IF(@lScopeSourceTypeId=2016)
	BEGIN
	INSERT INTO #StudentData
	SELECT 

			SA.ClassName+'-'+SA.DivisionName AS Class,
			SA.Code,
			SA.RollNo,
			SA.FirstName +' '+SA.MiddleName +' '+SA.LastName As StudentName ,
			ISNULL((SELECT (CASE WHEN OMA.IsPresent=1 THEN 'P' ELSE 'N' END)  FROM sEMeeting.tOnlineMeetingAttendance OMA 
			WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId),'N')AS IsPresent,
			SA.ClassID,
			SA.DivisionId,
			SA.AcademicId,
			ISNULL((SELECT (CASE WHEN OMA.IsLoggedIn=1 THEN 'ON' ELSE 'OFF' END)  FROM sEMeeting.tOnlineMeetingAttendance OMA 
			WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId),'OFF')AS IsLoggedIn,
			ISNULL((SELECT   OMA.FirstInDateTime  FROM sEMeeting.tOnlineMeetingAttendance OMA 
			WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId),OM.StartDate)AS FirstInDateTime,
			(SELECT  OMA.LastOutDateTime FROM sEMeeting.tOnlineMeetingAttendance OMA WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId ) as LastOutDateTime,
			ISNULL((SELECT  OMA.IsPresent  FROM sEMeeting.tOnlineMeetingAttendance OMA 
			WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId),0)AS IsPresentAbsent
			
			
  FROM		sStudent.vStudentAcademic SA
			INNER JOIN sEMeeting.tOnlineMeetingScope OMS ON SA.ClassId=OMS.ScopeSourceId
			INNER JOIN [sEMeeting].[tOnlineMeeting] OM ON OM.Id=OMS.OnlineMeetingId
			WHERE 
			OMS.OnlineMeetingId=@rOnlineMeetingId
			AND SA.StatusId = 1
			GROUP BY 
			SA.StudentId,SA.FirstName ,SA.MiddleName,SA.LastName,
			SA.ClassName,SA.Code,SA.RollNo,SA.RollNo,
			SA.DivisionName,SA.ClassID,
			SA.DivisionId,SA.AcademicId,OM.StartDate
			ORDER BY SA.ClassID,SA.DivisionId,SA.RollNo
	END
	--DeparmentId
	ELSE IF(@lScopeSourceTypeId=2017)
	BEGIN
	INSERT INTO #StudentData
	SELECT 

			SA.ClassName+'-'+SA.DivisionName AS Class,
			SA.Code,
			SA.RollNo,
			SA.FirstName +' '+SA.MiddleName +' '+SA.LastName As StudentName ,
			ISNULL((SELECT (CASE WHEN OMA.IsPresent=1 THEN 'P' ELSE 'N' END)  FROM sEMeeting.tOnlineMeetingAttendance OMA 
			WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId),'N')AS IsPresent,
			SA.ClassID,
			SA.DivisionId,
			SA.AcademicId,
			ISNULL((SELECT (CASE WHEN OMA.IsLoggedIn=1 THEN 'ON' ELSE 'OFF' END)  FROM sEMeeting.tOnlineMeetingAttendance OMA 
			WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId),'OFF')AS IsLoggedIn,
			ISNULL((SELECT   OMA.FirstInDateTime  FROM sEMeeting.tOnlineMeetingAttendance OMA 
			WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId),OM.StartDate)AS FirstInDateTime,
			(SELECT  OMA.LastOutDateTime FROM sEMeeting.tOnlineMeetingAttendance OMA WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId ) as LastOutDateTime,
			ISNULL((SELECT  OMA.IsPresent  FROM sEMeeting.tOnlineMeetingAttendance OMA 
			WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId),0)AS IsPresentAbsent
			
			
  FROM		sStudent.vStudentAcademic SA
			INNER JOIN sEMeeting.tOnlineMeetingScope OMS ON SA.DepartmentId=OMS.ScopeSourceId
			INNER JOIN [sEMeeting].[tOnlineMeeting] OM ON OM.Id=OMS.OnlineMeetingId
			WHERE 
			OMS.OnlineMeetingId=@rOnlineMeetingId
			AND SA.StatusId = 1
			GROUP BY 
			SA.StudentId,SA.FirstName ,SA.MiddleName,SA.LastName,
			SA.ClassName,SA.Code,SA.RollNo,SA.RollNo,
			SA.DivisionName,SA.ClassID,
			SA.DivisionId,SA.AcademicId,OM.StartDate
			ORDER BY SA.ClassID,SA.DivisionId,SA.RollNo
	END
	--OrgId
	ELSE IF(@lScopeSourceTypeId=2021)
	BEGIN
	INSERT INTO #StudentData
	SELECT 
			SA.ClassName+'-'+SA.DivisionName AS Class,
			SA.Code,
			SA.RollNo,
			SA.FirstName +' '+SA.MiddleName +' '+SA.LastName As StudentName ,
			ISNULL((SELECT (CASE WHEN OMA.IsPresent=1 THEN 'P' ELSE 'N' END)  FROM sEMeeting.tOnlineMeetingAttendance OMA 
			WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId),'N')AS IsPresent,
			SA.ClassID,
			SA.DivisionId,
			SA.AcademicId,
			ISNULL((SELECT (CASE WHEN OMA.IsLoggedIn=1 THEN 'ON' ELSE 'OFF' END)  FROM sEMeeting.tOnlineMeetingAttendance OMA 
			WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId),'OFF')AS IsLoggedIn,
			ISNULL((SELECT   OMA.FirstInDateTime  FROM sEMeeting.tOnlineMeetingAttendance OMA 
			WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId),OM.StartDate)AS FirstInDateTime,
			(SELECT  OMA.LastOutDateTime FROM sEMeeting.tOnlineMeetingAttendance OMA WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId ) as LastOutDateTime,
			ISNULL((SELECT  OMA.IsPresent  FROM sEMeeting.tOnlineMeetingAttendance OMA 
			WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId),0)AS IsPresentAbsent
			
			
  FROM		sStudent.vStudentAcademic SA
			INNER JOIN sEMeeting.tOnlineMeetingScope OMS ON SA.OrgId=OMS.ScopeSourceId
			INNER JOIN [sEMeeting].[tOnlineMeeting] OM ON OM.Id=OMS.OnlineMeetingId
			WHERE 
			OMS.OnlineMeetingId=@rOnlineMeetingId
			AND SA.StatusId = 1
			GROUP BY 
			SA.StudentId,SA.FirstName ,SA.MiddleName,SA.LastName,
			SA.ClassName,SA.Code,SA.RollNo,SA.RollNo,
			SA.DivisionName,SA.ClassID,
			SA.DivisionId,SA.AcademicId,OM.StartDate
			ORDER BY SA.ClassID,SA.DivisionId,SA.RollNo
	END
	--Division
	ELSE IF(@lScopeSourceTypeId=2005)
	BEGIN
	INSERT INTO #StudentData
	SELECT 
			SA.ClassName+'-'+SA.DivisionName AS Class,
			SA.Code,
			SA.RollNo,
			SA.FirstName +' '+SA.MiddleName +' '+SA.LastName As StudentName ,
			ISNULL((SELECT (CASE WHEN OMA.IsPresent=1 THEN 'P' ELSE 'N' END)  FROM sEMeeting.tOnlineMeetingAttendance OMA 
			WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId),'N')AS IsPresent,
			SA.ClassID,
			SA.DivisionId,
			SA.AcademicId,
			ISNULL((SELECT (CASE WHEN OMA.IsLoggedIn=1 THEN 'ON' ELSE 'OFF' END)  FROM sEMeeting.tOnlineMeetingAttendance OMA 
			WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId),'OFF')AS IsLoggedIn,
			ISNULL((SELECT   OMA.FirstInDateTime  FROM sEMeeting.tOnlineMeetingAttendance OMA 
			WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId),OM.StartDate)AS FirstInDateTime,
			(SELECT  OMA.LastOutDateTime FROM sEMeeting.tOnlineMeetingAttendance OMA WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId ) as LastOutDateTime,
			ISNULL((SELECT  OMA.IsPresent  FROM sEMeeting.tOnlineMeetingAttendance OMA 
			WHERE  OMA.OnlineMeetingId=@rOnlineMeetingId),0)AS IsPresentAbsent
			
			
  FROM		sStudent.vStudentAcademic SA
			INNER JOIN sEMeeting.tOnlineMeetingScope OMS ON SA.DivisionId=OMS.ScopeSourceId
			INNER JOIN [sEMeeting].[tOnlineMeeting] OM ON OM.Id=OMS.OnlineMeetingId 
			WHERE 
			OMS.OnlineMeetingId=@rOnlineMeetingId
			AND SA.StatusId = 1
			GROUP BY 
			SA.StudentId,SA.FirstName ,SA.MiddleName,SA.LastName,
			SA.ClassName,SA.Code,SA.RollNo,SA.RollNo,
			SA.DivisionName,SA.ClassID,
			SA.DivisionId,SA.AcademicId,OM.StartDate
			ORDER BY SA.ClassID,SA.DivisionId,SA.RollNo
	END

	SET @lId=@lId+1
	END
	Select * from #StudentData
	DROP TABLE #ScopeSourceTypeId
	DROP TABLE #StudentData
				
END
GO
PRINT N'Creating Procedure [sEMeeting].[pOnlineMeetingClassGet]...';


GO

CREATE PROCEDURE [sEMeeting].[pOnlineMeetingClassGet]
	@rDepartmentId int

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	BEGIN TRY
		SELECT Id, DisplayTitle as Title FROM sSetup.tClassMaster WHERE IsObsolete = 0 AND DepartmentId = @rDepartmentId
	END TRY

	BEGIN CATCH
	 SELECT -1 as Result
	END CATCH

END
GO
PRINT N'Creating Procedure [sEMeeting].[pOnlineMeetingDelete]...';


GO
 
 CREATE PROCEDURE [sEMeeting].[pOnlineMeetingDelete]
	 
	 @rId bigint
	 	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 

BEGIN 
	BEGIN TRY
		BEGIN TRANSACTION
			Delete from sEMeeting.tOnlineMeetingResource where OnlineMeetingId=@rId
			Delete from sEMeeting.tOnlineMeetingReference where OnlineMeetingId=@rId
			Delete from sEMeeting.tOnlineMeetingAttendance where OnlineMeetingId=@rId
			Delete from sEMeeting.tOnlineMeetingScope where OnlineMeetingId=@rId
			Delete from sEMeeting.tOnlineMeetingSettings Where OnlineMeetingId = @rId
			Delete from sEMeeting.tOnlineMeeting where Id=@rId
			select 1 as Result
		COMMIT TRANSACTION			 
	END TRY
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		select -1 as Result		
	END CATCH	
END
GO
PRINT N'Creating Procedure [sEMeeting].[pOnlineMeetingDepartmentGet]...';


GO


CREATE PROCEDURE [sEMeeting].[pOnlineMeetingDepartmentGet]
		@rOrgId int,
		@rUserId bigint 
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
	BEGIN TRY
		SELECT 
		DM.Id, 
		DM.DisplayTitle AS Title 
		FROM sSetup.tDepartmentMaster DM 
		INNER JOIN sSecurity.tUserAccess U ON U.SourceId = DM.Id 
		AND U.SourceTypeId=2017
        WHERE IsObsolete = 0 
		AND OrgId = @rOrgId  
		AND UserId = @rUserId
	END TRY
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pOnlineMeetingDivisionGet]...';


GO

CREATE PROCEDURE [sEMeeting].[pOnlineMeetingDivisionGet]
	 
	 @rClassId int,
	 @rUserSourceId bigint,
	 @rUserSourceTypeId int,
	 @rOrgId int,
	 @rUserId bigint,
	 @rBatchId int,
	 @rAttendanceEntryBasedOn nvarchar
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 
BEGIN
	BEGIN TRY
        if (@rAttendanceEntryBasedOn = 'Class Teacher' AND @rUserSourceTypeId = 95)
			BEGIN
				SELECT DISTINCT DM.Id AS Id, DM.DisplayTitle AS Title
					   FROM sSetup.tClassMaster CM 
					   INNER JOIN sSetup.tDivisionMaster DM ON DM.ClassId = CM.Id
                       INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=CM.OrgId
                       WHERE DM.InchargeemployeeID= @rUserSourceId  
					   AND CM.OrgId= @rOrgId 
					   AND CM.Id = @rClassId
			END	 
		else if (@rAttendanceEntryBasedOn = 'Academic Setup' AND @rUserSourceTypeId = 95)
				BEGIN
					SELECT DISTINCT DivisionId AS Id, DivisionTitle, DivisionOrderIndex AS Title 
                        FROM sAcademic.vTeacherSubjectDivision TSD 
						INNER JOIN sSetup.tOrganizationMaster OM ON OM.Id=TSD.OrgId 
						WHERE  TSD.EmployeeId= @rUserSourceId 
						AND TSD.ClassId = @rClassId
                        ORDER BY DivisionOrderIndex
				END
		else
					BEGIN
						SELECT DM.Id,DM.DisplayTitle AS Title 
							   FROM sSetup.tDivisionMaster DM 
                               INNER JOIN sSetup.tClassMaster CM ON CM.Id=DM.ClassId AND CM.OrgId= @rOrgId 
                               INNER JOIN sSecurity.tUserAccess U ON U.SourceId = DM.Id AND U.SourceTypeId=2005 
                               WHERE  CM.Id = @rClassId 
							   AND UserId = @rUserId 
							   Order by DM.OrderIndex
					END
	END TRY

	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
END
GO
PRINT N'Creating Procedure [sEMeeting].[pOnlineMeetingEmployeeGet]...';


GO

CREATE PROCEDURE [sEMeeting].[pOnlineMeetingEmployeeGet]
	 
	 @rDepartmentId int,
	 @rOrgId int
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
			 
BEGIN
	BEGIN TRY
	SELECT EmployeeId AS Id,
	EmployeeName AS Title 
	FROM sHrm.vEmployeeCommunication 
	WHERE ISNULL(IsOtherUser,0) = 0 
	AND StatusId=7 
	AND DepartmentId=@rDepartmentId 
	AND OrgId=@rOrgId ORDER BY Code
	END TRY

	BEGIN CATCH 
		SELECT -1 AS Result
	END CATCH
END
GO
PRINT N'Creating Procedure [sEMeeting].[pOnlineMeetingOrganizationGet]...';


GO

CREATE PROCEDURE [sEMeeting].[pOnlineMeetingOrganizationGet]
		@rOrgGroupId int,
		@rUserId bigint 
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
	BEGIN TRY
		SELECT 
		UO.Id, 
		UO.DisplayTitle AS Title 
		FROM sSetup.vUserOrganization UO
        WHERE IsObsolete = 0 
		AND OrgGroupId = @rOrgGroupId  
		AND UserId = @rUserId
	END TRY
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pOnlineMeetingProviderGet]...';


GO

CREATE PROCEDURE [sEMeeting].[pOnlineMeetingProviderGet]
	@rMeetingAccountId int

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
	BEGIN TRY
		SELECT MeetingProviderId FROM sAcademic.tOnlineMeetingAccount WHERE Id = @rMeetingAccountId AND ISNULL(IsObsolete,0) = 0
	END TRY
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pOnlineMeetingReferenceDelete]...';


GO
 
CREATE PROCEDURE [sEMeeting].[pOnlineMeetingReferenceDelete]

        @rOnlineMeetingId bigint,
		@rReferencePath nvarchar(Max)
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	IF NOT EXISTS (SELECT 1 FROM sEMeeting.tOnlineMeetingReference WHERE OnlineMeetingId = @rOnlineMeetingId AND ReferencePath = @rReferencePath)
	BEGIN 
		SELECT -1 AS Result
	END
	ELSE
	BEGIN
		DELETE FROM sEMeeting.tOnlineMeetingReference
		WHERE OnlineMeetingId = @rOnlineMeetingId 
		AND ReferencePath = @rReferencePath
		SELECT 1 AS Result
	END
END
GO
PRINT N'Creating Procedure [sEMeeting].[pOnlineMeetingResourceAdd]...';


GO

 
 
CREATE PROCEDURE [sEMeeting].[pOnlineMeetingResourceAdd]
	 
	@rOnlineMeetingId bigint,
	@rResourceTypeId smallint,
	@rResourcePath nvarchar(max),	
	@rIsObsolete bit = 0,
	@rCreatedUserId bigint
	
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN
	BEGIN TRY
	BEGIN TRANSACTION

	IF NOT EXISTS (SELECT 1 FROM [sEMeeting].[tOnlineMeetingResource] WHERE OnlineMeetingId = @rOnlineMeetingId AND ResourcePath = @rResourcePath)
	BEGIN		
	INSERT INTO [sEMeeting].[tOnlineMeetingResource]	
		(
		OnlineMeetingId,
		ResourceTypeId,
		ResourcePath,
		IsObsolete,
		CreatedUserId,
		CreatedDateTime
		)
	VALUES
		(
		@rOnlineMeetingId,
		@rResourceTypeId,
		@rResourcePath,
		@rIsObsolete,
		@rCreatedUserId,
		GetUtcDate()
		)
	END
	COMMIT TRANSACTION
		SELECT 1 AS Result		
	END TRY
	
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
		
END
GO
PRINT N'Creating Procedure [sEMeeting].[pOnlineMeetingSettingsGet]...';


GO

CREATE PROCEDURE [sEMeeting].[pOnlineMeetingSettingsGet]
		@rOnlineMeetingId bigint
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY
			SELECT APIKey, APISecret, AccountEmailId, OAuthAccountId, OAuthClientId, OAuthClientSecret FROM [sEMeeting].[tOnlineMeetingSettings] WHERE @rOnlineMeetingId = OnlineMeetingId
		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pOnlineMeetingStatusTypeGet]...';


GO

 
CREATE PROCEDURE [sEMeeting].[pOnlineMeetingStatusTypeGet]
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
	BEGIN TRY
	SELECT 
	SM.Id,
	SM.Title 
	FROM sCommon.tStatusMaster SM 
	WHERE StatusTypeId = 113 ORDER BY Id
	END TRY
	BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH	
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pOnlineMeetingStudentGet]...';


GO

CREATE PROCEDURE [sEMeeting].[pOnlineMeetingStudentGet]
	@rDivisionId int,
	@rOrgId int,
	@rBatchId int

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	BEGIN TRY

	BEGIN
		SELECT AcademicId as Id,(Code+' - '+CONVERT(NVARCHAR(10),RollNo)+' - '+Name) AS Title from sStudent.vStudentAcademic WHERE StatusId=1 AND DivisionId = @rDivisionId AND				OrgId = @rOrgId AND BatchId = @rBatchId ORDER BY RollNo 
	END 

	END TRY
	
	BEGIN CATCH 
		SELECT -1 AS Result
	END CATCH	
		
END
GO
PRINT N'Creating Procedure [sEMeeting].[pOrgGroupIdGet]...';


GO

CREATE PROCEDURE [sEMeeting].[pOrgGroupIdGet]
		@rOrgId int
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY
			SELECT OrgGroupId FROM sSetup.tOrganizationMaster WHERE Id = @rOrgId
		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pPermissionGet]...';


GO
-- exec sEMeeting.pPermissionGet 9911,9911000000270
CREATE PROCEDURE sEMeeting.pPermissionGet
	@rOrgGroupId [int],
	@rUserId [bigint]
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	
IF EXISTS (SELECT 1 FROM sSecurity.tPermissionUser WHERE UserId=@rUserId AND OrgGroupId=@rOrgGroupId)
BEGIN
	SELECT		TP.TaskId AS TaskID,
			CAST(MAX(TP.C) AS BIT) AS [Create],
			CAST(MAX(TP.R) AS BIT) AS [Read],
			CAST(MAX(TP.U) AS BIT) AS [Update],
			CAST(MAX(TP.D) AS BIT) AS [Delete],
			CAST(MAX(TP.S) AS BIT) AS [Search],
			CAST(MAX(TP.P) AS BIT) AS [Print],
			CAST(MAX(TP.X) AS BIT) AS [Execute]
	FROM
	(
		SELECT 	
			P.TaskId					AS	TaskID,
			(Permission & 1) 		AS	C, --[Create],
			(Permission & 2)/2		AS	R, --[Read],
			(Permission & 4)/4		AS	U, --[Update],
			(Permission & 8)/8		AS	D, --[Delete],
			(Permission & 16)/16 	AS	S, --[Search],
			(Permission & 32)/32 	AS	P, --[Print],
			(Permission & 64)/64 	AS	X --[Execute]
		FROM sSecurity.tPermissionUser P
		INNER JOIN sSecurity.tOrganizationTask T ON P.TaskId = T.TaskId And T.OrgGroupId = @rOrgGroupId
		INNER JOIN sSecurity.tTask T1 ON T1.Id=P.TaskId 
		INNER JOIN sSecurity.tModulePermission MP ON MP.ModuleId=T1.ModuleId AND MP.OrgGroupId=@rOrgGroupId
		WHERE
		P.OrgGroupId = @rOrgGroupId AND P.UserId = @rUserId 
	) TP 
	GROUP BY TP.TaskId
END
ELSE
BEGIN
	SELECT		TP.TaskId AS TaskID,
			CAST(MAX(TP.C) AS BIT) AS [Create],
			CAST(MAX(TP.R) AS BIT) AS [Read],
			CAST(MAX(TP.U) AS BIT) AS [Update],
			CAST(MAX(TP.D) AS BIT) AS [Delete],
			CAST(MAX(TP.S) AS BIT) AS [Search],
			CAST(MAX(TP.P) AS BIT) AS [Print],
			CAST(MAX(TP.X) AS BIT) AS [Execute]
	FROM
	(
		SELECT 	
			P.TaskId					AS	TaskID,
			(Permission & 1) 		AS	C, --[Create],
			(Permission & 2)/2		AS	R, --[Read],
			(Permission & 4)/4		AS	U, --[Update],
			(Permission & 8)/8		AS	D, --[Delete],
			(Permission & 16)/16 	AS	S, --[Search],
			(Permission & 32)/32 	AS	P, --[Print],
			(Permission & 64)/64 	AS	X --[Execute]
		FROM sSecurity.tPermission P
		INNER JOIN sSecurity.tOrganizationTask T ON P.TaskId = T.TaskId And T.OrgGroupId = @rOrgGroupId
		INNER JOIN sSecurity.tTask T1 ON T1.Id=P.TaskId 
		INNER JOIN sSecurity.tModulePermission MP ON MP.ModuleId=T1.ModuleId AND MP.OrgGroupId=@rOrgGroupId
		INNER JOIN (SELECT RoleId FROM sSecurity.tUserRole WHERE IsObsolete = 0 AND UserId = @rUserId) R
		ON R.RoleId = P.RoleId 
		WHERE
		P.OrgGroupId = @rOrgGroupId 
		--AND
	--RoleId 
	
	--IN
	-- (SELECT RoleId FROM sSecurity.tUserRole WHERE IsObsolete = 0 AND UserId = @rUserId )
	) TP 
	GROUP BY TP.TaskId
	
	--SELECT	CAST(MAX(TP.C) AS BIT) AS [Create],
	--		CAST(MAX(TP.R) AS BIT) AS [Read],
	--		CAST(MAX(TP.U) AS BIT) AS [Update],
	--		CAST(MAX(TP.D) AS BIT) AS [Delete],
	--		CAST(MAX(TP.S) AS BIT) AS [Search],
	--		CAST(MAX(TP.P) AS BIT) AS [Print],
	--		CAST(MAX(TP.X) AS BIT) AS [Execute]
	--FROM
	--(
	--	SELECT 	(Permission & 1) AS C,
	--			(Permission & 2)/2 AS R,
	--			(Permission & 4)/4 AS U,
	--			(Permission & 8)/8 AS D,
	--			(Permission & 16)/16 AS S,
	--			(Permission & 32)/32 AS P,
	--			(Permission & 64)/64 AS X
	--	FROM sSecurity.tPermission
	--	WHERE TaskId = @rTaskId AND 
	--		  RoleId IN (SELECT RoleId FROM sSecurity.tUserRole WHERE IsObsolete = 0 AND UserId = @rUserId)
	--) TP
END


END
GO
PRINT N'Creating Procedure [sEMeeting].[pScreenGet]...';


GO
-- exec sEMeeting.pScreenGet 
CREATE PROCEDURE sEMeeting.pScreenGet 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
begin
	BEGIN TRY
		SELECT	
			S.Id			AS ScreenId,
			S.Title		AS ScreenTitle,
			S.TaskId		AS TaskId,	
			S.Page		As PageUrl,
			S.IsObsolete	AS ScreenIsObsolete,	
			S.Title,		
			S.Page,
			SG.Id AS ScreenGroupId,
			ISNULL(SG.Title,'') AS ScreenGroupName
		
		FROM 
			sSecurity.tScreen S (nolock)
		LEFT JOIN sSecurity.tScreenGroupMaster SG ON SG.Id=S.ScreenGroupId
		WHERE 
			S.IsObsolete = 0 and S.ScreenGroupID IN (253) OR S.Id = 31293
	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH
end
GO
PRINT N'Creating Procedure [sEMeeting].[pSMSTemplateIdGet]...';


GO

CREATE PROCEDURE [sEMeeting].[pSMSTemplateIdGet]
		@rOrgId int,
		@rCustomEventId bigint
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY
			SELECT SMSTemplateID FROM sAlert.tCustomTemplateMaster WHERE Id = @rCustomEventId AND OrgId = @rOrgId
		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pStudentProfileGet]...';


GO

CREATE PROCEDURE [sEMeeting].[pStudentProfileGet]
		@rAcademicId bigint,
		@rBatchId int
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY
			SELECT A.OrgId, BatchId, StudentId, A.Id AS AcademicId, SM.Code, A.RollNo, (FirstName +' '+MiddleName+' '+LastName) AS StudentName,
                           SM.FirstName, SM.MiddleName, SM.LastName, A.ClassId, A.DivisionId, (CM.DisplayTitle + '-' + DM.DisplayTitle) AS ClassDivision,
                            DPM.Id AS DepartmentId, DPM.DisplayTitle AS DepartmentName, SM.SUID, B.Title as BatchName 
                            FROM sStudent.tAcademic A INNER JOIN sStudent.tStudentMaster SM ON A.StudentId=SM.Id
                            INNER JOIN sSetup.tClassMaster CM ON CM.Id=A.ClassId INNER JOIN sSetup.tDivisionMaster DM ON DM.Id = A.DivisionId
                            INNER JOIN sSetup.tDepartmentMaster DPM ON DPM.Id = CM.DepartmentId
                            INNER JOIN sSetup.tBatchMaster B ON b.Id = A.BatchId 
                            WHERE A.Id= @rAcademicId  AND BatchId = @rBatchId
		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pUpdateMeetingDetails]...';


GO

CREATE PROC [sEMeeting].[pUpdateMeetingDetails]
	@rId bigint,
	@rType nvarchar(20)

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	BEGIN TRY
 
	IF EXISTS (SELECT 1 from [sEMeeting].[tOnlineMeeting] where Id = @rId AND @rType IN ('Join', 'Start Again', 'Complete'))
	BEGIN
	IF (@rType = 'Join')
		UPDATE [sEMeeting].[tOnlineMeeting] SET IsMeetingStarted=1, StartDateTime = GetUtcDate() WHERE Id= @rId

	ELSE IF (@rType = 'Start Again')
		UPDATE [sEMeeting].[tOnlineMeeting] SET IsCompleted=0,IsMeetingStarted=1 WHERE Id= @rId	

	ELSE IF (@rType = 'Complete')
		UPDATE [sEMeeting].[tOnlineMeeting] SET IsCompleted=550,IsMeetingStarted=0,EndDateTime = GetUtcDate() WHERE Id= @rId

	SELECT 1 as Result
	END 
	ELSE
	BEGIN
		SELECT -101 as Result
	END
	END TRY

	BEGIN CATCH
	SELECT -1 as Result
	END CATCH

END
GO
PRINT N'Creating Procedure [sEMeeting].[pUpdateOnlineMeetingAccountToken]...';


GO

CREATE PROCEDURE [sEMeeting].[pUpdateOnlineMeetingAccountToken]
	@rAccessToken nvarchar(max),
	@rOnlineMeetingAccountId bigint

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY
			UPDATE sAcademic.tOnlineMeetingAccount SET JWTToken = @rAccessToken Where Id = @rOnlineMeetingAccountId AND MeetingProviderId = 1
			SELECT 11 as Result
		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pUpdateOnlineMeetingSettingsToken]...';


GO

CREATE PROCEDURE [sEMeeting].[pUpdateOnlineMeetingSettingsToken]
	@rAccessToken nvarchar(max),
	@rOnlineMeetingIds nvarchar(max)

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY
			UPDATE sEMeeting.tOnlineMeetingSettings SET JWTToken = @rAccessToken WHERE OnlineMeetingId IN (SELECT CAST(Nodes.node.value('.', 'BIGINT') AS INT) AS id
			FROM (
			SELECT CAST('<x>' + REPLACE(@rOnlineMeetingIds, ',', '</x><x>') + '</x>' AS XML) AS xmlcol
			) AS t
			CROSS APPLY xmlcol.nodes('/x') AS Nodes(node))
		SELECT 11 as Result
		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pValidateOnlineMeetingAccount]...';


GO

CREATE PROCEDURE [sEMeeting].[pValidateOnlineMeetingAccount]
@rId bigint = 0,
@rStartDate nvarchar(50),
@rStartTime nvarchar(50),
@rEndDate nvarchar(50),
@rEndTime nvarchar(50),
@rMeetingAccountId int = 0

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY
		if(@rId = 0)
		BEGIN
			SELECT 1 
			FROM sEMeeting.tOnlineMeeting 
			WHERE (((StartDate <= @rStartDate) 
			AND (EndDate >= @rEndDate)) 
			OR ((StartDate >= @rStartDate) 
			AND (EndDate <= @rEndDate)))
            AND (((StartTime <= @rStartTime) 
			AND (EndTime >= @rEndTime)) 
			OR ((StartTime <=  @rStartTime) 
			AND (EndTime >= @rEndTime))) 
			AND MeetingAccountId = @rMeetingAccountId
		END
		ELSE
		BEGIN
			SELECT 1 
			FROM sEMeeting.tOnlineMeeting OM
			WHERE (((StartDate <= @rStartDate) 
			AND (EndDate >= @rEndDate)) 
			OR ((StartDate >= @rStartDate) 
			AND (EndDate <= @rEndDate)))
            AND (((StartTime <= @rStartTime) 
			AND (EndTime >= @rEndTime)) 
			OR ((StartTime <= @rEndTime) 
			AND (EndTime >= @rStartTime))) 
			AND @rId = OM.Id
			AND MeetingAccountId = @rMeetingAccountId
		END
		END TRY
		BEGIN CATCH
		SELECT -101 AS Result
		END CATCH
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pValidateOnlineMeetingTeacher]...';


GO


CREATE PROCEDURE [sEMeeting].[pValidateOnlineMeetingTeacher]
@rId bigint = 0,
@rStartDate nvarchar(50),
@rStartTime nvarchar(50),
@rEndDate nvarchar(50),
@rEndTime nvarchar(50),
@rMeetingAccountId int = 0

WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY
		BEGIN
			SELECT 1 
			FROM sEMeeting.tOnlineMeeting OM
			WHERE  HostEmployeeId = @rId
			AND (((StartDate <= @rStartDate) 
			AND (EndDate >= @rStartDate)) 
			OR ((StartDate >= @rEndDate) 
			AND (EndDate <= @rEndDate)))
            AND (((StartTime <= @rStartTime) 
			AND (EndTime >= @rEndTime)) 
			OR ((StartTime <= @rEndTime) 
			AND (EndTime >= @rStartTime)))
		END
		END TRY
		BEGIN CATCH
		SELECT -101 AS Result
		END CATCH
	END
GO
PRINT N'Creating Procedure [sEMeeting].[pWhatsAppTemplateGet]...';


GO

CREATE PROCEDURE [sEMeeting].[pWhatsAppTemplateGet]
		@rEventId int,
		@rOrgId int
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY	
			SELECT Template,WhatsAppTemplate,WhatsAppTemplateLanguage FROM sAlert.tEventTemplate WHERE EventId = @rEventId AND OrgId = @rOrgId AND AlertModeId = 4
		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
	END
GO
PRINT N'Creating Procedure [sHrm].[pEmployeeGet]...';


GO

CREATE PROCEDURE [sPurchase].[pAttachmentLogAdd]
		@rMessageId nvarchar(max),
		@rAttachment nvarchar(max)
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY	
			INSERT INTO sAlert.tAlertAttachmentLog VALUES (@rMessageId, @rAttachment)
		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
	END CATCH
	END
GO
PRINT N'Creating Procedure [sPurchase].[pEventSMSTemplateIdGet]...';


GO

CREATE PROCEDURE [sPurchase].[pEventSMSTemplateIdGet]
		@rOrgId int,
		@rEventId int
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY
			Select SMSTemplateID FROM sAlert.tEventTemplate WHERE EventId = @rOrgId AND OrgId=@rOrgId AND AlertModeId = 2
		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
	END
GO
PRINT N'Creating Procedure [sPurchase].[pEventTemplateGet]...';


GO

CREATE PROCEDURE [sPurchase].[pEventTemplateGet]
		@rOrgId int
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY
			SELECT EventId,Template FROM sAlert.tEventTemplate WHERE IsAutoSend = 1 AND OrgId =@rOrgId AND EventId = 93 AND AlertModeId in (3)
		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
	END
GO
PRINT N'Creating Procedure [sPurchase].[pGetTemplate]...';


GO





--exec [sPurchase].[pGetTemplate] 991100008
CREATE PROCEDURE [sPurchase].[pGetTemplate]
 @rPurchaseOrderId int
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
	DECLARE @HTMLTable NVARCHAR(MAX);

    
-----------------------------------------------------------------------------------------------------------

 --   DECLARE @Column1 NVARCHAR(MAX);
 --   DECLARE @Column2 NVARCHAR(MAX);
 --   DECLARE @Column3 NVARCHAR(MAX);
 --   DECLARE @Column4 NVARCHAR(MAX);

 --   DECLARE TableCursor CURSOR FOR
 --   --SELECT Column1, Column2, Column3, Column3
 --   --FROM YourTable;
	--select 	Top 5   SIM.Code				AS StockItemCode,
	--		   SIM.Title				AS StockItemName,
	--		   SUMa.Code				AS UOM,
	--		   PRD.RequestedQty
	--	from   [sPurchase].[tPurchaseRequestDetail] PRD (nolock)
	--		   Inner JOIN	sPurchase.tPurchaseRequest PR	ON PR.Id = PRD.PurchaseRequestId AND PR.IsObsolete = 0
	--		   Inner JOIN	sIp.tStockItemMaster SIM	ON SIM.Id = PRD.StockItemId 
	--		   Inner JOIN  sFinanceCommon.tStockUomMaster SUMa ON SUMa.Id = SIM.IssueStockUomId
	--	--where  PurchaseRequestId=@rId

 --   OPEN TableCursor;

 --   FETCH NEXT FROM TableCursor INTO @Column1, @Column2, @Column3, @Column4;

 --   WHILE @@FETCH_STATUS = 0
 --   BEGIN
 --       SET @HTMLTable = @HTMLTable + '<tr>';
 --       SET @HTMLTable = @HTMLTable + '<td>' + ISNULL(@Column1, '') + '</td>';
 --       SET @HTMLTable = @HTMLTable + '<td>' + ISNULL(@Column2, '') + '</td>';
 --       SET @HTMLTable = @HTMLTable + '<td>' + ISNULL(@Column3, '') + '</td>';
 --       SET @HTMLTable = @HTMLTable + '<td>' + ISNULL(@Column4, '') + '</td>';
 --       SET @HTMLTable = @HTMLTable + '</tr>';

 --       FETCH NEXT FROM TableCursor INTO @Column1, @Column2, @Column3, @Column4;
 --   END;

 --   CLOSE TableCursor;
 --   DEALLOCATE TableCursor;

 --   SET @HTMLTable = @HTMLTable + '</table>';

	--SET @HTMLTable = '<table border="1">';


	------------------------------------------------------------------------------------------------------------
	Declare
	@rRefNumber  AS NVARCHAR(100),
	@rPurchaseRequestNumber AS NVARCHAR(100),
	@rRequestedBy AS NVARCHAR(100),
	@rPoDate AS NVARCHAR(100),
	@rStatus AS NVARCHAR(100),
	@rVendorCode AS NVARCHAR(100),
	@rVendorName AS NVARCHAR(100),
	@rRequiredBy AS NVARCHAR(100),
	@rBillToAddress AS NVARCHAR(100),
	@rShipToAddress AS NVARCHAR(100),
	@rPurchaseOrderDetails AS NVARCHAR(100)

	    select  @rRefNumber = PO.RefNumber,
				@rPurchaseRequestNumber = PR.Number,
				@rRequestedBy = PO.RequestedBy,
				@rPoDate = PO.PODate,
				@rStatus = ISNULL(SM.Title,''),
				@rVendorCode = VM.Code,
				@rVendorName = VM.Title,
				@rRequiredBy = PO.RequiredBy,
				@rBillToAddress = PO.BillToAddress,
				@rShipToAddress = PO.ShipToAddress
		from   [sPurchase].[tPurchaseOrder] PO(nolock) 
			   Left JOIN sProcurement.tVendorMaster  VM on PO.VendorId = VM.ID
			   Left Join [sWf].[tWfStatusMaster] SM ON SM.Id = PO.StatusId
			   Left Join [sPurchase].[tPurchaseRequest] PR on PR.Id = PO.PurchaseRequestId
		where  PO.Id = @rPurchaseOrderId AND PO.IsObsolete = 0


    SET @HTMLTable = '';
    SET @HTMLTable = @HTMLTable + '<tr>';
    SET @HTMLTable = @HTMLTable + '<th>StockItemCode</th>';
    SET @HTMLTable = @HTMLTable + '<th>StockItemName</th>';
    SET @HTMLTable = @HTMLTable + '<th>Uom</th>';
    SET @HTMLTable = @HTMLTable + '<th>TotalQty</th>';
    SET @HTMLTable = @HTMLTable + '</tr>';

	Select @HTMLTable = @HTMLTable+'<tr><td>'+Cast(SIM.Code as Nvarchar(100))+'</td><td>'
+SIM.Title+'</td><td>'+SUMa.Code+'</td><td>'+Cast(POD.TotalQty as Nvarchar(100))+'</td></tr>'
	  from   [sPurchase].[tPurchaseOrderDetail] POD (nolock)
			   Inner JOIN	sPurchase.tPurchaseOrder PO	ON PO.Id = POD.PurchaseOrderId AND PO.IsObsolete = 0
			   Inner JOIN	sIp.tStockItemMaster SIM	ON SIM.Id = POD.StockItemId 
			   Inner JOIN  sFinanceCommon.tStockUomMaster SUMa ON SUMa.Id = SIM.IssueStockUomId
		where  POD.PurchaseOrderId = @rPurchaseOrderId
-------------------------------------------------------------------------------------------------------
    --PRINT @HTMLTable;

	
--select * from #temp_table
--drop table #temp_table

------------------------------------------------------------------------------------------------------
select REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Template, 
		'{RefNumber}' , @rRefNumber),
		'{PurchaseRequestNumber}' , @rPurchaseRequestNumber),
		'{RequestedBy}' , @rRequestedBy),
		'{PoDate}' , @rPoDate),
		'{Status}' , @rStatus),
		'{VendorCode}' , @rVendorCode),
		'{VendorName}' , @rVendorName),
		'{RequiredBy}' , @rRequiredBy),
		'{BillToAddress}' , @rBillToAddress),
		'{ShipToAddress}' , @rShipToAddress),
		'{PurchaseOrderDetails}' , @HTMLTable)

		as Template from sPurchase.tPurchaseReportTemplate where id=2


	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error

    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  

    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH
END
GO
PRINT N'Creating Procedure [sPurchase].[pGoodsReceivedNoteAdd]...';


GO

PRINT N'Creating Procedure [sPurchase].[pInvoiceGenerateDelete]...';


GO




CREATE PROCEDURE [sPurchase].[pInvoiceGenerateDelete]
		@rId bigint,
		@rStatusId smallint
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
	BEGIN TRANSACTION
		If(@rId > 0)
		Begin
			Update sPurchase.tInvoiceGenerate
			set 
				StatusId = @rStatusId,
				IsObsolete = 1,
				DeletedDateTime = GetUtcDate()
			where Id = @rId and IsObsolete = 0

			if exists(select Id From sPurchase.tInvoiceGenerateDetail where InvoiceGenerateId = @rId)
			Begin
				delete from sPurchase.tInvoiceGenerateDetail
				where InvoiceGenerateId = @rId
			End
			select 1
		End
	COMMIT TRAN -- Transaction Success!	
	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error

    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  

    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH
END
GO
PRINT N'Creating Procedure [sPurchase].[pMenuGet_Purchase]...';


GO
CREATE PROCEDURE [sPurchase].[pMenuGet_Purchase]    
 @rUserId [bigint],    
 @rMenuId int = 0,    
 @rResourceTypeId int= 0
 --,    
 --@rIsCoreMenu bit = 0     
      
WITH EXECUTE AS CALLER    
AS    
SET NOCOUNT ON    
    
BEGIN    
 Declare @rIsCoreMenu int=1
 IF(@rIsCoreMenu=0)    
 BEGIN    
 IF EXISTS(SELECT 1 FROM sSecurity.tPermissionUser WHERE UserId = @rUserId)    
  BEGIN    
   SELECT    
    MenuID,    
    ModuleId,    
    ParentMenuID,    
    MenuTitle,      
    ScreenTitle,       
    PageURL,    
    ScreenId,    
    Permission,    
    OrderNumber,      
    TaskId,      
    PageURL  AS PagePath,    
    IconPath    
        
   FROM    
     (SELECT Menu.Id AS MenuID,     
    ISNULL(Menu.ParentMenuID, 0) AS ParentMenuID,     
    Menu.Title AS MenuTitle,       
    ISNULL(Screen.Title, '') AS ScreenTitle,        
    ISNULL(Screen.Page, '') AS PageURL,    
    ISNULL(Screen.ScreenGroupId,0) AS ScreenGroupId,    
    ISNULL(Menu.ScreenId, 0) AS ScreenId,    
    ISNULL(PermissionValue, 0) AS Permission,    
    ISNULL(OrderNumber, 0) AS OrderNumber,    
    ISNULL(Screen.TaskId,0) AS TaskId,    
    ISNULL(Screen.IconPath,'') As IconPath,     
    ISNULL(T.ResourceTypeId,0) AS ResourceTypeId,    
    ISNULL(T.ModuleId,0) AS ModuleId    
       
       
   FROM sSecurity.tMenu Menu     
   LEFT OUTER JOIN sSecurity.tScreen Screen    
    ON Menu.ScreenId = Screen.Id      
   LEFT OUTER JOIN (     
       SELECT     
        TaskId,     
        SUM(Permission & 2) PermissionValue          
       FROM     
        sSecurity.tPermissionUser P    
        INNER JOIN sSecurity.tTask T ON T.Id = P.TaskId    
        --INNER JOIN sSecuirty.tOrganizationTask OT ON OT.TaskId = T.Id AND OT.OrgGroupId = P.OrgGroupId     
        INNER JOIN sSecurity.tModulePermission MP ON MP.ModuleId = T.ModuleId AND MP.OrgGroupId = P.OrgGroupId    
       WHERE    
        T.IsObsolete = 0 AND P.UserId = @rUserId    
       GROUP BY     
        TaskId) TBL2    
       ON     
        Screen.TaskId = TBL2.TaskId     
         LEFT OUTER JOIN sSecurity.tTask  T ON T.Id =  Screen.TaskId AND ResourceTypeId = @rResourceTypeId    
          
       WHERE     
       (Menu.IsObsolete = 0)  --For ScreenGroupId Master Page    
       AND ISNULL(Screen.IsObsolete, 0) = 0)     
       MenuTable      
       --INNER JOIN sSecurity.tTask  T ON T.Id =  MenuTable.TaskId     
   WHERE     
    MenuTable.ScreenId = 0 OR MenuTable.Permission >= 2     
    AND     
    ((MenuTable.ResourceTypeId = @rResourceTypeId) OR (@rResourceTypeId = 0))    
    AND ((MenuTable.MenuID = @rMenuID)OR(@rMenuID = 0))    
        
   ORDER BY     
    ParentMenuID    
  END    
 ELSE     
  BEGIN    
   SELECT    
    MenuID,    
    ModuleId,    
    ParentMenuID,    
    MenuTitle,      
    ScreenTitle,       
    PageURL,    
    ScreenId,    
    Permission,    
    OrderNumber,      
    TaskId,      
     PageURL  AS PagePath,    
     IconPath    
        
   FROM    
     (SELECT Menu.Id AS MenuID,     
    ISNULL(Menu.ParentMenuID, 0) AS ParentMenuID,     
    Menu.Title AS MenuTitle,       
    ISNULL(Screen.Title, '') AS ScreenTitle,        
    ISNULL(Screen.Page, '') AS PageURL,    
    ISNULL(Screen.ScreenGroupId,0) AS ScreenGroupId,    
    ISNULL(Menu.ScreenId, 0) AS ScreenId,    
    ISNULL(PermissionValue, 0) AS Permission,    
    ISNULL(OrderNumber, 0) AS OrderNumber,    
    ISNULL(Screen.TaskId,0) AS TaskId,    
    ISNULL(Screen.IconPath,'') As IconPath,     
    ISNULL(T.ResourceTypeId,0) AS ResourceTypeId,    
    ISNULL(T.ModuleId,0) AS ModuleId    
       
       
   FROM sSecurity.tMenu Menu     
   LEFT OUTER JOIN sSecurity.tScreen Screen    
    ON Menu.ScreenId = Screen.Id      
   LEFT OUTER JOIN (     
       SELECT     
        TaskId,     
        SUM(Permission & 2) PermissionValue          
       FROM     
        sSecurity.tPermission P    
        INNER JOIN sSecurity.tTask T ON T.Id = P.TaskId    
        --INNER JOIN sSecuirty.tOrganizationTask OT ON OT.TaskId = T.Id AND OT.OrgGroupId = P.OrgGroupId     
        INNER JOIN sSecurity.tModulePermission MP ON MP.ModuleId = T.ModuleId AND MP.OrgGroupId = P.OrgGroupId    
        INNER JOIN    
        (    
        SELECT RoleId FROM sSecurity.tUserRole UR WHERE  UserId = @rUserId AND IsObsolete = 0    
        ) B ON B.RoleId = P.RoleId    
       WHERE    
        T.IsObsolete = 0    
       GROUP BY     
        TaskId) TBL2    
       ON     
        Screen.TaskId = TBL2.TaskId     
         LEFT OUTER JOIN sSecurity.tTask  T ON T.Id =  Screen.TaskId AND ResourceTypeId = @rResourceTypeId    
          
       WHERE     
       (Menu.IsObsolete = 0)  --For ScreenGroupId Master Page    
       AND ISNULL(Screen.IsObsolete, 0) = 0)     
       MenuTable      
       --INNER JOIN sSecurity.tTask  T ON T.Id =  MenuTable.TaskId     
   WHERE     
    MenuTable.ScreenId = 0 OR MenuTable.Permission >= 2     
    AND     
    ((MenuTable.ResourceTypeId = @rResourceTypeId) OR (@rResourceTypeId = 0))    
    AND ((MenuTable.MenuID = @rMenuID)OR(@rMenuID = 0))    
        
   ORDER BY     
    ParentMenuID    
  END    
 END    
 ELSE    
 BEGIN     
 SELECT    
   MenuID,    
   ModuleId,    
   ParentMenuID,    
   MenuTitle,      
   ScreenTitle,       
   PageURL,    
   ScreenId,    
   Permission,    
   OrderNumber,      
   TaskId,      
   PageURL  AS PagePath,    
   MenuIsObsolete,    
   Icon      
  FROM    
  (    

SELECT    
   ISNULL(Menu.Id,0) AS MenuID,     
   ISNULL(Menu.ParentMenuID, 0) AS ParentMenuID,     
   ISNULL(Menu.Title,'') AS MenuTitle,       
   ISNULL(Screen.Title, '') AS ScreenTitle,        
   ISNULL(Screen.Page, '') AS PageURL,    
   ISNULL(Screen.ScreenGroupId,0) AS ScreenGroupId,    
   ISNULL(Screen.Id, 0) AS ScreenId,    
   ISNULL(PermissionValue, 0) AS Permission,    
   ISNULL(OrderNumber, 0) AS OrderNumber,    
   ISNULL(Screen.TaskId,0) AS TaskId,    
   ISNULL(T.ResourceTypeId,0) AS ResourceTypeId,    
   ISNULL(T.ModuleId,0) AS ModuleId,    
   ISNULL(Menu.IsObsolete,1) AS MenuIsObsolete,    
   ISNULL(Screen.IconPath,'') AS Icon    
  FROM     
  sSecurity.tMenu Menu     
  LEFT OUTER JOIN sSecurity.tScreen Screen ON Menu.ScreenId = Screen.Id        
  LEFT OUTER JOIN     
  (    
  SELECT     
   P.TaskId,     
   SUM(OT.AvailablePermission & 2) PermissionValue          
  FROM     
   sSecurity.tPermission P    
   INNER JOIN sSecurity.tTask T ON T.Id = P.TaskId  AND T.IsObsolete = 0  
   INNER JOIN sSecurity.tModulePermission MP ON MP.ModuleId = T.ModuleId      
   INNER JOIN sCommon.tModuleMaster MM ON MP.ModuleId = MM.Id And  MM.IsObsolete = 0    
   INNER JOIN sSecurity.tOrganizationTask OT ON OT.TaskId = T.Id        
   INNER JOIN (SELECT RoleId FROM sSecurity.tUserRole WHERE  UserId = @rUserId AND IsObsolete = 0) UR ON UR.RoleId = P.RoleId     
  GROUP BY P.TaskId    
  ) TBL2 ON Screen.TaskId = TBL2.TaskId     
  LEFT OUTER JOIN sSecurity.tTask  T ON T.Id =  Screen.TaskId AND ResourceTypeId = @rResourceTypeId    
  WHERE (Menu.IsObsolete = 0)    
  AND ISNULL(Screen.IsObsolete, 0) = 0    
  AND (Menu.Id  in (19184028))   
  --Or ParentMenuID  in (24,2711))     
  --AND (
  ----Menu.Id IN (19184027)  
  ----OR ParentMenuID IN (55)
  --)  
  

  UNION
  

  SELECT     
  
   ISNULL(Menu.Id, 0) AS MenuID,     
   ISNULL(Menu.Id, 0) AS ParentMenuID,     
   ISNULL(Screen1.Title,'') AS MenuTitle,       
   ISNULL(Screen1.Title, '') AS ScreenTitle,        
   ISNULL(Screen1.Page, '')  AS PageURL,    
   ISNULL(Screen1.ScreenGroupId,0) AS ScreenGroupId,    
   ISNULL(Screen1.Id, 0) AS ScreenId,    
   ISNULL(PermissionValue, 0) AS Permission,    
   ISNULL(OrderNumber, 0) AS OrderNumber,    
   ISNULL(Screen1.TaskId,0) AS TaskId,    
   ISNULL(T.ResourceTypeId,0) AS ResourceTypeId,    
   ISNULL(T.ModuleId,0) AS ModuleId,    
   ISNULL(Menu.IsObsolete,1) AS MenuIsObsolete,    
   ISNULL(Screen1.IconPath,'') AS Icon    
  FROM     
   sSecurity.tMenu Menu     
   LEFT OUTER JOIN sSecurity.tScreen Screen ON Menu.ScreenId = Screen.Id        
   LEFT OUTER JOIN sSecurity.tScreen Screen1 ON Screen1.ScreenGroupId = Screen.ScreenGroupId     
   LEFT OUTER JOIN sSecurity.tTask  T ON T.Id =  Screen1.TaskId    
   LEFT OUTER JOIN (     
  SELECT     
   P.TaskId,     
   SUM(OT.AvailablePermission & 2) PermissionValue          
  FROM     
   sSecurity.tPermission P    
   INNER JOIN sSecurity.tTask T ON T.Id = P.TaskId   AND T.IsObsolete = 0  
   INNER JOIN sSecurity.tModulePermission MP ON MP.ModuleId = T.ModuleId        
   INNER JOIN sCommon.tModuleMaster MM ON MP.ModuleId = MM.Id And  MM.IsObsolete = 0    
   INNER JOIN sSecurity.tOrganizationTask OT ON OT.TaskId = T.Id       
   INNER JOIN (SELECT RoleId FROM sSecurity.tUserRole WHERE  UserId = @rUserId AND IsObsolete = 0) UR ON UR.RoleId = P.RoleId    
            
  GROUP BY P.TaskId    
  ) TBL2 ON Screen1.TaskId = TBL2.TaskId    
  WHERE (Menu.IsObsolete = 0) AND ISNULL(Screen1.IsObsolete, 0) = 0    
  --AND (Menu.ParentMenuID  in (24,2711)   
  --or Menu.Id  in (24,2711))   
    
  AND Screen1.Id IN (31296,31297,31298,31299,31300,31301,31310)

  ) MenuTable      
  WHERE     
  --(MenuTable.ScreenId = 0 OR MenuTable.Permission >= 2)  
  --AND   
  ((MenuTable.ResourceTypeId = @rResourceTypeId) OR (@rResourceTypeId = 0))    
  AND ((MenuTable.MenuID = @rMenuID)OR(@rMenuID = 0))    
  AND MenuIsObsolete = 0    
  AND MenuTable.MenuId IN (19184028)   
  AND ModuleId IN (96)
  ORDER BY ParentMenuID  
 END   
END
GO
PRINT N'Creating Procedure [sPurchase].[pMobileNotitficationTitleGet]...';


GO

CREATE PROCEDURE [sPurchase].[pMobileNotitficationTitleGet]
		@rEventId int
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY	
			SELECT Title FROM sAlert.tEventMaster WHERE Id= @rEventId
		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
	END
GO
PRINT N'Creating Procedure [sPurchase].[pOrgGroupIdGet]...';


GO

CREATE PROCEDURE [sPurchase].[pOrgGroupIdGet]
		@rOrgId int
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY
			SELECT OrgGroupId FROM sSetup.tOrganizationMaster WHERE Id = @rOrgId
		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
	END
GO
PRINT N'Creating Procedure [sPurchase].[pPurchaseScreenGet]...';


GO

CREATE PROCEDURE [sPurchase].[pPurchaseScreenGet]
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
begin
	BEGIN TRY
		SELECT	
			S.Id			AS ScreenId,
			S.Title		AS ScreenTitle,
			S.TaskId		AS TaskId,	
			S.Page		As PageUrl,
			S.IsObsolete	AS ScreenIsObsolete,	
			S.Title,		
			S.Page,
			SG.Id AS ScreenGroupId,
			ISNULL(SG.Title,'') AS ScreenGroupName
		
		FROM 
			sSecurity.tScreen S (nolock)
		LEFT JOIN sSecurity.tScreenGroupMaster SG ON SG.Id=S.ScreenGroupId
		WHERE 
			S.IsObsolete = 0 and S.ScreenGroupID=254 and S.Id in (31296,31297,31298,31299,31300,31301,31310)
	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH
end
GO
PRINT N'Creating Procedure [sPurchase].[pQuotationVendorGet]...';


GO

 
--exec [sPurchase].[pQuotationVendorGet] 991100010
CREATE PROCEDURE [sPurchase].[pQuotationVendorGet]
	
		 @rQuotationRequestId bigint
		 --,
	 	-- @rTemplate nvarchar(max)
	 	 
	WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
BEGIN

	Declare
		@rDescription  AS NVARCHAR(1000),
		@rStartDate AS NVARCHAR(100),
		@rEndDate AS NVARCHAR(100),
		@rBidOpen AS NVARCHAR(100),
		@rBidClose AS NVARCHAR(100),
		@rPersonContact AS NVARCHAR(100),
		@rEmail AS NVARCHAR(100),
		@rRFQDate AS NVARCHAR(100)

	    select  @rDescription = ISNULL(QR.Description,''),
				@rStartDate = ISNULL(QR.StartDate,''),
				@rEndDate = ISNULL(QR.EndDate,''),
				@rBidOpen = ISNULL(QR.BidOpen,''),
				@rBidClose = ISNULL(QR.BidClose,''),
				@rPersonContact = ISNULL(QR.PersonContact,''),
				@rEmail = ISNULL(QR.Email,''),
				@rRFQDate = ISNULL(QR.RFQDate,'')
		from   [sPurchase].[tQuotationRequest] QR(nolock) 
			   Left JOIN sProcurement.tVendorMaster  VM on QR.VendorId = VM.ID
			   Left Join [sWf].[tWfStatusMaster] SM ON SM.Id = QR.StatusId
			   Left Join [sPurchase].[tPurchaseRequest] PR on PR.Id = QR.PurchaseRequestId
		where  QR.Id = @rQuotationRequestId AND QR.IsObsolete = 0

	DECLARE @HTMLTable NVARCHAR(MAX);
	SET @HTMLTable = '';
    SET @HTMLTable = @HTMLTable + '<tr>';
    SET @HTMLTable = @HTMLTable + '<th>StockItemCode</th>';
    SET @HTMLTable = @HTMLTable + '<th>StockItemName</th>';
    SET @HTMLTable = @HTMLTable + '<th>Uom</th>';
    SET @HTMLTable = @HTMLTable + '<th>TotalQty</th>';
    SET @HTMLTable = @HTMLTable + '<th>TargetPrice</th>';
    SET @HTMLTable = @HTMLTable + '<th>Remark</th>';
    SET @HTMLTable = @HTMLTable + '</tr>';

	Select @HTMLTable = @HTMLTable+'<tr><td>'+Cast(SIM.Code as Nvarchar(100))+'</td><td>'
	+SIM.Title+'</td><td>'+SUMa.Code+'</td><td>'+Cast(QRD.RequestedQty as Nvarchar(100))+
	+'</td><td>'+Cast(QRD.TargetPrice as Nvarchar(100))+'</td><td>'+QRD.Remark+'</td></tr>'
	  from   [sPurchase].[tQuotationRequestDetail] QRD (nolock)
		Inner JOIN	sPurchase.tQuotationRequest QR	ON QR.Id = QRD.QuotationRequestId AND QR.IsObsolete = 0
		Inner JOIN	sIp.tStockItemMaster SIM	ON SIM.Id = QRD.StockItemId 
		Inner JOIN  sFinanceCommon.tStockUomMaster SUMa ON SUMa.Id = SIM.IssueStockUomId
		where  QRD.QuotationRequestId = @rQuotationRequestId
    --PRINT @HTMLTable;
	declare @txtMessage nvarchar(Max)
    select @txtMessage=REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Template, 
		'{Description}' , @rDescription),
		'{StartDate}' , @rStartDate),
		'{EndDate}' , @rEndDate),
		'{BidOpen}' , @rBidOpen),
		'{BidClose}' , @rBidClose),
		'{PersonContact}' , @rPersonContact),
		'{Email}' , @rEmail),
		'{RFQDate}' , @rRFQDate),
		'{QuatationDetails}' , @HTMLTable)from sPurchase.tPurchaseReportTemplate where id = 1

		Declare @VendorId bigint
		Select @VendorId = QR.VendorId from sPurchase.tQuotationRequest QR Where QR.Id = @rQuotationRequestId AND QR.IsObsolete = 0
	
	SELECT 
			--A.AcademicId,
			A.Code,
			A.Name,
			A.ParentContactNo AS MobileNo,	
			A.ParentEmail AS EmailId,
			@txtMessage	AS MsgText,
			'' AS Subject,
			 2001 AS ReceiverSourceTypeId,
			 (SELECT Id FROM sSetup.tOrganizationMaster where OrgGroupId=A.OrgGroupId and Id = A.OrgGroupId) AS OrgId,
			 A.OrgGroupId AS OrgGroupId,
			 --A.AcademicId AS ReceiverSourceId,
			 A.Id AS SourceId,
			 2001 AS SourceTypeId,
			 --DM.Title AS MeetingType,
			 --CONVERT(varchar(11),ISNULL(OM.StartDate,'') ,103) AS StartDate,
			 --CONVERT(varchar(11),ISNULL(OM.EndDate,'') ,103) AS EndDate,
			 --EE.EmployeeName AS Teacher,
			 --OM.Title AS Title,
			 A.WhatsAppContactNo
	FROM 
		 [sPurchase].[tQuotationRequestDetail] QRD (nolock)
		Inner JOIN	sPurchase.tQuotationRequest QR	ON QR.Id = QRD.QuotationRequestId AND QR.IsObsolete = 0
		Inner JOIN	sIp.tStockItemMaster SIM	ON SIM.Id = QRD.StockItemId 
		Inner JOIN  sFinanceCommon.tStockUomMaster SUMa ON SUMa.Id = SIM.IssueStockUomId
		INNER JOIN (
			SELECT	VM.Id,
					VM.ContactPersonName as Name,
					VM.Code,
					VM.PhoneNumberOffice as ParentContactNo,
					VM.Email as ParentEmail,
					VM.OrgGroupId,
					VM.PhoneNumberMobile WhatsAppContactNo		
			FROM	sProcurement.tVendorMaster  VM
			LEFT JOIN   sProcurement.tVendorCategoryMaster VC
			ON  VM.VendorCategoryId = VC.Id
			WHERE VM.Id = @VendorId
		) as A ON A.Id = QR.VendorId
		where  QRD.QuotationRequestId = @rQuotationRequestId
END
GO
PRINT N'Creating Procedure [sPurchase].[pStockItemMasterGetWithCategory]...';


GO

--exec [sPurchase].[pStockItemMasterGetWithCategory]  9911,991100001,null,null,0,0,991100001
CREATE PROCEDURE [sPurchase].[pStockItemMasterGetWithCategory]
    @rOrgGroupId int,
	@rId [int] = 0,
	@rCode [nvarchar](100),  
	@rTitle [nvarchar](100),  
	@rIsAbsoluteTitle [bit] = 0,
	@rIsWildCardSearch [bit] = 1,
	@rStoreLocationId int = 0
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
IF @rIsAbsoluteTitle = 0
	BEGIN
		IF @rIsWildCardSearch = 1
			BEGIN
				SET @rCode = sCommon.fSearchStringGet(@rCode)
				SET @rTitle = sCommon.fSearchStringGet(@rTitle)
			END
	END
 


	SELECT 	SIM.Id,
			SIM.Code,
			--CAST(SIM.StockItemTypeId AS INT) AS StockItemTypeId,
			SIM.Title,
			CAST(ISNULL(sIp.LastPurchasePrice, 0) AS DECIMAL(22, 2)) LastPurchasePrice, 
			SIM.CreatedUserId,
			SIM.CreatedDateTime,
			SIM.UpdatedUserId,
			SIM.UpdatedDateTime,
			--SIT.Title AS StockItemTypeTitle,
			--SIT.Code AS StockItemTypeCode,
			SIM.IssueStockUomId,
			SU.Code UomCode,
			Su.Title UomTitle,
			SIM.IsBatchWiseTracking,
			SCM.Id as CategoryId,
			SCM.Code as CategoryName
			
	   FROM	sIp.tStockItemMaster SIM
		INNER JOIN sIp.tStockItemPlanning SIP ON SIP.StockItemId=SIM.Id	

	 --  INNER JOIN sPurchase.tStockItemTypeMaster SIT
		--ON SIM.StockItemTypeId = SIT.Id
	   LEFT OUTER JOIN sFinanceCommon.tStockUomMaster SU
		ON SU.Id = SIM.IssueStockUomId
	   LEFT JOIN [sIp].[tStockItemCategoryMaster] SCM ON SCM.Id = SIM.StockItemCategoryId
	   WHERE((SIM.Id = @rId) OR (@rId = 0)) 
		AND	((SIM.Code LIKE @rCode) OR (ISNULL(@rCode, '') = ''))
		AND	(((SIM.Title LIKE @rTitle) AND @rIsAbsoluteTitle = 0) OR (@rTitle = '' AND @rIsAbsoluteTitle = 0) OR ((SIM.Title = @rTitle ) AND @rIsAbsoluteTitle = 1))
		AND SIM.OrgGroupId = @rOrgGroupId
		AND ((sIp.StoreLocationId = @rStoreLocationId) OR (@rStoreLocationId = 0)) 
		and SIM.IsObsolete= 0
		
END
GO
PRINT N'Creating Procedure [sPurchase].[pStudentProfileGet]...';


GO

CREATE PROCEDURE [sPurchase].[pStudentProfileGet]
@rAcademicId bigint,
@rbatchId int
AS
SET NOCOUNT ON
begin
	BEGIN TRY
		SELECT  A.OrgId, 
				BatchId, 
				StudentId, 
				A.Id AS AcademicId, 
				SM.Code, 
				A.RollNo, 
				(FirstName +' '+MiddleName+' '+LastName) AS StudentName,
				SM.FirstName, 
				SM.MiddleName, 
				SM.LastName, 
				A.ClassId, 
				A.DivisionId, 
				(CM.DisplayTitle +'-'+ DM.DisplayTitle) AS ClassDivision,
				DPM.Id AS DepartmentId, 
				DPM.DisplayTitle AS DepartmentName, 
				SM.SUID, B.Title as BatchName
		FROM    sStudent.tAcademic A INNER JOIN sStudent.tStudentMaster SM ON A.StudentId=SM.Id
				INNER JOIN sSetup.tClassMaster CM ON CM.Id=A.ClassId INNER JOIN sSetup.tDivisionMaster DM ON DM.Id = A.DivisionId
				INNER JOIN sSetup.tDepartmentMaster DPM ON DPM.Id = CM.DepartmentId
				INNER JOIN sSetup.tBatchMaster B ON b.Id = A.BatchId
		WHERE A.Id=@rAcademicId AND BatchId =@rbatchId;
	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH
end
GO
PRINT N'Creating Procedure [sPurchase].[pValidateCloudStorage]...';


GO



CREATE PROCEDURE [sPurchase].[pValidateCloudStorage]
	  @rOrgGroupId smallint
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
		 SELECT ISNULL(SUM(UD.DocumentSize),0) 
		 FROM sCommon.tUploadDocument UD (nolock)
		 INNER JOIN sSetup.tOrgGroupBatchMaster OGBM 
		 ON OGBM.Id = UD.OrgGroupBatchId 
		 WHERE OGBM.OrgGroupId = @rOrgGroupId
	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error
    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  
    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH
END
GO
PRINT N'Creating Procedure [sPurchase].[pWhatsAppTemplateGet]...';


GO

CREATE PROCEDURE [sPurchase].[pWhatsAppTemplateGet]
		@rEventId int,
		@rOrgId int
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON
	BEGIN
		BEGIN TRY	
			SELECT Template,WhatsAppTemplate,WhatsAppTemplateLanguage FROM sAlert.tEventTemplate WHERE EventId = @rEventId AND OrgId = @rOrgId AND AlertModeId = 4
		END TRY
		BEGIN CATCH 
		ROLLBACK TRANSACTION
		SELECT -1 AS Result
	END CATCH
	END
GO
PRINT N'Creating Procedure [sTimetable].[pDeleteTimeTable]...';


GO

PRINT N'Creating Procedure [sPurchase].[pQuotationRequestAdd]...';


GO


CREATE PROCEDURE [sPurchase].[pQuotationRequestAdd]
		@rId bigint,
		@rOrgGroupId smallint,
		@rBranchId int,
		@rLocationId int,
		@rPurchaseRequestId bigint,
		@rName varchar(100),
		@rRFQDate datetime,
		@rDescription nvarchar(300),
		@rStartDate datetime,
		@rEndDate datetime,
		@rBidOpen nvarchar(100),
		@rBidClose nvarchar(100),
		@rPersonContact bigint,
		@rEmail nvarchar(100),
		@rSendToVendor bit,
		@rStatusId smallint,
		@rVendorId int,
		@rUserId bigint,
		@rDetailList sPurchase.QuotationRequestDetailType READONLY
 
WITH EXECUTE AS CALLER
AS
SET NOCOUNT ON

BEGIN
	BEGIN TRY
	BEGIN TRANSACTION
		If(@rId > 0)
		Begin
			Update sPurchase.tQuotationRequest
			set
				OrgGroupId =@rOrgGroupId,
				BranchId =@rBranchId,
				LocationId =@rLocationId,
				PurchaseRequestId =@rPurchaseRequestId,
				Name =@rName,
				RFQDate =@rRFQDate,
				Description =@rDescription,
				StartDate =@rStartDate,
				EndDate =@rEndDate,
				BidOpen =@rBidOpen,
				BidClose =@rBidClose,
				PersonContact =@rPersonContact,
				Email =@rEmail,
				SendToVendor =@rSendToVendor,
				StatusId =@rStatusId,
				VendorId =@rVendorId,
				UpdatedUserId = @rUserId,		
				UpdatedDateTime = GetUtcDate()
			where Id = @rId and IsObsolete = 0

			delete from sPurchase.tQuotationRequestDetail
			where QuotationRequestId = @rId

			DECLARE @lNextNumberQRDA INT	 
			EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sPurchase.tQuotationRequestDetail',@lNextNumber = @lNextNumberQRDA output
			SET @lNextNumberQRDA=@lNextNumberQRDA-1
			INSERT INTO sPurchase.tQuotationRequestDetail
			(
				Id,
				OrgGroupId,
				QuotationRequestId,
				PurchaseRequestDetailId,
				StockItemId,
				RequestedQty,
				Remark,
				TargetPrice,
				CreatedUserId,
				CreatedDateTime
			)
			select	@lNextNumberQRDA+Id,
				    @rOrgGroupId,
					@rId,
					PurchaseRequestDetailId, 
					StockItemId,
					RequestedQty,
					Remark,
					TargetPrice,
					@rUserId, 
					GETUTCDATE() 
			from @rDetailList
			set @lNextNumberQRDA=@lNextNumberQRDA+(select Count(Id) from @rDetailList)
			EXECUTE sCommon.pTableRowIdModify @rOrgGroupId, @rTableTitle = 'sPurchase.tQuotationRequestDetail', @rCurrentNumber = @lNextNumberQRDA

			select @rId
		End
		Else
		Begin
			DECLARE @lNextNumber INT	 
			EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sPurchase.tQuotationRequest',@lNextNumber = @lNextNumber output	
			-------------select @lNextNumber
			--DECLARE @loNextNumber nVarchar(20)
			--EXECUTE sCommon.pPrefixSetupNextNumberGet @rOrgId = @rOrgGroupId, @rPrefixFunctionalityId = 1005,@rStoreLocationId = @rLocationId,
			-------------@rNextNumber = @loNextNumber OUTPUT

	 		INSERT INTO sPurchase.tQuotationRequest
			(
				Id,
				Number,
				OrgGroupId,
				BranchId,
				LocationId,
				PurchaseRequestId,
				Name,
				RFQDate,
				Description,
				StartDate,
				EndDate,
				BidOpen,
				BidClose,
				PersonContact,
				Email,
				SendToVendor,
				StatusId,
				VendorId,
				CreatedUserId,		
				CreatedDateTime
	
			)
			VALUES
			(
				@lNextNumber,
				@lNextNumber,
				@rOrgGroupId,
				@rBranchId ,
				@rLocationId,
				@rPurchaseRequestId,
				@rName,
				@rRFQDate,
				@rDescription,
				@rStartDate,
				@rEndDate ,
				@rBidOpen ,
				@rBidClose,
				@rPersonContact,
				@rEmail,
				@rSendToVendor,
				@rStatusId,
				@rVendorId ,
				@rUserId,		
				GETUTCDATE()
			)
 
			set @rId = @lNextNumber 

			DECLARE @lNextNumberQRD INT	 
			EXECUTE [sCommon].[pGetIntMaxIdForOrgGroup] @rOrgGroupId, 'sPurchase.tQuotationRequestDetail',@lNextNumber = @lNextNumberQRD output
			SET @lNextNumberQRD=@lNextNumberQRD+1
	 		INSERT INTO sPurchase.tQuotationRequestDetail
			(
				Id,
				OrgGroupId,
				QuotationRequestId,
				PurchaseRequestDetailId,
				StockItemId,
				RequestedQty,
				Remark,
				TargetPrice,
				CreatedUserId,
				CreatedDateTime
			)
			select	@lNextNumberQRD+Id,
				    @rOrgGroupId,
					@rId,
					PurchaseRequestDetailId, 
					StockItemId,
					RequestedQty,
					Remark,
					TargetPrice,
					@rUserId, 
					GETUTCDATE() 
			from @rDetailList
			set @lNextNumberQRD=@lNextNumberQRD+(select Count(Id) from @rDetailList)
			EXECUTE sCommon.pTableRowIdModify @rOrgGroupId, @rTableTitle = 'sPurchase.tQuotationRequestDetail', @rCurrentNumber = @lNextNumberQRD

			
			select @rId

			Declare @typeId smallint=0
			Declare @Level smallint=0
			SELECT @typeId=WfTypeMasterId, @Level=WfLevel FROM sWf.tWfSetup WHERE WfFunctionalityId = 2
			IF(@typeId = 1)--parallel
			BEGIN
				exec [sWf].[pWfApprovalAdd] 0,@rId,2041,@rUserId,1,'',0,0,2,@rOrgGroupId
			END
			ELSE IF(@typeId = 2)--sequencial
			Begin
				exec [sWf].[pWfApprovalAdd] 0,@rId,2041,@rUserId,2,'',1,@Level,2,@rOrgGroupId
			End
			ELSE IF(@typeId = 3)--auto approved
			BEGIN
				exec [sWf].[pWfApprovalAdd] 0,@rId,2041,@rUserId,3,'',0,0,2,@rOrgGroupId
			END
			--select 1;
		End
	COMMIT TRAN -- Transaction Success!	
	END TRY

	BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN --RollBack in case of Error

    
    DECLARE @ErrorMessage NVARCHAR(4000);  
    DECLARE @ErrorSeverity INT;  
    DECLARE @ErrorState INT;  

    SELECT   
       @ErrorMessage = ERROR_MESSAGE(),  
       @ErrorSeverity = ERROR_SEVERITY(),  
       @ErrorState = ERROR_STATE();  

    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);  
    
	END CATCH
END
GO

GO
PRINT N'Refreshing Procedure [sPurchase].[pGoodsReceivedNoteDelete]...';


GO
EXECUTE sp_refreshsqlmodule N'[sPurchase].[pGoodsReceivedNoteDelete]';


GO
PRINT N'Refreshing Procedure [sPurchase].[pQualityCheckDelete]...';


GO
EXECUTE sp_refreshsqlmodule N'[sPurchase].[pQualityCheckDelete]';


GO
PRINT N'Refreshing Procedure [sPurchase].[pQuotationRequestDelete]...';


GO
EXECUTE sp_refreshsqlmodule N'[sPurchase].[pQuotationRequestDelete]';


GO
PRINT N'Refreshing Procedure [sWf].[pWfApprovalAdd]...';


GO
EXECUTE sp_refreshsqlmodule N'[sWf].[pWfApprovalAdd]';


GO
PRINT N'Refreshing Procedure [sWf].[pWfApprovalGet]...';


GO
EXECUTE sp_refreshsqlmodule N'[sWf].[pWfApprovalGet]';


GO
PRINT N'Refreshing Procedure [sPurchase].[pSalesReturnDelete]...';


GO
EXECUTE sp_refreshsqlmodule N'[sPurchase].[pSalesReturnDelete]';

GO