Search Results pa_structure_types_u2

Overview

PA.PA_STRUCTURE_TYPES is a reference (seed) table in the Oracle E-Business Suite Projects (PA) schema that stores user-defined structure type definitions. The table is classified as a validated, standalone data object that resides in the APPS_TS_SEED tablespace, reflecting its role as a setup or seed data repository rather than a transactional table. Its documented purpose states that it stores user-defined structure types and is reserved for future use, which means its consumption in the standard 12.1.1 and 12.2.2 code base is limited and largely preparatory.

From a Data Vault modeling perspective, the metadata's heuristic classification places this table as a standalone object rather than a hub, link, or satellite. That classification is a modeling suggestion derived from the absence of inbound foreign-key dependencies; no other table in the documented relationship set references PA_STRUCTURE_TYPES as a parent. The single documented foreign-key relationship is an outbound reference from STRUCTURE_TYPE_ID to BOM_STRUCTURE_TYPES_B, indicating a conceptual alignment with Oracle Bills of Material structure type definitions. This makes the table best treated as a lookup or reference entity within a dimensional or Data Vault model, with STRUCTURE_TYPE_ID as the natural business key candidate.

Key Information Stored

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

• STRUCTURE_TYPE_ID (NUMBER(15)) — the system-generated surrogate primary key that uniquely identifies each user-defined structure type. It is enforced by the unique index PA_STRUCTURE_TYPES_U1.
• STRUCTURE_TYPE (VARCHAR2(150)) — the descriptive name of the structure type. It is a business-key candidate, enforced by unique index PA_STRUCTURE_TYPES_U2.
• STRUCTURE_TYPE_CLASS_CODE (VARCHAR2(30)) — the class or category to which the structure type belongs; a further business-key candidate enforced by PA_STRUCTURE_TYPES_U3.
• RECORD_VERSION_NUMBER (NUMBER(15)) — a sequence number supporting optimistic locking in self-service applications.
• ATTRIBUTE_CATEGORY (VARCHAR2(30)) — the descriptive flexfield context field.
• ATTRIBUTE1 through ATTRIBUTE13 (VARCHAR2(150)) — descriptive flexfield segments for extensible user-defined attributes.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN — the standard Who columns recording audit trail information.
• ZD_EDITION_NAME — the editioning column that participates in all three unique indexes, reflecting the Edition-Based Redefinition (EBR) architecture used in EBS 12.2.x.

The three unique indexes combine the business identifier with ZD_EDITION_NAME, so uniqueness is scoped to the edition in which the row is created. This is important for 12.2.2 environments, where the same logical business key can exist across editions.

Common Use Cases and Queries

Because the table is documented as reserved for future use, practical usage centers on inspection, validation, and reference reporting rather than transactional processing. A typical query retrieves all defined structure types with their class codes:

SELECT structure_type_id,
       structure_type,
       structure_type_class_code
FROM   pa.pa_structure_types
ORDER  BY structure_type;

To resolve the outbound foreign-key reference against the BOM structure type definition:

SELECT p.structure_type_id,
       p.structure_type,
       b.structure_type_name
FROM   pa.pa_structure_types p,
       bom.bom_structure_types_b b
WHERE  p.structure_type_id = b.structure_type_id;

For descriptive flexfield reporting, a query can pivot the ATTRIBUTE_CATEGORY and ATTRIBUTE1..13 columns to expose user-defined context data. A common validation pattern is to detect duplicates or edition overlaps by grouping on the business keys:

SELECT structure_type,
       structure_type_class_code,
       COUNT(*) 
FROM   pa.pa_structure_types
GROUP  BY structure_type, structure_type_class_code
HAVING COUNT(*) > 1;

Typical scenarios include setup verification during implementations, seed data comparison during upgrades from 12.1.1 to 12.2.2, and integration queries that map PA structure types to BOM structure types.

Related Objects

The documented relationship set identifies the following objects as significant to PA_STRUCTURE_TYPES:

• BOM.BOM_STRUCTURE_TYPES_B — referenced by PA_STRUCTURE_TYPES.STRUCTURE_TYPE_ID; the primary outbound foreign-key relationship in the metadata.
• PA.PA_STRUCTURE_TYPES_U1 — unique index on STRUCTURE_TYPE_ID and ZD_EDITION_NAME, enforcing the surrogate primary key.
• PA_STRUCTURE_TYPES_U2 — unique index on STRUCTURE_TYPE and ZD_EDITION_NAME, enforcing the business-name uniqueness that users search for.
• PA_STRUCTURE_TYPES_U3 — unique index on STRUCTURE_TYPE_CLASS_CODE and ZD_EDITION_NAME, enforcing class-code uniqueness.

Because the metadata classifies this table as standalone with no inbound foreign keys, no dependent child tables are documented. Consumers should treat it as a reference entity and verify any additional usage against the specific EBS patch level in use, given the documented note that the table is reserved for future use.