HCM Cloud – Time in Job, Time in Position Query

This query in Oracle HCM Cloud provides information on Employees and how much time they have been in a certain job or in a certain Position.

As you can see in the below screenshot from SQLConnect, this query provides Time in Job and Time in a particular position in YEARS. Copy this query and use it in SQLConnect – Get the data you want without even logging into BI tools for Oracle HCM cloud.

You can remove a few conditions from this query, if you want the time to be calculated even across global transfers/rehires.

SELECT papf.person_number
,paam.assignment_number
,paam.assignment_status_type
,pjfv.name Job_Name
,hapfv.name Position_Name
,round((
months_between(sysdate, (
SELECT MIN(paam1.effective_start_date)
FROM per_all_assignments_m paam1
WHERE paam1.person_id = paam.person_id
AND paam1.assignment_id = paam.assignment_id /* Remove this if you want the time to be calculated even across global transfers/rehires */
AND paam1.assignment_type NOT IN ('ET','CT','PT')
AND paam1.job_id = paam.job_id
AND NOT EXISTS (
SELECT 'x'
FROM per_all_assignments_m paam2
WHERE paam2.person_id = paam.person_id
AND paam2.assignment_id = paam.assignment_id /* Remove this if you want the time to be calculated even across global transfers/rehires */
AND paam2.job_id <> paam1.job_id
AND paam2.assignment_type NOT IN ('ET','CT','PT')
AND paam2.effective_start_date > paam1.effective_start_date
)
)) / 12
), 2) time_in_job_years
,round((
months_between(sysdate, (
SELECT MIN(paam1.effective_start_date)
FROM per_all_assignments_m paam1
WHERE paam1.person_id = paam.person_id
AND paam1.assignment_id = paam.assignment_id /* Remove this if you want the time to be calculated even across global transfers/rehires */
AND paam1.assignment_type NOT IN ('ET','CT','PT')
AND paam1.position_id = paam.position_id
AND NOT EXISTS (
SELECT 'x'
FROM per_all_assignments_m paam2
WHERE paam2.person_id = paam.person_id
AND paam2.assignment_id = paam.assignment_id /* Remove this if you want the time to be calculated even across global transfers/rehires */
AND paam2.position_id <> paam1.position_id
AND paam2.assignment_type NOT IN ('ET','CT','PT')
AND paam2.effective_start_date > paam1.effective_start_date
)
)) / 12
), 2) time_in_position_years
FROM per_all_assignments_m paam
,per_all_people_f papf
,HR_ALL_POSITIONS_F_VL hapfv
,PER_JOBS_F_VL pjfv
WHERE papf.person_id = paam.person_id
AND paam.assignment_type NOT IN ('ET','CT','PT')
AND paam.assignment_status_type = 'ACTIVE'
AND paam.primary_flag = 'Y'
AND paam.effective_latest_change = 'Y'
AND paam.position_id = hapfv.position_id(+)
AND paam.job_id = pjfv.job_id(+)
AND sysdate BETWEEN pjfv.effective_start_date(+)
AND pjfv.effective_end_date(+)
AND sysdate BETWEEN hapfv.effective_start_date(+)
AND hapfv.effective_end_date(+)
AND sysdate BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND sysdate BETWEEN paam.effective_start_date
AND paam.effective_end_date
Hello there
Request A Quote
Ready to Upgrade Your SQL Workflow?
Hello there
Looking for a partner to streamline your data tasks? SQL Connect is here to pull data from multiple Oracle ERP/SCM/HCM Pods. Run ad hoc queries, extract data to CSV or Excel, and make the most of your data. Discover the ease and efficiency of SQL Connect today!
Hello there
Select your version
Hello there
Would you like to view the installation guide?
Hello there
Step 1. Run the downloaded file to install SQL Connect on your computer.

Step 2: Register for Access
If you don’t have a username and password:
  • Click the “SIGN-UP” button on the login screen.
  • Complete the registration form.
  • Check your email for your login credentials.
installation guide
Step 3. First Time Launch
Hello there
Open SQL Connect:
  • For Windows: Go to Start Menu -> Programs -> SQL Connect -> SQL Connect.
  • For Mac: Open Applications and find SQL Connect.
    Logging In:
    • Enter the username and password provided by your administrator or received via email.
    • Note: Trial credentials expire after 15 days.
      Step 4. Creating a New Oracle Connection
      Hello there
      1. Open the Create Connection window in the application.
      2. Enter the following details:
        • Oracle Connection Name: Choose a name for this connection.
        • Oracle Cloud Username: Your user ID for Oracle Cloud.
        • Password: Your Oracle Cloud password. Use the EYE icon to show/hide the password.
        • URL: The web address for your Oracle pod, e.g., 'http://EEEE.us6.oraclecloud.oracle.com'
      3. Click "NEXT" to test and save the connection. If there's an error, check the details or contact your system administrator.
      Step 5. Using SQL connect
      Hello there
      To connect to a database, select “Connect To” and pick your pod from the dropdown list.

      For any issues, refer to the FAQ or contact your administrator. If you are using an enterprise license, please get assistance from your company’s administrator.
      Hello there
      Step 1. Run the downloaded file to install SQL Connect on your computer.

      Step 2: Register for Access
      If you don’t have a username and password:
      • Click the “SIGN-UP” button on the login screen.
      • Complete the registration form.
      • Check your email for your login credentials.
      installation guide
      Step 2. First Time Launch
      Hello there
      Open SQL Connect:
      • For Windows: Go to Start Menu -> Programs -> SQL Connect -> SQL Connect.
      • For Mac: Open Applications and find SQL Connect.
        Logging In:
        • Enter the username and password provided by your administrator or received via email.
        • Note: Trial credentials expire after 15 days.
          Step 3. Creating a New Oracle Connection
          Hello there
          1. Open the Create Connection window in the application.
          2. Enter the following details:
            • Oracle Connection Name: Choose a name for this connection.
            • Oracle Cloud Username: Your user ID for Oracle Cloud.
            • Password: Your Oracle Cloud password. Use the EYE icon to show/hide the password.
            • URL: The web address for your Oracle pod, e.g., 'http://EEEE.us6.oraclecloud.oracle.com'
          3. Click "NEXT" to test and save the connection. If there's an error, check the details or contact your system administrator.
          Step 4. Using SQL connect
          Hello there
          To connect to a database, select “Connect To” and pick your pod from the dropdown list.

          For any issues, refer to the FAQ or contact your administrator. If you are using an enterprise license, please get assistance from your company’s administrator.