Search Results pay_jp_isdf_spouse_dml_v




Overview
Explore in AI Chat

Overview

PAY_JP_ISDF_SPOUSE_DML_V is an APPS-owned database view within the Oracle E-Business Suite Payroll (PAY) product. Per the ETRM metadata, its documented purpose is narrow and explicit: it is used for Japanese localization only. In Oracle EBS release 12.1.1 and 12.2.2, the JP localization delivers a set of statutory forms and data extracts related to dependent and spousal information — most notably the spouse deduction (haigusha koujo) declarations and income reports used for Japanese tax withholding. This view supplies the spousal, dependent, and address attributes required by those processes in a denormalized, presentation-friendly column layout.

The object carries a VALID status in the APPS schema. Its name contains the token DML_V, which conventionally signals a view intended to support Data Manipulation Language operations — that is, insert, update, and delete against the underlying table through the view, rather than a purely read-only reporting projection.

Underlying Base Objects

According to the documented ETRM metadata, the view references a single base object: the synonym PAY_ACTION_INFORMATION. This synonym resolves to the core EBS table that stores generic, flexible action information lines associated with payroll actions. The product uses a key-flexfield style design: an ACTION_INFORMATION_CATEGORY discriminator identifies the context, while the numbered segments ACTION_INFORMATION1 through ACTION_INFORMATION10 hold the actual attribute values.

The view text documented in the ETRM excerpt projects the physical ROWID together with the identifiers (ACTION_INFORMATION_ID, OBJECT_VERSION_NUMBER, ACTION_CONTEXT_ID, ACTION_CONTEXT_TYPE, ASSIGNMENT_ID, EFFECTIVE_DATE), the category and segment columns, and the standard WHO audit columns (LAST_UPDATE_LOGIN, LAST_UPDATED_BY, LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE). The presence of ROWID in the select list is the mechanism that enables DML through the view: it allows the database to identify the exact base row for updates and deletes, provided the view is key-preserved with respect to the base table. The column listing additionally exposes derived/business-facing names such as STATUS, FULL_NAME_KANA, FULL_NAME, POSTAL_CODE, ADDRESS, EMP_INCOME, SPOUSE_TYPE, WIDOW_TYPE, SPOUSE_DCT_EXCLUDE, and SPOUSE_INCOME_ENTRY, which correspond to the JP spousal deduction attribute set.

Key Columns

  • ROW_ID / ROWID — Rowsource identifier enabling DML against the underlying PAY_ACTION_INFORMATION row.
  • ACTION_INFORMATION_ID — Primary key of the base information line.
  • OBJECT_VERSION_NUMBER — Optimistic locking column used by the EBS framework to detect concurrent updates.
  • ACTION_CONTEXT_ID / ACTION_CONTEXT_TYPE — Context identifying the payroll action the line belongs to.
  • ASSIGNMENT_ACTION_ID / ASSIGNMENT_ID — Links the information line to the assignment and its action, tying it to a specific employee.
  • EFFECTIVE_DATE — Date from which the spousal/dependent information applies.
  • ACTION_INFORMATION_CATEGORY — Discriminator identifying the JP spousal information context.
  • STATUS, FULL_NAME_KANA, FULL_NAME, POSTAL_CODE, ADDRESS — Spouse identity and address details, including the kana representation required on Japanese statutory forms.
  • EMP_INCOME — Employee income amount used in the deduction eligibility calculation.
  • SPOUSE_TYPE, WIDOW_TYPE, SPOUSE_DCT_EXCLUDE, SPOUSE_INCOME_ENTRY — Spousal deduction attributes covering spouse classification, widow status, exclusion flag, and income entry category.
  • WHO columns (LAST_UPDATE_LOGIN, LAST_UPDATED_BY, LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE) — Standard audit tracking.

Common Use Cases and Queries

The view is consumed by the Japanese localization processes that populate and maintain spousal deduction declarations. Functional users typically interact through the JP statutory form and deduction entry screens; technical users query the view directly for reconciliation, migration, and defect investigation. A typical read query is:

  • SELECT assignment_id, effective_date, spouse_type, spouse_income_entry, spouse_dct_exclude, full_name_kana, full_name, emp_income FROM apps.pay_jp_isdf_spouse_dml_v WHERE assignment_id = :p_assignment_id ORDER BY effective_date DESC;
  • SELECT status, COUNT(*) FROM apps.pay_jp_isdf_spouse_dml_v WHERE action_information_category = :p_category GROUP BY status; — used to reconcile declaration records before submission.
  • SELECT action_information_id, object_version_number FROM apps.pay_jp_isdf_spouse_dml_v WHERE action_context_id = :p_context_id; — used to retrieve identifiers and lock versions prior to an update.

Because the view retains ROWID and OBJECT_VERSION_NUMBER, it can also be used for controlled updates such as UPDATE apps.pay_jp_isdf_spouse_dml_v SET spouse_dct_exclude = 'Y', last_update_date = SYSDATE, last_updated_by = :user_id WHERE action_information_id = :id;. As this object is a localization component, direct DML should be reserved for supported scripts and corrective data fixes only, since the underlying rows are managed by the JP payroll processes and any inconsistency can affect statutory spouse-deduction reporting.