SQL to find the Next Working Day and skip the Weekend. | SQL DB2

An example of Db2-SQL to find the next working day and skip the weekend. In this example Friday and Saturday both are weekends.

SELECT

     sysdate AS current_date

    ,DAYNAME (sysdate) current_day

    ,sysdate + 3    AS come_after_3_days

    ,DAYNAME (sysdate+3) oh_after_3_is_friday

    ,decode(DAYNAME (sysdate + 3)

            ,'Friday',(sysdate + 3) + 2

            ,'Saturday',(sysdate + 3) + 1

            ,(sysdate + 3)

           ) so_the_nextworkday_is

    ,dayname(decode(DAYNAME (sysdate + 3)

                    ,'Friday',(sysdate + 3) + 2

                    ,'Saturday',(sysdate + 3) + 1

                    ,(sysdate + 3)

                    ) 

            ) AS so_the_nextworkday_is_sunday

  FROM

    sysibm.sysdummy1

Import SSL from the External Secured URL into Websphere Trust Store | WebSphere

To use the External Services in Maximo, or to integrate with an external system like MS Outlook 365, BIM by Autodesk Revit, or GIS, or any web/rest service. 

All famous browser generally trusts almost all known Certificate Authorities (Digicert, Google Trust Services and so on.) 

But when it comes to Websphere, it doesn't work. Only the Administrator specifically instructs WebSphere to trust by importing the certificate into the Trust Store either at the Cell level or Node level.

Let's instruct Websphere to import a Gmail certificate

1. Webspher > Security > SSL Certificates and Key management > Key stores and certificates> select  CellDefaultTrustStore(if cluster) / NodeDefaultTrustStore
2. Then Click Signer certificates > Click on Retrieve from port
• Host: hosturl i.e google.com
• port :  443
• Alias: any name, i.e gmail
3. press Retrieve signer information (retrieved signer information is displayed)
4. hit Apply and OK. 
5. restart cell manger service, nodeservice to take effect. 
6. done. 

----------------

errors we commonly see if a trust store is missing:

• PKIX path building failed: java.security.certi.CertPathBuilderException:
• PKIXCertPathBuilderImpl could not build a valid CertPath.
• Certificate issued is not trusted
• BMXAA1477E - the connection failed to the HTTP handler for the endpoint. 
• etc

Elapsed Time in View Status Dialog - Jython | Maximo

Add an additional column in View History Dialog to see the time elapsed for each status. 

This is a modified version of the solution written by Bruno for the same topic. 

We will accomplish this task with an attribute in the status table and an object-level launch point script written in Jython. 

1. Add a column in PRSTATUS table,  Name: ELAPSEDTIME, Type: Duration.

2. Display this column into View History Dialog using the application designer of the PR application.

2. Write an Object Launch Point Script: Object: PRSTATUS , Save, Add, AfterSave. Copy below the body and paste it. 

Python Function - Find the Next Business Days After n Days | Maximo

You have 15 business days (exclude weekends) to complete the task, what will be the 15thday?

from java.util import Calendar

#Function definition

def getNextBusinessDday(d, n ):

    cal = Calendar.getInstance();

    cal.setTime(d)

    loop=1

    while(loop<=n) :

        cal.add(cal.DATE, +1)

        if (cal.get(cal.DAY_OF_WEEK)) not in [cal.FRIDAY,cal.SATURDAY] : 

            loop=loop+1

        vserial = loop

    calculatedetc = cal.getTime()

    return calculatedetc

#variables for input

d = mbo.getDate("changedate")

n = 15

#function call whereever you want in the same script

getNextBusinessDday(d,n)

Reuseable Scripts - Invoke a Script from a Script - Jython | Maximo

 Question:

1. Can we invoke a script from another script?
2. Is it possible to reuse the existing Script?
3. Can we make the Global function and call it in the script when required?

Solution:

My answer is yes to all the above questions, let's start with this example. 

--- " Do this job within 5 business days (excluding weekends (Friday, Saturday))! "

Okay. Today is 19-Jan, what is my, etc after 5 days.

we will handle this with 2 scripts: 

1. a library script to take the parameter of date + days and gives an output. 
2. our script from where we will call the library script. 

1. Create a Library Script and name it any like "LS-GETNEXTBUSINESSDAY"

copy the paste the code below into the body of it. 

from java.util import Calendar

cal = Calendar.getInstance();

cal.setTime(d)

loop=1

while(loop<=n) :

    cal.add(cal.DATE, +1)

    if (cal.get(cal.DAY_OF_WEEK)) not in [cal.FRIDAY,cal.SATURDAY] : 

        loop=loop+1

vNextBDay = cal.getTime()

