Overview of Time and Labor

Time And Labor Overview

From: jijucvengal, 4 hours ago

Time And Labor Overview

view presentation (tags: time peoplesoft payroll american)

First in a series of presentations on Peoplesoft. For more information visit my blog at www.peoplesofthrms.blogspot.com


SlideShare Link

Peoplesoft popularity by numbers!

Every other day I indulge in some kind of debate regarding the future of Peoplesoft and the imminent assault of Fusion on the PS breed!! I must say that its murky times for us Peoplesoft consultants though Oracle has gone overboard to support and develop the product. Considering this, it was pleasant to read this post on the Grey Sparling blog about the popularity of Peoplesoft. Some interesting numbers from the article:

• 9 of top 10 commercial banks are ps customers
• 59% of top 100 of fortune 500 companies own ps
• retail - the 5 biggest use ps
• 6 of top 10 communications companies use PeopleSoft
• 60% of the top 15 insurance companies use PeopleSoft
• 70% of top 10 health care organizations use PeopleSoft
• 19 us states use PeopleSoft
• 50 of largest counties and cities use PeopleSoft
• 7 of top 10 research universities use PeopleSoft
• 8 of top 10 printing and publishing companies use PeopleSoft

Numbers and statistics can be misleading if not read in the correct context, but considering the features of the product, the way Peoplesoft has evolved under Oracle by incorporating so many of the Fusion middleware features and the very vocal commitment of Oracle to Peoplesoft customers tell me that we should be in business for quite some time!

Auto creation of user profiles

There are multiple scenarios where its required to create new user profiles in bulk. A recent requirement I had was to create user profiles for all employees in the system during a pilot upgrade exercise. The best approach to this problem is to achieve the same by a CI built on the USER_SAVEAS component (Copy User profile component).
Was mulling over the various options to auto generate passwords for the user profiles and ran into the delivered function in DERIVED_HINT.EMAILPSWD.Fieldformula event.

SQR Migration Strategies

In continuation to my earlier post on SQRs and XML Publisher, this discussion aims to arrive at a decision matrix to migrate from SQRs to other Peoplesoft/Fusion tools. A number of organisations that run on 8.0, 8.3 and other older versions of Peoplesoft have widely utilised SQRs for non-reporting scenarios like Inbound Interface processing, Mass Updates, Utility processes etc. With the very vocal recommendation of Oracle that customers should refrain from using SQRs (though its unlikely that Oracle will stop supporting SQRs on Peoplesoft any time now) in newer releases, it is extremely prudent to have in place a strategy to move all the SQR programs to an alternate tool. The decision matrix below has been prepared by taking into consideration the various type of SQRs programs as well as the capability/weaknesses of the other tools. This is a very high level illustration and your expert comments and thoughts are invited to further refine this tool.

Type of SQR	Migration Strategy
Inbound Interface – Data source: File	File Layout + AE + CI if insertion is done to a component
Inbound Interface – Data source: Peoplesoft	App Messaging/IB
Mass Data Changes (Mass hires, Mass update of a table etc.)	AE + CI if updation to a component is done. Analyse whether the Peoplesoft Mass Change functionality can be utilized.
Outbound Interface (Flat files, Dataset)	AE + XML Publisher for report generation/ Web Services if 3rd party supports
SQCs	Custom SQCs can be replaced by Application Classes to be invoked from Peoplecode.
Utility programs (Clean up, File manipulation, Audits, Status Checks)	Audits, File Operations, OS related processes are better done by SQRs, retain them. Utilities that affect database can be replaced by AE.
Letters and Forms (online forms as well as letters that are mailed)	XML Publisher + Peoplecode notification classes for mails
Reports – Simple single data source (reports with one BEGIN-SELECT)	XML Publisher
Reports – Simple multiple data sources (reports with more than one BEGIN-SELECT and simple calculations)	Migration of these might require the use of AE to execute the calculations and XML/XSL modifications. Move to XML Publisher if the logic is not complicated, otherwise retain SQRs.
Reports – On-breaks and multiple reports	XML Publishers
Reports – Complex with multiple BEGIN-SELECTS and complex conditional logic	Do not migrate – the effort is not worth it.
Reports – Status of batch process	This category contains the production of error reports and status reports of processing done by an AE or other batch programs – move to XML Publisher.

