-- Description: 		This script creates all Tables (and Indexes) used by Consignor
--
-- Created By: 		  Tellef Ormstad
-- Creation Date: 	27/6-2006
--
-- Updated By: 		  Tellef Ormstad
-- Updated Date: 		21/3-2007 09:00
--
-- Compatibility: MS SQL Server 2005 (9)
--
-- NOTE!
-- After generating the script, the following views and functions have to be moved 
-- manually to the end of the script
-- [fnFilterOutbox]
-- [vFullAddressReceivers]
-- [vFullAddress]
-- [fGetFullAddressSender]
-- [fGetFullAddressReceiver]


USE [Consignor]
GO
--IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'edi')
--CREATE USER [edi] FOR LOGIN [EDI-SOFT\edi] WITH DEFAULT_SCHEMA=[edi]
--GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Documents]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Documents](
	[doc_ID] [int] IDENTITY(1,1) NOT NULL,
	[doc_name] [varchar](100) NOT NULL,
 CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED 
(
	[doc_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CarrierMessageType]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CarrierMessageType](
	[cmt_ID] [int] IDENTITY(1,1) NOT NULL,
	[cmt_Name] [varchar](50) NULL,
 CONSTRAINT [PK_CarrierMessageType] PRIMARY KEY CLUSTERED 
(
	[cmt_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CarrierTagType]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CarrierTagType](
	[ctt_ID] [int] IDENTITY(1,1) NOT NULL,
	[ctt_Name] [varchar](50) NULL,
	[ctt_Description] [varchar](50) NULL,
 CONSTRAINT [PK_CarrierTagType] PRIMARY KEY CLUSTERED 
(
	[ctt_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConsignorTagType]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ConsignorTagType](
	[cot_ID] [int] IDENTITY(1,1) NOT NULL,
	[cot_Name] [varchar](50) NULL,
	[cot_Description] [nvarchar](50) NULL,
 CONSTRAINT [PK_ConsignorTagType] PRIMARY KEY CLUSTERED 
(
	[cot_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FileType]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[FileType](
	[fit_ID] [int] IDENTITY(1,1) NOT NULL,
	[fit_Name] [varchar](50) NOT NULL,
	[fit_Description] [varchar](50) NULL,
 CONSTRAINT [PK_FileType] PRIMARY KEY CLUSTERED 
(
	[fit_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CustomerListType]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CustomerListType](
	[clt_id] [int] IDENTITY(1,1) NOT NULL,
	[clt_type] [int] NOT NULL,
	[clt_name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_CustomerListType] PRIMARY KEY CLUSTERED 
(
	[clt_id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Location]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Location](
	[loc_ID] [int] IDENTITY(54,1) NOT NULL,
	[loc_name] [varchar](50) NOT NULL,
	[loc_order] [int] NOT NULL CONSTRAINT [DF_Location_loc_order]  DEFAULT ((0)),
	[loc_deleted] [bit] NULL,
 CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED 
(
	[loc_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Location]') AND name = N'UX_Location_name')
CREATE UNIQUE NONCLUSTERED INDEX [UX_Location_name] ON [dbo].[Location] 
(
	[loc_name] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Crossdock]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Crossdock](
	[cro_ID] [int] IDENTITY(1,1) NOT NULL,
	[cro_name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Crossdock] PRIMARY KEY CLUSTERED 
(
	[cro_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Category]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Category](
	[cat_ID] [int] IDENTITY(1,1) NOT NULL,
	[cat_name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
	[cat_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Category]') AND name = N'UX_Category_name')
CREATE UNIQUE NONCLUSTERED INDEX [UX_Category_name] ON [dbo].[Category] 
(
	[cat_name] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Installation]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Installation](
	[ins_ID] [int] IDENTITY(1,1) NOT NULL,
	[ins_Key] [varchar](100) NOT NULL,
	[ins_Value] [varchar](100) NULL,
	[ins_Description] [varchar](300) NULL,
	[ins_Created] [datetime] NOT NULL CONSTRAINT [DF_Installation_ins_Created]  DEFAULT (getdate()),
	[ins_Updated] [datetime] NULL,
 CONSTRAINT [PK_Installation] PRIMARY KEY CLUSTERED 
(
	[ins_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Installation]') AND name = N'UX_Installation_Key')
CREATE UNIQUE NONCLUSTERED INDEX [UX_Installation_Key] ON [dbo].[Installation] 
(
	[ins_Key] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShipmentKind]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ShipmentKind](
	[shk_ID] [int] NOT NULL,
	[shk_name] [varchar](20) NOT NULL,
 CONSTRAINT [PK_ShipmentKind] PRIMARY KEY CLUSTERED 
(
	[shk_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ShipmentKind]') AND name = N'UX_ShipmentKind_name')
CREATE UNIQUE NONCLUSTERED INDEX [UX_ShipmentKind_name] ON [dbo].[ShipmentKind] 
(
	[shk_name] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddressKind]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AddressKind](
	[adk_ID] [int] NOT NULL,
	[adk_name] [varchar](50) NOT NULL,
	[adk_multiple] [bit] NOT NULL,
 CONSTRAINT [PK_AddressKind] PRIMARY KEY CLUSTERED 
(
	[adk_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[AddressKind]') AND name = N'UX_AddressKind_name')
CREATE UNIQUE NONCLUSTERED INDEX [UX_AddressKind_name] ON [dbo].[AddressKind] 
(
	[adk_name] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Country]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Country](
	[cou_ID] [int] NOT NULL,
	[cou_identifier] [varchar](50) NULL,
	[cou_isoA2] [char](2) NOT NULL,
	[cou_isoA3] [char](3) NOT NULL,
	[cou_isoN3] [smallint] NOT NULL,
 CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED 
(
	[cou_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Country]') AND name = N'IX_Country_isoA2')
CREATE UNIQUE NONCLUSTERED INDEX [IX_Country_isoA2] ON [dbo].[Country] 
(
	[cou_isoA2] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Country]') AND name = N'IX_Country_isoA3')
CREATE UNIQUE NONCLUSTERED INDEX [IX_Country_isoA3] ON [dbo].[Country] 
(
	[cou_isoA3] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Country]') AND name = N'IX_Country_isoN3')
CREATE NONCLUSTERED INDEX [IX_Country_isoN3] ON [dbo].[Country] 
(
	[cou_isoN3] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Country]') AND name = N'UX_Country_identifier')
CREATE UNIQUE NONCLUSTERED INDEX [UX_Country_identifier] ON [dbo].[Country] 
(
	[cou_identifier] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Currency]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Currency](
	[cur_ID] [int] NOT NULL,
	[cur_code] [char](3) NOT NULL,
	[cur_name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Currency] PRIMARY KEY CLUSTERED 
(
	[cur_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Currency]') AND name = N'UX_Currency_code')
CREATE UNIQUE NONCLUSTERED INDEX [UX_Currency_code] ON [dbo].[Currency] 
(
	[cur_code] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AmountKind]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[AmountKind](
	[amk_ID] [int] NOT NULL,
	[amk_name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_AmountKind] PRIMARY KEY CLUSTERED 
(
	[amk_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[AmountKind]') AND name = N'UX_AmountKind_name')
CREATE UNIQUE NONCLUSTERED INDEX [UX_AmountKind_name] ON [dbo].[AmountKind] 
(
	[amk_name] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShipmentConnectionKind]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ShipmentConnectionKind](
	[sck_ID] [int] NOT NULL,
	[sck_name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_ShipmentConnectionKind] PRIMARY KEY CLUSTERED 
(
	[sck_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ShipmentConnectionKind]') AND name = N'UX_ShipmentConnectionKind_name')
CREATE UNIQUE NONCLUSTERED INDEX [UX_ShipmentConnectionKind_name] ON [dbo].[ShipmentConnectionKind] 
(
	[sck_name] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PrintBox]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[PrintBox](
	[prb_ID] [int] IDENTITY(1,1) NOT NULL,
	[prb_name] [varchar](50) NOT NULL,
	[prb_order] [int] NULL,
 CONSTRAINT [PK_PrintBox] PRIMARY KEY CLUSTERED 
(
	[prb_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RecycleKind]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[RecycleKind](
	[ryk_ID] [int] NOT NULL,
	[ryk_name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_RecycleKind] PRIMARY KEY CLUSTERED 
(
	[ryk_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[RecycleKind]') AND name = N'UX_RecycleKind_name')
CREATE UNIQUE NONCLUSTERED INDEX [UX_RecycleKind_name] ON [dbo].[RecycleKind] 
(
	[ryk_name] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MessageKind]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MessageKind](
	[mek_ID] [int] NOT NULL,
	[mek_name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_MessageKind] PRIMARY KEY CLUSTERED 
(
	[mek_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[MessageKind]') AND name = N'UX_MessageKind_name')
CREATE UNIQUE NONCLUSTERED INDEX [UX_MessageKind_name] ON [dbo].[MessageKind] 
(
	[mek_name] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ReferenceKind]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ReferenceKind](
	[rek_ID] [int] NOT NULL,
	[rek_name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_ReferenceKind] PRIMARY KEY CLUSTERED 
(
	[rek_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ReferenceKind]') AND name = N'UX_ReferenceKind_name')
CREATE UNIQUE NONCLUSTERED INDEX [UX_ReferenceKind_name] ON [dbo].[ReferenceKind] 
(
	[rek_name] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CarriagePayer]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CarriagePayer](
	[cap_ID] [int] NOT NULL,
	[cap_name] [varchar](20) NOT NULL,
 CONSTRAINT [PK_CarriagePayer] PRIMARY KEY CLUSTERED 
(
	[cap_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CarriagePayer]') AND name = N'UX_CarriagePayer_name')
CREATE UNIQUE NONCLUSTERED INDEX [UX_CarriagePayer_name] ON [dbo].[CarriagePayer] 
(
	[cap_name] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StatusKind]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[StatusKind](
	[stk_ID] [int] NOT NULL,
	[stk_name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_StatusKind] PRIMARY KEY CLUSTERED 
(
	[stk_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[StatusKind]') AND name = N'UX_StatusKind_name')
CREATE UNIQUE NONCLUSTERED INDEX [UX_StatusKind_name] ON [dbo].[StatusKind] 
(
	[stk_name] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Language]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Language](
	[lan_ID] [int] NOT NULL,
	[lan_name] [varchar](20) NOT NULL,
 CONSTRAINT [PK_Language] PRIMARY KEY CLUSTERED 
(
	[lan_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MassPrintBox]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MassPrintBox](
	[mpb_ID] [int] IDENTITY(1,1) NOT NULL,
	[mpb_Name] [varchar](50) NOT NULL,
	[mpb_Order] [int] NOT NULL,
 CONSTRAINT [PK_MASSPRINTBOX] PRIMARY KEY CLUSTERED 
(
	[mpb_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Temperature]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Temperature](
	[tem_ID] [int] NOT NULL,
	[tem_name] [varchar](20) NOT NULL,
 CONSTRAINT [PK_Temperature] PRIMARY KEY CLUSTERED 
(
	[tem_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Temperature]') AND name = N'UX_Temperature_name')
CREATE UNIQUE NONCLUSTERED INDEX [UX_Temperature_name] ON [dbo].[Temperature] 
(
	[tem_name] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Performance]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Performance](
	[per_id] [int] IDENTITY(1,1) NOT NULL,
	[per_date] [datetime] NOT NULL,
	[per_file] [image] NOT NULL,
	[per_sent] [bit] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HistoryItemKind]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[HistoryItemKind](
	[hik_id] [int] NOT NULL,
	[hik_name] [varchar](50) NULL,
 CONSTRAINT [PK_HistoryItemKind] PRIMARY KEY CLUSTERED 
(
	[hik_id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ErrorLog]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ErrorLog](
	[err_ID] [int] IDENTITY(1,1) NOT NULL,
	[err_Time] [datetime] NULL,
	[err_ErrorNumber] [int] NULL,
	[err_ErrorText] [varchar](500) NULL,
 CONSTRAINT [PK_ErrorLog] PRIMARY KEY CLUSTERED 
(
	[err_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CustomerCarrierReferenceKind]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CustomerCarrierReferenceKind](
	[crk_ID] [int] NOT NULL,
	[crk_Kind] [varchar](50) NULL,
 CONSTRAINT [PK_CustomerCarrierReferenceKind] PRIMARY KEY CLUSTERED 
(
	[crk_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ReportFormat]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ReportFormat](
	[ref_ID] [int] NOT NULL,
	[ref_Description] [varchar](50) NULL,
	[ref_Report_Extention] [varchar](4) NOT NULL,
	[ref_datetime] [datetime] NOT NULL CONSTRAINT [DF_ReportFormat_ref_datetime]  DEFAULT (getdate()),
 CONSTRAINT [PK_ReportFormat] PRIMARY KEY CLUSTERED 
(
	[ref_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spTrashShipments]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spTrashShipments] 
	@sIdList VARCHAR (MAX), 
	@sUser VARCHAR 
AS 
BEGIN 
	SET NOCOUNT ON; 
	DECLARE @nResult INT 
	DECLARE @dtNow DATETIME 
	DECLARE @sNow VARCHAR(25) 
	SET @dtNow=GETDATE() 
	SET @sNow=CONVERT(VARCHAR(25),@dtNow,126) 
	BEGIN TRY 
		BEGIN TRANSACTION 
			EXEC (''INSERT INTO ShipmentStatus (shs_fk_shi,shs_fk_stk,shs_date,shs_user)  SELECT shi_ID AS shs_fk_shi, CASE WHEN shi_fk_bat > 0 THEN 11 ELSE 10 END AS shs_fk_stk,CONVERT(DATETIME,''''''+@sNow+'''''',126) AS shs_date,''''''+@sUser+'''''' AS shs_user  FROM Shipment WHERE shi_ID IN(''+@sIdList+'') AND (shi_trashed IS NULL OR shi_trashed=0)'') 
			EXEC (''UPDATE Shipment SET shi_trashed=1 WHERE shi_ID IN ('' + @sIdList + '') AND (shi_trashed IS NULL OR shi_trashed=0)'') 
		COMMIT TRANSACTION 
		SET @nResult=0 
	END TRY 
	BEGIN CATCH 
		SET @nResult=@@ERROR 
		IF XACT_STATE() <> 0 
			ROLLBACK TRANSACTION 
	END CATCH 
	RETURN @nResult 
END 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Counter]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Counter](
	[cot_ID] [int] IDENTITY(1003,1) NOT NULL,
	[cot_Name] [varchar](50) NULL,
	[cot_Description] [varchar](50) NULL,
	[cot_Min] [bigint] NOT NULL,
	[cot_Max] [bigint] NOT NULL,
	[cot_NextToUse] [bigint] NULL,
	[cot_Wrap] [bit] NOT NULL CONSTRAINT [DF_Counter_cot_Wrap]  DEFAULT ((0)),
	[cot_WarningLow] [int] NULL,
	[cot_WarningHigh] [int] NULL,
	[cot_CreatedBy] [varchar](50) NULL,
	[cot_CreatedTime] [datetime] NULL,
	[cot_Updatedby] [varchar](50) NULL,
	[cot_UpdatedTime] [datetime] NULL,
	[cot_EANCustNo] [varchar](50) NULL,
	[cot_System] [bit] NULL CONSTRAINT [DF_Counter_cot_System]  DEFAULT ((0)),
	[cot_HTTPNotificationSent] [bit] NULL CONSTRAINT [DF_Counter_cot_HTTPNotificationSent]  DEFAULT ((0)),
	[cot_ConceptId] [int] NULL CONSTRAINT [DF_Counter_cot_ConceptId]  DEFAULT ((0)),
 CONSTRAINT [PK_Counter] PRIMARY KEY CLUSTERED 
(
	[cot_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spDeleteShipments]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spDeleteShipments] 
	@sIdList VARCHAR (MAX) 
AS 
BEGIN 
	DECLARE @nResult INT 
	BEGIN TRY 
		BEGIN TRANSACTION 
		EXEC (''DELETE PackageRef WHERE par_fk_pac IN (SELECT pac_ID FROM Package WHERE pac_fk_lin IN (SELECT lin_ID FROM Line WHERE lin_fk_shi IN (SELECT shi_ID FROM Shipment WHERE shi_ID IN ('' + @sIdList + '') AND shi_dateSubmitted IS NULL)))'') 
		EXEC (''DELETE PackageStatus WHERE pas_fk_pac IN (SELECT pac_ID FROM Package WHERE pac_fk_lin IN (SELECT lin_ID FROM Line WHERE lin_fk_shi IN (SELECT shi_ID FROM Shipment WHERE shi_ID IN ('' + @sIdList + '') AND shi_dateSubmitted IS NULL)))'') 
		EXEC (''DELETE LineHistory WHERE lhi_fk_lin IN (SELECT lin_ID FROM Line WHERE lin_fk_shi IN (SELECT shi_ID FROM Shipment WHERE shi_ID IN ('' + @sIdList + '') AND shi_dateSubmitted IS NULL))'') 
		EXEC (''DELETE Package WHERE pac_fk_lin IN (SELECT lin_ID FROM Line WHERE lin_fk_shi IN (SELECT shi_ID FROM Shipment WHERE shi_ID IN ('' + @sIdList + '') AND shi_dateSubmitted IS NULL))'') 
		EXEC (''DELETE RecycleItem WHERE ryi_fk_lin IN (SELECT lin_ID FROM Line WHERE lin_fk_shi IN (SELECT shi_ID FROM Shipment WHERE shi_ID IN ('' + @sIdList + '') AND shi_dateSubmitted IS NULL))'') 
		EXEC (''DELETE LineRef WHERE lir_fk_lin IN (SELECT lin_ID FROM Line WHERE lin_fk_shi IN (SELECT shi_ID FROM Shipment WHERE shi_ID IN ('' + @sIdList + '') AND shi_dateSubmitted IS NULL))'') 
		EXEC (''DELETE Address WHERE add_fk_shi IN (SELECT shi_ID FROM Shipment WHERE shi_ID IN ('' + @sIdList + '') AND shi_dateSubmitted IS NULL)'') 
		EXEC (''DELETE Amount WHERE amo_fk_shi IN (SELECT shi_ID FROM Shipment WHERE shi_ID IN ('' + @sIdList + '') AND shi_dateSubmitted IS NULL)'') 
		EXEC (''DELETE CarrierStatusMessages WHERE csm_fk_shi IN (SELECT shi_ID FROM Shipment WHERE shi_ID IN ('' + @sIdList + '') AND shi_dateSubmitted IS NULL)'') 
		EXEC (''DELETE ShipmentConnection WHERE shc_fk_shi_parent IN (SELECT shi_ID FROM Shipment WHERE shi_ID IN ('' + @sIdList + '') AND shi_dateSubmitted IS NULL)  OR shc_fk_shi_child IN (SELECT shi_ID FROM Shipment WHERE shi_ID IN ('' + @sIdList + '') AND shi_dateSubmitted IS NULL)'') 
		EXEC (''DELETE DangerousGoods WHERE dng_fk_shi IN (SELECT shi_ID FROM Shipment WHERE shi_ID IN ('' + @sIdList + '') AND shi_dateSubmitted IS NULL)'') 
		EXEC (''DELETE ShipmentDocument WHERE shd_fk_shi IN (SELECT shi_ID FROM Shipment WHERE shi_ID IN ('' + @sIdList + '') AND shi_dateSubmitted IS NULL)'') 
		EXEC (''DELETE Email WHERE ema_fk_shi IN (SELECT shi_ID FROM Shipment WHERE shi_ID IN ('' + @sIdList + '') AND shi_dateSubmitted IS NULL)'') 
		EXEC (''DELETE Line WHERE lin_fk_shi IN (SELECT shi_ID FROM Shipment WHERE shi_ID IN ('' + @sIdList + '') AND shi_dateSubmitted IS NULL)'') 
		EXEC (''DELETE ShipmentLinkFileStorage WHERE slf_fk_shi IN (SELECT shi_ID FROM Shipment WHERE shi_ID IN ('' + @sIdList + '') AND shi_dateSubmitted IS NULL)'') 
		EXEC (''DELETE MassPrint WHERE mp_fk_shi IN (SELECT shi_ID FROM Shipment WHERE shi_ID IN ('' + @sIdList + '') AND shi_dateSubmitted IS NULL)'') 
		EXEC (''DELETE ShipmentMessage WHERE shm_fk_shi IN (SELECT shi_ID FROM Shipment WHERE shi_ID IN ('' + @sIdList + '') AND shi_dateSubmitted IS NULL)'') 
		EXEC (''DELETE Reference WHERE ref_fk_shi IN (SELECT shi_ID FROM Shipment WHERE shi_ID IN ('' + @sIdList + '') AND shi_dateSubmitted IS NULL)'') 
		EXEC (''DELETE ServiceValue WHERE sev_fk_shi IN (SELECT shi_ID FROM Shipment WHERE shi_ID IN ('' + @sIdList + '') AND shi_dateSubmitted IS NULL)'') 
		EXEC (''DELETE ShipmentStatus WHERE shs_fk_shi IN (SELECT shi_ID FROM Shipment WHERE shi_ID IN ('' + @sIdList + '') AND shi_dateSubmitted IS NULL)'') 
		EXEC (''DELETE Shipment WHERE shi_ID IN ('' + @sIdList + '') AND shi_dateSubmitted IS NULL'') 
		COMMIT TRANSACTION 
		SET @nResult=0 
	END TRY 
	BEGIN CATCH 
		SET @nResult=@@ERROR 
		IF XACT_STATE() <> 0 
			ROLLBACK TRANSACTION 
	END CATCH 
	RETURN @nResult 
END 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spUnTrashShipments]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spUnTrashShipments] 
 @sIdList VARCHAR (MAX) 
AS 
BEGIN 
 SET NOCOUNT ON 
 DECLARE @nResult INT 
 BEGIN TRY 
  BEGIN TRANSACTION 
   EXEC (''DELETE ShipmentStatus WHERE shs_ID IN (SELECT MAX (ss2.shs_ID) FROM ShipmentStatus ss2 GROUP BY ss2.shs_fk_shi) AND shs_fk_stk IN (10,11) AND shs_fk_shi IN ('' + @sIdList + '')'') 
   EXEC (''UPDATE Shipment SET shi_trashed=NULL WHERE shi_ID IN ('' + @sIdList + '') AND (shi_trashed=1)'') 
  COMMIT TRANSACTION 
  SET @nResult=0 
 END TRY 
 BEGIN CATCH 
  SET @nResult=@@ERROR 
  IF XACT_STATE() <> 0 
   ROLLBACK TRANSACTION 
 END CATCH 
 RETURN @nResult 
END 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConfigFiles]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ConfigFiles](
	[cof_ID] [int] IDENTITY(1,1) NOT NULL,
	[cof_fileName] [varchar](100) NOT NULL,
	[cof_fileContent] [image] NULL,
	[cof_readOnly] [bit] NOT NULL,
	[cof_dateCreated] [datetime] NOT NULL CONSTRAINT [DF_ConfigFiles_cof_dateCreated]  DEFAULT (getdate()),
	[cof_dateModified] [datetime] NOT NULL CONSTRAINT [DF_ConfigFiles_cof_dateModified]  DEFAULT (getdate()),
	[cof_user] [varchar](100) NOT NULL,
 CONSTRAINT [PK_ConfigFiles] PRIMARY KEY CLUSTERED 
(
	[cof_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[tDateModified]'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER [dbo].[tDateModified] 
ON [dbo].[ConfigFiles] 
AFTER UPDATE 
AS 
BEGIN 
UPDATE ConfigFiles 
SET cof_dateModified = CURRENT_TIMESTAMP 
END; 
' 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Profiles]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Profiles](
	[pri_ID] [int] IDENTITY(1,1) NOT NULL,
	[pri_name] [varchar](100) NOT NULL,
 CONSTRAINT [PK_Profiles] PRIMARY KEY CLUSTERED 
(
	[pri_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CarrierTree]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CarrierTree](
	[crt_ID] [int] IDENTITY(1,1) NOT NULL,
	[crt_fk_car_concept] [int] NULL,
	[crt_fk_sub_concept] [int] NULL,
	[crt_fk_pro_concept] [int] NULL,
 CONSTRAINT [PK_CarrierTree] PRIMARY KEY CLUSTERED 
(
	[crt_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Message]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Message](
	[mes_ID] [int] IDENTITY(1,1) NOT NULL,
	[mes_referenceCount] [int] NOT NULL,
	[mes_messageText] [varchar](1000) NOT NULL,
 CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED 
(
	[mes_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spDeleteCustomers]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create procedure [dbo].[spDeleteCustomers]  
 @sIdList varchar (max)  
as  
begin  
      declare @nresult int  
      begin try  
            begin transaction  
            exec (''delete from CustomerCarrierReferences where ccr_fk_cus IN ('' + @sIdList + '')'') 
            exec (''delete from Address where add_fk_cus IN ('' + @sIdList + '')'') 
            exec (''delete from CustomerSubcarrier where csu_fk_cus IN ('' + @sIdList + '')'') 
            exec (''delete from Customer where cus_ID IN ('' + @sIdList + '')'') 
            commit transaction  
            set @nresult=0  
      end try  
      begin catch  
            set @nresult=@@error  
            if xact_state() <> 0  
                  rollback transaction  
      end catch  
      return @nresult  
end  
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Users]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Users](
	[usr_ID] [int] IDENTITY(1,1) NOT NULL,
	[usr_UserName] [varchar](50) NULL,
	[usr_DomainName] [varchar](50) NULL,
	[usr_LastComputerName] [varchar](50) NULL,
	[usr_LastLoginTime] [datetime] NULL,
	[usr_LastLogoutTime] [datetime] NULL,
	[usr_LastIPAddress] [varchar](25) NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[usr_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DocumentsFilesLink]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DocumentsFilesLink](
	[dof_ID] [int] IDENTITY(1,1) NOT NULL,
	[dof_fk_doc] [int] NOT NULL,
	[dof_fk_cof] [int] NOT NULL,
 CONSTRAINT [PK_DocumentsFilesLink] PRIMARY KEY CLUSTERED 
(
	[dof_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_DocumentsFilesLink] UNIQUE NONCLUSTERED 
(
	[dof_fk_cof] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CarrierStatusMessages]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CarrierStatusMessages](
	[csm_ID] [int] IDENTITY(1,1) NOT NULL,
	[csm_fk_shi] [int] NOT NULL,
	[csm_fk_fih] [int] NOT NULL,
	[csm_fk_cmt] [int] NULL,
 CONSTRAINT [PK_CarrierStatusMessages] PRIMARY KEY CLUSTERED 
(
	[csm_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CarrierStatusTags]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CarrierStatusTags](
	[cst_ID] [int] IDENTITY(1,1) NOT NULL,
	[cst_Value] [varchar](254) NULL,
	[cst_fk_ctt] [int] NOT NULL,
	[cst_fk_csm] [int] NOT NULL,
 CONSTRAINT [PK_CarrierStatusTags] PRIMARY KEY CLUSTERED 
(
	[cst_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConsignorStatusTags]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ConsignorStatusTags](
	[cos_ID] [int] IDENTITY(1,1) NOT NULL,
	[cos_Value] [varchar](254) NULL,
	[cos_fk_cot] [int] NOT NULL,
	[cos_fk_csm] [int] NOT NULL,
 CONSTRAINT [PK_ConsignorStatusTags] PRIMARY KEY CLUSTERED 
(
	[cos_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FileStorage]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[FileStorage](
	[fis_ID] [int] IDENTITY(1,1) NOT NULL,
	[fis_FileName] [varchar](50) NULL,
	[fis_File] [image] NULL,
	[fis_FileDate] [datetime] NULL,
	[fis_Created] [datetime] NULL,
	[fis_fk_fit] [int] NOT NULL,
 CONSTRAINT [PK_FileStorage] PRIMARY KEY CLUSTERED 
(
	[fis_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CustomerList]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CustomerList](
	[cul_id] [int] IDENTITY(1,1) NOT NULL,
	[cul_name] [varchar](50) NOT NULL,
	[cul_fk_clt] [int] NULL,
	[cul_fk_loc] [int] NULL,
 CONSTRAINT [PK_CustomerList] PRIMARY KEY CLUSTERED 
(
	[cul_id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Actor]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Actor](
	[act_ID] [int] IDENTITY(63,1) NOT NULL,
	[act_fk_loc] [int] NOT NULL,
	[act_name] [varchar](50) NOT NULL,
	[act_order] [int] NOT NULL CONSTRAINT [DF_Actor_act_order]  DEFAULT ((0)),
	[act_deleted] [bit] NULL,
	[act_defaultlist] [int] NULL,
 CONSTRAINT [PK_Actor] PRIMARY KEY CLUSTERED 
(
	[act_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Actor]') AND name = N'IX_Actor_location')
CREATE NONCLUSTERED INDEX [IX_Actor_location] ON [dbo].[Actor] 
(
	[act_fk_loc] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConfigTree]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ConfigTree](
	[ctr_ID] [int] IDENTITY(1,1) NOT NULL,
	[ctr_fk_loc] [int] NULL,
	[ctr_fk_act] [int] NULL,
	[ctr_fk_car] [int] NULL,
	[ctr_fk_sub] [int] NULL,
	[ctr_fk_pro] [int] NULL,
	[ctr_order] [int] NULL,
 CONSTRAINT [PK_ConfigTree] PRIMARY KEY CLUSTERED 
(
	[ctr_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserRigth]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[UserRigth](
	[urt_ID] [int] IDENTITY(1,1) NOT NULL,
	[urt_fk_loc] [int] NULL,
	[urt_fk_usr] [int] NOT NULL,
	[urt_Operation] [int] NOT NULL,
 CONSTRAINT [PK_UserRigth] PRIMARY KEY CLUSTERED 
(
	[urt_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[UserRigth]') AND name = N'UX_UserRigth_location_user_operation')
CREATE UNIQUE NONCLUSTERED INDEX [UX_UserRigth_location_user_operation] ON [dbo].[UserRigth] 
(
	[urt_fk_loc] ASC,
	[urt_fk_usr] ASC,
	[urt_Operation] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[UserRigth]') AND name = N'UX_UserRigth_user_location_operation')
CREATE UNIQUE NONCLUSTERED INDEX [UX_UserRigth_user_location_operation] ON [dbo].[UserRigth] 
(
	[urt_fk_usr] ASC,
	[urt_fk_loc] ASC,
	[urt_Operation] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CustomerSubcarrier]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CustomerSubcarrier](
	[csu_ID] [int] IDENTITY(1,1) NOT NULL,
	[csu_fk_cus] [int] NOT NULL,
	[csu_fk_sub] [int] NOT NULL,
	[csu_fk_cro] [int] NULL,
	[csu_Reference] [varchar](50) NULL,
	[csu_accountNumber] [varchar](50) NULL,
	[csu_messageToCarrier] [varchar](1000) NULL,
	[csu_messageToDriver] [varchar](1000) NULL,
 CONSTRAINT [PK_CustomerSubcarrier] PRIMARY KEY CLUSTERED 
(
	[csu_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomerSubcarrier]') AND name = N'IX_CustomerSubcarrier_crossDock')
CREATE NONCLUSTERED INDEX [IX_CustomerSubcarrier_crossDock] ON [dbo].[CustomerSubcarrier] 
(
	[csu_fk_cro] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomerSubcarrier]') AND name = N'IX_CustomerSubcarrier_customer')
CREATE NONCLUSTERED INDEX [IX_CustomerSubcarrier_customer] ON [dbo].[CustomerSubcarrier] 
(
	[csu_fk_cus] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomerSubcarrier]') AND name = N'IX_CustomerSubcarrier_subcarrier')
CREATE NONCLUSTERED INDEX [IX_CustomerSubcarrier_subcarrier] ON [dbo].[CustomerSubcarrier] 
(
	[csu_fk_sub] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Customer]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Customer](
	[cus_ID] [int] IDENTITY(1,1) NOT NULL,
	[cus_fk_cat] [int] NULL,
	[cus_codcustomer] [bit] NULL,
	[cus_fk_cul] [int] NULL,
	[cus_fk_pro] [int] NULL,
	[cus_fk_sub] [int] NULL,
	[cus_fk_car] [int] NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
	[cus_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Customer]') AND name = N'IX_Customer_category')
CREATE NONCLUSTERED INDEX [IX_Customer_category] ON [dbo].[Customer] 
(
	[cus_fk_cat] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Shipment]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Shipment](
	[shi_ID] [int] IDENTITY(1,1) NOT NULL,
	[shi_fk_shi_origin] [int] NULL,
	[shi_fk_pro] [int] NULL,
	[shi_carrier_pro] [int] NULL,
	[shi_fk_sta] [int] NULL,
	[shi_fk_bat] [int] NULL,
	[shi_fk_cap] [int] NULL,
	[shi_fk_tem] [int] NULL,
	[shi_fk_shk] [int] NOT NULL,
	[shi_number] [varchar](50) NULL,
	[shi_barcode1] [varchar](50) NULL,
	[shi_barcode2] [varchar](50) NULL,
	[shi_heightMM] [int] NULL,
	[shi_lengthMM] [int] NULL,
	[shi_widthMM] [int] NULL,
	[shi_volumeWeightG] [int] NULL,
	[shi_carrierWeightG] [int] NULL,
	[shi_carrierHeightMM] [int] NULL,
	[shi_carrierLengthMM] [int] NULL,
	[shi_carrierWidthMM] [int] NULL,
	[shi_carrierVolumeMM3] [bigint] NULL,
	[shi_carrierVolumeWeightG] [int] NULL,
	[shi_privateReceiver] [bit] NULL,
	[shi_insuranceGroup] [int] NULL,
	[shi_returnPackaging] [bit] NULL,
	[shi_dangerous] [bit] NULL,
	[shi_labelsCount] [int] NULL,
	[shi_labelsPrinterID] [int] NULL,
	[shi_freightBillsCount] [int] NULL,
	[shi_freightBillsPrinterID] [int] NULL,
	[shi_printInitiator] [varchar](50) NULL,
	[shi_dimensionalWeightG] [int] NULL,
	[shi_originPostcode] [varchar](12) NULL,
	[shi_originTerminal] [varchar](12) NULL,
	[shi_zonesA] [int] NULL,
	[shi_zonesB] [int] NULL,
	[shi_zonesC] [int] NULL,
	[shi_dateImported] [datetime] NULL,
	[shi_dateSubmitted] [datetime] NULL,
	[shi_outbox] [bit] NULL,
	[shi_trashed] [bit] NULL,
	[shi_exist_ref] [bit] NOT NULL CONSTRAINT [DF_Shipment_shi_exist_ref]  DEFAULT ((0)),
	[shi_ref_SenderCustNoCarrier] [varchar](25) NULL,
	[shi_ref_EdiIdentPostenNorge] [varchar](25) NULL,
	[shi_ref_SenderAccountNumber] [varchar](25) NULL,
	[shi_ref_OrderNumber] [varchar](25) NULL,
	[shi_ref_Operator] [varchar](25) NULL,
	[shi_ref_EdiFileName] [varchar](25) NULL,
	[shi_datePickup] [datetime] NULL,
	[shi_dateLabelPrint] [datetime] NULL,
	[shi_dateTransmit] [datetime] NULL,
	[shi_exist_dng] [bit] NOT NULL CONSTRAINT [DF_Shipment_shi_exist_dng]  DEFAULT ((0)),
	[shi_fk_sea] [int] NOT NULL,
	[shi_dateExpire] [datetime] NULL,
	[shi_ref_agentNo] [varchar](25) NULL,
	[shi_ref_pickupTerminal] [varchar](25) NULL,
 CONSTRAINT [PK_Shipment] PRIMARY KEY CLUSTERED 
(
	[shi_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Shipment]') AND name = N'IX_Shipment')
CREATE NONCLUSTERED INDEX [IX_Shipment] ON [dbo].[Shipment] 
(
	[shi_dateImported] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Shipment]') AND name = N'IX_Shipment_batch')
CREATE NONCLUSTERED INDEX [IX_Shipment_batch] ON [dbo].[Shipment] 
(
	[shi_fk_bat] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Shipment]') AND name = N'IX_Shipment_carrier_product')
CREATE NONCLUSTERED INDEX [IX_Shipment_carrier_product] ON [dbo].[Shipment] 
(
	[shi_carrier_pro] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Shipment]') AND name = N'IX_Shipment_dateImported')
CREATE NONCLUSTERED INDEX [IX_Shipment_dateImported] ON [dbo].[Shipment] 
(
	[shi_dateImported] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Shipment]') AND name = N'IX_Shipment_dateSubmitted')
CREATE NONCLUSTERED INDEX [IX_Shipment_dateSubmitted] ON [dbo].[Shipment] 
(
	[shi_dateSubmitted] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Shipment]') AND name = N'IX_Shipment_kind')
CREATE NONCLUSTERED INDEX [IX_Shipment_kind] ON [dbo].[Shipment] 
(
	[shi_fk_shk] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Shipment]') AND name = N'IX_Shipment_number')
CREATE NONCLUSTERED INDEX [IX_Shipment_number] ON [dbo].[Shipment] 
(
	[shi_number] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Shipment]') AND name = N'IX_Shipment_origin')
CREATE NONCLUSTERED INDEX [IX_Shipment_origin] ON [dbo].[Shipment] 
(
	[shi_fk_shi_origin] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Shipment]') AND name = N'IX_Shipment_outbox')
CREATE NONCLUSTERED INDEX [IX_Shipment_outbox] ON [dbo].[Shipment] 
(
	[shi_outbox] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Shipment]') AND name = N'IX_Shipment_payer')
CREATE NONCLUSTERED INDEX [IX_Shipment_payer] ON [dbo].[Shipment] 
(
	[shi_fk_cap] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Shipment]') AND name = N'IX_Shipment_product')
CREATE NONCLUSTERED INDEX [IX_Shipment_product] ON [dbo].[Shipment] 
(
	[shi_fk_pro] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Shipment]') AND name = N'IX_Shipment_stack')
CREATE NONCLUSTERED INDEX [IX_Shipment_stack] ON [dbo].[Shipment] 
(
	[shi_fk_sta] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Shipment]') AND name = N'IX_Shipment_temperature')
CREATE NONCLUSTERED INDEX [IX_Shipment_temperature] ON [dbo].[Shipment] 
(
	[shi_fk_tem] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Shipment]') AND name = N'IX_Shipment_trashed')
CREATE NONCLUSTERED INDEX [IX_Shipment_trashed] ON [dbo].[Shipment] 
(
	[shi_trashed] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShipmentDocumentStatus]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ShipmentDocumentStatus](
	[sds_ID] [int] IDENTITY(1,1) NOT NULL,
	[sds_fk_shd] [int] NOT NULL,
	[sds_fk_stk] [int] NOT NULL,
	[sds_date] [datetime] NOT NULL,
	[sds_user] [varchar](50) NULL,
 CONSTRAINT [PK_SHIPMENT_DOC_STATUS] PRIMARY KEY CLUSTERED 
(
	[sds_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Address]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Address](
	[add_ID] [int] IDENTITY(1,1) NOT NULL,
	[add_fk_adk] [int] NOT NULL,
	[add_fk_shi] [int] NULL,
	[add_fk_cus] [int] NULL,
	[add_fk_cuo] [int] NULL,
	[add_name1] [varchar](40) NULL,
	[add_name2] [varchar](40) NULL,
	[add_streetAddress1] [varchar](40) NULL,
	[add_streetAddress2] [varchar](40) NULL,
	[add_postCode] [varchar](12) NULL,
	[add_city] [varchar](40) NULL,
	[add_postOfficeBox] [varchar](40) NULL,
	[add_postOfficeBoxPostCode] [varchar](12) NULL,
	[add_postOfficeBoxCity] [varchar](40) NULL,
	[add_state] [varchar](40) NULL,
	[add_fk_cou] [int] NULL,
	[add_phone] [varchar](20) NULL,
	[add_fax] [varchar](20) NULL,
	[add_mobile] [varchar](20) NULL,
	[add_email] [varchar](50) NULL,
	[add_attention] [varchar](40) NULL,
	[add_openingHours] [varchar](8) NULL,
	[add_erpref] [varchar](20) NULL,
 CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED 
(
	[add_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Address]') AND name = N'IX_Address_customer')
CREATE NONCLUSTERED INDEX [IX_Address_customer] ON [dbo].[Address] 
(
	[add_fk_cus] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Address]') AND name = N'IX_Address_kind')
CREATE NONCLUSTERED INDEX [IX_Address_kind] ON [dbo].[Address] 
(
	[add_fk_adk] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Address]') AND name = N'IX_Address_shipment')
CREATE NONCLUSTERED INDEX [IX_Address_shipment] ON [dbo].[Address] 
(
	[add_fk_shi] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LineRef]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[LineRef](
	[lir_ID] [int] IDENTITY(1,1) NOT NULL,
	[lir_fk_lin] [int] NOT NULL,
	[lir_fk_rek] [int] NOT NULL,
	[lir_value] [varchar](50) NOT NULL,
 CONSTRAINT [PK_LineRef] PRIMARY KEY CLUSTERED 
(
	[lir_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[LineRef]') AND name = N'IX_LineRef_kind')
CREATE NONCLUSTERED INDEX [IX_LineRef_kind] ON [dbo].[LineRef] 
(
	[lir_fk_rek] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[LineRef]') AND name = N'IX_LineRef_line')
CREATE NONCLUSTERED INDEX [IX_LineRef_line] ON [dbo].[LineRef] 
(
	[lir_fk_lin] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RecycleItem]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[RecycleItem](
	[ryi_ID] [int] IDENTITY(1,1) NOT NULL,
	[ryi_fk_lin] [int] NOT NULL,
	[ryi_fk_ryk] [int] NOT NULL,
	[ryi_count] [int] NOT NULL,
 CONSTRAINT [PK_RecycleItem] PRIMARY KEY CLUSTERED 
(
	[ryi_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[RecycleItem]') AND name = N'IX_RecycleItem_kind')
CREATE NONCLUSTERED INDEX [IX_RecycleItem_kind] ON [dbo].[RecycleItem] 
(
	[ryi_fk_ryk] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[RecycleItem]') AND name = N'IX_RecycleItem_shipment')
CREATE NONCLUSTERED INDEX [IX_RecycleItem_shipment] ON [dbo].[RecycleItem] 
(
	[ryi_fk_lin] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Package]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Package](
	[pac_ID] [int] IDENTITY(1,1) NOT NULL,
	[pac_fk_lin] [int] NOT NULL,
	[pac_itemNumber] [int] NULL,
	[pac_parcelNumber] [varchar](50) NULL,
	[pac_barcode1] [varchar](50) NULL,
	[pac_barcode2] [varchar](50) NULL,
	[pac_carrierWeightG] [int] NULL,
	[pac_carrierHeightMM] [int] NULL,
	[pac_carrierLengthMM] [int] NULL,
	[pac_carrierWidthMM] [int] NULL,
	[pac_carrierVolumeMM3] [bigint] NULL,
	[pac_carrierVolumeWeightG] [int] NULL,
	[pac_trashed] [bit] NULL,
	[pac_exist_par] [bit] NOT NULL CONSTRAINT [DF_Package_pac_exist_par]  DEFAULT ((0)),
 CONSTRAINT [PK_Package] PRIMARY KEY CLUSTERED 
(
	[pac_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Package]') AND name = N'IX_Package_line')
CREATE NONCLUSTERED INDEX [IX_Package_line] ON [dbo].[Package] 
(
	[pac_fk_lin] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Package]') AND name = N'IX_Package_parcelNumber')
CREATE NONCLUSTERED INDEX [IX_Package_parcelNumber] ON [dbo].[Package] 
(
	[pac_parcelNumber] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LineHistory]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[LineHistory](
	[lhi_id] [int] IDENTITY(1,1) NOT NULL,
	[lhi_fk_hik] [int] NULL,
	[lhi_fk_lin] [int] NULL,
	[lhi_value] [sql_variant] NULL,
	[lhi_user] [varchar](20) NULL,
	[lhi_date] [datetime] NULL,
 CONSTRAINT [PK_LineHistory] PRIMARY KEY CLUSTERED 
(
	[lhi_id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SenderAddress]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[SenderAddress](
	[sea_ID] [int] IDENTITY(1,1) NOT NULL,
	[sea_name1] [varchar](40) NULL,
	[sea_name2] [varchar](40) NULL,
	[sea_streetAddress1] [varchar](40) NULL,
	[sea_streetAddress2] [varchar](40) NULL,
	[sea_postCode] [varchar](12) NULL,
	[sea_city] [varchar](40) NULL,
	[sea_postOfficeBox] [varchar](40) NULL,
	[sea_postOfficeBoxPostCode] [varchar](12) NULL,
	[sea_postOfficeBoxCity] [varchar](40) NULL,
	[sea_state] [varchar](40) NULL,
	[sea_fk_cou] [int] NULL,
	[sea_phone] [varchar](20) NULL,
	[sea_fax] [varchar](20) NULL,
	[sea_mobile] [varchar](20) NULL,
	[sea_email] [varchar](50) NULL,
	[sea_attention] [varchar](40) NULL,
	[sea_openingHours] [varchar](8) NULL,
 CONSTRAINT [PK_SenderAddress] PRIMARY KEY CLUSTERED 
(
	[sea_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Carrier]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Carrier](
	[car_ID] [int] IDENTITY(4,1) NOT NULL,
	[car_fk_cou] [int] NOT NULL,
	[car_fullName] [varchar](50) NOT NULL,
	[car_shortName] [varchar](20) NOT NULL,
	[car_conceptID] [int] NULL,
 CONSTRAINT [PK_Carrier] PRIMARY KEY CLUSTERED 
(
	[car_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Carrier]') AND name = N'IX_Carrier_conceptID')
CREATE NONCLUSTERED INDEX [IX_Carrier_conceptID] ON [dbo].[Carrier] 
(
	[car_conceptID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Carrier]') AND name = N'IX_Carrier_country')
CREATE NONCLUSTERED INDEX [IX_Carrier_country] ON [dbo].[Carrier] 
(
	[car_fk_cou] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Carrier]') AND name = N'UX_Carrier_fullName')
CREATE UNIQUE NONCLUSTERED INDEX [UX_Carrier_fullName] ON [dbo].[Carrier] 
(
	[car_fullName] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CountryName]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CountryName](
	[con_ID] [int] IDENTITY(1,1) NOT NULL,
	[con_fk_cou] [int] NOT NULL,
	[con_fk_lan] [int] NOT NULL CONSTRAINT [DF_CountryName_fk_languageID]  DEFAULT ((7)),
	[con_name] [varchar](50) NOT NULL,
	[con_secondary] [bit] NOT NULL CONSTRAINT [DF_CountryName_secondary]  DEFAULT ((0)),
 CONSTRAINT [PK_CountryName] PRIMARY KEY CLUSTERED 
(
	[con_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CountryName]') AND name = N'IX_CountryName_country')
CREATE NONCLUSTERED INDEX [IX_CountryName_country] ON [dbo].[CountryName] 
(
	[con_fk_cou] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CountryName]') AND name = N'IX_CountryName_language')
CREATE NONCLUSTERED INDEX [IX_CountryName_language] ON [dbo].[CountryName] 
(
	[con_fk_lan] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Amount]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Amount](
	[amo_ID] [int] IDENTITY(1,1) NOT NULL,
	[amo_fk_shi] [int] NOT NULL,
	[amo_fk_amk] [int] NOT NULL,
	[amo_fk_cur] [int] NULL,
	[amo_number] [decimal](16, 2) NOT NULL,
 CONSTRAINT [PK_Amount] PRIMARY KEY CLUSTERED 
(
	[amo_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Amount]') AND name = N'IX_Amount_currency')
CREATE NONCLUSTERED INDEX [IX_Amount_currency] ON [dbo].[Amount] 
(
	[amo_fk_cur] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Amount]') AND name = N'IX_Amount_kind')
CREATE NONCLUSTERED INDEX [IX_Amount_kind] ON [dbo].[Amount] 
(
	[amo_fk_amk] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Amount]') AND name = N'IX_Amount_shipment')
CREATE NONCLUSTERED INDEX [IX_Amount_shipment] ON [dbo].[Amount] 
(
	[amo_fk_shi] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CurrencyName]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CurrencyName](
	[cun_ID] [int] IDENTITY(1,1) NOT NULL,
	[cun_fk_cur] [int] NOT NULL,
	[cun_fk_lan] [int] NOT NULL,
	[cun_name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_CurrencyName] PRIMARY KEY CLUSTERED 
(
	[cun_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CurrencyName]') AND name = N'IX_CurrencyName_currency')
CREATE NONCLUSTERED INDEX [IX_CurrencyName_currency] ON [dbo].[CurrencyName] 
(
	[cun_fk_cur] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CurrencyName]') AND name = N'IX_CurrencyName_language')
CREATE NONCLUSTERED INDEX [IX_CurrencyName_language] ON [dbo].[CurrencyName] 
(
	[cun_fk_lan] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PackageRef]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[PackageRef](
	[par_ID] [int] IDENTITY(1,1) NOT NULL,
	[par_fk_pac] [int] NOT NULL,
	[par_fk_rek] [int] NOT NULL,
	[par_value] [varchar](50) NOT NULL,
 CONSTRAINT [PK_PackageRef] PRIMARY KEY CLUSTERED 
(
	[par_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[PackageRef]') AND name = N'IX_PackageRef_kind')
CREATE NONCLUSTERED INDEX [IX_PackageRef_kind] ON [dbo].[PackageRef] 
(
	[par_fk_rek] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[PackageRef]') AND name = N'IX_PackageRef_package')
CREATE NONCLUSTERED INDEX [IX_PackageRef_package] ON [dbo].[PackageRef] 
(
	[par_fk_pac] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trParAdd]'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER [dbo].[trParAdd]
ON [dbo].[PackageRef]
AFTER INSERT
AS
UPDATE Package SET pac_exist_par=1
FROM inserted
WHERE pac_ID=par_fk_pac
' 
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trParDelete]'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER [dbo].[trParDelete]
ON [dbo].[PackageRef]
AFTER DELETE
AS
UPDATE Package
SET pac_exist_par=0
WHERE NOT EXISTS
(
	SELECT par_fk_pac
	FROM PackageRef
	WHERE par_fk_pac=pac_ID
)' 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PackageStatus]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[PackageStatus](
	[pas_ID] [int] IDENTITY(1,1) NOT NULL,
	[pas_fk_pac] [int] NOT NULL,
	[pas_fk_stk] [int] NOT NULL,
	[pas_date] [datetime] NOT NULL CONSTRAINT [DF_PackageStatus_pas_date]  DEFAULT (getdate()),
	[pas_user] [varchar](50) NULL,
	[pas_documentID] [int] NULL,
 CONSTRAINT [PK_PackageStatus] PRIMARY KEY CLUSTERED 
(
	[pas_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[PackageStatus]') AND name = N'IX_PackageStatus_kind')
CREATE NONCLUSTERED INDEX [IX_PackageStatus_kind] ON [dbo].[PackageStatus] 
(
	[pas_fk_stk] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[PackageStatus]') AND name = N'IX_PackageStatus_package')
CREATE NONCLUSTERED INDEX [IX_PackageStatus_package] ON [dbo].[PackageStatus] 
(
	[pas_fk_pac] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShipmentConnection]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ShipmentConnection](
	[shc_ID] [int] IDENTITY(1,1) NOT NULL,
	[shc_fk_shi_parent] [int] NOT NULL,
	[shc_fk_shi_child] [int] NOT NULL,
	[shc_fk_sck] [int] NOT NULL,
 CONSTRAINT [PK_ShipmentConnection] PRIMARY KEY CLUSTERED 
(
	[shc_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ShipmentConnection]') AND name = N'IX_ShipmentConnection_kind')
CREATE NONCLUSTERED INDEX [IX_ShipmentConnection_kind] ON [dbo].[ShipmentConnection] 
(
	[shc_fk_sck] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ShipmentConnection]') AND name = N'UX_ShipmentConnection_child_parent_kind')
CREATE UNIQUE NONCLUSTERED INDEX [UX_ShipmentConnection_child_parent_kind] ON [dbo].[ShipmentConnection] 
(
	[shc_fk_shi_child] ASC,
	[shc_fk_shi_parent] ASC,
	[shc_fk_sck] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ShipmentConnection]') AND name = N'UX_ShipmentConnection_parent_child_kind')
CREATE UNIQUE NONCLUSTERED INDEX [UX_ShipmentConnection_parent_child_kind] ON [dbo].[ShipmentConnection] 
(
	[shc_fk_shi_parent] ASC,
	[shc_fk_shi_child] ASC,
	[shc_fk_sck] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Product]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Product](
	[pro_ID] [int] IDENTITY(53,1) NOT NULL,
	[pro_fk_sub] [int] NOT NULL,
	[pro_codeFile] [varchar](50) NULL,
	[pro_codeLabel] [varchar](50) NULL,
	[pro_name] [varchar](50) NOT NULL,
	[pro_availableAsReturn] [bit] NULL,
	[pro_availableFrom] [datetime] NULL,
	[pro_availableUntil] [datetime] NULL,
	[pro_allowCOD] [bit] NULL,
	[pro_allowInsurance] [bit] NULL,
	[pro_conceptID] [int] NULL,
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
	[pro_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Product]') AND name = N'IX_Product_conceptID')
CREATE NONCLUSTERED INDEX [IX_Product_conceptID] ON [dbo].[Product] 
(
	[pro_conceptID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Product]') AND name = N'IX_Product_subcarrier')
CREATE NONCLUSTERED INDEX [IX_Product_subcarrier] ON [dbo].[Product] 
(
	[pro_fk_sub] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FileHeader]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[FileHeader](
	[fih_ID] [int] IDENTITY(1,1) NOT NULL,
	[fih_FileID] [varchar](50) NULL,
	[fih_FileVersion] [varchar](50) NULL,
	[fih_ReceiverID] [varchar](50) NULL,
	[fih_ReceivedDate] [datetime] NULL,
	[fih_fk_fis] [int] NULL,
	[fih_fk_sub] [int] NULL,
 CONSTRAINT [PK_FileHeader] PRIMARY KEY CLUSTERED 
(
	[fih_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CustomerCarrierReferences]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CustomerCarrierReferences](
	[ccr_ID] [int] IDENTITY(1,1) NOT NULL,
	[ccr_fk_crk] [int] NULL,
	[ccr_fk_suc] [int] NULL,
	[ccr_fk_cus] [int] NULL,
	[ccr_Value] [varchar](50) NULL,
 CONSTRAINT [PK_CustomerCarrierReferences] PRIMARY KEY CLUSTERED 
(
	[ccr_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Batch]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Batch](
	[bat_ID] [int] IDENTITY(1,1) NOT NULL,
	[bat_fk_sub] [int] NOT NULL,
	[bat_date] [datetime] NULL,
	[bat_fileSent] [bit] NULL,
	[bat_fileSentDate] [datetime] NULL,
 CONSTRAINT [PK_Batch] PRIMARY KEY CLUSTERED 
(
	[bat_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Batch]') AND name = N'IX_Batch_subcarrier')
CREATE NONCLUSTERED INDEX [IX_Batch_subcarrier] ON [dbo].[Batch] 
(
	[bat_fk_sub] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Stack]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Stack](
	[sta_ID] [int] IDENTITY(1,1) NOT NULL,
	[sta_fk_act] [int] NOT NULL,
	[sta_fk_sub] [int] NOT NULL,
	[sta_name] [varchar](50) NOT NULL,
	[sta_order] [int] NOT NULL CONSTRAINT [DF_Stack_sta_order]  DEFAULT ((0)),
	[sta_TransmitOption] [int] NOT NULL CONSTRAINT [DF_Stack_sta_TransmitOption]  DEFAULT ((0)),
	[sta_trashed] [bit] NULL,
 CONSTRAINT [PK_Stack] PRIMARY KEY CLUSTERED 
(
	[sta_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Stack]') AND name = N'IX_Stack_actor')
CREATE NONCLUSTERED INDEX [IX_Stack_actor] ON [dbo].[Stack] 
(
	[sta_fk_act] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Stack]') AND name = N'IX_Stack_subcarrier')
CREATE NONCLUSTERED INDEX [IX_Stack_subcarrier] ON [dbo].[Stack] 
(
	[sta_fk_sub] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShipmentMessage]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ShipmentMessage](
	[shm_ID] [int] IDENTITY(1,1) NOT NULL,
	[shm_fk_shi] [int] NOT NULL,
	[shm_fk_mes] [int] NOT NULL,
	[shm_fk_mek] [int] NOT NULL,
 CONSTRAINT [PK_ShipmentMessage] PRIMARY KEY CLUSTERED 
(
	[shm_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ShipmentMessage]') AND name = N'IX_ShipmentMessage_message_shipment_kind')
CREATE UNIQUE NONCLUSTERED INDEX [IX_ShipmentMessage_message_shipment_kind] ON [dbo].[ShipmentMessage] 
(
	[shm_fk_mes] ASC,
	[shm_fk_shi] ASC,
	[shm_fk_mek] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ShipmentMessage]') AND name = N'UX_ShipmentMessage_shipment_message_kind')
CREATE UNIQUE NONCLUSTERED INDEX [UX_ShipmentMessage_shipment_message_kind] ON [dbo].[ShipmentMessage] 
(
	[shm_fk_shi] ASC,
	[shm_fk_mes] ASC,
	[shm_fk_mek] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Reference]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Reference](
	[ref_ID] [int] IDENTITY(1,1) NOT NULL,
	[ref_fk_shi] [int] NOT NULL,
	[ref_fk_rek] [int] NOT NULL,
	[ref_value] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Reference] PRIMARY KEY CLUSTERED 
(
	[ref_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Reference]') AND name = N'IX_Reference_kind')
CREATE NONCLUSTERED INDEX [IX_Reference_kind] ON [dbo].[Reference] 
(
	[ref_fk_rek] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Reference]') AND name = N'IX_Reference_shipment')
CREATE NONCLUSTERED INDEX [IX_Reference_shipment] ON [dbo].[Reference] 
(
	[ref_fk_shi] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trRefAdd]'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER [dbo].[trRefAdd]
ON [dbo].[Reference]
AFTER INSERT
AS
UPDATE Shipment SET shi_exist_ref=1
FROM inserted
WHERE shi_ID=ref_fk_shi
' 
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trRefDelete]'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER [dbo].[trRefDelete]
ON [dbo].[Reference]
AFTER DELETE
AS
UPDATE Shipment
SET shi_exist_ref=0
WHERE NOT EXISTS
(
	SELECT ref_fk_shi
	FROM Reference
	WHERE ref_fk_shi=shi_ID
)' 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShipmentStatus]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ShipmentStatus](
	[shs_ID] [int] IDENTITY(1,1) NOT NULL,
	[shs_fk_shi] [int] NOT NULL,
	[shs_fk_stk] [int] NOT NULL,
	[shs_date] [datetime] NOT NULL CONSTRAINT [DF_ShipmentStatus_shs_date]  DEFAULT (getdate()),
	[shs_user] [varchar](50) NULL,
 CONSTRAINT [PK_ShipmentStatus] PRIMARY KEY CLUSTERED 
(
	[shs_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ShipmentStatus]') AND name = N'IX_ShipmentStatus_shipment_kind')
CREATE NONCLUSTERED INDEX [IX_ShipmentStatus_shipment_kind] ON [dbo].[ShipmentStatus] 
(
	[shs_fk_shi] ASC,
	[shs_fk_stk] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LanguageName]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[LanguageName](
	[lgn_ID] [int] IDENTITY(1,1) NOT NULL,
	[lgn_fk_lan_of] [int] NOT NULL,
	[lgn_fk_lan_in] [int] NOT NULL,
	[lgn_name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_LanguageName_1] PRIMARY KEY CLUSTERED 
(
	[lgn_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[LanguageName]') AND name = N'IX_LanguageName_lan_in')
CREATE NONCLUSTERED INDEX [IX_LanguageName_lan_in] ON [dbo].[LanguageName] 
(
	[lgn_fk_lan_in] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[LanguageName]') AND name = N'IX_LanguageName_lan_of')
CREATE NONCLUSTERED INDEX [IX_LanguageName_lan_of] ON [dbo].[LanguageName] 
(
	[lgn_fk_lan_of] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MassPrint]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[MassPrint](
	[mp_fk_shi] [int] NOT NULL,
	[mp_fk_mpb] [int] NOT NULL,
	[mp_shi_status] [int] NULL,
	[mp_prn_status] [int] NULL,
	[mp_fk_cbshi] [int] NULL,
	[mp_ID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_MASSPRINT] PRIMARY KEY CLUSTERED 
(
	[mp_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JobLog]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[JobLog](
	[jlg_ID] [int] IDENTITY(1,1) NOT NULL,
	[jlg_time] [timestamp] NOT NULL,
	[jlg_status] [int] NULL,
	[jlg_message] [varchar](100) NULL,
	[jlg_elapsedms] [int] NULL,
	[jlg_fk_job] [int] NULL,
	[jlg_fk_err] [int] NULL,
 CONSTRAINT [PK_JobLog] PRIMARY KEY CLUSTERED 
(
	[jlg_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[JobLog]') AND name = N'IX_JobLog_errorLog')
CREATE NONCLUSTERED INDEX [IX_JobLog_errorLog] ON [dbo].[JobLog] 
(
	[jlg_fk_err] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[JobLog]') AND name = N'IX_JobLog_job')
CREATE NONCLUSTERED INDEX [IX_JobLog_job] ON [dbo].[JobLog] 
(
	[jlg_fk_job] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShipmentLinkFileStorage]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ShipmentLinkFileStorage](
	[slf_ID] [int] NOT NULL,
	[slf_fk_shi] [int] NOT NULL,
	[slf_fk_fis] [int] NOT NULL,
 CONSTRAINT [PK_ShipmentLinkFileStorage] PRIMARY KEY CLUSTERED 
(
	[slf_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Line]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Line](
	[lin_ID] [int] IDENTITY(1,1) NOT NULL,
	[lin_fk_shi] [int] NOT NULL,
	[lin_number] [int] NOT NULL,
	[lin_packageWeightG] [int] NULL,
	[lin_heightMM] [int] NULL,
	[lin_lengthMM] [int] NULL,
	[lin_widthMM] [int] NULL,
	[lin_packageVolumeMM3] [bigint] NULL,
	[lin_volumeWeightG] [int] NULL,
	[lin_dimensionalWeightG] [int] NULL,
	[lin_loadMeterMM] [int] NULL,
	[lin_trashed] [bit] NULL,
	[lin_lineWeightG] [int] NULL,
	[lin_lineVolumeMM3] [bigint] NULL,
 CONSTRAINT [PK_Line] PRIMARY KEY CLUSTERED 
(
	[lin_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Line]') AND name = N'IX_Line_shipment')
CREATE NONCLUSTERED INDEX [IX_Line_shipment] ON [dbo].[Line] 
(
	[lin_fk_shi] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ServiceValue]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ServiceValue](
	[sev_fk_shi] [int] NOT NULL,
	[sev_fk_ser] [int] NOT NULL,
	[sev_value] [bit] NOT NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShipmentDocument]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ShipmentDocument](
	[shd_ID] [int] IDENTITY(1,1) NOT NULL,
	[shd_fk_shi] [int] NOT NULL,
	[shd_documentID] [int] NOT NULL,
	[shd_count] [int] NULL,
	[shd_printerPath] [varchar](100) NULL,
	[shd_printed] [datetime] NULL,
	[shd_printbox] [int] NULL,
 CONSTRAINT [PK_ShipmentDocument] PRIMARY KEY CLUSTERED 
(
	[shd_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ShipmentDocument]') AND name = N'IX_ShipmentDocument_shipment')
CREATE NONCLUSTERED INDEX [IX_ShipmentDocument_shipment] ON [dbo].[ShipmentDocument] 
(
	[shd_documentID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EMail]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[EMail](
	[ema_id] [int] IDENTITY(1,1) NOT NULL,
	[ema_From] [varchar](32) NULL,
	[ema_To] [varchar](32) NULL,
	[ema_Subject] [varchar](32) NULL,
	[ema_Body] [ntext] NULL,
	[ema_cc] [varchar](32) NULL,
	[ema_fk_shi] [int] NULL,
	[ema_Body2] [varchar](256) NULL,
 CONSTRAINT [PK_EMail] PRIMARY KEY CLUSTERED 
(
	[ema_id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[EMail]') AND name = N'IX_EMail_shipment')
CREATE NONCLUSTERED INDEX [IX_EMail_shipment] ON [dbo].[EMail] 
(
	[ema_fk_shi] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DangerousGoods]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[DangerousGoods](
	[dng_id] [int] IDENTITY(1,1) NOT NULL,
	[dng_fk_shi] [int] NOT NULL,
	[dng_kind] [varchar](10) NULL,
	[dng_un] [int] NOT NULL,
	[dng_name] [varchar](300) NOT NULL,
	[dng_shortname] [varchar](50) NULL,
	[dng_description] [varchar](50) NULL,
	[dng_class] [varchar](5) NULL,
	[dng_classification] [varchar](10) NULL,
	[dng_packinggroup] [varchar](5) NULL,
	[dng_identification] [varchar](10) NULL,
	[dng_count] [int] NULL,
	[dng_grossweightG] [int] NULL,
	[dng_netweightG] [int] NULL,
	[dng_volumemm3] [bigint] NULL,
	[dng_ems] [varchar](10) NULL,
	[dng_mfag] [varchar](10) NULL,
	[dng_flashpoint] [varchar](10) NULL,
	[dng_stowingcat] [varchar](10) NULL,
	[dng_pagenumber] [varchar](10) NULL,
	[dng_labels] [varchar](10) NULL,
 CONSTRAINT [PK_DangerousGoods] PRIMARY KEY CLUSTERED 
(
	[dng_id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trDngAdd]'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER [dbo].[trDngAdd]
ON [dbo].[DangerousGoods]
AFTER INSERT
AS
UPDATE Shipment SET shi_exist_dng=1
FROM inserted
WHERE shi_ID=dng_fk_shi
' 
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trDngDelete]'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER [dbo].[trDngDelete]
ON [dbo].[DangerousGoods]
AFTER DELETE
AS
UPDATE Shipment
SET shi_exist_dng=0
WHERE NOT EXISTS
(
	SELECT dng_fk_shi
	FROM DangerousGoods
	WHERE dng_fk_shi=shi_ID
)
' 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[BatchReports]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[BatchReports](
	[bar_ID] [int] IDENTITY(1,1) NOT NULL,
	[bar_report_file] [image] NOT NULL,
	[bar_datetime] [datetime] NOT NULL,
	[bar_fk_bat] [int] NOT NULL,
	[bar_fk_ref] [int] NOT NULL,
	[bar_EDI_file] [bit] NOT NULL CONSTRAINT [DF_BatchReports_bar_EDI_file]  DEFAULT ((0)),
	[bar_identity] [varchar](20) NULL,
	[bar_report_name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_BatchReports] PRIMARY KEY CLUSTERED 
(
	[bar_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[BatchReports]') AND name = N'IX_BatchReports_batch')
CREATE NONCLUSTERED INDEX [IX_BatchReports_batch] ON [dbo].[BatchReports] 
(
	[bar_fk_bat] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[BatchReports]') AND name = N'IX_BatchReports_format')
CREATE NONCLUSTERED INDEX [IX_BatchReports_format] ON [dbo].[BatchReports] 
(
	[bar_fk_ref] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[JobValue]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[JobValue](
	[jov_ID] [int] IDENTITY(1,1) NOT NULL,
	[jov_Name] [varchar](50) NULL,
	[jov_sValue] [varchar](260) NULL,
	[jov_nValue] [int] NULL,
	[jov_fk_job] [int] NOT NULL,
 CONSTRAINT [PK_JobValue] PRIMARY KEY CLUSTERED 
(
	[jov_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[JobValue]') AND name = N'IX_JobValue_job')
CREATE NONCLUSTERED INDEX [IX_JobValue_job] ON [dbo].[JobValue] 
(
	[jov_fk_job] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ScheduleTime]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ScheduleTime](
	[sch_ID] [int] IDENTITY(1,1) NOT NULL,
	[sch_Time] [datetime] NULL,
	[sch_Days] [int] NULL,
	[sch_Once] [bit] NULL,
	[sch_fk_job] [int] NOT NULL,
	[sch_Later] [bit] NULL CONSTRAINT [DF_ScheduleTime_sch_Later]  DEFAULT ((0)),
	[sch_LastRuntime] [datetime] NULL,
 CONSTRAINT [PK_ScheduleTime] PRIMARY KEY CLUSTERED 
(
	[sch_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ScheduleTime]') AND name = N'IX_ScheduleTime_job')
CREATE NONCLUSTERED INDEX [IX_ScheduleTime_job] ON [dbo].[ScheduleTime] 
(
	[sch_fk_job] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ActorLinkCustomerList]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ActorLinkCustomerList](
	[alc_id] [int] IDENTITY(1,1) NOT NULL,
	[alc_fk_act] [int] NOT NULL,
	[alc_fk_cul] [int] NOT NULL,
 CONSTRAINT [PK_ActorLinkCustomerList] PRIMARY KEY CLUSTERED 
(
	[alc_id] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConfigTreeFilesLink]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ConfigTreeFilesLink](
	[ctf_ID] [int] IDENTITY(1,1) NOT NULL,
	[ctf_fk_ctr] [int] NOT NULL,
	[ctf_fk_cof] [int] NOT NULL,
 CONSTRAINT [PK_ConfigTreeFilesLink] PRIMARY KEY CLUSTERED 
(
	[ctf_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_ConfigTreeFilesLink] UNIQUE NONCLUSTERED 
(
	[ctf_fk_cof] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConfigSection]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ConfigSection](
	[cse_ID] [int] IDENTITY(1,1) NOT NULL,
	[cse_fk_cof] [int] NOT NULL,
	[cse_name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_ConfigSection] PRIMARY KEY CLUSTERED 
(
	[cse_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CarrierTreeFilesLink]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CarrierTreeFilesLink](
	[caf_ID] [int] IDENTITY(1,1) NOT NULL,
	[caf_fk_crt] [int] NOT NULL,
	[caf_fk_cof] [int] NOT NULL,
 CONSTRAINT [PK_CarrierTreeFilesLink] PRIMARY KEY CLUSTERED 
(
	[caf_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_CarrierTreeFilesLink] UNIQUE NONCLUSTERED 
(
	[caf_fk_cof] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProfilesFilesLink]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ProfilesFilesLink](
	[prf_ID] [int] IDENTITY(1,1) NOT NULL,
	[prf_fk_pri] [int] NOT NULL,
	[prf_fk_cof] [int] NOT NULL,
 CONSTRAINT [PK_ProfilesFilesLink] PRIMARY KEY CLUSTERED 
(
	[prf_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [IX_ProfilesFilesLink] UNIQUE NONCLUSTERED 
(
	[prf_fk_cof] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FactorySettings]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[FactorySettings](
	[fas_ID] [int] IDENTITY(1,1) NOT NULL,
	[fas_fk_cof] [int] NOT NULL,
 CONSTRAINT [PK_FactorySettings] PRIMARY KEY CLUSTERED 
(
	[fas_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Subcarrier]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Subcarrier](
	[sub_ID] [int] IDENTITY(9,1) NOT NULL,
	[sub_fk_car] [int] NOT NULL,
	[sub_name] [varchar](50) NOT NULL,
	[sub_maxShipmentLines] [int] NULL,
	[sub_conceptID] [int] NULL,
 CONSTRAINT [PK_Subcarrier] PRIMARY KEY CLUSTERED 
(
	[sub_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Subcarrier]') AND name = N'IX_Subcarrier_carrier')
CREATE NONCLUSTERED INDEX [IX_Subcarrier_carrier] ON [dbo].[Subcarrier] 
(
	[sub_fk_car] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Subcarrier]') AND name = N'IX_Subcarrier_conceptID')
CREATE NONCLUSTERED INDEX [IX_Subcarrier_conceptID] ON [dbo].[Subcarrier] 
(
	[sub_conceptID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConfigValue]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ConfigValue](
	[cov_ID] [int] IDENTITY(1,1) NOT NULL,
	[cov_fk_cse] [int] NOT NULL,
	[cov_name] [varchar](50) NOT NULL,
	[cov_valueShort] [varchar](200) NULL,
	[cov_valueLong] [image] NULL,
 CONSTRAINT [PK_ConfigValue] PRIMARY KEY CLUSTERED 
(
	[cov_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Constraints]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Constraints](
	[cns_ID] [int] IDENTITY(1,1) NOT NULL,
	[cns_Default_fk_cul] [int] NULL
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Job]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Job](
	[job_ID] [int] IDENTITY(1,1) NOT NULL,
	[job_Name] [varchar](50) NULL,
	[job_ModuleName] [varchar](50) NULL,
	[job_ObjectType] [varchar](50) NULL,
	[job_ObjectName] [varchar](50) NULL,
	[job_RunModule] [varchar](50) NULL,
	[job_ReportOption] [int] NULL,
	[job_Queued] [bit] NOT NULL CONSTRAINT [DF_Job_job_Queued]  DEFAULT ((0)),
	[job_CreatedTime] [datetime] NULL,
	[job_fk_usr] [int] NULL,
 CONSTRAINT [PK_Job] PRIMARY KEY CLUSTERED 
(
	[job_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Job]') AND name = N'IX_Job_user')
CREATE NONCLUSTERED INDEX [IX_Job_user] ON [dbo].[Job] 
(
	[job_fk_usr] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spAddEditDocument]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N' CREATE PROCEDURE [dbo].[spAddEditDocument] 
 	@DocID	INT OUTPUT, 
 	@Edit	BIT, 
 	@Name	VARCHAR (100), 
 	@Result	INT OUTPUT 
 AS 
 BEGIN 
 	BEGIN TRANSACTION 
 	SET @Result = 0 
 	IF @Edit = 1 
 	BEGIN 
 		UPDATE Documents SET doc_name = @Name WHERE doc_ID = @DocID 
 		IF (@@ERROR <> 0) OR (@@ROWCOUNT <> 1) 
 			SET @Result = 1 
 	END 
 	ELSE 
 	BEGIN 
 		IF ISNULL (@DocID, 0) = 0 
 		BEGIN 
 			INSERT INTO Documents (doc_name) VALUES (@Name) 
 			IF (@@ERROR <> 0) OR (@@ROWCOUNT <> 1) 
 				SET @Result = 2 
 			ELSE 
 				SELECT @DocID = SCOPE_IDENTITY() FROM Documents 
 		END 
 		ELSE 
 		BEGIN 
 			SET IDENTITY_INSERT Documents ON 
 			INSERT INTO Documents (doc_ID, doc_name) VALUES (@DocID, @Name) 
 			IF (@@ERROR <> 0) OR (@@ROWCOUNT <> 1) 
 				SET @Result = 3 
 			SET IDENTITY_INSERT Documents OFF 
 		END 
 	END 
 	IF @Result = 0 
 		COMMIT TRANSACTION 
 	ELSE 
 		ROLLBACK TRANSACTION 
 END 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spEnsureLanguageNameInDB]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Hans Kristian Haug
-- Create date: 03.11.2006
-- Description:	Ensure that a language name exist in DB
-- =============================================
CREATE PROCEDURE [dbo].[spEnsureLanguageNameInDB] 
	@nLanguageID_of INT, 
	@nLanguageID_in INT, 
	@sName VARCHAR (50)
AS
BEGIN
	IF EXISTS (SELECT * FROM LanguageName WHERE lgn_fk_lan_of=@nLanguageID_of AND lgn_fk_lan_in=@nLanguageID_in)
	BEGIN
		UPDATE LanguageName SET
			lgn_name=@sName
		WHERE lgn_fk_lan_of=@nLanguageID_of AND lgn_fk_lan_in=@nLanguageID_in
	END
	ELSE
	BEGIN
		INSERT LanguageName (lgn_fk_lan_of,lgn_fk_lan_in,lgn_name)
			VALUES (@nLanguageID_of,@nLanguageID_in,@sName)
	END
END

' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCleanDatabase]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spCleanDatabase]
AS
BEGIN
DELETE Address
DELETE Amount
DELETE CarrierStatusTags
DELETE ConsignorStatusTags
DELETE CarrierStatusMessages
DELETE Crossdock
DELETE DangerousGoods
DELETE EMail
DELETE ScheduleTime
DELETE JobLog
DELETE JobValue
DELETE Job
DELETE ErrorLog
DELETE MassPrint
DELETE MassPrintBox
DELETE Performance
DELETE PrintBox
DELETE ServiceValue
DELETE ShipmentConnection
DELETE ShipmentDocument
DELETE ShipmentLinkFileStorage
DELETE FileHeader
DELETE FileStorage
DELETE ShipmentMessage
DELETE Message
DELETE ShipmentStatus
DELETE PackageRef
DELETE PackageStatus
DELETE Package
DELETE RecycleItem
DELETE LineHistory
DELETE LineRef
DELETE Line
DELETE BatchReports
DELETE Reference
DELETE Shipment
DELETE SenderAddress
DELETE Batch
DELETE Stack
DELETE Product WHERE pro_conceptID IS NULL
DELETE Subcarrier WHERE sub_conceptID IS NULL
DELETE Carrier WHERE car_conceptID IS NULL
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spShipmentsToMassPrint]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'create procedure [dbo].[spShipmentsToMassPrint]  
      @ShiIDs     varchar(1024), 
      @BoxID      int, 
      @Result     int output 
as 
begin 
      declare @Tmp      varchar(1024), 
            @ShiIDStr   varchar(50), 
            @Pos        int 
      set @Result = 0 
      set @Tmp = @ShiIDs 
      while len(@Tmp) > 0 
      begin 
            set @ShiIDStr = '''' 
            set @Pos = charindex('';'', @Tmp) 
            if @Pos = 0 
            begin 
                  set @ShiIDStr = @Tmp 
                  set @Tmp = '''' 
            end 
            else 
            begin 
                  set @ShiIDStr = substring(@Tmp, 1, @Pos - 1) 
                  set @Tmp = substring(@Tmp, @Pos + 1, 1024) 
            end    
            if len(@ShiIDStr) > 0  
                  if not exists (select 1 from MassPrint where mp_fk_shi = cast(@ShiIDStr as int) and mp_fk_mpb = @BoxID) 
                  begin 
                        insert into MassPrint(mp_fk_shi, mp_fk_mpb, mp_shi_status, mp_prn_status)  
                        values (cast(@ShiIDStr as int), @BoxID, 0, 0) 
                        if (@@ERROR <> 0) or (@@ROWCOUNT <> 1) 
                        begin 
                              set @Result = 1 
                              BREAK 
                        end 
                  end 
      end 
end 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnTASMessage]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Adrian Budileanu
-- Description:	Build TAS message
-- =============================================
CREATE FUNCTION [dbo].[fnTASMessage]
(
	@jobID int
)
RETURNS nvarchar(4000)
AS
BEGIN

	DECLARE @jobLogID int;
	SET @jobLogID = (select jlg_ID from dbo.JobLog where jlg_fk_job=@jobID);

	DECLARE @errorID int;
	SET @errorID = (select jlg_fk_err as errID from dbo.JobLog where jlg_ID = @jobLogID)

	DECLARE @jobmodule nvarchar(50);
	SET @jobmodule = (select job_RunModule from dbo.Job where job_ID = @jobID)

	
	DECLARE @messagedate datetime;
	SET @messagedate = GetDate();
	
	DECLARE @msgtype int;
	SET @msgtype = (
		case @jobmodule 
			when ''Transmit.bpl'' then 0
--			when '''' then 1
--			when '''' then 2
			else -1
		end
	)

	DECLARE @msgtypename nvarchar(20);
	SET @msgtypename = (
		case @msgtype
			when 0 then ''Transmit''
--			when 1 then ''''
--			when 2 then ''''
			else ''unknown''
		end		
		)

	DECLARE @msgdatetime nvarchar(50);
	DECLARE @values nvarchar(4000);
	
	SET @msgdatetime = ''<DateTime>'' + (CONVERT(nvarchar(30), @messagedate, 126)) + ''</DateTime>'';

-- ================================= --
-- Here we build job specific values --
-- ================================= --

	SET @values =
		''<Values>'' + (
		case @msgtype
			when 0 then dbo.fnTASTransmitValues( @jobID )
			else ''''
		end	) +
		''</Values>''
-- ================================= --
--    End build message values       --
-- ================================= --


-- Building error message string
	DECLARE @error nvarchar(400);
	if EXISTS(select * from dbo.ErrorLog where err_ID = @errorID)
		SET @error = ''<Error>'' + (select * from dbo.ErrorLog where err_ID = @errorID FOR XML RAW) + ''</Error>''
	else
		SET @error = '''';


-- Installation info
	DECLARE @instinfo nvarchar(50);
	SET @instinfo = (''<AppID id="'' + (select ins_Value from Installation where ins_Key=''InstallationID'') + ''" />'');
	

-- Building Job info
	DECLARE @jobinfo nvarchar(256);
	SET @jobinfo = (''<JobInfo username="'' + (select usr_UserName from dbo.Users where usr_ID=(select job_fk_usr from dbo.Job where job_ID=@jobID) ) + ''"''
			+ '' computer="'' + (select usr_LastComputerName from dbo.Users where usr_ID=(select job_fk_usr from dbo.Job where job_ID=@jobID) ) + ''"''
			+ '' domain="'' + (select usr_DomainName from dbo.Users where usr_ID=(select job_fk_usr from dbo.Job where job_ID=@jobID) ) + ''"''
--			+ '' text="'' + (select jlg_message from dbo.JobLog where jlg_ID = @jobLogID) + ''"''
			+ '' />'');
 
 
-- Building message
	DECLARE @message nvarchar(4000);
	SET @message = ''<Message type="'' + @msgtypename + ''">''
		+ @msgdatetime
		+ @instinfo
		+ @jobinfo
		+ @values
		+ @error
		+''</Message>''
	
	return @message
END

' 
END

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnTraceViewLastStatusCarrier]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[fnTraceViewLastStatusCarrier]
(
	@shipmentID INT
)
RETURNS VARCHAR (50)
AS
BEGIN
	DECLARE @status VARCHAR (50)
	SET @status = 
	(
		SELECT TOP 1 cos_Value FROM CarrierStatusMessages
			INNER JOIN ConsignorStatusTags ON csm_ID=cos_fk_csm 
			INNER JOIN ConsignorTagType ON cot_ID=cos_fk_cot
		WHERE cot_Name=''Status'' AND csm_fk_shi=@shipmentID
		ORDER BY cos_ID DESC
	)
	RETURN @status
END



' 
END

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnTraceViewStatusCarrierHistory]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[fnTraceViewStatusCarrierHistory]
(
)
RETURNS TABLE
AS
RETURN
(
SELECT TOP 100 PERCENT csm_fk_shi,
	(SELECT cos_Value FROM ConsignorStatusTags INNER JOIN ConsignorTagType ON cos_fk_cot=cot_ID
		WHERE cot_Name=''Status'' and cos_fk_csm=csm_ID) AS CarrierStatus,
	(SELECT cos_Value FROM ConsignorStatusTags INNER JOIN ConsignorTagType ON cos_fk_cot=cot_ID
		WHERE cot_Name=''Status Date'' and cos_fk_csm=csm_ID) AS CarrierStatusDate,
	(SELECT cos_Value FROM ConsignorStatusTags INNER JOIN ConsignorTagType ON cos_fk_cot=cot_ID
		WHERE cot_Name=''Location'' and cos_fk_csm=csm_ID) AS Location
	FROM CarrierStatusMessages
	GROUP BY csm_fk_shi, csm_ID
	ORDER BY csm_ID DESC
)
' 
END

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnTraceViewLastStatusLocationCarrier]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[fnTraceViewLastStatusLocationCarrier]
(
	@shipmentID INT
)
RETURNS varchar(255)
AS
BEGIN
	DECLARE @location varchar(255)
	SET @location = 
	(
		SELECT TOP 1 cos_Value FROM CarrierStatusMessages
			INNER JOIN ConsignorStatusTags ON csm_ID=cos_fk_csm
			INNER JOIN ConsignorTagType ON cot_ID=cos_fk_cot
		WHERE cot_Name=''Location'' AND csm_fk_shi=@shipmentID
		ORDER BY cos_ID DESC
	)
	RETURN @location
END


' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnTASTransmitValues]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Adrian Budileanu
-- Description:	Return job values as text for a transmit job
-- =============================================
CREATE FUNCTION [dbo].[fnTASTransmitValues]
(
	@jobID int = 0
)
RETURNS nvarchar(256)
AS
BEGIN
	DECLARE @stackID int;
	SET @stackID = (select jov_nValue from dbo.JobValue where jov_fk_job = @jobID and jov_Name=''TransmitStackID'');

	return 
		''<Stack name="''
		+ (select sta_name from dbo.Stack where sta_ID = @stackID) +
		''" id="'' + LTRIM(RTRIM(CONVERT(nvarchar(20), @stackID))) + ''"/>''
		+
		''<Subcarrier name="''
		+ (select sub_name from dbo.Subcarrier where sub_ID = (select sta_fk_sub from dbo.Stack where sta_ID = @stackID) ) +
		''" id="'' + LTRIM(RTRIM(CONVERT(nvarchar(20), (select sta_fk_sub from dbo.Stack where sta_ID = @stackID)))) + ''"/>''
		+
		''<Carrier name="''
		+ (select car_fullName from dbo.Carrier where car_ID = (select sub_fk_car from dbo.Subcarrier where sub_ID = (select sta_fk_sub from dbo.Stack where sta_ID = @stackID) )  ) +
		''" id="'' + LTRIM(RTRIM(CONVERT(nvarchar(20), (select sub_fk_car from dbo.Subcarrier where sub_ID = (select sta_fk_sub from dbo.Stack where sta_ID = @stackID) )))) + ''"/>''

END

' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnReferencedShipment]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'


CREATE FUNCTION [dbo].[fnReferencedShipment] (@nReferenceID INT)
RETURNS TABLE
AS
RETURN
(
	SELECT lin_fk_shi AS ID, par_value AS value FROM Line
	INNER JOIN Package ON pac_fk_lin = lin_ID
	INNER JOIN PackageRef ON par_fk_pac = pac_ID
	WHERE par_fk_rek = @nReferenceID
		UNION
	SELECT lin_fk_shi AS ID, lir_value AS value FROM Line
	INNER JOIN LineRef ON lir_fk_lin = lin_ID
	WHERE lir_fk_rek = @nReferenceID
		UNION
	SELECT ref_fk_shi AS ID, ref_value AS value FROM Reference
	WHERE ref_fk_rek = @nReferenceID
)


' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vLineReferences]'))
EXEC dbo.sp_executesql @statement = N'
 

create view [dbo].[vLineReferences]

as

select lin_ID as vlinref_lin_fk_ID, 

            (select lir_value 

             from LineRef 

                        join ReferenceKind on lir_fk_rek = rek_ID and rek_name like ''Marking'' and lir_fk_lin = lin_ID) as Marking,

            (select lir_value 

             from LineRef 

                        join ReferenceKind on lir_fk_rek = rek_ID and rek_name like ''Contents'' and lir_fk_lin = lin_ID) as Contents,

            (select lir_value 

             from LineRef 

                        join ReferenceKind on lir_fk_rek = rek_ID and rek_name like ''GoodsKind'' and lir_fk_lin = lin_ID) as GoodsKind,

            (select lir_value 

             from LineRef 

                        join ReferenceKind on lir_fk_rek = rek_ID and rek_name like ''Attention'' and lir_fk_lin = lin_ID) as Attention,

            (select lir_value 

             from LineRef 

                        join ReferenceKind on lir_fk_rek = rek_ID and rek_name like ''ReceiverReference'' and lir_fk_lin = lin_ID) as ReceiverReference,

            (select lir_value 

             from LineRef 

                        join ReferenceKind on lir_fk_rek = rek_ID and rek_name like ''OrderNumber'' and lir_fk_lin = lin_ID) as OrderNumber,

            (select lir_value 

             from LineRef 

                        join ReferenceKind on lir_fk_rek = rek_ID and rek_name like ''AdditionalOrderNumbers'' and lir_fk_lin = lin_ID) as AdditionalOrderNumbers

from Line

' 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spEnsureCountryInDB]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Hans Kristian Haug
-- Create date: 06.11.2006
-- Description:	Ensure that a currency exist in DB
-- =============================================
CREATE PROCEDURE [dbo].[spEnsureCountryInDB]
	@nCountryID INT,
	@sIdentifier VARCHAR (50),
	@sIsoA2 CHAR (2),
	@sIsoA3 CHAR (3),
	@nIsoN3 SMALLINT
AS
BEGIN
	IF EXISTS (SELECT * FROM Country WHERE cou_ID=@nCountryID)
	BEGIN
		UPDATE Country SET
			cou_identifier=@sIdentifier,
			cou_isoA2=@sIsoA2,
			cou_isoA3=@sIsoA3,
			cou_isoN3=@nIsoN3
		WHERE cou_ID=@nCountryID
	END
	ELSE
	BEGIN
		INSERT Country (cou_ID,cou_identifier,cou_isoA2,cou_isoA3,cou_isoN3)
			VALUES (@nCountryID,@sIdentifier,@sIsoA2,@sIsoA3,@nIsoN3)
	END
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spEnsureCurrencyInDB]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Hans Kristian Haug
-- Create date: 03.11.2006
-- Description:	Ensure that a currency exist in DB
-- =============================================
CREATE PROCEDURE [dbo].[spEnsureCurrencyInDB] 
	@nCurrencyID INT, 
	@sCode CHAR (3), 
	@sName VARCHAR (50)
AS
BEGIN
	IF EXISTS (SELECT * FROM Currency WHERE cur_ID=@nCurrencyID)
	BEGIN
		UPDATE Currency SET
			cur_code=@sCode,
			cur_name=@sName
		WHERE cur_ID=@nCurrencyID
	END
	ELSE
	BEGIN
		INSERT Currency (cur_ID,cur_code,cur_name)
			VALUES (@nCurrencyID,@sCode,@sName)
	END
END


' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vShipmentAmounts]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vShipmentAmounts] 
AS 
SELECT DISTINCT 
	dbo.Shipment.shi_ID AS number_fk_shi, 
	CAST ((SELECT amo_number FROM dbo.Amount AS Amount_1 WHERE (amo_fk_amk = 1) AND (amo_fk_shi = dbo.Shipment.shi_ID)) AS varchar) + '' '' + dbo.Currency.cur_code AS number_Price1, 
	CAST ((SELECT amo_number FROM dbo.Amount AS Amount_2 WHERE (amo_fk_amk = 2) AND (amo_fk_shi = dbo.Shipment.shi_ID)) AS varchar) + '' '' + dbo.Currency.cur_code AS number_Price2, 
	CAST ((SELECT amo_number FROM dbo.Amount AS Amount_3 WHERE (amo_fk_amk = 3) AND (amo_fk_shi = dbo.Shipment.shi_ID)) AS varchar) + '' '' + dbo.Currency.cur_code AS number_Price3, 
	CAST ((SELECT amo_number FROM dbo.Amount AS Amount_4 WHERE (amo_fk_amk = 4) AND (amo_fk_shi = dbo.Shipment.shi_ID)) AS varchar) + '' '' + dbo.Currency.cur_code AS number_InsuranceValue, 
	CAST ((SELECT amo_number FROM dbo.Amount AS Amount_5 WHERE (amo_fk_amk = 5) AND (amo_fk_shi = dbo.Shipment.shi_ID)) AS varchar) + '' '' + dbo.Currency.cur_code AS number_CODAmount, 
	CAST ((SELECT amo_number FROM dbo.Amount AS Amount_6 WHERE (amo_fk_amk = 6) AND (amo_fk_shi = dbo.Shipment.shi_ID)) AS varchar) + '' '' + dbo.Currency.cur_code AS number_CarrierSpecifiedPrice, 
	CAST ((SELECT amo_number FROM dbo.Amount AS Amount_7 WHERE (amo_fk_amk = 7) AND (amo_fk_shi = dbo.Shipment.shi_ID)) AS varchar) + '' '' + dbo.Currency.cur_code AS number_CarrierSpecifiedCODAmoun, 
	CAST ((SELECT amo_number FROM dbo.Amount AS Amount_8 WHERE (amo_fk_amk = 8) AND (amo_fk_shi = dbo.Shipment.shi_ID)) AS varchar) + '' '' + dbo.Currency.cur_code AS number_InsuranceAmountPerPackage, 
	CAST ((SELECT amo_number FROM dbo.Amount AS Amount_9 WHERE (amo_fk_amk = 9) AND (amo_fk_shi = dbo.Shipment.shi_ID)) AS varchar) + '' '' + dbo.Currency.cur_code AS number_FixedPrice FROM dbo.Shipment  
	     LEFT OUTER JOIN dbo.Amount ON Amount.amo_fk_shi = dbo.Shipment.shi_ID LEFT OUTER JOIN dbo.Currency ON dbo.Currency.cur_ID = Amount.amo_fk_cur 
' 
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane1' , N'SCHEMA',N'dbo', N'VIEW',N'vShipmentAmounts', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[27] 4[34] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = -217
         Left = 0
      End
      Begin Tables = 
         Begin Table = "Shipment"
            Begin Extent = 
               Top = 176
               Left = 148
               Bottom = 284
               Right = 353
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Amount_1"
            Begin Extent = 
               Top = 354
               Left = 539
               Bottom = 462
               Right = 690
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Currency"
            Begin Extent = 
               Top = 389
               Left = 836
               Bottom = 482
               Right = 987
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 10
         Width = 284
         Width = 2010
         Width = 1500
         Width = 1980
         Width = 1995
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vShipmentAmounts'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPaneCount' , N'SCHEMA',N'dbo', N'VIEW',N'vShipmentAmounts', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vShipmentAmounts'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spEnsureSubcarrierInDB]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Hans Kristian Haug
-- Create date: 31.10.2006
-- Description:	Ensure that a concept subcarrier exist in DB
-- =============================================
CREATE PROCEDURE [dbo].[spEnsureSubcarrierInDB] 
	@nConceptID INT, 
	@nCarrierID INT,
	@sName VARCHAR (50),
	@nMaxShipmentLines INT
AS
BEGIN
	IF EXISTS (SELECT * FROM Subcarrier WHERE sub_conceptID=@nConceptID)
	BEGIN
		UPDATE Subcarrier SET
			sub_fk_car=@nCarrierID,
			sub_name=@sName,
			sub_maxShipmentLines=@nMaxShipmentLines
		WHERE sub_conceptID=@nConceptID
	END
	ELSE
	BEGIN
		INSERT Subcarrier (sub_fk_car,sub_name,sub_maxShipmentLines,sub_conceptID)
			VALUES (@nCarrierID,@sName,@nMaxShipmentLines,@nConceptID)
	END
	SELECT sub_ID FROM Subcarrier WHERE sub_conceptID=@nConceptID
END
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnTraceViewLastStatus]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[fnTraceViewLastStatus]
(
	@shipmentID INT
)
RETURNS VARCHAR (50)
AS
BEGIN
	DECLARE @status VARCHAR (50)
	SET @status = 
	(
		SELECT TOP 1 stk_name
		FROM ShipmentStatus INNER JOIN StatusKind ON stk_ID=shs_fk_stk
		WHERE shs_fk_shi=@shipmentID AND (stk_ID=17 or stk_ID=21 or stk_ID=22)
		ORDER BY shs_date DESC
	)
	RETURN @status
END




' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spEnsureLanguageInDB]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Hans Kristian Haug
-- Create date: 03.11.2006
-- Description:	Ensure that a language exist in DB
-- =============================================
CREATE PROCEDURE [dbo].[spEnsureLanguageInDB] 
	@nLanguageID INT, 
	@sName VARCHAR (20)
AS
BEGIN
	IF EXISTS (SELECT * FROM [Language] WHERE lan_ID=@nLanguageID)
	BEGIN
		UPDATE [Language] SET
			lan_name=@sName
		WHERE lan_ID=@nLanguageID
	END
	ELSE
	BEGIN
		INSERT [Language] (lan_ID,lan_name)
			VALUES (@nLanguageID,@sName)
	END
END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnFilterDraft]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
-- =============================================
-- Author:			Bjørn E Pedersen
-- Create date:		05.09.2006
-- Changed by:		
-- Changed date:	
-- Description:	Return the ID of shipments to view in Drafts
-- =============================================
CREATE FUNCTION [dbo].[fnFilterDraft]
(
	@nActorID  INT
)
RETURNS TABLE
AS
RETURN 
(
    SELECT
        shi_ID AS shi_ID_Filter
    FROM Shipment
		LEFT JOIN Stack ON shi_fk_sta = sta_ID 
    WHERE
        (ISNULL (shi_trashed, 0) = 0)
	AND
		(shi_dateSubmitted IS NULL)
	AND
		(shi_dateImported IS NULL)
	AND
	  sta_fk_act = @nActorID    
)
' 
END

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnFilterTraceView]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Adrian Budileanu
-- Create date:		06.09.2006
-- Description:		Return the ID of shipments to be listed in TraceView filtered by ActorID
-- =============================================
CREATE FUNCTION [dbo].[fnFilterTraceView]
(
	@nActorID  INT
)
RETURNS TABLE
AS
RETURN 
(
   SELECT
        shi_ID AS shi_ID_Filter
    FROM Shipment
		LEFT JOIN Stack ON shi_fk_sta = sta_ID 
    WHERE
        sta_fk_act = @nActorID
    AND
	ISNULL(shi_number,'''')<>''''
)
' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vFilterStatusDraft]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vFilterStatusDraft]
AS
SELECT DISTINCT dbo.Shipment.shi_ID AS FilterStatusDraft_fk_shi
FROM         dbo.Shipment LEFT OUTER JOIN
                      dbo.ShipmentStatus ON dbo.Shipment.shi_ID = dbo.ShipmentStatus.shs_fk_shi
WHERE     (dbo.ShipmentStatus.shs_fk_stk = 19) AND (NOT EXISTS
                          (SELECT     shs_ID
                            FROM          dbo.ShipmentStatus AS ShipmentStatus_1
                            WHERE      (shs_fk_shi = dbo.Shipment.shi_ID) AND (shs_fk_stk = 16 OR
                                                   shs_fk_stk = 17 OR
                                                   shs_fk_stk = 4)))
' 
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane1' , N'SCHEMA',N'dbo', N'VIEW',N'vFilterStatusDraft', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "ShipmentStatus"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 114
               Right = 189
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Shipment"
            Begin Extent = 
               Top = 6
               Left = 227
               Bottom = 114
               Right = 432
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vFilterStatusDraft'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPaneCount' , N'SCHEMA',N'dbo', N'VIEW',N'vFilterStatusDraft', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vFilterStatusDraft'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vShipmentReferences]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vShipmentReferences]
AS
SELECT     shi_ID AS ref_fk_shi,
                          (SELECT     ref_value
                            FROM          dbo.Reference AS Reference_9
                            WHERE      (ref_fk_rek = 5) AND (ref_fk_shi = dbo.Shipment.shi_ID)) AS refProjectName,
                          (SELECT     ref_value
                            FROM          dbo.Reference AS Reference_8
                            WHERE      (ref_fk_rek = 6) AND (ref_fk_shi = dbo.Shipment.shi_ID)) AS refReceiverCustNoSender,
                          (SELECT     ref_value
                            FROM          dbo.Reference AS Reference_7
                            WHERE      (ref_fk_rek = 7) AND (ref_fk_shi = dbo.Shipment.shi_ID)) AS refReceiverReference,
                          (SELECT     ref_value
                            FROM          dbo.Reference AS Reference_6
                            WHERE      (ref_fk_rek = 11) AND (ref_fk_shi = dbo.Shipment.shi_ID)) AS refReceiverCustNoCarrier,
                          (SELECT     ref_value
                            FROM          dbo.Reference AS Reference_5
                            WHERE      (ref_fk_rek = 12) AND (ref_fk_shi = dbo.Shipment.shi_ID)) AS refSenderReference,
                          (SELECT     ref_value
                            FROM          dbo.Reference AS Reference_3
                            WHERE      (ref_fk_rek = 25) AND (ref_fk_shi = dbo.Shipment.shi_ID)) AS refGoodsKind,
                          (SELECT     ref_value
                            FROM          dbo.Reference AS Reference_2
                            WHERE      (ref_fk_rek = 27) AND (ref_fk_shi = dbo.Shipment.shi_ID)) AS refAttention,
                          (SELECT     ref_value
                            FROM          dbo.Reference AS Reference_1
                            WHERE      (ref_fk_rek = 29) AND (ref_fk_shi = dbo.Shipment.shi_ID)) AS refKID
FROM         dbo.Shipment
' 
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane1' , N'SCHEMA',N'dbo', N'VIEW',N'vShipmentReferences', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[21] 4[11] 2[52] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "Shipment"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 114
               Right = 243
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vShipmentReferences'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPaneCount' , N'SCHEMA',N'dbo', N'VIEW',N'vShipmentReferences', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vShipmentReferences'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spDeleteOrTrashStack]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Hans Kristian Haug
-- Create date: 06.11.2006
-- Description:	Delete a stack, or, if used, trash it
-- =============================================
CREATE PROCEDURE [dbo].[spDeleteOrTrashStack] 
	@nStackID INT = 0
AS
BEGIN
	IF EXISTS (SELECT * FROM Shipment WHERE shi_fk_sta=@nStackID)
	BEGIN
		UPDATE Stack
			SET sta_trashed=1
			WHERE sta_ID=@nStackID
		SELECT @nStackID AS ID
	END
	ELSE
	BEGIN
		DELETE Stack
			WHERE sta_ID=@nStackID
		SELECT 0 AS ID
	END
END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnFilterSentbox]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
-- =============================================
-- Author:			Hans Kristian Haug
-- Create date:		27.06.2006
-- Changed by:		Tellef Ormstad
-- Changed date:	03.07.2006
-- Description:	Return the ID of shipments to view in SentBox
-- =============================================
CREATE FUNCTION [dbo].[fnFilterSentbox]
(
    @dtFrom DATETIME,
    @dtTo DATETIME,
	@nActorID  INT
)
RETURNS TABLE
AS
RETURN 
(
    SELECT
        shi_ID AS shi_ID_Filter
    FROM Shipment
		LEFT JOIN Stack ON shi_fk_sta = sta_ID 
    WHERE
        ISNULL (shi_trashed, 0) = 0
	AND
    (
        shi_outbox=1
    OR
        shi_dateSubmitted BETWEEN @dtFrom AND @dtTo
    )
	AND
	  sta_fk_act = @nActorID    
)


' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnFilterInbox]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'-- =============================================
-- Author:			Hans Kristian Haug
-- Create date:		27.06.2006
-- Changed by:		Tellef Ormstad
-- Changed date:	03.07.2006
-- Description:	Return the ID of shipments to view in InBox
-- =============================================
CREATE FUNCTION [dbo].[fnFilterInbox]
(
    @sFromDate DATETIME,
    @sToDate DATETIME,
	@nActorID  INT
)
RETURNS TABLE
AS
RETURN 
(
   SELECT
        shi_ID AS shi_ID_Filter
    FROM Shipment
		LEFT JOIN Stack ON shi_fk_sta = sta_ID 
    WHERE
        ISNULL (shi_trashed, 0) = 0
    AND
        ISNULL (shi_outbox, 0) = 0
    AND
        shi_dateSubmitted IS NULL
	AND
	  sta_fk_act = @nActorID    
	AND
       shi_dateImported BETWEEN @sFromDate AND @sToDate
)






' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnFilterComebackShipment]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'


-- =============================================
-- Author:			Tellef Ormstad
-- Create date:		07.07.2006
-- Changed by:		
-- Changed date:	
-- Description:	Return the ID of comback shipments
-- =============================================
CREATE FUNCTION [dbo].[fnFilterComebackShipment]
(
    @dtFrom DATETIME,
    @dtTo DATETIME,
	@nActorID  INT
)
RETURNS TABLE
AS
RETURN 
(
    SELECT
        shi_ID AS shi_ID_Filter
    FROM Shipment
		LEFT JOIN Stack ON shi_fk_sta = sta_ID 
    WHERE
        ISNULL (shi_trashed, 0) = 0
    AND
	  shi_fk_shk = 2
	AND
    (
        shi_outbox=1
    OR
        shi_dateSubmitted BETWEEN @dtFrom AND @dtTo
    )
	AND
	  sta_fk_act = @nActorID  

)

' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnFilterTrashCan]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
-- =============================================
-- Author:			Bjørn E Pedersen
-- Create date:		01.09.2006
-- Changed by:		
-- Changed date:	
-- Description:	Return the ID of shipments to view in trash view
-- =============================================
CREATE FUNCTION [dbo].[fnFilterTrashCan]
(
	@nActorID  INT
)
RETURNS TABLE
AS
RETURN 
(
    SELECT
        shi_ID AS shi_ID_Filter
    FROM Shipment
		LEFT JOIN Stack ON shi_fk_sta = sta_ID
    WHERE
        shi_trashed = 1
	AND
	  sta_fk_act = @nActorID    
)









' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vFilterStatusMBOutBox]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vFilterStatusMBOutBox]
AS
SELECT DISTINCT dbo.Shipment.shi_ID AS FilterStatusMBOutBox_fk_shi
FROM         dbo.Shipment LEFT OUTER JOIN
                      dbo.ShipmentStatus ON dbo.Shipment.shi_ID = dbo.ShipmentStatus.shs_fk_shi
WHERE     (dbo.ShipmentStatus.shs_fk_stk = 14) AND (NOT EXISTS
                          (SELECT     shs_ID
                            FROM          dbo.ShipmentStatus AS ShipmentStatus_1
                            WHERE      (shs_fk_shi = dbo.Shipment.shi_ID) AND (shs_fk_stk = 15)))
' 
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane1' , N'SCHEMA',N'dbo', N'VIEW',N'vFilterStatusMBOutBox', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "Shipment"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 114
               Right = 243
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "ShipmentStatus"
            Begin Extent = 
               Top = 6
               Left = 281
               Bottom = 114
               Right = 432
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vFilterStatusMBOutBox'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPaneCount' , N'SCHEMA',N'dbo', N'VIEW',N'vFilterStatusMBOutBox', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vFilterStatusMBOutBox'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCleanupAfterShipment]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spCleanupAfterShipment] 
AS	
 BEGIN	
	SET NOCOUNT ON 
	DELETE BatchReports WHERE bar_fk_bat IN (SELECT bat_ID FROM Batch WHERE NOT EXISTS (SELECT * FROM Shipment WHERE shi_fk_bat=bat_ID)) 
	DELETE Batch WHERE NOT EXISTS (SELECT * FROM Shipment WHERE shi_fk_bat=bat_ID) 
	DELETE Message WHERE NOT EXISTS (SELECT * FROM ShipmentMessage WHERE shm_fk_mes=mes_ID) 
	DELETE SenderAddress WHERE NOT EXISTS (SELECT * FROM Shipment WHERE shi_fk_sea=sea_ID) END 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vShipmentHandled]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vShipmentHandled] 
AS 
SELECT 
	shi_ID, 
	shi_fk_sta, 
	CASE shi_dateSubmitted 
		WHEN NULL THEN CONVERT(DATETIME,CONVERT(CHAR(8),shi_dateImported,112),112) 
		ELSE CONVERT(DATETIME,CONVERT(CHAR(8),shi_dateSubmitted,112),112) 
	END AS dateHandled 
FROM Shipment 
' 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnFilterActor]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[fnFilterActor]
(
            @ActorID int
)
RETURNS TABLE
AS

RETURN
(
   SELECT act_ID FROM Actor
            WHERE
                        act_ID=@ActorID
            AND
                        ISNULL (act_deleted, 0) = 0
)

' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spEnsureProductInDB]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Hans Kristian Haug
-- Create date: 31.10.2006
-- Description:	Ensure that a concept product exist in DB
-- =============================================
CREATE PROCEDURE [dbo].[spEnsureProductInDB] 
	@nConceptID INT,
	@nSubcarrierID INT,
	@sName VARCHAR (50),
	@sCodeFile VARCHAR (50),
	@sCodeLabel VARCHAR (50),
	@bAvailableAsReturn BIT,
	@dtAvailableFrom DATETIME,
	@dtAvailableUntil DATETIME,
	@bAllowCOD BIT,
	@bAllowInsurance BIT
AS
BEGIN
	IF EXISTS (SELECT * FROM Product WHERE pro_conceptID=@nConceptID)
	BEGIN
		UPDATE Product SET
			pro_fk_sub=@nSubcarrierID,
			pro_name=@sName,
			pro_codeFile=@sCodeFile,
			pro_codeLabel=@sCodeLabel,
			pro_availableAsReturn=@bAvailableAsReturn,
			pro_availableFrom=@dtAvailableFrom,
			pro_availableUntil=@dtAvailableUntil,
			pro_allowCOD=@bAllowCOD,
			pro_allowInsurance=@bAllowInsurance
		WHERE pro_conceptID=@nConceptID
	END
	ELSE
	BEGIN
		INSERT Product (pro_fk_sub,pro_name,pro_codeFile,pro_codeLabel,pro_availableAsReturn,pro_availableFrom,pro_availableUntil,pro_allowCOD,pro_allowInsurance,pro_conceptID)
			VALUES (@nSubcarrierID,@sName,@sCodeFile,@sCodeLabel,@bAvailableAsReturn,@dtAvailableFrom,@dtAvailableUntil,@bAllowCOD,@bAllowInsurance,@nConceptID)
	END
END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spEnsureCarrierInDB]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Hans Kristian Haug
-- Create date: 31.10.2006
-- Description:	Ensure that a concept carrier exist in DB
-- =============================================
CREATE PROCEDURE [dbo].[spEnsureCarrierInDB] 
	@nConceptID INT, 
	@sFullName VARCHAR (50),
	@sShortName VARCHAR (50),
	@nCountryID INT
AS
BEGIN
	IF EXISTS (SELECT * FROM Carrier WHERE car_conceptID=@nConceptID)
	BEGIN
		UPDATE Carrier SET
			car_fullName=@sFullName,
			car_shortName=@sShortName,
			car_fk_cou=@nCountryID
		WHERE car_conceptID=@nConceptID
	END
	ELSE
	BEGIN
		INSERT Carrier (car_fk_cou,car_fullName,car_shortName,car_conceptID)
			VALUES (@nCountryID,@sFullName,@sShortName,@nConceptID)
	END
	SELECT car_ID FROM Carrier WHERE car_conceptID=@nConceptID
END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spEnsureCountryNameInDB]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Hans Kristian Haug
-- Create date: 06.11.2006
-- Description:	Ensure that a country name exist in DB
-- =============================================
CREATE PROCEDURE [dbo].[spEnsureCountryNameInDB]
	@nCountryID INT,
	@nLanguageID INT,
	@sName VARCHAR (50),
	@bSecondary BIT
AS
BEGIN
	IF EXISTS (SELECT * FROM CountryName WHERE con_fk_cou=@nCountryID AND con_fk_lan=@nLanguageID)
	BEGIN
		UPDATE CountryName SET
			con_name=@sName,
			con_secondary=@bSecondary
		WHERE con_fk_cou=@nCountryID AND con_fk_lan=@nLanguageID
	END
	ELSE
	BEGIN
		INSERT CountryName (con_fk_cou,con_fk_lan,con_name,con_secondary)
			VALUES (@nCountryID,@nLanguageID,@sName,@bSecondary)
	END
END' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spEnsureCurrencyNameInDB]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author:		Hans Kristian Haug
-- Create date: 06.11.2006
-- Description:	Ensure that a currency name exist in DB
-- =============================================
CREATE PROCEDURE [dbo].[spEnsureCurrencyNameInDB]
	@nCurrencyID INT,
	@nLanguageID INT,
	@sName VARCHAR (50)
AS
BEGIN
	IF EXISTS (SELECT * FROM CurrencyName WHERE cun_fk_cur=@nCurrencyID AND cun_fk_lan=@nLanguageID)
	BEGIN
		UPDATE CurrencyName SET
			cun_name=@sName
		WHERE cun_fk_cur=@nCurrencyID AND cun_fk_lan=@nLanguageID
	END
	ELSE
	BEGIN
		INSERT CurrencyName (cun_fk_cur,cun_fk_lan,cun_name)
			VALUES (@nCurrencyID,@nLanguageID,@sName)
	END
END
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vLastStatusDeleteShipment]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vLastStatusDeleteShipment] 
AS 
SELECT     shs_ID, shs_fk_shi, shs_fk_stk, shs_date, shs_user 
FROM         dbo.ShipmentStatus AS ss1 
WHERE     (shs_fk_stk = 10 OR 
                      shs_fk_stk = 11) AND (shs_date = 
                          (SELECT     MAX(shs_date) AS Expr1 
                            FROM          dbo.ShipmentStatus AS ss2 
                            WHERE      (shs_fk_shi = ss1.shs_fk_shi) AND (shs_fk_stk = ss1.shs_fk_stk))) 
' 
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnTraceViewLastStatusDate]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[fnTraceViewLastStatusDate]
(
	@shipmentID INT
)
RETURNS DATETIME
AS
BEGIN
	DECLARE @statusdate DATETIME
	SET @statusdate = 
	(
		SELECT TOP 1 shs_date
		FROM ShipmentStatus 
		WHERE shs_fk_shi=@shipmentID AND (shs_fk_stk=17 or shs_fk_stk=21 or shs_fk_stk=22)
		ORDER BY shs_date DESC
	)
	RETURN @statusdate
END


' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vFilterByStatusInbox]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vFilterByStatusInbox]
AS
SELECT DISTINCT shs_fk_shi AS filtered_fk_shi
FROM         dbo.ShipmentStatus
WHERE    shs_fk_stk NOT IN (4, 16, 17, 19)' 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vFilterByStatusSent]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vFilterByStatusSent]
AS
SELECT DISTINCT shs_fk_shi AS FilterStatusSent_fk_shi
FROM         dbo.ShipmentStatus
WHERE     (shs_fk_stk = 17)
' 
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane1' , N'SCHEMA',N'dbo', N'VIEW',N'vFilterByStatusSent', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "ShipmentStatus"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 114
               Right = 189
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vFilterByStatusSent'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPaneCount' , N'SCHEMA',N'dbo', N'VIEW',N'vFilterByStatusSent', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vFilterByStatusSent'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vFilterStatusMBSent]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vFilterStatusMBSent]
AS
SELECT DISTINCT shs_fk_shi AS FilterStatusMBSent_fk_shi
FROM         dbo.ShipmentStatus
WHERE     (shs_fk_stk = 15)
' 
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane1' , N'SCHEMA',N'dbo', N'VIEW',N'vFilterStatusMBSent', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "ShipmentStatus"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 114
               Right = 189
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vFilterStatusMBSent'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPaneCount' , N'SCHEMA',N'dbo', N'VIEW',N'vFilterStatusMBSent', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vFilterStatusMBSent'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCleanupForInstallation]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spCleanupForInstallation] 
	@dtBefore DATETIME 
AS 
BEGIN 
	SET NOCOUNT ON 
	BEGIN TRY 
		BEGIN TRANSACTION 
		DELETE PackageRef WHERE par_fk_pac IN (SELECT pac_ID FROM Package WHERE pac_fk_lin IN (SELECT lin_ID FROM Line WHERE lin_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore))) 
		DELETE PackageStatus WHERE pas_fk_pac IN (SELECT pac_ID FROM Package WHERE pac_fk_lin IN (SELECT lin_ID FROM Line WHERE lin_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore))) 
		DELETE LineHistory WHERE lhi_fk_lin IN (SELECT lin_ID FROM Line WHERE lin_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore)) 
		DELETE Package WHERE pac_fk_lin IN (SELECT lin_ID FROM Line WHERE lin_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore)) 
		DELETE RecycleItem WHERE ryi_fk_lin IN (SELECT lin_ID FROM Line WHERE lin_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore)) 
		DELETE LineRef WHERE lir_fk_lin IN (SELECT lin_ID FROM Line WHERE lin_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore)) 
		DELETE Address WHERE add_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore) 
		DELETE Amount WHERE amo_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore) 
		DELETE CarrierStatusMessages WHERE csm_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore) 
		DELETE ShipmentConnection WHERE shc_fk_shi_parent IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore) 
		DELETE DangerousGoods WHERE dng_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore) 
		DELETE ShipmentDocument WHERE shd_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore) 
		DELETE Email WHERE ema_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore) 
		DELETE Line WHERE lin_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore) 
		DELETE ShipmentLinkFileStorage WHERE slf_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore) 
		DELETE MassPrint WHERE mp_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore) 
		DELETE ShipmentMessage WHERE shm_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore) 
		DELETE Reference WHERE ref_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore) 
		DELETE ServiceValue WHERE sev_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore) 
		DELETE ShipmentStatus WHERE shs_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore) 
		DELETE Shipment WHERE Shipment.shi_ID IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore) 
		EXEC spCleanupAfterShipment 
		COMMIT TRANSACTION 
	END TRY 
	BEGIN CATCH 
		IF XACT_STATE() <> 0 
			ROLLBACK TRANSACTION 
	END CATCH 
END 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCleanupForLocation]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spCleanupForLocation] 
	@dtBefore DATETIME, 
	@nLocationID INT 
AS 
BEGIN 
	SET NOCOUNT ON 
	BEGIN TRY 
		BEGIN TRANSACTION 
		DELETE PackageRef WHERE par_fk_pac IN (SELECT pac_ID FROM Package WHERE pac_fk_lin IN (SELECT lin_ID FROM Line WHERE lin_fk_shi IN (SELECT shi_ID FROM vShipmentHandled  WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act IN (SELECT act_ID FROM Actor WHERE act_fk_loc=@nLocationID))))) 
		DELETE PackageStatus WHERE pas_fk_pac IN (SELECT pac_ID FROM Package WHERE pac_fk_lin IN (SELECT lin_ID FROM Line WHERE lin_fk_shi IN (SELECT shi_ID FROM vShipmentHandled  WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act IN (SELECT act_ID FROM Actor WHERE act_fk_loc=@nLocationID))))) 
		DELETE LineHistory WHERE lhi_fk_lin IN (SELECT lin_ID FROM Line WHERE lin_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack  WHERE sta_fk_act IN (SELECT act_ID FROM Actor WHERE act_fk_loc=@nLocationID)))) 
		DELETE Package WHERE pac_fk_lin IN (SELECT lin_ID FROM Line WHERE lin_fk_shi IN (SELECT shi_ID FROM vShipmentHandled  WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act IN (SELECT act_ID FROM Actor WHERE act_fk_loc=@nLocationID)))) 
		DELETE RecycleItem WHERE ryi_fk_lin IN (SELECT lin_ID FROM Line WHERE lin_fk_shi IN (SELECT shi_ID FROM vShipmentHandled  WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act IN (SELECT act_ID FROM Actor WHERE act_fk_loc=@nLocationID)))) 
		DELETE LineRef WHERE lir_fk_lin IN (SELECT lin_ID FROM Line WHERE lin_fk_shi IN (SELECT shi_ID FROM vShipmentHandled  WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act IN (SELECT act_ID FROM Actor WHERE act_fk_loc=@nLocationID)))) 
		DELETE Address WHERE add_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act IN (SELECT act_ID FROM Actor WHERE act_fk_loc=@nLocationID))) 
		DELETE Amount WHERE amo_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act IN (SELECT act_ID FROM Actor WHERE act_fk_loc=@nLocationID))) 
		DELETE CarrierStatusMessages WHERE csm_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act IN (SELECT act_ID FROM Actor WHERE act_fk_loc=@nLocationID))) 
		DELETE ShipmentConnection WHERE shc_fk_shi_parent IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act IN (SELECT act_ID FROM Actor WHERE act_fk_loc=@nLocationID))) 
		DELETE DangerousGoods WHERE dng_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act IN (SELECT act_ID FROM Actor WHERE act_fk_loc=@nLocationID))) 
		DELETE ShipmentDocument WHERE shd_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act IN (SELECT act_ID FROM Actor WHERE act_fk_loc=@nLocationID))) 
		DELETE Email WHERE ema_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act IN (SELECT act_ID FROM Actor WHERE act_fk_loc=@nLocationID))) 
		DELETE Line WHERE lin_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act IN (SELECT act_ID FROM Actor WHERE act_fk_loc=@nLocationID))) 
		DELETE ShipmentLinkFileStorage WHERE slf_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act IN (SELECT act_ID FROM Actor WHERE act_fk_loc=@nLocationID))) 
		DELETE MassPrint WHERE mp_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act IN (SELECT act_ID FROM Actor WHERE act_fk_loc=@nLocationID))) 
		DELETE ShipmentMessage WHERE shm_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act IN (SELECT act_ID FROM Actor WHERE act_fk_loc=@nLocationID))) 
		DELETE Reference WHERE ref_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act IN (SELECT act_ID FROM Actor WHERE act_fk_loc=@nLocationID))) 
		DELETE ServiceValue WHERE sev_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act IN (SELECT act_ID FROM Actor WHERE act_fk_loc=@nLocationID))) 
		DELETE ShipmentStatus WHERE shs_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act IN (SELECT act_ID FROM Actor WHERE act_fk_loc=@nLocationID))) 
		DELETE Shipment WHERE Shipment.shi_ID IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act IN (SELECT act_ID FROM Actor WHERE act_fk_loc=@nLocationID))) 
		EXEC spCleanupAfterShipment 
		COMMIT TRANSACTION 
	END TRY 
	BEGIN CATCH 
		IF XACT_STATE() <> 0 
			ROLLBACK TRANSACTION 
	END CATCH 
END 
' 
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spCleanupForActor]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[spCleanupForActor] 
	@dtBefore DATETIME,  
	@nActorID INT 
AS 
BEGIN 
	SET NOCOUNT ON 
	BEGIN TRY 
		BEGIN TRANSACTION 
		DELETE PackageRef WHERE par_fk_pac IN (SELECT pac_ID FROM Package WHERE pac_fk_lin IN (SELECT lin_ID FROM Line  WHERE lin_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act=@nActorID)))) 
		DELETE PackageStatus WHERE pas_fk_pac IN (SELECT pac_ID FROM Package WHERE pac_fk_lin IN (SELECT lin_ID FROM Line  WHERE lin_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act=@nActorID)))) 
		DELETE LineHistory WHERE lhi_fk_lin IN (SELECT lin_ID FROM Line WHERE lin_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act=@nActorID))) 
		DELETE Package WHERE pac_fk_lin IN (SELECT lin_ID FROM Line WHERE lin_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act=@nActorID))) 
		DELETE RecycleItem WHERE ryi_fk_lin IN (SELECT lin_ID FROM Line WHERE lin_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act=@nActorID))) 
		DELETE LineRef WHERE lir_fk_lin IN (SELECT lin_ID FROM Line WHERE lin_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act=@nActorID))) 
		DELETE Address WHERE add_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act=@nActorID)) 
		DELETE Amount WHERE amo_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act=@nActorID)) 
		DELETE CarrierStatusMessages WHERE csm_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act=@nActorID)) 
		DELETE ShipmentConnection WHERE shc_fk_shi_parent IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act=@nActorID)) 
		DELETE DangerousGoods WHERE dng_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act=@nActorID)) 
		DELETE ShipmentDocument WHERE shd_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act=@nActorID)) 
		DELETE Email WHERE ema_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act=@nActorID)) 
		DELETE Line WHERE lin_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act=@nActorID)) 
		DELETE ShipmentLinkFileStorage WHERE slf_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act=@nActorID)) 
		DELETE MassPrint WHERE mp_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act=@nActorID)) 
		DELETE ShipmentMessage WHERE shm_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act=@nActorID)) 
		DELETE Reference WHERE ref_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act=@nActorID)) 
		DELETE ServiceValue WHERE sev_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act=@nActorID)) 
		DELETE ShipmentStatus WHERE shs_fk_shi IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act=@nActorID)) 
		DELETE Shipment WHERE Shipment.shi_ID IN (SELECT shi_ID FROM vShipmentHandled WHERE dateHandled<@dtBefore AND shi_fk_sta IN (SELECT sta_ID FROM Stack WHERE sta_fk_act=@nActorID)) 
		EXEC spCleanupAfterShipment 
		COMMIT TRANSACTION 
	END TRY 
	BEGIN CATCH 
		IF XACT_STATE() <> 0 
			ROLLBACK TRANSACTION 
	END CATCH 
END 
' 
END
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnTraceViewStatusCarrier]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[fnTraceViewStatusCarrier]
(
)
RETURNS TABLE
AS
RETURN
(
	SELECT shi_ID as csm_fk_shi, dbo.fnTraceViewLastStatusCarrier(shi_ID) as CarrierStatus , dbo.fnTraceViewLastStatusLocationCarrier(shi_ID) as Location
	FROM Shipment 
)





' 
END

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnTraceViewStatus]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'

CREATE FUNCTION [dbo].[fnTraceViewStatus]
(
)
RETURNS TABLE
AS
RETURN
(
	SELECT shi_ID as shs_fk_shi, dbo.fnTraceViewLastStatus(shi_ID) as Status , dbo.fnTraceViewLastStatusDate(shi_ID) as StatusDate
	FROM Shipment 
)






























' 
END

GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_DocumentsFilesLink_ConfigFiles]') AND parent_object_id = OBJECT_ID(N'[dbo].[DocumentsFilesLink]'))
ALTER TABLE [dbo].[DocumentsFilesLink]  WITH CHECK ADD  CONSTRAINT [FK_DocumentsFilesLink_ConfigFiles] FOREIGN KEY([dof_fk_cof])
REFERENCES [dbo].[ConfigFiles] ([cof_ID])
GO
ALTER TABLE [dbo].[DocumentsFilesLink] CHECK CONSTRAINT [FK_DocumentsFilesLink_ConfigFiles]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_DocumentsFilesLink_Documents]') AND parent_object_id = OBJECT_ID(N'[dbo].[DocumentsFilesLink]'))
ALTER TABLE [dbo].[DocumentsFilesLink]  WITH CHECK ADD  CONSTRAINT [FK_DocumentsFilesLink_Documents] FOREIGN KEY([dof_fk_doc])
REFERENCES [dbo].[Documents] ([doc_ID])
GO
ALTER TABLE [dbo].[DocumentsFilesLink] CHECK CONSTRAINT [FK_DocumentsFilesLink_Documents]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CarrierStatusMessages_CarrierMessageType]') AND parent_object_id = OBJECT_ID(N'[dbo].[CarrierStatusMessages]'))
ALTER TABLE [dbo].[CarrierStatusMessages]  WITH CHECK ADD  CONSTRAINT [FK_CarrierStatusMessages_CarrierMessageType] FOREIGN KEY([csm_fk_cmt])
REFERENCES [dbo].[CarrierMessageType] ([cmt_ID])
GO
ALTER TABLE [dbo].[CarrierStatusMessages] CHECK CONSTRAINT [FK_CarrierStatusMessages_CarrierMessageType]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CarrierStatusMessages_FileHeader]') AND parent_object_id = OBJECT_ID(N'[dbo].[CarrierStatusMessages]'))
ALTER TABLE [dbo].[CarrierStatusMessages]  WITH CHECK ADD  CONSTRAINT [FK_CarrierStatusMessages_FileHeader] FOREIGN KEY([csm_fk_fih])
REFERENCES [dbo].[FileHeader] ([fih_ID])
GO
ALTER TABLE [dbo].[CarrierStatusMessages] CHECK CONSTRAINT [FK_CarrierStatusMessages_FileHeader]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CarrierStatusMessages_Shipment]') AND parent_object_id = OBJECT_ID(N'[dbo].[CarrierStatusMessages]'))
ALTER TABLE [dbo].[CarrierStatusMessages]  WITH NOCHECK ADD  CONSTRAINT [FK_CarrierStatusMessages_Shipment] FOREIGN KEY([csm_fk_shi])
REFERENCES [dbo].[Shipment] ([shi_ID])
GO
ALTER TABLE [dbo].[CarrierStatusMessages] CHECK CONSTRAINT [FK_CarrierStatusMessages_Shipment]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CarrierStatusTags_CarrierStatusMessages]') AND parent_object_id = OBJECT_ID(N'[dbo].[CarrierStatusTags]'))
ALTER TABLE [dbo].[CarrierStatusTags]  WITH CHECK ADD  CONSTRAINT [FK_CarrierStatusTags_CarrierStatusMessages] FOREIGN KEY([cst_fk_csm])
REFERENCES [dbo].[CarrierStatusMessages] ([csm_ID])
GO
ALTER TABLE [dbo].[CarrierStatusTags] CHECK CONSTRAINT [FK_CarrierStatusTags_CarrierStatusMessages]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CarrierStatusTags_CarrierTagType]') AND parent_object_id = OBJECT_ID(N'[dbo].[CarrierStatusTags]'))
ALTER TABLE [dbo].[CarrierStatusTags]  WITH CHECK ADD  CONSTRAINT [FK_CarrierStatusTags_CarrierTagType] FOREIGN KEY([cst_fk_ctt])
REFERENCES [dbo].[CarrierTagType] ([ctt_ID])
GO
ALTER TABLE [dbo].[CarrierStatusTags] CHECK CONSTRAINT [FK_CarrierStatusTags_CarrierTagType]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ConsignorStatusTags_CarrierStatusMessages]') AND parent_object_id = OBJECT_ID(N'[dbo].[ConsignorStatusTags]'))
ALTER TABLE [dbo].[ConsignorStatusTags]  WITH CHECK ADD  CONSTRAINT [FK_ConsignorStatusTags_CarrierStatusMessages] FOREIGN KEY([cos_fk_csm])
REFERENCES [dbo].[CarrierStatusMessages] ([csm_ID])
GO
ALTER TABLE [dbo].[ConsignorStatusTags] CHECK CONSTRAINT [FK_ConsignorStatusTags_CarrierStatusMessages]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ConsignorStatusTags_ConsignorTagType]') AND parent_object_id = OBJECT_ID(N'[dbo].[ConsignorStatusTags]'))
ALTER TABLE [dbo].[ConsignorStatusTags]  WITH CHECK ADD  CONSTRAINT [FK_ConsignorStatusTags_ConsignorTagType] FOREIGN KEY([cos_fk_cot])
REFERENCES [dbo].[ConsignorTagType] ([cot_ID])
GO
ALTER TABLE [dbo].[ConsignorStatusTags] CHECK CONSTRAINT [FK_ConsignorStatusTags_ConsignorTagType]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_FileStorage_FileType]') AND parent_object_id = OBJECT_ID(N'[dbo].[FileStorage]'))
ALTER TABLE [dbo].[FileStorage]  WITH CHECK ADD  CONSTRAINT [FK_FileStorage_FileType] FOREIGN KEY([fis_fk_fit])
REFERENCES [dbo].[FileType] ([fit_ID])
GO
ALTER TABLE [dbo].[FileStorage] CHECK CONSTRAINT [FK_FileStorage_FileType]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CustomerList_CustomerListType]') AND parent_object_id = OBJECT_ID(N'[dbo].[CustomerList]'))
ALTER TABLE [dbo].[CustomerList]  WITH CHECK ADD  CONSTRAINT [FK_CustomerList_CustomerListType] FOREIGN KEY([cul_fk_clt])
REFERENCES [dbo].[CustomerListType] ([clt_id])
GO
ALTER TABLE [dbo].[CustomerList] CHECK CONSTRAINT [FK_CustomerList_CustomerListType]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CustomerList_Location]') AND parent_object_id = OBJECT_ID(N'[dbo].[CustomerList]'))
ALTER TABLE [dbo].[CustomerList]  WITH CHECK ADD  CONSTRAINT [FK_CustomerList_Location] FOREIGN KEY([cul_fk_loc])
REFERENCES [dbo].[Location] ([loc_ID])
GO
ALTER TABLE [dbo].[CustomerList] CHECK CONSTRAINT [FK_CustomerList_Location]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Actor_Location]') AND parent_object_id = OBJECT_ID(N'[dbo].[Actor]'))
ALTER TABLE [dbo].[Actor]  WITH NOCHECK ADD  CONSTRAINT [FK_Actor_Location] FOREIGN KEY([act_fk_loc])
REFERENCES [dbo].[Location] ([loc_ID])
GO
ALTER TABLE [dbo].[Actor] CHECK CONSTRAINT [FK_Actor_Location]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ConfigTree_Actor]') AND parent_object_id = OBJECT_ID(N'[dbo].[ConfigTree]'))
ALTER TABLE [dbo].[ConfigTree]  WITH CHECK ADD  CONSTRAINT [FK_ConfigTree_Actor] FOREIGN KEY([ctr_fk_act])
REFERENCES [dbo].[Actor] ([act_ID])
GO
ALTER TABLE [dbo].[ConfigTree] CHECK CONSTRAINT [FK_ConfigTree_Actor]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ConfigTree_Carrier]') AND parent_object_id = OBJECT_ID(N'[dbo].[ConfigTree]'))
ALTER TABLE [dbo].[ConfigTree]  WITH CHECK ADD  CONSTRAINT [FK_ConfigTree_Carrier] FOREIGN KEY([ctr_fk_car])
REFERENCES [dbo].[Carrier] ([car_ID])
GO
ALTER TABLE [dbo].[ConfigTree] CHECK CONSTRAINT [FK_ConfigTree_Carrier]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ConfigTree_Location]') AND parent_object_id = OBJECT_ID(N'[dbo].[ConfigTree]'))
ALTER TABLE [dbo].[ConfigTree]  WITH CHECK ADD  CONSTRAINT [FK_ConfigTree_Location] FOREIGN KEY([ctr_fk_loc])
REFERENCES [dbo].[Location] ([loc_ID])
GO
ALTER TABLE [dbo].[ConfigTree] CHECK CONSTRAINT [FK_ConfigTree_Location]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ConfigTree_Product]') AND parent_object_id = OBJECT_ID(N'[dbo].[ConfigTree]'))
ALTER TABLE [dbo].[ConfigTree]  WITH CHECK ADD  CONSTRAINT [FK_ConfigTree_Product] FOREIGN KEY([ctr_fk_pro])
REFERENCES [dbo].[Product] ([pro_ID])
GO
ALTER TABLE [dbo].[ConfigTree] CHECK CONSTRAINT [FK_ConfigTree_Product]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ConfigTree_Subcarrier]') AND parent_object_id = OBJECT_ID(N'[dbo].[ConfigTree]'))
ALTER TABLE [dbo].[ConfigTree]  WITH CHECK ADD  CONSTRAINT [FK_ConfigTree_Subcarrier] FOREIGN KEY([ctr_fk_sub])
REFERENCES [dbo].[Subcarrier] ([sub_ID])
GO
ALTER TABLE [dbo].[ConfigTree] CHECK CONSTRAINT [FK_ConfigTree_Subcarrier]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_UserRigth_Location]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserRigth]'))
ALTER TABLE [dbo].[UserRigth]  WITH NOCHECK ADD  CONSTRAINT [FK_UserRigth_Location] FOREIGN KEY([urt_fk_loc])
REFERENCES [dbo].[Location] ([loc_ID])
GO
ALTER TABLE [dbo].[UserRigth] CHECK CONSTRAINT [FK_UserRigth_Location]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_UserRigth_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[UserRigth]'))
ALTER TABLE [dbo].[UserRigth]  WITH CHECK ADD  CONSTRAINT [FK_UserRigth_Users] FOREIGN KEY([urt_fk_usr])
REFERENCES [dbo].[Users] ([usr_ID])
GO
ALTER TABLE [dbo].[UserRigth] CHECK CONSTRAINT [FK_UserRigth_Users]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CustomerSubcarrier_Crossdock]') AND parent_object_id = OBJECT_ID(N'[dbo].[CustomerSubcarrier]'))
ALTER TABLE [dbo].[CustomerSubcarrier]  WITH CHECK ADD  CONSTRAINT [FK_CustomerSubcarrier_Crossdock] FOREIGN KEY([csu_fk_cro])
REFERENCES [dbo].[Crossdock] ([cro_ID])
GO
ALTER TABLE [dbo].[CustomerSubcarrier] CHECK CONSTRAINT [FK_CustomerSubcarrier_Crossdock]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CustomerSubcarrier_Customer]') AND parent_object_id = OBJECT_ID(N'[dbo].[CustomerSubcarrier]'))
ALTER TABLE [dbo].[CustomerSubcarrier]  WITH CHECK ADD  CONSTRAINT [FK_CustomerSubcarrier_Customer] FOREIGN KEY([csu_fk_cus])
REFERENCES [dbo].[Customer] ([cus_ID])
GO
ALTER TABLE [dbo].[CustomerSubcarrier] CHECK CONSTRAINT [FK_CustomerSubcarrier_Customer]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CustomerSubcarrier_Subcarrier]') AND parent_object_id = OBJECT_ID(N'[dbo].[CustomerSubcarrier]'))
ALTER TABLE [dbo].[CustomerSubcarrier]  WITH NOCHECK ADD  CONSTRAINT [FK_CustomerSubcarrier_Subcarrier] FOREIGN KEY([csu_fk_sub])
REFERENCES [dbo].[Subcarrier] ([sub_ID])
GO
ALTER TABLE [dbo].[CustomerSubcarrier] CHECK CONSTRAINT [FK_CustomerSubcarrier_Subcarrier]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Customer_Carrier]') AND parent_object_id = OBJECT_ID(N'[dbo].[Customer]'))
ALTER TABLE [dbo].[Customer]  WITH NOCHECK ADD  CONSTRAINT [FK_Customer_Carrier] FOREIGN KEY([cus_fk_car])
REFERENCES [dbo].[Carrier] ([car_ID])
GO
ALTER TABLE [dbo].[Customer] NOCHECK CONSTRAINT [FK_Customer_Carrier]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Customer_Category]') AND parent_object_id = OBJECT_ID(N'[dbo].[Customer]'))
ALTER TABLE [dbo].[Customer]  WITH CHECK ADD  CONSTRAINT [FK_Customer_Category] FOREIGN KEY([cus_fk_cat])
REFERENCES [dbo].[Category] ([cat_ID])
GO
ALTER TABLE [dbo].[Customer] CHECK CONSTRAINT [FK_Customer_Category]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Customer_CustomerList]') AND parent_object_id = OBJECT_ID(N'[dbo].[Customer]'))
ALTER TABLE [dbo].[Customer]  WITH CHECK ADD  CONSTRAINT [FK_Customer_CustomerList] FOREIGN KEY([cus_fk_cul])
REFERENCES [dbo].[CustomerList] ([cul_id])
GO
ALTER TABLE [dbo].[Customer] CHECK CONSTRAINT [FK_Customer_CustomerList]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Customer_Product]') AND parent_object_id = OBJECT_ID(N'[dbo].[Customer]'))
ALTER TABLE [dbo].[Customer]  WITH NOCHECK ADD  CONSTRAINT [FK_Customer_Product] FOREIGN KEY([cus_fk_pro])
REFERENCES [dbo].[Product] ([pro_ID])
GO
ALTER TABLE [dbo].[Customer] NOCHECK CONSTRAINT [FK_Customer_Product]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Customer_Subcarrier]') AND parent_object_id = OBJECT_ID(N'[dbo].[Customer]'))
ALTER TABLE [dbo].[Customer]  WITH NOCHECK ADD  CONSTRAINT [FK_Customer_Subcarrier] FOREIGN KEY([cus_fk_sub])
REFERENCES [dbo].[Subcarrier] ([sub_ID])
GO
ALTER TABLE [dbo].[Customer] NOCHECK CONSTRAINT [FK_Customer_Subcarrier]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Shipment_Batch]') AND parent_object_id = OBJECT_ID(N'[dbo].[Shipment]'))
ALTER TABLE [dbo].[Shipment]  WITH NOCHECK ADD  CONSTRAINT [FK_Shipment_Batch] FOREIGN KEY([shi_fk_bat])
REFERENCES [dbo].[Batch] ([bat_ID])
GO
ALTER TABLE [dbo].[Shipment] CHECK CONSTRAINT [FK_Shipment_Batch]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Shipment_CarriagePayer]') AND parent_object_id = OBJECT_ID(N'[dbo].[Shipment]'))
ALTER TABLE [dbo].[Shipment]  WITH NOCHECK ADD  CONSTRAINT [FK_Shipment_CarriagePayer] FOREIGN KEY([shi_fk_cap])
REFERENCES [dbo].[CarriagePayer] ([cap_ID])
GO
ALTER TABLE [dbo].[Shipment] CHECK CONSTRAINT [FK_Shipment_CarriagePayer]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Shipment_Product]') AND parent_object_id = OBJECT_ID(N'[dbo].[Shipment]'))
ALTER TABLE [dbo].[Shipment]  WITH NOCHECK ADD  CONSTRAINT [FK_Shipment_Product] FOREIGN KEY([shi_fk_pro])
REFERENCES [dbo].[Product] ([pro_ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Shipment] CHECK CONSTRAINT [FK_Shipment_Product]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Shipment_Product1]') AND parent_object_id = OBJECT_ID(N'[dbo].[Shipment]'))
ALTER TABLE [dbo].[Shipment]  WITH NOCHECK ADD  CONSTRAINT [FK_Shipment_Product1] FOREIGN KEY([shi_carrier_pro])
REFERENCES [dbo].[Product] ([pro_ID])
GO
ALTER TABLE [dbo].[Shipment] CHECK CONSTRAINT [FK_Shipment_Product1]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Shipment_SenderAddress]') AND parent_object_id = OBJECT_ID(N'[dbo].[Shipment]'))
ALTER TABLE [dbo].[Shipment]  WITH CHECK ADD  CONSTRAINT [FK_Shipment_SenderAddress] FOREIGN KEY([shi_fk_sea])
REFERENCES [dbo].[SenderAddress] ([sea_ID])
GO
ALTER TABLE [dbo].[Shipment] CHECK CONSTRAINT [FK_Shipment_SenderAddress]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Shipment_ShipmentKind]') AND parent_object_id = OBJECT_ID(N'[dbo].[Shipment]'))
ALTER TABLE [dbo].[Shipment]  WITH NOCHECK ADD  CONSTRAINT [FK_Shipment_ShipmentKind] FOREIGN KEY([shi_fk_shk])
REFERENCES [dbo].[ShipmentKind] ([shk_ID])
GO
ALTER TABLE [dbo].[Shipment] CHECK CONSTRAINT [FK_Shipment_ShipmentKind]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Shipment_Stack]') AND parent_object_id = OBJECT_ID(N'[dbo].[Shipment]'))
ALTER TABLE [dbo].[Shipment]  WITH NOCHECK ADD  CONSTRAINT [FK_Shipment_Stack] FOREIGN KEY([shi_fk_sta])
REFERENCES [dbo].[Stack] ([sta_ID])
GO
ALTER TABLE [dbo].[Shipment] CHECK CONSTRAINT [FK_Shipment_Stack]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Shipment_Temperature]') AND parent_object_id = OBJECT_ID(N'[dbo].[Shipment]'))
ALTER TABLE [dbo].[Shipment]  WITH NOCHECK ADD  CONSTRAINT [FK_Shipment_Temperature] FOREIGN KEY([shi_fk_tem])
REFERENCES [dbo].[Temperature] ([tem_ID])
GO
ALTER TABLE [dbo].[Shipment] CHECK CONSTRAINT [FK_Shipment_Temperature]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_DOCSTATUS_DOCUMENT]') AND parent_object_id = OBJECT_ID(N'[dbo].[ShipmentDocumentStatus]'))
ALTER TABLE [dbo].[ShipmentDocumentStatus]  WITH CHECK ADD  CONSTRAINT [FK_DOCSTATUS_DOCUMENT] FOREIGN KEY([sds_fk_shd])
REFERENCES [dbo].[ShipmentDocument] ([shd_ID])
GO
ALTER TABLE [dbo].[ShipmentDocumentStatus] CHECK CONSTRAINT [FK_DOCSTATUS_DOCUMENT]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_DOCSTATUS_STATUSKIND]') AND parent_object_id = OBJECT_ID(N'[dbo].[ShipmentDocumentStatus]'))
ALTER TABLE [dbo].[ShipmentDocumentStatus]  WITH CHECK ADD  CONSTRAINT [FK_DOCSTATUS_STATUSKIND] FOREIGN KEY([sds_fk_stk])
REFERENCES [dbo].[StatusKind] ([stk_ID])
GO
ALTER TABLE [dbo].[ShipmentDocumentStatus] CHECK CONSTRAINT [FK_DOCSTATUS_STATUSKIND]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Address_AddressKind]') AND parent_object_id = OBJECT_ID(N'[dbo].[Address]'))
ALTER TABLE [dbo].[Address]  WITH NOCHECK ADD  CONSTRAINT [FK_Address_AddressKind] FOREIGN KEY([add_fk_adk])
REFERENCES [dbo].[AddressKind] ([adk_ID])
GO
ALTER TABLE [dbo].[Address] CHECK CONSTRAINT [FK_Address_AddressKind]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Address_Country]') AND parent_object_id = OBJECT_ID(N'[dbo].[Address]'))
ALTER TABLE [dbo].[Address]  WITH CHECK ADD  CONSTRAINT [FK_Address_Country] FOREIGN KEY([add_fk_cou])
REFERENCES [dbo].[Country] ([cou_ID])
GO
ALTER TABLE [dbo].[Address] CHECK CONSTRAINT [FK_Address_Country]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Address_Customer]') AND parent_object_id = OBJECT_ID(N'[dbo].[Address]'))
ALTER TABLE [dbo].[Address]  WITH NOCHECK ADD  CONSTRAINT [FK_Address_Customer] FOREIGN KEY([add_fk_cus])
REFERENCES [dbo].[Customer] ([cus_ID])
GO
ALTER TABLE [dbo].[Address] CHECK CONSTRAINT [FK_Address_Customer]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Address_Customer1]') AND parent_object_id = OBJECT_ID(N'[dbo].[Address]'))
ALTER TABLE [dbo].[Address]  WITH NOCHECK ADD  CONSTRAINT [FK_Address_Customer1] FOREIGN KEY([add_fk_cuo])
REFERENCES [dbo].[Customer] ([cus_ID])
GO
ALTER TABLE [dbo].[Address] NOCHECK CONSTRAINT [FK_Address_Customer1]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Address_Shipment]') AND parent_object_id = OBJECT_ID(N'[dbo].[Address]'))
ALTER TABLE [dbo].[Address]  WITH NOCHECK ADD  CONSTRAINT [FK_Address_Shipment] FOREIGN KEY([add_fk_shi])
REFERENCES [dbo].[Shipment] ([shi_ID])
GO
ALTER TABLE [dbo].[Address] CHECK CONSTRAINT [FK_Address_Shipment]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_LineRef_Line]') AND parent_object_id = OBJECT_ID(N'[dbo].[LineRef]'))
ALTER TABLE [dbo].[LineRef]  WITH CHECK ADD  CONSTRAINT [FK_LineRef_Line] FOREIGN KEY([lir_fk_lin])
REFERENCES [dbo].[Line] ([lin_ID])
GO
ALTER TABLE [dbo].[LineRef] CHECK CONSTRAINT [FK_LineRef_Line]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_LineRef_ReferenceKind]') AND parent_object_id = OBJECT_ID(N'[dbo].[LineRef]'))
ALTER TABLE [dbo].[LineRef]  WITH CHECK ADD  CONSTRAINT [FK_LineRef_ReferenceKind] FOREIGN KEY([lir_fk_rek])
REFERENCES [dbo].[ReferenceKind] ([rek_ID])
GO
ALTER TABLE [dbo].[LineRef] CHECK CONSTRAINT [FK_LineRef_ReferenceKind]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_RecycleItem_Line]') AND parent_object_id = OBJECT_ID(N'[dbo].[RecycleItem]'))
ALTER TABLE [dbo].[RecycleItem]  WITH NOCHECK ADD  CONSTRAINT [FK_RecycleItem_Line] FOREIGN KEY([ryi_fk_lin])
REFERENCES [dbo].[Line] ([lin_ID])
GO
ALTER TABLE [dbo].[RecycleItem] CHECK CONSTRAINT [FK_RecycleItem_Line]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_RecycleItem_RecycleKind]') AND parent_object_id = OBJECT_ID(N'[dbo].[RecycleItem]'))
ALTER TABLE [dbo].[RecycleItem]  WITH NOCHECK ADD  CONSTRAINT [FK_RecycleItem_RecycleKind] FOREIGN KEY([ryi_fk_ryk])
REFERENCES [dbo].[RecycleKind] ([ryk_ID])
GO
ALTER TABLE [dbo].[RecycleItem] CHECK CONSTRAINT [FK_RecycleItem_RecycleKind]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Package_Line]') AND parent_object_id = OBJECT_ID(N'[dbo].[Package]'))
ALTER TABLE [dbo].[Package]  WITH CHECK ADD  CONSTRAINT [FK_Package_Line] FOREIGN KEY([pac_fk_lin])
REFERENCES [dbo].[Line] ([lin_ID])
GO
ALTER TABLE [dbo].[Package] CHECK CONSTRAINT [FK_Package_Line]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_LineHistory_HistoryItemKind]') AND parent_object_id = OBJECT_ID(N'[dbo].[LineHistory]'))
ALTER TABLE [dbo].[LineHistory]  WITH CHECK ADD  CONSTRAINT [FK_LineHistory_HistoryItemKind] FOREIGN KEY([lhi_fk_hik])
REFERENCES [dbo].[HistoryItemKind] ([hik_id])
GO
ALTER TABLE [dbo].[LineHistory] CHECK CONSTRAINT [FK_LineHistory_HistoryItemKind]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_LineHistory_Line]') AND parent_object_id = OBJECT_ID(N'[dbo].[LineHistory]'))
ALTER TABLE [dbo].[LineHistory]  WITH CHECK ADD  CONSTRAINT [FK_LineHistory_Line] FOREIGN KEY([lhi_fk_lin])
REFERENCES [dbo].[Line] ([lin_ID])
GO
ALTER TABLE [dbo].[LineHistory] CHECK CONSTRAINT [FK_LineHistory_Line]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_SenderAddress_Country]') AND parent_object_id = OBJECT_ID(N'[dbo].[SenderAddress]'))
ALTER TABLE [dbo].[SenderAddress]  WITH CHECK ADD  CONSTRAINT [FK_SenderAddress_Country] FOREIGN KEY([sea_fk_cou])
REFERENCES [dbo].[Country] ([cou_ID])
GO
ALTER TABLE [dbo].[SenderAddress] CHECK CONSTRAINT [FK_SenderAddress_Country]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Carrier_Country]') AND parent_object_id = OBJECT_ID(N'[dbo].[Carrier]'))
ALTER TABLE [dbo].[Carrier]  WITH NOCHECK ADD  CONSTRAINT [FK_Carrier_Country] FOREIGN KEY([car_fk_cou])
REFERENCES [dbo].[Country] ([cou_ID])
GO
ALTER TABLE [dbo].[Carrier] CHECK CONSTRAINT [FK_Carrier_Country]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CountryName_Country]') AND parent_object_id = OBJECT_ID(N'[dbo].[CountryName]'))
ALTER TABLE [dbo].[CountryName]  WITH NOCHECK ADD  CONSTRAINT [FK_CountryName_Country] FOREIGN KEY([con_fk_cou])
REFERENCES [dbo].[Country] ([cou_ID])
GO
ALTER TABLE [dbo].[CountryName] CHECK CONSTRAINT [FK_CountryName_Country]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CountryName_Language]') AND parent_object_id = OBJECT_ID(N'[dbo].[CountryName]'))
ALTER TABLE [dbo].[CountryName]  WITH NOCHECK ADD  CONSTRAINT [FK_CountryName_Language] FOREIGN KEY([con_fk_lan])
REFERENCES [dbo].[Language] ([lan_ID])
GO
ALTER TABLE [dbo].[CountryName] CHECK CONSTRAINT [FK_CountryName_Language]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Amount_AmountKind]') AND parent_object_id = OBJECT_ID(N'[dbo].[Amount]'))
ALTER TABLE [dbo].[Amount]  WITH NOCHECK ADD  CONSTRAINT [FK_Amount_AmountKind] FOREIGN KEY([amo_fk_amk])
REFERENCES [dbo].[AmountKind] ([amk_ID])
GO
ALTER TABLE [dbo].[Amount] CHECK CONSTRAINT [FK_Amount_AmountKind]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Amount_Currency]') AND parent_object_id = OBJECT_ID(N'[dbo].[Amount]'))
ALTER TABLE [dbo].[Amount]  WITH NOCHECK ADD  CONSTRAINT [FK_Amount_Currency] FOREIGN KEY([amo_fk_cur])
REFERENCES [dbo].[Currency] ([cur_ID])
GO
ALTER TABLE [dbo].[Amount] CHECK CONSTRAINT [FK_Amount_Currency]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Amount_Shipment]') AND parent_object_id = OBJECT_ID(N'[dbo].[Amount]'))
ALTER TABLE [dbo].[Amount]  WITH NOCHECK ADD  CONSTRAINT [FK_Amount_Shipment] FOREIGN KEY([amo_fk_shi])
REFERENCES [dbo].[Shipment] ([shi_ID])
GO
ALTER TABLE [dbo].[Amount] CHECK CONSTRAINT [FK_Amount_Shipment]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CurrencyName_Currency]') AND parent_object_id = OBJECT_ID(N'[dbo].[CurrencyName]'))
ALTER TABLE [dbo].[CurrencyName]  WITH NOCHECK ADD  CONSTRAINT [FK_CurrencyName_Currency] FOREIGN KEY([cun_fk_cur])
REFERENCES [dbo].[Currency] ([cur_ID])
GO
ALTER TABLE [dbo].[CurrencyName] CHECK CONSTRAINT [FK_CurrencyName_Currency]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CurrencyName_Language]') AND parent_object_id = OBJECT_ID(N'[dbo].[CurrencyName]'))
ALTER TABLE [dbo].[CurrencyName]  WITH NOCHECK ADD  CONSTRAINT [FK_CurrencyName_Language] FOREIGN KEY([cun_fk_lan])
REFERENCES [dbo].[Language] ([lan_ID])
GO
ALTER TABLE [dbo].[CurrencyName] CHECK CONSTRAINT [FK_CurrencyName_Language]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PackageRef_Package]') AND parent_object_id = OBJECT_ID(N'[dbo].[PackageRef]'))
ALTER TABLE [dbo].[PackageRef]  WITH CHECK ADD  CONSTRAINT [FK_PackageRef_Package] FOREIGN KEY([par_fk_pac])
REFERENCES [dbo].[Package] ([pac_ID])
GO
ALTER TABLE [dbo].[PackageRef] CHECK CONSTRAINT [FK_PackageRef_Package]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PackageRef_ReferenceKind]') AND parent_object_id = OBJECT_ID(N'[dbo].[PackageRef]'))
ALTER TABLE [dbo].[PackageRef]  WITH CHECK ADD  CONSTRAINT [FK_PackageRef_ReferenceKind] FOREIGN KEY([par_fk_rek])
REFERENCES [dbo].[ReferenceKind] ([rek_ID])
GO
ALTER TABLE [dbo].[PackageRef] CHECK CONSTRAINT [FK_PackageRef_ReferenceKind]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PackageStatus_Package]') AND parent_object_id = OBJECT_ID(N'[dbo].[PackageStatus]'))
ALTER TABLE [dbo].[PackageStatus]  WITH NOCHECK ADD  CONSTRAINT [FK_PackageStatus_Package] FOREIGN KEY([pas_fk_pac])
REFERENCES [dbo].[Package] ([pac_ID])
GO
ALTER TABLE [dbo].[PackageStatus] CHECK CONSTRAINT [FK_PackageStatus_Package]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_PackageStatus_StatusKind]') AND parent_object_id = OBJECT_ID(N'[dbo].[PackageStatus]'))
ALTER TABLE [dbo].[PackageStatus]  WITH NOCHECK ADD  CONSTRAINT [FK_PackageStatus_StatusKind] FOREIGN KEY([pas_fk_stk])
REFERENCES [dbo].[StatusKind] ([stk_ID])
GO
ALTER TABLE [dbo].[PackageStatus] CHECK CONSTRAINT [FK_PackageStatus_StatusKind]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ShipmentConnection_Shipment]') AND parent_object_id = OBJECT_ID(N'[dbo].[ShipmentConnection]'))
ALTER TABLE [dbo].[ShipmentConnection]  WITH NOCHECK ADD  CONSTRAINT [FK_ShipmentConnection_Shipment] FOREIGN KEY([shc_fk_shi_parent])
REFERENCES [dbo].[Shipment] ([shi_ID])
GO
ALTER TABLE [dbo].[ShipmentConnection] CHECK CONSTRAINT [FK_ShipmentConnection_Shipment]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ShipmentConnection_Shipment1]') AND parent_object_id = OBJECT_ID(N'[dbo].[ShipmentConnection]'))
ALTER TABLE [dbo].[ShipmentConnection]  WITH NOCHECK ADD  CONSTRAINT [FK_ShipmentConnection_Shipment1] FOREIGN KEY([shc_fk_shi_child])
REFERENCES [dbo].[Shipment] ([shi_ID])
GO
ALTER TABLE [dbo].[ShipmentConnection] CHECK CONSTRAINT [FK_ShipmentConnection_Shipment1]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ShipmentConnection_ShipmentConnectionKind]') AND parent_object_id = OBJECT_ID(N'[dbo].[ShipmentConnection]'))
ALTER TABLE [dbo].[ShipmentConnection]  WITH CHECK ADD  CONSTRAINT [FK_ShipmentConnection_ShipmentConnectionKind] FOREIGN KEY([shc_fk_sck])
REFERENCES [dbo].[ShipmentConnectionKind] ([sck_ID])
GO
ALTER TABLE [dbo].[ShipmentConnection] CHECK CONSTRAINT [FK_ShipmentConnection_ShipmentConnectionKind]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Product_Subcarrier]') AND parent_object_id = OBJECT_ID(N'[dbo].[Product]'))
ALTER TABLE [dbo].[Product]  WITH NOCHECK ADD  CONSTRAINT [FK_Product_Subcarrier] FOREIGN KEY([pro_fk_sub])
REFERENCES [dbo].[Subcarrier] ([sub_ID])
GO
ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Subcarrier]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_FileHeader_FileStorage]') AND parent_object_id = OBJECT_ID(N'[dbo].[FileHeader]'))
ALTER TABLE [dbo].[FileHeader]  WITH CHECK ADD  CONSTRAINT [FK_FileHeader_FileStorage] FOREIGN KEY([fih_fk_fis])
REFERENCES [dbo].[FileStorage] ([fis_ID])
GO
ALTER TABLE [dbo].[FileHeader] CHECK CONSTRAINT [FK_FileHeader_FileStorage]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_FileHeader_Subcarrier]') AND parent_object_id = OBJECT_ID(N'[dbo].[FileHeader]'))
ALTER TABLE [dbo].[FileHeader]  WITH NOCHECK ADD  CONSTRAINT [FK_FileHeader_Subcarrier] FOREIGN KEY([fih_fk_sub])
REFERENCES [dbo].[Subcarrier] ([sub_ID])
GO
ALTER TABLE [dbo].[FileHeader] CHECK CONSTRAINT [FK_FileHeader_Subcarrier]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CustomerCarrierReferences_Customer]') AND parent_object_id = OBJECT_ID(N'[dbo].[CustomerCarrierReferences]'))
ALTER TABLE [dbo].[CustomerCarrierReferences]  WITH CHECK ADD  CONSTRAINT [FK_CustomerCarrierReferences_Customer] FOREIGN KEY([ccr_fk_cus])
REFERENCES [dbo].[Customer] ([cus_ID])
GO
ALTER TABLE [dbo].[CustomerCarrierReferences] CHECK CONSTRAINT [FK_CustomerCarrierReferences_Customer]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CustomerCarrierReferences_CustomerCarrierReferenceKind]') AND parent_object_id = OBJECT_ID(N'[dbo].[CustomerCarrierReferences]'))
ALTER TABLE [dbo].[CustomerCarrierReferences]  WITH CHECK ADD  CONSTRAINT [FK_CustomerCarrierReferences_CustomerCarrierReferenceKind] FOREIGN KEY([ccr_fk_crk])
REFERENCES [dbo].[CustomerCarrierReferenceKind] ([crk_ID])
GO
ALTER TABLE [dbo].[CustomerCarrierReferences] CHECK CONSTRAINT [FK_CustomerCarrierReferences_CustomerCarrierReferenceKind]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CustomerCarrierReferences_Subcarrier]') AND parent_object_id = OBJECT_ID(N'[dbo].[CustomerCarrierReferences]'))
ALTER TABLE [dbo].[CustomerCarrierReferences]  WITH CHECK ADD  CONSTRAINT [FK_CustomerCarrierReferences_Subcarrier] FOREIGN KEY([ccr_fk_suc])
REFERENCES [dbo].[Subcarrier] ([sub_ID])
GO
ALTER TABLE [dbo].[CustomerCarrierReferences] CHECK CONSTRAINT [FK_CustomerCarrierReferences_Subcarrier]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Batch_Subcarrier]') AND parent_object_id = OBJECT_ID(N'[dbo].[Batch]'))
ALTER TABLE [dbo].[Batch]  WITH NOCHECK ADD  CONSTRAINT [FK_Batch_Subcarrier] FOREIGN KEY([bat_fk_sub])
REFERENCES [dbo].[Subcarrier] ([sub_ID])
GO
ALTER TABLE [dbo].[Batch] CHECK CONSTRAINT [FK_Batch_Subcarrier]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Stack_Actor]') AND parent_object_id = OBJECT_ID(N'[dbo].[Stack]'))
ALTER TABLE [dbo].[Stack]  WITH NOCHECK ADD  CONSTRAINT [FK_Stack_Actor] FOREIGN KEY([sta_fk_act])
REFERENCES [dbo].[Actor] ([act_ID])
GO
ALTER TABLE [dbo].[Stack] CHECK CONSTRAINT [FK_Stack_Actor]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Stack_Subcarrier]') AND parent_object_id = OBJECT_ID(N'[dbo].[Stack]'))
ALTER TABLE [dbo].[Stack]  WITH NOCHECK ADD  CONSTRAINT [FK_Stack_Subcarrier] FOREIGN KEY([sta_fk_sub])
REFERENCES [dbo].[Subcarrier] ([sub_ID])
GO
ALTER TABLE [dbo].[Stack] CHECK CONSTRAINT [FK_Stack_Subcarrier]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ShipmentMessage_Message]') AND parent_object_id = OBJECT_ID(N'[dbo].[ShipmentMessage]'))
ALTER TABLE [dbo].[ShipmentMessage]  WITH NOCHECK ADD  CONSTRAINT [FK_ShipmentMessage_Message] FOREIGN KEY([shm_fk_mes])
REFERENCES [dbo].[Message] ([mes_ID])
GO
ALTER TABLE [dbo].[ShipmentMessage] CHECK CONSTRAINT [FK_ShipmentMessage_Message]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ShipmentMessage_MessageKind]') AND parent_object_id = OBJECT_ID(N'[dbo].[ShipmentMessage]'))
ALTER TABLE [dbo].[ShipmentMessage]  WITH NOCHECK ADD  CONSTRAINT [FK_ShipmentMessage_MessageKind] FOREIGN KEY([shm_fk_mek])
REFERENCES [dbo].[MessageKind] ([mek_ID])
GO
ALTER TABLE [dbo].[ShipmentMessage] CHECK CONSTRAINT [FK_ShipmentMessage_MessageKind]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ShipmentMessage_Shipment]') AND parent_object_id = OBJECT_ID(N'[dbo].[ShipmentMessage]'))
ALTER TABLE [dbo].[ShipmentMessage]  WITH NOCHECK ADD  CONSTRAINT [FK_ShipmentMessage_Shipment] FOREIGN KEY([shm_fk_shi])
REFERENCES [dbo].[Shipment] ([shi_ID])
GO
ALTER TABLE [dbo].[ShipmentMessage] CHECK CONSTRAINT [FK_ShipmentMessage_Shipment]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Reference_ReferenceKind]') AND parent_object_id = OBJECT_ID(N'[dbo].[Reference]'))
ALTER TABLE [dbo].[Reference]  WITH NOCHECK ADD  CONSTRAINT [FK_Reference_ReferenceKind] FOREIGN KEY([ref_fk_rek])
REFERENCES [dbo].[ReferenceKind] ([rek_ID])
GO
ALTER TABLE [dbo].[Reference] CHECK CONSTRAINT [FK_Reference_ReferenceKind]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Reference_Shipment]') AND parent_object_id = OBJECT_ID(N'[dbo].[Reference]'))
ALTER TABLE [dbo].[Reference]  WITH NOCHECK ADD  CONSTRAINT [FK_Reference_Shipment] FOREIGN KEY([ref_fk_shi])
REFERENCES [dbo].[Shipment] ([shi_ID])
GO
ALTER TABLE [dbo].[Reference] CHECK CONSTRAINT [FK_Reference_Shipment]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ShipmentStatus_Shipment]') AND parent_object_id = OBJECT_ID(N'[dbo].[ShipmentStatus]'))
ALTER TABLE [dbo].[ShipmentStatus]  WITH NOCHECK ADD  CONSTRAINT [FK_ShipmentStatus_Shipment] FOREIGN KEY([shs_fk_shi])
REFERENCES [dbo].[Shipment] ([shi_ID])
GO
ALTER TABLE [dbo].[ShipmentStatus] CHECK CONSTRAINT [FK_ShipmentStatus_Shipment]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ShipmentStatus_StatusKind]') AND parent_object_id = OBJECT_ID(N'[dbo].[ShipmentStatus]'))
ALTER TABLE [dbo].[ShipmentStatus]  WITH NOCHECK ADD  CONSTRAINT [FK_ShipmentStatus_StatusKind] FOREIGN KEY([shs_fk_stk])
REFERENCES [dbo].[StatusKind] ([stk_ID])
GO
ALTER TABLE [dbo].[ShipmentStatus] CHECK CONSTRAINT [FK_ShipmentStatus_StatusKind]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_LanguageName_Language_in]') AND parent_object_id = OBJECT_ID(N'[dbo].[LanguageName]'))
ALTER TABLE [dbo].[LanguageName]  WITH CHECK ADD  CONSTRAINT [FK_LanguageName_Language_in] FOREIGN KEY([lgn_fk_lan_in])
REFERENCES [dbo].[Language] ([lan_ID])
GO
ALTER TABLE [dbo].[LanguageName] CHECK CONSTRAINT [FK_LanguageName_Language_in]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_LanguageName_Language_of]') AND parent_object_id = OBJECT_ID(N'[dbo].[LanguageName]'))
ALTER TABLE [dbo].[LanguageName]  WITH CHECK ADD  CONSTRAINT [FK_LanguageName_Language_of] FOREIGN KEY([lgn_fk_lan_of])
REFERENCES [dbo].[Language] ([lan_ID])
GO
ALTER TABLE [dbo].[LanguageName] CHECK CONSTRAINT [FK_LanguageName_Language_of]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_MASSPRINT_CBSHIPMENT]') AND parent_object_id = OBJECT_ID(N'[dbo].[MassPrint]'))
ALTER TABLE [dbo].[MassPrint]  WITH CHECK ADD  CONSTRAINT [FK_MASSPRINT_CBSHIPMENT] FOREIGN KEY([mp_fk_cbshi])
REFERENCES [dbo].[Shipment] ([shi_ID])
GO
ALTER TABLE [dbo].[MassPrint] CHECK CONSTRAINT [FK_MASSPRINT_CBSHIPMENT]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_MP_MASSPRINTBOX]') AND parent_object_id = OBJECT_ID(N'[dbo].[MassPrint]'))
ALTER TABLE [dbo].[MassPrint]  WITH CHECK ADD  CONSTRAINT [FK_MP_MASSPRINTBOX] FOREIGN KEY([mp_fk_mpb])
REFERENCES [dbo].[MassPrintBox] ([mpb_ID])
GO
ALTER TABLE [dbo].[MassPrint] CHECK CONSTRAINT [FK_MP_MASSPRINTBOX]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_MP_SHIPMENT]') AND parent_object_id = OBJECT_ID(N'[dbo].[MassPrint]'))
ALTER TABLE [dbo].[MassPrint]  WITH CHECK ADD  CONSTRAINT [FK_MP_SHIPMENT] FOREIGN KEY([mp_fk_shi])
REFERENCES [dbo].[Shipment] ([shi_ID])
GO
ALTER TABLE [dbo].[MassPrint] CHECK CONSTRAINT [FK_MP_SHIPMENT]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_JobLog_ErrorLog]') AND parent_object_id = OBJECT_ID(N'[dbo].[JobLog]'))
ALTER TABLE [dbo].[JobLog]  WITH NOCHECK ADD  CONSTRAINT [FK_JobLog_ErrorLog] FOREIGN KEY([jlg_fk_err])
REFERENCES [dbo].[ErrorLog] ([err_ID])
GO
ALTER TABLE [dbo].[JobLog] NOCHECK CONSTRAINT [FK_JobLog_ErrorLog]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_JobLog_Job]') AND parent_object_id = OBJECT_ID(N'[dbo].[JobLog]'))
ALTER TABLE [dbo].[JobLog]  WITH CHECK ADD  CONSTRAINT [FK_JobLog_Job] FOREIGN KEY([jlg_fk_job])
REFERENCES [dbo].[Job] ([job_ID])
GO
ALTER TABLE [dbo].[JobLog] CHECK CONSTRAINT [FK_JobLog_Job]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ShipmentLinkFileStorage_FileStorage]') AND parent_object_id = OBJECT_ID(N'[dbo].[ShipmentLinkFileStorage]'))
ALTER TABLE [dbo].[ShipmentLinkFileStorage]  WITH CHECK ADD  CONSTRAINT [FK_ShipmentLinkFileStorage_FileStorage] FOREIGN KEY([slf_fk_fis])
REFERENCES [dbo].[FileStorage] ([fis_ID])
GO
ALTER TABLE [dbo].[ShipmentLinkFileStorage] CHECK CONSTRAINT [FK_ShipmentLinkFileStorage_FileStorage]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ShipmentLinkFileStorage_Shipment]') AND parent_object_id = OBJECT_ID(N'[dbo].[ShipmentLinkFileStorage]'))
ALTER TABLE [dbo].[ShipmentLinkFileStorage]  WITH NOCHECK ADD  CONSTRAINT [FK_ShipmentLinkFileStorage_Shipment] FOREIGN KEY([slf_fk_shi])
REFERENCES [dbo].[Shipment] ([shi_ID])
GO
ALTER TABLE [dbo].[ShipmentLinkFileStorage] CHECK CONSTRAINT [FK_ShipmentLinkFileStorage_Shipment]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Line_Shipment]') AND parent_object_id = OBJECT_ID(N'[dbo].[Line]'))
ALTER TABLE [dbo].[Line]  WITH NOCHECK ADD  CONSTRAINT [FK_Line_Shipment] FOREIGN KEY([lin_fk_shi])
REFERENCES [dbo].[Shipment] ([shi_ID])
GO
ALTER TABLE [dbo].[Line] CHECK CONSTRAINT [FK_Line_Shipment]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ServiceValue_Shipment]') AND parent_object_id = OBJECT_ID(N'[dbo].[ServiceValue]'))
ALTER TABLE [dbo].[ServiceValue]  WITH NOCHECK ADD  CONSTRAINT [FK_ServiceValue_Shipment] FOREIGN KEY([sev_fk_shi])
REFERENCES [dbo].[Shipment] ([shi_ID])
GO
ALTER TABLE [dbo].[ServiceValue] CHECK CONSTRAINT [FK_ServiceValue_Shipment]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ShipmentDocument_Shipment]') AND parent_object_id = OBJECT_ID(N'[dbo].[ShipmentDocument]'))
ALTER TABLE [dbo].[ShipmentDocument]  WITH NOCHECK ADD  CONSTRAINT [FK_ShipmentDocument_Shipment] FOREIGN KEY([shd_fk_shi])
REFERENCES [dbo].[Shipment] ([shi_ID])
GO
ALTER TABLE [dbo].[ShipmentDocument] CHECK CONSTRAINT [FK_ShipmentDocument_Shipment]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_EMail_Shipment]') AND parent_object_id = OBJECT_ID(N'[dbo].[EMail]'))
ALTER TABLE [dbo].[EMail]  WITH NOCHECK ADD  CONSTRAINT [FK_EMail_Shipment] FOREIGN KEY([ema_fk_shi])
REFERENCES [dbo].[Shipment] ([shi_ID])
GO
ALTER TABLE [dbo].[EMail] CHECK CONSTRAINT [FK_EMail_Shipment]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_DangerousGoods_Shipment]') AND parent_object_id = OBJECT_ID(N'[dbo].[DangerousGoods]'))
ALTER TABLE [dbo].[DangerousGoods]  WITH CHECK ADD  CONSTRAINT [FK_DangerousGoods_Shipment] FOREIGN KEY([dng_fk_shi])
REFERENCES [dbo].[Shipment] ([shi_ID])
GO
ALTER TABLE [dbo].[DangerousGoods] CHECK CONSTRAINT [FK_DangerousGoods_Shipment]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[bar_fk_bat]') AND parent_object_id = OBJECT_ID(N'[dbo].[BatchReports]'))
ALTER TABLE [dbo].[BatchReports]  WITH NOCHECK ADD  CONSTRAINT [bar_fk_bat] FOREIGN KEY([bar_fk_bat])
REFERENCES [dbo].[Batch] ([bat_ID])
GO
ALTER TABLE [dbo].[BatchReports] CHECK CONSTRAINT [bar_fk_bat]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[bar_fk_ref]') AND parent_object_id = OBJECT_ID(N'[dbo].[BatchReports]'))
ALTER TABLE [dbo].[BatchReports]  WITH NOCHECK ADD  CONSTRAINT [bar_fk_ref] FOREIGN KEY([bar_fk_ref])
REFERENCES [dbo].[ReportFormat] ([ref_ID])
GO
ALTER TABLE [dbo].[BatchReports] CHECK CONSTRAINT [bar_fk_ref]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_JobValue_Job]') AND parent_object_id = OBJECT_ID(N'[dbo].[JobValue]'))
ALTER TABLE [dbo].[JobValue]  WITH CHECK ADD  CONSTRAINT [FK_JobValue_Job] FOREIGN KEY([jov_fk_job])
REFERENCES [dbo].[Job] ([job_ID])
GO
ALTER TABLE [dbo].[JobValue] CHECK CONSTRAINT [FK_JobValue_Job]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ScheduleTime_Job]') AND parent_object_id = OBJECT_ID(N'[dbo].[ScheduleTime]'))
ALTER TABLE [dbo].[ScheduleTime]  WITH CHECK ADD  CONSTRAINT [FK_ScheduleTime_Job] FOREIGN KEY([sch_fk_job])
REFERENCES [dbo].[Job] ([job_ID])
GO
ALTER TABLE [dbo].[ScheduleTime] CHECK CONSTRAINT [FK_ScheduleTime_Job]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ActorLinkCustomerList_Actor]') AND parent_object_id = OBJECT_ID(N'[dbo].[ActorLinkCustomerList]'))
ALTER TABLE [dbo].[ActorLinkCustomerList]  WITH NOCHECK ADD  CONSTRAINT [FK_ActorLinkCustomerList_Actor] FOREIGN KEY([alc_fk_act])
REFERENCES [dbo].[Actor] ([act_ID])
GO
ALTER TABLE [dbo].[ActorLinkCustomerList] CHECK CONSTRAINT [FK_ActorLinkCustomerList_Actor]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ActorLinkCustomerList_CustomerList]') AND parent_object_id = OBJECT_ID(N'[dbo].[ActorLinkCustomerList]'))
ALTER TABLE [dbo].[ActorLinkCustomerList]  WITH CHECK ADD  CONSTRAINT [FK_ActorLinkCustomerList_CustomerList] FOREIGN KEY([alc_fk_cul])
REFERENCES [dbo].[CustomerList] ([cul_id])
GO
ALTER TABLE [dbo].[ActorLinkCustomerList] CHECK CONSTRAINT [FK_ActorLinkCustomerList_CustomerList]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ConfigTreeFilesLink_ConfigFiles]') AND parent_object_id = OBJECT_ID(N'[dbo].[ConfigTreeFilesLink]'))
ALTER TABLE [dbo].[ConfigTreeFilesLink]  WITH CHECK ADD  CONSTRAINT [FK_ConfigTreeFilesLink_ConfigFiles] FOREIGN KEY([ctf_fk_cof])
REFERENCES [dbo].[ConfigFiles] ([cof_ID])
GO
ALTER TABLE [dbo].[ConfigTreeFilesLink] CHECK CONSTRAINT [FK_ConfigTreeFilesLink_ConfigFiles]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ConfigTreeFilesLink_ConfigTree]') AND parent_object_id = OBJECT_ID(N'[dbo].[ConfigTreeFilesLink]'))
ALTER TABLE [dbo].[ConfigTreeFilesLink]  WITH CHECK ADD  CONSTRAINT [FK_ConfigTreeFilesLink_ConfigTree] FOREIGN KEY([ctf_fk_ctr])
REFERENCES [dbo].[ConfigTree] ([ctr_ID])
GO
ALTER TABLE [dbo].[ConfigTreeFilesLink] CHECK CONSTRAINT [FK_ConfigTreeFilesLink_ConfigTree]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ConfigSection_ConfigFiles]') AND parent_object_id = OBJECT_ID(N'[dbo].[ConfigSection]'))
ALTER TABLE [dbo].[ConfigSection]  WITH CHECK ADD  CONSTRAINT [FK_ConfigSection_ConfigFiles] FOREIGN KEY([cse_fk_cof])
REFERENCES [dbo].[ConfigFiles] ([cof_ID])
GO
ALTER TABLE [dbo].[ConfigSection] CHECK CONSTRAINT [FK_ConfigSection_ConfigFiles]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CarrierTreeFilesLink_CarrierTree]') AND parent_object_id = OBJECT_ID(N'[dbo].[CarrierTreeFilesLink]'))
ALTER TABLE [dbo].[CarrierTreeFilesLink]  WITH CHECK ADD  CONSTRAINT [FK_CarrierTreeFilesLink_CarrierTree] FOREIGN KEY([caf_fk_crt])
REFERENCES [dbo].[CarrierTree] ([crt_ID])
GO
ALTER TABLE [dbo].[CarrierTreeFilesLink] CHECK CONSTRAINT [FK_CarrierTreeFilesLink_CarrierTree]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CarrierTreeFilesLink_ConfigFiles]') AND parent_object_id = OBJECT_ID(N'[dbo].[CarrierTreeFilesLink]'))
ALTER TABLE [dbo].[CarrierTreeFilesLink]  WITH CHECK ADD  CONSTRAINT [FK_CarrierTreeFilesLink_ConfigFiles] FOREIGN KEY([caf_fk_cof])
REFERENCES [dbo].[ConfigFiles] ([cof_ID])
GO
ALTER TABLE [dbo].[CarrierTreeFilesLink] CHECK CONSTRAINT [FK_CarrierTreeFilesLink_ConfigFiles]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ProfilesFilesLink_ConfigFiles]') AND parent_object_id = OBJECT_ID(N'[dbo].[ProfilesFilesLink]'))
ALTER TABLE [dbo].[ProfilesFilesLink]  WITH CHECK ADD  CONSTRAINT [FK_ProfilesFilesLink_ConfigFiles] FOREIGN KEY([prf_fk_cof])
REFERENCES [dbo].[ConfigFiles] ([cof_ID])
GO
ALTER TABLE [dbo].[ProfilesFilesLink] CHECK CONSTRAINT [FK_ProfilesFilesLink_ConfigFiles]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ProfilesFilesLink_Profiles]') AND parent_object_id = OBJECT_ID(N'[dbo].[ProfilesFilesLink]'))
ALTER TABLE [dbo].[ProfilesFilesLink]  WITH CHECK ADD  CONSTRAINT [FK_ProfilesFilesLink_Profiles] FOREIGN KEY([prf_fk_pri])
REFERENCES [dbo].[Profiles] ([pri_ID])
GO
ALTER TABLE [dbo].[ProfilesFilesLink] CHECK CONSTRAINT [FK_ProfilesFilesLink_Profiles]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_FactorySettings_ConfigFiles]') AND parent_object_id = OBJECT_ID(N'[dbo].[FactorySettings]'))
ALTER TABLE [dbo].[FactorySettings]  WITH CHECK ADD  CONSTRAINT [FK_FactorySettings_ConfigFiles] FOREIGN KEY([fas_fk_cof])
REFERENCES [dbo].[ConfigFiles] ([cof_ID])
GO
ALTER TABLE [dbo].[FactorySettings] CHECK CONSTRAINT [FK_FactorySettings_ConfigFiles]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Subcarrier_Carrier]') AND parent_object_id = OBJECT_ID(N'[dbo].[Subcarrier]'))
ALTER TABLE [dbo].[Subcarrier]  WITH NOCHECK ADD  CONSTRAINT [FK_Subcarrier_Carrier] FOREIGN KEY([sub_fk_car])
REFERENCES [dbo].[Carrier] ([car_ID])
GO
ALTER TABLE [dbo].[Subcarrier] CHECK CONSTRAINT [FK_Subcarrier_Carrier]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ConfigValue_ConfigValue]') AND parent_object_id = OBJECT_ID(N'[dbo].[ConfigValue]'))
ALTER TABLE [dbo].[ConfigValue]  WITH CHECK ADD  CONSTRAINT [FK_ConfigValue_ConfigValue] FOREIGN KEY([cov_fk_cse])
REFERENCES [dbo].[ConfigSection] ([cse_ID])
GO
ALTER TABLE [dbo].[ConfigValue] CHECK CONSTRAINT [FK_ConfigValue_ConfigValue]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Constraints_CustomerList]') AND parent_object_id = OBJECT_ID(N'[dbo].[Constraints]'))
ALTER TABLE [dbo].[Constraints]  WITH CHECK ADD  CONSTRAINT [FK_Constraints_CustomerList] FOREIGN KEY([cns_Default_fk_cul])
REFERENCES [dbo].[CustomerList] ([cul_id])
GO
ALTER TABLE [dbo].[Constraints] CHECK CONSTRAINT [FK_Constraints_CustomerList]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Job_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[Job]'))
ALTER TABLE [dbo].[Job]  WITH CHECK ADD  CONSTRAINT [FK_Job_Users] FOREIGN KEY([job_fk_usr])
REFERENCES [dbo].[Users] ([usr_ID])
GO
ALTER TABLE [dbo].[Job] CHECK CONSTRAINT [FK_Job_Users]


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnFilterOutbox]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
CREATE FUNCTION [dbo].[fnFilterOutbox]
(
)
RETURNS TABLE
AS
RETURN 
(
    SELECT
        shi_ID AS shi_ID_Filter
    FROM Shipment
    WHERE
        ISNULL (shi_trashed, 0) = 0
    AND
       shi_outbox=1
)




