View: HXC_TIMECARD_AUDIT_SUMMARY

Product:	HXC - Time and Labor Engine
Description:	Timecard changes summary
Implementation/DBA Data:	APPS.HXC_TIMECARD_AUDIT_SUMMARY

View Text

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' )

Columns