Job Info Query

Job information for users

Job Information is appended to the PeopleSoft account in Sentinel to provide enhanced search and reporting capability within the application.

This is a sample query that is designed to work across any PeopleSoft HCM system. The output fields are required, but the filters can be customized as needed.

Job Query Required Fields

Sentinel reads the following Job Information fields through a default query. If the information is not accurate, a custom query can be defined.

• BUDESCRstring30Buiness Unit Description

• BUIDstring10Buiness Unit ID

• COMPANYstring12Company ID

• COMPANYDESCRstring30Company Description

• DEPTDESCRstring50Department Name

• DEPTIDstring10Department ID

• EFFDTdateEffective Date

• EMAILstring100Primary Email Address

• EMPLIDstring30Unique EMPLID

• ESTABDESCRstring30Establishment Description

• ESTABIDstring12Establishment ID

• HRSTATUSstring1Job status on current date

• JOBCODEstring50Job Code ID

• JOBDESCRstring50Job Code Description

• LOCATIONstring12Location

• LOCATIONDESCRstring12Location Description

• NAMEstring50Person name: <First name, Last name>

• POSDESCRstring30Position Description

• POSITIONstring8Position

• REG_REGIONstring12Region

• REGDESCRstring12Region Description

• REPORTS_TOstring12Reports to position ID

• SUPERVISOR_IDstring8Supervisor ID

WITH
job_cur AS (
  SELECT j.*,
         ROW_NUMBER() OVER (
           PARTITION BY j.EMPLID          -- add , j.EMPL_RCD if you need every job record
           ORDER BY j.HR_STATUS,          -- 'A' before 'I'
                    j.EFFDT DESC,
                    j.EFFSEQ DESC,
                    j.EMPL_RCD
         ) AS rnk
  FROM PS_JOB j
  WHERE j.EFFDT <= SYSDATE
),
names_cur AS (
  SELECT EMPLID, NAME
  FROM (
    SELECT EMPLID, NAME,
           ROW_NUMBER() OVER (
             PARTITION BY EMPLID
             ORDER BY EFFDT DESC, NAME_TYPE DESC
           ) AS rnk
    FROM PS_NAMES
    WHERE EFF_STATUS = 'A'
      AND EFFDT <= SYSDATE
  )
  WHERE rnk = 1
),
dept_cur AS (
  SELECT SETID, DEPTID, DESCR
  FROM (
    SELECT SETID, DEPTID, DESCR,
           ROW_NUMBER() OVER (
             PARTITION BY SETID, DEPTID
             ORDER BY EFFDT DESC
           ) AS rnk
    FROM PS_DEPT_TBL
    WHERE EFF_STATUS = 'A'
      AND EFFDT <= SYSDATE
  )
  WHERE rnk = 1
),
jobc_cur AS (
  SELECT SETID, JOBCODE, DESCR
  FROM (
    SELECT SETID, JOBCODE, DESCR,
           ROW_NUMBER() OVER (
             PARTITION BY SETID, JOBCODE
             ORDER BY EFFDT DESC
           ) AS rnk
    FROM PS_JOBCODE_TBL
    WHERE EFF_STATUS = 'A'
      AND EFFDT <= SYSDATE
  )
  WHERE rnk = 1
),
pos_cur AS (
  SELECT POSITION_NBR, DESCR
  FROM (
    SELECT POSITION_NBR, DESCR,
           ROW_NUMBER() OVER (
             PARTITION BY POSITION_NBR
             ORDER BY EFFDT DESC
           ) AS rnk
    FROM PS_POSITION_DATA
    WHERE EFF_STATUS = 'A'
      AND EFFDT <= SYSDATE
  )
  WHERE rnk = 1
),
co_cur AS (
  SELECT COMPANY, DESCR
  FROM (
    SELECT COMPANY, DESCR,
           ROW_NUMBER() OVER (
             PARTITION BY COMPANY
             ORDER BY EFFDT DESC
           ) AS rnk
    FROM PS_COMPANY_TBL
    WHERE EFF_STATUS = 'A'
      AND EFFDT <= SYSDATE
  )
  WHERE rnk = 1
),
loc_cur AS (
  SELECT SETID, LOCATION, DESCR
  FROM (
    SELECT SETID, LOCATION, DESCR,
           ROW_NUMBER() OVER (
             PARTITION BY SETID, LOCATION
             ORDER BY EFFDT DESC
           ) AS rnk
    FROM PS_LOCATION_TBL          -- prefer base table over PS_LOCATION_VW if equivalent
    WHERE EFF_STATUS = 'A'
      AND EFFDT <= SYSDATE
  )
  WHERE rnk = 1
),
email_cur AS (
  SELECT EMPLID, EMAIL_ADDR
  FROM (
    SELECT EMPLID, EMAIL_ADDR,
           ROW_NUMBER() OVER (
             PARTITION BY EMPLID
             ORDER BY PREF_EMAIL_FLAG DESC
           ) AS rnk
    FROM PS_EMAIL_ADDRESSES
    WHERE PREF_EMAIL_FLAG = 'Y'
  )
  WHERE rnk = 1
)
SELECT
    A.EMPLID,
    NVL(N.NAME, ' ')                    AS NAME,
    A.EFFDT,
    A.DEPTID,
    B.DESCR                             AS DEPTDESCR,
    A.JOBCODE,
    C.DESCR                             AS JOBDESCR,
    A.HR_STATUS                         AS HRSTATUS,
    A.BUSINESS_UNIT                     AS BUID,
    E.DESCR                             AS BUDESCR,
    PD.POSITION_NBR                     AS POSITION,
    PD.DESCR                            AS POSDESCR,
    A.SUPERVISOR_ID,
    A.REPORTS_TO,
    A.COMPANY,
    PSC.DESCR                           AS COMPANYDESCR,
    A.ESTABID,
    CAST(NULL AS VARCHAR2(50))          AS ESTABDESCR,
    A.REG_REGION,
    RR.DESCR50                          AS REGDESCR,
    A.LOCATION,
    LOC.DESCR                           AS LOCATIONDESCR,
    NVL(EA.EMAIL_ADDR, A.EMPLID || '@example.com') AS EMAIL
FROM job_cur A
LEFT JOIN names_cur        N   ON N.EMPLID = A.EMPLID
LEFT JOIN PS_BUSUNIT_HR_VW E   ON E.BUSINESS_UNIT = A.BUSINESS_UNIT
LEFT JOIN PS_REG_REGION_TBL RR ON RR.REG_REGION = A.REG_REGION
LEFT JOIN dept_cur         B   ON B.SETID = A.SETID_DEPT
                              AND B.DEPTID = A.DEPTID
LEFT JOIN jobc_cur         C   ON C.SETID = A.SETID_JOBCODE
                              AND C.JOBCODE = A.JOBCODE
LEFT JOIN pos_cur          PD  ON PD.POSITION_NBR = A.POSITION_NBR
LEFT JOIN co_cur           PSC ON PSC.COMPANY = A.COMPANY
LEFT JOIN loc_cur          LOC ON LOC.SETID = A.SETID_LOCATION
                              AND LOC.LOCATION = A.LOCATION
LEFT JOIN email_cur        EA  ON EA.EMPLID = A.EMPLID
WHERE A.rnk = 1;