Peoplesoft HCM has tens of thousands of record definitions, representing tables, views, and other Peopletools objects. Nonetheless, we can find more ways to organize our data usefully for easy access in Query Manager or in database browsers like SQLPlus and TOAD. This is the first of an occasional series of posts regarding new ways to access our data. This week: PS_LEAVE_ACCRUAL.
The Table Itself
Leave accrual is the process by which employees accumulate the right to be absent from work, for a certain amount of time, and to be paid for it. In the United States, the most common forms of paid absence are sick leave (PLAN_TYPE = ’50’), vacation (PLAN_TYPE = ’51’), and personal leave (PLAN_TYPE = ’52’). Check PSXLATITEM for a list of all the PLAN_TYPE values. Each organization can create more two-character plan types starting with ‘5’, with multiple plans for each plan type.
We assign leave plans to employees in the PS_LEAVE_PLAN table, and the PSPACCRL program adds rows to the PS_LEAVE_ACCRUAL table. Each row shows the balance of hours the employee had at the beginning of the calendar year, the number of hours added YTD (year to date) according to the rules defined by the setup tables, and the number of hours redeemed YTD.
More Useful Fields
The Peoplesoft designers chose not to add one more field and one more calculation for the most interesting number pertaining to leave accrual, the net balance. That means we need to write that formula in any Peoplecode, SQL, or SQR statement that works with leave balances. Alternately, we centralize that formula in a SQL view.
Peoplesoft provides us with a dynamic view called FLMA_LV_ACCR_VW. It simplifies an employee’s accrual history in two ways. First, by disregarding the EMPL_RCD and COMPANY fields, which combines the employee’s accrual history across simultaneous jobs, or different roles, or different legal entities of the organization. Second, by calculating LEAVE_HRS_BALANCE with a formula that combines ten fields. Unfortunately, a dynamic view is not available in database browsers or Query Manager.
We can save FLMA_LV_ACCR_VW with another name (perhaps LEAVE_BAL_VW), change it to a SQL View, save it again, build it, and use it wherever we like. If we don’t allow multiple values for EMPL_RCD or COMPANY, we can remove sum(…) and group by from the SQL and improve its performance.
Some people using this view may not know that ’50’ means sick leave, ’51’ means vacation, and whatever other plan types mean in our organization. We can add a text field like DESCRSHORT to the view to present this select list item:
decode(PLAN_TYPE,'50','Sick Leave','51','Vacation','52','Personal','53','FMLA','Other')
Another possibility is to create differential values; rather than HRS_EARNED_YTD, we can join each row of PS_LEAVE_ACCRUAL with its predecessor to calculate HRS_EARNED.
select (A.HRS_EARNED_YTD – B.HRS_EARNED_YTD)
from PS_LEAVE_ACCRUAL A, PS_LEAVE_ACCRUAL B
where B.EMPLID = A.EMPLID
and B.EMPL_RCD = A.EMPL_RCD
and B.COMPANY = A.COMPANY
and B.PLAN_TYPE = A.PLAN_TYPE
and B.ACCRUAL_PROC_DT = (select max(ACCRUAL_PROC_DT)
from PS_LEAVE_ACCRUAL
where EMPLID = A.EMPLID
and EMPL_RCD = A.EMPL_RCD
and COMPANY = A.COMPANY
and PLAN_TYPE = A.PLAN_TYPE
and ACCRUAL_PROC_DT < A.ACCRUAL_PROC_DT)
Granted, this is a three way self-join of a table that may have an order of magnitude more rows than PS_JOB. However, the other way to see how much each employee accrued in each pay period may be even harder.
- Get the employee’s service hours.
- Get the employee’s current leave plan enrollment.
- Determine which threshhold applies to the employee.
- Get the employee’s accrual rate.
- Check for eligible service hours in the pay period (was the employee on leave during the pay period?).
- Multiply eligible service hours by accrual rate.
Consolidating Rows
The next step could be to report all the plan types together. We could start with the PS_LEAVE_BAL_VW that gives us EMPLID, PLAN_TYPE, ACCRUAL_PROC_DT, and BALANCE. The next level of higher consolidation (and lower performance) would be a PS_ALL_BAL_VW that gives use EMPLID, ACCRUAL_PROC_DT, SICK_BAL, and VAC_BAL. The SQL would join PS_LEAVE_BAL_VW with itself as follows.
select S.EMPLID, S.ACCRUAL_PROC_DT, S.LEAVE_HRS_BALANCE, V.LEAVE_HRS_BALANCE
from PS_LEAVE_BAL_VW S, PS_LEAVE_BAL_VW V
where S.EMPLID = V.EMPLID
and S.PLAN_TYPE = '50'
and S.ACCRUAL_PROC_DT = V.ACCRUAL_PROC_DT
and V.PLAN_TYPE = '51'
This is not fast way to retrieve the PS_LEAVE_ACCRUAL table, but it could be a useful view on a page showing the balances of a single employee as of a single date.
Recent Comments