Search Results pay_jp_si_bon_payment_v




Overview
Explore in AI Chat

Overview

PAY_JP_SI_BON_PAYMENT_V is a read-only database view owned by the APPS schema within the Oracle E-Business Suite Payroll (PAY) module. In Oracle EBS 12.1.1 and 12.2.2, this view is delivered as part of the Japanese localization (JP) payroll functionality and is documented as VALID within the ETRM repository. Its purpose is to return the assignment action detail associated with a Bonus Payroll Process, filtered by business_group_id and the derived Social Insurance organization identifier (si_org_id).

From an implementation perspective, the view consolidates payroll action, assignment action, assignment, and run result information into a single denormalized result set. This makes it suitable for reporting and integration logic that must reconcile bonus payroll results with Social Insurance (SI) classification on a per-person, per-assignment basis. The view is not a base table and stores no data of its own; it derives all output at runtime from underlying payroll and HR entities.

The view is heavily optimized for its intended query pattern. Its definition embeds an ORDERED hint, NO_MERGE on the inline view, explicit AND_EQUAL and INDEX hints on the payroll actions, assignment actions, run results, run result values, and assignment primary keys. This indicates the view was tuned for large-volume Japanese payroll datasets in both 12.1.1 and 12.2.2 codelines.

Underlying Base Objects

The documented referenced objects that constitute this view are:

The dependency on PAY_JP_BALANCE_PKG and PAY_CORE_UTILS means the view relies on PL/SQL functions at execution time, which can influence performance and must be considered when troubleshooting.

Key Columns

The view exposes the following notable columns and their meaning:

  • PAYROLL_ACTION_ID — identifier of the parent bonus payroll action.
  • ASSIGNMENT_ACTION_ID — identifier of the assignment-level action within the payroll run.
  • ACTION_SEQUENCE — sequence of the action within the payroll action.
  • ASSIGNMENT_ID and PERSON_ID — the assignment and person to whom the bonus result applies.
  • BUSINESS_GROUP_ID — the business group that scopes the query.
  • EFFECTIVE_DATE — date on which the payroll action takes effect.
  • DATE_EARNED — the date the bonus is deemed earned.
  • SI_SUM_MTD_BON — value of the legislative parameter SI_SUM_MTD_BON as returned by PAY_CORE_UTILS.GET_PARAMETER.
  • SI_ORG_ID — Social Insurance organization value resolved through PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER.
  • HI_NUMBER and WP_NUMBER — the Health Insurance and Welfare Pension numbers resolved via GET_ENTRY_VALUE_CHAR.
  • SI_TYPE — aggregated sum of the SI type input value.

Common Use Cases and Queries

Typical scenarios include reconciling bonus payroll payments to Social Insurance records for Japanese reporting, extracting HI and WP numbers for downstream statutory filings, and auditing SI type classification per assignment.

A representative query filtered by business group and person:

  • SELECT payroll_action_id, assignment_action_id, person_id, assignment_id, effective_date, date_earned, si_org_id, hi_number, wp_number, si_type FROM apps.pay_jp_si_bon_payment_v WHERE business_group_id = :p_bg_id AND person_id = :p_person_id ORDER BY effective_date;

Because the view aggregates SI_TYPE and calls PL/SQL functions per row, queries should always be constrained by BUSINESS_GROUP_ID and, where possible, PERSON_ID or date ranges to avoid full scans and excessive function invocation costs.