if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_BusinessGapReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_BusinessGapReport]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_BusinessGapReport_Criteria]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_BusinessGapReport_Criteria]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_DeleteAnswers_b]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_DeleteAnswers_b]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GetReportDepartments]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_GetReportDepartments]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GetReportRegulations]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_GetReportRegulations]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GetUserAnswers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_GetUserAnswers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GetUserAnswers_new]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_GetUserAnswers_new]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GetUserQuestionParent]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_GetUserQuestionParent]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GetUserQuestions]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_GetUserQuestions]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GetUserQuestions_HHP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_GetUserQuestions_HHP]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GetUserQuestions_total]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_GetUserQuestions_total]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GetUserQuestions_v3]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_GetUserQuestions_v3]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GetUsersDepts]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_GetUsersDepts]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GetUsersDepts_o]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_GetUsersDepts_o]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_GetUsersDepts_tr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_GetUsersDepts_tr]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_Regulations]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_Regulations]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_ReturnCompletetionValues]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_ReturnCompletetionValues]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_ReturnCompletetionValues_m]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_ReturnCompletetionValues_m]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_ReturnCompletetionValues_manager]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_ReturnCompletetionValues_manager]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_UpdateAnswers_b]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_UpdateAnswers_b]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_Answers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_Answers]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_Business_Ab]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_Business_Ab]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_Business_Q]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_Business_Q]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_Comment]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_Comment]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_Dep_Area]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_Dep_Area]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_Depts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_Depts]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_Documents]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_Documents]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_FProject]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_FProject]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_Master]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_Master]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_Org]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_Org]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_Org_Dep]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_Org_Dep]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_Project]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_Project]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_Project_Document]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_Project_Document]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_Project_functions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_Project_functions]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_Q_OSHPD]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_Q_OSHPD]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_Q_REG]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_Q_REG]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_Q_Resp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_Q_Resp]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_Questions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_Questions]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_Regulations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_Regulations]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_Response]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_Response]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_Track]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_Track]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_Training]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_Training]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_User]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_User]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HG_User_Depts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[HG_User_Depts]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Projects]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Projects]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SUBREGULATION]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SUBREGULATION]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Standard_Jobs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Standard_Jobs]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Standard_Project]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Standard_Project]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Standard_dep]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Standard_dep]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Users]
GO

