Latest
Home » Oracle » Oracle Time and Labor » OTL timecard/timesheet query to fetch submitted/approved hours

OTL timecard/timesheet query to fetch submitted/approved hours

⚡ Must Read Fusion Article : Oracle Fusion Individual Compensation Plan Explained With Examples

OTL Query

Here is the query to get time submitted by any resource (employee as well as contractor). This will help you if you want to quickly want to check what is the time entered by any resource and what is the status of that time card (approved, submitted or anything else). If you want to run for a single person just change the employee number at the end of the query.

Just remember if you would run this for all resources, it will take very long time to run so its advisable to run it for small chunk of resources.

SELECT nvl(ppf.employee_number,ppf.npw_number) "Person Number",
 ppf.full_name,
 htbb1.start_time week_start_date,
 htbb1.stop_time week_end_date,
 hts.submission_date submitted_date,
 day.start_time day,
 detail.measure hours,
 ppa.project_number,
 pt.task_number,
 hta.attribute3 "Type",
 hts.approval_status
 FROM apps.per_all_people_f ppf,
 apps.hxc_time_building_blocks htbb1,
 apps.hxc_time_building_blocks DAY,
 apps.hxc_time_building_blocks detail,
 apps.hxc_base_pa_online_projects_v ppa,
 apps.hxc_base_pa_online_tasks_v pt,
 apps.hxc_time_attribute_usages htau,
 apps.hxc_time_attribute_usages htau1,
 apps.hxc_time_attributes hta,
 apps.hxc_bld_blk_info_types hbbit,
 apps.hxc_time_attributes hta1,
 apps.hxc_timecard_summary hts
 WHERE 1 = 1
 AND htbb1.resource_id = ppf.person_id
 AND htbb1.SCOPE = 'TIMECARD'
 AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
 AND NVL (ppf.effective_end_date,
 TRUNC (SYSDATE))
 AND DAY.parent_building_block_id = htbb1.time_building_block_id
 AND DAY.parent_building_block_ovn = htbb1.object_version_number
 AND DAY.SCOPE = 'DAY'
 AND DAY.date_to = TO_DATE ('31/12/4712', 'DD/MM/YYYY')
 AND detail.date_to = TO_DATE ('31/12/4712', 'DD/MM/YYYY')
 AND detail.SCOPE = 'DETAIL'
 AND detail.parent_building_block_id = DAY.time_building_block_id
 AND detail.parent_building_block_ovn = DAY.object_version_number
 AND hta.attribute1 = ppa.project_id
 AND hta.attribute2 = pt.task_id
 AND ppa.project_id = pt.project_id
 AND htau.time_building_block_id = detail.time_building_block_id
 AND htau.time_building_block_ovn = detail.object_version_number
 AND htau1.time_building_block_id = detail.time_building_block_id
 AND htau1.time_building_block_ovn = detail.object_version_number
 AND htau.time_attribute_id = hta.time_attribute_id
 AND hta.bld_blk_info_type_id = hbbit.bld_blk_info_type_id
 AND hbbit.bld_blk_info_type = 'PROJECTS'
 AND htau1.time_attribute_id = hta1.time_attribute_id
 AND hta1.attribute_category = 'SECURITY'
 AND hts.timecard_id = htbb1.time_building_block_id
 AND NVL (hts.timecard_ovn, htbb1.object_version_number) = htbb1.object_version_number
 and nvl(ppf.employee_number, ppf.npw_number) = '127856' -- Change your employee number here of comment this line for all resources
ORDER BY day.start_time DESC, htbb1.start_time DESC

Few other reusable queries if you are interested in

An error has occurred, which probably means the feed is down. Try again later.
☑ What to read next :
Question and answer forum of iavinash.com
☑ Related Job : Job opening in CTS for Oracle HRMS Bangalore

Check Also

Learn Oracle Time and Labor (OTL) Step by Step for Project Layout

Learn Oracle Time and Labor (OTL) Step by Step for Project Layout

Learn Oracle Time and Labor (OTL) In this article you will learn, ¬†step by step, …