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
 |
| 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"
 |
| 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;
|
0 comments