> For the complete documentation index, see [llms.txt](https://help.sentinelsoftware.com/sentinel-help-center/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://help.sentinelsoftware.com/sentinel-help-center/installation-and-updates/job-info-query.md).

# 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.

* `BUDESCRstring30`Buiness Unit Description
* `BUIDstring10`Buiness Unit ID
* `COMPANYstring12`Company ID
* `COMPANYDESCRstring30`Company Description
* `DEPTDESCRstring50`Department Name
* `DEPTIDstring10`Department ID
* `EFFDTdate`Effective Date
* `EMAILstring100`Primary Email Address
* `EMPLIDstring30`Unique EMPLID
* `ESTABDESCRstring30`Establishment Description
* `ESTABIDstring12`Establishment ID
* `HRSTATUSstring1`Job status on current date
* `JOBCODEstring50`Job Code ID
* `JOBDESCRstring50`Job Code Description
* `LOCATIONstring12`Location
* `LOCATIONDESCRstring12`Location Description
* `NAMEstring50`Person name: \<First name, Last name>
* `POSDESCRstring30`Position Description
* `POSITIONstring8`Position
* `REG_REGIONstring12`Region
* `REGDESCRstring12`Region Description
* `REPORTS_TOstring12`Reports to position ID
* `SUPERVISOR_IDstring8`Supervisor ID

{% code title="" overflow="wrap" %}

```
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;
```

{% endcode %}