Do Actions in an Application Engine: A Closer Look

I have sometimes found the Do actions in an Application Engine to be pretty confusing – especially the nature of iterations that happen with each of these actions. I thought it would be informative to share some insights into the working of these actions in an AE program.

Consider the following scenario:

If we have a select statement as below:

Select emplid from PS_JOB where action_dt = sysdate order by emplid.

Suppose executing this query on your DB returns 10 rows. To understand the working of the Do actions, if I embed this query in an AE and print the emplid in a file, how many EMPLIDs will be printed in case of each Do actions?

My AE step would looks like this:

The Do When in the above diagram can be replaced by Do While and Do Select, while checking for the number of iterations. The Do Actions will contain the SQL select statement as mentioned before and the Peoplecode will write the Emplid value to a File. In case of Do Until, Peoplecode will precede Do Until (as Do Until is the last action).

Coming back to our original question.

How many times will the EMPLID be written in the file in each of these cases??

Do When: Do When is a loop entry criteria. This will always be executed once and only once as long as the SQL statement fetches a row. Thus, with Do Until, one row will be printed. This is pretty straight forward.

Do While: All those of you who thought that the Do While loop will iterate 10 times will need to rethink the working of this loop. Do While will c

ontinue executing until at least one row is fetched. The background execution logic of a Do While loop is as follows: Every time, the AE executes the SQL select statement in the Do While Action, if it returns a row it will proceed to further actions, if not the step will be terminated. What is to be understood here is that, in the absence of a loop termination criteria (or in other words logic to discard already selected rows) the Select stat

ement inside a Do While will always fetch the same row. This will mean that a query like the one we have written (select emplid from ps_job) will go into an infinite loop, as there is no termination criterion for this SQL. Thus, this will go into an infinite loop and the same EMPLID (the first Id returned by the query) will be printed in the file. Always keep in mind that a loop termination criterion is absolutely necessary in case of a Do While.

Do Until: Do Until is uncomplicated. It will execute until a row is returned by the query and this ensures that the step is executed atleast once. As the query returns a row, the loop gets terminated after the first iteration itself.

Do Select: Do Select is the most commonly used Do action. But I left Do Select for the last because even this is kind of tricky like Do While. I do not know how many of you have noticed the various types of a Do Select action:

What concerns us out here is the difference between Select/Fetch and Reselect. Will there be any difference if I run the Do Select in these two different modes?

Yes, the working of the Do Select is completely different in these two cases. In case of a Select/Fetch the loop will iterate 10 times, while for Reselect an infinite loop is triggered (exactly similar to a Do While). The reason lies in the difference in working of these two Do Select types. Select/Fetch hits the Db just once, fetches all the values of the Select statement and puts it in a cursor and moves through the cursor during each iteration. This allows a sequential processing which is so integral to row by row processing in an Application Engine. While Reselect queries the DB for each iteration and picks up the first row returned by the select statement and puts it in a cursor. Thus, at any given point of time there will be only one row in the cursor of a Reselect type Do Select action. Moreover, Reselect will be slower than a Select/Fetch due to the DB trips involved in the former. Why does Reselect go into an infinite loop? This happens because for each iteration, the same SQL select statement is fired and the row returned first is buffered into the cursor (unlike a Select/Fetch where the SQL is executed against the DB once and then the AE moves successively through the cursor and terminates once it reaches the end of the cursor). Thus, in case of a Do Select of Reselect type, we will get a result similar to Do While – an infinite loop with the same EMPLID printed.

So what is the difference between a Do Select of Reselect type and a Do While? Remember that Reselect is used when the Restart function of an AE is turned on. A Select/Fetch Do Select does not execute commits for the entire action and thus will not be useful in case of a restart. This deficiency is overcome by the Reselect type, where commits are executed during the Step execution. At hindsight, I would always recommend to use the Restartable function (which is similar to Select/Fetch but with commits turned on) over the Reselect option. Coming to the difference between a Do Select (Reselect) and a Do While boils down the elementary question – why do we have two separate actions if they perform the same function? Do While and Do Select are fundamentally different, the first is similar to a FOR loop (executes for a definite number of times), while the latter is similar to a WHILE loop (executes till a condition is true). That is exactly the reason why a Do While comes before a Do Select.

What's new with Time and Labor 8.9? Schedules

First off - Time and Labor 8.9 is not vastly different from Time and Labor 8.8. There are no breakthrough changes and transitioning to the new version is seamless. That is not to say that the product has no enhancements at all! This post discusses one important feature change that has happened - Setting up and using Work Schedules.

8.8 Scenario:
In 8.8 Schedule creation was a 4 step process. The steps followed were:
Create Shifts --> Create Workdays --> Create Schedule Template --> Create Schedule Definition.
After creating the schedules, a Schedule Calendar Build process had to be run to create the whole Schedule Calendar.

8.9 Scenario:
Creating a schedule in 8.9 has been reduced to a One Step process!! In 8.9, creation of shifts and workdays have been made optional and the concept of a schedule template itself has been removed. In 8.9, one can create a schedule by directly entering the work hours of each day on the Schedule Definition page. This has greatly simplified the creation of Schedules in 8.9. Another caveat is that the Schedule Calendar creation process has been obsoleted. It is no more necessary to build a schedule calendar now, the calendar is auto-built by a view 'SCH_CLND_DTL_VW'.

New Feature: The most significant change in 8.9 related to schedules is the inclusion if a new definition - Schedule Group. In 8.9 and above, it is mandatory to create a schedule group before a schedule definition is created. One Schedule Group has to be created for each Setid. As all schedule definitions are keyed by Setid, a Schedule Group will act as a higher key to control the access to schedules to various users. The Schedule Group set up page is shown below:


This new definition has the following functional fallouts:

• A new field has been added in the Workgroup definition page called Schedule Group and it has to be filled before selecting the appropriate Work Schedule.
• Schedule Group has been added as a new field in the 'Assign Work Schedule' and 'Assign Work Schedule to Group' pages. In these pages also, a Schedule Group has to be defined first before entering the Schedule id value.

In short - the major changes in Time and Labor 8.9 for Schedules are:

• A single step schedule creation process compared to a 4 step one.
• A new definition called Schedule Group introduced.
• Schedule Assignment at employee, group and Workgroup level requires an extra field now - Schedule Group.
• New page in ESS and MSS to view the schedule assignment of the complete month

Major tables related to schedules:
Shift Definition - PS_SCH_SHFT_TBL
Schedule Defn. - PS_SCH_DEFN_TBL, PS_SCH_DEFN_DTL.
Sched. Calendar View - PS_SCH_CLND_DTL_VW
Schedule Assignment - PS_SCH_ASSIGN

Designing Workgroups in Time and Labor

Workgroup is a nodal definition in Time and Labor. It is synonymous to a Paygroup in Payroll and Absence Management and every valid Time and Labor employee has to be attached to a Workgroup. Time Reporters can inherit a number of features including their Work Schedule, Holiday Schedule, TRCs they have access to, the Time processing rules applied to them etc.from the workgroup. Designing workgroups and employees eligible for each workgroup is a critical aspect of any Time and Labor. Here are few thumb rules for designing workgroups:

• A Workgroup only contain either Positive Time Reporters or Elapsed Time Reporters - never a combination of both.
  
• All employees in a workgroup will have access to the same set of TRCs.
• All employees in a workgroup will be subject to the same Time Processing rules.
• All employees in a workgroup should have the same work Period (Weekly, Biweekly, Monthly etc.)
• Not a robust design to create separate workgroups for different schedules. A schedule can and should ideally be directly attached to an employee.
• If the definition of the day breaker varies for various employees in your organisation, then it might be required to group employees together based on the day breaker requirements. Thanks to Galego for bringing this point to notice.
  

In short - group together all employees who access the same TRCs and have the same time and labor rules together into one workgroup (also ensure that all employees are either Elapsed or Punch in a workgroup and share the same period id). All other definitions like a Work Schedule, Holiday Schedule, Compensatory Time Off plans etc. can be attached to the employee at other levels. A number of implementations have ended up having an unmanageably large number of workgroups as the creation of a workgroup depended on a schedule, location etc.

This approach will greatly reduce the number of workgroups that will have to be configured and increase the maintainability of the application. Workgroup is inherently a definition local to Time and Labor whose main purpose is to ensure that the correct time processing rules are applied during the TIMEADMIN process. The performance of the TIMEADMIN process can also be significantly improved by decreasing the number of workgroups.

Power Packed Time and Labor

Time and Labor is the native Peoplesoft application that captures the ‘time-at-work’ of your employees. It is an extremely powerful and comprehensive application that can greatly simplify effort capturing, reporting and interfacing requirements of your organizations. The innate capabilities of this product make it an extremely strong choice for the time capturing requirements of your organization. So what makes Peoplesoft Time and Labor, the preferred product?

Employee’s Perspective:

Ø Feature to enter time for any preferred time period. This makes time reporting seamless for employees. Employees can choose to enter time for a Day, Week, Month or any other period that they wish to choose.

Ø Feature to enter Mass Time. The Mass Time feature lets employees handle scenarios where they want to report a certain effort/time-off for an extended period of time (like vacation for one month). Complete time entry for such a scenario can be completed over three mouse clicks! It does not get easier than this!

Ø Pre-delivered reports: Peoplesoft Time and Labor come with powerful reports that show employees the summary and detail of their reported time on a daily, weekly and monthly basis. The employees can even compare their reported time to the scheduled time for any given day.

Ø Excellent notification features: Peoplesoft Time and Labor will notify employees when their manager approves/denies any of their reported time. This is again highly configurable and can be turned off if not required.

Ø Feature to view Payable Components separately: Not all reported time may be paid out to the employees and at times, the time reporting rules might create certain payable time for the employees. Peoplesoft Time and Labor provides separate reports to view the time that will be paid out to payroll.

Ø Mobile Time Feature: A powerful feature that lets employees report time on the move. Employees can report time while not connected to the Peoplesoft environment through their PDAs, Laptops etc.