> 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.&#x20;

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.

* **`EMPLID string30` Unique Employee ID**
* `REPORTS_TO string12` Reports to position ID
* `SUPERVISOR_ID string8` Supervisor ID
* `BUDESCR string30`  Buiness Unit Description
* `BUID string10` Buiness Unit ID
* `COMPANY string12` Company ID
* `COMPANYDESCR string30` Company Description
* `DEPTDESCR string50` Department Name
* `DEPTID string10` Department ID
* `EFFDT date` Effective Date
* `EMAIL string100` Primary Email Address
* `EMPLID string30` Unique EMPLID
* `ESTABDESCR string30` Establishment Description
* `ESTABID string12` Establishment ID
* `HRSTATUS string1` Job status on current date
* `JOBCODE string50` Job Code ID
* `JOBDESCR string50` Job Code Description
* `LOCATION string12` Location
* `LOCATIONDESCR string12` Location Description
* `NAME string50` Person name: \<First name, Last name>
* `POSDESCR string30` Position Description
* `POSITION string8` Position
* `REG_REGION string12` Region
* `REGDESCR string12` Region Description
* `ALT_EMPLID string30`  EMPLID (Optional External System ID)&#x20;

<details>

<summary>Oracle - Job Info Query</summary>

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

```

WITH
job_cur AS (
  SELECT *
  FROM (
    SELECT j.*,
           ROW_NUMBER() OVER (
             PARTITION BY j.EMPLID, j.EMPL_RCD
             ORDER BY j.EFFDT DESC, j.EFFSEQ DESC
           ) AS rcd_rnk
    FROM PS_JOB j
    WHERE j.EFFDT <= SYSDATE
  )
  WHERE rcd_rnk = 1
),
job_pick AS (
  SELECT jc.*,
         ROW_NUMBER() OVER (
           PARTITION BY jc.EMPLID
           ORDER BY CASE WHEN jc.HR_STATUS = 'A' THEN 0 ELSE 1 END,
                    jc.EMPL_RCD
         ) AS rnk
  FROM job_cur jc
),
names_cur AS (
  SELECT EMPLID, NAME
  FROM (
    SELECT EMPLID, NAME, EFF_STATUS,
           ROW_NUMBER() OVER (PARTITION BY EMPLID ORDER BY EFFDT DESC) AS rnk
    FROM PS_NAMES
    WHERE NAME_TYPE = 'PRI'
      AND EFFDT <= SYSDATE
  )
  WHERE rnk = 1 AND EFF_STATUS = 'A'
),
dept_cur AS (
  SELECT SETID, DEPTID, DESCR
  FROM (
    SELECT SETID, DEPTID, DESCR, EFF_STATUS,
           ROW_NUMBER() OVER (PARTITION BY SETID, DEPTID ORDER BY EFFDT DESC) AS rnk
    FROM PS_DEPT_TBL
    WHERE EFFDT <= SYSDATE
  )
  WHERE rnk = 1 AND EFF_STATUS = 'A'
),
jobc_cur AS (
  SELECT SETID, JOBCODE, DESCR
  FROM (
    SELECT SETID, JOBCODE, DESCR, EFF_STATUS,
           ROW_NUMBER() OVER (PARTITION BY SETID, JOBCODE ORDER BY EFFDT DESC) AS rnk
    FROM PS_JOBCODE_TBL
    WHERE EFFDT <= SYSDATE
  )
  WHERE rnk = 1 AND EFF_STATUS = 'A'
),
pos_cur AS (
  SELECT POSITION_NBR, DESCR
  FROM (
    SELECT POSITION_NBR, DESCR, EFF_STATUS,
           ROW_NUMBER() OVER (PARTITION BY POSITION_NBR ORDER BY EFFDT DESC) AS rnk
    FROM PS_POSITION_DATA
    WHERE EFFDT <= SYSDATE
  )
  WHERE rnk = 1 AND EFF_STATUS = 'A'
),
co_cur AS (
  SELECT COMPANY, DESCR
  FROM (
    SELECT COMPANY, DESCR, EFF_STATUS,
           ROW_NUMBER() OVER (PARTITION BY COMPANY ORDER BY EFFDT DESC) AS rnk
    FROM PS_COMPANY_TBL
    WHERE EFFDT <= SYSDATE
  )
  WHERE rnk = 1 AND EFF_STATUS = 'A'
),
loc_cur AS (
  SELECT SETID, LOCATION, DESCR
  FROM (
    SELECT SETID, LOCATION, DESCR, EFF_STATUS,
           ROW_NUMBER() OVER (PARTITION BY SETID, LOCATION ORDER BY EFFDT DESC) AS rnk
    FROM PS_LOCATION_TBL
    WHERE EFFDT <= SYSDATE
  )
  WHERE rnk = 1 AND EFF_STATUS = 'A'
),
estab_cur AS (
  SELECT ESTABID, DESCR
  FROM (
    SELECT ESTABID, DESCR, EFF_STATUS,
           ROW_NUMBER() OVER (PARTITION BY ESTABID ORDER BY EFFDT DESC) AS rnk
    FROM PS_ESTAB_TBL
    WHERE EFFDT <= SYSDATE
  )
  WHERE rnk = 1 AND EFF_STATUS = 'A'
),
email_cur AS (
  SELECT EMPLID, EMAIL_ADDR
  FROM (
    SELECT EMPLID, EMAIL_ADDR,
           ROW_NUMBER() OVER (
             PARTITION BY EMPLID
             ORDER BY CASE WHEN PREF_EMAIL_FLAG = 'Y' THEN 0 ELSE 1 END,
                      E_ADDR_TYPE
           ) AS rnk
    FROM PS_EMAIL_ADDRESSES
  )
  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,
    A.POSITION_NBR          AS POSITION,
    PD.DESCR                AS POSDESCR,
    A.SUPERVISOR_ID,
    A.REPORTS_TO,
    A.COMPANY,
    PSC.DESCR               AS COMPANYDESCR,
    A.ESTABID,
    ES.DESCR                AS ESTABDESCR,
    A.REG_REGION,
    RR.DESCR50              AS REGDESCR,
    A.LOCATION,
    LOC.DESCR               AS LOCATIONDESCR,
    EA.EMAIL_ADDR           AS EMAIL
FROM job_pick 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 estab_cur         ES  ON ES.ESTABID = A.ESTABID
LEFT JOIN email_cur         EA  ON EA.EMPLID = A.EMPLID
WHERE A.rnk = 1;
```

