------------------------------------------------------------------------------
-- ADVANTAGE 2004.3.9
-- DB OBJECT CHANGES SINCE ADVANTAGE 2004.3.8
-- 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='AS_Transaction_Details' AND
              C.Name='Back_Order'
    ) 
    BEGIN
        UPDATE AS_Transaction_Details
        SET Back_Order = 0
    END


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

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

CREATE TABLE [dbo].[AS_Sales_Order_Details] (
	[AS_Sales_Order_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 ,
	[Shipping_Cost] [money] NULL ,
	[Tax_Rate] [decimal](9, 3) NULL ,
	[Tax_Amount] [money] NULL ,
	[Extended_Amount] [money] NULL ,
	[Unit_Cost] [money] NULL ,
	[AP_Promotion_Products_ID] [int] NULL ,
	[Promo_Price] [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_AS_Sales_Order_Details] ON [dbo].[AS_Sales_Order_Details]([AC_Customers_ID]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AS_Sales_Order_Details] ADD 
	CONSTRAINT [PK_AS_Sales_Order_Details] PRIMARY KEY  NONCLUSTERED 
	(
		[AS_Sales_Order_Details_ID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_AS_Sales_Order_Details_AP_Products_ID] ON [dbo].[AS_Sales_Order_Details]([AP_Products_ID]) ON [PRIMARY]
GO

setuser
GO

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

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

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

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

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

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

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AS_Sales_Order_Details].[Shipping_Cost]'
GO

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

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

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

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

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

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

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

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

setuser
GO


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


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

CREATE TABLE [dbo].[Slu_AW_Shipping_Rates] (
	[Slu_AW_Shipping_Rates_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Post_Code] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Location] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Rate_Per_Kg] [money] NULL ,
	[Date_Captured] [datetime] NULL ,
	[User_ID] [int] NULL ,
	[Touched] [timestamp] NULL ,
	[Slu_AW_Active_ID] [tinyint] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Slu_AW_Shipping_Rates] WITH NOCHECK ADD 
	CONSTRAINT [PK_Slu_AW_Shipping_Rates] PRIMARY KEY  CLUSTERED 
	(
		[Slu_AW_Shipping_Rates_ID]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_AW_Shipping_Rates_Post_Code] ON [dbo].[Slu_AW_Shipping_Rates]([Post_Code]) ON [PRIMARY]
GO

setuser
GO

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

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[Slu_AW_Shipping_Rates].[Rate_Per_Kg]'
GO

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

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

setuser
GO





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





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


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