Results for “apply_type”

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

Overview

PAY_JP_GEPPEN_SANTEI_MT_V is an APPS-owned view in the Oracle E-Business Suite Payroll (PAY) module, classified as VALID and documented in ETRM for release 12.2.2. It exists to support the Japanese statutory report known as GEPPEN SANTEI (monthly base amount / monthly standard remuneration determination) and, more specifically, to generate the magtape file required for that report. Under Japanese payroll legislation, employers must report monthly standard remuneration figures to the relevant authorities, and the magtape variant of the report demands a denormalized, fixed-format-ready result set. The view supplies exactly that: a consolidated projection of assignment-level payroll results, element entry values, and derived input values, keyed by date earned, effective date, and assignment action so that the report generator can write the required tape records in sequence.

Underlying Base Objects

The view is defined over a select-list of documented base objects, resolved in the APPS schema. The core payroll run and result stack is represented by PAY_PAYROLL_ACTIONS, PAY_ASSIGNMENT_ACTIONS, PAY_RUN_RESULTS, PAY_RUN_RESULT_VALUES, PAY_ELEMENT_ENTRIES_F, and PAY_ELEMENT_ENTRY_VALUES_F. The assignment and person dimension comes from PER_ALL_ASSIGNMENTS_F. Configuration of the Japanese element sets and their input values is drawn from PAY_ELEMENT_SETS, PAY_ELEMENT_TYPES_F, and PAY_INPUT_VALUES_F, while the business group context is derived from PER_BUSINESS_GROUPS_PERF. A scalar calculation component is provided by the PAY_JP_BALANCE_PKG package, invoked through its GET_ENTRY_VALUE_NUMBER and GET_ENTRY_VALUE_CHAR functions for the social insurance organization identifier, the remuneration number, and related values.

The view text carries explicit optimizer hints, applying ORDERED plus NO_MERGE on the business group subquery and forcing index access paths on PAY_PAYROLL_ACTIONS, PAY_ASSIGNMENT_ACTIONS, PAY_ELEMENT_ENTRIES_F, PAY_ELEMENT_ENTRY_VALUES_F, and PER_ASSIGNMENTS_F. This reflects the expectation of a narrow, heavily filtered row set driven by the report's business group and effective-date selection criteria.

Key Columns

• ASSIGNMENT_ACTION_ID — Identifier of the assignment action that produced the result row, linking back to the payroll run processing unit.
• ASSIGNMENT_ID, PERSON_ID — Assignment and person identifiers, exposed with an explicit numeric coercion (plus zero) for the magtape layout.
• BUSINESS_GROUP_ID — Business group on which the report run is scoped.
• DATE_EARNED, EFFECTIVE_DATE — Date earned from the payroll action and effective date used for date-effective joins against assignment and element entry data.
• Element set indicator — A DECODE over ELEMENT_SET_ID comparing the payroll action element set to the GEPPEN element set, returning 'G' for GEPPEN or 'S' for SANTEI, and thus tagging each row by statutory category.
• Social insurance organization value — Returned by PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER for the SANTEI/insurance organization input value.
• Health insurance number and welfare pension number — Character values retrieved via GET_ENTRY_VALUE_CHAR and truncated with SUBSTRB to ten bytes to fit the fixed magtape field widths.
• Aggregate type sum — SUM over a SI_TYPE derived from the input value name (HI_LOCATION, WP_LOCATION, WPF_LOCATION mapping to 1, 2, and 4), which classifies and totals the insurance location components.

Common Use Cases and Queries

The principal use case is the GEPPEN SANTEI magtape submission. Report authors and support analysts also query the view to validate that element set configuration, input value names such as HI_COMP_APPLY_MTH and WP_COMP_APPLY_MTH, and payroll results reconcile before the tape is produced. The apply_mth lookup relevant to this view concerns the HI_COMP_APPLY_MTH and WP_COMP_APPLY_MTH input values defined on the HI and WP compensation elements, which govern the month to which the monthly remuneration components apply; these are resolved within the inline element set and input value subquery that the outer query joins to.

A representative query pattern filters by business group and effective date range, then aggregates by assignment:

• SELECT assignment_id, person_id, business_group_id, date_earned, effective_date
• FROM apps.pay_jp_geppen_santei_mt_v
• WHERE business_group_id = :p_business_group_id
• AND effective_date BETWEEN :p_start AND :p_end
• ORDER BY assignment_id, effective_date;

Because the view performs scalar function calls into PAY_JP_BALANCE_PKG per row and depends on date-effective joins, queries should always supply business group and date predicates to avoid full scans of the underlying payroll result tables. Validation reporting typically selects the element set indicator and SI_TYPE aggregate to confirm that both 'G' and 'S' rows are produced for each eligible assignment before the magtape file is generated.