TABLE OF CONTENTS
CHAPTER 1: SYSTEM SPECIFICATION
SCENARIO AND QUESTION PAPER
SCENARIO
Nowadays, Information Technology is applied in many fields, it helps works done effectively, precise, save time and human resources…, medicine management in hospital is not exception.
Medicine Management System in Hospital is developed to carry out these above criterions. The medicine management will be easier and more professional.
This System is applied in Bach Mai hospital- one of the largest hospita
103 trang |
Chia sẻ: huyen82 | Lượt xem: 1404 | Lượt tải: 0
Tóm tắt tài liệu Pharmaceutical Management System in Hospital, để xem tài liệu hoàn chỉnh bạn click vào nút DOWNLOAD ở trên
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._.
Các file đính kèm theo tài liệu này:
- V0124.doc