------------------------------------------------------------------------------
-- ADVANTAGE 2004.4.2
-- DB OBJECT CHANGES SINCE ADVANTAGE 2004.4.1
-- 24/01/2009
------------------------------------------------------------------------------



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

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AO_Enterprise_Supplier_Ageing]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[AO_Enterprise_Supplier_Ageing]
    GO
    
    CREATE TABLE [dbo].[AO_Enterprise_Supplier_Ageing] (
    	[AO_Enterprise_Supplier_Ageing_ID] [int] IDENTITY (1, 1) NOT NULL ,
    	[CompanyID] [int] NULL ,
    	[SourceDB] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Filter] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Supplier] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[SuppNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Total] [money] NULL ,
    	[Balance] [money] NULL ,
    	[Days0] [money] NULL ,
    	[Days30] [money] NULL ,
    	[Days60] [money] NULL ,
    	[Days90] [money] NULL ,
    	[Days120] [money] NULL ,
    	[Days150] [money] NULL ,
    	[Days180] [money] NULL 
    ) ON [PRIMARY]
    GO
    
     CREATE  CLUSTERED  INDEX [IX_AO_Enterprise_Supplier_Ageing] ON [dbo].[AO_Enterprise_Supplier_Ageing]([Filter], [Supplier], [SuppNo]) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[AO_Enterprise_Supplier_Ageing] ADD 
    	CONSTRAINT [PK_AO_Enterprise_Supplier_Ageing] PRIMARY KEY  NONCLUSTERED 
    	(
    		[AO_Enterprise_Supplier_Ageing_ID]
    	)  ON [PRIMARY] 
    GO
    
    setuser
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Numeric_One]', N'[AO_Enterprise_Supplier_Ageing].[CompanyID]'
    GO
    
    setuser
    GO

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

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AO_Enterprise_Ageing_Temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[AO_Enterprise_Ageing_Temp]
    GO
    
    CREATE TABLE [dbo].[AO_Enterprise_Ageing_Temp] (
    	[AO_Enterprise_Ageing_Temp_ID] [int] IDENTITY (1, 1) NOT NULL ,
    	[Filter] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[AO_Suppliers_ID] [int] NULL ,
    	[Supplier_Name] [varchar] (92) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Supplier_Number] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Amount] [money] NULL ,
    	[Payment] [money] NULL ,
    	[Account_Balance] [money] NULL ,
    	[Current] [money] NULL ,
    	[30Days] [money] NULL ,
    	[60Days] [money] NULL ,
    	[90Days] [money] NULL ,
    	[120Days] [money] NULL ,
    	[150Days] [money] NULL ,
    	[180+Days] [money] NULL ,
    	[Date_Captured] [datetime] NULL ,
    	[User_ID] [int] NULL ,
    	[AW_User_Companies_ID] [int] NOT NULL 
    ) ON [PRIMARY]
    GO
    
     CREATE  CLUSTERED  INDEX [IX_AO_Enterprise_Ageing_Temp] ON [dbo].[AO_Enterprise_Ageing_Temp]([Filter], [Supplier_Name], [Supplier_Number]) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[AO_Enterprise_Ageing_Temp] ADD 
    	CONSTRAINT [PK_AO_Enterprise_Ageing_Temp] PRIMARY KEY  NONCLUSTERED 
    	(
    		[AO_Enterprise_Ageing_Temp_ID]
    	)  ON [PRIMARY] 
    GO
    
    setuser
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AO_Enterprise_Ageing_Temp].[120Days]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AO_Enterprise_Ageing_Temp].[150Days]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AO_Enterprise_Ageing_Temp].[180+Days]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AO_Enterprise_Ageing_Temp].[30Days]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AO_Enterprise_Ageing_Temp].[60Days]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AO_Enterprise_Ageing_Temp].[90Days]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AO_Enterprise_Ageing_Temp].[Account_Balance]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AO_Enterprise_Ageing_Temp].[Amount]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_User_Companies]', N'[AO_Enterprise_Ageing_Temp].[AW_User_Companies_ID]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AO_Enterprise_Ageing_Temp].[Current]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Date_Captured]', N'[AO_Enterprise_Ageing_Temp].[Date_Captured]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AO_Enterprise_Ageing_Temp].[Payment]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_User_ID]', N'[AO_Enterprise_Ageing_Temp].[User_ID]'
    GO
    
    setuser
    GO

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




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

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

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


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




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