Oracle Fusion HCM Cloud/EBS (HCM, Financial): ORACLE HCM Technical

Legal Entity ( Soft Coding KeyFlex Field ) on HRMS

Query Soft Coding KeyFlex Field
Query to extract Employee Contact Information

SELECT DISTINCT * FROM
(SELECT   CONNECT_BY_ROOT
           hap.NAME parent_name, pse.parent_position_id, has.NAME child_name,
           pse.subordinate_position_id, LPAD (' ', 5 * LEVEL) || has.NAME HIERARCHY, LEVEL
      FROM (SELECT NAME, position_id
              FROM hr_all_positions_f_tl
             WHERE LANGUAGE = USERENV ('LANG')) hap,
           (SELECT NAME, position_id
              FROM hr_all_positions_f_tl
             WHERE LANGUAGE = USERENV ('LANG')) has,
           per_pos_structure_elements pse
     WHERE pse.business_group_id = 81
       AND hap.position_id = pse.parent_position_id
       AND has.position_id = pse.subordinate_position_id
--START WITH pse.parent_position_id = 3142--–40979   --Base Position Id (like CEO etc)
CONNECT BY PRIOR pse.parent_position_id = pse.subordinate_position_id
--       AND PRIOR pse.pos_structure_version_id = pse.pos_structure_version_id
       AND PRIOR pse.business_group_id = pse.business_group_id
ORDER BY 2
)

----------------(ELEMENT QUERY)
SELECT pet.element_name, pet.reporting_name, pet.description,
       pec.classification_name, pet.processing_type, pel.costable_type,

--       pel.cost_allocation_keyflex_id,
       cost_pcak.concatenated_segments cost_allocation,

--       pel.balancing_keyflex_id,
       bal_pcak.concatenated_segments balance_allocation,
       pet.post_termination_rule, pet.processing_priority, piv.NAME, piv.uom,
       piv.display_sequence,
       DECODE (mandatory_flag, 'Y', 'Y', 'N') mandatory_flag_name,
       DECODE (mandatory_flag, 'X', 'N', 'Y') user_enterable_flag,
       piv.generate_db_items_flag, piv.hot_default_flag
FROM pay_element_types_f pet,
       pay_element_links_f pel,
       pay_input_values_f piv,
       pay_element_classifications pec,
       pay_cost_allocation_keyflex cost_pcak,
       pay_cost_allocation_keyflex bal_pcak
WHERE pet.business_group_id = :p_business_group_id
   AND SYSDATE BETWEEN pet.effective_start_date AND pet.effective_end_date
   AND pet.element_type_id = pel.element_type_id
   AND SYSDATE BETWEEN pel.effective_start_date AND pel.effective_end_date
   AND piv.element_type_id = pet.element_type_id
   AND SYSDATE BETWEEN piv.effective_start_date AND piv.effective_end_date
   AND pet.classification_id = pec.classification_id
   --
   AND pel.cost_allocation_keyflex_id = cost_pcak.cost_allocation_keyflex_id(+)
   AND pel.balancing_keyflex_id = bal_pcak.cost_allocation_keyflex_id(+)
-- AND pet.element_name = 'BASIC'

PAYROLL Queries

Oracle Payroll Queries

Oracle Payroll Actions : Query to identify all payroll actions

Need Pay Run results Query in R12

Queries for costing in Payroll

Query to find Payroll details posted to GL in R12
Oracle HR and Payroll Related Queries

HRMS Security and Security Group

Payroll Query

SELECT ppa.payroll_action_id, ppf.person_id, ppf.full_name,

paf.assignment_id, paf.payroll_id, pay_f.payroll_name,
ptp.period_name, ptp.period_num, pay_f.period_type, pet.element_name,
pet.processing_type, piv.NAME input_name, piv.uom, piv.DEFAULT_VALUE,
ppa.action_type, prr.start_date, prr.end_date, prr.status,
prrv.result_value
FROM per_people_f ppf,
per_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_payrolls_f pay_f,
per_time_periods ptp,
pay_run_results prr,
pay_run_result_values prrv,
pay_element_types_f pet,
pay_input_values_f piv
WHERE 1 = 1
AND ppf.person_id = paf.person_id
AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
--
AND paf.assignment_id = paa.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.payroll_id = pay_f.payroll_id
AND pay_f.payroll_id = ppa.payroll_id
AND ptp.time_period_id = ppa.time_period_id
AND pay_f.payroll_id = ptp.payroll_id
AND TRUNC (SYSDATE) BETWEEN pay_f.effective_start_date AND pay_f.effective_end_date
AND prr.assignment_action_id = paa.assignment_action_id
AND prr.run_result_id = prrv.run_result_id
---
AND prr.element_type_id = pet.element_type_id
AND TRUNC (SYSDATE) BETWEEN pet.effective_start_date AND pet.effective_end_date
AND prrv.input_value_id = piv.input_value_id
AND piv.NAME = 'Pay Value'
AND TRUNC (SYSDATE) BETWEEN piv.effective_start_date AND piv.effective_end_date
----------
AND ptp.period_name = NVL (:p_period_name, ptp.period_name)
AND ppf.person_id = NVL (:p_person_id, ppf.person_id)
AND paa.payroll_action_id =
NVL (:p_payroll_action_id, paa.payroll_action_id)
--
ORDER BY ppf.person_id, prr.start_date, prr.end_date, pet.element_name

