BEN_Q_EESSN_VW(SQL View) |
Index Back |
|---|---|
Benefits Workcenter QueryWorkcenter Query for employees missing SSN |
| SELECT A.EMPLID , B.NAME , B.NAME_DISPLAY , C.COUNTRY , B.NAME , S.OPRID ,S.BUSINESS_UNIT ,S.DEPTID ,S.COMPANY ,S.REG_REGION FROM PS_PERSON A , PS_NAMES B , PS_PERS_NID C , PS_HEALTH_BENEFIT D , PS_PERS_SRCH_GBL S WHERE D.COVERAGE_ELECT = 'E' AND D.EFFDT = ( SELECT MAX(Z1.EFFDT) FROM PS_HEALTH_BENEFIT Z1 WHERE Z1.EMPLID = D.EMPLID AND Z1.COBRA_EVENT_ID = D.COBRA_EVENT_ID AND Z1.EMPL_RCD = D.EMPL_RCD AND Z1.PLAN_TYPE = D.PLAN_TYPE AND Z1.BENEFIT_NBR = D.BENEFIT_NBR AND Z1.EFFDT <= %CurrentDateIn) AND S.EMPLID=A.EMPLID AND B.EMPLID = A.EMPLID AND C.EMPLID = A.EMPLID AND D.EMPLID = A.EMPLID AND C.COUNTRY='USA' AND (C.NATIONAL_ID = 'XXXXXXXXX' OR C.NATIONAL_ID = ' ' OR C.NATIONAL_ID = ' ') AND B.NAME_TYPE = 'PRI' AND B.EFFDT = ( SELECT MAX(BB.EFFDT) FROM PS_NAMES BB WHERE BB.EMPLID = B.EMPLID AND BB.NAME_TYPE = B.NAME_TYPE AND BB.EFFDT <= %CurrentDateIn ) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 3 | NAME_DISPLAY | Character(50) | VARCHAR2(50) NOT NULL | Display Name - name formatted for Display based on the Country |
| 4 | COUNTRY | Character(3) | VARCHAR2(3) NOT NULL | Country |
| 5 | NAME_PSFORMAT | Character(50) | VARCHAR2(50) NOT NULL | A Person's name in the Peoplesoft Format (basically LN,FN MI |
| 6 | OPRID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 7 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: BUS_UNIT_TBL_HR |
| 8 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 9 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL |
Company
Prompt Table: COMPANY_TBL |
| 10 | REG_REGION | Character(5) | VARCHAR2(5) NOT NULL |
Regulatory Region
Prompt Table: REG_REGION_TBL |