Search Results pay_jp_itax_withheld_v




Overview
Explore in AI Chat

Overview

The APPS.PAY_JP_ITAX_WITHHELD_V view is a payroll reporting object within the Oracle E-Business Suite PAY (Payroll) product, owned by the APPS schema. Its documented purpose is to support the Japanese statutory report known as GENSEN CHOSYU HYO (源泉徴収票), the withholding tax statement issued to employees. The view consolidates withholding income tax amounts, social insurance premiums, mutual aid contributions, and prior-year reference figures for each assignment, organized by business group, tax year, and the Japanese income tax organization to which the record belongs. It provides a denormalized, report-ready projection over Japanese payroll withholding data, allowing the statutory report to be generated without repeatedly joining the underlying transactional payroll tables. Because the view is registered as VALID and owned by APPS, it is viable for ad-hoc queries and reporting integrations, though its statutory scope means it should be treated as payroll-sensitive data.

Underlying Base Objects

The documented metadata lists eight referenced objects: FND_NUMBER (package), PAY_ACTION_INTERLOCKS, PAY_ALL_PAYROLLS_F, PAY_ASSIGNMENT_ACTIONS, PAY_JP_PRE_TAX (view), PAY_PAYROLL_ACTIONS, PER_ALL_ASSIGNMENTS_F, and PER_PERIODS_OF_SERVICE, most of which are accessed through synonyms. The view text shows the primary data source is a merged inline query resting on PAY_JP_PRE_ITAX_V2 and its underlying PAY_JP_PRE_ITAX_V1 construct, which itself derives from PAY_JP_PRE_TAX. This core is outer-joined to PER_PERIODS_OF_SERVICE, aliased PPS, using PERSON_ID-related keys; the period-of-service row supplies start date, actual termination date, and leaving reason, which the view conditionally returns only when the corresponding year matches the tax year. Assignment action, payroll action, assignment, and payroll context come from the PAY_ASSIGNMENT_ACTIONS, PAY_PAYROLL_ACTIONS, PER_ALL_ASSIGNMENTS_F, and PAY_ALL_PAYROLLS_F objects, while PAY_ACTION_INTERLOCKS supports action sequencing logic. FND_NUMBER is referenced as a utility package.

Key Columns

The view exposes the columns needed by GENSEN CHOSYU HYO. BUSINESS_GROUP_ID and ITAX_ORGANIZATION_ID identify the legislative and tax-registration context; ITAX_ORGANIZATION_ID is the column most frequently used to segregate withheld amounts by tax filing unit. YEAR is derived from the effective date via TO_NUMBER(TO_CHAR(...,'YYYY')). ASSIGNMENT_ID, PERSON_ID, ASSIGNMENT_ACTION_ID, and ACTION_SEQUENCE tie each row to a specific payroll action. SALARY_CATEGORY, ITAX_CATEGORY, ITAX_YEA_CATEGORY, and EXECUTIVE_FLAG classify the taxpayer and withholding treatment. Monetary columns include TAXABLE_AMT (taxable amount), ITAX (withheld income tax), SI_PREM (social insurance premium), and MUTUAL_AID, with the PREV_SWOT_ prefixes holding prior-year equivalents. The view also derives the period-of-service start date, actual termination date, and a truncated leaving reason when the period's year matches the tax year, and exposes LEGISLATIVE_PARAMETERS and ITAX_DPNT_REF_TYPE.

Common Use Cases and Queries

Typical usage covers statutory report generation, withholding reconciliation, and tax-unit auditing. The following query filters withheld tax by organization and tax year:

  • SELECT itax_organization_id, year, person_id, taxable_amt, itax, si_prem, mutual_aid FROM apps.pay_jp_itax_withheld_v WHERE itax_organization_id = :p_org_id AND year = :p_year ORDER BY person_id;
  • Reconciliation of tax withheld against prior-year figures: SELECT assignment_id, itax, prev_swot_itax, itax - NVL(prev_swot_itax,0) variance FROM apps.pay_jp_itax_withheld_v WHERE year = :p_year;
  • Termination-year reporting: SELECT person_id, itax, start_date, termination_date, leaving_reason FROM apps.pay_jp_itax_withheld_v WHERE year = :p_year AND termination_date IS NOT NULL;
  • Category-based aggregation: SELECT itax_category, itax_yea_category, SUM(itax) total_itax FROM apps.pay_jp_itax_withheld_v WHERE itax_organization_id = :p_org_id GROUP BY itax_category, itax_yea_category;

Because the view joins period-of-service and action data, queries should always constrain ITAX_ORGANIZATION_ID and YEAR to preserve performance and return correct statutory results.