PeopleSoft Benefit Rate Type Table

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’.