Oracle EBS SQL: OTL
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