BN_M_EBX_ECV_VW (SQL View)	Index Back
eBenX Empl Coverage This view is used for the eBenX integration with Peoplesoft Benefits. This view joins to BN_M_SNAP_FILTR which is only populated during the EBNX_EXTRACT Application Engine Process

BN_M_EBX_ECV_VW

(SQL View)

eBenX Empl Coverage

This view is used for the eBenX integration with Peoplesoft Benefits. This view joins to BN_M_SNAP_FILTR which is only populated during the EBNX_EXTRACT Application Engine Process

SELECT A.EMPLID , A.BENEFIT_RCD_NBR , A.COBRA_EVENT_ID , A.PLAN_TYPE , B.EFFDT , B.COVERAGE_BEGIN_DT , B.COVERAGE_ELECT_DT , B.BENEFIT_PLAN , B.COVERAGE_ELECT , B.COVRG_CD , B.HLTH_PROVIDER_ID , B.PREVIOUSLY_SEEN , B.ANNUAL_PLEDGE , B.CALCULATED_BASE , B.PREMIUM_BASE FROM PS_BN_M_SNAP_FILTR A , PS_BN_SNAP_PLAN B WHERE A.EMPLID = B.EMPLID AND A.BENEFIT_RCD_NBR = B.BENEFIT_RCD_NBR AND A.COBRA_EVENT_ID = B.COBRA_EVENT_ID AND A.DEPENDENT_BENEF = B.DEPENDENT_BENEF AND A.PLAN_TYPE = B.PLAN_TYPE AND (A.COVERAGE_ELECT = B.COVERAGE_ELECT OR B.COVERAGE_ELECT = 'W') AND A.DEPENDENT_BENEF = ' ' AND NOT EXISTS ( SELECT 'x' FROM PS_BN_SNAP_PLAN T1 WHERE T1.DEDUCTION_BEGIN_DT <= ( SELECT MAX(T2.ASOFDATE) FROM PS_BN_SNAP_HIST T2 WHERE T2.BN_SNAP_HIST_TYPE = '3' ) AND T1.COVERAGE_ELECT_DT <= ( SELECT MAX(T3.ASOFDATE) FROM PS_BN_SNAP_HIST T3 WHERE T3.BN_SNAP_HIST_TYPE = '3' ) AND T1.EMPLID = B.EMPLID AND T1.BENEFIT_RCD_NBR = B.BENEFIT_RCD_NBR AND T1.COBRA_EVENT_ID = B.COBRA_EVENT_ID AND T1.DEPENDENT_BENEF = B.DEPENDENT_BENEF AND T1.PLAN_TYPE = B.PLAN_TYPE AND T1.COVERAGE_ELECT = 'T')

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

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

6 COVERAGE_BEGIN_DT Date(10) DATE Coverage Begin Date

7 COVERAGE_END_DT Date(10) DATE Coverage End Date

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

9 COVERAGE_ELECT Character(1) VARCHAR2(1) NOT NULL Coverage Election
E=Elect
T=Terminate
W=Waive

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

11 HLTH_PROVIDER_ID Character(30) VARCHAR2(30) NOT NULL Health Provider ID

12 PREVIOUSLY_SEEN Character(1) VARCHAR2(1) NOT NULL Previously Seen

13 ANNUAL_PLEDGE Signed Number(10,2) DECIMAL(8,2) NOT NULL Annual Pledge

14 CALCULATED_BASE Number(11,2) DECIMAL(10,2) NOT NULL Coverage Base

15 PREMIUM_BASE Number(11,2) DECIMAL(10,2) NOT NULL Premium Base

#	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	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.
5	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
6	COVERAGE_BEGIN_DT	Date(10)	DATE	Coverage Begin Date
7	COVERAGE_END_DT	Date(10)	DATE	Coverage End Date
8	BENEFIT_PLAN	Character(6)	VARCHAR2(6) NOT NULL	Benefit Plan
9	COVERAGE_ELECT	Character(1)	VARCHAR2(1) NOT NULL	Coverage Election E=Elect T=Terminate W=Waive
10	COVRG_CD	Character(2)	VARCHAR2(2) NOT NULL	Coverage Code
11	HLTH_PROVIDER_ID	Character(30)	VARCHAR2(30) NOT NULL	Health Provider ID
12	PREVIOUSLY_SEEN	Character(1)	VARCHAR2(1) NOT NULL	Previously Seen
13	ANNUAL_PLEDGE	Signed Number(10,2)	DECIMAL(8,2) NOT NULL	Annual Pledge
14	CALCULATED_BASE	Number(11,2)	DECIMAL(10,2) NOT NULL	Coverage Base
15	PREMIUM_BASE	Number(11,2)	DECIMAL(10,2) NOT NULL	Premium Base