CREATE TABLE [dbo].[HG_Answers] (
	[Ans_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Master_ID] [int] NULL ,
	[Q_ID] [int] NULL ,
	[Parent_ID] [int] NULL ,
	[Law_fl] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Dep_ID] [int] NULL ,
	[User_ID] [int] NULL ,
	[A_text] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_Polarity] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[A_Location] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_Pending_Date] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_Priority] [int] NULL ,
	[A_Date_submit] [smalldatetime] NULL ,
	[A_Verified] [bit] NOT NULL ,
	[A_Ver_user_ID] [int] NULL ,
	[A_Date_Verified] [smalldatetime] NULL ,
	[Comment_ID] [int] NULL ,
	[Creation_date] [smalldatetime] NULL ,
	[Created_by] [int] NULL ,
	[Last_Update_Date] [smalldatetime] NULL ,
	[Last_Updated_By] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HG_Business_Ab] (
	[QID] [int] IDENTITY (1, 1) NOT NULL ,
	[Q_PARENT_ID] [int] NULL ,
	[Q_SELF_ID] [int] NULL ,
	[Q_ANSWER] [int] NULL ,
	[Q_USER_ID] [int] NULL ,
	[Q_DEPT_ID] [int] NULL ,
	[Q_CREATE_DATE] [smalldatetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HG_Business_Q] (
	[Q_ID] [int] NULL ,
	[Q_Number] [int] NULL ,
	[Q_Parent_ID] [int] NULL ,
	[Q_Text] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Q_Type_ID] [int] NULL ,
	[Q_Version] [int] NULL ,
	[Creation_Date] [smalldatetime] NULL ,
	[Q_Polarity] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[HG_Comment] (
	[Comment_ID] [int] NOT NULL ,
	[User_ID] [int] NULL ,
	[Aproval_ID] [int] NULL ,
	[Dept_ID] [int] NULL ,
	[Q_ID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Comment] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Comment_Date] [smalldatetime] NULL ,
	[Comment_type] [int] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[HG_Dep_Area] (
	[Dep_Area_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Dep_ID] [int] NULL ,
	[Q_Law_Key] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Dep_delite_fl] [bit] NOT NULL ,
	[Creation_date] [smalldatetime] NULL ,
	[Created_by] [int] NULL ,
	[Last_Update_Date] [smalldatetime] NULL ,
	[Last_Updated_By] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HG_Depts] (
	[Dep_ID] [int] NULL ,
	[Dep_Code] [int] NULL ,
	[Dep_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Dep_Manager] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Dep_Email] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Dep_Phone] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Dep_Ext] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Status] [bit] NOT NULL ,
	[Creation_date] [smalldatetime] NULL ,
	[Created_by] [int] NULL ,
	[Last_Update_Date] [smalldatetime] NULL ,
	[Last_Updated_By] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HG_Documents] (
	[Document_id] [int] NOT NULL ,
	[Document_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Document_type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Server_path] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Client_path] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Document_status] [int] NULL ,
	[Checkout_by] [int] NULL ,
	[Checkout_date] [datetime] NULL ,
	[Created_by] [int] NULL ,
	[Created_date] [datetime] NULL ,
	[Updated_by] [int] NULL ,
	[Updated_date] [datetime] NULL ,
	[Last_read_by] [int] NULL ,
	[Last_read_date] [datetime] NULL ,
	[FS_size] [int] NULL ,
	[FS_mtime] [datetime] NULL ,
	[FS_atime] [datetime] NULL ,
	[FS_ctime] [datetime] NULL ,
	[FS_full_path] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FS_attribute] [int] NULL ,
	[FS_ax_user] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Document_Specifications] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HG_FProject] (
	[Project_id] [int] NULL ,
	[Project_Name] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Created_date] [datetime] NULL ,
	[Created_by] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HG_Master] (
	[Master_ID] [int] NOT NULL ,
	[System_name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Org_abr] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[System_Full_name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Creation_date] [smalldatetime] NULL ,
	[Created_by] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HG_Org] (
	[Org_ID] [int] NOT NULL ,
	[Org_Code] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Org_Abr] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Status] [bit] NOT NULL ,
	[Org_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Region_ID] [int] NULL ,
	[Region] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[System_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Org_Coordinator] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Org_Email] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Org_Phone] [float] NULL ,
	[Org_Ext] [int] NULL ,
	[Org_Address] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Org_City] [nvarchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Org_zip] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Creation_date] [smalldatetime] NULL ,
	[Created_by] [int] NULL ,
	[Last_Update_Date] [smalldatetime] NULL ,
	[Last_Updated_By] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HG_Org_Dep] (
	[Org_Dep_ID] [int] NOT NULL ,
	[Org_ID] [int] NULL ,
	[Dep_ID] [int] NULL ,
	[Dep_delite_fl] [bit] NOT NULL ,
	[Creation_date] [smalldatetime] NULL ,
	[Created_by] [int] NULL ,
	[Last_Update_Date] [smalldatetime] NULL ,
	[Last_Updated_By] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HG_Project] (
	[Project_ID] [int] NOT NULL ,
	[Mng_ID] [int] NULL ,
	[Dep_ID] [int] NULL ,
	[Org_ID] [int] NULL ,
	[Creation_date] [smalldatetime] NULL ,
	[Created_by] [int] NULL ,
	[Last_Update_Date] [smalldatetime] NULL ,
	[Last_Updated_By] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HG_Project_Document] (
	[Project_Document_id] [int] NOT NULL ,
	[Project_id] [int] NULL ,
	[Document_id] [int] NULL ,
	[Created_by] [int] NULL ,
	[Created_date] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HG_Project_functions] (
	[Project_func_ID] [int] NOT NULL ,
	[Project_Function] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Eval_Flag] [bit] NOT NULL ,
	[Mng_Flag] [bit] NOT NULL ,
	[Legal_Flag] [bit] NOT NULL ,
	[Plain_Flag] [bit] NOT NULL ,
	[Bus_Flag] [bit] NOT NULL ,
	[Admin_Flag] [bit] NOT NULL ,
	[Creation_date] [smalldatetime] NULL ,
	[Created_by] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HG_Q_OSHPD] (
	[ID] [int] NOT NULL ,
	[Q_ID] [int] NULL ,
	[OSHPD] [int] NULL ,
	[Q_Number] [int] NULL ,
	[Creation_date] [smalldatetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HG_Q_REG] (
	[Q_Reg_ID] [int] NOT NULL ,
	[Q_ID] [int] NULL ,
	[Reg_ID] [int] NULL ,
	[Q_Regulation] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[N_Subr] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Reg_code] [int] NULL ,
	[Subreg_code] [int] NULL ,
	[Creation_date] [smalldatetime] NULL ,
	[Created_by] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HG_Q_Resp] (
	[ID] [int] NOT NULL ,
	[Q_ID] [int] NULL ,
	[R_ID] [int] NULL ,
	[Creation_date] [smalldatetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HG_Questions] (
	[Q_Law_Key] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Q_ID] [int] NULL ,
	[Q_Parent_ID] [int] NOT NULL ,
	[temp] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Q_legal_txt] [varchar] (2200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Q_pl_txt] [varchar] (2200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Q_bus_txt] [varchar] (2200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Q_clinic_txt] [varchar] (2200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Q_phys_txt] [varchar] (2200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Q_Reg_ID] [int] NULL ,
	[Q_Reg] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Q_Reg_Title] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Q_Explanation] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Q_Subs] [bit] NOT NULL ,
	[Q_Polarity] [bit] NOT NULL ,
	[Creation_date] [smalldatetime] NULL ,
	[Created_by] [int] NULL ,
	[Last_Update_Date] [smalldatetime] NULL ,
	[Last_Updated_By] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HG_Regulations] (
	[Reg_id] [int] NULL ,
	[Section] [int] NULL ,
	[Law_Key] [nvarchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Section_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Sub_Part_name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Regulation_number] [int] NULL ,
	[T_Regulation] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Regulation_Name] [nvarchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Creation_date] [smalldatetime] NULL ,
	[Created_by] [int] NULL ,
	[Last_Update_Date] [smalldatetime] NULL ,
	[Last_Updated_By] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HG_Response] (
	[R_ID] [int] NOT NULL ,
	[R_Number] [int] NULL ,
	[R_text] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Creation_date] [smalldatetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HG_Track] (
	[Track_ID] [int] NOT NULL ,
	[name] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[email] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[phone] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Problem] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Manager] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[home_dep] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[home_ent] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[work_dep] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[work_ent] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[page] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[section] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[action] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Problem_Date] [smalldatetime] NULL ,
	[Creation_date] [smalldatetime] NULL ,
	[Created_by] [int] NULL ,
	[Last_Update_Date] [smalldatetime] NULL ,
	[Last_Updated_By] [int] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[HG_Training] (
	[Training_ID] [int] NOT NULL ,
	[User_ID] [int] NULL ,
	[Org_ID] [int] NULL ,
	[Training_Flag] [int] NOT NULL ,
	[Review_Flag] [int] NOT NULL ,
	[Creation_Date] [datetime] NULL ,
	[Created_by] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HG_User] (
	[User_ID] [int] NOT NULL ,
	[SystemUserID] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[fname] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[lname] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Full_Name] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Eval_flag] [bit] NOT NULL ,
	[Mng_Flag] [bit] NOT NULL ,
	[Legal_Flag] [bit] NOT NULL ,
	[Plain_Flag] [bit] NOT NULL ,
	[Bus_Flag] [bit] NOT NULL ,
	[Sys_Admin] [bit] NOT NULL ,
	[Entity_ID] [int] NULL ,
	[Dep_code] [int] NULL ,
	[Dep_ID] [int] NULL ,
	[Position] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Job_Func] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Phone] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Ext] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Email] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Region] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Status] [bit] NOT NULL ,
	[Creation_date] [smalldatetime] NULL ,
	[Created_by] [int] NULL ,
	[Last_Update_Date] [smalldatetime] NULL ,
	[Last_Updated_By] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[HG_User_Depts] (
	[User_Dep_ID] [int] NOT NULL ,
	[User_ID] [int] NULL ,
	[Org_ID] [int] NULL ,
	[Dep_ID] [int] NULL ,
	[Creation_date] [smalldatetime] NULL ,
	[Created_by] [int] NULL ,
	[Last_Update_Date] [smalldatetime] NULL ,
	[Last_Updated_By] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Projects] (
	[ProjectID] [int] NOT NULL ,
	[ProjectName] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ParentID] [int] NOT NULL ,
	[SiteID] [int] NOT NULL ,
	[Manager] [int] NULL ,
	[DateCreated] [datetime] NULL ,
	[LastUpdate] [datetime] NULL ,
	[Template] [int] NULL ,
	[PRJ_PERIOD] [int] NULL ,
	[PRJ_STS] [nchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FROM_DATE] [datetime] NULL ,
	[TO_DATE] [datetime] NULL ,
	[PROJECT_TYPE] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TREE_IDS] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TREENAME] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ref1] [int] NULL ,
	[ref2] [int] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[SUBREGULATION] (
	[REG_COD] [int] NOT NULL ,
	[SUBREG_COD] [int] NOT NULL ,
	[SUBREG_NAM] [nchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[SUBREG_DSC] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LINKNAME] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[URL] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TOP_ID] [int] NULL ,
	[REQ_COD] [int] NULL ,
	[LAST_UPDATE] [datetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Standard_Jobs] (
	[St_Job_ID] [int] NOT NULL ,
	[ST_Job_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[St_Job_Delete_fl] [bit] NOT NULL ,
	[Creation_date] [smalldatetime] NULL ,
	[Created_by] [int] NULL ,
	[Last_Update_Date] [smalldatetime] NULL ,
	[Last_Updated_By] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Standard_Project] (
	[Res_Project_ID] [int] NOT NULL ,
	[Project_name] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Law_section] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Creation_date] [datetime] NULL ,
	[Created_by] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Standard_dep] (
	[St_Dep_Code] [int] NULL ,
	[St_Dep_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[St_Dep_Delete_fl] [bit] NOT NULL ,
	[Creation_date] [smalldatetime] NULL ,
	[Created_by] [int] NULL ,
	[Last_Update_Date] [smalldatetime] NULL ,
	[Last_Updated_By] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Users] (
	[UserID] [int] NOT NULL ,
	[UserName] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[FirstName] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LastName] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Company] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Password] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[UserLevel] [int] NULL ,
	[Address1] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Address2] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[City] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Zip] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[WorkPhone] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[HomePhone] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Fax] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Title] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Email] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[SiteID] [int] NULL ,
	[LastUpdate] [datetime] NULL ,
	[Status] [int] NULL ,
	[notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Context] [int] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.usp_BusinessGapReport
