HS_SS_JOB_SRCH

(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 , A.EMPL_RCD , A.EFFDT , A.EMPL_STATUS , B.NAME , B.NAME_DISPLAY , B.LAST_NAME , B.FIRST_NAME , B.LAST_NAME_SRCH , B.SECOND_LAST_SRCH , B.FIRST_NAME_SRCH , B.SECOND_LAST_NAME , A.SETID_DEPT , A.DEPTID , C.DESCR , A.LOCATION , D.DESCR , A.JOBCODE , E.DESCR FROM PS_HS_SS_PRI_JOB_V A , PS_NAMES B , PS_DEPT_TBL C , PS_LOCATION_TBL D , PS_JOBCODE_TBL E WHERE A.EMPLID = B.EMPLID AND %EffdtCheck(HS_SS_PRI_JOB_V A1, A, %CurrentDateIn) AND B.NAME_TYPE = 'PRI' AND %EffdtCheck(NAMES B1, B, %CurrentDateIn) AND A.SETID_DEPT = C.SETID AND A.DEPTID = C.DEPTID AND %EffdtCheck(DEPT_TBL DEPT_ED, C, %CurrentDateIn) AND A.SETID_LOCATION = D.SETID AND A.LOCATION = D.LOCATION AND %EffdtCheck(LOCATION_TBL LOC_ED, D, %CurrentDateIn) AND A.SETID_JOBCODE = E.SETID AND A.JOBCODE = E.JOBCODE AND %EffdtCheck(JOBCODE_TBL JOBCD_ED, E, %CurrentDateIn)

  • Related Language Record: HS_SS_JOBSRCH_L

    • # 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 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    3 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    4 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Payroll Status
    A=Active
    D=Deceased
    L=Leave of Absence
    P=Leave With Pay
    Q=Retired With Pay
    R=Retired
    S=Suspended
    T=Terminated
    U=Terminated With Pay
    V=Terminated Pension Pay Out
    W=Short Work Break
    X=Retired-Pension Administration
    5 NAME Character(50) VARCHAR2(50) NOT NULL Name
    6 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    7 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
    8 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
    9 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
    10 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Last Name
    11 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
    12 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
    13 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
    14 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    15 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    16 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    17 LOCATION_DESCR Character(30) VARCHAR2(30) NOT NULL Location
    18 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    19 JOBCODE_DESCR Character(30) VARCHAR2(30) NOT NULL Job Title