USE [Elyse_DB]
GO
/****** Object:  Table [people].[duty_functions]    Script Date: Sat 05-09-2026 7:01:54 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [people].[duty_functions](
	[function_id] [bigint] IDENTITY(1,1) NOT NULL,
	[mnem] [nvarchar](10) NULL,
	[name] [nvarchar](50) NOT NULL,
	[description] [nvarchar](max) NULL,
	[list_position] [int] NULL,
	[created] [datetime2](7) NOT NULL,
	[created_by] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_function_list] PRIMARY KEY CLUSTERED 
(
	[function_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [UN_function_name]    Script Date: Sat 05-09-2026 7:01:55 AM ******/
ALTER TABLE [people].[duty_functions] ADD  CONSTRAINT [UN_function_name] UNIQUE NONCLUSTERED 
(
	[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
ALTER TABLE [people].[duty_functions] ADD  CONSTRAINT [DF_duty_functions_created]  DEFAULT (sysdatetime()) FOR [created]
GO
ALTER TABLE [people].[duty_functions] ADD  CONSTRAINT [DF_duty_functions_created_by]  DEFAULT (original_login()) FOR [created_by]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Name of a function.  e.g. ''Production Manager''.' , @level0type=N'SCHEMA',@level0name=N'people', @level1type=N'TABLE',@level1name=N'duty_functions', @level2type=N'COLUMN',@level2name=N'name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'List position is an integer which allows a list of options in a table to be set in any order.  The value is unconstrained and must be set at the application layer.' , @level0type=N'SCHEMA',@level0name=N'people', @level1type=N'TABLE',@level1name=N'duty_functions', @level2type=N'COLUMN',@level2name=N'list_position'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Function name must be unique.' , @level0type=N'SCHEMA',@level0name=N'people', @level1type=N'TABLE',@level1name=N'duty_functions', @level2type=N'CONSTRAINT',@level2name=N'UN_function_name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This list stores duty functions.  e.g. organisational positions or functions such as ''Authoriser of manufacturing SOPs''.

COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2023' , @level0type=N'SCHEMA',@level0name=N'people', @level1type=N'TABLE',@level1name=N'duty_functions'
GO