AS
SELECT     HG_Business_Ab.Q_DEPT_ID, HG_Regulations.Regulation_number, COUNT(HG_Business_Ab.Q_SELF_ID) AS SUM_QUESTION
FROM         HG_Regulation_Composite INNER JOIN
                      HG_Regulations ON HG_Regulation_Composite.REGQ_REGULATION = HG_Regulations.Section_ID INNER JOIN
                      HG_Business_Ab ON HG_Regulation_Composite.REGQ_QUESTION = HG_Business_Ab.QID
GROUP BY HG_Regulations.Regulation_number, HG_Business_Ab.Q_DEPT_ID
HAVING      (COUNT(HG_Business_Ab.Q_SELF_ID) IN
                          (SELECT     HG_Business_Q.Q_ID
                            FROM          HG_Regulations INNER JOIN
                                                   HG_Regulation_Composite ON HG_Regulations.Section_ID = HG_Regulation_Composite.REGQ_REGULATION INNER JOIN
                                                   HG_Business_Q ON HG_Regulation_Composite.REGQ_QUESTION = HG_Business_Q.Q_ID
 --                           WHERE      (HG_Business_Q.Q_Polarity = N'P')
))
ORDER BY HG_Business_Ab.Q_DEPT_ID, HG_Regulations.Regulation_number

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.usp_BusinessGapReport_Criteria
AS
SELECT DISTINCT HG_Business_Ab.Q_DEPT_ID, HG_Regulations.Regulation_number, COUNT(HG_Business_Ab.Q_SELF_ID) AS SUM_QUESTION
FROM         HG_Regulation_Composite INNER JOIN
                      HG_Regulations ON HG_Regulation_Composite.REGQ_REGULATION = HG_Regulations.Section_ID INNER JOIN
                      HG_Business_Ab ON HG_Regulation_Composite.REGQ_QUESTION = HG_Business_Ab.QID
