Tuesday, December 1, 2009

Peopletools 8.50 supports UK English!

UK English has been added as a new language option available in Peopletools 8.50! I am sure that my folks in UK will be happy to hear that!!
Cheers to that!

Sunday, November 22, 2009

Peoplesoft HRMS 9.1: Changes to Time Approval setup and Functional Implications

I had discussed in my previous posts about the introduction of Approval Workflow Engine for Time and Labor in version 9.1. This post discusses the functional implications, changes and challenges this change poses in comparison to the Approval Process prevalent in previous versions.

A note on Time Approval in Time and Labor: Time can be approved at two levels - Reported Time or/and Payable Time. Whether reported time/payable time requires approval can be setup at the Workgroup as well as TRC level. If a TRC is set to have Reported Time approval, then the Time Administration process will not pick up time reported under that TRC unless it is approved. Similarly if a TRC is setup to have Payable Time approval, then a payable time row under that TRC will not be sent to any payroll system unless the payable time is approved.

How Approval Used to happen prior to 9.1: The approve reported time/payable time component in versions 8.9/9.0 was designed to retrieve all rows requiring approval for employees a user's security had access to. The logic used to derive the employees a user can see in a T&L MSS page is explained in this post. This meant that the user could approve the time of all users that he/she had access to as per the security settings.

What has changed in version 9.1: In 9.1, time approval is driven by the approval workflow engine (AWE). Oracle has delivered a number of approval definition ids - for example - approval by position management, supervisor id, department manager, position department manager, position supervisor, T&L approval group etc. Also, the approver in an AWE definition is determined by the 'User List' attached to the defintion id as shown below:

A 'User List' can be a SQL/Application Package code/Query or a Role that determines the operatorids to which the request should be routed for approval. Thus, the approvers of a certain request are determined by the user list attached to the AWE definition id. This means that HRMS security/T&L security no longer plays a role in determining the approver - and more importantly the fact that a certain user has security to view a particular employee in Time and Labor does not guarantee the fact that he/she will be able to view the time requiring approval of those employees.

What is the functional implication of this change:

> The biggest functional implication of this change will be the way Time and Labor administrators approve time. In previous versions, administrators could approve time of all employees they had access to by their core row level security or time and labor security. This will no more be the case in version 9.1 - even if the administrators will be able to view the employees' timesheets, they will no longer be able to approve reported/payable time by default. The user list logic of the AWE process definition will have to be customized to return administrators also as legitimate approvers if they have to have access to approve reported/payable time. In effect I would say that from version 9.1 - search security and approval security have been differentiated in Time and Labor with row level security controlling search and AWE user list controlling approval security.

> There is an option to 'Push Back' reported time in version 9.1. In previous versions, managers could either Approve or Deny reported time, but with version 9.1, managers can even push back reported time. This is a useful feature especially in case of an elapsed time configuration and users can make use of the notification functionality of AWE to notify the requester when an approver has pushed back the request.

> Challenges in data migration to version 9.1: In versions 8.9 and 9.0, there were only two tables related to reported/payable time approval and they were TL_RPTD_TIME and TL_PAYABLE_TIME. The SUBMITTED_STATUS and PAYABLE_STATUS fields in the respective tables were enough to pick out rows requiring approval and no special logic was required to find the approver of a request as approval and search security were unified. But this has undergone a sea change in version 9.1. With 9.1, there are separate AWE tables that store the 'header' and 'line' information of each transaction done in time and labor and the approval details will be picked from these header and line tables. For example, assume that you are moving from version 8.9 to 9.1 and at the time you are going live with Time and Labor 9.1, there are a bunch of Payable Time entries that have not yet been approved. How do you bring over this data to 9.1 and ensure that when the managers log into the 9.1 application, they will be able to see the pending payable time entries for approval? It will not be enough to just migrate the TL_PAYABLE_TIME table to 9.1, but the AWE tables will also have to be populated to ensure that time entries pending approval show up in version 9.1. Note that the AWE tables related to Time and Labor are newly introduced in version 9.1 and are not present in any previous version. I am noting below the important AWE tables for Time and Labor in 9.1:

Payable Time Approval:

Cross Reference Table: TL_APP_PAY_XREF

Header Record : TL_APP_PAY_HDR

Line Record : TL_APP_PAY_LINE

Note that this change could potentially affect any custom reports/processes that were designed around time approval in Time and Labor also.

Sunday, November 1, 2009

Peoplesoft 9.1 - Time and Labor: Dynamic Groups are dead, long live Dynamic Groups!

The pronouncement of the title does not hint that Oracle has taken away the definition of Dynamic and Static groups from Time and Labor, but it's significance is greatly reduced with the introduction of approval workflow engine in 9.1 and by the improved row level security framework in 8.9. In pre-8.9 days, the entire row level security in T&L used to be driven by Dynamic Groups and it used to be a design nightmare to arrive at the best security strategy as various modules drove security differently (at that point of time, frankly dynamic groups offered a pretty flexible way of driving security as it's design is distantly similar to the current robust row level security structure). But with the introduction of the very flexible and configurable row level security structure in 8.9, the need to use Dynamic Groups for row security in T&L, almost died down and now with the introduction of the AWE framework in 9.1 - I really do not see any more need to use Dynamic Groups for security purposes. This is more a message for current 8.9 and 9.0 Time and Labor implementations - you should discourage the use of T&L security driven by Dynamic Groups as much as possible and adopt the HRMS Row Level security in Time and Labor. The focus of Oracle has been towards adopting common frameworks across various modules for driving aspects like Security, Approval, Delegation etc. and it will be great to keep that point in mind while designing solutions in these areas if you are on versions lower than 9.1/9.0.

So do we need Dynamic Groups anymore in Time and Labor?
There are two areas where the use of Dynamic Groups are significant:

1. As a parameter for running the Time Administration process. Dynamic Groups will still remain to be a very useful run control parameter for running the time administration process.

2. Grouping approvers together for use in some of the T&L specific AWE definition Ids. This is a change introduced in version 9.1 in the definition of a dynamic group. A new field called 'TL Approval Group' has been introduced in 9.1 as shown below:

When a Dynamic/Static group is selected as a 'TL Approval Group' - it will be used only as a group of potential approvers and not for row security. Row Level security cannot be attached to these groups and they can be used in the AWE Definition Ids for Time and Labor to specify the user list that approves time.

Peoplesoft 9.1 - Time and Labor: Changes to TRC Setup

The setup component in Time and Labor that has changed significantly in 9.1 (more in terms of layout and additional features and less of fundamental data structure changes to the TL_TRC_TBL) is the TRC Setup page. Considering that the TRC is the fundamental building block of the module, it is imperative for us to understand the details of the change. The salient enhancements to the TRC Setup is as below:

  • A completely new page called 'Approvals and Comments' has added to the component. This is in conjunction with the introduction of the Approval Workflow Engine (AWE) framework in Time and Labor. The introduction of AWE is a major change and changes the way approvals are handled in Time and Labor. Earlier, the field called 'Approval Option' (where one could choose 'Reported Time', 'Payable Time' or 'None') controlled the way in which the TRC had to be approved. But this field has been taken off the page in 9.1 and a completely new page has been provided to fill in the AWE details for each TRC. This setup is similar to the familiar 'Country Take' setup in Absence Management, where we attach an Approval Process Id to each Absence Take that is exposed in Self Service.
  • The fields that were present in the page called 'TRC2' in prior versions have been moved over to the first page of the definition. These fields include Effect on Comp/Leave, Hours Represent, Interface options etc.
  • The tabs have been renamed to Definition and Approval and Comments from TRC1 and TRC2.
