Query to get the Oracle Timecard (OTL) Approval Details

SELECT

  a.time_building_block_id,

  c.time_building_block_id,

  c.time_building_block_id,

  (select

    f.approver_id from hxc_tc_ap_links e, hxc_app_period_summary f, hxc_time_building_blocks g

    where e.timecard_id = g.time_building_block_id

     and f.application_period_id = e.application_period_id

     and g.time_building_block_id = a.time_building_block_id

     and g.date_to = hr_general.end_of_time

  ) Approver,

  (select f.creation_date

   from hxc_tc_ap_links e, hxc_app_period_summary f, hxc_time_building_blocks g

   where e.timecard_id = g.time_building_block_id

      and f.application_period_id = e.application_period_id

     and g.time_building_block_id = a.time_building_block_id

     and g.date_to = hr_general.end_of_time

  ) Approved_Date

FROM

  hxc_time_building_blocks a,

  hxc_time_building_blocks b,

  hxc_time_building_blocks c,

  hxc_timecard_summary d

WHERE 1=1

  and b.parent_building_block_id = a.time_building_block_id

  and b.parent_building_block_ovn = a.object_version_number

  and c.parent_building_block_id = b.time_building_block_id

  and b.parent_building_block_ovn = b.object_version_number

  and a.scope = 'TIMECARD'

  and b.scope = 'DAY'

  and c.scope = 'DETAIL'

  and c.date_to = hr_general.end_of_time

  and b.date_to = hr_general.end_of_time

  and a.date_to = hr_general.end_of_time

  and a.time_building_block_id = d.timecard_id

  and d.approval_status = nvl(:approval_status, d.approval_status)