Search Results pa_job_assignment_overrides




Overview
Explore in AI Chat

Overview

PA_JOB_ASSIGNMENT_OVERRIDES is a Projects (PA) module table in Oracle EBS 12.1.1 and 12.2.2 that stores non-standard employee job assignments and billing titles negotiated for specific projects and tasks. In standard Oracle Projects costing and billing flows, an employee's job and billing title are derived from Human Resources (PER) defaults. When a project or task requires a different job classification or a client-facing billing title than the employee's HR default, the exception is captured in this table and applied during cost rates, burdening, invoicing, and revenue generation.

From a modeling perspective, the metadata's FK structure suggests this object behaves as a link table. Its business identity is a composite of PERSON_ID, PROJECT_ID, TASK_ID, and START_DATE_ACTIVE, which connects the HR person dimension to the project/task hierarchy and to the job definition, with BILLING_TITLE and the active date range acting as link attributes. Analysts building a Data Vault or dimensional model should treat JOB_ASSIGNMENT_OVERRIDE_ID as the surrogate key and the composite business key as the natural key.

Key Information Stored

The table contains 14 documented columns. The most significant are:

  • PERSON_ID — the employee (HR person) whose job assignment or billing title is being overridden.
  • PROJECT_ID — the project for which the override applies; foreign key to PA_PROJECTS_ALL.
  • TASK_ID — the task within the project; foreign key to PA_TASKS.
  • JOB_ID — the overriding job definition; foreign key to PER_JOBS.
  • BILLING_TITLE — the negotiated client-facing title used on invoices and project reporting, distinct from the internal HR job.
  • START_DATE_ACTIVE — the effective start of the override; part of the composite primary key.
  • END_DATE_ACTIVE — the effective end of the override; defines the date-ranged validity of the record.
  • JOB_ASSIGNMENT_OVERRIDE_ID — the surrogate identifier, enforced by unique index PA_JOB_ASSIGNMENT_OVERRIDES_U4.
  • RECORD_VERSION_NUMBER — optimistic locking / concurrency control.
  • Audit columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.

The composite primary key PA_JOB_ASSIGNMENT_OVERRIDES_PK spans PERSON_ID, PROJECT_ID, TASK_ID, and START_DATE_ACTIVE, while the surrogate unique index on JOB_ASSIGNMENT_OVERRIDE_ID is the preferred single-column join key. The business key candidates are therefore the composite PK plus the attribute-style billing title.

Common Use Cases and Queries

Typical scenarios include verifying which employees have negotiated billing titles on a project, auditing job overrides for cost-rate accuracy, and extracting override history for revenue recognition.

Identify active overrides for a project:

SELECT o.person_id, o.task_id, o.job_id, o.billing_title,
       o.start_date_active, o.end_date_active
FROM   pa.pa_job_assignment_overrides o
WHERE  o.project_id = :project_id
AND    SYSDATE BETWEEN NVL(o.start_date_active, SYSDATE)
                   AND NVL(o.end_date_active, SYSDATE + 1);

Join to HR job definitions and the project for a billing-title report:

SELECT p.segment1 project_number, t.task_number,
       j.name job_name, o.billing_title
FROM   pa.pa_job_assignment_overrides o
JOIN   pa.pa_projects_all p ON p.project_id = o.project_id
JOIN   pa.pa_tasks t        ON t.task_id    = o.task_id
JOIN   per.per_jobs j       ON j.job_id     = o.job_id
WHERE  o.person_id = :person_id;

Reconciliation queries commonly test for overlapping date ranges per person/project/task combination, which would indicate a data integrity issue.

Related Objects

  • PA_PROJECTS_ALL — joined via PROJECT_ID; provides project definition, organization, and status.
  • PA_TASKS — joined via TASK_ID; provides the task hierarchy and task-level attributes.
  • PER_JOBS — joined via JOB_ID; supplies the override job name and HR job attributes.
  • PER_ALL_PEOPLE_F — typically joined via PERSON_ID to resolve employee names and effective-dated person details.
  • PA_PROJECT_ASSIGNMENTS — the standard project staffing table often compared against overrides to detect exceptions.
  • PA_COST_DISTRIBUTION_LINES_ALL and PA_EXPENDITURES_ALL — downstream cost/billing processing that consumes job and billing-title overrides.
  • PA_DRAFT_INVOICE_DETAILS_ALL — invoice generation where the negotiated BILLING_TITLE is printed.

When these objects are reconciled, PA_JOB_ASSIGNMENT_OVERRIDES acts as the exception layer between HR job defaults and project-specific billing and costing requirements.