Search Results load_xml

Overview

OTFR2483 is an Oracle Applications (APPS) package body shipped as part of the Oracle Time and Labor / OTA (Oracle Training Administration) reporting infrastructure in Oracle E-Business Suite 12.1.1 and 12.2.2. Its header comment (otfr2483.pkb 120.9) identifies it as a report-support package associated with a specific concurrent program or report definition, numbered 2483. The package's central purpose is to assemble an XML document from HRMS and OTA data by querying lookup values, organization units, people, time periods, and training-plan tables, then emitting the result as a CLOB suitable for consumption by an XML publisher or similar reporting engine.

The prefix "otfr" indicates Oracle Training/Time reporting functionality. The package encapsulates two categories of work: XML generation utilities and the data-extraction logic for the 2483 report. The user search term act_hrs ("actual hours") reflects the package's reporting domain, which concerns measured or actual training/attendance hours rather than planned values.

Key Procedures and Functions

• GET_DIF_BALANCE — Returns a computed balance or difference value, presumably comparing actual hours against a plan or expected balance for the reporting period. It supports the numeric calculations that feed the XML output.
• BUILD_XML — The principal XML assembly routine. It constructs the output CLOB by invoking the internal loading helpers (trace_sql, load_xml, load_xml_declaration) to write the XML declaration, open and close nodes, and append attribute strings and query results.
• RUN_2483 — The driver entry point. It sequences the retrieval of source data and the XML build to produce the report payload, and is the procedure most likely referenced by a concurrent program.

Internal helpers visible in the source support these public routines: trace_sql logs executed SQL via HR_UTILITY.TRACE; the overloaded load_xml appends typed data or a formatted node to the CLOB using DBMS_LOB.WRITEAPPEND; and load_xml_declaration resolves the correct ISO character set from FND_LOOKUP_VALUES to emit a valid <?xml ... ?> prologue.

Tables Accessed

• FND_LOOKUP_VALUES — Resolves the ISO character-set map and other coded meanings used in the XML declaration and node text.
• OTA_TRAINING_PLANS, OTA_EVENTS_TL, OTA_TP_MEASUREMENT_TYPES — Supply training-plan, event, and measurement-type definitions that drive actual-hours reporting.
• HR_ALL_ORGANIZATION_UNITS, HR_ALL_ORGANIZATION_UNITS_TL, HR_ORGANIZATION_INFORMATION, HR_LOCATIONS_ALL — Provide organizational hierarchy and location context for the report.
• PER_ALL_PEOPLE_F, PER_TIME_PERIODS — Identify the persons reported on and the date-effective periods over which actual hours are accrued.
• PAY_ELEMENT_CLASSIFICATIONS — Links measurement values to payroll element classifications where hours are captured as element inputs.
• DBMS_LOB — Oracle-supplied package used for CLOB manipulation; not an APPS synonym but integral to output assembly.

Usage Notes

OTFR2483 is not referenced by any other documented package (referenced by 0), so it is a terminal, self-contained unit invoked directly rather than through an API chain. In practice it is called from a concurrent program registered against report 2483, from an XML Publisher data template, or from custom PL/SQL that needs the 2483 XML payload. Because its output is a CLOB, callers must supply a suitably sized LOB locator and are responsible for post-processing (file generation, template merging). The trace_sql helper means SQL can be captured in HRMS tracing when HR_UTILITY tracing is enabled. The userenv('LANG') and USERENV('LANGUAGE') references confirm that output is language-sensitive and requires a correctly initialized Applications session; direct SQL*Plus execution outside a fully initialized EBS session may fail on FND_GLOBAL or security-group resolution.