Search Results get_value_from_id

Overview

APPS.HR_H2PI_DOWNLOAD is a PL/SQL package defined with AUTHID CURRENT_USER that supports the Oracle EBS Human Resources "HR to Payroll Interface" (H2PI) data extraction process. Its principal responsibility is to assemble and emit a large, hierarchically structured payload — expressed as a CLOB, typically XML produced through DBMS_XMLQUERY — representing organization information records that are transferred to an external payroll or third-party system. The package encapsulates the logic required to write the generated document to an output destination, orchestrate the download based on a business group and an effective transfer window, and resolve individual descriptive flexfield values from stored organization information identifiers. Because it is classified as OTHER in the ETRM repository, it is not a certified public API; it is an internal utility invoked by the H2PI concurrent process and, as noted, is referenced by one additional package. The AUTHID CURRENT_USER clause means the package executes with the privileges of the invoking session, so callers must already hold the necessary grants on the underlying HR tables.

Key Procedures and Functions

• WRITE — Accepts the standard concurrent program error buffer and return code outputs together with a CLOB payload, and writes that CLOB out to the designated destination. It serves as the final emission step of the download.
• DOWNLOAD — The main driver procedure. Using the standard concurrent parameter outputs plus a business group identifier and a transfer start and end date, it selects the applicable organization information records and initiates the download for the identified client. This is the entry point executed by the concurrent program.
• GET_REQUEST_ID — Returns the current concurrent request identifier as a number. It is used for traceability and for naming or correlating output produced by the download.
• GET_VALUE_FROM_ID — A function returning VARCHAR2. Given an organization information identifier and an organization information number, it resolves and returns the corresponding descriptive flexfield value. This is the documented object matching the search term get_value_from_id, and it centralizes the flexfield value lookup so the download logic does not embed that resolution directly.

Tables Accessed

The package reads from a set of HR organization and flexfield metadata tables, all reached through APPS synonyms:

• HR_ORGANIZATION_INFORMATION — the primary source of organization information rows, including descriptive flexfield segments, filtered by business group and date range.
• HR_ALL_ORGANIZATION_UNITS — supplies organization unit identity and attributes joined to the information rows.
• HR_ORG_INFORMATION_TYPES, HR_ORG_INFO_TYPES_BY_CLASS — define the valid organization information types and their class groupings used to select and label records.
• FND_DESCR_FLEX_COLUMN_USAGES — supplies descriptive flexfield segment metadata required to interpret segment values returned by GET_VALUE_FROM_ID.
• HR_H2PI_DATA_FEED_HIST and HR_H2PI_DATA_FEED_HIST_S — the H2PI data feed history and its sequence, used to record and track each extraction run.
• DUAL — used for singleton evaluations and date or sequence checks.

Programmatic dependencies include DBMS_LOB for CLOB manipulation and DBMS_XMLQUERY for constructing the XML document.

Usage Notes

This package is invoked indirectly through the HR H2PI download concurrent program rather than by end users, and it writes its results to a CLOB via WRITE for subsequent transfer to the external client system. In Oracle EBS 12.1.1 and 12.2.2, calls are typically made from concurrent program registration or from custom code that supplies the business group, transfer date range, and client identifier to DOWNLOAD. The GET_VALUE_FROM_ID function may be called independently by custom routines needing to resolve flexfield values from organization information identifiers.