Search Results max_action_sequence

Overview

PAY_JP_LAST_ACT_FOR_PYJPRLDA_V is a payroll view belonging to the PAY — Payroll product within Oracle E-Business Suite. As stated in the ETRM metadata, the view is used for Japanese localization only. It is a date-bounded, purpose-built query object that returns the last payroll action associated with an assignment within a fixed historical window (1999/01/01 through 1999/12/31), restricted to a specific set of action types. It does not represent a general-purpose payroll reporting entity; rather, it supports the specific needs of the Japanese payroll localization, where payroll action history must be reconciled against a defined statutory or migration period.

The user search term max_effective_date corresponds directly to a column exposed by this view. The view does not merely return existing columns from the base tables; it aliases an aggregate-related column, PACT.EFFECTIVE_DATE MAX_EFFECTIVE_DATE, so that consumers receive a column named MAX_EFFECTIVE_DATE. This naming signals the view's intent: to surface the latest effective date for a given assignment's qualifying payroll action.

Note that the ETRM metadata records this view as not implemented in this database in the reference environment. Its definition is nevertheless retained because the Japanese localization may deploy it in customer instances, and because the view text documents the historical business rules applied during a specific migration period.

Underlying Base Objects

The documented view metadata lists no referenced base objects explicitly, but the view text itself is defined over two Payroll base tables:

• PAY_PAYROLL_ACTIONS (aliased PACT and again as PPA, PPA2 in subqueries) — the payroll action header, supplying BUSINESS_GROUP_ID, PAYROLL_ID, PAYROLL_ACTION_ID, EFFECTIVE_DATE, and ACTION_TYPE.
• PAY_ASSIGNMENT_ACTIONS (aliased ASSACT, PAA, PAA2) — the assignment-level action detail, supplying ASSIGNMENT_ID, ACTION_SEQUENCE, PAYROLL_ACTION_ID, and ASSIGNMENT_ACTION_ID.
• PAY_ACTION_INTERLOCKS (aliased PAI2) — used within a correlated NOT EXISTS clause to exclude actions that are locked by a 'V' (void) action.

The view joins PAY_ASSIGNMENT_ACTIONS to PAY_PAYROLL_ACTIONS on PAYROLL_ACTION_ID and applies multiple correlated EXISTS and NOT EXISTS predicates. These predicates restrict the result set to assignments having at least one qualifying action between 1999/01/01 and 1999/03/31, exclude assignments whose actions are locked by a void action, and exclude any row for which a later ACTION_SEQUENCE exists for the same assignment within the window. The effective result is the last action per assignment for which the effective date falls in the qualifying period.

Key Columns

• BUSINESS_GROUP_ID — the business group owning the payroll action; used to partition results by legal employer or business group context. The commented-out predicate suggests this is a common runtime filter.
• PAYROLL_ID — identifies the payroll to which the action belongs; also subject to a commented-out bind parameter, indicating the view is intended to be filtered by payroll at query time.
• ASSIGNMENT_ID — the assignment (person-to-position/payroll relationship) associated with the action. This is the primary key by which the "last action" is determined.
• MAX_ACTION_SEQUENCE — aliases ASSACT.ACTION_SEQUENCE; identifies the highest action sequence retained for the assignment, confirming that only the final action within the window is returned.
• MAX_EFFECTIVE_DATE — aliases PACT.EFFECTIVE_DATE; the effective date of that last action. This is the column corresponding to the user's search term and is the principal output of interest for sequencing and reconciliation logic.

Common Use Cases and Queries

Typical usage centers on the Japanese localization's need to identify, per assignment, the last qualifying payroll action within 1999 and its effective date. A representative query, applying the commented-out bind parameters as literal filters, is:

• SELECT assignment_id, max_action_sequence, max_effective_date FROM pay_jp_last_act_for_pyjprlda_v WHERE business_group_id = :p_business_group_id AND payroll_id = :p_payroll_id;
• Determining the effective date of the last action for reconciliation against statutory reporting obligations during the localized migration window.
• Feeding downstream Japanese payroll reports that require identification of the terminal action per assignment when prior actions were voided or superseded.

Because the view embeds hard-coded date ranges and specific action types ('R', 'Q', 'B', 'I'), it should not be repurposed for general payroll action reporting outside the Japanese localization context. Consumers requiring current data should use the base tables directly, as the view's fixed 1999 window will not reflect subsequent periods.