Tables [dbo].[XmlSystemPayrollAccountMap]
Properties
PropertyValue
CollationSQL_Latin1_General_CP1_CI_AS
Row Count (~)0
Created9:10:47 AM Thursday, May 23, 2024
Last Modified9:10:52 AM Thursday, May 23, 2024
Columns
NameData TypeMax Length (Bytes)Allow NullsIdentity
Cluster Primary Key PK_XmlSystemPayrollAccountMap: XmlSystemPayrollAccountMapIdXmlSystemPayrollAccountMapIdint4
No
1 - 1
Foreign Keys FK_XmlSystemPayrollAccountMap_XmlSystem: [dbo].[XmlSystem].XmlSystemFIDIndexes IX_XmlSystemPayrollAccountMap_AllMappingFields: XmlSystemFID\AuthorityTypesFID\ServTypeClassFID\CommTypeFID\MoveTypeGroupsFID\SysUserFID\ServTypeFID\MoveTypeFID\LaborTypeFID\IsServiceLead\BranchFIDIndexes IX_XmlSystemPayrollAccountMap_XmlSystem_LaborType: XmlSystemFID\LaborTypeFIDIndexes IX_XmlSystemPayrollAccountMap_XmlSystem_LaborType_Branch: XmlSystemFID\LaborTypeFID\BranchFIDIndexes IX_XmlSystemPayrollAccountMap_XmlSystem_SysUser: XmlSystemFID\SysUserFIDIndexes IX_XmlSystemPayrollAccountMap_XmlSystem_SysUser_Branch: XmlSystemFID\SysUserFID\BranchFIDXmlSystemFIDint4
No
Foreign Keys FK_XmlSystemPayrollAccountMap_AuthorityTypes: [dbo].[AuthorityTypes].AuthorityTypesFIDIndexes IX_XmlSystemPayrollAccountMap_XmlSystem_AuthorityTypes_ServTypeClassGLCodeMap_CommType_MoveTypeGroups: AuthorityTypesFID\ServTypeClassFID\CommTypeFID\MoveTypeGroupsFIDIndexes IX_XmlSystemPayrollAccountMap_XmlSystem_AuthorityTypes_ServTypeClassGLCodeMap_CommType_MoveTypeGroups_Branch: AuthorityTypesFID\ServTypeClassFID\CommTypeFID\MoveTypeGroupsFID\BranchFIDIndexes IX_XmlSystemPayrollAccountMap_AllMappingFields: XmlSystemFID\AuthorityTypesFID\ServTypeClassFID\CommTypeFID\MoveTypeGroupsFID\SysUserFID\ServTypeFID\MoveTypeFID\LaborTypeFID\IsServiceLead\BranchFIDAuthorityTypesFIDint4
Yes
Foreign Keys FK_XmlSystemPayrollAccountMap_ServTypeClass: [dbo].[ServTypeClass].ServTypeClassFIDIndexes IX_XmlSystemPayrollAccountMap_XmlSystem_AuthorityTypes_ServTypeClassGLCodeMap_CommType_MoveTypeGroups: AuthorityTypesFID\ServTypeClassFID\CommTypeFID\MoveTypeGroupsFIDIndexes IX_XmlSystemPayrollAccountMap_XmlSystem_AuthorityTypes_ServTypeClassGLCodeMap_CommType_MoveTypeGroups_Branch: AuthorityTypesFID\ServTypeClassFID\CommTypeFID\MoveTypeGroupsFID\BranchFIDIndexes IX_XmlSystemPayrollAccountMap_AllMappingFields: XmlSystemFID\AuthorityTypesFID\ServTypeClassFID\CommTypeFID\MoveTypeGroupsFID\SysUserFID\ServTypeFID\MoveTypeFID\LaborTypeFID\IsServiceLead\BranchFIDServTypeClassFIDint4
Yes
Foreign Keys FK_XmlSystemPayrollAccountMap_CommType: [dbo].[CommType].CommTypeFIDIndexes IX_XmlSystemPayrollAccountMap_XmlSystem_AuthorityTypes_ServTypeClassGLCodeMap_CommType_MoveTypeGroups: AuthorityTypesFID\ServTypeClassFID\CommTypeFID\MoveTypeGroupsFIDIndexes IX_XmlSystemPayrollAccountMap_XmlSystem_AuthorityTypes_ServTypeClassGLCodeMap_CommType_MoveTypeGroups_Branch: AuthorityTypesFID\ServTypeClassFID\CommTypeFID\MoveTypeGroupsFID\BranchFIDIndexes IX_XmlSystemPayrollAccountMap_AllMappingFields: XmlSystemFID\AuthorityTypesFID\ServTypeClassFID\CommTypeFID\MoveTypeGroupsFID\SysUserFID\ServTypeFID\MoveTypeFID\LaborTypeFID\IsServiceLead\BranchFIDCommTypeFIDint4
Yes
Foreign Keys FK_XmlSystemPayrollAccountMap_MoveTypeGroups: [dbo].[MoveTypeGroups].MoveTypeGroupsFIDIndexes IX_XmlSystemPayrollAccountMap_XmlSystem_AuthorityTypes_ServTypeClassGLCodeMap_CommType_MoveTypeGroups: AuthorityTypesFID\ServTypeClassFID\CommTypeFID\MoveTypeGroupsFIDIndexes IX_XmlSystemPayrollAccountMap_XmlSystem_AuthorityTypes_ServTypeClassGLCodeMap_CommType_MoveTypeGroups_Branch: AuthorityTypesFID\ServTypeClassFID\CommTypeFID\MoveTypeGroupsFID\BranchFIDIndexes IX_XmlSystemPayrollAccountMap_AllMappingFields: XmlSystemFID\AuthorityTypesFID\ServTypeClassFID\CommTypeFID\MoveTypeGroupsFID\SysUserFID\ServTypeFID\MoveTypeFID\LaborTypeFID\IsServiceLead\BranchFIDMoveTypeGroupsFIDint4
Yes
Foreign Keys FK_XmlSystemPayrollAccountMap_SysUser: [dbo].[Sysuser].SysUserFIDIndexes IX_XmlSystemPayrollAccountMap_AllMappingFields: XmlSystemFID\AuthorityTypesFID\ServTypeClassFID\CommTypeFID\MoveTypeGroupsFID\SysUserFID\ServTypeFID\MoveTypeFID\LaborTypeFID\IsServiceLead\BranchFIDIndexes IX_XmlSystemPayrollAccountMap_XmlSystem_SysUser: XmlSystemFID\SysUserFIDIndexes IX_XmlSystemPayrollAccountMap_XmlSystem_SysUser_Branch: XmlSystemFID\SysUserFID\BranchFIDSysUserFIDint4
Yes
Foreign Keys FK_XmlSystemPayrollAccountMap_ServType: [dbo].[ServType].ServTypeFIDIndexes IX_XmlSystemPayrollAccountMap_AllMappingFields: XmlSystemFID\AuthorityTypesFID\ServTypeClassFID\CommTypeFID\MoveTypeGroupsFID\SysUserFID\ServTypeFID\MoveTypeFID\LaborTypeFID\IsServiceLead\BranchFIDServTypeFIDint4
Yes
Foreign Keys FK_XmlSystemPayrollAccountMap_MoveType: [dbo].[MoveType].MoveTypeFIDIndexes IX_XmlSystemPayrollAccountMap_AllMappingFields: XmlSystemFID\AuthorityTypesFID\ServTypeClassFID\CommTypeFID\MoveTypeGroupsFID\SysUserFID\ServTypeFID\MoveTypeFID\LaborTypeFID\IsServiceLead\BranchFIDMoveTypeFIDint4
Yes
Foreign Keys FK_XmlSystemPayrollAccountMap_LaborType: [dbo].[LaborType].LaborTypeFIDIndexes IX_XmlSystemPayrollAccountMap_AllMappingFields: XmlSystemFID\AuthorityTypesFID\ServTypeClassFID\CommTypeFID\MoveTypeGroupsFID\SysUserFID\ServTypeFID\MoveTypeFID\LaborTypeFID\IsServiceLead\BranchFIDIndexes IX_XmlSystemPayrollAccountMap_XmlSystem_LaborType: XmlSystemFID\LaborTypeFIDIndexes IX_XmlSystemPayrollAccountMap_XmlSystem_LaborType_Branch: XmlSystemFID\LaborTypeFID\BranchFIDLaborTypeFIDint4
Yes
Foreign Keys FK_XmlSystemPayrollAccountMap_Branch: [dbo].[Branch].BranchFIDIndexes IX_XmlSystemPayrollAccountMap_XmlSystem_AuthorityTypes_ServTypeClassGLCodeMap_CommType_MoveTypeGroups_Branch: AuthorityTypesFID\ServTypeClassFID\CommTypeFID\MoveTypeGroupsFID\BranchFIDIndexes IX_XmlSystemPayrollAccountMap_AllMappingFields: XmlSystemFID\AuthorityTypesFID\ServTypeClassFID\CommTypeFID\MoveTypeGroupsFID\SysUserFID\ServTypeFID\MoveTypeFID\LaborTypeFID\IsServiceLead\BranchFIDIndexes IX_XmlSystemPayrollAccountMap_XmlSystem_LaborType_Branch: XmlSystemFID\LaborTypeFID\BranchFIDIndexes IX_XmlSystemPayrollAccountMap_XmlSystem_SysUser_Branch: XmlSystemFID\SysUserFID\BranchFIDBranchFIDint4
Yes
ExternalCodevarchar(50)50
No
SecondaryExternalCodevarchar(50)50
Yes
Rankint4
Yes
Indexes IX_XmlSystemPayrollAccountMap_AllMappingFields: XmlSystemFID\AuthorityTypesFID\ServTypeClassFID\CommTypeFID\MoveTypeGroupsFID\SysUserFID\ServTypeFID\MoveTypeFID\LaborTypeFID\IsServiceLead\BranchFIDIsServiceLeadbit1
Yes
Indexes Indexes
NameColumnsUnique
Cluster Primary Key PK_XmlSystemPayrollAccountMap: XmlSystemPayrollAccountMapIdPK_XmlSystemPayrollAccountMapXmlSystemPayrollAccountMapId
Yes
IX_XmlSystemPayrollAccountMap_AllMappingFieldsXmlSystemFID, AuthorityTypesFID, ServTypeClassFID, CommTypeFID, MoveTypeGroupsFID, SysUserFID, ServTypeFID, MoveTypeFID, LaborTypeFID, IsServiceLead, BranchFID
Yes
IX_XmlSystemPayrollAccountMap_XmlSystem_AuthorityTypes_ServTypeClassGLCodeMap_CommType_MoveTypeGroupsAuthorityTypesFID, ServTypeClassFID, CommTypeFID, MoveTypeGroupsFID
IX_XmlSystemPayrollAccountMap_XmlSystem_AuthorityTypes_ServTypeClassGLCodeMap_CommType_MoveTypeGroups_BranchAuthorityTypesFID, ServTypeClassFID, CommTypeFID, MoveTypeGroupsFID, BranchFID
IX_XmlSystemPayrollAccountMap_XmlSystem_LaborTypeXmlSystemFID, LaborTypeFID
IX_XmlSystemPayrollAccountMap_XmlSystem_LaborType_BranchXmlSystemFID, LaborTypeFID, BranchFID
IX_XmlSystemPayrollAccountMap_XmlSystem_SysUserXmlSystemFID, SysUserFID
IX_XmlSystemPayrollAccountMap_XmlSystem_SysUser_BranchXmlSystemFID, SysUserFID, BranchFID
Foreign Keys Foreign Keys
NameDeleteColumns
FK_XmlSystemPayrollAccountMap_AuthorityTypesAuthorityTypesFID->[dbo].[AuthorityTypes].[AuthPriKey]
FK_XmlSystemPayrollAccountMap_BranchBranchFID->[dbo].[Branch].[BranchPriKey]
FK_XmlSystemPayrollAccountMap_CommTypeCommTypeFID->[dbo].[CommType].[PriKey]
FK_XmlSystemPayrollAccountMap_LaborTypeLaborTypeFID->[dbo].[LaborType].[PriKey]
FK_XmlSystemPayrollAccountMap_MoveTypeMoveTypeFID->[dbo].[MoveType].[PriKey]
FK_XmlSystemPayrollAccountMap_MoveTypeGroupsMoveTypeGroupsFID->[dbo].[MoveTypeGroups].[MTGroupPriKey]
FK_XmlSystemPayrollAccountMap_ServTypeClassServTypeClassFID->[dbo].[ServTypeClass].[ServTypeClassID]
FK_XmlSystemPayrollAccountMap_ServTypeServTypeFID->[dbo].[ServType].[ServiceID]
FK_XmlSystemPayrollAccountMap_SysUserSysUserFID->[dbo].[Sysuser].[SysUserID]
FK_XmlSystemPayrollAccountMap_XmlSystemCascadeXmlSystemFID->[dbo].[XmlSystem].[XmlSystemID]
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
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
Uses