BEN_H_PLAN_SRCH (SQL View)	Index Back
Benefit Plans BENEF_PLAN_TBL is a record that contains information on all the Benefit Plans offered by a company. There is one record for each plan type/provider combination (except for plantype '00'). This table is used as a prompt table for many panels and is accessed often by the batch processing of the Benefits Administration product.

BEN_H_PLAN_SRCH

(SQL View)

Benefit Plans

BENEF_PLAN_TBL is a record that contains information on all the Benefit Plans offered by a company. There is one record for each plan type/provider combination (except for plantype '00'). This table is used as a prompt table for many panels and is accessed often by the batch processing of the Benefits Administration product.

SELECT A.PLAN_TYPE ,A.BENEFIT_PLAN ,A.EFFDT ,A.DESCR ,A.DESCRSHORT ,A.SETID ,A.VENDOR_ID ,A.GROUP_NBR ,A.DEDCD ,A.INCLUDE_IN_NDT ,A.MIN_BENEFIC_ALLOC ,A.PAY_MODE ,A.AP_PAYMENT_DT_TYPE ,A.SEPARATE_AP_PAYMNT ,A.SMRYPLNDOC_URL_ID ,A.BN_834_PLN_CVG_DSC ,A.BN_834_FIELD_MAPPD ,A.DESCRLONG FROM PS_BENEF_PLAN_TBL A WHERE A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_BENEF_PLAN_TBL A_ED WHERE A.PLAN_TYPE = A_ED.PLAN_TYPE AND A.BENEFIT_PLAN = A_ED.BENEFIT_PLAN AND A_ED.EFFDT <= %CurrentDateIn)

Related Language Record: BENEF_PLAN_LANG

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

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

3 EFFDT Date(10) DATE NOT NULL Effective Date
Default Value: %date

4 DESCR Character(30) VARCHAR2(30) NOT NULL Description

5 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description

6 SETID Character(5) VARCHAR2(5) NOT NULL SetID
Prompt Table: SETID_TBL

7 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
Prompt Table: VENDOR_PROV_VW

8 GROUP_NBR Character(30) VARCHAR2(30) NOT NULL Group Number
Prompt Table: VENDOR_POLICY

9 DEDCD Character(6) VARCHAR2(6) NOT NULL Deduction Code
Prompt Table: DEDUCTION_TBL

10 INCLUDE_IN_NDT Character(1) VARCHAR2(1) NOT NULL Include in Nondiscrimination
Y/N Table Edit

11 MIN_BENEFIC_ALLOC Number(6,2) DECIMAL(5,2) NOT NULL This field is used to define the Minimum allocation a beneficiary ( usually the spouse ) must be assigned under a regulated benefit plan

12 PAY_MODE Character(1) VARCHAR2(1) NOT NULL Pay Mode
B=Pay when Bond Price met
C=Pay when Collection Completed
D=Pay as Deducted
S=Pay at Specified Date
Default Value: D
Prompt Table: PAY_MODE_VW

13 AP_PAYMENT_DT_TYPE Character(1) VARCHAR2(1) NOT NULL AP Payment Date Type
C=Check Date
P=Pay Period End Date
Default Value: C

14 SEPARATE_AP_PAYMNT Character(1) VARCHAR2(1) NOT NULL Separate AP Payment
Y/N Table Edit
Default Value: N

15 SMRYPLNDOC_URL_ID Character(30) VARCHAR2(30) NOT NULL This field defines a URL ID which can be used to access the Summary Plan Document on file for a Benefit Plan.
Prompt Table: PSURLDEFN

16 BN_834_PLN_CVG_DSC Character(50) VARCHAR2(50) NOT NULL Stores the Plan Coverage Description used on the HIPAA 834 EDI file. It is populated on the Benefits Plan table. This field is not mandatory.

17 BN_834_FIELD_MAPPD Character(20) VARCHAR2(20) NOT NULL Stores the mapped field value on the EDI 834 Mapping table.
Prompt Table: BN_834_MAP_VW

18 DESCRLONG Long Character CLOB Long Description

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	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.
2	BENEFIT_PLAN	Character(6)	VARCHAR2(6) NOT NULL	Benefit Plan
3	EFFDT	Date(10)	DATE NOT NULL	Effective Date Default Value: %date
4	DESCR	Character(30)	VARCHAR2(30) NOT NULL	Description
5	DESCRSHORT	Character(10)	VARCHAR2(10) NOT NULL	Short Description
6	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID Prompt Table: SETID_TBL
7	VENDOR_ID	Character(10)	VARCHAR2(10) NOT NULL	Vendor Identifier Prompt Table: VENDOR_PROV_VW
8	GROUP_NBR	Character(30)	VARCHAR2(30) NOT NULL	Group Number Prompt Table: VENDOR_POLICY
9	DEDCD	Character(6)	VARCHAR2(6) NOT NULL	Deduction Code Prompt Table: DEDUCTION_TBL
10	INCLUDE_IN_NDT	Character(1)	VARCHAR2(1) NOT NULL	Include in Nondiscrimination Y/N Table Edit
11	MIN_BENEFIC_ALLOC	Number(6,2)	DECIMAL(5,2) NOT NULL	This field is used to define the Minimum allocation a beneficiary ( usually the spouse ) must be assigned under a regulated benefit plan
12	PAY_MODE	Character(1)	VARCHAR2(1) NOT NULL	Pay Mode B=Pay when Bond Price met C=Pay when Collection Completed D=Pay as Deducted S=Pay at Specified Date Default Value: D Prompt Table: PAY_MODE_VW
13	AP_PAYMENT_DT_TYPE	Character(1)	VARCHAR2(1) NOT NULL	AP Payment Date Type C=Check Date P=Pay Period End Date Default Value: C
14	SEPARATE_AP_PAYMNT	Character(1)	VARCHAR2(1) NOT NULL	Separate AP Payment Y/N Table Edit Default Value: N
15	SMRYPLNDOC_URL_ID	Character(30)	VARCHAR2(30) NOT NULL	This field defines a URL ID which can be used to access the Summary Plan Document on file for a Benefit Plan. Prompt Table: PSURLDEFN
16	BN_834_PLN_CVG_DSC	Character(50)	VARCHAR2(50) NOT NULL	Stores the Plan Coverage Description used on the HIPAA 834 EDI file. It is populated on the Benefits Plan table. This field is not mandatory.
17	BN_834_FIELD_MAPPD	Character(20)	VARCHAR2(20) NOT NULL	Stores the mapped field value on the EDI 834 Mapping table. Prompt Table: BN_834_MAP_VW
18	DESCRLONG	Long Character	CLOB	Long Description