Example MES Queries

The following will discuss a number of example queries. The image below demonstrates the different parts of the SOAP query for your reference.

SOAPQueryExplanation

*Note that the final query string will not contain any carriage returns after each line as indicated above.

Get all Part Definitions

We will develop a MES Query that will get all the Part Definitions and that will show the following columns: part number, part revision, part name and part description.

The MES Object to query: PD_Flow (Chapter 7 shows all MES Objects)

The PD attributes to query (From the diagram in Chapter 8):

sPDPN (Part Number)
sPDRN (Revision Number)
sPDName (Part Name)
sPDDescr (Part Description)

S_arJSColm

First we are going to define the S_arJSColm argument that defines the columns that will be returned in the Excel file:

[

{"sTyp":"string","sNm":"sPDPN","sTitle":"Part Number"},

{"sTyp":"string","sNm":"sPDRN","sTitle":"Revision"},

{"sTyp":"string","sNm":"sPDName","sTitle":"Name"},

{"sTyp":"string","sNm":"sPDDescr","sTitle":"Description"}

]

"sTyp":"string": The return type, we want to have all data as strings.

"sNm":"sPDPN": The MES Object attribute name.

"sTitle":"Part Number": The title that will in the header row of the excel spreadsheet

You can paste this string into the EZ_SoapReader into the S_arJSColm field. Before you do so, you have to make a single string, by removing the carriage returns.

S_o

You are going to query the MES Object Part Definition. So for the second argument S_o you can use the following string: {"sFlowName":"PD_Flow"}

S_arCheckVals and S_oSort

We will not use these for this example, so we will enter value: null into the EZ_SoapReader Fields.

Running the MES Query

After you entered the User Name, PassKey, specified the Result Path and file type, you can now create an Excel File by executing the mGenerateFile SOAP request, by pressing the RUN File button.

Saving and Loading the MES Query

If the MES Query worked like expected, you can now save it into an txt file by pressing the SAVE button in the EZ_SoapReader. This will create the following file:

This file can be loaded when you need it and can be adjusted either in the txt file or in the EZ_SoapReader.

Adding the Object State as an additional column

If we want to display the Object State in the Excel spread sheet as well, we just have to add an additional object to the S_arJSColm argument.

[

{"sTyp":"string","sNm":"sPDPN","sTitle":"Part Number"},

{"sTyp":"string","sNm":"sPDRN","sTitle":"Revision"},

{"sTyp":"string","sNm":"CurentStatus","sTitle":"State"},

{"sTyp":"string","sNm":"sPDName","sTitle":"Name"},

{"sTyp":"string","sNm":"sPDDescr","sTitle":"Description"}

]

CurrentStatus is an attribute that is defined for each EZ-MES Object

Only display Active Part Definitions

If we just want to export the Active Part Definitions we can add an object to the S_arCheckVals array. We will add the following object:

[

{

"sNm":"CurrentStatus",

"sTyp":"string",

"sOperation":"==",

"sVal":"Active"

}

]

The Part Definition attribute ("sNm") that is being checked is the "CurrentStatus".

The data type for the attribute ("sTyp") is "string"

We want to check which object have a status attribute that is equal to Active, so for the "sOperation" we will use the equal operation: "==" (two equal signs)

Last argument we define is the value: "sVal":"Active"

Note: S_arCheckVals is an array containing objects, so you need the ‘[ ]’ brackets to define the array, and ‘{ }’ define the objects of the array and separate them with commas. In this case there is only a single attribute.

Executing this MES Query will only return the Active parts.

The saved MES Query:

{"arJSColm":"[{\"sTyp\":\"string\",\"sNm\":\"sPDPN\",\"sTitle\":\"Part Number\"},{\"sTyp\":\"string\",\"sNm\":\"sPDRN\",\"sTitle\":\"Revision\"},{\"sTyp\":\"string\",\"sNm\":\"CurrentStatus\",\"sTitle\":\"State\"},{\"sTyp\":\"string\",\"sNm\":\"sPDName\",\"sTitle\":\"Name\"},{\"sTyp\":\"string\",\"sNm\":\"sPDDescr\",\"sTitle\":\"Description\"}]","o":"{\"sFlowName\":\"PD_Flow\"}","arCheckVals":"[{\"sNm\":\"CurrentStatus\",\"sTyp\":\"string\",\"sOperation\":\"==\",\"sVal\":\"Active\"}]","oSort":"null"}

Sort on the Part Number

If we want to sort the Part Definition list by the Part Number, we can do so by defining the S_oSort argument:

