Search Results qa_ss_lov_api




Overview
Explore in AI Chat

Overview

QA_SS_LOV_API is a PL/SQL package owned by APPS in the Oracle E-Business Suite, classified as an API. It belongs to the Oracle Quality (QA) product family and supports the collection plan and specification setup functionality known as "QA Self-Service" (the "SS" in the package name). The package provides a central repository of List of Values (LOV) SQL generation routines used by the Quality Self-Service user interface, together with helper functions that determine whether a given specification element defines a SQL validation rule or is associated with a particular collection plan.

The package header carries a version marker of 120.5 (2006), placing it well within the 12.1.1 and 12.2.2 code lines, where it remains an unmodified core API. In practice, QA_SS_LOV_API acts as a metadata helper layer: rather than hard-coding LOV queries into forms or JSP pages, the Quality Self-Service UI calls these procedures to obtain a dynamically constructed SQL string that drives a flexfield or LOV selection. The Boolean predicate functions allow callers to branch their validation logic based on whether an element requires SQL-based validation and whether the element is actually attached to a plan.

Key Procedures and Functions

The package exposes both predicate functions and SQL-producing procedures.

  • values_exist — Returns a Boolean indicating whether values have been defined for a given element within a specified plan.
  • sql_validation_exists — Returns a Boolean indicating whether a SQL validation string has been defined for a given element. This is the function that users encounter when searching the term "sql_validation_exists".
  • element_in_plan — Returns a Boolean confirming that an element belongs to a specified collection plan.
  • get_sql_validation_string — Returns the actual SQL validation string associated with an element, used when the caller needs to execute the validation rather than merely test for its presence.
  • get_department_lov, get_job_lov, get_work_order_lov, get_production_lov, get_resource_code_lov — Generate the LOV SQL for organizational and manufacturing entities, each keyed to an operating unit and an optional partial value.
  • get_supplier_lov, get_po_number_lov, get_po_release_number_lov — Generate LOV SQL for purchasing entities, including the release number LOV that was reworked under Bug 5003511.
  • get_customer_lov, get_so_number_lov, get_so_line_number_lov, get_rma_number_lov — Generate LOV SQL for order management and returns entities.
  • get_project_number_lov, get_task_number_lov — Generate LOV SQL for project and task references.
  • get_uom_lov, get_revision_lov — Generate LOV SQL for units of measure and item revisions.

Each LOV procedure returns its query statement through a NOCOPY OUT parameter so the caller can bind it to a dynamic LOV or flexfield validation.

Tables Accessed

The package reads from a small set of base tables, referenced through APPS synonyms:

  • MTL_SYSTEM_ITEMS — Source of item, revision, and unit-of-measure values for the item-related LOVs.
  • QA_CHARS — Quality characteristics (elements) master, used to resolve element definitions and validation attributes.
  • QA_PLAN_CHARS — Association of characteristics to collection plans, used by values_exist and element_in_plan to confirm element-to-plan relationships.
  • PLITBLM — The PL/SQL integer table type used internally for bulk collection handling within the package logic.

Usage Notes

QA_SS_LOV_API is invoked from the Oracle Quality Self-Service HTML and JSP flows, and is referenced by three other packages within the QA schema. Customers and partners typically call the LOV procedures when building custom Quality extensions that need to present consistent, validated selection lists for departments, jobs, suppliers, purchase orders, sales orders, projects, tasks, and item attributes. The predicate functions, particularly sql_validation_exists, are useful when authoring custom specification validation logic: a routine can test for the presence of a SQL validation string before attempting to fetch it via get_sql_validation_string. Because the package produces SQL text rather than executed results, callers are responsible for executing the returned statement within an appropriate security and operating-unit context; the org_id parameter passed to the organizational LOV procedures is the mechanism for restricting results to a single operating unit. The package should be treated as read-only with respect to the QA base tables and should not be modified, as it is a shipped Oracle object subject to standard EBS support policies.