Time and Labor Security demystified

Security in Time and Labor is an area that needs more clarity to most new implementors. Almost every team I have consulted for has had the question 'how do I ensure that the employees reporting to a manager shows up in the manager self service pages of Time and Labor?'
This post aims at being a guide towards designing the T&L security setup.

T&L Security is primarily concerned with ensuring the correct access of reportees to managers as well as controlling the dates which are open to any employee to report time. We are more concerned with the first case here.
Security in Time and Labor is a two step process - 1. Grouping the employees reporting to a manager/HR administrator together and 2. Granting access to the correct groups to the respective managers. To group employees together T&L has a powerful feature called Dynamic groups (there is another category called static groups which are not practically used and thus does not deserve mention here) which is similar to the Group Build feature in core HRMS. A Dynamic Group constructs a group of employees based on an SQL selection criteria (for example - select emplid from ps_job where reports_to = ) and is thus truly dynamic. The Dynamic Group once created has to be refreshed on a daily basis to ensure that the group includes all eligible employees.
The illustration below shows how a dynamic group can be created by defining the appropriate SQL eligibility criteria.
After the group has been created, the next step is to assign this group to those managers/administrators who need access to the same. Access in Time and Labor is controlled by an Operator's ROWSECCLASS. So the groups will have to be linked to the eligible ROWSECCLASSES to enable access for managers to their reportee's data. The illustration below shows how a group can be linked to a ROWSECCLASS. Keep in mind that one group can be linked to multiple ROWSECCLASSES and one ROWSECCLASS can be linked to multiple groups.



Now coming to the important question - how does a manager get access to employees under him? Let's just look at the below process flow for that:

1. Manager logs in with his/her Operatorid.
2. The ROWSECCLASS of this Operator is fetched from PSOPRDEFN.
3. The TL_GRP_SECURITY table is queried to get all the groups that this ROWSECCLASS has access to.
4. The TL_GROUP_DTL table is queried to get all the EMPLIDs part of each group fetched in step 3.
5. The employees fetched are displayed on the MSS pages.

This is an extremely simplified definition of what actually goes behind the scenes when the system tries to retrieve the employees in a T&L MSS page. So to ensure that a manager has access to his reportees ensure the following:
a. All the reportees are part of atleast one Dynamic Group. Query TL_GROUP_DTL to get the groups of an employee.
b. The manager/administrator has access to those groups. Query TL_GRP_SECURITY to get all the groups that a particular ROWSECCLASS has access to.
c. Schedule the Dynamic Group Refresh process on a nightly basis to ensure that the dynamic groups are up to date.

Caveat: The above process requires the creation of a large number of ROWSECCLASSES and Dynamic Groups to maintain exclusivity of employee access to managers. Organisations that follow the above approach will need to custom create a program thatwill create new ROWSECCLASSES when a new manager is hired and programatically create dynamic group and assign security to that dynamic group for the new ROWSECCLASS created. This complex process can be circumvented by customising the code behind the 'Get Employees' button present in all T&L MSS Employee Search pages. The approach would be to create a single Dynamic Group and enroll all employee in the same and give access to all managers/administrators for that single group. Customize the Peoplecode behind the Get Employees button to fetch only those reporting to the manager from the entire set fetched by the vanilla logic explained above. The customisation has to be done in FUNCLIB_TL_CP.MGR_SS_FUNC.FieldFormula event.




T&L security design is an area where we see lot of questions being raised, especially considering the integration of AWE in v9.1. If you have specific questions on this, reach out to me @ jiju@hroiconsulting.com

Peoplesoft Time and Labor Setup Guide: Summary

