Search Results jp_def_dep_os

Overview

APPS.PAY_JP_DEF_DEP_OS_V is a reporting and integration view in the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 Oracle Payroll (PAY) module, specific to the Japanese localization. It exposes a filtered, decoded projection of the dependent and beneficiary information associated with the JP_DEF_DEP_OS (Japan Defined Dependent / Other Source) payroll action information category. The view presents the data in a relational, application-consumable form rather than the canonical (character-encoded) storage format favored by Oracle Payroll’s action-information framework. Its presence allows external payroll processes, localization routines, and custom reporting to read Japanese payroll dependent records using standard SQL, without having to manually decode canonical date and number values.

This view is Oracle Proprietary and Confidential. It resides in the APPS schema, as documented in ETRM 12.2.2, and its owner is APPS.

Underlying Base Objects

The view is defined by a single SELECT statement executed over the base view PAY_JP_DEF_DEP_OS_DML_V, projected through a constant filter. It also references the FND_DATE and FND_NUMBER PL/SQL packages as documented base objects, which are used for type conversion within the projection.

• PAY_JP_DEF_DEP_OS_DML_V — The immediate source. It provides the DML-friendly records from which the reported columns are derived.
• FND_DATE (PACKAGE) — Used via FND_DATE.CANONICAL_TO_DATE to convert stored canonical character representations into proper DATE values for DATE_OF_BIRTH and DATE_OF_DEATH.
• FND_NUMBER (PACKAGE) — Used via FND_NUMBER.CANONICAL_TO_NUMBER to convert canonical character representations into numeric values for CONTACT_EXTRA_INFO_ID, CEI_OBJECT_VERSION_NUMBER, and CONTACT_RELATIONSHIP_ID.

Two predicate conditions are applied on the source: ACTION_CONTEXT_TYPE = 'AAP' and ACTION_INFORMATION_CATEGORY = 'JP_DEF_DEP_OS'. These constrain the view to a single well-defined category of Japanese dependent/beneficiary records.

Key Columns

• ACTION_INFORMATION_ID / ASSIGNMENT_ACTION_ID / ASSIGNMENT_ID — Core identifiers linking the record to the originating payroll action and assignment.
• ACTION_CONTEXT_TYPE / ACTION_INFORMATION_CATEGORY — Discriminator columns; fixed at 'AAP' and 'JP_DEF_DEP_OS' respectively within this view.
• EFFECTIVE_DATE / STATUS / ACTION_INFORMATION_CATEGORY — Date-effectiveness and lifecycle attributes of the action information row.
• CONTACT_EXTRA_INFO_ID / CEI_OBJECT_VERSION_NUMBER / CONTACT_RELATIONSHIP_ID — Canonically-encoded identifiers decoded to numbers via FND_NUMBER.CANONICAL_TO_NUMBER.
• LAST_NAME_KANA / FIRST_NAME_KANA / LAST_NAME / FIRST_NAME — Japanese dependent name fields, including Kana phonetic forms.
• CONTACT_TYPE / OCCUPATION / OCCUPATION_O — Classification data; the _O suffixed columns hold descriptive or override values.
• DATE_OF_BIRTH / DATE_OF_DEATH — Decoded to DATE via FND_DATE.CANONICAL_TO_DATE.
• OS_SALARY_PAYER_NAME / OS_SALARY_PAYER_ADDRESS and their _O counterparts — Other-source salary payer details, relevant to dependent income validation.
• LAST_UPDATE_LOGIN, LAST_UPDATED_BY, LAST_UPDATE_DATE, CREATED_BY, CREATION_DATE — Standard EBS audit columns.

Common Use Cases and Queries

The view is typically used for dependent eligibility review, other-source income reconciliation, and extract generation for reporting to Japanese authorities or downstream payroll systems. Because it decodes canonical values, it is well suited for ad-hoc SQL and integration extracts.

Example query to list dependents for a given assignment:

• SELECT assignment_id, last_name, first_name, last_name_kana, first_name_kana, date_of_birth, contact_type, os_salary_payer_name FROM apps.pay_jp_def_dep_os_v WHERE assignment_id = :p_assignment_id ORDER BY last_name_kana;

Example query to reconcile other-source salary payers:

• SELECT assignment_action_id, contact_extra_info_id, os_salary_payer_name, os_salary_payer_address FROM apps.pay_jp_def_dep_os_v WHERE os_salary_payer_name IS NOT NULL;

Example query across an effective-date range:

• SELECT * FROM apps.pay_jp_def_dep_os_v WHERE effective_date BETWEEN :p_start AND :p_end AND status = 'A';

In all cases the fixed context and category predicates ensure only JP_DEF_DEP_OS records are returned; callers need only supply assignment, action, or date-range filters.