-----------------------------------------------------------------------------
-- ADVANTAGE 2004.4.8.2
-- DB OBJECT CHANGES
-- 08/08/2010
------------------------------------------------------------------------------



-------------------------------------------------------------------------------
-- 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='AW_User_Companies' AND
                  C.Name='Sp_Web_Password'
        ) 
        BEGIN
            UPDATE AW_User_Companies
            SET Sp_Web_Password = '3sigma22'
        END

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



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

--Added NEW GL Division and Group as PRIMARY Group By Sort Order
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_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 ,
	[Divisions] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Groups] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Total] [money] NULL 
) 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

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

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

CREATE TABLE [dbo].[AS_Enterprise_Sales_All] (
	[AS_Enterprise_Sales_All_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 ,
	[Total] [money] NULL ,
	[Filter] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

 CREATE  CLUSTERED  INDEX [IX_AS_Enterprise_Sales_All_Search] ON [dbo].[AS_Enterprise_Sales_All]([Filter], [Product_Code], [Pack_Size]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AS_Enterprise_Sales_All] ADD 
	CONSTRAINT [PK_AS_Enterprise_Sales_All] PRIMARY KEY  NONCLUSTERED 
	(
		[AS_Enterprise_Sales_All_ID]
	)  ON [PRIMARY] 
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_One]', N'[AS_Enterprise_Sales_All].[Pack_Size]'
GO

setuser
GO



-------------------------------------------------------
-- NEW Internet Cash Book tables
-------------------------------------------------------


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

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

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

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

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

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

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

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

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

CREATE TABLE [dbo].[AB_Cash_Book_IBS_Detail] (
	[AB_Cash_Book_IBS_Detail_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[AB_Cash_Book_IBS_Header_ID] [int] NOT NULL ,
	[Post] [bit] NOT NULL ,
	[Sys_AB_Transaction_Types_ID] [tinyint] NOT NULL ,
	[Transaction_Date] [datetime] NOT NULL ,
	[Ref_No] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[AB_Accounts_ID] [int] NOT NULL ,
	[Sys_AW_Tax_Methods_ID] [tinyint] NOT NULL ,
	[Post_Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Amount] [money] NOT NULL ,
	[Tax_Amount] [money] NOT NULL ,
	[Net_Amount] [money] NOT NULL ,
	[Comments] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Bank_Description] [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

CREATE TABLE [dbo].[AB_Cash_Book_IBS_Detail_Splits] (
	[AB_Cash_Book_IBS_Detail_Splits_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[AB_Cash_Book_IBS_Header_ID] [int] NOT NULL ,
	[AB_Cash_Book_IBS_Detail_ID] [int] NOT NULL ,
	[AB_Accounts_ID] [int] NOT NULL ,
	[Sys_AW_Tax_Methods_ID] [tinyint] NOT NULL ,
	[Amount] [money] NOT NULL ,
	[Tax_Amount] [money] NOT NULL ,
	[Net_Amount] [money] NOT 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

CREATE TABLE [dbo].[AB_Cash_Book_IBS_Header] (
	[AB_Cash_Book_IBS_Header_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[AW_Company_Banks_ID] [int] NOT NULL ,
	[Posting_Type] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Slu_AW_Banks_ID] [smallint] NOT NULL ,
	[Date_Posted] [datetime] NOT NULL ,
	[Start_Date] [datetime] NOT NULL ,
	[End_Date] [datetime] NOT NULL ,
	[Slu_AW_Months_ID] [tinyint] NOT NULL ,
	[Slu_AW_Years_ID] [smallint] NOT NULL ,
	[Import_Filename] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Auto_Recon] [tinyint] 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

CREATE TABLE [dbo].[AB_Cash_Book_IBS_Map] (
	[AB_Cash_Book_IBS_Map_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[AW_Company_Banks_ID] [int] NULL ,
	[Post] [bit] NULL ,
	[Sys_AB_Transaction_Types_ID] [tinyint] NULL ,
	[Map_1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Map_2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Map_3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[AB_Accounts_ID] [int] NULL ,
	[Sys_AW_Tax_Methods_ID] [tinyint] NULL ,
	[Bank_Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Comments] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Date_Captured] [datetime] NULL ,
	[User_ID] [int] NULL ,
	[AW_User_Companies_ID] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AB_Cash_Book_IBS_Map_Splits] (
	[AB_Cash_Book_IBS_Map_Splits_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[AW_Company_Banks_ID] [int] NULL ,
	[AB_Cash_Book_IBS_Map_ID] [int] NOT NULL ,
	[AB_Accounts_ID] [int] NULL ,
	[Sys_AW_Tax_Methods_ID] [tinyint] NULL ,
	[Amount] [money] NULL ,
	[Tax_Amount] [money] NULL ,
	[Net_Amount] [money] NULL ,
	[Date_Captured] [datetime] NULL ,
	[User_ID] [int] NULL ,
	[AW_User_Companies_ID] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AB_Cash_Book_IBS_Notes] (
	[AB_Cash_Book_IBS_Notes_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Note] [varchar] (500) 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 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Lup_AB_Transaction_Type_Maps] (
	[Lup_AB_Transaction_Type_Maps_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Slu_AW_Banks_ID] [smallint] NOT NULL ,
	[Sys_AB_Transaction_Types_ID] [tinyint] NOT NULL ,
	[Mapping_CSV] [varchar] (300) 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

CREATE TABLE [dbo].[Slu_AB_Bank_IBS_Rules] (
	[Slu_AB_Bank_IBS_Rules_ID] [smallint] IDENTITY (1, 1) NOT NULL ,
	[AW_Company_Banks_ID] [int] NOT NULL ,
	[Header_Rows] [tinyint] NULL ,
	[Footer_Rows] [tinyint] NULL ,
	[Delimiter] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Date_Field_No] [tinyint] NULL ,
	[Description_Field_No] [tinyint] NULL ,
	[Amount_Field_No] [tinyint] NULL ,
	[Slu_AW_Date_Format_ID] [tinyint] NULL ,
	[Combine_Field_Nos] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TranType_Field_No] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Cheque_Field_No] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Cheque_Identifier] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Cheque_No_From] [tinyint] NULL ,
	[Cheque_No_To] [tinyint] NULL ,
	[Date_Captured] [datetime] NULL ,
	[User_ID] [int] NULL ,
	[Touched] [timestamp] NULL ,
	[Slu_AW_Active_ID] [tinyint] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Sys_AW_Date_Formats] (
	[Sys_AW_Date_Formats_ID] [tinyint] IDENTITY (1, 1) NOT NULL ,
	[Description] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AB_Cash_Book_IBS_Detail] WITH NOCHECK ADD 
	CONSTRAINT [PK_AB_Cash_Book_IBS_Detail] PRIMARY KEY  CLUSTERED 
	(
		[AB_Cash_Book_IBS_Detail_ID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[AB_Cash_Book_IBS_Detail_Splits] WITH NOCHECK ADD 
	CONSTRAINT [PK_AB_Cash_Book_IBS_Detail_Splits] PRIMARY KEY  CLUSTERED 
	(
		[AB_Cash_Book_IBS_Detail_Splits_ID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[AB_Cash_Book_IBS_Header] WITH NOCHECK ADD 
	CONSTRAINT [PK_AB_Cash_Book_IBS_Header] PRIMARY KEY  CLUSTERED 
	(
		[AB_Cash_Book_IBS_Header_ID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[AB_Cash_Book_IBS_Map] WITH NOCHECK ADD 
	CONSTRAINT [PK_AB_Cash_Book_IBS_Map] PRIMARY KEY  CLUSTERED 
	(
		[AB_Cash_Book_IBS_Map_ID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[AB_Cash_Book_IBS_Map_Splits] WITH NOCHECK ADD 
	CONSTRAINT [PK_AB_Cash_Book_IBS_Map_Splits] PRIMARY KEY  CLUSTERED 
	(
		[AB_Cash_Book_IBS_Map_Splits_ID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[AB_Cash_Book_IBS_Notes] WITH NOCHECK ADD 
	CONSTRAINT [PK_AB_Cash_Book_IBS_Notes] PRIMARY KEY  CLUSTERED 
	(
		[AB_Cash_Book_IBS_Notes_ID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Lup_AB_Transaction_Type_Maps] WITH NOCHECK ADD 
	CONSTRAINT [PK_Lup_AB_Transaction_Type_Maps] PRIMARY KEY  CLUSTERED 
	(
		[Lup_AB_Transaction_Type_Maps_ID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Slu_AB_Bank_IBS_Rules] WITH NOCHECK ADD 
	CONSTRAINT [PK_Slu_AB_Bank_IBS_Rules] PRIMARY KEY  CLUSTERED 
	(
		[Slu_AB_Bank_IBS_Rules_ID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Sys_AW_Date_Formats] WITH NOCHECK ADD 
	CONSTRAINT [PK_Sys_AW_Date_Formats_ID] PRIMARY KEY  CLUSTERED 
	(
		[Sys_AW_Date_Formats_ID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_Cash_Book_IBS_Detail] ON [dbo].[AB_Cash_Book_IBS_Detail]([Transaction_Date], [Bank_Description], [Amount], [Slu_AW_Active_ID]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Cash_Book_IBS_Detail] ON [dbo].[AB_Cash_Book_IBS_Detail_Splits]([AB_Cash_Book_IBS_Header_ID], [AB_Cash_Book_IBS_Detail_ID]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_AB_Cash_Book_IBS_Map_Splits] ON [dbo].[AB_Cash_Book_IBS_Map_Splits]([AW_Company_Banks_ID], [AB_Cash_Book_IBS_Map_ID]) ON [PRIMARY]
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_One]', N'[AB_Cash_Book_IBS_Detail].[AB_Accounts_ID]'
GO

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

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

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[AB_Cash_Book_IBS_Detail].[Bank_Description]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[AB_Cash_Book_IBS_Detail].[Comments]'
GO

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

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AB_Cash_Book_IBS_Detail].[Net_Amount]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[AB_Cash_Book_IBS_Detail].[Post_Description]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[AB_Cash_Book_IBS_Detail].[Ref_No]'
GO

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

EXEC sp_bindefault N'[dbo].[df_Numeric_One]', N'[AB_Cash_Book_IBS_Detail].[Sys_AB_Transaction_Types_ID]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Three]', N'[AB_Cash_Book_IBS_Detail].[Sys_AW_Tax_Methods_ID]'
GO

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

EXEC sp_bindefault N'[dbo].[df_Date_Captured]', N'[AB_Cash_Book_IBS_Detail].[Transaction_Date]'
GO

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

setuser
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_One]', N'[AB_Cash_Book_IBS_Detail_Splits].[AB_Accounts_ID]'
GO

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

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

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

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[AB_Cash_Book_IBS_Detail_Splits].[Net_Amount]'
GO

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

EXEC sp_bindefault N'[dbo].[df_Numeric_Three]', N'[AB_Cash_Book_IBS_Detail_Splits].[Sys_AW_Tax_Methods_ID]'
GO

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

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

setuser
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_One]', N'[AB_Cash_Book_IBS_Header].[Auto_Recon]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_One]', N'[AB_Cash_Book_IBS_Header].[AW_Company_Banks_ID]'
GO

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

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

EXEC sp_bindefault N'[dbo].[df_Date_Captured]', N'[AB_Cash_Book_IBS_Header].[Date_Posted]'
GO

EXEC sp_bindefault N'[dbo].[df_Date_Captured]', N'[AB_Cash_Book_IBS_Header].[End_Date]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[AB_Cash_Book_IBS_Header].[Import_Filename]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[AB_Cash_Book_IBS_Header].[Posting_Type]'
GO

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

EXEC sp_bindefault N'[dbo].[df_Numeric_One]', N'[AB_Cash_Book_IBS_Header].[Slu_AW_Banks_ID]'
GO

EXEC sp_bindefault N'[dbo].[df_Current_Month]', N'[AB_Cash_Book_IBS_Header].[Slu_AW_Months_ID]'
GO

EXEC sp_bindefault N'[dbo].[df_Current_Year]', N'[AB_Cash_Book_IBS_Header].[Slu_AW_Years_ID]'
GO

EXEC sp_bindefault N'[dbo].[df_Date_Captured]', N'[AB_Cash_Book_IBS_Header].[Start_Date]'
GO

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

setuser
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_One]', N'[AB_Cash_Book_IBS_Map].[AB_Accounts_ID]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_One]', N'[AB_Cash_Book_IBS_Map].[AW_Company_Banks_ID]'
GO

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

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[AB_Cash_Book_IBS_Map].[Bank_Description]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[AB_Cash_Book_IBS_Map].[Comments]'
GO

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

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[AB_Cash_Book_IBS_Map].[Map_1]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[AB_Cash_Book_IBS_Map].[Map_2]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[AB_Cash_Book_IBS_Map].[Map_3]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_One]', N'[AB_Cash_Book_IBS_Map].[Sys_AB_Transaction_Types_ID]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Three]', N'[AB_Cash_Book_IBS_Map].[Sys_AW_Tax_Methods_ID]'
GO

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

setuser
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_One]', N'[AB_Cash_Book_IBS_Map_Splits].[AB_Accounts_ID]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_One]', N'[AB_Cash_Book_IBS_Map_Splits].[AW_Company_Banks_ID]'
GO

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

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

EXEC sp_bindefault N'[dbo].[df_Numeric_Three]', N'[AB_Cash_Book_IBS_Map_Splits].[Sys_AW_Tax_Methods_ID]'
GO

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

setuser
GO

setuser
GO

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

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

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[AB_Cash_Book_IBS_Notes].[Note]'
GO

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

setuser
GO

setuser
GO

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

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

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

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

setuser
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[Slu_AB_Bank_IBS_Rules].[Amount_Field_No]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_One]', N'[Slu_AB_Bank_IBS_Rules].[AW_Company_Banks_ID]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[Slu_AB_Bank_IBS_Rules].[Cheque_Field_No]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[Slu_AB_Bank_IBS_Rules].[Cheque_Identifier]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[Slu_AB_Bank_IBS_Rules].[Cheque_No_From]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[Slu_AB_Bank_IBS_Rules].[Cheque_No_To]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[Slu_AB_Bank_IBS_Rules].[Combine_Field_Nos]'
GO

EXEC sp_bindefault N'[dbo].[df_Date_Updated]', N'[Slu_AB_Bank_IBS_Rules].[Date_Captured]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[Slu_AB_Bank_IBS_Rules].[Date_Field_No]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[Slu_AB_Bank_IBS_Rules].[Delimiter]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[Slu_AB_Bank_IBS_Rules].[Description_Field_No]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[Slu_AB_Bank_IBS_Rules].[Footer_Rows]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_Zero]', N'[Slu_AB_Bank_IBS_Rules].[Header_Rows]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_One]', N'[Slu_AB_Bank_IBS_Rules].[Slu_AW_Active_ID]'
GO

EXEC sp_bindefault N'[dbo].[df_Numeric_One]', N'[Slu_AB_Bank_IBS_Rules].[Slu_AW_Date_Format_ID]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[Slu_AB_Bank_IBS_Rules].[TranType_Field_No]'
GO

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

setuser
GO

ALTER TABLE [dbo].[Lup_AB_Transaction_Type_Maps] ADD 
	CONSTRAINT [FK_Lup_AB_Transaction_Type_Maps_Slu_AW_Banks] FOREIGN KEY 
	(
		[Slu_AW_Banks_ID]
	) REFERENCES [dbo].[Slu_AW_Banks] (
		[Slu_AW_Banks_ID]
	),
	CONSTRAINT [FK_Lup_AB_Transaction_Type_Maps_Sys_AB_Transaction_Types] FOREIGN KEY 
	(
		[Sys_AB_Transaction_Types_ID]
	) REFERENCES [dbo].[Sys_AB_Transaction_Types] (
		[Sys_AB_Transaction_Types_ID]
	)
GO



-------------------------------------------------------
-- Populate data into Sys_AW_Date_Formats
-------------------------------------------------------

INSERT Sys_AW_Date_Formats (Description) VALUES ('yyyy/MM/dd')
INSERT Sys_AW_Date_Formats (Description) VALUES ('yyyy-MM-dd')
INSERT Sys_AW_Date_Formats (Description) VALUES ('dd/MM/yyyy')
INSERT Sys_AW_Date_Formats (Description) VALUES ('dd-MM-yyyy')
INSERT Sys_AW_Date_Formats (Description) VALUES ('MM/dd/yyyy')
INSERT Sys_AW_Date_Formats (Description) VALUES ('MM-dd-yyyy')
INSERT Sys_AW_Date_Formats (Description) VALUES ('yyyyMMdd')
INSERT Sys_AW_Date_Formats (Description) VALUES ('ddMMyyyy')
INSERT Sys_AW_Date_Formats (Description) VALUES ('MMddyyyy')

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


-------------------------------------------------------------------------------
-- NEW REPORTS
-------------------------------------------------------------------------------
    IF NOT EXISTS
        (
            SELECT Sys_AW_Reports_ID
            FROM Sys_AW_Reports
            WHERE Report_No = '194' AND
                  Method_Name = 'Show_frmSalesEnterpriseSales'
        ) 
        BEGIN
            INSERT Sys_AW_Reports
                (
                    Sys_AW_Modules_ID,
                    Type_ID,
                    Location,
                    Report_No,
                    Report_Name,
                    Description,
                    Object_DLL,
                    Class_Name,
                    Method_Name
                )
            VALUES
                (
                    1,
                    1,
                    'Enterprise Sales Reports',
                    '194',
                    'Enterprise Sales',
                    'Enterprise Sales',
                    'SpSSalesReports',
                    'CInterface',
                    'Show_frmSalesEnterpriseSales'
                )
        END

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

    IF NOT EXISTS
        (
            SELECT Sys_AW_Reports_ID
            FROM Sys_AW_Reports
            WHERE Report_No = '219' AND
                  Method_Name = 'Show_frmInventoryReplenishAnalysis'
        ) 
        BEGIN
            INSERT Sys_AW_Reports
                (
                    Sys_AW_Modules_ID,
                    Type_ID,
                    Location,
                    Report_No,
                    Report_Name,
                    Description,
                    Object_DLL,
                    Class_Name,
                    Method_Name
                )
            VALUES
                (
                    2,
                    0,
                    'Product Reports',
                    '219',
                    'Inventory Replenishment Analysis',
                    'Inventory Replenishment Analysis',
                    'SpSProductReports',
                    'CInterface',
                    'Show_frmInventoryReplenishAnalysis'
                )
        END

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

    IF NOT EXISTS
        (
            SELECT Sys_AW_Reports_ID
            FROM Sys_AW_Reports
            WHERE Report_No = '220' AND
                  Method_Name = 'Show_frmInventoryReplenishAnalysis2'
        ) 
        BEGIN
            INSERT Sys_AW_Reports
                (
                    Sys_AW_Modules_ID,
                    Type_ID,
                    Location,
                    Report_No,
                    Report_Name,
                    Description,
                    Object_DLL,
                    Class_Name,
                    Method_Name
                )
            VALUES
                (
                    2,
                    0,
                    'Product Reports',
                    '220',
                    'Inventory Replenishment Analysis Filtered',
                    'Inventory Replenishment Analysis Filtered',
                    'SpSProductReports',
                    'CInterface',
                    'Show_frmInventoryReplenishAnalysis2'
                )
        END

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

    IF NOT EXISTS
        (
            SELECT Sys_AW_Reports_ID
            FROM Sys_AW_Reports
            WHERE Report_No = '115' AND
                  Method_Name = 'Show_RepSalesGPCustomerFilter'
        ) 
        BEGIN
            INSERT Sys_AW_Reports
                (
                    Sys_AW_Modules_ID,
                    Type_ID,
                    Location,
                    Report_No,
                    Report_Name,
                    Description,
                    Object_DLL,
                    Class_Name,
                    Method_Name
                )
            VALUES
                (
                    1,
                    0,
                    'Sales Reports',
                    '115',
                    'Rep Sales GP by Customer Filter',
                    'Rep Sales GP by Customer Filter',
                    'SpSSalesReports',
                    'CInterface',
                    'Show_RepSalesGPCustomerFilter'
                )
        END

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

    IF NOT EXISTS
        (
            SELECT Sys_AW_Reports_ID
            FROM Sys_AW_Reports
            WHERE Report_No = '116' AND
                  Method_Name = 'Show_SalesTransactionListing'
        ) 
        BEGIN
            INSERT Sys_AW_Reports
                (
                    Sys_AW_Modules_ID,
                    Type_ID,
                    Location,
                    Report_No,
                    Report_Name,
                    Description,
                    Object_DLL,
                    Class_Name,
                    Method_Name
                )
            VALUES
                (
                    1,
                    0,
                    'Sales Reports',
                    '116',
                    'Sales Transaction Listing',
                    'Sales Transaction Listing',
                    'SpSSalesReports',
                    'CInterface',
                    'Show_SalesTransactionListing'
                )
        END

-------------------------------------------------------------------------------
-- RETIRED REPORTS
-------------------------------------------------------------------------------

--Retired:    503 - General Ledger Account Listing
    UPDATE Sys_AW_Reports
    SET Slu_AW_Active_ID = 2
    WHERE Sys_AW_Modules_ID = 5 AND
          Report_No = 503

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



-------------------------------------------------------------------------------
--> DATA MODS
-------------------------------------------------------------------------------

--> Set PING timeout PER SITE - uses redundant Area_Code field
UPDATE AW_Remote_Access
SET Area_Code = '4000'

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



--> For USERS that create new Payment Methods - Default to Institution type Bank = 2
EXEC sp_bindefault N'[dbo].[df_Numeric_Two]', N'[Sys_AW_Payment_Methods].[Sys_AW_Institution_Types_ID]'
GO

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






----------------------------------------------------------------------------
-- CREATE THIS NEW FUNCTION - NEEDED TO RESET GL ACCOUNT BALANCE
----------------------------------------------------------------------------
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_AB_SUM_GL_Account_Transactions]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_AB_SUM_GL_Account_Transactions]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


----------------------------------------------------------------------------
--Description   :dbo.fn_AB_SUM_GL_Account_Transactions
--Purpose       :SUM GL Transaction Total for one GL Account
--Date          :2010/07/15
--Authors       :TB
--MODS          :
----------------------------------------------------------------------------

CREATE               FUNCTION dbo.fn_AB_SUM_GL_Account_Transactions
    (
    	@GLAccountID int,
        @CompanyID int
    )

RETURNS money

AS

    BEGIN

        DECLARE @TranTotal money

        SELECT @TranTotal = ISNULL(SUM(AB_Transaction_Amount), 0)
        FROM AB_GL_Transactions
        WHERE AW_User_Companies_ID = @CompanyID AND
              AB_Accounts_ID = @GLAccountID AND
              Slu_AW_Active_ID = 1        --Income/Expense accounts NOT Year-Ended

        RETURN(ROUND(@TranTotal, 2))
    
    END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
----------------------------------------------------------------------------


-- ---------------------------------------------------------------------------------
-- --> SYNC GL ACCOUNT BALANCE to GL TRAN TOTAL - NEW 2004.4.8 - 15/07/10
-- ---------------------------------------------------------------------------------
--     UPDATE AB_GL_Transactions
--     SET AB_Transaction_Amount = ROUND(AB_Transaction_Amount, 2)
--     -----------------------------------------------------------------------------
--     -- UPDATE GL Account Balance SET GL Account Balance <> SUM(GL Transactions)
--     -----------------------------------------------------------------------------
--     UPDATE AB_Accounts
--     SET AB_Account_Balance = dbo.fn_AB_SUM_GL_Account_Transactions(AB_Accounts_ID, AW_User_Companies_ID)
--     FROM AB_Accounts
--     WHERE AW_User_Companies_ID = @CompanyID
-- ---------------------------------------------------------------------------------


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

--> Change System Logo to Spiralsoft logo
UPDATE Sys_AW_Options
SET Control_Value = 'C:\Program Files\Spiralsoft\Advantage\Graphics\SpiralsoftADV1.bmp'
WHERE Control_Type = 'TextBox' AND
      Control_Name = 'txtLogoGraphic' AND
      Control_Index = 0


UPDATE Sys_AW_Options
SET Control_Value = '80'
WHERE Control_Type = 'TextBox' AND
      Control_Name = 'txtBannerLogoLeft' AND
      Control_Index = 0


UPDATE Sys_AW_Options
SET Control_Value = '80'
WHERE Control_Type = 'TextBox' AND
      Control_Name = 'txtBannerLogoTop' AND
      Control_Index = 0

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

UPDATE Slu_AW_Banks
SET Description = 'Bidvest Bank'
WHERE Description = 'Allied Bank'

UPDATE Slu_AW_Banks
SET Description = 'Barclays Bank Plc'
WHERE Description = 'Barclays Swaziland'

UPDATE Slu_AW_Banks
SET Description = 'BNP Paribas'
WHERE Description = 'Bank Of Windhoek'

UPDATE Slu_AW_Banks
SET Description = 'Credit Suisse'
WHERE Description = 'Cape Of Good Hope Bank Limited'

UPDATE Slu_AW_Banks
SET Description = 'Commerzbank AG'
WHERE Description = 'Citizen Bank'

UPDATE Slu_AW_Banks
SET Description = 'HSBC Bank International'
WHERE Description = 'Future Bank'

UPDATE Slu_AW_Banks
SET Description = 'Kwazulu Fin. & Invest. Corp.'
WHERE Description = 'Kwazulu Finance & Investment Corp. Ltd (Ithala)'

UPDATE Slu_AW_Banks
SET Description = 'Lloyds TSB'
WHERE Description = 'Lesotho Bank'

UPDATE Slu_AW_Banks
SET Description = 'Royal Bank of Scotland'
WHERE Description = 'Permanent Bank'

UPDATE Slu_AW_Banks
SET Description = 'Standard Chartered'
WHERE Description = 'Standard Chartered Swaziland & Lesotho'

UPDATE Slu_AW_Banks
SET Description = 'Capitec Bank'
WHERE Description = 'SWA Building Society'

UPDATE Slu_AW_Banks
SET Description = 'FirstRand Bank'
WHERE Description = 'Swabou'

UPDATE Slu_AW_Banks
SET Description = 'Investec Bank'
WHERE Description = 'Investec'

UPDATE Slu_AW_Banks
SET Description = 'Sasfin Bank'
WHERE Description = 'Trust Bank'

UPDATE Slu_AW_Banks
SET Description = 'Rand Merchant Bank'
WHERE Description = 'Unibank Limited'

UPDATE Slu_AW_Banks
SET Description = 'Rennies Bank'
WHERE Description = 'United'

UPDATE Slu_AW_Banks
SET Description = 'Wesbank'
WHERE Description = 'Volkskas'

----------------------------------------------
UPDATE Sys_AW_Applications
SET Application_Seq = 3
WHERE Description = 'Internet Cash Book' AND
      Form_Name = 'frmCashbookIBS'
---------------------------
UPDATE Sys_AW_Applications
SET Application_Seq = 4
WHERE Description = 'Cash Book Batch Posting' AND
      Form_Name = 'frmCashbookBatch'
---------------------------
UPDATE Sys_AW_Applications
SET Application_Seq = 5
WHERE Description = 'General Journals' AND
      Form_Name = 'frmGLJournals'
---------------------------
UPDATE Sys_AW_Applications
SET Application_Seq = 6
WHERE Description = 'Control Accounts' AND
      Form_Name = 'frmControlAccounts'
---------------------------
UPDATE Sys_AW_Applications
SET Application_Seq = 7
WHERE Description = 'Budgeting' AND
      Form_Name = 'frmGLBudgets'
---------------------------
UPDATE Sys_AW_Applications
SET Application_Seq = 8
WHERE Description = 'Year-End Procedures' AND
      Form_Name = 'frmYearEndProcedures'
---------------------------
UPDATE Sys_AW_Applications
SET Application_Seq = 9
WHERE Description = 'GL Transaction Numbers' AND
      Form_Name = 'frmGLTransactionNumbers'
---------------------------
UPDATE Sys_AW_Applications
SET Application_Seq = 10
WHERE Description = 'GL Transaction Editor' AND
      Form_Name = 'frmGLTranEditor'
---------------------------
UPDATE Sys_AW_Applications
SET Application_Seq = 11
WHERE Description = 'GL Account Filters' AND
      Form_Name = 'frmGLFilters'
---------------------------

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'IX_AP_Product_Description'
    )
    BEGIN
        DROP INDEX AP_Products.IX_AP_Product_Description
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AP_Products]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE INDEX [IX_AP_Product_Description] ON [dbo].[AP_Products] ([Description])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'IX_AP_Products_Keywords'
    )
    BEGIN
        DROP INDEX AP_Products.IX_AP_Products_Keywords
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AP_Products]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE INDEX [IX_AP_Products_Keywords] ON [dbo].[AP_Products] ([Keywords])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'IX_Sys_AW_Options_Key'
    )
    BEGIN
        DROP INDEX Sys_AW_Options.IX_Sys_AW_Options_Key
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sys_AW_Options]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE INDEX [IX_Sys_AW_Options_Key] ON [dbo].[Sys_AW_Options] ([Control_Type], [Control_Name], [Control_Index])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'IX_Sys_AW_Applications_Key'
    )
    BEGIN
        DROP INDEX Sys_AW_Applications.IX_Sys_AW_Applications_Key
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sys_AW_Applications]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE INDEX [IX_Sys_AW_Applications_Key] ON [dbo].[Sys_AW_Applications] ([Sys_AW_Modules_ID], [Application_Seq], [Description], [Form_Name])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO


----------------------------------------------------
--> NEW PRIMAEY KEY indexes not originally defined in ADV2004
----------------------------------------------------

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'PK_Sys_AW_Period_Procedure_Types'
    )
    BEGIN
        DROP INDEX Sys_AW_Period_Procedure_Types.PK_Sys_AW_Period_Procedure_Types
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sys_AW_Period_Procedure_Types]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE UNIQUE CLUSTERED INDEX [PK_Sys_AW_Period_Procedure_Types] ON [dbo].[Sys_AW_Period_Procedure_Types] ([Sys_AW_Period_Procedure_Types_ID])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'PK_Sys_AW_Transaction_Numbers'
    )
    BEGIN
        DROP INDEX Sys_AW_Transaction_Numbers.PK_Sys_AW_Transaction_Numbers
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sys_AW_Transaction_Numbers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE CLUSTERED INDEX [PK_Sys_AW_Transaction_Numbers] ON [dbo].[Sys_AW_Transaction_Numbers]([Sys_AW_Transaction_Types_ID])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'PK_Sys_AW_Delivery_Methods'
    )
    BEGIN
        DROP INDEX Sys_AW_Delivery_Methods.PK_Sys_AW_Delivery_Methods
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sys_AW_Delivery_Methods]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE UNIQUE CLUSTERED INDEX [PK_Sys_AW_Delivery_Methods] ON [dbo].[Sys_AW_Delivery_Methods] ([Sys_AW_Delivery_Methods_ID])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'PK_Sys_AS_Numeric_Keys'
    )
    BEGIN
        DROP INDEX Sys_AS_Numeric_Keys.PK_Sys_AS_Numeric_Keys
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sys_AS_Numeric_Keys]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE UNIQUE CLUSTERED INDEX [PK_Sys_AS_Numeric_Keys] ON [dbo].[Sys_AS_Numeric_Keys] ([Sys_AS_Numeric_Keys_ID])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'PK_Sys_AB_Transaction_Numbers'
    )
    BEGIN
        DROP INDEX Sys_AB_Transaction_Numbers.PK_Sys_AB_Transaction_Numbers
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Sys_AB_Transaction_Numbers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE CLUSTERED INDEX [PK_Sys_AB_Transaction_Numbers] ON [dbo].[Sys_AB_Transaction_Numbers]([Sys_AB_Transaction_Types_ID])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'PK_Slu_AW_Inflections'
    )
    BEGIN
        DROP INDEX Slu_AW_Inflections.PK_Slu_AW_Inflections
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Slu_AW_Inflections]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE UNIQUE CLUSTERED INDEX [PK_Slu_AW_Inflections] ON [dbo].[Slu_AW_Inflections] ([Slu_AW_Inflections_ID])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'PK_Lup_AS_Transaction_Profiles'
    )
    BEGIN
        DROP INDEX Lup_AS_Transaction_Profiles.PK_Lup_AS_Transaction_Profiles
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Lup_AS_Transaction_Profiles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE UNIQUE CLUSTERED INDEX [PK_Lup_AS_Transaction_Profiles] ON [dbo].[Lup_AS_Transaction_Profiles] ([Lup_AS_Transaction_Profiles_ID])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'PK_AS_Profiles'
    )
    BEGIN
        DROP INDEX AS_Profiles.PK_AS_Profiles
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AS_Profiles]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE UNIQUE CLUSTERED INDEX [PK_AS_Profiles] ON [dbo].[AS_Profiles] ([AS_Profiles_ID])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'PK_AS_Defaulters'
    )
    BEGIN
        DROP INDEX AS_Defaulters.PK_AS_Defaulters
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AS_Defaulters]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE UNIQUE CLUSTERED INDEX [PK_AS_Defaulters] ON [dbo].[AS_Defaulters] ([AS_Defaulters_ID])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'IX_AS_Day_Ends_Key'
    )
    BEGIN
        DROP INDEX AS_Day_Ends.IX_AS_Day_Ends_Key
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AS_Day_Ends]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE INDEX [IX_AS_Day_Ends_Key] ON [dbo].[AS_Day_Ends] ([Day_End]  desc )
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'PK_AO_Statements'
    )
    BEGIN
        DROP INDEX AO_Statements.PK_AO_Statements
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AO_Statements]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE UNIQUE CLUSTERED INDEX [PK_AO_Statements] ON [dbo].[AO_Statements] ([AO_Statements_ID])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'IX_AO_Statements_Key'
    )
    BEGIN
        DROP INDEX AO_Statements.IX_AO_Statements_Key
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AO_Statements]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE INDEX [IX_AO_Statements_Key] ON [dbo].[AO_Statements] ([AW_Period_Procedures_ID], [FullName])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'PK_AO_Statement_Details'
    )
    BEGIN
        DROP INDEX AO_Statement_Details.PK_AO_Statement_Details
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AO_Statement_Details]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE UNIQUE CLUSTERED INDEX [PK_AO_Statement_Details] ON [dbo].[AO_Statement_Details] ([AO_Statement_Details_ID])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'IX_AO_Statement_Details_Key'
    )
    BEGIN
        DROP INDEX AO_Statement_Details.IX_AO_Statement_Details_Key
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AO_Statement_Details]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE INDEX [IX_AO_Statement_Details_Key] ON [dbo].[AO_Statement_Details] ([AO_Statements_ID])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

