Introduction
This Post illustrates the steps required to OTL Time Entries Details in Fusion Application
Script to OTL Time Entries Details – Fusion HCM
SELECT papf.person_number emp_id,
TO_DATE (TO_CHAR (sh21.start_time, ‘DD/MM/YYYY’),
‘DD/MM/YYYY’
) start_time,
TO_DATE (TO_CHAR (sh21.stop_time, ‘DD/MM/YYYY’),
‘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,
sh26.attribute_category,
sh27.status_value,
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’)
Queries
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions
