------------------------------------------------------------------------------
-- ADVANTAGE 2004.3.8
-- DB OBJECT CHANGES SINCE ADVANTAGE 2004.3.7
-- 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='AO_Suppliers' AND
              C.Name='Lup_AO_Supplier_Types_ID'
    ) 
    BEGIN
        UPDATE AO_Suppliers
        SET Lup_AO_Supplier_Types_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='AO_Suppliers' AND
              C.Name='Lup_AO_Supplier_Groups_ID'
    ) 
    BEGIN
        UPDATE AO_Suppliers
        SET Lup_AO_Supplier_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='AW_Users' AND
              C.Name='IsProcurementSupervisor'
    ) 
    BEGIN
        UPDATE AW_Users
        SET IsProcurementSupervisor = 0
    END



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

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

CREATE TABLE [dbo].[Lup_AO_Supplier_Groups] (
	[Lup_AO_Supplier_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] NULL ,
	[Touched] [timestamp] NULL ,
	[Slu_AW_Active_ID] [tinyint] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Lup_AO_Supplier_Groups] WITH NOCHECK ADD 
	CONSTRAINT [PK_Lup_AO_Supplier_Groups] PRIMARY KEY  CLUSTERED 
	(
		[Lup_AO_Supplier_Groups_ID]
	)  ON [PRIMARY] 
GO

setuser
GO

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

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

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

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

setuser
GO

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

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

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

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

CREATE TABLE [dbo].[Lup_AO_Supplier_Types] (
	[Lup_AO_Supplier_Types_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] NULL ,
	[Touched] [timestamp] NULL ,
	[Slu_AW_Active_ID] [tinyint] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Lup_AO_Supplier_Types] WITH NOCHECK ADD 
	CONSTRAINT [PK_Lup_AO_Supplier_Types] PRIMARY KEY  CLUSTERED 
	(
		[Lup_AO_Supplier_Types_ID]
	)  ON [PRIMARY] 
GO

setuser
GO

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

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

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

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

setuser
GO

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

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

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

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

CREATE TABLE [dbo].[AO_Transactions_Log] (
	[AO_Transactions_Log_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[AO_Suppliers_ID] [int] NULL ,
	[AO_Transactions_ID] [int] NULL ,
	[AO_GRV_ID] [int] NULL ,
	[Posted_Month_ID] [tinyint] NULL ,
	[Posted_Year_ID] [int] NULL ,
	[Current_Month_ID] [tinyint] NULL ,
	[Current_Year_ID] [int] NULL ,
	[LastME_Month_ID] [tinyint] NULL ,
	[LastME_Year_ID] [int] NULL ,
	[Date_Captured] [datetime] NULL ,
	[User_ID] [int] NULL ,
	[AW_User_Companies_ID] [int] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AO_Transactions_Log] WITH NOCHECK ADD 
	CONSTRAINT [PK_Purchase_Transactions_Log] PRIMARY KEY  CLUSTERED 
	(
		[AO_Transactions_Log_ID]
	)  ON [PRIMARY] 
GO

setuser
GO

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

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

setuser
GO

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

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

CREATE TABLE [dbo].[AW_Period_Procedures_Deleted] (
	[AW_Period_Procedures_Deleted_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[AW_Period_Procedures_ID] [int] NULL ,
	[Sys_AW_Period_Procedure_Types_ID] [int] NULL ,
	[Description] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Month_ID] [tinyint] NULL ,
	[Year_ID] [smallint] NULL ,
	[Month_End_Date] [datetime] NULL ,
	[Year_End_Date] [datetime] NULL ,
	[Stock_Valuation] [money] NULL ,
	[Date_Captured] [datetime] NULL ,
	[User_ID] [int] NULL ,
	[AW_User_Companies_ID] [int] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AW_Period_Procedures_Deleted] WITH NOCHECK ADD 
	CONSTRAINT [PK_AW_Period_Procedures_Deleted] PRIMARY KEY  CLUSTERED 
	(
		[AW_Period_Procedures_Deleted_ID]
	)  ON [PRIMARY] 
GO

setuser
GO

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

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

setuser
GO



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

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

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

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AB_Enterprise_Management_Accounts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[AB_Enterprise_Management_Accounts]
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_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] (100) 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

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] (100) 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 ,
	[TCRUST] [money] NULL ,
	[TCSTEEL] [money] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AB_Enterprise_Management_Accounts] (
	[AB_Enterprise_Management_Accounts_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 ,
	[Sys_AB_I5_ID] [int] NULL ,
	[Account_Number] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Category] [varchar] (100) 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 ,
	[Level5] [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

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] (100) 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

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

ALTER TABLE [dbo].[AB_Enterprise_Management_Accounts] WITH NOCHECK ADD 
	CONSTRAINT [PK_AB_Enterprise_Management_Accounts] PRIMARY KEY  CLUSTERED 
	(
		[AB_Enterprise_Management_Accounts_ID]
	)  ON [PRIMARY] 
GO

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



----------------------------------------------------
--> NEW INDEXES - for DELETE INACTIVE Customers
----------------------------------------------------

CREATE INDEX [IX_Contract_Prices_Customers_ID] ON [dbo].[AC_Contract_Prices] ([AC_Customers_ID])
GO

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

CREATE CLUSTERED INDEX [PK_AC_Statements_ID] ON [dbo].[AC_Statements] ([AC_Statements_ID])
--WITH DROP_EXISTING
GO

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

CREATE INDEX [IX_AC_Statements_Customers_ID] ON [dbo].[AC_Statements] ([AC_Customers_ID])
GO

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

CREATE CLUSTERED INDEX [PK_AC_Statement_Details] ON [dbo].[AC_Statement_Details] ([AC_Statements_ID])
GO

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

CREATE INDEX [IX_Xref_Tran_ID] ON [dbo].[AS_Transactions_Xref] ([AS_Transactions_ID])
GO

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

CREATE INDEX [IX_Xref_Source_Tran_ID] ON [dbo].[AS_Transactions_Xref_Sources] ([AS_Transactions_ID])
GO

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

CREATE INDEX [IX_CBilling_Tran_ID] ON [dbo].[AS_Transactions_CBilling] ([AS_Transactions_ID])
GO

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