USE MI;

/* EMPLOYEE */
DBCC CHECKIDENT ('EMPLOYEE', RESEED, 100);
INSERT INTO EMPLOYEE ( LastName, FirstName, Department, Position, Supervisor, OfficePhone, OfficeFax, EmailAddress)
VALUES ('Morgan', 'James', 'Executive', 'CEO', '101', '310-208-1401', '310-208-1499', 'James.Morgan@morganimporting.com');

INSERT INTO EMPLOYEE ( LastName, FirstName, Department, Position, Supervisor, OfficePhone, OfficeFax, EmailAddress)
VALUES ('Morgan', 'Jessica', 'Executive', 'CFO', '101', '310-208-1402', '310-208-1499', 'Jessica.Morgan@morganimporting.com');

INSERT INTO EMPLOYEE ( LastName, FirstName, Department, Position, Supervisor, OfficePhone, OfficeFax, EmailAddress)
VALUES ('Williams', 'David', 'Purchasing', 'Purchasing Manager', '101', '310-208-1434', '310-208-1498', 'David.Williams@morganimporting.com');

INSERT INTO EMPLOYEE ( LastName, FirstName, Department, Position, Supervisor, OfficePhone, OfficeFax, EmailAddress)
VALUES ('Gilbertson', 'Teri', 'Purchasing', 'Purchasing Agent', '103', '310-208-1435', '310-208-1498', 'Teri.Gilbertson@morganimporting.com');

INSERT INTO EMPLOYEE ( LastName, FirstName, Department, Position, Supervisor, OfficePhone, OfficeFax, EmailAddress)
VALUES ('Wright', 'James', 'Receiving', 'Receiving Supervisor', '101', '310-208-1456', '310-208-1497', 'James.Wright@morganimporting.com');

INSERT INTO EMPLOYEE ( LastName, FirstName, Department, Position, Supervisor, OfficePhone, OfficeFax, EmailAddress)
VALUES ('Douglas', 'Tom', 'Receiving', 'Receiving Agent', '105', '310-208-1457', '310-208-1497', 'Tom.Douglas@morganimporting.com');

/* STORE */
DBCC CHECKIDENT ('STORE', RESEED, 950);
INSERT INTO STORE(StoreName, City, Country, Phone, Fax, EmailAddress, Contact)
VALUES ('Eastern Sales', 'Singapore', 'Singapore', '65-543-1233', '65-543-1239', 'Sales@EasternSales.com.sg', 'Jeremy');

INSERT INTO STORE(StoreName, City, Country, Phone, Fax, EmailAddress, Contact)
VALUES ('Eastern Treasures', 'Manila', 'Philippines', '63-2-654-2344', '63-2-654-2349', 'Sales@EasternTreasures.com.ph', 'Gracielle');

INSERT INTO STORE(StoreName, City, Country, Phone, Fax, EmailAddress, Contact)
VALUES ('Jade Antiques', 'Singapore', 'Singapore', '65-543-3455', '65-543-3459', 'Sales@JadeAntiques.com.sg', 'Swee Lai');

INSERT INTO STORE(StoreName, City, Country, Phone, Fax, EmailAddress, Contact)
VALUES ('Andes Treasures', 'Lima', 'Peru', '51-14-765-4566', '51-14-765-4569', 'Sales@AndesTreasures.com.pe', 'Juan Carlos');

INSERT INTO STORE(StoreName, City, Country, Phone, Fax, EmailAddress, Contact)
VALUES ('Eastern Sales', 'Hong Kong', 'Peoples Republic of China', '852-876-5677', '852-876-5679', 'Sales@EasternSales.com.hk', 'Sam');

INSERT INTO STORE(StoreName, City, Country, Phone, Fax, EmailAddress, Contact)
VALUES ('Eastern Treasures', 'New Delhi', 'India', '91-11-987-6788', '91-11-987-6789', 'Sales@EasternTreasures.com.in', 'Deepinder');

INSERT INTO STORE(StoreName, City, Country, Phone, Fax, EmailAddress, Contact)
VALUES ('European Imports', 'New York City', 'United States', '800-432-8766', '800-432-8769', 'Sales@EuropeanImports.com.sg', 'Marcello');

/* PURCHASE_ITEM */
DBCC CHECKIDENT ('PURCHASE_ITEM', RESEED, 495);
INSERT INTO PURCHASE_ITEM(StoreID, PurchasingAgentID, PurchaseDate, ItemDescription, Category, PriceUSD)
VALUES ('1050', '101', '12/10/2017', 'Antique Large Bureaus', 'Furniture', '13415');

INSERT INTO PURCHASE_ITEM(StoreID, PurchasingAgentID, PurchaseDate, ItemDescription, Category, PriceUSD)
VALUES('1050', '102', '12/12/2017', 'Porcelain Lamps', 'Lamps', '13300');

INSERT INTO PURCHASE_ITEM(StoreID, PurchasingAgentID, PurchaseDate, ItemDescription, Category, PriceUSD)
VALUES('1200', '104', '12/15/2017', 'Gold Rim Design China', 'Tableware', '38500');

INSERT INTO PURCHASE_ITEM(StoreID, PurchasingAgentID, PurchaseDate, ItemDescription, Category, PriceUSD)
VALUES('1200', '104', '12/16/2017', 'Gold Rim Design Serving Dishes', 'Tableware', '3200');

INSERT INTO PURCHASE_ITEM(StoreID, PurchasingAgentID, PurchaseDate, ItemDescription, Category, PriceUSD)
VALUES('1050', '102', '4/7/2018', 'QE Dining Set', 'Furniture', '14300');

INSERT INTO PURCHASE_ITEM(StoreID, PurchasingAgentID, PurchaseDate, ItemDescription, Category, PriceUSD)
VALUES('1100', '103', '5/18/2018', 'Misc Linen', 'Linen', '88545');

INSERT INTO PURCHASE_ITEM(StoreID, PurchasingAgentID, PurchaseDate, ItemDescription, Category, PriceUSD)
VALUES('1000', '103', '5/19/2018', 'Large Masks', 'Decorations', '22135');

INSERT INTO PURCHASE_ITEM(StoreID, PurchasingAgentID, PurchaseDate, ItemDescription, Category, PriceUSD)
VALUES('1100', '104', '5/20/2018', 'Willow Design China', 'Tableware', '147575');

INSERT INTO PURCHASE_ITEM(StoreID, PurchasingAgentID, PurchaseDate, ItemDescription, Category, PriceUSD)
VALUES('1100', '104', '5/20/2018', 'Willow Design Serving Dishes', 'Tableware', '12040');

INSERT INTO PURCHASE_ITEM(StoreID, PurchasingAgentID, PurchaseDate, ItemDescription, Category, PriceUSD)
VALUES('1150', '102', '6/14/2018', 'Woven Goods', 'Decorations', '1200');

INSERT INTO PURCHASE_ITEM(StoreID, PurchasingAgentID, PurchaseDate, ItemDescription, Category, PriceUSD)
VALUES('1150', '101', '6/16/2018', 'Antique Leather Chairs', 'Furniture', '5375');

INSERT INTO PURCHASE_ITEM(StoreID, PurchasingAgentID, PurchaseDate, ItemDescription, Category, PriceUSD)
VALUES('1100', '104', '7/15/2018', 'Willow Design Serving Dishes', 'Tableware', '4500');

INSERT INTO PURCHASE_ITEM(StoreID, PurchasingAgentID, PurchaseDate, ItemDescription, Category, PriceUSD)
VALUES('1000', '103', '7/17/2018', 'Large Bureau', 'Furniture', '9500');

INSERT INTO PURCHASE_ITEM(StoreID, PurchasingAgentID, PurchaseDate, ItemDescription, Category, PriceUSD)
VALUES('1100', '104', '7/20/2018', 'Brass Lamps', 'Lamps', '1200');


/* SHIPPER */

DBCC CHECKIDENT ('SHIPPER', RESEED, 0);
INSERT INTO SHIPPER(ShipperName, Phone, Fax, EmailAddress, Contact)
VALUES('ABC Trans-Oceanic', '800-234-5656', '800-234-5659', 'Sales@ABCTransOceanic.com', 'Jonathan');

INSERT INTO SHIPPER(ShipperName, Phone, Fax, EmailAddress, Contact)
VALUES('International', '800-123-8898', '800-123-8899', 'Sales@International.com', 'Marylin');

INSERT INTO SHIPPER(ShipperName, Phone, Fax, EmailAddress, Contact)
VALUES('Worldwide', '800-123-4567', '800-123-4569', 'Sales@worldwide.com', 'Jose');
 
