CREATE DATABASE [<<DATABASE_NAME>>]
GO

USE [<<DATABASE_NAME>>]

exec sp_dboption N'<<DATABASE_NAME>>', N'autoclose', N'false'
GO

exec sp_dboption N'<<DATABASE_NAME>>', N'bulkcopy', N'false'
GO

exec sp_dboption N'<<DATABASE_NAME>>', N'trunc. log', N'false'
GO

exec sp_dboption N'<<DATABASE_NAME>>', N'torn page detection', N'true'
GO

exec sp_dboption N'<<DATABASE_NAME>>', N'read only', N'false'
GO

exec sp_dboption N'<<DATABASE_NAME>>', N'dbo use', N'false'
GO

exec sp_dboption N'<<DATABASE_NAME>>', N'single', N'false'
GO

exec sp_dboption N'<<DATABASE_NAME>>', N'autoshrink', N'false'
GO

exec sp_dboption N'<<DATABASE_NAME>>', N'ANSI null default', N'false'
GO

exec sp_dboption N'<<DATABASE_NAME>>', N'recursive triggers', N'false'
GO

exec sp_dboption N'<<DATABASE_NAME>>', N'ANSI nulls', N'false'
GO

exec sp_dboption N'<<DATABASE_NAME>>', N'concat null yields null', N'false'
GO

exec sp_dboption N'<<DATABASE_NAME>>', N'cursor close on commit', N'false'
GO

exec sp_dboption N'<<DATABASE_NAME>>', N'default to local cursor', N'false'
GO

exec sp_dboption N'<<DATABASE_NAME>>', N'quoted identifier', N'false'
GO

exec sp_dboption N'<<DATABASE_NAME>>', N'ANSI warnings', N'false'
GO

exec sp_dboption N'<<DATABASE_NAME>>', N'auto create statistics', N'true'
GO

exec sp_dboption N'<<DATABASE_NAME>>', N'auto update statistics', N'true'
GO

if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and (@@microsoftversion & 0xffff >= 1082) ) )
	exec sp_dboption N'<<DATABASE_NAME>>', N'db chaining', N'false'
GO

use [<<DATABASE_NAME>>]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Item_Classification]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Item] DROP CONSTRAINT FK_Item_Classification
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Products_Classification]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Products] DROP CONSTRAINT FK_Products_Classification
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_EPCStatusTable_EPCEventTypesTable]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[EPCStatusTable] DROP CONSTRAINT FK_EPCStatusTable_EPCEventTypesTable
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ProductPackaging_Packaging]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ProductPackaging] DROP CONSTRAINT FK_ProductPackaging_Packaging
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ProductPackaging_Products]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[ProductPackaging] DROP CONSTRAINT FK_ProductPackaging_Products
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TagsDetails_Products]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TagsDetails] DROP CONSTRAINT FK_TagsDetails_Products
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_PurchaseOrderDetails_PurchaseOrders]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[PurchaseOrderDetails] DROP CONSTRAINT FK_PurchaseOrderDetails_PurchaseOrders
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TagsDetails_PurchaseOrders]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TagsDetails] DROP CONSTRAINT FK_TagsDetails_PurchaseOrders
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Item_Suppliers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Item] DROP CONSTRAINT FK_Item_Suppliers
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_PurchaseOrders_Suppliers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[PurchaseOrders] DROP CONSTRAINT FK_PurchaseOrders_Suppliers
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TagsDetails_TagType]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TagsDetails] DROP CONSTRAINT FK_TagsDetails_TagType
GO

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