WHERE     (HG_Business_Ab.Q_ANSWER = 75) OR
                      (HG_Business_Ab.Q_ANSWER = 133)
GROUP BY HG_Regulations.Regulation_number, HG_Business_Ab.Q_DEPT_ID
HAVING      (COUNT(HG_Business_Ab.Q_SELF_ID) IN
                          (SELECT     HG_Business_Q.Q_ID
                            FROM          HG_Regulations INNER JOIN
                                                   HG_Regulation_Composite ON HG_Regulations.Section_ID = HG_Regulation_Composite.REGQ_REGULATION INNER JOIN
                                                   HG_Business_Q ON HG_Regulation_Composite.REGQ_QUESTION = HG_Business_Q.Q_ID
                            WHERE      (HG_Business_Q.Q_Polarity = N'P')))
ORDER BY HG_Business_Ab.Q_DEPT_ID, HG_Regulations.Regulation_number

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.usp_DeleteAnswers_b
@SELF_ID Int,
@ANSWER_ID Int,
@USER_ID Int,
@DEPT_ID Int
AS
-- Delete existing values for this question first
Delete From  HG_Business_Ab
Where  Q_SELF_ID = @SELF_ID 
 AND Q_USER_ID = @USER_ID
 AND Q_DEPT_ID = @DEPT_ID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.usp_GetReportDepartments
@DEPT_LIST Varchar(300)
AS
Declare @SQL nvarchar(2000)
Set @SQL = 'SELECT     Org_ID, Org_Name, Org_Abr  '
Set @SQL = @SQL + 'FROM         HG_Org '
Set @SQL = @SQL + 'WHERE     (Org_ID IN ('+@DEPT_LIST+'))'
-- Print @SQL
exec sp_executesql @SQL

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.usp_GetReportRegulations
AS
SELECT DISTINCT HG_Regulations.Regulation_number
FROM         HG_Regulations INNER JOIN
                      HG_Regulation_Composite ON HG_Regulations.Section_ID = HG_Regulation_Composite.REGQ_REGULATION INNER JOIN
                      HG_Business_Q ON HG_Regulation_Composite.REGQ_QUESTION = HG_Business_Q.Q_ID
--WHERE     (HG_Business_Q.Q_Polarity = N'P')
/*SELECT DISTINCT HG_Regulations.Regulation_number,r_law_key
FROM         HG_Regulations INNER JOIN
                      HG_Regulation_Composite ON HG_Regulations.Section_ID = HG_Regulation_Composite.REGQ_REGULATION INNER JOIN
                      HG_Business_Q ON HG_Regulation_Composite.REGQ_QUESTION = HG_Business_Q.Q_ID
--WHERE     (HG_Business_Q.Q_Polarity = N'P')
order by r_law_key
*/

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.usp_GetUserAnswers
@USER_ID Int,
@DEPT_ID Int
AS
SELECT     HG_Business_Ab.Q_SELF_ID, HG_Business_Ab.Q_ANSWER, HGCommentTBL.HGComment
FROM         HG_Business_Ab  LEFT OUTER JOIN
                      HGCommentTBL ON HG_Business_Ab.Q_SELF_ID = HGCommentTBL.HGQ_ID AND HG_Business_Ab.Q_USER_ID = HGCommentTBL.HGUser_ID AND 
                      HG_Business_Ab.Q_DEPT_ID = HGCommentTBL.HGDepartment_ID
WHERE     (HG_Business_Ab.Q_USER_ID = @USER_ID) AND (HG_Business_Ab.Q_DEPT_ID = @DEPT_ID)
ORDER BY HG_Business_Ab.QID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.usp_GetUserAnswers_new
@USER_ID Int,
@DEPT_ID Int
AS
SELECT     HG_Business_Ab.Q_SELF_ID, HG_Business_Ab.Q_ANSWER,
HG_Comment.Comment, HG_Comment.Q_ID
FROM         HG_Business_Ab FULL OUTER JOIN
                      HG_Comment ON HG_Business_Ab.Q_SELF_ID =
HG_Comment.Q_ID AND HG_Business_Ab.Q_USER_ID =
HG_Comment.User_ID AND 
                      HG_Business_Ab.Q_DEPT_ID =
HG_Comment.Dept_ID
WHERE     (HG_Business_Ab.Q_USER_ID = @USER_ID) AND
(HG_Business_Ab.Q_DEPT_ID = @DEPT_ID)
OR        (HG_Comment.User_ID =  @USER_ID) AND
(HG_Comment.Dept_ID = @DEPT_ID)
ORDER BY HG_Business_Ab.QID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.usp_GetUserQuestionParent
@QUESTION Int,
@DEPT_ID Int,
@USER_ID Int
AS
SELECT Q_ANSWER,R_TEXT  FROM HG_BUSINESS_AB
INNER JOIN HG_RESPONSE ON
 HG_RESPONSE.R_ID=HG_BUSINESS_AB.Q_ANSWER
 WHERE Q_SELF_ID=@QUESTION AND 
Q_USER_ID=@USER_ID AND Q_DEPT_ID=@DEPT_ID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.usp_GetUserQuestions
@DEPT_ID Int
AS
SELECT     HG_Business_Q.Q_ID, HG_Business_Q.Q_Number,
HG_Business_Q.Q_Parent_ID, HG_Business_Q.Q_Text,HG_Business_Q.Q_Type_ID 
FROM         HG_Business_Q 
INNER JOIN HG_Q_OSHPD ON HG_Business_Q.Q_ID=HG_Q_OSHPD.Q_ID
WHERE     HG_Q_OSHPD.OSHPD IN ((SELECT DEP_CODE FROM HG_DEPTS WHERE DEP_ID=@DEPT_ID),0)
ORDER BY  HG_Business_Q.Q_ID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.usp_GetUserQuestions_HHP
@DEPT_ID Int
AS
SELECT     HG_Business_HHP.Q_ID
FROM         HG_Business_HHP 
INNER JOIN HG_Q_CODE_HHP ON HG_Business_HHP.Q_ID=HG_Q_CODE_HHP.Q_ID
WHERE     HG_Q_CODE_HHP.CODE  in ((SELECT DEP_CODE FROM HG_DEPTS WHERE DEP_ID=@DEPT_ID),0)
ORDER BY  HG_Business_HHP.Q_ID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.usp_GetUserQuestions_total
@DEPT_ID Int
AS
SELECT     count(HG_Business_Q.Q_ID)
FROM         HG_Business_Q 
INNER JOIN HG_Q_OSHPD ON HG_Business_Q.Q_ID=HG_Q_OSHPD.Q_ID
WHERE     HG_Q_OSHPD.OSHPD IN ((SELECT DEP_CODE FROM HG_DEPTS WHERE DEP_ID=@DEPT_ID),0)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.usp_GetUserQuestions_v3
@DEPT_ID Int,
@Q_START Int,
@RECNUMBER Int
AS
SELECT     HG_Business_Q.Q_ID, HG_Q_OSHPD.Q_Number,
HG_Business_Q.Q_Parent_ID, HG_Business_Q.Q_Text,
HG_Business_Q.Q_Type_ID, 
                      HG_Response.R_text, HG_Response.R_ID
FROM         HG_Response INNER JOIN
                      HG_Q_Resp ON HG_Response.R_ID = HG_Q_Resp.R_ID
RIGHT OUTER JOIN
                      HG_Business_Q ON HG_Q_Resp.Q_ID =
HG_Business_Q.Q_ID
INNER JOIN HG_Q_OSHPD ON HG_Business_Q.Q_ID=HG_Q_OSHPD.Q_ID
WHERE     HG_Q_OSHPD.OSHPD IN ((SELECT DEP_CODE FROM HG_DEPTS WHERE DEP_ID=@DEPT_ID),0)
and    HG_Q_OSHPD.Q_Number between (@Q_START) and (@Q_START +@RECNUMBER) 
ORDER BY  HG_Q_OSHPD.Q_Number,HG_Response.R_number  DESC

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.usp_GetUsersDepts
@ARRAY_INPUT VarChar(200)
AS
Declare @SQL nvarchar(2000)
Set @SQL = 'SELECT HG_Depts.Dep_Name, HG_User.User_ID, HG_User.FName, HG_User.LName, HG_User.Full_Name, HG_User_Depts.Dep_ID, HG_Depts.Dep_Code  '
Set @SQL = @SQL + 'FROM HG_User INNER JOIN '
Set @SQL = @SQL + 'HG_User_Depts ON HG_User.User_ID = HG_User_Depts.User_ID INNER JOIN '
Set @SQL = @SQL + 'HG_Depts ON HG_User_Depts.Dep_ID = HG_Depts.Dep_ID '
Set @SQL = @SQL + ' WHERE  HG_Depts.Dep_ID IN (' + @ARRAY_INPUT+ ') '
Set @SQL = @SQL + 'ORDER BY HG_Depts.Dep_Name, HG_User.Full_Name '
-- Print @SQL
exec sp_executesql @SQL

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.usp_GetUsersDepts_o
@ENTITY Int
AS
SELECT     HG_Depts.Dep_Name, HG_User.User_ID, HG_User.FName, HG_User.LName,
HG_User.Full_Name, HG_User_Depts.Dep_ID,Position,Email
FROM         HG_User INNER JOIN
                      HG_User_Depts ON HG_User.User_ID =
HG_User_Depts.User_ID INNER JOIN
                      HG_Depts ON HG_User_Depts.Dep_ID = HG_Depts.Dep_ID
WHERE     (HG_User.Entity_ID = @ENTITY)
ORDER BY HG_Depts.Dep_Name, HG_User.Full_Name

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE usp_GetUsersDepts_tr
@ENTITY Int
AS
/*
SELECT     HG_Depts.Dep_Name, HG_User.User_ID, HG_User.FName, HG_User.LName,
HG_User.Full_Name, HG_User_Depts.Dep_ID,Position,Email,TRAINING_FLAG,REVIEW_FLAG
FROM HG_User 
INNER JOIN HG_User_Depts ON HG_User.User_ID = HG_User_Depts.User_ID 
INNER JOIN HG_Depts ON HG_User_Depts.Dep_ID = HG_Depts.Dep_ID
FULL JOIN HG_TRAINING ON HG_User.User_ID = HG_TRAINING.USER_ID
WHERE     (HG_User.Entity_ID = @ENTITY)
ORDER BY HG_Depts.Dep_Name, HG_User.Full_Name
SELECT     HG_Depts.Dep_Name, HG_User.User_ID, HG_User.FName, HG_User.LName,
HG_User.Full_Name, HG_Depts.Dep_ID,Position,Email,TRAINING_FLAG,REVIEW_FLAG,Comment_ID
FROM HG_User 
INNER JOIN HG_Depts ON HG_User.Dep_ID = HG_Depts.Dep_ID
FULL JOIN HG_TRAINING ON HG_User.User_ID = HG_TRAINING.USER_ID
FULL JOIN HG_COMMENT ON HG_User.User_ID = HG_COMMENT.USER_ID
WHERE     (HG_User.Entity_ID = @ENTITY) and Comment_Type=3 and Q_ID=0
ORDER BY HG_Depts.Dep_Name, HG_User.Full_Name
*/
SELECT     HG_Depts.Dep_Name, HG_User.User_ID, HG_User.FName, HG_User.LName,
HG_User.Full_Name, HG_Depts.Dep_ID,Position,Email,TRAINING_FLAG,REVIEW_FLAG
FROM HG_User 
INNER JOIN HG_Depts ON HG_User.Dep_ID = HG_Depts.Dep_ID
INNER JOIN HG_DEP_AREA ON HG_DEP_AREA.DEP_ID=HG_Depts.Dep_ID
FULL JOIN HG_TRAINING ON HG_User.User_ID = HG_TRAINING.USER_ID
WHERE     (HG_User.Entity_ID = @ENTITY)  AND Q_Law_Key='T'
ORDER BY HG_Depts.Dep_Name, HG_User.Full_Name


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.usp_Regulations
AS
SELECT     HG_Business_Q.Q_ID, HG_Business_Q.Q_Polarity, HG_Regulations.Section_ID, HG_Regulations.Regulation_number
FROM         HG_Regulations INNER JOIN
                      HG_Regulation_Composite ON HG_Regulations.Section_ID = HG_Regulation_Composite.REGQ_REGULATION INNER JOIN
                      HG_Business_Q ON HG_Regulation_Composite.REGQ_QUESTION = HG_Business_Q.Q_ID
WHERE     (HG_Business_Q.Q_Polarity = N'P')
ORDER BY HG_Regulations.Regulation_number

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/* 
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Stored Procedure 
    Project:  GBC Application
    Author: Derek Randles
    Create Date: 02082002
    Modification History
    Date  Editor   Changes
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
*/
CREATE PROCEDURE dbo.usp_ReturnCompletetionValues
@USER_ID INT,
@DEP_ID INT,
@VERIFIED INT = NULL,
@ANSWERS_P_Q INT OUTPUT,
@ANSWERS_P_C INT OUTPUT,
@ANSWERS_P_R INT OUTPUT,
@ANSWERS_D_Q INT OUTPUT,
@ANSWERS_D_C INT OUTPUT,
@ANSWERS_D_R INT OUTPUT,
@ANSWERS_S_Q INT OUTPUT,
@ANSWERS_S_C INT OUTPUT,
@ANSWERS_S_R INT OUTPUT,
@BUSINESS_Q INT OUTPUT,
@BUSINESS_C INT OUTPUT
AS
/*
 STEP 1 -- RETURN ALL DATA FROM ANSWERS TABLE BY USER ID, DEPT
*/
-- IF VERIFIED IS PASSED
IF @VERIFIED = 1
BEGIN
 
 -- LAW = P
 -- RETURN ALL QUESTIONS BY USER
 SELECT @ANSWERS_P_Q = COUNT(*) FROM HG_ANSWERS 
 WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID
 AND Law_fl = 'P'
 
 -- RETURN NOT NULL FROM USER
 SELECT @ANSWERS_P_C = COUNT(*) FROM HG_ANSWERS 
 WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID 
 AND Law_fl = 'P'
 AND Len(A_TEXT) > 0
 AND A_Verified =1
 
 -- RETURN YES/NO
 SELECT @ANSWERS_P_R = COUNT(*) FROM HG_ANSWERS 
 WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID 
 AND Law_fl = 'P'
 AND A_TEXT IN ('YES', 'N/A')
 AND A_Verified =1
 
 -- LAW = D
 -- RETURN ALL QUESTIONS BY USER
 SELECT @ANSWERS_D_Q = COUNT(*) FROM HG_ANSWERS 
 WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID
 AND Law_fl = 'D'
 
 -- RETURN NOT NULL FROM USER
 SELECT @ANSWERS_D_C = COUNT(*) FROM HG_ANSWERS 
 WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID 
 AND Law_fl = 'D'
 AND Len(A_TEXT) > 0
 AND A_Verified =1
 
 -- RETURN YES/NO
 SELECT @ANSWERS_D_R = COUNT(*) FROM HG_ANSWERS 
 WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID 
 AND Law_fl = 'D'
 AND A_TEXT IN ('YES', 'N/A')
 AND A_Verified =1
 
 -- LAW = S
 -- RETURN ALL QUESTIONS BY USER
 SELECT @ANSWERS_S_Q = COUNT(*) FROM HG_ANSWERS 
 WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID
 AND Law_fl = 'S'
 
 -- RETURN NOT NULL FROM USER
 SELECT @ANSWERS_S_C = COUNT(*) FROM HG_ANSWERS 
 WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID 
 AND Law_fl = 'S'
 AND Len(A_TEXT) > 0
 AND A_Verified =1
 
 -- RETURN YES/NO
 SELECT @ANSWERS_S_R = COUNT(*) FROM HG_ANSWERS 
 WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID 
 AND Law_fl = 'S'
 AND A_TEXT IN ('YES', 'N/A')
 AND A_Verified =1
 
END
ELSE
BEGIN
 -- LAW = P
 -- RETURN ALL QUESTIONS BY USER
 SELECT @ANSWERS_P_Q = COUNT(*) FROM HG_ANSWERS 
 WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID
 AND Law_fl = 'P'
 
 -- RETURN NOT NULL FROM USER
 SELECT @ANSWERS_P_C = COUNT(*) FROM HG_ANSWERS 
 WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID 
 AND Law_fl = 'P'
 AND Len(A_TEXT) > 0
 
 -- RETURN YES/NO
 SELECT @ANSWERS_P_R = COUNT(*) FROM HG_ANSWERS 
 WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID 
 AND Law_fl = 'P'
 AND A_TEXT IN ('YES', 'N/A')
 
 -- LAW = D
 -- RETURN ALL QUESTIONS BY USER
 SELECT @ANSWERS_D_Q = COUNT(*) FROM HG_ANSWERS 
 WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID
 AND Law_fl = 'D'
 
 -- RETURN NOT NULL FROM USER
 SELECT @ANSWERS_D_C = COUNT(*) FROM HG_ANSWERS 
 WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID 
 AND Law_fl = 'D'
 AND Len(A_TEXT) > 0
 
 -- RETURN YES/NO
 SELECT @ANSWERS_D_R = COUNT(*) FROM HG_ANSWERS 
 WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID 
 AND Law_fl = 'D'
 AND A_TEXT IN ('YES', 'N/A')
 
 -- LAW = S
 -- RETURN ALL QUESTIONS BY USER
 SELECT @ANSWERS_S_Q = COUNT(*) FROM HG_ANSWERS 
 WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID
 AND Law_fl = 'S'
 
 -- RETURN NOT NULL FROM USER
 SELECT @ANSWERS_S_C = COUNT(*) FROM HG_ANSWERS 
 WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID 
 AND Law_fl = 'S'
 AND Len(A_TEXT) > 0
 
 -- RETURN YES/NO
 SELECT @ANSWERS_S_R = COUNT(*) FROM HG_ANSWERS 
 WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID 
 AND Law_fl = 'S'
 AND A_TEXT IN ('YES', 'N/A')