' 
END

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vFilterStatusOutBox]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vFilterStatusOutBox]
AS
SELECT DISTINCT dbo.Shipment.shi_ID AS FilterStatusInBox_fk_shi
FROM         dbo.Shipment LEFT OUTER JOIN
                      dbo.ShipmentStatus ON dbo.Shipment.shi_ID = dbo.ShipmentStatus.shs_fk_shi
WHERE     (dbo.ShipmentStatus.shs_fk_stk = 4) AND (NOT EXISTS
                          (SELECT     shs_ID
                            FROM          dbo.ShipmentStatus AS ShipmentStatus_1
                            WHERE      (shs_fk_shi = dbo.Shipment.shi_ID) AND (shs_fk_stk = 16 OR
                                                   shs_fk_stk = 17)))
' 
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane1' , N'SCHEMA',N'dbo', N'VIEW',N'vFilterStatusOutBox', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "Shipment"
            Begin Extent = 
               Top = 6
               Left = 227
               Bottom = 114
               Right = 432
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "ShipmentStatus"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 114
               Right = 189
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vFilterStatusOutBox'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPaneCount' , N'SCHEMA',N'dbo', N'VIEW',N'vFilterStatusOutBox', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vFilterStatusOutBox'
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vFullAddressReceivers]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].[vFullAddressReceivers]
AS
SELECT DISTINCT
                      dbo.Address.add_fk_shi AS shi_ID_receiver, dbo.Address.add_ID AS add_ID_receiver, dbo.Address.add_name1 AS name1_receiver,
                      dbo.Address.add_name2 AS name2_receiver, dbo.Address.add_streetAddress1 AS streetAddress1_receiver,
                      dbo.Address.add_streetAddress2 AS streetAddress2_receiver, dbo.Address.add_postOfficeBox AS postOfficeBox_receiver,
                      dbo.Address.add_postCode AS postCode_receiver, dbo.Address.add_city AS city_receiver, dbo.Address.add_state AS state_receiver,
                      dbo.Address.add_fk_cou AS country_receiver, dbo.Address.add_phone AS phone_receiver, dbo.Address.add_fax AS fax_receiver,
                      dbo.Address.add_mobile AS mobile_receiver, dbo.Address.add_email AS eMail_receiver, dbo.Address.add_attention AS attention_receiver,
                      dbo.Address.add_postOfficeBoxPostCode AS postOfficeBoxPostCode_receiver, dbo.Address.add_postOfficeBoxCity AS postOfficeBoxCity_receiver,
                      dbo.Country.cou_isoA2 AS countryIsoA2_receiver
