Search Results transfer_reason_code

Overview

PER_JP_SI_DEPENDENT_TRANSFER_V is a read-only view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the PER (Human Resources) product family and is registered as VALID in the E-Business Suite Technical Reference Manual (ETRM). Its stated purpose is to supply the data set required by Japanese statutory reporting, specifically the Notification of Health Insurance Dependents and the National Pension Type 3 Insured report.

The view consolidates social-insurance dependent registration records and their transfer history for employees and their dependents. It exposes identifiers, Japanese Kanji name elements, dependent classification, transfer direction, transfer date, transfer reason, and a Type 3 disqualification notice indicator. Because it is a view rather than a table, it carries no data of its own; every row is derived at query time from the underlying HR and contact-relationship tables. It is intended for reporting and extract generation rather than for transactional processing, and it should not be used as a target for DML.

Underlying Base Objects

The documented base objects referenced by this view are:

• PER_ALL_PEOPLE_F (synonym) — person and assignment-level person records, providing business group, employee number, and the Kanji surname and given name held in descriptive flexfield attributes.
• PER_ALL_ASSIGNMENTS_F (synonym) — assignment records supplying ASSIGNMENT_ID.
• PER_CONTACT_RELATIONSHIPS (synonym) — the relationship between an employee and a contact (dependent), supplying PERSON_ID, CONTACT_PERSON_ID, and the relationship start and end dates.
• PER_CONTACT_EXTRA_INFO_F (synonym) — the dated extra information that carries the Japanese social-insurance attributes used to derive the transfer date and Type 3 disqualification notice.
• FND_DATE (package) — used to convert canonical date strings held in the extra-information flexfield into proper date values.
• DUAL (synonym) — a single-row source used within the inline construction.

Internally, the view inline-joins the contact relationship rows to the contact extra information rows, then joins out to the person and assignment tables. The ORDERED hint and explicit USE_NL and INDEX hints in the view text force a nested-loops execution path with specific indexes (PER_CONTACT_RELATIONSHIPS_N2, PER_PEOPLE_F_PK, PER_ASSIGNMENTS_F_N12), which is significant for performance tuning when the view is queried for large reporting populations.

Key Columns

• BUSINESS_GROUP_ID — the business group of the employee, used to scope the report to the correct legislative data group.
• PERSON_ID and CONTACT_PERSON_ID — the employee and the dependent party identifiers.
• EMPLOYEE_NUMBER — the employee's assigned number.
• LAST_NAME_KANJI and FIRST_NAME_KANJI — Kanji name values sourced from descriptor flexfield segments (PER_INFORMATION18 and PER_INFORMATION19).
• ASSIGNMENT_ID — the assignment against which the dependent is reported.
• DEPENDENT_TYPE — distinguishes the dependent category (for example spouse, dependent, or Type 3 insured).
• TRANSFER_TYPE — the direction of the social-insurance transfer, 'I' (in) or 'E' (out); this is the column most commonly searched for.
• TRANSFER_DATE — derived by a DECODE on TRANSFER_TYPE and DEPENDENT_TYPE; for inbound transfers it takes the flexfield effective date, and for outbound transfers it subtracts one day to close the qualifying period.
• TRANSFER_REASON_CODE and TRANSFER_REASON_DESCRPITION — the coded reason and its descriptive text for the transfer event.
• TYPE3_DISQUALIFIED_NOTICE — the flag indicating whether a Type 3 insured qualification notice applies.

Common Use Cases and Queries

The principal use case is extracting dependent transfer activity for a reporting period for the Japanese health insurance and national pension filings. A typical query filters by business group and transfer date range, and can be narrowed by TRANSFER_TYPE to isolate acquisitions or losses.

• Retrieve all inbound (acquisition) dependent transfers in a period:

SELECT person_id, employee_number, last_name_kanji, first_name_kanji, dependent_type, transfer_type, transfer_date, transfer_reason_code FROM apps.per_jp_si_dependent_transfer_v WHERE business_group_id = :p_bg_id AND transfer_type = 'I' AND transfer_date BETWEEN :p_start AND :p_end;

• Retrieve all outbound (loss) dependent transfers for the same window by changing the predicate to transfer_type = 'E'.
• Summarize transfer volumes by type and reason for reconciliation before submission:

SELECT transfer_type, transfer_reason_code, COUNT(*) FROM apps.per_jp_si_dependent_transfer_v WHERE business_group_id = :p_bg_id AND transfer_date BETWEEN :p_start AND :p_end GROUP BY transfer_type, transfer_reason_code;

• Identify records flagged for the Type 3 disqualification notice by filtering on the TYPE3_DISQUALIFIED_NOTICE column.

Because the view is defined with fixed access hints, ad hoc queries with additional, non-indexed predicates can drive the nested-loops plan unfavourably. Production extracts should restrict business group and date ranges and avoid wrapping the view in aggregation over an unrestricted row set.