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_TC_AUDIT_SUMMARY

Source

View Text - Preformatted


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

View Text - HTML Formatted

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