OTL Timecard Data Model

The major tables used by timecard in OTL are

hxc_time_building_blocks
hxc_time_attribute_usages
hxc_time_attributes

HXC_TIME_BUILDING_BLOCKS
This table stores the actual time data (start_time, stop_time, hours) for the timecard. The table has a hierarchial way of storing the data.
The column scope determines the level of data stored. Following are the important scope values in terms of their order in hierarchy (top to bottom)

1. TIMECARD
2. DAY
3. DETAIL

The scope TIMECARD is highest level which stores information about the header details of timecard like total hours worked, comments, start date of timecard (start_time column), end date of timecard (end_time column).

The scope DAY is child of TIMECARD which header information for the particular DAY of timecard. Please note that this scope entry just stores information about a particular DAY and not the start_time and end_time of employee that DAY. One of the reason can be that for a particular person the end_time may go to next day in case of working through midnight.

The scope DETAIL stores the actual start_time, end_time or hours of employee on a day. The column TYPE denotes whether start_time/end_time are stored or just hours. In DETAIL, time can be stored either as start_time/end_time (against TYPE = 'RANGE') or hours worked (against TYPE = 'HOURS').

The hierarchy in time_building_blocks is created using the column parent_building_block_id. So scope = 'DAY' row will have praent_building_block_id as time_building_block_id of corresponding row SCOPE = 'TIMECARD' and scope = 'DETAIL' will have parent_building_block_id as time_building_block_id of scope = 'DAY'.

In hxc_time_building_blocks, an update to timecard doesnt update the current row but it creates another row with same time_building_block_id but increases the object_version_number by 1.

Example: If there is row for TIMECARD scope saying start_time 25-Oct-09 and end_time of 01-Nov-09, hxc_time_building_id =1 and object_version_number = 1. If we update the comments for this timecard, it will create another row with new comments (in comments column), start_time 25-Oct-09 and end_time of 01-Nov-09, hxc_time_building_id =1 and object_version_number = 2. It also updates the date_from and date_to column of hxc_time_building_blocks to identify the row which is active currently. This type of data model has been created to keep history of changes made (say for audit purposes).

Below is an example query of how data can be retrived from hxc_time_building_blocks for a WEEKLY timecard

SELECT
MAX(DECODE(TRIM(TO_CHAR(TRUNC(b1.start_time),'DAY')),'SUNDAY',TO_CHAR(b2.start_time,'HH24:MI'))) sun_start,

MAX(DECODE(TRIM(TO_CHAR(TRUNC(b1.start_time),'DAY')),'SUNDAY',TO_CHAR(b2.stop_time,'HH24:MI'))) sun_stop
..
..
..

MAX(DECODE(TRIM(TO_CHAR(TRUNC(b1.start_time),'DAY')),'SATURDAY',TO_CHAR(b2.start_time,'HH24:MI'))) sat_start,

MAX(DECODE(TRIM(TO_CHAR(TRUNC(b1.start_time),'DAY')),'SATURDAY',TO_CHAR(b2.stop_time,'HH24:MI'))) sat_stop

FROM
hxc_time_building_blocks b1,
hxc_time_building_blocks b2

WHERE b1.time_building_block_id = b2.parent_building_block_id
AND b1.scope = 'DAY'
AND b2.scope = 'DETAIL'
AND b1.parent_building_block_id = p_time_building_block_id

-- p_time_building_block_id => scope = 'TIMECARD'
AND SYSDATE BETWEEN b1.date_from AND b1.date_to
AND SYSDATE BETWEEN b2.date_from AND b2.date_to
AND b2.parent_building_block_ovn = b1.object_version_number
AND b1.object_version_number = (SELECT MAX(object_version_number)
FROM hxc_time_building_blocks
WHERE time_building_block_id = b1.time_building_block_id)
AND b2.object_version_number = (SELECT MAX(object_version_number)
FROM hxc_time_building_blocks
WHERE time_building_block_id = b2.time_building_block_id)
GROUP BY b1.parent_building_block_id

HXC_TIME_ATTRIBUTE_USAGES

The table is a link between hxc_time_building_blocks and hxc_time_attribute_usages.

HXC_TIME_ATTRIBUTES

This table holds the information about the time attributes against an attribute_category. Please note that an update to an existing attribute rows results in new row with new time_attribute_id and hxc_time_attribute_usages gets updated to point to this new row. Below qeury retrieves data for a particular attributes for a time_building_block_id

