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