------------------------------------------------------------------------------
-- ADVANTAGE 2004.4.6
-- DB OBJECT CHANGES
-- 16/08/2009
------------------------------------------------------------------------------



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

-- --1. Modify AW_User_Companies table as follows
--     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_Hide_Remote'
--         ) 
--         BEGIN
--             UPDATE AP_Products
--             SET Slu_AW_YesNo_Hide_Remote = 0
--         END


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

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

CREATE TABLE [dbo].[AS_Till_Payouts] (
	[AS_Till_Payouts_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Payout_Type_ID] [tinyint] NULL ,
	[AB_Account_DR_ID] [int] NULL ,
	[AB_Account_CR_ID] [int] NULL ,
	[AO_Suppliers_ID] [int] NULL ,
	[Sys_AB_Transaction_Types_ID] [tinyint] NULL ,
	[Description] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Narration] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Payout_Number] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Payout_Date] [datetime] NULL ,
	[Slu_AW_Months_ID] [tinyint] NULL ,
	[Slu_AW_Years_ID] [int] NULL ,
	[Amount] [money] NULL ,
	[Day_End] [smalldatetime] 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].[AS_Till_Payouts] WITH NOCHECK ADD 
	CONSTRAINT [PK_AS_Till_Payouts] PRIMARY KEY  CLUSTERED 
	(
		[AS_Till_Payouts_ID]
	)  ON [PRIMARY] 
GO

setuser
GO

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

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

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

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

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

setuser
GO

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


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

UPDATE AP_Products
SET Average_Cost = Last_Cost
WHERE Average_Cost IS NULL

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


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



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