Pharmaceutical Management System in Hospital

ate_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 Description Medicine_Inventory_ID Number 10 PK,FK ID of Medicine Inventory Order_Number Number 3 PK Order Number of Medicine Inventory Medicine_Code Varchar2 13 FK Code of Medicine Amount Number 10 Amount of medicine in stock Inventory_Amount Number 10 Amount of medicine inventory Table Medicine_Request Field Name Data Type Width Primary Key Foreign Key Description Medicine_Request_ID Number 10 PK ID of Medicine Requested Request_Store Varchar2 4 Store which requests medicine Distribute_Store Varchar2 4 Store which distribute medicine Request_Staff_ID Number 6 ID of Staff who requests medicine Distribute_Staff_ID Number 6 ID of Staff who distribute medicine Request_Date Date Date of Medicine requested Distribute_Date Date Date of Medicine distributed Classifying_Code Varchar2 6 Classifying Code of Medicine State Varchar2 1 Distributed or not Type Number 1 Differ type of store level Table Medicine_Request_Detail Field Name Data Type Width Primary Key Foreign Key Description Requested_Medicine_ID Number 10 PK, FK ID of Medicine Requested Order_Number Number 3 PK Order Number of Medicine Requested Medicine_Code Varchar2 13 FK Code of Medicine Requested Request_Amount Number 8 Amount of Medicine Requested Distributed_Amount Number 8 Amount of Medicine Distributed Table Medicine_Return Field Name Data Type Width Primary Key Foreign Key Description Medicine_Returned_ID Number 10 PK ID of Medicine Returned Return_Date Date Date of Medicine Returned Return_Staff_ID Number 6 ID of Staff who returns medicine Return_Store Varchar2 4 Store which returns medicine Receiving_Staff_ID Number 6 ID of Staff who receive Medicine Receiving_Store Varchar2 4 Store which receive medicine Note Varchar2 100 Note about Medicine Returned State Varchar2 1 Returned or not Reason Number 1 Reason for return Table Medicine_Return_Detail Field Name Data Type Width Primary Key Foreign Key Description Medicine_Returned_ID Number 10 PK,FK ID of Medicine returned Order_Number Number 4 PK Order Number of Medicine Returned Medicine_Code Varchar2 13 FK Code of Medicine Returned Amount Number 8 Amount of Medicine Returned Reason Varchar2 50 Reason of Medicine Returned Table Producing_Country Field Name Data Type Width Primary Key Foreign Key Description Country_Code Varchar2 3 PK Code of Producing Country Country_Name Varchar2 20 Name of Producing Country Table Users Field Name Data Type Width Primary Key Foreign Key Description Usr Varchar2 20 PK User who direct interact with the system Pas Varchar2 10 Password of User Staff_ID Number 10 FK ID of Staff Note Varchar2 200 Note about User Store_Code Varchar2 4 FK Code of Store Table Funcs Field Name Data Type Width Primary Key Foreign Key Description Funcs_ID Number 10 PK ID of Function Funcs_Name Varchar2 200 Name of Function CMD_Menu Varchar2 35 Func_Group Varchar2 2 Group of Function Table Users_Funcs Field Name Data Type Width Primary Key Foreign Key Description Funcs_ID Number 10 PK ID of Function Usr Varchar2 20 Pk User who direct interact with the system P_Insert Varchar2 1 Insert assigment P_Update Varchar2 1 Update assigment P_Delete Varchar2 1 Delete assigment P_Query Varchar2 1 Querry assigment CHAPTER 4: OUTPUT 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, Namebut 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 Look up for Medicine Field Name Table Data Type Width Primary Key Foreign Key Medicine_Code Medicine Varchar2 13 PK Medicine_Name Medicine Varchar2 50 Use Medicine Varchar2 1 Effect_Code Medicine Varchar2 4 General_Code General_Medicine Varchar2 13 FK International_Name General_Medicine Varchar2 50 Effectively General_Medicine Varchar2 4000 Unit_Code Unit Varchar2 2 Unit_Name Unit Varchar2 20 Classification_Code Classification Varchar2 6 Classification_Name Classification Varchar2 80 Specification_Code Specification Varchar2 6 FK Specification_Name Specification Varchar2 40 Country_Code Country FK Country_Name Country Varchar2 Manufacturer_Code Manufacturer Varchar2 4 FK Manufacture_Name Manufacturer Varchar2 40 Supplier_Code Supplier Varchar2 6 Supplier_Name Supplier Varchar2 50 Supplier_Address Supplier Varchar2 200 Phone_Number Supplier Varchar2 30 Fax Supplier Varchar2 12 Website Supplier Varchar2 30 Email Supplier Varchar2 30 Delegate Supplier Varchar2 50 Note Supplier Varchar2 200 Abbreviate_Name Supplier Varchar2 20 Decision Supplier Varchar2 1 License Supplier Varchar2 1 Tax_Code Supplier Varchar2 14 Report of Medicine Intake Field Name Table Data Type Width Primary Key Foreign Key Order_Number Medicine_Intake_Detail Number 3 PK Medicine_Name Medicine Varchar2 50 Unit_Name Unit Varchar2 20 Classifying_Name Classifying Varchar2 6 Amount Medicine_Intake_Detail Number 8 Unit_Price Medicine_Intake_Detail Number 12,2 Vat Medicine_Intake_Detail Number 2 Discount_Amount Medicine_Intake_Detail Number 4 Lot_Number Medicine_Intake_Detail Varchar2 20 Supplier_Name Supplier Varchar2 50 Report of Medicine Distribution Field Name Table Data Type Width Primary Key Foreign Key Request_Date Medicine_Request Date Distribute_Date Medicine_Request Date Request_Staff Staff Varchar2 50 Distribute_Staff Staff Varchar2 50 Order_Number Medicine_Request_Detail Number 3 PK Medicine_Name Medicine Varchar2 50 Unit_Name Unit Varchar2 20 Country_Name Producing_Country Varchar2 20 Request_Amount Medicine_Request_Detail Number 8 Distribute_Amount Medicine_Request_Detail Number 8 Report of Medicine in Stock Field Name Table Data Type Width Primary Key Foreign Key Store_Name Store Varchar2 200 Medicine_Name Medicine Varchar2 50 Unit_Name Unit Varchar2 20 Manufacturer_Name Manufacturer Varchar2 40 Country_Name Producing_Country Varchar2 20 FIFO_Amount Store_Medicine Number 12,2 Distribute_Amount Medicine_Request_Detail Number 8 Stock_Amount Store_Medicine Number 12,2 Report of Medicine Inventory Field Name Table Data Type Width Primary Key Foreign Key Store_name Store Varchar2 200 Inventory_Date Medicine_Inventory Date Staff1_Name Staff Varchar2 50 Staff2_Name Staff Varchar2 50 Order_Number Medicine_Inventory_Detail Number 3 PK Medicine_Name Medicine Varchar2 50 Amount Medicine_Inventory_Detail Number 10 Inventory_Amount Medicine_Inventory_Detail Number 10 Report of Yearly Expectation Field Name Table Data Type Width Primary Key Foreign Key Bidding_Expectation_ID Bidding_Expectation Number 10 Pk Year Number 4 Expectation_Staff Staff Varchar2 50 Expectation_Date Bidding_Expectation Date Medicine_Name Medicine Varchar2 50 Unit_Name Unit Varchar2 20 Classifying_Name Classifying Varchar2 80 Intake_Amount Bidding_Expectation_Detail Number 8 Distribute_Amount Bidding_Expectation_Detail Number 8 Stock_Amount Bidding_Expectation_Detail Number 8 Expectation_Amount Bidding_Expectation_Detail Number 8 Report of Discarded Medicine Field Name Table Data Type Width Primary Key Foreign Key Discarding_Store_Name Store Varchar2 200 Discarding_Date Date Discarding_Staff_Name Staff Varchar2 50 Order_Number Medicine_Discarded_Detail Number 3 PK Classifying_Name classifying Varchar2 80 Medicine_Name Medicine Varchar2 50 Unit_Name Unit Varchar2 20 Country_Name Producing_Country Varchar2 20 Lot_Number Medicine_Discarded_Detail Varchar2 20 Expired_Date Medicine_Discarded_Detail Date Amount Medicine_Discarded_Detail Number 8 Reason Medicine_Discarded_Detail Varchar2 50 Report of Returned Medicine Field Name Table Data Type Width Primary Key Foreign Key Return_Date Medicine_Return Date Return_Store Store Varchar2 200 Receiving_Store Store Varchar2 200 Return_Staff Staff Varchar2 50 Receiving_Staff Staff Varchar2 50 Order_Number Number 4 Medicine_Name Medicine Varchar2 50 Unit_Name Unit Varchar2 20 Country_Name Producing_Country Varchar2 20 Amount Medicine_Return_Detail Number 8 Reason Medicine_Return_Detail Varchar2 50 PRINT LAYOUT CHART Main Form Change Password Login Form CHAPTER 5: PROGRAM DESIGN DECISION TABLE Decision table of Add new procedure Condition Stub Condition Entry 1. Valid input data Y Y Y Y N N N N 2. Unique Data Y Y N N Y Y N N 3. Add Success Y N Y N Y N Y N Action Stub Action Entry 1. Show invalid data message - - - - X X X X 2. Show error message - X X - - - - 3. Show add successful message - X - - - - - - 4. Update table X - - - - - - - Decision table of Update procedure Condition Stub Condition Entry 1. The record is exist Y Y N N 2. valid changing data Y N Y N Action Stub Action Entry 1. Show not found message - - X X 2. Invalid record message - X - - 3. Display the record X - - - 4. Modify the record X - - - 5. Update database X - - - Decision table of Search procedure Condition Stub Condition Entry 1. Searching data is valid Y Y N N 2. The record is exist Y N Y N Action Stub Action Entry 1. Show invalid data message - X - X 2. Show not found message - X - X 3. Display the matched record X - - - Decision table of Delete procedure Condition Stub Condition Entry 1. The record is exist Y Y N N 2. Delete confirmation Y N Y N Action Stub Action Entry 1.Show not found message - - X X 2. Leave database intact - X - - 3. Delete record X - - - Decision table of Print procedure Condition Stub Condition Entry 1. The data is exist Y Y N N 2. User wants to print Y N Y N Action Stub Action Entry 1. Show not found message - - X X 2. Display the required information X X - - 3. Print information X - - - Decision table of Keeping Track of Medicine in Stock Condition Stub Condition Entry 1. Valid input data Y N Action Stub Action Entry 1. Show invalid data message - X 2. Calculate Stock Quantity X - Decision table of Keeping Track of Expired date Medicine Condition Stub Condition Entry 1. Valid input data Y Y N N 2. ED-CD<= P Y N Y N Action Stub Action Entry 1. Show invalid data message - - X X 2. Show near expired date message X - - - Decision table of Bidding Process Condition Stub Condition Entry 1. Valid input data Y Y N N 2. Satisfying requirement Y N Y N Action Stub Action Entry 1. Show invalid data message - - X X 2. Tick the winner column X - - - Decision Table of Estimate Medicine Condition Stub Condition Entry 1. Valid input data Y Y N N 2. FLS=FTS+IA-DA Y N Y N Action Stub Action Entry 1. Show invalid data message - - X X 2. Estimate the expectation amount X - - - FLOWCHART Flowchart for Access Right Figure 5.1: Flowchart for Access Right Flowchart for Add Function Figure 5.2: Flowchart for Add Function Flowchart for Update Function Figure 5.3: Flowchart for Update Function Flowchart for Search Function Figure 5.4: Flowchart for Search Function Flowchart for Delete Function Figure 5.5: Flowchart for Delete Function Flowchart for Report Figure 5.6: Flowchart for Report Flowchart for Keeping Track of Medicine in Stock Figure 5. 7 Flowchart for keeping trackof medicine in stock Flowchart for Keeping Track of Expired date Medicine Figure 5.8 – Flowchart for Keeping Track of Expiry Date Of Medicine Flowchart for Bidding Process Figure 5.9 Flowchart for Bidding Process Flowchart for estimated expectation amount of Medicine Figure 5.10– Flowchartfor Expected Expectation Amount CHAPTER 6: PROGSua so dau chapter RAM LISTING PROGRAM LISTING We will list the most important trigger, functions, procedures in our program Triggers SP_store_medicine This procedure related to manage the amount medicine in store. Create or replace procedure SP_store_medicine (v_store_code varchar2, v_medicine_code varchar2,v_stock_amount number, v_dk varchar2,v_cong number) is v_count Number(10); BEGIN --Check medicine to keep track that medicine have exit or not begin select count(*) into v_count from store_medicine where medicine_code = v_medicine_code and store_code = v_store_code; exception when others then v_count := 0; end; --Decrease medicine that medicine have not exist in store IF v_count = 0 AND v_cong = 0 THEN -Decrease stock amount IF v_dk = 'C' THEN insert into store_medicine (medicine_code ,store_code ,fifo_amount ,stock_amount) values(v_medicine_code ,v_store_code ,(0-v_stock_amount) ,0); -Decrease amount ELSIF V_DK = 'S' THEN insert into store_medicine (medicine_code ,store_code ,fifo_amount ,stock_amount) values(v_medicine_code ,v_store_code ,0 ,(0-v_stock_amount)); -Decrease all ELSIF v_dk = 'K' THEN insert into store_medicine (medicine_code ,store_code ,fifo_amount ,stock_amount) values(v_medicine_code ,v_store_code ,(0-v_stock_amount) ,(0-v_stock_amount)); END IF; --Increase medicine in store when have not medicine ELSIF v_count = 0 AND v_cong = 1 THEN -Increase stock amount IF v_dk = 'C' THEN insert into store_medicine (medicine_code ,store_code ,fifo_amount ,stock_amount) values(v_medicine_code ,v_store_code ,v_stock_amount ,0); -Increase amount ELSIF v_dk = 'S' THEN insert into store_medicine (medicine_code ,store_code ,fifo_amount ,stock_amount) values(v_medicine_code ,v_store_code ,0 ,v_stock_amount); -All ELSIF V_DK = 'K' THEN insert into store_medicine (medicine_code ,store_code ,fifo_amount ,stock_amount) v_medicine_code ,v_store_code ,v_stock_amount ,v_stock_amount); END IF; --Decrease stock amount in stores , medicine code ELSIF v_count > 0 and v_cong = 0 THEN IF v_dk = 'C' THEN -Change stock amount in store Update store_medicine SM set SM.fifo_amount = nvl(SM.fifo_amount,0) - nvl(v_stock_amount,0) WHERE SM.medicine_code = v_medicine_code AND SM.store_code = v_store_code; --Change the real amount ELSIF V_DK = 'S' THEN -Change the real amount UPDATE store_medicine SM SET SM.stock_amount = nvl(SM.stock_amount,0) - nvl(v_stock_amount,0) WHERE SM.medicine_code = v_medicine_code AND SM.store_code = v_store_code; ELSIF V_DK = 'K' THEN -Change the stock amount both of 2 UPDATE store_medicine SM SET SM.fifo_amount= nvl(SM.fifo_amount,0) - nvl(v_stock_amount,0) ,SM.stock_amount = nvl(SM.fifo_amount,0) - nvl(v_stock_amount,0) WHERE SM.medicine_code = v_medicine_code AND SM.store_code = v_store_code; END IF; --Desrease the stock amount in store, medicine_code ELSIF v_count > 0 and v_cong = 1 THEN IF V_DK = 'C' then -Chage the stock amount UPDATE store_medicine SM