Search Results days_ytd_val

Overview

PAY_MX_EMPLOYEE_EARNINGS_V is an Oracle E-Business Suite payroll view owned by the APPS schema and delivered as part of the PAY — Payroll product. It exposes employee earnings information specifically for the Mexican (MX) payroll legislation, joining assignment-level payroll action data with balance type definitions and the derived "days" balance types used by Mexican statutory earnings reporting. The view is documented as VALID in ETRM for both 12.1.1 and 12.2.2.

The view's role is to flatten the relationship between a payroll action, an assignment action, and the earning balance type that was processed, while simultaneously resolving the corresponding balance names, reporting names, units of measure, and run-to-date/YTD values. Because it surfaces both currency-based earnings balances and their associated "days" balances, it is frequently used in Mexican payroll reporting, integration extracts, and downstream statutory layouts where an earning must be reported alongside the number of days it represents.

Underlying Base Objects

The view is defined over the following documented base objects:

• PAY_PAYROLL_ACTIONS (PPA) — the payroll action header, supplying ACTION_TYPE, EFFECTIVE_DATE (aliased DATE_PAID), and DATE_EARNED.
• PAY_ASSIGNMENT_ACTIONS (PAA) — the assignment-level action, supplying ASSIGNMENT_ID, TAX_UNIT_ID, ASSIGNMENT_ACTION_ID, ACTION_STATUS, RUN_TYPE_ID, and BUSINESS_GROUP_ID.
• PAY_BALANCE_TYPES (PBT) — the defining row for the earning balance, supplying BALANCE_TYPE_ID, BALANCE_UOM, BUSINESS_GROUP_ID, and LEGISLATION_CODE.
• PAY_DEFINED_BALANCES (PDB), PAY_BALANCE_ATTRIBUTES (PBA), and PAY_BAL_ATTRIBUTE_DEFINITIONS (PBAD) — used to filter the balance types to those carrying the attribute EMPLOYEE EARNINGS.
• PAY_RUN_BALANCES — the underlying balances referenced indirectly through the balance retrieval utility calls.

The view also invokes the PL/SQL packages PAY_AC_UTILITY (GET_BALANCE_NAME, GET_BAL_REPORTING_NAME, GET_VALUE) and PAY_MX_UTILITY (GET_DAYS_BAL_TYPE_ID). The predicate restricts rows to balance types whose attribute name is EMPLOYEE EARNINGS with a null BUSINESS_GROUP_ID (a seeded, non-business-group-specific definition).

Key Columns

• Identity/context: ASSIGNMENT_ID, ASSIGNMENT_ACTION_ID, PAYROLL_ACTION_ID, TAX_UNIT_ID, BUSINESS_GROUP_ID, RUN_TYPE_ID, ACTION_STATUS, ACTION_TYPE.
• Dates: DATE_PAID (from PPA.EFFECTIVE_DATE) and DATE_EARNED.
• Earning balance: EARN_BAL_TYPE_ID, EARN_BAL_NAME, EARN_REPORTING_NAME, EARN_BAL_UOM.
• Days balance: DAYS_BAL_TYPE_ID, DAYS_BAL_NAME, DAYS_REPORTING_NAME. DAYS_BAL_NAME in particular is the user-searched column: it resolves the balance name of the derived "days" balance type obtained via PAY_MX_UTILITY.GET_DAYS_BAL_TYPE_ID(PBT.BALANCE_TYPE_ID) and then PAY_AC_UTILITY.GET_BALANCE_NAME(...).
• Run and YTD values: EARN_RUN_VAL, EARN_YTD_VAL, DAYS_RUN_VAL, DAYS_YTD_VAL, resolved by PAY_AC_UTILITY.GET_VALUE using the _ASG_GRE_RUN and _ASG_GRE_YTD dimension keys.

Common Use Cases and Queries

The primary scenario is a report or extract that lists each employee's earnings for a payroll run together with the number of days for which each earning was paid. A representative query filtering on DAYS_BAL_NAME follows:

SELECT e.assignment_id,
       e.tax_unit_id,
       e.date_paid,
       e.date_earned,
       e.earn_bal_name,
       e.earn_run_val,
       e.earn_ytd_val,
       e.days_bal_name,
       e.days_run_val,
       e.days_ytd_val
FROM   apps.pay_mx_employee_earnings_v e
WHERE  e.days_bal_name IS NOT NULL
AND    e.business_group_id = :p_business_group_id
ORDER  BY e.assignment_id, e.earn_bal_name;

Typical uses include Mexican statutory earnings reporting, validation that run and YTD values reconcile against PAY_RUN_BALANCES, and integration extracts feeding downstream systems that require both monetary and days balances for each earning.