{% endcode %}

</details>

<details>

<summary>Microsoft Sql Server - Job info Query</summary>

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

```
WITH
job_cur AS (
 SELECT *
 FROM (
   SELECT j.*,
          ROW_NUMBER() OVER (
            PARTITION BY j.EMPLID, j.EMPL_RCD
            ORDER BY j.EFFDT DESC, j.EFFSEQ DESC
          ) AS rcd_rnk
   FROM PS_JOB j
   WHERE j.EFFDT <= GETDATE()
 ) x
 WHERE rcd_rnk = 1
),
job_pick AS (
 SELECT jc.*,
        ROW_NUMBER() OVER (
          PARTITION BY jc.EMPLID
          ORDER BY CASE WHEN jc.HR_STATUS = 'A' THEN 0 ELSE 1 END,
                   jc.EMPL_RCD
        ) AS rnk
 FROM job_cur jc
),
names_cur AS (
 SELECT EMPLID, NAME
 FROM (
   SELECT EMPLID, NAME, EFF_STATUS,
          ROW_NUMBER() OVER (PARTITION BY EMPLID ORDER BY EFFDT DESC) AS rnk
   FROM PS_NAMES
   WHERE NAME_TYPE = 'PRI'
     AND EFFDT <= GETDATE()
 ) x
 WHERE rnk = 1 AND EFF_STATUS = 'A'
),
dept_cur AS (
 SELECT SETID, DEPTID, DESCR
 FROM (
   SELECT SETID, DEPTID, DESCR, EFF_STATUS,
          ROW_NUMBER() OVER (PARTITION BY SETID, DEPTID ORDER BY EFFDT DESC) AS rnk
   FROM PS_DEPT_TBL
   WHERE EFFDT <= GETDATE()
 ) x
 WHERE rnk = 1 AND EFF_STATUS = 'A'
),
jobc_cur AS (
 SELECT SETID, JOBCODE, DESCR
 FROM (
   SELECT SETID, JOBCODE, DESCR, EFF_STATUS,
          ROW_NUMBER() OVER (PARTITION BY SETID, JOBCODE ORDER BY EFFDT DESC) AS rnk
   FROM PS_JOBCODE_TBL
   WHERE EFFDT <= GETDATE()
 ) x
 WHERE rnk = 1 AND EFF_STATUS = 'A'
),
pos_cur AS (
 SELECT POSITION_NBR, DESCR
 FROM (
   SELECT POSITION_NBR, DESCR, EFF_STATUS,
          ROW_NUMBER() OVER (PARTITION BY POSITION_NBR ORDER BY EFFDT DESC) AS rnk
   FROM PS_POSITION_DATA
   WHERE EFFDT <= GETDATE()
 ) x
 WHERE rnk = 1 AND EFF_STATUS = 'A'
),
co_cur AS (
 SELECT COMPANY, DESCR
 FROM (
   SELECT COMPANY, DESCR, EFF_STATUS,
          ROW_NUMBER() OVER (PARTITION BY COMPANY ORDER BY EFFDT DESC) AS rnk
   FROM PS_COMPANY_TBL
   WHERE EFFDT <= GETDATE()
 ) x
 WHERE rnk = 1 AND EFF_STATUS = 'A'
),
loc_cur AS (
 SELECT SETID, LOCATION, DESCR
 FROM (
   SELECT SETID, LOCATION, DESCR, EFF_STATUS,
          ROW_NUMBER() OVER (PARTITION BY SETID, LOCATION ORDER BY EFFDT DESC) AS rnk
   FROM PS_LOCATION_TBL
   WHERE EFFDT <= GETDATE()
 ) x
 WHERE rnk = 1 AND EFF_STATUS = 'A'
),
estab_cur AS (
 SELECT ESTABID, DESCR
 FROM (
   SELECT ESTABID, DESCR, EFF_STATUS,
          ROW_NUMBER() OVER (PARTITION BY ESTABID ORDER BY EFFDT DESC) AS rnk
   FROM PS_ESTAB_TBL
   WHERE EFFDT <= GETDATE()
 ) x
 WHERE rnk = 1 AND EFF_STATUS = 'A'
),
email_cur AS (
 SELECT EMPLID, EMAIL_ADDR
 FROM (
   SELECT EMPLID, EMAIL_ADDR,
          ROW_NUMBER() OVER (
            PARTITION BY EMPLID
            ORDER BY CASE WHEN PREF_EMAIL_FLAG = 'Y' THEN 0 ELSE 1 END,
                     E_ADDR_TYPE
          ) AS rnk
   FROM PS_EMAIL_ADDRESSES
 ) x
 WHERE rnk = 1
)
SELECT
   A.EMPLID,
   ISNULL(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,
   A.POSITION_NBR          AS POSITION,
   PD.DESCR                AS POSDESCR,
   A.SUPERVISOR_ID,
   A.REPORTS_TO,
   A.COMPANY,
   PSC.DESCR               AS COMPANYDESCR,
   A.ESTABID,
   ES.DESCR                AS ESTABDESCR,
   A.REG_REGION,
   RR.DESCR50              AS REGDESCR,
   A.LOCATION,
   LOC.DESCR               AS LOCATIONDESCR,
   EA.EMAIL_ADDR           AS EMAIL
FROM job_pick 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 estab_cur         ES  ON ES.ESTABID = A.ESTABID
LEFT JOIN email_cur         EA  ON EA.EMPLID = A.EMPLID
WHERE A.rnk = 1;
```

{% endcode %}

</details>
