IBM Reference:
On the url page referenced above is a tab called Attachments, and on that tab a pdf called: MIF-Proc_Rules.pdf which is a very good explanation of the functionality available for the rules, conditions and evaluations of a MIF interface. Along with some good examples to get someone started.
The purpose of this article is to help you plan your rules, conditions, and evaluations for implementation when starting with a complex SQL query that meets your requirements, but needs to be implemented in an outbound, publish channel in Maximo
Below is an example of a complex SQL query that returns the work orders that are desired to be transferred to another system via a MIF outbound web service, ie a publish channel. There are a number of criteria that need to be meet both related to work order characteristics and location specific requirements.
set linesize 120
set define '~'
Select reportdate Reported,
to_char(reportdate,'HH:MI PM') Time,' ',
substr(wonum,1,9) WorkOrder,
substr(wo4,1,8) System,
Substr(Changeby,1,10) ChangedBy,
Status Currently
from workorder
where istask = 0 and
worktype like 'TC' and
woclass like 'WORKORDER' and
parent is null and
status in ('INPRG','APPR') and
reportdate > sysdate - 365 and
((wo4 = 'SYS-E' and
(location like 'BLDG-920-%' or
location like 'BLDG-220-%' or
location like 'BLDG-640-%' or
location like 'BLDG-710-%')) or
wo4 = 'SYS-C' or
wo4 = 'SYS-T' or
(wo4 = 'SYS-D' and
location like 'BLDG-100-%') or
(wo4 = 'SYS-O' and
(location like 'BLDG-750-%' or
location like 'BLDG-100-%')) or
wo4 is null)
order by reportdate; The aforementioned query has a fairly complex set of and/or requirements to determine if the work order information should be sent. Note the set commands, select fields and “reportdate > sysdate – 365” where clause can be ignored as it does not apply for an event driven MIF interface. Also the order by clause can be ignored as it does not apply as well. So we are just going to focus on the remaining where clauses and their implementation in the MIF publish channel.
The first concept you have to understand is that while a SQL statement is a positive selection based request the publish channel rules are what I call negative based selection criteria. In other words you “SKIP” those records you do not want to send versus selecting those records you do want to send. So the trick is to figure out how to reverse the logic of the SQL statement to match the MIF interface.
I came up with this shortcut diagram to help me remember where in the MIF things are or’d versus where they are and’d. This becomes important as we reverse the logic of the SQL statement and then try to implement in the MIF.
Rules – Processed in order, which then acts like a functional or.
Conditions – or’d
XML Fields, Object Fields, Object Set, and Controls Evaluations – and’d.
The first five conditions of the where clause are listed below. Note we deal with them as a set because the left parenthesis in the SQL statement starts a whole new requirement that gets and’d with these.
where istask = 0 and worktype like 'TC' and woclass like 'WORKORDER' and parent is null and status in (‘INPRG’,’APPR’)
Note we have to reverse the logic, as we want to ‘SKIP’ those transactions that do not meet this criteria. So the equal, like and in become notequals. The connecting and becomes an or. The select becomes a SKIP, and the is null becomes an ‘isnotnull’.
SKIP if
Istask notequals 0 or
Worktype notequals ‘TC’ or
Woclass notequals ‘WORKORDER’ or
Parent isnotnull or
Status notequals (‘INPRG’ or ‘APPR’)
Reviewing our shortcut diagram above we see that this can be implemented with one rule and five conditions, with one evaluation per condition. Where the first two evaluations are xml fields as istask and worktype are included in the output xml and the next three woclass, parent and status are object fields as they are not included in the output xml, but are part of the workorder object, at least in this particular example. Note the or between conditions is understood. Below is a shorthand for documenting and implementing the MIF rule.
Rule 1 “Check for task, work type, work class, parent and status” SKIP (if)
Condition 10
Evaluation xml istask notequals value 0 ALWAYS
Condition 20
Evaluation xml worktype notequals value ‘TC’ ALWAYS
Condition 30
Evaluation object woclass notequals value ‘WORKORDER’ ALWAYS
Condition 40
Evaluation object parent isnotnull ALWAYS
Condition 50
Evaluation object status NOTEQUALS Integration Control WOSTART ALWAYS
At about this moment one feels this is really dumb to have to invert the logic, but for some reason the designers felt it was the way to go. Perhaps performance is better.
Note I have added a description for the rule to help with identifying in the MIF integration screens.
Note we chose to use an integration control to implement the last evaluation. For more information on using integration controls see the second attachment mentioned at the IBM URL above. The attachment is called MIF-Int_Controls.pdf.
Please understand the rules are processed in order so you will want the most exclusive rules to be first so subsequent rules are not processed needlessly. For example most work orders have multiple tasks so you might want the “istask notequals 0” to be a rule by itself, and that way all tasks would be skipped without processing any subsequent rules needlessly.
The astute developer will now realize that all five of these could have been implemented as five skip rules each with one condition and one evaluation and then created from most restrictive to least restrictive, thus acting like the rules are or’d together, matching our reverse logic write up.
Also the Boolean istask in Maximo is never null, so a special case of reverse logic would be SKIP if Evaluation object istask equals 1 also works. Though I don’t recommend this logic generally speaking as it is a special case. But some may find it clearer and easier to use.
Below is the next section of the where clause which can be treated separately as the entire unit is contained in parenthesis and and’d with the first five where clauses. The components have been rearranged from simplest to most complex so we can address them in the same manner.
(wo4 is null or wo4 = 'SYS-C' or wo4 = 'SYS-T' or (wo4 = 'SYS-D' and location like 'BLDG-100-%') or (wo4 = 'SYS-O' and (location like 'BLDG-750-%' or location like 'BLDG-100-%')) or (wo4 = 'SYS-E' and (location like 'BLDG-920-%' or location like 'BLDG-220-%' or location like 'BLDG-640-%' or location like 'BLDG-710-%')))
Reversing the logic of the first level gives us the following SKIP rule. In this case the select becomes the SKIP. The equal becomes not equal, the null becomes not null and the ors become ands.
SKIP if
wo4 is not null and
wo4 is not equal to ‘SYS-C’ and
wo4 is not equal to ‘SYS-T’ and
WO4 is not equal to ‘SYS-D’ and
WO4 is not equal to ‘SYS-O’ and
WO4 is not equal to SYS-E’
The only way to implement the ands is in evaluations so this then equates to one rule with one condition and six evaluations. Below is the shorthand for documenting and implementing this rule.
Rule 2 “Check WO4 values.” SKIP (if)
Condition 10
Evaluation object WO4 ISNOTNULL ALWAYS (and)
Evaluation object WO4 NOTEQUALS value ‘SYS-C’ ALWAYS (and)
Evaluation object WO4 NOTEQUALS value ‘SYS-T’ ALWAYS (and)
Evaluation object WO4 NOTEQUALS value ‘SYS-D’ ALWAYS (and)
Evaluation object WO4 NOTEQUALS value ‘SYS-O’ ALWAYS (and)
Evaluation object WO4 NOTEQUALS value ‘SYS-E’ ALWAYS
The next step is to reverse the logic associated with the locations:
wo4 = ‘SYS-D’ and
location like ‘BLDG-100-%’
becomes
Rule 3 “Check SYS-D locations” SKIP (if)
Condition 10
Evaluation object WO4 EQUALS ‘SYS-D’ ALWAYS (and)
Evaluation object location NOTLIKE ‘BLDG-100-’ ALWAYS
The next one becomes:
Rule 4 “Check SYS-O locations” SKIP (if)
Condition 10
Evaluation object WO4 EQUALS ‘SYS-O’ (and)
Evaluation object location NOTLIKE ‘BLDG-750-‘ (and)
Evaluation object location NOTLIKE ‘BLDG-100-‘ (and)
The last one becomes:
Rule 5 “Check SYS-E locations” SKIP (if)
Condition 10
Evaluation WO4 EQUALS ‘SYS-E’ ALWAYS (and)
Evaluation object location NOTLIKE ‘BLDG-920-’ ALWAYS (and)
Evaluation object location NOTLIKE ‘BLDG-220-’ ALWAYS (and)
Evaluation object location NOTLIKE ‘BLDG-640-’ ALWAYS (and)
Evaluation object location NOTLIKE ‘BLDG-710’ ALWAYS (and)
The reversal of the logic for the locations of WO4 is not as obvious as the other examples so you have to be careful and keep in mind that you are after those records you wish to SKIP, leaving the transactions of interest to process through the MIF. In the location evaluations the WO4 must be equal and then skip if the locations do not match. If you think about this you will realize it makes sense but it is not necessarily intuitively obvious the first time encountered.
So our original SQL where clauses resulted in a total of five rules with their associated conditions and evaluations.
You may have noticed the original SQL code had a percent sign at the end of the locations signifying that there were sub locations, ie extensions to the location codes that should be included. Experimentation shows the MIF will also pick up the sub locations in its evaluation, no percent sign needed.
Remember the rules are evaluated in order and you are skipping not selecting and when to use conditions versus evaluations and you should not have too much trouble. Read the two IBM documents referenced at the beginning as they are very helpful.
Hope this helps you understand the MIF logic as much as it helped me.
