OTL Timecard Upload – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

OTL Timecard Upload =================== Create, update and delete Oracle Time and Labor (OTL / HXC) timecards via the public TimeStore deposit API hxc_timestore_deposit, as an Excel-based alternative to self-service time entry.

Row model

One spreadsheet row per timecard line and day: Employee, Date, classification and Hours. Rows are grouped into timecards by employee and the OTL timecard period (e.g. the weekly period from the employee’s OTL preferences) containing the Date. Rows of the same employee and period must be contiguous and are deposited together as one timecard. The Period Starting column fills automatically once Employee and Date are entered.

Classification

Each line is classified either for Projects (Project, Task and Expenditure Type - straight time) or for Payroll (Hours Type = payroll element). The deposit derives the employee’s application set, approval style and security context from the OTL preferences, exactly like self-service time entry.

Run the upload under a responsibility whose operating unit context matches the projects being charged (e.g. the OTL or Projects responsibility the employees use) - the deposit validates the project, task, expenditure type and expenditure organization under the session’s operating unit, exactly like self-service time entry.

Save and submit

Leave Submit blank to save the timecard in Working status. Set Submit to Yes on any row of a timecard to submit it for approval (the OTL approval workflow starts, e.g. HR supervisor approval). Updating a timecard that is already Submitted or Approved re-submits it for approval automatically.

Updates and deletes

Downloaded lines carry hidden building block ids and are updated through the OTL deposit API, which creates a new timecard version and re-routes approval. Set Delete Line to Yes to remove a line; deleting all lines of a timecard deletes the whole timecard. Timecards already transferred to a recipient application (Projects, Payroll/BEE or Purchasing) are protected and cannot be modified by the upload.

Download

Submission Date is the date the timecard was last submitted for approval. Organization restricts the download to employees whose primary assignment belongs to the organization at the start of the timecard period.

Upload modes

Create - empty template for new timecards. Create, Update - downloads existing timecards (project and payroll element lines) for review, correction, resubmission or deletion.

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Upload Mode, Employee, Period Start Date From, Period Start Date To, Approval Status, Organization

Oracle EBS Tables Used

hxc_transaction_details, hxc_transactions, hxc_timecard_summary, hxc_time_building_blocks, hxc_time_attribute_usages, hxc_time_attributes, per_all_people_f, pa_projects_all, pa_tasks, pay_element_types_f

Report Categories

Enginatics, Upload

OTL Timecard Upload 11i, PA Missing Timecards, GL Account Analysis, GL Account Distribution Analysis

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource Link
Excel Example Output Example
Blitz Report™ XML Import OTL_Timecard_Upload.xml
SQL Source OTL Timecard Upload.sql
Full Report on Enginatics www.enginatics.com/reports/otl-timecard-upload/

Case Study & Technical Analysis: OTL Timecard Upload

Executive Summary

This upload creates, updates, submits and deletes Oracle Time and Labor (OTL) timecards. It is an Excel-based alternative to employee self-service time entry: a timekeeper or project administrator downloads the timecards of employees, enters or corrects hours by day and classification, and uploads them. Each line is charged either to a project, task and expenditure type, or to a payroll element. The upload calls the public TimeStore deposit API, so the rules of self-service time entry, including approval routing, apply to every timecard. It is used for timecard catch-up at period end, for bulk corrections after a project reclassification, and for resubmitting timecards that were rejected. Blitz Report™ provides the spreadsheet layout used for these corrections.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. HXC_TIMECARD_SUMMARY is joined to the employee in PER_ALL_PEOPLE_F on the resource identifier, with the employee effective on the start of the timecard.
  2. The day building block is a child of the timecard, and the detail building block is a child of the day.
  3. Each detail links to its attributes through HXC_TIME_ATTRIBUTE_USAGES, and to HXC_TIME_ATTRIBUTES for the values.
  4. A project classification is taken from the project and task identifiers in the attribute values, and an element classification from the element identifier in the attribute category.
  5. The project, task and element are outer joined, so payroll and project lines are both returned.
  6. A detail is marked as transferred when a successful retrieval transaction exists for its building block.

Parameters & Filtering

Performance & Optimization

FAQ

Q: How do I resubmit a rejected timecard? A: Download the timecard, correct the lines, and set Submit to Yes on any row of that timecard. Upload with Create, Update.

Q: Why can I not change a timecard? A: Timecards already transferred to Projects, Payroll or Purchasing are protected, and the upload cannot modify them.

Q: Which responsibility should run the upload? A: Run it under a responsibility whose operating unit matches the projects being charged, such as the OTL or Projects responsibility that the employees use.

© Enginatics GmbH