
------------------------------------------------------------------------------
-- ADVANTAGE 2004.3.8
-- OPTIONS and APPLICATION SINCE ADVANTAGE 2004.3.7
-- 31 MAY 2008
------------------------------------------------------------------------------


--************************************************
--******     NB. NB. NB. RUN ONLY ONCE     *******
--************************************************


-- USE ADV2004
-- GO



----------------------------------------------------------------
-- New System Options
----------------------------------------------------------------

IF NOT EXISTS
    (
        SELECT Sys_AW_Options_ID
        FROM Sys_AW_Options
        WHERE Control_Type = 'CheckBox' AND
              Control_Name = 'chkHideCost' AND
              Control_Index = 0
    )
    BEGIN
        INSERT Sys_AW_Options
            (
                Control_Type,
                Control_Name,
                Control_Index,
                Control_Value
            )
        VALUES
            (
                'CheckBox',
                'chkHideCost',
                0,
                '0'
            )
    END

------------------------------------

IF NOT EXISTS
    (
        SELECT Sys_AW_Options_ID
        FROM Sys_AW_Options
        WHERE Control_Type = 'CheckBox' AND
              Control_Name = 'chkHideGP' AND
              Control_Index = 0
    )
    BEGIN
        INSERT Sys_AW_Options
            (
                Control_Type,
                Control_Name,
                Control_Index,
                Control_Value
            )
        VALUES
            (
                'CheckBox',
                'chkHideGP',
                0,
                '0'
            )
    END

------------------------------------

IF NOT EXISTS
    (
        SELECT Sys_AW_Options_ID
        FROM Sys_AW_Options
        WHERE Control_Type = 'CheckBox' AND
              Control_Name = 'chkSellZeroQty' AND
              Control_Index = 0
    )
    BEGIN
        INSERT Sys_AW_Options
            (
                Control_Type,
                Control_Name,
                Control_Index,
                Control_Value
            )
        VALUES
            (
                'CheckBox',
                'chkSellZeroQty',
                0,
                '0'
            )
    END

------------------------------------

IF NOT EXISTS
    (
        SELECT Sys_AW_Options_ID
        FROM Sys_AW_Options
        WHERE Control_Type = 'CheckBox' AND
              Control_Name = 'chkAutoAdvancePeriod' AND
              Control_Index = 4
    )
    BEGIN
        INSERT Sys_AW_Options
            (
                Control_Type,
                Control_Name,
                Control_Index,
                Control_Value
            )
        VALUES
            (
                'CheckBox',
                'chkAutoAdvancePeriod',
                4,
                '1'
            )
    END

------------------------------------

IF NOT EXISTS
    (
        SELECT Sys_AW_Options_ID
        FROM Sys_AW_Options
        WHERE Control_Type = 'TextBox' AND
              Control_Name = 'txtCountryRegionCode' AND
              Control_Index = 0
    )
    BEGIN
        INSERT Sys_AW_Options
            (
                Control_Type,
                Control_Name,
                Control_Index,
                Control_Value
            )
        VALUES
            (
                'TextBox',
                'txtCountryRegionCode',
                0,
                '27'
            )
    END

------------------------------------

IF NOT EXISTS
    (
        SELECT Sys_AW_Options_ID
        FROM Sys_AW_Options
        WHERE Control_Type = 'TextBox' AND
              Control_Name = 'txtCountryDateFormat' AND
              Control_Index = 0
    )
    BEGIN
        INSERT Sys_AW_Options
            (
                Control_Type,
                Control_Name,
                Control_Index,
                Control_Value
            )
        VALUES
            (
                'TextBox',
                'txtCountryDateFormat',
                0,
                'yyyy/MM/dd'
            )
    END

------------------------------------

IF NOT EXISTS
    (
        SELECT Sys_AW_Options_ID
        FROM Sys_AW_Options
        WHERE Control_Type = 'CheckBox' AND
              Control_Name = 'chkPOSOverrideMinGP' AND
              Control_Index = 0
    )
    BEGIN
        INSERT Sys_AW_Options
            (
                Control_Type,
                Control_Name,
                Control_Index,
                Control_Value
            )
        VALUES
            (
                'CheckBox',
                'chkPOSOverrideMinGP',
                0,
                '0'
            )
    END

------------------------------------

IF NOT EXISTS
    (
        SELECT Sys_AW_Options_ID
        FROM Sys_AW_Options
        WHERE Control_Type = 'CheckBox' AND
              Control_Name = 'chkProfitProtectorNormal' AND
              Control_Index = 0
    )
    BEGIN
        INSERT Sys_AW_Options
            (
                Control_Type,
                Control_Name,
                Control_Index,
                Control_Value
            )
        VALUES
            (
                'CheckBox',
                'chkProfitProtectorNormal',
                0,
                '1'
            )
    END

