W3EB_3XCVG_VW(SQL View) |
Index Back |
---|---|
Enrollment 3x Current CoverageThis view displays the employee's current coverage for the 3x 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.COVERAGE_BEGIN_DT , B.BENEFIT_PLAN , B.COVERAGE_ELECT , 0 FROM PS_BAS_PARTIC_PLAN A , PS_DISABILITY_BEN B WHERE A.PLAN_TYPE LIKE '3%' AND B.COVERAGE_ELECT = 'W' AND B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.BENEFIT_RCD_NBR AND B.PLAN_TYPE = A.PLAN_TYPE AND B.EFFDT = ( SELECT MAX(BB.EFFDT) FROM PS_DISABILITY_BEN BB WHERE BB.EMPLID = B.EMPLID AND BB.EMPL_RCD = B.EMPL_RCD AND BB.PLAN_TYPE = B.PLAN_TYPE AND BB.EFFDT <= A.COVERAGE_BEGIN_DT) AND B.EFFDT <= A.EVENT_DT AND B.COVERAGE_BEGIN_DT = ( SELECT MAX(BBB.COVERAGE_BEGIN_DT) FROM PS_DISABILITY_BEN BBB WHERE BBB.EMPLID = B.EMPLID AND BBB.EMPL_RCD = B.EMPL_RCD AND BBB.PLAN_TYPE = B.PLAN_TYPE AND BBB.COVERAGE_BEGIN_DT <= A.EVENT_DT) UNION SELECT A.SCHED_ID , A.EMPLID , A.BENEFIT_RCD_NBR , A.EVENT_ID , A.PLAN_TYPE , B.COVERAGE_BEGIN_DT , B.BENEFIT_PLAN , B.COVERAGE_ELECT , C.SALARY_REPLACE_PCT FROM PS_BAS_PARTIC_PLAN A , PS_DISABILITY_BEN B , PS_DISBLTY_PLN_TBL C WHERE A.PLAN_TYPE LIKE '3%' AND B.COVERAGE_ELECT <> 'W' AND B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.BENEFIT_RCD_NBR AND B.PLAN_TYPE = A.PLAN_TYPE AND B.EFFDT = ( SELECT MAX(BB.EFFDT) FROM PS_DISABILITY_BEN BB WHERE BB.EMPLID = B.EMPLID AND BB.EMPL_RCD = B.EMPL_RCD AND BB.PLAN_TYPE = B.PLAN_TYPE AND BB.EFFDT <= A.COVERAGE_BEGIN_DT) AND B.EFFDT <= A.EVENT_DT AND B.COVERAGE_BEGIN_DT = ( SELECT MAX(BBB.COVERAGE_BEGIN_DT) FROM PS_DISABILITY_BEN BBB WHERE BBB.EMPLID = B.EMPLID AND BBB.EMPL_RCD = B.EMPL_RCD AND BBB.PLAN_TYPE = B.PLAN_TYPE AND BBB.COVERAGE_BEGIN_DT <= A.EVENT_DT) AND C.PLAN_TYPE = B.PLAN_TYPE AND C.BENEFIT_PLAN = B.BENEFIT_PLAN AND C.EFFDT <= A.EVENT_DT AND C.EFFDT = ( SELECT MAX(BB.EFFDT) FROM PS_DISBLTY_PLN_TBL BB WHERE BB.PLAN_TYPE = B.PLAN_TYPE AND BB.BENEFIT_PLAN = B.BENEFIT_PLAN AND BB.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 | SALARY_REPLACE_PCT | Number(7,3) | DECIMAL(6,3) NOT NULL | Salary Replacement % |