Search Results pay_jp_pre_tax




Overview
Explore in AI Chat

Overview

PAY_JP_PRE_TAX is a read-only database view owned by the APPS schema in Oracle E-Business Suite. It is registered under the PAY (Payroll) product and carries a VALID status in the ETRM repository for releases 12.1.1 and 12.2.2. As its description states, the view contains Japanese legislative reports data, meaning it exposes the pre-tax and social insurance figures required by Japanese statutory reporting obligations — most notably the annual withholding tax statement (Gensen Choshu-hyo), the resident tax report, and social/labour insurance premium declarations.

Rather than storing data itself, the view projects and reformats values already captured by the Japanese payroll processes into action information records. It is therefore a reporting and integration object: it gives consumers a denormalised, column-labelled representation of the insurance and tax attributes that would otherwise appear as opaque, positionally-numbered segments. Because the values are decoded and renamed, the view is well suited to custom reports, extracts, and downstream interfaces that must not depend on the raw segment layout of the underlying storage.

Underlying Base Objects

The view is defined over two documented referenced objects:

  • PAY_ACTION_INFORMATION (SYNONYM) — the payroll action information store. The view text joins this object to itself, aliased PAI and PAI1, to bring together the current-period pre-tax row and its related previous-period row (used for the prior taxable amount). The ordered hint, the nested-loops join, and the reference to index PAY_ACTION_INFORMATION_N3 show that both scans are index-driven on the action information key.
  • FND_NUMBER (PACKAGE) — the EBS number conversion API. Every monetary and identifier value in the view is passed through FND_NUMBER.CANONICAL_TO_NUMBER, which converts the canonical (character) representation held in the action information columns into native numeric values.

Internally the view pins ACTION_ID to the literal 1 and ACTION_STATUS to 'C' (completed), fixes the row source identity via PAI.ROWID and the PRE_TAX_ID alias for ACTION_INFORMATION_ID, and derives ASSIGNMENT_ACTION_ID from ACTION_INFORMATION1 while retaining the raw value in ASSIGNMENT_ACTION_ID_PERF for performance/traceability. This design reveals that PAY_JP_PRE_TAX is a shaping layer over the Japanese pre-tax business process rather than an independent entity.

Key Columns

Common Use Cases and Queries

Typical consumers include custom Japanese statutory extracts, payroll reconciliation reports, and integration programs that push pre-tax and insurance values into a data warehouse or tax-filing file. A simple inspection query is:

  • SELECT pre_tax_id, assignment_action_id, salary_category, taxable_sal_amt, taxable_mat_amt FROM apps.pay_jp_pre_tax WHERE salary_category = :p_category;
  • SELECT assignment_action_id, hi_prem_ee, hi_prem_er, wp_prem_ee, wp_prem_er, wpf_prem_ee, wpf_prem_er FROM apps.pay_jp_pre_tax ORDER BY assignment_action_id;
  • SELECT assignment_action_id, ui_sal_amt, ui_prem_ee, itax_organization_id, itax_category FROM apps.pay_jp_pre_tax WHERE itax_category IS NOT NULL;

Because the view restricts results to action id 1 with a completed status and resolves all amounts through FND_NUMBER, queries can join directly to PAY_ASSIGNMENT_ACTIONS or PAY_PAYROLL_ACTIONS on ASSIGNMENT_ACTION_ID without further decoding. Note that the view exposes current and previous taxable totals but no period key, so period filtering is normally applied through the joined payroll action tables.