eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.HXC_TIMECARD_AUDIT_SUMMARY

Source

View Text - Preformatted


SELECT distinct decode(tbb.type,'RANGE',(tbb.stop_time-tbb.start_time)*24,'MEASURE',tbb.measure) Hours ,flv.meaning ,tar.attribute2 ,tar.attribute3 ,paop.project_number ,paot.task_number ,tap.attribute3 ,tbb.date_from ,trunc(tbbd.start_time) ,tbbd.parent_building_block_id ,tbb.time_building_block_Id ,tbb.object_version_number from hxc_time_building_blocks tbb ,hxc_time_building_blocks tbbd ,hxc_time_attribute_usages taup ,hxc_time_attribute_usages taur ,hxc_time_attributes tap ,hxc_time_attributes tar ,hxc_base_pa_online_projects_v paop ,hxc_base_pa_online_tasks_v paot ,fnd_lookup_values flv where tap.attribute_category = 'PROJECTS' and tar.attribute_category = 'REASON' and tar.attribute3 = 'CHANGE' and tap.time_attribute_id = taup.time_attribute_id and tar.time_attribute_id = taur.time_attribute_id and taup.time_building_block_id = tbb.time_building_block_id and taup.time_building_block_ovn = tbb.object_version_number and taur.time_building_block_id = tbb.time_building_block_id and taur.time_building_block_ovn = tbb.object_version_number and tbb.scope = 'DETAIL' and tbb.parent_building_block_id = tbbd.time_building_block_id and tbb.parent_building_block_ovn = tbbd.object_version_number and tbbd.scope = 'DAY' and paop.project_id = to_number(tap.attribute1) and paot.task_id = to_number(tap.attribute2) and (tbb.object_version_number < (select max(object_version_number) from hxc_time_building_blocks tbb2 where tbb2.time_building_block_id = tbb.time_building_block_id) or tbb.date_to = hr_general.end_of_time) and tbb.object_version_number > 1 and flv.language = userenv('LANG') and flv.lookup_type = 'HXC_CHANGE_AUDIT_REASONS' and flv.lookup_code = tar.attribute1 union select distinct to_number(null) ,flv.meaning ,tar.attribute2 ,tar.attribute3 ,paop.project_number ,paot.task_number ,tap.attribute3 ,tbb.date_from ,trunc(tbbd.start_time) ,tbbd.parent_building_block_id ,tbb.time_building_block_Id ,tbb.object_version_number from hxc_time_building_blocks tbb ,hxc_time_building_blocks tbbd ,hxc_time_attribute_usages taup ,hxc_time_attribute_usages taur ,hxc_time_attributes tap ,hxc_time_attributes tar ,hxc_base_pa_online_projects_v paop ,hxc_base_pa_online_tasks_v paot ,fnd_lookup_values flv where tap.attribute_category = 'PROJECTS' and tar.attribute_category = 'REASON' and tar.attribute3 = 'CHANGE' and tap.time_attribute_id = taup.time_attribute_id and tar.time_attribute_id = taur.time_attribute_id and taup.time_building_block_id = tbb.time_building_block_id and taup.time_building_block_ovn = tbb.object_version_number and taur.time_building_block_id = tbb.time_building_block_id and taur.time_building_block_ovn = tbb.object_version_number and tbb.scope = 'DETAIL' and tbb.parent_building_block_id = tbbd.time_building_block_id and tbb.parent_building_block_ovn = tbbd.object_version_number and tbbd.scope = 'DAY' and paop.project_id = to_number(tap.attribute1) and paot.task_id = to_number(tap.attribute2) and tbb.object_version_number = (select max(object_version_number) from hxc_time_building_blocks tbb2 where tbb2.time_building_block_id = tbb.time_building_block_id) and tbb.date_to <> hr_general.end_of_time and flv.language = userenv('LANG') and flv.lookup_type = 'HXC_CHANGE_AUDIT_REASONS' and flv.lookup_code = tar.attribute1 union select distinct decode(tbb.type,'RANGE',(tbb.stop_time-tbb.start_time)*24,'MEASURE',tbb.measure) Hours ,flv.meaning ,tar.attribute2 ,tar.attribute3 ,paop.project_number ,paot.task_number ,tap.attribute3 ,tbb.date_from ,tbbd.start_time ,tbbd.parent_building_block_id ,tbb.time_building_block_Id ,tbb.object_version_number from hxc_time_building_blocks tbb ,hxc_time_building_blocks tbbd ,hxc_time_attribute_usages taup ,hxc_time_attribute_usages taur ,hxc_time_attributes tar ,hxc_time_attributes tap ,hxc_base_pa_online_projects_v paop ,hxc_base_pa_online_tasks_v paot ,fnd_lookup_values flv where tap.attribute_category = 'PROJECTS' and tar.attribute_category = 'REASON' and tap.time_attribute_id = taup.time_attribute_id and tar.time_attribute_id = taur.time_attribute_id and taup.time_building_block_id = tbb.time_building_block_id and taup.time_building_block_ovn = tbb.object_version_number and taur.time_building_block_id = tbb.time_building_block_id and taur.time_building_block_ovn = tbb.object_version_number and tbb.scope = 'DETAIL' and tbb.parent_building_block_id = tbbd.time_building_block_id and tbb.parent_building_block_ovn = tbbd.object_version_number and tbbd.scope = 'DAY' and paop.project_id = to_number(tap.attribute1) and paot.task_id = to_number(tap.attribute2) and tbb.object_version_number = 1 and flv.language = userenv('LANG') and flv.lookup_type = 'HXC_LATE_AUDIT_REASONS' and flv.lookup_code = tar.attribute1 union select distinct decode(tbb.type,'RANGE',(tbb.stop_time-tbb.start_time)*24,'MEASURE',tbb.measure) Hours ,null ,null ,null ,paop.project_number ,paot.task_number ,tap.attribute3 ,tbb.date_from ,trunc(tbbd.start_time) ,tbbd.parent_building_block_id ,tbb.time_building_block_Id ,tbb.object_version_number from hxc_time_building_blocks tbb ,hxc_time_building_blocks tbbd ,hxc_time_attribute_usages taup ,hxc_time_attributes tap ,hxc_base_pa_online_projects_v paop ,hxc_base_pa_online_tasks_v paot where tap.attribute_category = 'PROJECTS' and tap.time_attribute_id = taup.time_attribute_id and taup.time_building_block_id = tbb.time_building_block_id and taup.time_building_block_ovn = tbb.object_version_number and tbb.scope = 'DETAIL' and tbb.parent_building_block_id = tbbd.time_building_block_id and tbb.parent_building_block_ovn = tbbd.object_version_number and tbbd.scope = 'DAY' and paop.project_id = to_number(tap.attribute1) and paot.task_id = to_number(tap.attribute2) and tbb.object_version_number = 1 and exists (select 'Y' from hxc_time_building_blocks tbb2 ,hxc_time_attributes ta ,hxc_time_attribute_usages tau where tbb2.time_building_block_id = tbb.time_building_block_id and tbb2.object_version_number > 1 and tau.time_building_block_id = tbb2.time_building_block_id and tau.time_building_block_ovn = tbb2.object_version_number and tau.time_attribute_id = ta.time_attribute_id and ta.attribute_category = 'REASON' ) and not exists (select 'Y' from hxc_time_attributes ta ,hxc_time_attribute_usages tau where ta.time_attribute_id = tau.time_attribute_id and tau.time_building_block_id = tbb.time_building_block_id and tau.time_building_block_ovn = tbb.object_version_number and ta.attribute_category = 'REASON' )

