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:
1_

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:
Rule

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:
2

And I created an Event Hub Receiver:
Channel

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:
WorkUnit

And I get the primary keys and the picking list lines from the SQL:

Workunit 70 for process NewPickingList execution started @ 05/16/2014 12:01:17 AM


Activity name:Start id:1 started @ 05/16/2014 12:01:17 AM
 Executing Start Activity...
Activity name:Start id:1 completed @ 05/16/2014 12:01:17 AM

Activity name:SQL id:1 started @ 05/16/2014 12:01:17 AM
 SQL Query SQL: Query string SELECT H6WHSL, H6ITNO, H6ITDS, H6ALQT
FROM MVXJDTA.MHPICD
WHERE H6CONO=<!CONO> AND H6DLIX=<!DLIX> AND H6PLSX=<!PLSX>
 SQL SQL: Using JDBC connection String Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver, URL: jdbc:sqlserver://m3db-2013;databaseName=MVXFEMD2, User: *****
 SQL Query SQL: Query string SELECT H6WHSL, H6ITNO, H6ITDS, H6ALQT
FROM MVXJDTA.MHPICD
WHERE H6CONO=910 AND H6DLIX=6940 AND H6PLSX=1
 SQL_errorCode = 0
 SQL_informationCode = 0
 SQL_returnMessage = SQL query SQL: Execution complete.
 SQL_outputData = 
Activity name:SQL id:1 completed @ 05/16/2014 12:01:17 AM
 SQL_RETURN_MSG = Success
 SQL_RETURN_CODE = 0
 SQL_errorCode = 0
 SQL_informationCode = 0
 SQL_returnMessage = SQL query SQL: Execution complete.
 SQL_outputData = 
 SQL_RECORD_COUNT = 4
 SQL Query SQL: Executing loop 1 of 4
 SQL_1 = T0101 
 SQL_H6WHSL = T0101 
 SQL_2 = TLSITEM01 
 SQL_H6ITNO = TLSITEM01 
 SQL_3 = Item 01
 SQL_H6ITDS = Item 01
 SQL_4 = 11
 SQL_H6ALQT = 11
 Message Builder:MsgBuilder6340 Executing this activity...

Activity name:MsgBuilder6340 id:1 started @ 05/16/2014 12:01:17 AM
Activity name:MsgBuilder6340 id:1 completed @ 05/16/2014 12:01:17 AM
 SQL_RETURN_MSG = Success
 SQL_RETURN_CODE = 0
 SQL_errorCode = 0
 SQL_informationCode = 0
 SQL_returnMessage = SQL query SQL: Execution complete.
 SQL_outputData = 
 SQL_RECORD_COUNT = 4
 SQL Query SQL: Executing loop 2 of 4
 SQL_1 = T0102 
 SQL_H6WHSL = T0102 
 SQL_2 = TLSITEM02 
 SQL_H6ITNO = TLSITEM02 
 SQL_3 = Item 02
 SQL_H6ITDS = Item 02
 SQL_4 = 13
 SQL_H6ALQT = 13
 Message Builder:MsgBuilder6340 Executing this activity...

Activity name:MsgBuilder6340 id:1 started @ 05/16/2014 12:01:17 AM
Activity name:MsgBuilder6340 id:1 completed @ 05/16/2014 12:01:17 AM
 SQL_RETURN_MSG = Success
 SQL_RETURN_CODE = 0
 SQL_errorCode = 0
 SQL_informationCode = 0
 SQL_returnMessage = SQL query SQL: Execution complete.
 SQL_outputData = 
 SQL_RECORD_COUNT = 4
 SQL Query SQL: Executing loop 3 of 4
 SQL_1 = T0301 
 SQL_H6WHSL = T0301 
 SQL_2 = TLSITEM03 
 SQL_H6ITNO = TLSITEM03 
 SQL_3 = Item 03
 SQL_H6ITDS = Item 03
 SQL_4 = 17
 SQL_H6ALQT = 17
 Message Builder:MsgBuilder6340 Executing this activity...

Activity name:MsgBuilder6340 id:1 started @ 05/16/2014 12:01:18 AM
Activity name:MsgBuilder6340 id:1 completed @ 05/16/2014 12:01:18 AM
 SQL_RETURN_MSG = Success
 SQL_RETURN_CODE = 0
 SQL_errorCode = 0
 SQL_informationCode = 0
 SQL_returnMessage = SQL query SQL: Execution complete.
 SQL_outputData = 
 SQL_RECORD_COUNT = 4
 SQL Query SQL: Executing loop 4 of 4
 SQL_1 = T0302 
 SQL_H6WHSL = T0302 
 SQL_2 = TLSITEM04 
 SQL_H6ITNO = TLSITEM04 
 SQL_3 = Item 04
 SQL_H6ITDS = Item 04
 SQL_4 = 19
 SQL_H6ALQT = 19
 Message Builder:MsgBuilder6340 Executing this activity...

Activity name:MsgBuilder6340 id:1 started @ 05/16/2014 12:01:18 AM
Activity name:MsgBuilder6340 id:1 completed @ 05/16/2014 12:01:18 AM

Activity name:End id:1 started @ 05/16/2014 12:01:18 AM
 Activity End: Executing End activity
Activity name:End id:1 completed @ 05/16/2014 12:01:18 AM

Workunit 70 for process NewPickingList execution completed @ 05/16/2014 12:01:18 AM

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.

Published by

thibaudatwork

ex- M3 Technical Consultant

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

  1. 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?

    Thanks,
    Scott

    Like

    Reply

    1. 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

      Like

      Reply
  2. Pingback: Hello Google Glass from Infor Process Automation | M3 ideas
  3. Pingback: M3 Picking Lists in Google Glass | M3 ideas

Leave a comment