HR_RW_VACC_DTL(SQL View) |
Index Back |
|---|---|
RW EmployeeVaccination Details |
| SELECT A.EMPLID , A.REG_REGION , A.IMMUN_CODE , JOB1.EMPL_RCD , A.START_DATE , CASE WHEN A.HS_DECLINE_VACCINE = 'Y' THEN 'D' ELSE A.HS_VACC_COMPLETE END , A1.DESCR , A.HS_MANUFACTURER_ID , A.HS_DOSE_NUM , A.HS_LOT_NUM , A.HS_VACC_SITE , A.HS_LOCATION , A.HS_VACC_COMPLETE , A.HS_NEXT_DUE_DATE , A.HS_DECLINE_VACCINE , A.HS_DECLINE_REASON , COALESCE(( SELECT M1.DESCR50 FROM PS_HS_MANUFACT_TBL M1 WHERE M1.SETID = A1.SETID AND M1.HS_MANUFACTURER_ID = A.HS_MANUFACTURER_ID AND %EffdtCheck(HS_MANUFACT_TBL M11, M1, %CurrentDateIn)),' '),CASE WHEN JOB1.LASTUPDDTTM >=A.LASTUPDDTTM THEN JOB1.LASTUPDDTTM ELSE A.LASTUPDDTTM END, CASE WHEN JOB1.LASTUPDDTTM >=A.LASTUPDDTTM THEN JOB1.LASTUPDDTTM ELSE A.LASTUPDDTTM END , ( SELECT D.XLATLONGNAME FROM XLATTABLE_VW D WHERE D.EFFDT = ( SELECT MAX(D_ED.EFFDT) FROM XLATTABLE_VW D_ED WHERE D.FIELDNAME = D_ED.FIELDNAME AND D.FIELDVALUE = D_ED.FIELDVALUE AND D_ED.EFFDT <= %CurrentDateIn) AND D.FIELDNAME = 'HS_VACC_STATUS' AND D.FIELDVALUE = CASE WHEN A.HS_DECLINE_VACCINE = 'Y' THEN 'D' ELSE A.HS_VACC_COMPLETE END ) FROM PS_EE_IMMUN_DTL A , PS_HR_RWORK_VW JOB1 , PS_HS_IMMUN_TBL A1 ,PS_JOBCODE_TBL J WHERE JOB1.EMPLID = A.EMPLID AND A.START_DATE =( SELECT MAX(B.START_DATE) FROM PS_EE_IMMUN_DTL B WHERE B.EMPLID=A.EMPLID AND B.IMMUN_CODE=A.IMMUN_CODE AND B.START_DATE<=%CurrentDateIn) AND A1.SETID = ( SELECT RR11.DEFAULT_SETID FROM PS_REG_REGION_TBL RR11 WHERE RR11.REG_REGION = A.REG_REGION) AND A1.IMMUN_CODE = A.IMMUN_CODE AND %EffdtCheck(HS_IMMUN_TBL A2, A1, %CurrentDateIn) AND A1.EFF_STATUS = 'A' AND J.SETID = JOB1.SETID_JOBCODE AND J.JOBCODE = JOB1.JOBCODE UNION ALL SELECT JOB4.EMPLID , JOB4.REG_REGION , I.IMMUN_CODE , JOB4.EMPL_RCD , %CurrentDateIn , 'U' , I.DESCR , ' ' , ' ' , ' ' , ' ' , ' ' , 'N' , %DateNull , ' ' , ' ' , ' ' , %CurrentDateTimeIn ,CASE WHEN JOB4.LASTUPDDTTM IS NOT NULL THEN JOB4.LASTUPDDTTM ELSE %DateTimeIn('1900-01-01-00.00.00.000000') END , ( SELECT D.XLATLONGNAME FROM XLATTABLE_VW D WHERE D.EFFDT = ( SELECT MAX(D_ED.EFFDT) FROM XLATTABLE_VW D_ED WHERE D.FIELDNAME = D_ED.FIELDNAME AND D.FIELDVALUE = D_ED.FIELDVALUE AND D_ED.EFFDT <= %CurrentDateIn) AND D.FIELDNAME = 'HS_VACC_STATUS' AND D.FIELDVALUE = 'U') FROM PS_HR_RWORK_VW JOB4 , PS_HS_IMMUN_VW I, PS_JOBCODE_TBL J1 WHERE I.SETID = ( SELECT R.DEFAULT_SETID FROM PS_REG_REGION_TBL R WHERE R.REG_REGION = JOB4.REG_REGION) AND J1.SETID = JOB4.SETID_JOBCODE AND J1.JOBCODE = JOB4.JOBCODE AND NOT EXISTS ( SELECT 'X' FROM PS_EE_IMMUN_DTL X1 WHERE X1.EMPLID = JOB4.EMPLID AND X1.IMMUN_CODE = I.IMMUN_CODE) AND NOT EXISTS( SELECT 'X' FROM PS_HR_RW_VAC_DEL C WHERE C.EMPLID=JOB4.EMPLID AND C.IMMUN_CODE=I.IMMUN_CODE) UNION ALL SELECT JOB4.EMPLID , JOB4.REG_REGION , I.IMMUN_CODE , JOB4.EMPL_RCD , %CurrentDateIn , 'U' , I.DESCR , ' ' , ' ' , ' ' , ' ' , ' ' , 'N' , %DateNull , ' ' , ' ' , ' ' , %CurrentDateTimeIn ,%CurrentDateTimeIn , ( SELECT D.XLATLONGNAME FROM XLATTABLE_VW D WHERE D.EFFDT = ( SELECT MAX(D_ED.EFFDT) FROM XLATTABLE_VW D_ED WHERE D.FIELDNAME = D_ED.FIELDNAME AND D.FIELDVALUE = D_ED.FIELDVALUE AND D_ED.EFFDT <= %CurrentDateIn) AND D.FIELDNAME = 'HS_VACC_STATUS' AND D.FIELDVALUE = 'U') FROM PS_HR_RWORK_VW JOB4 , PS_HS_IMMUN_VW I, PS_JOBCODE_TBL J1 WHERE I.SETID = ( SELECT R.DEFAULT_SETID FROM PS_REG_REGION_TBL R WHERE R.REG_REGION = JOB4.REG_REGION) AND J1.SETID = JOB4.SETID_JOBCODE AND J1.JOBCODE = JOB4.JOBCODE AND (EXISTS( SELECT 'X' FROM PS_EE_IMMUN_DTL X2 WHERE X2.EMPLID = JOB4.EMPLID AND X2.IMMUN_CODE = I.IMMUN_CODE AND X2.START_DATE>%CurrentDateIn ) OR EXISTS( SELECT 'X' FROM PS_HR_RW_VAC_DEL C2 WHERE C2.EMPLID=JOB4.EMPLID AND C2.IMMUN_CODE=I.IMMUN_CODE)) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | REG_REGION |
Character(5) | VARCHAR2(5) NOT NULL | Region |
| 3 | IMMUN_CODE |
Character(4) | VARCHAR2(4) NOT NULL | Immunization Code |
| 4 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 5 | START_DATE | Date(10) | DATE | Date |
| 6 | HS_VACC_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Indicates if an employee has completed the vaccine series.
D=Vaccine Declined N=Partially Vaccinated U=Not Vaccinated Y=Fully Vaccinated |
| 7 | VACCINE_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Vaccine |
| 8 | HS_MANUFACTURER_ID | Character(4) | VARCHAR2(4) NOT NULL | Health and safety manufacturer id |
| 9 | HS_DOSE_NUM | Character(1) | VARCHAR2(1) NOT NULL |
Vaccine dose number
1=1st Dose 2=2nd Dose 3=3rd Dose 4=4th Dose 5=5th Dose |
| 10 | HS_LOT_NUM | Character(30) | VARCHAR2(30) NOT NULL | Vaccine lot number |
| 11 | HS_VACC_SITE | Character(1) | VARCHAR2(1) NOT NULL |
Vaccination Site
1=Workplace 2=Other |
| 12 | HS_LOCATION | Character(50) | VARCHAR2(50) NOT NULL | Location Name |
| 13 | HS_VACC_COMPLETE | Character(1) | VARCHAR2(1) NOT NULL |
Indicates if an employee has completed the vaccine series.
N=No Y=Yes |
| 14 | HS_NEXT_DUE_DATE | Date(10) | DATE | Next due date for a vaccine |
| 15 | HS_DECLINE_VACCINE | Character(1) | VARCHAR2(1) NOT NULL |
Indicates if employee declines a vaccine
N=No Y=Yes |
| 16 | HS_DECLINE_REASON | Character(1) | VARCHAR2(1) NOT NULL |
Reason for declining a vaccine
A=Allergic Reaction M=Medical Exemption O=Other P=Personal Reasons R=Religious Beliefs |
| 17 | DESCR50 | Character(50) | VARCHAR2(50) NOT NULL | Description of length 50 |
| 18 | RW_LASTUPDDTTM | DateTime(26) | TIMESTAMP | Last Updated |
| 19 | LASTUPDDTTM | DateTime(26) | TIMESTAMP | Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts. |
| 20 | HR_RW_VACC_STATUS | Character(30) | VARCHAR2(30) NOT NULL | Indicates if an employee has completed the vaccine series. |