Inventory Control Method for Pharmaceutial Industry

General Background

The thesis is about an inventory management system for a pharmaceutical company and an interactive system that stays in touched with its member and how it relates to an organization, improving performance, and achieving objectives for profitability and growth. In any project, the project developer should use an approach to study and solution of problems in a particular field using computer based system.

Pharmacy inventory management system is complex systems that consist of interrelated and interlocking subsystems. Changes in one part of the Inventory system have both anticipated and unanticipated consequences in other part of the system. The project approach is a way of thinking about the analysis and design of computer based application.

It provides a framework for visualizing the organization and environmental factors that operate on a inventory management system. When computer introduced into an organization, various functions and dysfunctions operate on the user as well as the organization. Among the positive consequences are improved performance and a feeling of achievement with quality information.

Among the unanticipated consequences might be (1) possible threats to employees that their work no longer “measures up”, (2) decreased morale of personnel who were not consulted about the installation, and (3) feeling of intimidation by users who have limited training in the new computer. In assessing these consequences, I have tried to alleviating fears and removing barriers for the user is extremely crucial for the Computerized Inventory Management System’s success.

The pharmaceutical sector is growing one in Bangladesh:

Pharmaceutical sector in Bangladesh today symbolizes name – a state of mind. But its journey to the growth and prosperity has been no bed of roses. At present the native pharmaceutical company in Bangladesh is holding the strong leadership position in the pharmaceutical industry of Bangladesh and Further the native Pharmaceutical company of Bangladesh on its way to becoming a high performance global player. This sector plays a vital role in the economical growth of Bangladesh. Many of our native pharmaceutical company export their medicines and other products outside the country. By exporting the products they earn a lot of foreign money that plays an important effect in our country. By utilizing the manpower of Bangladesh this sector can help to solve the un-employments problem in Bangladesh.

The government of Bangladesh has realized the importance of improving the pharmaceutical sector in Bangladesh. For this, the govt. of Bangladesh takes a series of steps to growing this sector.

Competition between Native and Multinational companies in our local market:

After the implementation of the NDP in 1982, nearly 1700 drug formulations were deemed hazardous or of no therapeutic value, and were gradually banned. Owing to the new restrictions, many foreign drugs manufacturers (Roche, Novertis, Sanofi Aventies, Squibb, ICI, GlaxoSmithKline and French) did discontinue their businesses in the country; notably, foreign companies were no longer allowed to manufacture ‘simple’ products, such as antacids and oral vitamin preparations. Moreover, they were not permitted to import or manufacture a number of drugs locally if similar products were already being made in the country.

Under this protectionism, the Bangladeshi pharmaceutical market began to flourish. In 1981, the year before the NDP went into effect, the value of locally produced drugs was about USD 58 million; by 1985, that figure had nearly doubled. Before the drug policy was implemented, the national companies’ share in local production had been about 30 percent; by 1985, this had reached 54 percent, and increased to 67 percent by 1988. By 1991, the top three firms in pharmaceutical sales were all Bangladeshi owned.

At present, the domestic manufacturers own 82 percent of the total market share, followed by 13 percent by the locally based multinational corporations. Only the remaining five percent is imported, thus demonstrating notable national self-sufficiency in pharmaceutical manufacture. Overall, the current market size of the Bangladesh drug industry stands at some USD 500 million – compared with approximately USD 1.2 billion, USD 140 million and USD 116 million respectively for India, Pakistan and Nepal. (Pakistan today has about 400 local pharmaceutical-manufacturing units; Nepal has about 265, of which 40 are considered in good shape).
Against these potentially optimistic figures, however, is one that remains notably daunting: Bangladesh continues to import approximately 80 percent of the raw material required to feed its drug industry, mostly from India and China. As such, the country is forced to spend more than USD 130 million a year on imports of pharmaceutical raw materials. Although promoting local production of basic raw materials was a major objective of the NDP, this aspect has seen failure. The easy availability of pharmaceutical raw materials at competitive prices, coupled with lack of government support and lack of relevant technologies, have discouraged national manufacturers from producing these.

The Manual system loss in inventory management is now 8.9 %

From a statistics of 10 Pharmaceutical companies where 5 of the companies inventory control system is manual and remaining 5 company’s inventory control system is computerized, we find that at present manual system loss in inventory management is 8.9% over computerized inventory management. The main causes for this in manual inventory control system are- wrong order taking, steal of product, wrong information about the inventory, erroneous money collection from customer, under-billing etc due to the paper work.

The importance of computerized inventory management:- The importance of computerized inventory management system in pharmaceutical sector are given below-

Service Efficiency: The computer based system drastically reduces various unnecessary jobs to implement business operation.
Correct Calculation/ Management of Inventory: The computer based system always performs right calculations of the inventory product and their prices. So it is much useful system in the commercial field.
Customer Balance: Computerized system tracks the balance of the customer and can update as required.
Time saving and faster Processing: The computer based inventory management system is much less time consuming to maintain all the records of the products and update them. So, it also takes care to produce all jobs such as purchase, sales and stock faster.
Helps to take quick decisions: The computer based inventory management system provides various kinds of reports to the management. By using these reports, the management can take quick proactive decision about any problems or conditions.
User friendly interface: Computerized system provides an user friendly interface to the user.
Save paper work: Computerized system will reduce the paper work to manage the Inventory System.
Connecting to database so we use different type of queries, data report: Because the data are store into the Oracle 9i database; so we can perform different types of queries and provide various kind of report to the management.
Give facility of different type of inquiry: The Computerized system gives the facilities of different types of inquiry.
Formatted data: We can further work using the data stored into the database.
Data’s are easily approachable: In Computerized system data are easily approachable.

The importance of Interactive System:-

The importance of interactive system in pharmaceutical sector are given below-

Advertising: Pharmaceutical Company can advertise their products and let the subscribed customers (members) to know about any update in their areas-of-interest.
Membership Card: Each and every member will be given membership card which has integrated RFID tag.
Customize Member: While subscribing for the service customer has to select areas-of-interest (for example, Medicine, Chemicals, and insecticide) so that they would be informed if there any updates on those areas.
News feed: The interactive system can provide updates about arrival of new product, promotion, or special discount..
User friendly interface: Interactive system provides an user friendly interface to the user.
Save paper work: Interactive system will reduce the paper work to advertising, products and promotions.
Payment: By using RFID smart card member can pay their bills.

OBJECTIVES OF THE RESEARCH

The objectives to develop an Automated Inventory Management System at different management perspective are described in this Chapter.

Inventory Management: At Inventory management perspective the Automated Inventory Management System will be able to perform the following –

Efficient and Quick information processing: The Automated Inventory management system will provide the information processing for the inventory in a quick and an efficient manner. Main workflow of the product cycle involves –

1. Chalan Receive: This is the first steps of the product cycle in a pharmaceutical company. In this step, the warehouse received chalan of the product from the production department of the company. And stores the product at the warehouse to be sold.

2. Invoice against order: This is the second steps of the product cycle in a pharmaceutical company. In this step, the warehouse received order from the customers. Then prepare an invoice against the order and after that the product is supply to the customers.

3. Return of goods: This step of the product cycle take place, when the customer willing to return the goods that has been delivered to them. In this step, the warehouse receives the returned product from the customer and updates the customer balance and the inventory record.

Calculation of Stock position: The Automated Inventory management system will provide quick and correct calculation of the current stock position such as- number of specific products in the stock, number selling product from the stock, number of required product to the stock.

Calculation of Customer bills: The Automated Inventory management system will provide quick and correct calculation of the customer bills that required to collects money from customer. This system also update customer bills when they order new products or return existing ones.

Field Force Management: At Field management perspective the Automated Inventory Management System will be able to perform the following –

Give them sales target: The automated system has the ability to gives the sales target to the field force (MPO). The sales targets are defined by the management of the company. This is a business policy of the company to sell their products rapidly into the market.

Track of their performance: The proposed automated system has the ability to tracks the performance of the field force. Thus the company can evaluate the performance of any employee of the field force.

Information Repository: The automated inventory management system is as like as a repository of information. By using the information different level of management can take proactive decision at any situation to achieve the company goal. The Information Repository of the automated inventory management system provides three categories of information that are related to the managerial levels and the decision managers make. The following support system of the pharmaceutical company are assisted by the information repository-

DSS(Decision Support System) –The DSS uses strategic information from the information repository of the automated inventory management system which relates to long range planning policies that are of direct interest to upper management. Information such as- trends in financial investment, human resources changes, production strategy changes etc. The information repository of the automated inventory management system acts as the data warehouse for setting up business strategies of the company. Many of the company’s business policies are applied by using the previous experience/data of the company. So the automated inventory management system warehousing the data that are used for setting up business strategies.

MIS (Management Information System) –The second level of information is the managerial information that are also acquired from the information repository of the Automated Inventory Management System. It is of direct use to middle management and department heads for implementation and control. This information assists to generate MIS report of the company that helps to make decision to emphasis the promotion of certain product to boost up its sale.

DPS (Data Processing System) –The third information level is operational information, which is short-term, daily information used to operate the inventory department and enforce the day-to-day rules and regulations of the business. This information includes correct position of stocks and other daily operational information etc that are elicit from the information repository of the Automated Inventory Management System.

The main objective of the interactive system is to stays in touched with its member:-

Members can be informed about the updates in four different ways:-

LCD (Liquid Crystal Display) terminals:- Through LCD terminals located in different locations of the cities, Each LCD terminal will have RFID reader; as member comes in the range of the reader, it retrieves member’s information from the membership card and displays updates on the LCD screen.

Payment using RFID Card:- By using the RFID smart card members can pay their bills from any location of the country.

Company’s Webpage:- Webpage will be updated by the pharmaceutical company’s intend.

E-mail (Electronic Mail):- E-mail will be sent according to member’s preference.

SMS (Short Message Service):- SMS will be sent according to member’s preference.

RECOGNITION OF NEED

Problem of Manual Stock taking

The following problems are the root causes to need for transformation of the manual inventory management system to the Automated Inventory Management System in a pharmaceutical company:

i. Not Synchronized – wrong order taking: In manual inventory management system, the order taking procedure is not synchronized that’s why sometimes wrong has been taken. This problem cause major loss of the company. To solve this problem Automated Inventory Management System is required.

ii. Steal of products: In manual inventory management system, sometimes products are stealing by the dishonest staff of the company. Field force also fraud with the company by sealing the products. To deal with this problem, there is no other ways without Computerized Inventory Management System.

iii. Stock information is wrong: Due to the paperwork of the manual inventory management system, The Depot manager sometimes carry wrong information about the stock. For this, the wrong information of the stock hampers the production cycle of the company problem.

iv. Collection of money from Customer is erroneous: In manual inventory management system, when collectors collect money from the customer sometimes under-billing or over-billing occurs. This problem is the major obstacles to achieve the company goal. To solve this we have to transform the manual inventory system into computerized inventory management system.

v. Inefficient MPO Management: In manual inventory management system, the management can not efficiently handle the Medical Promotion Officer (MPO) of the company. The management can’t evaluate the performance among the MPO. For this reason sometimes several MPO are working hard, but several MPO intentionally avoid the work. To manage the MPO more effectively and efficiently we required Automated Inventory Management System.

vi. Very few reports leading to inefficient MIS: Manual inventory management system provides very few reports to the management that leading to inefficient MIS (Management Information System). For the lack of generating enough reports to management the company can not run well and the following problems are arises-

1. Higher level management doesn’t get consolidated reports frequently: In this case, for the few report generation problem of the manual inventory management system, the higher level management can’t accommodate the DSS (Decision Support System) of the company. For this reason, long term important decision can’t effectively take by the higher level management.

2. Lower level management doesn’t get detailed day-to-day information for control decisions: The manual inventory management system can’t provide efficient report to the lower level management of the company. For this reason the lower level management suffers to control DPS (Data Processing System) of the company.

