------------------------------------------------------------------------------
-- ADVANTAGE 2004.3.7
-- DB OBJECT CHANGES SINCE ADVANTAGE 2004.3.6
-- 13 OCT 2008
------------------------------------------------------------------------------


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

IF EXISTS
    (
        SELECT * FROM SysObjects O
            INNER JOIN SysColumns C
                ON O.ID=C.ID
        WHERE ObjectProperty(O.ID,'IsUserTable') = 1 AND
              O.Name='AC_Customers' AND
              C.Name='AW_YesNo_CostPlus_Billing_ID'
    ) 
    BEGIN
        UPDATE AC_Customers
        SET AW_YesNo_CostPlus_Billing_ID = 0
    END

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

IF EXISTS
    (
        SELECT * FROM SysObjects O
            INNER JOIN SysColumns C
                ON O.ID=C.ID
        WHERE ObjectProperty(O.ID,'IsUserTable') = 1 AND
              O.Name='AC_Customers' AND
              C.Name='Lup_AC_Markup_Categories_ID'
    ) 
    BEGIN
        UPDATE AC_Customers
        SET Lup_AC_Markup_Categories_ID = 1
    END

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

IF EXISTS
    (
        SELECT * FROM SysObjects O
            INNER JOIN SysColumns C
                ON O.ID=C.ID
        WHERE ObjectProperty(O.ID,'IsUserTable') = 1 AND
              O.Name='AP_Products' AND
              C.Name='Lup_AP_Markup_Groups_ID'
    ) 
    BEGIN
        UPDATE AP_Products
        SET Lup_AP_Markup_Groups_ID = 1
    END

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

IF EXISTS
    (
        SELECT * FROM SysObjects O
            INNER JOIN SysColumns C
                ON O.ID=C.ID
        WHERE ObjectProperty(O.ID,'IsUserTable') = 1 AND
              O.Name='AP_Products' AND
              C.Name='Slu_AW_YesNo_Cost_Plus_Item'
    ) 
    BEGIN
        UPDATE AP_Products
        SET Slu_AW_YesNo_Cost_Plus_Item = 0
    END

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

IF EXISTS
    (
        SELECT * FROM SysObjects O
            INNER JOIN SysColumns C
                ON O.ID=C.ID
        WHERE ObjectProperty(O.ID,'IsUserTable') = 1 AND
              O.Name='AW_Users' AND
              C.Name='IsDataExporter'
    ) 
    BEGIN
        UPDATE AW_Users
        SET IsDataExporter = 0
    END

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

IF EXISTS
    (
        SELECT * FROM SysObjects O
            INNER JOIN SysColumns C
                ON O.ID=C.ID
        WHERE ObjectProperty(O.ID,'IsUserTable') = 1 AND
              O.Name='AB_Accounts' AND
              C.Name='Lup_AB_Account_Groups_ID'
    ) 
    BEGIN
        UPDATE AB_Accounts
        SET Lup_AB_Account_Groups_ID = 1
    END

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

IF EXISTS
    (
        SELECT * FROM SysObjects O
            INNER JOIN SysColumns C
                ON O.ID=C.ID
        WHERE ObjectProperty(O.ID,'IsUserTable') = 1 AND
              O.Name='AB_Accounts' AND
              C.Name='Lup_AB_Account_Divisions_ID'
    ) 
    BEGIN
        UPDATE AB_Accounts
        SET Lup_AB_Account_Divisions_ID = 1
    END



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


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_AS_Markup_Categories_Groups_Lup_AC_Markup_Categories]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[AS_Markup_Categories_Groups] DROP CONSTRAINT FK_AS_Markup_Categories_Groups_Lup_AC_Markup_Categories
GO

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

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

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

