Person and Assignment in Oracle HCM Cloud

 

— Person & Assignment Information
SELECT ppl.person_number,
       ppl.person_id,
       ppln.display_name,
       pps.date_start                                               hire_date,
       pps.actual_termination_date                                  term_date,
       paf.assignment_type,
       paf.assignment_number,
       paf.asg_information1                                         aca_ft,
       paf.action_code,
       paf.effective_start_date                                     paf_effective_start,
       paf.effective_end_date                                       paf_effective_end,
       paf.primary_flag,
       pni.national_identifier_number,
       le.name                                                      le_name,
       rep_est.name                                                 reporting_establishment,
       ppln_sup.full_name                                           supervisor_name,
       pps.worker_number,
       ppos_sup.worker_number                                       supervisor_worker_number,
       paf_sup.assignment_number                                    supervisor_asg,
       ppl_sup.person_number                                        supervisor_person_num,
       pps.adjusted_svc_date,
       org.name                                                     org_name,
       jobs.name                                                    job_name,
       paf.assignment_id,
       paf.assignment_name,
       paf.employee_category,
       paf.employment_category,
       hr_general.Decode_lookup(‘EMP_CAT’, paf.employment_category) asg_category,
       past.user_status                                             assignment_status,
       paf.assignment_status_type_id,
       To_char(pp.date_of_birth, ‘MM/DD/YYYY’)                      emp_dob
FROM   per_all_assignments_m paf,
       hr_all_organization_units org,
       per_all_people_f ppl,
       per_person_names_f_v ppln,
       per_jobs jobs,
       per_national_identifiers pni,
       hr_all_organization_units_vl rep_est,
       per_persons pp,
       per_assignment_status_types_vl past,
       per_periods_of_service pps,
       per_assignment_supervisors_f sup,
       per_all_assignments_f paf_sup,
       per_all_people_f ppl_sup,
       per_person_names_f_v ppln_sup,
       per_periods_of_service ppos_sup,
       per_legal_employers le
WHERE  1 = 1
       AND Trunc(SYSDATE) BETWEEN paf.effective_start_date (+) AND paf.effective_end_date (+)
       AND Trunc(SYSDATE) BETWEEN ppl.effective_start_date AND ppl.effective_end_date
       AND Trunc(SYSDATE) BETWEEN ppln.effective_start_date (+) AND ppln.effective_end_date (+)
       AND ppl.person_id = pp.person_id (+)
       AND ppl.person_id = paf.person_id(+)
       AND ppl.person_id = ppln.person_id (+)
       AND paf.job_id = jobs.job_id(+)
       AND paf.organization_id = org.organization_id (+)
       AND paf.assignment_status_type_id = past.assignment_status_type_id (+)
       AND ppl.person_id = pni.person_id (+)
       AND paf.assignment_type = ‘E’
       AND pni.national_identifier_type (+) = ‘SSN’
       AND paf.assignment_id = sup.assignment_id (+)
       AND sup.manager_assignment_id = paf_sup.assignment_id(+)
       AND paf_sup.person_id = ppl_sup.person_id(+)
       AND ppl_sup.person_id = ppln_sup.person_id(+)
       AND paf_sup.period_of_service_id = ppos_sup.period_of_service_id (+)
       AND Trunc(SYSDATE) BETWEEN sup.effective_start_date (+) AND sup.effective_end_date(+)
       AND Trunc(SYSDATE) BETWEEN paf_sup.effective_start_date (+) AND paf_sup.effective_end_date(+)
       AND Trunc(SYSDATE) BETWEEN ppl_sup.effective_start_date(+) AND ppl_sup.effective_end_date(+)
       AND Trunc(SYSDATE) BETWEEN ppln_sup.effective_start_date (+) AND
                                  ppln_sup.effective_end_date(+)
       AND Trunc(SYSDATE) BETWEEN le.effective_start_date (+) AND le.effective_end_date(+)
       AND paf.legal_entity_id = le.organization_id (+)
       AND le.status = ‘A’
       AND past.user_status NOT LIKE ‘%In%ctive%’
       AND paf.establishment_id = rep_est.organization_id (+)
       AND 1 = 1
ORDER  BY pps.date_start ASC,
          paf.last_update_date DESC,
          paf.effective_start_date ASC; 

 

Other Sample Queries

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.