HRS_ACT_RECR_VW

(SQL View)
 Index Back

view for curr active employees

view for curr active employees Only one row per emplid is returned. The EFFDT is included only for reference back to NAMES as ASOFDATE. No Query Security record is specified because in query this would be one of the joined in records. The main record should contain the query security.

SELECT DISTINCT A.EMPLID ,A.FIRST_NAME ,A.LAST_NAME ,A.NAME_DISPLAY ,A.NAME_TYPE ,A.EFFDT FROM PS_NAMES A , PS_ROLEUSER_VW R WHERE R.EMPLID = A.EMPLID AND A.NAME_TYPE = 'PRI' AND A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_NAMES B WHERE B.EMPLID = A.EMPLID AND B.NAME_TYPE = A.NAME_TYPE AND B.EFFDT <= %CurrentDateIn ) AND (EXISTS ( SELECT 'X' FROM PS_JOB B WHERE B.EMPLID = A.EMPLID AND B.EFFDT = ( SELECT MAX (C.EFFDT) FROM PS_JOB C WHERE C.EMPLID = B.EMPLID AND C.EMPL_RCD = B.EMPL_RCD AND C.EFFDT <= %CurrentDateIn) AND B.EFFSEQ = ( SELECT MAX(D.EFFSEQ) FROM PS_JOB D WHERE D.EMPLID = B.EMPLID AND D.EMPL_RCD = B.EMPL_RCD AND D.EFFDT = B.EFFDT) AND B.HR_STATUS = 'A') OR EXISTS ( SELECT 'X' FROM PS_PER_POI_TRANS P WHERE P.EMPLID = A.EMPLID AND P.EFFDT = ( SELECT MAX(C.EFFDT) FROM PS_PER_POI_TRANS C WHERE C.EMPLID = P.EMPLID AND C.EFFDT <= %CurrentDateIn) AND P.EFF_STATUS = 'A' )) AND R.EMPLID <> ' ' AND R.ROLENAME IN ( SELECT S.ROLENAME FROM PS_HRS_ROLE_DEFN S WHERE S.HRS_ROLE_TYPE = '01' AND S.EFFDT = ( SELECT MAX(T.EFFDT) FROM PS_HRS_ROLE_DEFN T WHERE T.HRS_ROLE_TYPE = S.HRS_ROLE_TYPE AND T.EFFDT <= %CurrentDateIn) AND S.EFF_STATUS = 'A')

  • Related Language Record: HRS_ACT_RECR_LN

  • Parent record: PERSON

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Prompt Table: PERSON

    2 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
    3 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
    4 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
    5 NAME_TYPE Character(3) VARCHAR2(3) NOT NULL Type of Name

    Prompt Table: NAME_TYPE_TBL

    6 ASOFDATE Date(10) DATE As of Date