Results for “status_level”

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

Overview

PA_PROJECT_ROLE_TYPES_B is the base (B) table in the Oracle Projects (PA) schema that stores implementation-defined responsibilities or positions assigned to employees on projects — commonly referred to as project role types. It is the definition table that drives role-based staffing, access levels, labour cost visibility, and job-level defaults across the Projects suite. In Oracle EBS 12.1.1 and 12.2.2, this table belongs to the PA product family and is owned by the APPS/PA schema. It carries the ZD_EDITION_NAME column, confirming it participates in the multi-tenant Editioning architecture introduced in EBS 12.2.2, so queries must be aware of edition (RUN_EDITION / EDITION) filtering when reading from the base table directly.

Under a heuristic Data Vault classification, this object is best modelled as satellite-leaning: it holds descriptive, time-stamped attributes that are functionally dependent on a project role identifier, and the edition column reinforces its role as a versioned descriptive store. It references PA_PROJECT_ROLE_TYPES (the associated translation/hub record), with the unique index PA_PROJECT_ROLE_TYPES_B_U1 on (PROJECT_ROLE_TYPE, ZD_EDITION_NAME) and PA_PROJECT_ROLE_TYPES_B_U3 on (PROJECT_ROLE_ID, ZD_EDITION_NAME) acting as business-key candidates.

Key Information Stored

The table comprises 38 documented columns. The most significant are:

• PROJECT_ROLE_TYPE — the user-visible code identifying the role (business key).
• MEANING — the descriptive name displayed to users and reports.
• PROJECT_ROLE_ID — the surrogate identifier that ties this base row to its translated row; it is also a documented foreign-key candidate in the surrogate structure.
• DESCRIPTION — extended narrative defining the role responsibilities.
• START_DATE_ACTIVE / END_DATE_ACTIVE — effective-dating controls governing when the role is selectable.
• MENU_ID — foreign key to FND_MENUS, linking the role to a menu that constrains role-specific navigation.
• DEFAULT_JOB_ID, DEFAULT_MIN_JOB_LEVEL, DEFAULT_MAX_JOB_LEVEL — job defaults and level thresholds used when assigning the role.
• DEFAULT_ACCESS_LEVEL and STATUS_LEVEL — govern the visibility and authorisation hierarchy of the role.
• QUERY_LABOR_COST_FLAG — controls whether users in this role can query labour costs.
• FREEZE_RULES_FLAG — determines whether assignment freeze rules apply.
• ROLE_PARTY_CLASS — classifies the party type associated with the role.
• RECORD_VERSION_NUMBER, LAST_UPDATE_DATE, CREATED_BY, and LAST_UPDATED_BY — audit and concurrency columns.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 — the standard EBS DFF (descriptive flexfield) columns for user extensions.
• ZD_EDITION_NAME — editioning discriminator for 12.2.2 multi-tenant data.

The surrogate primary key is the combination of PROJECT_ROLE_ID and ZD_EDITION_NAME (unique index U3); PROJECT_ROLE_TYPE with ZD_EDITION_NAME (U1) represents the alternate business-key candidate. Note that PPT_ASSIGNMENT_ID is documented as a foreign key column to PA_PROJECT_ASSIGNMENTS in the ETRM metadata, indicating a linkage — via the role assignment layer — to project staffing.

Common Use Cases and Queries

Typical scenarios include populating role-type lookup lists for project staffing, validating role assignments, and driving role-based access and labour-cost visibility in reports.

-- List all active role types for the current edition
SELECT b.project_role_type, b.meaning, b.description,
       b.start_date_active, b.end_date_active, b.query_labor_cost_flag
FROM   pa_project_role_types_b b
WHERE  TRUNC(SYSDATE) BETWEEN b.start_date_active
                          AND NVL(b.end_date_active, SYSDATE + 1)
AND    b.zd_edition_name = 'SET1';   -- current run edition

-- Join to translated table for a full name
SELECT b.project_role_type, t.meaning
FROM   pa_project_role_types_b b, pa_project_role_types_tl t
WHERE  b.project_role_id = t.project_role_id(+)
AND    t.language = USERENV('LANG');

Reporting use cases include headcount/role matrices for project managers, identifying roles with QUERY_LABOR_COST_FLAG enabled for cost-reporting breadth, and auditing DEFAULT_ACCESS_LEVEL values against corporate security policy.

Related Objects

• PA_PROJECT_ROLE_TYPES — the corresponding base/hub entity referenced via PROJECT_ROLE_ID.
• PA_PROJECT_ASSIGNMENTS — referenced through the documented PPT_ASSIGNMENT_ID foreign key; joins role-type definitions to actual staffed assignments.
• FND_MENUS — foreign-key target for MENU_ID, tying roles to navigational menus.
• PA_PROJECT_ROLE_TYPES_TL — translation table supplying language-specific MEANING values.
• PER_JOBS / PER_JOB_GROUPS — referenced indirectly through DEFAULT_JOB_ID and job-level defaults.
• PA_PROJECT_PARTIES and PA_PROJECT_PLAYERS — consume role definitions when staffing project teams.
• PA_ROLE_JOB_GROUPS (if licensed) — associates role types with specific job groups.

Because the table governs authorisation and labour-cost visibility, changes should be applied through the Oracle Projects role-type setup UI rather than direct DML, ensuring the auditing, effective-dating, and editioning columns remain consistent.