CASE STUDIES CASE A: Bondstreet Company (Purchasing and receiving processes) The...

CASE STUDIES

CASE A: Bondstreet Company (Purchasing and receiving processes)

The Bondstreet Company sells medical supplies to hospitals, clinics, and doctor’s offices. Bondstreet uses an ERP system for all of its business processes. These supplies are maintained on a real-time basis in an inventory database in an enterprise system. The inventory records include reorder points for all regularly used items and one or two preferred vendors for each item. Vendors are researched and approved by the purchasing manager before being added to the vendor database by a clerk designated to maintain the database. Bondstreet employs the following procedures for purchasing and receiving.

Throughout the day, the supplies clerk receives from the enterprise system an online report listing those items that have reached their reorder point. The clerk reviews the report and creates a purchase requisition by filling out a requisition form in the company’s enterprise system. Each requisition has a unique identifier, and after creation, the purchase requisition data and inventory master data are updated to reflect the purchase requisition. Inventory manager approval is required for purchases over $1,000 and not covered by a blanket order. The inventory manager can log on to the enterprise system any time to look at open purchase requisitions that require approval and to approve those requisitions by checking the acceptance box. The computer records these approvals on the purchase requisition data.

Throughout the day, buyers in the purchasing department receive from the enterprise system online approved requisitions with a list of appropriate vendors. They select a vendor and enter PO data. After the PO is saved, the purchase requisition data and inventory master data are updated. The completed, prenumbered PO is then printed in the purchasing department and mailed to the vendor.

The receiving department inspects and counts the goods when they are received, compares the count to the packing slip, pulls up the PO in the enterprise system, and enters the quantity received. The PO and inventory master data are updated after the receiving record is saved. The general ledger master data is also updated to reflect the increase in the inventory balance.

CASE B: Internet Payment Platform (Purchasing and receiving processes)

The following describes the purchasing and receiving processes at the United States Department of the Treasury’s Bureau of Engraving and Printing (BEP) during a pilot of the Internet Payment Platform (IPP). Components of the IPP include a server with an “appreciating database” located at Xign, Inc. and an Intel server at BEP called the “Enterprise Adapter.” BEP’s mainframe, legacy enterprise system is called BEPMIS, which has an IDMS network database.

Contracting officers (CO) in BEP’s Office of Procurement enter purchase orders (POs) into the BEPMIS purchasing module, where they are stored on the IDMS database. Because the IPP does not accommodate a digitally signed PO and does not include sufficient text to comply with the Federal Acquisition Regulation (FAR), POs are printed by the BEPMIS system, signed by the CO, and mailed to the supplier. A routine on the BEPMIS system, written for the IPP pilot, is manually initiated each evening to extract and format POs for suppliers participating in the pilot. The core PO data (vendor, items, amounts, quantities, etc., but not including additional descriptive and contractual text) are extracted by this routine, as a batch, directly from the IDMS (BEPMIS) database and sent to the enterprise adapter, which converts the PO data from IDMS format into XML, encrypts the batch, and sends it to the IPP server at Xign, where it is stored on the IPP appreciating database. Now a PO record exists on both the IDMS (BEPMIS) database and the IPP appreciating database. After a PO is posted to the IPP database, IPP notifies suppliers via e-mail.

Having been notified that a PO has been issued, an employee at a BEP supplier logs on to IPP and reads the POs onscreen. Depending on the nature of the PO and a supplier’s policies, its employees might be required to wait for the paper PO before beginning the process of providing the goods, or they might be permitted to act on the electronic PO. The supplier sends the goods with a packing slip to BEP, where receiving personnel record the receipt into BEPMIS (no record of the receipt was recorded on the IPP appreciating database). BEPMIS prints a stock notice and it and the goods are sent to the warehouse.

This problem should be completed with database software, such as Microsoft Access. As directed by your instructor, submit the completed database file and the printouts noted below.

1. Using the E-R diagram in Figure 12.10 (pg. 455), select any three or four related entities and implement them as tables using Microsoft Access (or any other database software acceptable to your instructor). Link the tables using the cardinalities shown in the figure. For the tables, you may use attributes shown in Figure 12.11 (pg. 457) or create different attributes.

2. Create forms for each table from part 1 of this problem and use the forms to populate the tables with representative data. Forms should be in good order, readable, and properly formatted. Create at least three records in each table. Print out the populated tables and one instance of each form.

3. Design three queries using the tables from part 1 of this problem. Print out the output of each query and attach an explanation as to why someone would be interested in the output of each query.

4. Using the report function in Access, design a report for each query from part 3 of this problem. Reports should be in good order, readable, and properly formatted. Print out each report.