OTL Timecard Upload

Description

OTL Timecard Upload creates, updates, submits for approval and deletes Oracle Time and Labor (OTL) timecards from Excel. Each row is one timecard line for one day, either Projects time (project, task and expenditure type) or Payroll time (an hours type element). The timecards are deposited through the standard OTL TimeStore, as an alternative to self-service time entry.

When to use it

Enter timecards for many employees at once instead of one by one in self-service.
Load Projects time and Payroll time in a single sheet.
Save timecards in Working status for review, then submit them for approval later.
Correct hours, comments, projects, tasks or hours types on existing timecards and re-submit them.
Delete wrong timecard lines, or whole timecards, in bulk.
Load time recorded outside Oracle EBS, for example in an external time system.

Before you start

Access to the upload in Blitz Report.
Each employee is set up for OTL time entry: the OTL preferences give a timecard period (for example weekly) and an application set.
For Projects lines, run the upload under a responsibility whose operating unit matches the projects being charged, for example the Projects or OTL responsibility the employees use. The Project list and the deposit validation use that operating unit.

Choose a template

Template	Use it for
Default (default)	All columns.
Timecards Pending Approval	Submitted OTL timecards awaiting approval, with their submission date. Approval Status defaults to Submitted.
Timecard Hours by Project	OTL hours per employee and project per period, summarized in a pivot table.

Step 1 – Set the parameters

Open OTL Timecard Upload in Blitz Report and set the parameters:

Parameter	Meaning
Upload Mode	Create opens an empty sheet for new timecards; Create, Update (default) downloads existing timecard lines for editing and lets you add new ones.
Employee	Downloads the timecards of this employee.
Period Start Date From / To	Downloads the timecards whose period starts in this date range. Period Start Date To defaults to the last day of the month of Period Start Date From.
Approval Status	Downloads only timecards in this status, for example Working, Submitted, Approved or Rejected.
Organization	Downloads the timecards of the employees whose primary assignment is in this organization at the start of the timecard period.

Step 2 – Run to download the Excel file

Click Run. The Excel file downloads and opens with one row per existing Projects or Payroll timecard line, showing the timecard’s Approval Status and whether the line is Transferred, or an empty sheet in Create mode. The Timecards Pending Approval template also shows the read-only Submission Date, the date the timecard was last submitted for approval.

Step 3 – Enter or change the timecard lines

For a new line enter the Employee, the Date Worked and the Hours, then classify it: for Projects time fill Project, Task and Expenditure Type (straight time types); for Payroll time fill Hours Type (the payroll element) instead. Add a Line Comment if needed. Employee Number and Period Starting fill automatically; Employee Number is only needed to tell apart two employees with the same name.

Rows of the same employee whose dates fall in the same timecard period are deposited together as one timecard: a new timecard is created, or new lines are added to the existing timecard of that period. On a downloaded line you can change the Hours, Line Comment, Project, Task, Expenditure Type or Hours Type; switching a line between Projects and Payroll is not possible, delete it and add a new line instead. Lines you leave unchanged stay on the timecard as they are.

Leave Submit Timecard blank to save the timecard in Working status, or set it to Yes on any row of the timecard to submit the whole timecard for approval. To remove a downloaded line set Delete Line to Yes; deleting all lines of a timecard deletes the timecard.

Step 4 – Validate and Save

Click Validate and Save. This checks for missing required values and saves the file. Correct any rows it flags before uploading.

Step 5 – Upload the file

In Blitz Report click Upload and select the saved file. This submits the upload request, which deposits each timecard, saved in Working status or submitted for approval.

Step 6 – Review the result report

When the request completes, a result report opens listing every uploaded row with its status, message and the timecard’s current approval status.

What’s produced

OTL timecards created, updated, submitted for approval or deleted, with the same validation and approval routing as self-service time entry.
A result report listing every row with a status (success or error) and a message.

Common questions

How do I save a timecard without submitting it?
Leave Submit Timecard blank on all its rows. The timecard is saved in Working status and can be submitted later, in self-service or with another upload.

Why was my changed timecard submitted again?
Changing a timecard that is already Submitted or Approved always re-submits it for approval, as in self-service.

Why can’t I change a particular timecard?
Its lines have already been transferred to Projects, Payroll or Purchasing (Transferred = Yes). Such timecards cannot be changed by the upload; correct the time in the receiving application.

How do I delete a whole timecard?
Download it, set Delete Line to Yes on all of its lines and upload.

Why do all rows of a timecard show the same error?
A timecard is deposited as a whole. If one of its rows fails, none of its rows are saved and every row of that timecard shows the error.

Which timecard lines are downloaded?
Projects lines and Payroll (hours type) lines of the timecards that match the parameters.

Troubleshooting

Message	Cause	What to do
Employee “…” is ambiguous. Populate the Employee Number column to identify the person uniquely.	More than one person with this name exists on the date.	Enter the Employee Number.
Employee “…” was not found or is not active on …	The name does not match a person active on the Date Worked.	Pick the employee from the list and check the date.
No OTL timecard period could be determined for “…” on …. Check the employee’s OTL preferences.	The employee has no timecard period preference.	Complete the employee’s OTL preferences.
Employee “…” has no OTL application set preference and timecards cannot be deposited. Check the OTL preference eligibility setup.	The employee is not eligible for OTL time entry.	Set up the OTL preference eligibility for the employee.
Enter either Project, Task and Expenditure Type or an Hours Type on a timecard line, not both.	The line has both Projects and Payroll values.	Keep one classification per line.
Project, Task and Expenditure Type must all be entered for a project time entry.	A Projects line misses one of the three values.	Fill Project, Task and Expenditure Type.
Enter either Project, Task and Expenditure Type or an Hours Type on a timecard line.	The line has no classification.	Fill the Projects columns or the Hours Type.
Task “…” was not found for project “…”.	The task does not belong to the project.	Pick the task from the list after entering the project.
Hours Type (payroll element) “…” was not found.	The element does not exist for the employee’s business group on the Date Worked.	Pick the Hours Type from the list.
This timecard has already been transferred to a recipient application (e.g. Projects or Payroll) and cannot be modified by the upload.	Lines of the timecard were already transferred.	Correct the time in the receiving application.
Changing a timecard line between project and payroll element classification is not supported. Delete the line and add a new one instead.	A downloaded Projects line was changed to an Hours Type, or the other way round.	Set Delete Line to Yes on the old line and add a new row.
Only downloaded timecard lines can be deleted.	Delete Line is Yes on a new row.	Delete only downloaded lines; remove the new row from the sheet instead.
No existing timecard was found for “…” in the period starting … to delete lines from.	The timecard was already deleted or replaced since the download.	Download the timecard again.
Hours must not be negative.	Hours is below zero.	Enter zero or more hours.
No change.	The rows of this timecard contain no change, so nothing was deposited.	No action needed.
Other OTL or Projects validation messages (for example about the project, task, expenditure type or organization)	The deposit validation rejected the timecard, often because the responsibility’s operating unit does not match the projects.	Run the upload under a responsibility of the projects’ operating unit, or correct the values named in the message.

SQL 77 lines

select
null action_,
null status_,
null message_,
null modified_columns_,
row_number() over (order by x.employee, x.period_starting, x.date_worked, x.detail_bb_id) upload_row,
x.employee,
x.employee_number,
x.approval_status,
x.transferred,
x.submission_date,
x.period_starting,
x.date_worked,
x.project,
x.task,
x.expenditure_type,
x.hours_type,
x.hours,
x.line_comment,
x.submit_timecard,
x.delete_line,
x.timecard_bb_id,
x.detail_bb_id
from
(
select
papf.full_name employee,
papf.employee_number,
xxen_util.meaning(hts.approval_status,'HXC_APPROVAL_STATUS',3) approval_status,
xxen_util.yes(case when exists (select null from hxc_transaction_details htd, hxc_transactions ht where htd.time_building_block_id=htbb_det.time_building_block_id and htd.time_building_block_ovn=htbb_det.object_version_number and htd.status='SUCCESS' and htd.transaction_id=ht.transaction_id and ht.type='RETRIEVAL' and ht.status='SUCCESS') then 'Y' end) transferred,
hts.submission_date,
trunc(hts.start_time) period_starting,
trunc(htbb_day.start_time) date_worked,
ppa.segment1 project,
pt.task_number task,
decode(hta.attribute_category,'PROJECTS',hta.attribute3) expenditure_type,
petf.element_name hours_type,
htbb_det.measure hours,
htbb_det.comment_text line_comment,
null submit_timecard,
null delete_line,
hts.timecard_id timecard_bb_id,
htbb_det.time_building_block_id detail_bb_id,
papf.person_id
from
hxc_timecard_summary hts,
hxc_time_building_blocks htbb_day,
hxc_time_building_blocks htbb_det,
hxc_time_attribute_usages htau,
hxc_time_attributes hta,
per_all_people_f papf,
pa_projects_all ppa,
pa_tasks pt,
pay_element_types_f petf
where
:p_upload_mode like '%'||xxen_upload.action_update and
hts.resource_id=papf.person_id and
hts.start_time between papf.effective_start_date and papf.effective_end_date and
htbb_day.parent_building_block_id=hts.timecard_id and
htbb_day.parent_building_block_ovn=hts.timecard_ovn and
htbb_day.scope='DAY' and
htbb_day.date_to=to_date('4712-12-31','YYYY-MM-DD') and
htbb_det.parent_building_block_id=htbb_day.time_building_block_id and
htbb_det.parent_building_block_ovn=htbb_day.object_version_number and
htbb_det.scope='DETAIL' and
htbb_det.date_to=to_date('4712-12-31','YYYY-MM-DD') and
htau.time_building_block_id=htbb_det.time_building_block_id and
htau.time_building_block_ovn=htbb_det.object_version_number and
htau.time_attribute_id=hta.time_attribute_id and
(hta.attribute_category='PROJECTS' or hta.attribute_category like 'ELEMENT - %') and
ppa.project_id(+)=decode(hta.attribute_category,'PROJECTS',to_number(hta.attribute1)) and
pt.task_id(+)=decode(hta.attribute_category,'PROJECTS',to_number(hta.attribute2)) and
petf.element_type_id(+)=case when hta.attribute_category like 'ELEMENT - %' then to_number(substr(hta.attribute_category,11)) end and
htbb_day.start_time between petf.effective_start_date(+) and petf.effective_end_date(+)
) x
where
1=1

select null action_, null status_, null message_, null modified_columns_, row_number() over (order by x.employee, x.period_starting, x.date_worked, x.detail_bb_id) upload_row, x.employee, x.employee_number, x.approval_status, x.transferred, x.submission_date, x.period_starting, x.date_worked, x.project, x.task, x.expenditure_type, x.hours_type, x.hours, x.line_comment, x.submit_timecard, x.delete_line, x.timecard_bb_id, x.detail_bb_id from ( select papf.full_name employee, papf.employee_number, xxen_util.meaning(hts.approval_status,'HXC_APPROVAL_STATUS',3) approval_status, xxen_util.yes(case when exists (select null from hxc_transaction_details htd, hxc_transactions ht where htd.time_building_block_id=htbb_det.time_building_block_id and htd.time_building_block_ovn=htbb_det.object_version_number and htd.status='SUCCESS' and htd.transaction_id=ht.transaction_id and ht.type='RETRIEVAL' and ht.status='SUCCESS') then 'Y' end) transferred, hts.submission_date, trunc(hts.start_time) period_starting, trunc(htbb_day.start_time) date_worked, ppa.segment1 project, pt.task_number task, decode(hta.attribute_category,'PROJECTS',hta.attribute3) expenditure_type, petf.element_name hours_type, htbb_det.measure hours, htbb_det.comment_text line_comment, null submit_timecard, null delete_line, hts.timecard_id timecard_bb_id, htbb_det.time_building_block_id detail_bb_id, papf.person_id from hxc_timecard_summary hts, hxc_time_building_blocks htbb_day, hxc_time_building_blocks htbb_det, hxc_time_attribute_usages htau, hxc_time_attributes hta, per_all_people_f papf, pa_projects_all ppa, pa_tasks pt, pay_element_types_f petf where :p_upload_mode like '%'||xxen_upload.action_update and hts.resource_id=papf.person_id and hts.start_time between papf.effective_start_date and papf.effective_end_date and htbb_day.parent_building_block_id=hts.timecard_id and htbb_day.parent_building_block_ovn=hts.timecard_ovn and htbb_day.scope='DAY' and htbb_day.date_to=to_date('4712-12-31','YYYY-MM-DD') and htbb_det.parent_building_block_id=htbb_day.time_building_block_id and htbb_det.parent_building_block_ovn=htbb_day.object_version_number and htbb_det.scope='DETAIL' and htbb_det.date_to=to_date('4712-12-31','YYYY-MM-DD') and htau.time_building_block_id=htbb_det.time_building_block_id and htau.time_building_block_ovn=htbb_det.object_version_number and htau.time_attribute_id=hta.time_attribute_id and (hta.attribute_category='PROJECTS' or hta.attribute_category like 'ELEMENT - %') and ppa.project_id(+)=decode(hta.attribute_category,'PROJECTS',to_number(hta.attribute1)) and pt.task_id(+)=decode(hta.attribute_category,'PROJECTS',to_number(hta.attribute2)) and petf.element_type_id(+)=case when hta.attribute_category like 'ELEMENT - %' then to_number(substr(hta.attribute_category,11)) end and htbb_day.start_time between petf.effective_start_date(+) and petf.effective_end_date(+) ) x where 1=1

Parameters 6

Parameter

Type

List of values SQL

Upload Mode

LOV

:p_upload_mode like '%' || xxen_upload.action_update

Employee

LOV

x.employee=:p_employee

Period Start Date From

Date

x.period_starting>=:p_period_start_date_from

Period Start Date To