Acc3260 In-Class Activity February 2021 Name: (Please print) Group : Fairborn Pharmacy General Description Fairborn Pharmacy is a local independent drug store and the firm uses several application systems that were developed over the years. The owner would like to switch to one (total) database system to improve his business. The pharmacy serves about 2,000 patients, filling approximately 10,000 prescriptions per year. There are four pharmacists: the owner and three associates. They alternate shifts, with two working weekdays, one during the evening hours, and one during the weekend. Basic Operations Patients normally come to the pharmacy in person with prescriptions from their physicians, dentists, or other health practitioners. Most prescriptions can be refilled a specified number of times, as determined by the physician, who writes the number on the original prescription. Any prescription marked *NO REFILLS" cannot be refilled, while one marked "UNLIMITED REFILLS" can be refilled any number of times. Refill requests are often telephoned in by the patient and picked up later. The patient must provide the prescription number (Rx #: a PK) for a refill. The pharmacy has to take special care when filling prescriptions for controlled substances such as tranquilizers. To prevent abuse, periodic reports about the number of such prescriptions are required by various government agencies. Prescriptions for controlled substances can never be refilled. When a prescription is filled, a label and a receipt are printed. The label is attached to the bottle or other container, and the receipt is given to the patient for insurance or tax reporting purposes. To permit third-party (Medicare, Medicaid, and or private insurance plans) billing. the pharmacist must keep track of all prescriptions filled for patients who have health plans or coverage for prescription drugs, so that the third-party payer(s) can be billed according to the contract prices. Some plans require that the patient pay the pharmacy a flat fee for each prescription, others require that the patient pay 20 percent of the cost, while others require no payment. The pharmacy must obtain the customer's signature at the time the drug is issued, so that it can provide proof. if needed, that the drug was actually issued. Information Needs The burden of maintaining several application systems is increasing because the number of prescriptions is growing and customers expect more services than in the past. For example, customers expect the pharmacist to be able to find a prescription record and refill a prescription without providing the prescription number. to do third-party billing, and to check for possible drug interactions with medications they are now taking before filling a prescription. Families and individuals expect to be able to obtain a detailed annual or on-demand report of prescription charges for tax or insurance reporting. To compete with large chains that offer these services, the owner has decided to hire you to design and implement a new accounting (database) system that can provides support for these new services. The database must print several routine reports: 1. Label: Rx Number, Doctor Name, Patient Name, Patient Address, Directions, Drug Name, Form, Strength, Quantity, Pharmacist 's Initials, Date Filled. Original Date, Previous Date, Number of Refills Remaining The Rx Number is issued sequentially by the system. If the prescription is a refill, the Rx Number for the original prescription is used. There are several standard directions, such as "Four times a day" or "Every six hours." If the directions are not one f the standard phrases, the label reads "As directed by physician." Drug Name is the name of the medication. It may be a brand name or a generic name, depending on which is used in filling the prescription. Medications come in several forns, including tablets, capsules, creams, elixirs, and others. The label tells the quantity of medication, which varies with the form (e.g., for capsules or tablets it is the number, but for clixirs it nay be the number of fluid Ounces). The strength of the medication is also given, usually in milligrams. The initials of the pharmacist who actually fills the prescription are included. Date Filled refers to the actual date issued. Original Date is the date of the original prescription, and the Previous Date, if any, is the date of the refill prior to the present one Number of Refills Remaining is decreased by one every time a retill is made, except when unlimited refills are permitted. 2. Receipt: All of the information on the label, plus Price, Amount Due Ilerc, Price is the entire cost of the prescription, while Amount Due is what the patient should pay the drugstore. For patients who do not use third-party billing, these are identical. For those using third-party billing, Amount Due is Price minus the anticipated insurance payment. 3. Insurance Claim: A universal claim form is used for third-party billing. It contains the following information: Patient Name, Patient Address, Phone 4, Insurance Company, Policy Number. Insured Name, Relationship to Insured, Patient Date of Birth, Rx Number, Drug, Form, Strength, Quantity, Date Filled, Price, Amount Paid. Amount Paid here is the same as the Amount Due on the receipt. Note that Insured Name may be different from Patient Name. For example, a child might have coverage under a parent's policy. A claim could be filed for each covered prescription individually, or the pharmacist could submit the claims in batches, at the end of cach weck or other timc period. 4. Family Report: This report, generated as needed, contains the following information: Report Dale. Starting Date. For each member of the family, it shows Patient Address, Phone 1, Last Name, First Name, Date of Birth. For cach prescription for that family member, it lists Rx Number, Dats, Drug, Form, Strength, Quantity, Date Tilled, Price, Amount Paid. The report lists each family member who had prescriptions filled during the period from the Starting Date to the Report Date. For cach member, it lists the information about every prescription filled during the period. 5. Controlled Substance Report: This report is filed monthly or quarterly with agencies that supervise distribution of controlled substances. It contains: Report Date. Starting Date. For cach physician who has written prescriptions for controlled substances, it shows Physician Name, Physician Address. For each prescription from that physician, it lists Rx Number, Patient Name, Patient Address, Drug, Form, Strength, Quantity, Date Filled. The report is in order by Physician Name and lists information about every prescription for controlled substances written by that physician and filled by the pharmacy. 6. Drug Inventory Report: Drug Same, Generic Name, Form, Strength, Controlled Substance, Quantity on Hand, Quantity on Order, Reorder Point, Supplier Name, Supplier Address, Unit Cost. REQUIREMENT: Use the E-R Approach to design an AIS for Fairborn Pharmacy so that they can get all the relevant information concerning the sales cycle. 2 Acc3260 In-Class Activity February 2021 Name: (Please print) Group : Fairborn Pharmacy General Description Fairborn Pharmacy is a local independent drug store and the firm uses several application systems that were developed over the years. The owner would like to switch to one (total) database system to improve his business. The pharmacy serves about 2,000 patients, filling approximately 10,000 prescriptions per year. There are four pharmacists: the owner and three associates. They alternate shifts, with two working weekdays, one during the evening hours, and one during the weekend. Basic Operations Patients normally come to the pharmacy in person with prescriptions from their physicians, dentists, or other health practitioners. Most prescriptions can be refilled a specified number of times, as determined by the physician, who writes the number on the original prescription. Any prescription marked *NO REFILLS" cannot be refilled, while one marked "UNLIMITED REFILLS" can be refilled any number of times. Refill requests are often telephoned in by the patient and picked up later. The patient must provide the prescription number (Rx #: a PK) for a refill. The pharmacy has to take special care when filling prescriptions for controlled substances such as tranquilizers. To prevent abuse, periodic reports about the number of such prescriptions are required by various government agencies. Prescriptions for controlled substances can never be refilled. When a prescription is filled, a label and a receipt are printed. The label is attached to the bottle or other container, and the receipt is given to the patient for insurance or tax reporting purposes. To permit third-party (Medicare, Medicaid, and or private insurance plans) billing. the pharmacist must keep track of all prescriptions filled for patients who have health plans or coverage for prescription drugs, so that the third-party payer(s) can be billed according to the contract prices. Some plans require that the patient pay the pharmacy a flat fee for each prescription, others require that the patient pay 20 percent of the cost, while others require no payment. The pharmacy must obtain the customer's signature at the time the drug is issued, so that it can provide proof. if needed, that the drug was actually issued. Information Needs The burden of maintaining several application systems is increasing because the number of prescriptions is growing and customers expect more services than in the past. For example, customers expect the pharmacist to be able to find a prescription record and refill a prescription without providing the prescription number. to do third-party billing, and to check for possible drug interactions with medications they are now taking before filling a prescription. Families and individuals expect to be able to obtain a detailed annual or on-demand report of prescription charges for tax or insurance reporting. To compete with large chains that offer these services, the owner has decided to hire you to design and implement a new accounting (database) system that can provides support for these new services. The database must print several routine reports: 1. Label: Rx Number, Doctor Name, Patient Name, Patient Address, Directions, Drug Name, Form, Strength, Quantity, Pharmacist 's Initials, Date Filled. Original Date, Previous Date, Number of Refills Remaining The Rx Number is issued sequentially by the system. If the prescription is a refill, the Rx Number for the original prescription is used. There are several standard directions, such as "Four times a day" or "Every six hours." If the directions are not one f the standard phrases, the label reads "As directed by physician." Drug Name is the name of the medication. It may be a brand name or a generic name, depending on which is used in filling the prescription. Medications come in several forns, including tablets, capsules, creams, elixirs, and others. The label tells the quantity of medication, which varies with the form (e.g., for capsules or tablets it is the number, but for clixirs it nay be the number of fluid Ounces). The strength of the medication is also given, usually in milligrams. The initials of the pharmacist who actually fills the prescription are included. Date Filled refers to the actual date issued. Original Date is the date of the original prescription, and the Previous Date, if any, is the date of the refill prior to the present one Number of Refills Remaining is decreased by one every time a retill is made, except when unlimited refills are permitted. 2. Receipt: All of the information on the label, plus Price, Amount Due Ilerc, Price is the entire cost of the prescription, while Amount Due is what the patient should pay the drugstore. For patients who do not use third-party billing, these are identical. For those using third-party billing, Amount Due is Price minus the anticipated insurance payment. 3. Insurance Claim: A universal claim form is used for third-party billing. It contains the following information: Patient Name, Patient Address, Phone 4, Insurance Company, Policy Number. Insured Name, Relationship to Insured, Patient Date of Birth, Rx Number, Drug, Form, Strength, Quantity, Date Filled, Price, Amount Paid. Amount Paid here is the same as the Amount Due on the receipt. Note that Insured Name may be different from Patient Name. For example, a child might have coverage under a parent's policy. A claim could be filed for each covered prescription individually, or the pharmacist could submit the claims in batches, at the end of cach weck or other timc period. 4. Family Report: This report, generated as needed, contains the following information: Report Dale. Starting Date. For each member of the family, it shows Patient Address, Phone 1, Last Name, First Name, Date of Birth. For cach prescription for that family member, it lists Rx Number, Dats, Drug, Form, Strength, Quantity, Date Tilled, Price, Amount Paid. The report lists each family member who had prescriptions filled during the period from the Starting Date to the Report Date. For cach member, it lists the information about every prescription filled during the period. 5. Controlled Substance Report: This report is filed monthly or quarterly with agencies that supervise distribution of controlled substances. It contains: Report Date. Starting Date. For cach physician who has written prescriptions for controlled substances, it shows Physician Name, Physician Address. For each prescription from that physician, it lists Rx Number, Patient Name, Patient Address, Drug, Form, Strength, Quantity, Date Filled. The report is in order by Physician Name and lists information about every prescription for controlled substances written by that physician and filled by the pharmacy. 6. Drug Inventory Report: Drug Same, Generic Name, Form, Strength, Controlled Substance, Quantity on Hand, Quantity on Order, Reorder Point, Supplier Name, Supplier Address, Unit Cost. REQUIREMENT: Use the E-R Approach to design an AIS for Fairborn Pharmacy so that they can get all the relevant information concerning the sales cycle. 2