Benefits Life Events in Oracle HCM Cloud

SELECT ppln.full_name,
       ppln.display_name,
       ppl.person_number,
       paf.assignment_number,
       le_org.name le_name,
       ptnl.ptnl_ler_for_per_id,
       pil.per_in_ler_id,
       ler.name    ler,
       ptnl.ptnl_ler_for_per_stat_cd ptnl_stat,
       pil.per_in_ler_stat_cd   pil_stat,
       ptnl.lf_evt_ocrd_dt,
       ptnl.dtctd_dt,
       ptnl.ntfn_dt,
       ptnl.voidd_dt,
       ptnl.procd_dt,
       ppos.date_start               hire_date,
       ptnl.creation_date,
       ptnl.created_by,
       ptnl.last_update_date,
       ptnl.last_updated_by,
       pil.creation_date             pil_creation_date,
       pil.created_by                pil_created_by,
       pil.last_update_date          pil_last_update_date,
       pil.last_updated_by           pil_last_updated_by
FROM   ben_ptnl_ler_for_per ptnl,
       ben_per_in_ler pil,
       ben_ler_f_vl ler,
       per_all_assignments_f paf,
       per_all_people_f ppl,
       hr_all_organization_units org,
       per_jobs jobs,
       per_person_names_f_v ppln,
       ben_benefit_relations_f brn,
       per_periods_of_service ppos,
       hr_all_organization_units le_org
WHERE  ptnl.person_id = ppl.person_id
       AND Trunc (SYSDATE) BETWEEN ppl.effective_start_date AND ppl.effective_end_date
       AND ler.ler_id = ptnl.ler_id
       AND ptnl.benefit_relation_id = brn.benefit_relation_id
       AND ptnl.ptnl_ler_for_per_id = pil.ptnl_ler_for_per_id (+)
       AND brn.legal_entity_id = le_org.organization_id (+)
       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 ler.effective_start_date AND ler.effective_end_date
       AND Trunc (SYSDATE) BETWEEN ppln.effective_start_date AND ppln.effective_end_date
       AND Trunc(SYSDATE) BETWEEN brn.effective_start_date AND brn.effective_end_date
       AND brn.rel_prmry_asg_id = paf.assignment_id
       AND ppl.person_id = paf.person_id
       AND paf.organization_id = org.organization_id (+)
       AND paf.job_id = jobs.job_id(+)
       AND paf.period_of_service_id = ppos.period_of_service_id
       AND ppl.person_id = ppln.person_id (+)
ORDER  BY ppl.person_number,
          ptnl.creation_date DESC;  

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.