HDL Lines

SELECT TEXT

FROM fusion.hrc_dl_file_lines
WHERE data_set_bus_obj_id IN
(SELECT data_set_bus_obj_id
FROM fusion.hrc_dl_data_set_bus_objs
WHERE data_set_id IN
(SELECT data_set_id
FROM fusion.hrc_dl_data_sets
WHERE ucm_content_id = 'UCMFA15238827'))
--and (TEXT like 'METADATA%' or TEXT like '%17019%')
order by SEQ_NUM

HDL errors

Select  fl.text, ml.MSG_TEXT

 from  fusion.hrc_dl_file_lines fl
     , fusion.hrc_dl_data_set_bus_objs dsbo
     , fusion.hrc_dl_file_rows fr
     , fusion.hrc_dl_physical_lines pl
     , fusion.hrc_dl_message_lines ml
where fl.data_set_bus_obj_id = dsbo.data_set_bus_obj_id
  and fr.line_id = fl.line_id
  and pl.data_set_bus_obj_id = dsbo.data_set_bus_obj_id
  and fr.row_id = pl.row_id
  and dsbo.data_set_id in (
       select data_set_id
         from fusion.hrc_dl_data_sets
        where ucm_content_id = 'UCMFA15238827'
--AND ml.MSG_TEXT like '%The values Benefits and Pensions aren't valid for the attribute DeductionGroupId%'
      )
and (ml.MESSAGE_SOURCE_LINE_ID=pl.physical_line_id OR ml.MESSAGE_SOURCE_LINE_ID=fr.logical_line_id)
order by ml.MSG_TEXT

 

Query to pull skills from Job Requisition in Oracle HCM

 SELECT irc.requisition_number
      ,hpb.profile_code
      ,hpt.description profile_desc
      ,hpt.summary,
  hpi.ITEM_TEXT240_1 Job_Requisition_Skill
  FROM HRT_PROFILE_ITEMS hpi
      ,HRT_PROFILES_B hpb
      ,HRT_PROFILES_TL hpt
      ,HRT_PROFILE_RELATIONS hpr
      ,irc_requisitions_vl irc
 WHERE hpi.profile_id = hpb.profile_id
   AND hpb.profile_usage_code = 'R'
   AND hpi.profile_id = hpr.profile_id
   AND hpi.profile_id = hpt.profile_id
   AND irc.requisition_id = hpr.object_id
   AND irc.profile_id = hpt.profile_id
  and irc.requisition_number='12345'
   AND hpt.language = 'US'

Oracle HCM Extract Schedule Query

 SELECT pfv.base_flow_name Base_flow_name
       ,
       pfi.instance_name,
       (SELECT To_char(Min(fti1.creation_date),'DD-MON-YYYY')
        FROM   pay_flow_task_instances fti1
        WHERE  pfi.root_flow_instance_id=fti1.flow_instance_id)
       FIRST_SCHEDULE_DATE,
       To_char(fti.scheduled_date,'DD-MON-YYYY')
       Current_Schedule_Date,
       fti.status
       CURRENT_SCHEDULE_STATUS,
       (SELECT Listagg(fp.parameter_name
                       || '='
                       || pv.flow_param_value,',')
                 within GROUP(ORDER BY fp.parameter_name)
        FROM   pay_flow_parameters_vl fp,
               pay_flow_param_values pv
        WHERE  1 = 1 
               AND fp.base_flow_id=pfv.base_flow_id
               AND pv.flow_instance_id=pfi.flow_instance_id
               AND fp.base_flow_parameter_id=pv.base_flow_parameter_id
               AND flow_param_value IS NOT NULL
               AND pfi.instance_name=pfi.instance_name)
       Flow_parameters_Latest_Run,
       fti.created_by
       Schedule_Created_By
FROM   pay_flow_task_instances fti,
       pay_flow_tasks_vl ft,
       pay_flow_instances pfi,
       pay_flows_vl pfv
WHERE  1 = 1 
       AND pfv.base_flow_id=pfi.base_flow_id
       AND fti.base_flow_task_id=ft.base_flow_task_id
       AND pfi.flow_instance_id=fti.flow_instance_id
       AND fti.scheduled_date IS NOT NULL
       AND fti.status='SCHEDULED'
       AND pfi.status='SCHEDULED'
ORDER  BY flow_name DESC

Element entries query

 SELECT
    'BURSTING_KEY' BUR_KEY,
    person_number,
    plan_id,
    assignment_id,
    person_id,
    start_date,
    end_date,
    SUM(ELE1)      ELE1,
    SUM(ELE2)      ELE2,
    SUM(ELE3)      ELE3,
    SUM(ELE4)      ELE4
