Search Results select_table

Overview

PSB.PSB_ATTRIBUTE_TYPES is a reference and metadata table within the Oracle E-Business Suite Human Resource Management Systems (HRMS) module, owned by the PSB schema. It stores the definitions of attribute types that drive how employee, position, and assignment attributes are resolved dynamically at runtime. Such attributes may originate from Key Flexfields (KFF), Descriptive Flexfields (DFF), QuickCodes (QC), or arbitrary application tables (Others). Rather than hard-coding the source of each attribute, EBS records the select clause, table, join key, and filtering predicate here, allowing the rendering engine to construct attribute-value queries on the fly.

The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10. It is uniquely indexed by PSB_ATTRIBUTE_TYPES_U1 on ATTRIBUTE_TYPE_ID in the APPS_TS_TX_IDX tablespace, and its primary key is PSB_ATTRIBUTE_TYPES_PK on the same column. Based on the documented foreign-key topology, the heuristic Data Vault classification for this object is standalone, suggesting it functions as an independent reference hub whose surrogate key is consumed by dependent transactional or configuration tables rather than participating in link-style relationships itself.

Key Information Stored

The table contains 14 documented columns. The most significant include:

• ATTRIBUTE_TYPE_ID — NUMBER(15), the surrogate primary key and unique identifier for each attribute type definition. This is the column surfaced by the user's search term and is the target of the PSB_ATTRIBUTE_TYPES_U1 unique index.
• ATTRIBUTE_TYPE — VARCHAR2(20), the classification of the attribute source: KFF (Key Flexfield), DFF (Descriptive Flexfield), QC (QuickCodes), or Table (Others).
• NAME — VARCHAR2(30), the name of the attribute belonging to the business group.
• SELECT_COLUMN and SELECT_TABLE — VARCHAR2(50) each; identify the column and table from which the attribute value is fetched.
• SELECT_KEY — VARCHAR2(30), the key column on the select table joined to the link key column.
• SELECT_WHERE — VARCHAR2(1500), an arbitrary predicate appended to the generated select statement.
• LINK_KEY — VARCHAR2(30), the key column in the table indicated by LINK_TYPE.
• LINK_TYPE — VARCHAR2(30), indicating whether the attribute associates with the Assignment (A), Employee (E), or Position (P).
• Standard Who columns: LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE.

The surrogate key is ATTRIBUTE_TYPE_ID; the only documented business-key candidate is the unique index PSB_ATTRIBUTE_TYPES_U1, which happens to mirror the primary key rather than introducing a separate natural key.

Common Use Cases and Queries

Typical scenarios involve resolving HR attributes at runtime—populating employee or position detail forms, building attribute-driven reports, or auditing which flexfields and QuickCodes back specific HR fields. A representative lookup by primary key is:

• SELECT attribute_type, name, select_table, select_column, link_type FROM psb.psb_attribute_types WHERE attribute_type_id = :p_id;
• Bulk enumeration by type: SELECT attribute_type_id, name FROM psb.psb_attribute_types WHERE attribute_type = 'KFF' ORDER BY name;
• Joining to dependent attribute values: SELECT a.attribute_type_id, t.name, a.attribute_value FROM psb.psb_attributes a, psb.psb_attribute_types t WHERE a.attribute_type_id = t.attribute_type_id AND t.link_type = 'E';

Reporting use cases include cataloguing all DFF-backed attributes, verifying SELECT_TABLE/SELECT_COLUMN integrity before upgrades, and tracing which business-group attributes map to employee versus assignment versus position scopes via LINK_TYPE.

Related Objects

The documented dependency graph is intentionally narrow. The principal referencing object is:

• PSB.PSB_ATTRIBUTES — references PSB_ATTRIBUTE_TYPES via PSB_ATTRIBUTES.ATTRIBUTE_TYPE_ID → PSB_ATTRIBUTE_TYPES.ATTRIBUTE_TYPE_ID. This is the primary consumer of the reference definitions and the table most commonly joined for attribute-value retrieval.

Beyond the documented FK, related HRMS objects typically include the HR business-group and person/assignment/position entities referenced indirectly through LINK_TYPE semantics, as well as flexfield definition tables (FND_ID_FLEX_STRUCTURES, FND_DESCRIPTIVE_FLEXS) and QuickCode tables (FND_LOOKUP_VALUES) whose values the SELECT_TABLE/SELECT_COLUMN pairs point to. Because PSB_ATTRIBUTE_TYPES is classified as standalone with no inbound foreign keys other than PSB_ATTRIBUTES, it behaves as an independent reference hub within the PSB module schema.