---------------------------- >-- TURBO

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'PK_AO_Remittances_Temp'
    )
    BEGIN
        DROP INDEX AO_Remittances_Temp.PK_AO_Remittances_Temp
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AO_Remittances_Temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE UNIQUE CLUSTERED INDEX [PK_AO_Remittances_Temp] ON [dbo].[AO_Remittances_Temp] ([AO_Remittances_Temp_ID])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'PK_AO_Remittance_Details_Temp'
    )
    BEGIN
        DROP INDEX AO_Remittance_Details_Temp.PK_AO_Remittance_Details_Temp
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AO_Remittance_Details_Temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE UNIQUE CLUSTERED INDEX [PK_AO_Remittance_Details_Temp] ON [dbo].[AO_Remittance_Details_Temp] ([AO_Remittance_Details_Temp_ID])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

---------------------------- <-- TURBO

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'PK_AC_Statements_Temp'
    )
    BEGIN
        DROP INDEX AC_Statements_Temp.PK_AC_Statements_Temp
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AC_Statements_Temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE UNIQUE CLUSTERED INDEX [PK_AC_Statements_Temp] ON [dbo].[AC_Statements_Temp] ([AC_Statements_Temp_ID])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'PK_AC_Statement_Details_Temp'
    )
    BEGIN
        DROP INDEX AC_Statement_Details_Temp.PK_AC_Statement_Details_Temp
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AC_Statement_Details_Temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE UNIQUE CLUSTERED INDEX [PK_AC_Statement_Details_Temp] ON [dbo].[AC_Statement_Details_Temp] ([AC_Statement_Details_Temp_ID])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'PK_AC_Statement_Details'
    )
    BEGIN
        DROP INDEX AC_Statement_Details.PK_AC_Statement_Details
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AC_Statement_Details]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE UNIQUE CLUSTERED INDEX [PK_AC_Statement_Details] ON [dbo].[AC_Statement_Details] ([AC_Statement_Details_ID])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'IX_AC_Statement_Details_Key'
    )
    BEGIN
        DROP INDEX AC_Statement_Details.IX_AC_Statement_Details_Key
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AC_Statement_Details]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE INDEX [IX_AC_Statement_Details_Key] ON [dbo].[AC_Statement_Details] ([AC_Statements_ID])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'PK_AC_Statements_ID'
    )
    BEGIN
        DROP INDEX AC_Statements.PK_AC_Statements_ID
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AC_Statements]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE UNIQUE CLUSTERED INDEX [PK_AC_Statements_ID] ON [dbo].[AC_Statements] ([AC_Statements_ID])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'IX_AC_Customers_Fullname'
    )
    BEGIN
        DROP INDEX AC_Customers.IX_AC_Customers_Fullname
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AC_Customers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE INDEX [IX_AC_Customers_Fullname] ON [dbo].[AC_Customers] ([Surname], [Firstnames], [Cell_No], [Home_Tel_No], [Work_Tel_No])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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

