Search Results per_se_postalcode_upload




Overview
Explore in AI Chat

Overview

PER_SE_POSTALCODE_UPLOAD is a server-side PL/SQL package owned by the APPS schema in Oracle E-Business Suite. Its documented purpose is to support the bulk upload of Swedish postal code data into Oracle Human Resources reference data. The package reads a flat file containing postal code entries and loads them into the FND_LOOKUP_VALUES table, validating and transforming each entry as it is processed. This supports the Swedish localization of the Person/HR module, where postal code lookups are required for address entry and validation during employee and applicant registration.

The package is classified as OTHER in the ETRM metadata, indicating that it is not an officially supported or externally published API. It is best characterized as an internal data-loading utility whose lifecycle is governed by the localizations team rather than by the public HRMS API family. The header comment revision (120.0, dated 2005) indicates the package has remained stable across the 12.1.1 and 12.2.2 releases, with no documented functional change between them.

Key Procedures and Functions

  • IS_NUMBER — A helper function that evaluates whether a supplied value can be interpreted as a numeric string. It is used during record parsing to detect numeric fields within an uploaded postal code line.
  • READ_FILE — The main driver procedure. It accepts a filename and a business group identifier and coordinates the overall upload. It opens the specified file, iterates through its lines, and dispatches each line for processing. It returns a standard concurrent program completion status via the errbuf and retcode output parameters.
  • READ_RECORD — Parses a single line of input (p_line). This is the routine named in the user's search and represents the core record-level parsing step: it decomposes the delimited or fixed-format text line into its constituent lookup code, meaning, and description fields.
  • INSERT_ROW — Inserts a new row into FND_LOOKUP_VALUES for a postal code that does not already exist, given a lookup code, meaning, and description.
  • UPDATE_ROW — Updates the meaning and description of an existing FND_LOOKUP_VALUES record whose lookup code already exists, ensuring re-runs of the upload refresh rather than duplicate existing entries.

Tables Accessed

The package reads and writes FND_LOOKUP_VALUES, the core reference-data table that stores the Swedish postal code lookup entries inserted or updated by INSERT_ROW and UPDATE_ROW. It also references FND_LOOKUP_TYPES, which defines the lookup type under which the postal code values are grouped and determines the context in which they are visible. File handling is performed through the UTL_FILE PL/SQL built-in: the input file is opened, read line by line, and closed, with no database table used to stage the raw file content. The business group identifier passed to READ_FILE scopes the upload to the correct HR organization.

Usage Notes

PER_SE_POSTALCODE_UPLOAD is conventionally invoked as a concurrent program from the Oracle EBS System Administrator responsibility, with the input filename and business group supplied as parameters and the standard errbuf/retcode pair used to report completion to the concurrent manager. Because the procedures are declared in the package specification, they can also be called directly from custom PL/SQL, although such use is not officially supported. The referenced UTL_FILE directory must exist on the database server, be registered as an Oracle directory object, and be granted to the APPS schema, and the database initialization parameter UTL_FILE_DIR (or the directory object grant) must permit access to the file's location. The package is idempotent in intent: existing lookup codes are updated through UPDATE_ROW while new codes are inserted through INSERT_ROW, allowing the load to be re-run without creating duplicates. Administrators should back up FND_LOOKUP_VALUES rows for the relevant lookup type before execution, since the package performs direct DML rather than calling any public lookup API.