The new pages are shown below:


The major change as seen above is the introduction of the approval framewok settings. The adoption of AWE in Time and Labor has far fetched consequences, especially when one considers security and integration with Absence Management (it will now be imperative to synch up T&L security, Approval framework and Absence Approval framework). I am studying the technical architecture of the approval framework in T&L and will post about it in detail soon.

Peoplesoft 9.1 - Time and Labor: Changes to TRC Category Setup

TRC Category was introduced by Oracle in version 8.9 and I had written about it in a previous post. Oracle has given a face lift to this definition in 9.1 adding two new fields in the TRC Category definition - Reported Time Approval Display and Payable Time Approval Display. The new page is shown below:

A maximum of three categories can be chosen to be displayed in the reported time and payable time approval pages. We can classify this change more as a cosmetic one impacting the layout of the time approval pages and the structure of the TRC Category changes - all in all, goes one small step in improving the usability of the application.

Peoplesoft 9.1 - Time and Labor: Viewing Unprocessed Time

Oracle has strategically combined Time and Labor, Absence Management, NA Payroll and Payroll Interface into a category called 'Time and Pay' modules (interesting that Global Payroll has been maintained outside this classification!). One of the thrust areas of 9.0 as well as 9.1 has been about improving the integration and audit around these modules. Oracle took a visionary step in integrating absence self service into Time and Labor in 9.0, which I reckon is singularly the most important and useful feature upgrade from a T&L perspective after the changes to the schedule definition architecture in 8.9. For a shop that runs Absence Management, Time and Labor and Payroll for North America, the biggest challenge is to reconcile the interconnected data between the three modules - I've seen major glitches in the integration between Absence Management and Time and Labor in 8.9, which invariably leads to data inconsistency between the three modules and worse, has a negative impact in terms on Payroll compliance. While I expect much of the loose ends to be tied with the enhanced integration of Absence Management and T&L in 9.0 and above, it was really heartening to note that Oracle has provided two new pages to review unprocessed reported and payable time in 9.1. I am sure my folks in the payroll team would love that - to see which all rows did not get over to Payroll from Payable time and to see the rows in reported time not processed by Time Admin (it's not great drooling into PS Query reports all the time!).

The new pages can be found under the following navigation:
Time and Labor --> View Time --> View Unprocessed Time.

The page to view unprocessed payable time is given below:

On the flip side - I am slightly let down by the fact that Oracle did not come out with more delivered reports in 9.1 - we are still stuck with the same old Time Card, Scheduled Hours, Payable Status and TCD Usage reports. Any area related to Payroll requires extensive reporting and auditing and it would be great for Oracle to come out with something like a Payroll dashboard that would integrate data between AM, T&L and NA Payroll. The job to audit the data consistency between these three modules is not straight forward and easy, while it is critical for business to get the numbers spot on - wouldn't it be wonderful to have an off the shelf suite to audit the entire payroll data and certify that what is being paid out is the correct amount? I would love to see that!

Peoplesoft 9.1 - Time and Labor Rule Utilities

When I wrote my wish list for Time and Labor in April, one of the features I really wanted to see was a utility in T&L to migrate rules across environments, as currently it was a cumbersome process of writing manual export and import DMS scripts and manually recompiling all the rules in the target environment. I am really happy to note that Oracle has included that utility in the 9.1 release (surprising though that they never highlighted that feature in any of the pre-release announcements or transfer of information sessions). So what is new in 9.1 related to rule migration?

Oracle has introduced three new pages for exporting, importing and recompiling rules. This is pretty similar to the Rule Packager utility in Global Payroll and can be found under the familiar navigation of:
Setup HRMS --> System Administration --> Utilities --> Build Time and Labor rules.

The utility uses an Application Engine program to create DMS scripts in the location mentioned on the page. The utility used for exporting rules is shown below:

The page used for recompiling rules is shown below:

My thumps up to Oracle for providing these very needed utilities in 9.1 - this is something the developer community was longing for a long time. In hindsight, projects that are not yet on 9.1, should be looking at building a custom process mimicking the ones mentioned above to automate the process of T&L rule migration, may be I will write on this in detail sometime later.

Sunday, October 18, 2009

The deceiving case of the 'Exceptional Scenario'

