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!