Search Results district_code




Overview
Explore in AI Chat

Overview

PAY_JP_SWOT_NUMBERS_V is an APPS-owned database view defined in the Oracle E-Business Suite Payroll (PAY) product, delivered specifically to support the Japanese localization. It exposes the contents of the SWOT numbers setup alongside resolved district address descriptions, providing a reporting and integration-friendly projection of Japan-specific statutory reporting configuration. The view is documented as VALID in both Oracle EBS 12.1.1 and 12.2.2 and carries no seeded rows of its own; it is a read-only query construct layered over two underlying entities. In practice, it is used by Japanese payroll and year-end adjustment (Nenmatsu Chousei) processing, and by external e-filing interfaces, to obtain the district codes, SWOT numbers, and file naming conventions required when producing magnetic or electronic media submissions to Japanese authorities. Because it resolves district codes to descriptive address-line text, it is considerably easier to consume in reports and extracts than the denormalized base table alone.

Underlying Base Objects

The view is defined over two documented synonyms in the APPS schema:

  • PAY_JP_SWOT_NUMBERS (aliased PJSN) — the primary base table holding SWOT numbers and the related district and file attributes.
  • PER_JP_ADDRESS_LOOKUPS (aliased PJAL1 and PJAL2) — the Japan address lookup table, joined twice to resolve the district code and the report district code into address-line descriptions.

The join is performed on the first five bytes of the district codes: the primary district code is matched with an inner join to PJAL1 (SUBSTRB(PJSN.DISTRICT_CODE,1,5) = PJAL1.DISTRICT_CODE), while the report district code is matched with an outer join to PJAL2 (SUBSTRB(PJSN.REPORT_DISTRICT_CODE,1,5) = PJAL2.DISTRICT_CODE(+)). The outer join ensures rows survive even when no report district address lookup exists. The view also derives a ROW_ID using ROWIDTOCHAR over the base table row identifier, giving consumers a stable row handle suitable for EBS-style forms or update-aware integrations.

Key Columns

  • ROW_ID — character representation of the base table ROWID.
  • ORGANIZATION_ID — the business group / organization that owns the SWOT number record.
  • DISTRICT_CODE — the Japanese tax office district code stored on the base table.
  • DISTRICT_NAME — the resolved ADDRESS_LINE_1 from PER_JP_ADDRESS_LOOKUPS for DISTRICT_CODE, appearing in the column list as the first address line of the matched lookup.
  • REPORT_DISTRICT_CODE — the district code used on the submitted report, which may differ from the primary district.
  • REPORT_DISTRICT_NAME — the resolved description for REPORT_DISTRICT_CODE (nullable due to the outer join).
  • SWOT_NUMBER — the statutory SWOT identifier assigned to the organization for that district.
  • EFILE_EXCLUSIVE_FLAG / OUTPUT_FILE_NAME — exclusive e-filing indicator and the output file name used when generating submission media.
  • IMPORT_EXCLUSIVE_FLAG / INPUT_FILE_NAME — corresponding import indicator and input file name for inbound e-filing data.
  • Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE.

Common Use Cases and Queries

Typical scenarios include year-end adjustment reporting, e-filing file generation, district validation, and setup verification for Japanese payroll implementations. A standard lookup by district code is:

  • SELECT organization_id, district_code, district_name, report_district_code, report_district_name, swot_number FROM apps.pay_jp_swot_numbers_v WHERE district_code = :district_code;
  • SELECT swot_number, output_file_name, efile_exclusive_flag FROM apps.pay_jp_swot_numbers_v WHERE organization_id = :org_id ORDER BY district_code;
  • SELECT district_code, district_name, swot_number FROM apps.pay_jp_swot_numbers_v WHERE report_district_code IS NOT NULL;

Because the view performs the lookup resolution internally, these queries avoid the need to re-implement the SUBSTRB join logic in every report or interface extract.