Pages

Contact

Getting and processing an M3 picking list

Continuing my Google Glass project to display picking lists from Infor M3 onto Glass, here is how to use Event Hub and Event Analytics to get notified of new picking lists from M3 and how to process the events in Infor Process Automation (IPA) to get the details of each picking list: item numbers, item descriptions, quantities, and stock locations. This is a continuation of my previous posts How to create a picking list in M3 and Event Analytics for Infor Process Automation (IPA).The challenge is to determine which event to listen to, and from which database tables to collect the data from.

M3 Programs

According to the instructions of the previous post to create a picking list, the M3 Programs involved in the creation of a picking list are at least the following:

M3 Customer Order. Open Toolbox OIS300

M3 Customer Order. Open – OIS100

M3 Customer Order. Open Line – OIS101

M3 Allocation. Perform Detailed – MMS121

M3 Delivery. Open Toolbox – MWS410

M3 Picking List. Report – MWS420

M3 Picking List. Report Lines – MWS422

Database tables

I was told the database tables for picking lists involve at least the following ones:

MHDISH – Deliveries

MHDISL – Delivery lines

MHPICH – Picking list headers

MHPICD – Picking list details

MHPICL – Pick list headers

MITALO – Allocation

MITMAS – Item Master

I’m not completely familiar with the picking list tables so I got confused when I realized MHPICL is for the picking list headers despite the letter L in the name suggesting it’s for picking list lines, also when I realized there are three tables for picking lists instead of two like for deliveries, and when I realized there are two tables for picking list headers instead of just one, with inconsistent naming Picking and Pick. Over the years I’ve learned to accept the quirks of M3. So I used SQL to read each table and find my picking list.

My sample test in the previous post consisted of the following data:

1 customer order

4 customer order lines

1 delivery order

4 deliver lines

1 picking list

4 picking list lines

After reading each table with SQL and filtering by Company (CONO) and Delivery number (DLIX) I found the following numbers of rows:

MHDISH – 1 rows

MHDISL – 4 rows

MHPICH – 1 row

MHPICD – 4 rows

MHPICL – 1 row

Observation 1: The picking list lines are in table MHPICD. So I’ll get my picking list details from there.

Observation 2: Table MHPICD contains the columns for item numbers, item descriptions (usually found in table MITMAS), quantities, and stock locations, which is what I need for now, so I don’t need to do any joins with any other tables for now.

Here is a sample screenshot of the result:

Event Hub

My goal is to get one event per picking list so I can process the picking list in its entirety as a single entity; I’m not interested in getting one event per picking list line, four in my case, as that would loose visibility of the higher level abstraction that is the picking list. So what subscription do I need in Event Hub knowing there are six plausible tables and three possible operations Create, Update, and Delete?

At first I tried the most obvious subscription M3:MHPICH:C for the creation of a row in the table of picking list headers. But that didn’t work because it was too early: by the time I had received the event and processed it in IPA the picking lines didn’t exist yet and I got zero results. It was the same problem with MHPICL. And I didn’t want to do complex processing like count the number of expected picking lines and wait for the last one to arrive, or bad design ideas like wait a second for the rows to be created.

So I tried all possible subscriptions:

M3:MHDISH:CUD

M3:MHDISL:CUD

M3:MHPICH:CUD

M3:MHPICD:CUD

M3:MHPICL:CUD

M3:MITALO:CUD

And I received the following events in this chronological order:

M3:MHDISH:C

M3:MHDISH:U

M3:MHDISL:C

M3:MHDISH:U

M3:MHDISL:C

M3:MHDISH:U

M3:MHDISL:C

M3:MHDISH:U

M3:MHDISL:C

M3:MITALO:C

M3:MITALO:C

M3:MITALO:C

M3:MITALO:C

M3:MHDISH:U

M3:MHPICH:C

M3:MHDISH:U

M3:MITALO:D

M3:MITALO:C

M3:MITALO:D

M3:MITALO:C

M3:MITALO:D

M3:MITALO:C

M3:MITALO:D

M3:MITALO:C

M3:MHPICH:U

M3:MITALO:U

M3:MHDISL:U

M3:MITALO:U

M3:MHDISL:U

M3:MITALO:U

M3:MHDISL:U

M3:MITALO:U

M3:MHDISL:U

M3:MHPICH:U

M3:MHDISH:U

M3:MHPICH:U

M3:MHPICL:C

M3:MHPICD:C

M3:MHPICD:C

M3:MHDISH:U

M3:MHPICD:C

M3:MHPICD:C

M3:MHPICL:U

Observation 3: The subscription M3:MHPICL:U is the last one of the sequence so it will happen at the right time after the picking list lines have been created, and it’s unique per picking list so I won’t get duplicate events nor events per picking list line. Good. I’ll subscribe to that event. And the primary keys I’ll receive for MHPICL are Company (CONO), Delivery number (DLIX), and Picking list suffix (PLSX).

Event Analytics

Then, I created a Drools Rule in Event Analytics to filter the events by Company (CONO) and by Warehouse (WHLO) as I’m only interested in that particular warehouse. Here is a screenshot:

Process flow

Then, I created a process flow in Infor Process Designer (IPD) to receive the primary keys of the picking list and get the picking list lines details with SQL. Here is a screenshot:

And I created an Event Hub Receiver:

Result

Here is the result when I create the picking list following the instructions from my previous post, I get WorkUnits triggered by Event Analytics:

Conclusion

In this post I showed you how to get an M3 picking list and process it using Event Hub, Event Analytics, and Process Automation, to get the picking list details with item number, item description, quantities, and stock location. I also showed you my thought process to identify the table MHPICD for the picking list lines, and the subscription M3:MHPICL:U to get a unique event for the picking list at the right time.

Future work

In a future work, I will send the picking list to my Glass using the Mirror API, I will get the item image from Infor’s Document Archive, and I will show walking directions on a warehouse plan.

That’s it! If you liked this post please subscribe to this blog with the Follow button below, leave your comments in the section below, like, share with your colleagues, and enjoy.

4 thoughts on “Getting and processing an M3 picking list”

Hey Thibauld – this is Scott Queen, from OtterBox. I took LPA training from you (assuming you’re the same Thibaud). Question on a slightly different topic, but still around EventHub suscriptions… from MEC, in this case, though. Do you know the proper subscription string for consuming off of an EventAnalytics queue in a different grid than the MEC instance? If EV is in the same grid, then one uses the following in the EventHub Subscription configuration: “EventAnalytics:[DESIRED_QUEUE]”. I’ve read somewhere that one must use some sort of socket designation when subscribing to a queue in a diff grid, perhaps something like: “[EA_SERVER]:[PORT]:EventAnalytics:[DESIRED_QUEUE]”. If you’ve not seen this, do you know where I might get some assistance?

Hi Scott, yes I remember you. Glad to read you here. In IPA you define separately an Event Hub Channel with host and port number and an Event Receiver with the subscription. I don’t know about MeC but I guess it’s the same separation. Check with your MeC consultant. /Tibo