To overcome the problem of generating very few reports that leading to inefficient MIS of the manual inventory management system, we have to Computerized the company’s inventory management system.

From the above discussion, we have decided that, without Computerized Inventory Management System of the company, we can’t remedy the above problem.

Figure of the manual system

The given figure 3.1 illustrates the vulnerable activities of the manual inventory management system. The following figure focuses on the several problems of the manual inventory management system of a pharmaceutical company that includes:

A lot of working staff required.
Difficulties in determining the current stock status.
Paper works in order tracking
Paper works in the calculation of the customer balance
Paper documents data are not preserved
Difficulties in product returns
Two or more person cross-check the inventory of the product.
Difficulties in determining the current dead-stock status.

Graph of Company’s under growth situation in manual system

The figure 3.2 shows the graph of the ABC pharmaceutical company’s under growth situation in manual inventory management system. The following figure depicts the revenue of the company during the previous year (Established-2009).

The curve illustrates that, at the beginning of the company there was a low revenue return early in its life because of the certain mismanagement of the company. After mismanagements are corrected the company run well with its manual system and returning a high revenue rate.

But after the certain period of time, the rate of revenue are low again and continued, because there exists others competitors in the pharmaceutical sectors. These competitors run their inventory management system in a computerized way. So, to juxtapose the company with these competitors we have to convert the manual inventory management system into computerized inventory management system.

SYSTEM PLANNING AND INITIAL INVESTIGATION

To resolve the company’s dire situation we were invited to develop an automated inventory management system after the following actions have taken-

Determination of the system performance constraints

Statement of constraints:

In manual inventory management system, there are many constraints that reduce the desired performance of the system. These constraints are stated below:-

1. Depot manager is worried about the status of the discount: Depot manager is worried about the status of the discount after returns the products from customer. This constraint provides a greater problem to apply the business policies of the company.

2. Incorrect track of customer balance: Track of the customer balance is very important issues in the inventory management of any pharmaceutical company. The manual system can’t track the customer balance correctly. This constraint hampers the system performance of the company.

3. Product stock does not match the stock in the paper when auditing: When auditing take place, the number of product in the stock doesn’t match the stock in the paper. That causes a greater constraint to achieve the higher system performance.

4. We need to solve these issues by implementing a computerized inventory management system for the company.

Identification of specific system objective

To improve the performance of the existing manual inventory management system of a pharmaceutical company, we have to identify the specific system objective of company. By achieving these objectives the company will performs well in the marketplace. The following are the major identification to improve the performance of the company:

1. Improved product inventory management: By improving the product inventory management the Depot manager will be able to cross-check daily inventory.

2. Cost reduction: The money and time is the main constraint of any system. In many ways – by improving Statement of constraints the management will be able to reduce the cost of the inventory management system.

3. Improved customer service: The customer is the existence of any business organization. So, every business organization should provide the best services to catch their customer. By improving the customer services, the Customers will get better service and they will not be harassed due to misconduct.

To look into the existing business we did the following –

Information gathering about the manual system –

To understand about the details operation of the existing inventory management system, I did the following-

Review of written documents

By reviewing the written documents of ABC Pharmaceutical Ltd such as Chalan pro-forma, Invoice, Customer bill, financial reports, System documents and manuals, Reports and their transaction documents, I have tried to collected many different kinds of information of their business.

On site observation

I observed their activities to understand the practical sequence of tasks adopted by the company. First of all I have carried out a through surveillance of existing Inventory Management System to obtain various details of inventory like sales order, purchase order, customer details, supplier details and item information.

During on-site observation, I have collected the following kind of information from the organization, user staff and the work itself:

Business Policies
Business Goals
Business Objectives
Job functions of inventory department staff
Interpersonal relationships among the staffs.
Work flow of the Inventory management department
Methods and procedures of the Inventory management system.

During On-site observation I have performed the following-

a. Interviews

The employee of the company was enthusiastic in passing valuable information, which helped me a lot while making this project. I have taken a formal interview with the manager and the staff of the existing inventory system department. And I have tried to find the answer of the following questions:

What is the main function of your department?

Manager: We store the items of the company that has been manufactured and supply to the customer according to order.

What is the number of items currently into the stocks?

Manager: It is difficult to answer. I have to count the items of the current stock.

How many items are supplied today?

Manager: Sorry, This will require checking and calculating the today’s delivery info.

What items, when, and how much should require to chalan from factory to the stocks?

Manager: Actually to answer this question, I have to check the current stocks status. This will require a day.

How many people work here?

Manager: Why do you want to know?

It could be that you have too many people on your department.

Manager: May be, I should be judge of that.

I want to talk to the staff at Desk-04.

The Manger reluctantly agrees. The manager walks with me to the clerk’s desk. He is idle. He introduces me to him.

What work do you do Mr. Ahmed (Desk-04 Staff Name)?

Ahmed: I verify the goods against order.

How do you know that the Shipping orders are correct?

Ahmed: I verify the type, number of units ordered and shipping address against the unit produced before they are loaded on the truck.

Thank you Mr. Ahmed for passing the valuable information.

Ahmed: Please No mention it.

Thanks a lot Sir (Manager).

Manager: You are welcome.

After the face to face interview I have carefully noted the answer of my above questions.

b. Questionnaires

To find the facts of the system, I have use the another information gathering tools called questionnaires. I have presents the following Dichotomous Questions:-

Do you personally prefer the automation of the Inventory Management System?

Yes No

In performance of your system are you satisfied?

Yes No

Did the existing system faster enough?

Yes No

Do you think that the computerized system can perform faster?

Yes No

Do you think that the computerized system can reduce costs of your current system?

Yes No

After finding the answer of the above questionnaires form, I have rating scale the answer of these questions.

Info about the company & management

Company ABC, is a Pharmaceuticals production firm whose future depends on its ability to continuously develop and market new products. Company ABC, is concerned about maintaining market for its product. To facilitate the product marketing process, the company creates a department called, appropriately, the Inventory Management Department. The department is responsible for on going management of product marketing, monitoring inventory stock, add new stock, update stock information in daily wise marketing.