FROM         dbo.Address LEFT OUTER JOIN
                      dbo.Country ON dbo.Address.add_fk_cou = dbo.Country.cou_ID
WHERE     (dbo.Address.add_fk_adk = 1)
' 
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane1' , N'SCHEMA',N'dbo', N'VIEW',N'vFullAddressReceivers', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[18] 4[49] 2[15] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "Address"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 114
               Right = 189
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1920
         Alias = 2235
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vFullAddressReceivers'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPaneCount' , N'SCHEMA',N'dbo', N'VIEW',N'vFullAddressReceivers', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vFullAddressReceivers'
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vFullAddress]'))
EXEC dbo.sp_executesql @statement = N'/*SELECT     dbo.Address.*, dbo.fnAddressPart(add_ID, 1) AS name1, dbo.fnAddressPart(add_ID, 2) AS name2, dbo.fnAddressPart(add_ID, 3) AS inCareOf, 
                      dbo.fnAddressPart(add_ID, 4) AS department, dbo.fnAddressPart(add_ID, 5) AS building, dbo.fnAddressPart(add_ID, 6) AS streetAddress1, 
                      dbo.fnAddressPart(add_ID, 7) AS streetAddress2, dbo.fnAddressPart(add_ID, 8) AS postOfficeBox, dbo.fnAddressPart(add_ID, 9) AS postCode, 
                      dbo.fnAddressPart(add_ID, 10) AS city, dbo.fnAddressPart(add_ID, 11) AS state, dbo.fnAddressPart(add_ID, 12) AS country, dbo.fnAddressPart(add_ID, 
                      13) AS phone, dbo.fnAddressPart(add_ID, 14) AS fax, dbo.fnAddressPart(add_ID, 15) AS mobile, dbo.fnAddressPart(add_ID, 16) AS eMail, 
                      dbo.fnAddressPart(add_ID, 17) AS attention*/
