esi-r-aprm

This is an ESI Rails library. Please refer to Declarative Scripting to first understand the concept, in case you are not familiar with it already.

The esi-r-aprm library supplies an interface to AspenTech Production Record Manager (APRM). It allows to fetch batch records from APRM, in order to further process in the inmation environment.

Changes

version date description

0.1.1

2018-05-27

Initial release

Dependencies

library version built-in Notes

luasql.odbc

2.3.4

yes

Deprecated, shall be replaced by esi-odbc in 0.1.2

dkjson

2.5

yes

esi-odbc

0.1.2

no

For ODBC connections, queries and MERGE constructions

esi-string

0.1.2

no

esi-tool

0.1.1

no

Known Issues

  • The library does not meet ESI Coding Standards. Work in progess.

  • Documentation (this markdown file) is not completed

  • Unit tests are missing

Example Flow

The following example serves as a template how the esi-r-aprm library is used in production. The first four (ESI:) commands set the esi-r environment for the execution, the first ESI:APRM(): command loads the esi-r-aprm library and configures the APRM monitor, the command ESI:APRM():GETNEXTBATCH(ESI:APRM():OPTIONAERECORD()) instructs the esi-r-aprm library to start monitoring and to create an Asset Effectiveness record for each new batch record found in APRM.

In the example we are only using fantasy names derived from a working implementation.

-- APRM Batch Record Fetching and Asset Effectiveness Event Creation/Transmission for the XX-X191T Propalan Plant (example)

-- ESI inclusion, only this library is required
-- please refer to the esi-r-lib.lua and esi-r-lib.md for further information
local ESI=require('esi-r')

-- This must! be the first call
-- documented in esi-r-lib.md
ESI:RSCRIPT():START()

-- This will turn on tracing and hard errors, the line shall be commented out
-- once the script is in productive use, documented in esi-r-lib.md
ESI:DEBUG()

-- This line defines the error options to be used in production
-- (line above to be commented then), documented in esi-r-lib.md
ESI:SETERROROPTION("MAIL")

-- Defines the AssetEffectiveness target database table.
-- This is optional, and only required in case Asset Effectiveness is the desired
-- use case for the script. In the given case, the library is instructed to forward
-- Asset Effectiveness records to a Connector machine 'WIN999915971', which would
-- in turn merge the records into the specified database table
ESI:APRM():SETAETARGET("WIN9915971","[AE_Staging].[etlAE].[Production")

-- This loads the esi-r-aprm library, sets the database connection to APRM (ODBC data source or DSN), this ODBC DSN must exist and the Connector service must have the right account (username, password ommitted), this is mandatory to set
ESI:APRM():SETDB("IP21-X191T-32")

-- This selects the logical batch datasource in APRM, mandatory to set
ESI:APRM():SETBATCHSOURCE("IP21-X191T")

-- This selects the logical batch area in APRM, mandatory to set
ESI:APRM():SETAREA("X191T")

-- This sets the plant name, mandatory to set
-- All following calls will relate to this plant
ESI:APRM():SETPLANTNAME("XX-X191T Propalan")

-- Add all units to monitor
-- Note: Only units which are added using ADDUNIT will be processed. A certain APRM
-- batch system may cover more units. If a batch record is retrieved which only consists
-- of units which have not been added. Please note: The chained SETAECODE command is
-- equivalent to SETSAPCODE and SETERPCODE.
-- In all cases, this would be the unit code under which this production unit is
-- listed as an Asset in the ERP system (which in often cases might be SAP)
ESI:APRM():ADDUNIT("UNIT_01"):SETAECODE("87873364")
ESI:APRM():ADDUNIT("UNIT_02"):SETAECODE("87873369")
ESI:APRM():ADDUNIT("UNIT_03"):SETAECODE("87873357")
ESI:APRM():ADDUNIT("UNIT_04"):SETAECODE("87873367")
ESI:APRM():ADDUNIT("UNIT_05"):SETAECODE("87873368")
ESI:APRM():ADDUNIT("UNIT_06"):SETAECODE("87873381")
ESI:APRM():ADDUNIT("UNIT_07"):SETAECODE("87873385")
ESI:APRM():ADDUNIT("UNIT_08"):SETAECODE("87873384")
ESI:APRM():ADDUNIT("UNIT_09"):SETAECODE("87873373")
ESI:APRM():ADDUNIT("UNIT_11"):SETAECODE("87873359")
ESI:APRM():ADDUNIT("UNIT_12"):SETAECODE("87873360")
ESI:APRM():ADDUNIT("UNIT_13"):SETAECODE("87873362")
ESI:APRM():ADDUNIT("UNIT_14"):SETAECODE("87873383")
ESI:APRM():ADDUNIT("UNIT_16"):SETAECODE("87873379")
ESI:APRM():ADDUNIT("UNIT_17"):SETAECODE("87873358")
ESI:APRM():ADDUNIT("UNIT_26"):SETAECODE("87873372")
ESI:APRM():ADDUNIT("UNIT_27"):SETAECODE("87873359")
ESI:APRM():ADDUNIT("UNIT_28"):SETAECODE("87873371")
ESI:APRM():ADDUNIT("UNIT_29"):SETAECODE("87873374")
ESI:APRM():ADDUNIT("UNIT_30"):SETAECODE("87873386")

