Search Results pa_structures_tasks_tmp

Overview

PA_STRUCTURES_TASKS_TMP is a staging and temporary workspace table in the Oracle Projects (PA) schema. It holds project structure and task element rows — work breakdown structure (WBS) elements, tasks, phases, and their rolled-up progress, cost, and scheduling attributes — while those rows are being prepared, refreshed, or published for a structure version. The "_TMP" suffix indicates that the table is transient in nature: rows are generated by concurrent programs and Oracle Projects forms (particularly those supporting Project Workplan, Structure maintenance, and Gantt-style schedule views) to build the result set that will ultimately be presented to the user or flushed into the operative PA tables. In Oracle EBS 12.1.1 and 12.2.2 the physical definition carries 233 documented columns, an unusually wide shape consistent with a denormalized, presentation-oriented staging surface rather than a normalized transactional entity.

From a Data Vault modeling perspective, the metadata's heuristic classification is standalone — the table is mined as neither a hub nor a link, because it does not carry its own natural business key that anchors to a master entity. In practice the closest modeling analogy is a satellite-like staging projection: it references the element version, object relationship, and progress rollup identifiers of the operative tables but does not act as a source of durable, deduplicated identity. Treating it as a staging cache that is periodically purged is the correct operational posture.

Key Information Stored

Although 233 columns are documented, the salient ones fall into identifiable groups. The table does not publish an explicit surrogate primary key; the row grain is effectively the structure/task element version, and the strongest business-key candidate is the combination of PROJECT_ID (or PARENT_PROJECT_ID) with ELEMENT_VERSION_ID, PROJ_ELEMENT_ID, and PARENT_STRUCTURE_VERSION_ID, which together identify a task element within a published structure version.

• Identity and hierarchy: ELEMENT_VERSION_ID, PROJ_ELEMENT_ID, PARENT_ELEMENT_VERSION_ID, PROJECT_ID, PARENT_PROJECT_ID, PARENT_STRUCTURE_VERSION_ID, WBS_LEVEL, WBS_NUMBER, ELEMENT_NUMBER, ELEMENT_NAME.
• Element classification: OBJECT_TYPE, PARENT_OBJECT_TYPE, STRUCTURE_TYPE_CLASS_CODE, SUMMARY_ELEMENT_FLAG, MILESTONE_FLAG, CRITICAL_FLAG, LOWEST_TASK, CHILD_ELEMENT_FLAG.
• Relationships: OBJECT_RELATIONSHIP_ID, RELATIONSHIP_TYPE, RELATIONSHIP_SUBTYPE, DISPLAY_PARENT_VERSION_ID, DISPLAY_SEQUENCE.
• Scheduling: SCHEDULED_START_DATE, SCHEDULED_FINISH_DATE, BASELINE_START_DATE, BASELINE_FINISH_DATE, ACTUAL_START_DATE, ACTUAL_FINISH_DATE, EARLY_START_DATE, LATE_FINISH_DATE, TOTAL_SLACK, CONSTRAINT_TYPE.
• Progress and effort: PROGRESS_STATUS_CODE, PERCENT_COMPLETE_ID, COMPLETED_PERCENTAGE, PLANNED_EFFORT, ESTIMATED_REMAINING_EFFORT, ACTUAL_EFFORT, WEIGHTING_PERCENTAGE.
• Cost and earned value: RAW_COST, BURDENED_COST, ACTUAL_COST, BASELINE_COST, EARNED_VALUE, COST_VARIANCE, SCHEDULE_PERFORMANCE_INDEX, COST_PERFORMANCE_INDEX.
• Publication state: LATEST_EFF_PUBLISHED_FLAG, STRUCT_PUBLISHED_FLAG, PUBLISHED_DATE, TASK_UNPUB_VER_STATUS_CODE, AS_OF_DATE, VERSIONING_ENABLED_FLAG.
• Progress rollup linkage: PROGRESS_ROLLUP_ID, PEV_SCHEDULE_ID.

Three foreign keys are documented: ELEMENT_VERSION_ID → PA_PROJ_ELEMENT_VERSIONS, OBJECT_RELATIONSHIP_ID → PA_OBJECT_RELATIONSHIPS, and PROGRESS_ROLLUP_ID → PA_PROGRESS_ROLLUP.

Common Use Cases and Queries

The table supports the Project Workplan, Structure and Task maintenance, and Earned Value reporting flows. Typical uses include identifying tasks with slipped or at-risk schedules, exporting a workplan to Excel via the Projects UI, and validating structure version publication. A representative query lists open tasks with schedule variance:

• SELECT t.PROJECT_NUMBER, t.WBS_NUMBER, t.ELEMENT_NAME, t.SCHEDULED_FINISH_DATE, t.BASELINE_FINISH_DATE, t.SCH_BSL_FINISH_VAR FROM pa.pa_structures_tasks_tmp t WHERE t.LOWEST_TASK = 'Y' AND t.ACTIVE_TASK_FLAG = 'Y' AND t.SCH_BSL_FINISH_VAR > 0 ORDER BY t.SCH_BSL_FINISH_VAR DESC;
• SELECT t.ELEMENT_VERSION_ID, t.ELEMENT_NAME, t.PERCENT_COMPLETE_EFFORT, t.EARNED_VALUE, t.COST_PERFORMANCE_INDEX FROM pa.pa_structures_tasks_tmp t WHERE t.PROJECT_ID = :project_id AND t.STRUCT_PUBLISHED_FLAG = 'Y';
• SELECT r.PROGRESS_ROLLUP_ID, t.ELEMENT_NAME, t.ACTUAL_EFFORT, t.ESTIMATED_REMAINING_EFFORT FROM pa.pa_structures_tasks_tmp t JOIN pa.pa_progress_rollup r ON r.PROGRESS_ROLLUP_ID = t.PROGRESS_ROLLUP_ID;

Because the table is temporary, joins should always be constrained by the session or run context (for example PROJECT_ID, REPORT_VERSION_ID, or AS_OF_DATE) to avoid mixing data from concurrent generations.

Related Objects

• PA_PROJ_ELEMENT_VERSIONS — master source of element versions; joined on ELEMENT_VERSION_ID.
• PA_PROJECTS_ALL — project header; joined on PROJECT_ID / PROJECT_NUMBER.
• PA_PROJ_ELEMENTS — element definitions; joined on PROJ_ELEMENT_ID.
• PA_OBJECT_RELATIONSHIPS — parent/child relationships among elements; joined on OBJECT_RELATIONSHIP_ID.
• PA_PROGRESS_ROLLUP — progress rollup results; joined on PROGRESS_ROLLUP_ID.
• PA_STRUCTURES / PA_STRUCTURE_VERSIONS — structure version context for published workplans.
• PA_TASKS — operative task records for the same elements.
• Oracle Projects APIs — PA_PROJECT_STRUCTURE_PUB, PA_TASK_PUB, and PA_PROGRESS_ROLLUP_PUB — consume and populate this staging surface during publication and rollup processing.