Search Results pay_jp_def_dep_os_v




Overview
Explore in AI Chat

Overview

PAY_JP_DEF_DEP_OS_V is an APPS-owned database view within the Oracle E-Business Suite Payroll (PAY) product module. It is documented as valid in ETRM for release 12.2.2 and applies equally to the 12.1.1 code line. As its description states, the view is used for Japanese localization only, and it exists to surface a jurisdiction-specific slice of assignment action information that the Japanese payroll and statutory reporting processes require.

The view presents dependent and other-source (OS) person data captured against an assignment action, including dependent names in both kanji and kana, relationship type, dates of birth and death, occupation, and details of a salary payer outside the employee's own household. Because Japanese payroll and tax reporting demand this level of dependent detail, the view acts as the reporting and integration interface for the corresponding DML layer, presenting data in a canonical, report-ready form rather than as raw denormalized storage.

Underlying Base Objects

The view derives its rows from a single underlying view, PAY_JP_DEF_DEP_OS_DML_V, and it references two Oracle Application Object Library conversion packages, FND_DATE and FND_NUMBER. The FND_DATE and FND_NUMBER packages are applied to specific columns to normalize stored values: date fields are passed through FND_DATE.CANONICAL_TO_DATE, and numeric identifier fields are passed through FND_NUMBER.CANONICAL_TO_NUMBER. This pattern indicates that the underlying DML view stores certain values in a canonical (character) form, and the conversion packages restore them to proper DATE and NUMBER data types for downstream consumers.

The WHERE clause restricts output to records where ACTION_CONTEXT_TYPE equals 'AAP' and ACTION_INFORMATION_CATEGORY equals 'JP_DEF_DEP_OS'. Consequently, only Japanese dependent and other-source action information is exposed. The view inherits the assignment action context (ASSIGNMENT_ACTION_ID, ASSIGNMENT_ID, EFFECTIVE_DATE) from the base view, tying each dependent record to the payroll action that produced or amended it.

Key Columns

  • DATE_OF_DEATH — the date of death of the dependent or other-source person, converted to a true DATE through FND_DATE.CANONICAL_TO_DATE. This is the column most directly relevant to the "date_of_death" search.
  • DATE_OF_BIRTH — the corresponding date of birth, converted in the same manner.
  • LAST_NAME_KANA / FIRST_NAME_KANA — dependent names recorded in kana, supporting Japanese phonetic matching and statutory forms.
  • LAST_NAME / FIRST_NAME — dependent names in standard script.
  • CONTACT_TYPE and CONTACT_RELATIONSHIP_ID — classify the dependent's relationship to the employee.
  • OCCUPATION / OCCUPATION_O — occupation and its override value for the dependent or other-source person.
  • OS_SALARY_PAYER_NAME / OS_SALARY_PAYER_ADDRESS plus the _O override columns — identify and locate an external salary payer, used where the dependent's support is provided by another party.
  • ASSIGNMENT_ACTION_ID / ASSIGNMENT_ID / EFFECTIVE_DATE — link the record to the payroll assignment action and its effective date.
  • STATUS, ACTION_INFORMATION_CATEGORY, ACTION_CONTEXT_TYPE — control and context attributes for the action information record.
  • Audit columns — CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN provide standard EBS who-audit tracking.

Common Use Cases and Queries

The view is typically queried for Japanese dependent registration reporting, statutory notification preparation, and validation of dependent records that carry a date of death. A targeted query for deceased dependents on an assignment would resemble:

  • SELECT assignment_id, last_name_kana, first_name_kana, date_of_birth, date_of_death FROM apps.pay_jp_def_dep_os_v WHERE date_of_death IS NOT NULL;
  • SELECT assignment_action_id, effective_date, contact_type, occupation FROM apps.pay_jp_def_dep_os_v WHERE assignment_id = :p_assignment_id ORDER BY effective_date;
  • SELECT contact_relationship_id, os_salary_payer_name, os_salary_payer_address FROM apps.pay_jp_def_dep_os_v WHERE contact_type = :p_contact_type;

Because the view already converts dates and numbers, consumers need not reapply FND_DATE or FND_NUMBER logic. Joins back to assignment and action tables for additional attributes remain the standard extension pattern.