Oracle Time & Labor(OTL) - How to derive project, task, overriding approver from hxc_time_building_blocks

The table hxc_time_building_blocks stores the time related information. This is a hierarchial table which stores one record for timecard (identified by scope as TIMECARD), one record for every day in the timecard (identified by scope as DAY) and 1 record for every detail in the timecard (identified by scope as DETAIL).

The attributes are stored in hxc_time_attributes table. The tables hxc_time_building_blocks and hxc_time_attributes are linked through hxc_time_attribute_usages. i.e. hxc_time_attribute_usages contains the time building block id and time building block ovn of hxc_timecard_building_blocks and time_attribute_id of hxc_time_attributes table.

These attributes are Descriptive Flexfields with the following navigation:

Application Developer => Flexfields => Descriptive => Segments
Search for Title - OTL Information Types, Code is "PROJECTS"

Attribute1,Attribute2 and Attribute3 of hxc_time_attributes stores Project Id,Task id and Expenditure Type respectively, where as attribute category is 'PROJECTS'. Details are highlighted in the below diagram:

 

SELECT   pp.project_id, pp.segment1 project_number, pp.name project_name,
         pt.task_number, pt.task_name, pt.task_id, ppx.person_id resource_id,
         ppx.employee_number resource_empnum, ppx.full_name resource_name,
         ppxa.person_id override_approver_personid,
         ppxa.employee_number override_approver_empnum,
         ppxa.full_name ovverride_approver_name,
         tbbde.measure Hours,

         tbbde.unit_of_measure uom,
         tbbde.time_building_block_id detail_time_building_block_id,
         tbbda.time_building_block_id day_time_building_block_id,
         tbb.time_building_block_id parent_building_block_id
 FROM hxc_time_attributes ta,
         hxc_time_attribute_usages tau,
         hxc_time_building_blocks tbb,
         per_people_x ppxa,
         hxc_time_attributes tap,
         hxc_time_attribute_usages taup,
         hxc_time_building_blocks tbbde,
         hxc_time_building_blocks tbbda,
         per_people_x ppx,
         pa_projects_all pp,
         pa_tasks pt
   WHERE tbb.time_building_block_id = tau.time_building_block_id
     AND tbb.object_version_number = tau.time_building_block_ovn
     AND ta.time_attribute_id = tau.time_attribute_id
     AND ta.attribute10 IS NOT NULL
     AND ta.bld_blk_info_type_id = 10
     AND TO_NUMBER (ta.attribute10) = ppxa.person_id
     AND ta.attribute_category = 'APPROVAL'
     AND tbb.approval_style_id = 1
     AND (tbb.start_time) BETWEEN TO_DATE (:P_START_DATE)
                              AND TO_DATE (:P_END_DATE)
     AND tbb.object_version_number =
            (SELECT MAX (object_version_number)
               FROM hxc_time_building_blocks
              WHERE tbb.time_building_block_id = time_building_block_id
                AND tbb.approval_style_id = approval_style_id)
     AND tbbde.time_building_block_id = taup.time_building_block_id
     AND tbbde.object_version_number = taup.time_building_block_ovn
     AND tbbde.parent_building_block_id = tbbda.time_building_block_id
     AND tbbde.parent_building_block_ovn = tbbda.object_version_number
     AND tbbda.parent_building_block_id = tbb.time_building_block_id
     AND tbbda.parent_building_block_ovn = tbb.object_version_number
     AND tap.time_attribute_id = taup.time_attribute_id
     AND tap.bld_blk_info_type_id = 13
     AND tbbde.resource_id = ppx.person_id
     AND tap.attribute_category = 'PROJECTS'
     AND TO_NUMBER (tap.attribute1) = pp.project_id
     AND TO_NUMBER (tap.attribute2) = pt.task_id
     AND pt.project_id = pp.project_id
     AND tbbde.approval_style_id = 1
     AND (tbbda.start_time) BETWEEN TO_DATE (:P_START_DATE)
                                AND TO_DATE (:P_END_DATE)
     AND tbbde.object_version_number =
            (SELECT MAX (object_version_number)
               FROM hxc_time_building_blocks
              WHERE tbbde.time_building_block_id = time_building_block_id
                AND tbbde.approval_style_id = approval_style_id)
ORDER BY 1, 6;