Results for “pay_assignment_actions_pk”

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

Overview

PAY_EMEA_PAYMNTS_ACTION_INFO_V is an APPS-owned database view within the Oracle E-Business Suite Payroll (PAY) module. It is registered in the ETRM metadata with a status of VALID and is defined in the APPS schema. The view is designed to present payment-related action information for EMEA (Europe, Middle East, and Africa) payroll processing, exposing element definition data tied to payroll and assignment actions so that it can be consumed by reporting tools, interfaces, and concurrent programs.

The view the user searched for, "pay_assignment_actions_pk," is significant because PAY_ASSIGNMENT_ACTIONS is joined twice inside this view using that primary key index. Both PAA1 and PAA2 aliases reference PAY_ASSIGNMENT_ACTIONS, and the view author deliberately supplied an INDEX(PAA1 PAY_ASSIGNMENT_ACTIONS_PK) and INDEX(PAA2 PAY_ASSIGNMENT_ACTIONS_PK) hint. This confirms that PAY_ASSIGNMENT_ACTIONS_PK is the access path that drives the nested-loop execution plan, making it a critical object for tuning the view's performance.

Underlying Base Objects

The view is defined over the following documented base objects, as recorded in the ETRM 12.2.2 metadata: FND_NUMBER (PACKAGE), PAY_ACTION_INFORMATION (SYNONYM), PAY_ACTION_INTERLOCKS (SYNONYM), PAY_ASSIGNMENT_ACTIONS (SYNONYM), PAY_EMEA_PAYMENT_VALUES_V (VIEW), and PAY_PAYROLL_ACTIONS (SYNONYM).

• PAY_ACTION_INTERLOCKS — aliased LCK; supplies the LOCKING_ACTION_ID and LOCKED_ACTION_ID linkage. Access is forced through PAY_ACTION_INTERLOCKS_PK.
• PAY_ASSIGNMENT_ACTIONS — used twice (PAA1, PAA2). PAA1.ASSIGNMENT_ACTION_ID joins to the locked action, with SOURCE_ACTION_ID required to be NULL; PAA2 is reached via the locking action ID and linked to PAY_ACTION_INFORMATION.
• PAY_PAYROLL_ACTIONS — aliased PPA; restricted to ACTION_TYPE IN ('P','U'). Access is forced through PAY_PAYROLL_ACTIONS_PK.
• PAY_ACTION_INFORMATION — aliased PAI; filtered to ACTION_CONTEXT_TYPE = 'PA' and ACTION_INFORMATION_CATEGORY = 'EMEA ELEMENT DEFINITION'. Index PAY_ACTION_INFORMATION_N2 is requested.
• PAY_EMEA_PAYMENT_VALUES_V — aliased PET; provides RESULT_VALUE, joined on ASSIGNMENT_ACTION_ID, ELEMENT_TYPE_ID, and INPUT_VALUE_ID.
• FND_NUMBER — the FND numeric utility package available to the underlying logic.

The view carries an ORDERED hint plus explicit index hints, annotated as "HINTS ADDED TO RESOLVE PERFORMANCE BUG 2421978," indicating that the join order is fixed for a known performance issue.

Key Columns

Documented columns exposed by the view are:

• ACTION_CONTEXT_ID — the payroll action context identifier, linking to PAA2.PAYROLL_ACTION_ID.
• NARRATIVE — descriptive text associated with the payment/action information.
• PAYMENT_TYPE — the classification of the payment record.
• VALUE — the result value sourced from PAY_EMEA_PAYMENT_VALUES_V (PET.RESULT_VALUE), selected twice in the view text.
• NUMERIC_VALUE — the numeric representation of the returned value.

Additional projected expressions include LCK.LOCKING_ACTION_ID and the action information attributes ACTION_INFORMATION4 and ACTION_INFORMATION5 from PAY_ACTION_INFORMATION.

Common Use Cases and Queries

This view is typically used to reconcile EMEA element definition payments against assignment and payroll actions, supporting payment auditing and reporting.

A representative query filtering by action context:

• SELECT action_context_id, narrative, payment_type, value, numeric_value FROM apps.pay_emea_paymnts_action_info_v WHERE action_context_id = :p_payroll_action_id;

Aggregating payment values by type:

• SELECT payment_type, COUNT(*), SUM(numeric_value) FROM apps.pay_emea_paymnts_action_info_v GROUP BY payment_type;

Because the plan is hint-driven and relies on PAY_ASSIGNMENT_ACTIONS_PK, queries should filter on the locking or locked action identifiers where possible to preserve nested-loop efficiency and avoid full scans.