IF EXISTS
    (
        SELECT name FROM sysindexes
        WHERE name = 'CI_AS_Transactions'
    )
    BEGIN
        DROP INDEX AS_Transactions.CI_AS_Transactions
    END
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AS_Transactions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE CLUSTERED INDEX [CI_AS_Transactions] ON [dbo].[AS_Transactions] ([AC_Customers_ID], [AS_Transactions_ID])
WITH FILLFACTOR = 90
ON [PRIMARY]
GO

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



----------------------------------------------
-- FULL INDEX REBUILD
----------------------------------------------

-- DECLARE @Database varchar(255),
--         @Table varchar(255),
--         @cmd nvarchar(500),
--         @fillfactor int
-- 
-- 
-- SET @Database = 'DASTEK'
-- SET @fillfactor = 90
-- 
-- 
-- SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName   
--             FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''
-- 
-- -- Create table cursor  
-- EXEC (@cmd)  
-- 
-- OPEN TableCursor
-- 
-- FETCH NEXT FROM TableCursor INTO @Table
-- WHILE @@FETCH_STATUS = 0
-- 
-- BEGIN
-- --    PRINT 'Reindexing ' + @Table
-- 
--     -- SQL 2000 command
--     -------------------
--     DBCC DBREINDEX(@Table,' ',@fillfactor)   
-- 
--     -- SQL 2005 command
--     -------------------
--     -- SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'  
--     -- EXEC (@cmd)  
-- 
--     FETCH NEXT FROM TableCursor INTO @Table   
-- END
-- 
-- CLOSE TableCursor   
-- DEALLOCATE TableCursor  
-- 
-- ----------------------------------------------
-- -- UPDATE STATISTICS - REQUIRED - Performance is impacted after index rebuild
-- ----------------------------------------------
-- EXEC sp_updatestats
-- ----------------------------------------------

