Search Results per_shared_types

Overview

The PER_SHARED_TYPES table is a core reference data repository within the Oracle E-Business Suite Human Resources (PER) module. It functions as a centralized lookup table designed to hold standardized 'type' and 'status' information that is shared across multiple HR and related application components. Its primary role is to enforce data integrity and consistency by providing a single source of truth for various classification codes, eliminating redundant storage and potential discrepancies. The table's design supports a flexible lookup mechanism, allowing different functional areas to define and maintain their own sets of types while leveraging a common data structure.

Key Information Stored

The table's structure is optimized for managing lookup values. The primary identifier is the SHARED_TYPE_ID, a unique system-generated key. The LOOKUP_TYPE column categorizes the broad group of lookup values (e.g., 'AVAILABILITY_STATUS', 'BUDGET_UNIT'), while the SHARED_TYPE_NAME stores the human-readable name for a specific type within that group. A critical column is SYSTEM_TYPE_CD, which holds the actual code value used by application logic and referenced by foreign key columns in other tables. This column, combined with LOOKUP_TYPE, forms a unique key (PER_SHARED_TYPE_UK1), ensuring code uniqueness within a specific lookup context.

Common Use Cases and Queries

A primary use case is retrieving descriptive names for status or type codes stored in transactional tables. For instance, when reporting on position availability or budget transactions, a join to PER_SHARED_TYPES is required to translate stored ID or code values into meaningful text. Common SQL patterns include lookups for specific code sets and joins to transactional data.

• Retrieve all types for a specific lookup category: SELECT shared_type_name, system_type_cd FROM per_shared_types WHERE lookup_type = 'AVAILABILITY_STATUS' ORDER BY shared_type_name;
• Join to position data to report availability status: SELECT pos.name, st.shared_type_name AS availability_status FROM hr_all_positions_f pos, per_shared_types st WHERE pos.availability_status_id = st.shared_type_id;
• Integrate with budget data to resolve unit types: SELECT bg.name, st1.shared_type_name AS unit1, st2.shared_type_name AS unit2 FROM pqh_budgets bg, per_shared_types st1, per_shared_types st2 WHERE bg.budget_unit1_id = st1.shared_type_id AND bg.budget_unit2_id = st2.shared_type_id;

Related Objects

As indicated by its foreign key relationships, PER_SHARED_TYPES is a critical reference point for several key transactional tables. The primary related objects are:

• HR_ALL_POSITIONS_F: References PER_SHARED_TYPES via the AVAILABILITY_STATUS_ID column to store a position's current availability status.
• PQH_BUDGETS: References this table multiple times (BUDGET_UNIT1_ID, BUDGET_UNIT2_ID, BUDGET_UNIT3_ID) to classify budget units, demonstrating its use in the Position Budgeting functionality.
• PQH_BUDGET_POOLS: References PER_SHARED_TYPES via BUDGET_UNIT_ID for budget pool classification.
• PQH_POSITION_TRANSACTIONS: References the table via AVAILABILITY_STATUS_ID to track status changes during position management transactions.

These relationships underscore the table's integral role in connecting standardized type codes with core HR, position, and budgeting entities.