Search Results pay_jp_ui_v




Overview
Explore in AI Chat

Overview

PAY_JP_UI_V is a PL/SQL view owned by the APPS schema in Oracle E-Business Suite (validated in releases 12.1.1 and 12.2.2). It belongs to the PAY – Payroll product family and is defined specifically to support the Japanese statutory report known as the Unemployment Insurance Check List, internally identified as PAYJPRUI. Its role is to assemble the input values, assignment identifiers, and qualifying dates required to produce that statutory extract, joining payroll element entry data against assignment and business group definitions and resolving Japanese-specific input value identifiers through HR_JP_ID_PKG.

Users searching for the per_assignments_f_pk index will encounter this view because the view's SQL text carries an explicit optimizer hint targeting PER_ASSIGNMENTS_F with that primary key index, reflecting the view's heavy dependence on PER_ALL_ASSIGNMENTS_F.

Underlying Base Objects

The documented base objects for PAY_JP_UI_V span several payroll, HR, and date-handling components:

The view's inline subquery groups PER_BUSINESS_GROUPS_PERF to derive INPUT_VALUE_ID1, INPUT_VALUE_ID2, and INPUT_VALUE_ID6 for the qualifying date, qualifying change type, and EI location respectively.

Key Columns

  • ASSIGNMENT_ID – assignment identifier from PER_ALL_ASSIGNMENTS_F; the anchor for downstream reporting.
  • PERSON_ID – person identifier associated with the assignment.
  • BUSINESS_GROUP_ID – business group context used to scope the statutory report.
  • Qualifying date columns – derived through FND_DATE.CANONICAL_TO_DATE applied to PEEV.SCREEN_ENTRY_VALUE, bounded by element entry value effective dates.
  • Qualify change type – a SUBSTRB extract of PEEV2.SCREEN_ENTRY_VALUE, capturing the change classification.
  • Numeric entry value – returned by PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER for INPUT_VALUE_ID6 (EI_LOCATION), correlated to the assignment.

Common Use Cases and Queries

The view is consumed directly by the PAYJPRUI Unemployment Insurance Check List report. Technical users frequently query it for reconciliation, validation, and extract troubleshooting of Japanese unemployment insurance eligibility data.

A typical query retrieves assignment and qualifying information for a business group:

  • SELECT assignment_id, person_id, business_group_id FROM apps.pay_jp_ui_v WHERE business_group_id = :p_bg_id;
  • Joining back to PER_ALL_ASSIGNMENTS_F to constrain by effective dates or payroll, since the view already filters to primary assignments via PER_ASSIGNMENTS_F_PK access.
  • Correlating PERSON_ID to PER_ALL_PEOPLE_F for employee name and number in reconciliation reports.

Because the view embeds ORDERED and NO_MERGE hints plus explicit index hints on PAY_ELEMENT_ENTRY_VALUES_F_N1, PAY_ELEMENT_ENTRIES_F_PK, PAY_ELEMENT_ENTRY_VALUES_F_N50, and PER_ASSIGNMENTS_F_PK, execution plans are fixed in the view text; ad-hoc queries should respect these access paths when analyzing performance.