Query to get payroll process information in Fusion HCM

 select 
  to_char(ppa.last_update_date, 'yyyy-mm-dd hh24:mi') run_date, 
  to_char(ppa.effective_date, 'yyyy-mm-dd') effective_date, 
  hl_actiontype.meaning process_type_name, 
  decode(ppa.action_type
         ,'XRD'
         ,(select rtrim(listagg(pra.action_status || '-' || count(*) || ',') WITHIN GROUP (ORDER BY pra.action_status), ',')
             from pay_temp_object_actions pra where pra.payroll_action_id = ppa.payroll_action_id group by pra.action_status)
         ,'XWr'
         ,(select rtrim(listagg(pra.action_status || '-' || count(*) || ',') WITHIN GROUP (ORDER BY pra.action_status), ',')
             from pay_temp_object_actions pra where pra.payroll_action_id = ppa.payroll_action_id group by pra.action_status)
         ,(select rtrim(listagg(pra.action_status || '-' || count(*) || ',') within group (order by pra.action_status), ',')
             from pay_payroll_rel_actions pra where pra.source_action_id is null and pra.payroll_action_id = ppa.payroll_action_id group by pra.action_status)) pra_status,
  ppa.action_type process_type_code, 
  pfi.instance_name, 
  prc.base_category_name task_base_name, 
  prc.category_name task_name, 
  ldg.name ldg_name, 
  pap.payroll_name, 
  pcs.consolidation_set_name,
  prt_ppa.run_type_name, 
  ppa.payroll_action_id, 
  ppa.action_status process_status_code, 
  hl_actionstat.meaning process_status_meaning,
  to_char(ppa.start_date, 'yyyy-mm-dd') start_date, 
  to_char(ppa.date_earned, 'yyyy-mm-dd') date_earned,
  to_char(ptp.start_date, 'yyyy-mm-dd') period_Start_date, 
  to_char(ptp.end_date, 'yyyy-mm-dd') period_end_date, 
  ptp.period_name period_name, papg.action_param_group_name, 
  ppa.element_Set_id, 
  pog_ele.object_group_name element_set_name, 
  ppa.assignment_set_id, 
  pog_asg.object_group_name assignment_set_name, 
  ppa.payment_type_id, ppt.payment_type_name, 
  ppa.parent_org_pay_method_id, popm.org_payment_method_name, 
  ppa.start_cheque_number, 
  ppa.overriding_dd_date,
  ppa.last_update_date,
  ppa.legislative_parameters leg_params
from
  pay_payroll_actions ppa, 
  pay_all_payrolls_f pap, 
  pay_consolidation_sets pcs, 
  pay_object_groups_vl pog_ele,
  pay_object_groups_vl pog_asg,
  pay_report_categories_vl prc, 
  per_legislative_data_groups_vl ldg, 
  pay_requests pr, pay_flow_instances pfi, 
  pay_run_types_vl prt_ppa,
  pay_time_periods ptp, hr_lookups hl_actiontype, hr_lookups hl_actionstat,
  pay_action_param_groups papg, pay_payment_types_vl ppt, pay_org_pay_methods_vl popm
where ppa.payroll_id = pap.payroll_id (+) 
   and ppa.effective_date between pap.effective_start_date(+) and pap.effective_end_date(+)
   and ppa.run_type_id = prt_ppa.run_type_id (+) and ppa.consolidation_set_id = pcs.consolidation_set_id(+)
   and ppa.report_category_id = prc.report_category_id(+)
   and ppa.action_parameter_group_id = papg.action_param_group_id(+)
   and ppa.element_set_id = pog_ele.object_group_id(+)
   and ppa.effective_date between pog_ele.start_date(+) and pog_ele.end_date(+)
   and ppa.assignment_set_id = pog_asg.object_group_id(+)
   and ppa.effective_date between pog_asg.start_date(+) and pog_asg.end_date(+)
   and ppa.payment_type_id = ppt.payment_type_id(+)
   and ppa.org_payment_method_id = popm.org_payment_method_id(+) 
   and ppa.effective_date between popm.effective_start_date(+) and popm.effective_end_date(+)
   and ldg.legislative_data_group_id = ppa.legislative_data_group_id
   and ppa.earn_time_period_id = ptp.time_period_id(+)
   and ppa.action_type = hl_actiontype.lookup_code 
   and hl_actiontype.lookup_type = 'ACTION_TYPE'
   and ppa.action_status = hl_actionstat.lookup_code 
   and hl_actionstat.lookup_type = 'PAY_ACTION_STATUS'
   and ldg.name like :LDGName
   and (hl_actiontype.meaning in (:ProcessTypeName) or :ProcessTypeName is null)
   and (pap.payroll_name in (:PayrollName) or :PayrollName is null)
   and (prt_ppa.run_type_name in (:PayrollRunTypeName) or :PayrollRunTypeName is null)
   and (pfi.instance_name in (:FlowName) or :FlowName is null)
   and (prc.category_name in (:TaskName) or :TaskName is null)
   and trunc(ppa.effective_date) between nvl(:EffectiveStartDate, trunc(sysdate, 'Y')) 
                              and nvl(:EffectiveEndDate, add_months(trunc(sysdate, 'Y'), 12)-1)
   and pr.pay_request_id (+) = ppa.pay_request_id
   and pfi.flow_instance_id(+) = pr.flow_instance_id

order by ppa.last_update_date desc, pap.payroll_name, ppa.effective_date