To achieve the goal of the business the company director managing certain kinds of projects or portion of projects, such as exploration and development, technology related new business, and new product commercialization. The role of the directors of ABC company is to facilitate, coordinate, and monitors the efforts of various department- Research and development, Application Engineering, Marketing, Manufacturing and Legal.

Info about Products & generics

a. Unit costs: Unit cost is the information about the price of the item per unit i.e.; TK/Tablet or TK/Bottle.
b. Business policies: Business policies are the information about the business offers include discount and other offers that are provided by the management.

c. About Customers with-

District and Thana: The information about the district or thana of the customer.
Type: The information about the type of customer such as- regular, new, corporate etc.
Current Balance: The information about the current balance of the customer.
d. About MPO with-
1. Their respective Thana: The information about the respective thana of the Medical Promotion Officer (MPO).
2. Current target: The information about the current target of selling product for each of the MPO.
3. Status (continuous/discontinuous): The information about the appointment status of the MPO.

Evaluation of the manual process –

As we know the manual processing is quite tedious, time consuming, less accurate in comparison to computerized processing. Obviously the present system is not is exception consultant encountering all the above problems. After the initial investigation of the existing manual system, I have found the following troublesome position of the current system:

1. Time consuming.

2. It is very tedious.

3. All information is not placed separately.

4. Lot of paper work.

5. Slow data processing.

6. Not user-friendly environment.

7. It is difficult to found records due file management system.

8. Two or more people are looking at the stock of medicine at the same time.

Determination of the user requirements

The input and output user requirements of the proposed system are given below:

Input of the system:

The following are the required information that should be inputted to the database of the automated inventory management system:

a. Product name with generic: Input the generic name of the medicine such as- Paracitamol, Levocetirizine, Loratadine, Calcium etc.
1. i. Cost: Product name should input along with their cost per items that means the unit price of the item i.e.; tk/tablet or tk/bottle.
2. ii. Discounts in (%): Input the discount amount of the product for say if buy 100 pcs then free provide 5 pcs.

b. Chalan info: Input the information of the Products that Warehouse receives from Factory.
c. Order Info: Input the order information of the customer that is the customer sought for medicines.
d. Invoice info: Input the invoice information that includes the MPO who actually able to sale.
e. Return info: Input returns information that includes the invoice number against which the return happened.
f. Lost/Broken info: Entry the information of the lost/broken/expired products those are stored in the dead stock.

Output sought from the people:

The following reports or information are required to produces from the computerized inventory management system:

a. Needs identification from different point of view:

The automated inventory management system should produce the information that will require to identifying the needs of the Manager and the Users.

b. User/ Depot manager’s point of view (DPS reports):

From the User/ Depot manager point of view the automated inventory management system should be provides/ performs the following:

Automated Stock & dead stock update: Update of stock information in the automated system when new chalan are received from factory or products are sell to the customers. And also update the dead stock when required.
ii. Customer balance: Producing or updating the balance of the customer when needed.
iii. Daily Sales: Reporting the daily sales status to the user or depot manager.
iv. Daily Collection Report: Reporting the daily-wise collection information.
v. Other Reports

c. Manager’s point of view (MIS reports):

From the manager point of view the automated inventory management system should be provides the following report for MIS:

i. MPO sales target: Generates Reports to the management about the sales target of the MPO.
ii. Monthly/Half-Yearly Sales Status: Generates the monthly half-yearly sales status of the company to MIS.
iii. Market-required Product: Reports the most-required product in the marketplace.
iv. Other Reports

SYSTEM ANALYSIS AND DESIGN

General description of the proposed system

The computerized inventory management system will reduce the vulnerable situation of the manual inventory management system that have already described at the chapter-03 (RECOGNITION OF NEED). Right now I would like to describe the depot floor activities in computerized inventory management system.

The figure 5.1 illustrates the depot floor in the computerized system. In the proposed computerized system, the numbers of staffs are reduced and the total system of depot floor is more simplified. In the computerized inventory management system the depot floor activities of the ABC pharmaceutical company are work in a systematic way. The figure also depicts the reduction of paper work in the computerized system. In this new system, the depot manager controls and monitors the major activities of the system. The data entry operators input the required data to the database.

The service efficiency of the customer is also shown in the figure. The computerized inventory management of a pharmaceutical company remedies the problem of manual inventory management system. In this system, The MPO places the customer order to the data entry operators of the inventory management system. Then the system process order accordingly. The following figure shows the depot floor activities in a computerized inventory management system:

Structured Analysis Using DFD: DFD is a graphical representation of the data. It identifies the path the data will take, what process will take place to it from one from to another. Different types of DFD for the proposed system are given below:

Context Level DFD

The level-0 or context level DFD is shown in figure-5.2. The DFD describes that-

The Inventory Management System receives Chalan from the production factory.
Customer place order to the MPO
MPO receives the order and pass it to the Inventory Management System.
The Inventory Management system supplies products to the customer according to the order.
The collector collects money from the customer.

Level 1 DFD

The level-1 DFD is shown in figure-5.3. The DFD describes that-

In the Chalan process the Warehouse receives Chalan from the production factory and updates their inventory stock.
In the order process the customer place order to the MPO and after process the order the system update the customer balance and after delivery the items to the customer the inventory stocks also updated.
In the return process, the customer returns the products that have been delivered. Thus the inventory stocks and the customer balance should be updated in this process.
In the collection process, the collector collects money from the customer and updates the balance of the customer.

The players: The players of the system with all their properties and their management issues are described below:

1. Depot Manager & Others:

The most important player of the inventory management is the Depot manager, a person whose single, overriding responsibility is to plan, direct, and integrate the work efforts of participants to achieve the goal. He controls and monitors the total events of the inventory management department. He also has the task of keeping track the inventory stock. When the stocks of any specific product are low; he informs it to production department to add new product at the stock.

There is two or more data entry operator whose function is to enter the data into the database when required. There are three or more security guards who check the invoice and match the quantity of the delivered product with the invoice letter. These forces also involve other kinds of security issues. The labor brings product from production department to inventory department and also brings from inventory stock to truck.