{

"sSort":"SortA",

"sTyp":"string",

"sNm":"sPDPN"

}

SortA: Ascending Sort

sTyp: Data is of the type string

sNm: The Part Definitions attribute, in this case sPDPN is the Part Number.

Get the Part Record Job Number for a UID

The MES system keeps its own unique ID’s for all MES Objects. We call these Unique ID’s Job numbers (aka sJob).

For some queries it is important to know the Job Number. This MES query shows you how you can query the Job Number for any given UID.

We will display the Job Number in a column we will give the title: UID. The Job Number can be queried by querying the ‘sJob’ attribute (sNm) on the PR (Part Record) Object.

S_arJSColm

Display the sJob attribute as text in a column with name Job Number:

[

{

"sTyp":"text",

"sNm":"sJob",

"sTitle":"Job Number"

{

"sTyp":"text",

"sNm":"sPRFR",

"sTitle":"Traveler Job Number"

}

]

Single line:

[{"sTyp":"text","sNm":"sJob","sTitle":"Job Number"},{"sTyp":"text","sNm":"sPRFR","sTitle":"Traveler Job Number"}]

A second column is added to display the FR Job number.

S_o

To get the Job Name for the Part Record we have to query the Part Records: the PR_Flow

{"sFlowName":"PR_Flow"}

S_arCheckVals

In the arCheckVals you define the condition you would like to check. In this case we are looking for Part Records with the Unique ID equals (==) to SN0001. Because the jobname of a PR object is the Unique ID, we can check if the jobname representation of the Part Record equals to the assigned Unique ID.

NOTE: SN0001 is an example, this depends on the Uniques ID’s you use in your system.

[

{

"sNm":"sJob",

"sTyp":"jobname",

"sOperation":"==",

"sVal":"SN0001"

}

]

Single line: [{"sNm":"sJob","sTyp":"jobname","sOperation":"==","sVal":"SN0001"}]

S_arCheckVals is an array (array = […] in JSON notation) of objects (object = {…} in JSON notation)

S_oSort

We will not sort the results so we can use ‘null’

The result in the EZ_SoapTester is shown in the following figure (Note: pressing the RUN JSON button will return the JSON string shown in the lower left corner):

Get the parent UID’s

In some case it could be use full to get the Unique ID’s of the material that is consumed by a part with a specific Unique ID.

E.g. You want to know the Serial Numbers of the parts that were used to assemble the part with Serial Number: SN0001

To get these Unique ID’s you first have to convert the Unique ID SN0001 into a job number (the internal UID for the MES object). You can do this using the MES query explained in the previous sub-chapter.

Suppose the Job number for SN0001 is PR10-000010 then we can use the following query to get the Unique ID’s of the consumed parts:

S_arJSColm

For the consumed parts the following columns will be displayed: The Unique ID, the Part State and the Part Definition for the Part Record:

[

{"sTyp":"string","sNm":"sPRUniqueID","sTitle":"UID"},

{"sTyp":"string","sNm":"sPRPartType","sTitle":"Part State"},

{"sTyp":"jobname","sNm":"sPRVP","sTitle":"Part Definition"}

]

S_o

We need to query the Part Records:

{"sFlowName":"PR_Flow"}

S_arCheckVals

We will check for each part record if PR10-000010 is in the parent array. If that is the case, that Part is consumed by PR10-000010. So for the operation we have to contains.

[

{

"sNm":"sPRarPRParents",

"sTyp":"string",

"sOperation":"contains",

"sVal":" PR10-000010"

}

]

S_oSort

Sort the UID assending

{

"sSort":"SortD",

"sTyp":"string",

"sNm":"sPRUniqueID"

}

The result of the MES query as entered into the EZ_SoapReader is shown in the following figure:

Get the non null values for a specific NV per Part

S_arJSColm

[

{"sTyp":"string","sNm":"sPRUniqueID","sTitle":"UID"},

{"sTyp":"string","sNm":"sNVRValue","oHash":{"sKey":"NV10000010","sVal":"obj_hash_NVR"},"sFilterValue":"& != []","sTitle":"Failure Mode","iW":100}

]

S_o

{"sFlowName":"PR_Flow"}

S_arCheckVals

[

{"_EZQueryItem":true,"sNm":"sNVRValue","sTyp":"string","sOperation":"!=","sVal":"","bNull":true,"bAnd":true,"bOr":false,"bCase":false,"arJbConv":[],"oHash":{"sKey":"NV10000010","sVal":"obj_hash_NVR"}}

]

Explanation

"sFilterValue":"& != []": Filter to filter all empty values. Now only parts will show that have a value for this part record. This will not work, it has to be defined in the arCheckVals

"oHash":{"sKey":"NV10000010","sVal":"obj_hash_NVR"}: Will specify that you would like to get the values for NV10000010 that are stored on this Part Record. It will only show the current value, not the historic values. If you want to access the historic values, you have to query the NVR.

"sOperation":"!=","sVal":"": This will do the actual filtering to take care the empty values are filtered out.

Get all the Data Entries for a list of Parts

This query works the same as the get data listbox on the Execution Desktop. You could configure this listbox to show the data as described in the following sub chapter and use fidler to capture the MES Query that is sent to the server.

Set the List with Recorded Data Values

On the Execution Desktop a list with recorded data values can be opened (the icon showing the histogram). After opening the list take the following steps:

Increase the time span to make sure that when the data values are recorded is included in the time span.
Pick the NV values you want to display form the ‘Value Name’ pull down.
Press the refresh button in the upper right corner (you will see that an additional column is added to the list box).
Repeat step 2 and 3 until you have all the values you are interested in.
You can filter the parts you are interested in by adding filters to the list-box columns. E.g you could filter for all scrapped parts if you are interested in failure modes.

Now export to Excel and use Fidler to capture the MES Query.

Enter the data into the EZ_SoapReader

From the text obtained from the Fiddler program, extract the following EZ_SoapReader Arguments:

S_arJSColm

[

{"i":0,"sTyp":"jobname","sNm":"sPRLT","sTitle":"Lot","iW":90},

{"i":1,"sTyp":"string","sNm":"sPRUniqueID","sTitle":"UID","iW":90},

{"i":2,"sTyp":"number","sNm":"sPRQty","sTitle":"#","iW":60},

{"i":3,"sTyp":"string","sNm":"sPRPartType","sFilterValue":"& contains [Scrapped]","sTitle":"Part Type","iW":50},

{"i":4,"sTyp":"string","sNm":"sPRFRProcessState","sTitle":"Part State","iW":50},

{"i":5,"sTyp":"jobname","sNm":"sPRVP","sTitle":"Current Part","iW":180},

{"i":6,"sTyp":"jobname","sNm":"sPRSD","sTitle":"Current Step","iW":180},

{"i":7,"sTyp":"string","sNm":"sNVRValue","oHash":{"sKey":"NV10000002","sVal":"obj_hash_NVR"},"sFilterValue":"","sTitle":"Failure Mode","iW":100},

{"i":8,"sTyp":"string","sNm":"sNVRValue","oHash":{"sKey":"NV10000007","sVal":"obj_hash_NVR"},"sFilterValue":"","sTitle":"Failure Mode","iW":100},

{"i":9,"sTyp":"string","sNm":"sNVRValue","oHash":{"sKey":"NV10000010","sVal":"obj_hash_NVR"},"sFilterValue":"","sTitle":"Failure Mode","iW":100},

{"i":10,"sTyp":"string","sNm":"sNVRValue","oHash":{"sKey":"NV10000028","sVal":"obj_hash_NVR"},"sFilterValue":"","sTitle":"Failure Mode","iW":100},

{"i":11,"sTyp":"string","sNm":"sNVRValue","oHash":{"sKey":"NV10000030","sVal":"obj_hash_NVR"},"sFilterValue":"","sTitle":"Failure Mode","iW":100}

]

In this example 12 columns (from 0 to 11) are displayed. The first 7 columns show Part Record Attributes:

sPRLT: The lot number
sPRUniqueID: The Unique ID
sPRQty: The quantity (should be 1 for discrete parts, and can be any number for non-discrete parts)
sPRPartType: The current state of the Part Record
sPRFRProcessState: The State of the Traveler the part is currently on.
sPRVP: The type of Part (Part Definition)
sPRSD: The current Step where the part is.

NOTE: These are attributes of the CURRENT Part Record. These Part Attributes could have different values at the moment data for the NVRs was recorded.

Column 7 to 11 are 5 NV values that for which the corresponding NVR containing the recorded data will be shown in the list.

Note you are now accessing NVR attributes through a hash table that is stored on the Part Record:

"sNm":"sNVRValue" Defines the NVR attribute you want to get. (The real recorded data value)

"oHash":{"sKey":"NV10000002","sVal":"obj_hash_NVR"} defines which NV has to be pulled from the hash table. In this case NV’s 2, 7, 10, 28, and 30 are pulled.

You can add more NV’s if you would like to get more NV in the returned file.