PLAN_TYPEBENEFIT_PLANEMPLIDDEPENDENT_IDEFFDTBAS_DPND_RLT_VW(SQL View) |
Index Back |
|---|---|
Depend View for BAS EnrollmentThis is a view of DEPENDENT_BENEF, restricted to individuals of type "Dependent" or "Dependent/Beneficiary", used as a prompt table for BAS Enrollment. |
| SELECT R.PLAN_TYPE ,R.BENEFIT_PLAN ,A.EMPLID ,A.DEPENDENT_BENEF ,A.EFFDT ,B.NAME ,A.RELATIONSHIP FROM PS_DEP_BENEF_ASOF A , PS_DEP_BEN_NAME B , PS_LIFE_ADD_TBL P , PS_LIFE_ADD_RELATN R WHERE B.EMPLID = A.EMPLID AND B.DEPENDENT_BENEF = A.DEPENDENT_BENEF AND (A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_DEP_BENEF_ASOF A1 WHERE A1.EMPLID = A.EMPLID AND A1.DEPENDENT_BENEF = A.DEPENDENT_BENEF AND A.DEP_BENEF_TYPE IN ('D','O') AND A.DT_OF_DEATH IS NULL )) AND P.PLAN_TYPE = R.PLAN_TYPE AND P.BENEFIT_PLAN = R.BENEFIT_PLAN AND P.EFFDT = R.EFFDT AND P.SPEC_RELATION = 'Y' AND R.RELATIONSHIP = A.RELATIONSHIP AND P.EFFDT = ( SELECT MAX(P1.EFFDT) FROM PS_LIFE_ADD_TBL P1 WHERE P.PLAN_TYPE = P1.PLAN_TYPE AND P.BENEFIT_PLAN = P1.BENEFIT_PLAN AND P1.SPEC_RELATION = 'Y') |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | 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. |
| 2 | BENEFIT_PLAN |
Character(6) | VARCHAR2(6) NOT NULL | Benefit Plan |
| 3 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 4 | DEPENDENT_ID |
Character(2) | VARCHAR2(2) NOT NULL | Dependent ID |
| 5 | EFFDT |
Date(10) | DATE | Effective Date |
| 6 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 7 | RELATIONSHIP | Character(2) | VARCHAR2(2) NOT NULL | Relationship to Employee |