Top PeopleSoft Jobs

PeopleSoft Benefit Rate Type Table

Saturday, February 20, 2010

Question:

We are on PeopleSoft version below 9.0 and we have seen that All benefit rate data like Flat Rate table, Age Graded table etc are consolidated into one table i.e Benefit Rate Type Table;

We use the PROV_COVRG_RATE field in order to establish the total amount that is paid to an insurance provider and back into the administration fees.

we used to use PROV_COVRG_RATE field under Flat-Rate table in 8.8 (also available in 8.9) , where or how do I use it in on HR 9.0 or in 9.1?

Answer:

All benefit rate data is consolidated into a single common structure. The individual Age-Graded, Flat, Salary-Percent and Service tables will be dropped, and all rate data migrated to the new Benefit Rate Tables. This refers to AGE_COVG_TBL, FLAT_RATE_TBL, SALARY_RATE_TBL and AGE_RATE_TBL are being replaced by BN_RATE_TBL and BN_RATE_DATA.

The new Ben Rate Table provides the flexibility for the user to define different rates including Provider Rates if they choose to do so. Below Screen shots would provide you some idea:

Let's take an example:

Employee Amt: 60.00 Employer Amt: 40.00 Provider Amt: 99.00
In this example, the total deduction amount (non-tax and before-tax) is not paid to the provider. The difference (in this example $1) is kept by the employer as an internal administration fee.
It is used in a custom report process that calculates vendor insurance premiums.”
Here’s the screen shot of Flat Rate Table (in PS version 8.8 or 8.9) showing the Provider Amount:


How It Can Work in 9.0 and above:

The different Rate Tables of 8.8 or 8.9 are lumped into 1 component in 9.0 and above, differentiating them by Benefit Rate Type in new Benefit Rate Table


In this case, need to define Flat Rate. Using same example from 8.8, here’s 9.0/9.1 counterpart.


what’s missing is the previous Provider Rate of 99.0. This can easily be defined in 9.0 and above by using same component with Benefit Type of Flat Rate as illustrated below with Employee Rate of 99.0:
To access the Employee Rate above that is defined as Provider Rate, use Record PS_BN_RATE_DATA field BN_EMPL_RATE.

To access the Employee and Employer Rate for ‘RT1’:

SELECT
,E.BN_EMPL_RATE
,E.BN_EMPLR_RATE
FROM PS_BN_RATE_DATA E
WHERE E.RATE_TBL_ID IN (’RT1’,’RT1P’)
AND (E.EFFDT=
(SELECT MAX(E1.EFFDT)
FROM PS_BN_RATE_DATA E1
WHERE E1.RATE_TBL_ID=E.RATE_TBL_ID
AND E1.EFFDT <= current date or whatever effective date required)
Then in your SQR or batch program, add logic to match up Employee/Employer Rates to the corresponding provider rate, e.g.:

IF RATE-TBL-ID = ‘RT1’
THEN TOTAL-DEDUCTION = BN-EMPL-RATE + BN-EMPLR-RT

IF RATE-TBL-ID = ‘RT1P’
THEN PROV-COVRG-RATE = BN-EMPL-RATE

CO-ADMIN-FEE = TOTAL-DEDUCTION - PROV-COVRG-RATE

Or if you have naming convention schema for RATE_TBL_ID’s between the Employee/Employer Rates and Provider Rates, e.g. 1st 3 characters of the RATE_TBL_ID match, then you can join or add it in the program instead of hard coding it in the program.
You can also use the Benefit Rate Type to identify Coverage Provider Rates, e.g.:
When defining the Coverage Provider Rate, use the new Provider Rate Type just created:




To extract Provider Rate Types from table and corresponding Provider Rate:

SELECT
,R.RATE_TYPE
,P.BN_EMPL_RATE
FROM PS_BN_RATE_DATA P
, PS_BN_RATE_TBL R
WHERE P.RATE_TBL_ID = R.RATE_TBL_ID
AND (P.EFFDT=
(SELECT MAX(P1.EFFDT)
FROM PS_BN_RATE_DATA P1
WHERE P1.RATE_TBL_ID=P.RATE_TBL_ID
AND P1.EFFDT <= current date or whatever effective date required)
AND P.EFFDT = R.EFFDT

In batch program, add the logic to match up the Provider Coverage Rate to its corresponding Employee/Employer Rates. In our example the Rate Table ID ‘RT1P’ Rate Type ‘RT1’ in Benefits Rate Table matches with Rate Table ID ‘RT1P’. Hence you can create a schema where the Provider’s Rate Type matches with the corresponding Rate Table ID of Employee/Employer Rate in Benefits Rate Table.

Another option is to have one Provider Rate Type that applies to multiple Employee/Employer Rates, e.g. Benefit Rate with Rate Type of ‘RT1’ contains the Provider Coverage Rate for Benefit Rate ID’s ‘RT1’, ‘RT2’ and ‘RT3’.

3 comments:

dsaracena May 18, 2010 at 4:59 AM  

Have anyone encountered issues with using the Benefit Rate Type of Benefit Plan and Coverage code for Domestic Partners? We cannot seem to get the calculation to work. Any insight is greatly appreciated.

Komal June 29, 2010 at 6:39 PM  

Yes we had issues with DP rates (for employees with coverage of empl+children and a DP coverage) where we had to offset the premium to employees using additional pay. But with 9.1 we have delivered cvrg codes 12,13 and 14 which will solve the issue. You will have to do a rate chart and calculation but that will definitely work.

Komal June 29, 2010 at 9:47 PM  

I am not convinced to use this oracle's workaround for configuring provider rates due to one main reason. I am sure that by maintaining two rate ids for a single flat rate id in 8.8, 1 for capturing empl, emplr rates and other for provider rate will have me update two rate ids when the rate changes. And at that time any of the config members may miss to update the providers rates. Has anyone considered customizing the rate page in 9.1 to accomodate provider rates? I still don't understand why Oracle came up with this dumb idea of droping prov rate field.

Post a Comment

About This Blog

Have you got a PeopleSoft Question? Post it in the forums section or in your preferred place or in comments section, so that we can answer it for you in detail with screen shots;

Learn PeopleSoft functionality and how to use the setups and process flow offered in different modules;

Hope this would give you some ideas...

Blog Archive

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP