HCM Extracts - BI Report not running

If you have an extract delivery option linked to the BI report, add the below 3 permissions to the BI report so that the output is generated.

Enterprise Scheduler Job Application Identity for HCM

Manage HCM Extract Definition Report

Run HCM Extracts Report

Run Results Query Oracle Payroll Cloud

 SELECT
            papf.person_number,
            ele.element_name,
piv.name,
            prrv.result_value,
prr.run_result_id
        FROM
            pay_element_types_vl     ele,
            pay_run_results          prr,
            pay_run_result_values    prrv,
            pay_input_values_vl      piv,
            pay_payroll_rel_actions  ppra,
            pay_payroll_assignments  ppasg,
            per_all_assignments_f    paaf,
            per_all_people_f         papf,
            pay_payroll_actions      ppa,
            pay_all_payrolls_f       pap,
            pay_time_periods         ptp,
            pay_assigned_payrolls_dn papd
        WHERE
                1 = 1
            AND ele.element_type_id = prr.element_type_id
            AND prr.run_result_id = prrv.run_result_id
            AND prrv.input_value_id = piv.input_value_id
            AND prr.payroll_rel_action_id = ppra.payroll_rel_action_id
            AND ppra.payroll_relationship_id = ppasg.payroll_relationship_id

            AND ppasg.hr_assignment_id = paaf.assignment_id

            AND ppasg.payroll_term_id = papd.payroll_term_id

            AND paaf.person_id = papf.person_id
            AND ppra.payroll_action_id = ppa.payroll_action_id
            AND ppa.payroll_id = pap.payroll_id
            AND pap.payroll_id = ptp.payroll_id
            AND pap.payroll_id = papd.payroll_id
            AND ((ppra.run_type_id IS NOT NULL AND ppa.action_type in ('R','Q','V')) 
OR ppa.action_type in ('B'))
AND ppra.RETRO_COMPONENT_ID is NULL
            AND paaf.assignment_type = 'E'
            AND paaf.primary_flag = 'Y'
AND ele.element_name = 'Annual Salary Earnings Results'
            AND ptp.period_category = 'E'
            AND to_date('31-12-2022','dd-mm-rrrr') BETWEEN pap.effective_start_date AND pap.effective_end_date
            AND to_date('31-12-2022','dd-mm-rrrr') BETWEEN papf.effective_start_date AND papf.effective_end_date
            AND to_date('31-12-2022','dd-mm-rrrr') BETWEEN paaf.effective_start_date AND paaf.effective_end_date
            AND to_date('31-12-2022','dd-mm-rrrr') BETWEEN ele.effective_start_date AND ele.effective_end_date
            AND to_date('31-12-2022','dd-mm-rrrr') BETWEEN piv.effective_start_date AND piv.effective_end_date
            AND to_date('31-12-2022','dd-mm-rrrr') BETWEEN ptp.start_date AND ptp.end_date
            AND ppa.effective_date BETWEEN ptp.start_date AND ptp.end_date
            AND papf.person_number = '12345'

Query to get ABsence Types and Absence reasons

 select typ.name absence_type, rea.name absence_reason from 
ANC_ABSENCE_TYPES_VL typ,
ANC_ABSENCE_REASONS_VL rea,
ANC_ABSENCE_TYPE_REASONS_F typ_rea
where 1=1
and typ_rea.ABSENCE_TYPE_ID = typ.ABSENCE_TYPE_ID
and typ_rea.ABSENCE_REASON_ID = rea.ABSENCE_REASON_ID
and trunc(sysdate) between typ.effective_start_date and typ.effective_end_date
and trunc(sysdate) between rea.effective_start_date and rea.effective_end_date
and trunc(sysdate) between typ_rea.effective_start_date and typ_rea.effective_end_date
and typ.LEGISLATION_CODE='IN'
order by 1,2

ORC Report - Requisition phase, state and Selection Process

 select 
pr.name process_name,
req.requisition_number, 
pha.name phase_name ,
sta.name state_name,
cand.CANDIDATE_NUMBER,
pha_cand.name Subm_phase_name,
sta_cand.name Subm_state_name
from 
irc_requisitions_vl req,
IRC_PHASES_VL pha,
IRC_STATES_VL sta,
IRC_PROCESSES_B proc,
IRC_PROCESSES_B proc_tmp,
IRC_PROCESSES_VL pr,
IRC_SUBMISSIONS subm,
IRC_CANDIDATES cand,
IRC_PHASES_VL pha_cand,
IRC_STATES_VL sta_cand
where 1=1
and req.CURRENT_PHASE_ID = pha.phase_id
and req.CURRENT_STATE_ID = sta.state_id
and req.requisition_id = subm.requisition_id
and subm.PROCESS_ID = proc.process_id
and proc.PROCESS_TEMPLATE_ID = proc_tmp.process_id
and proc_tmp.process_id = pr.process_id
and subm.person_id = cand.person_id
and subm.CURRENT_PHASE_ID = pha_cand.phase_id
and subm.CURRENT_STATE_ID = sta_cand.state_id
and req.requisition_number='1234'
--and pr.name='Test Candidate selection process'

HCM Extracts - How to read parameter in Data Group filter

Calling a date parameter in filter - pay_report_utils.get_parameter_value_date('EFFECTIVE_DATE')

Calling a number parameter in filter - pay_report_utils.get_parameter_value_number('LEGAL_EMPLOYER_ID')

Calling a text parameter in filter - pay_report_utils.get_parameter_value('PERSON_NUMBER')

Important points to remember

How can I create a payroll relationship?

Oracle HCM Cloud automatically creates a payroll relationship when you hire an employee or create other types of person records such as candidate and pending workers.

What happens if I don't use Cloud Payroll, do I need a payroll relationship?

If you want to capture payroll related information in Oracle HCM Cloud, such as salary, compensation, and time cards, you need a payroll relationship.

How can I end a payroll relationship?

Enter a Final Close Date for a terminated employee after you have completed all payroll processing including any remaining payments and year-end balance adjustments. The Final Close Date ends the payroll relationship record and discontinues the employee to be included in payroll processes.

Enter the Final Close Date on the assignment of the terminated employee. If the employee has no other active assignments, the Final Close Date that you have entered on the assignment shows up on the payroll relationship record.

Correct Termination Date HDL

METADATA|WorkRelationship|PersonNumber|DateStart|WorkerType|LegalEmployerName|CorrectTerminationFlag|ActualTerminationDate
MERGE|WorkRelationship|12345|2021/08/12|E|US1 LE|Y|2022/09/01

CorrectTerminationFlag is passed as Y