SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
GO
SET DATEFORMAT YMD
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
-- Pointer used for text / image updates. This might not be needed, but is declared here just in case
DECLARE @pv binary(16)

PRINT(N'Drop constraints from [dbo].[REF_CUSTOM_CONTROL]')
ALTER TABLE [dbo].[REF_CUSTOM_CONTROL] NOCHECK CONSTRAINT [FK_REF_CUSTOM_CONTROL_REF_CUSTOMISATIONS]

PRINT(N'Add rows to [dbo].[REF_CUSTOM_CONTROL]')
INSERT INTO [dbo].[REF_CUSTOM_CONTROL] ([TableName], [FieldName], [CustomisationID], [Caption], [TabOrder], [ListOrder], [Enabled], [ControlTop], [ControlLeft], [ControlWidth], [ControlHeight], [Mask], [DefaultValue], [ControlType], [Compulsory], [LookupID], [ToolTipText], [ControlBackColor], [FontColor], [FontStyle], [FontBold], [FontItalic], [FontUnderline], [FontSize], [Visible], [MaximumLength], [CharacterTable], [Characters], [ShowCodesInCombo], [Justification], [MinValue], [MaxValue], [IntDigits], [DecDigits], [ValidateMask], [ShowZero], [fmtThousands], [EntityUnique], [TreatZeroAsBlank], [ObjectChooserTable], [OverideComboTextField], [MiscSelectorTable], [WidthOffset], [DisplayInHeader], [KeyField], [LabelAbove], [HideLabelOnFrame], [ImageMode], [ImageFilePattern], [ScaleImage], [MaintainAspect], [MiscSelectorSortFields], [MailMergeReport], [NULLDisplayField], [DisplayTab], [SecurityIdentifier], [ExcelTextOutput], [Hidden], [Locked], [DataProtectionTrigger]) VALUES (N'DT_EMPLOYEE', N'MANAGEREMAIL', 0, N'Manageremail', 0, 0, 1, 0, 0, 4, 1, N'', NULL, 0, 0, NULL, N'Manageremail', -2147483633, -2147483630, N'Calibri', 0, 0, 0, 9, 1, 0, N'', -1, 0, 0, NULL, NULL, NULL, NULL, 0, 1, N',', 0, 0, NULL, NULL, NULL, 0, 0, 0, 0, 0, 0, N'', 0, 0, NULL, NULL, NULL, NULL, NULL, 0, 0, 0, 0)

PRINT(N'Add rows to [dbo].[REF_SQLFIELDS]')
INSERT INTO [dbo].[REF_SQLFIELDS] ([Table_Name], [Field_Name], [CustomisationID], [Version], [Code], [OnByDefault], [InLine]) VALUES (N'DT_EMPLOYEE', N'ManagerEmail', 0, 1, N'SELECT TOP 1 CAST(DTA.email as nvarchar(128))
FROM DT_ADDRESS DTA
INNER JOIN LNK_PERSON_ADDRESS LPA
ON DTA.Entity_ID = LPA.Right_Join
AND [CalcDate] BETWEEN DTA.Entity_Start_Date AND COALESCE(DTA.Entity_End_Date, [CalcDate])
AND [CalcDate] BETWEEN LPA.Start_Date AND COALESCE(LPA.End_Date, [CalcDate])
INNER JOIN LNK_EMPLOYEE_PERSON LEP
ON LEP.Right_Join = LPA.Left_Join
AND [CalcDate] BETWEEN LEP.Start_Date AND COALESCE(LEP.End_Date, [CalcDate])
INNER JOIN LNK_EMPLOYEE_CONTRACT LEC
ON LEP.Left_Join = LEC.Left_Join
AND [CalcDate] BETWEEN LEC.Start_Date AND COALESCE(LEC.End_Date, [CalcDate])
INNER JOIN LNK_JOB_CONTRACT_VIRTUAL LJC
ON LJC.Right_Join = LEC.Right_Join
AND [CalcDate] BETWEEN LJC.Start_Date AND COALESCE(LJC.End_Date, [CalcDate])
AND LJC.Left_Join = Manager_Man.ManagingJob 
ORDER BY LJC.Start_Date DESC{{J}}[#J#Manager]', 0, 1)

PRINT(N'Add constraints to [dbo].[REF_CUSTOM_CONTROL]')
ALTER TABLE [dbo].[REF_CUSTOM_CONTROL] CHECK CONSTRAINT [FK_REF_CUSTOM_CONTROL_REF_CUSTOMISATIONS]
COMMIT TRANSACTION
GO