------------------------------------------------------------------------------
-- ADVANTAGE 2004.4.7.2
-- DB OBJECT CHANGES
-- 20/09/2009
------------------------------------------------------------------------------



-------------------------------------------------------------------------------
-- INSERT DEFAULT VALUES for NEWLY CREATED COLUMN in 0.sql !!! Cannot do it there !!!
-------------------------------------------------------------------------------

--1. Modify AO_Suppliers table as follows
    IF EXISTS
        (
            SELECT * FROM SysObjects O
                INNER JOIN SysColumns C
                    ON O.ID = C.ID
            WHERE ObjectProperty(O.ID,'IsUserTable') = 1 AND
                  O.Name='AO_Suppliers' AND
                  C.Name='Sys_AC_Account_Types_ID'
        ) 
        BEGIN
            UPDATE AO_Suppliers
            SET Sys_AC_Account_Types_ID = 2
        END


-------------------------------------------------------------------------------
-- NEW TABLES
-------------------------------------------------------------------------------


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AP_Stock_Transfer_Details]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[AP_Stock_Transfer_Details]
GO

CREATE TABLE [dbo].[AP_Stock_Transfer_Details] (
	[AP_Stock_Transfer_Details_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[AC_Customers_ID] [int] NULL ,
	[AP_Products_ID] [int] NULL ,
	[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Product_Code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Quantity] [decimal](9, 3) NULL ,
	[Back_Order] [decimal](9, 3) NULL ,
	[Original_Price] [money] NULL ,
	[Unit_Price] [money] NULL ,
	[Discount_Amount] [money] NULL ,
	[Amount] [money] NULL ,
	[Tax_Rate] [decimal](9, 3) NULL ,
	[Tax_Amount] [money] NULL ,
	[Extended_Amount] [money] NULL ,
	[Unit_Cost] [money] NULL ,
	[Date_Captured] [datetime] NULL ,
	[User_ID] [int] NULL ,
	[AW_User_Companies_ID] [int] NOT NULL ,
	[Touched] [timestamp] NULL ,
	[Slu_AW_Active_ID] [tinyint] NULL 
) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [CI_AP_Stock_Transfer_Details] ON [dbo].[AP_Stock_Transfer_Details]([AC_Customers_ID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AP_Stock_Transfer_Details] ADD 
	CONSTRAINT [PK_AP_Stock_Transfer_Details] PRIMARY KEY  NONCLUSTERED 
	(
		[AP_Stock_Transfer_Details_ID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_AP_Stock_Transfer_Details_AP_Products_ID] ON [dbo].[AP_Stock_Transfer_Details]([AP_Products_ID]) ON [PRIMARY]
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AP_Stock_Transfer_Details].[Amount]'
GO

EXEC sp_bindefault N'[dbo].[df_User_Companies]', N'[AP_Stock_Transfer_Details].[AW_User_Companies_ID]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AP_Stock_Transfer_Details].[Back_Order]'
GO

EXEC sp_bindefault N'[dbo].[df_Date_Captured]', N'[AP_Stock_Transfer_Details].[Date_Captured]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AP_Stock_Transfer_Details].[Discount_Amount]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AP_Stock_Transfer_Details].[Extended_Amount]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AP_Stock_Transfer_Details].[Original_Price]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AP_Stock_Transfer_Details].[Quantity]'
GO

EXEC sp_bindefault N'[dbo].[df_Active]', N'[AP_Stock_Transfer_Details].[Slu_AW_Active_ID]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AP_Stock_Transfer_Details].[Tax_Amount]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AP_Stock_Transfer_Details].[Tax_Rate]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AP_Stock_Transfer_Details].[Unit_Cost]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AP_Stock_Transfer_Details].[Unit_Price]'
GO

EXEC sp_bindefault N'[dbo].[df_User_ID]', N'[AP_Stock_Transfer_Details].[User_ID]'
GO

setuser
GO


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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AP_Stock_Transfer_Log]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[AP_Stock_Transfer_Log]
GO

CREATE TABLE [dbo].[AP_Stock_Transfer_Log] (
	[AP_Stock_Transfer_Log_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[AS_Transactions_ID] [int] NULL ,
	[AC_Customers_ID] [int] NULL ,
	[AP_Products_ID] [int] NULL ,
	[AB_GL_Transactions_ID] [int] NULL ,
	[AB_Transaction_Ref] [int] NULL ,
	[AB_Source_Ref] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Transaction_Date] [datetime] NULL ,
	[Quantity] [decimal](12, 3) NULL ,
	[Original_Price] [money] NULL ,
	[Unit_Cost] [money] NULL ,
	[Unit_Price] [money] NULL ,
	[Discount_Amount] [money] NULL ,
	[Amount] [money] NULL ,
	[Tax_Amount] [money] NULL ,
	[Extended_Amount] [money] NOT NULL ,
	[AO_GRV_ID] [int] NULL ,
	[Date_Received] [datetime] NULL ,
	[SIT_User_ID] [int] NULL ,
	[Date_Captured] [datetime] NULL ,
	[TRF_User_ID] [int] NULL ,
	[AW_User_Companies_ID] [int] NOT NULL ,
	[Touched] [timestamp] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AP_Stock_Transfer_Log] WITH NOCHECK ADD 
	CONSTRAINT [PK_AP_Stock_Transfer_Log] PRIMARY KEY  CLUSTERED 
	(
		[AP_Stock_Transfer_Log_ID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [IDX_AP_Stock_Transfer_Log_AS_Transactions_ID] ON [dbo].[AP_Stock_Transfer_Log]([AS_Transactions_ID]) ON [PRIMARY]
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AP_Stock_Transfer_Log].[Amount]'
GO

EXEC sp_bindefault N'[dbo].[df_User_Companies]', N'[AP_Stock_Transfer_Log].[AW_User_Companies_ID]'
GO

EXEC sp_bindefault N'[dbo].[df_Date_Updated]', N'[AP_Stock_Transfer_Log].[Date_Captured]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AP_Stock_Transfer_Log].[Discount_Amount]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AP_Stock_Transfer_Log].[Extended_Amount]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AP_Stock_Transfer_Log].[Original_Price]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AP_Stock_Transfer_Log].[Quantity]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AP_Stock_Transfer_Log].[Tax_Amount]'
GO

EXEC sp_bindefault N'[dbo].[df_Date_Captured]', N'[AP_Stock_Transfer_Log].[Transaction_Date]'
GO

EXEC sp_bindefault N'[dbo].[df_User_ID]', N'[AP_Stock_Transfer_Log].[TRF_User_ID]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AP_Stock_Transfer_Log].[Unit_Cost]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AP_Stock_Transfer_Log].[Unit_Price]'
GO

setuser
GO

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


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AS_Enterprise_GP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[AS_Enterprise_GP]
GO

CREATE TABLE [dbo].[AS_Enterprise_GP] (
	[AS_Enterprise_GP_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Product_Code] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Pack_Size] [decimal](9, 3) NULL ,
	[TotalSales] [money] NULL ,
	[TotalCost] [money] NULL ,
	[Total] [money] NULL ,
	[Filter] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [IX_AS_Enterprise_GP_Search] ON [dbo].[AS_Enterprise_GP]([Filter], [Product_Code], [Pack_Size]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AS_Enterprise_GP] ADD 
	CONSTRAINT [PK_AS_Enterprise_GP] PRIMARY KEY  NONCLUSTERED 
	(
		[AS_Enterprise_GP_ID]
	)  ON [PRIMARY] 
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_One]', N'[AS_Enterprise_GP].[Pack_Size]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AS_Enterprise_GP].[Total]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AS_Enterprise_GP].[TotalCost]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AS_Enterprise_GP].[TotalSales]'
GO

setuser
GO





-------------------------------------------------------------------------------
-- NEW REPORTS
-------------------------------------------------------------------------------

    IF NOT EXISTS
        (
            SELECT Sys_AW_Reports_ID
            FROM Sys_AW_Reports
            WHERE Report_No = '114' AND
                  Method_Name = 'Show_SalesCustomerFilter'
        ) 
        BEGIN
            INSERT Sys_AW_Reports
                (
                    Sys_AW_Modules_ID,
                    Type_ID,
                    Location,
                    Report_No,
                    Report_Name,
                    Description,
                    Object_DLL,
                    Class_Name,
                    Method_Name
                )
            VALUES
                (
                    1,
                    0,
                    'Sales Reports',
                    '114',
                    'Customer Sales by Customer Filter',
                    'Customer Sales by Customer Filter',
                    'SpSSalesReports',
                    'CInterface',
                    'Show_SalesCustomerFilter'
                )
        END

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

    IF NOT EXISTS
        (
            SELECT Sys_AW_Reports_ID
            FROM Sys_AW_Reports
            WHERE Report_No = '193' AND
                  Method_Name = 'Show_frmSalesEnterpriseGP'
        ) 
        BEGIN
            INSERT Sys_AW_Reports
                (
                    Sys_AW_Modules_ID,
                    Type_ID,
                    Location,
                    Report_No,
                    Report_Name,
                    Description,
                    Object_DLL,
                    Class_Name,
                    Method_Name
                )
            VALUES
                (
                    1,
                    1,
                    'Enterprise Sales Reports',
                    '193',
                    'Enterprise Gross Profit',
                    'Enterprise Gross Profit',
                    'SpSSalesReports',
                    'CInterface',
                    'Show_frmSalesEnterpriseGP'
                )
        END

