Maximo Integration and Scripting

 View Only

Invoke SQL Statement from Condition Expression into Automation Script

By Nivin Jacob George posted Fri October 29, 2021 12:39 PM

A simple example to run a backend update statement stored in Condition Expression Manager, using Automaton Script. Here I am updating the Inventory attributes such as Issue 1 Year Ago, Issue 2 year ago and Issue 3 year ago which is updated once every year on the 1st day of the month.

Update statement:
update inventory set ISSUEYTD = 0, ISSUE1YRAGO = ISSUEYTD, ISSUE2YRAGO = ISSUE1YRAGO, ISSUE3YRAGO = ISSUE2YRAGO where orgid='EAGLENA' and siteid='BEDFORD' and status='ACTIVE'

Create the condition expression and insert the update statement

Create an automation script : Name: YTDUPDATESCRIPT

from psdi.mbo import Mbo
from psdi.mbo import MboConstants
from import UserInfo
from psdi.server import MXServer
from java.sql import Statement
from java.sql import PreparedStatement
from java.sql import Connection
from java.sql import ResultSet
from psdi.util import MXException
from java.util import Date

mxserver = MXServer.getMXServer()
userInfo = mbo.getThisMboSet().getUserInfo()

edmIMPSet = mxserver.getMboSet("CONDITION",userInfo);
edmIMP = edmIMPSet.getMbo(0);
con = edmIMP.getMboServer().getDBConnection(userInfo.getConnectionKey());

edmIMP_sql = str(edmIMP.getString("EXPRESSION"))

s2 = con.createStatement()
rs2 = s2.executeUpdate(edmIMP_sql)
except Exception, e:
print ' IN Finally'

Create a cron task to execute the automation script

Cron task name: updateInventoryYTD
Description: Update Inventory YTD attributes

Create the cron task instance that runs the automation script.

    1. In the Cron Task Instances section, add a row.
    2. Specify the cron task instance name.
    3. Set the schedule for when the cron task is run.
    4. Select Run As User. 
    5. In the Parameters tab, in the SCRIPTARG row, specify a value that represents an argument for the cron task.
    6. In the SCRIPTNAME row, specify the name of the script that you created "YTDUPDATESCRIPT" to run as part of the cron task. 
    7. In the Details section, select the Active check box to activate the cron task instance.
    8. Save the record.
1 comment



Thu March 31, 2022 12:41 PM

Thanks a bunch for your post, this is really helpful. As I am looking into automating some of the year end tasks.

Do you run ABC analysis and update as well part of year end tasks?