BEN_QRY_EESP_VW(SQL View) |
Index Back |
|---|---|
Ben Workcenter EE Spouse ViewView used in benefits workcenter for employee / spouse heath benefits audit view |
| SELECT N.EMPLID , N.EMPL_RCD ,K.NAME ,K.NAME_PSFORMAT ,N.PLAN_TYPE ,N.BENEFIT_PLAN ,N.COVRG_CD ,ND.DEPENDENT_BENEF ,J.EMPLID , S.OPRID ,S.BUSINESS_UNIT ,S.DEPTID ,S.COMPANY ,S.REG_REGION FROM PS_HEALTH_BENEFIT J , PS_PERSON_NAME K , PS_PERS_NID MN , PS_DEP_BENEF_NID LN , PS_HEALTH_BENEFIT N , PS_HEALTH_DEPENDNT ND , PS_PERS_SRCH_GBL S WHERE J.COVERAGE_ELECT = 'E' AND S.EMPLID=J.EMPLID AND J.EFFDT = ( SELECT MAX(X.EFFDT) FROM PS_HEALTH_BENEFIT X WHERE X.EMPLID = J.EMPLID AND X.EMPL_RCD = J.EMPL_RCD AND X.COBRA_EVENT_ID = J.COBRA_EVENT_ID AND X.PLAN_TYPE = J.PLAN_TYPE AND X.BENEFIT_NBR = J.BENEFIT_NBR AND X.EFFDT <= %CurrentDateIn) AND (J.COVERAGE_END_DT IS NULL OR J.COVERAGE_END_DT > %CurrentDateIn) AND MN.EMPLID = J.EMPLID AND MN.COUNTRY = 'USA' AND MN.PRIMARY_NID = 'Y' AND MN.NATIONAL_ID <> ' ' AND MN.NATIONAL_ID <> '999999999' AND LN.COUNTRY = MN.COUNTRY AND LN.PRIMARY_NID = MN.PRIMARY_NID AND LN.NATIONAL_ID = MN.NATIONAL_ID AND N.EMPLID = LN.EMPLID AND N.PLAN_TYPE = J.PLAN_TYPE AND N.COVERAGE_ELECT = 'E' AND N.EFFDT = ( SELECT MAX(X.EFFDT) FROM PS_HEALTH_BENEFIT X WHERE X.EMPLID = N.EMPLID AND X.EMPL_RCD = N.EMPL_RCD AND X.COBRA_EVENT_ID = N.COBRA_EVENT_ID AND X.PLAN_TYPE = N.PLAN_TYPE AND X.BENEFIT_NBR = N.BENEFIT_NBR AND X.EFFDT <= %CurrentDateIn) AND (N.COVERAGE_END_DT IS NULL OR N.COVERAGE_END_DT > %CurrentDateIn) AND ND.EMPLID = N.EMPLID AND ND.EMPL_RCD = N.EMPL_RCD AND ND.COBRA_EVENT_ID = N.COBRA_EVENT_ID AND ND.PLAN_TYPE = N.PLAN_TYPE AND ND.EFFDT = N.EFFDT AND ND.DEPENDENT_BENEF = LN.DEPENDENT_BENEF AND K.EMPLID = N.EMPLID |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | EMPL_RCD | Number(3,0) | SMALLINT NOT NULL | Benefit Record Number |
| 3 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 4 | NAME_PSFORMAT | Character(50) | VARCHAR2(50) NOT NULL | A Person's name in the Peoplesoft Format (basically LN,FN MI |
| 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 | BENEFIT_PLAN | Character(6) | VARCHAR2(6) NOT NULL | Benefit Plan |
| 7 | COVRG_CD | Character(2) | VARCHAR2(2) NOT NULL | Coverage Code |
| 8 | DEPENDENT_BENEF | Character(2) | VARCHAR2(2) NOT NULL |
Dependent Benefit
01=default |
| 9 | EMPLID_DEP | Character(11) | VARCHAR2(11) NOT NULL | Dependent's employee id |
| 10 | OPRID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 11 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: BUS_UNIT_TBL_HR |
| 12 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 13 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL |
Company
Prompt Table: COMPANY_TBL |
| 14 | REG_REGION | Character(5) | VARCHAR2(5) NOT NULL |
Regulatory Region
Prompt Table: REG_REGION_TBL |