Application Engine - Part 1 - Development & Execution

 

If I have to provide a training on Application Engine to my co-workers who have never developed any Application Engine in Peoplesoft, I would use this blog.

Here I am going to describe how to develop a simple Application Engine program covering Sections, Steps, Actions, State records, Meta-SQLs, etc. in their simple forms using a simple example. We will discuss four ways to execute an Application Engine as well. Once you have the basic idea, you will be able to grasp the detailed topics in an efficient manner.

The example has been created and tested in Peoplesoft environment with DEMO database.

In this example, we will copy two GL journals i.e. create copies of two journals with new journal ids and dates. The major steps involved will be
1) Start the Application Engine Program.
2) Log a message.
3) Check if there is any journal specified in the in the detail run control record.
4) If there is no more journal to be copied, Go to Step # 9.
5) Read the next row from Run Control detail record for Business Unit, Journal Id, Journal Date to be copied from and the Journal Id, Journal Date to be copied to.
6) Save the above Business Unit, Journal Id, Journal Date fields from the current run control row to a temp/working table. (This table is going to be STATE record for the Application Engine).
7) Copy the journal.
8) Go to Step # 3 for next journal to be copied.
9) This marks the completion of the Application Engine Completion.
 Let us create a run control record with the following structure. The first 3 fields OPRID, RUN_CNTL_ID and REQUEST_NBR are keys. Please go ahead and build the record as well.

Let us start building Application Engine program. In Application Designer select File and New and select Application Engine. The following new Application Engine will be shown.

In the MAIN section, change description to "Copy Journals".
Save the Application Engine as AJ_COPY_JRNL.
Select Application Engine properties. Update the tabs as follows.

Updated step01 as "GoPrgMsg" and its description as "Log Program Begin Message".
Right Click on step "GoPrgMsg' and select "Insert Action". Right Click on the default action 'SQL' and select "Log Message".

Right Click on step "GoPrgMsg' and select "Insert Step/Action". Click on the new step GoPrgMs2 which was created under GoPrgMsg step. Change GoPrgMs2 as "ReadReq1" and its description as "Read Next Run Control Request".

Under the Step ReadReq1, change the default action from SQL to Do Select and the description to "Get new Journal to copy". Save the Application Engine.

Right click on the action Do Select and select View SQL option. Paste the following SQL there.
%SelectInit(OPRID, RUN_CNTL_ID, REQUEST_NBR, BUSINESS_UNIT, JOURNAL_ID, JOURNAL_DATE, NEW_JOURNAL_ID, NEW_JOURNAL_DATE, PROCESS_FREQUENCY )
 SELECT OPRID , RUN_CNTL_ID , REQUEST_NBR , BUSINESS_UNIT , JOURNAL_ID , %DateOut(JOURNAL_DATE) , NEW_JOURNAL_ID , %DateOut(NEW_JOURNAL_DATE) , PROCESS_STATUS
  FROM PS_AJ_JRNL_COPY_RC
 WHERE OPRID = %Bind(OPRID) AND RUN_CNTL_ID = %Bind(RUN_CNTL_ID)
   AND PROCESS_STATUS IN ( 'N' )
  ORDER BY REQUEST_NBR

The %SelectInit is a special function for Application Engine. It will update (or insert if not exists) the STATE record with the values selected from the run control. The STATE record is used as working storage to save the values to be passed between various steps/actions of the Application Engine.

Right Click on MAIN section and select Insert Section. A new section is created under MAIN section. Change section name to COPYJRNL and its description to Copy Journal.

On the Do Select action in step ReadReq1 of MAIN section, Right click and select Insert Action option.
Under the Step ReadReq1, for the new action just inserted, change the default action from SQL to Call Section and the description to "Copy Journal". In the drop down for Section name, select COPYJRNL.
 So far the Application Engine program looks like the following.

Go to the section COPYJRNL. Right click and select Insert Step/Action.
Repeat the above 3 times.
In the section COPYJRNL, update the step names and descriptions as follows.
001 - step name = CPJRNLHD,  Description = Copy Journal Header
002 - step name = UPDJRNLH,  Description = Update Journal Header
003 - step name = CPJRNLLN,  Description = Copy Journal Lines
004 - step name = UPDJRNLL,  Description = Update Journal Lines

Go to the section COPYJRNL,
   step CPJRNLHD, Right click and select Insert Action.
   step UPDJRNLH, Right click and select Insert Action.
   step CPJRNLLN, Right click and select Insert Action.
   step UPDJRNLL, Right click and select Insert Action.
Now the section COPYJRNL should look the following.

Right click on SQL action of the step CPJRNLHD and select View SQL to open the SQL editor for the action. Paste the following SQL there.
%InsertSelect(JRNL_HEADER, JRNL_HEADER, JOURNAL_ID = %Bind(NEW_JOURNAL_ID), JOURNAL_DATE = %Bind(NEW_JOURNAL_DATE))
  FROM %Table(JRNL_HEADER)
 WHERE business_unit = %Bind(BUSINESS_UNIT) and journal_id = %Bind(JOURNAL_ID) and journal_DATE = %Bind(JOURNAL_DATE);
This will insert the header using all fields from the journal_id specified but the three fields business_unit, journal_id and journal_DATE will be picked up from the bind variables we populated earlier.
 
Right click on SQL action of the step UPDJRNLH and select View SQL to open the SQL editor for the action. Paste the following SQL there.
UPDATE ps_jrnl_header
  SET UNPOST_SEQ = 0, JRNL_HDR_STATUS = 'N', JRNL_CREATE_DTTM = SYSDATE, (FISCAL_YEAR,ACCOUNTING_PERIOD) = (
 SELECT FISCAL_YEAR,ACCOUNTING_PERIOD
  FROM ps_cal_detp_tbl
 WHERE setid = 'SHARE'   AND calendar_id = '01'
   AND %Bind(NEW_JOURNAL_DATE) BETWEEN begin_dt AND end_dt)
 WHERE business_unit = %Bind(BUSINESS_UNIT)
   AND journal_id = %Bind(NEW_JOURNAL_ID)
   AND journal_DATE = %Bind(NEW_JOURNAL_DATE);
Selection from PS_CAL_DETP_TBL is made to update the appropriate Fiscal_Year and Accounting_Period for the Journal_date.

Right click on SQL action of the step CPJRNLLN and select View SQL to open the SQL editor for the action. Paste the following SQL there.
%InsertSelect(JRNL_LN, JRNL_LN, JOURNAL_ID = %Bind(NEW_JOURNAL_ID), JOURNAL_DATE = %Bind(NEW_JOURNAL_DATE))
  FROM %Table(JRNL_LN)
 WHERE business_unit = %Bind(BUSINESS_UNIT)
   AND journal_id = %Bind(JOURNAL_ID)
   AND journal_DATE = %Bind(JOURNAL_DATE);

Right click on SQL action of the step UPDJRNLL and select View SQL to open the SQL editor for the action. Paste the following SQL there.
UPDATE ps_jrnl_ln
  SET UNPOST_SEQ = 0
 WHERE business_unit = %Bind(BUSINESS_UNIT)
   AND journal_id = %Bind(NEW_JOURNAL_ID)
   AND journal_DATE = %Bind(NEW_JOURNAL_DATE);

As of now, the Application Engine is complete and ready. If you are up to this stage, you know the basic Application Engine development. Let us discuss Application Engine execution. There are four ways to execute an Application Engine.
1) Executing through Process Scheduler.
2) Executing from Application Designer.
3) Executing from Command Prompt.
4) Calling from Peoplecode.

Let us discuss the Application Engine Execution in each of the four ways.

Executing through Process Scheduler
Perform the necessary tasks of creating a run control page, component and registering the component.
I have added the objects with the following navigation.

Add two records to the run control as shown above.
The run control table should have the following data.select * from PS_AJ_JRNL_COPY_RC where OPRID = 'VP1';

Go back to Run control page. Click on RUN.

Click on OK. Click on Process Monitor.

When the process completes successfully, let us run following SQL to see if journals are copied.
select * from ps_jrnl_header where business_unit = 'FED01'
and journal_id in ('GTAS000018','GTAS000019','TSTS000018','TSTS000019');

Let us verify that the new journals are viewable online. Navigate as shown below. Enter Business unit as FED01 and Journal Id begins with TST. Blank out all other criteria.

Select the journal id TST000018. The Journal opens up.

If you want to rerun the Application Engine, please make sure to either update the journal_id and journal_date combination in the run control or delete the newly created journal using the following SQLs.
delete from ps_jrnl_header where business_unit = 'FED01'
and journal_id in ('TSTS000018','TSTS000019');
delete from ps_jrnl_ln where business_unit = 'FED01'
and journal_id in ('TSTS000018','TSTS000019');

 

Executing from Application Designer
Let us delete the journals that we had just created using our Application Engine running in Process Scheduler. Open the Application Engine in Application Designer. Click on Edit. Select Run Program. Specify the Run Control Name as shown.

 

The Application Engine will run on the client, a window will appear showing Application Engine execution.
The window will close automatically when Application Engine completes.

The log is there at c:\temp\AJ_COPY_JRNL.log which will look like

If you try to open the Journal online, you will see the journals are copied.

Executing from Command Prompt
Let us delete the journals that we had just created using our Application Engine running in Application Designer.
Open the a command prompt. Type the following command to execute the Program.
psae -CT ORACLE -CS EP92DM05 -CD EP92DM05 -CO "VP1" -CP "VP1" -R TEST1 -AI AJ_COPY_JRNL -I 0 -FP"c:\temp\AE_AJ_COPY_JRNL" -OF14

You may save the above command in a batch file too.
The general syntax is
psae -CT dbtype -CS server -CD database_name -CO oprid -CP oprpswd
-R run_control_id -AI program_id -I process_instance -DEBUG (Y|N)?
-DR (Y|N) -TRACE tracevalue -DBFLAGS flagsvalue -TOOLSTRACESQL value?
-TOOLSTRACEPC value -OT outtype -OF outformat -FP filepath

If you try to open the Journal online, you will see the journals are copied.


Calling from Peoplecode
Let us delete the journals that we had just created using our Application Engine running in Command Prompt.
The general syntax for alling Application Engine from Peoplecode is

CallAppEngine(applid [, statereclist ]);

So in our case it will be

CallAppEngine(AJ_COPY_JRNL , GL_JRCPSJE_AET);


That is it in this article. Now it is a good time to read about advanced topics in Application Engine like Application Engine types, Multiple State records, Temporary table instances, COMMIT handling, etc.
There is delivered Application Engine GL_JRNL_COPY. You may compare that one with the one simple one you just developed and start enhancing yours by incorporating more complex things like ADB_DATE, LEDGER_GROUP, LEDGER, REVERSAL, Document Sequencing, etc.

Play around with Application Engine, one feature at a time.