Search Results pa_proj_structure_types_u2

Overview

PA.PA_PROJ_STRUCTURE_TYPES is an Oracle Projects (PA) schema table that stores the structure types assigned to a particular project structure. As documented in the ETRM metadata for Oracle EBS 12.1.1 and 12.2.2, the object is currently flagged "For future use," meaning it exists as a registered, valid dictionary object but is not heavily consumed by seeded application logic in the current release. It resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10 and carries the FND Design Data identifier PA.PA_PROJ_STRUCTURE_TYPES under the VALID status.

From a Data Vault modeling perspective, the heuristic classification is satellite-leaning. The table attaches descriptive and associative context to both a project element and a structure type, making it a good candidate to be modeled as a satellite (or a link-satellite pair) rather than a pure hub. Its unique indexes place it firmly as a relationship-bearing object rather than an independent entity.

Key Information Stored

The table contains 25 documented columns. The following are the most significant:

• PROJ_STRUCTURE_TYPE_ID — a NUMBER(15) system-generated surrogate primary key that uniquely identifies each structure type record. It is the single-column candidate key backed by the PA_PROJ_STRUCTURE_TYPES_U1 unique index.
• PROJ_ELEMENT_ID — a NUMBER(15) identifier for the project element, joined to PA.PA_PROJ_ELEMENTS. Together with STRUCTURE_TYPE_ID it forms the composite business key enforced by the PA_PROJ_STRUCTURE_TYPES_U2 unique index.
• STRUCTURE_TYPE_ID — a NUMBER(15) identifier of the structure type, resolved against BOM_STRUCTURE_TYPES_B. This is the second half of the U2 business key.
• RECORD_VERSION_NUMBER — a NUMBER(15) sequence used for optimistic locking in self-service applications.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — the standard Who columns capturing audit and change history.
• ATTRIBUTE_CATEGORY — a VARCHAR2(30) descriptive flexfield context field.
• ATTRIBUTE1 through ATTRIBUTE15 — VARCHAR2(150) descriptive flexfield segments available for customer-defined extensions.

The three indexes—U1 (unique on PROJ_STRUCTURE_TYPE_ID), U2 (unique on PROJ_ELEMENT_ID plus STRUCTURE_TYPE_ID), and N1 (non-unique on STRUCTURE_TYPE_ID)—support both primary-key lookups and reverse lookups by structure type.

Common Use Cases and Queries

Because the table is marked "For future use," primary usage scenarios center on extensions, reporting, and integration rather than core transaction processing. Typical patterns include:

• Enumerating structure types for a given project element: SELECT structure_type_id FROM pa_proj_structure_types WHERE proj_element_id = :element_id;
• Resolving the surrogate key back to the composite business key via the U2 index during data migration or reconciliation.
• Reverse lookup of all elements bound to a given structure type using the N1 index: SELECT proj_element_id FROM pa_proj_structure_types WHERE structure_type_id = :type_id;
• Extracting descriptive flexfield values (ATTRIBUTE_CATEGORY plus ATTRIBUTE1–15) into downstream analytics or data warehouse satellite tables.
• Audit and change-tracking reports driven by LAST_UPDATE_DATE and RECORD_VERSION_NUMBER, useful for reconciliation between source and target systems.

Related Objects

The most significant related objects, based on documented foreign-key relationships, are:

• PA.PA_PROJ_ELEMENTS — joined on PA_PROJ_STRUCTURE_TYPES.PROJ_ELEMENT_ID = PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID; the parent of the project element side of the relationship.
• BOM.BOM_STRUCTURE_TYPES_B — joined on PA_PROJ_STRUCTURE_TYPES.STRUCTURE_TYPE_ID = BOM_STRUCTURE_TYPES_B.STRUCTURE_TYPE_ID; supplies the structure type definition.
• PA.PA_PROJECTS_ALL — indirectly reachable through project elements and the projects-to-elements relationship, relevant when reporting structure types at the project level.
• PA.PA_PROJECT_STRUCTURES (dependent on release) — the broader structure container whose element/type assignments this table extends.

Because the object is supplied by Oracle Projects and depends on BOM lookup data, any custom development should treat PA_PROJ_STRUCTURE_TYPES as a satellite of the proj_element/structure_type link and preserve the U1 and U2 unique constraints when populating it through interfaces or DML.