Search Results pa_proj_elem_ver_tmp

Overview

PA_PROJ_ELEM_VER_TMP is a transient working table owned by the PA (Projects) schema in Oracle E-Business Suite 12.1.1 and 12.2.2. Its name, ending in the _TMP suffix, identifies it as a temporary or staging structure rather than a permanent transactional entity. It is used by the Project Foundation component of Oracle Projects during the construction, flattening, and sequencing of project element version hierarchies — most notably the work breakdown structure (WBS) — before those hierarchies are persisted to permanent tables. The table holds nine documented columns and carries a single foreign key, ELEMENT_VERSION_ID, to PA_PROJ_ELEMENT_VERSIONS.

Under the heuristic Data Vault classification supplied in the ETRM metadata, this object is modelled as standalone. Because it exposes no downstream references and only one outbound foreign key, it does not behave as a hub or link in the strict Data Vault sense. A reasonable modelling suggestion is therefore to treat it as a transient satellite or work area scoped to the element version it qualifies, useful only while a hierarchy build or refresh operation is in flight.

Key Information Stored

The documented columns fall into three functional groups: identification, hierarchy structure, and ordering. The column set is:

• ELEMENT_VERSION_ID — the foreign key to PA_PROJ_ELEMENT_VERSIONS and the principal business-key candidate, tying each staged row to the specific element version (a WBS or task version) being processed.
• PROJECT_ID — identifies the project to which the staged element belongs.
• TASK_ID — the task or element currently being positioned in the hierarchy.
• PARENT_TASK_ID — the immediate parent of the task above, establishing the self-referencing parent-child relationship used to build the tree.
• WBS_LEVEL and WBS_NUMBER — the numeric depth of the element within the structure and its descriptive WBS number (for example, 1.2.3) rendered during flattening.
• DISPLAY_SEQUENCE, LEVEL_SEQUENCE, and SUB_LEVEL_SEQ — ordering columns that determine sibling sequence at each level and relative position within a parent, driving the flattened presentation order.

No unique index or surrogate primary key is documented in the metadata; ELEMENT_VERSION_ID combined with the sequence columns functions as the de facto identifying key. Note that this table is intentionally intermediary — rows are typically populated, consumed, and cleared during processing rather than retained for reporting.

Common Use Cases and Queries

Because the table is a working structure, its primary consumers are Oracle Projects concurrent programs and internal PL/SQL that rebuild WBS hierarchies. Practical investigative uses include diagnosing WBS rebuild or renumbering jobs, verifying ordering, and auditing hierarchy integrity at the point of staging.

• Retrieve a flattened hierarchy for a specific element version: SELECT PROJECT_ID, TASK_ID, PARENT_TASK_ID, WBS_LEVEL, WBS_NUMBER, DISPLAY_SEQUENCE FROM PA.PA_PROJ_ELEM_VER_TMP WHERE ELEMENT_VERSION_ID = :element_version_id ORDER BY DISPLAY_SEQUENCE;
• Locate orphaned or mis-parented rows by left-joining TASK_ID to PARENT_TASK_ID within the same element version and filtering for unexpected results.
• Trace which project and element version are in flight when a hierarchy build fails, using PROJECT_ID and ELEMENT_VERSION_ID to correlate with the erroring program.

Related Objects

The most significant dependencies are:

• PA_PROJ_ELEMENT_VERSIONS — joined on ELEMENT_VERSION_ID; the parent definition of the element version being staged.
• PA_PROJECTS_ALL — source of PROJECT_ID, the owning project.
• PA_TASKS — source of TASK_ID and the parent task elements.
• PA_PROJ_ELEMENTS and PA_PROJECT_STRUCTURE — permanent hierarchy and structure targets into which the staged data is ultimately written.
• PA_PROJ_ELEM_VER_TMP is consumed by the Project Foundation WBS processing APIs, notably the hierarchy build and renumbering programs that read and clear this structure.