CREATE TABLE [dbo].[AS_Markup_Categories_Groups] (
	[AS_Markup_Categories_Groups_ID] [smallint] IDENTITY (1, 1) NOT NULL ,
	[Lup_AP_Markup_Groups_ID] [smallint] NULL ,
	[Lup_AC_Markup_Categories_ID] [smallint] NULL ,
	[Markup_Percentage] [decimal](9, 3) 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 TABLE [dbo].[Lup_AC_Markup_Categories] (
	[Lup_AC_Markup_Categories_ID] [smallint] IDENTITY (1, 1) NOT NULL ,
	[Description] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS 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 TABLE [dbo].[Lup_AP_Markup_Groups] (
	[Lup_AP_Markup_Groups_ID] [smallint] IDENTITY (1, 1) NOT NULL ,
	[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS 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

ALTER TABLE [dbo].[Lup_AP_Markup_Groups] WITH NOCHECK ADD 
	CONSTRAINT [PK_Lup_AP_Markup_Groups] PRIMARY KEY  CLUSTERED 
	(
		[Lup_AP_Markup_Groups_ID]
	)  ON [PRIMARY] 
GO

 CREATE  CLUSTERED  INDEX [IX_AS_Markup_Categories_Groups_GroupsID] ON [dbo].[AS_Markup_Categories_Groups]([Lup_AP_Markup_Groups_ID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AS_Markup_Categories_Groups] ADD 
	CONSTRAINT [PK_AS_Markup_Categories_Groups] PRIMARY KEY  NONCLUSTERED 
	(
		[AS_Markup_Categories_Groups_ID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_AS_Markup_Categories_Groups_CategoriesID] ON [dbo].[AS_Markup_Categories_Groups]([Lup_AC_Markup_Categories_ID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Lup_AC_Markup_Categories] ADD 
	CONSTRAINT [PK_Lup_AS_Markup_Categories] PRIMARY KEY  NONCLUSTERED 
	(
		[Lup_AC_Markup_Categories_ID]
	)  ON [PRIMARY] 
GO

setuser
GO

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

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

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AS_Markup_Categories_Groups].[Markup_Percentage]'
GO

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

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

setuser
GO

setuser
GO

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

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

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

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

setuser
GO

setuser
GO

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

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

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

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

setuser
GO

ALTER TABLE [dbo].[AS_Markup_Categories_Groups] ADD 
	CONSTRAINT [FK_AS_Markup_Categories_Groups_Lup_AC_Markup_Categories] FOREIGN KEY 
	(
		[Lup_AC_Markup_Categories_ID]
	) REFERENCES [dbo].[Lup_AC_Markup_Categories] (
		[Lup_AC_Markup_Categories_ID]
	)
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


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


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

CREATE TABLE [dbo].[AB_Enterprise_Trial_Balance] (
	[AB_Enterprise_Trial_Balance_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Account_Number] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Category] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Total] [money] NULL 
) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [IX_AB_Enterprise_Trial_Balance] ON [dbo].[AB_Enterprise_Trial_Balance]([Account_Number]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AB_Enterprise_Trial_Balance] ADD 
	CONSTRAINT [PK_AB_Enterprise_Trial_Balance] PRIMARY KEY  NONCLUSTERED 
	(
		[AB_Enterprise_Trial_Balance_ID]
	)  ON [PRIMARY] 
GO


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


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

CREATE TABLE [dbo].[AB_Enterprise_Income_Statement] (
	[AB_Enterprise_Income_Statement_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Sys_AB_I1_ID] [int] NULL ,
	[Sys_AB_I2_ID] [int] NULL ,
	[Sys_AB_I3_ID] [int] NULL ,
	[Sys_AB_I4_ID] [int] NULL ,
	[Account_Number] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Category] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Level1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Level2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Level3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Level4] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Account] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Total] [money] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AB_Enterprise_Income_Statement] WITH NOCHECK ADD 
	CONSTRAINT [PK_AB_Enterprise_Income_Statement] PRIMARY KEY  CLUSTERED 
	(
		[AB_Enterprise_Income_Statement_ID]
	)  ON [PRIMARY] 
GO


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


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

CREATE TABLE [dbo].[AB_Enterprise_Balance_Sheet] (
	[AB_Enterprise_Balance_Sheet_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Sys_AB_B1_ID] [int] NULL ,
	[Sys_AB_B2_ID] [int] NULL ,
	[Sys_AB_B3_ID] [int] NULL ,
	[Account_Number] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Category] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Level1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Level2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Level3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Account] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Total] [money] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AB_Enterprise_Balance_Sheet] WITH NOCHECK ADD 
	CONSTRAINT [PK_AB_Enterprise_Balance_Sheet] PRIMARY KEY  CLUSTERED 
	(
		[AB_Enterprise_Balance_Sheet_ID]
	)  ON [PRIMARY] 
GO

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


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

CREATE TABLE [dbo].[Lup_AB_Account_Groups] (
	[Lup_AB_Account_Groups_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS 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

ALTER TABLE [dbo].[Lup_AB_Account_Groups] WITH NOCHECK ADD 
	CONSTRAINT [PK_Lup_GL_Account_Groups] PRIMARY KEY  CLUSTERED 
	(
		[Lup_AB_Account_Groups_ID]
	)  ON [PRIMARY] 
GO

setuser
GO

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

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

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

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

setuser
GO


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

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

CREATE TABLE [dbo].[Lup_AB_Account_Divisions] (
	[Lup_AB_Account_Divisions_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS 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

ALTER TABLE [dbo].[Lup_AB_Account_Divisions] WITH NOCHECK ADD 
	CONSTRAINT [PK_Lup_GL_Account_Divisions] PRIMARY KEY  CLUSTERED 
	(
		[Lup_AB_Account_Divisions_ID]
	)  ON [PRIMARY] 
GO

setuser
GO

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

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

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

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

setuser
GO



-----------------------------------------------------------------
-- REMOVE SINGLE QUOTE from GL Account Descriptions
-----------------------------------------------------------------

UPDATE AB_Accounts
SET Description = REPLACE(Description, '''', '')

UPDATE Lup_AP_Product_Types
SET Description = REPLACE(Description, '''', '')

-----------------------------------------------------------------
-- INSERT Lup_AB_Account_Groups from Lup_AP_Product_Types
-----------------------------------------------------------------

INSERT Lup_AB_Account_Groups
    (
        Description
    )
SELECT Description = REPLACE(Description, '''', '')
FROM Lup_AP_Product_Types

-----------------------------------------------------------------
-- INSERT INCOME and EXPENSE GROUPS into from Sys_AB_I4
-----------------------------------------------------------------

INSERT Lup_AB_Account_Groups
    (
        Description
    )
SELECT Description = LEFT(REPLACE(Description, '''', ''), 50)
FROM Sys_AB_I4
WHERE Sys_AB_I4_ID > 20
ORDER BY Sys_AB_I4_ID

-----------------------------------------------------------------
-- INSERT Lup_AB_Account_Divisions
-----------------------------------------------------------------

INSERT Lup_AB_Account_Divisions
    (
        Description
    )
VALUES
    (
        '<None>'
    )


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


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


IF NOT EXISTS
    (
        SELECT Description
        FROM Lup_AC_Markup_Categories
        WHERE Description = '<None>'
    )
    BEGIN
        INSERT Lup_AC_Markup_Categories
            (
                Description
            )
        VALUES
            (
                '<None>'
            )
    END

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

IF NOT EXISTS
    (
        SELECT Description
        FROM Lup_AP_Markup_Groups
        WHERE Description = '<None>'
    )
    BEGIN
        INSERT Lup_AP_Markup_Groups
            (
                Description
            )
        VALUES
            (
                '<None>'
            )
    END


----------------------------------------------------
--> New Indexes
----------------------------------------------------

-- Created for fn_AP_SUM_Product_Job_Reserved
CREATE INDEX [IX_AS_ActiveJobSalesOrders] ON [dbo].[AS_Transactions]
    ( [Sys_AW_Transaction_Types_ID], [Slu_AW_Active_ID] )
GO


-- Created for fn_AO_Get_Qty_On_Order
CREATE INDEX [IX_AO_Open_Orders] ON [dbo].[AO_Orders]
    ( [Date_Ordered], [Complete] )
GO


----------------------------------------------------
--> Clean up Customer CELL No
----------------------------------------------------

UPDATE AC_Customers
SET Cell_No = REPLACE(Cell_No, ' ', '')
GO

UPDATE AC_Customers
SET Cell_No = REPLACE(Cell_No, '-', '')
GO

UPDATE AC_Customers
SET Cell_No = REPLACE(Cell_No, '(', '')
GO

UPDATE AC_Customers
SET Cell_No = REPLACE(Cell_No, ')', '')
GO


----------------------------------------------------
--> Clear REDUNDANT Reserved fields in AP_Products
----------------------------------------------------

UPDATE AP_Products
SET Sales_Order_Reserved = 0,
    Quote_Reserved = 0,
    Job_Reserved = 0   
GO

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

--> ENABLE editing Sys_AB_Account_Categories
UPDATE Sys_AW_Lookup_Editor
SET Slu_AW_Active_ID = 1
WHERE TableName = 'Sys_AB_Account_Categories'
GO

--> ENABLE editing Lup_AC_Customer_Types
UPDATE Sys_AW_Lookup_Editor
SET Slu_AW_Active_ID = 1
WHERE TableName = 'Lup_AC_Customer_Types'
GO



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