View: HXC_TC_AUDIT_SUMMARY

Product:	HXC - Time and Labor Engine
Description:
Implementation/DBA Data:	APPS.HXC_TC_AUDIT_SUMMARY

View Text

SELECT DISTINCT B.TIME_ATTRIBUTE_ID TIME_ATTRIBUTE_ID
, AUB.TIME_BUILDING_BLOCK_ID DETAIL_BUILDING_BLOCK_ID
, BBIT1.BLD_BLK_INFO_TYPE BLD_BLK_INFO_TYPE
, B.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, DECODE (B.ATTRIBUTE3
, 'LATE'
, ( SELECT FLV.MEANING
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LANGUAGE = USERENV('LANG')
AND FLV.LOOKUP_TYPE ='HXC_LATE_AUDIT_REASONS'
AND FLV.LOOKUP_CODE = B.ATTRIBUTE1
AND B.ATTRIBUTE_CATEGORY = 'REASON'
AND FLV.VIEW_APPLICATION_ID = 3
AND FLV.SECURITY_GROUP_ID = DECODE(SUBSTR(USERENV('CLIENT_INFO')
, 55
, 1)
, ''
, 0
, NULL
, 0
, '0'
, 0
, FND_GLOBAL.LOOKUP_SECURITY_GROUP(LOOKUP_TYPE
, VIEW_APPLICATION_ID)) UNION SELECT MEANING
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE = 'HXC_LATE_AUDIT_REASONS'
AND FLV.TAG='TK'
AND FLV.LOOKUP_CODE = B.ATTRIBUTE1)
, 'CHANGE'
, ( SELECT FLV.MEANING
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LANGUAGE = USERENV('LANG')
AND FLV.LOOKUP_TYPE ='HXC_CHANGE_AUDIT_REASONS'
AND FLV.LOOKUP_CODE = B.ATTRIBUTE1
AND B.ATTRIBUTE_CATEGORY = 'REASON'
AND FLV.VIEW_APPLICATION_ID = 3
AND FLV.SECURITY_GROUP_ID = DECODE(SUBSTR(USERENV('CLIENT_INFO')
, 55
, 1)
, ''
, 0
, NULL
, 0
, '0'
, 0
, FND_GLOBAL.LOOKUP_SECURITY_GROUP(LOOKUP_TYPE
, VIEW_APPLICATION_ID)) UNION SELECT FLV.MEANING
FROM FND_LOOKUP_VALUES FLV
WHERE FLV.LOOKUP_TYPE = 'HXC_CHANGE_AUDIT_REASONS'
AND FLV.TAG='TK'
AND FLV.LOOKUP_CODE = B.ATTRIBUTE1)
, B.ATTRIBUTE1 ) ATTRIBUTE1
, B.ATTRIBUTE2 ATTRIBUTE2
, B.ATTRIBUTE3 ATTRIBUTE3
, B.ATTRIBUTE4 ATTRIBUTE4
, B.ATTRIBUTE5 ATTRIBUTE5
, B.ATTRIBUTE6 ATTRIBUTE6
, B.ATTRIBUTE7 ATTRIBUTE7
, B.ATTRIBUTE8 ATTRIBUTE8
, B.ATTRIBUTE9 ATTRIBUTE9
, B.ATTRIBUTE10 ATTRIBUTE10
, B.ATTRIBUTE11 ATTRIBUTE11
, B.ATTRIBUTE12 ATTRIBUTE12
, B.ATTRIBUTE13 ATTRIBUTE13
, B.ATTRIBUTE14 ATTRIBUTE14
, B.ATTRIBUTE15 ATTRIBUTE15
, B.ATTRIBUTE16 ATTRIBUTE16
, B.ATTRIBUTE17 ATTRIBUTE17
, B.ATTRIBUTE18 ATTRIBUTE18
, B.ATTRIBUTE19 ATTRIBUTE19
, B.ATTRIBUTE20 ATTRIBUTE20
, B.ATTRIBUTE21 ATTRIBUTE21
, B.ATTRIBUTE22 ATTRIBUTE22
, B.ATTRIBUTE23 ATTRIBUTE23
, B.ATTRIBUTE24 ATTRIBUTE24
, B.ATTRIBUTE25 ATTRIBUTE25
, B.ATTRIBUTE26 ATTRIBUTE26
, B.ATTRIBUTE27 ATTRIBUTE27
, B.ATTRIBUTE28 ATTRIBUTE28
, B.ATTRIBUTE29 ATTRIBUTE29
, B.ATTRIBUTE30 ATTRIBUTE30
, AUB.TIME_BUILDING_BLOCK_OVN DETAIL_TBB_OVN
, TBBD.PARENT_BUILDING_BLOCK_ID
, DECODE (TBB.TYPE
, 'RANGE'
, (TBB.STOP_TIME - TBB.START_TIME) * 24
, 'MEASURE'
, TBB.MEASURE )
, TBB.DATE_FROM
, TBBD.START_TIME
, TAP.APPLICATION_PERIOD_ID
, 'N' NEW
, 'N' CHANGED
FROM HXC_TIME_ATTRIBUTES B
, HXC_TIME_ATTRIBUTE_USAGES AUB
, HXC_BLD_BLK_INFO_TYPES BBIT1
, HXC_TIME_BUILDING_BLOCKS TBB
, HXC_TIME_BUILDING_BLOCKS TBBD
, HXC_TC_AP_LINKS TAP
WHERE AUB.TIME_BUILDING_BLOCK_ID = TBB.TIME_BUILDING_BLOCK_ID
AND AUB.TIME_BUILDING_BLOCK_OVN = TBB.OBJECT_VERSION_NUMBER
AND AUB.TIME_ATTRIBUTE_ID = B.TIME_ATTRIBUTE_ID
AND B.BLD_BLK_INFO_TYPE_ID = BBIT1.BLD_BLK_INFO_TYPE_ID
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 B.ATTRIBUTE_CATEGORY <> 'SECURITY'
AND TBBD.SCOPE = 'DAY'
AND ( TAP.TIMECARD_ID (+) = TBBD.PARENT_BUILDING_BLOCK_ID
AND ( EXISTS (SELECT 1
FROM HXC_APP_PERIOD_SUMMARY APSUM
WHERE TAP.APPLICATION_PERIOD_ID = APSUM.APPLICATION_PERIOD_ID
AND TBBD.START_TIME BETWEEN APSUM.START_TIME
AND APSUM.STOP_TIME ) OR TAP.TIMECARD_ID IS NULL ) )
AND EXISTS (SELECT 'Y'
FROM HXC_TIME_ATTRIBUTES TA
, HXC_TIME_ATTRIBUTE_USAGES TAU
WHERE TAU.TIME_BUILDING_BLOCK_ID = TBB.TIME_BUILDING_BLOCK_ID
AND TA.TIME_ATTRIBUTE_ID = TAU.TIME_ATTRIBUTE_ID
AND TA.ATTRIBUTE_CATEGORY = 'REASON'
AND ( (B.ATTRIBUTE3 NOT IN ('LATE'
, 'CHANGE')
AND TAU.TIME_BUILDING_BLOCK_OVN IN ( TBB.OBJECT_VERSION_NUMBER
, (TBB.OBJECT_VERSION_NUMBER + 1) )) OR B.ATTRIBUTE3 IN ('LATE'
, 'CHANGE') ) ) ORDER BY START_TIME
, DETAIL_BUILDING_BLOCK_ID
, DETAIL_TBB_OVN

Columns