-------------------------------------------------------------------------------
-- TABLE MODS
-------------------------------------------------------------------------------

    IF NOT EXISTS
        (
            SELECT Sys_AW_Transaction_Types_ID
            FROM Sys_AW_Transaction_Types
            WHERE Sys_AW_Transaction_Types_ID = 23 AND
                  Description = 'Stock Transfer'
        ) 
        BEGIN
            INSERT Sys_AW_Transaction_Types
                (
                    Description,
                    Type_Code,
                    POS_Used,
                    Cash_Up,
                    Factor
                )
            VALUES
                (
                    'Stock Transfer',
                    'SJTRF',
                    0,
                    0,
                    1
                )
        END

-------------------------------------------------------------------------------
-- VALIDATE if AB_Control_Accounts.AB_Control_Accounts_ID is an IDENTITY column
--     (Some LEGACY ADV2004 databases ARE NOT SET as IDENTITY)
-------------------------------------------------------------------------------

-- -- 	IF NOT EXISTS
-- --         (
-- --             SELECT * FROM syscolumns WHERE id = Object_ID('AB_Control_Accounts') and colstat & 1 = 1
-- --         )
-- -- 	  BEGIN
--             BEGIN TRANSACTION
--                 SET QUOTED_IDENTIFIER ON
--                 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
--                 SET ARITHABORT ON
--                 SET NUMERIC_ROUNDABORT OFF
--                 SET CONCAT_NULL_YIELDS_NULL ON
--                 SET ANSI_NULLS ON
--                 SET ANSI_PADDING ON
--                 SET ANSI_WARNINGS ON
--             COMMIT
-- 
--             BEGIN TRANSACTION
--                 CREATE TABLE dbo.Tmp_AB_Control_Accounts
--                 	(
--                     	AB_Control_Accounts_ID smallint NOT NULL IDENTITY (1, 1),
--                     	Control_Number smallint NOT NULL,
--                     	Description varchar(40) NULL,
--                     	User_Description varchar(40) NULL,
--                     	AB_Accounts_ID int NULL,
--                     	Control_Type tinyint NULL,
--                     	Date_Captured datetime NULL,
--                     	User_ID int NULL,
--                     	AW_User_Companies_ID int NOT NULL,
--                     	Touched timestamp NULL,
--                     	Slu_AW_Active_ID tinyint NOT NULL
--                 	)  ON [PRIMARY]
--                 GO
--                 EXECUTE sp_bindefault N'dbo.df_Numeric_Zero', N'Tmp_AB_Control_Accounts.Control_Number'
--                 GO
--                 EXECUTE sp_bindefault N'dbo.df_Numeric_Zero', N'Tmp_AB_Control_Accounts.Control_Type'
--                 GO
--                 EXECUTE sp_bindefault N'dbo.df_Date_Captured', N'Tmp_AB_Control_Accounts.Date_Captured'
--                 GO
--                 EXECUTE sp_bindefault N'dbo.df_User_ID', N'Tmp_AB_Control_Accounts.User_ID'
--                 GO
--                 EXECUTE sp_bindefault N'dbo.df_User_Companies', N'Tmp_AB_Control_Accounts.AW_User_Companies_ID'
--                 GO
--                 EXECUTE sp_bindefault N'dbo.df_Active', N'Tmp_AB_Control_Accounts.Slu_AW_Active_ID'
--                 GO
--                 SET IDENTITY_INSERT dbo.Tmp_AB_Control_Accounts ON
--                 GO
--                 IF EXISTS(SELECT * FROM dbo.AB_Control_Accounts)
--                     EXEC('INSERT INTO dbo.Tmp_AB_Control_Accounts (AB_Control_Accounts_ID, Control_Number, Description, User_Description, AB_Accounts_ID, Control_Type, Date_Captured, User_ID, AW_User_Companies_ID, Slu_AW_Active_ID)
--                     SELECT AB_Control_Accounts_ID, Control_Number, Description, User_Description, AB_Accounts_ID, Control_Type, Date_Captured, User_ID, AW_User_Companies_ID, Slu_AW_Active_ID FROM dbo.AB_Control_Accounts TABLOCKX')
--                 GO
--                 SET IDENTITY_INSERT dbo.Tmp_AB_Control_Accounts OFF
--                 GO
--                 DROP TABLE dbo.AB_Control_Accounts
--                 GO
--                 EXECUTE sp_rename N'dbo.Tmp_AB_Control_Accounts', N'AB_Control_Accounts', 'OBJECT'
--                 GO
--                 CREATE UNIQUE CLUSTERED INDEX idx_Control_Accounts_Control_No ON dbo.AB_Control_Accounts
--                 	(
--                 	Control_Number
--                 	) ON [PRIMARY]
--                 GO
--                 CREATE UNIQUE NONCLUSTERED INDEX PK_AB_Control_Accounts ON dbo.AB_Control_Accounts
--                 	(
--                 	AB_Control_Accounts_ID
--                 	) ON [PRIMARY]
--                 GO
--             COMMIT
-- --	  END



--------------------------------------------------------------
-- SET Sales Control as a Subsidiary Control Account
--------------------------------------------------------------
    IF EXISTS
        (
            SELECT AB_Control_Accounts_ID
            FROM AB_Control_Accounts
            WHERE Control_Number = 102
        )
        BEGIN
            UPDATE AB_Control_Accounts
            SET Control_Type = 1
            WHERE Control_Number = 102
        END



--------------------------------------------------------------
--> CHECK IF AB_Control_Accounts table has IDENTITY Column defined
--------------------------------------------------------------

    DECLARE @Identity_Exists int


    SET @Identity_Exists = 0


	IF EXISTS
        (
            SELECT * FROM syscolumns WHERE id = Object_ID('AB_Control_Accounts') and colstat & 1 = 1
        )
    BEGIN
        SET @Identity_Exists = 1
    END

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


--------------------------------------------------------------
--> INSERT NEW Stock-in-Transit GL Account and Control Account
--------------------------------------------------------------

    DECLARE @SIT_ID int

    SET @SIT_ID = 0


    IF NOT EXISTS
        (
            SELECT AB_Accounts_ID
            FROM AB_Accounts
            WHERE Description = 'Stock-in-Transit' AND
                  Sys_AB_Account_Categories_ID = 7
        )
        BEGIN
            INSERT AB_Accounts
                (
                    Description,
                    Sys_AB_Account_Categories_ID
                )
            VALUES
                (
                    'Stock-in-Transit',
                    7
                )

            SELECT @SIT_ID = Scope_Identity()
        END


    IF @SIT_ID = 0
        BEGIN
            SELECT @SIT_ID = AB_Accounts_ID
            FROM AB_Accounts
            WHERE Description = 'Stock-in-Transit' AND
                  Sys_AB_Account_Categories_ID = 7
        END


    IF @SIT_ID = 0
        BEGIN
			SELECT @SIT_ID = AB_Accounts_ID
            FROM AB_Control_Accounts
            WHERE Control_Number = 601
        END

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


    IF @Identity_Exists = 1
        BEGIN
            IF NOT EXISTS
                (
                    SELECT AB_Control_Accounts_ID
                    FROM AB_Control_Accounts
                    WHERE Control_Number = 602
                )
                BEGIN
                    INSERT AB_Control_Accounts
                        (
                            Control_Number,
                            Description,
                            User_Description,
                            AB_Accounts_ID,
                            Control_Type
                        )
                    VALUES
                        (
                            602,
                            'Stock-in-Transit',
                            'Stock-in-Transit',
                            @SIT_ID,
                            0
                        )
                END
        END
    ELSE
        BEGIN
            IF NOT EXISTS
                (
                    SELECT AB_Control_Accounts_ID
                    FROM AB_Control_Accounts
                    WHERE Control_Number = 602
                )
                BEGIN
                    INSERT AB_Control_Accounts
                        (
                            AB_Control_Accounts_ID,
                            Control_Number,
                            Description,
                            User_Description,
                            AB_Accounts_ID,
                            Control_Type
                        )
                    VALUES
                        (
                            21,
                            602,
                            'Stock-in-Transit',
                            'Stock-in-Transit',
                            @SIT_ID,
                            0
                        )
                END
        END


--------------------------------------------------------------
--> INSERT NEW Stock Transfer Income GL Account and Control Account
--------------------------------------------------------------

    DECLARE @STI_ID int

    SET @STI_ID = 0


    IF NOT EXISTS
        (
            SELECT AB_Accounts_ID
            FROM AB_Accounts
            WHERE Description = 'Stock Transfer Income' AND
                  Sys_AB_Account_Categories_ID = 14
        )
        BEGIN
            INSERT AB_Accounts
                (
                    Description,
                    Sys_AB_Account_Categories_ID
                )
            VALUES
                (
                    'Stock Transfer Income',
                    14
                )

            SELECT @STI_ID = Scope_Identity()
        END


    IF @STI_ID = 0
        BEGIN
            SELECT @STI_ID = AB_Accounts_ID
            FROM AB_Accounts
            WHERE Description = 'Stock Transfer Income' AND
                  Sys_AB_Account_Categories_ID = 14
        END


    IF @STI_ID = 0
        BEGIN
			SELECT @STI_ID = AB_Accounts_ID
            FROM AB_Control_Accounts
            WHERE Control_Number = 106
        END

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


    IF @Identity_Exists = 1
        BEGIN
            IF NOT EXISTS
                (
                    SELECT AB_Control_Accounts_ID
                    FROM AB_Control_Accounts
                    WHERE Control_Number = 108
                )
                BEGIN
                    INSERT AB_Control_Accounts
                        (
                            Control_Number,
                            Description,
                            User_Description,
                            AB_Accounts_ID,
                            Control_Type
                        )
                    VALUES
                        (
                            108,
                            'Stock Transfer Income',
                            'Stock Transfer Income',
                            @STI_ID,
                            0
                        )
                END
        END
    ELSE
        BEGIN
            IF NOT EXISTS
                (
                    SELECT AB_Control_Accounts_ID
                    FROM AB_Control_Accounts
                    WHERE Control_Number = 108
                )
                BEGIN
                    INSERT AB_Control_Accounts
                        (
                            AB_Control_Accounts_ID,
                            Control_Number,
                            Description,
                            User_Description,
                            AB_Accounts_ID,
                            Control_Type
                        )
                    VALUES
                        (
                            22,
                            108,
                            'Stock Transfer Income',
                            'Stock Transfer Income',
                            @STI_ID,
                            0
                        )
                END
        END


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

-- Replace in Sys_AW_Payment_Methods 12 = Foreign Currency with 12 = Debit Order

UPDATE Sys_AW_Payment_Methods
SET Description = 'Debit Order'
WHERE Sys_AW_Payment_Methods_ID = 12


-------------------------------------------------------------------------------
-- DELETE REDUNDANT TABLES
-------------------------------------------------------------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AP_Stock_Transfers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[AP_Stock_Transfers]
GO


----------------------------------------------------
--> NEW INDEXES
----------------------------------------------------



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