------------------------------------------------------------------------------
-- ADVANTAGE 2004.3.4
-- DB OBJECT CHANGES SINCE ADVANTAGE 2004.3.3
-- 09 APR 2007
------------------------------------------------------------------------------


-- USE Advantage
-- GO



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

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Lup_AP_Purchase_Categories]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[Lup_AP_Purchase_Categories]
    GO
    
    CREATE TABLE [dbo].[Lup_AP_Purchase_Categories] (
    	[Lup_AP_Purchase_Categories_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_AP_Purchase_Categories] WITH NOCHECK ADD 
    	CONSTRAINT [PK_Lup_Purchase_Categories] PRIMARY KEY  CLUSTERED 
    	(
    		[Lup_AP_Purchase_Categories_ID]
    	)  ON [PRIMARY] 
    GO
    
    setuser
    GO
    
    EXEC sp_bindefault N'[dbo].[df_User_Companies]', N'[Lup_AP_Purchase_Categories].[AW_User_Companies_ID]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Date_Captured]', N'[Lup_AP_Purchase_Categories].[Date_Captured]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Active]', N'[Lup_AP_Purchase_Categories].[Slu_AW_Active_ID]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_User_ID]', N'[Lup_AP_Purchase_Categories].[User_ID]'
    GO
    
    setuser
    GO
    
-----------------------------------------
    
    INSERT Lup_AP_Purchase_Categories ( Description ) VALUES  ( '<None>' )

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

    --NEW Suppliers Month End Period Procedure ID = 6
    DECLARE @ProcedureDescription varchar(50)

    SET @ProcedureDescription = ''

    SELECT @ProcedureDescription = Description
    FROM Sys_AW_Period_Procedure_Types
    WHERE Sys_AW_Period_Procedure_Types_ID = 6


    IF @ProcedureDescription = '' OR @ProcedureDescription IS NULL
        BEGIN
            INSERT Sys_AW_Period_Procedure_Types ( Description ) VALUES  ( 'Suppliers Month End' )
        END
    ELSE        --@ProcedureDescription <> ''
        BEGIN
            UPDATE Sys_AW_Period_Procedure_Types
            SET Description = 'Suppliers Month End'
            WHERE Sys_AW_Period_Procedure_Types_ID = 6
        END

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


    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AO_Statements]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[AO_Statements]
    GO
    
    CREATE TABLE [dbo].[AO_Statements] (
    	[AO_Statements_ID] [int] IDENTITY (1, 1) NOT NULL ,
    	[AW_Period_Procedures_ID] [int] NULL ,
    	[AO_Suppliers_ID] [int] NULL ,
    	[FullName] [varchar] (92) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Supplier_Number] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Post_Address] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Post_Office] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Post_Code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Email_Address] [varchar] (40) 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 ,
    	[Touched] [timestamp] NULL 
    ) ON [PRIMARY]
    GO
    
    setuser
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Date_Captured]', N'[AO_Statements].[Date_Captured]'
    GO
    
    setuser
    GO

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

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AO_Statement_Details]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[AO_Statement_Details]
    GO
    
    CREATE TABLE [dbo].[AO_Statement_Details] (
    	[AO_Statement_Details_ID] [int] IDENTITY (1, 1) NOT NULL ,
    	[AO_Statements_ID] [int] NULL ,
    	[AO_Transactions_ID] [int] NULL ,
    	[AO_Suppliers_ID] [int] NULL ,
    	[AB_Accounts_ID] [int] NULL ,
    	[Supplier_Reference] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Sys_AW_Transaction_Types_ID] [tinyint] NULL ,
    	[Document_Number] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Document_Date] [datetime] NULL ,
    	[Slu_AW_Months_ID] [tinyint] NULL ,
    	[Slu_AW_Years_ID] [int] NULL ,
    	[AO_GRV_ID] [int] NULL ,
    	[Comment] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[AW_Comments_ID] [int] NULL ,
    	[Trade_Discount_Total] [money] NULL ,
    	[Rebate_Discount_Total] [money] NULL ,
    	[Other_Discount_Total] [money] NULL ,
    	[Net_Amount] [money] NULL ,
    	[Total_Tax_Amount] [money] NULL ,
    	[Total_Amount] [money] NULL ,
    	[Payment_Amount] [money] NULL ,
    	[Settlement_Discount] [money] NULL ,
    	[To_Be_Paid] [bit] NULL ,
    	[To_Be_Paid_Amount] [money] NULL ,
    	[Remittance_Balance] [money] NULL ,
    	[Date_Captured] [datetime] NULL ,
    	[User_ID] [int] NULL ,
    	[AW_User_Companies_ID] [int] NOT NULL
    ) ON [PRIMARY]
    GO

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

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AS_Enterprise_Sales]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[AS_Enterprise_Sales]
    GO
    
    CREATE TABLE [dbo].[AS_Enterprise_Sales] (
    	[AS_Enterprise_Sales_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 ,
    	[Quantity] [decimal](12, 3) NULL ,
    	[Total] [money] NULL ,
    	[Filter] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
    ) ON [PRIMARY]
    GO
    
     CREATE  CLUSTERED  INDEX [IX_AS_Enterprise_Sales_Search] ON [dbo].[AS_Enterprise_Sales]([Filter], [Product_Code], [Pack_Size]) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[AS_Enterprise_Sales] ADD 
    	CONSTRAINT [PK_AS_Enterprise_Sales] PRIMARY KEY  NONCLUSTERED 
    	(
    		[AS_Enterprise_Sales_ID]
    	)  ON [PRIMARY] 
    GO
    
    setuser
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Numeric_One]', N'[AS_Enterprise_Sales].[Pack_Size]'
    GO
    
    setuser
    GO



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

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AS_Enterprise_Customer_Sales]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[AS_Enterprise_Customer_Sales]
    GO
    
    CREATE TABLE [dbo].[AS_Enterprise_Customer_Sales] (
    	[AS_Enterprise_Customer_Sales_ID] [int] IDENTITY (1, 1) NOT NULL ,
    	[Customer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[CustNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Total] [money] NULL ,
    	[Filter] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
    ) ON [PRIMARY]
    GO
    
     CREATE  CLUSTERED  INDEX [IX_AS_Enterprise_Customer_Sales_Search] ON [dbo].[AS_Enterprise_Customer_Sales]([Filter], [Customer], [CustNo]) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[AS_Enterprise_Customer_Sales] ADD 
    	CONSTRAINT [PK_AS_Enterprise_Customer_Sales] PRIMARY KEY  NONCLUSTERED 
    	(
    		[AS_Enterprise_Customer_Sales_ID]
    	)  ON [PRIMARY] 
    GO



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

--     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
--     
--     CREATE TABLE [dbo].[AP_Stock_Transfers] (
--     	[AP_Stock_Transfers_ID] [int] IDENTITY (1, 1) NOT NULL ,
--     	[AP_Products_ID] [int] NULL ,
--     	[AB_Accounts_ID] [int] NULL ,
--     	[Last_Cost] [money] NULL ,
--     	[Average_Cost] [money] NULL ,
--     	[Standard_Cost] [money] NULL ,
--     	[Quantity] [decimal](9, 3) NULL ,
--     	[Unit_Price] [money] NULL ,
--     	[Transfer_Date] [datetime] NULL ,
--     	[Note] [varchar] (100) 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].[AP_Stock_Transfers] WITH NOCHECK ADD 
--     	CONSTRAINT [PK_AP_Stock_Transfers] PRIMARY KEY  CLUSTERED 
--     	(
--     		[AP_Stock_Transfers_ID]
--     	)  ON [PRIMARY] 
--     GO
--     
--     setuser
--     GO
--     
--     EXEC sp_bindefault N'[dbo].[df_Numeric_One]', N'[AP_Stock_Transfers].[AB_Accounts_ID]'
--     GO
--     
--     EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AP_Stock_Transfers].[Average_Cost]'
--     GO
--     
--     EXEC sp_bindefault N'[dbo].[df_User_Companies]', N'[AP_Stock_Transfers].[AW_User_Companies_ID]'
--     GO
--     
--     EXEC sp_bindefault N'[dbo].[df_Date_Captured]', N'[AP_Stock_Transfers].[Date_Captured]'
--     GO
--     
--     EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AP_Stock_Transfers].[Last_Cost]'
--     GO
--     
--     EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AP_Stock_Transfers].[Quantity]'
--     GO
--     
--     EXEC sp_bindefault N'[dbo].[df_Active]', N'[AP_Stock_Transfers].[Slu_AW_Active_ID]'
--     GO
--     
--     EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AP_Stock_Transfers].[Standard_Cost]'
--     GO
--     
--     EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AP_Stock_Transfers].[Unit_Price]'
--     GO
--     
--     EXEC sp_bindefault N'[dbo].[df_User_ID]', N'[AP_Stock_Transfers].[User_ID]'
--     GO
--     
--     setuser
--     GO



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

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AC_Enterprise_Customer_Ageing]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[AC_Enterprise_Customer_Ageing]
    GO
    
    CREATE TABLE [dbo].[AC_Enterprise_Customer_Ageing] (
    	[AC_Enterprise_Customer_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 ,
    	[Customer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[CustNo] [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_AC_Enterprise_Customer_Ageing] ON [dbo].[AC_Enterprise_Customer_Ageing]([Filter], [Customer], [CustNo]) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[AC_Enterprise_Customer_Ageing] ADD 
    	CONSTRAINT [PK_AC_Enterprise_Customer_Ageing] PRIMARY KEY  NONCLUSTERED 
    	(
    		[AC_Enterprise_Customer_Ageing_ID]
    	)  ON [PRIMARY] 
    GO
    
    setuser
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Numeric_One]', N'[AC_Enterprise_Customer_Ageing].[CompanyID]'
    GO
    
    setuser
    GO



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

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AC_Enterprise_Ageing_Temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[AC_Enterprise_Ageing_Temp]
    GO
    
    CREATE TABLE [dbo].[AC_Enterprise_Ageing_Temp] (
    	[AC_Enterprise_Ageing_Temp_ID] [int] IDENTITY (1, 1) NOT NULL ,
    	[Filter] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[AC_Customers_ID] [int] NULL ,
    	[FullName] [varchar] (92) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    	[Customer_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_AC_Enterprise_Ageing_Temp] ON [dbo].[AC_Enterprise_Ageing_Temp]([Filter], [FullName], [Customer_Number]) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[AC_Enterprise_Ageing_Temp] ADD 
    	CONSTRAINT [PK_AC_Enterprise_Ageing_Temp] PRIMARY KEY  NONCLUSTERED 
    	(
    		[AC_Enterprise_Ageing_Temp_ID]
    	)  ON [PRIMARY] 
    GO
    
    setuser
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AC_Enterprise_Ageing_Temp].[120Days]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AC_Enterprise_Ageing_Temp].[150Days]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AC_Enterprise_Ageing_Temp].[180+Days]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AC_Enterprise_Ageing_Temp].[30Days]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AC_Enterprise_Ageing_Temp].[60Days]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AC_Enterprise_Ageing_Temp].[90Days]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AC_Enterprise_Ageing_Temp].[Account_Balance]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AC_Enterprise_Ageing_Temp].[Amount]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_User_Companies]', N'[AC_Enterprise_Ageing_Temp].[AW_User_Companies_ID]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AC_Enterprise_Ageing_Temp].[Current]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Date_Captured]', N'[AC_Enterprise_Ageing_Temp].[Date_Captured]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AC_Enterprise_Ageing_Temp].[Payment]'
    GO
    
    EXEC sp_bindefault N'[dbo].[df_User_ID]', N'[AC_Enterprise_Ageing_Temp].[User_ID]'
    GO
    
    setuser
    GO


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



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