Search Results pay_element_entry_values_f_n50




Overview
Explore in AI Chat

Overview

APPS.PAY_JP_GEPPEN_SANTEI_V is a Japanese localization reporting view in Oracle E-Business Suite, defined in the APPS schema. Its name derives from the Japanese payroll concept of geppen santei (monthly assessment/recalculation), which refers to the periodic determination of an employee's social insurance and labour insurance contribution basis. The view consolidates monthly assessment data drawn from payroll actions, assignment actions, and element entry values into a single denormalized result set suitable for statutory reporting extracts and downstream payroll interfaces.

The view is not a general-purpose master view; it is purpose-built for generating insurer-facing assessment output for Japanese payroll. It exposes one row per assignment action with aggregated categorization of the insurance type, the applicable monthly period, the organization to which the assessed employee belongs, and administrative identifiers such as the health insurance number and welfare pension number.

Underlying Base Objects

The view is defined over the following documented base objects:

  • PAY_PAYROLL_ACTIONS — provides the payroll action context, including business group, payroll, consolidation set, date earned, effective date, and element set.
  • PAY_ASSIGNMENT_ACTIONS — links the payroll action to the specific assignment being processed.
  • PAY_ELEMENT_ENTRIES_F — supplies the element entry rows holding the assessment inputs.
  • PAY_ELEMENT_ENTRY_VALUES_F — referenced multiple times to obtain the applied month, the applied category, the organization identifier, and the health insurance and welfare pension numbers.
  • PAY_LINK_INPUT_VALUES_F — used to resolve the input values attached to the link definition for the relevant element entries.
  • PAY_JP_REPORT_PKG — returns the seeded input value identifiers used to identify the insurance type and the assessment category.
  • PAY_JP_BALANCE_PKG — used to derive the output flag from accumulated balance values.
  • DUAL — used to construct an inline union of organization input value identifiers.

The view text confirms a deliberately tuned join strategy. Ordered and nested loops hints are supplied with explicit index directives, including repeated use of the PAY_ELEMENT_ENTRY_VALUES_F_N50 index. Because the query touches the entry values table several times for different semantic purposes, each access path is pinned to a specific index to keep the plan stable for large payroll runs.

Key Columns

  • assignment_action_id, assignment_id — identify the processed assignment.
  • business_group_id, payroll_id, consolidation_set_id — the payroll processing context.
  • date_earned, effective_date — the earning and effective dates of the action, used to determine the assessment month.
  • applied_category — the assessment category, defaulted to 'O' when no screen entry value exists.
  • application month and organization identifiers — applied_month returns the month being assessed, while organization_id returns the insurer organization, taking the maximum numeric value and defaulting to -1 when absent.
  • si_type — a computed social insurance type. It decodes the organization input value into 1 for health insurance, 2 for welfare pension, or 4 for welfare pension fund, based on identifiers returned by PAY_JP_REPORT_PKG.
  • hi_number, wp_number — truncated health insurance and welfare pension numbers for the employee.
  • output_flag — derived from the balance package, indicating whether the assessment is to be output.

Common Use Cases and Queries

The primary use case is producing insurer submission files and reconciliation reports for monthly assessment processing. Typical queries filter by business group and effective date range:

SELECT assignment_id, applied_month, si_type, organization_id, hi_number, wp_number, output_flag FROM apps.pay_jp_geppen_santei_v WHERE business_group_id = :p_bg AND effective_date BETWEEN :p_start AND :p_end;

A second pattern aggregates by insurance type to verify counts and organization distribution before submission:

SELECT si_type, organization_id, COUNT(*) FROM apps.pay_jp_geppen_santei_v WHERE business_group_id = :p_bg AND applied_month = :p_month GROUP BY si_type, organization_id;

Because the view is not indexable directly and performs nested loops over element entry values, queries should always be constrained by business group and a bounded date or month range. Callers should also be aware that the view depends on package functions that resolve seeded input value identifiers, so any change to payroll definitions or the Japanese localization package configuration can affect the returned rows.