CREATE TABLE [dbo].[Classification] (
	[ClassificationId] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Description] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CompanyPrefixTranslationTable] (
	[CompanyPrefix] [nvarchar] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CompanyPrefixIndex] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CompanySerialNumber] (
	[LastUsedSerialNumber] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[EPCEventTypesTable] (
	[EPCEventId] [int] NOT NULL ,
	[EPCEventStatus] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[EPCStatusTable] (
	[EPCId] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[EPCEventId] [int] NOT NULL ,
	[EPCEventDate] [datetime] NULL ,
	[Event_From] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Event_To] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Location] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Item] (
	[ItemId] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [nchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Description] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ClassificationId] [int] NOT NULL ,
	[ReorderPoint] [int] NULL ,
	[ReorderQuantity] [int] NULL ,
	[SupplierId] [int] NOT NULL ,
	[UPC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[OnHand] [int] NULL ,
	[OnOrder] [int] NULL ,
	[LocationId] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Packaging] (
	[PackagingID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[UnitOfMeasure] [smallint] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ProductPackaging] (
	[ProductID] [int] NOT NULL ,
	[PackagingID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Products] (
	[ProductId] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ClassificationId] [int] NOT NULL ,
	[UnitPrice] [money] NULL ,
	[UnitsInStock] [int] NULL ,
	[UnitsOnOrder] [int] NULL ,
	[UPC] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LastUsedSerialNumber] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PurchaseOrderDetails] (
	[OrderDetailsId] [int] IDENTITY (1, 1) NOT NULL ,
	[OrderId] [int] NOT NULL ,
	[ProductId] [int] NOT NULL ,
	[UnitPrice] [money] NULL ,
	[Quantity] [int] NULL ,
	[Discount] [float] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PurchaseOrders] (
	[OrderId] [int] NOT NULL ,
	[SupplierId] [int] NULL ,
	[OrderDate] [datetime] NULL ,
	[RequiredDate] [datetime] NULL ,
	[ShippedDate] [datetime] NULL ,
	[OrderStatusId] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Suppliers] (
	[SupplierId] [int] IDENTITY (1, 1) NOT NULL ,
	[CompanyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[ContactName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ContactTitle] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Address] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[City] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[State] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PostalCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Country] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Phone] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Fax] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TagType] (
	[TagTypeId] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ParentType] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TagsDetails] (
	[TagId] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[TagTypeId] [int] NOT NULL ,
	[ParentTagId] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[OrderId] [int] NULL ,
	[ProductId] [int] NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Classification] WITH NOCHECK ADD 
	CONSTRAINT [PK_Classification] PRIMARY KEY  CLUSTERED 
	(
		[ClassificationId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[CompanyPrefixTranslationTable] WITH NOCHECK ADD 
	CONSTRAINT [PK_CompanyPrefixTranslationTable] PRIMARY KEY  CLUSTERED 
	(
		[CompanyPrefix]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[EPCEventTypesTable] WITH NOCHECK ADD 
	CONSTRAINT [PK_EPCEventTypesTable] PRIMARY KEY  CLUSTERED 
	(
		[EPCEventId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Packaging] WITH NOCHECK ADD 
	CONSTRAINT [PK_Packaging] PRIMARY KEY  CLUSTERED 
	(
		[PackagingID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Products] WITH NOCHECK ADD 
	CONSTRAINT [PK_Products] PRIMARY KEY  CLUSTERED 
	(
		[ProductId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[PurchaseOrders] WITH NOCHECK ADD 
	CONSTRAINT [PK_PurchaseOrders] PRIMARY KEY  CLUSTERED 
	(
		[OrderId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Suppliers] WITH NOCHECK ADD 
	CONSTRAINT [PK_Suppliers] PRIMARY KEY  CLUSTERED 
	(
		[SupplierId]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[TagType] WITH NOCHECK ADD 
	CONSTRAINT [PK_TagType] PRIMARY KEY  CLUSTERED 
	(
		[TagTypeId]
	)  ON [PRIMARY] 
GO

 CREATE  INDEX [IX_CompanyPrefixTranslationTable] ON [dbo].[CompanyPrefixTranslationTable]([CompanyPrefixIndex]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[EPCStatusTable] ADD 
	CONSTRAINT [FK_EPCStatusTable_EPCEventTypesTable] FOREIGN KEY 
	(
		[EPCEventId]
	) REFERENCES [dbo].[EPCEventTypesTable] (
		[EPCEventId]
	)
GO

ALTER TABLE [dbo].[Item] ADD 
	CONSTRAINT [FK_Item_Classification] FOREIGN KEY 
	(
		[ClassificationId]
	) REFERENCES [dbo].[Classification] (
		[ClassificationId]
	),
	CONSTRAINT [FK_Item_Suppliers] FOREIGN KEY 
	(
		[SupplierId]
	) REFERENCES [dbo].[Suppliers] (
		[SupplierId]
	)
GO

ALTER TABLE [dbo].[ProductPackaging] ADD 
	CONSTRAINT [FK_ProductPackaging_Packaging] FOREIGN KEY 
	(
		[PackagingID]
	) REFERENCES [dbo].[Packaging] (
		[PackagingID]
	),
	CONSTRAINT [FK_ProductPackaging_Products] FOREIGN KEY 
	(
		[ProductID]
	) REFERENCES [dbo].[Products] (
		[ProductId]
	)
GO

ALTER TABLE [dbo].[Products] ADD 
	CONSTRAINT [FK_Products_Classification] FOREIGN KEY 
	(
		[ClassificationId]
	) REFERENCES [dbo].[Classification] (
		[ClassificationId]
	)
GO

ALTER TABLE [dbo].[PurchaseOrderDetails] ADD 
	CONSTRAINT [FK_PurchaseOrderDetails_PurchaseOrders] FOREIGN KEY 
	(
		[OrderId]
	) REFERENCES [dbo].[PurchaseOrders] (
		[OrderId]
	)
GO

ALTER TABLE [dbo].[PurchaseOrders] ADD 
	CONSTRAINT [FK_PurchaseOrders_Suppliers] FOREIGN KEY 
	(
		[SupplierId]
	) REFERENCES [dbo].[Suppliers] (
		[SupplierId]
	)
GO

ALTER TABLE [dbo].[TagsDetails] ADD 
	CONSTRAINT [FK_TagsDetails_Products] FOREIGN KEY 
	(
		[ProductId]
	) REFERENCES [dbo].[Products] (
		[ProductId]
	),
	CONSTRAINT [FK_TagsDetails_PurchaseOrders] FOREIGN KEY 
	(
		[OrderId]
	) REFERENCES [dbo].[PurchaseOrders] (
		[OrderId]
	),
	CONSTRAINT [FK_TagsDetails_TagType] FOREIGN KEY 
	(
		[TagTypeId]
	) REFERENCES [dbo].[TagType] (
		[TagTypeId]
	)
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [dbo].[AddEpcStatusInfo]
(@EPCId AS VARCHAR(30),
@EPCEventId AS INT)
AS
	INSERT INTO EpcStatusTable (EPCId, EPCEventId, EPCEventDate)
	VALUES (@EPCId, @EPCEventId, getDate ()) ;
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


/********Object: Stored Procedure dbo.addFullEpcStatusInfo ********/
CREATE PROCEDURE [dbo].[AddFullEpcStatusInfo]
(@EPCId nvarchar(30),
@EPCEventId int,
@EPCEventDate datetime,
@EventFrom nvarchar(50),
@EventTo nvarchar(50),
@Location nvarchar(50))
AS
	INSERT INTO EpcStatusTable (EPCId, EPCEventId, EPCEventDate, Event_From, Event_To, Location)
	VALUES (@EPCId, @EPCEventId, @EPCEventDate, @EventFrom, @EventTo, @Location) ;
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

/****** Object:  Stored Procedure dbo.GetCompanyPrefix******/
CREATE PROCEDURE [DBO].[GetCompanyPrefix] 
@companyPrefixIndex nvarchar(5)
AS
Select CompanyPrefix
  From CompanyPrefixTranslationTable
 Where CompanyPrefixIndex = @companyPrefixIndex



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


/****** Object:  Stored Procedure dbo.GetCompanyPrefixIndex******/
CREATE PROCEDURE [dbo].[GetCompanyPrefixIndex] 
@companyPrefix nvarchar(13)
AS
Select CompanyPrefixIndex
  From CompanyPrefixTranslationTable
 Where CompanyPrefix = @companyPrefix


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [dbo].[GetEPCEvents]
(@EPCId VARCHAR(30))
AS
	SELECT EPCId AS [EPC ID], EPCEventId AS [EVENT ID],
	EPCEventDate AS [EVENT DATE]
	FROM EPCStatusTable
	WHERE EPCId = @EPCId ;
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

/********* StoredProcedure to retreive All EPC events from EPCStatusTable and ordered by any one column********/
CREATE PROCEDURE [dbo].[GetEPCEventsOrdered]
@SortField VARCHAR(30)
AS
DECLARE @SQLStatement varchar(500)
SELECT @SQLStatement =  'SELECT [EPCStatusTable].[EPCId] AS [EPC ID],  ' +
			      '[EPCStatusTable].[EPCEventDate] AS [EVENT DATE],  ' +
			      '[EPCEventTypesTable].[EPCEventStatus] AS [EVENT] FROM [EPCStatusTable],  [EPCEventTypesTable]  '  +
			      'WHERE ([EPCStatusTable].[EPCEventId] = [EPCEventTypesTable].[EPCEventId])  ORDER BY  [EPCStatusTable]. ' + @SortField;
EXEC(@SQLStatement)
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

/********* StoredProcedure to retreive EPC events from EPCStatusTable ********/
CREATE PROCEDURE [dbo].[GetFullEPCEvents]
(@EPCId VARCHAR(30))
AS
	SELECT EPCId AS [EPC ID], EPCEventId AS [EVENT ID],
	EPCEventDate AS [EVENT DATE], Event_To AS [EVENT TO],
	Event_From AS [EVENT FROM], Location AS [LOCATION]
	FROM EPCStatusTable
	WHERE EPCId = @EPCId ;
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

/********* StoredProcedure to retreive All EPC events from EPCStatusTable and ordered by any one column********/
CREATE PROCEDURE [dbo].[GetFullEPCEventsOrdered]
@SortField VARCHAR(60)
AS
DECLARE @SQLStatement varchar(500)
SELECT @SQLStatement =  'SELECT [EPCStatusTable].[EPCId] AS [EPC ID],  ' +
			      '[EPCStatusTable].[EPCEventDate] AS [EVENT DATE],  ' +
			      '[EPCEventTypesTable].[EPCEventStatus] AS [EVENT], ' +
			      '[EPCStatusTable].[Event_From] AS [EVENT FROM], ' +
			      '[EPCStatusTable].[Event_To] AS [EVENT TO], ' +
			      '[EPCStatusTable].[Location] AS [LOCATION] FROM [EPCStatusTable],  [EPCEventTypesTable]  '  +
			      'WHERE ([EPCStatusTable].[EPCEventId] = [EPCEventTypesTable].[EPCEventId])  ORDER BY  [EPCStatusTable].' + @SortField;
EXEC(@SQLStatement)
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


/****** Object:  Stored Procedure dbo.resetDatabase******/
CREATE PROCEDURE [dbo].[ResetDatabase]
AS
	delete from EPCStatusTable where 1 = 1 ;
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


/****** Object:  Stored Procedure dbo.addEpcTag******/
CREATE PROCEDURE [dbo].[addEpcTag]
@TagId nvarchar(30),
@TagTypeId int,
@ParentTagId nvarchar(30),
@OrderId int,
@ProductId int
AS
 INSERT INTO TagsDetails (TagId, TagTypeId, ParentTagId, OrderId, ProductId)
VALUES (@TagId, @TagTypeId, @ParentTagId, @OrderId, @ProductId);


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


/****** Object:  Stored Procedure dbo.getAssociatedCasesEPCInformation******/
CREATE PROCEDURE dbo.getAssociatedCasesEPCInformation
@OrderId int,
@PalletEPC varchar(30)
AS
select a.TagId, a.TagTypeId, a.ParentTagId, a.OrderId, a.ProductId, b.Description, b.UPC
from TagsDetails a,Products b where OrderId = @OrderId and ParentTagId = @PalletEPC
and a.ProductId <> '-1'
and a.ProductId = b.ProductId
order by TagId;


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


/****** Object:  Stored Procedure dbo.getCompanySerialNumber******/
CREATE PROCEDURE [dbo].[getCompanySerialNumber]
AS
DECLARE @Count int, @SerialNumber int;
SET @Count = (select count(*)from CompanySerialNumber);
If(@Count > 0)
SELECT IsNull(LastUsedSerialNumber,0) + 1
    FROM CompanySerialNumber;
else
/* No rows in table */
select count(*) + 1 from CompanySerialNumber


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


/****** Object:  Stored Procedure dbo.getItemDetails******/
CREATE PROCEDURE
[dbo].getItemDetails ( @UPC numeric(20))
AS
    Select Item.UPC AS UPC,
           Item.Name AS Name,
           Item.Description AS Description,
           Classification.Name AS ClassificationName,
           Item.ReorderPoint AS ReorderPoint,
           Item.ReorderQuantity AS ReorderQuantity,
           Suppliers.CompanyName AS CompanyName,
           Item.OnHand AS OnHand,
           Item.OnOrder AS OnOrder
      From dbo.Item
Inner Join dbo.Suppliers On Item.SupplierId = Suppliers.SupplierId
Inner Join dbo.Classification On Item.ClassificationId = Classification.ClassificationId
  WHERE Item.UPC = @UPC
UNION
    Select Products.UPC AS UPC,
           Products.Name AS Name,
           Products.Description AS Description,
           Classification.Name AS ClassificationName,
           null AS ReorderPoint,
           null AS ReorderQuantity,
           null AS CompanyName,
           Products.UnitsInStock AS OnHand,
           Products.UnitsOnOrder AS OnOrder
      From dbo.Products
Inner Join dbo.Classification On Products.ClassificationId = Classification.ClassificationId
  WHERE Products.UPC = @UPC


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



/****** Object:  Stored Procedure dbo.getProductSerialNumber******/
CREATE PROCEDURE [dbo].[getProductSerialNumber]
@upc nvarchar(30)
AS
 SELECT LastUsedSerialNumber + 1
    FROM Products
 WHERE upc = @upc


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


/****** Object:  Stored Procedure dbo.getProductsInPallet******/
CREATE PROCEDURE [dbo].[getProductsInPallet]
@palletTagId nvarchar(30)
AS
    SELECT Pallet.OrderId, Pallet.TagId PalletTagId, [Case].TagId CaseTagId, Products.Upc
      FROM TagsDetails [Pallet]
INNER JOIN TagsDetails [Case]    ON [Case].ParentTagId = Pallet.TagId
INNER JOIN TagsDetails [Product] ON Product.ParentTagId = [Case].TagId
INNER JOIN TagType PalletTagType   ON Pallet.TagTypeId = PalletTagType.TagTypeId
INNER JOIN TagType CaseTagType     ON [Case].TagTypeId = CaseTagType.TagTypeId
INNER JOIN TagType ProductTagType  ON ProductTagType.TagTypeId = ProductTagType.TagTypeId
INNER JOIN Products ON Products.ProductId = Product.ProductId
     WHERE PalletTagType.Name = 'Pallet'
       AND CaseTagType.Name = 'Case'
       AND ProductTagType.Name = 'Product'
       AND Pallet.TagId = @palletTagId


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


/****** Object:  Stored Procedure dbo.getPurchaseOrderCaseEPCDetails******/
CREATE PROCEDURE dbo.getPurchaseOrderCaseEPCDetails
@OrderId int,
@ProductId int
AS
select TagId, TagTypeId,ParentTagId, OrderId, ProductId
from TagsDetails where OrderId = @OrderId and ProductId = @ProductId
order by TagId;

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/****** Object:  Stored Procedure dbo.getPurchaseOrderDetails******/
CREATE PROCEDURE dbo.getPurchaseOrderDetails
@OrderId int
AS
select a.Orderid as "Order #",
b.ProductId as "Product ID",
c.Description,
c.UPC,
Sum(b.Quantity) as Quantity,
d.UnitOfMeasure as UOM,
CAST(CEILING (Sum(b.Quantity)/CAST(d.UnitOfMeasure as float)) AS int) as "Count of Cases"
FROM PurchaseOrders a,
PurchaseOrderDetails b,
Products c,
Packaging d,
ProductPackaging e
WHERE a.orderid=@OrderId
and a.orderid = b.orderid
and b.productid <> '-1'
and b.productid = c.productid
and e.productid = c.productid
and e.packagingid = d.packagingid
GROUP BY a.orderid, b.productid, c.Description, c.UPC, b.Quantity, d.UnitOfMeasure;

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/****** Object:  Stored Procedure dbo.getPurchaseOrderPalletEPCDetails******/
CREATE PROCEDURE dbo.getPurchaseOrderPalletEPCDetails
@OrderId int
AS
select TagId, TagTypeId,ParentTagId, OrderId, ProductId
from TagsDetails where OrderId = @OrderId and ProductId = '-1'
order by TagId;

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO



--Create Stored procedures

/****** Object:  Stored Procedure dbo.getTagType******/
CREATE PROCEDURE [dbo].[getTagType]
@TagId nvarchar(30)
AS
    Select Name
      From TagsDetails
INNER JOIN TagType On TagsDetails.TagTypeId = TagType.TagTypeId
     WHERE TagsDetails.TagId = @TagId


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


/****** Object:  Stored Procedure dbo.resetPalletInformation******/
CREATE PROCEDURE [dbo].[resetPalletInformation]
AS
delete from TagsDetails where ProductId = '-1';

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


/****** Object:  Stored Procedure dbo.setParentTag******/
CREATE PROCEDURE [dbo].[setParentTag]
@TagId nvarchar(30),
@ParentTagId nvarchar(30)
AS
DECLARE @parentTagType int
SELECT @parentTagType = TagTypeId
  FROM TagsDetails
 WHERE TagId = @ParentTagId
 UPDATE TagsDetails
    SET ParentTagId = @ParentTagId
   FROM TagType
  WHERE TagType.TagTypeId = TagsDetails.TagTypeId
    AND TagType.ParentType = @parentTagType
    AND TagId = @TagId


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/****** Object:  Stored Procedure dbo.updateCompanySerialNumber******/
CREATE PROCEDURE [dbo].[updateCompanySerialNumber]
AS
 Update CompanySerialNumber
      Set  LastUsedSerialNumber = LastUsedSerialNumber + 1
If @@RowCount = 0
	Insert into CompanySerialNumber  Values(1)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


/****** Object:  Stored Procedure dbo.updateProductSerialNumber******/
CREATE PROCEDURE [dbo].[updateProductSerialNumber]
@UPC nvarchar(30)
AS
 Update Products
      Set  LastUsedSerialNumber = LastUsedSerialNumber + 1
 WHERE upc = @upc


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

