GPUS_BENPRG_VW (SQL View)	Index Back
Benefit Program Definition Contains Benefit Program information including plan option and cost data

GPUS_BENPRG_VW

(SQL View)

Benefit Program Definition

Contains Benefit Program information including plan option and cost data

SELECT BP.BENEFIT_PROGRAM , BP.EFFDT , OP.PLAN_TYPE , OP.BENEFIT_PLAN , OP.COVRG_CD , OP.OPTION_TYPE , OP.OPTION_ID , OP.DEDCD , CO.COST_TYPE , CO.RATE_TYPE , CO.RATE_TBL_ID , CO.CALC_RULES_ID FROM PS_BEN_DEFN_PGM BP , PS_BEN_DEFN_OPTN OP , PS_BEN_DEFN_COST CO WHERE OP.BENEFIT_PROGRAM = BP.BENEFIT_PROGRAM AND OP.EFFDT = ( SELECT MAX(OP2.EFFDT) FROM PS_BEN_DEFN_OPTN OP2 WHERE OP2.BENEFIT_PROGRAM = OP.BENEFIT_PROGRAM AND OP2.EFFDT <= BP.EFFDT) AND CO.BENEFIT_PROGRAM = OP.BENEFIT_PROGRAM AND CO.PLAN_TYPE = OP.PLAN_TYPE AND CO.OPTION_ID = OP.OPTION_ID AND CO.EFFDT = ( SELECT MAX(CO2.EFFDT) FROM PS_BEN_DEFN_COST CO2 WHERE CO2.BENEFIT_PROGRAM = CO.BENEFIT_PROGRAM AND CO2.PLAN_TYPE = CO.PLAN_TYPE AND CO2.OPTION_ID = CO.OPTION_ID AND CO2.EFFDT <= BP.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BENEFIT_PROGRAM Character(3) VARCHAR2(3) NOT NULL Defines a collection of benefit plans and their associated rate and calculation rules.

2 EFFDT Date(10) DATE Effective Date

3 PLAN_TYPE Character(2) VARCHAR2(2) NOT NULL Identifies a category of benefit plan, such as Medical, Dental, and Life (Insurance). A set of plan type codes, as well as rules for creating new codes, is provided by PeopleSoft. Plan Type codes determine how the system processes and defines eligibility for the various benefit plans.

4 BENEFIT_PLAN Character(6) VARCHAR2(6) NOT NULL Benefit Plan

5 COVRG_CD Character(2) VARCHAR2(2) NOT NULL Coverage Code

6 OPTION_TYPE Character(1) VARCHAR2(1) NOT NULL Option Type
D=General Deduction
G=General Credit
O=Option
P=Program
W=Waive Option

7 OPTION_ID Number(4,0) SMALLINT NOT NULL Option ID

8 DEDCD Character(6) VARCHAR2(6) NOT NULL Deduction Code

9 COST_TYPE Character(1) VARCHAR2(1) NOT NULL A flag that indicates how the employee-paid portion of a benefit plan is paid, either as Cost (C) or Price (P). 'Price' indicates that the benefit is paid from an employee payroll deduction. 'Credit' specifies that the system calculates additional earnings which are used as credit to pay for a benefit.
C=Credit
P=Price

10 RATE_TYPE Character(4) VARCHAR2(4) NOT NULL This is used to identify the type of benefit rate in use - the keys, usage, and high-level attributes the govern how a rate behaves.
Prompt Table: BN_RATE_TYPE

11 RATE_TBL_ID Character(6) VARCHAR2(6) NOT NULL This is used as the record key that identifies a specific set of benefit rates.

12 CALC_RULES_ID Character(4) VARCHAR2(4) NOT NULL Calculation Rules Table ID

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BENEFIT_PROGRAM	Character(3)	VARCHAR2(3) NOT NULL	Defines a collection of benefit plans and their associated rate and calculation rules.
2	EFFDT	Date(10)	DATE	Effective Date
3	PLAN_TYPE	Character(2)	VARCHAR2(2) NOT NULL	Identifies a category of benefit plan, such as Medical, Dental, and Life (Insurance). A set of plan type codes, as well as rules for creating new codes, is provided by PeopleSoft. Plan Type codes determine how the system processes and defines eligibility for the various benefit plans.
4	BENEFIT_PLAN	Character(6)	VARCHAR2(6) NOT NULL	Benefit Plan
5	COVRG_CD	Character(2)	VARCHAR2(2) NOT NULL	Coverage Code
6	OPTION_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Option Type D=General Deduction G=General Credit O=Option P=Program W=Waive Option
7	OPTION_ID	Number(4,0)	SMALLINT NOT NULL	Option ID
8	DEDCD	Character(6)	VARCHAR2(6) NOT NULL	Deduction Code
9	COST_TYPE	Character(1)	VARCHAR2(1) NOT NULL	A flag that indicates how the employee-paid portion of a benefit plan is paid, either as Cost (C) or Price (P). 'Price' indicates that the benefit is paid from an employee payroll deduction. 'Credit' specifies that the system calculates additional earnings which are used as credit to pay for a benefit. C=Credit P=Price
10	RATE_TYPE	Character(4)	VARCHAR2(4) NOT NULL	This is used to identify the type of benefit rate in use - the keys, usage, and high-level attributes the govern how a rate behaves. Prompt Table: BN_RATE_TYPE
11	RATE_TBL_ID	Character(6)	VARCHAR2(6) NOT NULL	This is used as the record key that identifies a specific set of benefit rates.
12	CALC_RULES_ID	Character(4)	VARCHAR2(4) NOT NULL	Calculation Rules Table ID