SELECT DISTINCT PP.SEGMENT1 "Project Number",
PP.NAME PROJECT_NAME,
PT.TASK_NUMBER,
PT.TASK_NAME,
HTA.ATTRIBUTE3,
submission_date,
DAY.START_TIME,
DET.MEASURE,
hts.APPROVAL_STATUS,
DET.COMMENT_TEXT,
papf.employee_number employee_number,
papf.first_name,
papf.last_name,
papf.middle_names,
papf.full_name,
sup.first_name sup_first_name,
sup.last_name sup_last_name,
sup.middle_names sup_middle_name,
sup.full_name sup_full_name
FROM (SELECT TIME_BUILDING_BLOCK_ID,
PARENT_BUILDING_BLOCK_ID,
PARENT_BUILDING_BLOCK_OVN,
OBJECT_VERSION_NUMBER,
MEASURE,
RESOURCE_ID,
APPROVAL_STATUS,
COMMENT_TEXT,
TRANSLATION_DISPLAY_KEY
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'DETAIL') DET,
(SELECT TIME_BUILDING_BLOCK_ID,
PARENT_BUILDING_BLOCK_ID,
PARENT_BUILDING_BLOCK_OVN,
OBJECT_VERSION_NUMBER,
START_TIME,
APPROVAL_STATUS,
RESOURCE_ID
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'DAY') DAY,
(SELECT TIME_BUILDING_BLOCK_ID,
START_TIME,
APPROVAL_STATUS,
OBJECT_VERSION_NUMBER,
COMMENT_TEXT,
RESOURCE_ID
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'TIMECARD') TC,
HXC_TIME_ATTRIBUTE_USAGES HTAU,
HXC_TIME_ATTRIBUTES HTA,
PA_PROJECTS_ALL PP,
PA_TASKS PT,
hxc_timecard_summary hts,
per_all_people_f papf,
per_all_assignments_f paaf,
per_all_people_f sup
WHERE DAY.PARENT_BUILDING_BLOCK_ID = TC.TIME_BUILDING_BLOCK_ID
AND DAY.PARENT_BUILDING_BLOCK_OVN = TC.OBJECT_VERSION_NUMBER
AND DET.PARENT_BUILDING_BLOCK_ID = DAY.TIME_BUILDING_BLOCK_ID
AND DET.PARENT_BUILDING_BLOCK_OVN = DAY.OBJECT_VERSION_NUMBER
AND TRUNC (DAY.START_TIME) BETWEEN '1-MAY-2024' AND '31-MAY-2024'
AND DAY.RESOURCE_ID = (SELECT EMPLOYEE_ID from FND_USER WHERE USER_NAME='XXX')
AND TC.OBJECT_VERSION_NUMBER =
(SELECT MAX (OBJECT_VERSION_NUMBER)
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'TIMECARD'
AND TIME_BUILDING_BLOCK_ID = TC.TIME_BUILDING_BLOCK_ID)
AND DET.OBJECT_VERSION_NUMBER =
(SELECT MAX (OBJECT_VERSION_NUMBER)
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'DETAIL'
AND TIME_BUILDING_BLOCK_ID = DET.TIME_BUILDING_BLOCK_ID)
AND DET.TIME_BUILDING_BLOCK_ID = HTAU.TIME_BUILDING_BLOCK_ID
AND DET.OBJECT_VERSION_NUMBER = HTAU.TIME_BUILDING_BLOCK_OVN
AND HTAU.TIME_ATTRIBUTE_ID = HTA.TIME_ATTRIBUTE_ID
AND HTA.ATTRIBUTE1 = TO_CHAR (PP.PROJECT_ID)
AND HTA.ATTRIBUTE2 = TO_CHAR (PT.TASK_ID)
AND HTA.ATTRIBUTE5 = 'ST'
AND HTS.timecard_id = TC.TIME_BUILDING_BLOCK_ID
-- papf, paaf, sup
AND TC.resource_id = papf.person_id
AND SYSDATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND papf.person_id = paaf.person_id
AND SYSDATE BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND paaf.supervisor_id = sup.person_id
AND SYSDATE BETWEEN sup.effective_start_date
AND sup.effective_end_date
-- tms
ORDER BY 7;
PP.NAME PROJECT_NAME,
PT.TASK_NUMBER,
PT.TASK_NAME,
HTA.ATTRIBUTE3,
submission_date,
DAY.START_TIME,
DET.MEASURE,
hts.APPROVAL_STATUS,
DET.COMMENT_TEXT,
papf.employee_number employee_number,
papf.first_name,
papf.last_name,
papf.middle_names,
papf.full_name,
sup.first_name sup_first_name,
sup.last_name sup_last_name,
sup.middle_names sup_middle_name,
sup.full_name sup_full_name
FROM (SELECT TIME_BUILDING_BLOCK_ID,
PARENT_BUILDING_BLOCK_ID,
PARENT_BUILDING_BLOCK_OVN,
OBJECT_VERSION_NUMBER,
MEASURE,
RESOURCE_ID,
APPROVAL_STATUS,
COMMENT_TEXT,
TRANSLATION_DISPLAY_KEY
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'DETAIL') DET,
(SELECT TIME_BUILDING_BLOCK_ID,
PARENT_BUILDING_BLOCK_ID,
PARENT_BUILDING_BLOCK_OVN,
OBJECT_VERSION_NUMBER,
START_TIME,
APPROVAL_STATUS,
RESOURCE_ID
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'DAY') DAY,
(SELECT TIME_BUILDING_BLOCK_ID,
START_TIME,
APPROVAL_STATUS,
OBJECT_VERSION_NUMBER,
COMMENT_TEXT,
RESOURCE_ID
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'TIMECARD') TC,
HXC_TIME_ATTRIBUTE_USAGES HTAU,
HXC_TIME_ATTRIBUTES HTA,
PA_PROJECTS_ALL PP,
PA_TASKS PT,
hxc_timecard_summary hts,
per_all_people_f papf,
per_all_assignments_f paaf,
per_all_people_f sup
WHERE DAY.PARENT_BUILDING_BLOCK_ID = TC.TIME_BUILDING_BLOCK_ID
AND DAY.PARENT_BUILDING_BLOCK_OVN = TC.OBJECT_VERSION_NUMBER
AND DET.PARENT_BUILDING_BLOCK_ID = DAY.TIME_BUILDING_BLOCK_ID
AND DET.PARENT_BUILDING_BLOCK_OVN = DAY.OBJECT_VERSION_NUMBER
AND TRUNC (DAY.START_TIME) BETWEEN '1-MAY-2024' AND '31-MAY-2024'
AND DAY.RESOURCE_ID = (SELECT EMPLOYEE_ID from FND_USER WHERE USER_NAME='XXX')
AND TC.OBJECT_VERSION_NUMBER =
(SELECT MAX (OBJECT_VERSION_NUMBER)
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'TIMECARD'
AND TIME_BUILDING_BLOCK_ID = TC.TIME_BUILDING_BLOCK_ID)
AND DET.OBJECT_VERSION_NUMBER =
(SELECT MAX (OBJECT_VERSION_NUMBER)
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'DETAIL'
AND TIME_BUILDING_BLOCK_ID = DET.TIME_BUILDING_BLOCK_ID)
AND DET.TIME_BUILDING_BLOCK_ID = HTAU.TIME_BUILDING_BLOCK_ID
AND DET.OBJECT_VERSION_NUMBER = HTAU.TIME_BUILDING_BLOCK_OVN
AND HTAU.TIME_ATTRIBUTE_ID = HTA.TIME_ATTRIBUTE_ID
AND HTA.ATTRIBUTE1 = TO_CHAR (PP.PROJECT_ID)
AND HTA.ATTRIBUTE2 = TO_CHAR (PT.TASK_ID)
AND HTA.ATTRIBUTE5 = 'ST'
AND HTS.timecard_id = TC.TIME_BUILDING_BLOCK_ID
-- papf, paaf, sup
AND TC.resource_id = papf.person_id
AND SYSDATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND papf.person_id = paaf.person_id
AND SYSDATE BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND paaf.supervisor_id = sup.person_id
AND SYSDATE BETWEEN sup.effective_start_date
AND sup.effective_end_date
-- tms
ORDER BY 7;