SELECT hta1.* FROM
hxc_time_attribute_usages htau,
hxc_time_attributes hta1
WHERE htau.time_attribute_id = hta1.time_attribute_id
AND hta1.attribute_category = 'ATTR_CAT'
AND htau.time_building_block_id = p_time_building_block_id
AND htau.time_building_block_ovn = p_time_building_block_n
AND NVL(hta1.object_version_number,-1) = (SELECT NVL(MAX(object_version_number),-1)
FROM hxc_time_attributes
WHERE time_attribute_id = hta1.time_attribute_id)
AND NVL(hta1.time_attribute_id,-1) = (SELECT NVL(MAX(ta.time_attribute_id),-1)
FROM hxc_time_attributes ta,hxc_time_attribute_usages tau
WHERE ta.time_attribute_id = tau.time_attribute_id
AND ta.attribute_category = 'ATTR_CAT'
AND htau.time_building_block_id = tau.time_building_block_id
AND htau.time_building_block_ovn = tau.time_building_block_ovn
);

Oracle Apps Table Registeration Script

DECLARE
l_schema VARCHAR2(30) := 'XX';
l_tab_name VARCHAR2(30) := 'XX_CUSTOM_TABLE';
CURSOR c_get_tab_info IS
SELECT next_extent,
pct_free,
pct_used
FROM all_tables
WHERE owner = l_schema
AND table_name = l_tab_name;
CURSOR c_get_col_info IS
SELECT column_name,
column_id,
data_type,
data_length,
nullable,
--, data_precision,
data_scale
FROM all_tab_columns
WHERE owner = l_schema
AND table_name = l_tab_name
ORDER BY column_id;

CURSOR c_get_pk_info IS
SELECT constraint_name,
DECODE(status, 'ENABLED', 'Y', 'N') enabled_flag
FROM all_constraints
WHERE owner = l_schema
AND table_name = l_tab_name
AND constraint_type = 'P';

CURSOR c_get_pk_col_info(cp_pk_name VARCHAR2) IS
SELECT column_name,
position
FROM all_cons_columns
WHERE owner = l_schema
AND table_name = l_tab_name
AND constraint_name = cp_pk_name
ORDER BY position;

BEGIN

FOR tab_rec IN c_get_tab_info LOOP

ad_dd.register_table(p_appl_short_name => l_schema
,p_tab_name => l_tab_name
,p_tab_type => 'T'
,p_next_extent => tab_rec.next_extent
,p_pct_free => tab_rec.pct_free
,p_pct_used => tab_rec.pct_used);

END LOOP;

FOR col_rec IN c_get_col_info
LOOP

ad_dd.register_column(p_appl_short_name => l_schema
,p_tab_name => l_tab_name
,p_col_name => col_rec.column_name
,p_col_seq => col_rec.column_id
,p_col_type => col_rec.data_type
,p_col_width => col_rec.data_length
,p_nullable => col_rec.nullable
,p_translate => 'N'
,p_precision => col_rec.data_precision
,p_scale => col_rec.data_scale);

END LOOP;

FOR pk_rec IN c_get_pk_info
LOOP
ad_dd.register_primary_key(p_appl_short_name => l_schema,
p_key_name => pk_rec.constraint_name,
p_tab_name => l_tab_name,
p_description => NULL,
p_key_type => 'S',
p_audit_flag => 'Y',
p_enabled_flag => pk_rec.enabled_flag);


FOR pk_col_rec IN c_get_pk_col_info(pk_rec.constraint_name)
LOOP
ad_dd.register_primary_key_column
(p_appl_short_name => l_schema,
p_key_name => pk_rec.constraint_name,
p_tab_name => l_tab_name,
p_col_name => pk_col_rec.column_name,
p_col_sequence => pk_col_rec.position);
END LOOP;

END LOOP;

END;
/
COMMIT;

My HRMS Learnings

• The p_validate parameters in Oracle HRMS API is used in case we want to validate the data. If set to TRUE, it validates the parameters passed and returns validation errors if any but doesnt commit the record into database.
• Various date track modes in Oracle API are: UPDATE --> To Update a record at particular date, CORRECTION --> To correct a record at a particular date, UPDATE_CHANGE_INSERT (Insert) - The changes that the user makes remain in effect until the effective end date of the current record, UPDATE_OVERRIDE (Replace) - The user's changes take effect from now until the end date of the last record in the future. All future dated changes are deleted.
• You can rollback termination using the hrempter.cancel_termination API but it is not a supported public API.