Results for “pay_mx_hourly_earnings_v”

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

Overview

PAY_MX_HOURLY_EARNINGS_V is a reporting view owned by the APPS schema within the Oracle E-Business Suite Payroll module (PAY). It is a Mexican (MX) localization object, designed to expose hourly earnings balances for assignments processed through payroll runs. The view consolidates payroll action data, assignment action data, and balance definitions into a single denormalized structure suitable for statutory reporting, earnings analysis, and downstream integration with external payroll processing or government-mandated reporting formats.

The view is of particular relevance in EBS 12.1.1 and 12.2.2 environments where the Mexican payroll localization is enabled. It serves as a bridge between the transactional payroll action tables and the balance calculation utilities, allowing report developers and integrators to retrieve earnings values in both run-level and year-to-date terms without navigating the complexity of the underlying balance architecture. Because it references the PAY_AC_UTILITY and PAY_MX_UTILITY packages via calling functions, the view dynamically resolves balance names, reporting names, and calculated values at query time.

Underlying Base Objects

The view is defined over several payroll base objects and utilities, per the documented ETRM metadata:

• PAY_PAYROLL_ACTIONS (PPA) — Provides payroll action context, including action type, effective date (aliased as DATE_PAID), and date earned.
• PAY_ASSIGNMENT_ACTIONS (PAA) — Supplies assignment-level action records, action status, assignment ID, tax unit ID, and run type ID.
• PAY_BALANCE_TYPES (PBT) — Supplies balance type definitions, including UOM and balance type ID, which form the basis for earnings and hours balances.
• PAY_DEFINED_BALANCES (PDB) and PAY_BALANCE_ATTRIBUTES (PBA) — Connect balance types to balance attributes.
• PAY_BAL_ATTRIBUTE_DEFINITIONS (PBAD) — Filtered by ATTRIBUTE_NAME = 'HOURLY EARNINGS' and BUSINESS_GROUP_ID IS NULL, restricting the view to the hourly earnings attribute across legislation contexts.
• PAY_AC_UTILITY (Package) — Provides GET_BALANCE_NAME, GET_BAL_REPORTING_NAME, and GET_VALUE functions used to resolve balance names and calculate run-level and YTD values.
• PAY_MX_UTILITY (Package) — Provides GET_HOURS_BAL_TYPE_ID, which derives the corresponding hours balance type from an earnings balance type.
• PAY_RUN_BALANCES — Referenced indirectly through the utility functions for balance value retrieval.

Key Columns

The view exposes a rich set of columns organized around assignment actions and balance values:

• ASSIGNMENT_ID, TAX_UNIT_ID, ASSIGNMENT_ACTION_ID, PAYROLL_ACTION_ID — Core identifiers linking the record to the assignment, tax unit, and specific payroll action.
• ACTION_STATUS, ACTION_TYPE, RUN_TYPE_ID — Status and classification of the payroll action, useful for filtering completed or reversed runs.
• DATE_PAID (EFFECTIVE_DATE), DATE_EARNED — Temporal columns for period-based reporting and reconciliation.
• EARN_BAL_TYPE_ID, EARN_BAL_NAME, EARN_REPORTING_NAME, EARN_BAL_UOM — Earnings balance identity, human-readable name, reporting name, and unit of measure.
• HOURS_BAL_TYPE_ID, HOURS_BAL_NAME, HOURS_REPORTING_NAME — Derived hours balance identity and names, obtained via PAY_MX_UTILITY.GET_HOURS_BAL_TYPE_ID.
• EARN_RUN_VAL, EARN_YTD_VAL — Earnings values for the current run and year-to-date, calculated by PAY_AC_UTILITY.GET_VALUE using the '_ASG_GRE_RUN' and '_ASG_GRE_YTD' balance dimensions.
• HOURS_RUN_VAL, HOURS_YTD_VAL — Corresponding hours values for the run and YTD periods.

Common Use Cases and Queries

This view is typically used in Mexican localization reporting where hourly earnings must be reported alongside hours worked. Common scenarios include statutory earnings reporting, reconciliation of payroll run results against expected hourly balances, and data extraction for external interfaces.

A typical query to retrieve hourly earnings for a specific assignment and payroll action is shown below:

SELECT assignment_id,
       tax_unit_id,
       date_paid,
       earn_bal_name,
       earn_run_val,
       earn_ytd_val,
       hours_run_val,
       hours_ytd_val
FROM  apps.pay_mx_hourly_earnings_v
WHERE  assignment_id = :p_assignment_id
AND    action_status = 'C';

For period-based reporting, developers often filter on date_paid and action_status to include only completed payroll runs, aggregating earn_run_val and hours_run_val by assignment or tax unit. Because the value columns are populated through package function calls, performance can be affected on large data sets; restricting by assignment_id, payroll_action_id, or date ranges is recommended. The view does not expose all balance attributes, so it should be treated as a specialized hourly earnings extract rather than a general-purpose balance reporting view.