HR_RWORKVW

(SQL View)
 Index Back

Remote worker view

This view holds the active employees job and remote request details

SELECT DISTINCT A.emplid ,A.empl_rcd ,A.EFFDT ,A.EFFSEQ ,%Coalesce(R.START_DATE ,%DateTimeIn('1900-01-01-00.00.00.000000')) ,R.hr_rw_type , CASE WHEN HR_RW_TYPE='H' THEN ( SELECT XLATLONGNAME FROM PSXLATITEM A WHERE FIELDNAME = 'HR_RW_TYPE' AND FIELDVALUE = 'H' AND EFFDT = ( SELECT MAX(EFFDT) FROM PSXLATITEM AA WHERE A.FIELDNAME = AA.FIELDNAME AND A.FIELDVALUE = AA.FIELDVALUE AND AA.EFFDT <= %CurrentDateIn) ) WHEN HR_RW_TYPE='F' THEN ( SELECT XLATLONGNAME FROM PSXLATITEM A WHERE FIELDNAME = 'HR_RW_TYPE' AND FIELDVALUE = 'F' AND EFFDT = ( SELECT MAX(EFFDT) FROM PSXLATITEM AA WHERE A.FIELDNAME = AA.FIELDNAME AND A.FIELDVALUE = AA.FIELDVALUE AND AA.EFFDT <= %CurrentDateIn) ) ELSE ( SELECT MESSAGE_TEXT FROM PSMSGCATDEFN WHERE message_set_nbr='1000' AND message_nbr='3050' ) END FROM ((PS_JOB A LEFT OUTER JOIN ( SELECT * FROM PS_HR_RWORK_TBL R WHERE (R.START_DATE IS NULL OR R.START_DATE=( SELECT MAX(START_DATE) FROM PS_HR_RWORK_TBL R1 WHERE R.EMPLID=R1.EMPLID AND R.EMPL_RCD=R1.EMPL_RCD AND R1.START_DATE <= %CurrentDateIn AND R1.RW_SS_STATUS='A' ) AND (R.END_DATE IS NULL OR R.END_DATE >= %CurrentDateIn ))) R ON A.EMPLID=R.EMPLID AND A.EMPL_RCD=R.EMPL_RCD )) WHERE A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES WHERE A.EMPLID = A_ES.EMPLID AND A.EMPL_RCD = A_ES.EMPL_RCD AND A.EFFDT = A_ES.EFFDT) AND A.HR_STATUS='A'

  • Related Language Record: HR_RWORKVW_LNG

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

    Default Value: PER_ORG_ASGN.EMPLID

    Prompt Table: PERS_SRCH_GBL

    2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    3 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    4 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
    5 START_DATE Date(10) DATE NOT NULL Start Date for Gen Standing PO
    6 EMPL_TYPE Character(1) VARCHAR2(1) NOT NULL Employee Type
    E=Exception Hourly
    H=Hourly
    N=Not Applicable
    S=Salaried
    7 RW_TYPE_DESCR Character(30) VARCHAR2(30) NOT NULL Remote Worker Type