Time card info will be stored in​​ HXC_TIMECARD_BUILDING_BLOCKS​​ in​​ hierarchical way. We have to join some other tables to receive the additional information.  ​​​​ 

We will try to identify​​ the backed end tables and columns when we perform below actions with the timecard.​​ 

• Create a Timecard

• Submit Timecard

• Reject Timecard

• Re-submit Timecard

• Transfer to Project​​ 

Create a Timecard:

Login to the Timecard page and select the corresponding time period.
If we have any predefined template, select under the template options and enter/select the project details.

After entering date and time, click on save button and now we see what tables are getting affected.

SELECT *  

  FROM hxc_time_building_blocks  -- Get the time_building_block_id

 WHERE     scope = 'TIMECARD'  

       AND resource_id = 1234  

       AND TRUNC (DATE_FROM) = TRUNC (SYSDATE);  

Pass the​​ resource id​​ (1234) and we can pass the​​ start time​​ and​​ stop time​​ which​​ is nothing but the period week start and end date.

Note: ​​ Approval_status column will be populated as​​ Working​​ and​​ PARENT_BUILDING_BLOCK_ID​​ column will be null, then we know its start of the hierarchical structure.

We look at the timecard page or time screen we are entering the hours based on the​​ Day​​ level so we could see day level details in the table. We have 7 days per a week, so we can see​​ 7 days in HXC_TIME_BUILDING_BLOCKS​​ table.

• SELECT *  

•   FROM hxc_time_building_blocks   -- Get the time_building_block_id

•  WHERE PARENT_BUILDING_BLOCK_ID = 123456 AND scope = 'DAY';  

If we look at the above screenshot and we can observe the SCOPE as a DAY, START_TIME as entire week days from Mon to Sun 7 days and PARENT_BUILDING_BLOCK_ID from the 1st​​ SQL statement.

Note: If we observe the​​ MEASURE​​ is​​ NULL​​ for all the days, we can find our​​ entered​​ hours in​​ MEASURE​​ column.​​ 

We have entered 8 hours for each day from Mon to Fri and Sat (19 Jan), Sun (20 Jan) we have not entered anything.​​ 

By using below SQL query we can get the time entry details

SELECT *  

  FROM hxc_time_building_blocks  

 WHERE     scope = 'DETAIL'  

       AND resource_id = 1234  

       AND TRUNC (DATE_FROM) = TRUNC(SYSDATE);

OR

SELECT h2.*  

  FROM hxc_time_building_blocks h1, hxc_time_building_blocks h2  

 WHERE     h1.PARENT_BUILDING_BLOCK_ID = 123456  

       AND h1.scope = 'DAY'  

       -- Details/Time Entry Hours  

       AND h1.time_building_block_id = h2.parent_building_block_id  

       AND h2.scope = 'DETAIL';  

Here if we notice we have entered hours only for 5 days and not 7 days, so we could see the records count from above screenshot is 5.

Now we need to get the project details for the corresponding week/time period week.

In order​​ to get those details we have to use the​​ HXC_TIME_ATTRIBUTE_USAGES​​ and​​ HXC_TIME_ATTRIBUTES​​ tables.

Before this we should know the attributes that we are using to store the attribute details.

select tms.approval_status as status,  

     TO_CHAR (tm1.start_time, 'DD-MON-YYYY ') period_starts,  

     TO_CHAR (tm2.stop_time, 'DD-MON-YYYY ') period_ends,  

     tms.submission_date,  

     tm1.time_building_block_id timecard_id,  

     NULL user_name,  

     papf.employee_number employee_number,  

     papf.first_name,  

     papf.last_name,  

     papf.middle_names,  

     papf.full_name,  

     tma.attribute1 project_number,  

     tma.attribute2 task_number,  

     tma.attribute3 expensiture_type,  

     tm3.measure hours,  

     sup.first_name sup_first_name,  

     sup.last_name sup_last_name,  

     sup.middle_names sup_middle_name,  

     sup.full_name sup_full_name  

from hxc_time_building_blocks tm1,  

     hxc_time_building_blocks tm2,  

     hxc_time_building_blocks tm3,  

     hxc_time_attribute_usages tmau,  

     hxc_time_attributes tma,  

     per_all_people_f papf,  

     per_all_assignments_f paaf,  

     per_all_people_f sup,  

     hxc_timecard_summary tms       

where 1 = 1  

-- tm1  

and tm1.resource_id = 1234  -- Pass the user id

and tm1.scope = 'TIMECARD'  

and tm1.date_to >= SYSDATE  

and tm1.time_building_block_id = 123456 -- Provide the​​ timecard_id

-- tm2  

and tm1.time_building_block_id = tm2.parent_building_block_id  

and tm2.scope = 'DAY'  

-- tm3  

and tm2.time_building_block_id = tm3.parent_building_block_id  

and tm3.scope = 'DETAIL'  

-- tmau  

and tm3.time_building_block_id = tmau.time_building_block_id  

-- tma  

and tmau.time_attribute_id = tma.time_attribute_id  

and tma.attribute_category = 'PROJECTS'  

-- papf, paaf, sup  

and tm1.resource_id = papf.person_id  

and sysdate between papf.effective_start_date and papf.effective_end_date  

and papf.person_id = paaf.person_id  

and sysdate between paaf.effective_start_date and paaf.effective_end_date  

and paaf.supervisor_id = sup.person_id  

and sysdate between sup.effective_start_date and sup.effective_end_date  

-- tms  

and tm1.time_building_block_id = tms.timecard_id;

This final SQL query when we club all the hierarchical building blocks tables.​​ 

Submit Timecard:

Click on submit button to submit the timecard.

SELECT *  

  FROM hxc_timecard_summary  

 WHERE timecard_id = 123456;  

If we check the​​ hxc_time_building_blocks​​ table we could notice multiple records for same date, the reason​​ is first we saved the timecard and now we submitted the timecard. So we could see same records but different status and versions.

SELECT *  

  FROM hxc_time_building_blocks   

 WHERE PARENT_BUILDING_BLOCK_ID = 123456 AND scope = 'DAY';  

When we submit​​ the timecard, if we have defined the approval flow the timecard will route to approver.​​ 

We do have an auto approver option also.

In this case we have defined our workflow approval process as based on the employee supervisor hierarchy.

We can check those​​ steps based on the below navigations.​​ 

Reject and Re-Submit Timecard:

What happens if approver rejects the timecard and what tables will be get updated.

• Timecard submitter will receive a notification with rejection comments.

• Go to the recent​​ timecard page and select the rejected timecard and click edit.

• Change the hours/project based on the comments and click on submits.

You can check the same way as step 1 and 2 to check flow of the in​​ HXC_TIME_BUILDING_BLOCKS​​ table for a Day and Detail scope.

We can check the​​ status in​​ HXC_TIMECARD_SUMMARY​​ table​​ also.

Transferred to Projects:

• Here once the timecard is approved then we have to move it to projects.

• To move it to the projects we have to run​​ PRC: Transaction Import.

• This process moves only approved timecards to the Projects.

• We can check the timecard status from recent timecard screen.​​ 

hxc_self_service_timecard.get_timecard_transferred_to  

           (timecard_id,  

           timecard_ovn) 

 ​​​​ 

​​ We can check in the system, is the timecard​​ moved to projects or not. Go to recent timecards and see the status.

​​ We can use above API to check the timecard status.

 ​​​​ 

Retrieval setups, we can define what type of timecards should be imported to the Projects when we run PRC: Transaction Import concurrent program.

Here we defined as only approved timecards should be imported to the Projects.  ​​​​