BEN_PGM_PLN_VW

(SQL View)
 Index Back

Benefit Program Definition Tbl

BEN_DEFN_PGM is a record that contains information on all the Benefit Programs offered by a company. This table is used as a prompt table for many panels and accessed often by the batch processing of the Benefits Administration product.

SELECT A.BENEFIT_PROGRAM ,A.EFFDT ,E.PLAN_TYPE ,A.EFF_STATUS ,A.DESCR ,A.DESCRSHORT ,A.PROGRAM_TYPE ,A.FSA_RUN_ID ,A.FSA_MAX_ANNL_PLDG ,A.CURRENCY_CD ,A.DFLT_EXPIRATION_DD ,A.DFLT_CREDIT_RLLOVR ,A.COBRA_SURCHARGE ,A.COBRA_DISABL_SURCG ,A.FMLA_PLAN_ID ,A.SHOW_CREDIT ,A.COST_FREQUENCY ,A.HANDBOOK_URL_ID ,A.INCLD_CNSLSTX ,A.BAS_SHOW_ER_COSTS ,A.BAS_SHOW_TAX_IMPCT ,A.COBRA_CONTACT_ID ,F.DEPENDENT_MARRIAGE ,F.DEP_AGE_LIMIT ,F.EXCL_DISABLED_AGE ,F.STUDENT_AGE_LIMIT FROM PS_BEN_DEFN_PGM A , PS_BEN_DEFN_PLAN E , PS_DEP_RULE_TBL F WHERE A.EFF_STATUS = 'A' AND E.BENEFIT_PROGRAM = A.BENEFIT_PROGRAM AND E.EFFDT = A.EFFDT AND E.DEP_RULE_ID <> ' ' AND E.DEP_RULE_ID = F.DEP_RULE_ID AND F.EFFDT = ( SELECT MAX(F1.EFFDT) FROM PS_DEP_RULE_TBL F1 WHERE F1.DEP_RULE_ID = F.DEP_RULE_ID AND F1.EFFDT <= E.EFFDT) UNION SELECT A.BENEFIT_PROGRAM ,A.EFFDT ,E.PLAN_TYPE ,A.EFF_STATUS ,A.DESCR ,A.DESCRSHORT ,A.PROGRAM_TYPE ,A.FSA_RUN_ID ,A.FSA_MAX_ANNL_PLDG ,A.CURRENCY_CD ,A.DFLT_EXPIRATION_DD ,A.DFLT_CREDIT_RLLOVR ,A.COBRA_SURCHARGE ,A.COBRA_DISABL_SURCG ,A.FMLA_PLAN_ID ,A.SHOW_CREDIT ,A.COST_FREQUENCY ,A.HANDBOOK_URL_ID ,A.INCLD_CNSLSTX ,A.BAS_SHOW_ER_COSTS ,A.BAS_SHOW_TAX_IMPCT ,A.COBRA_CONTACT_ID ,'N' ,99 ,'Y' ,99 FROM PS_BEN_DEFN_PGM A , PS_BEN_DEFN_PLAN E WHERE A.EFF_STATUS = 'A' AND E.BENEFIT_PROGRAM = A.BENEFIT_PROGRAM AND E.EFFDT = A.EFFDT AND E.DEP_RULE_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 NOT NULL Effective Date

Default Value: %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.

Prompt Table: BN_PLAN_TYPE_VW

4 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive

Default Value: A

5 DESCR Character(30) VARCHAR2(30) NOT NULL Description
6 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
7 PROGRAM_TYPE Character(1) VARCHAR2(1) NOT NULL Program Type
A=Automated
M=Manual
8 FSA_RUN_ID Character(3) VARCHAR2(3) NOT NULL Flexible Spending Acct Run ID

Prompt Table: FSA_RUN_TBL

9 FSA_MAX_ANNL_PLDG Number(9,2) DECIMAL(8,2) NOT NULL FSA Maximum Annual Pledge
10 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

Default Value: INSTALLATION.EXCHNG_TO_CURRENCY

Prompt Table: CURRENCY_CD_TBL

11 DFLT_EXPIRATION_DD Number(3,0) SMALLINT NOT NULL Number of Days Before Defaults
12 DFLT_CREDIT_RLLOVR Character(1) VARCHAR2(1) NOT NULL Field to Apply Excess Credits to
C=Cash
D=FSA - Dependent Care
F=Forfeit Excess Credits
H=FSA - Health Care
R=FSA-Retirement Counseling
S=Savings - 401(k)

Default Value: C

13 COBRA_SURCHARGE Number(2,0) SMALLINT NOT NULL COBRA Surcharge Percent
14 COBRA_DISABL_SURCG Number(2,0) SMALLINT NOT NULL COBRA Disabled Surcharge Percent
15 FMLA_PLAN_ID Character(3) VARCHAR2(3) NOT NULL Family Medical Leave Plan ID

Prompt Table: FMLA_PLAN_TBL

16 SHOW_CREDIT Character(1) VARCHAR2(1) NOT NULL This field tells the system whether or not to show credits on the enrollment form.

Y/N Table Edit

Default Value: Y

17 COST_FREQUENCY Character(1) VARCHAR2(1) NOT NULL Cost Freq on Enrollment Form
A=Annual Frequency
D=Deduction Frequency

Default Value: A

18 HANDBOOK_URL_ID Character(30) VARCHAR2(30) NOT NULL This field holds the ID of a URL object. The URL object points to the companies enrollment handbook.

Prompt Table: PSURLDEFN

19 INCLD_CNSLSTX Character(1) VARCHAR2(1) NOT NULL Indicator that designates whether to Include the Canadian Sales Tax on the enrollment form or not.

Y/N Table Edit

Default Value: N

20 BAS_SHOW_ER_COSTS Character(1) VARCHAR2(1) NOT NULL This field tells the system whether or not to show the employer's contributions ("costs") on the enrollment form.

Y/N Table Edit

Default Value: N

21 BAS_SHOW_TAX_IMPCT Character(1) VARCHAR2(1) NOT NULL This field tells the system whether or not to alert the employee to any potential tax impacts (due to the employer's contributions) on the enrollment form. Most typically, this would be if there is a T-Tax (imputed Income) component.

Y/N Table Edit

Default Value: N

22 COBRA_CONTACT_ID Number(3,0) SMALLINT NOT NULL Used to identify a contact name/address for COBRA Administration purposes. This is a reference to an existing entry on the BEN_CONTACT_TBL.

Prompt Table: BEN_CONTACT_TBL

23 DEPENDENT_MARRIAGE Character(1) VARCHAR2(1) NOT NULL Dependent Ineligible if Married Flag

Y/N Table Edit

Default Value: N

24 DEP_AGE_LIMIT Number(2,0) SMALLINT NOT NULL Dependent Age Limit

Default Value: 99

25 EXCL_DISABLED_AGE Character(1) VARCHAR2(1) NOT NULL Exclude Disabled from Age Limit

Y/N Table Edit

Default Value: Y

26 STUDENT_AGE_LIMIT Number(2,0) SMALLINT NOT NULL Student Age Limit

Default Value: 99