Having completed over seven end to end roll outs in Peoplesoft Time and Labor - my team recently decided to design a quick implementation package for Peoplesoft Time and Labor implementations. This package contains ready made data loading programs for every T&L setup component, a complete T&L design strategy guide which talks about the best practices in designing the various data elements, ready made Peoplesoft HRMS to Time and Labor integration program, an exhaustive Client Questionnaire gathered from our experience that will help the team capture even the smallest client requirement at the earliest stage, a Data Collection form for all T&L components and a reported time mass load program. These tools will drastically reduce the implementation time for any T&L implementation and is a ready reckoner for any organisation going for a T&L implementation.

Pasted below is a summary of the setup sequence as well as set up methodology for all T&L components, an excerpt from the setup guide.

Setup Component	Data Loading Method
T&L Installation	Manual
Time Zone Offset	Manual
Time Periods	Manual
Pay System	Manual
TRC Category	Manual
TRC	AE
TRC Program	Manual/AE depending on volume of data
Compensatory Time Off Plan	AE
Override Reason Code	Manual
Time Reporting Template	Manual
Shifts	AE
Schedule Group	Manual
Schedule Definition	AE
Holiday Schedule	AE
Value List	Manual
Template Built Rule	Manual
Rule Program	Manual
Task Template	Manual
Task Profile	Manual
Task Group	Manual
Time Administration Options	Manual
TL Permission List Security	Manual
Dynamic Group Creation	Manual
Employee Enrolment	AE
Compensation Plan Enrolment	AE
Schedule Assignment	AE

SQR and the XML Publisher Juggernaut

I have been surprised at the vigour with which Oracle has been promoting its Fusion reporting tool - XML Publisher. Any documentation/presentation/webcast on Fusion Tools talks in great length about the features and advantages of XMLP, how it has been integrated into Peopletools and more importantly how its going to be the only reporting tool for Fusion moving forward. One of the earliest documents I read on Strategies for Peoplesoft customers to be fusion ready had abandoning the use of SQRs and embracing XMLP as one of the main fusion oriented decisions for a Peoplesoft customer. Toeing this line, almost all new Peoplesoft implementations are religiously rejecting SQRs and embracing XMLP as the standard reporting tool.Is this the end of the road for SQRs? Probably yes!As much as I have seen, XMLP is a wonderful reporting tool. Creating a report using XMLP is many times easier, faster and elegant than SQRs. Its powerful feature that the layout can be designed separately will make it a hit with the business end users - who can now design their own desired layout on a Microsoft Word Document without any help from IT (I had a good laugh when I realised that all that I had to do to add a header with the Company Logo in XMLP was to add a header in MS Word! Huh? That's it?! Good bye to all the SQR coding! At hindsight another major advantage is with spacing the report. Adding a new column to an SQR report will require programatically adjusting the column number of various variables being printed to align to the new layout. But with XMLP, it would just require us to drag the fields to their appropriate places!). The Peoplesoft team at Oracle have done well to integrate XMLP in the Peopletools environment. XMLP is the newest addition to the Reporting Tools in PIA and there are a flurry of new Peoplecode classes (refer Application Packages starting with PSXP_) that facilitate the running of XMLP reports from onlines pages, AEs etc. But is XMLP a complete replacement to SQRs? XMLP taken alone is just a reporting tool, while SQR is a programming language. The flexibility and power of SQRs to handle extremely complex reporting requirements (working with multiple data sets, calculations, conditional logic, usage of data structures etc.) can not be matched by XMLP. For example can I write a XMLP report that fetches all the employees who returned from sick and maternity leave today (for this I will need to fetch the action reason of the LOA row previous to the latest RFL row assuming that sick and maternity are captured using the LOA action) and execute different processing logic depending on the leave reason? No. XMLP is not designed to work with multiple data sets and can not handle such a scenario where conditional logic has to be executed on multiple data sets (first on the current effective dated row to fetch all RFL rows and second to find out those RFL employees who had gone on LOA due to sickness or maternity). This is the forte of SQRs - the ability to handle any complex logic being a procedural language. So the more important question is - is it wise completely abandoning the use of SQRs as a matter of strategy? Both XMLP and SQRs have their own strengths and why can't we continue to use SQRs for requirements that are easier to code and better handled by that tool? For me, the most prudent approach will be to have a mix of both tools and to judiciously choose the right tool for the right requirement.

Custom names for SQR multiple report outputs

I had a requirement to produce two PDF files from a single SQR with specific file names - say RFL011008_USA.pdf and RFL011008_CAN.pdf. While there is significant documentation on generating multiple reports in SQRs, I thought that it would be instructive to share the steps to produce multiple reports with unique output file names.

The file names of the various output files are controlled by the output-file-mode parameter in the sqr.Ini file and it usually follows the convention of sqrname_XX.ext (like sqrname_01.ext, sqrname_02.ext). To override this default file naming convetion, use the NEW-REPORT command.
A sample program to produce multiple reports is as follows:

BEGIN-SETUP

DECLARE-REPORT USA
END-DECLARE

DECLARE-REPORT CANADA
END-DECLARE

END-SETUP


BEGIN-PROGRAM

LET $USA_FILE = /OPT/PSOFT/HRMS/SQR/RFL011008_USA.PDF
LET $CAN_FILE = /OPT/PSOFT/HRMS/SQR/RFL011008_CAN.PDF

USE-REPORT USA
NEW-REPORT $USA_FILE
PRINT 'HRMS REPORT FOR USA' (1,1)

USE-REPORT CANADA
NEW-REPORT $CAN_FI;E
PRINT 'HRMS REPORT FOR CANADA' (1,1)

END-PROGRAM

**Append the command -MR nn to the command line while running a multiple report. Here nn represents the number of multiple reports to be produced. (I was left wondering why only a single report was being produced when I had coded for two reports in the SQR. Adding the -MR 2 command to the command line solved the issue).

SQR Optimisation Techniques

One of the biggest challenges I've faced while writing HRMS and Payroll reports has been the performance tuning of the SQRs. A typical payroll report which needs to display the EMPLID, NAME and Earnings for a certain pay period involves the join of three to four massive transaction tables and the execution time can exceed more than 15-20 minutes regularly. How do we bring it down to an acceptable run time of below 5 minutes? Though its a long deal getting such a drastic performance improvement, I have been able to get significant results by following the below mentioned steps:

1. Check your SQL queries: Most of the performance issue can be attributed to poorly written SQLs. Critically review each SQL statement of your SQR and ensure their correctness.

2. Usage of Temporary Tables: This is number two on my list. A strategy that I adopt when multiple tables have to be joined and the report involves complex processing, is to pull out the master data and insert the same into a temporary table that has been created in the begin-setup section of the SQR. Further processing can be done by referring the data in the temporary table and this reduces trips to transaction tables whereby giving us significant performance enhancements.

3. Usage of arrays: Arrays are excellent instruments to improve the maintainability of the program. I would always use arrays to hold values of totals and other variables that are to be re-initialised multiple times in the report (note that the values of an array can be reset by using the clear-array command). If variables were used instead, the number of variables that would have to be maintained, the initialisation routine etc. would add to the complexity to the program.

4. Dynamic SQLs: The usage of dynamic sqls greatly reduces the length of a program and helps in improving its performance. One requirement I had was to generate two reports with the same output fields and filter criteria, but one sorted by EMPLID and another by NAME. In this case, the only difference was the ORDER BY clause and instead of writing two different BEGIN-SELECTs, the requirement was achieved by maintaining a single BEGIN-SELECT and passing the order by clause as a dynamic SQL.

5. Break down your SQLs: This is something you will find in Peoplebooks also. Make sure that you are querying the database only when necessary. Let's say you have to produce a report which lists the EMPLID, NAME, BIRTHDATE, MANAGER'S NAME and JOBCODE DESCRIPTION. This will involve the join of 4 tables (including PS_NAMES aliased twice) and considering the key structure of these tables, will take considerable execution time. An alternate strategy would be to have a different procedure to get the JOBCODE descr where only the JOBCODE_TBL is queried for instead of making it a part of the join.

6. Minimal code and re-usable logic: The beauty of SQRs is that it is the most flexible tool in the Peoplesoft technical environment (especially considering the rigidity and ready made nature of an AE, CI or Peoplecode) and offers the developer multiple paths to one solution. Evolve a logic that re-uses your procedures and minimises database trips.

What about Load Lookups? Documentation says that Load Lookups give significant performance enhancements. Practically I have not been able to achieve this. The look up gets loaded at the start of the report and the time taken to load the lookup offsets any advantage that this concept delivers. Usage of temporary tables is a much better alternatives - in effect the concept of the two are inherently the same - reduce database trips and querying of large transaction tables.

Learnings from a multi country phased out Peoplesoft HRMS implementation

• Understand the overall architecture and AS-IS structure of the various countries. Even if a phased out implementation strategy is sought out, it is extremely important to understand the working of the various countries in as much detail as possible. This will ensure that no major design changes are required at a later phase. It is a common observation that projects planned in silos (drawing up a design document with just one country in mind) run into major design issues at later phases as the initial design does not cater to the later phases.

• Traditional Waterfall or other sequential styles of project management does not work. This is especially true in cases of a new implementation/ migration from a legacy system to Peoplesoft scenarios. The learning curve of the users in the new system is large, their expectations are hinged on their experience of the old system and they are unaware of the capabilities of the current system. Fundamentally the users will be in a very fuzzy state and it takes time for them to come to terms with the new system. So don’t expect your requirements to be frozen. Any plan that looks at freezing requirements after the analysis phase will fail.

• Get the user to participate in development early. This is one of the most crucial points in a multi country implementation. There will be a number of screens/ functionalities that will be common across the various countries – thus its extremely important to let the users participate actively in the development of those common functionalities. A common mistake is to involve only the users of the current countries being implemented in the testing phase and realize that users of other countries have different requirement on the same panels later. The rework required at this stage can be damaging. To prevent such a happening – involve users of all countries in the development of common panels and functionalities.

• Evolutionary Prototyping is a wonderful model to use in this case (development of common screens and functionalities). Evolutionary Prototyping is a concept where the development team comes up with early prototypes (even when the requirements are not completely finalized) and lets the users test the same and come with suggestions. The prototype evolves in this manner and finally the desired product results. This is an extremely powerful method for the development of user screens and common functionalities.
• Different Project Management models for various phases. The nature of implementation varies from phase to phase. The initial phase represents a period of high risk, uncertain requirements, skeptical customer, inexperienced team members etc. The project becomes more predictable and stable as the phases progress. It makes lot of sense to use different Project Management models at the initial and later stages. Its also effective to divide the initial phase into overall system architecture analysis into one part and phase specific analysis, design and development into another. The System Analysis phase should adopt a combination of Spiral and Waterfall model. As this is a purely analysis phase, the waterfall model gives great control over the progress. The Spiral method concentrates on a specific phase with emphasis on risks and tackling the risks. For the second part, abandon the Waterfall approach and go for a combination of Spiral and Customer Oriented Development methods. This will ensure that optimum development speed is achieved with customer involvement. As the project phases out, we have the option of going for a Waterfall model or an Agile model. Waterfall because the project is more predictable and this will enable good control over various phases. But this is not a high productivity model as its extremely rigid. So its best to go for an Agile method (XP/Scrum etc.). Agile models are development oriented models. It places great onus on the capability, experience and commitment of developers. This will leverage the experience of the developers as well as deliver the product at a faster rate. One of the best practices in case of a phased roll out is to leverage the learning of the past phases in the current and future phases. Each phase in the migration will almost follow the same steps. Project management can ease out if effort is made to evolve a specific migration process document that can be followed for the coming phases.
  
• Never leave the most complex roll outs to be the last. Though it’s a good idea to start off implementing the easiest country first (will let the developers get a good idea of the architecture, gain confidence, evolve processes and more importantly give customer confidence), it can be disastrous to put off the implementation of the toughest phase till the end. The reasons for that are as below:
• This phase might have the most complex architecture and if not properly designed, it might require major redesign to the existing architecture. It is easier to make a change to the design earlier in the project.
• The most complex phase will require maximum effort and user intervention. Its always better finishing such tasks earlier in the project as you can catch up for lost time later in the project with easier tasks.
• Motivation levels of employees fag with the timeline of the project. Make use of the initial excitement and energy to get the toughest phase implemented.
• Any unforeseen event will be more difficult to handle in the end (performance drop, code overwrite, attrition etc.)
• Planning for the Roll Out dates. A multi country implementation that is spread across more than one fiscal/calendar year need to plan the rollout dates prudently. Certain points to be considered:
• Will it affect Year End processing? If so, what is the window of time when migration cannot take place? Should all migration happen before Year End? Is it ok to process Year End in two separate systems? This has to reflect in the project plan.
• Will scenarios where an employee has data in two different systems affect any processing? For example, let’s say the project involves the migration of two countries from a legacy system to an ERP. Suppose the migration of one country has been completed and suppose an employee is transferred from the second country (legacy) to the new country (ERP) after the Go Live. Now the employee data is spread across the two systems for the current year. Will there be any process (for example Payroll processing/ Leave Processing) that looks at the cumulative data of the employee for processing? This is a major design question, but generally applicable to all implementations.
• Serial-Parallel-Series approach. Can we have a mix of Phase wise and Big Bang approach? This would be the optimum plan. Ideally the project should start with one current phase (Series) and once the team gathers significant experience of the system, more than one country/phase can be implemented in parallel. This will help speed up the complete migration process. A final approach could be looking at the application in general after the implementation of all the phases and pitching in for system wide improvements (Series).

• An alternate phase wise implementation strategy is given below. This method is built on the fact that any phase wise implementation can be thought of as a Wheel and Spoke model (refer the illustration below). The system consists of a Central Core that encompasses common setups, screens, data, reports and architecture. All the other phases can be visualized as plug-ins to the core. Thus, the critical activity is getting the Central Core setup and deciding on the integration points for various plug ins. Once this is designed and developed, any number of plug-ins can be seamlessly integrated to the core. Consider the design and development of the core itself as a separate phase. This lays the foundation of a very stable integration of various phases. Combining the Series-Parallel approach to this would mean that the Central Core is implemented first, followed by Phase I in a Series manner. Now that the team has sufficient experience and processes being mature, Phase II and Phase III implementations can be taken up in parallel.

Query to get the number of working days between two given dates

A friend of mine talked about a requirement where reminder mails had to be sent out to managers who had leave requests from their subordinates which were unapproved for the last five 'working days'. Working Days meant all days between two days excluding saturday, sunday and statutory holidays.
While this can be easily achieved by a two step process, i.e. - looping through all days between the two given days and checking individually whether the day is non-working ir not, I felt that it would be interesting to get the desired output in a single SQL query.
This is what transpired:
Pseudo Code:
1. Get all the days between the two dates in an inline view.
2. Exclude saturdays and sundays from the above result set.
3. Exclude all holidays that fall in the given date range by checking the holiday schedule of the employee.

The below query fetches the working days between 01 Jan 08 and 10 Jan 08. Note that the logic to exclude holidays is constructed from a Peoplesoft point of view.
select daynum from
(select to_date('31-DEC-2007','DD-MON-YYYY') + rownum as daynum
from
all_tables
where
rownum<=(select to_date('10-JAN-2008','DD-MON-YYYY') - to_date('31-DEC-2007','DD-MON-YYYY') from dual))
where to_char(daynum,'DY') not in ('SAT','SUN')
and daynum not in (select holiday from ps_holiday_date where holiday_schedule = 'CHEG' and holiday between '01-JAN-2008' and '10-JAN-2008')

First and Last Dates of a Quarter: Oracle Query

Below is one of the many queries (Oracle specific syntaxes) one can write to get the first and last days of all the four quarters in any year:

select
add_months(trunc(sysdate,'Y'),(3*(rownum-1))) ,last_day(add_months(add_months(trunc(sysdate,'Y'),(3*(rownum-1))),2))
from
all_indexes
where
rownum<5