Search Results values_exist

Overview

QA_SS_LOV_API is an Oracle EBS Application Programming Interface (API) package owned by the APPS schema in the Oracle Quality Management module. Its purpose is to supply list-of-values (LOV) queries and supporting validation logic used throughout the Oracle Quality setup and data-collection user interfaces. In Oracle EBS 12.1.1 and 12.2.2, Quality collection plans rely on collection elements, and the values selectable for those elements are frequently derived from external sources such as inventory items, departments, jobs, suppliers, customers, sales orders, and purchase orders. Rather than hard-coding each LOV query into the forms, Oracle Quality centralizes the LOV SQL generation inside QA_SS_LOV_API. The package exposes a public record type, LovRecord (containing code and description), and a LovRefCursor ref cursor type, establishing the standard contract for returning LOV rows to callers. Callers use values_exist to confirm that a given element in a plan is populated with valid values, and they use the various get_*_lov procedures to obtain dynamic SQL text that a form can open as a dynamic cursor.

Key Procedures and Functions

The package documents 71 procedures and functions. The validation functions include VALUES_EXIST, which verifies that values exist for a specified plan and element combination; SQL_VALIDATION_EXISTS, which indicates whether SQL-based validation is defined for an element; ELEMENT_IN_PLAN, which confirms an element belongs to a plan; and GET_SQL_VALIDATION_STRING, which returns the validation SQL text for an element.

The LOV generation procedures build and return SQL statements via an OUT NOCOPY parameter. By business entity they include: GET_DEPARTMENT_LOV, GET_JOB_LOV, GET_WORK_ORDER_LOV, GET_PRODUCTION_LOV, GET_RESOURCE_CODE_LOV, GET_SUPPLIER_LOV, GET_PO_NUMBER_LOV, GET_CUSTOMER_LOV, GET_SO_NUMBER_LOV, GET_SO_LINE_NUMBER_LOV, GET_PO_RELEASE_NUMBER_LOV, GET_PROJECT_NUMBER_LOV, GET_TASK_NUMBER_LOV, GET_RMA_NUMBER_LOV, GET_UOM_LOV, and GET_REVISION_LOV. Several take an org_id and a value parameter to scope the query to the operating unit and a partial entry; others accept only value. The PO release number procedure additionally accepts a plan identifier and PO header identifier, reflecting the dependency noted in the package header (Bug 5003511), where the release number LOV depends on the selected PO number.

Tables Accessed

The package reads from four documented base objects through APPS synonyms. MTL_SYSTEM_ITEMS supplies item-related LOV values, supporting item, revision, and production selections. QA_CHARS and QA_PLAN_CHARS are the Quality character and plan-character tables used to validate element and plan relationships, supporting VALUES_EXIST and ELEMENT_IN_PLAN. PLITBLM is a seeded Oracle Foundation table used in dynamic SQL construction patterns.

Usage Notes

QA_SS_LOV_API is invoked from Oracle Quality forms and concurrent programs, and it is referenced by three other packages. It is an API-classified component, so customizations should call it rather than modifying it. Custom code can call the validation functions to gate element selection and the get_*_lov procedures to obtain a SQL string, then open it dynamically as a ref cursor to populate an LOV. Because callers receive SQL text rather than result sets for the LOV procedures, they must observe the returned cursor type and handle dynamic SQL securely.