CREATE TABLE [dbo].[SecProfileDetail]
(
[SPDPriKey] [int] NOT NULL IDENTITY(1, 1),
[SecProfilePriKey] [int] NOT NULL,
[ModulePriKey] [int] NOT NULL,
[BranchPriKey] [int] NOT NULL,
[AccessPriKey] [int] NOT NULL,
[ManagerFlag] [bit] NOT NULL CONSTRAINT [DF_ManagerFlag] DEFAULT (0)
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SecProfileDetail] ADD CONSTRAINT [CK_SecProfileDetail_ModuleID_AccessID] CHECK ((NOT [dbo].[GetSecModuleName]([ModulePriKey])='AR Credit & Collections' OR [dbo].[GetSecurityAccessID]('Read Only')<>[AccessPriKey]))
GO
ALTER TABLE [dbo].[SecProfileDetail] ADD CONSTRAINT [PK_SecProfileDetail] PRIMARY KEY CLUSTERED  ([SPDPriKey]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SecProfileDetail] ADD CONSTRAINT [IX_Unique_SecProfilePriKey_ModulePriKey_BranchPriKey] UNIQUE NONCLUSTERED  ([SecProfilePriKey], [ModulePriKey], [BranchPriKey]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SecProfileDetail_AccessPriKey] ON [dbo].[SecProfileDetail] ([AccessPriKey]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SecProfileDetail_BranchPriKey] ON [dbo].[SecProfileDetail] ([BranchPriKey]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SecProfileDetail_ModulePriKey] ON [dbo].[SecProfileDetail] ([ModulePriKey]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SecProfileDetail_Module_Branch_Access_Manager] ON [dbo].[SecProfileDetail] ([SecProfilePriKey], [AccessPriKey]) INCLUDE ([BranchPriKey], [ManagerFlag], [ModulePriKey]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_SecProfileDetail_SecProfilePriKey_ModulePriKey] ON [dbo].[SecProfileDetail] ([SecProfilePriKey], [ModulePriKey]) INCLUDE ([AccessPriKey]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SecProfileDetail] ADD CONSTRAINT [FK_SecProfileDetail_SecAccessTypes] FOREIGN KEY ([AccessPriKey]) REFERENCES [dbo].[SecAccessTypes] ([AccessPriKey])
GO
ALTER TABLE [dbo].[SecProfileDetail] ADD CONSTRAINT [FK_SecProfileDetail_BRANCH] FOREIGN KEY ([BranchPriKey]) REFERENCES [dbo].[Branch] ([BranchPriKey])
GO
ALTER TABLE [dbo].[SecProfileDetail] ADD CONSTRAINT [FK_SecProfileDetail_SecModules] FOREIGN KEY ([ModulePriKey]) REFERENCES [dbo].[SecModules] ([ModulePriKey])
GO
ALTER TABLE [dbo].[SecProfileDetail] ADD CONSTRAINT [FK_SecProfileDetail_SecurityProfile] FOREIGN KEY ([SecProfilePriKey]) REFERENCES [dbo].[SecurityProfile] ([SecProfilePriKey])
GO
GRANT SELECT ON  [dbo].[SecProfileDetail] TO [MssExec]
GRANT INSERT ON  [dbo].[SecProfileDetail] TO [MssExec]
GRANT DELETE ON  [dbo].[SecProfileDetail] TO [MssExec]
GRANT UPDATE ON  [dbo].[SecProfileDetail] TO [MssExec]
GO