Thursday, October 29, 2009

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
);

1 comment:

oracle procurement said...

Credit rating score reporting isn't always something however oracle fusion procurement way wherein viable avail with alternatives to provide the client or the economic enterprise agency with all of the credit score and debt facts
thank regards
Oracle Fusion Cloud procurement Training
Oracle Fusion procurement Training Institute