University Food Receiving Department Case (D)
Chapters 5 and 6 detailed the work of the receiving clerk using IDEF0 and IDEF1X. Among the tasks she is responsible for are accepting shipments from vendors, creating material lots for storage in inventory, and filling out the receiving report. The management of University Food would like to automate these processes by implementing the database of Figure 5.12. The populated tables of this database are shown in Appendix 6A, and the database is on the Web site supporting this book.
You will design two forms to assist the receiving clerk. The first form will be used for accepting the shipment and creating the material lot. The second form will be used to assign the material lot to a warehouse location.
(a) Develop a form for creating material lots.
When the receiving clerk gets the bill of laden from the truck driver, she performs the following steps:
1. Compare the PO number on the bill of laden with the purchase order in the database.
2. Ensure that the material ID and quantity correspond with a line item of the purchase order.
3. Group the items of the shipment into lots by assigning lot numbers and enter the lot assignments into the material lot table.
Figure CS6.1 is the layout of the proposed form. The form is based on the PURCHASE ORDER table as the master form because the purchase order table is the link to the information that the receiving clerk needs to compare to items on the bill of laden and needs to complete the required fields for the MATERIAL LOT table. For example, the PURCHASE ORDER table has the vendor ID that can be used to retrieve the vendor name and address, which appears on the bill of laden. The purchase order number and vendor name and address from the bill of laden should both agree with what is in the database. The vendor subform information that is linked to the purchase order through the relationship model is shown on the lower left of Figure CS6.1.
The purchase order is also related to the PO detail, which contains line item information on the materials ordered and quantities. This will be needed to compare with the bill of laden. This information is shown on the left side center of Figure CS6.1. Note that navigation buttons are available for navigating among the line items declared within the purchase order number. Finally, the bill of laden refers to the material of the line item by the vendor item code. However, material lots are classified by the material ID system used by University Food. Therefore, it is necessary to cross reference the vendor code with the material ID before entering the latter into the MATERIAL LOT table. The cross reference comes from the VENDOR MATERIAL XREF table. This is shown in the lower right of Figure CS6.1. All of the aforementioned blocks are “read only” and should not be allowed to be other than read only during use. The only block for data entry is the block based on the table MATERIAL_LOT.
The data entry block is on the upper right of Figure CS6.1. These are the data items that will be entered by the receiving clerk. Finally, there is a Close button that should automatically save the data entered on the form to the database, followed by closing the form.
The following sequence outlines the way the receiving clerk uses the form:
1. The receiving clerk reads the PO number on the bill of laden and navigates to that number on the form. Then she checks to see that the vendor associated with that purchase order in the database is the one who appears on the bill of laden.
2. Using the vendor item code that appears on the bill of laden, she navigates through the vendor material cross reference block, which reveals the related material ID.
3. She navigates through the PO detail block to find the line item having that material ID and checks to see that an appropriate line item exists with a remaining balance to be delivered that is equal to or greater than the current shipment.
4. At this point, enough information is available to begin filling out the material lot block.
5. Other data are obtained as follows. The material lot number is the next available sequential number for material lots. The vendor lot number must be taken from the pallets as they come off the truck. The date received is the current date. The quantity received and the quantity on hand is the amount received in the shipment.
Create the receiving report entry form as shown in Figure CS6.1. Remember to link the subforms to the main form using the appropriate foreign keys.
The following data can be used as test data for executing the form. Assume a new shipment has arrived with the following information on the bill of laden.
After entering the test data, check the MATERIAL LOT table to ensure that the information was saved. To use these material lot numbers again, you must first delete the test data from the table.
(b) Develop a form to assign material lots to the warehouse.
After lot numbers are assigned, the receiving clerk looks for available storage in the warehouse. Lots will be assigned to storage locations, and the assignments will be given to the forklift truck operator, who will move the material from the receiving dock to the warehouse. The form that will be used for this purpose is shown as Figure CS6.2. The following steps are involved.
1. The receiving clerk navigates to the lot number that is to be assigned to the warehouse. This is done on the master form based on the table MATERIAL_LOT, as shown on the upper left side of Figure CS6.2.
2. The datasheet view in the subform block on the upper right shows the quantities of the selected material lot by storage location. As Figure CS6.2 indicates, there are no quantities of lot number 1011 currently assigned to the warehouse.
3. The block in the lower right is the data entry block. The receiving clerk navigates to a location that is currently empty and enters the lot number and the amount to be stored. This block is based on the WAREHOUSE_LOCATION table, but is not bound to the master form.
4. After a new record is saved using the Save Record command button, the screen is refreshed using the Refresh command button. This will update the text boxes on the screen to the current values in the database, thus confirming that the data have been properly entered into the database.
The “Current Location of Lots” data sheet should be bound to the master form using the appropriate foreign key. This block and the master block should be read only. The “Refresh” command button is found under the command button category “Form Operations” and the associated action “Refresh Form Data.” It will refresh the data on the screen from the data sources.
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.Read more
Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.Read more
Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.Read more
By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.Read more