Batch Production Records

The Production Tracking data store can be queried and modified using the Lua API. The queries use a syntax similar to the MongoDB query language such as the $lte and $gte comparison operators.

ISA-88 schema which describes the structure of the Batch Production Records is documented here. It’s good to have it as a reference when using the functions documented below.

Error Handling for isa88.db library functions

The following isa88.db functions will return error codes and messages along with the result:

All these functions will return three values following the convention shown below:

local result, err_code, err_msg = isa88.db.<func>()

If result is nil, then err_code is a numeric value and err_msg an error string. The error code allows to differentiate hard (permanent) and soft (transient) errors. The function can be retried in the event of transient errors if so desired.

The following example using the isa88.db.find function demonstrates how this works. If the result is nil and transient errors are encountered the function is retried (upto 3 times). Otherwise, an error message describing the nature of the error.

local isa88 = require "isa88"

local find = function(q)
    local retries = 3
    local result, err_code, err_msg
    while retries > 0 do
        retries = retries - 1
        result, err_code, err_msg = isa88.db.find(q)
        if result then
            return result
        elseif err_code ~= isa88.db.error_code.transient_failure then
            -- It doesn't make sense to retry
            return result, err_msg
        end
    end
    return result, err_msg
end

local result, err_msg = find([[ ... custom BPR query ...]])

Please look at the isa88.db.update description for more information on error handling with this function.

Querying Batch Production Records (isa88 format)

The Batch Production Record isa88.db.find() function is provided by the isa88.lua library.

The find() function takes a table or a JSON string which represents the MongoDB query by which the user wants to filter the Batch Production Records. Additionally, the user can pass a second table of options to configure the behavior of the function.

The query should specify the type of the document and the intended query for that type. find() returns an array of tables containing filtered documents of the type specified.

Example 1. Query Events within a range of time

For example, here is how the user can find documents of type Event whose TimeStamp is within a range of time.

local isa88 = require "isa88"
return  isa88.db.find([[
{
    "Event": {
	    "TimeStamp": {
	        "$lte": {
		        "$date": "2019-06-24T12:30:00.0Z"
            },
            "$gte": {
		        "$date": "2019-06-23T12:00:00.0Z"
            }
	    }
    }
}
]])

The returned array of tables has the following structure.

[
   {
      "ID":<bpr_id>,
      "Events":[{...}, {...}]
   },
   {
      "ID":<bpr_id>,
      "Events":[{...}, {...}]
   },
   .
   .
]

If the BatchTracker is used with the partial batches option enabled, then several BPRs with the same BatchID can be created. Each of this BPRs will be linked to a different equipment. In order to get only one BPR in this case, the equipment ID can be included into the query.

Example 2. Query BatchId and Equipment ID
local isa88 = require"isa88"
local batchId = "Demo_Batch_1619042181991"
local eq_id = 281483214520320

local query = [[  [{"BatchProductionRecord": { "BatchID": "%s", "EquipmentID": %i }}]  ]]
doc = isa88.db.find(query:format(batchId, eq_id))

return doc

The same query can also be used for isa88.db.findRetunrBatch(). Additionally the query can also be based on the euipment name, rather than the equipment ID.

Example 3. Query BatchId and Equipment Name
local isa88 = require"isa88"

local batchId = "Demo_Batch_1619042181991"
local eqName = "/TheCompany/Plant1/Bulk/Building_A10/Floor_C/Line1"

local query = [[ [{"BatchProductionRecord": { "BatchID": "%s", "EquipmentID": {"$elemMatch": {"@name": "%s" } } } }]  ]]
doc = isa88.db.find(query:format(batchId, eqName))

return doc

The user can use standard MongoDB keywords like "$and", "$or", "$elemMatch" or "$in" to construct complexe query conditions. This is peticulary usefull to filter for timestamp, material type, equipmentID.

Example 4. Query complex conditions
local isa88 = require "isa88"
return isa88.db.find( [[
  [
    { "EquipmentProceduralElement":
      { "$and":
        [
          { "Parameter":
            { "$elemMatch":
              { "Description.#": "StartTime", "Value":{"$lt": 1605695000000} }
            }
          },
          { "Parameter":
            { "$elemMatch":
              { "Description.#": "EndTime", "Value":{"$gt":  1605694000000} }
            }
          },
          { "EquipmentProceduralElementType.@name": "Unit Procedure" }
        ]
      }
    }
  ]
]] )