-- Defines the database column source for the variant parameters
-- in order to assign them to a standardized asset effectiveness record
-- The colum names given are the desired columns in the asset effectiveness
-- staging database, the BATCHFIELD and CONSTANT mappings inform the
-- script logic where to find the matching information
-- Note: The APRM source characteristics are taken from a German APRM
-- system
-- Note: In case, APRM supplies multiple product records (BATCHFIELD
-- refers to an array
ESI:APRM():SETCOLUMN("START_TIME"):BATCHFIELD("START")
ESI:APRM():SETCOLUMN("END_TIME"):BATCHFIELD("ENDE")
ESI:APRM():SETCOLUMN("PRODUCT_NAME"):BATCHFIELD("PRO_NAME")
ESI:APRM():SETCOLUMN("PRODUCT_UNIT"):CONSTANT("kg")
ESI:APRM():SETCOLUMN("PRODUCT_SP"):BATCHFIELD("PRO_MENGE_SOLL")
ESI:APRM():SETCOLUMN("PRODUCT_ACTUAL"):BATCHFIELD("PRO_MENGE_IST")
ESI:APRM():SETCOLUMN("PRODUCTION_VERSION"):BATCHFIELD("REC_VER")

-- Assign educts for an optional additional product staging table
-- which is supposed to be linked to the batch header fields table
-- using referential integrity
ESI:APRM():SETPRODUCTCOLUMN("PRODUCT_NAME"):BATCHFIELD("EDU_NAME")
ESI:APRM():SETPRODUCTCOLUMN("PRODUCT_UNIT"):CONSTANT("kg")
ESI:APRM():SETPRODUCTCOLUMN("PRODUCT_SP"):BATCHFIELD("EDU_MENGE_SOLL")
ESI:APRM():SETPRODUCTCOLUMN("PRODUCT_ACTUAL"):BATCHFIELD("EDU_MENGE_IST")

-- Sets the material type for the configured product columns to 1,
-- which has the meaning "Educt"
ESI:APRM():SETPRODUCTCOLUMN("MATERIAL_TYPE"):CONSTANT(1)

-- This fetches the next batch record from APRM, and creates an asset
-- effectiveness event, which is sent to the Core by an Event Stream
-- object, which is automatically created in case not present.
-- When assigned to a Generic Item, it will execute once a minute.
ESI:APRM():GETNEXTBATCH(ESI:APRM():OPTIONAERECORD())

-- This must! be the last call
-- It will return a JSON structure to the dynamic property of the object,
-- which can be used by some esi-r monitoring process to understand the
-- current health of the scripted functionality
return ESI:RSCRIPT():END()

Execution framework

Prerequisites

The following prerequisites must be met, in order to successfully run esi-r-aprm scripts to fetch batch records from AspenTech APRM:

  1. Install and configure an AspenTech APRM ODBC data source on the Windows computer, you plan to have the Connector logic running on. Please note that it is possible to have many batch data sources to be monitored on a single computer, managed by a single inmation Connector. It is best practice to name the ODBC data source something like IP21-<batch data source>-<bit model>, like in the example here "IP21-X191T-32", where the APRM batch data source is "X191T".

  2. Install the Connector on a Windows computer and connect it to your Core system. Make sure the bit-model matches the AspenTech APRM ODBC driver. In often cases, this will be still 32-bit.

  3. Locate the right hierarchy position in your Connector I/O Model and create a Generic Item object which is set to 'Lua Script Data Generator'. It is best practice to name this object like the APRM Batch Area, it is supposed to monitor. One esi-r-aprm script cannot monitor batch records from multiple different APRM batch areas, as such areas could have different structural configurations. In case you wish to monitor multiple areas, you need to create multiple objects. It is also best practice to host the monitoring objects in a folder hierarchy Use Cases/Batch Monitoring relative to the Connector object itself. The fully qualified location in our example would be System/<Core Name>/<ConnectorName>/Use Cases/Batch Monitoring/X191T.

  4. Paste the prepared script into the objects .GenLuaScript.AdvancedLuaScript property and apply.

  5. Check the desired execution flow according to the following sections.

Rails Automation

Due to the fact, that the esi-r-aprm library functions according to the ESI Rails concept and principles it does a few things automatically to ensure its own proper runtime environment in inmation:

  1. It will adjust some of the objects properties to match its desired state a) the .GenerationPeriod property will be set to 60000 (msec, equal one execution per minute), b) it will set its .AuxiliaryStateManagement property to 'Volatile', and c) it will set its own .ObjectDescription according to the configuration in the script body, and d) it will check and set the .DedicatedThreadExecution property, because the script logic is by design a long run.

  2. It will check that its Connector has an Event Stream child object. If it should not exist it will be created and named "Script Events".

  3. It will create a sub-structure of VariableGroups and Variables, as described below:

First runtime checks

Use Case Monitoring

Documentation

OPTIONAERECORD()

This function returns the AE record


OPTIONBATCHRECORD()

This function returns the batch record


SETDB(dsn, user, pwd)

This function sets the database connection string to be used for the scripted logic. It points to the right AspenTech SQLplus server. user and pwd are usually omitted, as it is common practice to apply specific Windows credentials to the executing Connector instance instead. The Connector instance is then using the impersonation to authenticate itself against the AspenTech APRM database.

SETDB Parameters

dsn (string, required)

The name of the data source which has an associated data structure used to describe a connection to a data source

user (string, optional)

The name of the user who has granted access to the data source. In often cases not required, see function explanation above.

pwd (string, optional)

The password of the DSN user. In often cases not required, see function explanation above.

SETDB Usage

 — This loads the esi-r-aprm library, sets the database connection to APRM (ODBC data source or DSN), this ODBC DSN must exist and the Connector service must have the right account (username, password ommitted), this is mandatory to set


SETBATCHSOURCE(batchsource)

This function sets the batch source

SETBATCHSOURCE Parameters

batchsource (string, required)

The batchsource is

SETBATCHSOURCE Usage

 — This …​


SETAREA(areaname)

This function sets the area name

SETAREA Parameters

areaname (string, required)

The areaname is

SETAREA Usage

 — This …​


SETUNITTABLE(tableobject)

This function sets the unit name

SETUNITTABLE Parameters

tableobject (string, required)

The tableobject…​

SETUNITTABLE Usage

 — This …​


SETPLANTNAME(plant)

desc…​

SETPLANTNAME Parameters

plant (string, required)

The plant…​

SETPLANTNAME Usage

 — This …​


SETAETARGET(aedbhost, aetarget)

desc…​

SETAETARGET Parameters

aedbhost (string, required)

The aedbhost…​

aetarget (string, required)

The aetarget…​

SETAETARGET Usage

 — This …​


SETCOLUMN(column)

desc…​

SETCOLUMN Parameters

column (string, required)

The column…​

SETCOLUMN Usage

 — This …​


SETPRODUCTCOLUMN(column)

desc…​

SETPRODUCTCOLUMN Parameters

column (string, required)

The column…​

SETPRODUCTCOLUMN Usage

 — This …​


SETCODEPAGE(cp)

desc…​

SETCODEPAGE Parameters

cp (string, required)

The cp…​

SETCODEPAGE Usage

 — This …​


GETNEXTBATCH(options)

desc…​

GETNEXTBATCH Parameters

options (string, required)

The options…​

GETNEXTBATCH Usaeg

 — This …​