11/10/11

Various Tables in Base Benefits module of PeopleSoft HCM

When I started working with Benefits module, as any beginner initially I didn't have any clue of how it works and what tables are behind it? Being a novice, I spent sometime understanding these aspects and figuring out the various navigation for user activities and the underlying tables. Based on my research(though not extensive) I am sharing my knowledge of basic Benefits tables covering Base Benefits, and eBenefits modules.

Some of these Benefit records have EMPL_RCD as a key field  and its existence should not be overseen too much. The details are provided below this table.

Record Name
Purpose of the Record
PLAN Types
EMPL _RCD
BENEFIT_PARTIC
BENEFIT_PARTIC stores non-historical data of employee participation in a benefit Plan Type. This table acts as an intermediate parent between PER_ORG_ASGN record and the effective dated Plan level enrollments by Benefits Enrollment pages.
Note: In case the data in this record goes out of synch and corrupt, it could be rebuilt using a delivered PeopleSoft DMS script - BNPARTIC.DMS
NA
Yes
BN_DEDN_SUMMARY
This record is useful in providing a snapshot of an employee's Benefit deductions at any point in time.
NA
No
HEALTH_BENEFIT
HEALTH_BENEFIT has information on all the participants enrollment for health related benefits.
 '1x' series such as Medical, Dental, and Vision.
Yes
HEALTH_DEPENDNT
HEALTH_DEPENDENT record has information on all the dependents covered under an employee's various Health plans.  It is a child record of HEALTH_BENEFIT record.
LIFE_ADD_BEN
LIFE_ADD_BEN record has information of various Life and Accidental death related benefits for an employee.
 '2X' series such as Basic Life, AD&D, Dependent Life, etc.
Yes
LIFE_ADD_BENEFC
LIFE_ADD_BENEFC record has information on the the beneficiaries associated with the employee's Life Insurance or Accidental Death plans, or dependents covered under an employee's Dependent Life Plans.
DISABILITY_BEN
DISABILITY_BEN has information related to the disability benefit elects for an employee.
 '3X' series such as STD and LTD.
Yes
FSA_BENEFIT
FSA_BENEFIT record is populated in the Base Benefits system using the enrollment panel, or by the automated Benefits Administration product.  It has information on participants enrolment in Flexible Spending Accounts such as Health or Dependent Care. The FSA Claims Administration product looks at this record to verify enrollment.
Need to investigate further
Yes
PENSION_PLAN
As the name says, the PENSION_PLAN table has information on all the Pension related benefits for an employee.
 '80' series.
Yes
RTRMNT_PLAN
As the name says, the RTRMNT_PLAN table has information on the Retirement benefits for an employee.
 '70' series.
Yes
LEAVE_PLAN
Employee's choice of Leave Plan(s) are stored in this record.
Need to investigate further
Yes
SAVINGS_BENEFIC
SAVINGS_BENEFIC record has information on the participants enrolment of a savings plan.
'4x' series, such as 401(k), 403(b), and ESPP plans
Yes
SAVINGS_PLAN
SAVINGS_PLAN record has information on the employees enrollment on savings related benefit plans.
SAVINGS_INVEST
SAVINGS_INVEST record has information on employee's investment option and percentage contribution within an enrolled savings plan. This is a child record for SAVINGS_PLAN. There can be multiple investment records per savings plan, although the total of all investment percentages must be 100%.
SIMPLE_BENEFIT
The information that all I could find from google is that, this record stores employee elections in SIMPLE benefit plans, but has no participation attributes beyond the status of the election.
Need to investigate further
No


Why EMPL_RCD field is present in many Base Benefits Tables?
As per PeopleSoft, the EMPL_RCD field displays as key field in benefit tables due to technical reasons. However, this is neither logically nor functionally accurate.


Should we join the EMPL_RCD field in Base Benefits record with PS_JOB?
No never do that. The EMPL_RCD data in JOB record of an employee is completely different from the EMPL_RCD field stored in Base Benefits tables.


Does EMPL_RCD field in Base Benefits module save the same value as in BENEFIT_RCD_NBR field?
Yes, It is so.
Across Base Benefits module, in the context of benefit enrollment, EMPL_RCD must only be considered as an alias for BENEFIT_RCD_NBR. All benefit enrollment-related records are logically keyed by BENEFIT_RCD_NBR, so here EMPL_RCD actually contains the value of the appropriate BENEFIT_RCD_NBR. 

How to join data in Base Benefits table with other tables?
According to PeopleSoft, it is quite critical that all joins, comparisons, and updates to Benefit related tables be against BENEFIT_RCD_NBR. In other words, the EMPL_RCD field in Benefits tables can be joined with BENEFIT_RCD_NBR in Benefits records.
So a typical driving join for:
  • Benefit Participation is: PS_PER_ORG_ASGN.BENEFIT_RCD_NBR = PS_BEN_PROG_PARTIC.EMPL_RCD
  • Benefit Enrollment is: PS_PER_ORG_ASGN.BENEFIT_RCD_NBR = PS_HEALTH_BENEFIT.EMPL_RCD
This mapping is performed internally by the security and search views in Base Benefits program. 

2 comments:

  1. http://peoplesoft-answers.blogspot.com/2011/07/peoplesoft-leave-accrual.html has some plan type related information

    ReplyDelete
  2. hi ,
    what is the benefits_data name in peoplesoft 9.2.
    please share the answer if anyone knows answer for this qtn .

    ReplyDelete

Please refrain for marketing messages and unnecessary back links.

Workflow errors on New Hire and Add employment instance

User tries to hire a new person or tries to add a new organization relationship encounters a series of error messages related to workflow...

Trending in this Blog this month