Results for “pay_system_status”

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

Overview

The view APPS.HRI_CL_ASGSTYP_BX_V belongs to the HRI — Human Resources Intelligence product family, an obsolete Oracle EBS module once used to support HR business intelligence, data warehousing, and extract-driven reporting. The suffix _BX_V denotes a "business extract" view, a naming convention used throughout the HRI and HRMS analytical layers to identify flattened, denormalized structures intended for consumption by downstream ETL processes and reporting tools rather than by OLTP forms.

Functionally, HRI_CL_ASGSTYP_BX_V exposes assignment status type definitions — the seeded and user-defined statuses such as Active, Terminated, Suspended, and their amendment variants — in a single, query-friendly result set. It resolves the multilanguage translation rows, the amendment or change records, and the security profile restriction rules into one row per effective assignment status type. Its role in Oracle EBS is therefore that of a reporting and integration surface: it shields report writers and extract programs from the complexity of joining the base status tables, their translation tables, and the security profile function, while simultaneously applying row-level business group security.

The status is documented as VALID in the APPS schema and the view is declared WITH READ ONLY, confirming it is strictly a query-only object. The column PER_SYSTEM_STATUS, which appears among the exposed columns, is the payload attribute governing how the status participates in the person or per-system (assignment status) orchestration logic — the attribute a user searching on "per_system_status" would be looking for.

Underlying Base Objects

The view is defined over six documented referenced objects. The 12.2.2 ETRM metadata lists them as follows:

• PER_ASSIGNMENT_STATUS_TYPES (synonym) — the base table holding assignment status type definitions, business group, and the system status flags.
• PER_ASSIGNMENT_STATUS_TYPES_TL (synonym) — the translation table supplying the user-visible status name for the session language.
• PER_ASS_STATUS_TYPE_AMENDS (synonym) — the amendment table capturing dated changes to a status type.
• PER_ASS_STATUS_TYPE_AMENDS_TL (synonym) — the translation table for amendment rows.
• HR_GENERAL (package) — supplies the START_OF_TIME and END_OF_TIME date boundary constants.
• HR_BIS (package) — supplies GET_SEC_PROFILE_BG_ID, the security profile business group identifier used in the WHERE clause.

The joins are outer-driven from PER_ASSIGNMENT_STATUS_TYPES to its translation rows and to the amendment records via ASSIGNMENT_STATUS_TYPE_ID (with the amendment side marked (+)). The NVL constructs at each projection point ensure that when no amendment exists the base status type values are returned, producing a stable superset of status definitions. The final predicate constrains rows to the security profile's business group using HR_BIS.GET_SEC_PROFILE_BG_ID, so the view is inherently security-aware.

Key Columns

The view exposes ten columns whose semantics are as follows:

• ID — the ASSIGNMENT_STATUS_TYPE_ID, the primary key of the status type.
• VALUE — the translated user status name, resolved from the amendment translation when present, otherwise from the base translation.
• DATE_FROM / DATE_TO — the effective date boundaries, populated from the HR_GENERAL.START_OF_TIME and END_OF_TIME constants.
• BUSINESS_GROUP_ID — the owning business group, taken from the amendment row when available, else the base row.
• PAY_SYSTEM_STATUS — the payload status flag controlling payroll system behavior.
• PER_SYSTEM_STATUS — the per-system (assignment status) flag indicating how the status is treated by the person/assignment system; this is the attribute most commonly queried against this view.
• PRIMARY_FLAG — indicates a primary status type.
• DEFAULT_FLAG — indicates the default status type for the business group.
• VALID_CODE — the active flag from the amendment when present, otherwise from the base status type.

Only rows whose resolved ACTIVE_FLAG is 'Y' are returned.

Common Use Cases and Queries

Typical uses include populating HR analytics extracts, validating status-to-system mappings, and providing lookup lists for reporting tools. A representative query filtering on the searched attribute is:

• SELECT ID, VALUE, PER_SYSTEM_STATUS FROM APPS.HRI_CL_ASGSTYP_BX_V WHERE PER_SYSTEM_STATUS IS NOT NULL;
• SELECT ID, VALUE, BUSINESS_GROUP_ID FROM APPS.HRI_CL_ASGSTYP_BX_V WHERE PRIMARY_FLAG = 'Y';
• SELECT ID, VALUE FROM APPS.HRI_CL_ASGSTYP_BX_V WHERE VALID_CODE = 'Y' ORDER BY VALUE;

Because the view enforces security profile business group filtering and reads only active rows, it is suitable for operational extracts without additional predicates. Note that HRI is obsolete; new development should target supported HRMS views.