Results for “per_assignment_status_types_v”

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

Overview

PER_ASSIGNMENT_STATUS_TYPES_V is an APPS-owned, VALID database view within the Oracle E-Business Suite Human Resources (PER) product module. It is documented in ETRM for both 12.1.1 and 12.2.2 and is described as a view "used to support status types table and translated table." In practical terms, the view presents assignment status type definitions together with their language-specific, translated descriptive columns, so that consumers can read the user-facing status names and the external status code for a given session language without manually joining the base and translation tables.

Its principal integration role is to expose a single, denormalized row per assignment status type that includes the translated USER_STATUS label and the EXTERNAL_STATUS code, alongside the functional flags and system status derivations used by HR, Payroll, and downstream interfaces. Because it resolves the translation using the current session language, it behaves as a reporting and integration convenience layer over the multilingual storage model.

Underlying Base Objects

The view is defined over two synonyms that resolve to the underlying PER tables:

• PER_ASSIGNMENT_STATUS_TYPES (SYNONYM) — the base transactional/definition table holding the untranslated attributes of each assignment status type (identifier, business group, legislation, flags, and system statuses).
• PER_ASSIGNMENT_STATUS_TYPES_TL (SYNONYM) — the translated table holding language-specific text, specifically USER_STATUS and EXTERNAL_STATUS.

The join is performed on ASSIGNMENT_STATUS_TYPE_ID (AST.ASSIGNMENT_STATUS_TYPE_ID = ASTTL.ASSIGNMENT_STATUS_TYPE_ID), with the translation side constrained by ASTTL.LANGUAGE = USERENV('LANG'). This restricts the result to the language of the current session. The view also selects AST.ROWID as ROW_ID, which is why the first documented column is named ROW_ID rather than ROWID.

Key Columns

• ROW_ID — the ROWID of the base PER_ASSIGNMENT_STATUS_TYPES row; useful for identity-based operations.
• ASSIGNMENT_STATUS_TYPE_ID — the primary identifier linking the base and translated records.
• BUSINESS_GROUP_ID — the business group context for the status type.
• LEGISLATION_CODE — the legislation under which the status type applies.
• USER_STATUS — the translated, user-facing status label (from the TL table).
• EXTERNAL_STATUS — the external status code exposed for integrations and interfaces; this is the column most commonly targeted by searches for "external_status."
• ACTIVE_FLAG, DEFAULT_FLAG, PRIMARY_FLAG — flags indicating whether the status type is active, is the default, and is primary.
• PAY_SYSTEM_STATUS, PER_SYSTEM_STATUS — the derived system statuses used by Payroll and HR respectively.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard audit columns inherited from the base table.

Common Use Cases and Queries

Typical uses include looking up the external status code for a status type, validating active statuses within a business group, and joining assignment status values to their external equivalents in outbound interfaces. A representative query is:

• SELECT external_status, user_status, assignment_status_type_id FROM per_assignment_status_types_v WHERE active_flag = 'Y';
• SELECT assignment_status_type_id, external_status FROM per_assignment_status_types_v WHERE external_status = :p_external_status;
• SELECT user_status, per_system_status, pay_system_status FROM per_assignment_status_types_v WHERE business_group_id = :p_bg_id;

Because translation is resolved by USERENV('LANG'), results for USER_STATUS and EXTERNAL_STATUS reflect the session language; integrations requiring a specific language should set the session language accordingly.