Results for “per_shared_types”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PER_SHARED_TYPES is a reference and lookup table owned by the HR schema within the PER (Human Resources) product family of Oracle E-Business Suite. The table is documented as valid in both 12.1.1 and 12.2.2 and its stated purpose is to hold "type" and "status" information used across human resources, position management, and budgeting functions. Rather than storing transactional data, PER_SHARED_TYPES acts as a shared vocabulary of coded values that other modules reference through foreign key columns, ensuring consistent semantics for classification attributes such as availability status, budget unit, and shared type name.

From a data modeling perspective, the mined relationship structure classifies PER_SHARED_TYPES heuristically as a hub. This classification is a modeling suggestion: the table anchors a set of unique business keys (shared type identity), and multiple dependent tables attach to it via foreign keys, which is the classic signature of a hub surrounded by satellite and link structures in a Data Vault representation. In operational EBS terms, it behaves as a central lookup or code table supporting both HR and federal position/budgeting (PQH) functionality.

Key Information Stored

The physical schema contains 44 columns, but the functionally significant ones are the identity, classification, and audit attributes.

• SHARED_TYPE_ID — the surrogate primary key, enforced by the PER_SHARED_TYPES_PK index. It uniquely identifies each shared type row and is the column that downstream foreign keys resolve to.
• SHARED_TYPE_NAME — the descriptive business-key name of the shared type, forming part of the unique business key (PER_SHARED_TYPE_UK1) alongside LOOKUP_TYPE and SYSTEM_TYPE_CD.
• LOOKUP_TYPE — the classification of the lookup group, distinguishing which family of values this row belongs to and supporting the business-key uniqueness constraint.
• SYSTEM_TYPE_CD — the system-defined type code, the third business-key candidate component that qualifies the row within its lookup grouping.
• SHARED_TYPE_CODE — an additional code value used to identify the shared type in application logic.
• BUSINESS_GROUP_ID — the partitioning identifier that scopes the row to a specific business group, essential for multi-organization HR implementations.
• INFORMATION1 through INFORMATION30 — a wide, descriptor-flexfield-style block of attribute columns allowing context-specific values to be attached to a shared type without schema change.
• INFORMATION_CATEGORY — classifies the informational block associated with the row.
• OBJECT_VERSION_NUMBER — supports optimistic locking for concurrent updates.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard EBS audit columns recording who created and last modified each row.
• ZD_EDITION_NAME — the editioning column introduced by the 12.2 online patching architecture; it participates in the primary key index (SHARED_TYPE_ID, ZD_EDITION_NAME).

The distinction between the surrogate key (SHARED_TYPE_ID) and the business-key candidates (LOOKUP_TYPE, SYSTEM_TYPE_CD, SHARED_TYPE_NAME, and the edition-aware PK) matters for migration and integration work, where the surrogate is typically used for joins while the business keys enforce semantic uniqueness.

Common Use Cases and Queries

PER_SHARED_TYPES is queried when resolving codes into human-readable values, when validating that a status or unit code exists, and when building reference data extracts. A typical lookup pattern joins a dependent table to the shared type to decode a status:

SELECT p.position_id, p.availability_status_id, s.shared_type_name
FROM   hr_all_positions_f p,
       per_shared_types s
WHERE  p.availability_status_id = s.shared_type_id;

For budget-side reporting, resolving the budget unit code follows the same pattern against PQH_BUDGETS.BUDGET_UNIT1_ID through BUDGET_UNIT3_ID. A representative query identifies all defined shared types for a given lookup grouping:

SELECT shared_type_id, shared_type_name, system_type_cd
FROM   per_shared_types
WHERE  lookup_type = :p_lookup_type
AND    business_group_id = :p_bg_id;

Analysts also use the table for reference-data reconciliation, verifying that every foreign key value in the dependent tables resolves to a valid shared type row, and for auditing INFORMATION1–INFORMATION30 columns where site-specific attribute values have been populated.

Related Objects

The documented foreign keys establish PER_SHARED_TYPES as a parent of several position and budgeting tables. The most significant relationships are:

• HR_ALL_POSITIONS_F — joins on AVAILABILITY_STATUS_ID; positions reference a shared type to indicate availability status.
• PQH_BUDGETS — joins on BUDGET_UNIT1_ID, BUDGET_UNIT2_ID, and BUDGET_UNIT3_ID; each budget row may reference up to three shared types representing budget units.
• PQH_BUDGET_POOLS — joins on BUDGET_UNIT_ID; budget pools reference a shared type as their budget unit.
• PQH_POSITION_TRANSACTIONS — joins on AVAILABILITY_STATUS_ID; position transactions reference a shared type for availability status.

These four dependent tables collectively demonstrate the hub role of PER_SHARED_TYPES: it is the single reference point from which HR position management and the federal budgeting (PQH) modules draw their type and status code definitions. Maintaining this table correctly is therefore prerequisite to accurate position and budget reporting across both the 12.1.1 and 12.2.2 releases.