Archive for the ‘OTL’ Category

Oracle EBS SQL: OTL

Friday, December 20th, 2013

The following SQL statement can be used for debugging the Oracle Time and Labor (OTL) and Projects (PA) interface.
This was created during a recent R12 upgrade where attributes where somehow resulting in a quantity in projects multiplied by the project ID.

SELECT   hts.timecard_id, hts.resource_id, hts.start_time,
hts.stop_time, hts.submission_date,
htb1.start_time each_day, hta.attribute1 project_id,
hta.attribute2 task_id, htb2.measure, pt.task_name, 
hts.approval_item_key, hts.transferred_to,
htb.unit_of_measure
,ppa.segment1, pt.task_number
/**                Funny Attributes
  These should be cleared during the upgrade Process. They are still populated post upgrade. 
**/                
,hta.attribute25
,hta.attribute26
,hta.attribute27
,hta.attribute29
FROM hxc_time_building_blocks htb,
hxc_time_building_blocks htb1,
hxc_time_building_blocks htb2,
hxc_time_attribute_usages htau,
hxc_time_attributes hta,
pa_projects_all ppa,
hxc_timecard_summary hts,
pa_tasks pt
WHERE 1=1
AND htb1.parent_building_block_id = htb.time_building_block_id
AND htb1.parent_building_block_ovn = htb.object_version_number
AND htb2.parent_building_block_id = htb1.time_building_block_id
AND htb2.parent_building_block_ovn = htb1.object_version_number              
AND htb.SCOPE = 'TIMECARD'
AND htb1.SCOPE = 'DAY'
AND htb2.SCOPE = 'DETAIL'
AND htau.time_building_block_id = htb2.time_building_block_id
AND htau.time_building_block_ovn = htb2.object_version_number
AND htau.time_attribute_id = hta.time_attribute_id
AND hts.start_time = htb.start_time
AND hts.resource_id = htb.resource_id 
AND hta.attribute_category = 'PROJECTS'
AND hta.attribute2 = pt.task_id
AND hta.attribute1 = pt.project_id
AND ppa.project_id = hta.attribute1              
AND hts.timecard_id = :p_timecard_id              
and ppa.segment1 like 'YOUR_PROJECT_NUMBER'
--AND hts.approval_status <> 'APPROVED'
--AND trunc(hts.submission_date) = trunc(sysdate-360)
--AND hts.transferred_to is null        
--and ppa.project_id <> hta.attribute25