Activities:

The Field force section of the workflow of the Inventory system has to perform the following activities:

i. Appointment: Recruiting new People for the organization.

ii. Posting: Transferring People at the different field organization such as- District or Thana Level.

iii. Terminate/ Discontinue: Terminating and discontinuing people as needed. Provide discontinuing facility with the performance of the employee.
2. MPOs

The medical promotion officer has the task of introducing and selling the product on the market place. They produce the catalog of the product to the customer and convince them to buy the products or items. Then they record the product orders of each customer with quantity. And they bring the order record from customer to Depot manager.

3. Products

The product lists involved with the products which are available at the inventory stock. Product lists part contains the following information of the product:

a. Generic: Store the generic name of the medicine such as- Paracitamol, Levocetirizine, Loratadine, Calcium etc.
b. Product: Store the product name provided by th company such as- NAPA, LECET, ORADIN, CALBO etc.
c. Unit Price: Store the unit price of the item i.e.; TK/Tablet or TK/Bottle.

Activities: The activities include:

Add new Product: Adding new product to the database when new product launched.
Price Update: Updating prices into the database when any of the particular product’s prices rises or fall.
4. Customers
1. a. Customer Type: Store the type of customer that is categorized into Retail, Corporate, and Regulars.
2. b. Address: Store the address of the customer that is district, thana, and market.

Activity: The customer activities include:

Balance Calculation: Calculates the current balance of the customer, update balance as required.

Product Management

a. Level 2 DFD of product management:

The level-2 DFD of product management is shown in figure-5.4. The product management sub modules of the inventory management system DFD describe that-

When the company launch new product to the market segment, the adding new products process, add these new products into the system database with their generic and product name.
When the prices of the products are change, then the price update process update the price accordingly.
When the stock of the products in the warehouse is low, then the chalan process receives new chalan from the factory to warehouse. Thus updating the inventory stocks should require.

b. Introduction of New products and generics: The product management sub-modules of the inventory management system involves for adding new product to the database when new product launched.

c. Unit Price: In the product management module, this requires to store the unit price of the item i.e.; TK/Tablet or TK/Bottle.

d. Add/edit product with their price: Adding/edit product to the database when new product launched/ when existing products information change. Updating prices into the database when any of the particular product’s prices rises or fall.

Chalan

a. Level 2 DFD

The level-2 DFD of Chalan sub-modules is shown in figure-5.5. The Chalan sub-modules of the inventory management system DFD describe that-

The warehouse request to intend chalan of products to the factory.
Then the warehouse gets chalan of items from factory.
After receiving the chalan, the warehouse updates inventory stocks.

1. Chalan Number: Each of the chalan has a chalan number that includes the quantity of the products of that chalan.
2. Interactors – Product: In the chalan process, product and warehouse are the main interactors.

Order

a. Level 2 DFD:

The level-2 DFD of Order process is shown in figure-5.6. The Order sub-modules of the inventory management system DFD describe that-

Customer place order to the MPO
MPO passes the order to the warehouse
The warehouse process the order and supply the product to the customer
After supplying the products to the customer the inventory should be updated.

b. Interactors – MPO, customers, products: Here MPO, customers and the products are interact in the order process.
c. Description of the process :

Order (Will be given by MPO – Customer Wise): Take order from customer that will be given by Medical Promotion Officer. This system tracks the customer order and records them into customer wise transaction.

Invoice

a. Level 2 DFD:

The level-2 DFD of Invoice process is shown in figure-5.7. The Invoice of the inventory management system DFD describe that-

To prepare a new invoice, at first the system cross check the invoice record
Then cross check the business policies to be applied according to the customer types (Ordinary or corporate)
Finally prepare the Invoice letter and print to provide customer.

b. Interactors – MOP, customers, products: Here MPO, customers and the products are interact in the Invoice process.

C. Description of the process

Invoice (Against Order): When the products are delivered to the customer according to the customer, the company will provide an invoice letter to the customer against the order

Return

a. Level 2 DFD: The level-2 DFD of Return process is shown in figure-5.8. The Return process of the inventory management system DFD describe that-

The customer request to return products
The system check the invoice record of that product
Then the system checks the business policy record of the return products against the invoice.
After that the system process the return products
Update the balance of the customer since the products has returned.
Update the inventory stocks since the returned products are added to the current stocks.

b. Interactors – MPO, customers, products: In the return process the MPO, Customer, and the products are interacts with each other.

c. Description of the process

Return from Customers: This section of the software perform its operation when the supplied products will return from customer. When the supplied products will return from customer, the following tasks should be performing:-

1. Reorganization of Business Offers: To convince the customer for receiving the items, provide special business offer to them. For this re-organization of business offers are required.
2. Customer Balance Update: If the items has been return from customer then the current balance of the customer need to be update i.e.; debit the customer balance that is equal to the amount of the return products.
3. Update of Inventory: When the return products are added to the inventory the Inventory should be updated.

Collection

a. Level 2 DFD: The level-2 DFD of Collection process is shown in figure-5.9. The Collection process of the inventory management system DFD describe that-

The collector checks the current balance of the customer.
Then the collector pending a request to paid the money.
If the customer willing to pay the money, then the collector cross check the amount according to the invoice record.
After collecting the money the collector update the customer current balance.

b. Interactors – customers, Amount: In the collection process the customers and amount interactions occur.

c. Description of the process

Collections: After selling the products successfully, the amount of the selling product money should be collected by the staff of the company. To collect the money the following should track:

1. Customer Status: Recognize the status of the customer.
2. Daily Sales Collection: Collect the money according to the daily wise sales from the customer.
3. Update of Customer Balance: After collecting the money, update the balance of the customer.

Depot Activity

a. Level 2 DFD: The level-2 DFD of Depot Activity process is shown in figure-5.10. The Depot Activity of the inventory management system DFD describe that-

