OTL - Query with PROJECTS, TASKS

   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;