Pharmaceutical Management System in Hospital

ls in Ha Noi with a thousand patients everyday, so the medicine management is very important. Medicine Management System in Hospital includes five main parts: System Management, List Management, Medicine Expectation and Bidding Management, Medicine Management and Report System. System Management: Each User have their own role assignment made by admin to log-in the system, User have to log-in with a valid user name and password. Besides they can change their password, so the security for system is very good. List Management: This function provides all necessary information about Medicine, Medicine Unit, Medicine Specification, Medicine Classification, Store, Department, Producing Country, Manufacturer, Supplier. User can add new, search, update and delete information in each list. Medicine Expectation and Bidding Management: This part includes two main functions: Yearly Medicine Expectation Management and Medicine Bidding Management. Yearly Medicine Expectation Management: To have the Yearly Medicine Expectation the user must bases on the medicine intake amount and the amount of medicine distributed to the substore and the health insurance store, the amount of the medicine in stock in a year. User who has the role assignment can add new, update, search, and delete the information. Medicine Bidding Management includes: Bidding Document: The hospital sends the bidding document to each supplier. Price Quotation Input: When the supplier received the bidding document they will fill in the document the price of each medicine and send back to the hospital. Bidding Open: After have the price quotation, the hospital will inputs the bidding information of suppliers and then decides which supplier win and get the medicine bidding contract. Bidding Result Announcement: The hospital will announce the bidding result to suppliers Medicine Management: Medicine Management is very important in hospital so this function is given to do all the works related to medicine helps user easier in medicine keeping track and management. Medicine in stock watching and management: The user must keep track of the amount of medicine intake and the amount of medicine distributed to calculate the medicine in stock and how to handle it. Besides the user can make the medicine inventory. Medicine Intake Management: User can add new, view, search, update and delete the medicine intaked from suppliers. Medicine Distribution Management: This includes: Main store distributes to substore and health insurance store. Substore and health insurance store distribute to departments. User also can add new, search, update and delete any information which is available in database. Substore makes the Monthly Medicine Expectation and Requests Medicine: User makes the medicine expectation every month bases on the medicine intaked amount, the amount of Medicine distributed to the department and the amount of the medicine in stock in a month. After that they will request the main store to distribute medicine for the next month. Look up Medicine Information: This part is like a dictionary providing all necessary information about medicine. User can add new, view, search, update, delete any information related to medicine in database. Expired date medicine keeping track: Keeping track of expired date medicine and show an alert when the medicine is near expired date. Discarded Medicine Management: All the expired Medicine or has any problem are discarded. This function also allows to add new, view, search, update and delete the discarded Medicine information. Returned Medicine Management: This function is to manage the medicine returned to the substore, health insurance store and main store when they are not used, expired date or have any problem. Other Distribution Management: Manage the medicine distributed in the special case. Report System: This system allows user to view and print any information relating to Lists, Medicine, Medicine Expectation and Bidding. QUESTION PAPER Questionnaires are an effective way to survey, conduct information and gathering background information. We have some questions helping for analyzing: How is the framework of organization in Pharmacy Department? The framework of organization in Pharmacy Department includes: Main unit Clinic unit Made-up medicine unit Distribution unit How many stores are there in Pharmacy Department? There are: 1 main store 1 substore 1 health insurance store 1 discarded medicine store about 50 stores of department What medicine’s information need to be considered? The related-to medicine information is: Medicine Unit Medicine Specification Medicine Classification Producing Country Manufacturer Supplier Expired Date … Where are medicine intaken from? The Medicine is intaked from Suppliers after organizing bid every one year. Made-up Medicine Unit. How does Bidding process take place? There are 4 steps: Make the bidding document and invite bid to suppliers Suppliers send back the price quotation to hospital Open Bidding to evaluate the bidding package Announce bidding result to suppliers Which Store is responsible for medicine expectation? The main store will make the medicine expectation bases on the amount of medicine intake and distribution as well as the medicine in stock. After bidding, how long does hospital request for medicine? Based on the medicine requirement of substore, main store make monthly expectation and call medicine of the winning supplier. In which case may the medicine be returned? Medicine returned when it is expired date, broken, or have any problem… Which report related to medicine? The Staff must send the report related to medicine to the leaders of hospital every day, week, month, year or any time they require. They are: Medicine intake report Medicine distribution report Medicine in stock report Medicine inventory report Yearly expectation report Discarded medicine report Returned medicine report Who will use this system? Only Administrator and the Delegated User can use this system. Administrator is responsible for assign role to Delegate User and has the full control to the system. Delegate user is the staff of the hospital who directly use this system. BACKGROUND We are living in the era of information technology, so the applications of IT are very commonly used. Medicine Management System software is developed used by computer instead of the traditional manual management which is less efficient. Bach Mai Hospital has been treated and cured for a large number of patients so the medicine management in the traditional method is becoming overload. Our software program is an efficient solution for medicine management in hospital. Medicine Management System in Hospital is developed follows the real time method which helps the updating information fastest, most convenient and most complete at any time. This software program has the friendly interface, easy to use. Furthermore, it helps hospital in understanding the situation fast, reporting, minimizing the using of papers by exchanging information between the departments through the Lan network. Pharmaceutical Management System software is developed using the newest programming language of Oracle Developer 6i( Form Builder, Report Builder) and the powerful database management system Oracle SQL*PLUS 8i. INFRASTRUCTURE This system is based on LAN with the Database Server Figure 1.1: Infrastructure of Pharmaceutical Management System in Hospital FUNCTION HIERARCHY DIAGRAM Figure 1.2: The Functional Hierarchy Diagram of Pharmaceutical Management System In Hospital CONTEXT DIAGRAM Figure 1.3: The context diagram of Pharmaceutical Management System In Hospita DATA FLOW DIAGRAM DFD Level 0 for Pharmaceutical Management System in Hospital Figure 1.4: DFD Level 0 for Pharmaceutical Management System In Hospital DFD Level 1 for System Management Figure 1.5: DFD Level 1 for System Management DFD Level 2 for User Management of System Management Figure 1.6: DFD Level 2 for User Management of System Management DFD Level 1 for List Management Figure 1.7: DFD Level 1 for List Management DFD level 2 of List Management Figure 1.8: DFD Level 2 of List Management DFD Level 1 for Medicine Expectation and Bidding Management Figure 1.9: DFD Level 1 for Medicine Expectation and Bidding Management DFD Level 2 for Yearly Expectation Management Figure 1.10: DFD Level 2 for Yearly Expectation Management DFD Level 2 for Bidding Management Figure 1.11: DFD Level 2 for Bidding Management DFD Level 3 for Bidding Document of Bidding Management Figure 1.12: DFD Level 3 for Bidding Document of Bidding Management DFD Level 3 for Price Quotation Input of Bidding Management Figure 1.13: DFD Level 3 for Price Quotation Input of Bidding Management DFD Level 3 for Bidding Open of Bidding Management Figure 1.14: DFD Level 3 for Bidding Open of Bidding Management DFD Level 3 for Bidding Result Announcement of Bidding Management Figure 1.15: DFD Level 3 for Bidding Result Announcement of Bidding Management DFD Level 1 for Medicine Management Figure 1.16: DFD Level 1 for Medicine Management DFD Level 2 for Medicine in Stock Keeping Track and Management of Medicine Management Figure 1.17: DFD Level 2 for Medicine in Stock Keeping Track and Management of Medicine Management DFD Level 3 for Medicine Inventory of Medicine in Stock Keeping Track and Management Figure 1.18: DFD Level 3 for Medicine Inventory of Medicine in Stock Keeping Track and Management DFD Level 2 for Medicine Intake Management of Medicine Management Figure 1.19: DFD Level 2 for Medicine Intake Management of Medicine Management DFD Level 2 for Medicine Distribution Management of Medicine Management Figure 1.20: DFD Level 2 for Medicine Distribution Management of Medicine Management DFD Level 2 for Monthly Medicine Expectation and Request Medicine of Medicine Management Figure 1.21: DFD Level 2 for Monthly Medicine Expectation and Request Medicine of Medicine Management DFD Level 1 for Report System Figure 1.22: DFD Level 1 for Report System METHOD OF INVESTIGATION Data collection method:collecting and obtaining copies of the actual forms and operating documents that are currently used in the system. Survey method: We survey in the Bach Mai hospital to get information for the system. Preliminary Investigation: involves the system’s anylysis and feasibility. It defines what need to be done and scale of the problem that need to be solved in particular issues of the system. Detailed Investigation: Direct interview: The objective of the detailed investigation is to find out the best solution of the system and to make the good plan for our project. Moreover we can gather the necessary information to carry out the phases of the system development lifecycle. We contacted the doctors and staff of the hospital and make an appointment with them. Indirect Interview: We send email to the staff and they reply us the necessary information relating to medical knowledge. Telephone is also a good method for us to make an appointments. SCHEDULE OF PROJECT Figure 1.23: Schedule of Project DATABASE DESIGN THIRD NORMAL FORM Table Medicine Table General Medicine Table Unit Table Specification Table Classifying Table Producing Country Table Manufacturer Table Supplier Table Staff Table Store Table Store Medicine Table Department Table Bidding Expectation Table Bidding Expectation Detail Table Bidding Package Table Bidding Package Detail Table Monthly Expectation Table Monthly Expectation Detail Table Price Quotation Table Price Quotation Detail Table Medicine Intake Table Medicine Intake Detail Table Medicine Request Table Medicine Request Detail Table Medicine Inventory Table Medicine Inventory Detail Table Medicine Discarded Table Medicine Discarded Detail Table Medicine Return Table Medicine Returned Detail Table User Table Funcs Table Usr-Funcs ENTITIES RELATIONSHIP DIAGRAM Figure 1.24: Entities Relationship Diagram for Pharmaceutial Management System in Hospital CHAPTER 2 - PROGRAM SPECIFICATION PROGRAM IDENTIFICATION Project Name: PHARMACEUTICAL MANAGEMENT SYSTEM IN HOSPITAL Programming Language: Oracle Developer 6i Database: Oracle 8i SQL*PLUS Platform: Windows 2000 Advanced Server and Windows XP Design Tools: Microsoft Office Visio 2003 SUPERVISOR: MSc. PHAM QUANG HAI DEVELOPER: Project Group 21, Batch 7 of HUT - Genetic Singapore Name: Reg.No NGUYEN THI HONG HOA 0701 - 022 - 0027 NGUYEN TUAN MANH 0701 - 022 - 0042 NGUYEN PHUONG THANH 0701 - 022 - 0058 NGUYEN THI THUY 0701 - 022 - 0065 Program Size: Start Date: December 2006 End Date: May 2007 PROGRAM DESCRIPTION Overview: Pharmaceutical Management System in Hospital is developed with the aim to solve many difficult in medicine management. This software is support the nurses and doctors in medicine management efficiently. Beside, this system support administrator to manage users, assign role for each user to perform a certain function such as manage medicine in stores, make a medicine expectation or give a report. This system is developed by Oracle Developer 6i (Form builder, Report builder) and database of Oracle 8i. Advantage of using the system Security using Username and Password authentication Oracle help to save a huge data and execute command fast and effectively Used for a large system and organization Friendly GUI based on well-designed Oracle Form and Report Role-based security using a fixed principal for role authorization Easy and save Backup database and easy manipulation How the program works The operation starts with a staff click on icon of software system, the main form of program will display. Unregistered staffs are able to view general information such as:general medicine information, staff information. Registered staffs log in system can manipulate with functions of system that they are granted permission. Admin has full control in managing the system Delegated user a staff in the hospital to whom admin assigns role for performing a certain function of the system Supplier provides medicine to the hospital Staff is a person (may be a nurse, a doctor or leader of the hospital) who will make request (about the quantity of medine in a receipt or about report…) PROGRAM OUTLINE Input Table Process Output Staff_ID, Dept_ID, Staff_Code, Staff_Name, Birth_Date, Sex,Position, Staff_Address Staff Add Update Delete Search View - Display Medicine Information - Report about Medicine Intaked - Report about Medicine Requested - Report about Medicine Inventory - Report about Medicine in Stock - Report about Medicine Discarded - Report about Medicine Returned - Report about Expired Date Medicine - Display Price Quotation Information - Display Monthly Expectation Information - Display Bidding Expectation Dept_ID, Dept_Code, Dept_Name, Dept_Parent_ID, Dept_Address, Dept_Type Department Supplier_Code, Supplier_Name, Supplier_Address, Phone_Number, Fax, Website, Email, Delegate, Note, Abbreviate_Name, Decision, Licence, Tax_Code Supplier Unit_Code , Unit_Name Unit Classifying_Code, Classifying_Name, Parent Classifying Specification_Code, Specification_Name Specification Manufacture_Code, Manufacture_Name Manufacturer Store_Code, Dept_ID, Store_Name, Category, Health_Insurance, Intake, Distribute Store Medicine_Code, Store_Code, Stock_Amount, FIFO_Amount, Total_Curency, Use Store_Medicine Medicine_Code, General_Code, Country_Code, Medicine_Name, Use, Manufacture_Code, Effect_Code, Specification_Code Medicine General_Code, International_Name, Effectively, Unit_Code, Classifying_Code General_Medicine Bidding_Expectation_ID, Classifying_Code, Year, Expectation_Staff_ID, Expectation_Date, Note Bidding_Expectation Bidding_Expectation_ID, Medicine_Code, Stock_Amount, Intake, Distributed, Expectation_Amount Bidding_Expectation_ID Bidding_Package_ID, Year, Bidding_Package_Name, Open_Date, Council, Opinion, Chairman, Chief_Pharmaceutical, Chief_Synthetic_Plan, Chief_Accountant, Chief_Inspection_Section, Deputy_1, Deputy_2, Deputy_3, Deputy_4 Bidding_Package Bidding_package_ID, General_Code, Expectation_Amount Bidding_Package_Detail Monthly_Expectaion_ID, Expectation_Date, Year, Month, Classifying_Code, Expectation_Staff_ID, State, Note Monthly_Expectation Monthly_Expectation_ID, Medicine_Code, Stock_Amount, Intake, Distributed, Expectation_Amount Monthly_Expectation_Detail Price_Quotation_ID, Bidding_Package_ID, From_Date, To_Date, Supplier_Code Price_Quotation Price_Quotation_ID, Medicine_Code, Unit_Price, Expired_Date, Visa_YS, Note, Amount, Specification_Code, Quality_GMP, Speciality, Import_Licence, Prestige, Mark, Successful_YN Price_Quotation_Detail Medicine_Discarded_ID, Discarding_Date, Discarding_Staff_ID, Store_Code, Note Medicine_Discarded Medicine_Discarded_ID, Order_Number, Medicine_Code, Amount, Reason, Lot_Number, Expired_Date Medicine_Discarded_Detail Medicine_Intaked_ID, Store_Code, Intake_Date, Voucher_Number, Supplier_Code, Delegate, Intake_Staff_ID, Discount_Percent, Discount_Currency, Pay, Distribute_Store, Distribute_Staff_ID, Table_ID, Invoice_Date Medicine_Intaked Medicine_Intaked_ID, Order_Number, Medicine_Code, Unit_Price, Expired_Date, Amount, Vat, Discount_Amount, FIFO_Amount, Lot_Number, Total_Currency Medicine_Intaked_Detail Medicine_Inventory_ID, Store_Code, Inventory_Date, Staff1_ID, Staff2_ID, Note, State, Medicine_Intake_ID, Other_Distributed_ID Medicine_Inventory Medicine_Inventory_ID, Order_Number, Medicine_Code, Amount, Inventory_Amount Medicine_Inventory_Detail Medicine_Request_ID, Export_Store, Request_Store, Request_Staff_ID, Distribute_Staff_ID, Request_Date, Distribute_Date, Classifying_Code, State, Type Medicine_Request Medicine_Request_ID, Order_Number, Medicine_Code, Distribute_Amount, Request_Amount Medicine_Request_Detail Medicine_return_ID, Return_Date, Return_Staff_ID, Return_Store, Receipt_Staff_ID, Receipt_Store, Note, State, Reason Medicine_Return Medicine_Return_ID, Order_Number, Medicine_Code, AmountReason Medicine_Return_Detail PROGRAM SCHEDULE This project started in December 2006 and ended on 15th May 2007, the project implementation is divided into 5 stages as shown in the following figure: System Analysis (1 month) System Design (25 days) Coding (30 days) Testing, Maintenance and Documentation (20 days) Preliminary Investigation (22 days) PROGRAM ESTIMATION The cost may be estimated for doing this project as following: Items Price Hardware $12,000 Software $1,200 Power Supply $300 Other $2,000 Total $15,000 Sua lai chap ter so CHAPTER 3: INPUT SPECIFICATION FILE SPECIFICATION The database management system stored in the file name is PMIH. In this file contain the tables which have same fields in each table such as ID, Address, DOB, Name…but data is different. When the management program need the information in the database manage access system, the system will supply the appropriate data RECORD SPECIFICATION Table Staff Field Name Data Type Width Primary Key Foreign Key Description Staff_ID Number 6 PK ID of Staff Dept_ID Number 6 FK ID of Department Staff _Code Varchar2 10 Code of Staff Staff_Name Varchar2 50 Name of Staff Birth_Date Date Birth_date of Staff Sex Varchar2 1 Sex of Staff Position Varchar2 50 Position of Staff Staff_Address Varchar2 50 Address of Staff Table Department Field Name Data Type Width Primary Key Foreign Key Description Dept_ID Number 6 PK ID of Department Dept_Code Varchar2 4 Code of Department Dept_Name Varchar2 50 Name of Department Dept_Parent_ID Number 6 ID of Department_Parent Dept_Address Varchar2 50 Address of Department Dept_Type Varchar2 2 Type of Department Table Supplier Field Name Data Type Width Primary Key Foreign Key Description Supplier_Code Varchar2 6 PK Code of Supplier Supplier_Name Varchar2 50 Name of Supplier Supplier_Address Varchar2 200 Address of Supplier Phone_Number Varchar2 30 Phone Number of Supplier Fax Varchar2 12 Fax of Supplier Website Varchar2 30 Website of Supplier Email Varchar2 30 Email of Supplier Delegate Varchar2 50 Delegate of Supplier Note Varchar2 200 Note about Supplier Abbreviate_Name Varchar2 20 Abbreviate Name of Supplier Decision Varchar2 1 License Varchar2 1 License of Supplier Tax_Code Varchar2 14 Tax Code of Supplier Table Unit Field Name Data Type Width Primary Key Foreign Key Description Unit_Code Varchar2 2 PK Unit Code of Medicine Unit_Name Varchar2 20 Unit Name of Medicine Table Classifying Field Name Data Type Width Primary Key Foreign Key Description Classifying_Code Varchar2 6 PK Classifying Code of Medicine Classifying_Name Varchar2 80 Classifying Name of Medicine Table Specification Field Name Data Type Width Primary Key Foreign Key Description Specification_Code Varchar2 6 PK Specification Code of Medicine Specification_Name Varchar2 40 Specification Name of Medicine Table Manufacturer Field Name Data Type Width Primary Key Foreign Key Description Manufacturer_Code Varchar2 4 PK Code of Manufacturer Manufacturer_Name Varchar2 40 Name of Manufacturer Table Store Field Name Data Type Width Primary Key Foreign Key Description Store_Code Varchar2 4 PK Code of Store Dept_ID Number 6 FK ID of Department Store_Name Varchar2 200 Name of Store Category Varchar2 1 Health_Insurance Varchar2 1 Intake Varchar2 1 Distribute Varchar2 1 Table Store Medicine Field Name Data Type Width Primary Key Foreign Key Description Store_Code Varchar2 4 PK,FK Code of Store Medicine_Code Varchar2 13 PK,FK Code of Medicine Stock_Amount Number 12,2 Amount of Medicine in Stock FIFO_Amount Number 12,2 Amount of transastion Total_Currency Number 12 Total payment Use Varchar2 1 Check the use of medicine Table Medicine Field Name Data Type Width Primary Key Foreign Key Description Medicine_Code Varchar2 13 PK Code of Medicine General_Code Varchar2 13 FK General Code of Medicine Country_Code Varchar2 3 FK Medicine_Name Varchar2 50 Name of Medicine Use Varchar2 1 Manufacturer_Code Varchar2 4 FK Code of Manufacturer Effect_Code Varchar2 4 Specification_Code Varchar2 6 FK Specification Code of Medicine Table General _Medicine Field Name Data Type Width Primary Key Foreign Key Description General_Code Varchar2 13 PK General Code of Medicine International_Name Varchar2 50 International Name of Medicine Effectively Varchar2 4000 Unit_Code Varchar2 2 FK Unit Code of Medicine Classifying_Code Varchar2 6 FK Classification Code of Medicine Table Bidding_Expectation Field Name Data Type Width Primary Key Foreign Key Description Bidding_Expectaion_ID Number 10 PK ID of Bidding Expectation Classifying_Code Varchar2 6 Classification_Code of Medicine Year Number 4 Year of Bidding Expectation Expectation_Staff_ID Number 6 ID of Staff Expectation_Date Date Date of Bidding Expectation Note Varchar2 100 Note about Bidding Expectation Table Bidding _Expectation_Detail Field Name Data Type Width Primary Key Foreign Key Description Bidding_Expectaion_ID Number 10 PK, FK ID of Bidding Expectation Medicine_Code Varchar2 13 PK, FK Code of Medicine Stock_Amount Number 8 The Amount of Medicine in Stock Intake Number 8 The Amount of Medicine Intaked Distribute Number 8 The Amount of Medicine Distributed Expectation_Amount Number 8 The Amount of the Medicine Expectaion Table Bidding_Package Field Name Data Type Width Primary Key Foreign Key Description Bidding_Package_ID Number 10 PK ID of Bidding Package Year Number 4 Year of Bidding Package Bidding_Package_Name Varchar2 100 Name of Bidding Package Open_Date Date Open Date of Bidding Package Council Varchar2 1000 Council of Bidding Package Opinion Varchar2 1000 Opinion of Bidding Package Chairman Varchar2 50 Chairman of Bidding Package Chief_Pharmaceutics Varchar2 50 Chief of Pharmaceutics Chief_Synthetic_Plan Varchar2 50 Chief of Syntheic Plan Chief_Accountant Varchar2 50 Chief Accountant Chief_Inspection_Section Varchar2 50 Chief of Inspection Section Deputy_1 Varchar2 50 Deputy of Chief_Pharmaceutics Deputy_2 Varchar2 50 Deputy of Chief_Synthetic_Plan Deputy_3 Varchar2 50 Deputy of Chief_Accountant Deputy_4 Varchar2 50 Deputy of Chief_Inspection_Section Table Bidding_Package_Detail Field Name Data Type Width Primary Key Foreign Key Data Type Bidding_Package_ID Number 10 PK, FK ID of Bidding Package General_Code Varchar2 13 FK General Code of Medicine Expectation_Amount Number 8 Amount of Medicine Expectation Table Monthly_Expectation Field Name Data Type Width Primary Key Foreign Key Description Monthly_Expectation_ID Number 10 PK ID of Monthly Expectation Expectation_Date Date Date of Expectation Year Number 4 Year of Expectation Month Number 2 Month of Expectation Classifying_Code Varchar2 6 Classification Code of Medicine Expectation_Staff_ID Number 6 ID of Staff State Varchar2 1 Note Varchar2 200 Note about Monthly Expectation Table Monthly_Expectation_Detail Field Name Data Type Width Primary Key Foreign Key Description Monthly_Expectation_ID Number 10 PK, FK ID of Monthly Expectation Medicine_Code Varchar2 13 PK, FK Code of Medicine Stock_Amount Number 8 Amount of Medicine in Stock Intake_Amount Number 8 Amount of Medicine Intaked Distribute_Amount Number 8 Amount of Medicine Distributed Expextation_Amount Number 8 Amount of Medicine Expectation Table Price_Quotation Field Name Data Type Width Primary Key Foreign Key Description Price_Quotation_ID Number 10 PK ID of Price Quotation Bidding_Package_ID Number 10 FK ID of Bidding Package Supplier_Code Varchar2 6 FK Code of Supplier From_Date Date Price quotation from date To_Date Date Price quotation to date Table Price_Quotation_Detail Field Name Data Type Width Primary Key Foreign Key Description Price_Quotation_ID Number 10 PK, FK ID of Price Quotation Medicine_Code Varchar2 13 PK, FK Code of Medicine Unit_Price Number 10,2 The price of medicine Expired_Date Date 10 Expiry date of medicine Visa_YN Varchar2 1 Visa or not Note Varchar2 100 Further info Amount Number 8 The quantity of medicine Specification_Code Varchar2 6 Package form of medicine Quality_GMP Number 1 Quality for bidding Speciality Number 1 professional Import_License Varchar2 1 The license for import Prestige Number 2 Marks Number 3 Mark of bidding Successful_YN Varchar2 1 Win bidding or not Table Medicine_Discarded Field Name Data Type Width Primary Key Foreign Key Description Medicine_Discarded_ID Number 10 PK ID of Discarded Medicine Discard_Date Date 12 Date of Medicine Discarded Discard_Staff_ID Number 6 ID of Staff who discard Medicine Discard_Store_Code Varchar2 4 FK Code of Store Note Varchar2 50 Note about discarded Medicine Table Medicine_Discarded_Detail Field Name Data Type Width Primary Key Foreign Key Description Medicine_Discarded_ID Number 10 PK, FK ID of Discarded Medicine Order_Number Number 3 PK Order_Number of Medicine Discarded Medicine_Code Varchar2 13 FK Code of Medicine Discarded Amount Number 8 Amount of Medicine Discarded Reason Varchar2 50 Reason of Medicine Discarded Lot_Number Varchar2 20 Lot Number of Medicine Discarded Expired_Date Date Expired Date of Medicine Table Medicine_Intaked Field Name Data Type Width Primary Key Foreign Key Description Medicine_Intaked_ID Number 10 PK ID of Intaked Medicine Store_Code Varchar2 4 FK Code of Store Intake_Date Date Date of Medicine Intaked Voucher_Number Varchar2 20 Invoice Number of Medicine Intaked Supplier_Code Varchar2 6 FK Code of Supplier Delegate Varchar2 50 Delegate of Supplier Intake_Staff_ID Number 6 Staff who receive medicine Discount_Percent Number 3,1 The quantity reduction Discount_Currency Number 12,2 The quantity of money reduction Pay Number 14,2 Payment Distribute_Store Varchar2 4 Store that distribute medicine Distribute_Staff_ID Number 6 Staff who distribute medicine Invoice_Date Date Date of invoice Table Medicine_Intaked_Detail Field Name Data Type Width Primary Key Foreign Key Description Medicine_Intaked_ID Number 10 PK, FK ID of Intaked Medicine Order_Number Number 3 PK Order Number of Medicine Intaked Medicine_Code Varchar2 13 FK Code of Medicine Intaked Unit_Price Number 10,2 Expiry_Date Date Expired Date of Medicine Intaked Amount Number 8 Amount of Medicine Intaked Vat Number 2 Vat of Medicine Intaked Discount_Amount Number 4 Discount Amount of Medicine Intaked FIFO_Amount Number 8 Lot_Number Varchar2 20 Lot_Number of Medicine Intaked Total_Currency Number 14,2 Total Payment of Medicine Intaked Table Medicine Inventory Field Name Data Type Width Primary Key Foreign Key Description Medicine_Inventory_ID Number 10 PK ID of Inventory Medicine Store_Code Varchar2 4 FK Code of Store Inventory_Date Date Date of Inventory Staff1_ID Number 6 Staff Staff2_ID Number 6 Staff Note Varchar2 100 Further info State Varchar2 1 Handle or not Medicine_Intaked_ID Number 10 ID of medicine intake Other_Distribute_ID Number 10 IID of other distributed medicine Table Medicine_Inventory_Detail Field Name Data Type Width Primary Key Foreign Key D._.