Oracle Time and Labor Tables Query | Oracle OTL Tech Guide

By Jag - October 26, 2014

How to View Segments Summary OTL Information Types Projects Code


View ERD Diagram  Relationships between OTL Timecard and Project tables

Example For 


OTL Oracle Application Developer
Relationships between OTL

Go to Responsibility

Select the  Application Developer Module 

Descriptive Flexfields with the following navigation:


Application Developer > Flexfields > Descriptive >Segments

Find  the Titile - OTL Information Types, Code is "PROJECTS" 


OTL Applicattion Devaloper
Descriptive Flexfields Segments


In this 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;

  • Share:

You Might Also Like

0 comments