Two weeks back I had to drop my dad in the railway station for a night train. This particular railway station was one where very few trains had a stop and thus it's location was not familiar to many people.The first time I had to go to that place, I looked up Google Maps and it presented me with three alternate routes. Two routes were through small and confusing roads and were shorter than the third route where I had to take the national highway and then turn to local street roads which were much clearer than the first two options. I picked the third option, irrespective of it being longer, because there was an element of 'risk' involved in trying out small and confusing roads especially when you have a train to catch! The story goes on that I had to go to the station thrice (before this incident of dropping my dad) and all the time I took the national highway route, the route was perfect and hassle free except for a small stretch of road, about 200 meters which connected the main road to the station. That final stretch was through a slum like area, the road was just wide enough for a car and bike to go and I was confused whether it was a public road at all as the residents seemed to treat the road as an extension of their houses. The place had a unique co-existence of , Hindu, Christian and Islamic places of worship next to each other and every time I drove through that lane, the people used to have some celebration or the other (I appreciated the element of religious harmony, but not the prospect of having to drive through that narrow piece of road with people dancing around!). One day while I dropped my parents and was going back, I was forced to take another route (one of the first two options given by Google Maps) and found that even though I had to go through dingy roads, it was much shorter than the highway route as prescribed by Google Maps, but I could not exactly correlate the roads and the turns I had to take to reach the station as it was night time.
Now let's pan back to me dropping my dad! The train was at 9:30 PM and the drive takes around 45 minutes. We started from my home at 8:15 PM and I was juggling the two routes I could take - through the highway or through the shorter new route which I found the last time I came back after dropping my folks. The highway route was longer and I had to go through that last 200 meter stretch where I had the 'risk' of the entire road being blocked because of some celebration or the other. But nothing of that sort had happened anytime when I went there before - where the entire road was blocked and I could not reach the station, yes there was an outside possibility that it could happen, but that 'scenario' had never happened and was very 'rare'. The alternate route on the other hand was indeed shorter, but much more 'uncertain' and 'complex' than the highway route - there was a pretty high probability that I could take a wrong turn and lose my way and eventually my dad could end up missing the train, but that road did not present me with any possibility of being blocked - which mean that if I was able to find the correct way and take the correct turns, I had an almost 100% probability of reaching the station.
Which option should have I taken and why?
Well, this is not a suspense thriller (though it came close to it, my dad did not have to turn superman and chase the train to board it!!), so no spoilers in here - I took the 'tried and tested' highway route, had a smooth ride on the highway and reached the last 200 meter stretch in almost 30 minutes, I turned into the small road, drove for around 50 meters and tragedy struck! The entire road ahead was blocked by an idol of Virgin Mary and people were dancing and singing around it!! The station was just 150 meters ahead and I could not let my dad walk in the night through that area, there was only one option left - take a U turn and find a new way!! Finding a new way at the eleventh hour was a risk I did not want to take, so I had to do the most deplorable act of asking my dad to take a rickshaw and go to the station. The story had a not-so-tragic ending with my dad reaching the station before the train arrived and I kissed him goodbye (I followed the rick to reach the station! Shame on me!!).
That incident made me reflect on some of the design decisions and risk evaluations that we make in our projects. This is a very typical scenario - two alternate solutions are proposed for a problem. Solution 'A' is pretty straightforward using proven technology and easy to implement, the developers vouch that it will work for nine out of ten cases and the one case where it will fail being a very rare one. Solution 'B' on the other hand is a more complex and difficult one (but not impossible), using technology and methods which are not commonly used, but it will work for all scenarios. I have seen that majority of the time, Solution 'A' will be chosen and signed off by even the business team, vouching that they would handle that one exceptional scenario manually. The usual justifactions for this decision is the time saved by going for 'A' and the opinion that the incremental advantage accrued by fixing 1% of the exceptional cases cannot be justified by the increased effort and cost involved in developing solution 'B'.
I am convinced that the risk of the 'rare case' or 'exceptional condition' can be under-evaluated during the design phase and could strangle the entire operations if it does happen sometime (statistically it will!!). Even if it does not disrupt the operations, it could turn out to be an administrative menace over the long run for business users. This is something I have learnt during my support experience in TESCO, that seemingly trivial issues ignored during the development phase becomes a headache once the system goes live and once the organisation grows and the number of transactions increase. At this stage, extra effort will have to be spent in finding a solution to the problem, which in turn increases the cost of ownership of the product (the IT Support/ Maintenance team on the other hand would be happy, because it represents an opportunity to 'improve the system' and they can flaunt the 'savings' accrued by the fix in their quarterly metrics! It really doesn't speak volumes when you fix a defect downstream, though it is a great help for the business plugging holes in the system, it is highly undesirable and should be ideally captured during the development phase).
The argument that the cost involved in solving 1% of the issues is not justified stands true only when cost is calculated over the development phase. But if one considers 'Total Cost of Ownership (TCO)', which would be the cost of developing as well as supporting the system over it's entire life time, it can very easily be understood that the extra cost and effort incurred during the development phase to develop a fool-proof solution will lead to lesser maintenance issues and thus lesser TCO. (Considering the fact that typical development phases extend somewhere between 8 months to 2 years, while support phases will run into years and decades).
So in conclusion, I would like to state that when presented with alternate solutions, the choice should always go to the fool-proof one, even if it will take more effort and cost - because the money saved in the development phase by going for a quicker fix that works 99% of the time, will be lost during the support phase handling 1% of the exceptions over the course of the system.
P.S.: This issue and many others bring to the fore, the need for 'System Thinking'. I think that it is one of the most powerful management thoughts ever, but unfortunately is more linked to the way you look at a problem and is not a 'tool set' that can be easily implemented, which should explain why this great idea finds very little practical application. The fact is that IT projects are driven in such a manner that development and support are done by completely different teams and in many cases by different organisations - this takes away the accountability of developers and the organisation that carries out the development on the issues that prop up during the support phase and most developers have only done development and have never been in support, which cripples their ability to foresee possible issues and their impact. The way I see it, it is the customer that loses out it in the end - the IT teams, let it be development or support comes out clean in the whole act - once the product has been implemented the development team moves on to other engagements, while the support team gets more work fixing the issues left over unattended during development - it is the customer that gets caught in between. The world is not perfect nor ideal and from an ethical point of view, these are all not deliberate decisions that people make (in most cases) ('they signed off the design, so don't blame me!!', 'we had brought up that risk during the design phase, but even the business agreed to it', 'it is important that we meet the deadline, so considering the fact that the solution meets 99% of the cases, we should go with it' etc) - but can't we take small steps and decisions to improve the probability of perfection, not of the world, but atleast of the IT systems that we design?

Friday, October 2, 2009

Can you implement T&L without Absence Management?

I found this very interesting question in ITToolBox yesterday and thought would 'type in' (I would have scribbled or jotted few years back!!) my opinion on this. Let me restate the exact question as it appeared in the forum:

"We are trying to do an analysis of using Absence Manager without Time and Labor. We already have a customized module that feeds online timecards to payroll in peoplesoft. First, is this possible? If so, what functionality would we be missing out on? Everything I have read talks about them together. Is AM just the workflow piece?"

Though the user did not mention their actual requirement, the obvious answer is yes - traditionally over the years T&L has been implemented as a different module from Absence Management. May be the understanding of what these two modules do might help the user take an informed choice.
Absence Management is a module that is specifically designed to manage leaves - that's all it does. It is a very elegant and powerful module that can handle all types of leave accruals, carry forwards, prorations, workflows for approvals etc. It is interesting to note that before AM gained the popularity that it enjoys today, organisations have used Benefits, Monitor Absence in Workforce Admin and even T&L to manage leaves. Absence Management is an independent module and can be implemented standalone with core HRMS.
Time and Labor on the other hand is a much more diverse module that can handle time reporting of employee, track compensatory time offs, enable task based time reporting, calculate overtime and shift premiums etc. If there are no complex rules, T&L can also double up as the module handling leaves of your organisation.

The interdependence of the two modules has to be a thing of version 9.0. With version 9.0, T&L and AM have been coupled very tightly, where entire absence self service has almost been integrated in the timesheet. This could be the reason why it looks as if they cannot be implemented separately.
But the fact remains that both of them are independent modules and the choice of which module to use should completely depend on the depth and nature of your organisation's requirement.

Saturday, September 12, 2009

Support.Oracle.Com is here

How I welcome the new site for product support from Oracle! https://support.oracle.com is a very very welcome change from the clumsy erstwhile metalink website. For users who were comfortable with the Peoplesoft's customer connection site, navigating and raising a Service Request in Oracle's Metalink site was a night mare, it was terribly slow and required tons of clicks to get somewhere, add to add, finding the required information and product was incredibly difficult on Metalink.
But the new site for product support is an excellent one by my initial experience. Oracle has really gone in for flashy Web 2.0 features and Ajax and DHTML are heavily sprinkled all over the site. User friendly pagelets, saner page layouts, DHTML driven menu bars and an extremely improved process to raise Service Requests gives a rich experience to the users. I am particularly impressed with the redesigned SR creation process. A new window which summarises the information and points out the fields that a user has missed to fill are extremely handy. The image below shows the new Ajax enabled Oracle support site with a refreshing layout and design, note the small bar on the left side of the page alerting the users of the status of the IR. All in all, a neat job from Oracle!

Peoplesoft 9.1 - Release Value Proposition for Time and Labor

Oracle's coming out with some welcome enhancements to Time and Labor in the 9.1 release. The release value proposition (RVP) document which highlights the major feature additions to the various modules talk about the following enhancements to T&L:

  • Integration with Oracle Workforce Scheduler: I had mentioned this in a previous post and it will be a major product enhancement and offering from Oracle. I am raring to see how this integration works in 9.1 and I am sure a number of customers would love to have this feature in their enterprise application suites for workforce management.
  • Adoption of Approval Workflow Engine for T&L approvals: A number of HRMS modules like Absence Management has been using AWE from version 8.9, while the T&L application development team stuck to the traditional row security and time and labor groups driven approval. It is good to bring T&L to the fold of modules using AWE as organisations will be able to maintain the approval rules under a single framework. It will be interesting to see though how customers who are using custom T&L Dynamic group driven approval routings will migrate to AWE - I see some retrofit work there.
  • Adoption of Delegation Framework in T&L: Oracle started offering the delegation framework in version 9.0 and it is a welcome move that even T&L has moved over to that fold.
I also read about a very interesting and welcome enhancement is a bi-party integration with Microsoft Outlook 2007 (they have not mentioned whether they will be supporting older versions of MS Outlook) - I had mulled about this possibility while piloting the one way integration with MS Outlook from Peoplesoft. It is exciting to note that in 9.1, the Outlook Calendar and events can be read from Peoplesoft - it's high time that enterprise products integrate with desktop applications and this is a move in the right direction.

Sunday, August 9, 2009

Mass Loading Time into Timesheet

Last week there was a question on ittoolbox on how we can mass upload time into Time and Labor. I had mentioned in an earlier post that one of the features I would love to have in T&L would be one to upload reported time through a spreadsheet, because it is quite a common requirement to have a feature to mass upload time (especially during conversion projects). Here are the options one can mull over if ever it's required to mass load time into time and labor:

1. If you want to quickly enter time from PIA for a group of employees, then use the 'Report Rapid Time' utility. This can be found under the navigation - Time and Labor --> Report Time --> Rapid Time. Ensure that you run the 'Submit All Rapid Time' process after the above step. The only disadvantage with the above process is that you still need to key in the time as it's done online/
2. If on the other hand you want to automate the process completely by using an AE, you could explore the following options:
(a) Write a CI on the Report Rapid Time component and load time directly into the rapid time table and then run the Submit Rapid Time process.
(b) Use a File Layout and insert data into the TL_ST_ELPTIME table if you are loading Elapsed Time or TL_ST_PCHTIME if you are loading Punch time and then run the TL_ST_LIB Application Engine. The TL_ST_LIB AE will insert the data into the reported time table.
(c) Or if you want to directly insert into the RPTD_TIME table, then use the CI 'TL_RPTD_TIME_CI'

Wednesday, August 5, 2009

Deciphering the skeleton of Peoplesoft HRMS modules

The more I get to work on various modules, the more I find similarities between their basic architecture. For example, sample the architectural similarity between Payroll for North America, Time and Labor and Absence Management.

NA Payroll T&L Absence Management
Paygroups Workgroups GP Paygroups
Earning Program TRC Program Element Group
Earnings/Deductions TRCs Absence Takes
Calendars NA Calendars
Pay Run ID NA Calendar Run ID
Company NA Pay Entity
NA T&L Rules Formulas

It has also been my experience that almost all modules in Peoplesoft HRMS is designed to link the smallest functional unit of that module to the employee. For example, the smallest unit in T&L in TRC and everything that we do in Time and Labor is designed to link the correct TRC and quantity to an employee. The same would be the case for Absence Management and NA Payroll where the smallest unit would be an Absence Take and Earnings/Deductions respectively. Once you have this fundamental understanding, everything else will automatically fall in place.
Another powerful analogy is to an inverted pyramid where the smallest unit of the module will be at the tip at the bottom and the employee on the flat base at the top. The architecture of most modules I know builds up like an inverted pyramid from the smallest unit to aggregate in size and finally attach itself to an employee.

A simple example from Time and Labor would be the case of how a TRC is linked to an employee. At the bottom of the pyramid there is the TRC, above that is the TRC Program, above that there is the workgroup and finally at the base there is the employee. You can seamlessly extend this analogy to other modules as well.

A quick list of Absence Management Tables

I was thinking of writing a short yet extremely useful post and the first thing that came to my mind is Application Tables....For a table driven product like peoplesoft, getting to know the main records and their function is a huge plus.
So here is a very quick list of important tables in Absence Management.

1. PS_GP_PIN - Table that stores the pin details of all elements in GP/AM. Use to retrieve the Pin Name or Pin Code corresponding to a pin number.

2. PS_GP_ABS_EVENT - Major transaction table in Absence Management. All absences reported get stored in this table. The data in this table the raw reported absence. This table is analogous to the TL_RPTD_TIME table in Time and Labor.

3. PS_GP_RSLT_ABS - Table containing the calculated result of reported absence takes. The source for this table is GP_ABS_EVENT. This table is analogous to the TL_PAYABLE_TIME table. This table will contain one row for each day of the absence event. For example, an employee might report sick leave from the 1st to 5th of a month. GP_ABS_EVENT will have a single entry for the above event with a start date and end date. But, GP_RSLT_ABS will have five rows with each row containing the number of hours or days of absence.

4. PS_GP_RSLT_ACUM - Table containing the results of the accumulators in GP/AM. Accumulators can be used to track absence balances, cumulative takes, cumulative paid hours etc. This table is populated only after the absence calculation COBOL is run.

5. PS_GP_CAL_RUN - Table containing details regarding the Calendar Group ID. The Calendar Group ID is the major run control parameter used for running the absence processes and is a collection of various calendars.

6. PS_GP_PI_GEN_DATA - Any absence event that need to be sent to payroll will require a positive input (in terms of an Earning Code) to be defined. The positive input can be attached to a take in the Absence Take setup page. This table contains the corresponding positive input for the processed absences.

7. PS_GP_ABS_SS_STA - This table is coupled with the GP_ABS_EVENT table and is a workflow table. This table can be used to know the workflow status of an absence event. A separate entry is created for each workflow actions like Submit, Approval etc. This table can be very useful to track details regarding the approval/workflow status of an absence event.

Sunday, August 2, 2009

A short T&L quiz for the starters!

We recently started off an initiative that would fall under the category of 'Knowledge Management' (well using these terms are such a fad, but it helps to brand the initiative. Put simply it's just a quiz!!) to publish short quizzes/trivias on Peoplesoft every Thursday. I think it does it's small bit in building knowledge within the team - as it's short and simple, generates interest amongst the people, it's attached to a small rewards and recognition scheme and most importantly makes people go back to the basics of the things they work with every day.

So here is a small Time and Labor quiz that I prepared, it would rate 'easy' of the difficulty scale and we had a couple of people getting all the answers right. Why don't you give it a shot?



Field that identifies whether time has come from TCD or Absence Management

What happens in Time and Labor when an employee goes on Maternity Leave?

Where can you configure the fields that appear on a timesheet?

Name the view that gives schedule details for each day

Name the table that contains the mapping of schedules and employees

Can you save a workgroup without specifying a TRC Program and Rule Program?

Field that can be used to identify whether payable time has been approved or not

After successful completion of time admin, the earliest change date of an employee is reset to which date? (Assuming there are no exceptions)

Which table contains the earliest change date field?

Monday, July 27, 2009

Goldplating vs. Great Customer Experience

Steve McConnell's classic mistakes in Software Project Management defines Developer Goldplating as following:
"Developers are fascinated by new technology and are sometimes anxious to try out new features of their language or environment or to create their own implementation of a slick feature they saw in another product--whether or not it's required in their product. The effort required to design, implement, test, document, and support features that are not required lengthens the schedule."
I find the entire concept of developer goldplating and it's classification as a 'classic mistake' extremely amusing. This is because the unfounded fear of goldplating restricts the creativity and realms of a developer and nips the chances of delving out outstanding customer service. Almost all the projects that I have worked on has had the customer coming back with his/her own 'wishlist' at the very last moment and traditional software engineering theory would say that it has to be treated as requirement creep and will have to be carried out as separate change requests in the future. Well that might sound judicious from the point of view of project management, but not from the view of customer experience management. Any project dedicated to providing a great customer experience should try to address even the last minute requests from the client - this is no goldplating and it will be something the customer will rave about. I know that may be nine out of ten people will not agree on this - but for someone who values a happy customer above anything else, requirement creep management can never come in the way of customer satisfaction!!

Auto-Displaying Search page results

What do you do if the customer comes up to you and says that they find clicking the 'Search' button on the search page cumbersome and would 'love' to have the search results defaulted when the search page loads.
This is where the SetSearchDialogBehaviour function will come to your rescue. Assign wildcard values to your search fields and call the SetSearchDialog behaviour function. This function is normally used in the context of skipping the search page. Theoretically, if a complete value is provided for all search keys before calling the SetSearchDialog behaviour function, then the search page will be skipped and the users will be taken directly into the transaction page. But the actual idea behind the function is that it will auto-trigger the search functionality. So naturally when just a wild card value is given to the search fields and the function invoked, then all the search results will be displayed. For example, if EMPLID is the only search field, the following code can be written to popualte the search results automatically on loading the search page.

/*Write this code in SearchInit.*/
REC.EMPLID.value = '%';

Thursday, July 16, 2009

Oracle MINUS function works bottom to top

Remember that the MINUS function in Oracle works bottom to top and the order in which statements are written will change the result set! So select x from a minus select y from b is not the same as select y from b minus select x from a....The bottom part of the query is executed first and a MINUS function will always give those rows present in the bottom part of the query and not in the top half.

Sunday, July 5, 2009

Follow this blog on Twitter!

Twitter has not gained the popularity of a Facebook or LinkedIn in this part of the world - and the first time I took notice of the new kid on the social networking space was when the U.S. State department endorsed the importance of Twitter as a medium of communication for Iranians during the recent turmoil following the presidential elections there. That made me sit up andtake notice, boy if the U.S. State department had to make an appeal to a privately held dot com newbie, then it's got to be a phenomenon.
So the story goes that this blog is now on Twitter and you can follow me and all the updates to this blog at http://twitter.com/peoplesofthrms.
As a sidenote, I believe that it's time enterprise applications embrace Web 2.o applications like Twitter, Facebook and LinkedIn. Oracle is introducing a lot of Web 2.0 technologies like Ajax in the next Peopletools release (8.50), but they need to have a rethink in terms of adopting some of these social networking applications in the core product functionality. I believe that with the widespread adoption and following of these applications, the line between social portals and enterprise portals is slowly fading away - one immediate thought that comes to me is the use of tweets as a supplementary to alerts in Peoplesoft - wouldn't it be 'cool' to get tweets from Peoplesoft when your manager approves your absence request or when he completes your performance document review?

Saturday, July 4, 2009

Best Peoplesoft Time and Labor blog?

This might sound vane, but according to the Google Search engines you are now reading the best blog on Peoplesoft Time and Labor!

While I know that's an insignificant indicator to be proud about, but I hope that my posts have been helpful to consultants seeking information on Peoplesoft HRMS and Time and Labor in particular. I started this blog in 2008 primarily to try my part in filling the lack of quality reference materials available for Peoplesoft functional modules and after being appalled at the prohibitive cost of the training courses available for the product. Do let me know your comments and suggestions to improve the content of this blog and keep those visits coming!!

Search Page security in Peoplesoft Time and Labor

I had detailed how Time and Labor security works in a previous post and today I want to touch upon how customers can configure the security on the Manager Self Service and Administrators for Time and Labor. Currently, there are three distinct ways of configuring search page security in Peoplesoft (including Time and Labor):
1. Use the highly robust and modified Core Row Level security, where any level of flexible security definition can be created with the introduction of Security Access Types in Peoplesoft HRMS 8.9.

2. Use the Direct Reports functionality which most of the MSS pages of Absence Management and e-Performance make use of. This feature will provide a standard page containing the direct reports of a user.

3. Use Time and Labor security to show to users employees in the Static and Dynamic groups that they have access to.

(You can also enforce search security through Search Init peoplecode, but this approach is highly discouraged.)

Most organisations will be designing their search security by making use of the new Row Level security design and in this context, a debatable question is whether we need to use Time and Labor security itself? Also, a number of people ask whether we can use Row Level security for the Time and Labor search pages, or whether it can make use of only Time and Labor security.

The answer is provided by the below decision chart (click on the picture to see a larger view):

All the Search pages in Time and Labor is flexible enough to utilize both Time and Labor security and core Row Level security. This decision depends on how the rowsecclass attached to a user is configured. If the rowsecclass is attached to any Time and Labor group (this can be found from the table PS_TL_GRP_SECURITY), then the user will be able to see only employees belonging to the groups the user's rowsecclass has access to. On the other hand, if there are no Time and Labor groups attached to the rowsecclass of the user, then core row level security available for the user will be used to fetch the employees.

What should be the optimum Time and Labor search security strategy?
If your organisation is using other Peoplesoft HRMS modules then it is best to utilise a common security design for all the modules. So I will always suggest using row level security for Time and Labor pages and not to configure any extra Time and Labor groups for the purpose of row level security.
Time and Labor groups can be created for the purpose of batch processing of employees. It is very useful to create Time and Labor groups and use the group as an input parameter for the Time Administration or Batch Approval or Batch Schedule Assignment processes. Note that a Time and Labor group can be defined without linking it to any row security class and used solely for the purpose of grouping employees together.

Thursday, June 25, 2009

Heard of the TL_XREF_TBL??

True to the Pareto Principle, 80% or more of the work in any Peoplesoft module can be done by knowing just 20% (or far lesser) of the tables in the system. But it's real exciting to explore and look at the rest of the infrequently used tables as most of them will have a very specific purpose which might come to light only under certain rare occassions. One such table is TL_XREF_TBL.

Where is it used?
The XREF table plays a critical part in the integration of Time and Labor and North American Payroll. When Payable Time is loaded from the TL_PAYABLE_TIME table to the paysheets in North American Payroll, the load program consolidates payable time entries before sending to NA Payroll. Consolidation means that the quantity for each TRC is summed up and a single entry with the aggregate quantity is sent to NA Payroll for each TRC under the period in question. During this process, the system assigns a cross reference number (XREF_NUM) to all the consolidated entries of a single TRC. The TL_XREF_TBL stores the XREF_NUM corresponding to each sequence number on the payable time table. The NA Payroll table PS_PAY_OTH_EARNS also contains a filed called XREF_NUM which can be used to find out the pay line entries corresponding to the spefic data that came from time and labor.

What is the practical utility of the table?
For most parts the XREF table is a background table which a developer may never need to use. But it will be very useful to audit the data flow between Time and Labor and N A Payroll. I have used this in scenarios where customers have reported issues with payable time data not coming over to payroll. In this case, the link between payable time entries and paysheet entries can be drawn to the minutest detail using the XREF_NUM.

Automatic Schedule assignment during hire

Schedule Assignment to an employee can be done at multiple levels in Peoplesoft. With the continuous improvement in scheduling that Peoplesoft/Oracle has brought into the product, they introduced a feature to directly assign schedules to employees. This feature is available under the Manage Schedule folder in MSS. A lot of clients require the process of schedule assignment to an employee to be automated at the time of hire and I have seen specific application engines being written for this purpose. That is an unnecessary effort as Peoplesoft already provides that feature with the WORKFORCE_SYNC message. The WORKFORCE_SYNC message will assign a row in the PS_SCH_ASSIGN table with the Use Default Schedule option ticked as Yes. The message itself is triggered on the Save Post Change event in the JOB DATA components. For tools versions higher than 8.47, it will be required to write specific handler logic for this to happen. This is because the assignment was handled by subscription peoplecode in versions lesser than 8.48 and this has become defunct with the architectural changes to the IB framework in 8.48. The steps to do that is:
1. Obtain the code of the schedule assignment from the subscription peoplecode assigned to the WORKFORCE_SYNC message.
2. Create a new Application Package and extent the OnNotify method of the PS_PT App Package.
3. Call the code for the assignment in the OnNotify method. The best practice will be to create a function in a custom FUNCLIB record containing the logic for the schedule assignment and calling just the function from the OnNotify method.
4. Add a new handler to the WORKFORCE_SYNC Service Operation with an OnNotify event defintion and give the application package and method details.
5. Ensure that the service operation is active and that the queue is running. Also generate Local routing if that has not yet been done.
6. You are good to go now!

Friday, April 24, 2009

What is the purpose of a TRC Category in Time and Labor?

Peoplesoft introduced a new definition in Time and Labor 8.9 called Time Reporting Category. A new field was introduced in the TRC Setup page, where a TRC could be mapped to a category. A TRC Category can be used to group functionally similar TRCs together. For example, if your organisation uses three different over time TRCs like OT1, OT2 and OT3 for different rates of overtime - you could create a TRC Category called Over Time and link the above three TRCs to this.
But where exactly is this new definition used?

The primary purpose of this new definition is to populate the newly introduced grid called 'Reported Hours Summary' in the Timesheet page. This grid gives the summary based on the TRC Category reported in the timesheet. This is specifically useful as a single snapshot view of the summary of various categories of time reported in a period. For example, suppose there are three TRC Categories like Regular, Overtime and Absence. In a week an employee has reported 4 hours of Regular time on three days, 2 hours of OT1 TRC on one day, 2 hours of OT2 TRC on one day, 8 hours of Sick Leave on one day and 3 hours of Bereavement Leave on one day. When this is summarised based on the TRC Categories - the viewer very clearly gets the picture that the employee reported a total of 12 hours of Regular Time, 4 hours of Over Time and 11 hours of Absence in the week.

Another potential use of the TRC Category definition is in reports to group TRCs together. Reports based on the TRCs reported, like overtime reports are very common in any implementation. In this case, it will be useful to create a single TRC Category called Over Time and link all the Over Time TRCs to that category and later use the category in the report rather than hard coding the TRC names.

You will also notice that this new definition has a mention in the Time and Labor installation page also, where we can control whether it should appear in the timesheet page. Even though its a very small functionality enhancement, I will advice any implementation that has more than 10 TRCs, to consider the use of well segmented, logical TRC Categories.

Wednesday, April 22, 2009

Peoplesoft Time and Labor: My wishlist - I

I believe that Peoplesoft has done an awesome job with the Time and Labor product - it's flexibility, features and strong integration capabilities make it a very strong product that caters very well to most business needs. Yet, there are some areas I wish Oracle could improve upon, some features that I sorely miss and so here is my wish list for a future release of Time and Labor.

> Integration with HRMS: It is critical that Time and Labor data is kept in sync with the HRMS data. Most implementations require the development of a process to do this, considering the inevitability of this process, it will be a relief for customers to have this as a delivered process.

> Restore Feature on Timesheet: There should be a feature to restore the timesheet to the last saved state. This will be a usability improvement and a much asked one by many customers I've worked with.

> Online Rules as Validation Messages: There is a delivered feature that allows the users to execute Time and Labor rules on the timesheet, but unfortunately this process just generates exceptions. It would be more meaningful and helpful if these online rules could be thrown up as a validation/error message. Most implementations require varying degree of online validations during time entry - it would be great to have those configured as T&L rules rather than through Peoplecode as T&L rules can be configured from the PIA and will not have to disturb any delivered component.

> Performance Improvement: Performance is a real issue with Time and Labor. Oracle needs to take a good look at both online performance (some of the delivered pagelets perform so poorly that they cannot be used at all!) as well as that of batch jobs like Time Administration.

> Mass Upload time through spreadsheet: It would be a nice to have feature to have a page from where users can directly upload reported time from a spreadsheet. Even though Time and Labor comes with the Rapid Time feature, there is nothing like working directly with an excel worksheet!

> More Delivered Reports: Customers would love to have more delivered reports with the product. Reports on employee schedules, reported time-payable time reconciliation, absence data-reported time reconciliation etc. would be a run away hit amongst the customers.

> T&L Rule Migration Engine: Modelled in a way similar to the Rule Packager utility in Absence Management, Time and Labor should also have a simple engine to aid in the migration of T&L Rules from one environment to another.

> Manager Dashboard: As Time and Labor data talks a lot about employee productivity and discipline, it would be wonderful to have a Manager Dashboard pagelet with analysis on Employee attendance information, Employee punctuality, Overtime and Productivity, Absenteeism etc.

> Delivered Localised Rules: Similar to the country extensions of GP that cater to the local country taxation and payroll rules, it would be a wonderful idea for Oracle to deliver Time and Labor with local rules of countries/cities. Some countries and cities have mandatory time and labor rules which every organisation will have to follow and there is nothing like getting that delivered with the product. The development, testing and maintenance time that this will save is immense for customers to readily lap up this feature.

If Oracle is reading, I am sure that some of these changes will go a long way in making Time and Labor a stronger product and the developer and user community would love to see some of these propping in to future releases.

Direct Reports UI and Peoplecode Transfer Function

The Transfer function is used in Peoplecode to move the users to a desired page within the same component or another component. A syntax for the function is as shown below(source Peoplebooks 8.48 - Peoplecode Language Reference) :


As shown above, the Menuname, Barname, Itemname and Page name of the target page has to be passed in this function. Rather than hard coding these values in the code, it will be a good practice if the above parameters are derived from a PIA page. Take a look at the page where we set up the Direct Reports Utility (Setup HRMS --> Common Definitions --> Direct Reports for Managers --> Direct Reports Setup).

The delivered Direct Reports Utility is designed to show the same employee selection page to the manager in various components and then is designed to redirect the manager to the appropriate page using the Transfer function. This functionality can be adopted for any custom requirement also. The parameters for the transfer function can be configured on this page and later retrieved Peoplecode by passing the component and market details (Use %Component and %Market as best practis). The only disadvantage I see is that if there are multiple transfer functions being used in the same custom component, then this is not a feasible solution. The elegance of the solution lies in the fact that nothing will have to be hardcoded in the transfer function code and is a completely maintainable and scalable solution.

Sunday, April 19, 2009

Which city has the maximum Peoplesoft projects?

I installed Google Analytics on this blog last month - it is really fascinating to see the amount of data the tool provides free of cost. The metrics provided include - number of visits per day, source of visitors, key words used to search for the site, language, browser, time spent by a user on the site, navigation pattern etc. Out of all the metrics, one that really interests me is one called 'map overlay' - this gives the geographical location of the visitors. Now, I've been thinking whether I could look at the data on the map overlay metrics to arrive at the places around the globe where maximum Peoplesoft activity (read projects) happens. Here is how the metrics look as of today -
The number of visits are directly proportional to the darkness of the green on the map. So undoubtedly the USA is the place to be as far as Peoplesoft is concerned - within the US, California sent in maximum visitors followed by New York. India, Canada and the UK follow suite. There was considerable activity from South American countries like Brazil, Chile and Columbia as well as from Asian countries like Vietnam, Philippines and Singapore. These countries represent a viable alternative to traditional outsourcing centres like India and the South American activity is especially significant in this regard. The geographical proximity to the US and the growing Hispanic population in US should make South American countries an attractive 'near-shoring' alternative. It will be really interesting if other Peoplesoft bloggers and especially the ivy league PS bloggers like Grey Sparling and Jim Marion could share the visitor profiles on their site as it would give a more realistic view of Peoplesoft activity from across the globe. But in the mean time, keep those clicks coming!

Time and Labor 9.1 - Integration with Oracle Workforce Scheduler

One of the biggest challenges that we face in designing a time and attendance system for a retail business is the ability to accurately forecast and capture the work schedule of employees. One aspect of the problem is arriving at the work schedule itself - how does a retail store manager decide upon the schedule of the employees? A number of factors go into this - the size of the store, the sales demand forecast for a period, the availability of the workforce, employees on planned absences, legal work rules etc. Given the multitude of parameters involved, drawing up the work schedules is a complex, difficult and practically inaccurate task for the store managers. The second aspect is about keeping the time and attendance system (Peoplesoft in our case) updated with the actual work schedule details. It has been our common experience that though manager verbally communicate the work schedule details to the store employees, a manual updation of the same in the system does not take place. The third dimension of the problem is the importance of having the correct schedule data in Peoplesoft. Modules like Absence Management and Time and Labor (and ultimately Payroll as these two modules feed data to the Payroll module/system) heavily relies on having the correct scheduling data for proper processing and calculations.
This is where a Workforce Scheduling system comes into play. A Workforce Scheduler is a system that will generate the schedules for your organisation based on various rules like sales forecast, demand, labour availability, time studies for tasks etc. From an enterprise perspective - there will be a bi-directional integration between a Workforce Scheduling System and the Time and Attendance system. The Time and Attendance (or HRMS) System will send the employee and absence details to the Scheduling System and the Scheduling System in turn will send the work schedule details back to the Time and Attendance System. We have been piloting the use of such a Workforce Scheduler in TESCO and work on it's integration with Peoplesoft Time and Labor is on.
With this background, it came as a pleasant surprise for me to read that Oracle is planning on delivering an integration between Peoplesoft Time and Labor and Oracle Workforce Scheduler in it's 9.1 release.

For a retail organisation, where work schedule of employees is dynamic and dependant on a number of external factors like sales demand - having a workforce scheduling system is of strategic importance. Having a system that will accurately generate schedules and keep the schedule data in sync with the Time and Attendance System will result in huge time and cost savings. In this context, this is a brilliant service offering from Oracle and will further bolster it's Peoplesoft strategy and customer base.

Thursday, March 26, 2009

Automating Release Management - the XMLP way

Most organisations have a process where a document containing all the objects of a Peoplesoft project that is to be migrated to another environment is prepared. This document will be the reference point for the DBAs or the release management team who will be responsible to migrate Peoplesoft projects across environments. For a developer, the task of preparing the document is an over head especially if it's a project with a large number of objects - it's close to being a painful exercise of going through all the objects in the project and reflecting that in the document. Amongst the innovative tools developed here at TESCO we had a .NET based Automatic Release Notes generator. The user just had to input the name of the project and the tool would produce a file (again we had the choice to select the output type required - .doc or .xls) containing the details of all the objects in the project including the comments attached to each individual object definition wherever possible. But this tool had few limitations - (a) The output format was not in the template dictated by our standards (b) The tool was not available locally, we had to login to a remote desktop to access that and (c) The source code was in .NET and that was not appealing to a bunch of Peoplpesoft developers!
So the team could not use the tool and had to resort to a manual preparation of the release notes. It was then that we came up with the idea of writing an XML Publisher report to do the same. XML Publisher is a perfect tool to produce letters and documents because we directly develop the template in MS Word and that was just perfect in our case. For the initial version, we decided to just print the details of the objects in the project and not the comments for each object. Printing the comments would mean that we had to query the metadata table of each of the objects and that would be nearly impossible with a simple PS Query.
The datasource of the XMLP report was a simple PS Query that pulled data from the PSPROJECTITEM table. We added some prompts to the PS Query so that the user could input details to be printed in the release notes like the change number, name of author, change description etc. It's a very simple template which I am sure your organisation can develop with minimal effort. This tool has surely automated a non-value add area in our development process and helped our developers spent more time of development rather than documentation!
A small presentation on the report and how to use that is given below:

Tuesday, March 24, 2009

My WebEx session on important Time and Labor tables

I've been wanting to upload audio visual sessions on Time and Labor on my blog for pretty long time now. This is because I strongly believe that there is a serious lack of authentic Peoplesoft training sources available and seeing and listening is any day better than plain reading! So here is the first audio cast I have come up with. It is a WebEx recording. I could not find a free tool to convert the recording to a format accepted by a video uploading site - so I had to upload the file in a free file hosting site.
The session is on my Top Six tables in Time and Labor. The session covers the structure and importance of each of the six tables and explains the context and place where they are used in Time and Labor. It should be an informative watch for any person working on Time and Labor. The audio cast is almost 60 minutes long.

You can download the session from this url - http://www.hotlinkfiles.com/browse/jijucvengal/257993

You will need a WebEx player to view the session. That can be downloaded here.
I sincerely hope that the session will be useful to you. Please leave your comments on the same here and also suggest any aspect on Peoplesoft Time and Labor that you might want the next session on.

Simulating Time Collection Devices using Peoplesoft Handler Tester Utility

What would you do if you had to send adhoc punches from a TCD to Peoplesoft for testing and you don't have access to a TCD or an immediate mechanism to send offline punches? Use the Peoplesoft Delivered Handler Tester utility. Handler Tester is a tool where you can feed the XML file and simulate the invocation of the handler of a Service Operation. It is an excellent utility while you are developing any service operation.
Any punch time interface that comes from a TCD calls the PUNCHED_TIME_ADD Service Operation. Extract the XML of the message (this can be easily taken from the Service Operation monitor) and make appropriate changes to the XML file (like adding the correct EMPLID, PUNCH_DTTM, PUNCH_TYPE etc.) and submit the XML to the tester using the 'Provide XML' button. To fire the handler - click on 'Execute Event'. If the handler was fired a text - 'Done' will be displayed below the Execute Event button. Check the Process Monitor to see whether there is an instance of the ST_LOADER AE. The handler calls the ST_LOADER AE that actually loads the punch time to the TL_RPTD_TIME table. I found it to be a very handy mechanism to simulate the action of TCDs.
A snapshot of the Handler Tester page is given below:

Thursday, March 19, 2009

Innovation is a matter of culture

Innovation is undoubtedly the most overused cliche ('value addition' would be a close competitor) in modern management jargon. Infinite media and printing space has been dedicated on this single subject and I am sure many companies and individuals have made millions selling that idea! But I believed that getting an entire organisation to innovate together was almost an impossibility until I joined TESCO.
Read the rest of the post here.

Leaving it to XSL to do the dirty work!

The biggest misconception I had about XML Publisher was it's ability to do any logical processing - for me it was merely a presentation layer when I initially saw the product and far inferior to anything SQRs could do. How wrong I was! And this post is about the power of using XSL to do the programming logic within the template of XML Publisher to design faster and better reports.

When we initially started designing XML Publisher reports we stuffed in most of the calculations and logic in the data source itself - majority being PS Queries. This meant we had pretty bulky and costly queries with a number of PS Query expressions. This was not helping the cause at all with the reports taking an unacceptably long time to run. But as we got to understand the tool, we started doing more and more of the calculations and logical processing within the template using xsl - the groupings, summations, summaries etc. This has lead to dramatic improvements in performance and we believe that's the right way to approach XMLP reporting.
For example in one of our reports we had to print the total scheduled hours for all employees in a department along with some other information. The xml schema consisted of individual employee's data along with the deptid. A normal for-each-group:row;DEPTID tag was used to print individual departments, but to retrieve the total scheduled hours for a department an expression was used in the PS Query. This lead to significant performance drop, the query itself was taking over 30 minutes to execute. By eliminating the expression and summing up the scheduled hours for the department in the template itself the report was running in less than 5 minutes. A further grouping was done inside the DEPTID grouping to sum the scheduled hours of each employee (note that there were multiple rows for a single employee in the data set, but grouping by emplid would result in a single distinct row) and the set_variable function was used to add the scheduled hours to a variable.

In short keep the data structure of your data source as flat as possible and leave all the dirty processing work to the XMLP template! (And keep learning more of XSL to leverage the capabilities fully).

Friday, March 13, 2009

Experiences in T&L-Absence Management Integration

It's always exciting to work on cross module assignments and this post details some of the learnings I've had piloting the T&L-AM integration for my present organisation.

The integration of T&L and AM can be again divided into two distinct categories - integration with NA Payroll as the pay system and integration with GP as the pay system. One of the biggest drawbacks we found with the delivered integration is that it doesn't work if you've decided to use PI (Payroll Interface) as your pay system. This was a problem as we were not using Global Payroll and an initial design decision was made to use Payroll Interface as the pay system of all employees in job data. We had to reverse this decision and forcefully use GP as the pay system in job to ensure that we could leverage the delivered integration between AM and T&L.

The purpose of integrating these two modules is two folds:
  • Bring AM data into T&L and display the same on the timesheet.
  • Feed the absence data to NA Payroll through Time and Labor.
With 9.0, absences can be reported in the timesheet itself and is a major enhancement, but this post is about versions lower than 9.0.
There are some limitations with the display of absences on the timesheet. Absences will not be loaded on the timesheet grid, so a user cannot see an absence TRC on the timesheet grid per se. Just a greyed out row with a reported status of approved and the number of hours of absence will appear on the timesheet. This can be pretty confusing and sometimes even useless for the users. Absences are shown in the grid called 'Reported Hours Summary' that is just below the timesheet. But if it's the punch timesheet page, only the time reporting category is displayed here. This is where the TRC Category which was introduced in 8.9 plays an important part. If one creates appropriate TRC Categories for each category of absence and link the category to an absence TRC, the data appearing on this grid can be useful for the users.
Another limitation with the integration of the two modules with NA Payroll as the pay system is that the integration program can be run only for finalised AM calendar groups. This is a significant limitation as the users will be able to see the absence data on the timesheet only after a calendar is finalised which happens at the end of a period. To overcome this limitation, we had to customise the process to enable it to run for any absence calendar that is open.
On the other hand, the integration with GP as the paygroup can happen after a manager approves an absence and need not even wait till the absence calculation is run. This means that the data that is seen in T&L can be inaccurate and incomplete.
We faced interesting challenges while designing the payroll interface (custom interface to local payrolls) in taking T&L and Absence data because of the above design. There was no way we could take the entire 'payable data' from time and labor though it contained the absence data too. This was because the absence data in time and labor was inaccurate and incomplete. This necessitated the design of two interface programs - one to take T&L data from the TL_PAYABLE_TIME table and another to take AM data from GP_PI_GEN_DATA table.
Also note that any system that has the integration of these two modules will have to closely tie any setup change to one module with the other. For example, any change to the absence code setup or the creation of a new absence code etc. will require similar actions on the T&L TRC end too.

Calendars related to T&L that needs periodic update

This was a question posed today in the popular forum ittoolbox. The question was about all the calendars related to T&L that required periodic update. This should be on the checklist of every implementation to have a yearly check to ensure that the following are up to date:

1. T&L Time Period Calendars which can be found in Setup HRMS --> Product Related --> Time and Labor --> Periods. Time Period Calendars are critical for the Time Administration process to find the Period of Interest (POI) and for T&L rules. These calendars will have to be built on a periodic basis.

2. Holiday Schedule has to be updated every year to reflect the statutory holidays of a year. The Holiday Schedule is a common HRMS definition shared across various modules like AM, GP, NA Payroll, T&L etc.

3. Time and Labor dates table which can be found in the second tab of the Time and Labor installation page will also have to be built periodically.

4. Time Zone Offset table found in Peopletools, International folder also has to be updated as necessary. The Time Zone offset table is used in a number of T&L processes and they will not run unless and until the table is populated by using the 'Generate Query Offset' feature.

Wednesday, March 11, 2009

The case of the missing exceptions

Peoplebooks say that if an exception is defined with the 'archive' checkbox ticked, then once an exception is resolved, it will remain in the TL_EXCEPTION table. But does that happen always? The answer is no. If you make any manual change that nullfies the criteria of an exception, time administration will clear the same from the table.

Tuesday, March 10, 2009

Time and Labor and Day Light Savings

This was a very interesting piece of change for me - to understand how T&L handles the reported and payable time of time reporters working across the time when day light saving starts or ends. I was surprised to find that despite being on the latest tools version, T&L was not completely error proof handling DST changes. We have a large number of employees working in PST time zone and PST moved ahead by one hour (from GMT -8 to GMT -7) at 2:00 AM on 8th March, so what happens to employees who started work before 2:00 AM and ended work after 2:00 AM? For example sample the following punch timings:

In: 7th March 10:00:00 PM
Break: 8th March 1:00:00 AM
In: 8th March 1:30:00 AM
Out: 8th March 3:30:00 AM

Though it appears from the punch timings that the employee has worked for 5 hours and 30 mins, the actual hours worked is only 4.5 as at 2:00 AM the clocks gained an hour and became 3:00 AM. There are many implications of this scenario which every organisation that is using T&L has to be aware of:

1. A high severity exception (TLX10076) will be generated if any punches are done during the DST change window - in the case above, the window would be between 2:00:00 AM and 2:59:59 AM. Though this is very unlikely if your organisation uses TCDs - this can happen if any manual change is made to the timesheet.

2. For employees who are working across the DST change window - the difference between the last Out and the first In will not add up to the punch total on the timesheet. In the example above, the punch total on the timesheet will come as 4.5 hours. This is Peoplesoft doing the right timezone calculation and should not be taken as an issue.
This caused considerable confusion among the users where they even manually adjusted the timesheets in an effort to bring the reported time difference equal to the punch time total.

3. Payable time will be affected by this change. When the clocks move ahead by an hour - the payable time generated will be one hour lesser than the actual worked time and when the clocks move back, the payable time generated will be one hour more than the actual reported time (yeah Peoplesoft overpays your emplyees!). On 8th March, we found that all employees who worked during the DST change were paid one hour less.

So how do you prepare for the DST change?
  • Its advisable to have audit queries prepared before hand to identify all employees who worked across the DST change. Track the reported and payable time of these employees to ensure that there are no discrepancies.
  • Have a communication plan in place telling the users to not edit the timesheet on a DST change date/time if possible. Also let the users know that a high severity exception might be generated which is due to the DST change. There is nothing much an employee or a manager can do to resolve that exception so it's better to get the HR support team to do it.
  • Payable time will be affected by this change. This means that there are chances of overpaying or underpaying employees who worked across a DST change. I prefer to correct this in T&L itself before passing data to downstream systems. When the DST change happened on March 8th, we had employees who had payable time one hour less than the actual worked hours. This difference was offset by reporting 1 hour of Regular TRC on the elapsed timesheet.
In conclusion, I feel Time and Labor handles DST time changes pretty well. The most important thing is to monitor the system properly to identify the affected employees and ensure that their reported and payable time are as expected.

Thursday, February 19, 2009

Dynamic Views as Prompt Tables

Dynamic Views are an excellent object type provided by Peoplesoft - a pseudo view that can take any shape! They are excellent candidates where the SQL condition of a view changes dynamically according and are not even a database object.
But as many might already know, one has to be extremely careful while using dynamic views in prompts. There are two major limitations I have come across when you use a DV as a prompt:

1. If the SQL of the dynamic view contains joins and if the search fields are used in the join.
2. If the SQL of the dynamic view contains joins.

What causes the failure is that the component processor takes the search fields from the prompt and appends to the SQL statement like 'and = '. Now, unfortunately the processor does not do any aliasing which causes the common 'ambiguous column definition' SQL error.

To overcome this, either put the SQL in a view and query from the view or better, treat the SQL as an inline view and query from the inline view.
For example, if the initial SQL of the DV was something like:
SELECT EMPLID FROM PSOPRDEFN UNION SELECT EMPLID FROM PSOPRDEFN, for this to work in a dynamic view, it has to be called from an inline view like: