HD_360_BEN_VW (SQL View)	Index Back
Benefits View This view is the top level information (Employee / Benefits Record Number / Cobra Event) sent to the CRM Help Desk application via the Integration Broker. We put BEN_REC_NUM at end of the view, it's value of BENEFIT_RCD_NBR. The reason we are doing this is that tools always hide BENEFIT_RCD_NBR on page, so we are using BEN_REC_NUM to display on page.

HD_360_BEN_VW

(SQL View)

Benefits View

This view is the top level information (Employee / Benefits Record Number / Cobra Event) sent to the CRM Help Desk application via the Integration Broker. We put BEN_REC_NUM at end of the view, it's value of BENEFIT_RCD_NBR. The reason we are doing this is that tools always hide BENEFIT_RCD_NBR on page, so we are using BEN_REC_NUM to display on page.

SELECT A.EMPLID , A.EMPL_RCD , A.COBRA_EVENT_ID , A.EFFDT , A.BENEFIT_PROGRAM , B.CURRENCY_CD , A.EMPL_RCD FROM PS_BEN_PROG_PARTIC A , PS_BEN_DEFN_PGM B WHERE B.BENEFIT_PROGRAM = A.BENEFIT_PROGRAM AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_BEN_DEFN_PGM B1 WHERE B1.BENEFIT_PROGRAM = A.BENEFIT_PROGRAM AND B1.EFFDT <= A.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

2 BENEFIT_RCD_NBR Number(3,0) SMALLINT NOT NULL Specifies a sequence number associated with each of multiple concurrent jobs for a one employee. It is used to determine the applicable benefit program for each job. The first job entered for an employee is automatically assigned a Benefit Record Number of zero (0). This field is linked with EMPL_RCD#, the Employment Record Number.

3 COBRA_EVENT_ID Number(3,0) SMALLINT NOT NULL Uniquely identifies a COBRA event within an employee's benefits records, and is system-generated.

4 EFFDT Date(10) DATE Effective Date
Default Value: %date

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

6 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL This field stores the Currency Code value.

7 BEN_REC_NUM Number(3,0) SMALLINT NOT NULL Benefit Record Number

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	BENEFIT_RCD_NBR	Number(3,0)	SMALLINT NOT NULL	Specifies a sequence number associated with each of multiple concurrent jobs for a one employee. It is used to determine the applicable benefit program for each job. The first job entered for an employee is automatically assigned a Benefit Record Number of zero (0). This field is linked with EMPL_RCD#, the Employment Record Number.
3	COBRA_EVENT_ID	Number(3,0)	SMALLINT NOT NULL	Uniquely identifies a COBRA event within an employee's benefits records, and is system-generated.
4	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
5	BENEFIT_PROGRAM	Character(3)	VARCHAR2(3) NOT NULL	Defines a collection of benefit plans and their associated rate and calculation rules.
6	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	This field stores the Currency Code value.
7	BEN_REC_NUM	Number(3,0)	SMALLINT NOT NULL	Benefit Record Number