Search Results iv_id_wp_comp_apply_typ

Overview

APPS.PAY_JP_GEPPEN_SANTEI_MT_V is a Japanese payroll reporting view within Oracle E-Business Suite 12.1.1 and 12.2.2. Its name combines "GEPPEN" (monthly payroll variation/adjustment) and "SANTEI" (determination or assessment), indicating it exposes data used to reconcile monthly payroll adjustments against the statutory determination amounts for health insurance (HI) and welfare pension (WP) contributions. The view joins payroll action, assignment action, element entry, run result, and element definition data to produce a horizontally structured dataset suitable for statutory reporting, balance verification, and downstream interface files consumed by Japanese social insurance processing.

The view carries a prominent hint block — ORDERED NO_MERGE(HRV) plus explicit index directives — reflecting an intentionally tuned execution plan for large payroll volumes. This design suggests the view is used operationally rather than for ad hoc low-volume querying.

Underlying Base Objects

The documented base objects are:

• PAY_PAYROLL_ACTIONS (SYNONYM) — parent payroll action header, providing date_earned, effective_date, and element_set_id.
• PAY_ASSIGNMENT_ACTIONS (SYNONYM) — assignment-level action line, supplying assignment_action_id.
• PAY_ELEMENT_ENTRIES_F (SYNONYM) and PAY_ELEMENT_ENTRY_VALUES_F (SYNONYM) — the entry and its stored input values.
• PAY_ELEMENT_SETS (SYNONYM) and PAY_ELEMENT_TYPES_F (SYNONYM) — element set and element type definitions, used to derive GEPPEN vs. SANTEI classification.
• PAY_INPUT_VALUES_F (SYNONYM) — input value definitions; the view resolves specific input value IDs (HI/WP compare apply month, apply type, numbers, organization).
• PAY_RUN_RESULTS (SYNONYM) and PAY_RUN_RESULT_VALUES (SYNONYM) — calculated payroll results.
• PER_ALL_ASSIGNMENTS_F (SYNONYM) — assignment master, supplying assignment_id and person_id.
• PER_BUSINESS_GROUPS_PERF (VIEW) — business group context via business_group_id.
• PAY_JP_BALANCE_PKG (PACKAGE) — Japan payroll balance API invoked for get_entry_value_number and get_entry_value_char.

The inner derived query (aliased HRV) seeds the input-value IDs by matching element set names 'GEP' and 'SAN' and decodes the SI_TYPE from the input value name: HI_LOCATION=1, WP_LOCATION=2, WPF_LOCATION=4.

Key Columns

• assignment_action_id — primary linkage to the assignment action.
• assignment_id, person_id — zero-added (+0) to prevent synonym-based predicate pushdown issues.
• business_group_id — legislative/business group scoping.
• date_earned, effective_date — payroll action dates used as the "as of" parameters for balance lookups.
• Decode of effective_date vs. element_set_id — returns 'G' for GEPPEN and 'S' for SANTEI, establishing the record classification.
• pay_jp_balance_pkg.get_entry_value_number(...) — the SI organization numeric value.
• Two substrb(...) columns over get_entry_value_char for HI_NUM and WP_NUM, truncated to 10 bytes.
• sum(hrv.si_type) — aggregated SI type indicator.

The apply_type the user searched for maps to the HI/WP "comp apply type" input values (IV_ID_HI_COMP_APPLY_TYP, IV_ID_WP_COMP_APPLY_TYP) resolved through the HRV inner query, used to distinguish which month/type the adjustment applies to.

Common Use Cases and Queries

Typical uses include verifying monthly GEPPEN adjustments against SANTEI determinations, generating interface data for social insurance submissions, and auditing contribution calculations per assignment.

SELECT assignment_id,
       person_id,
       date_earned,
       apply_type_indicator,
       si_organization_value
FROM   apps.pay_jp_geppen_santei_mt_v
WHERE  business_group_id = :p_bg_id
AND    date_earned BETWEEN :p_start AND :p_end
ORDER  BY date_earned, assignment_id;

Filtering by the G/S classification supports split reporting: WHERE decode(...) = 'G' isolates GEPPEN records, while 'S' isolates SANTEI. Because the view invokes PL/SQL package functions per row, performance is best when constrained by business_group_id, date_earned, and assignment_id to limit function invocations.