------------------------------------

IF NOT EXISTS
    (
        SELECT Sys_AW_Options_ID
        FROM Sys_AW_Options
        WHERE Control_Type = 'CheckBox' AND
              Control_Name = 'chkProfitProtectorCostPlus' AND
              Control_Index = 0
    )
    BEGIN
        INSERT Sys_AW_Options
            (
                Control_Type,
                Control_Name,
                Control_Index,
                Control_Value
            )
        VALUES
            (
                'CheckBox',
                'chkProfitProtectorCostPlus',
                0,
                '0'
            )
    END

------------------------------------

IF NOT EXISTS
    (
        SELECT Sys_AW_Options_ID
        FROM Sys_AW_Options
        WHERE Control_Type = 'CheckBox' AND
              Control_Name = 'chkProfitProtectorPromotion' AND
              Control_Index = 0
    )
    BEGIN
        INSERT Sys_AW_Options
            (
                Control_Type,
                Control_Name,
                Control_Index,
                Control_Value
            )
        VALUES
            (
                'CheckBox',
                'chkProfitProtectorPromotion',
                0,
                '0'
            )
    END

------------------------------------

IF NOT EXISTS
    (
        SELECT Sys_AW_Options_ID
        FROM Sys_AW_Options
        WHERE Control_Type = 'CheckBox' AND
              Control_Name = 'chkProfitProtectorContract' AND
              Control_Index = 0
    )
    BEGIN
        INSERT Sys_AW_Options
            (
                Control_Type,
                Control_Name,
                Control_Index,
                Control_Value
            )
        VALUES
            (
                'CheckBox',
                'chkProfitProtectorContract',
                0,
                '0'
            )
    END

------------------------------------

IF NOT EXISTS
    (
        SELECT Sys_AW_Options_ID
        FROM Sys_AW_Options
        WHERE Control_Type = 'CheckBox' AND
              Control_Name = 'chkProfitProtectorDiscount' AND
              Control_Index = 0
    )
    BEGIN
        INSERT Sys_AW_Options
            (
                Control_Type,
                Control_Name,
                Control_Index,
                Control_Value
            )
        VALUES
            (
                'CheckBox',
                'chkProfitProtectorDiscount',
                0,
                '0'
            )
    END

------------------------------------

IF NOT EXISTS
    (
        SELECT Sys_AW_Options_ID
        FROM Sys_AW_Options
        WHERE Control_Type = 'CheckBox' AND
              Control_Name = 'chkProductIDCatalogOrder' AND
              Control_Index = 0
    )
    BEGIN
        INSERT Sys_AW_Options
            (
                Control_Type,
                Control_Name,
                Control_Index,
                Control_Value
            )
        VALUES
            (
                'CheckBox',
                'chkProductIDCatalogOrder',
                0,
                '0'
            )
    END

------------------------------------

IF NOT EXISTS
    (
        SELECT Sys_AW_Options_ID
        FROM Sys_AW_Options
        WHERE Control_Type = 'CheckBox' AND
              Control_Name = 'chkSellNegativeUnitPrice' AND
              Control_Index = 0
    )
    BEGIN
        INSERT Sys_AW_Options
            (
                Control_Type,
                Control_Name,
                Control_Index,
                Control_Value
            )
        VALUES
            (
                'CheckBox',
                'chkSellNegativeUnitPrice',
                0,
                '0'
            )
    END

------------------------------------

IF NOT EXISTS
    (
        SELECT Sys_AW_Options_ID
        FROM Sys_AW_Options
        WHERE Control_Type = 'CheckBox' AND
              Control_Name = 'chkSellNonStockBelowCost' AND
              Control_Index = 0
    )
    BEGIN
        INSERT Sys_AW_Options
            (
                Control_Type,
                Control_Name,
                Control_Index,
                Control_Value
            )
        VALUES
            (
                'CheckBox',
                'chkSellNonStockBelowCost',
                0,
                '0'
            )
    END

------------------------------------

IF NOT EXISTS
    (
        SELECT Sys_AW_Options_ID
        FROM Sys_AW_Options
        WHERE Control_Type = 'CheckBox' AND
              Control_Name = 'chkPOSClearAll' AND
              Control_Index = 0
    )
    BEGIN
        INSERT Sys_AW_Options
            (
                Control_Type,
                Control_Name,
                Control_Index,
                Control_Value
            )
        VALUES
            (
                'CheckBox',
                'chkPOSClearAll',
                0,
                '0'
            )
    END

--------------------------------------------------


----------------------------------------------------------------
-- Retired System Options
----------------------------------------------------------------

