Oracle Fusion hcm queries

Oracle Fusion hcm queries

In this post , We will be discuss about Oracle Fusion hcm queries. We will be share the most important Oracle Fusion hcm queries which do uses in day to day operations support in fusion HCM. This is one of the useful HCM query uses in fusion hcm , which commonly used in oracle fusion. We do use this hcm query to extract the data from Oracle fusion and to develop the custom BIP reports in oracle fusion. Here below is the Oracle Fusion hcm queries.

Oracle Fusion hcm queries

9 Most Important tables used by Oracle Fusion hcm queries

1.per_all_people_f
2.per_all_assignments_m

3.per_jobs_f

4.per_jobs_f_tl
5.GL_CODE_COMBINATIONS
6.PER_EMAIL_ADDRESSES
7.per_assignment_supervisors_f
8.per_person_names_f
9.HR_ORGANIZATION_UNITS_F_TL

Detail Oracle Fusion hcm queries used to extract the Data from Oracle Fusion

SELECT papf.person_number EMPLOYEE_NUMBER,
   pjft.name    jobname,
PAAM.ASSIGNMENT_NUMBER EMP_ASSIGNMENT_NUMBER,
gcc.segment1||'-'||gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||gcc.segment5||'-'||gcc.segment6 EXP_ACCOUNT,
PAAM.EFFECTIVE_START_DATE ASSG_EFFECTIVE_START_DATE,
PEA.EMAIL_ADDRESS,
   ppnf.full_name supervisor_name,
 papf2.person_number sUPERVISOR_NUMBER,
hauft.NAME BusinessUnit,
PAPF.START_DATE HIRE_DATE
FROM   per_all_people_f papf,
 per_all_people_f papf2,
       per_all_assignments_m paam,
       per_jobs_f pjf,
   per_jobs_f_tl pjft,
GL_CODE_COMBINATIONS GCC,
PER_EMAIL_ADDRESSES PEA,
 per_assignment_supervisors_f pasf,
  per_person_names_f ppnf,
HR_ORGANIZATION_UNITS_F_TL hauft
WHERE  papf.person_id = paam.person_id

AND    TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date

AND    paam.primary_assignment_flag = 'Y'

AND    paam.assignment_type = 'E'

and    paam.effective_latest_change = 'Y'
and paam.DEFAULT_CODE_COMB_ID=gcc.code_combination_id

AND    TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date

AND    paam.job_id = pjf.job_id

AND    TRUNC(SYSDATE) BETWEEN pjf.effective_start_date AND pjf.effective_end_date
AND PEA.EMAIL_ADDRESS_ID=PAPF.PRIMARY_EMAIL_ID
AND    papf.person_id = pasf.person_id

AND    pasf.manager_type = 'LINE_MANAGER'

AND    ppnf.person_id = pasf.manager_id

AND  papf2.person_id = pasf.manager_id

AND    TRUNC(SYSDATE) BETWEEN papf2.effective_start_date AND papf2.effective_end_date

AND    TRUNC(SYSDATE) BETWEEN pasf.effective_start_date AND pasf.effective_end_date

AND    TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date

AND    pjf.job_id = pjft.job_id

AND hauft.NAME=nvl(:P_BU_NAME,hauft.NAME)

AND    pjft.language = 'US'
AND    ppnf.name_type = 'GLOBAL'

AND    TRUNC(SYSDATE) BETWEEN pjft.effective_start_date AND pjft.effective_end_date
AND hauft.organization_id = paam.business_unit_id

order by papf.person_number asc,pjft.name asc nulls first

Oracle Fusion hcm queries

Oracle Fusion hcm queries