HS_SS_JOBSRCH_L

(SQL View)
 Index Back

Job and Name Info for all EEs

View that retrieves all employee rows from Job and person name.

SELECT DISTINCT A.EMPLID , L.LANGUAGE_CD , COALESCE(( SELECT NL.NAME FROM PS_NAMES_LNG NL WHERE NL.EMPLID = A.EMPLID AND NL.NAME_TYPE = 'PRI' AND NL.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(NAMES_LNG NL1, NL, %CurrentDateIn)), ( SELECT N.NAME FROM PS_NAMES N WHERE N.EMPLID = A.EMPLID AND N.NAME_TYPE = 'PRI' AND %EffdtCheck(NAMES N1, N, %CurrentDateIn)), ' ') , COALESCE(( SELECT NL.NAME_DISPLAY FROM PS_NAMES_LNG NL WHERE NL.EMPLID = A.EMPLID AND NL.NAME_TYPE = 'PRI' AND NL.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(NAMES_LNG NL1, NL, %CurrentDateIn)), ( SELECT N.NAME_DISPLAY FROM PS_NAMES N WHERE N.EMPLID = A.EMPLID AND N.NAME_TYPE = 'PRI' AND %EffdtCheck(NAMES N1, N, %CurrentDateIn)), ' ') , COALESCE(( SELECT NL.LAST_NAME FROM PS_NAMES_LNG NL WHERE NL.EMPLID = A.EMPLID AND NL.NAME_TYPE = 'PRI' AND NL.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(NAMES_LNG NL1, NL, %CurrentDateIn)), ( SELECT N.LAST_NAME FROM PS_NAMES N WHERE N.EMPLID = A.EMPLID AND N.NAME_TYPE = 'PRI' AND %EffdtCheck(NAMES N1, N, %CurrentDateIn)), ' ') , COALESCE(( SELECT NL.FIRST_NAME FROM PS_NAMES_LNG NL WHERE NL.EMPLID = A.EMPLID AND NL.NAME_TYPE = 'PRI' AND NL.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(NAMES_LNG NL1, NL, %CurrentDateIn)), ( SELECT N.FIRST_NAME FROM PS_NAMES N WHERE N.EMPLID = A.EMPLID AND N.NAME_TYPE = 'PRI' AND %EffdtCheck(NAMES N1, N, %CurrentDateIn)), ' ') , COALESCE(( SELECT NL.LAST_NAME_SRCH FROM PS_NAMES_LNG NL WHERE NL.EMPLID = A.EMPLID AND NL.NAME_TYPE = 'PRI' AND NL.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(NAMES_LNG NL1, NL, %CurrentDateIn)), ( SELECT N.LAST_NAME_SRCH FROM PS_NAMES N WHERE N.EMPLID = A.EMPLID AND N.NAME_TYPE = 'PRI' AND %EffdtCheck(NAMES N1, N, %CurrentDateIn)), ' ') , COALESCE(( SELECT NL.SECOND_LAST_SRCH FROM PS_NAMES_LNG NL WHERE NL.EMPLID = A.EMPLID AND NL.NAME_TYPE = 'PRI' AND NL.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(NAMES_LNG NL1, NL, %CurrentDateIn)), ( SELECT N.SECOND_LAST_SRCH FROM PS_NAMES N WHERE N.EMPLID = A.EMPLID AND N.NAME_TYPE = 'PRI' AND %EffdtCheck(NAMES N1, N, %CurrentDateIn)), ' ') , COALESCE(( SELECT NL.FIRST_NAME_SRCH FROM PS_NAMES_LNG NL WHERE NL.EMPLID = A.EMPLID AND NL.NAME_TYPE = 'PRI' AND NL.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(NAMES_LNG NL1, NL, %CurrentDateIn)), ( SELECT N.FIRST_NAME_SRCH FROM PS_NAMES N WHERE N.EMPLID = A.EMPLID AND N.NAME_TYPE = 'PRI' AND %EffdtCheck(NAMES N1, N, %CurrentDateIn)), ' ') , COALESCE(( SELECT NL.SECOND_LAST_NAME FROM PS_NAMES_LNG NL WHERE NL.EMPLID = A.EMPLID AND NL.NAME_TYPE = 'PRI' AND NL.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(NAMES_LNG NL1, NL, %CurrentDateIn)), ( SELECT N.SECOND_LAST_NAME FROM PS_NAMES N WHERE N.EMPLID = A.EMPLID AND N.NAME_TYPE = 'PRI' AND %EffdtCheck(NAMES N1, N, %CurrentDateIn)), ' ') , COALESCE(( SELECT DL.DESCR FROM PS_DEPT_TBL_LANG DL WHERE DL.SETID = A.SETID_DEPT AND DL.DEPTID = A.DEPTID AND DL.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(DEPT_TBL_LANG DL1, DL, %CurrentDateIn)), ( SELECT D.DESCR FROM PS_DEPT_TBL D WHERE D.SETID = A.SETID_DEPT AND D.DEPTID = A.DEPTID AND %EffdtCheck(DEPT_TBL D1, D, %CurrentDateIn)), ' ') , COALESCE(( SELECT LOCL.DESCR FROM PS_LOCATION_LANG LOCL WHERE LOCL.SETID = A.SETID_LOCATION AND LOCL.LOCATION = A.LOCATION AND LOCL.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(LOCATION_LANG LOCL1, LOCL, %CurrentDateIn)), ( SELECT LOC.DESCR FROM PS_LOCATION_TBL LOC WHERE LOC.SETID = A.SETID_LOCATION AND LOC.LOCATION = A.LOCATION AND %EffdtCheck(LOCATION_TBL LOC1, LOC, %CurrentDateIn)), ' ') , COALESCE(( SELECT JCL.DESCR FROM PS_JOBCODE_LANG JCL WHERE JCL.SETID = A.SETID_JOBCODE AND JCL.JOBCODE = A.JOBCODE AND JCL.LANGUAGE_CD = L.LANGUAGE_CD AND %EffdtCheck(JOBCODE_LANG JCL1, JCL, %CurrentDateIn)), ( SELECT JC.DESCR FROM PS_JOBCODE_TBL JC WHERE JC.SETID = A.SETID_JOBCODE AND JC.JOBCODE = A.JOBCODE AND %EffdtCheck(JOBCODE_TBL JC1, JC, %CurrentDateIn)), ' ') FROM PS_HS_SS_PRI_JOB_V A, PSLANGUAGES L WHERE %EffdtCheck(HS_SS_PRI_JOB_V A1, A, %CurrentDateIn)

  • Related Language Record for HS_SS_JOB_SRCH

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 HS_EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID for Health and Safety
    2 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    3 NAME Character(50) VARCHAR2(50) NOT NULL Name
    4 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    5 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
    6 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
    7 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
    8 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Last Name
    9 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
    10 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
    11 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    12 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location
    13 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Title