IF EXISTS
    (
        SELECT Sys_AW_Options_ID
        FROM Sys_AW_Options
        WHERE Control_Type = 'CheckBox' AND
              Control_Name = 'chkSetShortDateFormat' AND
              Control_Index = 0
    )
    BEGIN
        DELETE
        FROM Sys_AW_Options
        WHERE Control_Type = 'CheckBox' AND
              Control_Name = 'chkSetShortDateFormat' AND
              Control_Index = 0
    END

------------------------------------

-- OLD chkMinPOSGPPerc System Option deleted in procedure 4

------------------------------------

IF EXISTS
    (
        SELECT Sys_AW_Options_ID
        FROM Sys_AW_Options
        WHERE Control_Type = 'CheckBox' AND
              Control_Name = 'chkPOSOverrideDiscount' AND
              Control_Index = 0
    )
    BEGIN
        DELETE
        FROM Sys_AW_Options
        WHERE Control_Type = 'CheckBox' AND
              Control_Name = 'chkPOSOverrideDiscount' AND
              Control_Index = 0
    END

----------------------------------------------------------------
-- New Applications
----------------------------------------------------------------

DECLARE @AppID int 


IF NOT EXISTS
    (
        SELECT Sys_AW_Applications_ID
        FROM Sys_AW_Applications
        WHERE Description = 'Mail Merge' AND
              Form_Name = 'frmCustomerMailMerge'
    )
    BEGIN
        INSERT Sys_AW_Applications
            (
                Description,
                Sys_AW_Modules_ID,
                Application_Seq,
                Form_Name,
                Date_Captured,
                User_ID,
                Slu_AW_Active_ID
            )
        VALUES
            (
                'Mail Merge',
                3,
                19,
                'frmCustomerMailMerge',
                getdate(),
                1,
                1
            )

        SELECT @AppID = Scope_Identity()
        
        EXEC sp_AW_Insert_Apps_Per_Role 2, @AppID, 1, 1

    END


------------------------------------

IF NOT EXISTS
    (
        SELECT Sys_AW_Applications_ID
        FROM Sys_AW_Applications
        WHERE Description = 'Stock Level Settings' AND
              Form_Name = 'frmStockLevelSettings'
    )
    BEGIN
        INSERT Sys_AW_Applications
            (
                Description,
                Sys_AW_Modules_ID,
                Application_Seq,
                Form_Name,
                Date_Captured,
                User_ID,
                Slu_AW_Active_ID
            )
        VALUES
            (
                'Stock Level Settings',
                2,
                17,
                'frmStockLevelSettings',
                getdate(),
                1,
                1
            )

        SELECT @AppID = Scope_Identity()
        
        EXEC sp_AW_Insert_Apps_Per_Role 2, @AppID, 1, 1

    END

--------------------------------------------------


----------------------------------------------------------------------------
-- Retired Applications
----------------------------------------------------------------------------

UPDATE Sys_AW_Applications
SET Slu_AW_Active_ID = 2
WHERE Description = 'Purchase Discounts' AND
      Form_Name = 'frmPurchaseDiscounts'

------------------------------------

----------------------------------------------------------------------------
-- Lookup Editor
----------------------------------------------------------------------------

IF NOT EXISTS
    (
        SELECT Sys_AW_Lookup_Editor_ID
        FROM Sys_AW_Lookup_Editor
        WHERE TableName = 'Lup_AO_Supplier_Groups' AND
              Caption = 'Supplier Groups'
    )
    BEGIN
        INSERT Sys_AW_Lookup_Editor
            (
                Sys_AW_Lookup_Editor_ID,
                TableName,
                Caption,
                ColumnWidths
            )
        VALUES
            (
                72,
                'Lup_AO_Supplier_Groups',
                'Supplier Groups',
                '0,7275,0,0,0,1005'
            )
    END

------------------------------------

IF NOT EXISTS
    (
        SELECT Sys_AW_Lookup_Editor_ID
        FROM Sys_AW_Lookup_Editor
        WHERE TableName = 'Lup_AO_Supplier_Types' AND
              Caption = 'Supplier Types'
    )
    BEGIN
        INSERT Sys_AW_Lookup_Editor
            (
                Sys_AW_Lookup_Editor_ID,
                TableName,
                Caption,
                ColumnWidths
            )
        VALUES
            (
                73,
                'Lup_AO_Supplier_Types',
                'Supplier Types',
                '0,7275,0,0,0,1005'
            )
    END

------------------------------------

--> REMOVE Min_GP_Percentage edit column in Lup_AP_Product_Departments
UPDATE Sys_AW_Lookup_Editor
SET ColumnWidths = '0,7275,0,0,0,1005'
WHERE TableName = 'Lup_AP_Product_Departments'