Fetching Mbo Sets using RelationShip or MXServer - Automation script Examples

I would like to draw your attention towards one important thing which we keep using in our java codes or Jython scripts but we do not use them properly. This is about getMboSet() method. As we are aware there are two ways of fetching an Mbo set, either by using a relationship or MXServer. Improper use of this may lead to performance issues, poor memory management, etc.

For example, I am working with save event of Object launch point on WORKODER object. I need to set a value in all related tasks which are in approved status. There is a relationship between WORKORDER object and WOACTIVITY object which can give me the set of all related tasks of the workorder which are in approved status. It will be easier for me to use this relationship to fetch the WOACTIVITY records. Please keep in mind that when I am accessing the Mbo records using a relationship, it is included in the same transaction as the parent Mbo Set and if I call a save on the parent Mbo Set, all MboSets in the current transaction will be saved. So, the Mbo sets fetched using relationship do not require save to be called explicitly because their save is called when their parent is saved.

In the above code snippet, I can also use a setWhere clause to specifically select some of the records, this new where clause will be appended with the existing where clause of the relationship and you will have to call a reset() method to execute and update the resultset obtained using the relationship. 

Above script will save the WORKORDER records and the related WOACTIVITY records in the current transaction.

Let us consider that the relationship from WORKORDER object to WOACTIVITY does not exist which may satisfy the search criteria we are looking for. In this case, we can create one on the fly as below: 

The parameter “$WOTASK” is a temporary relationship name, “WOACTIVITY” is the child object, and “parent=:wonum and siteid=:siteid and istask=1 and status=’APPR’ and targcompdate is not null” is the relationship where clause. The values of :wonum and :siteid will be taken from the current Mbo record from WORKORDER object.

While fetching MboSets from MXServer, we have to be careful about a few things.

1. Always use setWhere() and reset() methods with this MboSet. The setWhere() method will reduce the number of Mbo records being fetched and reset() method will ensure fetching of data with the where clause.
2. If the Mbo set is being fetched only for traversing and not for any addition or update, it is a best practice to set the DISCARDABLE flag as true. If the Mbo set is being fetched for read-only purpose it is a good practice to set the NOSAVE flag as true. These steps will shorten the looping time of the transaction.
3. If any setValue method is used for some Mbo record of the MboSet, or any add or update is being done on the Mbo set, it is required to call the save() method on the set. Mbo sets fetched from MXServer needs to be saved before the set is closed otherwise the changes will be lost.
4. Always use clear() and close() methods on the Mbo set fetched using MXServer if it is not required anymore. This will release memory and the database resources

Let us consider one example. Every Saturday, we need to run an escalation to update the actual cost of all issue transactions of active inventory records of all capitalized items that have happened between last Monday and Friday. We have written an escalation to run every Saturday on Inventory Object which calls an Action. Keeping in mind all the above rules, this action logic is written in an automation script with Action launch point, below is the sample source code of the script.

Exploring Automation Scripts with some examples

Lets explore some of the solutions achieved through automation scripts in Maximo. If one has worked with customization or extension of Java classes in Maximo framework, he/she would be well versed with these solutions. But here we will follow the idea of achieving these solutions without having to think of building the ear file and deploying it into the web server. These solutions may not be agreed upon by every one but this is to show how automation scripts can be helpful in achieving the requirements. And, to highlight that automation scripts can be used in place of Java class files to achieve the functionality in similar fashion at the cost of no extension to the existing ear file. The contents used in this blog are examples only. Hope you like reading my blog. Suggestions / comments are most welcome.

Trade-off for using Automation Script over Cross Over Domains with YORN fields

You add a cross-over domain to an attribute when you want to copy one or more attribute(s) values from a related object to current object's attributes. For example, when you select an item in a Purchase Order Line record, you want to cross over the item's description, issue unit, order unit, etc. from Item Master record, cross-over domain can be an effective tool. Now, think about this. I have this requirement to copy the Storeroom and Storeroom Site from the logged in user's profile to a PO record when user checks the internal checkbox. Ideally, we would suggest cross-over domain to be used with PO.INTERNAL attribute. But, is it really this simple? We go ahead to create a cross-over domain to copy MAXUSER.DEFSITE and MAXUSER.DEFSTOREROOM (source fields) to PO.SITEID and PO.STORELOC (destination fields). 

