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
[su_feed url=”https://iavinash.com//category/reusable-queries/feed/” limit=”5″]
