Oracle Time & Labor(OTL) - Timecard, Project details query

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

The tables hxc_timecard_summary stores the current timecard for a particular period and resource. 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). 

HXC_APP_PERIOD_SUMMARY table has the application_period_id. HXC_AP_DETAIL_LINKS table has application_period_id, timecard_id which links hxc_app_period_summary,hxc_timecard_summary.

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.

Following is an ERD Diagram which outlines the relationships between OTL Timecard and Project tables:

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:

Following query will list out all the Approvers for a given time period :-
Parameters: P_START_DATE  -- Timecard building block start date
                  P_END_DATE      -- Timecard building block end date

SELECT   ppxr.person_id resource_id,
         ppxr.employee_number resource_emp_num,
         ppxr.full_name resource_emp_name,
         tbbda.start_time, tbbda.stop_time,
         pp.project_id, pp.segment1 project_number,
         pp.name project_name, pt.task_id,
         pt.task_name, pt.task_number,
         pt.attribute7 billable_flag,
         tbb.approval_style_id,
         tbs.timecard_id timecard_id,
         haps.application_period_id,
         haps.application_period_ovn,
         ppx.person_id approver_id,
         ppx.employee_number approver_empnum,
         ppx.full_name approver_name,
         tbbde.time_building_block_id detail_building_block_id
    FROM hxc_app_period_summary haps,
         hxc_timecard_summary tbs,
         hxc.hxc_tc_ap_links htal,
         per_people_x ppx,
         per_people_x ppxr,
         hxc_time_building_blocks tbb,
         hxc_time_attribute_usages taup,
         hxc_time_attributes tap,
         hxc_time_building_blocks tbbda,
         hxc_time_building_blocks tbbde,
         pa_projects_all pp,
         pa_tasks pt
   WHERE 1 = 1
     AND htal.timecard_id = tbs.timecard_id
     AND htal.application_period_id = haps.application_period_id
     AND haps.application_period_ovn = tbs.timecard_ovn
     AND haps.resource_id = tbs.resource_id
     AND TRUNC (haps.start_time) = TRUNC (tbs.start_time)
     AND TRUNC (haps.stop_time) = TRUNC (tbs.stop_time)
     AND haps.application_period_ovn =
                   (SELECT MAX (application_period_ovn)
                      FROM hxc_app_period_summary
                     WHERE application_period_id = haps.application_period_id)
     AND haps.approver_id = ppx.person_id
     AND haps.start_time BETWEEN ppx.effective_start_date
                             AND ppx.effective_end_date
     AND TRUNC (haps.start_time) = TRUNC (tbb.start_time)
     AND TRUNC (haps.stop_time) = TRUNC (tbb.stop_time)
     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 tbb.resource_id = haps.resource_id
     AND tbb.SCOPE = 'TIMECARD'
     AND tbb.approval_style_id = 1021
     AND tbbda.SCOPE = 'DAY'
     AND tbbde.SCOPE = 'DETAIL'
     AND tbb.time_building_block_id = tbs.timecard_id
     AND tbb.object_version_number = tbs.timecard_ovn
     AND tbbda.parent_building_block_id = tbb.time_building_block_id
     AND tbbda.parent_building_block_ovn = tbbda.object_version_number
     AND tbbde.parent_building_block_id = tbbda.time_building_block_id
     AND tbbde.parent_building_block_ovn = tbb.object_version_number
     AND tbbde.time_building_block_id = taup.time_building_block_id
     AND tbbde.object_version_number = taup.time_building_block_ovn
     AND tap.attribute1 IS NOT NULL
     AND tap.bld_blk_info_type_id = 13
     AND tap.time_attribute_id = taup.time_attribute_id
     AND tap.attribute_category = 'PROJECTS'
     AND TO_NUMBER (tap.attribute1) = pp.project_id
     AND pt.project_id = pp.project_id
     AND TO_NUMBER (tap.attribute2) = pt.task_id
     AND tbbde.resource_id = tbb.resource_id
     AND tbbde.resource_id = tbbda.resource_id
     AND ppxr.person_id = tbs.resource_id
     AND TRUNC (tbs.start_time) BETWEEN TO_DATE (:p_start_date, 'DD-MON-RRRR')
                                    AND TO_DATE (:p_end_date, 'DD-MON-RRRR')
     AND tbbda.object_version_number =
            (SELECT MAX (object_version_number)
               FROM hxc_time_building_blocks
              WHERE tbbda.time_building_block_id = time_building_block_id
                AND tbbda.approval_style_id = approval_style_id)
     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, tbs.timecard_id, 4;