Results for “qualified_date”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

PAY_JP_SI_DISQUALIFIED_MT_V is a PL/SQL view owned by the APPS schema in Oracle E-Business Suite, residing within the Payroll (PAY) product family. Its documented purpose is to supply data for the Japanese statutory SANTEI report when that report is produced as a magtape file. SANTEI (also written as "Santei") refers to the periodic Japanese social insurance reporting that establishes and adjusts an employee's standard monthly remuneration (SMR) and standard bonus amount, which are the bases on which health insurance and pension (Employee's Pension) contributions are calculated. The view isolates the "disqualified" record set — that is, the input values that record dates on which a qualifying or disqualifying status for insurance coverage takes effect.

The view is therefore best understood as a reporting extract rather than a transactional object. It exposes assignment-level and person-level identifiers alongside several dynamically resolved entry values, assembled by joining payroll element entry data to elements, assignments, and business group parameters. In EBS 12.1.1 the object is present and valid in the same form; in 12.2.2 the documented structure is unchanged and it continues to be referenced by the Japanese payroll statutory reporting process.

Underlying Base Objects

The view is defined over the following documented base objects:

• PER_ALL_ASSIGNMENTS_F (synonym) — provides ASSIGNMENT_ID and PERSON_ID, the primary grain of the report output.
• PAY_ELEMENT_ENTRIES_F (synonym) — the element entry header rows for the assignment, filtered to the relevant effective dates.
• PAY_ELEMENT_ENTRY_VALUES_F (synonym) — the individual input values attached to each entry; the view reads INPUT_VALUE_ID and SCREEN_ENTRY_VALUE and is driven by index PAY_ELEMENT_ENTRY_VALUES_F_N1.
• PER_BUSINESS_GROUPS_PERF (view) — supplies BUSINESS_GROUP_ID used to resolve input value IDs per legislative data group.
• HR_JP_ID_PKG (package) — resolves the internal input value IDs for the Japanese qualification and disqualification date information types (COM_HI_QUALIFY_INFO, COM_WP_QUALIFY_INFO, COM_WPF_QUALIFY_INFO and their corresponding disqualification information types).
• PAY_JP_BALANCE_PKG (package) — returns the entry value date, number, and character content for a given input value, assignment, and effective date.
• FND_DATE (package) — converts the canonical stored screen entry value into a DATE via CANONICAL_TO_DATE.

The join is driven by an inline business-group subquery that maps symbolic input value names to their numeric IDs, so all logic remains legislation-independent at the source level.

Key Columns

• ASSIGNMENT_ID / PERSON_ID — the assignment and person to which the SANTEI record pertains.
• BUSINESS_GROUP_ID — the legislative data group under which the input values were resolved.
• Effective date — computed as FND_DATE.CANONICAL_TO_DATE(SCREEN_ENTRY_VALUE) - 1, the day before the recorded qualification/disqualification date, used as the retrieval date for balance-package lookups.
• Qualification/Disqualification date — returned by PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_DATE using a DECODE that switches between the health insurance (HI), welfare pension (WP), and WPF variants.
• Organization number (first 10 bytes) — SUBSTRB of GET_ENTRY_VALUE_CHAR for the HI and WP organization number input values, truncated to 10 bytes for magtape fixed-width output.
• Record type flag — a SUM over a DECODE returning 1 for HI disqualification, 2 for WP disqualification, and 4 for WPF disqualification, allowing the caller to distinguish the insurance scheme.

Common Use Cases and Queries

The principal use case is generation of the SANTEI magtape submission, where the report program selects disqualification records for a business group and effective period. A typical query is:

SELECT assignment_id, person_id, business_group_id, hi_number, wp_number
FROM  apps.pay_jp_si_disqualified_mt_v
WHERE  business_group_id = :p_business_group_id;

A supplemental query for reconciliation of scheme flags:

SELECT assignment_id, record_type_flag, COUNT(*)
FROM  apps.pay_jp_si_disqualified_mt_v
GROUP BY assignment_id, record_type_flag;

Because the view invokes PL/SQL functions per row, large-volume extracts should be filtered tightly by business group and assignment to avoid full scans of PAY_ELEMENT_ENTRY_VALUES_F. The optimizer hints embedded in the view text (ORDERED, NO_MERGE, and explicit index directives) are intended to enforce this access path.