Search Results asg_gre_run




Overview
Explore in AI Chat

Overview

APPS.PAY_US_GTN_EARNINGS_V is a reporting view in Oracle E-Business Suite Release 12.1.1 and 12.2.2 that exposes United States gross-to-net (GTN) earnings data at the intersection of assignment actions, payroll actions, element types, earnings classifications, and balance types. Its central role is to feed statutory reporting and payroll reconciliation processes that require earnings amounts to be expressed in both monetary and time (hours) terms, keyed by assignment action and tax unit.

The view is specifically referenced by the internal view-mode identifier ASG_GRE_RUN, which appears as a hard-coded argument passed into the PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM function for both the cash and hours balance derivations. This identifier ties the view to a defined balance-view mode used by US payroll tax and gross-to-net reporting logic, meaning any consumer of this view is implicitly requesting the "ASG_GRE_RUN" balance presentation rather than a generic balance lookup.

Underlying Base Objects

The view is defined over the following documented objects, all owned (or synonymed) in the APPS schema:

  • PAY_ASSIGNMENT_ACTIONS (SYNONYM) — supplies assignment_action_id, assignment_id, action_status, tax_unit_id, and payroll_action_id.
  • PAY_PAYROLL_ACTIONS (SYNONYM) — supplies action_type, effective_date, and date_earned; only action types 'Q', 'R', 'V', 'B', and 'I' qualify (the 'B' and 'I' values were added per Bug 12637772).
  • PAY_ELEMENT_TYPES_F (SYNONYM) — supplies element attributes including reporting_name, processing_priority, classification, and the effective-date range that bounds the earnings record.
  • PAY_BALANCE_TYPES (SYNONYM) — joined twice (pbt for cash, pbt2 outer-joined for hours) via element_information10 and element_information12.
  • PAY_RUN_RESULTS (SYNONYM) — used in an EXISTS subquery to confirm the element was actually processed for the assignment action.
  • PAY_US_EARNINGS_TYPES_V (VIEW) — provides element_name, element_type_id, classification_name, and effective dates.
  • PAY_US_BALANCE_VIEW_PKG and PAY_US_TAXBAL_VIEW_PKG (PACKAGE) — supply view_mode, calc_all_timetypes, and the named-balance value functions.

Key Columns

  • tax_unit_id, assignment_id, assignment_action_id, payroll_action_id, action_status — identify the payroll run context and processing state of each row.
  • action_type, effective_date — from the payroll action, indicating the run type and timing.
  • element_name, element_type_id, classification_name, reporting_name, reporting_name_alt — describe the earnings or deduction element; reporting_name_alt falls back to element_name via NVL.
  • balance_name, balance_type_id, balance_uom — identify the balance being reported.
  • view_mode, calc_all_timetypes — runtime flags returned from PAY_US_BALANCE_VIEW_PKG, driven by the underlying balance definition.
  • cash_value, hours_value — the two principal measures, each computed through PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM using the 'ASG_GRE_RUN' view mode, the assignment action, date earned, tax unit, and business group.

Common Use Cases and Queries

The view is typically queried to reconcile US gross earnings to net pay, to verify that a given assignment action produced the expected element balances, or to audit hours versus cash amounts for statutory filings.

SELECT assignment_action_id,
       element_name,
       reporting_name_alt,
       cash_value,
       hours_value
  FROM apps.pay_us_gtn_earnings_v
 WHERE assignment_action_id = :p_assignment_action_id;

To compare all qualifying runs for one assignment:

SELECT v.assignment_id,
       v.action_type,
       v.effective_date,
       v.element_name,
       v.cash_value,
       v.hours_value
  FROM apps.pay_us_gtn_earnings_v v
 WHERE v.assignment_id = :p_assignment_id
   AND v.action_type IN ('Q','R','V','B','I')
 ORDER BY v.effective_date;

Because the view performs function-based balance lookups per row, performance-sensitive integrations should restrict by assignment_action_id or tax_unit_id rather than scanning by business group, and should avoid calling this view inside high-volume loops.