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.

How to create a picking list in M3

As part of my Google Glass project to display Infor M3 picking lists on Glass, I had to learn how to easily create picking lists in M3. M3 is a comprehensive Enterprise resource planning (ERP) suite that does customer sales, supply chain, manufacturing, inventory, packing, shipping, accounting, and many other functions. It takes learning and practice to conquer it. I’m a technical consultant for M3, not a functional consultant, so I had to ask for help for the setup to create picking lists. My dear colleagues Philip Cancino, M3 Business Consultant – SCM at Infor in Manila, and Marie-Pascale Authié, Fashion Solution Consultant at Infor in Chicago generously helped me.

I recorded Philip’s and Marie-Pascale’s instructions and the final setup, and I’m sharing the results here so you can follow my Glass project along. Watch the videos in full screen and in high definition to see the small details. Also, Philip’s video has audio, and I transcribed it, so you can turn on closed captioning (CC) if needed; the other videos don’t have audio.

Philip’s instructions and demo

Philip showed me detailed instructions to create a picking list on one of the Infor Education servers that was already setup for Manufacturing, and we recorded a demo.

Philip’s instructions are:

  1. Create a customer order in M3 Customer Order. Open – OIS100
  2. Create order lines in M3 Customer Order. Open Line – OIS101
  3. Select the order > Related Options > Delivery Toolbox CTRL+43; it takes us to M3 Delivery. Open Toolbox – MWS410
  4. Select the delivery > Related Options > Release for Picking CTRL+32
  5. Refresh until Released for Picking RIP = 1; it can take a while for the auto-jobs
  6. Back in OIS300, the order will change to Status 44
  7. Back in MWS410, select the delivery > Related Options > Picking Lists CTRL+11; it takes us to M3 Picking List. Report – MWS420
  8. Select the picking list > Related Options > Pick List Lines CTRL+11; it takes us to M3 Picking List. Report Lines – MWS422
  9. It shows the picking list lines with item numbers, quantities, and stock locations; this is what I need for Google Glass
  10. Select one line at a time > Related Options > Confirm Issues CTRL+16, that’s what the picker will do on Google Glass to complete the pick list
  11. When all the picking list lines have been confirmed, the picking list and the delivery will change to status 90, and the order will change to status 66.

Here is Philip’s demo:

Back to basics

After Philip’s demo, I wasn’t able to reproduce the instructions on another server that had a different setup for Equipment service management and rental (ESM&R). So Marie-Pascale helped me setup that other server. She setup everything from scratch, mostly copying from existing records, then going back and forth adjusting what was important for my project until it was working as desired.

Setup 1/4 – configuration tables

First, Marie-Pascale set up of the configuration tables company, division, facility, warehouse, and stock locations:

  • M3 Company. Open – MNS095
  • M3 Company. Connect Division – MNS100
  • M3 Facility. Open – CRS008
  • M3 Warehouse. Open – MMS005
  • M3 Stock Location. Open – MMS010:
    • In panel B we set the Warehouse (WHLO) and the Location (WHSL)
    • In panel E we set the Name (TX40) of each location to the respective aisle, rack, and bin
    • In panel F I will later set the Geo codes XYZ based on my previous work Geocoding of Stock Locations in MMS010.

Here is my quick walkthrough of the result (pause the video as needed):

Setup 2/4 – order type

Then, Marie-Pascale set up the all-important order type, and she chose a fast order type that automatically allocates inventory, creates a picking list, and does not require packing nor shipping:

  • M3 CO Type. Open – OIS010:
    • Allocation method: 1-Automatic/manually
  • M3 CO Type. Connect Documents – OIS011:
    • Print document: Yes
  • M3 CO Type. Update Field Selection – OIS014:
    • Route: blank
    • Route departure: blank
    • Contact method: phone
  • M3 Dispatch Policy. Open – MWS010:
    • 030 Released for allocation: Yes
    • 040 Released for picking: Yes
    • 100 Auto print of picking lists: Yes
    • 160 Shipment assembly point: 9-Not used
    • 240 Packing reporting method: 0-Packing not used
    • 330 Automatic connection to shipment: 0-No auto connect

Here is the result:

Setup 3/4 – items and inventory

Then, Marie-Pascale setup the items and inventory:

  • M3 Item. Open – MMS001:
    • Status: 20-Released
    • Responsible
    • Make/buy code: 2-Purchased
    • Lot control method: 0-Not used
    • Lot numb method: 0-Manual entry
    • Purchase price
    • Sales price
    • Supplier
  • M3 Item. Connect Warehouse – MMS002:
    • Planner
    • Acquisition code: 2-Purchased
    • Status: 20-Released
    • Location
  • M3 Item. Connect Facility – MMS003
  • M3 Physical Inventory. Quick Entry – MMS310:
    • Warehouse
    • Item number
    • Location
    • Physical inventory quantity (STQI)
    • Status – ph inv (STAG): 1-Auto approved

Here is the result:

Setup 4/4 – customer and supplier

Then, Marie-Pascale the setup of the customer and supplier:

  • M3 Customer. Open – CRS610:
    • Status: 20-Definite
    • Order Type
    • Warehouse
  • M3 Supplier. Open – CRS620

Here is the result:

Final demo

And finally, here is the quick demo to create a customer order and get the picking list with item numbers, quantities, and stock locations to display in Google Glass:

And here is a screenshot of the final picking list:
result

They are the same steps as Philip’s, except now everything is automatic: allocation and picking are automatic, and there’s no packing nor shipping.

We had a problem with automatic allocation though, it failed, and we had to allocate manually in M3 Allocation. Perform Detailed – MMS121. Marie-Pascale said she will look into it.

Future work

Next, I will capture the event that creates the picking list, and I will gather more information about the Items, like names, and I will send the formatted picking list to Glass. For that, I will use Event Analytics and Infor Process Automation (IPA) and the Glass API.

 

That’s it!

Special thanks to Philip Cancino and Marie-Pascale Authié for making this possible. I wouldn’t have been able to progress without your help.

Subscribe to this blog to follow my Google Glass adventures. Comment. Share. Enjoy.

Google Glass

I just applied to get a pair of Google Glass.

Google Glass is an anticipated product from Google X for bringing Augmented Reality to the masses in a sports fashion pair of glasses containing a video camera, a Heads-Up Display, a processing unit running Android, Wifi connectivity, and a battery (c.f. the patent).

I was at Google I/O 2012 were they accepted pre-orders for Glass Explorer Edition but I made the regretful decision to not apply. Google is now offering a second chance: What would you do if you had Glass? Answer with #ifihadglass.

If I had Glass I would improve the workers job in a warehouse: I would show walking directions to the picking location, I would display information about the item, and I would keep track of the picking list. I’m an enthusiast I/On working on AR in the enterprise.

This would be a continuation of my previous implementation of Augmented Reality for M3.

Here are my three concepts pictures for Google Glass:

1

2

3

Here’s my application:

4

Wish me luck, and see you at Google I/O 2013.