Is it possible to create an escalation and notification based on data from multiple tables?
Only one Object can be associated to the Escalation; if there is no relationship defined with another object, then only data from this one object can be evaluated in the Escalation.
Two objects can be used in an Escalation. An example of a business scenario to illustrate this usage case is as follows. A person requests a PO; when the ordered items arrive at the shipping dock, the receiver marks the status as
'WINSP' on the receipt; the person who ordered the items is required to come to the shipping dock to physically inspect the items before the receipt status can be updated to WASSET.
To trigger the notification process, the person who requests the PO, and who also needs to be notified of the receipt, is indicated in the Ship To Attention field on the PO. (po.shiptoattn) Additionally, the item must be flagged on the PO to require inspection.
An Escalation is configured to send a Notification to the PO requester when it finds a receipt that has been updated to 'WINSP' status within the last 24 hours. (matrectrans.status = 'WINSP';)
There is a simple way to use the 2 tables necessary, PO and MATRECTRANS, to query for this receipt scenario in a single Escalation/Notification. In the Escalation's Applies To field, select PO. Subsequently, a relationship between the PO and MATRECTRANS objects can be made in the clause that is entered in the Condition field with a syntax similar to, or based on the following:
ponum IN (SELECT ponum FROM matrectrans WHERE status = 'WINSP'
AND issuetype ='RECEIPT' AND transdate >= (SYSDATE-3/1440))