OTL Timecard Upload 11i – 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, pay_element_types_f, hxc_timecard_summary, hxc_time_building_blocks, hxc_time_attribute_usages, hxc_time_attributes, per_all_people_f, pa_projects_all, pa_tasks

Report Categories

Enginatics, Upload

OTL Timecard Upload, 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_11i.xml
SQL Source OTL Timecard Upload 11i.sql
Full Report on Enginatics www.enginatics.com/reports/otl-timecard-upload-11i/

Case Study & Technical Analysis: OTL Timecard Upload 11i

Executive Summary

The OTL Timecard Upload 11i report creates, updates and deletes Oracle Time and Labor (OTL, also known as HXC) timecards from a spreadsheet. It uses the public TimeStore deposit API, so the timecards follow the same rules as self-service time entry. It is an Excel-based alternative for timekeepers and payroll or projects administrators who must enter or correct timecards for many employees, and it supports both project charging and payroll element hours. Each row is one timecard line for one day, and the upload groups lines into timecards by employee and timecard period.

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 ID, with the employee record effective on the timecard start date.
  2. The day building block is joined to the timecard on the timecard ID and version, and restricted to the day scope and an open-ended date.
  3. The detail building block is joined to the day building block, restricted to the detail scope.
  4. Attribute usages link the detail to its attributes. Only PROJECTS and element attribute categories are kept.
  5. Project and task are outer joined from the PROJECTS attribute values, and the payroll element name is looked up by the element type ID and the day’s date.
  6. A subquery checks the transfer history, so a timecard with a successful retrieval transaction is flagged as transferred.

Parameters & Filtering

Performance & Optimization

FAQ

Q: How do I submit a timecard for approval through the upload? A: Set Submit to Yes on any row of the timecard. The OTL approval workflow then starts for the timecard. Q: What happens if I change a timecard that is already approved? A: The upload resubmits it for approval automatically, and the update creates a new timecard version that re-routes approval. Q: Why can I not change some downloaded timecards? A: Timecards already transferred to Projects, Payroll or Purchasing are protected, and the report flags them as transferred.

© Enginatics GmbH