Friday, February 3, 2017

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)


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect