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: