Application Engine SQL Actions

Application Engine (AE) is a powerful tool delivered by PeopleSoft which can replace COBOL completely and SQR’s partly. With AE’s you will be able to lots of works. But one of the common mistake seen across the industry is that people are not sure about the features of an AE and they just see it as a process which can be scheduled to execute PeopleCode.

If you have seen the application engines created at customer site, 80% of them will have a Do Select statement, which selects the row and a PeopleCode action which will either insert/update the data using a CI or some SqlExec’s which will insert/update the data.

I wonder why people don’t use the other actions available in the application engine, which if used could save time, effort and improve the performance of the process. I believe it is because of lack of training related to PeopleSoft Application Engine. With this post I would like to provide an overview of SQL actions available in PeopleSoft Application Engine’s so that those who are not familiar of AE’s can think of using it when you are designing an Application Engine. There are separate properties for the SQL actions, but I’m not going to describe it here. If you are looking for details on any specific action, please feel free to put your question in the comments section.

Do When Action

This action can be related to an IF statement. The Do When is true when the sql inside the action returns any one row. If the Do When action is true, then the application engine will execute actions following the Do When, otherwise it will skip to next Step. Think of using this statement if you have a requirement when you need to check a flag in database table (most used will be installation tables) and based on the flag you need to insert or update values into database. You can use Do When to check the flag and then use sql actions for insert or update. Do not think of using this action if more than one step in your process requires this same check. In such scenarios select the value to the state record and use peoplecode action to branch out. This will eliminate duplicate database trips.

Do While Action

If you want to put a loop on your PeopleCode/SQL actions based on a value in the database tables, then this is the best action for you. The DoWhile action will execute whenever your sql select statement inside the action returns a row. So make sure that the flag which you are checking for looping with DoWhile is turned off by your SQL or PeopleCode actions whenever you condition is satisfied. Otherwise you Do While will go for an infinite loop.

Do Until

This action is same as your Do While action, only difference is that it will check for the conditions only after executing all other actions. What it means to you is, your Do Until executes at least once even if the condition in the sql is false. Use this looping mechanism if you wish to execute your actions at least once.

Do Select

I think you needs no explanation for this, because almost 90% of you will be using Do Select actions. You can consider this action if you want to select some data from db tables and process it row by row. Typically, your do select should exit execution once it selects all the rows from db tables based on the conditions specified in the SQl. But still the behavior can vary based on the properties defined for the action, it can even go for an endless loop of select statement if not carefully coded.

Sql Action

SQL action is to execute any database statements like select, insert, update, delete etc… This action will directly interact with database and execute your sql statements. Although this action is dedicated for sql’s I see people tend to write SqlExec() function inside PeopleCode action. This is a wrong trend and I think they try that because they need to execute multiple sql’s. In such scenarios you can think of creating a new section with multiple steps which executes all your sqls in SQL action and you can think of calling the section from the base section. Alternatively you can think of using %Execute() meta sql for executing multiple sql statements.

All these actions I have mentioned here are related to SQL’s only. There are other powerful and targeted actions available in AE are including PeopleCode, Log Message, Call Section and XSLT.