MasterHrcm View Schema
View Name:ApplicationUserView
Description:
This view describes complete information about User and its related data.
Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14 | USE [MasterHRCM]
GO
/****** Object: View [dbo].[ApplicationUserView] Script Date: 09/03/2019 15:25:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[ApplicationUserView]
AS
select mau.ApplicationUserId,mau.UserName,mau.Password,mau.EmailId,mhu.Active as HospitalUser_Active,mau.SuperAdmin,mau.FullName,mau.Bio,mau.Photo,mau.Lang,mau.TimeZone,
mau.Theme,mau.LastLogin,mau.Active as ApplicationUser_Active,mhu.HospitalAdmin,mhu.ApplicationRoleId,mhu.HospitalId from MST_ApplicationUser mau
left join MST_Hospital_User mhu on mhu.ApplicationUserId=mau.ApplicationUserId
;
GO
|
======================================================
View Name:ClinicalDataSourceView
Description:
Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 | USE [MasterHRCM]
GO
/****** Object: View [dbo].[ClinicalDataSourceView] Script Date: 09/03/2019 15:25:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[ClinicalDataSourceView]
AS
select distinct mcdst.ClinicalDataSourceName,ISNULL(mkmcdt.ClinicalDataSourceId,0) ClinicalDataSourceId,mcdt.ClinicalDataId,mcdt.ClinicalDataName,mcdt.ClinicalDataDesc,mkmcdt.KpaiModuleId
from MST_ClinicalDatatype mcdt
left join MST_KpaiModule_ClinicalDataType mkmcdt on mcdt.ClinicalDataId=mkmcdt.ClinicalDataId
left join dbo.MST_ClinicalDataSourceType mcdst on mcdst.ClinicalDataSourceId=mkmcdt.ClinicalDataSourceId
;
GO
|
======================================================
View Name:HospitalGroupView
Description:
This view describes complete information about Hospital Groups.
Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13 | USE [MasterHRCM]
GO
/****** Object: View [dbo].[HospitalGroupView] Script Date: 09/03/2019 15:25:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[HospitalGroupView]
AS
select distinct HospitalGroupId,HospitalGroupName,HospitalGroupDesc,Active,Address1,Address2,Address3,City,RegionName,StateProvince,Country,PostalCode,
Photo,CreatedByUserId,InsertedDate,ModifiedDate from MST_HospitalGroup
;
GO
|
======================================================
View Name:HospitalView
Description:
This view describes information about Hospital wise users.
Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 | USE [MasterHRCM]
GO
/****** Object: View [dbo].[HospitalView] Script Date: 09/03/2019 15:25:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[HospitalView]
AS
select distinct mh.*,
mhg.hospitalGroupName,mhu.Active HospitalUser_Active,mhu.ApplicationUserId
from MST_Hospital_User mhu
inner join MST_Hospital mh on mh.HospitalID=mhu.HospitalId
inner join MST_HospitalGroup mhg on mhg.HospitalGroupId=mh.HospitalGroupID
;
GO
|
======================================================
View Name:KpaiModuleView
Description:
Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14 | USE [MasterHRCM]
GO
/****** Object: View [dbo].[KpaiModuleView] Script Date: 09/03/2019 15:25:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[KpaiModuleView]
AS
select distinct mkm.KpaiModuleId,mkm.ModuleName,mkm.ModuleDesc,mkm.ModuleIcon,isnull(mhkm.Active,0) Active,mhkm.HospitalID,mkm.InsertedDate,mkm.ModifiedDate
from MST_KpaiModule mkm
left join MST_Hospital_KpaiModule mhkm on mhkm.KpaiModuleId=mkm.KpaiModuleId
;
GO
|
======================================================
View Name:PermissionView
Description:
This view describes information about Permissions and related to its data.
Schema:
1
2
3
4
5
6
7
8
9
10
11
12
13
14 | USE [MasterHRCM]
GO
/****** Object: View [dbo].[PermissionView] Script Date: 09/03/2019 15:25:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[PermissionView]
AS
select distinct ap.ApplicationPermissionId,ap.PermissionCodeName,ap.AliasName,ap.PermissionDesc,ap.ParentId,ap.Active,ap.InsertedDate,ap.ModifiedDate,
arp.ApplicationRoleId,arp.HospitalId from MST_ApplicationPermission ap
left join MST_ApplicationRole_Permission arp on arp.ApplicationPermissionId=ap.ApplicationPermissionId
;
GO
|
======================================================
View Name:RoleView
Description:
This view describes complete information about Application roles and related.
Schema:
1
2
3
4
5
6
7
8
9
10
11
12 | USE [MasterHRCM]
GO
/****** Object: View [dbo].[RoleView] Script Date: 09/03/2019 15:25:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[RoleView]
AS
select distinct ApplicationRoleId,RoleName,HospitalId,Active,InsertedDate,ModifiedDate,RoleDescription from MasterHRCM.dbo.MST_ApplicationRole
;
GO
|
======================================================