The Powerful Dot Notation in Maximo

We are familiar with displaying additional information through a relationship in Maximo. If I want to display the asset's serial number in Work Order Tracking application when the asset field is populated, I just have to add a textbox in Application Designer for WOTRACK application and reference the RELATIONSHIPNAME.ATTRIBUTENAME in the attribute property of the textbox. For the WORKORDER object, there exists an OOB relationship "ASSET" to the child table ASSET. So, I need to simply use ASSET.SERIALNUM in the attribute property of the textbox. When an asset is entered in the Work Order record, this textbox will display the serial number.

Maximo relationships are same in terms of SQL joins and we are aware that even multilevel relationships can be used in Maximo. For example, if I have to send a notification to the affected person from a Service Request once it is created. In the communication template for this notification, I can specify a dataset role on SR object, in which value will be :AFFECTEDPERSON.EMAIL.emailaddress. AFFECTEDPERSON is the first level relationship name from parent object TICKET to child object PERSON and EMAIL is the next level relationship name from parent object PERSON to child object EMAIL. This will help is identifying the email address to which notification should be sent.

If we need to set a value in a field from a related field on some condition, we can use the dot notation here too. We have to set the value of the purchase agent of a PO with the value of the ship to person. We can create an action on PO object of type SETVALUE for parameter/attribute PUCHASEAGENT and in the value we can associate the ship to person ID using :SHIPTOPERSON.personid, where SHIPTOPERSON is the relationship name from parent object PO to child object PERSON. We can now reference this action from either a workflow or escalation whichever is applicable.

We can use the dot notation in building conditions also in Conditional expression manager. For example, a condition can be created to check if the bin number in related inventory balances record is same as default bin of inventory record, :invbalances.binnum = :binnum, where INVBALANCES is the relationship from parent object INVENTORY to child object INVBALANCES.

In recent times, we have also seen the extension of this powerful dot notation to result set portlets. I have tried this in Maximo 7.6. I create an object structure "REP_INVENTORY" for reporting purpose as below:

Then I go to Report Administration application and from more actions, I select "Set Report Object Structure Security". Provide access to MAXADMIN security group for the above object structure as below:

Now, in the Inventory application say you have created a query to display the inventory records of the logged in user, "Inventory of User Storeroom". If you want to set up a new result set in some existing start center, when you select to edit the portlet, you will be able to select the application, query and the object structure created above. And, you will be able to see the child table(s) along with the parent table under Object List.

When you select the attributes from the child table, you can see the <RELATIONSHIPNAME>.<ATTRIBUTENAME> in selected fields.

Once you save and finish the result set portlet setup, you will be able to see the resultset as below:

Recently I read an interesting blog about one more power packed feature of using this dot notation. I could not resist myself in trying that out and yes, it is worth mentioning about. You can use this relationshipname.attributename in cross-over domain's source and destination fields. Let us consider an example. We create a custom attribute in TICKET object, which is say, CUSTAPPROVER. In this field in Service Requests application, we need to display the supervisor of the supervisor of the affected person. So, we create a cross over domain as per the image below, which we associate with the CUSTAPPROVER attribute in TICKET object.

Hope you like reading this blog and your suggestions/comments are most welcome.

References:

https://www-304.ibm.com/support/docview.wss?uid=swg21508596

http://www-01.ibm.com/support/docview.wss?uid=swg21980423
https://www.ibm.com/developerworks/community/blogs/a9ba1efe-b731-4317-9724-a181d6155e3a/entry/the_hidden_feature_of_crossover_domain?lang=en

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: