Results for “pay_payrolls_f_pk”

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

Overview

APPS.PAY_REVERSE_PAYROLL_V is a reporting view in Oracle E-Business Suite 12.1.1 and 12.2.2 that exposes payroll reversal and quick-payroll activity at the assignment-action level. It is defined over the payroll action, assignment action, payroll, consolidation set, assignment, run type, session, and lookup tables, and it filters specifically for payroll actions whose ACTION_TYPE is 'R' (reversal) or 'Q' (quick pay) and whose assignment action status is 'C' (completed). Because the view resolves the effective context from the current FND session — via USERENV('sessionid') and the FND_SESSIONS effective date — it presents the reversal and quick-pay records that are valid as of the session's effective date rather than as of the system date.

The user search term "pay_payrolls_f_pk" is directly relevant: the view text carries the optimizer hint /*+ INDEX(PPF PAY_PAYROLLS_F_PK) */, which forces the query planner to drive or join PAY_PAYROLLS_F through its primary key index. The view therefore exists to support queries that resolve reversal and quick-pay information efficiently against the payroll definition, and it is commonly consumed by payroll reconciliation reports, reversal diagnostics, and integration extracts.

Underlying Base Objects

The documented base objects underlying the view are:

• PAY_PAYROLL_ACTIONS (alias PPA) — the primary payroll action header, supplying action type, effective date, payroll action ID, and consolidation set ID.
• PAY_ASSIGNMENT_ACTIONS (alias PAA) — the assignment-level action lines, supplying assignment ID, action status, source action ID, run type ID, and action sequence.
• PAY_PAYROLLS_F (view, alias PPF) — the payroll definition, joined to PER_ALL_ASSIGNMENTS_F by PAYROLL_ID and to PAY_CONSOLIDATION_SETS by CONSOLIDATION_SET_ID.
• PAY_CONSOLIDATION_SETS (aliases PCS and PCS2) — supplies the consolidation set name for the action and the "reverse" consolidation set name associated with the payroll.
• PAY_RUN_TYPES_F_VL (alias PRT) — run type name, outer-joined on RUN_TYPE_ID and constrained by effective dates.
• PER_ALL_ASSIGNMENTS_F (alias PAF) — assignment context and payroll assignment.
• FND_SESSIONS (alias FND) — supplies the session effective date used throughout the date range predicates.
• HR_LOOKUPS (alias HR_T) — decodes ACTION_TYPE into a meaningful action type description.
• PAY_ACTION_INTERLOCKS — used in a NOT EXISTS subquery to exclude assignment actions that are locked by another action.
• Packages HR_API, HR_GENERAL, and HR_SECURITY are also referenced, indicating that security and general HR APIs participate in the view's execution context.

Key Columns

• EFFECTIVE_DATE — effective date of the payroll action, bounded by the session effective date.
• ACTION_TYPE — 'R' or 'Q' action code; ACT_TYP_MEANING presents the decoded lookup meaning, optionally suffixed with the run type name.
• ASSIGNMENT_ID / ASSIGNMENT_ACTION_ID / PAYROLL_ACTION_ID — the identifying keys for the assignment, its action line, and the parent payroll action.
• ACTION_STATUS — restricted to 'C' (completed) records.
• CONSOLIDATION_SET_NAME / REVERSE_CONSOL_NAME — the action's consolidation set and the payroll's reverse consolidation set.
• CONSOLIDATION_SET_ID / REVERSE_CONSOL_ID — numeric identifiers for the same.
• PAYROLL_ID — payroll definition identifier from PAY_PAYROLLS_F.
• SOURCE_ACTION_ID — links a reversal to the action it reverses.
• ACTION_SEQUENCE — ordering of the action within its payroll action.
• Audit columns — BUSINESS_GROUP_ID, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical uses include reverse/quick-pay audit reporting, reconciliation of reversals against source actions, and integration extracts that need reversal metadata within a session context. A representative query is:

• SELECT assignment_id, payroll_action_id, action_type, act_typ_meaning, action_status, consolidation_set_name, reverse_consol_name, effective_date FROM apps.pay_reverse_payroll_v WHERE effective_date BETWEEN :p_start AND :p_end ORDER BY effective_date, assignment_id;
• Trace a reversal: SELECT assignment_action_id, source_action_id, action_sequence FROM apps.pay_reverse_payroll_v WHERE source_action_id IS NOT NULL AND assignment_id = :p_assignment_id;
• Count reversals by consolidation set: SELECT consolidation_set_name, COUNT(*) FROM apps.pay_reverse_payroll_v GROUP BY consolidation_set_name;

Because the view depends on FND_SESSIONS and the current session's effective date, queries are best issued from a session whose effective date matches the payroll period being investigated; otherwise results may appear empty even where reversal records exist.