Results for “com_si_info”

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

Overview

The view APPS.PAY_JP_SI_QUALIFIED_V is a Japanese localization database object within Oracle E-Business Suite, delivered as part of the Oracle Payroll (PAY) schema. It identifies assignments that satisfy the Social Insurance (SI) qualification criteria established for Japanese statutory reporting, deriving qualification dates and reporting identifiers from element entry values captured against payroll element input values. The view is closely associated with the COM_SI_INFO input value group, which defines the location and identifier attributes used in the Social Insurance qualification determination. It also draws on the related input value groups COM_HI_QUALIFY_INFO, COM_WP_QUALIFY_INFO, and COM_WPF_QUALIFY_INFO, which supply the respective qualification dates for Health Insurance (HI), Welfare Pension (WP), and Welfare Pension Fund (WPF) coverage. In practice, the view serves as a reporting and integration layer that abstracts the complex join logic between payroll element entries, assignment records, and the input value identifiers resolved dynamically at runtime through HR_JP_ID_PKG. It is typically consumed by Japanese statutory reporting processes, extraction programs, and downstream integrations that require a consolidated picture of which employees are qualified for Social Insurance and under which scheme.

Underlying Base Objects

The view is defined over a combination of payroll and HR base objects, joined through a hint-directed query that forces an ordered, non-merged execution plan with specific index usage on PAY_ELEMENT_ENTRY_VALUES_F_N1, PAY_ELEMENT_ENTRIES_F_PK, and PER_ASSIGNMENTS_F_PK. The documented base objects are:

• PAY_ELEMENT_ENTRIES_F (synonym) — stores the element entry header rows for each assignment.
• PAY_ELEMENT_ENTRY_VALUES_F (synonym) — stores the individual input value rows, including the qualification date and location values central to the view.
• PER_ALL_ASSIGNMENTS_F (synonym) — supplies the assignment, person, business group, and period of service context.
• PER_BUSINESS_GROUPS_PERF (view) — used in the inline query to obtain the business group identifier for input value resolution.
• HR_JP_ID_PKG (package) — provides INPUT_VALUE_ID, which resolves the internal input value identifiers for the COM_SI_INFO, COM_HI_QUALIFY_INFO, COM_WP_QUALIFY_INFO, and COM_WPF_QUALIFY_INFO groups at runtime.
• PAY_JP_BALANCE_PKG (package) — provides GET_ENTRY_VALUE_NUMBER and GET_ENTRY_VALUE_CHAR, used to retrieve the numeric and character entry values associated with the resolved input value identifiers.
• FND_DATE (package) — provides CANONICAL_TO_DATE for converting the stored canonical date representation into a true date value.

Key Columns

The view exposes the following principal columns:

• ASSIGNMENT_ID — the assignment identifier taken from PER_ALL_ASSIGNMENTS_F.
• PERSON_ID — the person identifier associated with the assignment.
• BUSINESS_GROUP_ID — the business group context used to resolve input value identifiers.
• PERIOD_OF_SERVICE_ID — the period of service associated with the assignment.
• Qualification date — derived from PEEV.SCREEN_ENTRY_VALUE converted via FND_DATE.CANONICAL_TO_DATE.
• HI/WP/WPF location values — resolved from the COM_SI_INFO input value identifiers HI_LOCATION, WP_LOCATION, and WPF_LOCATION; the numeric identifier is obtained through PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_NUMBER using PEE.ASSIGNMENT_ID for performance.
• HI_NUMBER and WP_NUMBER — ten-character strings returned by PAY_JP_BALANCE_PKG.GET_ENTRY_VALUE_CHAR for the HI_NUMBER_IV_ID and WP_NUMBER_IV_ID identifiers.
• Qualification scheme flag — a summed DECODE that returns 1 for HI, 2 for WP, and 4 for WPF qualification date matches.
• Output flag — a single character from the COM_SI_REPORT_INFO input value group's OUTPUT_FLAG attribute, retrieved via HR_JP_ID_PKG.INPUT_VALUE_ID.

Common Use Cases and Queries

The view is typically queried to extract the set of assignments qualified for Japanese Social Insurance schemes, or to feed statutory reports and reconciliation extracts. A representative query retrieving qualification data for a business group is:

SELECT assignment_id, person_id, business_group_id, period_of_service_id FROM apps.pay_jp_si_qualified_v WHERE business_group_id = :p_business_group_id;

For report extraction, consumers filter on the qualification scheme flag and output flag to isolate rows destined for a specific scheme:

SELECT assignment_id, person_id FROM apps.pay_jp_si_qualified_v WHERE business_group_id = :p_business_group_id AND output_flag = 'Y';

Because the view invokes HR_JP_ID_PKG and PAY_JP_BALANCE_PKG functions, queries should be constrained by business group and assignment where possible to limit function execution overhead. The hint-directed execution plan and the use of PEE.ASSIGNMENT_ID in place of PA.ASSIGNMENT_ID indicate the view was tuned for performance on large element entry value sets. The view is a read-only reporting object; updates to qualification data must be performed against the underlying element entry records rather than through the view.