-- ===============================================
-- Manufacturing EPC IS DEMO DATABASE
-- Load Sample Data Script
-- 
-- Version:	1.0 - 01/2005
--
-- ===============================================

-- =======================================================
-- INSERT INITIAL DATA INTO EPC IS DB
-- =======================================================

-- point to proper DB 
USE [<<DATABASE_NAME>>]
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

-- Packaging Table
INSERT INTO PACKAGING (PackagingID,UnitOfMeasure) VALUES ('CASE_1',1);
INSERT INTO PACKAGING (PackagingID,UnitOfMeasure) VALUES ('CASE_10',10);
INSERT INTO PACKAGING (PackagingID,UnitOfMeasure) VALUES ('CASE_11',11);
INSERT INTO PACKAGING (PackagingID,UnitOfMeasure) VALUES ('CASE_12',12);
INSERT INTO PACKAGING (PackagingID,UnitOfMeasure) VALUES ('CASE_13',13);
INSERT INTO PACKAGING (PackagingID,UnitOfMeasure) VALUES ('CASE_14',14);
INSERT INTO PACKAGING (PackagingID,UnitOfMeasure) VALUES ('CASE_15',15);
INSERT INTO PACKAGING (PackagingID,UnitOfMeasure) VALUES ('CASE_2',2);
INSERT INTO PACKAGING (PackagingID,UnitOfMeasure) VALUES ('CASE_3',3);
INSERT INTO PACKAGING (PackagingID,UnitOfMeasure) VALUES ('CASE_4',4);
INSERT INTO PACKAGING (PackagingID,UnitOfMeasure) VALUES ('CASE_5',5);
INSERT INTO PACKAGING (PackagingID,UnitOfMeasure) VALUES ('CASE_6',6);
INSERT INTO PACKAGING (PackagingID,UnitOfMeasure) VALUES ('CASE_7',7);
INSERT INTO PACKAGING (PackagingID,UnitOfMeasure) VALUES ('CASE_8',8);
INSERT INTO PACKAGING (PackagingID,UnitOfMeasure) VALUES ('CASE_9',9);
GO

--Classification Table
SET IDENTITY_INSERT Classification ON;
INSERT INTO Classification(ClassificationId, Name, Description) VALUES (1, 'XBOX', 'XBOX Console Games');
INSERT INTO Classification(ClassificationId, Name, Description) VALUES (2, 'XBOX 2', 'XBOX Console Games 2');
INSERT INTO Classification(ClassificationId, Name, Description) VALUES (3, 'SPOT', 'SPOT Watch');
SET IDENTITY_INSERT Classification OFF;
GO

--Products Table
SET IDENTITY_INSERT PRODUCTS ON;
INSERT INTO PRODUCTS (ProductId,Name,Description,ClassificationId,UnitPrice,UnitsInStock,UnitsOnOrder,UPC,LastUsedSerialNumber) VALUES (1,'XBOX Halo 2','XBOX Halo 2',1,50,5000,10,'01245782541252',500);
INSERT INTO PRODUCTS (ProductId,Name,Description,ClassificationId,UnitPrice,UnitsInStock,UnitsOnOrder,UPC,LastUsedSerialNumber) VALUES (2,'XBOX Soccer','XBOX Soccer',1,35,5000,10,'01245782241251',500);
INSERT INTO PRODUCTS (ProductId,Name,Description,ClassificationId,UnitPrice,UnitsInStock,UnitsOnOrder,UPC,LastUsedSerialNumber) VALUES (3,'XBOX Golf','XBOX Golf',1,45,5000,10,'01245782256254',500);
INSERT INTO PRODUCTS (ProductId,Name,Description,ClassificationId,UnitPrice,UnitsInStock,UnitsOnOrder,UPC,LastUsedSerialNumber) VALUES (-1,'Reserved for Pallets','Reserved for Pallets',1,45,5000,10,'01245782256254',500);
SET IDENTITY_INSERT PRODUCTS OFF;
GO

--ProductPackaging Table
INSERT INTO PRODUCTPACKAGING(ProductID,PackagingID) VALUES (1,'CASE_5');
INSERT INTO PRODUCTPACKAGING(ProductID,PackagingID) VALUES (2,'CASE_10');
INSERT INTO PRODUCTPACKAGING(ProductID,PackagingID) VALUES (3,'CASE_8');
GO

--Suppliers Table
SET IDENTITY_INSERT SUPPLIERS ON;
INSERT INTO SUPPLIERS(SupplierId,CompanyName,ContactName,ContactTitle,Address,City,State,PostalCode,Country,Phone,Fax) VALUES (1,'Flextronics','Tom J','Sales Manager','100 Main St','Los Angeles','CA','23345','US','111-111-1111','111-111-1111');
INSERT INTO SUPPLIERS(SupplierId,CompanyName,ContactName,ContactTitle,Address,City,State,PostalCode,Country,Phone,Fax) VALUES (2,'Intel','John J','Sales Manager','145 Ave','Seattle','WA','99999','US','999-999-9999','999-999-9999');
SET IDENTITY_INSERT SUPPLIERS OFF;
GO

--PurchaseOrders Table
INSERT INTO PURCHASEORDERS (OrderId,SupplierId,OrderDate,RequiredDate,ShippedDate,OrderStatusId) VALUES (550101000,1,'2004-12-24 00:00:00','2005-01-15 00:00:00','','');
INSERT INTO PURCHASEORDERS (OrderId,SupplierId,OrderDate,RequiredDate,ShippedDate,OrderStatusId) VALUES (550101001,2,'2004-12-24 00:00:00','2005-01-15 00:00:00','','');
GO

--PurchaseOrderDetails Table
SET IDENTITY_INSERT PURCHASEORDERDETAILS ON;
INSERT INTO PURCHASEORDERDETAILS(OrderDetailsId,OrderId,ProductId,UnitPrice,Quantity,Discount) VALUES (1,550101000,1,50,15,0);
INSERT INTO PURCHASEORDERDETAILS(OrderDetailsId,OrderId,ProductId,UnitPrice,Quantity,Discount) VALUES (2,550101000,2,35,50,0);
INSERT INTO PURCHASEORDERDETAILS(OrderDetailsId,OrderId,ProductId,UnitPrice,Quantity,Discount) VALUES (3,550101001,1,50,35,0);
INSERT INTO PURCHASEORDERDETAILS(OrderDetailsId,OrderId,ProductId,UnitPrice,Quantity,Discount) VALUES (4,550101001,2,35,75,0);
INSERT INTO PURCHASEORDERDETAILS(OrderDetailsId,OrderId,ProductId,UnitPrice,Quantity,Discount) VALUES (5,550101001,3,45,60,0);
SET IDENTITY_INSERT PURCHASEORDERDETAILS OFF;
GO


--TagType Table
SET IDENTITY_INSERT TAGTYPE ON;
INSERT INTO TAGTYPE(TagTypeId,Name,ParentType) VALUES (1,'Product',4);
INSERT INTO TAGTYPE(TagTypeId,Name,ParentType) VALUES (2,'Item',1);
INSERT INTO TAGTYPE(TagTypeId,Name,ParentType) VALUES (3,'Case',4);
INSERT INTO TAGTYPE(TagTypeId,Name,ParentType) VALUES (4,'Pallet',4);
SET IDENTITY_INSERT TAGTYPE OFF;
GO

--CompanySerialNumber Table
INSERT INTO COMPANYSERIALNUMBER(LastUsedSerialNumber) VALUES(0);
GO

--CompanyPrefixTranslation Table
INSERT INTO CompanyPrefixTranslationTable VALUES ('054665', '1');
INSERT INTO CompanyPrefixTranslationTable VALUES ('124578', '2');
INSERT INTO CompanyPrefixTranslationTable VALUES ('055443', '5');
INSERT INTO CompanyPrefixTranslationTable VALUES ('552185', '10');
GO

INSERT INTO EPCEventTypesTable (EPCEventId, EPCEventStatus) VALUES (1, 'Advance Shipment Notification (3B2ASN)');
INSERT INTO EPCEventTypesTable (EPCEventId, EPCEventStatus) VALUES (2, 'Acknowledgment (3B2ASN)');
INSERT INTO EPCEventTypesTable (EPCEventId, EPCEventStatus) VALUES (3, 'Shipping Order Request (3B12)');
INSERT INTO EPCEventTypesTable (EPCEventId, EPCEventStatus) VALUES (4, 'Shipping Order Confirmation (3B12)');
INSERT INTO EPCEventTypesTable (EPCEventId, EPCEventStatus) VALUES (11, 'Tag Read-SA1; Item Pickup');
INSERT INTO EPCEventTypesTable (EPCEventId, EPCEventStatus) VALUES (12, 'Advance Shipment Notification (3B2ASN)');
INSERT INTO EPCEventTypesTable (EPCEventId, EPCEventStatus) VALUES (13, 'Acknowledgment (3B2ASN)');
INSERT INTO EPCEventTypesTable (EPCEventId, EPCEventStatus) VALUES (14, 'Notify of Shipping Order Confirmation (3B13)');
INSERT INTO EPCEventTypesTable (EPCEventId, EPCEventStatus) VALUES (15, 'Tag Read-SA2; Item Delivered');
INSERT INTO EPCEventTypesTable (EPCEventId, EPCEventStatus) VALUES (21, 'Tag Read-RA1; Item Received');
INSERT INTO EPCEventTypesTable (EPCEventId, EPCEventStatus) VALUES (22, 'Notify of Shipment Receipt (4B2)');
INSERT INTO EPCEventTypesTable (EPCEventId, EPCEventStatus) VALUES (23, 'Tag Read-RA2; Item Delivered');
INSERT INTO EPCEventTypesTable (EPCEventId, EPCEventStatus) VALUES (31, 'Tag Read-REC1; Item Received');
INSERT INTO EPCEventTypesTable (EPCEventId, EPCEventStatus) VALUES (32, 'Notify of Shipment Receipt (4B2)');
GO