When the factory request to add new products, the depot manager adds the product to the database and updates the inventory stocks.
When the inventory items are damaged or expired then the depot manager update the inventory and add them to the dead stocks.
Depot manager tracks the information about the current inventory status to reports to the management.
Depot manager updates the inventory and dead stocks as needed.

b. Interactors – Product: In this process the product interacts with the depot manager.

c. Descriptions of Depot Activities:

The Depot manager performs the following activities:

1. Broken/Expired: Depot manager has the responsibility to tracks the broken or damaged or expired products as a dead stocks

2. Dead Stock Update: Depot manager has the responsibility to update the dead stocks when any inventory item has been destroyed.

3. Update of Inventory: Depot manager has the responsibility to update the Inventory record when chalan received from factory or when the products are supplied to the customer.

MPO Target: The management sometimes provides selling target to the MPO to give a competitive conditions among the MPOs. To achieve the target the MPO perform their best. The MPO target includes:

a. Product Target: The target to selling a quantity of products per month by each of the MPO.

b. Amount Target: The target to selling products that are equivalent to a predefined amount per month by each of the MPO.

Major Output of the Software System (Reports):

The proposed software produces the following reports for different level of management according to their needs:

1. Product current Inventory: The Computerized Inventory Management System can generate reports about the current inventory status of the products.

2. Detailed Product Inventory: The Computerized Inventory Management System can generate reports about the detailed product inventory from a certain date to a certain date that includes:

i. Existing Stock: Produces the existing stocks condition of the inventory.

ii. Chalan: Produces reports about when to require Chalan of any items with quantity.

iii. Sold: Produces reports about the total sold products at a given period of time.

iv. Other Spoils: Produces reports about the other spoils.

v. Final Stock: Produces reports about the Final stocks of the inventory.

3. Customer Balance: The Computerized Inventory Management System generates reports about the customer balance such as-10,000 tk or up like this.

4. Product List: The Computerized Inventory Management System can generate reports about the lists of the items in the inventory.

5. MPO List: The Computerized Inventory Management System can generate reports about lists of MPO works in the company.

6. All Lists: The Computerized Inventory Management System can generate reports about any lists that are associated with the inventory management system.

7. MPO Target/ Achievement in a month: The Computerized Inventory Management System can generate reports about target and achievement of the MPO.

8. Customer wise Returns (Against Invoice): The Computerized Inventory Management System generates reports about the customer-wise returns against invoice.

9. Particular Invoice: The Computerized Inventory Management System can generate reports about a particular invoice.

10. Particular Bill against Invoice: The Computerized Inventory Management System can generate reports about a particular bill against invoice.

11. Total Sale (Product – Price): The Computerized Inventory Management System can generate reports about the total sales products in quantity or in amount for a particular period of time.

12. Etc.

IMPLEMENTATION PROCEDURE OF INTERACTIVE SYSTEM

Introduction to Interactive System for Pharmaceutical Company:

ISPhC (Interactive System for Pharmaceutical Company) is a solution containing both hardware and software system that let the pharmaceutical company to advertise their products and let the subscribed customers (members) to know about any update in their areas-of-interest. Subscribed customers or members are those who have understood and agreed to the terms of service. Each and every member will be given membership card which has integrated RFID tag. While subscribing for the service customer has to select areas-of-interest (for example, Medicine, Chemicals, and insecticide etc) so that they would be informed if there are any updates on those areas. Updates could be about arrival of new product, promotion, or special discount. Members can be informed about the updates in four different ways, through LCD terminals located in different locations of the country, webpage, e-mail, and SMS.

Each LCD terminal will have RFID reader; as member comes in the range of the reader, it retrieves member’s information from the membership card and displays updates on the LCD screen. Webpage will be updated by the pharmaceutical company’s intend; e-mail and SMS will be sent according to member’s preference.

“We are living in a world where everyone is one call away, and everything is one click away”, is it really true? Well, the answer is “yes, almost everyone is one call away; but, not everything is one click away”. The world is moving fast, so is the information technology. What is new today, tomorrow that might be in waste-box. Computer technology has been integrated in almost every step of our lives. However, when it comes to pharmaceutical industry we are still following the traditional way (except for the online ordering) where we have to go to warehouse centre and spend half of the day just to find the required products. This is a big waste of time. We could just spend that time chatting with friends, hanging with family or working in office. In addition, sometimes we require going to several pharmaceutical company to find preferred product, which is full of hustles. It would have been better if we could just decide what exactly the MPOs are going to buy, what would be the price and so on before going to the warehouse centre.

This Thinking has been undertaken to design and implement the system called ISPhC. The motto of ISPhC is “Everything is one click away for the Pharmaceutical industry”. ISPhC is destined to save members’ time, reduce hustles, guide them and always stay in touch. It saves time in the sense that members do not require to come to the warehouse centre to check if there any new product, promotion or discount, they will be notified according to their areas-of-interest. It reduces hustle as members can check from website if the product they are looking for is available or not. When registered customers go to the important places of the cities, LCD terminals are there to guide them, in which there is discount or new product has arrived. Weekly newsletter will be sent to all members to keep them in touch.

ISPhC has user friendly interface for the company so that they can update their product list, upload pictures of the products. Company can update their product list by using standard workstation and keyboard or RFID reader. If any customer wants to use chatting system to get online customer service that would be allowed too. In addition, company can add online booking system tool to let the members to book any product, which will be called off according to predefined time limit.

ISPhC has three types of user, these are: members, operators, supporting staffs. Members are the registered customer; Operators are the owners or staffs of the company that reside on the warehouse centre; and supporting staffs are those who manage, control and troubleshoot the system.

The main concern of the system is to make sure that the members always stay up-to-date on their selected areas-of-interest. Members have the flexibility to set when they should be sent updates and anytime they can change areas-of-interest. In gist, ISPhC is interactive system that always communicates with its members.

Areas of research:

Customer Motivating Factors, RFID Technologies, Short Message Service (SMS), Compression Protocols, .NET framework, Active Directory Schema, Network Congestion Control.

apology apology

Objectives

To identify initial system requirements and user requirements and make a project plan.

