Fusion HCM – Query for OTL Time Entries Details

SELECT   papf.person_number emp_id,
         TO_DATE (TO_CHAR (sh21.start_time, ‘DD/MM/YYYY’),
                 ) start_time,
         TO_DATE (TO_CHAR (sh21.stop_time, ‘DD/MM/YYYY’),
                 ) stop_time,
         sh26.attribute_category elements, sh27.status_value,
         ROUND (SUM (sh21.measure), 2) measure
    FROM per_all_people_f papf,
         per_all_assignments_m asg,
         per_legal_employers ple,
         hwm_tm_rec sh21,
         hwm_tm_rec_grp_usages sh22,
         hwm_tm_rec_grp sh23,
         hwm_grp_type sh24,
         hwm_tm_rep_atrb_usages sh25,
         hwm_tm_rep_atrbs sh26,
         hwm_tm_statuses sh27,
         hwm_tm_status_def_b sh28
   WHERE papf.person_id = asg.person_id(+)
     –AND papf.person_number = ‘24043’
     AND asg.legal_entity_id = ple.organization_id
     AND asg.primary_flag = ‘Y’
     AND asg.assignment_type IN (‘E’, ‘C’, ‘N’, ‘P’)
     AND asg.assignment_status_type = ‘ACTIVE’
     AND ple.status = ‘A’
     AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                             AND papf.effective_end_date
     AND TRUNC (SYSDATE) BETWEEN asg.effective_start_date
                             AND asg.effective_end_date
     AND TRUNC (SYSDATE) BETWEEN ple.effective_start_date
                             AND ple.effective_end_date
     AND sh21.latest_version = ‘Y’
     AND sh21.resource_type = ‘PERSON’
     AND sh21.tm_rec_id = sh22.tm_rec_id
     AND sh21.tm_rec_version = sh22.tm_rec_version
     AND sh21.layer_code = ‘TIME_RPTD’
     AND sh22.layer_code = ‘TIME_RPTD’
     AND sh22.tm_rec_grp_id = sh23.tm_rec_grp_id
     AND sh22.tm_rec_grp_version = sh23.tm_rec_grp_version
     AND sh23.latest_version = ‘Y’
     AND sh21.resource_id = sh23.resource_id
     AND sh23.grp_type_id = sh24.grp_type_id
     AND sh21.tm_rec_id = sh25.usages_source_id
     AND sh21.tm_rec_version = sh25.usages_source_version
     AND sh25.usages_type = ‘TIME_RECORD’
     AND sh24.NAME = ‘Processed TimecardDay’
     AND sh25.tm_rep_atrb_id = sh26.tm_rep_atrb_id
     AND sh26.attribute_category IN (SELECT base_element_name
                                       FROM pay_element_types_f)
     AND sh21.resource_id = papf.person_id
     AND sh21.tm_rec_type IN (‘RANGE’, ‘MEASURE’)
     AND sh27.tm_status_def_id = sh28.tm_status_def_id
     AND sh27.tm_bldg_blk_id = sh21.tm_rec_id
     AND sh27.tm_bldg_blk_version = sh21.tm_rec_version
     AND TRUNC (sh27.date_to) = TO_DATE (’31/12/4712′, ‘DD/MM/YYYY’)
GROUP BY papf.person_number,
         TO_DATE (TO_CHAR (sh21.start_time, ‘DD/MM/YYYY’), ‘DD/MM/YYYY’),
         TO_DATE (TO_CHAR (sh21.stop_time, ‘DD/MM/YYYY’), ‘DD/MM/YYYY’)
  • February 15, 2019 | 343 views
  • Comments