Timecard API – Create, Update, Delete & Retrieve
This article talks about how to use Timecard API’s. By using these API’s we can create the data into Oracle timecard tables, update timecard information, delete timecard information and retrieval of timecard information by using API’s.
- Create Timecard
- Update Timecard
- Delete Timecard
- Retrieve Timecard Information
Create Timecard
We can create timecard in 2 ways before we begin creating timecard, timecard information will be stored in a table as blocks.
We have 3 blocks in timecard table(hxc_time_buidling_blocks)
- Scope = Timecard(this is the main information like a header)
- Scope = Day(7 days week)
- Scope = Details(How amny hours employee enters in ecah day while entreting timecard)
Process 1: Timecard, Day and Details creation controlled by API
If we want to create a short timecard like above, here employees enter only entire week hours at once then we can go with the below logic.
- We can create time using procedure called CREATE_TIME_ENTRY
hxc_timestore_deposit.create_time_entry - After time creation, we need to assign this time to projects, you can assign to specific Task, Project and Expenditure Type
hxc_timestore_deposit.create_attribute - Finally, submit the timecard to timestore by calling
hxc_self_service_timecard_api.execute_deposit_process
Along with time building blocks we have to create project, task and expenditure type information. All these information stored in OTL attributes.
DECLARE
-- PL/SQL Table to have the complete timecard details
l_tbl_timecard_info hxc_self_service_time_deposit.timecard_info;
-- PL/SQL Table to have all the attributes
l_tbl_attributes_info hxc_self_service_time_deposit.app_attributes_info;
-- PL/SQL Table have the error messages returned by the API
l_tbl_messages hxc_self_service_time_deposit.message_table;
-- Timecard ID, returned by the deposit process
l_new_timecard_id NUMBER;
-- Timecard Verison Number, returned by the deposit process
l_new_timecard_ovn NUMBER;
l_time_building_block_id hxc_time_building_blocks.time_building_block_id%TYPE;
l_error_message VARCHAR2 (4000);
BEGIN
FND_GLOBAL.APPS_INITIALIZE (user_id => 1234,
resp_id => 55598,
resp_appl_id => 809);
hxc_timestore_deposit.create_time_entry (
p_measure => 40, -- Total Week hours
p_day => FND_DATE.CANONICAL_TO_DATE ('2016/09/11'), -- Passing Week end date
p_resource_id => 1234,
p_app_blocks => l_tbl_timecard_info,
p_app_attributes => l_tbl_attributes_info,
p_time_building_block_id => l_time_building_block_id);
DBMS_OUTPUT.put_line (
'Time Building Block Id: ' || l_time_building_block_id);
-- Attribute 1 - Task Name, we need to pass task id
-- You can get the task details from PA_TASKS table for the specific project
hxc_timestore_deposit.create_attribute (
p_building_block_id => l_time_building_block_id, --returned by create_time_entry
p_attribute_name => 'Task_Id',
p_attribute_value => 123,
p_app_attributes => l_tbl_attributes_info);
-- Attribute2 - Project Name, we need to pass Project_id
-- You can get the project details from a pa_projects_all table for the specific project
hxc_timestore_deposit.create_attribute (
p_building_block_id => l_time_building_block_id, --returned by create_time_entry
p_attribute_name => 'Project_Id',
p_attribute_value => 12,
p_app_attributes => l_tbl_attributes_info);
-- Attribute3 - Expenditure Type, we need to pass the expenditure type
-- You can get the expenditure type details from pa_expenditure_types
hxc_timestore_deposit.create_attribute (
p_building_block_id => l_time_building_block_id, --returned by create_time_entry
p_attribute_name => 'Expenditure_Type',
p_attribute_value => 'ABC',
p_app_attributes => l_tbl_attributes_info);
-- Attribute4 - Expenditure Comment
hxc_timestore_deposit.create_attribute (
p_building_block_id => l_time_building_block_id, --returned by create_time_entry
p_attribute_name => 'Expenditure_Comment',
p_attribute_value => NULL,
p_app_attributes => l_tbl_attributes_info);
-- Attribute5, System Linkage Function
hxc_timestore_deposit.create_attribute (
p_building_block_id => l_time_building_block_id, --returned by create_time_entry
p_attribute_name => 'SYSTEM_LINKAGE_FUNCTION',
p_attribute_value => 'ST',
p_app_attributes => l_tbl_attributes_info);
-- Finally, submit the time to the time store
-- After successful execution of below API we could see the data in OTL Self Service i.e OTL base tables
hxc_timestore_deposit.execute_deposit_process (
p_validate => FALSE,
p_app_blocks => l_tbl_timecard_info,
p_app_attributes => l_tbl_attributes_info,
p_messages => l_tbl_messages,
p_mode => 'SUBMIT',
p_deposit_process => 'OTL Deposit Process',
p_timecard_id => l_new_timecard_id,
p_timecard_ovn => l_new_timecard_ovn);
-- To get an error message if any by above API
IF (l_tbl_messages.COUNT <> 0)
THEN -- messages have been returned
FOR i IN l_tbl_messages.FIRST .. l_tbl_messages.LAST
LOOP
EXIT WHEN (NOT l_tbl_messages.EXISTS (i));
l_error_message :=
fnd_message.get_string (
appin => l_tbl_messages (i).application_short_name,
namein => l_tbl_messages (i).message_name);
DBMS_OUTPUT.put_line (l_message);
END LOOP;
END IF;
COMMIT;
DBMS_OUTPUT.put_line (
'New Timecard Id: '|| l_new_timecard_id|| 'and New Timecard Version: '|| l_new_timecard_ovn);
/*
-- Another time entry procedure to create time with a date range instead of a measure
hxc_timestore_deposit.create_time_entry (
p_start_time=> fnd_date.canonical_to_date('2016/05/10 09:00:00'),
p_stop_time=> fnd_date.canonical_to_date('2016/05/10 17:00:00'),
p_resource_id=> 1234,
p_app_blocks=> l_tbl_timecard_info,
p_app_attributes=> l_tbl_attributes_info,
p_time_building_block_id=> l_time_building_block_id
);*/
END;
Process 2: Timecard, Day and Details creation controlled by US
We will try to create an entire week timecard, the employee worked whole week i.e 8 hours each day, so the employee is going to enter Mon-Fri 8 hours for a week, no time entry on Sat and Sun.
If we would like to create in details level, we can use below API’s
- hxc_timestore_deposit.create_timecard_bb
Create a Timecard building block (only timecard, no days, or details) - hxc_timestore_deposit.create_day_bb
Creates a DAY building block (only DAY no details) - hxc_timestore_deposit.create_detail_bb
Creates a DETAIL building block, in next step we have to attach the attribute to this DETAIL - hxc_timestore_deposit.create_attribute
Create Attribute Information like Proejct, Task Name, Expenditure Type, Expenditure Comments, System Linkage Function - hxc_timestore_deposit.execute_deposit_process
This is the Submission Call. This process will submit the timecard, days and details with attributes.
Timecard will stay in SUBMITTED State until approved via Manual or AUTO Approve process - hxc_timestore_deposit.log_messages ( p_messages => l_tbl_messages )
The API provides a procedure you can use to review the messages that are
returned by the deposit process. If a deposit fails, the API records the error in a table that can be read at the end.
DECLARE
l_tbl_timecard_info hxc_self_service_time_deposit.timecard_info;
l_tbl_attributes_info hxc_self_service_time_deposit.app_attributes_info;
l_tbl_messages hxc_self_service_time_deposit.message_table;
l_new_timecard_id NUMBER;
l_new_timecard_ovn NUMBER;
l_message VARCHAR2 (4000);
l_start_time DATE
:= fnd_date.canonical_to_date ('2016/07/11 00:00:00');
l_stop_time DATE
:= fnd_date.canonical_to_date ('2016/07/17 23:59:59');
l_timecard_bb_id hxc_time_building_blocks.time_building_block_id%TYPE;
l_day_bb_id hxc_time_building_blocks.time_building_block_id%TYPE;
l_detail_bb_id hxc_time_building_blocks.time_building_block_id%TYPE;
l_time_attribute_id hxc_time_attributes.time_attribute_id%TYPE;
BEGIN
FND_GLOBAL.APPS_INITIALIZE (user_id => 1234,
resp_id => 55598,
resp_appl_id => 809);
/* We must create Time Building Block with the scope of Timecard, this is the top level Time Building
block of the Timecard
Scope as Timecard and parent_building_block_id as NULL*/
hxc_timestore_deposit.create_timecard_bb (
p_start_time => fnd_date.canonical_to_date ('2016/07/11 00:00:00'),
p_stop_time => fnd_date.canonical_to_date ('2016/07/17 23:59:59'),
p_resource_id => 1234,
p_comment_text => 'API Timecard Creation',
p_app_blocks => l_tbl_timecard_info,
p_time_building_block_id => l_timecard_bb_id);
/*
Time Building Block with a scope of DAY for every day 7 days.
DAY Time Building Blocks must be linked to a TIMECARD Time Building Block from above API.
We have to loop for 7 days
*/
FOR i IN 1 .. 7
LOOP
-- here we create 7 records for the whole week starts from Monday and ends on Sunday
-- Scope as Day
hxc_timestore_deposit.create_day_bb (
p_day => TRUNC (l_start_time) + i,
p_parent_building_block_id => l_timecard_bb_id, -- returned by create_timecard_bb
p_comment_text => 'API Day Creation',
p_app_blocks => l_tbl_timecard_info,
p_time_building_block_id => l_day_bb_id);
-- Since we are entering only 5 days for a week so we create only 5 days Mon-Fri,
-- we dont create for Sat and Sun since no entry from employee timecard page
-- Scope as Measure
IF i < 5 THEN hxc_timestore_deposit.create_detail_bb ( p_type => 'MEASURE',
p_measure => 8,
p_parent_building_block_id => l_day_bb_id,
p_comment_text => 'API Hours Creation',
p_app_blocks => l_tbl_timecard_info,
p_app_attributes => l_tbl_attributes_info,
p_time_building_block_id => l_detail_bb_id);
-- Attribute1
hxc_timestore_deposit.create_attribute (
p_building_block_id => l_detail_bb_id,
p_attribute_name => 'Task_Id',
p_attribute_value => 2018,
p_app_attributes => l_tbl_attributes_info);
-- Attribute2
hxc_timestore_deposit.create_attribute (
p_building_block_id => l_detail_bb_id,
p_attribute_name => 'Project_Id',
p_attribute_value => 123,
p_app_attributes => l_tbl_attributes_info);
-- Attribute3
hxc_timestore_deposit.create_attribute (
p_building_block_id => l_detail_bb_id,
p_attribute_name => 'Expenditure_Type',
p_attribute_value => 'Test',
p_app_attributes => l_tbl_attributes_info);
-- Attribute4
hxc_timestore_deposit.create_attribute (
p_building_block_id => l_detail_bb_id,
p_attribute_name => 'Expenditure_Comment',
p_attribute_value => 'ABC',
p_app_attributes => l_tbl_attributes_info);
-- Attribute5
hxc_timestore_deposit.create_attribute (
p_building_block_id => l_detail_bb_id,
p_attribute_name => 'SYSTEM_LINKAGE_FUNCTION',
p_attribute_value => 'ST',
p_app_attributes => l_tbl_attributes_info);
END IF;
END LOOP;
-- Finally submit the time to the timestore
-- After successful execution of below API we could see the data in OTL Self Service i.e OTL base tables
hxc_timestore_deposit.execute_deposit_process (
p_validate => FALSE,
p_app_blocks => l_tbl_timecard_info,
p_app_attributes => l_tbl_attributes_info,
p_messages => l_tbl_messages,
p_mode => 'SUBMIT',
p_deposit_process => 'OTL Deposit Process',
p_timecard_id => l_new_timecard_id,
p_timecard_ovn => l_new_timecard_ovn);
-- To get error message if any by above API
IF (l_tbl_messages.COUNT <> 0)
THEN -- messages have been returned
FOR i IN l_tbl_messages.FIRST .. l_tbl_messages.LAST
LOOP
EXIT WHEN (NOT l_tbl_messages.EXISTS (i));
l_error_message :=
fnd_message.get_string (
appin => l_tbl_messages (i).application_short_name,
namein => l_tbl_messages (i).message_name);
DBMS_OUTPUT.put_line (l_message);
END LOOP;
END IF;
COMMIT;
DBMS_OUTPUT.put_line ('New Timecard Id: '|| l_new_timecard_id|| 'and New Timecard Version: '|| l_new_timecard_ovn);
END;
Update Timecard
Sometimes we might fall into a situation to update some of the timecard stuff, like updating of timecard hours or updating of attributes information like Project Name change or Task name change.
Note: We cannot update the timecard always, it depends on the Timecard setups. In timecard setup preferences – SelfService timecard status that allows user edits
Ex: If you want to update the timecard which is under submitted or approval pending or approved the SelfService timecard status that allows user edits should be set as RETRO
If not we won’t be able to update the timecard info which are under submitted/approved status
If self-service timecard status that allows user edits set as Working/Rejected
then you’ll be able to update the timecard only when timecard status in New, Working/Rejected status
Updating Timecard Hours:
DECLARE
l_tbl_timecard_info hxc_self_service_time_deposit.timecard_info;
l_tbl_attributes_info hxc_self_service_time_deposit.app_attributes_info;
l_tbl_messages hxc_self_service_time_deposit.message_table;
l_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE;
l_tc_obj_version_num hxc_time_building_blocks.object_version_number%TYPE;
BEGIN
FND_GLOBAL.APPS_INITIALIZE (user_id => 1234,
resp_id => 55598,
resp_appl_id => 809);
hxc_timestore_deposit.update_building_block (
p_building_block_id => 123456, -- Time building block id of the scope = Measure
p_measure => 10, -- Timecard hours changing from 8 to 10
p_app_blocks => l_tbl_timecard_info,
p_app_attributes => l_tbl_attributes_info);
hxc_timestore_deposit.execute_deposit_process (
p_validate => FALSE,
p_app_blocks => l_tbl_timecard_info,
p_app_attributes => l_tbl_attributes_info,
p_messages => l_tbl_messages,
p_mode => 'SUBMIT',
p_deposit_process => 'OTL Deposit Process',
p_timecard_id => l_timecard_id,
p_timecard_ovn => l_tc_obj_version_num);
-- To get error message if any by above API
IF (l_tbl_messages.COUNT <> 0)
THEN
FOR i IN l_tbl_messages.FIRST .. l_tbl_messages.LAST
LOOP
EXIT WHEN (NOT l_tbl_messages.EXISTS (i));
l_error_message :=
fnd_message.get_string (
appin => l_tbl_messages (i).application_short_name,
namein => l_tbl_messages (i).message_name);
DBMS_OUTPUT.put_line (l_message);
END LOOP;
END IF;
COMMIT;
DBMS_OUTPUT.put_line ('New Timecard Id: '|| l_timecard_id|| 'and New Timecard Version: '|| l_tc_obj_version_num);
-- Obejct verion will change from 1 to 2 i.e object version number increases
END;
Updating Attribute Information:
DECLARE
l_tbl_timecard_info hxc_self_service_time_deposit.timecard_info;
l_tbl_attributes_info hxc_self_service_time_deposit.app_attributes_info;
l_tbl_messages hxc_self_service_time_deposit.message_table;
l_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE;
l_tc_obj_version_num hxc_time_building_blocks.object_version_number%TYPE;
BEGIN
FND_GLOBAL.APPS_INITIALIZE (user_id => 1234,
resp_id => 55598,
resp_appl_id => 809);
hxc_timestore_deposit.update_attribute (
p_time_attribute_id=> 252369, --Time attribute id can be found in hxc_time_attributes table
p_attribute_name=> 'Task_Id',
p_attribute_value=> 456,
p_app_blocks=> l_tbl_timecard_info,
p_app_attributes=> l_tbl_attributes_info
);
hxc_timestore_deposit.execute_deposit_process (
p_validate => FALSE,
p_app_blocks => l_tbl_timecard_info,
p_app_attributes => l_tbl_attributes_info,
p_messages => l_tbl_messages,
p_mode => 'SUBMIT',
p_deposit_process => 'OTL Deposit Process',
p_timecard_id => l_timecard_id,
p_timecard_ovn => l_tc_obj_version_num);
-- To get error message if any by above API
IF (l_tbl_messages.COUNT <> 0)
THEN
FOR i IN l_tbl_messages.FIRST .. l_tbl_messages.LAST
LOOP
EXIT WHEN (NOT l_tbl_messages.EXISTS (i));
l_error_message :=
fnd_message.get_string (
appin => l_tbl_messages (i).application_short_name,
namein => l_tbl_messages (i).message_name);
DBMS_OUTPUT.put_line (l_message);
END LOOP;
END IF;
COMMIT;
DBMS_OUTPUT.put_line ('New Timecard Id: '|| l_timecard_id|| 'and New Timecard Version: '|| l_tc_obj_version_num);
-- Obejct verion will change from 1 to 2 i.e object version number increases
END;
Delete Timecard
We may require to delete the timecard from the system.
We can achieve this by using below API and we should know the time building block id for Timecard, Day, Detail.
DECLARE
l_tbl_timecard_info hxc_self_service_time_deposit.timecard_info;
l_tbl_attributes_info hxc_self_service_time_deposit.app_attributes_info;
l_tbl_messages hxc_self_service_time_deposit.message_table;
l_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE;
l_tc_obj_version_num hxc_time_building_blocks.object_version_number%TYPE;
BEGIN
FND_GLOBAL.APPS_INITIALIZE (user_id => 1234,
resp_id => 55598,
resp_appl_id => 809);
--Scope = Timecard from hxc_time_building_blocks
hxc_timestore_deposit.delete_detail_bb (
p_building_block_id => 123450,
p_app_blocks => l_tbl_timecard_info,
p_app_attributes => l_tbl_attributes_info);
-- Scope = Day from hxc_time_building_blocks
-- Here we may find multiple records for each day in the week,
-- you can mention 7 times fro each time block id or run in a loop
hxc_timestore_deposit.delete_detail_bb (
p_building_block_id => 123451,
p_app_blocks => l_tbl_timecard_info,
p_app_attributes => l_tbl_attributes_info);
-- Scope = Detail from hxc_time_building_blocks
-- Sometimes we may find multiple records then run in loop or mention for time BB id
hxc_timestore_deposit.delete_detail_bb (
p_building_block_id => 123452,
p_app_blocks => l_tbl_timecard_info,
p_app_attributes => l_tbl_attributes_info);
hxc_timestore_deposit.execute_deposit_process (
p_validate => FALSE,
p_app_blocks => l_tbl_timecard_info,
p_app_attributes => l_tbl_attributes_info,
p_messages => l_tbl_messages,
p_mode => 'SUBMIT',
p_deposit_process => 'OTL Deposit Process',
p_timecard_id => l_timecard_id,
p_timecard_ovn => l_tc_obj_version_num);
-- To get error message if any by above API
IF (l_tbl_messages.COUNT <> 0)
THEN
FOR i IN l_tbl_messages.FIRST .. l_tbl_messages.LAST
LOOP
EXIT WHEN (NOT l_tbl_messages.EXISTS (i));
l_error_message :=
fnd_message.get_string (
appin => l_tbl_messages (i).application_short_name,
namein => l_tbl_messages (i).message_name);
DBMS_OUTPUT.put_line (l_message);
END LOOP;
END IF;
COMMIT;
END;
Retrieve Timecard Information
We can retrieve the timecard information by joining multiple HXC tables or we can use below API retrieve time building blocks of timecard, day, details and attributes info as well.
DECLARE
l_timecard_id hxc_time_building_blocks.time_building_block_id%TYPE
:= 1234567;
l_tbl_timecard_info hxc_self_service_time_deposit.timecard_info;
l_tbb_array hxc_block_table_type;
l_tbl_attributes_info hxc_self_service_time_deposit.app_attributes_info;
BEGIN
apps.hxc_timestore_deposit_util.get_timecard_tables (
p_building_block_id => l_timecard_id,
p_app_blocks => l_tbl_timecard_info,
p_deposit_process => 'OTL Deposit Process',
p_app_attributes => l_tbl_attributes_info);
--l_tbb_array := hxc_deposit_wrapper_utilities.blocks_to_array(p_blocks => l_tbl_timecard_info);
DBMS_OUTPUT.put_line (
'Timecard Info: Timecard Id, Type, Measure, Scope, Object Version Number, Start Time, Stop Time');
FOR i IN l_tbl_timecard_info.FIRST .. l_tbl_timecard_info.LAST
LOOP
DBMS_OUTPUT.put_line (l_tbl_timecard_info (i).TIME_BUILDING_BLOCK_ID);
DBMS_OUTPUT.put_line (l_tbl_timecard_info (i).TYPE);
DBMS_OUTPUT.put_line (l_tbl_timecard_info (i).MEASURE);
DBMS_OUTPUT.put_line (l_tbl_timecard_info (i).SCOPE);
DBMS_OUTPUT.put_line (l_tbl_timecard_info (i).OBJECT_VERSION_NUMBER);
DBMS_OUTPUT.put_line (l_tbl_timecard_info (i).START_TIME);
DBMS_OUTPUT.put_line (l_tbl_timecard_info (i).STOP_TIME);
END LOOP;
DBMS_OUTPUT.put_line ('--------------------------------------------');
DBMS_OUTPUT.put_line (
'Attributes Info: Time Attribute Id, Timecard Id, Attribute Name, Attribute Value, Arrtibute Category');
FOR i IN l_tbl_attributes_info.FIRST .. l_tbl_attributes_info.LAST
LOOP
DBMS_OUTPUT.put_line (l_tbl_attributes_info (i).time_attribute_id);
DBMS_OUTPUT.put_line (l_tbl_attributes_info (i).building_block_id);
DBMS_OUTPUT.put_line (l_tbl_attributes_info (i).attribute_name);
DBMS_OUTPUT.put_line (l_tbl_attributes_info (i).attribute_value);
DBMS_OUTPUT.put_line (l_tbl_attributes_info (i).CATEGORY);
END LOOP;
DBMS_OUTPUT.put_line ('--------------------------------------------');
END;
-- Attributes Info
TYPE app_attributes IS RECORD (
time_attribute_id hxc_time_attributes.time_attribute_id%TYPE,
building_block_id hxc_time_building_blocks.time_building_block_id%TYPE,
attribute_name hxc_mapping_components.field_name%TYPE,
attribute_value hxc_time_attributes.attribute1%TYPE,
attribute_index number,
segment hxc_mapping_components.segment%type,
bld_blk_info_type hxc_bld_blk_info_types.bld_blk_info_type%TYPE,
CATEGORY hxc_bld_blk_info_type_usages.building_block_category%TYPE,
updated VARCHAR2 (30),
changed VARCHAR2 (30),
process VARCHAR2 (30)
);
TYPE app_attributes_info IS TABLE OF app_attributes INDEX BY BINARY_INTEGER;
TYPE building_block_info IS RECORD (
time_building_block_id hxc_time_building_blocks.time_building_block_id%TYPE,
TYPE hxc_time_building_blocks.TYPE%TYPE,
measure hxc_time_building_blocks.measure%TYPE,
unit_of_measure hxc_time_building_blocks.unit_of_measure%TYPE,
start_time hxc_time_building_blocks.start_time%TYPE,
stop_time hxc_time_building_blocks.stop_time%TYPE,
parent_building_block_id hxc_time_building_blocks.parent_building_block_id%TYPE,
parent_is_new VARCHAR2 (1),
SCOPE hxc_time_building_blocks.SCOPE%TYPE,
object_version_number hxc_time_building_blocks.object_version_number%TYPE,
approval_status hxc_time_building_blocks.approval_status%TYPE,
resource_id hxc_time_building_blocks.resource_id%TYPE,
resource_type hxc_time_building_blocks.resource_type%TYPE,
approval_style_id hxc_time_building_blocks.approval_style_id%TYPE,
date_from hxc_time_building_blocks.date_from%TYPE,
date_to hxc_time_building_blocks.date_to%TYPE,
comment_text hxc_time_building_blocks.comment_text%TYPE,
parent_building_block_ovn hxc_time_building_blocks.parent_building_block_ovn%TYPE,
NEW VARCHAR2 (30),
changed VARCHAR2 (30),
process varchar2 (30),
application_set_id hxc_time_building_blocks.application_set_id%type,
translation_display_key hxc_time_building_blocks.translation_display_key%type);
TYPE timecard_info IS TABLE OF building_block_info INDEX BY BINARY_INTEGER;