W3EB_6XCVG_VW (SQL View)	Index Back
Enrollment 6x Current Coverage This view displays the employee's current coverage for the 6x plan types. It shows the coverage current as of the DEDUCTION_BEGIN_DT on the BAS_PARTIC_PLAN row.

W3EB_6XCVG_VW

(SQL View)

Enrollment 6x Current Coverage

This view displays the employee's current coverage for the 6x plan types. It shows the coverage current as of the DEDUCTION_BEGIN_DT on the BAS_PARTIC_PLAN row.

SELECT A.SCHED_ID ,A.EMPLID ,A.BENEFIT_RCD_NBR ,A.EVENT_ID ,A.PLAN_TYPE ,B.EFFDT ,B.BENEFIT_PLAN ,B.COVERAGE_ELECT ,B.EMPL_CONTRBUTN_AMT ,B.ANNUAL_PLEDGE ,B.FSA_ACCT_STATUS ,B.CARRYFWD_AMT_CLM ,B.CARRYFWD_AMT_CRD ,B.CARRYFWD_CLM_SPENT ,B.CARRYFWD_CRD_SPENT ,0 FROM PS_BAS_PARTIC_PLAN A , PS_FSA_BENEFIT B WHERE A.PLAN_TYPE LIKE '6%' AND B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.BENEFIT_RCD_NBR AND B.COBRA_EVENT_ID = 0 AND B.PLAN_TYPE = A.PLAN_TYPE AND ((B.EFFDT <= A.DEDUCTION_BEGIN_DT AND B.EFFDT = ( SELECT MAX(BB.EFFDT) FROM PS_FSA_BENEFIT BB WHERE BB.EMPLID = B.EMPLID AND BB.EMPL_RCD = B.EMPL_RCD AND BB.COBRA_EVENT_ID = 0 AND BB.PLAN_TYPE = B.PLAN_TYPE AND BB.EFFDT <= A.DEDUCTION_BEGIN_DT)) OR (A.DEDUCTION_BEGIN_DT IS NULL AND B.EFFDT <= A.EVENT_DT AND B.EFFDT = ( SELECT MAX(BBB.EFFDT) FROM PS_FSA_BENEFIT BBB WHERE BBB.EMPLID = B.EMPLID AND BBB.EMPL_RCD = B.EMPL_RCD AND BBB.COBRA_EVENT_ID = 0 AND BBB.PLAN_TYPE = B.PLAN_TYPE AND BBB.EFFDT <= A.EVENT_DT)))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 SCHED_ID Character(6) VARCHAR2(6) NOT NULL Schedule ID

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

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

4 EVENT_ID Number(6,0) INTEGER NOT NULL Event Identification

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

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

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

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

9 EMPL_CONTRBUTN_AMT Number(7,2) DECIMAL(6,2) NOT NULL Employee Contribution Amount

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

11 FSA_ACCT_STATUS Character(1) VARCHAR2(1) NOT NULL FSA Account Status
A=Active
C=Closed
I=Not Eligible
T=Terminated by Employer

12 CARRYFWD_AMT_CLM Number(9,2) DECIMAL(8,2) NOT NULL Carryforward Amount - Claims

13 CARRYFWD_AMT_CRD Number(9,2) DECIMAL(8,2) NOT NULL Carryforward Amount - Credits

14 CARRYFWD_CLM_SPENT Number(9,2) DECIMAL(8,2) NOT NULL Carryforward - Claim Spent

15 CARRYFWD_CRD_SPENT Number(9,2) DECIMAL(8,2) NOT NULL Carryforward - Credit Spent

16 FSA_BALANCE Signed Number(10,2) DECIMAL(8,2) NOT NULL Account Balance

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	SCHED_ID	Character(6)	VARCHAR2(6) NOT NULL	Schedule ID
2	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
3	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.
4	EVENT_ID	Number(6,0)	INTEGER NOT NULL	Event Identification
5	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.
6	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
7	BENEFIT_PLAN	Character(6)	VARCHAR2(6) NOT NULL	Benefit Plan
8	COVERAGE_ELECT	Character(1)	VARCHAR2(1) NOT NULL	Coverage Election E=Elect T=Terminate W=Waive
9	EMPL_CONTRBUTN_AMT	Number(7,2)	DECIMAL(6,2) NOT NULL	Employee Contribution Amount
10	ANNUAL_PLEDGE	Signed Number(10,2)	DECIMAL(8,2) NOT NULL	Annual Pledge
11	FSA_ACCT_STATUS	Character(1)	VARCHAR2(1) NOT NULL	FSA Account Status A=Active C=Closed I=Not Eligible T=Terminated by Employer
12	CARRYFWD_AMT_CLM	Number(9,2)	DECIMAL(8,2) NOT NULL	Carryforward Amount - Claims
13	CARRYFWD_AMT_CRD	Number(9,2)	DECIMAL(8,2) NOT NULL	Carryforward Amount - Credits
14	CARRYFWD_CLM_SPENT	Number(9,2)	DECIMAL(8,2) NOT NULL	Carryforward - Claim Spent
15	CARRYFWD_CRD_SPENT	Number(9,2)	DECIMAL(8,2) NOT NULL	Carryforward - Credit Spent
16	FSA_BALANCE	Signed Number(10,2)	DECIMAL(8,2) NOT NULL	Account Balance