END
/*
 STEP 2 -- RETURN ALL BUSINESS RESPONSES
*/
-- RETURN ALL BUSINESS ANSWERS
SELECT     @BUSINESS_Q=COUNT(HG_Q_OSHPD.Q_Number)
FROM    HG_Q_OSHPD
INNER JOIN  HG_BUSINESS_Q ON HG_BUSINESS_Q.Q_ID=HG_Q_OSHPD.Q_ID
WHERE HG_Q_OSHPD.OSHPD in ((SELECT DEP_CODE FROM HG_DEPTS WHERE DEP_ID=@DEP_ID),0) AND HG_BUSINESS_Q.Q_PARENT_ID=0
-- RETURN ALL BUSINESS ANSWERS
SELECT     @BUSINESS_C = COUNT(distinct HG_Business_Ab.Q_SELF_ID )
FROM         HG_Business_Ab 
INNER JOIN HG_BUSINESS_Q on HG_BUSINESS_Q.Q_ID=HG_Business_Ab.Q_SELF_ID 
Where (HG_Business_Ab.Q_User_ID = @USER_ID) AND (HG_Business_Ab.Q_Dept_ID = @DEP_ID)
AND HG_BUSINESS_Q.Q_PARENT_ID=0

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE usp_ReturnCompletetionValues_m
@USER_ID INT,
@DEP_ID INT,
@ANSWERS_T_Q INT OUTPUT,
@ANSWERS_T_V INT OUTPUT,
@ANSWERS_T_R INT OUTPUT,
@BUSINESS_Q INT OUTPUT,
@BUSINESS_C INT OUTPUT
AS
/*
 STEP 1 -- RETURN ALL DATA FROM ANSWERS TABLE BY USER ID, DEPT
*/
--Totals
-- RETURN ALL QUESTIONS BY USER
SELECT @ANSWERS_T_Q = COUNT(*) FROM HG_ANSWERS
WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID
--Validated
-- RETURN ALL QUESTIONS BY USER
SELECT @ANSWERS_T_V = COUNT(*) FROM HG_ANSWERS
WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID and  A_Verified=1
-- RETURN YES/NO
/*
SELECT @ANSWERS_T_R = COUNT(*) FROM HG_ANSWERS
WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID
AND A_TEXT IN ('YES', 'N/A')
*/
SELECT @ANSWERS_T_R = COUNT(*) FROM HG_ANSWERS
WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID
AND A_TEXT IN ('YES', 'N/A')
/*
 STEP 2 -- RETURN ALL BUSINESS RESPONSES
*/
-- RETURN ALL BUSINESS Questions
/*
SELECT  @BUSINESS_Q = COUNT(*) FROM HG_BUSINESS_Q
WHERE  (Dep_OSHPD=(select Dep_Code from HG_Depts 
where hg_depts.DEP_ID = @DEP_ID)or Dep_OSHPD=0) 
*/
-- RETURN ALL BUSINESS ANSWERS
SELECT @BUSINESS_C = COUNT(QID) FROM HG_BUSINESS_Ab
WHERE Q_USER_ID = @USER_ID AND Q_DEPT_ID = @DEP_ID

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.usp_ReturnCompletetionValues_manager
@USER_ID INT,
@DEP_ID INT,
@ANSWERS_T_Q INT OUTPUT,
@ANSWERS_T_V INT OUTPUT,
@ANSWERS_T_R INT OUTPUT,
@BUSINESS_Q INT OUTPUT,
@BUSINESS_C INT OUTPUT
AS
/*
 STEP 1 -- RETURN ALL DATA FROM ANSWERS TABLE BY USER ID, DEPT
*/
--Totals
-- RETURN ALL QUESTIONS BY USER
SELECT @ANSWERS_T_Q = COUNT(*) FROM HG_ANSWERS
WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID
--Validated
-- RETURN ALL QUESTIONS BY USER
SELECT @ANSWERS_T_V = COUNT(*) FROM HG_ANSWERS
WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID and  A_Verified=1
-- RETURN YES/NO
/*
SELECT @ANSWERS_T_R = COUNT(*) FROM HG_ANSWERS
WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID
AND A_TEXT IN ('YES', 'N/A')
*/
SELECT @ANSWERS_T_R = COUNT(*) FROM HG_ANSWERS
WHERE USER_ID = @USER_ID AND DEP_ID = @DEP_ID
AND A_TEXT IN ('YES', 'N/A')
/*
 STEP 2 -- RETURN ALL BUSINESS RESPONSES
*/
-- RETURN ALL BUSINESS Questions
/*
SELECT  @BUSINESS_Q = COUNT(*) FROM HG_BUSINESS_Q
WHERE  (Dep_OSHPD=(select Dep_Code from HG_Depts 
where hg_depts.DEP_ID = @DEP_ID)or Dep_OSHPD=0) 
-- RETURN ALL BUSINESS ANSWERS
SELECT @BUSINESS_C = COUNT(distinct Q_self_ID) FROM HG_BUSINESS_Ab
WHERE Q_USER_ID = @USER_ID AND Q_DEPT_ID = @DEP_ID
*/

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.usp_UpdateAnswers_b
@SELF_ID Int,
@ANSWER_ID Int,
@USER_ID Int,
@DEPT_ID Int
AS
 -- Delete existing values for this question first
 /*
 Delete From  HG_Business_Ab
 Where  Q_SELF_ID = @SELF_ID 
  AND Q_USER_ID = @USER_ID
  AND Q_DEPT_ID = @DEPT_ID
 */
 -- Now, insert new values 
 INSERT INTO HG_Business_Ab
                       (Q_SELF_ID, Q_ANSWER, Q_USER_ID, Q_DEPT_ID, Q_CREATE_DATE)
 VALUES     (@SELF_ID, @ANSWER_ID, @USER_ID, @DEPT_ID, GetDate())

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

