Results for “approval_status_code”

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

Overview

PA_ROLE_PROFILES is a Projects (PA) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores business-level attributes and resource identifiers for each project role profile. Role profiles in Oracle Projects define the criteria by which resources are matched to project roles, governing how labor, jobs, positions, organizations, and named resources are associated with specific roles used in project staffing, forecasting, and costing.

The table distinguishes between two categories of entries. A resource profile carries a specific RESOURCE_ID and represents an actual named resource bound to role criteria. A business-level specific profile, sometimes referred to as a default profile, defines the role profile at the organizational or business group level without a named resource. This distinction allows role profiles to function both as templates and as concrete resource assignments.

From a Data Vault modeling perspective, the heuristic classification derived from the foreign key structure is a link. PA_ROLE_PROFILES connects multiple reference entities — resources, jobs, positions, and organizations — through its foreign keys, making it a natural relationship or association table in a dimensional or Data Vault representation.

Key Information Stored

The physical schema documents 17 columns. The most significant are:

• PROFILE_ID — the surrogate primary key and the single documented unique index candidate (PA_ROLE_PROFILES_U1).
• PROFILE_NAME and DESCRIPTION — the business-facing identifiers for the role profile.
• PROFILE_TYPE_CODE — distinguishes resource profiles from business-level default profiles.
• EFFECTIVE_START_DATE and EFFECTIVE_END_DATE — date-effective range controlling when the profile is valid, supporting date-tracked changes.
• APPROVAL_STATUS_CODE — the workflow or approval state of the profile record.
• BUSINESS_GROUP_ID — the HR business group owning the record.
• RESOURCE_ID — foreign key to PA_RESOURCES, populated for resource-type profiles.
• JOB_ID — foreign key to PER_JOBS, linking the profile to a job definition.
• POSITION_ID — foreign key to PER_ALL_POSITIONS, linking the profile to a position.
• ORGANIZATION_ID — foreign key to HR_ALL_ORGANIZATION_UNITS, identifying the associated organization.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard EBS audit columns.

The surrogate key is PROFILE_ID; no separate composite natural business key is documented in the unique index beyond PROFILE_ID itself.

Common Use Cases and Queries

Typical reporting and integration scenarios involve resolving which resources map to which jobs, positions, or organizations, and validating profile activity windows.

• List active profiles for a given role: SELECT profile_id, profile_name, profile_type_code FROM pa_role_profiles WHERE TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;
• Identify all resource-bound profiles: SELECT p.profile_name, r.resource_name FROM pa_role_profiles p, pa_resources r WHERE p.resource_id = r.resource_id;
• Analyze profiles by job and position: join PER_JOBS on JOB_ID and PER_ALL_POSITIONS on POSITION_ID to report staffing coverage per role.
• Review approval workflow status: filter on APPROVAL_STATUS_CODE to find pending or rejected profiles.
• Date-effective history checks: query profiles where EFFECTIVE_END_DATE has passed to detect expired role assignments.

Related Objects

The following objects are directly referenced through foreign keys or depend on PA_ROLE_PROFILES:

• PA_RESOURCES — joined on PA_ROLE_PROFILES.RESOURCE_ID = PA_RESOURCES.RESOURCE_ID.
• PER_JOBS — joined on PA_ROLE_PROFILES.JOB_ID = PER_JOBS.JOB_ID.
• PER_ALL_POSITIONS — joined on PA_ROLE_PROFILES.POSITION_ID = PER_ALL_POSITIONS.POSITION_ID.
• HR_ALL_ORGANIZATION_UNITS — joined on PA_ROLE_PROFILES.ORGANIZATION_ID = HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID.
• Role profile lookups and Projects staffing, forecasting, and resource assignment processes in Oracle Projects depend on this table for role-to-resource resolution.