MasterHrcm Schema
Table Name:CMS_CCMCCCode
Description:
Describes Standard CCMCC Codes based on DiagnosisCode and ICDCode Version.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[CMS_CCMCCCode] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CMS_CCMCCCode](
[CodeId] [int] IDENTITY(1,1) NOT NULL,
[Code] [varchar](10) NULL,
[DxCode] [varchar](10) NULL,
[CCMCC] [varchar](10) NULL,
[PDXId] [varchar](10) NULL,
[CodeCount] [int] NOT NULL,
[DxDesc] [varchar](250) NULL,
[Version] [int] NOT NULL,
[EffectiveYearFrom] [datetime] NOT NULL,
[EffectiveYearTo] [datetime] NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[CMS_CCMCCCode] ADD CONSTRAINT [DF_CMS_CCMCCCode_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[CMS_CCMCCCode] ADD CONSTRAINT [DF_CMS_CCMCCCode_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:CMS_DrugDXCode
Description:
Describes Standard Drug codes based on Drugs and DrugType.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[CMS_DrugDXCode] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CMS_DrugDXCode](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[MainTerm] [varchar](100) NULL,
[Level1] [varchar](1000) NULL,
[Level2] [varchar](1000) NULL,
[Level3] [varchar](1000) NULL,
[Level4] [varchar](1000) NULL,
[FinalLevel] [varchar](1000) NULL,
[Level] [int] NOT NULL,
[Nemod] [varchar](600) NULL,
[Key] [varchar](500) NULL,
[Value] [varchar](500) NULL,
[Substance] [varchar](100) NULL,
[DrugType] [varchar](100) NULL,
[Code] [varchar](50) NULL,
[Status] [int] NOT NULL,
[LastModify] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [Ncix_MainTerm] ON [dbo].[CMS_DrugDXCode]
(
[MainTerm] ASC,
[Level] ASC,
[DrugType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
|
======================================================
Table Name:CMS_DXCodeDesc
Description:
Describes Standard ICD10 Diagnosis codes and its description and related information.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[CMS_DXCodeDesc] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CMS_DXCodeDesc](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[ICDCode] [varchar](10) NULL,
[ICD10Code] [varchar](10) NULL,
[Status] [int] NULL,
[ShortDesc] [varchar](250) NULL,
[LongDesc] [varchar](250) NULL,
[Chronic] [tinyint] NULL,
[ObStatus] [tinyint] NULL,
[FStatus] [int] NULL,
[EffectiveYearFrom] [varchar](30) NULL,
[EffectiveYearTo] [varchar](30) NULL,
[GroupId] [varchar](10) NULL,
[IcdLevel] [tinyint] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [Ncix_EffectiveYearFrom] ON [dbo].[CMS_DXCodeDesc]
(
[EffectiveYearFrom] ASC,
[EffectiveYearTo] ASC,
[Status] ASC,
[ICD10Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CMS_DXCodeDesc] ADD CONSTRAINT [DF_CMS_DxCodeDesc_FStatus] DEFAULT ((0)) FOR [FStatus]
GO
|
======================================================
Table Name:CMS_HCCDXCode
Description:
Describes Standard HCCCodes based on DiagnosisCode and it's Description which are from CMS web site.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[CMS_HCCDXCode] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CMS_HCCDXCode](
[DXCodeId] [int] IDENTITY(1,1) NOT NULL,
[DxCode] [varchar](10) NULL,
[DxDesc] [varchar](250) NULL,
[HCCCode] [int] NULL,
[HStatus] [varchar](10) NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[CMS_HCCDXCode] ADD CONSTRAINT [DF_CMS_HCCDXCode_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[CMS_HCCDXCode] ADD CONSTRAINT [DF_CMS_HCCDXCode_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:CMS_PCSCodeDesc
Description:
Describes Standard ICD10 Procedure codes and its description and related information.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[CMS_PCSCodeDesc] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CMS_PCSCodeDesc](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[CPTCode] [varchar](20) NULL,
[Status] [int] NULL,
[ShortDesc] [varchar](250) NULL,
[LongDesc] [varchar](250) NULL,
[EffectiveYearFrom] [varchar](30) NULL,
[EffectiveYearTo] [varchar](30) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [Ncix_EffectiveFrom] ON [dbo].[CMS_PCSCodeDesc]
(
[EffectiveYearFrom] ASC,
[EffectiveYearTo] ASC,
[Status] ASC,
[CPTCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
|
======================================================
Table Name:KB_AllergiesDXCode
Description:
Knowledge based table which consists of ICD10 codes related to allergy Disorders and it can be mapped using NumericId.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_AllergiesDXCode] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_AllergiesDXCode](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[DisorderId] [int] NULL,
[Disorder] [varchar](255) NULL,
[IcdCode1] [varchar](10) NULL,
[Status] [int] NULL,
[LastModify] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [Ncix_Disorder] ON [dbo].[KB_AllergiesDXCode]
(
[Disorder] ASC,
[Status] ASC
)
INCLUDE ( [DisorderId],
[IcdCode1]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
|
======================================================
Table Name:KB_Cause
Description:
Knowledge based table which consists of causes related to Disorders and it can be mapped using NumericId.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_Cause] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_Cause](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[CauseId] [int] NULL,
[Cause] [varchar](250) NULL,
[Status] [int] NOT NULL,
[UniqueId] [int] NOT NULL,
[LastModify] [datetime] NOT NULL,
[UserName] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [Ncix_Cause] ON [dbo].[KB_Cause]
(
[Cause] ASC,
[Status] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[KB_Cause] ADD CONSTRAINT [DF_KB_Cause_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[KB_Cause] ADD CONSTRAINT [DF_KB_Cause_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_CHFDXCode
Description:
Knowledge based table which consists of Codes related to CHF and getting final code by using combination of these codes.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_CHFDXCode] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_CHFDXCode](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[CHF1] [varchar](20) NULL,
[CHF2] [varchar](20) NULL,
[FinalCode] [varchar](20) NULL,
[Tconcept1] [int] NOT NULL,
[LastModify] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[KB_CHFDXCode] ADD CONSTRAINT [DF_KB_ChfDxCode_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_Disorder
Description:
Knowledge based table which consists of All Disorders and it can be mapped using NumericId.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_Disorder] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_Disorder](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[DisorderId] [int] NULL,
[Disorder] [varchar](250) NULL,
[Status] [int] NOT NULL,
[UniqueId] [int] NOT NULL,
[LastModify] [datetime] NOT NULL,
[UserName] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [Ncix_Disorder] ON [dbo].[KB_Disorder]
(
[Disorder] ASC,
[Status] ASC
)
INCLUDE ( [DisorderId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[KB_Disorder] ADD CONSTRAINT [DF_KB_Disorder_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[KB_Disorder] ADD CONSTRAINT [DF_KB_Disorder_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_DXCode
Description:
Knowledge based table which consists of all combinations of NumericIds like Disorder,Cause,Site,Site1,...and using those NumericIds we can get Final ICD10 Diagnosis Code by our Analysis Application.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_DXCode] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_DXCode](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[DisorderId] [int] NULL,
[CauseId] [int] NOT NULL,
[SiteId] [int] NOT NULL,
[Site1Id] [int] NOT NULL,
[Site2Id] [int] NOT NULL,
[ManifestationId] [int] NOT NULL,
[ExtensionId] [varchar](10) NULL,
[Stage] [int] NOT NULL,
[Type] [int] NOT NULL,
[History] [varchar](10) NULL,
[TemporalConcept1] [int] NOT NULL,
[TemporalConcept2] [int] NOT NULL,
[IcdCode1] [varchar](10) NULL,
[IcdCode2] [varchar](10) NULL,
[IcdCode3] [varchar](10) NULL,
[Status] [int] NOT NULL,
[LastModify] [datetime] NOT NULL,
[UserName] [varchar](50) NULL,
[TemplateId] [int] NULL,
[ckstatus] [int] NOT NULL,
[Neonatal] [int] NULL,
[Gender] [int] NOT NULL,
[Icdcode4] [varchar](10) NULL,
[Icdcode5] [varchar](10) NULL,
[Icdcode6] [varchar](10) NULL,
[obStatus] [tinyint] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [Ncix_KB_DxCode] ON [dbo].[KB_DXCode]
(
[DisorderId] ASC,
[Status] ASC
)
INCLUDE ( [Icdcode4]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[KB_DXCode] ADD CONSTRAINT [DF_KB_DxCode_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[KB_DXCode] ADD CONSTRAINT [DF_KB_DxCode_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
ALTER TABLE [dbo].[KB_DXCode] ADD CONSTRAINT [DF_KB_DxCode_ckstatus] DEFAULT ((0)) FOR [ckstatus]
GO
ALTER TABLE [dbo].[KB_DXCode] ADD CONSTRAINT [DF_KB_DxCode_Neonatal] DEFAULT ((0)) FOR [Neonatal]
GO
ALTER TABLE [dbo].[KB_DXCode] ADD CONSTRAINT [DF_KB_DxCode_Gender] DEFAULT ((0)) FOR [Gender]
GO
ALTER TABLE [dbo].[KB_DXCode] ADD CONSTRAINT [DF_KB_DxCode_obStatus] DEFAULT ((0)) FOR [obStatus]
GO
|
======================================================
Table Name:KB_DXCodeCombination
Description:
Knowledge based table which consists of Combination Codes according to the Coding Guidelines.
Example :--COPD w acute bronchitis(J44.0) ----> J44.9 + J20.9
J44.9 and J20.9 coded separately from Document, so J44.0 is coded from this combination table.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_DXCodeCombination] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_DXCodeCombination](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[dx] [varchar](20) NULL,
[dxcomb] [varchar](20) NULL,
[GroupId] [int] NULL,
[count] [int] NULL,
[Content] [varchar](500) NULL,
[DisorderId] [int] NOT NULL,
[UserName] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [Ncix_KB_DxCodeCombination] ON [dbo].[KB_DXCodeCombination]
(
[dxcomb] ASC,
[dx] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
|
======================================================
Table Name:KB_DXCodeFirst
Description:
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_DXCodeFirst] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_DXCodeFirst](
[internalid] [int] IDENTITY(1,1) NOT NULL,
[CODE] [varchar](50) NULL,
[CodeFirst] [varchar](500) NULL,
[rstatus] [int] NULL,
[LASTMODIFY] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
[internalid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
|
======================================================
Table Name:KB_DXExclusionCode
Description:
Knowledge based table which consists of Excluded ICD10 codes based on Priority and Disease Group.Lowest priority means less Specific code and Highest Priority means Most Specific Diagnosis code.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_DXExclusionCode] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_DXExclusionCode](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[IcdCode] [varchar](50) NULL,
[Priority] [int] NULL,
[GroupId] [int] NULL,
[GroupName] [nvarchar](255) NULL,
[LastModify] [datetime] NOT NULL,
[UserName] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [Ncix_KB_DxExclusionCode] ON [dbo].[KB_DXExclusionCode]
(
[GroupId] ASC,
[IcdCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[KB_DXExclusionCode] ADD CONSTRAINT [DF_KB_DxExclusionCode_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_DXFactorCombination
Description:
Knowledge based table which consists of Combination codes based on Icdcode and keyterm according to coding guidelines.
Example :--- K25.9 and GI bleed are there in document, then we have to code K25.4
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_DXFactorCombination] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_DXFactorCombination](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[ICDCode] [varchar](20) NULL,
[FactorName] [varchar](200) NULL,
[TargetCode] [varchar](20) NULL,
[CombCount] [tinyint] NULL,
[GroupId] [int] NULL,
[RStatus] [tinyint] NULL,
[LastModify] [datetime] NULL,
[UserName] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [Ncix_KB_DxFactorCombination] ON [dbo].[KB_DXFactorCombination]
(
[ICDCode] ASC,
[FactorName] ASC,
[TargetCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[KB_DXFactorCombination] ADD CONSTRAINT [DF_KB_DxFactorCombination_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_DXTabExclusion1Code
Description:
this table is build with the data from Tabular List of Diseases and Injuries Pdf for Code Exclusions
according to the coding guidelines.
Example :-- C44.1 Other and unspecified malignant neoplasm of skin of eyelid, including canthus
Excludes1: connective tissue of eyelid (C49.0)
here if C49.0 and any C44.1 series codes are there in document, need to exclude C44.1 series codes.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_DXTabExclusion1Code] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_DXTabExclusion1Code](
[Internalid] [int] IDENTITY(1,1) NOT NULL,
[Code1] [varchar](50) NULL,
[Code2] [varchar](50) NULL,
[Rstatus] [int] NULL,
[LastModify] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [Ncix_KB_DxTabExclusion1Code] ON [dbo].[KB_DXTabExclusion1Code]
(
[Code1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[KB_DXTabExclusion1Code] ADD CONSTRAINT [DF_KB_DxTabExclusion1Code_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_FBSynonyms
Description:
Knowledge based table which consists of preferred names for some body parts regions.
Example :--- Lower end, lateral end, distal end, distal are preferred as 'Lower end'.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_FBSynonyms] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_FBSynonyms](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[ElementName] [varchar](500) NULL,
[PreferredName] [varchar](250) NULL,
[Status] [int] NOT NULL,
[LastModify] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[KB_FBSynonyms] ADD CONSTRAINT [DF_KB_FBSynonyms_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[KB_FBSynonyms] ADD CONSTRAINT [DF_KB_FBSynonyms_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_FractureElements
Description:
Knowledge based table which consists of Elements to identify Fracture and it's location.
Example : --- Laceration, Contusion, Dislocation are comes under --->Fdx
Left, Left lower are comes under --->FBLoc1
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_FractureElements] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_FractureElements](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[Element] [varchar](50) NULL,
[ElementName] [varchar](500) NULL,
[GroupId] [int] NULL,
[Priority] [int] NULL,
[Status] [int] NOT NULL,
[LastModify] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[KB_FractureElements] ADD CONSTRAINT [DF_KB_FractureElements_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[KB_FractureElements] ADD CONSTRAINT [DF_KB_FractureElements_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_FXElementTypes
Description:
Knowledge based table which consists of standard terms for location, stage and types for Fractures.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_FXElementTypes] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_FXElementTypes](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[ElementType] [varchar](200) NULL,
[Status] [int] NOT NULL,
[LastModify] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[KB_FXElementTypes] ADD CONSTRAINT [DF_KB_FXElementTypes_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[KB_FXElementTypes] ADD CONSTRAINT [DF_KB_FXElementTypes_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_FXExtension
Description:
Knowledge based table which consists of Final digit for Fracture related Icdcode. based on fracture types and encounter.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_FXExtension] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_FXExtension](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[FType] [varchar](100) NULL,
[Ftype2] [varchar](100) NULL,
[Encounter] [varchar](50) NULL,
[ExtDigit] [varchar](10) NULL,
[Status] [int] NOT NULL,
[LastModify] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[KB_FXExtension] ADD CONSTRAINT [DF_KB_FXExtension_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[KB_FXExtension] ADD CONSTRAINT [DF_KB_FXExtension_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_HabitsDXCode
Description:
Knowledge based table which consists of Icdcodes related to patient habits like smoking, alcohol and drugs.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_HabitsDXCode] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_HabitsDXCode](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[DisorderId] [int] NOT NULL,
[Disorder] [varchar](255) NULL,
[Type] [varchar](255) NULL,
[IcdCode1] [varchar](50) NULL,
[History] [varchar](10) NULL,
[Status] [int] NULL,
[LastModify] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [Ncix_KB_HabitsDxCode] ON [dbo].[KB_HabitsDXCode]
(
[IcdCode1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[KB_HabitsDXCode] ADD CONSTRAINT [DF_KB_HabitsDxCode_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_HivDXCode
Description:
Knowledge based table which consists of combinations to code B20(HIV).
based on bacteria and status code of HIV.
Example :--- B55.9 + Z21 ===> B20
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_HivDXCode] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[KB_HivDXCode](
[A1Code] [nvarchar](10) NULL,
[A2Code] [nvarchar](10) NULL,
[FinalCode] [nvarchar](10) NULL,
[Status] [int] NULL
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [Ncix_KB_HivDxCode] ON [dbo].[KB_HivDXCode]
(
[A1Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
|
======================================================
Table Name:KB_HTNDXCode
Description:
Knowledge based table which consists of Combination code for CHF and CKD.
Example : --- I11.9 + I12.0 ===> I13.11
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_HTNDXCode] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_HTNDXCode](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[CHF] [varchar](50) NULL,
[CKD] [varchar](50) NULL,
[FinalCode] [varchar](50) NULL,
[LastModify] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[KB_HTNDXCode] ADD CONSTRAINT [DF_KB_HtnDxCode_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_Injurycodes
Description:
Knowledge based table which consists of Injury related Icdcodes based on fractures, body parts and locations.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_Injurycodes] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_Injurycodes](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[Code] [varchar](50) NULL,
[FDX] [varchar](100) NULL,
[BodyPart] [varchar](50) NULL,
[MainBodyPartLoc] [varchar](200) NULL,
[bodypartloc] [varchar](100) NULL,
[bodypartloc2] [varchar](100) NULL,
[Position] [varchar](50) NULL,
[source] [varchar](100) NULL,
[effect] [varchar](500) NULL,
[foreignbody] [varchar](500) NULL,
[bloodvessels] [varchar](200) NULL,
[bloodvesseldata] [varchar](500) NULL,
[lacerationdata] [varchar](50) NULL,
[Displaced] [varchar](100) NULL,
[Type] [varchar](100) NULL,
[Type3] [varchar](100) NULL,
[ExtraData] [varchar](250) NULL,
[FinalCode] [varchar](100) NULL,
[Encounter] [varchar](10) NULL,
[Status] [int] NULL,
[UserName] [varchar](50) NULL,
[LastModify] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[KB_Injurycodes] ADD CONSTRAINT [DF_KB_Injurycodes_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_InjuryElements
Description:
Knowledge based table which consists of data related to Fractures, body parts and regions.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_InjuryElements] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_InjuryElements](
[Internalid] [int] IDENTITY(1,1) NOT NULL,
[Bodypart] [varchar](100) NULL,
[FDX] [varchar](100) NULL,
[ElementType] [varchar](100) NULL,
[ElementName] [varchar](100) NULL,
[RStatus] [int] NOT NULL,
[LastModify] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[KB_InjuryElements] ADD CONSTRAINT [DF_KB_InjuryElements_RStatus] DEFAULT ((0)) FOR [RStatus]
GO
ALTER TABLE [dbo].[KB_InjuryElements] ADD CONSTRAINT [DF_KB_InjuryElements_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_LongTermDrugDXCode
Description:
Knowledge based table which consists of Icdcodes related Long term use of Drugs based on Drugs pdf.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_LongTermDrugDXCode] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_LongTermDrugDXCode](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[ICDCode] [varchar](10) NULL,
[Description] [varchar](100) NULL,
[DrugName] [varchar](500) NULL,
[DrugCTZ] [varchar](50) NULL,
[RStatus] [int] NULL,
[LastModify] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[KB_LongTermDrugDXCode] ADD CONSTRAINT [DF_KB_LongTermDrugDxCode_RStatus] DEFAULT ((0)) FOR [RStatus]
GO
ALTER TABLE [dbo].[KB_LongTermDrugDXCode] ADD CONSTRAINT [DF_KB_LongTermDrugDxCode_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_Manifestation
Description:
Knowledge based table which consists of manifestation terms which are used to get Icdcodes while analysis.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_Manifestation] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_Manifestation](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[ManifestationId] [int] NOT NULL,
[Manifestation] [varchar](250) NULL,
[Status] [int] NOT NULL,
[UniqueId] [int] NOT NULL,
[LastModify] [datetime] NOT NULL,
[UserName] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [Ncix_KB_Manifestation] ON [dbo].[KB_Manifestation]
(
[Manifestation] ASC
)
INCLUDE ( [ManifestationId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[KB_Manifestation] ADD CONSTRAINT [DF_KB_Manifestation_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[KB_Manifestation] ADD CONSTRAINT [DF_KB_Manifestation_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_OBGComplication
Description:
Knowledge based table which consists of OBG related Icdcodes based on trimester data.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_OBGComplication] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_OBGComplication](
[internalid] [int] IDENTITY(1,1) NOT NULL,
[code] [varchar](50) NULL,
[Icd10code] [varchar](50) NULL,
[trimester] [varchar](1000) NULL,
[trimstatus] [tinyint] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [Ncix_KB_ObgCompication] ON [dbo].[KB_OBGComplication]
(
[trimstatus] ASC,
[code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
|
======================================================
Table Name:KB_OBGDeliveryStatus
Description:
Knowledge based table which consists of Z37 series icdcodes based on newborn birth status.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_OBGDeliveryStatus] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_OBGDeliveryStatus](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[FetusCount] [varchar](50) NULL,
[StillBirthStatus] [varchar](500) NULL,
[TargetCode] [varchar](50) NULL,
[CodeDescription] [varchar](700) NULL,
[LastModify] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[KB_OBGDeliveryStatus] ADD CONSTRAINT [DF_KB_ObgDeliveryStatus_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_OBGDisorder
Description:
Knowledge based table which consists of disorders data related to OBG(Pregnancy) patients.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_OBGDisorder] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_OBGDisorder](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[DisorderId] [int] NULL,
[Disorder] [varchar](500) NULL,
[Status] [int] NOT NULL,
[UniqueId] [int] NOT NULL,
[LastModify] [datetime] NOT NULL,
[UserName] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[KB_OBGDisorder] ADD CONSTRAINT [DF_KB_ObgDisorder_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_OBGDXCode
Description:
Knowledge based table which consists of Icdodes related OBG patients.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_OBGDXCode] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_OBGDXCode](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[DisorderId] [int] NULL,
[CauseId] [int] NOT NULL,
[SiteId] [int] NOT NULL,
[Site1Id] [int] NOT NULL,
[Site2Id] [int] NOT NULL,
[ManifestationId] [int] NOT NULL,
[ExtensionId] [int] NULL,
[Stage] [int] NOT NULL,
[Type] [int] NOT NULL,
[History] [varchar](50) NOT NULL,
[TemporalConcept1] [int] NOT NULL,
[TemporalConcept2] [int] NOT NULL,
[IcdCode1] [varchar](10) NULL,
[IcdCode2] [varchar](10) NULL,
[IcdCode3] [varchar](10) NULL,
[Status] [int] NOT NULL,
[LastModify] [datetime] NOT NULL,
[UserName] [varchar](50) NULL,
[TemplateId] [int] NULL,
[ckstatus] [int] NOT NULL,
[Neonatal] [int] NULL,
[Gender] [int] NOT NULL,
[Pstatus] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [Ncix_KB_ObgDxCode] ON [dbo].[KB_OBGDXCode]
(
[DisorderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[KB_OBGDXCode] ADD CONSTRAINT [DF_KB_ObgDxCode_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[KB_OBGDXCode] ADD CONSTRAINT [DF_KB_ObgDxCode_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
ALTER TABLE [dbo].[KB_OBGDXCode] ADD CONSTRAINT [DF_KB_ObgDxCode_ckstatus] DEFAULT ((0)) FOR [ckstatus]
GO
ALTER TABLE [dbo].[KB_OBGDXCode] ADD CONSTRAINT [DF_KB_ObgDxCode_Gender] DEFAULT ((0)) FOR [Gender]
GO
|
======================================================
Table Name:KB_OBGInfectiousCrosswalk
Description:
Knowledge based table which consists of Icdcodes(O series codes) related to OBG based on General Icdcodes.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_OBGInfectiousCrosswalk] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_OBGInfectiousCrosswalk](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[ObgCode] [varchar](50) NULL,
[Icdcode] [varchar](50) NULL,
[LastModify] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [Ncix_KB_ObgInfectiousCrosswalk] ON [dbo].[KB_OBGInfectiousCrosswalk]
(
[Icdcode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
|
======================================================
Table Name:KB_PCSDXExclusion
Description:
Knowledge based table which consists of Icdcode exclusions based on some Pcscodes.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_PCSDXExclusion] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_PCSDXExclusion](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[DxCode] [varchar](10) NOT NULL,
[PCSCode] [varchar](10) NOT NULL,
[Status] [int] NOT NULL,
[LastModify] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[KB_PCSDXExclusion] ADD CONSTRAINT [DF_KB_PcsDxExclusion_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[KB_PCSDXExclusion] ADD CONSTRAINT [DF_KB_PcsDxExclusion_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_Site
Description:
Knowledge based table which consists of all primary body parts data which are used to get Icdcodes.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_Site] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_Site](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[SiteId] [int] NOT NULL,
[Site] [varchar](250) NULL,
[Status] [int] NOT NULL,
[UniqueId] [int] NOT NULL,
[LastModify] [datetime] NOT NULL,
[UserName] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[KB_Site] ADD CONSTRAINT [DF_KB_Site_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[KB_Site] ADD CONSTRAINT [DF_KB_Site_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_Site1
Description:
Knowledge based table which consists of all body part locations data which are used to get Icdcode specifications.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_Site1] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_Site1](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[Site1Id] [int] NULL,
[Site] [varchar](250) NULL,
[Status] [int] NOT NULL,
[UniqueId] [int] NOT NULL,
[LastModify] [datetime] NOT NULL,
[UserName] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[KB_Site1] ADD CONSTRAINT [DF_KB_Site1_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[KB_Site1] ADD CONSTRAINT [DF_KB_Site1_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_Site2
Description:
Knowledge based table which consists of cells and regions related to body parts data which are used to get Icdcodes.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_Site2] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_Site2](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[Site2Id] [int] NULL,
[Site] [varchar](250) NULL,
[Status] [int] NOT NULL,
[UniqueId] [int] NOT NULL,
[LastModify] [datetime] NOT NULL,
[UserName] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[KB_Site2] ADD CONSTRAINT [DF_KB_Site2_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[KB_Site2] ADD CONSTRAINT [DF_KB_Site2_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_SurgicalDXCode
Description:
Knowledge based table which consists of 'Z' series Icdcodes related to Surgeries.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_SurgicalDXCode] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_SurgicalDXCode](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[DisorderId] [int] NULL,
[Disorder] [varchar](100) NULL,
[Icdcode1] [varchar](50) NULL,
[Icdcode2] [varchar](50) NULL,
[Status] [int] NULL,
[LastModify] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[KB_SurgicalDXCode] ADD CONSTRAINT [DF_KB_SurgicalDxCode_Status] DEFAULT ((0)) FOR [Status]
GO
|
======================================================
Table Name:KB_TemporalConcept1
Description:
Knowledge based table which consists of terms which are used to know the Acute or Chronic Condition of the disease.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_TemporalConcept1] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_TemporalConcept1](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[TConcept1Id] [int] NULL,
[TConcept1] [varchar](50) NULL,
[Status] [int] NOT NULL,
[UniqueId] [int] NULL,
[LastModify] [datetime] NOT NULL,
[UserName] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [Ncix_KB_TemporalConcept1] ON [dbo].[KB_TemporalConcept1]
(
[TConcept1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[KB_TemporalConcept1] ADD CONSTRAINT [DF_KB_TemporalConcept1_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[KB_TemporalConcept1] ADD CONSTRAINT [DF_KB_TemporalConcept1_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_TemporalConcept2
Description:
Knowledge based table which consists of terms which are used to know the Systolic or Diastolic conditions of heart failure and controlled or uncontrolled states of Diabetic disease.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_TemporalConcept2] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_TemporalConcept2](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[TConcept2Id] [int] NULL,
[TConcept2] [varchar](50) NULL,
[Status] [int] NOT NULL,
[UniqueId] [int] NULL,
[LastModify] [datetime] NOT NULL,
[UserName] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [Ncix_KB_TemporalConcept2] ON [dbo].[KB_TemporalConcept2]
(
[TConcept2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[KB_TemporalConcept2] ADD CONSTRAINT [DF_KB_TemporalConcept2_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[KB_TemporalConcept2] ADD CONSTRAINT [DF_KB_TemporalConcept2_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:KB_Type
Description:
Knowledge based table which consists of terms like Types, grades and malignant and benign words.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_Type] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_Type](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[TypeId] [int] NULL,
[Type] [varchar](50) NULL,
[Status] [int] NOT NULL,
[UniqueId] [int] NULL,
[LastModify] [datetime] NOT NULL,
[UserName] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
|
======================================================
Table Name:KB_VSeriesCore
Description:
Knowledge based table which consists of 'V' Series Icdcodes based on Transport Accidents in External causes of morbidity.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_VSeriesCore] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_VSeriesCore](
[InternalID] [int] IDENTITY(1,1) NOT NULL,
[Event] [varchar](50) NULL,
[Victim] [varchar](50) NULL,
[VTVehicle] [varchar](50) NULL,
[OPVehicle] [varchar](50) NULL,
[Traffic] [varchar](50) NULL,
[Encounter] [varchar](50) NULL,
[Code] [varchar](10) NULL,
[Rstatus] [tinyint] NULL,
[UserName] [varchar](50) NULL,
[Lastmodify] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[KB_VSeriesCore] ADD CONSTRAINT [DF_KB_VSeriesCore_Rstatus] DEFAULT ((0)) FOR [Rstatus]
GO
ALTER TABLE [dbo].[KB_VSeriesCore] ADD CONSTRAINT [DF_KB_VSeriesCore_Lastmodify] DEFAULT (getdate()) FOR [Lastmodify]
GO
|
======================================================
Table Name:KB_WSeriesCoreData
Description:
Knowledge based table which consists of 'W' Series Icdcodes based on falls, exposure to mechanical forces or electric or temperature and non-transport accidents in External causes of morbidity.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_WSeriesCoreData] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_WSeriesCoreData](
[internalid] [int] IDENTITY(1,1) NOT NULL,
[codeseries] [varchar](10) NULL,
[StandardDisorder] [varchar](150) NULL,
[keyterm] [varchar](100) NULL,
[keytype] [varchar](50) NULL,
[code] [varchar](10) NULL,
[lastmodify] [datetime] NULL,
[rstatus] [int] NULL,
[action] [varchar](100) NULL,
[username] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[KB_WSeriesCoreData] ADD CONSTRAINT [DF_KB_WSeriesCoreData_lastmodify] DEFAULT (getdate()) FOR [lastmodify]
GO
ALTER TABLE [dbo].[KB_WSeriesCoreData] ADD CONSTRAINT [DF_KB_WSeriesCoreData_rstatus] DEFAULT ((0)) FOR [rstatus]
GO
|
======================================================
Table Name:KB_YSeriesActivity
Description:
Knowledge based table which consists of 'Y' series codes
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[KB_YSeriesActivity] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[KB_YSeriesActivity](
[InternalId] [tinyint] IDENTITY(1,1) NOT NULL,
[Activity] [varchar](150) NULL,
[Place] [varchar](100) NULL,
[Code] [varchar](50) NULL,
[rstatus] [tinyint] NULL,
[LastModify] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[KB_YSeriesActivity] ADD CONSTRAINT [DF_KB_YSeriesActivity_rstatus] DEFAULT ((0)) FOR [rstatus]
GO
ALTER TABLE [dbo].[KB_YSeriesActivity] ADD CONSTRAINT [DF_KB_YSeriesActivity_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:LOG_Log4j2
Description:
All Info,Warn and Error Log data related to KPAI application.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[LOG_Log4j2] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LOG_Log4j2](
[Log4jId] [int] IDENTITY(1,1) NOT NULL,
[ApplicationName] [varchar](100) NULL,
[LogLevel] [varchar](50) NULL,
[Logger] [varchar](300) NULL,
[LogLine] [varchar](300) NULL,
[LogMessage] [varchar](800) NULL,
[LogException] [varchar](800) NULL,
[Active] [tinyint] NOT NULL,
[LogDate] [datetime] NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[LOG_Log4j2] ADD CONSTRAINT [DF_LOG_Log4j2_Active] DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[LOG_Log4j2] ADD CONSTRAINT [DF_LOG_Log4j2_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[LOG_Log4j2] ADD CONSTRAINT [DF_LOG_Log4j2_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_Address
Description:
This tables describes information about Address.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_Address] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_Address](
[AddressID] [int] IDENTITY(1,1) NOT NULL,
[Address1] [varchar](80) NOT NULL,
[Address2] [varchar](80) NULL,
[Address3] [varchar](80) NULL,
[City] [varchar](80) NOT NULL,
[RegionName] [varchar](80) NOT NULL,
[StateProvince] [varchar](80) NOT NULL,
[Country] [varchar](80) NOT NULL,
[PostalCode] [varchar](8) NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MST_Address] PRIMARY KEY CLUSTERED
(
[AddressID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_Address] ADD CONSTRAINT [DF_MST_Address_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_Address] ADD CONSTRAINT [DF_MST_Address_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_ApplicationPermission
Description:
This table describes complete information about Permissions and its related data.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_ApplicationPermission] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_ApplicationPermission](
[ApplicationPermissionId] [int] IDENTITY(1,1) NOT NULL,
[PermissionCodeName] [varchar](100) NULL,
[PermissionDesc] [varchar](100) NULL,
[Active] [bit] NOT NULL,
[ParentId] [int] NOT NULL,
[SelfParent] [bit] NOT NULL,
[AliasName] [varchar](50) NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
[RType] [varchar](20) NULL,
[Icon] [varchar](200) NULL,
[Priority] [tinyint] NOT NULL,
CONSTRAINT [PK_MST_ApplicationPermission] PRIMARY KEY CLUSTERED
(
[ApplicationPermissionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_ApplicationPermission] ADD CONSTRAINT [DF_MST_ApplicationPermission_Active] DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[MST_ApplicationPermission] ADD CONSTRAINT [DF_MST_ApplicationPermission_SelfParent] DEFAULT ((0)) FOR [SelfParent]
GO
ALTER TABLE [dbo].[MST_ApplicationPermission] ADD CONSTRAINT [DF_MST_ApplicationPermission_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_ApplicationPermission] ADD CONSTRAINT [DF_MST_ApplicationPermission_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
ALTER TABLE [dbo].[MST_ApplicationPermission] ADD DEFAULT ((0)) FOR [Priority]
GO
|
======================================================
Table Name:MST_ApplicationRole
Description:
This table describes complete information about Roles and its related data.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_ApplicationRole] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_ApplicationRole](
[ApplicationRoleId] [int] IDENTITY(1,1) NOT NULL,
[RoleName] [varchar](100) NOT NULL,
[HospitalId] [int] NOT NULL,
[Active] [bit] NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
[RoleDescription] [varchar](150) NULL,
CONSTRAINT [PK_MST_ApplicationUserRole] PRIMARY KEY CLUSTERED
(
[ApplicationRoleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
CONSTRAINT [IX_MST_ApplicationRole] UNIQUE NONCLUSTERED
(
[RoleName] ASC,
[HospitalId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_ApplicationRole] WITH CHECK ADD CONSTRAINT [FK_MST_ApplicationRole_MST_Hospital] FOREIGN KEY([HospitalId])
REFERENCES [dbo].[MST_Hospital] ([HospitalID])
GO
ALTER TABLE [dbo].[MST_ApplicationRole] CHECK CONSTRAINT [FK_MST_ApplicationRole_MST_Hospital]
GO
ALTER TABLE [dbo].[MST_ApplicationRole] ADD CONSTRAINT [DF_MST_ApplicationRole_Active] DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[MST_ApplicationRole] ADD CONSTRAINT [DF_MST_ApplicationUserRole_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_ApplicationRole] ADD CONSTRAINT [DF_MST_ApplicationUserRole_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_ApplicationRole_Permission
Description:
This is data relation table and describes relation between ApplicationRoles and Permissions and hospital.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_ApplicationRole_Permission] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MST_ApplicationRole_Permission](
[RolePermissionId] [int] IDENTITY(1,1) NOT NULL,
[ApplicationRoleId] [int] NOT NULL,
[ApplicationPermissionId] [int] NOT NULL,
[HospitalId] [int] NOT NULL,
[Active] [bit] NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MST_ApplicationRole_Permission] PRIMARY KEY CLUSTERED
(
[RolePermissionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MST_ApplicationRole_Permission] WITH CHECK ADD CONSTRAINT [FK_MST_ApplicationRole_Permission_MST_ApplicationPermission] FOREIGN KEY([ApplicationPermissionId])
REFERENCES [dbo].[MST_ApplicationPermission] ([ApplicationPermissionId])
GO
ALTER TABLE [dbo].[MST_ApplicationRole_Permission] CHECK CONSTRAINT [FK_MST_ApplicationRole_Permission_MST_ApplicationPermission]
GO
ALTER TABLE [dbo].[MST_ApplicationRole_Permission] WITH CHECK ADD CONSTRAINT [FK_MST_ApplicationRole_Permission_MST_ApplicationRole] FOREIGN KEY([ApplicationRoleId])
REFERENCES [dbo].[MST_ApplicationRole] ([ApplicationRoleId])
GO
ALTER TABLE [dbo].[MST_ApplicationRole_Permission] CHECK CONSTRAINT [FK_MST_ApplicationRole_Permission_MST_ApplicationRole]
GO
ALTER TABLE [dbo].[MST_ApplicationRole_Permission] WITH CHECK ADD CONSTRAINT [FK_MST_ApplicationRole_Permission_MST_Hospital] FOREIGN KEY([HospitalId])
REFERENCES [dbo].[MST_Hospital] ([HospitalID])
GO
ALTER TABLE [dbo].[MST_ApplicationRole_Permission] CHECK CONSTRAINT [FK_MST_ApplicationRole_Permission_MST_Hospital]
GO
ALTER TABLE [dbo].[MST_ApplicationRole_Permission] ADD CONSTRAINT [DF_MST_ApplicationRole_Permission_Active] DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[MST_ApplicationRole_Permission] ADD CONSTRAINT [DF_MST_ApplicationRole_Permission_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_ApplicationRole_Permission] ADD CONSTRAINT [DF_MST_ApplicationRole_Permission_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_ApplicationUser
Description:
This tables describes information about Users and related data.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_ApplicationUser] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_ApplicationUser](
[ApplicationUserId] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](50) NOT NULL,
[Password] [varchar](250) NOT NULL,
[EmailId] [varchar](50) NOT NULL,
[SuperAdmin] [bit] NULL,
[FullName] [varchar](80) NOT NULL,
[Bio] [text] NULL,
[Photo] [varchar](200) NOT NULL,
[Lang] [varchar](25) NOT NULL,
[TimeZone] [varchar](25) NOT NULL,
[PhoneNumber] [varchar](20) NULL,
[Theme] [varchar](20) NOT NULL,
[ResetToken] [varchar](250) NULL,
[LastLogin] [datetime] NOT NULL,
[Active] [tinyint] NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MST_ApplicationUser] PRIMARY KEY CLUSTERED
(
[ApplicationUserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
CONSTRAINT [IX_MST_ApplicationUser] UNIQUE NONCLUSTERED
(
[UserName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
CONSTRAINT [IX_MST_ApplicationUser_1] UNIQUE NONCLUSTERED
(
[EmailId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_ApplicationUser] ADD CONSTRAINT [DF_MST_ApplicationUser_SuperUser] DEFAULT ((0)) FOR [SuperAdmin]
GO
ALTER TABLE [dbo].[MST_ApplicationUser] ADD CONSTRAINT [DF_MST_ApplicationUser_Photo] DEFAULT ('user_default.png') FOR [Photo]
GO
ALTER TABLE [dbo].[MST_ApplicationUser] ADD CONSTRAINT [DF_MST_ApplicationUser_Lang] DEFAULT ('English') FOR [Lang]
GO
ALTER TABLE [dbo].[MST_ApplicationUser] ADD CONSTRAINT [DF_MST_ApplicationUser_TimeZone] DEFAULT ('UTC') FOR [TimeZone]
GO
ALTER TABLE [dbo].[MST_ApplicationUser] ADD CONSTRAINT [DF_MST_ApplicationUser_Theme] DEFAULT ('KPAI') FOR [Theme]
GO
ALTER TABLE [dbo].[MST_ApplicationUser] ADD CONSTRAINT [DF_MST_ApplicationUser_LastLogin] DEFAULT (getutcdate()) FOR [LastLogin]
GO
ALTER TABLE [dbo].[MST_ApplicationUser] ADD CONSTRAINT [DF_MST_ApplicationUser_Active] DEFAULT ((2)) FOR [Active]
GO
ALTER TABLE [dbo].[MST_ApplicationUser] ADD CONSTRAINT [DF_MST_ApplicationUser_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_ApplicationUser] ADD CONSTRAINT [DF_MST_ApplicationUser_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_ApplicationUser_Permission
Description:
Relational table which describes relational information between users, permissions and hospitals
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_ApplicationUser_Permission] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_ApplicationUser_Permission](
[UserPermissionId] [int] IDENTITY(1,1) NOT NULL,
[ApplicationUserId] [int] NOT NULL,
[ApplicationPermissionId] [int] NOT NULL,
[HospitalId] [int] NOT NULL,
[Active] [bit] NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
[UEType] [varchar](20) NULL,
CONSTRAINT [PK_MST_ApplicationUser_Permission] PRIMARY KEY CLUSTERED
(
[UserPermissionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_ApplicationUser_Permission] WITH CHECK ADD CONSTRAINT [FK_MST_ApplicationUser_Permission_MST_ApplicationPermission] FOREIGN KEY([ApplicationPermissionId])
REFERENCES [dbo].[MST_ApplicationPermission] ([ApplicationPermissionId])
GO
ALTER TABLE [dbo].[MST_ApplicationUser_Permission] CHECK CONSTRAINT [FK_MST_ApplicationUser_Permission_MST_ApplicationPermission]
GO
ALTER TABLE [dbo].[MST_ApplicationUser_Permission] WITH CHECK ADD CONSTRAINT [FK_MST_ApplicationUser_Permission_MST_ApplicationUser] FOREIGN KEY([ApplicationUserId])
REFERENCES [dbo].[MST_ApplicationUser] ([ApplicationUserId])
GO
ALTER TABLE [dbo].[MST_ApplicationUser_Permission] CHECK CONSTRAINT [FK_MST_ApplicationUser_Permission_MST_ApplicationUser]
GO
ALTER TABLE [dbo].[MST_ApplicationUser_Permission] WITH CHECK ADD CONSTRAINT [FK_MST_ApplicationUser_Permission_MST_Hospital] FOREIGN KEY([HospitalId])
REFERENCES [dbo].[MST_Hospital] ([HospitalID])
GO
ALTER TABLE [dbo].[MST_ApplicationUser_Permission] CHECK CONSTRAINT [FK_MST_ApplicationUser_Permission_MST_Hospital]
GO
ALTER TABLE [dbo].[MST_ApplicationUser_Permission] ADD CONSTRAINT [DF_MST_ApplicationUser_Permission_Active] DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[MST_ApplicationUser_Permission] ADD CONSTRAINT [DF_MST_ApplicationUser_Permission_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_ApplicationUser_Permission] ADD CONSTRAINT [DF_MST_ApplicationUser_Permission_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_ArthroplastyDevice
Description:
Device Information related to Arthroplasty operation.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_ArthroplastyDevice] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_ArthroplastyDevice](
[Internalid] [int] IDENTITY(1,1) NOT NULL,
[Operation] [varchar](50) NULL,
[Bodypart] [varchar](50) NULL,
[DeviceSynonym] [varchar](50) NULL,
[Device] [varchar](50) NULL,
[Lastmodify] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_ArthroplastyDevice] ADD CONSTRAINT [DF_MST_ArthroplastyDevice_Lastmodify] DEFAULT (getdate()) FOR [Lastmodify]
GO
|
======================================================
Table Name:MST_ClinicalDataSourceType
Description:
Standard table which describes types of ClinicalData sources we are getting data.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_ClinicalDataSourceType] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_ClinicalDataSourceType](
[ClinicalDataSourceId] [int] IDENTITY(1,1) NOT NULL,
[ClinicalDataSourceName] [varchar](50) NULL,
[ClinicalDataSourceDesc] [varchar](150) NULL,
[Active] [bit] NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MST_ClinicalDataSourceType] PRIMARY KEY CLUSTERED
(
[ClinicalDataSourceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_ClinicalDataSourceType] ADD CONSTRAINT [DF_MST_ClinicalDataSourceType_Active] DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[MST_ClinicalDataSourceType] ADD CONSTRAINT [DF_MST_ClinicalDataSourceType_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_ClinicalDataSourceType] ADD CONSTRAINT [DF_MST_ClinicalDataSourceType_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_ClinicalDataType
Description:
Standard table which describes types of ClinicalData we are servicing to a Facility.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_ClinicalDataType] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_ClinicalDataType](
[ClinicalDataId] [int] IDENTITY(1,1) NOT NULL,
[ClinicalDataName] [varchar](50) NULL,
[ClinicalDataDesc] [varchar](150) NULL,
[Active] [bit] NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MST_ClinicalDataType] PRIMARY KEY CLUSTERED
(
[ClinicalDataId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_ClinicalDataType] ADD CONSTRAINT [DF_MST_ClinicalDataType_Active] DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[MST_ClinicalDataType] ADD CONSTRAINT [DF_MST_ClinicalDataType_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_ClinicalDataType] ADD CONSTRAINT [DF_MST_ClinicalDataType_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_DeviceExclusion
Description:
Table to Exclude less priority Devices, if one or more devices are there for a Procedure in document.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_DeviceExclusion] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_DeviceExclusion](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[Device] [varchar](100) NULL,
[Priority] [int] NULL,
[GroupId] [int] NULL,
[GroupName] [nvarchar](255) NULL,
[Status] [int] NULL,
[LastModify] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_DeviceExclusion] ADD CONSTRAINT [DF_MST_DeviceExclusion_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[MST_DeviceExclusion] ADD CONSTRAINT [DF_MST_DeviceExclusion_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:MST_ExclusionType
Description:
Standard table which describes types of exclusions to Diagnosis as well as Procedures.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_ExclusionType] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_ExclusionType](
[ExclusionTypeId] [int] NOT NULL,
[ExclusionType] [varchar](30) NULL,
[ExclusionDesc] [varchar](50) NULL,
[RStatus] [tinyint] NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MST_DxExclusionType] PRIMARY KEY CLUSTERED
(
[ExclusionTypeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_ExclusionType] ADD CONSTRAINT [DF_MST_DxExclusionType_RStatus] DEFAULT ((0)) FOR [RStatus]
GO
ALTER TABLE [dbo].[MST_ExclusionType] ADD CONSTRAINT [DF_MST_ExclusionType_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_ExclusionType] ADD CONSTRAINT [DF_MST_ExclusionType_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_Hospital
Description:
Describes information about Hospitals and its related data.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_Hospital] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_Hospital](
[HospitalID] [int] IDENTITY(1,1) NOT NULL,
[HospitalName] [varchar](80) NOT NULL,
[HospitalAliasName] [varchar](80) NULL,
[HospitalGroupID] [int] NOT NULL,
[HospitalDesc] [varchar](100) NULL,
[Active] [int] NOT NULL,
[Address1] [varchar](80) NULL,
[Address2] [varchar](80) NULL,
[Address3] [varchar](80) NULL,
[City] [varchar](80) NULL,
[RegionName] [varchar](80) NULL,
[StateProvince] [varchar](80) NULL,
[Country] [varchar](80) NULL,
[PostalCode] [varchar](8) NULL,
[FaxNumber] [varchar](20) NULL,
[Photo] [varchar](200) NULL,
[CreatedByUserId] [int] NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MST_Hospital] PRIMARY KEY CLUSTERED
(
[HospitalID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
CONSTRAINT [IX_MST_Hospital] UNIQUE NONCLUSTERED
(
[HospitalName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
CONSTRAINT [IX_MST_Hospital_1] UNIQUE NONCLUSTERED
(
[HospitalAliasName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_Hospital] WITH CHECK ADD CONSTRAINT [FK_MST_Hospital_MST_ApplicationUser] FOREIGN KEY([CreatedByUserId])
REFERENCES [dbo].[MST_ApplicationUser] ([ApplicationUserId])
GO
ALTER TABLE [dbo].[MST_Hospital] CHECK CONSTRAINT [FK_MST_Hospital_MST_ApplicationUser]
GO
ALTER TABLE [dbo].[MST_Hospital] WITH CHECK ADD CONSTRAINT [FK_MST_Hospital_MST_HospitalGroup] FOREIGN KEY([HospitalGroupID])
REFERENCES [dbo].[MST_HospitalGroup] ([HospitalGroupId])
GO
ALTER TABLE [dbo].[MST_Hospital] CHECK CONSTRAINT [FK_MST_Hospital_MST_HospitalGroup]
GO
ALTER TABLE [dbo].[MST_Hospital] ADD CONSTRAINT [DF_MST_Hospital_Active] DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[MST_Hospital] ADD CONSTRAINT [DF_MST_Hospital_Photo] DEFAULT ('hospital_default.png') FOR [Photo]
GO
ALTER TABLE [dbo].[MST_Hospital] ADD CONSTRAINT [DF_MST_Hospital_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_Hospital] ADD CONSTRAINT [DF_MST_Hospital_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Description:
Standard table which describes hospital contact information and related to its.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_HospitalContact] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_HospitalContact](
[HospitalContactId] [int] IDENTITY(1,1) NOT NULL,
[Department] [varchar](30) NULL,
[Designation] [varchar](30) NULL,
[Name] [varchar](30) NULL,
[EmailId] [varchar](30) NULL,
[ContactNumber] [varchar](15) NULL,
[ApplicationUserId] [int] NOT NULL,
[HospitalId] [int] NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MST_HospitalContact] PRIMARY KEY CLUSTERED
(
[HospitalContactId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_HospitalContact] ADD CONSTRAINT [DF_MST_HospitalContact_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_HospitalContact] ADD CONSTRAINT [DF_MST_HospitalContact_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_HospitalGroup
Description:
Standard table which describes information about hospitalGroups and its related data.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_HospitalGroup] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_HospitalGroup](
[HospitalGroupId] [int] IDENTITY(1,1) NOT NULL,
[HospitalGroupName] [varchar](80) NULL,
[HospitalGroupDesc] [varchar](100) NULL,
[Active] [bit] NOT NULL,
[Address1] [varchar](80) NULL,
[Address2] [varchar](100) NULL,
[Address3] [varchar](100) NULL,
[City] [varchar](80) NULL,
[RegionName] [varchar](80) NULL,
[StateProvince] [varchar](80) NULL,
[Country] [varchar](80) NULL,
[PostalCode] [varchar](8) NULL,
[Photo] [varchar](200) NULL,
[CreatedByUserId] [int] NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MST_HospitalGroup] PRIMARY KEY CLUSTERED
(
[HospitalGroupId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY],
CONSTRAINT [IX_MST_HospitalGroup] UNIQUE NONCLUSTERED
(
[HospitalGroupName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_HospitalGroup] WITH CHECK ADD CONSTRAINT [FK_MST_HospitalGroup_MST_ApplicationUser] FOREIGN KEY([CreatedByUserId])
REFERENCES [dbo].[MST_ApplicationUser] ([ApplicationUserId])
GO
ALTER TABLE [dbo].[MST_HospitalGroup] CHECK CONSTRAINT [FK_MST_HospitalGroup_MST_ApplicationUser]
GO
ALTER TABLE [dbo].[MST_HospitalGroup] ADD CONSTRAINT [DF_MST_HospitalGroup_Active] DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[MST_HospitalGroup] ADD CONSTRAINT [DF_MST_HospitalGroup_Photo] DEFAULT ('hospital_default.png') FOR [Photo]
GO
ALTER TABLE [dbo].[MST_HospitalGroup] ADD CONSTRAINT [DF_MST_HospitalGroup_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_HospitalGroup] ADD CONSTRAINT [DF_MST_HospitalGroup_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Description:
Standard table which describes hospitalGroup contact information and related to its.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_HospitalGroupContact] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_HospitalGroupContact](
[HospitalGroupContactId] [int] IDENTITY(1,1) NOT NULL,
[Department] [varchar](30) NULL,
[Designation] [varchar](30) NULL,
[Name] [varchar](30) NULL,
[EmailId] [varchar](30) NULL,
[ContactNumber] [varchar](15) NULL,
[ApplicationUserId] [int] NOT NULL,
[HospitalGroupId] [int] NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MST_HospitalGroupContact] PRIMARY KEY CLUSTERED
(
[HospitalGroupContactId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_HospitalGroupContact] ADD CONSTRAINT [DF_MST_HospitalGroupContact_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_HospitalGroupContact] ADD CONSTRAINT [DF_MST_HospitalGroupContact_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_HospitalGroup_Permission
Description:
standard table which describes information about HospitalGroup wise Permissions.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_HospitalGroup_Permission] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MST_HospitalGroup_Permission](
[HGPermissionId] [int] IDENTITY(1,1) NOT NULL,
[ApplicationPermissionId] [int] NOT NULL,
[HospitalGroupId] [int] NOT NULL,
[Active] [bit] NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MST_HospitalGroup_Permission] PRIMARY KEY CLUSTERED
(
[HGPermissionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MST_HospitalGroup_Permission] WITH CHECK ADD CONSTRAINT [FK_MST_HospitalGroup_Permission_MST_ApplicationPermission] FOREIGN KEY([ApplicationPermissionId])
REFERENCES [dbo].[MST_ApplicationPermission] ([ApplicationPermissionId])
GO
ALTER TABLE [dbo].[MST_HospitalGroup_Permission] CHECK CONSTRAINT [FK_MST_HospitalGroup_Permission_MST_ApplicationPermission]
GO
ALTER TABLE [dbo].[MST_HospitalGroup_Permission] WITH CHECK ADD CONSTRAINT [FK_MST_HospitalGroup_Permission_MST_HospitalGroup] FOREIGN KEY([HospitalGroupId])
REFERENCES [dbo].[MST_HospitalGroup] ([HospitalGroupId])
GO
ALTER TABLE [dbo].[MST_HospitalGroup_Permission] CHECK CONSTRAINT [FK_MST_HospitalGroup_Permission_MST_HospitalGroup]
GO
ALTER TABLE [dbo].[MST_HospitalGroup_Permission] ADD CONSTRAINT [DF_MST_HospitalGroup_Permission_Active] DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[MST_HospitalGroup_Permission] ADD CONSTRAINT [DF_MST_HospitalGroup_Permission_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_HospitalGroup_Permission] ADD CONSTRAINT [DF_MST_HospitalGroup_Permission_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_Hospital_KpaiModule
Description:
Standard table but having no data yet......
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_Hospital_KpaiModule] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MST_Hospital_KpaiModule](
[HospitalKpaiModuleId] [int] IDENTITY(1,1) NOT NULL,
[HospitalID] [int] NOT NULL,
[KpaiModuleId] [int] NOT NULL,
[Active] [bit] NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MST_Hospital_KpaiModule] PRIMARY KEY CLUSTERED
(
[HospitalKpaiModuleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MST_Hospital_KpaiModule] WITH CHECK ADD CONSTRAINT [FK_MST_Hospital_KpaiModule_MST_Hospital] FOREIGN KEY([HospitalID])
REFERENCES [dbo].[MST_Hospital] ([HospitalID])
GO
ALTER TABLE [dbo].[MST_Hospital_KpaiModule] CHECK CONSTRAINT [FK_MST_Hospital_KpaiModule_MST_Hospital]
GO
ALTER TABLE [dbo].[MST_Hospital_KpaiModule] WITH CHECK ADD CONSTRAINT [FK_MST_Hospital_KpaiModule_MST_KpaiModule] FOREIGN KEY([KpaiModuleId])
REFERENCES [dbo].[MST_KpaiModule] ([KpaiModuleId])
GO
ALTER TABLE [dbo].[MST_Hospital_KpaiModule] CHECK CONSTRAINT [FK_MST_Hospital_KpaiModule_MST_KpaiModule]
GO
ALTER TABLE [dbo].[MST_Hospital_KpaiModule] ADD CONSTRAINT [DF_MST_Hospital_KpaiModule_Active] DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[MST_Hospital_KpaiModule] ADD CONSTRAINT [DF_Table_1_InsertedDate
InsertedDate
InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_Hospital_KpaiModule] ADD CONSTRAINT [DF_MST_Hospital_KpaiModule_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_Hospital_Permission
Description:
Relation table which describes relation between hospital and permissions.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_Hospital_Permission] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MST_Hospital_Permission](
[HospitalPermissionId] [int] IDENTITY(1,1) NOT NULL,
[ApplicationPermissionId] [int] NOT NULL,
[HospitalId] [int] NOT NULL,
[Active] [bit] NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MST_Hospital_Permission] PRIMARY KEY CLUSTERED
(
[HospitalPermissionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MST_Hospital_Permission] WITH CHECK ADD CONSTRAINT [FK_MST_Hospital_Permission_MST_ApplicationPermission] FOREIGN KEY([ApplicationPermissionId])
REFERENCES [dbo].[MST_ApplicationPermission] ([ApplicationPermissionId])
GO
ALTER TABLE [dbo].[MST_Hospital_Permission] CHECK CONSTRAINT [FK_MST_Hospital_Permission_MST_ApplicationPermission]
GO
ALTER TABLE [dbo].[MST_Hospital_Permission] WITH CHECK ADD CONSTRAINT [FK_MST_Hospital_Permission_MST_Hospital] FOREIGN KEY([HospitalId])
REFERENCES [dbo].[MST_Hospital] ([HospitalID])
GO
ALTER TABLE [dbo].[MST_Hospital_Permission] CHECK CONSTRAINT [FK_MST_Hospital_Permission_MST_Hospital]
GO
ALTER TABLE [dbo].[MST_Hospital_Permission] ADD CONSTRAINT [DF_MST_Hospital_Permission_Active] DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[MST_Hospital_Permission] ADD CONSTRAINT [DF_MST_Hospital_Permission_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_Hospital_Permission] ADD CONSTRAINT [DF_MST_Hospital_Permission_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_Hospital_User
Description:
Standard table which describes hospital wise users and its related information.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_Hospital_User] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_Hospital_User](
[HospitalUserId] [int] IDENTITY(1,1) NOT NULL,
[HospitalAdmin] [bit] NOT NULL,
[EmailId] [varchar](50) NULL,
[ApplicationUserId] [int] NOT NULL,
[ApplicationRoleId] [int] NOT NULL,
[HospitalId] [int] NOT NULL,
[InvitedByUserId] [int] NOT NULL,
[InvitedText] [text] NULL,
[Active] [tinyint] NOT NULL,
[DefaultHospital] [tinyint] NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MST_Hospital_User] PRIMARY KEY CLUSTERED
(
[HospitalUserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_Hospital_User] WITH CHECK ADD CONSTRAINT [FK_MST_Hospital_User_MST_ApplicationRole] FOREIGN KEY([ApplicationRoleId])
REFERENCES [dbo].[MST_ApplicationRole] ([ApplicationRoleId])
GO
ALTER TABLE [dbo].[MST_Hospital_User] CHECK CONSTRAINT [FK_MST_Hospital_User_MST_ApplicationRole]
GO
ALTER TABLE [dbo].[MST_Hospital_User] WITH CHECK ADD CONSTRAINT [FK_MST_Hospital_User_MST_ApplicationUser] FOREIGN KEY([ApplicationUserId])
REFERENCES [dbo].[MST_ApplicationUser] ([ApplicationUserId])
GO
ALTER TABLE [dbo].[MST_Hospital_User] CHECK CONSTRAINT [FK_MST_Hospital_User_MST_ApplicationUser]
GO
ALTER TABLE [dbo].[MST_Hospital_User] WITH CHECK ADD CONSTRAINT [FK_MST_Hospital_User_MST_Hospital] FOREIGN KEY([HospitalId])
REFERENCES [dbo].[MST_Hospital] ([HospitalID])
GO
ALTER TABLE [dbo].[MST_Hospital_User] CHECK CONSTRAINT [FK_MST_Hospital_User_MST_Hospital]
GO
ALTER TABLE [dbo].[MST_Hospital_User] ADD CONSTRAINT [DF_MST_Hospital_User_Admin] DEFAULT ((0)) FOR [HospitalAdmin]
GO
ALTER TABLE [dbo].[MST_Hospital_User] ADD CONSTRAINT [DF_MST_Hospital_User_ApplicationRoleId] DEFAULT ((0)) FOR [ApplicationRoleId]
GO
ALTER TABLE [dbo].[MST_Hospital_User] ADD CONSTRAINT [DF_MST_Hospital_User_Active] DEFAULT ((2)) FOR [Active]
GO
ALTER TABLE [dbo].[MST_Hospital_User] ADD CONSTRAINT [DF_MST_Hospital_User_DefaultHospital] DEFAULT ((0)) FOR [DefaultHospital]
GO
ALTER TABLE [dbo].[MST_Hospital_User] ADD CONSTRAINT [DF_MST_Hospital_User_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_Hospital_User] ADD CONSTRAINT [DF_MST_Hospital_User_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_JobTitle
Description:
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_JobTitle] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_JobTitle](
[JobTitleId] [int] IDENTITY(1,1) NOT NULL,
[JobTitle] [varchar](100) NULL,
[JobTitleDesc] [varchar](150) NULL,
[AliasName] [varchar](100) NULL,
[Icon] [varchar](200) NULL,
[Active] [bit] NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MST_JobTitle] PRIMARY KEY CLUSTERED
(
[JobTitleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_JobTitle] ADD CONSTRAINT [DF_MST_JobTitle_Active] DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[MST_JobTitle] ADD CONSTRAINT [DF_MST_JobTitle_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_JobTitle] ADD CONSTRAINT [DF_MST_JobTitle_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_KpaiModule
Description:
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_KpaiModule] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_KpaiModule](
[KpaiModuleId] [int] IDENTITY(1,1) NOT NULL,
[ModuleName] [varchar](50) NOT NULL,
[ModuleDesc] [varchar](100) NULL,
[ModuleIcon] [varchar](200) NULL,
[Active] [bit] NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MST_KpaiModule] PRIMARY KEY CLUSTERED
(
[KpaiModuleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_KpaiModule] ADD CONSTRAINT [DF_MST_KpaiModule_Active] DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[MST_KpaiModule] ADD CONSTRAINT [DF_MST_KpaiModule_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_KpaiModule] ADD CONSTRAINT [DF_MST_KpaiModule_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_KpaiModule_ClinicalDataType
Description:
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_KpaiModule_ClinicalDataType] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MST_KpaiModule_ClinicalDataType](
[KpaiModuleClinicalDataId] [int] IDENTITY(1,1) NOT NULL,
[ClinicalDataId] [int] NOT NULL,
[ClinicalDataSourceId] [int] NOT NULL,
[KpaiModuleId] [int] NOT NULL,
[HospitalId] [int] NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MST_KpaiModule_ClinicalDataType_1] PRIMARY KEY CLUSTERED
(
[KpaiModuleClinicalDataId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MST_KpaiModule_ClinicalDataType] WITH CHECK ADD CONSTRAINT [FK_MST_KpaiModule_ClinicalDataType_MST_ClinicalDataSourceType] FOREIGN KEY([ClinicalDataSourceId])
REFERENCES [dbo].[MST_ClinicalDataSourceType] ([ClinicalDataSourceId])
GO
ALTER TABLE [dbo].[MST_KpaiModule_ClinicalDataType] CHECK CONSTRAINT [FK_MST_KpaiModule_ClinicalDataType_MST_ClinicalDataSourceType]
GO
ALTER TABLE [dbo].[MST_KpaiModule_ClinicalDataType] WITH CHECK ADD CONSTRAINT [FK_MST_KpaiModule_ClinicalDataType_MST_ClinicalDataType] FOREIGN KEY([ClinicalDataId])
REFERENCES [dbo].[MST_ClinicalDataType] ([ClinicalDataId])
GO
ALTER TABLE [dbo].[MST_KpaiModule_ClinicalDataType] CHECK CONSTRAINT [FK_MST_KpaiModule_ClinicalDataType_MST_ClinicalDataType]
GO
ALTER TABLE [dbo].[MST_KpaiModule_ClinicalDataType] WITH CHECK ADD CONSTRAINT [FK_MST_KpaiModule_ClinicalDataType_MST_Hospital] FOREIGN KEY([HospitalId])
REFERENCES [dbo].[MST_Hospital] ([HospitalID])
GO
ALTER TABLE [dbo].[MST_KpaiModule_ClinicalDataType] CHECK CONSTRAINT [FK_MST_KpaiModule_ClinicalDataType_MST_Hospital]
GO
ALTER TABLE [dbo].[MST_KpaiModule_ClinicalDataType] WITH CHECK ADD CONSTRAINT [FK_MST_KpaiModule_ClinicalDataType_MST_KpaiModule] FOREIGN KEY([KpaiModuleId])
REFERENCES [dbo].[MST_KpaiModule] ([KpaiModuleId])
GO
ALTER TABLE [dbo].[MST_KpaiModule_ClinicalDataType] CHECK CONSTRAINT [FK_MST_KpaiModule_ClinicalDataType_MST_KpaiModule]
GO
ALTER TABLE [dbo].[MST_KpaiModule_ClinicalDataType] ADD CONSTRAINT [DF_MST_KpaiModule_ClinicalDataType_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_KpaiModule_ClinicalDataType] ADD CONSTRAINT [DF_MST_KpaiModule_ClinicalDataType_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_ListFile
Description:
which describes information about All List files which includes ListfileName, DEFFileName, majorType and minorType related to DGPCSJAPE Application.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_ListFile] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_ListFile](
[ListFileId] [int] IDENTITY(1,1) NOT NULL,
[DefName] [varchar](50) NULL,
[DefPath] [varchar](150) NULL,
[ListName] [varchar](50) NULL,
[MajorType] [varchar](50) NULL,
[MinorType] [varchar](50) NULL,
[Active] [tinyint] NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MST_DefFile] PRIMARY KEY CLUSTERED
(
[ListFileId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_ListFile] ADD CONSTRAINT [DF_MST_DefFile_Active] DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[MST_ListFile] ADD CONSTRAINT [DF_MST_ListFile_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_ListFile] ADD CONSTRAINT [DF_MST_ListFile_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_ListTerm
Description:
which describes complete information about all terms or data which are related to ListFiles.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_ListTerm] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_ListTerm](
[ListTermId] [int] IDENTITY(1,1) NOT NULL,
[ListFileId] [int] NOT NULL,
[Term] [varchar](max) NULL,
[STY] [varchar](150) NULL,
[TUI] [varchar](80) NULL,
[CUI] [varchar](80) NULL,
[SnomedCtId] [varchar](80) NULL,
[Active] [tinyint] NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MST_ListTerm] PRIMARY KEY CLUSTERED
(
[ListTermId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_ListTerm] ADD CONSTRAINT [DF_MST_ListTerm_Active] DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[MST_ListTerm] ADD CONSTRAINT [DF_MST_ListTerm_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_ListTerm] ADD CONSTRAINT [DF_MST_ListTerm_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_MedicalServiceTypes
Description:
Standard table which describes information about MedicalServiceTypes.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_MedicalServiceTypes] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_MedicalServiceTypes](
[MSTypeID] [int] IDENTITY(1,1) NOT NULL,
[MSDRG] [int] NOT NULL,
[MSTypeLongDesc] [varchar](100) NULL,
[MSTypeShortDesc] [varchar](100) NULL,
[MSDRGType] [varchar](50) NULL,
[EffectiveFrom] [datetime] NOT NULL,
[EffectiveTo] [datetime] NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MST_MedicalServiceTypes] PRIMARY KEY CLUSTERED
(
[MSTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_MedicalServiceTypes] ADD CONSTRAINT [DF_MST_MedicalServiceTypes_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_MedicalServiceTypes] ADD CONSTRAINT [DF_MST_MedicalServiceTypes_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_PCSBodyPart
Description:
Body parts related data which is used for Porcedure coding.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_PCSBodyPart] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_PCSBodyPart](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[BodyPart] [varchar](100) NULL,
[Site] [varchar](100) NULL,
[Site0] [varchar](100) NULL,
[Site1] [varchar](50) NULL,
[Status] [int] NOT NULL,
[UserName] [varchar](50) NULL,
[LastModify] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_PCSBodyPart] ADD CONSTRAINT [DF_MST_PCSBodyPart_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[MST_PCSBodyPart] ADD CONSTRAINT [DF_MST_PCSBodyPart_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:MST_PCSBodyPartSynonym
Description:
Body part Synonyms related data which is used for Porcedure coding.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_PCSBodyPartSynonym] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_PCSBodyPartSynonym](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[BodyPart] [varchar](250) NOT NULL,
[BodyPartSynonym] [varchar](250) NOT NULL,
[BodyPartLoc] [varchar](50) NULL,
[CommonName] [varchar](50) NULL,
[Operation] [varchar](150) NULL,
[RStatus] [int] NOT NULL,
[UserName] [varchar](50) NULL,
[LastModify] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_PCSBodyPartSynonym] ADD CONSTRAINT [DF_MST_PCSBodyPartSynonym_RStatus] DEFAULT ((0)) FOR [RStatus]
GO
ALTER TABLE [dbo].[MST_PCSBodyPartSynonym] ADD CONSTRAINT [DF_MST_PCSBodyPartSynonym_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:MST_PCSDevice
Description:
Device Information based on Groupid for Procedure coding.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_PCSDevice] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_PCSDevice](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[GroupId] [int] NULL,
[Device] [varchar](500) NULL,
[Device1] [varchar](100) NULL,
[Rstatus] [tinyint] NOT NULL,
[UserName] [varchar](50) NULL,
[LastModify] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [Ncix_MST_PcsDevice] ON [dbo].[MST_PCSDevice]
(
[Device1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MST_PCSDevice] ADD CONSTRAINT [DF_MST_PCSDevice_Rstatus] DEFAULT ((0)) FOR [Rstatus]
GO
ALTER TABLE [dbo].[MST_PCSDevice] ADD CONSTRAINT [DF_MST_PCSDevice_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:MST_PCSExclusionByCode
Description:
Priorities on Procedure Coding, to give most priority codes only for coding remaining are exclude based on priority.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_PCSExclusionByCode] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_PCSExclusionByCode](
[PCSExclusionID] [int] IDENTITY(1,1) NOT NULL,
[ProcCode] [varchar](10) NULL,
[Priority] [tinyint] NOT NULL,
[GroupId] [int] NOT NULL,
[GroupName] [varchar](50) NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MST_PCSExclusionByCode] PRIMARY KEY CLUSTERED
(
[PCSExclusionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [Ncix_MST_PcsExclusionByCode] ON [dbo].[MST_PCSExclusionByCode]
(
[ProcCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MST_PCSExclusionByCode] ADD CONSTRAINT [DF_MST_PCSExclusionByCode_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_PCSExclusionByCode] ADD CONSTRAINT [DF_MST_PCSExclusionByCode_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_PCSExclusionByContent
Description:
Priorities on procedure Content for Procedure Coding, to give most priority Content only for coding remaining are exclude based on priority.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_PCSExclusionByContent] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_PCSExclusionByContent](
[PCSExclusionID] [int] IDENTITY(1,1) NOT NULL,
[PCSId] [varchar](10) NULL,
[Content] [varchar](200) NULL,
[Priority] [tinyint] NOT NULL,
[GroupId] [int] NOT NULL,
[GroupName] [varchar](50) NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MST_PCSExclusionByContent] PRIMARY KEY CLUSTERED
(
[PCSExclusionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_PCSExclusionByContent] ADD CONSTRAINT [DF_MST_PCSExclusionByContent_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_PCSExclusionByContent] ADD CONSTRAINT [DF_MST_PCSExclusionByContent_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_PCSFromReports
Description:
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_PCSFromReports] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_PCSFromReports](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[TestName] [varchar](50) NOT NULL,
[Result] [varchar](100) NULL,
[Code] [varchar](50) NULL,
[Rstatus] [tinyint] NULL,
[UserName] [varchar](50) NULL,
[Lastmodify] [datetime] NOT NULL,
[OperationName] [varchar](100) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_PCSFromReports] ADD CONSTRAINT [DF_MST_PCSFromReports_Rstatus] DEFAULT ((0)) FOR [Rstatus]
GO
ALTER TABLE [dbo].[MST_PCSFromReports] ADD CONSTRAINT [DF_MST_PCSFromReports_Lastmodify] DEFAULT (getdate()) FOR [Lastmodify]
GO
|
======================================================
Table Name:MST_PCSOPBodyPart
Description:
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_PCSOPBodyPart] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_PCSOPBodyPart](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[Operation] [varchar](250) NOT NULL,
[BodyPart] [varchar](250) NOT NULL,
[BodyPartLoc] [varchar](50) NULL,
[Count] [int] NULL,
[BodyPartSynonym] [varchar](50) NULL,
[UserName] [varchar](50) NULL,
[LastModify] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_PCSOPBodyPart] ADD CONSTRAINT [DF_MST_PCSOpBodyPart_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:MST_PCSOperation
Description:
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_PCSOperation] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_PCSOperation](
[Internalid] [int] IDENTITY(1,1) NOT NULL,
[Operation] [varchar](200) NULL,
[Approach] [varchar](50) NULL,
[Code] [varchar](50) NULL,
[RStatus] [int] NOT NULL,
[UserName] [varchar](50) NULL,
[LastModify] [datetime] NULL,
[Gender] [varchar](5) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_PCSOperation] ADD CONSTRAINT [DF_MST_PCSOperation_Status] DEFAULT ((0)) FOR [RStatus]
GO
ALTER TABLE [dbo].[MST_PCSOperation] ADD CONSTRAINT [DF_MST_PCSOperation_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
ALTER TABLE [dbo].[MST_PCSOperation] ADD CONSTRAINT [DF_MST_PCSOperation_Gender] DEFAULT ('U') FOR [Gender]
GO
|
======================================================
Table Name:MST_PCSOPSynonym
Description:
Table to describe All Operastions and their Synonyms which are using for Procedure coding.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_PCSOPSynonym] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_PCSOPSynonym](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[Operation] [varchar](100) NULL,
[OpSynonym] [varchar](100) NULL,
[UserName] [varchar](50) NULL,
[Rstatus] [tinyint] NULL,
[LastModify] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_PCSOPSynonym] ADD CONSTRAINT [DF_MST_PCSOpSynonym_Rstatus] DEFAULT ((0)) FOR [Rstatus]
GO
ALTER TABLE [dbo].[MST_PCSOPSynonym] ADD CONSTRAINT [DF_MST_PCSOpSynonym_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:MST_PCSQualifier
Description:
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_PCSQualifier] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_PCSQualifier](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[GroupId] [int] NULL,
[Qualifier] [varchar](100) NULL,
[Qualifier1] [varchar](100) NULL,
[Rstatus] [tinyint] NOT NULL,
[UserName] [varchar](50) NULL,
[Lastmodify] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [Ncix_MST_PcsQualifier] ON [dbo].[MST_PCSQualifier]
(
[Qualifier1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MST_PCSQualifier] ADD CONSTRAINT [DF_MST_PCSQualifier_Rstatus] DEFAULT ((0)) FOR [Rstatus]
GO
ALTER TABLE [dbo].[MST_PCSQualifier] ADD CONSTRAINT [DF_MST_PCSQualifier_Lastmodify] DEFAULT (getdate()) FOR [Lastmodify]
GO
|
======================================================
Table Name:MST_PCSTabular
Description:
Table for All PCS Tabular Pdf data.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_PCSTabular] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_PCSTabular](
[internalid] [int] IDENTITY(1,1) NOT NULL,
[GroupId] [int] NULL,
[SectionTitle] [varchar](50) NULL,
[Section] [varchar](100) NULL,
[SectionCode] [varchar](20) NULL,
[BodySystemTitle] [varchar](100) NULL,
[BodySystem] [varchar](100) NULL,
[bodySystemCode] [varchar](20) NULL,
[rootTitle] [varchar](100) NULL,
[Operation] [varchar](100) NULL,
[OperationCode] [varchar](20) NULL,
[Definition] [varchar](1000) NULL,
[digit4Title] [varchar](100) NULL,
[BodyPart] [varchar](100) NULL,
[BodyPartCode] [varchar](20) NULL,
[digit5Title] [varchar](100) NULL,
[Approach] [varchar](100) NULL,
[ApproachCode] [varchar](20) NULL,
[digit6Title] [varchar](100) NULL,
[Device] [varchar](100) NULL,
[DeviceCode] [varchar](20) NULL,
[digit7Title] [varchar](100) NULL,
[Qualifier] [varchar](100) NULL,
[QualifierCode] [varchar](20) NULL,
[Status] [int] NULL,
[CODE] [varchar](50) NULL,
[Gender] [tinyint] NOT NULL,
[Neonatal] [tinyint] NOT NULL,
[UserName] [varchar](50) NULL,
[LastModify] [datetime] NOT NULL,
CONSTRAINT [PK__MST_PCSTabular] PRIMARY KEY CLUSTERED
(
[internalid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [Ncix_PcsTabular] ON [dbo].[MST_PCSTabular]
(
[Operation] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MST_PCSTabular] ADD CONSTRAINT [DF_MST_PCSTabular_Gender] DEFAULT ((0)) FOR [Gender]
GO
ALTER TABLE [dbo].[MST_PCSTabular] ADD CONSTRAINT [DF_MST_PCSTabular_Neonatal] DEFAULT ((0)) FOR [Neonatal]
GO
ALTER TABLE [dbo].[MST_PCSTabular] ADD CONSTRAINT [DF_MST_PCSTabular_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:MST_ProcedureStatus
Description:
Table to Know PcsCode and corresponding Status(History) Icdcodes for Procedures.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_ProcedureStatus] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_ProcedureStatus](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[ProcedureName] [varchar](100) NULL,
[PCSCode] [varchar](10) NULL,
[PastCode] [varchar](20) NULL,
[ActualDesc] [varchar](200) NULL,
[LastModify] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [Ncix_MST_ProcedureStatus] ON [dbo].[MST_ProcedureStatus]
(
[PCSCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MST_ProcedureStatus] ADD CONSTRAINT [DF_MST_ProcedureStatus_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:MST_QualifierExclusion
Description:
Qualifiers based on priority for Fusion and Arthroplasty Groups.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_QualifierExclusion] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_QualifierExclusion](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[Qualifier] [varchar](100) NULL,
[Priority] [int] NULL,
[GroupId] [int] NULL,
[GroupName] [nvarchar](255) NULL,
[Status] [int] NULL,
[LastModify] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_QualifierExclusion] ADD CONSTRAINT [DF_MST_QualifierExclusion_Status] DEFAULT ((0)) FOR [Status]
GO
ALTER TABLE [dbo].[MST_QualifierExclusion] ADD CONSTRAINT [DF_MST_QualifierExclusion_LastModify] DEFAULT (getdate()) FOR [LastModify]
GO
|
======================================================
Table Name:MST_StandardBodyParts2
Description:
Table to know the standard body parts names which are used in Procedure coding.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_StandardBodyParts2] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_StandardBodyParts2](
[InternalId] [int] IDENTITY(1,1) NOT NULL,
[OperationName] [varchar](50) NOT NULL,
[StandardBodyPart] [varchar](100) NULL,
[BodySystem] [varchar](50) NULL,
[BodyPart] [varchar](50) NULL,
[BodyPartLoc] [varchar](50) NULL,
[Code] [varchar](10) NULL,
[Excision] [varchar](20) NULL,
[Rstatus] [tinyint] NULL,
[UserName] [varchar](50) NULL,
[Lastmodify] [datetime] NOT NULL,
[Operation2] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_StandardBodyParts2] ADD CONSTRAINT [DF_MST_StandardBodyParts2_Rstatus] DEFAULT ((0)) FOR [Rstatus]
GO
ALTER TABLE [dbo].[MST_StandardBodyParts2] ADD CONSTRAINT [DF_MST_StandardBodyParts2_Lastmodify] DEFAULT (getdate()) FOR [Lastmodify]
GO
|
======================================================
Table Name:MST_TableStatus
Description:
Table to describe about rstatus field from all Tables in HRCM and MasterHrcm data bases.
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_TableStatus] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_TableStatus](
[TableStatusId] [int] IDENTITY(1,1) NOT NULL,
[DBName] [varchar](25) NOT NULL,
[TableName] [varchar](50) NOT NULL,
[ColumnName] [varchar](20) NOT NULL,
[ColumnValue] [tinyint] NOT NULL,
[ColumnDesc] [varchar](150) NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_TableStatus] ADD CONSTRAINT [DF_MST_TableStatus_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_TableStatus] ADD CONSTRAINT [DF_MST_TableStatus_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================
Table Name:MST_UniqueListTerm
Description:
Table Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33 | USE [MasterHRCM]
GO
/****** Object: Table [dbo].[MST_UniqueListTerm] Script Date: 09/03/2019 15:22:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MST_UniqueListTerm](
[UniqueListTermId] [int] IDENTITY(1,1) NOT NULL,
[Term] [varchar](max) NULL,
[STY] [varchar](150) NULL,
[TUI] [varchar](80) NULL,
[CUI] [varchar](80) NULL,
[SnomedCtId] [varchar](80) NULL,
[Active] [tinyint] NOT NULL,
[InsertedDate] [datetime] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_MST_UniqueListTerm] PRIMARY KEY CLUSTERED
(
[UniqueListTermId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MST_UniqueListTerm] ADD CONSTRAINT [DF_MST_UniqueListTerm_Active] DEFAULT ((1)) FOR [Active]
GO
ALTER TABLE [dbo].[MST_UniqueListTerm] ADD CONSTRAINT [DF_MST_UniqueListTerm_InsertedDate] DEFAULT (getutcdate()) FOR [InsertedDate]
GO
ALTER TABLE [dbo].[MST_UniqueListTerm] ADD CONSTRAINT [DF_MST_UniqueListTerm_ModifiedDate] DEFAULT (getutcdate()) FOR [ModifiedDate]
GO
|
======================================================