2.  Call this function from another script with 2 parameters d, n ( date, days)

Create Purchase Requisition from Inventory Usage using Action Script (Customized Option) | IBM Maximo

Recently we had the requirement to disable the direct MR to PR automation. so instead of

creating direct PR from MR, the users will select a store on material requisition to reserve material

from store. When the MR is approved, the inventory usage is created from MR.

If the material is available in-store, storeroom will issue the material otherwise he clicks the option

to create Purchase Requisition (PR) from inventory usage.

So we developed this new functionality.

Note: This script contains a few custom attributes. I have commented those line, In your case please remove them.

Step 1:

First Add new Status for Inventory Usage Application: PR_CREATED

Step 2: Create Automation Script

Launch Point:        ACTION

Object:                  INVUSE

Variable:                v_mr

Binding Value:      INVUSELINE[MRNUM is null].MRNUM*

Relations:              INVUSE --> INVUSELINE -->MRNUM (child object:MR, mrnum=:mrnum)

IBM Support Terminology

 APRR (defect)

a request / defect reported to IBM (authorized program analysis report)

TPAE (application architecture)

Tivoli's Process Automation Engine / Base Services

Core Java Classes that can be used to build Java applications. 

Benefit: The benefit of using TPAE architecture is that the core functionality of many Java applications DOES NOT need to be coded by the application. Each application relies on base classes for core functionality rather than to code in each.

base system core functionally, security tools for user, role, group management

Interim Fix (patch):

a patch to fix a confirmed product defect reported by clients from IBM technical developers reported. 

It contains 1 or more product defects/APARs. the most common patch that IBM delivers.

Fix Pack (Cumulative patches):

A cumulative collection of all patches/fixes since that last released FP. (usually every after 4 months)

Service Pack: (like windows sp1,2,3,4 and then new windows like from 98 to xp, 7, 8,10)

A new feature release

Delete a Person from a list of Person Groups (Dialog, Automation Script) | Maximo

A revised version of the existing post Delete a Person from Person Groups (LIST VIEW QUERY) using Automation Script | Maximo.

We are going to add a dialog box on the list view of Person Group Application to choose the Person to be deleted. 

1. New Non-Persistent Object with one attribute Personid

Object Name: NP_DELETE_PERSONID

Attribute:        NP_personid

Reassign Workflow Assignments in Bulk using Escalation | IBM Maximo

Sometimes you have the requirement of reassignment of 200-300 workflow assignments for users.

You can reassign the workflow assignments using an Escalation based on the WFAssignment object. In this example, we will reassign all of Umar's (personid=116524) WF Assignments for application to Qadeer. To do all of Umar's assignments, not just the specific application assignments, leave off the "app =" parameter used below. 

Condition:

WFID != 0 and assignstatus = 'ACTIVE' and assigncode = '116524' and app = 'SR'

Create escalation point bute Leave all the escalation point fields empty. Repeat is off

Create and Attach Action:

Create an action on the same object with set Value of replacement user's assigncode.

Invoke/Call any dialog from Automation Script | Maximo

Question:

Can we invoke/call my custom dialog or any dialog from the automation script, like we usually can trigger it from the Push button or from Select Action of the application 

Answer:

Yes, you can in the latest version 7.6.1. 

1. Get the dialog name from the application designer 

2. using below mentioned automation script to call it. 

In my example, I created one object launch point automation on the work order application to trigger automation. 

Below is the body of the automation in which I called one of the standard change status dialogs. 

from psdi.server import MXServer

from psdi.mbo import MboConstants

from psdi.common.context import UIContext

from psdi.webclient.system.controller import SessionContext, Utility, WebClientEvent

mxServer = MXServer.getMXServer()

userInfo = mxServer.getUserInfo("maxadmin")

vmbo = mbo.getString("wonum")

if vmbo == '30352551':

    context = UIContext.getCurrentContext()

    if context:

        wcs = context.getWebClientSession()

        Utility().sendEvent(WebClientEvent("status", wcs.getCurrentPageId(), None, SessionContext(wcs)))

        #service.error("",str(abc))

To Close a dialog, either use "okdialog" or in the newer version in can be done with 1 line of code:

service.closeDialog() 

Make Memo field mandatory on specific inputs in workflow | Maximo

 It's very simple by using just once Global data restriction based on conditions.

1. create an expression using Conditional expression Manager (copy this)

(

    :ACTIONID in (

        select

            distinct ACTIONID

          from

            WFACTION

         where

            1 = 1

            and PROCESSNAME = 'SR'

            and PROCESSREV='1'

    )

)

2. Create Global Data Restriction -> Attribute Restriction -> New Row

Object	:	INPUTWF
Attribute	:	MEMO
Application	:	SR
Type	:	QUALIFIED
Reevaluate	?	y
Condition	:	myCondition (the one we created in step 1)

Workflow Handling from Automation Script | IBM Maximo

Initiate a workflow from Automation Script. 

from psdi.server import MXServer

mxs = MXServer.getMXServer()

wfstart = mxs.lookup('WORKFLOW')

wfstart.initiateWorkflow('CSPREIMB',mbo)

 

-------------------------

or define a function in your script and call it whenever needed as below:

Function Defination:

def startwf(a):
   actionSet = MXServer.getMXServer().getMboSet("action", a.getUserInfo());
   sqf = SqlFormat("action = :1")
   sqf.setObject(1, "action", "action", "STWFEUNRI")
   actionSet.setWhere(sqf.format())
   actionSet.reset()
   actionSet.getMbo(0).executeAction(a)

 

Function Call:

startwf(MboVariable)

 2. Stop an active workflow from Automation Script. 

from psdi.server import MXServer

mxs = MXServer.getMXServer().lookup('WORKFLOW') 

wfstop = mxs.getActiveInstances(mbo)

         wfstart.getMbo(0).stopWorkflow('Stopped')

 

Invoke Action from Automation Script | IBM Maximo

from psdi.common.action import Action, ActionRemote

from psdi.security import UserInfo

from psdi.server import MXServer

mxServer = MXServer.getMXServer()

userInfo = mbo.getUserInfo()

actionSet = mxServer.getMboSet("ACTION", userInfo)

actionSet.reset()

actionSet.setWhere("action = 'INV_RUN_REP'")

if(not actionSet.isEmpty()):

action = actionSet.getMbo(0)

action.executeAction(mbo)

actionSet.close()

Modify Maximo URL's Context Root | Maximo

 Some times we need to change the default context root (/maximo) to something else, for example "/maximoprod"

http://localhost/maximo     ------->>>>        http://localhost/maximoprod

1. login to Websphere (https://maximo76.el.com:9043/ibm/console/) with wasadmin user

2. Applications > Enterprise Applications > MAXIMO > Context Root For Web Modules.

and modify the context root to "maximoprod" instead of "maximo" , as in my example. 

Restart Application server or Ripple start your cluster to login with new URL : 

http://maximo76.el.com/maximoprod

you are done :)

Refresh/Update Start Center for All Users in a specific Security Group | Maximo

 Create an automation script to delete the records from the respected table to refresh the start centers displayed on the user's screen

or delete the records from the tables using SQL.

Today we are going to make an automation script and attached it to a signature option on the toolbar. 

So that whenever the user clicks on this button on the security group application, all the respected records will be deleted, and the start center will try to create new records, which means our records will be updated. 

This:

1. Create an Action Launch Point Automation Script with a name like( STARTCENTER_REFRESH ).

Copy the below code in the script section, use jython as language. 

from psdi.server import MXServer

from psdi.mbo import MboConstants

from psdi.util.logging import MXLoggerFactory

logger = MXLoggerFactory.getLogger("maximo.maximodev")

logger.info("Entering MXD_RESETSC")

def deleteSc(objectName, where):

    scSet = MXServer.getMXServer().getMboSet(objectName, mbo.getUserInfo())

    scSet.setWhere(where)

    logger.debug("Deleting " + str(scSet.count()) + " rows from " + objectName)

    # disable logging of large fetch result set to avoid FetchResultLogLimit errors

    scSet.setLogLargFetchResultDisabled(True)

    scSet.deleteAll()

    scSet.save()

Copy WF Memo to Status Memo in View History of Status dialog | Maximo

Copy the Memo from the MANUAL INPUT of the workflow dialog into Memo field of CHANGE STATUS in View History Dialog of any application. 

Both Memos are different , one is coming from wftransaction, and the other one is in the xxstatus table. 

We were trying to cross over the memo from Wftrancation to status table's memo. 

here is the solution we developed using automation script in jyton. 

In my example i am using PR object and PRSTATUS table.

1. Make a relationship from WFTRANSACTION to PR

RelationShip Name: PR

Child Object: PR

Where Clause: prid = :ownerid and :ownertable = 'PR'

1. Object Launch Point 

Object : WFTRANSACTION

Events: SAVE, Add, After Save

wftmemo = mbo.getString("memo")

if (wftmemo!=''):

    prset = mbo.getMboSet("pr")

    pr = prset.moveFirst()

    if pr is not None:

        prstatusset = pr.getMboSet("prstatus")

        prstatus = prstatusset.moveLast()

        prstatus.setValue("memo",wftmemo,2L)

        #prstatusset.save()

 

Implicit Variables like Current Date and or Time to a Communications Template | Maximo

:DATETIME - to display the current Date and Time

:DATE - to display the current Date

:TIME - to display the current Time

:HOSTNAME - to display the hostname defined in System Properties 

Change Status method using Automation script | Maximo

from psdi.server import MXServer

 mbo.changeStatus("APPR", MXServer.getMXServer().getDate(), "heheh")

Tax to be applied on Inventory Cost (Exclusive Tax) - Functional | Maximo

When a Purchase order is approved and tax is also charged to the vendor. In this case, Inventory costing will take the price of the unit cost inclusive tax, whereas the last price will be shown as pretax price. 

There is a configuration for in Tax Options of Organization - > Purchasing option. 

Tax Types: there are by default 5 types are available. 

Expand the tax type, let's say TAX1. Under the section "Add Tax 1 Amount to Vendor Price

For All Items:

Delete a Person from Person Groups (LIST VIEW QUERY) using Automation Script | Maximo

Requirement # 1:

How can we delete all persons from a list of PersonGroups except Group Default at once?

Requirement #2:

 How can we delete a specific person from all the listed person groups?

Solution:

1. Create an Action Launch Point Script (let's say. DEL_PGTEAM)
2. Open PERSONGR in application designer: 
1. Create a Signature Option: DEL_PGTEAM., and choose "This is an action that must be invoked by the user in the UI" in the advance section
2. Create Action with type Option with the same name as the signature option in step 1.
3. Grant newly created signature option for specific Security Group.

Swagger - Maximo Restful API Categories

Maximo APIs are grouped into modules based on the functional aspect of the software they cater to. 

Here is the link to the list of all modules and then proceed with swagger to open one by one based on the need/requirements.

https://localhost/maximo/maximomodules.jsp

or use direct link to open selected API in swagger with actions =1 or 0 for no

https://localhost/maximo/oas3/api.html?module=PURCHASE&includeactions=0 

Service Specific to an Application/Object in Maximo - AppService | Maximo

These are wrapped with multiple capabilities as a service to support an Application/object within Maximo. 

for example, a service specifically for asset, another is for workflow, and so on. 

Also, some of the services work globally like Escalations service.

If we look into the log, we can see something like 

BMXAA6348I - The ASSET service is initializing.

BMXAA6348I - The WORKFLOW service is initializing.

 

So as mentioned above, there are 2 types of AppServices

1. App-related services

• psdi.app.asset.AssetService
• psdi.app.item.ItemService
• psdi.app.pr.PRService
• psdi.app.inventory.InventoryService
• and so on.

Display Last Workflow Memo in Application field | Maximo

 Requirement: 

Is it possible to see the last workflow message when I open any record just beside/below the status field? 

Solution: 

Yes, It is possible with 2 different approaches. Let take this example and display the last memo in work order application.

A. Directly with the relationship, no need for a view also. 

B. Create a View to handle a query

A.  Start with easy way, just relationship and display the field

1. add the following relationship to the workorder
• Relationship: LAST_MEMO_VIEW
• Where Clause: ownertable= 'WORKORDER' and ownerid= :workorderid and memo is not null and transid in (select max(transid) from wftransaction where ownerid= :workorderid and memo is not null)
• Child Object: WFTRANSACTION
2. Now in the application designer just add a new text field and put LAST_MEMO_VIEW.MEMO in attribute property and make it read-only also. 
3. 

Object Lauch Point - Allow Objet Creation/Deletion (CanAdd/CanDelete) | Maximo

These 2 events fire immediate and do not even wait for the framework to save/insert the record.

Allow Object Creation - canAdd() method of mbo framework

Creation is bit tricky because it runs when there is no record. And because there is no record there is no mbo the implicit variable. In that case, we use "mboset" as 

if mboset.getOwner() is not None and mboset.getOwner().getName()=="PO" and mboset.getOwner().isNull("vendor"): 

   service.error("PO", "vendor is null") 

Allow Object Delete - canDelete() method of mbo framework

Deletion is simple if we throw an error, the system will prompt with an error and stop further processing, and the record will not be deleted.  

if mbo.getOwner() is not None and mbo.getOwner().getName()=="PO" and !mbo.getOwner().isNull("vendor"): 

   service.error("PO", "vendor is not null") 

Change Status of Child Work Orders or Child Tasks from Parent Work Order using Automation Script || IBM Maximo

 This Automation Script will close all child Task work orders when the Parent work order is closed.

Based on Relationship, We can get Child Work orders or Tasks as we required.

When we are closing the work order if the child is complete. It will close the child WO or Tasks.

Launch Point: Attribute

Object: Workorder

Attribute: Status

Binding Variable: status

Body:

from psdi.server import MXServer