When we try to associate this cross over domain to PO.INTERNAL attribute in Database Configurations application, we get the error: BMXAA0664E - Domain must be null for data type YORN. This is but obvious. So, other option left for us to explore is using the automation script. We can easily create an automation script on object "PO" with an attribute launch point on attribute 'INTERNAL" to achieve our desired solution.

Triggering the automation script - Which Launch Point to use?

I would  like to explain this using an example. We had a requirement that for a Kit item, we had to store the values of its sub-assemblies in one attribute. We identified the long description to capture this for a kit item. Next question is when to trigger this solution, when we identify an item to be a Kit item or when we save it. In Item Master application, we identify Kit items with checking of the "Kit?" checkbox and the "Item Assembly Structure" tab lists out the children this item has. We can make use of these out-of-the box attribute (ISKIT) and object ITEMSTRUCT to arrive at the solution. But generally, as and when we check the checkbox, "Kit?", for an item, Item Assembly structure is not available at that point of time. So, if we create an automation script with attribute launch point on attribute ITEM.ISKIT, it will not serve our purpose. Moreover, we need to associate the item assembly structure of this kit item to its children items/assets. Let us assume there is a custom relationship "CHILDITEMS", on parent=ITEM, child=ITEMSTRUCT with where clause as "itemsetid=:itemsetid and parent=:itemnum". we can use this relationship to fetch the children for the kit item.
We created an automation script with object launch point on object=ITEM as below:

And, we can use the following code snippet:

Automation Script for Calculations

Another example which I want to share involves some calculations. We need to store the Turns Ratio for items in Storerooms, which is calculated as net issues divided by average inventory for last 52 weeks. Net Issues is the number of issues minus returns from the inventory in last 52 weeks. And, avergare inventory was calculated by multiplying the sum of inventory balance of each week in last 52 weeks with average cost of inventory and then dividing this number by 52.

For this solution, we created a custom table WEEKLYBAL and had implemented a batch job (you can use escalation or a cron task), running every Saturday at a fixed time to copy the current balance of each inventory item (storeroom, item number, item set, system date as WEEKDATE, current balance as CURWEEKBAL and average cost), assuming ISO week starting on Sunday and ending on a Saturday. We also created a custom attribute, "INVENTORY.TURNRATIO", to store the value of all our calculations for each Inventory record. And, one more escalation can be created every Sunday after midnight in early hours of the day to trigger one action which in turn can be an automation script to do the following.
If you want to calculate the net issues for each inventory item using a SQL query, it would be as below:
select A.itemnum, A.storeloc, A.siteid, abs(sum(A.numofissues) - sum(A.numofreturns)) NetIssues  from
(select itemnum, itemsetid, storeloc, siteid,
case when issuetype='ISSUE' then actualcost 0 end as numofissues,
case when issuetype='RETURN' then actualcost else 0 end as numofreturns from matusetrans
where transdate between (trunc(sysdate,'IW')- 51*7) and (trunc(SYSDATE, 'IW')+6))A
group by A.itemnum, A.itemsetid, A.storeloc, A.siteid;
For this above part, we can create a relationship from INVENTORY object to MATUSETRANS object. Let us assume there is a relationship "ISSUERETURN52WK", parent=INVENTORY, child=MATUSETRANS with where clause as "itemnum=:itemnum and itemsetid=:itemsetid and siteid=:siteid and storeloc=:location and (transdate between (trunc(sysdate,'IW')- 51*7) and (trunc(SYSDATE, 'IW')+6))". Code snippet to calculate the net issues for all inventory items would likely be as below:

And, the average inventory for each inventory item will be calculated using the below query from the custom table "WEEKLYBAL":
select itemnum, itemsetid, storeroom, siteid, sum(curweekbal*avgcost)/52 from WEEKLYBAL 
where (weekdate between (trunc(sysdate,'IW')- 51*7) and (trunc(SYSDATE, 'IW')+6))
and trunc(weekdate, 'IW')+5 = trunc(weekdate)
group by itemnum, itemsetid, storeroom, siteid;

To simplify this, we have a new relationship "INVWKBAL52" for parent=INVENTORY and CHILD=WEEKLYBAL with where clause as "storeroom=:location and siteid=:siteid and itemnum=:itemnum and itemsetid=:itemsetid and (weekdate between (trunc(sysdate,'IW')- 51*7) and (trunc(SYSDATE, 'IW')+6)) and trunc(weekdate, 'IW') + 5=trunc(weekdate)". Turn ratio can be calcuated and stored in the custom attribute using an extension to the above snippet as below: