Search Results pa_proj_elements_tmp




Overview
Explore in AI Chat

Overview

PA_PROJ_ELEMENTS_TMP is a temporary (staging) table in the Oracle Projects (PA) schema within Oracle E-Business Suite 12.1.1 and 12.2.2. It serves as a transient work area used by Oracle Projects concurrent programs and internal processes to stage, restructure, or bulk-process project element (work breakdown structure) hierarchies before they are validated and merged into the permanent project element tables. The "_TMP" suffix signals that rows are typically session- or request-scoped, populated during a process run, consumed by that same process, and then purged. It is not intended as a permanent system of record.

Under a heuristic Data Vault classification, this object is best modeled as a satellite, holding descriptive and structural attributes (hierarchy levels, sequencing, dates, WBS identifiers) keyed against project element business keys. Because the table carries two foreign keys to permanent entities (ELEMENT_VERSION_ID and TOP_TASK_ID), those relationships can alternatively be represented as links between the project element and its versioning/top-task hubs. The classification is a modeling suggestion only; the physical table is a standard relational staging structure.

Key Information Stored

The documented physical schema exposes 15 columns. The most significant are:

  • PROJ_ELEMENT_ID — the primary business identifier of the project element being staged; the principal surrogate/business key candidate for joining back to permanent element tables.
  • PROJECT_ID — the owning project, anchoring every staged element to its project context.
  • TASK_ID — the specific task within the project's WBS that the staged row represents.
  • PARENT_TASK_ID — the immediate parent task, defining the parent-child (rollup) structure of the hierarchy.
  • TOP_TASK_ID — the top-level task of the structure; carries a documented FK to PA_TOP_TASKS_IT.
  • ELEMENT_VERSION_ID — the element version being processed; carries a documented FK to PA_PROJ_ELEMENT_VERSIONS and links the staging row to versioning history.
  • PARENT_STRUCTURE_VERSION_ID — the version of the parent structure, used to validate consistency across hierarchy versions.
  • WBS_NUMBER — the human-readable WBS code assigned to the element.
  • DISPLAY_SEQUENCE and LEVEL_SEQUENCE — ordering and depth within the displayed/tree hierarchy.
  • OLD_LEVEL and DEFER_CODE — the prior level value and a deferral indicator, used during restructuring to detect level changes and deferred processing.
  • SCHEDULED_START_DATE / SCHEDULED_FINISH_DATE / DURATION — scheduling attributes carried alongside the hierarchy during the process.

Common Use Cases and Queries

Typical uses include hierarchy restructuring (WBS move/copy), element version promotion, and bulk import validation. Reporting against the temporary table is generally diagnostic — confirming what a concurrent request staged before or after a failure.

  • Inspect staged hierarchy: select TASK_ID, PARENT_TASK_ID, LEVEL_SEQUENCE, WBS_NUMBER from PA_PROJ_ELEMENTS_TMP order by LEVEL_SEQUENCE, DISPLAY_SEQUENCE;
  • Validate against permanent elements: join PA_PROJ_ELEMENTS_TMP to PA_PROJ_ELEMENTS on PROJ_ELEMENT_ID and PROJECT_ID to find rows not yet merged.
  • Detect level changes: compare OLD_LEVEL with LEVEL_SEQUENCE to identify elements whose depth shifted during restructuring.
  • Orphan checks: left-join PARENT_TASK_ID back to TASK_ID within the same PROJECT_ID to find staged rows lacking a parent.

Related Objects

The documented foreign keys point to two permanent tables, and the surrounding PA element family supplies the remainder:

  • PA_PROJ_ELEMENT_VERSIONS — joined on ELEMENT_VERSION_ID; the versioning parent of each staged element record.
  • PA_TOP_TASKS_IT — joined on TOP_TASK_ID; the top-task definition for the structure.
  • PA_PROJ_ELEMENTS — the permanent project element table, target of the staging merge on PROJ_ELEMENT_ID and PROJECT_ID.
  • PA_TASKS — task master, joined on TASK_ID / PARENT_TASK_ID.
  • PA_PROJECTS_ALL — project master, joined on PROJECT_ID.
  • PA_PROJ_ELEMENTS_TMP is typically consumed by Oracle Projects API/validation PL/SQL packages and the associated concurrent programs that manage WBS restructuring.