Results for “spr_term_valid”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PAY_JP_TAX_SWOT_V is an APPS-owned database view within the Oracle E-Business Suite Payroll (PAY) product module. Per its ETRM documentation, the view exists solely to support the Japanese localization of Oracle Payroll. Its purpose is to expose withholding tax (income tax) statement-of-earnings data — the acronym "SWOT" in this context refers to the Japanese statutory withholding tax reporting construct rather than the business-strategy term — by joining payroll assignment action details to a person's registered addresses and deriving a set of tax-reporting attributes. The object carries status VALID in the documented 12.2.2 environment and is also referenced in 12.1.1 ETRM metadata, indicating continuity across the two release trains.

Because the view is a localization-specific reporting object, it is not part of the core, globally applicable payroll data model. It sits between the underlying payroll action data and downstream Japanese tax-reporting processes (such as withholding tax slips and municipal tax reporting), presenting a denormalized row for each qualifying assignment action combined with the current and residential addresses documented for the person. This design lets consumers retrieve tax-relevant facts without re-implementing the multi-table join logic themselves.

Underlying Base Objects

The view text is built primarily over PAY_JP_WIC_ASSACTS_V, a Japanese localization view that supplies assignment action and tax-category information (aliased PJWA). It then outer-joins the PER_ADDRESSES synonym twice — once for the residential address (address type 'JP_R', alias PADR) and once for the current address (address type 'JP_C', alias PADC) — keyed on PERSON_ID. Both joins are outer joins, so a person without a matching address type still yields a row, with the DECODE falling back to whichever address town or city is available. The date-range predicates use an effective-date window: the residential and current address records are matched over the range from DATE_FROM to DATE_TO (defaulting to 4712-12-31), with the upper bound derived from NVL(ACTUAL_TERMINATION_DATE, first day of the year following EFFECTIVE_DATE). The view also invokes the packaged function PAY_JP_WIC_PKG.SPR_TERM_VALID, which computes the SPR_TERM_VALID column, and the documented metadata lists FND_DATE and FND_NUMBER packages as referenced objects (consistent with the date and numeric conversions appearing in the select list). Collectively these dependencies mean the view cannot be queried meaningfully without the Japanese localization module installed and the supporting PER_ADDRESSES data present.

Key Columns

• BUSINESS_GROUP_ID — identifies the business group (legislative context) under which the payroll action was processed.
• YEAR — derived via TO_NUMBER(TO_CHAR(EFFECTIVE_DATE,'YYYY')), giving the tax year of the action.
• TAX_ORGANIZATION_ID — the withholding tax organization (ITAX_ORGANIZATION_ID) associated with the action.
• EFFECTIVE_DATE / DATE_EARNED — the effective date of the assignment action and the date the earnings were earned.
• ASSIGNMENT_ACTION_ID, PERSON_ID, PERIOD_OF_SERVICE_ID — keys linking the row to the payroll action, the person, and the period of service.
• TAX_CATEGORY / TAX_YEA_CATEGORY — Japanese income tax categories (ITAX_CATEGORY, ITAX_YEA_CATEGORY) used to classify withholding status.
• TOWN_OR_CITY — the person's town or city, taken from the residential address when present, otherwise from the current address.
• ACTUAL_TERMINATION_DATE — the person's actual termination date, also used to bound the address-match window.
• SPR_TERM_VALID — a computed validity indicator returned by PAY_JP_WIC_PKG.SPR_TERM_VALID.

Common Use Cases and Queries

Typical consumers are Japanese localization reports, withholding tax extracts, and reconciliation queries that need a person's tax category alongside address and termination context. A representative query selects the tax year and category for a person:

SELECT person_id, year, tax_category, town_or_city, spr_term_valid
  FROM apps.pay_jp_tax_swot_v
 WHERE person_id = :p_person_id
   AND year = :p_year;

For reconciliation, users often filter by assignment action or business group:

SELECT assignment_action_id, effective_date, tax_organization_id
  FROM apps.pay_jp_tax_swot_v
 WHERE business_group_id = :p_bg_id
   AND effective_date BETWEEN :p_from AND :p_to;

Because the view performs outer joins and calls a packaged function per row, queries should be restricted by PERSON_ID, BUSINESS_GROUP_ID, or date range wherever possible to avoid full-scan overhead. Reported output should be validated against the underlying PAY_JP_WIC_ASSACTS_V rows, since values such as SPR_TERM_VALID are computed and depend on the current state of the PAY_JP_WIC_PKG logic rather than being stored.