Search Results me_subj_avg

Overview

PAY_KR_SEP_RESULT_SLIP_V is a PL/SQL-based view owned by the APPS schema in Oracle E-Business Suite Payroll (PAY). Its stated purpose, per the ETRM object metadata, is to return the result values that populate the Korean separation pay slip (severance pay statement) for a given payroll assignment action. The view is defined with the /*+RULE*/ hint, which forces the Cost Based Optimizer to use rule-based optimization semantics and prevents predicate pushing into the underlying function calls — an important characteristic because the view invokes PL/SQL functions that must execute for every row returned.

The view acts as a single-row-per-assignment-action projection of severance calculation results. Rather than querying the individual balance and result columns of the payroll run directly, it calls packaged report logic in PAY_KR_REPORT_PKG to resolve derived dates and monetary amounts. This makes it the semantic layer that Oracle's Korean localization reports and any downstream extract depend upon when producing separation pay documentation. It is part of the Korean legislation-specific payroll reporting set, and is delivered only where the KR localization is installed.

Underlying Base Objects

The view is defined over the following documented base objects:

• PAY_ASSIGNMENT_ACTIONS (synonym to the base table) — supplies ASSIGNMENT_ACTION_ID, ASSIGNMENT_ID and RUN_TYPE_ID; this is the driving table of the join.
• PAY_PAYROLL_ACTIONS (synonym) — supplies EFFECTIVE_DATE, DATE_EARNED and BUSINESS_GROUP_ID, which are passed to the package functions.
• PAY_ACTION_INTERLOCKS (synonym) — referenced in the view's join structure for assignment action context.
• PAY_RUN_TYPES_F (synonym) — the run type definition (base table of the run type view).
• PAY_KR_REPORT_PKG (package) — not a table, but the primary functional dependency. Almost every non-key column in the view is the return value of a call to GET_RESULT_VALUE_DATE, GET_RESULT_VALUE_CHAR, or GET_BALANCE_VALUE in this package.

Because the derived columns are function-based, the view has no simple column-to-table mapping; performance is governed by the argument values passed into the package, each ending in a _ASG_RUN balance dimension or a result-name lookup against the balance/result definitions registered for the assignment action.

Key Columns

• ASSIGNMENT_ACTION_ID — the primary filter key and the parameter passed to all package functions. Callers should always restrict on this column.
• ASSIGNMENT_ID — the assignment (employee work relationship) being processed.
• EFFECTIVE_DATE / DATE_EARNED — payroll action dates carried through from PAY_PAYROLL_ACTIONS.
• HIRING_DATE, LEAVING_DATE — returned by GET_RESULT_VALUE_DATE with the WKPD result group and result names H_DATE / L_DATE; the service period boundaries used in severance computation.
• WORKING_PERIOD — character result WKPD from the same result group.
• WKPD_SEP_PAY — severance pay balance value retrieved from balance WKPD_SEP_PAY under the _ASG_RUN dimension.
• ME_1ST_STD, ME_1ST_EDD — start and end dates of the first monthly average (Me criteria) period, resolved from result group ME_SUBJ_AVG, result names ME_1ST_STD and ME_1ST_EDD. These are the columns most commonly sought when searching on "ME_1st_std".
• ME_1ST_WKD, ME_1ST_SUBJ_AVG — the working days and subject-to-average amount for the first period.
• ME_2ND_STD / ME_2ND_EDD / ME_2ND_WKD / ME_2ND_SUBJ_AVG and the ME_3RD set — equivalent date, working-day and averaging values for the second and third averaging periods.

Common Use Cases and Queries

The principal use case is generating or auditing the Korean severance pay slip for a completed payroll action, and validating the three-month average (Me) calculation windows selected by the Korean payroll logic.

SELECT assignment_action_id,
       assignment_id,
       hiring_date,
       leaving_date,
       working_period,
       me_1st_std,
       me_1st_edd,
       me_1st_subj_avg,
       wkpd_sep_pay
  FROM apps.pay_kr_sep_result_slip_v
 WHERE assignment_action_id = :p_assignment_action_id;

To trace an action from the run side, join through PAY_ASSIGNMENT_ACTIONS on ASSIGNMENT_ID and RUN_TYPE_ID to locate the correct action before selecting from the view. Because of the /*+RULE*/ hint and the embedded function calls, queries should be point-filtered on ASSIGNMENT_ACTION_ID; unrestricted scans against large payroll histories execute the package functions row by row and degrade severely. When troubleshooting a missing or incorrect ME_1ST_STD value, confirm that the ME_SUBJ_AVG result group definition exists for the business group and that the assignment action has been successfully processed, as the function returns NULL when no matching result value has been written.