Agile Factory

How to get Vendor Rating from Mapics data - cheap and easy

Personally I hate complicated and expensive solutions. Everytime I try to find out how to reach the target with low cost, easy-to-do and easy-to-use solution.

One of the issues I had to solve was to set up something giving to purchasing dept. "Vendors Rating" information to keep ISO requirements. Some data could be found in MRP system Mapics XA6 but some like history of purchase prices were not.
Maybe somebody will solve the same or similar solution.

At the beginning I had to decide if we should build some add-on program and database on AS/400 or if we will build external application connected to Mapics data trough ODBC.
From the cost point of view it was obvious that solution created on AS/400 would be quite expensive.
And from the flexibility and easy-to-use point of view again green screen of AS/400 couldnot compete to Windows application.

At this point I decided to focus to MS Windows based solution. The two choices were to ask somebody to make the program or to do it in house.
The final decision was to create the application in house in MS Access. There were several reasons. Application had to import data from AS/400 Mapics libraries, it means a programmer had to understand Mapics data structures and data exchange with AS/400. And he had to know how to make program in Windows. Not easy to find such person and if you find, then he is rather expensive.
There was an issue with the philosophy of the software as well. As it purchasing dept. had no idea which data they need in fact, which data are available in Mapics and so on. Typical situation :)

Why did I use MS Access? Due to several reasons. Everybody had MS Access installed on PC. The workspace looks familiar to Excel users, easy to export into Excel, easy to adjust the program logic if any change is needed. Even experienced user could add more queries.

The Vendor Rating should measure three main areas:
1. Purchasing price history - to understand trends
2. Scrap and Rejects ratio
3. Vendor delivery performance

Based on those requirements I made simple MS Access applicaton.

Main Screen


1. Purchasing price history - to understand trends

Click to see big screenshot

First, I needed the purchasing prices history. Unfortunately, there were not available in Mapics. Therefore I had to create and keep such history in Access.

By clicking button "STEP1" I downloaded contracts by calling simple SQL statement running on AS/400:
Select A.vndnr,A.itnbr,A.cnunp PurPrice,A.cnstd ContractStartDate,A.mdate as ContractLastUpdated from amflib.contrf A join amflib.itembl B ON A.itnbr=B.itnbr and A.vndnr=B.vndnr where'1'
I had to download vendor master data as well by SQL query:
Select vndnr Vendor,vname Vendorname,vcntr Country,curid from amflib.vennam
And I saved actual date and time information directly into MS Access database.

Step1 should be done periodically, e.g. at the end of each month.

If there were historical data already available, STEP2 could be done. It is just a simple filter setting table.

And finally STEP3 button would show Purchasing prices history as pivot table, something like.

VENDORNAME ITEM DESCRIPTION ITEMCLASS U/M CURID 9_30_2004 08:30:00 10_30_2004 08:45:00

11_30_2004 09:22:00

12_30_2004 10:41:15
KING LIMITED MAX451219W FIXING ROD S7 PC USD 0,108 0,105 0,105 0,104
KING LIMITED PBH1UPR16 WHITE CREAM S7R1   PC USD 0,108 0,105 0,105 0,105
SUNSUN LIMITED PJKK781 CARDBOX S8 PC USD 1,37 1,37 1,37 1,37
AGILE CO LTD AX45PLS SMALL WHEEL MMR KG EUR 12,51 12,51 12,25 12,25
AGILE CO LTD AX45PLP BIG WHEEL MMR KG EUR 10,14 10,14 10,14 10,11

Easy to analyze in Excel, make charts, ...


2. Scrap and Rejects ratio

Scrap and Rejects ratio screen

It is quite easy to get data in order to measure scrap or reject ratio.
It is enough to download data from amflib.imhist, amblib.itembl and amflib.vennam.

Then queries giving you the results could be set up.


3. Vendor delivery performance

Vendor delivery performance screen

In order to measure vendor performance, we have to check if supplier had to split our orders and if he kept requested delivery day (ETA).
Such data are available in transaction history table amflib.imhist. We need to get original quantity and requested ETA, therefore we have to pick up data from purchase order details table amflib.poitem.
The best way is to use simple SQl JOIN query to get all information at once.
SQL query could look as:
Select,A.tcode,A.trndt,A.itnbr,A.trqty,A.ordno,A.vndnr,B.qtyor,b.foldt from amflib.imhist A JOIN amflib.poitem B ON A.ordno=B.ordno and A.itnbr=B.itnbr where'1' and A.tcode='RP' and A.vndnr<>''

If we need full vendor names we have to download amflib.vennam.

And now we have all data available and we can make in MS Access queries giving us the results


4. Conclusion

I spent about 50 hours to analyze the problem, check available data in Mapics system, and build this simple solution.
The only cost was my time consumed to do this application. Some may argue time is money, it was not free of charge. Yes, you're right. But on the other hand I do understand more to Mapics and I know more about the job of purchasing dept. Such experience is worth for me.