Activities: Go through IEEE journals, newspaper articles, forums, reviews and whitepapers written on RFID technologies, messaging systems and SMS. Make a list of possible system and user requirements.

Deliverables: Literature review, initial system requirements and user requirements.

To select appropriate Research Methodology, and to finalize Functional and Non-functional System and User Requirements.

Activities: Refering to the lecture notes and Research Methodology Books select either qualitative or quantitative research method. Apply selected research method and instruments. Analyze findings and tabulate normalized data using SPSS. According to findings finalize Functional and Non-functional System and User Requirements.

Deliverables: Documentation on Research Methodology, finalized Functional and Non-Functional System and User Requirements along with tabulated data and graph.

To come up with a system design.

Activities: Considering the Functional and Non-Functional System and User Requirements, designing a model System using Microsoft Visio.

Deliverables: Graphs, diagrams, and documentations.

To develop the complete system.

Activities: Develop the core system using Microsoft Visual Studio IDE. Develop interface for RFID module in .NET framework. Integrate RFID module interface with core system. Modify Microsoft Active Directory Schema according to the requirement. Develop interface for LCD terminal, Develop interface for SMS. Build interface for company staff. Build web-application for web-based users. Build database system which will hold information of the members and products. Integrate all the tools with the core system. Implement messaging service and link with the core system. All the tools will be developed using Microsoft Visual Studio IDE.

Deliverables: Documentation, coding and configuration related to each and every development phases, protocol and software architecture. UML diagrams with explanation.

To evaluate the system, and find out if there is any flaw to be fixed or improvement needed.

Activities: Implement the system and test it several times. Log performance and variations. Analyze the performance log and decide if further improvement is needed.

Deliverables: Tabulated performance log and report.

To upgrade ISPhC if needed and finalize the system.

Activities: Finalizing the system by fixing the errors (if exist) and making improvements if needed. Create installation files and icons for all types of implementation (e.g. peer-to-peer, client-server).

Deliverables: Documentation and report.

Required resources:

Access to libraries, Access to the Internet, Standard Workstation(s), GSM Modem, RFID Reader/Writer, Active/Passive RFID tag, Operating System(s), XML parser, Microsoft Visual Studio, Microsoft Expression, Microsoft Windows SDK, Microsoft Virtual PC, Microsoft Exchange Server, Microsoft Virtual Server.

RFID TECHNOLOGY IN INTERACTIVE SYSTEM

Definition of RFID:

RFID stands for Radio-Frequency IDentification. The acronym refers to small electronic devices that consist of a small chip and an antenna. The chip typically is capable of carrying 2,000 bytes of data or less.

The RFID device serves the same purpose as a bar code or a magnetic strip on the back of a credit card or ATM card; it provides a unique identifier for that object. And, just as a bar code or magnetic strip must be scanned to get the information, the RFID device must be scanned to retrieve the identifying information.

Radio-frequency identification (RFID) is the wireless non-contact use of radio-frequency electromagnetic fields to transfer data, for the purposes of automatically identifying and tracking tags attached to objects. Some tags require no battery and are powered and read at short ranges via magnetic fields (electromagnetic induction). Others use a local power source and emit radio waves (electromagnetic radiation at radio frequencies). The tag contains electronically stored information which may be read from up to several meters away. Unlike a bar code, the tag does not need to be within line of sight of the reader and may be embedded in the tracked object.

RFID tags are used in many industries. An RFID tag attached to an automobile during production can be used to track its progress through the assembly line. Pharmaceuticals can be tracked through warehouses. Livestock and pets may have tags injected, allowing positive identification of the animal.

Application of RFID:

RFID can be used in a variety of applications such as:

Access management
Tracking of goods
Tracking of persons and animals
Toll collection and contactless payment
Machine readable travel documents
Tracking sports memorabilia to verify authenticity
Airport baggage tracking logistics

Working Principle of RFID:

A Radio-Frequency IDentification system has three parts:

A scanning antenna
A transceiver with a decoder to interpret the data
A transponder – the RFID tag – that has been programmed with information.

The scanning antenna puts out radio-frequency signals in a relatively short range. The RF radiation does two things:

It provides a means of communicating with the transponder (the RFID tag) AND
It provides the RFID tag with the energy to communicate.

This is an absolutely key part of the technology; RFID tags do not need to contain batteries, and can therefore remain usable for very long periods of time (maybe decades). The scanning antennas can be permanently affixed to a surface; handheld antennas are also available. They can take whatever shape you need; for example, you could build them into a door frame to accept data from persons or objects passing through.

When an RFID tag passes through the field of the scanning antenna, it detects the activation signal from the antenna. That “wakes up” the RFID chip, and it transmits the information on its microchip to be picked up by the scanning antenna.

In addition, the RFID tag may be of one of two types. Active RFID tag has their own power source; the advantage of these tags is that the reader can be much farther away and still get the signal. Even though some of these devices are built to have up to a 10 year life span, they have limited life spans. Passive RFID tag however, do not require batteries, and can be much smaller and have a virtually unlimited life span. RFID tags can be read in a wide variety of circumstances, where barcodes or other optically read technologies are useless.

The tag need not be on the surface of the object (and is therefore not subject to wear)
The read time is typically less than 100 milliseconds
Large numbers of tags can be read at once rather than item by item.

In essence, that’s how RFID works.

Security and Privacy of RFID Technology:

Anyone with an appropriately equipped scanner and close access to the RFID device can activate it and read its contents. Obviously, some concerns are greater than others. If someone walks by your bag of books from the bookstore with a 13.56 Mhz “sniffer” with an RF field that will activate the RFID devices in the books you bought, that person can get a complete list of what you just bought. That’s certainly an invasion of your privacy, but it could be worse. Another scenario involves a military situation in which the other side scans vehicles going by, looking for tags that are associated with items that only high-ranking officers can have, and targeting accordingly. Companies are more concerned with the increasing use of RFID devices in company badges. An appropriate RF field will cause the RFID chip in the badge to “spill the beans” to whomever activates it. This information can then be stored and replayed to company scanners, allowing the thief access – and your badge is the one that is “credited” with the access.

