-----------------------------------------------------------------------------
-- ADVANTAGE 2004.4.9.2
-- DB OBJECT CHANGES
-- 08/10/2010
------------------------------------------------------------------------------



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



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



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

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

CREATE TABLE [dbo].[Lup_AP_Shipping_Agents] (
	[Lup_AP_Shipping_Agents_ID] [smallint] IDENTITY (1, 1) NOT NULL ,
	[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Customer_Code] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Warehouse_No] [varchar] (50) 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].[Lup_AP_Shipping_Agents] WITH NOCHECK ADD 
	CONSTRAINT [PK_Lup_AP_Shipping_Agents] PRIMARY KEY  CLUSTERED 
	(
		[Lup_AP_Shipping_Agents_ID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

setuser
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[Lup_AP_Shipping_Agents].[Description]'
GO

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

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

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

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

setuser
GO

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

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

CREATE TABLE [dbo].[Lup_AP_Shipping_Agent_Files] (
	[Lup_AP_Shipping_Agent_Files_ID] [smallint] IDENTITY (1, 1) NOT NULL ,
	[Lup_AP_Shipping_Agents_ID] [smallint] NULL ,
	[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Server_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Login_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Login_Password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Folder] [varchar] (50) 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].[Lup_AP_Shipping_Agent_Files] WITH NOCHECK ADD 
	CONSTRAINT [PK_Lup_AP_Shipping_Agent_Files] PRIMARY KEY  CLUSTERED 
	(
		[Lup_AP_Shipping_Agent_Files_ID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

setuser
GO

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

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

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[Lup_AP_Shipping_Agent_Files].[Folder]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[Lup_AP_Shipping_Agent_Files].[Login_Name]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[Lup_AP_Shipping_Agent_Files].[Login_Password]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[Lup_AP_Shipping_Agent_Files].[Server_Name]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[Lup_AP_Shipping_Agent_Files].[Description]'
GO

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

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

setuser
GO

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

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

CREATE TABLE [dbo].[AP_Bond_Stock_Log] (
	[AP_Bond_Stock_Log_ID] [int] IDENTITY (1, 1) NOT NULL ,
	[File_Type] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Status] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[File_Location] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Computer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FTP_Login_URL] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FTP_User_Name] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FTP_Password] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FTP_Folder] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[File_Date] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[File_Time] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Row_CNT] [varchar] (20) 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 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[AP_Bond_Stock_Log] WITH NOCHECK ADD 
	CONSTRAINT [PK_AP_Bond_Stock_Log] PRIMARY KEY  CLUSTERED 
	(
		[AP_Bond_Stock_Log_ID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

setuser
GO

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

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[AP_Bond_Stock_Log].[Computer]'
GO

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

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[AP_Bond_Stock_Log].[File_Date]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[AP_Bond_Stock_Log].[File_Location]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[AP_Bond_Stock_Log].[File_Time]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[AP_Bond_Stock_Log].[File_Type]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[AP_Bond_Stock_Log].[FTP_Folder]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[AP_Bond_Stock_Log].[FTP_Login_URL]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[AP_Bond_Stock_Log].[FTP_Password]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[AP_Bond_Stock_Log].[FTP_User_Name]'
GO

EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[AP_Bond_Stock_Log].[Row_CNT]'
GO

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

setuser
GO

-------------------------------------------------------
-- POPULATE DATA
-------------------------------------------------------

IF NOT EXISTS
    (
        SELECT Lup_AP_Shipping_Agents_ID
        FROM Lup_AP_Shipping_Agents
        WHERE Customer_Code = '<None>'
    ) 
    BEGIN
        INSERT Lup_AP_Shipping_Agents
            (
                Description,
                Customer_Code,
                Warehouse_No
            )
        VALUES
            (
                '<None>',
                '',
                ''
            )
    END

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

IF NOT EXISTS
    (
        SELECT Lup_AP_Shipping_Agent_Files_ID
        FROM Lup_AP_Shipping_Agent_Files
        WHERE Lup_AP_Shipping_Agents_ID = 1 AND
              Description = '<None>'
    ) 
    BEGIN
        INSERT Lup_AP_Shipping_Agent_Files
            (
                Lup_AP_Shipping_Agents_ID,
                Description,
                Server_Name,
                Login_Name,
                Login_Password,
                Folder
            )
        VALUES
            (
                1,
                '<None>',
                '',
                '',
                '',
                ''
            )
    END

---- Savino Del Bene ONLY ---------------------------------------

-- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Lup_AP_Shipping_Agents]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
-- drop table [dbo].[Lup_AP_Shipping_Agents]
-- GO
-- 
-- CREATE TABLE [dbo].[Lup_AP_Shipping_Agents] (
-- 	[Lup_AP_Shipping_Agents_ID] [smallint] IDENTITY (1, 1) NOT NULL ,
-- 	[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
-- 	[Customer_Code] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
-- 	[Warehouse_No] [varchar] (50) 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].[Lup_AP_Shipping_Agents] WITH NOCHECK ADD 
-- 	CONSTRAINT [PK_Lup_AP_Shipping_Agents] PRIMARY KEY  CLUSTERED 
-- 	(
-- 		[Lup_AP_Shipping_Agents_ID]
-- 	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
-- GO
-- 
-- setuser
-- GO
-- 
-- EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[Lup_AP_Shipping_Agents].[Description]'
-- GO
-- 
-- EXEC sp_bindefault N'[dbo].[df_User_Companies]', N'[Lup_AP_Shipping_Agents].[AW_User_Companies_ID]'
-- GO
-- 
-- EXEC sp_bindefault N'[dbo].[df_Date_Captured]', N'[Lup_AP_Shipping_Agents].[Date_Captured]'
-- GO
-- 
-- EXEC sp_bindefault N'[dbo].[df_Active]', N'[Lup_AP_Shipping_Agents].[Slu_AW_Active_ID]'
-- GO
-- 
-- EXEC sp_bindefault N'[dbo].[df_User_ID]', N'[Lup_AP_Shipping_Agents].[User_ID]'
-- GO
-- 
-- setuser
-- GO
-- 
-- -------------------------------------------
-- 
-- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Lup_AP_Shipping_Agent_Files]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
-- drop table [dbo].[Lup_AP_Shipping_Agent_Files]
-- GO
-- 
-- CREATE TABLE [dbo].[Lup_AP_Shipping_Agent_Files] (
-- 	[Lup_AP_Shipping_Agent_Files_ID] [smallint] IDENTITY (1, 1) NOT NULL ,
-- 	[Lup_AP_Shipping_Agents_ID] [smallint] NULL ,
-- 	[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
-- 	[Server_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
-- 	[Login_Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
-- 	[Login_Password] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
-- 	[Folder] [varchar] (50) 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].[Lup_AP_Shipping_Agent_Files] WITH NOCHECK ADD 
-- 	CONSTRAINT [PK_Lup_AP_Shipping_Agent_Files] PRIMARY KEY  CLUSTERED 
-- 	(
-- 		[Lup_AP_Shipping_Agent_Files_ID]
-- 	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
-- GO
-- 
-- setuser
-- GO
-- 
-- EXEC sp_bindefault N'[dbo].[df_User_Companies]', N'[Lup_AP_Shipping_Agent_Files].[AW_User_Companies_ID]'
-- GO
-- 
-- EXEC sp_bindefault N'[dbo].[df_Date_Captured]', N'[Lup_AP_Shipping_Agent_Files].[Date_Captured]'
-- GO
-- 
-- EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[Lup_AP_Shipping_Agent_Files].[Folder]'
-- GO
-- 
-- EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[Lup_AP_Shipping_Agent_Files].[Login_Name]'
-- GO
-- 
-- EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[Lup_AP_Shipping_Agent_Files].[Login_Password]'
-- GO
-- 
-- EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[Lup_AP_Shipping_Agent_Files].[Server_Name]'
-- GO
-- 
-- EXEC sp_bindefault N'[dbo].[df_Nullstring]', N'[Lup_AP_Shipping_Agent_Files].[Description]'
-- GO
-- 
-- EXEC sp_bindefault N'[dbo].[df_Active]', N'[Lup_AP_Shipping_Agent_Files].[Slu_AW_Active_ID]'
-- GO
-- 
-- EXEC sp_bindefault N'[dbo].[df_User_ID]', N'[Lup_AP_Shipping_Agent_Files].[User_ID]'
-- GO
-- 
-- setuser
-- GO

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

-- INSERT Lup_AP_Shipping_Agents (Description,Customer_Code,Warehouse_No) VALUES ('Savino Del Bene','Lombard','JHBOS 35044')

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

-- INSERT Lup_AP_Shipping_Agent_Files (Lup_AP_Shipping_Agents_ID,Description,Server_Name,Login_Name,Login_Password,Folder)
-- VALUES (1,'Goods Received into Bond','ftp.sdbsa.co.za','LombardT','L0mb@rD','RGB')
-- 
-- INSERT Lup_AP_Shipping_Agent_Files (Lup_AP_Shipping_Agents_ID,Description,Server_Name,Login_Name,Login_Password,Folder)
-- VALUES (1,'Export Notification','ftp.sdbsa.co.za','LombardT','L0mb@rD','EXP')
-- 
-- INSERT Lup_AP_Shipping_Agent_Files (Lup_AP_Shipping_Agents_ID,Description,Server_Name,Login_Name,Login_Password,Folder)
-- VALUES (1,'XIB Issues','ftp.sdbsa.co.za','LombardT','L0mb@rD','XIB')
-- 
-- INSERT Lup_AP_Shipping_Agent_Files (Lup_AP_Shipping_Agents_ID,Description,Server_Name,Login_Name,Login_Password,Folder)
-- VALUES (1,'Bonded Stock Issues','ftp.sdbsa.co.za','LombardT','L0mb@rD','ISS')
-- 
-- INSERT Lup_AP_Shipping_Agent_Files (Lup_AP_Shipping_Agents_ID,Description,Server_Name,Login_Name,Login_Password,Folder)
-- VALUES (1,'Bonded Stock Adjustments','ftp.sdbsa.co.za','LombardT','L0mb@rD','BSA')
-- 
-- INSERT Lup_AP_Shipping_Agent_Files (Lup_AP_Shipping_Agents_ID,Description,Server_Name,Login_Name,Login_Password,Folder)
-- VALUES (1,'Bonded Stock Transfers Out','ftp.sdbsa.co.za','LombardT','L0mb@rD','TRO')
-- 
-- INSERT Lup_AP_Shipping_Agent_Files (Lup_AP_Shipping_Agents_ID,Description,Server_Name,Login_Name,Login_Password,Folder)
-- VALUES (1,'Bonded Stock Transfers In','ftp.sdbsa.co.za','LombardT','L0mb@rD','TRI')
-- 
-- INSERT Lup_AP_Shipping_Agent_Files (Lup_AP_Shipping_Agents_ID,Description,Server_Name,Login_Name,Login_Password,Folder)
-- VALUES (1,'Stock on Hand','ftp.sdbsa.co.za','LombardT','L0mb@rD','SOH')

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


-------------------------------------------------------------------------------
-- NEW REPORTS
-------------------------------------------------------------------------------




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


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



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

IF NOT EXISTS
    (
        SELECT Sys_AC_Account_Types_ID
        FROM Sys_AC_Account_Types
        WHERE Sys_AC_Account_Types_ID = 7 AND
              Description = 'Export'
    ) 
    BEGIN
        INSERT Sys_AC_Account_Types
            (
                Description
            )
        VALUES
            (
                'Export'
            )
    END

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

UPDATE Lup_AS_Procurement_Providers
SET Server_Name = '41.134.56.170'
WHERE Description = '3Sigma Logistics'

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