Oracle Performance Management

Oracle Performance Management OPM: SQL Query to get an appraisal employee details
Query to retrieve basic Objectives Information in scorecards
Oracle Appraisals query
Objectives Query
Oracle Performance Management
Performance Management Module Important Query
Query to retrieve Competency Information in Appraisal

iRecruitment Manager

How to extract more that one records from xml file (Table HR_API_TRANSACTIONS column TRANSACTION_DOCUMENT)
How to get iRecruitment Vacancy details in Oracle HRMS before Vacancy is approved.
Query to fetch applicants been hired as employees

SELECT pvv.position_name,
papf.title || ' ' || papf.full_name full_name, io.offer_letter
FROM irc_offer_status_history iosh,
per_applications pa,
irc_offers io,
per_vacancies_v pvv,
per_all_assignments_f paaf,
per_all_people_f papf
WHERE iosh.offer_id = io.offer_id
AND io.vacancy_id = pvv.vacancy_id(+)
AND io.vacancy_id = paaf.vacancy_id
AND paaf.person_id = papf.person_id
AND pa.application_id = paaf.application_id
AND io.offer_assignment_id = paaf.assignment_id
AND papf.effective_end_date = '31-DEC-4712'
AND paaf.effective_end_date = '31-DEC-4712'
AND papf.full_name = v_appname
AND pvv.NAME = v_vacancy_id;

Learning Management

Query to Get OLM delegated Employee Information

-----------------------------------------------------------------------------

SELECT ppmp.plan_id, ppmp.plan_name, pps.scorecard_id, pps.scorecard_name,
       ppf.full_name employee_name, ppfs.full_name manager, po.group_code,
       po.NAME object_name, po.measure_name, po.target_date, po.uom_code,
       TO_CHAR (ppmp.start_date, 'YYYY') ayear,
       hr_general.decode_job (paf.job_id) job_name,
       hr_general.decode_organization (paf.organization_id) department,
       pa.appraisal_period_start_date, pa.appraisal_period_end_date,
       pap.appraisal_type, pa.status, pa.comments
FROM per_perf_mgmt_plans ppmp,
       per_personal_scorecards pps,
       per_objectives po,
       per_people_f ppf,
       per_assignments_f paf,
       per_people_f ppfs,
       ---
       per_appraisals pa,
       per_appraisal_templates pat,
       per_appraisal_periods pap
WHERE 1 = 1
   AND ppmp.plan_id = :p_plan_id
   AND pps.scorecard_id = :p_scorecard_id
   --
   AND pps.plan_id = ppmp.plan_id
   AND pps.scorecard_id = po.scorecard_id
   AND pps.person_id = ppf.person_id
   AND SYSDATE BETWEEN ppf.effective_start_date AND ppf.effective_end_date
   AND ppf.person_id = paf.person_id
   AND SYSDATE BETWEEN paf.effective_start_date AND paf.effective_end_date
   AND paf.primary_flag = 'Y'
   AND paf.supervisor_id = ppfs.person_id
   AND SYSDATE BETWEEN ppfs.effective_start_date AND ppfs.effective_end_date
   ---
   AND pa.appraisal_id = po.appraisal_id
   AND :p_year BETWEEN TO_CHAR (pa.appraisal_period_start_date, 'YYYY')
                   AND TO_CHAR (pa.appraisal_period_end_date, 'YYYY')
   AND pat.appraisal_template_id = pap.appraisal_template_id
   AND pap.start_date = pa.appraisal_period_start_date
   AND pap.end_date = pa.appraisal_period_end_date
   AND pap.plan_id = ppmp.plan_id

