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
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.
Important is that the variables used in the Message text are also defined in the Template Variables grid. See the screenshot below.
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 = :1So 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
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