View Text - HTML Formatted

SELECT DISTINCT DECODE(TBB.TYPE
, 'RANGE'
, (TBB.STOP_TIME-TBB.START_TIME)*24
, 'MEASURE'
, TBB.MEASURE) HOURS
, FLV.MEANING
, TAR.ATTRIBUTE2
, TAR.ATTRIBUTE3
, PAOP.PROJECT_NUMBER
, PAOT.TASK_NUMBER
, TAP.ATTRIBUTE3
, TBB.DATE_FROM
, TRUNC(TBBD.START_TIME)
, TBBD.PARENT_BUILDING_BLOCK_ID
, TBB.TIME_BUILDING_BLOCK_ID
, TBB.OBJECT_VERSION_NUMBER
FROM HXC_TIME_BUILDING_BLOCKS TBB
, HXC_TIME_BUILDING_BLOCKS TBBD
, HXC_TIME_ATTRIBUTE_USAGES TAUP
, HXC_TIME_ATTRIBUTE_USAGES TAUR
, HXC_TIME_ATTRIBUTES TAP
, HXC_TIME_ATTRIBUTES TAR
, HXC_BASE_PA_ONLINE_PROJECTS_V PAOP
, HXC_BASE_PA_ONLINE_TASKS_V PAOT
, FND_LOOKUP_VALUES FLV
WHERE TAP.ATTRIBUTE_CATEGORY = 'PROJECTS'
AND TAR.ATTRIBUTE_CATEGORY = 'REASON'
AND TAR.ATTRIBUTE3 = 'CHANGE'
AND TAP.TIME_ATTRIBUTE_ID = TAUP.TIME_ATTRIBUTE_ID
AND TAR.TIME_ATTRIBUTE_ID = TAUR.TIME_ATTRIBUTE_ID
AND TAUP.TIME_BUILDING_BLOCK_ID = TBB.TIME_BUILDING_BLOCK_ID
AND TAUP.TIME_BUILDING_BLOCK_OVN = TBB.OBJECT_VERSION_NUMBER
AND TAUR.TIME_BUILDING_BLOCK_ID = TBB.TIME_BUILDING_BLOCK_ID
AND TAUR.TIME_BUILDING_BLOCK_OVN = TBB.OBJECT_VERSION_NUMBER
AND TBB.SCOPE = 'DETAIL'
AND TBB.PARENT_BUILDING_BLOCK_ID = TBBD.TIME_BUILDING_BLOCK_ID
AND TBB.PARENT_BUILDING_BLOCK_OVN = TBBD.OBJECT_VERSION_NUMBER
AND TBBD.SCOPE = 'DAY'
AND PAOP.PROJECT_ID = TO_NUMBER(TAP.ATTRIBUTE1)
AND PAOT.TASK_ID = TO_NUMBER(TAP.ATTRIBUTE2)
AND (TBB.OBJECT_VERSION_NUMBER < (SELECT MAX(OBJECT_VERSION_NUMBER)
FROM HXC_TIME_BUILDING_BLOCKS TBB2
WHERE TBB2.TIME_BUILDING_BLOCK_ID = TBB.TIME_BUILDING_BLOCK_ID) OR TBB.DATE_TO = HR_GENERAL.END_OF_TIME)
AND TBB.OBJECT_VERSION_NUMBER > 1
AND FLV.LANGUAGE = USERENV('LANG')
AND FLV.LOOKUP_TYPE = 'HXC_CHANGE_AUDIT_REASONS'
AND FLV.LOOKUP_CODE = TAR.ATTRIBUTE1 UNION SELECT DISTINCT TO_NUMBER(NULL)
, FLV.MEANING
, TAR.ATTRIBUTE2
, TAR.ATTRIBUTE3
, PAOP.PROJECT_NUMBER
, PAOT.TASK_NUMBER
, TAP.ATTRIBUTE3
, TBB.DATE_FROM
, TRUNC(TBBD.START_TIME)
, TBBD.PARENT_BUILDING_BLOCK_ID
, TBB.TIME_BUILDING_BLOCK_ID
, TBB.OBJECT_VERSION_NUMBER
FROM HXC_TIME_BUILDING_BLOCKS TBB
, HXC_TIME_BUILDING_BLOCKS TBBD
, HXC_TIME_ATTRIBUTE_USAGES TAUP
, HXC_TIME_ATTRIBUTE_USAGES TAUR
, HXC_TIME_ATTRIBUTES TAP
, HXC_TIME_ATTRIBUTES TAR
, HXC_BASE_PA_ONLINE_PROJECTS_V PAOP
, HXC_BASE_PA_ONLINE_TASKS_V PAOT
, FND_LOOKUP_VALUES FLV
WHERE TAP.ATTRIBUTE_CATEGORY = 'PROJECTS'
AND TAR.ATTRIBUTE_CATEGORY = 'REASON'
AND TAR.ATTRIBUTE3 = 'CHANGE'
AND TAP.TIME_ATTRIBUTE_ID = TAUP.TIME_ATTRIBUTE_ID
AND TAR.TIME_ATTRIBUTE_ID = TAUR.TIME_ATTRIBUTE_ID
AND TAUP.TIME_BUILDING_BLOCK_ID = TBB.TIME_BUILDING_BLOCK_ID
AND TAUP.TIME_BUILDING_BLOCK_OVN = TBB.OBJECT_VERSION_NUMBER
AND TAUR.TIME_BUILDING_BLOCK_ID = TBB.TIME_BUILDING_BLOCK_ID
AND TAUR.TIME_BUILDING_BLOCK_OVN = TBB.OBJECT_VERSION_NUMBER
AND TBB.SCOPE = 'DETAIL'
AND TBB.PARENT_BUILDING_BLOCK_ID = TBBD.TIME_BUILDING_BLOCK_ID
AND TBB.PARENT_BUILDING_BLOCK_OVN = TBBD.OBJECT_VERSION_NUMBER
AND TBBD.SCOPE = 'DAY'
AND PAOP.PROJECT_ID = TO_NUMBER(TAP.ATTRIBUTE1)
AND PAOT.TASK_ID = TO_NUMBER(TAP.ATTRIBUTE2)
AND TBB.OBJECT_VERSION_NUMBER = (SELECT MAX(OBJECT_VERSION_NUMBER)
FROM HXC_TIME_BUILDING_BLOCKS TBB2
WHERE TBB2.TIME_BUILDING_BLOCK_ID = TBB.TIME_BUILDING_BLOCK_ID)
AND TBB.DATE_TO <> HR_GENERAL.END_OF_TIME
AND FLV.LANGUAGE = USERENV('LANG')
AND FLV.LOOKUP_TYPE = 'HXC_CHANGE_AUDIT_REASONS'
AND FLV.LOOKUP_CODE = TAR.ATTRIBUTE1 UNION SELECT DISTINCT DECODE(TBB.TYPE
, 'RANGE'
, (TBB.STOP_TIME-TBB.START_TIME)*24
, 'MEASURE'
, TBB.MEASURE) HOURS
, FLV.MEANING
, TAR.ATTRIBUTE2
, TAR.ATTRIBUTE3
, PAOP.PROJECT_NUMBER
, PAOT.TASK_NUMBER
, TAP.ATTRIBUTE3
, TBB.DATE_FROM
, TBBD.START_TIME
, TBBD.PARENT_BUILDING_BLOCK_ID
, TBB.TIME_BUILDING_BLOCK_ID
, TBB.OBJECT_VERSION_NUMBER
FROM HXC_TIME_BUILDING_BLOCKS TBB
, HXC_TIME_BUILDING_BLOCKS TBBD
, HXC_TIME_ATTRIBUTE_USAGES TAUP
, HXC_TIME_ATTRIBUTE_USAGES TAUR
, HXC_TIME_ATTRIBUTES TAR
, HXC_TIME_ATTRIBUTES TAP
, HXC_BASE_PA_ONLINE_PROJECTS_V PAOP
, HXC_BASE_PA_ONLINE_TASKS_V PAOT
, FND_LOOKUP_VALUES FLV
WHERE TAP.ATTRIBUTE_CATEGORY = 'PROJECTS'
AND TAR.ATTRIBUTE_CATEGORY = 'REASON'
AND TAP.TIME_ATTRIBUTE_ID = TAUP.TIME_ATTRIBUTE_ID
AND TAR.TIME_ATTRIBUTE_ID = TAUR.TIME_ATTRIBUTE_ID
AND TAUP.TIME_BUILDING_BLOCK_ID = TBB.TIME_BUILDING_BLOCK_ID
AND TAUP.TIME_BUILDING_BLOCK_OVN = TBB.OBJECT_VERSION_NUMBER
AND TAUR.TIME_BUILDING_BLOCK_ID = TBB.TIME_BUILDING_BLOCK_ID
AND TAUR.TIME_BUILDING_BLOCK_OVN = TBB.OBJECT_VERSION_NUMBER
AND TBB.SCOPE = 'DETAIL'
AND TBB.PARENT_BUILDING_BLOCK_ID = TBBD.TIME_BUILDING_BLOCK_ID
AND TBB.PARENT_BUILDING_BLOCK_OVN = TBBD.OBJECT_VERSION_NUMBER
AND TBBD.SCOPE = 'DAY'
AND PAOP.PROJECT_ID = TO_NUMBER(TAP.ATTRIBUTE1)
AND PAOT.TASK_ID = TO_NUMBER(TAP.ATTRIBUTE2)
AND TBB.OBJECT_VERSION_NUMBER = 1
AND FLV.LANGUAGE = USERENV('LANG')
AND FLV.LOOKUP_TYPE = 'HXC_LATE_AUDIT_REASONS'
AND FLV.LOOKUP_CODE = TAR.ATTRIBUTE1 UNION SELECT DISTINCT DECODE(TBB.TYPE
, 'RANGE'
, (TBB.STOP_TIME-TBB.START_TIME)*24
, 'MEASURE'
, TBB.MEASURE) HOURS
, NULL
, NULL
, NULL
, PAOP.PROJECT_NUMBER
, PAOT.TASK_NUMBER
, TAP.ATTRIBUTE3
, TBB.DATE_FROM
, TRUNC(TBBD.START_TIME)
, TBBD.PARENT_BUILDING_BLOCK_ID
, TBB.TIME_BUILDING_BLOCK_ID
, TBB.OBJECT_VERSION_NUMBER
FROM HXC_TIME_BUILDING_BLOCKS TBB
, HXC_TIME_BUILDING_BLOCKS TBBD
, HXC_TIME_ATTRIBUTE_USAGES TAUP
, HXC_TIME_ATTRIBUTES TAP
, HXC_BASE_PA_ONLINE_PROJECTS_V PAOP
, HXC_BASE_PA_ONLINE_TASKS_V PAOT
WHERE TAP.ATTRIBUTE_CATEGORY = 'PROJECTS'
AND TAP.TIME_ATTRIBUTE_ID = TAUP.TIME_ATTRIBUTE_ID
AND TAUP.TIME_BUILDING_BLOCK_ID = TBB.TIME_BUILDING_BLOCK_ID
AND TAUP.TIME_BUILDING_BLOCK_OVN = TBB.OBJECT_VERSION_NUMBER
AND TBB.SCOPE = 'DETAIL'
AND TBB.PARENT_BUILDING_BLOCK_ID = TBBD.TIME_BUILDING_BLOCK_ID
AND TBB.PARENT_BUILDING_BLOCK_OVN = TBBD.OBJECT_VERSION_NUMBER
AND TBBD.SCOPE = 'DAY'
AND PAOP.PROJECT_ID = TO_NUMBER(TAP.ATTRIBUTE1)
AND PAOT.TASK_ID = TO_NUMBER(TAP.ATTRIBUTE2)
AND TBB.OBJECT_VERSION_NUMBER = 1
AND EXISTS (SELECT 'Y'
FROM HXC_TIME_BUILDING_BLOCKS TBB2
, HXC_TIME_ATTRIBUTES TA
, HXC_TIME_ATTRIBUTE_USAGES TAU
WHERE TBB2.TIME_BUILDING_BLOCK_ID = TBB.TIME_BUILDING_BLOCK_ID
AND TBB2.OBJECT_VERSION_NUMBER > 1
AND TAU.TIME_BUILDING_BLOCK_ID = TBB2.TIME_BUILDING_BLOCK_ID
AND TAU.TIME_BUILDING_BLOCK_OVN = TBB2.OBJECT_VERSION_NUMBER
AND TAU.TIME_ATTRIBUTE_ID = TA.TIME_ATTRIBUTE_ID
AND TA.ATTRIBUTE_CATEGORY = 'REASON' )
AND NOT EXISTS (SELECT 'Y'
FROM HXC_TIME_ATTRIBUTES TA
, HXC_TIME_ATTRIBUTE_USAGES TAU
WHERE TA.TIME_ATTRIBUTE_ID = TAU.TIME_ATTRIBUTE_ID
AND TAU.TIME_BUILDING_BLOCK_ID = TBB.TIME_BUILDING_BLOCK_ID
AND TAU.TIME_BUILDING_BLOCK_OVN = TBB.OBJECT_VERSION_NUMBER
AND TA.ATTRIBUTE_CATEGORY = 'REASON' )