Monday, November 28, 2011

How to derive Approvers, Overriding Approvers - Oracle Time & Labor(OTL) using SQL Statement

In Oracle Time & Labor(OTL) module, while entering the Timecard, the employee has an option to enter the Overriding Approver instead of the default approver, PERSON_ID of the overriding approver is stored in HXC_TIME_ATTRIBUTES.ATTRITE10
These attributes are Descriptive Flexfields with the following navigation:

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



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   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,
         ppxr.person_id resource_id, ppxr.employee_number resource_emp_num,
         ppxr.full_name resource_emp_name
 FROM hxc_app_period_summary haps,
         hxc_timecard_summary tbs,
         hxc.hxc_tc_ap_links htal,
         per_people_x ppx,
         per_people_x ppxr
 WHERE 1 = 1
     AND htal.timecard_id = tbs.timecard_id
     AND htal.application_period_id = haps.application_period_id
     AND haps.resource_id = tbs.resource_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 (tbs.start_time)
     AND TRUNC (haps.stop_time) = TRUNC (tbs.stop_time)
     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 haps.application_period_ovn =
                   (SELECT MAX (application_period_ovn)
                      FROM hxc_app_period_summary
                     WHERE application_period_id = haps.application_period_id)
ORDER BY 1, 2;


Following query will list out all the Overriding approvers for a given time period :-
Parameters: P_START_DATE  -- Timecard building block start date
                  P_END_DATE      -- Timecard building block end date
SELECT tau.time_building_block_id,
       ppx.person_id override_approver_personid,
       ppx.employee_number override_approver_empnum,
       ppx.full_name ovverride_approver_name,
       ppxr.person_id Resource_id,
       ppxr.EMPLOYEE_NUMBER Resource_Emp_Num,
       ppxr.full_name Resource_Emp_Name
  FROM hxc_time_attributes ta,
       hxc_time_attribute_usages tau,
       hxc_time_building_blocks tbb,
       hxc_bld_blk_info_types bbi,
       per_people_x ppx,
       per_people_x ppxr
 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 bbi.bld_blk_info_type ='APPROVAL'
   AND bbi.bld_blk_info_type_id = ta.bld_blk_info_type_id
   AND ppxr.person_id = tbb.resource_id
   AND tbb.resource_type = 'PERSON'
  AND TO_NUMBER (ta.attribute10) = ppx.person_id
   AND ta.attribute_category = 'APPROVAL'
   AND tbb.approval_style_id = 1
   AND (tbb.start_time) BETWEEN TO_DATE (:p_start_date, 'DD-MON-RRRR')
                                    AND TO_DATE (:p_end_date, 'DD-MON-RRRR')
   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);

2 comments:

  1. Hi,

    We have a Project based timesheet approval mechanism, but now our client wants to define Task Manager for each of the task under project and wants to send the timesheet for approval to Task Manager's, so I created a new FF based on the existing Override Approver WF Person Mechanism (Seeded Approval) FF but its not working, my FF returns the correct value but WF goes into error if I pass Task Manager ID.
    Do you have any idea what could be the possible reason for it?

    Thanks,
    Punit

    ReplyDelete
  2. These queries are amazingly helpful. Thank you so much for sharing them.

    ReplyDelete