SourceCode/PROMS/VEPROMS User Interface/ROBuild.Sql

/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/

--/****   DBA FUNCTION  ****/
--/*  The following can be used as a basis for creating the sql RO database for storing of referenced object for PROMS */
--/*  Note that the default sql database name will be generated in during conversion as the current PROMS database name suffixed with '_RO'. */
--/*    In the script below, the [VE_PROMS_DB_RO] should be replaced with the name that the RO database should be given */
--/*	In the script below, the pathname/filename of the physical database should be modified also. */
--USE [master]
--GO
--/****** Object:  Database [VEPROMS_DB_RO]    Script Date: 7/23/2019 9:42:54 AM ******/
--CREATE DATABASE [VEPROMS_DB_RO]
-- ON  PRIMARY 
--( NAME = N'VEPROMS_DB_RO', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS_2012\MSSQL\DATA\VEPROMS_DB_RO.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
-- LOG ON 
--( NAME = N'VEPROMS_DB_RO_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS_2012\MSSQL\DATA\VEPROMS_DB_RO_log.ldf' , SIZE = 2816KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
--GO
--ALTER DATABASE [VEPROMS_DB_RO] SET COMPATIBILITY_LEVEL = 110
--GO
--ALTER DATABASE [VEPROMS_DB_RO] set TRUSTWORTHY ON; 
--GO 
--USE VEPROMS_DB_RO
--GO
--EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false 
--GO 
--sp_configure 'show advanced options', 1; 
--GO 
--RECONFIGURE; 
--GO 
--sp_configure 'clr enabled', 1; 
--GO 
--RECONFIGURE; 
--GO
--IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
--begin
--EXEC [VEPROMS_DB_RO].[dbo].[sp_fulltext_database] @action = 'enable'
--end
--GO
--USE [VEPROMS_DB_RO]
--GO
--/****   END OF DBA FUNCTION ****/


-- AFTER THE DATABASE IS CREATED, the following should be executed to create the ROALL table & create the sql stored procedures that */
--  are used by PROMS, to migrate and access the ro data.
if db_name() in('master','model','msdn','tempdb')
begin
	DECLARE @ErrorMsg varchar(255)
	SET @ErrorMsg = 'Don''t add these procedures and functions to ' + db_name()
	PRINT '=========================================================================='
	PRINT ''
	PRINT @ErrorMsg
	PRINT ''
	PRINT 'You probably want to be in the VEPROMS database'
	PRINT ''
	PRINT '=========================================================================='
	RAISERROR (@ErrorMsg, 20, -1) with log
	RETURN
end
print 'Adding procedures and functions to ' + db_name()
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  StoredProcedure [dbo].[vlnErrorHandler] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[vlnErrorHandler]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [vlnErrorHandler];
GO
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[vlnErrorHandler]
	(@ExpectedCount int=-1
	,@MessageFormat nvarchar(512)=N'Expected RowCount (%d) not met (%d)')
WITH EXECUTE AS OWNER
AS
BEGIN
	DECLARE @ErrorMessage NVARCHAR(4000),	@ErrorNumber INT, @ErrorSeverity INT, @ErrorState INT, @ErrorProcedure NVARCHAR(126)
		, @ErrorLine INT, @RowCount INT;
	SELECT @ErrorMessage = ERROR_MESSAGE(),	@ErrorSeverity = ERROR_SEVERITY(),	@ErrorState = ERROR_STATE(),@ErrorNumber = ERROR_NUMBER()
		, @ErrorLine = ERROR_LINE(), @ErrorProcedure = ERROR_PROCEDURE(), @RowCount = @@RowCount;
	IF @ErrorNumber > 0
		BEGIN
			IF @ErrorProcedure = OBJECT_NAME(@@PROCID) -- If the Procedure is the current procedure just pass the error message
				RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState)
			ELSE -- Add in the procedure name and line as well as the error number
				RAISERROR (N'%s[%d] - (%d) %s', @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorNumber, @ErrorMessage)
		END
	ELSE IF @ExpectedCount <> -1 AND @ExpectedCount <> @RowCount
		RAISERROR (@MessageFormat, 16, 1, @ExpectedCount, @RowCount)
END
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: vlnErrorHandler Succeeded'
ELSE PRINT 'Procedure Creation: vlnErrorHandler Error on Creation'
GO

/****** Object:  StoredProcedure [dbo].[deleteByParid] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteByParid]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [deleteByParid];
GO
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[deleteByParid]
(
	@ROTable char(8),
	@ParentID char(8)
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	DELETE ROALL 
		WHERE [ROTable] = @ROTable and [ParentID] = @ParentID
	IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
	IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
 	ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit.  Top level will rollback
	EXEC vlnErrorHandler
END CATCH
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteByParid Succeeded'
ELSE PRINT 'Procedure Creation: deleteByParid Error on Creation'
GO

/****** Object:  StoredProcedure [dbo].[deleteByRecid] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteByRecid]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [deleteByRecid];
GO
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[deleteByRecid]
(
	@ROTable char(8),
	@RecID char(8)
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	DELETE ROALL 
		WHERE [ROTable] = @ROTable and [RecID] = @RecID
	IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
	IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
 	ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit.  Top level will rollback
	EXEC vlnErrorHandler
END CATCH
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteByRecid Succeeded'
ELSE PRINT 'Procedure Creation: deleteByRecid Error on Creation'
GO

/****** Object:  StoredProcedure [dbo].[deleteByROTable] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[deleteByROTable]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [deleteByROTable];
GO
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[deleteByROTable]
(
	@ROTable char(8)
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	DELETE ROALL 
		WHERE [ROTable] = @ROTable
	IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
	IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
 	ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit.  Top level will rollback
	EXEC vlnErrorHandler
END CATCH
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: deleteByROTable Succeeded'
ELSE PRINT 'Procedure Creation: deleteByROTable Error on Creation'
GO

/****** Object:  StoredProcedure [dbo].[getChildData] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getChildData]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [getChildData];
GO
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getChildData]

(
	@ROTable char(8),
	@ParentID char(8)
)
WITH EXECUTE AS OWNER
AS
	SELECT RecID,RecType, isnull(AccPageID, '') AccPageID, Info 
	FROM [ROALL]
	where ROTable = @ROTable and (RecType = 3 or RecType = 5) and ParentID=@ParentID ORDER BY RecID ASC
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getChildData Succeeded'
ELSE PRINT 'Procedure Creation: getChildData Error on Creation'
GO

/****** Object:  StoredProcedure [dbo].[getCountChildrenOfChildData] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getCountChildrenOfChildData]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [getCountChildrenOfChildData];
GO
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getCountChildrenOfChildData]
(
	@ROTable char(8),
	@ParentID char(8)
)
WITH EXECUTE AS OWNER
AS
	SELECT R1.ParentID,COUNT(*) AS CNT 
	FROM ROALL R1 INNER JOIN ROALL R2 ON R1.ParentID = R2.RecID 
	WHERE (R1.RecType = 3 or R1.RecType = 5) and R1.ROTable = @ROTable and R2.ROTable = @ROTable and R2.ParentID = @ParentID group by R1.ParentID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getCountChildrenOfChildData Succeeded'
ELSE PRINT 'Procedure Creation: getCountChildrenOfChildData Error on Creation'
GO

/****** Object:  StoredProcedure [dbo].[getCountRecidByAccid] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getCountRecidByAccid]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [getCountRecidByAccid];
GO
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getCountRecidByAccid]
(
	@ROTable char(8),
	@AccPageID char(32)
)
WITH EXECUTE AS OWNER
AS
	SELECT COUNT (RecID)
	FROM [ROALL]
	WHERE [ROTable]=@ROTable AND AccPageID=@AccPageID	
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getCountRecidByAccid Succeeded'
ELSE PRINT 'Procedure Creation: getCountRecidByAccid Error on Creation'
GO

/****** Object:  StoredProcedure [dbo].[getCountRecidByAccidNotRecid] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getCountRecidByAccidNotRecid]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [getCountRecidByAccidNotRecid];
GO
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getCountRecidByAccidNotRecid]
(
	@ROTable char(8),
	@RecID char(8),
	@AccPageID char(32)
)
WITH EXECUTE AS OWNER
AS
	SELECT COUNT (RecID)
	FROM [ROALL]
	WHERE [ROTable]=@ROTable AND AccPageID=@AccPageID AND (NOT RecID = @RecID)
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getCountRecidByAccidNotRecid Succeeded'
ELSE PRINT 'Procedure Creation: getCountRecidByAccidNotRecid Error on Creation'
GO

/****** Object:  StoredProcedure [dbo].[getCountRecidByParid] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getCountRecidByParid]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [getCountRecidByParid];
GO
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getCountRecidByParid]

(
	@ROTable char(8),
	@ParentID char(8)
)
WITH EXECUTE AS OWNER
AS
	SELECT COUNT (RecID)
	FROM [ROALL]
	WHERE [ROTable]=@ROTable AND [ParentID]=@ParentID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getCountRecidByParid Succeeded'
ELSE PRINT 'Procedure Creation: getCountRecidByParid Error on Creation'
GO

/****** Object:  StoredProcedure [dbo].[getCountRectypeByRectype] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getCountRectypeByRectype]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [getCountRectypeByRectype];
GO	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getCountRectypeByRectype]
(
	@ROTable char(8),
	@RecType char(32)
)
WITH EXECUTE AS OWNER
AS
	SELECT COUNT (RecType)
	FROM [ROALL]
	WHERE [ROTable]=@ROTable AND RecType=@RecType	
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getCountRectypeByRectype Succeeded'
ELSE PRINT 'Procedure Creation: getCountRectypeByRectype Error on Creation'
GO

/****** Object:  StoredProcedure [dbo].[getGroupAndSubgroups] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getGroupAndSubgroups]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [getGroupAndSubgroups];
GO
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getGroupAndSubgroups]

(
	@ROTable char(8)
)
WITH EXECUTE AS OWNER
AS
	SELECT RecID, RecType, ParentID, isnull(AccPageID, '') AccPageID, Info
	FROM [ROALL]
	WHERE [ROTable]=@ROTable AND (RecType = 3 or RecType = 5) AND ParentID <> '00000002' 
	ORDER BY ParentID,RecID ASC
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getGroupAndSubgroups Succeeded'
ELSE PRINT 'Procedure Creation: getGroupAndSubgroups Error on Creation'
GO

/****** Object:  StoredProcedure [dbo].[getInfoByParidRectype] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getInfoByParidRectype]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [getInfoByParidRectype];
GO
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getInfoByParidRectype]

(
	@ROTable char(8),
	@ParentID char(8),
	@RecType int
)
WITH EXECUTE AS OWNER
AS
	SELECT [Info]
	FROM [ROALL]
	WHERE [ROTable]=@ROTable AND [ParentID]=@ParentID AND [RecType]=@RecType
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getInfoByParidRectype Succeeded'
ELSE PRINT 'Procedure Creation: getInfoByParidRectype Error on Creation'
GO

/****** Object:  StoredProcedure [dbo].[getInfoByRecid] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getInfoByRecid]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [getInfoByRecid];
GO	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getInfoByRecid]
(
	@ROTable char(8),
	@RecID char(8)
)
WITH EXECUTE AS OWNER
AS
	SELECT [Info]
	FROM [ROALL]
	WHERE [ROTable]=@ROTable AND [RecID]=@RecID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getInfoByRecid Succeeded'
ELSE PRINT 'Procedure Creation: getInfoByRecid Error on Creation'
GO

/****** Object:  StoredProcedure [dbo].[getInfoByRecidRectype] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getInfoByRecidRectype]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [getInfoByRecidRectype];
GO	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getInfoByRecidRectype]
(
	@ROTable char(8),
	@RecID char(8),
	@RecType int
)
WITH EXECUTE AS OWNER
AS
	SELECT [Info]
	FROM [ROALL]
	WHERE [ROTable]=@ROTable AND [RecID]=@RecID AND [RecType]=@RecType
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getInfoByRecidRectype Succeeded'
ELSE PRINT 'Procedure Creation: getInfoByRecidRectype Error on Creation'
GO

/****** Object:  StoredProcedure [dbo].[getParidAccidInfoByRecid] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getParidAccidInfoByRecid]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [getParidAccidInfoByRecid];
GO
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getParidAccidInfoByRecid]
(
	@ROTable char(8),
	@RecId char(8)
)
WITH EXECUTE AS OWNER
AS
	SELECT [ParentID], [AccPageID], [Info]
	FROM [ROALL]
	WHERE [ROTable]=@ROTable AND [RecID]=@RecId
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getParidAccidInfoByRecid Succeeded'
ELSE PRINT 'Procedure Creation: getParidAccidInfoByRecid Error on Creation'
GO

/****** Object:  StoredProcedure [dbo].[getParidAccidInfoByRectypeParid] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getParidAccidInfoByRectypeParid]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [getParidAccidInfoByRectypeParid];
GO	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getParidAccidInfoByRectypeParid]
(
	@ROTable char(8),
	@RecType int,
	@ParentID char(8)
)
WITH EXECUTE AS OWNER
AS
	SELECT [ParentID], [AccPageID], [Info]
	FROM [ROALL]
	WHERE [ROTable]=@ROTable AND [RecType]=@RecType AND [ParentID]= @ParentID
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getParidAccidInfoByRectypeParid Succeeded'
ELSE PRINT 'Procedure Creation: getParidAccidInfoByRectypeParid Error on Creation'
GO

/****** Object:  StoredProcedure [dbo].[getRecidByRectypeInfo] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRecidByRectypeInfo]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [getRecidByRectypeInfo];
GO	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getRecidByRectypeInfo]
(
	@ROTable char(8),
	@RecType int,
	@mstrecid nvarchar(max)
)
WITH EXECUTE AS OWNER
AS
	SELECT [RecID]
	FROM [ROALL]
	WHERE [ROTable]=@ROTable AND [RecType]=@RecType AND Info like @mstrecid
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRecidByRectypeInfo Succeeded'
ELSE PRINT 'Procedure Creation: getRecidByRectypeInfo Error on Creation'
GO

/****** Object:  StoredProcedure [dbo].[getRecidInfoByRectype] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRecidInfoByRectype]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [getRecidInfoByRectype];
GO		
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getRecidInfoByRectype]
(
	@ROTable char(8),
	@RecType int
)
WITH EXECUTE AS OWNER
AS
	SELECT [RecID], [Info]
	FROM [ROALL]
	WHERE [ROTable]=@ROTable AND [RecType]=@RecType
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRecidInfoByRectype Succeeded'
ELSE PRINT 'Procedure Creation: getRecidInfoByRectype Error on Creation'
GO

/****** Object:  StoredProcedure [dbo].[getRecidInfoByRectypeAsc] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRecidInfoByRectypeAsc]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [getRecidInfoByRectypeAsc];
GO		
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getRecidInfoByRectypeAsc]
(
	@ROTable char(8),
	@RecType int
)
WITH EXECUTE AS OWNER
AS
	SELECT [RecID], [Info]
	FROM [ROALL]
	WHERE [ROTable]=@ROTable AND [RecType]=@RecType
	ORDER BY RecID ASC
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRecidInfoByRectypeAsc Succeeded'
ELSE PRINT 'Procedure Creation: getRecidInfoByRectypeAsc Error on Creation'
GO

/****** Object:  StoredProcedure [dbo].[getRecidInfoByRectypeParidAsc] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[getRecidInfoByRectypeParidAsc]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [getRecidInfoByRectypeParidAsc];
GO	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[getRecidInfoByRectypeParidAsc]
(
	@ROTable char(8),
	@RecType int,
	@ParentID char(8)
)
WITH EXECUTE AS OWNER
AS
	SELECT [RecID], [Info]
	FROM [ROALL]
	WHERE [ROTable]=@ROTable AND [RecType]=@RecType AND [ParentID]=@ParentID
	ORDER BY RecID ASC
	RETURN
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: getRecidInfoByRectypeParidAsc Succeeded'
ELSE PRINT 'Procedure Creation: getRecidInfoByRectypeParidAsc Error on Creation'
GO

/****** Object:  StoredProcedure [dbo].[insertAllRectypes] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[insertAllRectypes]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [insertAllRectypes];
GO	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[insertAllRectypes]
(
	@ROTable char(8),
	@RecID char(8),
	@RecType int,
	@ParentID char(8),
	@AccPageID char(32),
	@Info nvarchar(max),
	@ModDateTime char(14)
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	INSERT INTO [ROALL] 
	(
		ROTable, 
		RecID, 
		RecType, 
		ParentID, 
		ModDateTime, 
		AccPageID, 
		Info
	) 
	VALUES
	(
		@ROTable, 
		@RecID, 
		@RecType, 
		@ParentID, 
		@ModDateTime, 
		@AccPageID,  
		@Info
	)

	IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
	IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
 	ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit.  Top level will rollback
	EXEC vlnErrorHandler
END CATCH
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: insertAllRectypes Succeeded'
ELSE PRINT 'Procedure Creation: insertAllRectypes Error on Creation'
GO

/****** Object:  StoredProcedure [dbo].[updateInfoAccidByRecid] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateInfoAccidByRecid]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [updateInfoAccidByRecid];
GO	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[updateInfoAccidByRecid]
(
	@ROTable char(8),
	@RecID char(8),
	@AccPageID char(32),
	@Info nvarchar(max),
	@ModDateTime char(14),
	@ParentID varchar(8) = NULL
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	UPDATE [ROALL]
		SET
			[Info]=@Info,
			[ModDateTime]=@ModDateTime,
			[AccPageID]=@AccPageID,
			[ParentID]=ISNULL(@ParentID, ParentID)
		WHERE [ROTable]=@ROTable AND [RecID]=@RecID
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [ROALL] WHERE [ROTable]=@ROTable AND [RecID]=@RecID)
			RAISERROR('ROALL record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('ROALL has been edited by another user', 16, 1)            	
	END

	IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
	IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
 	ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit.  Top level will rollback
	EXEC vlnErrorHandler
END CATCH
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateInfoAccidByRecid Succeeded'
ELSE PRINT 'Procedure Creation: updateInfoAccidByRecid Error on Creation'
GO

/****** Object:  StoredProcedure [dbo].[updateInfoByRecid] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateInfoByRecid]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [updateInfoByRecid];
GO	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[updateInfoByRecid]
(
	@ROTable char(8),
	@RecID char(8),
	@Info nvarchar(max),
	@ModDateTime char(14)
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	UPDATE [ROALL]
		SET
			[Info]=@Info,
			[ModDateTime]=@ModDateTime
		WHERE [ROTable]=@ROTable AND [RecID]=@RecID
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [ROALL] WHERE [ROTable]=@ROTable AND [RecID]=@RecID)
			RAISERROR('ROALL record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('ROALL has been edited by another user', 16, 1)            	
	END

	IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
	IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
 	ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit.  Top level will rollback
	EXEC vlnErrorHandler
END CATCH
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateInfoByRecid Succeeded'
ELSE PRINT 'Procedure Creation: updateInfoByRecid Error on Creation'
GO

/****** Object:  StoredProcedure [dbo].[updateInfoByRecidRectype] ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[updateInfoByRecidRectype]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE [updateInfoByRecidRectype];
GO	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE PROCEDURE [dbo].[updateInfoByRecidRectype]
(
	@ROTable char(8),
	@RecID char(8),
	@Info nvarchar(max),
	@ModDateTime char(14),
	@RecType int
)
WITH EXECUTE AS OWNER
AS
BEGIN TRY -- Try Block
	BEGIN TRANSACTION
	UPDATE [ROALL]
		SET
			[Info]=@Info,
			[ModDateTime]=@ModDateTime
		WHERE [ROTable]=@ROTable AND [RecID]=@RecID AND [RecType]=@RecType
	IF @@ROWCOUNT = 0
	BEGIN
		IF NOT exists(select * from [ROALL] WHERE [ROTable]=@ROTable AND [RecID]=@RecID AND [RecType]=@RecType)
			RAISERROR('ROALL record has been deleted by another user', 16, 1)
		ELSE           	
			RAISERROR('ROALL has been edited by another user', 16, 1)            	
	END

	IF( @@TRANCOUNT > 0 ) COMMIT
END TRY
BEGIN CATCH -- Catch Block
	IF( @@TRANCOUNT = 1 ) ROLLBACK -- Only rollback if top level
 	ELSE IF( @@TRANCOUNT > 1 ) COMMIT -- Otherwise commit.  Top level will rollback
	EXEC vlnErrorHandler
END CATCH
GO
-- Display the status of Proc creation
IF (@@Error = 0) PRINT 'Procedure Creation: updateInfoByRecidRectype Succeeded'
ELSE PRINT 'Procedure Creation: updateInfoByRecidRectype Error on Creation'
GO
	
/****** Object:  Table [dbo].[ROALL] ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ROALL]') AND type in (N'U'))
DROP TABLE [dbo].[ROALL]
GO	
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2019 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
CREATE TABLE [dbo].[ROALL](
	[ROTable] [char](8) NOT NULL,
	[RecID] [char](8) NOT NULL,
	[RecType] [int] NOT NULL,
	[ParentID] [char](8) NOT NULL,
	[AccPageID] [char](32) NULL,
	[ModDateTime] [char](14) NOT NULL,
	[Info] [nvarchar](max) NULL,
 CONSTRAINT [PK_ROMASTER] PRIMARY KEY CLUSTERED 
(
	[ROTable] ASC,
	[RecID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-- Display the status of Table creation
IF (@@Error = 0) PRINT 'Table Creation: ROALL Succeeded'
ELSE PRINT 'Table Creation: DeleteLog ROALL on Creation'
GO