-------------------------------------------------------------------------
SELECT   appraisal_type_m, a.appraisee, c.employee_number, a.main_appraiser,
         a.department, a.appraisal_status_m appraisal_status,
         DECODE (b.appraisal_system_status,
                 'ONGOING', 'Appraisal Pending With - ' || a.main_appraiser,
                 'APPRFEEDBACK', 'Appraisal Pending With - ' || a.appraisee,
                 'SAVED', ' Appraisal Pending With - ' || a.appraisee,
                 'TRANSFER', 'Appraisal Pending With - ' || a.appraisee,
                 'COMPLETED', 'Appraisal Completed',
                 'PENDINGAPPR', 'Appraisal Pending With - '
                  || a.main_appraiser,
                 b.appraisal_system_status
                ) ownership
    FROM apps.hrfv_appraisal_details a, per_appraisals b, per_all_people_f c
   WHERE c.person_id = a.appraisee_person_id
     AND a.appraisal_id = b.appraisal_id
     AND a.appraisal_id IN (SELECT   MAX (e.appraisal_id)
                                FROM per_appraisals e
                               WHERE e.plan_id IS NULL
                            GROUP BY e.appraisee_person_id
                              HAVING COUNT (*) >= 1)
     AND TRUNC (SYSDATE) BETWEEN TRUNC (c.effective_start_date)
                             AND TRUNC (c.effective_end_date)
ORDER BY a.appraise
------------------------------------------------------------------------
SELECT po.NAME objective_name, hrlookup.meaning uom, po.attribute1 base,
       po.attribute2 stretch, po.attribute3 actual,
       po.weighting_percent weighting,
       (SELECT ppr.comments
          FROM per_performance_ratings ppr
         WHERE ppr.person_id = po.owning_person_id
           AND ppr.objective_id = po.objective_id) emp_self_assessment,
       (SELECT DISTINCT ppr2.comments
                   FROM per_performance_ratings ppr2
                  WHERE ppr2.person_id =
                           (SELECT supervisor_id
                              FROM per_assignments_f
                             WHERE person_id =
                                       po.owning_person_id
                               AND TRUNC (SYSDATE) BETWEEN effective_start_date
                                                       AND effective_end_date
                               AND primary_flag = 'Y')
                    AND ppr2.objective_id = po.objective_id)
                                                           objective_comments
FROM per_appraisals pa,
       per_perf_mgmt_plans ppmp,
       per_appraisal_templates pat,
       per_appraisal_periods pap,
       per_business_groups pbg,
       per_objectives po,
       hr_standard_lookups hrlookup
WHERE 1 = 1
   AND ppmp.plan_id = :p_plan_id
   AND pap.appraisal_period_id = :p_period_id
   AND pa.plan_id = ppmp.plan_id
   AND pa.appraisal_template_id = pat.appraisal_template_id
   AND pap.plan_id = ppmp.plan_id
   AND pap.appraisal_template_id = pat.appraisal_template_id
   AND pa.appraisal_period_start_date = pap.start_date
   AND pa.appraisal_period_end_date = pap.end_date
   AND po.appraisal_id = pa.appraisal_id
   AND pa.business_group_id = pbg.business_group_id
   AND po.owning_person_id = :p_person_id
   AND hrlookup.lookup_type(+) = 'HR_WPM_MEASURE_UOM'
   AND hrlookup.lookup_code(+) = po.uom_code
   AND hrlookup.enabled_flag(+) = 'Y'
-------------------------------------------------------

SELECT pap.person_id, pap.full_name appraiser, pc.NAME compentency,
       NVL2 (prl1.NAME,
             prl1.step_value || '-' || prl1.NAME,
             prl1.step_value
            ) AS emp_prof_level,
       pce.comments, prl1.step_value
FROM per_competence_elements pce,
       per_rating_levels_vl prl1,
       per_rating_levels_vl prl2,
       per_rating_levels_vl prl3,
       per_all_people_f pap,
       per_competences pc
WHERE pce.proficiency_level_id = prl1.rating_level_id(+)
   AND pce.rating_level_id = prl2.rating_level_id(+)
   AND pce.weighting_level_id = prl3.rating_level_id(+)
   AND object_id = pap.person_id
   AND pc.competence_id = pce.competence_id
   AND TRUNC (SYSDATE) BETWEEN pap.effective_start_date AND effective_end_date
   AND pce.object_name = 'ASSESSOR_ID'
   AND pce.object_id IN (
          SELECT perp.person_id
            FROM per_participants perp, per_assessments pass
           WHERE perp.participation_in_id = pass.appraisal_id
             AND perp.participation_in_table = 'PER_APPRAISALS'
             AND perp.participation_in_column = 'APPRAISAL_ID'
             AND pass.assessment_id = pce.assessment_id
             AND pass.appraisal_id = 74515
                                          ---(Appraisal id receieved from previous query)
       );