The user can also specify conditions on multiple document types at once. In that case, the full 'Batch Production Record' documents which match the criterion are returned regardless of the type of documents specified. This is especially usefull to combine conditions of different document types which are spread across different MondoDB collections. Internally, this will be implemented with subquerries ($lookup) and does only allow to combine the conditions in an "and" manner. Additionally the performance might depend on the order of the subquerries.

Example 5. Query conditions on multiple document types
local isa88 = require "isa88"
return isa88.db.find([[
[
    {
        "BatchProductionRecord": {
            "ID": {"$regex": "^a","$options": ""}
        }
    },
    {
        "Event": {
            "Value": 3
        }
    },
    {
        "ProductionResponse": {
            "TimeStamp": {
                "$lte": {"$date": "2019-01-24T12:30:00.0Z"},
                "$gte": {"$date": "2018-01-23T12:00:00.0Z"}
            }
        }
    }
]
]])

Querying Batch Production Records (simple format)

The user can use the 'isa88.db.findReturnBatch()' function to query BPRs, but in this case the result will be transformed in a simpler presentatition which is not structured according to the isa88 standard, but reflects the internal structure of the BPR during creation.

The query prameters, as well as the querry options follow the same format, as described for 'isa88.db.find'.

Querying IDs of Batch Production Records

The user can use the 'isa88.db.getIDs()' function to query IDs and root_ids of BPRs. This is especially usefull if a large number of documents shall be requested as resonse to a query. Then the ID query returns a unique identification of each relevant document. Consecutive queries can then requests a small number of documents each, without missing documents or duplicates.

The query to retrieve docments based on root_ids can look like this:

Example 6. Query documents by root_id
local options
options = {limit=1000, timeOut=30000}
local doc_ids, rootIds, err =  isa88.db.getIDs(query, options)

local idQuery = JSON.encode( {{BatchProductionRecord={["_root"]= {["$in"]=rootIds}}}} )
options = {limit=1000, timeOut=30000}
local res,err1,err2 = isa88.db.findReturnBatch(idQuery, options)

The query prameters, as well as the querry options follow the same format, as described for 'isa88.db.find'.

Counting Batch Production Records

The user can also use the isa88.db.count() function to count the number of documents that satisfy some specified conditions. The arguments it accepts are similar to the find() function. Instead of returning an array of documents, it returns an integer count.

Options

The user can specify a number of options to override the default behavior of the function.

Option

Default Value

Description

data_store

nil

Specifies a data store for the find/count operation. If not specified, this defaults to the System Production Tracking Data Store for Master Cores. For Local Cores, this option is required. The value is an inmation objspec, identifying the System object or a custom store object.

timeOut

10000

Specifies the time in milliseconds for a single database operation after which the query is aborted

batchSize

10000

The maximum number of items returned by MongoDB in a single batch.

returnSubDocuments

true

If set to true, children documents of a document type according to the ISA-88 specification are fetched from MongoDB and a document that is constructed according to the ISA-88 specification is returned. If not, only the top level fields of the specified document type are returned.

This option is not applicable to count()

limit

nil

The maximum number of matching documents to return.

skip

nil

The number of matching documents to skip before returning results.

Updating Batch Production Records

The isa88.db.update() function updates or replaces an existing Batch Production Record or inserts a new Batch Production Record

The Batch Production Record update() function is provided by the isa88.lua library.

The update() function has two parameters:

  1. doc (mandatory)
    A Lua table or a valid JSON object. The table must represent a valid S88 document (a BatchProductionRecord, Comment, Sample, etc.)

  2. options (optional)
    A Lua table or valid JSON object with different fields. The fields used to query for existing documents (bpr, parent, and filter) only support the specification of document attributes. Trying to include attributes for sub-documents will result in an error - use the isa88.db.find() function instead and then use for example the EntryID attribute in a parent or filter specification.

The options fields are described in the following table:

Option Default Value Description

data_store

nil

Specifies a data store for the update operation. If not specified, this defaults to the System Production Tracking Data Store for Master Cores. For Local Cores, this option is required. The value is an inmation objspec, identifying the System object or a custom store object.

operation

isa88.db.update_op.insert

Specifies one of the update operations insert (0), replace (1) or update (2) (these are coded in the isa88.db.update_op table).

upsert

false

For update or replace operations, inserts the document if upsert = true and filter does not match any documents. Only applies to updating or replacing full Batch Production Record documents.

multi

false

For update operations, updates all documents that match the filter if multi = true. Otherwise, updates at most one document. For replace operations, removes all documents that match the filter if multi = true. Otherwise, the replace operation fails if more than one document matches. This options is currently not supported and ignored for insert operations.

doc_type

"BatchProductionRecord"

Specifies the S88 document type of the doc argument.

parent

nil

A S88 document used to match parent documents in a S88 Batch Production Record for "insert" operations (specified in options.operation). If parent is nil, doc is inserted as top-level document without parent. If a parent document can’t be found unambiguously using both filter and bpr options, the operation fails.

parent_type

BatchProductionRecord

The document type (see doc_type) for the parent document.

filter

nil

A S88 document used to match documents for "update" and "replace" operations. filter must not be nil for such operations. The filter document type must match the document type of the doc argument.

filter_type

Same as the doc document type

The doc_type (see above) for the filter document

bpr

nil

Specifies a filter for the attributes of a Batch Production Record. This option can be used to further constrain the parent or filter option to the set of matched Batch Production Record documents. If a parent document can’t be found unambiguously using both filter and bpr options, the operation fails.

validate_refs

false

If true, DataReference attributes in Change and Comment documents are validated. The validation is performed locally if a new stand-alone BPR with embedded Change or Comment sub-documents is inserted. Otherwise, DataReference attributes are validated by checking the existence of the referenced document attribute in the database.

wait_for_completion

true

If true, the update function will synchronously execute the operation (including database commands) and the return values indicate success or failure directly. If false, the function will only check the validity of the input parameters and return as quickly as possible after queuing database operations.
The replace operation currently only supports the replacement of full Batch Production Record documents. If multi is specified, all matching BPRs are deleted and then a single replacement document is inserted.

The bpr option is useful for update and insert operations, if the filter or parent option would match sub-documents from different Batch Production Records. Then the bpr option can be used to restrict the matched sub-documents to the set of BachProductionRecord documents matching the attributes specified in the bpr option.

The update() function always returns three values: a numeric code and string message (as defined in the isa88.db.error table) and a unique string id identifying the operation. If wait_for_completion = false, this id can be used to identify matching events published by a BPR Publisher object.

Insert Operations

Insert operations (options.operation = isa88.db.update_op.insert) are used to insert new stand-alone ISA88 documents (typically full BatchProductionRecord documents) or to insert sub-documents into existing records.

Example 7. Insert a new BatchProductionRecord document

This example inserts a new stand-alone Batch Production Record:

local isa88 = require "isa88"

local bpr = isa88.BatchProductionRecord{
  BatchID = "insert-bpr-01",
  EquipmentID = "/A/B",
  ControlRecipes = {
    ID = "CR-01"
  }
}
isa88.db.update(bpr)
Example 8. Insert a Comment into an existing BatchProductionRecord document

This example inserts a new Comment sub-document into an existing Batch Production Record:

local isa88 = require "isa88"

local comment = isa88.Comment{
  Comment = "pipe repaired"
}
local insert_options = {
  bpr = { BatchID = "insert-bpr-01" }
}
isa88.db.update(comment, insert_options)
Example 9. Insert a RecipeElement into an existing ControlRecipe document

This example inserts a new RecipeElement sub-document into an existing Batch Production Record, specifying its direct parent control recipe:

local isa88 = require "isa88"

local recipe_element = isa88.RecipeElement{
  ID = "RE-01",
  RecipeElementType = syslib.model.codes.S88RecipeElementType.PHASE
}
local insert_options = {
  bpr = { BatchID = "insert-bpr-01" },
  parent = { ID = "CR-01" },
  parent_type = "ControlRecipe"
}
isa88.db.update(recipe_element, insert_options)
Example 10. Error due to an ambiguous parent

This example tries to insert a new ControlRecipe sub-document into an existing Batch Production Record but fails because a parent document can’t be found unambiguously:

local isa88 = require "isa88"

local control_recipe = isa88.ControlRecipe{
  ID = "CR-01"
}
local insert_options = {
  parent = { LotID = "Lot-01" },
  parent_type = "BatchProductionRecord"
}
local code, msg, opid = isa88.db.update(control_recipe, insert_options)

If there is more than one BatchProductionRecord with the LotID specified, the call fails with the message ambiguous parent. It can be fixed either by modifying the parent option or by specifying the bpr option to make it possible to find the parent document unambiguously.

local isa88 = require "isa88"

local control_recipe = isa88.ControlRecipe{
  ID = "CR-01"
}
local insert_options = {
  parent = { LotID = "Lot-01" },
  parent_type = "BatchProductionRecord",
  bpr = {EntryID = "82124a5c-c402-11e9-8b93-a0f3c16f6109"},
}
local code, msg, opid = isa88.db.update(control_recipe, insert_options)

Update Operations

Update operations (options.operation = isa88.db.update_op.update) are used to update attributes of existing ISA-88 documents. If upsert = true, then Batch Production Record documents are inserted if the filter option does not match any document.

Example 11. Update a Batch Production Record by adding a Lot ID.

This example updates an existing Batch Production Record, adding an entry to the LotID array attribute.

local isa88 = require "isa88"

local update_options = {
  operation = isa88.db.update_op.update,
  filter = { BatchID = "insert-bpr-01" }
}
isa88.db.update({ LotID = "lot-0001"}, update_options)
Example 12. Update the description of Control Recipe documents.

This example updates the description of all Control Recipe documents of batch production records which contain the specified BatchID.

local isa88 = require "isa88"

local update_options = {
  operation = isa88.db.update_op.update,
  bpr = { BatchID = "insert-bpr-01" },
  filter = {}, -- matches all Control Recipe documents
  multi = true
}
isa88.db.update(
  isa88.ControlRecipe{ Description = "Imported control recipe" },
  update_options
)

Replace Operations

Replace operations (options.operation = isa88.db.update_op.replace) are used to fully replace existing ISA-88 documents. If upsert = true, then the replacement document is inserted if the filter option does not match any document.

Example 13. Replace a Batch Production Record

This example replaces all Batch Production Records which match the specified filter with the specified record. If no records match the filter, the record is inserted.

local isa88 = require "isa88"

local replace_options = {
  operation = isa88.db.update_op.replace,
  filter = { BatchID = "BPR-001" }, -- matches all BPRs with a BatchID array element of BPR-001
  upsert = true, -- insert if no match found
  multi = true -- replace all matching BPRs
}

local bpr = isa88.BatchProductionRecord{
  BatchID = "BPR-002",
  Description = "replaces all BPRs with BatchID BPR-001"
}

isa88.db.update(bpr, replace_options)
Example 14. Replace a single Batch Production Record

This example tries to replace a single Batch Production Record which matches the specified filter with the specified record. If more than one document matches the filter, the operation fails with the message ambiguous replacement target since multi is false.

local isa88 = require "isa88"

local replace_options = {
  operation = isa88.db.update_op.replace,
  filter = { BatchID = "BPR-001" }, -- the operation fails if more than one BPR with BatchID 'BPR-001' exists
  upsert = true -- insert if no match found
}

local bpr = isa88.BatchProductionRecord{
  BatchID = "BPR-002",
  Description = "replaces all BPRs with BatchID BPR-001"
}

isa88.db.update(bpr, replace_options)

Annotating Batch Production Records

The isa88.db.annotate() function adds a textual comment to an existing Batch Production Record.

The Batch Production Record annotate() function is provided by the isa88.lua library.

The annotate() function has three parameters:

  1. doc (mandatory)
    A Lua table or valid JSON object, representing the Batch production Record you wish to annotate.

  2. annotation (mandatory)
    A Lua table or valid JSON object containing the annotation. This is added into the BPR as a "Comment" document type.

  3. options (optional)
    A Lua table or valid JSON object with the validate_refs and wait_for_completion fields as described for the update function above.

The annotate() function is a convenience wrapper around update() and does not provide additional functionality at the moment. The update operation is always "insert" and only the validate_refs and wait_for_completion options are applicable.
Example 15. Annotate a Batch Production Record

This example annotates an existing Batch Production Record by adding a ISA-88 Comment sub-document to it.

local isa88 = require "isa88"

isa88.db.annotate(
  { BatchID = "BATCH-0001" }, -- Annotate a record with this BatchID
  { Comment = "Custom annotation" }
)

Comment and Change Data References

The ISA-88 objects Comment and Change may have a DataReference attribute set. This attribute references data in the same Batch Production Record by specifying one or more of the following fields:

DataReference.ReferenceObjectType

Specifies the referenced object type, using a coding from syslib.model.codes.S88ProductionRecordEntryType or a string representation of a valid code from this coding group. This is a mandatory field.

DataReference.ReferenceEntryID

Specifies the EntryID attribute the referenced document, if available.

DataReference.ReferenceID

Specifies the ID attribute of the referenced document, if available.

DataReference.Attribute

Optionally specifies a path pointing to a particular attribute / data value in the referenced document. The path separator is a . and arrays indexes are 0-based. For example, referencing a particular batch id in the BatchID array attribute looks like BatchID.3. Referencing the EquipmentID attribute inside a TagSpecification attribute of a DataSet document looks like TagSpecification.0.EquipmentID

A DataReference points to a particular ISA-88 (sub-)document using the ReferenceObjectType attribute and one or both of the ReferenceEntryID and ReferenceID attributes. It may then further qualify the reference by specifying an attribute path via Attribute, pointing into the referenced (sub-)document.

NOTE

If you have a ISA-88 object available in your script, make use of the isa88.getDataReference() function described below. This function always returns a valid DataReference attribute.

Creating Data References

A DataReference attribute can be created manually by specifying the attributes described above.

Example 16. Manual data reference creation for a DataSet document

Create a data reference into a DataSet document, referencing the EquipmentID attribute.

local isa88 = require "isa88"

local ref = isa88.DataReference{
  ReferenceObjectType = syslib.model.codes.S88ProductionRecordEntryType.ET_DATASET,
  ReferenceEntryID = "abcd-efghijklmn-opqr",
  Attribute = "TagSpecification.0.EquipmentID"
}
Example 17. Manual data reference creation for a MaterialActual document

Create a data reference into a MaterialActual document, referencing a MaterialLotID entry. The MaterialActual document does not have an EntryID attribute.

local isa88 = require "isa88"

local ref = isa88.DataReference{
  ReferenceObjectType = syslib.model.codes.S88ProductionRecordEntryType.ET_OTHER,
  ReferenceID = "MA-0001",
  Attribute = "MaterialLotID.2"
}
ref.ReferenceObjectType["@name"] = "MaterialActual"

If the document which is to be referenced is available in the Lua script, the function isa88.getDataReference() can be used. It returns a ISA-88 DataReference object based on two parameters:

  1. doc (mandatory)
    A ISA-88 object representing a (sub-)document supporting either the EntryID or ID attribute.

  2. attribute (optional)
    An attribute path specification into the doc object. Path elements are separated by a dot and array indices are 0-based. E.g. "TagSpecification.0.StartTime" for a DataSet object.

The following examples create references to the same elements as the examples for the manual reference creation above, but use the getDataReference() function instead.

Example 18. Automatic data reference creation for a DataSet document

Create a data reference into a DataSet document, referencing the EquipmentID attribute.

local isa88 = require "isa88"

local bpr = isa88.BatchProductionRecord{
  DataSets = {
    TagSpecification = {
      EquipmentID = "/a/b"
    }
  },
}

local ref = isa88.getDataReference(bpr.DataSets[1], "TagSpecification.0.EquipmentID")
bpr.Comments = {
    Comment = "Wrong equipment id",
    DataReference = ref
}
Example 19. Automatic data reference creation for a MaterialActual document

Create a data reference into a MaterialActual document, referencing a MaterialLotID entry. The MaterialActual document does not have an EntryID attribute.

local isa88 = require "isa88"

local bpr = isa88.BatchProductionRecord{
  ProductionResponses = {
    SegmentResponse = {
      ID = "SR-01",
      MaterialActual = {
        ID = "MA-01",
        MaterialLotID = {
          "LOT-01", "LOT-02", "LOT-03"
        }
      }
    }
  }
}

local ref = isa88.getDataReference(
    bpr.ProductionResponses[1],
    "SegmentResponse.0.MaterialActual.0.MaterialLotID.2"
)
bpr.Comments = {
    Comment = "Wrong lot id",
    DataReference = ref
}

Resolving Data References

The isa88.resolveDataReference() function resolves a DataReference object for a given ISA-88 object or table and returns the referenced value. It accepts two parameters:

  1. doc (mandatory)
    A table (usually a proper ISA-88 object) used to resolve the reference ref.

  2. ref (mandatory)
    A ISA-88 DataReference object or a regular table encoding a valid data reference.

If the reference is invalid a Lua error is raised. If it is resolvable, the function returns the referenced value and nil otherwise.