Search Results hxt_timecards_pk

Overview

HXT.HXT_TIMECARDS_F is a core table within the Oracle E-Business Suite Time and Labor (HXT) and Oracle Time and Labor integration with Oracle Payroll. It stores timecard records that capture the number of hours an employee works within a given payroll period. The table functions as the persistent home for timecard header information, linking a worker to a specific payroll period, batch, and payroll, while carrying approval status, generation flags, and descriptive flexfield context. As documented in the ETRM 12.2.2 schema (49 columns, owner HXT), it resides in the APPS_TS_TX_DATA tablespace and is registered under FND Design Data as HXT.HXT_TIMECARDS_F with a VALID status.

Based on the heuristic Data Vault classification mined from its foreign key structure, this object is modeled as a standalone entity. In Data Vault terms it most closely resembles a hub-with-satellite composite: the surrogate key column ID anchors the identity of the timecard, while the effective-dated nature of the table (EFFECTIVE_START_DATE / EFFECTIVE_END_DATE) and the presence of WHO audit columns plus descriptive flexfield attributes closely resemble a satellite holding descriptive, time-bounded detail. Because it maintains only one documented incoming foreign key (TIME_PERIOD_ID to PER_TIME_PERIODS), it does not act as a pure link table between multiple hubs.

Key Information Stored

The surrogate primary key is ID, a system-generated NUMBER(15) that uniquely identifies each timecard. The effective-dating columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE participate in the primary key, forming a composite date-tracked identity typical of EBS date-tracked (datetrack) entities.

• ID — System-generated primary key; part of HXT_TIMECARDS_PK.
• FOR_PERSON_ID — Foreign key to the PERSON_ID record, identifying the worker whose hours are stored.
• TIME_PERIOD_ID — Foreign key to PER_TIME_PERIODS, identifying the payroll period for the timecard.
• PAYROLL_ID — Foreign key to PAY_PAYROLLS_F, the payroll to which the timecard relates.
• BATCH_ID — Foreign key to PAY_BATCH_HEADERS, linking the timecard to a payroll batch.
• APPROV_PERSON_ID — Foreign key to PERSON_ID identifying the approver.
• APPROVED_TIMESTAMP — Date and time the timecard was approved.
• AUTO_GEN_FLAG — Y/N flag indicating whether the timecard was auto-generated, or C if details have been changed.
• STATUS — Status value: "A" for auto-generated, "C" for changed.
• ATTRIBUTE_CATEGORY / ATTRIBUTE1–30 — Descriptive flexfield structure column and segment columns.
• OBJECT_VERSION_NUMBER — Optimistic locking / concurrency column.
• DATA_SET_ID — Multi-tenant data set discriminator.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — Standard WHO audit columns.

Two unique indexes define business-key candidates: HXT_TIMECARDS_PK (ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE) and HXT_TIMECARDS_UK (FOR_PERSON_ID, TIME_PERIOD_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE). The latter enforces that a person has a single effective-dated timecard per time period. Two nonunique indexes, HXT_TIMECARDS_N1 and HXT_TIMECARDS_N2, support lookups by BATCH_ID and TIME_PERIOD_ID respectively.

Common Use Cases and Queries

The table is central to payroll turnaround and time-entry reporting. Typical scenarios include retrieving all timecards for a person in a payroll period, reconciling timesheet hours against approved payroll, and auditing auto-generated versus manually changed timecards.

• List timecards for a worker and period:

  SELECT tc.id, tc.for_person_id, tc.time_period_id,
         tc.status, tc.auto_gen_flag, tc.approved_timestamp
    FROM   hxt.hxt_timecards_f tc
   WHERE  tc.for_person_id = :person_id
     AND  tc.time_period_id = :period_id
     AND  SYSDATE BETWEEN tc.effective_start_date
                      AND tc.effective_end_date;

• Identify changed or auto-generated entries in a batch:

  SELECT tc.id, tc.for_person_id, tc.status
    FROM   hxt.hxt_timecards_f tc
   WHERE  tc.batch_id = :batch_id;

• Join to time periods for period names and dates:

  SELECT tp.period_name, tc.id
    FROM   hxt.hxt_timecards_f tc, per_time_periods tp
   WHERE  tc.time_period_id = tp.time_period_id;

Related Objects

• PER_TIME_PERIODS — Joined on TIME_PERIOD_ID; supplies period name and dates.
• PAY_PAYROLLS_F — Joined on PAYROLL_ID; identifies the payroll definition.
• PAY_BATCH_HEADERS — Joined on BATCH_ID; groups timecards into payroll batches.
• PER_ALL_PEOPLE_F — Implicit relationship through FOR_PERSON_ID and APPROV_PERSON_ID to the PERSON_ID record.
• HXT_TIMECARDS_PK / HXT_TIMECARDS_UK indexes — Enforce uniqueness on ID and person/period combinations.
• HXT timecard detail tables and HXT APIs — Downstream detail lines and the HXT timecard retrieval/update business APIs depend on the header identity defined here.

The presence of WHO columns, eff