Search Results copy_lookup_values




Overview
Explore in AI Chat

Overview

APPS.PER_ZA_WSP_LOOKUP is a South Africa–specific Human Resources package that supports the Workplace Skills Plan (WSP) reporting process required under South African skills development legislation. The WSP requires employers to submit structured training and skills-development data to their Sector Education and Training Authority (SETA), and Oracle EBS delivers supporting functionality for this through the Oracle Training Administration (OTA) modules. This package provides the utility layer that seeds, maintains, and synchronises the FND lookup values that drive WSP-related validation lists, code values, and reporting structures.

Functionally, the package bridges Oracle HR organisation data and Oracle Learning Management activity, enrollment, and certification data with the seeded lookup values used by WSP forms and concurrent programs. It is classified under the ETRM "OTHER" API classification, meaning Oracle does not publish it as a supported public API for customer extension; it is intended primarily for internal use by Oracle-delivered WSP concurrent programs and forms. The package header carries the version $Header: perzawsp.pkh 120.1.12010000.1 2008/07/28, indicating a long-standing, stable component carried forward into EBS 12.1.1 and 12.2.2.

Key Procedures and Functions

  • WSP_LOOKUP_VALUES — A driver procedure that accepts a synchronisation flag, a reporting year, a processing mode, a from-year value, and a plan/training indicator. It coordinates the population of WSP lookup data for the specified period.
  • LOOKUP_VAL_INSERT_ROW — Inserts a single row into the lookup values structure, accepting the lookup type, lookup code, fifteen descriptive attribute columns, enabled flag, meaning, description, and start/end active dates. This is the low-level insert routine used by the higher-level procedures.
  • CREATE_LOOKUP_VALUES — The procedure the user searched for. It accepts the standard concurrent program OUT parameters (errbuf, retcode) plus a year, a plan/training indicator, and a delete-mode flag. It creates the WSP lookup values for the nominated year, with the delete-mode parameter controlling whether existing values are purged first. As a concurrent-program entry point, it returns status through errbuf and retcode.
  • REFRESH_LOOKUP_VALUES — Rebuilds or updates lookup values for a year and plan/training indicator without the delete-mode option, providing a synchronisation path when source data has changed.
  • COPY_LOOKUP_VALUES — Copies lookup values from a source year to a target year for the specified plan/training indicator, supporting year-on-year rollover of WSP configuration data.
  • COPY_PLAN_2_TRINING — Copies lookup values from a plan-year context to a training-year context, reflecting the distinction between planning and delivery periods in the WSP cycle.
  • VS_WSP_C_YR — A function returning a varchar2, taking a lookup code and lookup type. It is a validation/derivation helper, typically used to resolve the effective WSP year associated with a given lookup entry.

Tables Accessed

The package writes to and reads from FND_LOOKUP_VALUES, the core repository it maintains, through the APPS synonym. It queries HR_ALL_ORGANIZATION_UNITS and HR_ALL_ORGANIZATION_UNITS_TL together with HR_ORGANIZATION_INFORMATION to resolve the legal employer, business group, and organisation-level classification data that determines which WSP records apply to which organisation.

On the Oracle Learning Management side, the package references OTA_ACTIVITY_VERSIONS and OTA_ACTIVITY_VERSIONS_TL, OTA_EVENTS, OTA_LEARNING_PATHS and OTA_LEARNING_PATHS_TL, OTA_CERTIFICATIONS_B and OTA_CERTIFICATIONS_TL, OTA_CERT_ENROLLMENTS, OTA_LP_ENROLLMENTS, OTA_DELEGATE_BOOKINGS, and OTA_BOOKING_STATUS_TYPES. These are used to derive the training activity, enrollment, and certification categories that must appear as valid lookup codes in WSP reporting.

Usage Notes

CREATE_LOOKUP_VALUES, REFRESH_LOOKUP_VALUES, COPY_LOOKUP_VALUES, COPY_PLAN_2_TRINING, and WSP_LOOKUP_VALUES expose concurrent-program signatures (errbuf, retcode) and are normally registered as Oracle Concurrent Programs within the South African HRMS responsibility. Administrators submit them from the Submit Requests form, selecting the reporting year, plan/training indicator, and delete mode. CREATE_LOOKUP_VALUES is the standard initialisation step when beginning a new WSP cycle, and COPY_LOOKUP_VALUES is used to roll configuration forward from a prior year.

The package reports zero referencing packages in the ETRM metadata, confirming there is no downstream PL/SQL dependency within the documented API inventory. Custom code should not call these procedures directly; Oracle does not support PER_ZA_WSP_LOOKUP as a public API, and lookups created or modified outside the delivered flow risk inconsistency with the WSP extract. Where customers require additional WSP lookup entries, the supported route is to maintain them through the standard FND lookup maintenance forms under the appropriate lookup type, or by extending the WSP concurrent program output rather than patching this package.