BEN_Q_DP_SSN_VW(SQL View) |
Index Back |
|---|---|
Benefits Workcenter QueryWorkcenter query for dependents missing SSN |
| SELECT DISTINCT D.EMPLID , N.NAME_DISPLAY , E.DEPENDENT_BENEF , E.NAME_DISPLAY , NN.NATIONAL_ID , S.OPRID ,S.BUSINESS_UNIT ,S.DEPTID ,S.COMPANY ,S.REG_REGION FROM PS_HEALTH_BENEFIT D , PS_DEPENDENT_BENEF E , PS_HEALTH_DEPENDNT G , PS_NAMES N ,PS_DEP_BENEF_NID NN , PS_PERS_SRCH_GBL S WHERE D.COVERAGE_ELECT = 'E' AND S.REG_REGION = 'USA' 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=D.EMPLID AND D.EMPLID = E.EMPLID AND G.DEPENDENT_BENEF = E.DEPENDENT_BENEF AND G.EMPLID = D.EMPLID AND G.EMPL_RCD = D.EMPL_RCD AND G.COBRA_EVENT_ID = D.COBRA_EVENT_ID AND G.PLAN_TYPE = D.PLAN_TYPE AND G.BENEFIT_NBR = D.BENEFIT_NBR AND G.EFFDT = D.EFFDT AND E.DEPENDENT_BENEF=NN.DEPENDENT_BENEF AND NN.EMPLID=E.EMPLID AND NN.COUNTRY='USA' AND (NN.NATIONAL_ID = 'XXXXXXXXX' OR NN.NATIONAL_ID = ' ' OR NN.NATIONAL_ID = ' ') AND D.EMPLID=N.EMPLID AND N.NAME_TYPE='PRI' AND N.EFFDT= ( SELECT MAX(B.EFFDT) FROM PS_NAMES B WHERE B.EMPLID = N.EMPLID AND B.NAME_TYPE = N.NAME_TYPE AND B.EFFDT <= %CurrentDateIn ) UNION SELECT DISTINCT D.EMPLID , N.NAME_DISPLAY , E.DEPENDENT_BENEF , E.NAME_DISPLAY , ' ' , S.OPRID ,S.BUSINESS_UNIT ,S.DEPTID ,S.COMPANY ,S.REG_REGION FROM PS_HEALTH_BENEFIT D , PS_DEPENDENT_BENEF E , PS_HEALTH_DEPENDNT G , PS_NAMES N , PS_PERS_SRCH_GBL S WHERE D.COVERAGE_ELECT = 'E' AND S.REG_REGION = 'USA' 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=D.EMPLID AND D.EMPLID = E.EMPLID AND G.DEPENDENT_BENEF = E.DEPENDENT_BENEF AND G.EMPLID = D.EMPLID AND G.EMPL_RCD = D.EMPL_RCD AND G.COBRA_EVENT_ID = D.COBRA_EVENT_ID AND G.PLAN_TYPE = D.PLAN_TYPE AND G.BENEFIT_NBR = D.BENEFIT_NBR AND G.EFFDT = D.EFFDT AND D.EMPLID=N.EMPLID AND N.NAME_TYPE='PRI' AND N.EFFDT= ( SELECT MAX(B.EFFDT) FROM PS_NAMES B WHERE B.EMPLID = N.EMPLID AND B.NAME_TYPE = N.NAME_TYPE AND B.EFFDT <= %CurrentDateIn ) AND NOT EXISTS ( SELECT 'X' FROM PS_DEP_BENEF_NID NN WHERE E.DEPENDENT_BENEF=NN.DEPENDENT_BENEF AND NN.EMPLID=E.EMPLID AND NN.COUNTRY='USA' ) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | NAME_DISPLAY | Character(50) | VARCHAR2(50) NOT NULL | Display Name - name formatted for Display based on the Country |
| 3 | DEPENDENT_BENEF | Character(2) | VARCHAR2(2) NOT NULL |
Dependent Benefit
01=default |
| 4 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 5 | NATIONAL_ID | Character(20) | VARCHAR2(20) NOT NULL | National ID |
| 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 |