Search Results pay_jp_pre_itax_v




Overview
Explore in AI Chat

Overview

PAY_JP_PRE_ITAX_V is an APPS-owned database view within the Oracle E-Business Suite Payroll (PAY) product. It is a Japanese localization object, defined specifically to support the Japanese Tax Entry form. The view consolidates payroll action, assignment action, and Japanese pre-tax data into a single queryable structure, allowing the Tax Entry form to retrieve the assignment-level records that are eligible for itemized tax (ITAX) processing.

The defining characteristic of this view is its reliance on optimizer hints, most notably NO_MERGE. The NO_MERGE(PPT) directive prevents the optimizer from merging the inline PAY_JP_PRE_TAX query block into the outer query, preserving the intended execution plan and join order. The view also uses ORDERED to force the join sequence and explicit INDEX hints (PAY_ASSIGNMENT_ACTIONS_N50, PAY_JP_PRE_TAX_U1) to steer the access path. For users investigating a no_merge search, this view is a representative case of hint-driven SQL where query block boundaries are deliberately preserved to control cardinality and performance.

Underlying Base Objects

The documented referenced base objects are:

  • PAY_PAYROLL_ACTIONS (synonym) — the payroll action header, aliased PPA.
  • PAY_ASSIGNMENT_ACTIONS (synonym) — assignment-level action rows, aliased PAA.
  • PAY_JP_PRE_TAX (view) — the Japanese pre-tax view, aliased PPT.
  • PAY_ACTION_INTERLOCKS (synonym) — referenced within the nested anti-join logic.
  • FND_NUMBER (package) — a standard Oracle Application Object Library utility.

The view joins PAY_PAYROLL_ACTIONS to PAY_ASSIGNMENT_ACTIONS on PAYROLL_ACTION_ID, then to PAY_JP_PRE_TAX on ASSIGNMENT_ACTION_ID. Filtering restricts payroll action types to 'R', 'Q', 'B', and 'I', and requires completed action status ('C') at both the assignment action and pre-tax levels. A nested NOT EXISTS clause excludes future YEA assignment actions within the same calendar year, guarding against duplicate or premature year-end adjustment processing.

Key Columns

Common Use Cases and Queries

The view is primarily consumed by the Japanese Tax Entry form, but it is equally useful for diagnostic and reporting SQL that needs the same eligibility logic the form applies.

  • Auditing which assignments are pending itemized tax entry for a given period.
  • Verifying that future YEA actions are correctly excluded within the same tax year.
  • Reconciling ITAX organization and salary category combinations against payroll actions.

A typical query returns assignment-level tax entry candidates for a specific organization and year:

SELECT business_group_id,
       itax_organization_id,
       assignment_id,
       assignment_action_id,
       effective_date,
       date_earned,
       salary_category,
       itax_category
FROM   apps.pay_jp_pre_itax_v
WHERE  itax_organization_id = :p_org_id
AND    TO_CHAR(effective_date,'YYYY') = :p_year
ORDER BY assignment_id, action_sequence;

Because the definition embeds an anti-join against future YEA assignment actions, the result set already represents only the latest eligible action per assignment and year. Analysts tuning this view should preserve the NO_MERGE and index hints, since removing them may cause the optimizer to merge query blocks and alter the anti-join execution plan, degrading performance in high-volume Japanese payroll environments.