CREATE TABLE [dbo].[XmlSystemPayrollAccountMap]
(
[XmlSystemPayrollAccountMapId] [int] NOT NULL IDENTITY(1, 1),
[XmlSystemFID] [int] NOT NULL,
[AuthorityTypesFID] [int] NULL,
[ServTypeClassFID] [int] NULL,
[CommTypeFID] [int] NULL,
[MoveTypeGroupsFID] [int] NULL,
[SysUserFID] [int] NULL,
[ServTypeFID] [int] NULL,
[MoveTypeFID] [int] NULL,
[LaborTypeFID] [int] NULL,
[BranchFID] [int] NULL,
[ExternalCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[SecondaryExternalCode] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Rank] [int] NULL,
[IsServiceLead] [bit] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[XmlSystemPayrollAccountMap] ADD CONSTRAINT [PK_XmlSystemPayrollAccountMap] PRIMARY KEY CLUSTERED  ([XmlSystemPayrollAccountMapId]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[XmlSystemPayrollAccountMap] ADD CONSTRAINT [IX_XmlSystemPayrollAccountMap_AllMappingFields] UNIQUE NONCLUSTERED  ([XmlSystemFID], [AuthorityTypesFID], [ServTypeClassFID], [CommTypeFID], [MoveTypeGroupsFID], [SysUserFID], [ServTypeFID], [MoveTypeFID], [LaborTypeFID], [IsServiceLead], [BranchFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_XmlSystemPayrollAccountMap_XmlSystem_AuthorityTypes_ServTypeClassGLCodeMap_CommType_MoveTypeGroups] ON [dbo].[XmlSystemPayrollAccountMap] ([AuthorityTypesFID], [ServTypeClassFID], [CommTypeFID], [MoveTypeGroupsFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_XmlSystemPayrollAccountMap_XmlSystem_AuthorityTypes_ServTypeClassGLCodeMap_CommType_MoveTypeGroups_Branch] ON [dbo].[XmlSystemPayrollAccountMap] ([AuthorityTypesFID], [ServTypeClassFID], [CommTypeFID], [MoveTypeGroupsFID], [BranchFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_XmlSystemPayrollAccountMap_XmlSystem_LaborType] ON [dbo].[XmlSystemPayrollAccountMap] ([XmlSystemFID], [LaborTypeFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_XmlSystemPayrollAccountMap_XmlSystem_LaborType_Branch] ON [dbo].[XmlSystemPayrollAccountMap] ([XmlSystemFID], [LaborTypeFID], [BranchFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_XmlSystemPayrollAccountMap_XmlSystem_SysUser] ON [dbo].[XmlSystemPayrollAccountMap] ([XmlSystemFID], [SysUserFID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_XmlSystemPayrollAccountMap_XmlSystem_SysUser_Branch] ON [dbo].[XmlSystemPayrollAccountMap] ([XmlSystemFID], [SysUserFID], [BranchFID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[XmlSystemPayrollAccountMap] ADD CONSTRAINT [FK_XmlSystemPayrollAccountMap_AuthorityTypes] FOREIGN KEY ([AuthorityTypesFID]) REFERENCES [dbo].[AuthorityTypes] ([AuthPriKey])
GO
ALTER TABLE [dbo].[XmlSystemPayrollAccountMap] ADD CONSTRAINT [FK_XmlSystemPayrollAccountMap_Branch] FOREIGN KEY ([BranchFID]) REFERENCES [dbo].[Branch] ([BranchPriKey])
GO
ALTER TABLE [dbo].[XmlSystemPayrollAccountMap] ADD CONSTRAINT [FK_XmlSystemPayrollAccountMap_CommType] FOREIGN KEY ([CommTypeFID]) REFERENCES [dbo].[CommType] ([PriKey])
GO
ALTER TABLE [dbo].[XmlSystemPayrollAccountMap] ADD CONSTRAINT [FK_XmlSystemPayrollAccountMap_LaborType] FOREIGN KEY ([LaborTypeFID]) REFERENCES [dbo].[LaborType] ([PriKey])
GO
ALTER TABLE [dbo].[XmlSystemPayrollAccountMap] ADD CONSTRAINT [FK_XmlSystemPayrollAccountMap_MoveType] FOREIGN KEY ([MoveTypeFID]) REFERENCES [dbo].[MoveType] ([PriKey])
GO
ALTER TABLE [dbo].[XmlSystemPayrollAccountMap] ADD CONSTRAINT [FK_XmlSystemPayrollAccountMap_MoveTypeGroups] FOREIGN KEY ([MoveTypeGroupsFID]) REFERENCES [dbo].[MoveTypeGroups] ([MTGroupPriKey])
GO
ALTER TABLE [dbo].[XmlSystemPayrollAccountMap] ADD CONSTRAINT [FK_XmlSystemPayrollAccountMap_ServTypeClass] FOREIGN KEY ([ServTypeClassFID]) REFERENCES [dbo].[ServTypeClass] ([ServTypeClassID])
GO
ALTER TABLE [dbo].[XmlSystemPayrollAccountMap] ADD CONSTRAINT [FK_XmlSystemPayrollAccountMap_ServType] FOREIGN KEY ([ServTypeFID]) REFERENCES [dbo].[ServType] ([ServiceID])
GO
ALTER TABLE [dbo].[XmlSystemPayrollAccountMap] ADD CONSTRAINT [FK_XmlSystemPayrollAccountMap_SysUser] FOREIGN KEY ([SysUserFID]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
ALTER TABLE [dbo].[XmlSystemPayrollAccountMap] ADD CONSTRAINT [FK_XmlSystemPayrollAccountMap_XmlSystem] FOREIGN KEY ([XmlSystemFID]) REFERENCES [dbo].[XmlSystem] ([XmlSystemID]) ON DELETE CASCADE
GO
GRANT SELECT ON  [dbo].[XmlSystemPayrollAccountMap] TO [MssExec]
GRANT INSERT ON  [dbo].[XmlSystemPayrollAccountMap] TO [MssExec]
GRANT DELETE ON  [dbo].[XmlSystemPayrollAccountMap] TO [MssExec]
GRANT UPDATE ON  [dbo].[XmlSystemPayrollAccountMap] TO [MssExec]
GO