The tracking of active ingredients in the pharmaceutical production process of drugs it is crucial in case of contamination of an ingredient. The production process is very complex and involves lots of different ingredients. It is challenging to keep track of all details. But this can save the lives of patients and prevents expensive lawsuits. Alphaquant’s task was to streamline this process and make it more efficient through automation.
Challenge
In case of contamination of an ingredient in a drug, all lots must be identified to recall the drugs from various distribution channels. Tracking of active ingredients is crucial. At all times it needs to be documented which ingredients were used in which drugs, where the ingredients were sourced from and where they were used. The process was time consuming and error-prone because of lots of manual work involved. Excel tables and makeshift databases needed to replaced.
Solution
Alphaquant enhanced the existing data warehouse and added data structures to cater the needs of a fast search of all ingredients. We setup a hierarchical model that makes tracking of active ingredients easy. Moreover we established easy exploration of the ingredients used in each drug and their corresponding lots. This data mart serves now as the single point of truth. To speed up the search we developed a reverse search function. Moreover a graphical server based interface was build to replace Excel and Access.
Result
If an active ingredient is contaminated, the employees can do a quick search to find out the affected lot numbers. It will also tell about the receiving side to inform them immediately. This will prevent patients from taking drugs that will harm them instead of helping them. Tracking of active ingredients has become fast and easy. As a result the search time reduced to a 1/10 and leaving no room for error with a new error rate of 0.
Technologies
Oracle, SQL, PL/SQL, OWB, APEX