The smallest tags that will likely be used for consumer items don’t have enough computing power to do data encryption to protect your privacy. The most they can do is PIN-style or password-based protection.

Technical problems with RFID:

Problems with RFID Standards

RFID has been implemented in different ways by different manufacturers; global standards are still being worked on. It should be noted that some RFID devices are never meant to leave their network (as in the case of RFID tags used for inventory control within a company). This can cause problems for companies.

Consumers may also have problems with RFID standards. For example, ExxonMobil’s SpeedPass system is a proprietary RFID system; if another company wanted to use the convenient SpeedPass (say, at the drive-in window of your favorite fast food restaurant) they would have to pay to access it – an unlikely scenario. On the other hand, if every company had their own “SpeedPass” system, a consumer would need to carry many different devices with them.

RFID systems can be easily disrupted

Since RFID systems make use of the electromagnetic spectrum (like WiFi networks or cellphones), they are relatively easy to jam using energy at the right frequency. Although this would only be an inconvenience for consumers in stores (longer waits at the checkout), it could be disastrous in other environments where RFID is increasingly used, like hospitals or in the military in the field. Also, active RFID tags (those that use a battery to increase the range of the system) can be repeatedly interrogated to wear the battery down, disrupting the system.

RFID Reader Collision

Reader collision occurs when the signals from two or more readers overlap. The tag is unable to respond to simultaneous queries. Systems must be carefully set up to avoid this problem; many systems use an anti-collision protocol (also called a singulation protocol. Anti-collision protocols enable the tags to take turns in transmitting to a reader.

RFID Tag Collision

Tag collision occurs when many tags are present in a small area; but since the read time is very fast, it is easier for vendors to develop systems that ensure that tags respond one at a time.

Security, privacy and ethics problems with RFID

The following problems with RFID tags and readers have been reported.

The contents of an RFID tag can be read after the item leaves the supply chain

An RFID tag cannot tell the difference between one reader and another. RFID scanners are very portable; RFID tags can be read from a distance, from a few inches to a few yards. This allows anyone to see the contents of your purse or pocket as you walk down the street. Some tags can be turned off when the item has left the supply chain.

RFID tags are difficult to remove

RFID tags are difficult to for consumers to remove; some are very small (less than a half-millimeter square, and as thin as a sheet of paper) – others may be hidden or embedded inside a product where consumers cannot see them. New technologies allow RFID tags to be “printed” right on a product and may not be removable at all.

RFID tags can be read without your knowledge

Since the tags can be read without being swiped or obviously scanned (as is the case with magnetic strips or barcodes), anyone with an RFID tag reader can read the tags embedded in your clothes and other consumer products without your knowledge. For example, you could be scanned before you enter the store, just to see what you are carrying. You might then be approached by a clerk who knows what you have in your backpack or purse, and can suggest accessories or other items.

RFID tags can be read a greater distances with a high-gain antenna

For various reasons, RFID reader/tag systems are designed so that distance between the tag and the reader is kept to a minimum (see the material on tag collision above). However, a high-gain antenna can be used to read the tags from much further away, leading to privacy problems.

RFID tags with unique serial numbers could be linked to an individual credit card number

At present, the Universal Product Code (UPC) implemented with barcodes allows each product sold in a store to have a unique number that identifies that product. Work is proceeding on a global system of product identification that would allow each individual item to have its own number. When the item is scanned for purchase and is paid for, the RFID tag number for a particular item can be associated with a credit card number.

CONCLUSION

The Thesis is designed to meet the requirements of Computerized Inventory Management and an Interactive System that stays in touched with its members of a Pharmaceutical Company. Inventory control will be developed in Oracle 10g, C#.Net by keeping in mind the specifications of the system. For designing the system we have used simple data flow diagrams. The main concern of the system is to make sure that the members always stay up-to-date on their selected areas-of-interest. Members have the flexibility to set when they should be sent updates and anytime they can change areas-of-interest. In gist, ISPhC is interactive system that always communicates with its members.

Overall the project teaches us the essential skills like:

To carry out, independently, a task of significant magnitude in an area.
Using system analysis and design techniques like data flow diagram in designing the system.
Understanding the database handling and query processing.

PAPERS REVIEW

Lee W. M. (2007), “Practical .NET 2.0 Networking Projects”, Apress®.
Allen R. and Hunter L. (2006), “Active Directory Cookbook”, 2^nd Edition, O’Reilly.
Pearman G. and Goodwill J. (2006), “Pro .NET 2.0 Extreme Programming”, Apress®.
Davies J. and Northrup T. (2008), “Windows Server 2008 Networking and Network Access Protection (NAP)”, Microsoft Press.
Tulloch M., Northrup T., Honeycutt J., and Wilson E. (2007), “Windows Vista™ Resource Kit”, Microsoft Press.
Johnson S. (2008), “Microsoft Expression Web 2 On Demand”, Que Publishing.
Rankins R., Bertucci P., Gallelli C., and Silverstein A. T. (2006), “Microsoft SQL Server 2005 Unleashed”, Sams Technical Publishing.
Nijholt A., Welbergen H. V. & Zwiers J. (2007), Introducing an Embodied Virtual Presenter Agent in a Virtual Meeting Room, “A Scientific and Technical Publishing Company”, www.actapress.com, [accessed at 22^nd March, 2008]
Elias M. Awad, Systems Analysis and Design, Galgotia Publications (p), 2007.
Roger S. Pressman, Software Engineering-A practitioner’s approach, McGraw-Hill, 1997.
Abraham Silberschatz, Henry F. Korth, S. Sudarshan, Database System Concepts, McGraw-Hill, 2006.
Ivan Bayross, SQL, PL/SQL-The Programming Language of Oracle, BPB Publications, 2005.
John M. Nicholas, Project Management for Business and Technology,
James Rumbaugh, Michael Blaha, Willium Premerlani, Frederick Eddy, Willium Loernsen, Object Oriented Modeling and Design, Prentice-Hall, 2002
Ramez Elmasri, Shamkant B. Navathe, Database Systems