FROM
    (
        SELECT
            papf.person_number,
            paaf.ass_attribute8                                                                       plan_id,
            paaf.assignment_id,
            paaf.person_id,
            to_char(ptp.start_date,'YYYYMMDD','NLS_DATE_LANGUAGE=AMERICAN') start_date,
to_char(ptp.end_date,'YYYYMMDD','NLS_DATE_LANGUAGE=AMERICAN') end_date,
            decode(ele.element_name, 'Ele1',(prrv.result_value), 0) ELE1,
            decode(ele.element_name, 'Ele2',(prrv.result_value), 0) ELE2,
            decode(ele.element_name, 'Ele3',(prrv.result_value), 0) ELE3,
            decode(ele.element_name, 'Ele4',(prrv.result_value), 0) ELE4
        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
        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 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 ((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 paaf.ass_attribute_category = 'CA'
            AND paaf.ass_attribute8 IS NOT NULL
            AND ((ele.element_name IN ('Element1','Element2')
            AND piv.name = 'Pay Value')
OR (ele.element_name IN ('Element3','Element4')
            AND piv.name = 'Earnings'))
            AND ptp.period_category = 'E'
            AND :p_eff_date BETWEEN pap.effective_start_date AND pap.effective_end_date
            AND :p_eff_date BETWEEN papf.effective_start_date AND papf.effective_end_date
            AND :p_eff_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
            AND :p_eff_date BETWEEN ele.effective_start_date AND ele.effective_end_date
            AND :p_eff_date BETWEEN piv.effective_start_date AND piv.effective_end_date
            AND :p_eff_date BETWEEN ptp.start_date AND ptp.end_date
            AND ppa.effective_date BETWEEN ptp.start_date AND ptp.end_date
            AND papf.person_number = nvl(:p_person_number, papf.person_number)
    )
GROUP BY
    'BURSTING_KEY',
    person_number,
    plan_id,
    assignment_id,
    person_id,
    start_date,
    end_date

HCM query to fetch LE,department, position

 SELECT PAPF.person_number
,PPNF.full_name
,To_char(PPOS_MAIN.date_start, 'DD-MON-YYYY', 'nls_date_language=American') hire_date
,dept.NAME dept_name
,pos.NAME pos_name
,HRL.NAME LE_NAME
,hc.value HEADCOUNT
,To_char(PAAF.effective_start_date, 'DD-MON-YYYY', 'nls_date_language=American') effective_start_date
,To_char(PAAF.effective_end_date, 'DD-MON-YYYY', 'nls_date_language=American') effective_end_date
,To_char(PPOS_MAIN.actual_termination_date, 'DD-MON-YYYY', 'nls_date_language=American') termination_date
,pastvl.user_status assignment_status
FROM per_all_people_f PAPF
,per_person_names_f PPNF
,per_all_assignments_f PAAF
,per_periods_of_service PPOS_MAIN
,pay_payroll_assignments PPASG
,per_assignment_status_types_vl pastvl
,per_legal_employers HRL
,HR_ALL_ORGANIZATION_UNITS_F_VL dept
,HR_ALL_POSITIONS_F_VL pos
,PER_ASSIGN_WORK_MEASURES_F_V hc
WHERE PAPF.person_id = PPNF.person_id
AND PPNF.name_type = 'GLOBAL'
AND PPOS_MAIN.date_start = (
SELECT Max(date_start)
FROM per_periods_of_service PPOS
WHERE PPOS.person_id = PAPF.person_id
)
AND PPOS_MAIN.person_id = PAPF.person_id
AND PAAF.person_id = PAPF.person_id
AND PAAF.assignment_type IN ('E')
AND PPOS_MAIN.period_of_service_id = PAAF.period_of_service_id
AND PAAF.assignment_id = PPASG.hr_assignment_id
AND PAAF.legal_entity_id = HRL.organization_id
AND HRL.STATUS = 'A'
AND paaf.organization_id = dept.organization_id
AND paaf.position_id = pos.position_id(+)
AND paaf.assignment_id = hc.assignment_id(+)
AND hc.UNIT(+) = 'HEAD'
AND Nvl(PPOS_MAIN.actual_termination_date, Trunc(SYSDATE)) BETWEEN PPASG.start_date
AND PPASG.end_date
AND PAAF.assignment_status_type_id = PASTVL.assignment_status_type_id --AND PAAF.ASSIGNMENT_STATUS_TYPE <> 'INACTIVE'
AND Trunc(SYSDATE) BETWEEN PAPF.effective_start_date
AND PAPF.effective_end_date
AND Trunc(SYSDATE) BETWEEN PPNF.effective_start_date
AND PPNF.effective_end_date
AND Trunc(SYSDATE) BETWEEN PAAF.effective_start_date
AND PAAF.effective_end_date
AND Trunc(SYSDATE) BETWEEN HRL.effective_start_date
AND HRL.effective_end_date
AND Trunc(SYSDATE) BETWEEN dept.effective_start_date
AND dept.effective_end_date
AND Trunc(SYSDATE) BETWEEN pos.effective_start_date(+)
AND pos.effective_end_date(+)
AND Trunc(SYSDATE) BETWEEN hc.effective_start_date(+)
AND hc.effective_end_date(+)
AND PAPF.person_number IN ('12345')
x

Enable Advanced Edit in Oracle HCM Extracts

1. Navigate to Support and Maintenance

2. Go to task Manage common Lookups

3. Open ORA_PER_EXT_CONFIG lookup

4. Add Lookup code - EXT_PUI_ENABLE and meaning as Yes

5. Save and Close.