SELECT 'DYNAMIC|'||LOOKUP_CODE VALUE , MEANING DISPLAY_VALUE , FND_GLOBAL.EMPLOYEE_ID RESOURCE_ID , 'PERSON' RESOURCE_TYPE , NULL LAYOUT_ID FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'HXC_DYNAMIC_TEMPLATES' AND ENABLED_FLAG = 'Y' AND SYSDATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE , HR_GENERAL.END_OF_TIME) AND LOOKUP_CODE <> 'APP|HXT' UNION ALL SELECT 'PRIVATE|'||TBB.TIME_BUILDING_BLOCK_ID VALUE , TA.ATTRIBUTE1 DISPLAY_VALUE , TBB.RESOURCE_ID RESOURCE_ID , TBB.RESOURCE_TYPE RESOURCE_TYPE , TA2.ATTRIBUTE1 LAYOUT_ID FROM HXC_TIME_BUILDING_BLOCKS TBB , HXC_TIME_ATTRIBUTE_USAGES TAU , HXC_TIME_ATTRIBUTES TA , HXC_TIME_ATTRIBUTES TA2 , HXC_TIME_ATTRIBUTE_USAGES TAU2 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 TBB.DATE_TO = HR_GENERAL.END_OF_TIME AND TA.ATTRIBUTE_CATEGORY = 'TEMPLATES' AND TA.ATTRIBUTE2 = 'PRIVATE' AND TBB.SCOPE = 'TIMECARD_TEMPLATE' AND TBB.DATE_TO = HR_GENERAL.END_OF_TIME AND TA2.TIME_ATTRIBUTE_ID = TAU2.TIME_ATTRIBUTE_ID AND TA2.ATTRIBUTE_CATEGORY = 'LAYOUT' AND TAU2.TIME_BUILDING_BLOCK_OVN = TBB.OBJECT_VERSION_NUMBER AND TBB.TIME_BUILDING_BLOCK_ID = TAU2.TIME_BUILDING_BLOCK_ID UNION ALL SELECT 'PUBLIC|'||TBB.TIME_BUILDING_BLOCK_ID VALUE , TA.ATTRIBUTE1 DISPLAY_VALUE , FND_GLOBAL.EMPLOYEE_ID RESOURCE_ID , TBB.RESOURCE_TYPE RESOURCE_TYPE , TA2.ATTRIBUTE1 LAYOUT_ID FROM HXC_TIME_BUILDING_BLOCKS TBB , HXC_TIME_ATTRIBUTES TA , HXC_TIME_ATTRIBUTE_USAGES TAU , HXC_TIME_ATTRIBUTES NTA , HXC_TIME_ATTRIBUTE_USAGES NTAU , HXC_TIME_ATTRIBUTES TA2 , HXC_TIME_ATTRIBUTE_USAGES TAU2 WHERE TBB.RESOURCE_ID=-1 AND NTAU.TIME_ATTRIBUTE_ID = NTA.TIME_ATTRIBUTE_ID AND NTAU.TIME_BUILDING_BLOCK_OVN = TBB.OBJECT_VERSION_NUMBER AND TBB.TIME_BUILDING_BLOCK_ID = NTAU.TIME_BUILDING_BLOCK_ID AND NTA.ATTRIBUTE_CATEGORY = 'SECURITY' AND NTA.ATTRIBUTE2= DECODE(FND_PROFILE.VALUE('HR_CROSS_BUSINESS_GROUP') , 'N' , FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID') , NTA.ATTRIBUTE2) AND TBB.TIME_BUILDING_BLOCK_ID = TAU.TIME_BUILDING_BLOCK_ID AND TAU.TIME_ATTRIBUTE_ID = TA.TIME_ATTRIBUTE_ID AND TAU.TIME_BUILDING_BLOCK_OVN = TBB.OBJECT_VERSION_NUMBER AND TBB.DATE_TO = HR_GENERAL.END_OF_TIME AND TA.ATTRIBUTE2 = 'PUBLIC' AND TA.ATTRIBUTE_CATEGORY = 'TEMPLATES' AND TBB.SCOPE = 'TIMECARD_TEMPLATE' AND TBB.DATE_TO = HR_GENERAL.END_OF_TIME AND TBB.TIME_BUILDING_BLOCK_ID = TAU2.TIME_BUILDING_BLOCK_ID AND TAU2.TIME_BUILDING_BLOCK_OVN = TBB.OBJECT_VERSION_NUMBER AND TA2.TIME_ATTRIBUTE_ID = TAU2.TIME_ATTRIBUTE_ID AND TA2.ATTRIBUTE_CATEGORY = 'LAYOUT'