• Query to check if for a person a Timecard has been submitted or not for the start and stop time

SELECT time_building_block_id, object_version_number, start_time,

comment_text, created_by, creation_date, last_updated_by,

last_update_date, last_update_login

FROM hxc_time_building_blocks

WHERE resource_id = <employee id>

AND SCOPE = 'TIMECARD'

AND start_time = <Start Time>

AND stop_time = <stop Time>

AND date_to = hr_general.end_of_time;

• Query to get the Complete Timecard Data:

SELECT *

FROM HXC_TIME_BUILDING_BLOCKS

WHERE date_to=hr_general.end_of_time

CONNECT BY prior time_building_block_id=parent_building_block_id

AND object_version_number=parent_building_block_ovn

START WITH time_building_block_id=< Timecard Id>

ORDER BY 1

• Query to get all Attributes Information for a complete Timecard

SELECT htb.resource_id Person_id,

htb.start_time Start_time, htb.stop_time Stop_time,

htb2.time_building_block_id, hta.time_attribute_id,

htb2.measure, htb2.start_time, htb2.stop_time,

hta.Attribute_category

FROM hxc_time_building_blocks htb,

hxc_time_building_blocks htb1,

hxc_time_building_blocks htb2,

hxc_time_attribute_usages htau,

hxc_time_attributes hta

WHERE htb1.parent_building_block_id = htb.time_building_block_id

AND htb1.parent_building_block_ovn = htb.object_version_number

AND htb.date_to = hr_general.end_of_time

AND htb.SCOPE = 'TIMECARD'

AND htb1.SCOPE = 'DAY'

AND htb1.date_to = hr_general.end_of_time

AND htb2.parent_building_block_id = htb1.time_building_block_id

AND htb2.parent_building_block_ovn = htb1.object_version_number

AND htb2.SCOPE = 'DETAIL'

AND htb2.date_to = hr_general.end_of_time

AND htau.time_building_block_id = htb2.time_building_block_id

AND htau.time_building_block_ovn = htb2.object_version_number

AND htau.time_attribute_id = hta.time_attribute_id

AND TRUNC(htb.start_time) = <start Date>

AND TRUNC(htb.stop_time) = <End Date>

AND htb.resource_id = <resource_id>

Use the preference to get the setup for a particular resource id

hxc_preference_evaluation.resource_preferences

(<.Resource_id>,'TC_W_TCRD_LAYOUT',1,SYSDATE);

Table : hxc_pref_definitions

l_pref_table hxc_preference_evaluation.t_pref_table;

hxc_preference_evaluation.resource_preferences(<resource_id>,l_pref_table,sysdate);

l_index := l_pref_table.first;

LOOP

EXIT WHEN

(NOT l_pref_table.exists(l_index));

IF (l_pref_table(l_index).preference_code = 'TC_W_TCRD_LAYOUT') THEN ....

l_index := l_pref_table.next(l_index);

END LOOP;