Search Results com_hi_smr_info

Overview

APPS.PAY_JP_HI_COMP_ENTRIES_V is a payroll element entry view in Oracle E-Business Suite (EBS) that surfaces element entry data for the Japanese "COM_HI_SMR_INFO" element type. It is part of the Oracle Payroll (PAY) product schema and is owned by the APPS database user. The view is specifically designed to present the input values associated with the COM_HI_SMR_INFO element in a horizontally pivoted format, where information stored across multiple input value rows is consolidated into a single row per element entry.

The view plays a targeted reporting and integration role: rather than exposing the generic, vertically structured element entry data found in the underlying tables, it decodes and reshapes the individual input values (identified by their display sequence) into discrete columns. This makes the view particularly suitable for extracting Japanese social insurance and labor insurance related statutory information for reporting, interfaces, or downstream processing. Because it enforces a session-level filter and a fixed element name, it functions as a specialized access point rather than a general-purpose element entry query.

Underlying Base Objects

The view is defined over six base objects, all referenced in the ETRM metadata for 12.2.2:

• FND_SESSIONS (SYNONYM) — used to enforce the session context via fnd.session_id = userenv('sessionid') and to bound the effective date range for the element type.
• PAY_ELEMENT_TYPES_F (SYNONYM) — supplies element type metadata, filtered to element_name = 'COM_HI_SMR_INFO'.
• PAY_ELEMENT_ENTRIES_F (SYNONYM) — the primary entry table, providing element_entry_id, assignment_id, version number, and audit columns.
• PAY_ELEMENT_LINKS_F (SYNONYM) — the link between the element type and the assignment/entry.
• PAY_ELEMENT_ENTRY_VALUES_F (SYNONYM) — stores the actual input values and screen entry values per entry.
• PAY_INPUT_VALUES_F (SYNONYM) — provides display_sequence and lookup_type for each input value, driving the pivot logic.
• HR_GENERAL (PACKAGE) — supplies the decode_lookup function used to translate coded values into their lookup meaning.

The view relies on the FND_SESSIONS effective date to validate the element type's effective start and end dates, ensuring only currently valid element definitions are processed. The join path is enforced through explicit index hints (FND_SESSIONS_U1, PAY_ELEMENT_TYPES_F_UK2, PAY_ELEMENT_ENTRIES_F_N50, PAY_ELEMENT_LINKS_F_PK, PAY_ELEMENT_ENTRY_VALUES_F_N50, PAY_INPUT_VALUES_F_PK) with an ORDERED directive, reflecting a performance-tuned design.

Key Columns

The view exposes the following notable columns:

• element_entry_id, effective_start_date, effective_end_date — identity and date range of the element entry.
• assignment_id — the assignment to which the entry belongs.
• element_link_id — links to the element link record.
• object_version_number — optimistic locking/versioning value.
• Input value identifier columns — six columns derived from decode(piv.display_sequence, 1..6, peev.input_value_id), capturing the input value IDs for display sequences 1 through 6.
• Screen entry value columns — the actual input values, transformed by data type: sequence 1 is a character substring (1–6), sequences 2 and 3 are numeric, sequence 4 is a character substring (1–30), sequence 5 and 6 are numeric.
• Decoded lookup column — sequence 4 is additionally rendered as an HR_GENERAL.decode_lookup value (substringed to 80 characters), providing the human-readable meaning of the coded value.
• Audit columns — creation_date, created_by, last_update_date, last_updated_by, last_update_login (aggregated with min(distinct ...)).

Common Use Cases and Queries

Typical scenarios include extracting Japanese statutory insurance entries for reporting, validating input values for an assignment, and feeding downstream interfaces with consolidated element entry data. A representative query is:

SELECT assignment_id,
       element_entry_id,
       effective_start_date,
       effective_end_date
FROM   apps.pay_jp_hi_comp_entries_v
WHERE  assignment_id = :p_assignment_id;

Because the view already pivots the input values into columns, consumers need not join to PAY_ELEMENT_ENTRY_VALUES_F or PAY_INPUT_VALUES_F themselves. Note that the view requires a valid FND session and only returns data for the COM_HI_SMR_INFO element, so it should be used narrowly rather than as a general element entry source.