------------------------------------------------------------------------------------------
SELECT
papf.full_name employee_name,
papf1.full_name supervisor_name,
hr_general.decode_job(paaf.job_id) job_name,
hr_general.decode_organization(paaf.organization_id) department,
pc.name,pce.comments EmployeeComments,
(select pce1.comments from per_competence_elements pce1
   where
        pce.assessment_id = pce1.assessment_id
   AND pce.competence_id = pce1.competence_id
   AND pce1.object_id = pce.object_id)ManagerComments
FROM per_all_people_f papf,
per_all_people_f papf1,
per_all_assignments_f paaf,
per_appraisals pa,
per_appraisal_templates pat,
per_assessments pas,
per_competence_elements pce,
per_competences pc
WHERE papf.person_id   = paaf.person_id
AND paaf.supervisor_id = papf1.person_id
AND paaf.primary_flag = 'Y'
AND pa.appraisee_person_id = papf.person_id
AND pa.appraisal_template_id = pat.appraisal_template_id
AND pa.appraisal_id = pas.appraisal_id
AND pat.assessment_type_id = pas.assessment_type_id
AND pas.assessment_id = pce.assessment_id
AND pce.object_id = papf.person_id
AND pce.competence_id = pc.competence_id
AND TRUNC(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND TRUNC(sysdate) BETWEEN papf1.effective_start_date AND papf1.effective_end_date
AND TRUNC(sysdate) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND papf.employee_number= :p_employee_number
AND pa.appraisal_date   = :p_appraisal_date
AND papf.business_group_id=:p_bg_id
--------------------------------------------------------------------------------

In per_competence_elements table we have column named PARENT_COMPETENCE_ELEMENT_ID

select pc_child.name
      ,pc_parent.name
from   per_competence_elements   pce_child
      ,per_competences           pc_child
      ,per_competence_elements   pce_parent
      ,per_competences           pc_parent
where pce_child.parent_competence_element_id = pce_parent.competence_element_id
and    pc_child.competence_id                 = pce_child.competence_id
and    pc_parent.competence_id                = pce_parent.competence_id

HR_API_TRANSACTION

select status
from hr_api_transactions
where product_code = 'IRC'
group by status;

SOLUTION

Statuses in the hr_api_transactions table:

W - Review page
D - Deleted
Y - Pending Approval
E - Error
S - Save for Later

Additional notes:

You start creating a vacancy and you navigate to step 2. The status at this point will be 'W.'

Now suppose you save for later a vacancy, the status will become 'S.' If you close the window
abruptly, the status will remain 'W.' Both the 'W' and 'S' transactions will find their place in
save for later table.

If you save for later a vacancy, the status will be 'S.' Now suppose you delete this vacancy from
the save for later table. The status will be changed to 'D.' You can delete transactions belonging to
different statuses using "Complete Defunct HR Workflow" concurrent process.

4 comments:

Quenataucus Corner22 October 2019 at 12:31
Because if your company is extremely successful it will grow, and it'll grow rapidly. To get more detailed info on payslip system, visit on hyperlinked site.
megan collins17 November 2020 at 12:40
What an informative blog it was. It had such relevant and interesting points that actually enhanced my knowledge and cleared my doubts. Thankyou for this blog, it was very useful. Here is a referred link same as yours oracle fusion hcm training.
charliesmith20 December 2021 at 15:08
Workflow software helps companies automate and manage processes. Its human-oriented and automated features enable the creation of custom dashboards, dynamic procedure documents, and a variety of business processes. Get more interesting details about simple workflow software on this site.
Ajmal Amir13 May 2022 at 11:17
Great article, I hope that you will going to post another one.
employment verification

Oracle Fusion HCM Cloud/EBS (HCM, Financial)

ORACLE HCM Technical

Interview Questions

Form Personalization

HRMS APIs

HRMS API User Hooks

Approval in EIT/SIT/Document of Records (SSHR)

Document of Records (DOR)

Organization API

Job API

Contact API

Salary API

Element Entry API with Assignment

Document of Records

Performance Management

USER TABLES/COLUMN/COLUMN INSTANCES

HRMS Queries