CREATE VIEW [dbo].[vFullAddress]
AS
SELECT     add_ID, add_fk_adk, add_fk_shi, add_fk_cus, add_name1 AS name1, add_name2 AS name2, add_streetAddress1 AS streetAddress1, 
                      add_streetAddress2 AS streetAddress2, add_city AS city, add_postCode AS postCode, add_postOfficeBox AS postOfficeBox, 
                      add_postOfficeBoxPostCode, add_state AS state, add_fk_cou AS country, add_phone AS phone, add_fax AS fax, add_mobile AS mobile, 
                      add_email AS eMail, add_attention AS attention, add_openingHours, add_postOfficeBoxCity
FROM         dbo.Address
' 
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPane1' , N'SCHEMA',N'dbo', N'VIEW',N'vFullAddress', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[14] 4[42] 2[24] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "Address"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 121
               Right = 257
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 2385
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vFullAddress'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_DiagramPaneCount' , N'SCHEMA',N'dbo', N'VIEW',N'vFullAddress', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vFullAddress'
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fGetFullAddressSender]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'


-- =============================================
-- Author:		Tellef Ormstad
-- Create date: 18/4-2006
-- Description:	Receive Full Address for Shipment
-- Parameter:	AddressKind
-- =============================================
CREATE FUNCTION [dbo].[fGetFullAddressSender] (@nAddressKindID INT)
RETURNS TABLE
AS  
RETURN
(
  SELECT 
	add_ID AS ''add_ID_sender'',
	add_fk_shi AS ''add_fk_shi_sender'',
	add_fk_cus AS ''add_fk_cus_sender'',
	name1 AS ''name1_sender'',
	name2 AS ''name2_sender'',
	postCode AS ''postcode_sender'',
	streetAddress1 AS ''streetaddress1_sender'',
	streetAddress2 AS ''streetaddress2_sender'',
	city AS ''city_sender'',
	phone AS ''phone_sender'',
	fax AS ''fax_sender'',
	mobile AS ''mobile_sender'',
	eMail AS ''email_sender'',
	attention AS ''attention_sender''
 FROM vFullAddress
 WHERE add_fk_adk = @nAddressKindID
)



' 
END

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fGetFullAddressReceiver]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'


-- =============================================
-- Author:		Tellef Ormstad
-- Create date: 18/4-2006
-- Description:	Receive Full Address for Shipment
-- Parameter:	AddressKind
-- =============================================
CREATE FUNCTION [dbo].[fGetFullAddressReceiver] (@nAddressKindID INT)
RETURNS TABLE
AS  
RETURN
(
 SELECT 
	add_ID AS ''add_ID_receiver'',
	add_fk_shi AS ''add_fk_shi_receiver'',
	add_fk_cus AS ''add_fk_cus_receiver'',
	name1 AS ''name1_receiver'',
	name2 AS ''name2_receiver'',
	postCode AS ''postcode_receiver'',
	streetAddress1 AS ''streetaddress1_receiver'',
	streetAddress2 AS ''streetaddress2_receiver'',
	city AS ''city_receiver'',
	phone AS ''phone_receiver'',
	fax AS ''fax_receiver'',
	mobile AS ''mobile_receiver'',
	eMail AS ''email_receiver'',
	attention AS ''attention_receiver''
 FROM vFullAddress
 WHERE add_fk_adk = @nAddressKindID
)



' 
END

GO