Escalation mail message variables (AWE)

When configuring an Approval Workflow Engine (AWE) transaction there is a possibility to configure mail messages on certain events. For example if you like to send the requester of the transaction an email when the approver doesn’t approve within a certain time frame you can setup an escalation for this.

In this example we consider a learner in PeopleSoft ELM 9.2 who requests an enrollment. His manager needs to approve his request. If he doesn’t approve in X days an escalation mail should be sent to the learner to notify him.

Step 1: Setup escalation

The Transaction Configuration component is used to set up escalation on an approval transaction. It can be found under: Enterprise Components > Approvals > Approvals > Transaction Configuration

transaction_config

Here you specify an On Escalate event and there are 2 parameters here that are related to the content of the email. The first one is the Template Name and the second one is the SQL Object Identifier. The template defines the content of the email, the SQL object fetches variables that are used in the template.

Step 2: Setup the template

The email template is set up via PeopleTools > Workflow > Notifications > Generic Templates. The template you create here you will select on the Transaction Configuration page.

template

Important is that the variables used in the Message text are also defined in the Template Variables grid. See the screenshot below.

template_variables

Step 3: Setup the SQL Objects

To fetch data, to fill in these variables, a SQL object needs to be created. This SQL object will fetch, based on the enrollment ID, the other variables. In this case Course Code, Course Name and Class Code. The SQL looks like this:

SELECT C.LM_CRSE_CODE
, C.LM_CS_LONG_NM
, B.LM_ACT_CD
FROM %Table(LM_ENRLMT) A
, %Table(LM_ACT) B
, %Table(LM_CI_TBL) C
WHERE B.LM_ACT_ID = A.LM_ACT_ID
AND C.LM_CI_ID = A.LM_CI_ID
AND C.EFFDT = (
SELECT MAX(CED.EFFDT)
FROM %Table(LM_CI_TBL) CED
WHERE CED.LM_CI_ID = C.LM_CI_ID
AND CED.EFFDT <= %CurrentDateIn)
AND A.LM_ENRLMT_ID = :1

So the date for the three variables is fetched based on the LM_ENRLMT_ID. This ID will be filled in by AWE automatically.

The reason for this is because for this transaction the header level is the LM_ENRLMNT record where LM_ENRLMT_ID is the key field of this record. You can find this configuration in the Transaction Registry: Enterprise Components > Approvals > Approvals > Transaction Registry

transaction_registry

The three variables will be available in the Generic Template as variable %2, %3 and %4. %1 is reserved for the key value, in this case the LM_ENRLMT_ID.

If the mail is sent out in multiple languages you also have to define an extra SQL object that processes the other languages. The name should be the same as the base SQL object but with a _LNG postfix. In our example the base SQL is C_ESCAL_VAR_ACT and the language variant is C_ESCAL_VAR_ACT_LNG. The language variant looks like this. It fetches the same fields, but now from the language related record. It also has an extra parameter, namely LANGUAGE_CD:

SELECT C.LM_CRSE_CODE 
 , D.LM_CS_LONG_NM 
 , B.LM_ACT_CD 
  FROM %Table(LM_ENRLMT) A 
  , %Table(LM_ACT) B 
  , %Table(LM_CI_TBL) C 
  , %Table(LM_CI_LANG) D 
 WHERE B.LM_ACT_ID = A.LM_ACT_ID 
   AND C.LM_CI_ID = A.LM_CI_ID 
   AND C.EFFDT = ( 
 SELECT MAX(CED.EFFDT) 
  FROM %Table(LM_CI_TBL) CED 
 WHERE CED.LM_CI_ID = C.LM_CI_ID 
   AND CED.EFFDT <= %CurrentDateIn) 
   AND D.LM_CI_ID = C.LM_CI_ID 
   AND D.EFFDT = C.EFFDT 
   AND A.LM_ENRLMT_ID = :1 
   AND D.LANGUAGE_CD = :2

3 thoughts on “Escalation mail message variables (AWE)”

  1. Howdy would you mind sharing which blog platform you’re using? I’m going to start my own blog in the near future but I’m having a hard time deciding between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your design and style seems different then most blogs and I’m looking for something completely unique. P.S My apologies for being off-topic but I had to ask!

    Reply

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.