Scribd
Upload
182 views32 pages

OTL Code and Timecard Queries Guide

This document provides details about an application for time and labor management. It includes the application codes, interfaces, responsibilities, and sample queries. The application codes are HXC for time and labor and HXT for time management. The interfaces include a professional user interface, self-service application, and support for disconnected entry. Responsibilities involve self-service time and expenses, being an OTL application developer and timekeeper, and mass timecard approval. Sample queries are provided to check for a submitted timecard, retrieve full timecard data, and get attribute information for a timecard. Preferences for an individual's timecard layout can be retrieved from tables.

Uploaded by

gireeshkr
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
182 views32 pages

OTL Code and Timecard Queries Guide

This document provides details about an application for time and labor management. It includes the application codes, interfaces, responsibilities, and sample queries. The application codes are HXC for time and labor and HXT for time management. The interfaces include a professional user interface, self-service application, and support for disconnected entry. Responsibilities involve self-service time and expenses, being an OTL application developer and timekeeper, and mass timecard approval. Sample queries are provided to check for a submitted timecard, retrieve full timecard data, and get attribute information for a timecard. Preferences for an individual's timecard layout can be retrieved from tables.

Uploaded by

gireeshkr
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Applications Details

 Schema/ Product Code


HXC – Time and Labor

HXT – Time Management

 Application Interfaces

- Professional User Interface

- Self Service Application

- Disconnected Entry

 Responsibilities

- Self Service Time and Expenses

- OTL Application Developer

- OTL Timekeeper

- Mass Timecard Approval


 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,

[Link], 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 [Link] = 'TIMECARD'

AND [Link] = '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 [Link] = '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;

You might also like