|
|
|
|
|
|
|
|
|
|
|
|
AutoSoft Systems |
|
|
|
|
AutoSoft
Systems |
2 Round Hill Court |
|
|
|
|
East Greenwich, RI
02818 |
|
|
|
|
401.885.3631 |
|
|
|
|
401.884.5653 Fax |
|
|
|
|
|
|
|
|
|
|
|
|
401.996.3631 Cell |
AMDG |
|
|
|
Decision Ready
Information! |
autosoft@aol.com |
Commercial & custom multi-user computer software for a
variety of applications including performance metrics, statistical analysis,
data extraction and merger from multiple large databases, computer simulation
and management information systems.
Founded in 1982. |
We know Excel! Whenever possible, Excel
is the user interface, VBA is the programming language, ODBC connects to the
database of your choice. |
Home |
About Us |
Literature |
Wire
& Cable |
Pharmaceutical
& Biotech |
Construction
Management |
Commercial
Products |
Custom Solutions |
Contact Us |
Events |
Downloads |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
AMDG |
List of Custom Software
written by Dr. Serdakowski for a Major Biotech Corporation |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
This page lists the many software applications written and
maintained by Joe Serdakowski, Ph.D., P.E., M.B.A. in use at a $20+Billion
annual sales Biotech firm with manufacturing on 3 continents (code-named
“Acme Pharmaceuticals”). This list
represents an ongoing effort of 6 days a month for over 5 years. (Last
updated May 12, 2019). |
Joe supports initiatives at Corporate Headquarters and 6
manufacturing facilities. Joe's role is to improve information flow and thus
assist the users of his software work more efficiently with better
information & "GET HOME ON TIME". 😊 |
Joe teaches a series of Excel and VBA (Visual Basic for
Applications) training courses. Joe welcomes the opportunity to teach
these courses at other companies. |
Acme has provided Joe with 4 computers: |
▪ |
His primary
laptop computer |
▪ |
A Server used to
update the list of reports below and automatically send out email
notifications |
▪ |
A Desktop
"Mini-Server" that allows Joe and other selected users to RDP
(Remote Desktop Protocol) into this Server using a Pulse Secure
connection. Because of security and firewall considerations, certain
operations take much longer if you are off site. By using this RDP
connection, these operations execute as fast as they would if the user were
on site. If you periodically log in from home and are burdened by
sluggish performance, contact Joe for access to this "Mini-Server"
to improve productivity. |
▪ |
A
"legacy" lap top computer that continues to support older computers
at Acme. This computer is also used to perform lengthy calculations in
support of Joe's efforts. |
AutoSoft's Solutions Paradigm |
It is important to note that Joe has been developing custom
applications and reports since 1981 and at Acme since 1999. Joe has had
a continuous presence at Acme since 2005. He has acquired intimate
knowledge about several of Acme's corporate databases, including Maximo, Trackwise, StarLIMS, Pi Historian and the Document Management Database. He has a
proven track record of delivering solutions for very low costs on very short
time tables that exceed expectations. He provides 24x7x365 support and
typically resolves issues in less than 1 day. Anyone who argues against
his paradigm for application development is simply ignoring the facts
and 14 years of success. Joe's paradigm is constantly evolving and
employing the newest tools and features, but only when there are derived
benefits. Joe's paradigm will continue to be the most cost-effective
platform to develop and maintain a large swath of current and future
requirements. Joe believes that it is in major corporations’ best interest to
exploit this paradigm and formally recognize it and promote it as one of
several paradigms for custom software development. Joe's long-term
commitment to his work is strong and he is eager to work with decision makers
at other major corporations in whatever capacity is best for the corporation,
whether that be separate POs for each project, a blanket PO with a formal
contract and receiving direction from one source, or as an employee and
part of a team that develops and supports this paradigm. |
Microsoft Excel is used as the user interface. VBA (Visual Basic for
Applications) is the programming language. Various back-end databases
are employed (Azure, SQL Server, Oracle,
Progress, AWS DynamoDB, Access). It is a
common misconception that Excel is not a suitable framework upon which to
build "industrial strength" multi-user applications.
This is true for Excel workbooks that are composed and managed by most
casual users. HOWEVER, there are a number of features that when
employed provide the necessary structure to exceed even the most rigid
corporate guidelines for security (see WeKnowExcel.com/ExcelSecurity.htm).
Several of Joe’s tools at Acme have been validated. |
AutoSoft's Body of Work at Acme |
The following is a list of many of Joe's programs, past and
present. |
Work Order Administration (WOA) Software |
▪ |
SQDIP (Safety, Quality,
Delivery, Inventory, Production), Work Center Board and Innovations
suite. This collection of integrated applications is used by WOA,
Mechanical Maintenance and Facilities. In use since January 1, 2016.
Since April 1, 2017 used by 135 different users. A complete
Change Log has recorded over 78,000 separate transactions during that time
span. Over 3,200 hours/year have been saved since April 1, 2017. The
underlying Access database is backed up hourly to prevent data
loss. Upon creation of a SQDIP, the App automatically emails the
assignee with notification of the issue. The assignee can edit and
update the issue directly from the email. The suite includes the
following applications: |
|
▪ |
SQDIP.xlsm -
SQDIP issues and Innovations editor. Upon opening provides the
user with a dashboard listing the following metrics for both site and
the individual user: |
|
|
▪ |
SQDIP Open
Records with no due date |
|
|
▪ |
SQDIP overdue
records |
|
|
▪ |
SQDIP completed
or cancelled records with no completed date |
|
|
▪ |
Delivery SQDIP
completed with no Flow Interrupter Code |
|
|
▪ |
SQDIP completed
with blank Extended Hours saved |
|
|
▪ |
SQDIP open
records w/ Date Due < 7 days |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
▪ |
Work Center
Board.xlsm - Work Center Board Issues and Overview Email
generator. The Overview Email is a "one-click" comprehensive
Facilities Status Report, fully rich HTML format, with varying colors and
fonts to highlight key issues and recent changes. The email is sent to over
60 people. |
|
▪ |
SQDIP and Work
Center Board.xlsm - SQDIP issues, Work Center Board issues, Manager
Notes and Innovations editor and Work Center Board Overview Email
generator |
|
▪ |
SQDIP
Metrics - Generates a 9 slide PowerPoint document that includes the
following: |
|
|
▪ |
High frequency
Job Plan repeats |
|
|
▪ |
SQDIP Issues
count sorted by Department, Shift and SQDIP type |
|
|
▪ |
Common
Productivity Flow Interrupters Sorted by Shift |
|
|
▪ |
Common Delivery
Reason Codes Sorted by Shift |
|
|
▪ |
SQDIP Assignment
Completion Metrics - Days from entered to completed and # of current open |
|
|
▪ |
Overdue SQDIP
records |
|
|
▪ |
SQDIP Exceptions |
|
▪ |
SQDIP Cycle
Counter |
|
|
▪ |
Allows user to
select a subset of SQDIP issues over a specified time period and provides a
dashboard including the following metrics trending with time |
|
|
|
▪ |
Open and closed
count |
|
|
|
▪ |
Cycle time |
|
|
|
▪ |
Time Lost |
|
|
|
▪ |
Hours saved |
|
|
|
▪ |
Percent completed
in # of days |
|
▪ |
SQDIP Weekly
Exception Report - All users with one or more exceptions receive a
weekly email asking them to resolve the exceptions. The assignee can
edit and update the issue directly from the email. The exceptions include: |
|
|
▪ |
SQDIP Due Date is
within 7 days |
|
|
▪ |
SQDIP status is
'Completed' or 'Cancelled' and completed date is blank. |
|
|
▪ |
SQDIP status is
'Completed' and Flow Interrupter code is blank for either 'Delivery' or all
other types. |
|
|
▪ |
SQDIP status is
'Completed' and Extended Hours saved is blank (must be either a positive
number or ZERO). |
|
|
▪ |
Root Cause Code
is 'Other.' and Comments are blank. |
▪ |
Weekly WOA Snapshot -
Generates in less than 2 minutes with a few mouse clicks a weekly email to
30+ recipients, providing dashboards on the following metrics. The
tables are embedded into the body of the email. |
|
▪ |
PMs Past Target
Completion Date by Building and Level (not including OFSR) |
|
▪ |
Weekly Work Order
snapshot on 38 different metrics for the last 4 weeks |
|
▪ |
CM and GM work
order count (table and charts) by 9 craft "buckets" and 3 age
"buckets" (<30 days, 30-90 days, > 90 days) |
|
▪ |
Backlog by Craft,
work orders over 90 days of age, trended over the last 13 weeks. |
|
▪ |
Average work
order backlog by craft over the last 8 weeks (table and chart) |
|
▪ |
Level 1 CMs that
are past the target completion date. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
▪ |
Work Order Scheduling "Schedule Helper" - This is a suite of apps that work together to assist the
work order scheduler/planner in his/her duties. Very tight integration
with Maximo, both read and write. Scheduler clicks on a work order then
clicks on a day to add it to the schedule. The committed vs available
person hours to complete the task list each day is presented. Several
cautionary messages are presented if a work order is in danger of not being
completed on the target completion date. There is a supplemental
Access database (daily backups) supporting this tool. This app was
commissioned because other options did not fulfill WOA's requirements |
|
▪ |
Schedule_Saver -
an Excel XLSA "Add-in" that provides the user with a number of
custom functions by pressing the "F" keys: |
|
|
▪ |
F4 - Build a Snap
Shot of the schedule |
|
|
▪ |
F5 - Updates Work
Orders on the Detail Sheet from the Report sheet |
|
|
▪ |
F6 - Updates
available hours on the Detail sheet |
|
|
▪ |
F7 - Updates Work
Order status from Maximo |
|
|
▪ |
F8 - Generates a
Maximo Loader file |
|
|
▪ |
F10 - Saves
entire Detail sheet to database |
|
|
▪ |
F11 - Updates
Ready Work |
|
|
▪ |
F12 - Attempts to
repair the database |
|
▪ |
Maximo_Query -
The scheduler/planner opens this file to start the process. A one-time
configuration for each user provides a list of work orders to be scheduled.
The generated scheduling folder opens with two windows, one window listing
all work orders, the second is a calendar. The user simply clicks on a work
order, then clicks on a date to schedule it. The user then assigns a
shift. The available technician hours are calculated based on the labor
hours in each work order. The scheduler/planner can add colored background
and fonts to highlight instructions. |
|
▪ |
Staffing
Editor - Tracks the technicians working each shift. The user can
record time off and added coverage for any technician on any day so that an
accurate total of available technician hours is calculated for each day. |
|
▪ |
Schedule
Reviewer - User can generate a list of scheduled work orders filtered by
date range, location and labor group. The colored fonts and background
recorded by the scheduler/planner are reflected in the report. |
|
▪ |
Work Order Status
Updater - synchronizes the Maximo and Schedule Helper databases. Updates
nightly. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
▪ |
Work Order Schedule Adherence Tool. Queries Maximo, looks at actual vs target work
order completion dates and schedule changes. In the case of a work order
closing beyond the target date, prompts the associated planner to identify
the root cause as to why the work order was late. Regarding schedule
changes, prompts the associated planner to identify the root cause for the
change. There are two components to this: |
|
▪ |
AutoSAT -
user interface to review and assign root causes to late and rescheduled work
orders. Each user can control what subset of work orders are their
responsibility and add/edit root causes. Synchronizes with Maximo
nightly. |
|
▪ |
Summary
Metrics - provides a dashboard of metrics |
▪ |
Maximo-LMS (Learning Management System) Reconciliation
Utility - Takes a report from the LMS
which lists all personnel trained in Maximo access and compares that to the
current Maximo access for each person, identifying if anyone has expired
training. Great auditing tool. |
▪ |
Job Plan Duration Tool -
Compares actual labor hours recorded against Job Plans to target labor
hours. Provides dashboard illustrating how each Craft group is
performing. Assist planners in their review of Job Plans to align
targets with actuals. Allows planners to drill down into each craft for
each work order on each job plan to see actuals. |
▪ |
Many Maximo
Reports |
|
▪ |
Asset_Calibration_Ranges -
lists over 4,000 Asset Calibration Ranges. - updates nightly |
|
▪ |
Maximo_Asset_Query -
lists properties of over 44,000 Assets - updates nightly |
|
▪ |
Maximo_CC_CE_Query -
lists properties of open work orders of Work Type - "CC" or
"CE". This utility has a supplemental Access database that
allows the users to record and save comments pertaining to each work order
for others to view. A historical log is maintained so one can view the
changes made to the comments for each work order |
|
▪ |
Maximo_JP_Waiting_Query -
lists Job Plans that are in selected Statuses and emails the Owner and/or the
QA Assignee if the Job Plan has been in the specified Status beyond a
specified period of time - updates nightly. On April 15, 2019 the following
user defined criteria have been set: |
|
|
▪ |
If the Job Plan
has been one of the specified Statuses for 5 or more days, notify the QA
Assignee with an email. The specified Statuses are 'WQA' and 'WQAP'. |
|
|
▪ |
If the Job Plan
has been one of the specified Statuses for 10 or more days, notify the Owner
with an email. The specified Statuses are
'JPWA2R','JPWA2RP','JPWAR','WJPO','JPO2','WJPI','JPO2P','WJPOP' and 'JPWARP'. |
|
▪ |
Maximo_Labor_Groups_Query -
Shows all Labor Groups, their members and default assignees - updates
nightly. On April 15, 2019 there were 680 people listed in 431 Labor
Groups. |
|
▪ |
Maximo_Location_Query -
Lists all Maximo Locations and 14 key properties of each location - updates
nightly. |
|
▪ |
Maximo_PM_Status_8 -
Lists the status of PM that are due in the current month or are
overdue. Provides a Metrics Dashboard to show percent completed based
on Labor Group Default Assignee. User selects option and updates.
Requires Oracle ODBC drivers installation. There are 4 options: |
|
|
▪ |
Manufacturing PMs |
|
|
▪ |
QA PMs |
|
|
▪ |
Manufacturing
with MM Crafts and Labor Groups |
|
|
▪ |
All Open Work
Orders with one of the three filters above. |
|
▪ |
Maximo_PMM_Query
- Lists all PMM work orders that are in one of many statuses picked by
the user from a list of possible work order statuses. A total of 15
properties of each work order are listed. There are 41 possible work
order statuses, 29 statuses are currently selected resulting in 59 work
orders (April 17, 2019). Additionally, if any Level 1 work order
is due within 14 days, the associated Primary, Secondary and Tertiary Planner
receive an email. The email is repeated if the work order is due within
7 days. The email is sent out daily if a work order is overdue. There
is a "Comments" field for each work order where users can record
comments about each work order. These comments are saved in an Access
database. The user can then select a work order and review the history
of the comments. - updates nightly. |
|
▪ |
Maximo_WATTCH_Query -
Lists all work orders in the WATTCH status. Includes 11 different work
order properties, including documents stored in Maximo that are associated
with each work order. A link is provided to open a document directly.
Accommodates the fact that a single work order may have multiple attached
documents. Provides a visual metric if the work order exceeds a specified
time in the WATTCH status. There is a "Comments" field
for each work order where users can record comments about each work
order. These comments are saved in an Access database. The user
can then select a work order and review the history of the comments. -
updates nightly. |
|
▪ |
Maximo_WRECON_Query -
lists all work orders in WRECON status. Emails the responsible party
every Monday if a work order is in WRECON status for 5 or more days.
Supplemental database allows WOA personnel to record notes specific to each work
order. |
|
▪ |
Open_WO_Report_Today
- Lists all open work orders - updates nightly. |
|
▪ |
Maximo Data
Integrity Report - performs a series of queries that looks for over 50
different possible data inconsistencies in Maximo. One can drill into
any issue to its root cause. Very powerful. Can be quickly
reconfigured to support any Maximo facility |
Maintenance |
▪ |
Spend Analysis Version 17 -
Allows the user to analyze spare parts consumption - updates weekly |
▪ |
Maximo Z-Number Editing Tool (ZeeNET) - Maximo manages creating and revising Z-Numbers
(spare parts) using AAE work orders. This tool tracks all the key
properties and documents and manages the AAE work order process.
There is a supplemental back-end Access database and the tool is fully
multi-user. No data is stored in the front-end Excel document which is
stored "read-only" on the network. There is a web-based tool
developed at Acme that provides this same function. However, personnel
have found this option not adequate for their requirements. The ZeeNET
tool has been modeled after a prototype and fulfills all requirements. |
▪ |
Notification Emailer - This
tool performs a query into the Maximo Database, returning records that are
within 21 days of, or beyond the deadline for owner action. The tool
will send out emails to each owner, clustering all records for each owner
into one email. Records will be included in the email if the deadline
is 21, 14, 4,3,2,1,0 days away or if the deadline has passed. |
▪ |
Maximo Mean Time Between Maintenance - This tool looks at all work orders in Maximo®, and
allows the user to slice and dice the data by 20 different parameters (i.e.
Start Date, Work Type, Site, System Description,….). Once the user
defines the subset that is to be analyzed, a stand-alone workbook containing
all of the pertinent records is generated. In addition to a
detailed listing of all workorders matching the filter criteria, one can
perform Pareto analysis by Site, Building, System and Sub-System based on #
of Events or $$$ spent. Pareto charts can also be generated. |
▪ |
Maximo Mean Time Between Failure - Let's face it. Equipment breaks. Often
at the worst possible time. Some equipment breaks more often than
others. Maximo® has stored in it a huge treasure-trove of
information. Getting it out sometimes seems more difficult than getting
gold out of sea water. AutoSoft's Mean Time Between Failure tool
calculates just that for all equipment stored in Maximo. This is a
pre-compiled program, meaning the program pre-processes the necessary
information overnight, resulting in a quick and easy user interface that gets
you the answers quick |
▪ |
Maximo Late PM Maintenance Notification Tool - This tool scans the Maximo database and culls out PM
workorders that are overdue or soon to be overdue. The owner of each
work order is identified, and an email is sent to each owner warning
him/her. This utility can be set up to run automatically at whatever
frequency is desired. |
▪ |
Maximo Asset Spare Parts Email Reminder - This tool performs a query into the Maximo Database,
returning records that are within 21 days of, or beyond the deadline for
owner action. The tool will send out emails to each owner, clustering
all records for each owner into one email. Records will be included in
the email if the deadline is 21, 14, 4,3,2,1,0 days away or if
the deadline has passed. |
▪ |
Maximo Data Scrubber -
This program combs through the Maximo database and isolates 5 defined
exceptions and 2 specific database changes. It automatically sends
emails to the system owners, notifying them of the exceptions and
"encouraging" them to make corrections. The number of
exceptions are trended with time so that progress can be tracked. |
▪ |
Maximo Subsystem Analysis Workbook - The program assists system owners in optimizing
Preventative Maintenance frequencies and costs. |
▪ |
Maximo Data Dictionary Generator - The Maximo database comprises approximately 400 tables
and views and in excess of 14,000 total fields. This tool provides
lists of the tables and associated fields, as well as "clones" each
table into a stand-alone Excel workbook putting the entire Maximo table
structure and content at your fingertips! This tool is a great time
saver for any programmer looking to generates reports out of Maximo |
▪ |
Equipment Engineering KPI (Key Performance Indicators) - The report updates nightly. |
▪ |
AutoDispatchLog - Multi-user
Excel front end, Access backend database to track maintenance issues that
occurred. |
▪ |
AutoCriticalUpdates-
identifies the count of followup work orders that had to be executed after
the completion of a job plan work order. |
F&E (Facilities & Engineering) Infrastructure Operations |
▪ |
PPM (Project Portfolio Manager) - This multi-user tool manages resources, schedules
and cash flows for the portfolio of capital construction projects at 3 major
manufacturing facilities. The PPM solves the vexing problem of managing
a slew of projects without the overhead of a long learning curve associated
with other project portfolio management software programs. Integration with
corporate ERP systems eliminates double entry of purchase order and invoice
information for cash flow and budget reporting. Each user is presented with a
list of all projects where he/she has been assigned tasks and can verify or
modify the estimated and actual time required for each project on a weekly
basis. The Project Managers have total visibility to all resources,
budgets, cash flows and schedules and can quickly adjust to changes.
Resource managers can level load resources and quickly recognize and
eliminate future "pinch points." This tool has been
continuous use at Acme at as many as 3 sites since 2005. Acme
"alumni" have employed this tool at 2 other pharma companies. A
supercharged version of this tool controls over $1 Billion in construction
projects at New Line Structures, one of the 7 largest construction management
firms in New York City. For several years a companion app,
the TransMaster, used at two sites, provided PO line and Expense
visibility, importing data from SAP. |
▪ |
Document Management Database Audit Trail Detail Report -a flexible search utility into the Document
Management database. Upon opening, the default behavior is to generate
a list of all open work flows. The user can also query the database for any
subset of documents, and generate a detailed report for any document that
includes: |
|
▪ |
Header
information |
|
▪ |
Open Workflow
items |
|
▪ |
User
Acknowledgements |
|
▪ |
Change Status |
|
▪ |
Other
Activity |
▪ |
AutoPlaybook - Outage planning is time
consuming. One or more Microsoft Project files are updated frequently
to schedule all of the events associated with an Outage. The
AutoPlaybook reads from the Microsoft Project file(s) and generates a series
of PDF files. 1-10 files are generated for each day. The PDF
files have a floor plan of the building(s) in outage, with color coding to
indicate the gowning level and work being performed in each area. Also,
a comprehensive list of all work is included in each file. The
AutoPlaybook then performs daily updates, naming and placing copies of
"today's" files so that they can be displayed on video screens
around the company to keep all informed. AutoPlaybook has
supported 3 outages at two facilities (2015, 2016, 2018). |
▪ |
PM Forecast Reporting -
these tools, one version for each of 5 sites, allows the user to forecast PM
work orders with a 2 year look ahead - updates weekly. Predicts meter-based
PMs as a function of the future production schedule (some products uses
certain equipment more, so the meter moves at varying rate depending upon the
product being made. |
StarLIMS and PI Historian Data Extraction Tools |
▪ |
Asset Calibration Ranges -
This report lists all the calibration parameters for all calibrated assets
and greatly simplified the task of verifying that the span of each
calibration exceeded the minimum and maximum operating conditions.
Provided detailed metrics as to the progress of each system
owner. Managed over 7,300 calibrations. |
▪ |
AutoEvents - Using PI
Historian Process Data to Optimize Preventive Maintenance - This App won Corporate recognition and an
award! Preventive Maintenance is a necessary function of every
manufacturing facility. How much is too much? How much is too
little? AutoSoft Systems has developed a suite of software tools that
can be easily customized to assist in this decision at a variety of
manufacturing facilities. Here is how it works: A large Bioprocess
facility has thousands of diaphragm valves. Diaphragms wear out and
need to be periodically replaced before they fail. The site has a
well-established process for periodically shutting down each system for a
"Lock-Out Tag-Out" (LOTO) where all of the diaphragms in the valves
are replaced. They rarely if ever have a diaphragm failure, so they do
not want to mess with success. They do know, however, that not every
valve experiences the same amount of usage. They would like to be able
to determine the amount of wear and tear on each valve and extend the
replacement cycle on those valves that see lighter duty. Diaphragms
experience wear and tear when they are opened and closed and when they are
subject to elevated temperatures. AutoSoft Systems extracted wear and
tear information about each valve from the PI Historian system and provided
engineers with a slick, quick and easy to use interface to determine the wear
and tear on each valve, and offer recommendations on the maintenance
frequency. This process could be extended to all devices subject to
repetitive motion. |
▪ |
Global Water Committee Monthly Report This app generates a monthly report summarizing the status
of WFI and PUR water systems at 6 different manufacturing
facilities. |
|
▪ |
The app collects
over 4 Million data points from 103 PI tags at the 6 different
facilities and compiles the data into a 3-dimensional dashboard. By
right-clicking a value, the user can see a Histogram on the monthly
performance of any PI tag as well as a list of all excursions. The 3-D
dashboard tracks: |
|
|
▪ |
Event Count Above
Alert Limit |
|
|
▪ |
Total Sample
Count |
|
|
▪ |
Hours Above Alert
Limit |
|
|
▪ |
Sample Count
Above Alert Limit |
|
|
▪ |
Hours Above
Action Limit |
|
|
▪ |
Sample Count
Above Action Limit |
|
|
▪ |
Intervention Free
Operation |
|
▪ |
The StarLIMS
database is also scanned, identifying over 5,000 data points each month, and
track a variety of exceptions in the LIMS data, reporting on the count each
month. |
|
▪ |
The end result is
a Microsoft Word document which then the system owner can add some clarifying
comments and distribute to key stakeholders around the corporation. Joe
has been preparing this report every month since January, 2009. |
▪ |
WFI Trace Metals Analysis -
Slices and dices StarLIMS WFI trace metal test results (Cr, Fe, Mn,
Mo, Ni) by Site, building, Room and Sample point. Provided very
comprehensive and colorized dashboard to compare and contrast results.
Assigned a numeric "grade" on a scale from 0-100 to allow for
quantitative valuations of each sample point. Identified potential
trouble spots. |
▪ |
WFI and PUR Water LIMS Query Tool - This app provides a quick report for users to extract
StarLIMS data on WFI and PUR water. The "Advanced" option
allows the user to extract information about any and all data in the StarLIMS
database. Used around the Acme since 2013 with minor updates. |
▪ |
MATRIX LIMS Query Tool -
a comprehensive LIMS Query tool allowing the user to filter on 48 different
StarLIMS data fields. Provides results as a table, Histogram and
Statistical Process Control Chart. |
▪ |
Generic PI Data Extractor -
a simple Query tool allowing the user to enter a single PI tag and date
range. Provides results as a table, Histogram and Statistical Process
Control Chart. |
▪ |
PI Reporter - A suite of
custom tools that allows the user to perform the following analysis. My
intimate knowledge of Chemical Engineering principles, The PI Historian
database, Excel and VBA allows me to quickly and economically build custom
models of this type. |
|
▪ |
WFI Tank Mass
Balance |
|
▪ |
WFI Still 1
Production |
|
▪ |
WFI Still 2
Production |
|
▪ |
Neutralization
Volume |
Financial Applications |
▪ |
SWEET - F&E Expense
Budgeting and Tracking. Imports Purchase Order and Expense data from
SAP. Compares Budget, Allocation and Actual expenses. Can slice
and dice the data by SAP Account, Vendor, Hyperion Account, Accounting Group
and Accounting Sub-Group. Very quick, powerful and intuitive.
Many data validation features included. In its second year of use. |
Quality Applications |
▪ |
Department Trackwise Reports -
These reports generate daily emails to a designated list. The Trackwise
contents are filtered to only include those records that are pertinent to
each department. Additionally, if a CAPA Action is impending or
overdue, the CAPA Action Owner receives an email. This is not a trivial
task because the CAPA Action Owner field in Trackwise does not have data
validation, so users enter the strangest things. I have a complicated
algorithm to determine the CAPA Action Owner. If the algorithm fails, I
receive an email and can then improve the algorithm. In use by 5
different departments at 3 sites. |
|
▪ |
The Daily report
lists the following: |
|
|
▪ |
NCs |
▪ |
CAPA Actions |
▪ |
CCs |
▪ |
ARAs |
|
|
|
|
|
|
|
▪ |
CAPAs |
▪ |
EVs |
|
▪ |
PMAs |
▪ |
Annual To Date and
Prior Years' Performance Metrics |
▪ |
AutoReporter Utility - This
tool queried the Trackwise database and builds a very comprehensive
PowerPoint slide deck on quarterly performance around the corporation.
The framework has been built to also query Maximo and other corporate
databases. Huge time saver. |
▪ |
Site-Wide NC Trending program -
a fully validated application. |
Cross-Functional Applications |
▪ |
The MATRIX {Corporate-Wide} -
This tool is a universal search and retrieval tool that can simultaneously
search multiple corporate databases for key words. Results are achieved
in seconds! Potential annual times savings can easily exceed 1 million dollars. Corporate
databases are good at getting information into them but leave much to be
desired when you need to find something. Typically, you must
search one database at a time. The MATRIX does it all for you. |
|
▪ |
The MATRIX is
currently being hosted at that 10 different network locations. |
|
▪ |
The following
databases are now searchable: |
|
|
▪ |
Document
Management Database |
|
|
▪ |
Maximo |
|
|
|
▪ |
Assets |
▪ |
Job Plans |
▪ |
Spare Parts |
▪ |
Work Orders |
|
|
|
|
|
▪ |
PMs |
▪ |
Job Plan Tasks |
▪ |
Locations |
|
|
|
|
|
|
|
▪ |
Trackwise |
|
|
|
▪ |
NCs |
▪ |
CAPAs |
|
▪ |
CCMS |
|
▪ |
CCPRD |
|
|
|
▪ |
Trackwise - Maximo Data Synchronization - Non-conformances continue to exist despite
everyone's best efforts. Accurate and complete documentation of each
and every NC is of paramount performance. Some NCs are related to
process equipment and/or have a "Machinery" root cause
classification. Text entry fields in Trackwise allow the NC owner to
record the equipment and the system associated with equipment and/or
machinery related NCs. There is no mechanism in Trackwise to validate
that the equipment or system entered is indeed a valid Maximo entry.
AutoSoft can! |
▪ |
Trackwise - Maximo combined report - Don't you just love it. Corporations today
have software tools for all different functions, however getting them to talk
to each other is difficult to say the least. I was asked by Acme to
develop one report that would automatically extract data from both the
Maximo® and Trackwise® databases. I delivered the finished product in
less than one hour. |
Manufacturing Support |
▪ |
Chromatography Column Scheduling and PM Cost Forecasting - This single app looks at the High Level Production
Schedule, the current state of over a dozen chromatography columns
and closed Maximo work orders. The app determines when
Chromatography column packing schedule to minimize the number of repackings
and production schedule interruptions, provides date ranges for column
changeovers, calculates column changeover costs for the year. Very
powerful and difficult to program. |
▪ |
Manufacturing Workforce Models - I have worked on several variants of this tool since
2010. In all cases Acme system owners built a complicated Excel workbook or
series of workbooks that eventually became cumbersome and difficult to
use. At that point I was brought into the development cycle to rebuild
the tool to make it workable again.
The current model accommodates up to 20 different products in a
multi-product facility with varying run rate and labor requirements for each
product. |
▪ |
AutoMedia - The cost of
a storage bag exceeds the cost of the media inside it in many
cases. SOP requires that full batches of media be prepared. With
a multi-product facility, often not all of the media is consumed before it
expires. AutoMedia looks at the High Level Production Schedule, looks at
production constraints (e.g. how many media batches can be produced per
shift) and calculates when the media must be produced and what fraction of
the media will be consumed before expiring therefore determining how much
media should be packaged and how much could be discarded. Potential
annual savings is $400,000. |
▪ |
AutoServices -
scheduling aid for storage vessel maintenance and Media/Buffer tasks. |
▪ |
AutoSAM - Schedule
Adherence for Manufacturing |
▪ |
AutoMAC - Calculated
the Maximum Allowable Carryover contamination level during change overs
between products. |
▪ |
AMPP Visualizer Program - This
program takes a Werum scheduling export file and visualizes when the various
pieces of equipment listed in the file are scheduled to be busy. The
Gantt chart is shaded red whenever the equipment on that line is scheduled to
be in use. |
Utilities |
▪ |
Training Assistant Program -
an app that builds a decision tree. Used to build individual training
requirements based on the tasks to be performed. Very powerful. |
▪ |
Server Maintenance Utilities -
these are used by Joe Serdakowski to manage the Acme server with minimum
amount of work. These tools send emails and if a critical issue is
detected, a text message to alert Joe of issues before they affect server
performance or users' work. |
▪ |
CART - Custom Automated
Reporting Tool - this tool manages the 60+ reports that are generated each
day |
▪ |
Drive Checker - checks
to assure the server's hard drive is not running low on space. |
▪ |
All Is Well - confirms
all reports have run on time |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
AutoSoft Systems | 2 Round Hill Court, East
Greenwich, Rhode Island, USA 02818 | 401.885.3631 | Fax: 401.884.5653 |
Mobile: 401.996.3631 |
This web page was last updated at 06/05/2020 04:03 PM and is
written in EXCEL! |
AXIOM is a Trademark of Consona - USYS is a Trademark of Zumbach
- AutoSoft Systems is not affiliated with Consona nor Zumbach |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|