/* SHIPMENT */

DBCC CHECKIDENT ('SHIPMENT', RESEED, 99);
INSERT INTO SHIPMENT(ShipperID, PurchasingAgentID, ShipperInvoiceNumber, Origin, Destination, ScheduledDepartureDate, ActualDepartureDate, EstimatedArrivalDate)
VALUES('1', '103', '2017651', 'Manila', 'Los Angeles', '10-Dec-17', '10-Dec-17', '15-Mar-18');

INSERT INTO SHIPMENT(ShipperID, PurchasingAgentID, ShipperInvoiceNumber, Origin, Destination, ScheduledDepartureDate, ActualDepartureDate, EstimatedArrivalDate)
VALUES('1', '104', '2018012', 'Hong Kong', 'Seattle', '10-Jan-18', '12-Jan-18', '20-Mar-18');

INSERT INTO SHIPMENT(ShipperID, PurchasingAgentID, ShipperInvoiceNumber, Origin, Destination, ScheduledDepartureDate, ActualDepartureDate, EstimatedArrivalDate)
VALUES('1', '103', '49100300', 'Manila', 'Los Angeles', '05-May-18', '05-May-18', '17-Jun-18');

INSERT INTO SHIPMENT(ShipperID, PurchasingAgentID, ShipperInvoiceNumber, Origin, Destination, ScheduledDepartureDate, ActualDepartureDate, EstimatedArrivalDate)
VALUES('1', '104', '399400', 'Singapore', 'Portland', '02-Jun-18', '04-Jun-18', '17-Jul-18');

INSERT INTO SHIPMENT(ShipperID, PurchasingAgentID, ShipperInvoiceNumber, Origin, Destination, ScheduledDepartureDate, ActualDepartureDate, EstimatedArrivalDate)
VALUES('1', '103', '84899440', 'Lima', 'Los Angeles', '10-Jul-18', '10-Jul-18', '28-Jul-18');

INSERT INTO SHIPMENT(ShipperID, PurchasingAgentID, ShipperInvoiceNumber, Origin, Destination, ScheduledDepartureDate, ActualDepartureDate, EstimatedArrivalDate)
VALUES('1', '104', '488955', 'Singapore', 'Portland', '05-Aug-18', '09-Aug-18', '11-Sep-18');

/* SHIPMENT_ITEM */
INSERT INTO SHIPMENT_ITEM
VALUES('100', '1', '500', '15000');

INSERT INTO SHIPMENT_ITEM 
VALUES('100', '2', '505', '15000');

INSERT INTO SHIPMENT_ITEM 
VALUES('101', '1', '510', '40000');

INSERT INTO SHIPMENT_ITEM 
VALUES('101', '2', '515', '3500');

INSERT INTO SHIPMENT_ITEM 
VALUES('102', '1', '520', '15000');

INSERT INTO SHIPMENT_ITEM 
VALUES('103', '1', '525', '90000');

INSERT INTO SHIPMENT_ITEM 
VALUES('103', '2', '530', '25000');

INSERT INTO SHIPMENT_ITEM 
VALUES('103', '3', '535', '150000');

INSERT INTO SHIPMENT_ITEM 
VALUES('103', '4', '540', '12500');

INSERT INTO SHIPMENT_ITEM 
VALUES('104', '1', '545', '12500')

INSERT INTO SHIPMENT_ITEM 
VALUES('104', '2', '550', '5500');

INSERT INTO SHIPMENT_ITEM 
VALUES('105', '1', '555', '4500');

INSERT INTO SHIPMENT_ITEM 
VALUES('105', '2', '560', '10000');

INSERT INTO SHIPMENT_ITEM 
VALUES('105', '3', '565', '1500');

/* SHIPMENT_RECEIPT */
INSERT INTO SHIPMENT_RECEIPT(ShipmentID, PurchaseItemID, ReceivingAgentID, ReceiptDate, ReceiptTime, ReceiptQuantity, isReceivedUndamaged, DamageNotes)
VALUES('100', '500', '105', '17-Mar-18', '10:00 AM', '3', '1', NULL);

INSERT INTO SHIPMENT_RECEIPT(ShipmentID, PurchaseItemID, ReceivingAgentID, ReceiptDate, ReceiptTime, ReceiptQuantity, isReceivedUndamaged, DamageNotes)
VALUES('100', '505', '105', '17-Mar-18', '10:00 AM', '50', '1', NULL);

INSERT INTO SHIPMENT_RECEIPT(ShipmentID, PurchaseItemID, ReceivingAgentID, ReceiptDate, ReceiptTime, ReceiptQuantity, isReceivedUndamaged, DamageNotes)
VALUES('101', '510', '105', '23-Mar-18', '3:30 PM', '100', '1', NULL);

INSERT INTO SHIPMENT_RECEIPT(ShipmentID, PurchaseItemID, ReceivingAgentID, ReceiptDate, ReceiptTime, ReceiptQuantity, isReceivedUndamaged, DamageNotes)
VALUES('101', '515', '105', '23-Mar-18', '3:30 PM', '10', '1', NULL);

INSERT INTO SHIPMENT_RECEIPT(ShipmentID, PurchaseItemID, ReceivingAgentID, ReceiptDate, ReceiptTime, ReceiptQuantity, isReceivedUndamaged, DamageNotes)
VALUES('102', '520', '106', '19-Jun-18', '10:15 AM', '1', '0', 'One leg on one chair broken');

INSERT INTO SHIPMENT_RECEIPT(ShipmentID, PurchaseItemID, ReceivingAgentID, ReceiptDate, ReceiptTime, ReceiptQuantity, isReceivedUndamaged, DamageNotes)
VALUES('103', '525', '106', '20-Jul-18', '2:20 AM', '1000', '1', NULL);

INSERT INTO SHIPMENT_RECEIPT(ShipmentID, PurchaseItemID, ReceivingAgentID, ReceiptDate, ReceiptTime, ReceiptQuantity, isReceivedUndamaged, DamageNotes)
VALUES('103', '530', '106', '20-Jul-18', '2:20 AM', '100', '1', NULL);

INSERT INTO SHIPMENT_RECEIPT(ShipmentID, PurchaseItemID, ReceivingAgentID, ReceiptDate, ReceiptTime, ReceiptQuantity, isReceivedUndamaged, DamageNotes)
VALUES('103', '535', '106', '20-Jul-18', '2:20 AM', '100', '1', NULL);

INSERT INTO SHIPMENT_RECEIPT(ShipmentID, PurchaseItemID, ReceivingAgentID, ReceiptDate, ReceiptTime, ReceiptQuantity, isReceivedUndamaged, DamageNotes)
VALUES('103', '540', '106', '20-Jul-18', '2:20 AM', '10', '1', NULL);

INSERT INTO SHIPMENT_RECEIPT(ShipmentID, PurchaseItemID, ReceivingAgentID, ReceiptDate, ReceiptTime, ReceiptQuantity, isReceivedUndamaged, DamageNotes)
VALUES('104', '545', '105', '29-Jul-18', '9:00 PM', '100', '1', NULL);

INSERT INTO SHIPMENT_RECEIPT(ShipmentID, PurchaseItemID, ReceivingAgentID, ReceiptDate, ReceiptTime, ReceiptQuantity, isReceivedUndamaged, DamageNotes)
VALUES('104', '550', '105', '29-Jul-18', '9:00 PM', '5', '1', NULL);

INSERT INTO SHIPMENT_RECEIPT(ShipmentID, PurchaseItemID, ReceivingAgentID, ReceiptDate, ReceiptTime, ReceiptQuantity, isReceivedUndamaged, DamageNotes)
VALUES('105', '555', '106', '14-Sep-18', '2:45 PM', '4', '1', NULL);

INSERT INTO SHIPMENT_RECEIPT(ShipmentID, PurchaseItemID, ReceivingAgentID, ReceiptDate, ReceiptTime, ReceiptQuantity, isReceivedUndamaged, DamageNotes)
VALUES('105', '560', '106', '14-Sep-18', '2:45 PM', '1', '1', NULL);

INSERT INTO SHIPMENT_RECEIPT(ShipmentID, PurchaseItemID, ReceivingAgentID, ReceiptDate, ReceiptTime, ReceiptQuantity, isReceivedUndamaged, DamageNotes)
VALUES('105', '565', '106', '14-Sep-18', '2:45 PM', '10', '0', 'Base of one lamp scratched');