Project

1. Project • Based on fictitious Coffee Roasting Company • Automatic Order Processing System • Process “projected” • Accept order from customer and validate • Update coffee mix inventory • Schedule production of coffee mixes • Update raw materials inventory (green beans) • Generate purchase order • Process “actual” • Order Fulfillment: Fill and ship, generate Bill of Lading • Production: Update inventories after production • Receiving: Update inventories, generate Payment Voucher

3. External Entities • Customers • 27 restaurants • Some have multiple shipping addresses • Suppliers • 5 wholesalers where we purchase raw green beans • Some have multiple warehouses • Freight Carriers • Use three carriers with rates that are negotiated periodically • OE Clerk • Receives order from customer and enters into system • Receiving Clerk • Receives shipment from supplier and enters into system

5. Enter and Validate Subsystem

7. Validation Checks • The sales order number must be numeric and non-zero • The customer code exists in the customer information file. • The customer shipping code exists in the customer shipping address file. • The delivery date must be at least two days after the order date. • The mix and package codes exists in the mix description file. • The quantity ordered must be numeric, greater than 0 and less than 1000. • The unit price must be numeric, greater than $2.99 and less than $100.00

8. Compute Shipping Date • Look at difference between Order Acceptance and Desired Delivery Date • If 2 days, ship one day before delivery using overnight rate (most expensive) • If 3 days, ship two days before delivery using express rate • If 4 or more, ship three days before delivery using regular rate

10. Q1 • Create T-Accounts

13. Update Mix Inventory • Check the inventory status on the estimated shipping date • Ensure that a sufficient projected quantity-on-hand exists to fill the order. • If an Inventory Status Record exists for the shipping date, the projected due-out and projected quantity-on-hand are updated. • If an inventory status record does not exist, one is created for the shipping date, and the projected due-out and projected quantity-on-hand are derived. • If the projected quantity-on-hand falls below the minimum inventory level for the specified mix code, a Mix Production Request record is created for the day prior to the shipping date. 

15. Q2 • Update Mix Inventory

16. Update Mix Inventory • If the projected quantity-on-hand falls below the minimum inventory level for the specified mix code, a Mix Production Request record is created for the day prior to the shipping date. 

18. Schedule Production • Use the Mix Code and Production date to determine if a batch is available in the Production Schedule file • If a batch is not available, then check all dates before the production date, but after the Order Acceptance date, to determine if a production batch is available • If a batch is available, update the Production Schedule file, write a record to the Scheduled Mix Production data file, and update the due-in status in the Mix Inventory file for the production date. • If a batch is still not available, display a message to decline the order

20. Q3 • Schedule Production

22. Determine Green Bean Requirement • Each variety is comprised of a mix of green coffee beans. • Contained in Mix-Bean-Proportions file

24. Determine Bean Availability • Update inventory file • If at or below reorder point, generate purchase requisition

25. Q4 • Update Bean Inventory

26. Select Supplier and Generate PO • Maintain information about purchases from suppliers

27. Notes • Business Rules regarding shipment • Must ship at least one day prior to delivery • Must produce at least one day prior to ship • If only two days between OA and Delivery, must reject order if need production • Requisition for raw material delivery at least one day prior to production • If assume that the Supplier also needs two days between OA and delivery, how many days are needed between our OA and promised delivery date if we do not have sufficient quantity of raw beans on hand?

28. Order Fulfillment • Pick order • When pick completed • Generate Bill of Lading • Update Mix Inventory File (Actual Out and on-Hand) • If cannot fill order, generate backorder for deficient amount and ship what is available • Generate Backorder Notice for customer

31. Production • After production batch: • Update Actual In and QOH for Mix, Actual Out and QOH for Bean • If there is not sufficient quantity of a coffee bean to produce a batch, then juggle production schedule and mark the “skipped” batch as “Expedite”

32. Receiving • Fill out Receiving Report • Look up corresponding Purchase Order • Derive shipment number • If quantity ordered = quantity received, generate Payment Voucher • Update Actual In and QOH Inventory for Bean • Check for “Expedited” batches requiring this Bean • If sufficient quantity of all required Beans available, generate Expedite Production Notice • Trigger Production, and then check for backorders and trigger order fulfillment

36. First Project • Team Project • Design a normalized relational database for the Order Fullfillment Subsystems of the CRC. • Completion of the Sales Order Confirmation, Bill of Lading and Backorder Notice reports. • Updating the Mix Inventory file.

37. First Project – cont’d • Requirements • Justify your selection of fields for the primary keys, and describe how you know that each table is in third normal form. • List all the assumptions you had to make regarding the nature of the business operations to support your normalized structure.

38. First Project – cont’d • Once you have completed the data modeling, use Access to document the normalized relational database • Create all the third normal form tables, and the relationships between those tables.

39. To Do • Find a friend or two • Send email with team for Project 1 • Team name (no more than 15 characters) • Team members • Thursday – Review Normalization