How to get a JBA Supplier History by Country

I was asked to build a spreadsheet showing “All purchase totals for all suppliers by year and include the supplier country code”

A simple request – so I thought I would blog the process for those new programers out there or for anyone wanting to understand the file structures in JBA (aka INFOR SYSTEM21).

Supplier Summary Balances (PLP06)

Now, we know that all purchase history is in file PLP06 (Purchase Ledger Physical 06), and we can see it sequenced by JBA COMPANY and SUPPLIER using logical view PLACSMRY:

Supplier Address (SLP05)

The good old Supplier master file is pretty straightforward. The database has a dedicated COUNTRY Code field COCD05, but many users also enter the country code in the address 4 line of the address. This is because JBA originally used a design idea of allowing free format addresses to be entered.

This relies on the user to enforce strict rules like “column 4 is the country name” but as you can see this idea often leads to messy data layouts:

So, we can only work with what we have, so let’s use SQL to view the supplier summary balances and include the supplier country code:

SQL to view the Data

set schema oslplf3;
select a.cono10,
       b.supn05,
       b.snam05,
       b.sad105,
       b.sad205,
       b.sad305,
       b.sad405,
       b.sad505,
       b.pcd105,
       b.pcd205,
       b.cocd05,
       a.pper10,
       a.binv10,
       a.bcrd10,
       a.bjnl10,
       a.bpay10,
       a.bdis10,
       a.bvtp10,
       a.npdr10,
       a.npcr10
    from plp10 a
         join plp05 b
             on a.cono10 = b.cono05
                 and a.supn10 = b.supn05
    where b.dseq05 = '000'

Which gives us everything we need to see:

Now let’s download this view as a spreadsheet:

Use IBM i ACS and download as XLSX spreadsheet

First of all – check your ACS configuration and make sure it is set to allow download (which is *OFF by default – don’t ask me why!!!)

• In Run SQL Scripts on the menu click Edit -> Preferences.
• Select the General tab
• Select (check) Enable saving or results using scrollable cursors.
• IMPORTANT – Exit and restart Run SQL Scripts for this change to take effect

Now you can run your query and have option to Save Results as a spreadsheet 🙂

Remember to click the LOAD ALL RESULTS — a little button to retrieve all rows:

Now you can right click and save those results as a spreadsheet direct to your desktop:

NOTE: if you are not using IBM i ACS then you can use the DISTRIBUTE DATA command from Software Projex to do all this in one command.

What about doing this for selected dates? Periods? Years?

We know that PLP06 is a running balance of purchase / supplier totals, but it’s always showing the totals of what our company is doing right NOW. So how do we find what values were for a range of dates?

Quite nicely, we have a very similar total file called PLP10 – Supplier Period Balances

If we want to select a whole year – for example lets look at 2019. Note that the JBA database stores dates in format of CYYMMDD and Periods as CYYMM:

set schema OSLPLF3;
SELECT a.CONO10,
       B.SUPN05,
       b.SNAM05,
       b.SAD105,
       b.SAD205,
       b.SAD305,
       b.SAD405,
       b.SAD505,
       b.PCD105,
       b.PCD205,
       a.pper10,
       a.BINV10,
       a.BCRD10,
       a.BJNL10,
       a.BPAY10,
       a.BDIS10,
       a.BVTP10,
       a.npdr10,
       a.npcr10
    FROM PLP10 a
         JOIN PLP05 b
             ON a.CONO10 = b.CONO05
                 AND a.SUPN10 = b.SUPN05
                 WHERE b.DSEQ05 = '000' and pper10 >= 11901 and pper10 <= 11912 
                 order by a.cono10, b.supn05, a.pper10;

So here we are selecting Customer Sequence as ‘000’ for master address and posting periods as ‘11901’ (Jan 2019) through ‘11912’ (Dec 2019), and this shows us the view in much more detail.

There you have it!

Hope this helps someone out there trying to find purchase ledger, aka Account Payable, in the JBA/GEAC/INFOR System 21 database.