SELECT ppf.full_name Resource_Name,
-- paaf.supervisor_id,
ppf1.full_name Manager_Name,
papa.name Project_Name,
papa.segment1 project_num,
pt.task_name,
TO_CHAR (hts.start_time, 'DD-MON-YYYY') Week_Start,
--hts.resource_id,
htb2.measure Hours_Charge,
hts.approval_status,
-- hts.submission_date,
htb1.start_time Individual_date,
TO_CHAR (htb1.start_time, 'DAY') Individual_day
FROM hxc.hxc_time_building_blocks htb,
hxc.hxc_time_building_blocks htb1,
hxc.hxc_time_building_blocks htb2,
hxc.hxc_time_attribute_usages htau,
hxc.hxc_time_attributes hta,
apps.pa_projects_all papa,
hxc.hxc_timecard_summary hts,
apps.per_all_people_f ppf,
apps.per_all_people_f ppf1,
apps.pa_tasks pt,
apps.per_all_assignments_f paaf
WHERE htb1.parent_building_block_id = htb.time_building_block_id
AND htb1.parent_building_block_ovn = htb.object_version_number
AND ppf.person_id = hts.resource_id
-- AND htb.date_to = apps.hr_general.end_of_time
AND htb.SCOPE = 'TIMECARD'
AND htb1.SCOPE = 'DAY'
-- AND htb1.date_to = apps.hr_general.end_of_time
AND htb2.parent_building_block_id = htb1.time_building_block_id
AND htb2.parent_building_block_ovn = htb1.object_version_number
AND htb2.SCOPE = 'DETAIL'
AND paaf.person_id = ppf.person_id
AND paaf.organization_id = XXX
-- AND htb2.date_to = apps.hr_general.end_of_time
AND htau.time_building_block_id = htb2.time_building_block_id
AND htau.time_building_block_ovn = htb2.object_version_number
AND htau.time_attribute_id = hta.time_attribute_id
AND papa.project_id = hta.attribute1
AND hts.start_time = htb.start_time
AND hts.resource_id = htb.resource_id
AND htb2.approval_status = 'SUBMITTED'
--AND htb.resource_id = p_resource_id
-- AND hts.timecard_id = :p_timecard_id
AND hta.attribute_category = 'PROJECTS'
AND paaf.supervisor_id = ppf1.person_id
AND hts.start_time >= SYSDATE -XXX(Days)
-- AND hts.approval_status = 'WORKING'
AND hta.attribute2 = pt.task_id
AND hta.attribute1 = pt.project_id
AND pt.project_id = pt.project_id
-- and hts.resource_id=46787
AND TO_CHAR (htb1.start_time, 'DAY') NOT IN ('SATURDAY ', 'SUNDAY ')
AND ppf.effective_end_date > SYSDATE
AND ppf1.effective_end_date > SYSDATE
AND paaf.effective_end_date > SYSDATE
ORDER BY ppf.full_name, htb1.start_time, htb1.start_time
-- paaf.supervisor_id,
ppf1.full_name Manager_Name,
papa.name Project_Name,
papa.segment1 project_num,
pt.task_name,
TO_CHAR (hts.start_time, 'DD-MON-YYYY') Week_Start,
--hts.resource_id,
htb2.measure Hours_Charge,
hts.approval_status,
-- hts.submission_date,
htb1.start_time Individual_date,
TO_CHAR (htb1.start_time, 'DAY') Individual_day
FROM hxc.hxc_time_building_blocks htb,
hxc.hxc_time_building_blocks htb1,
hxc.hxc_time_building_blocks htb2,
hxc.hxc_time_attribute_usages htau,
hxc.hxc_time_attributes hta,
apps.pa_projects_all papa,
hxc.hxc_timecard_summary hts,
apps.per_all_people_f ppf,
apps.per_all_people_f ppf1,
apps.pa_tasks pt,
apps.per_all_assignments_f paaf
WHERE htb1.parent_building_block_id = htb.time_building_block_id
AND htb1.parent_building_block_ovn = htb.object_version_number
AND ppf.person_id = hts.resource_id
-- AND htb.date_to = apps.hr_general.end_of_time
AND htb.SCOPE = 'TIMECARD'
AND htb1.SCOPE = 'DAY'
-- AND htb1.date_to = apps.hr_general.end_of_time
AND htb2.parent_building_block_id = htb1.time_building_block_id
AND htb2.parent_building_block_ovn = htb1.object_version_number
AND htb2.SCOPE = 'DETAIL'
AND paaf.person_id = ppf.person_id
AND paaf.organization_id = XXX
-- AND htb2.date_to = apps.hr_general.end_of_time
AND htau.time_building_block_id = htb2.time_building_block_id
AND htau.time_building_block_ovn = htb2.object_version_number
AND htau.time_attribute_id = hta.time_attribute_id
AND papa.project_id = hta.attribute1
AND hts.start_time = htb.start_time
AND hts.resource_id = htb.resource_id
AND htb2.approval_status = 'SUBMITTED'
--AND htb.resource_id = p_resource_id
-- AND hts.timecard_id = :p_timecard_id
AND hta.attribute_category = 'PROJECTS'
AND paaf.supervisor_id = ppf1.person_id
AND hts.start_time >= SYSDATE -XXX(Days)
-- AND hts.approval_status = 'WORKING'
AND hta.attribute2 = pt.task_id
AND hta.attribute1 = pt.project_id
AND pt.project_id = pt.project_id
-- and hts.resource_id=46787
AND TO_CHAR (htb1.start_time, 'DAY') NOT IN ('SATURDAY ', 'SUNDAY ')
AND ppf.effective_end_date > SYSDATE
AND ppf1.effective_end_date > SYSDATE
AND paaf.effective_end_date > SYSDATE
ORDER BY ppf.full_name, htb1.start_time, htb1.start_time
No comments:
Post a Comment