HEALTH_VW(SQL View) |
Index Back |
---|---|
EE Current Health BenefitsHEALTH_VW is a select-only view used to display an employee's current health plan elections (plan type '1X'). This view accesses fields from the PS_HEALTH_BENEFIT table, and is used by the Benefits Summary 1 panel. |
SELECT A.EMPLID ,A.EMPL_RCD ,A.PLAN_TYPE ,A.EFFDT ,A.DEDUCTION_END_DT ,A.COVERAGE_BEGIN_DT ,A.COVERAGE_END_DT ,A.COVERAGE_ELECT ,A.BENEFIT_PLAN ,A.COVRG_CD FROM PS_HEALTH_BENEFIT A WHERE A.EFFDT = (SELECT MAX(B.EFFDT) FROM PS_HEALTH_BENEFIT B WHERE B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD AND B.PLAN_TYPE = A.PLAN_TYPE AND B.EFFDT <= %CURRENTDATEIN) |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table: PERSON |
2 | EMPL_RCD | Number(3,0) | SMALLINT NOT NULL |
Empl Rcd Nbr
Prompt Table: PER_ORG_ASGN_VW |
3 | 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. |
4 | DEDUCTION_BEGIN_DT | Date(10) | DATE | Deduction Begin Date |
5 | DEDUCTION_END_DT | Date(10) | DATE | Deduction End Date |
6 | COVERAGE_BEGIN_DT | Date(10) | DATE NOT NULL | Coverage Begin Date |
7 | COVERAGE_END_DT | Date(10) | DATE | Coverage End Date |
8 | COVERAGE_ELECT | Character(1) | VARCHAR2(1) NOT NULL |
Coverage Election
E=Elect T=Terminate W=Waive |
9 | BENEFIT_PLAN | Character(6) | VARCHAR2(6) NOT NULL | Benefit Plan |
10 | COVRG_CD | Character(2) | VARCHAR2(2) NOT NULL |
Coverage Code
Prompt Table: COVRG_CD_TBL |