Search Results sql_validation_string

Overview

QA_CHARS_V is an APPS-owned, VALID database view within the Oracle E-Business Suite Quality (QA) module. Its documented purpose is to expose collection element information — the specification characteristics (collection elements) defined for quality collection plans in Oracle Quality. In EBS 12.1.1 and 12.2.2 the object is unchanged in role: it is a reporting and integration surface that flattens the QA_CHARS entity together with its decoded lookup meanings, unit-of-measure descriptions, and foreign-key metadata, so that downstream reports, concurrent programs, and interfaces can consume collection element definitions without repeatedly joining to the lookup views. Because it resolves descriptive lookups (character type, datatype, mandatory flag, enabled flag, context flag, values-exist flag) into human-readable meanings, it is commonly used as the source for quality setup extracts and for validating configuration prior to plan execution.

Underlying Base Objects

The documented referenced base objects are FND_COMMON_LOOKUPS (VIEW), FND_GLOBAL (PACKAGE), MFG_LOOKUPS (VIEW), MTL_UNITS_OF_MEASURE (SYNONYM), QA_CHARS (SYNONYM), and QA_LOOKUPS (VIEW). QA_CHARS is the primary driving table, supplying the characteristic rows (CHAR_ID, NAME, CHAR_TYPE_CODE, datatype and limit columns, sequence columns, and the descriptive flexfield ATTRIBUTE1–15 columns). MFG_LOOKUPS is joined multiple times (aliases ML, ML3, ML4, ML6, ML7) to translate code columns into MEANING/DESCRIPTION text for mandatory, context, datatype, enabled, and values-exist flags. MTL_UNITS_OF_MEASURE supplies the UNIT_OF_MEASURE description for UOM_CODE. QA_LOOKUPS and FND_COMMON_LOOKUPS provide additional lookup resolution, while FND_GLOBAL contributes session context such as the current user and login used in the view's WHERE clause against LAST_UPDATE_LOGIN.

Key Columns

• CHAR_ID, NAME, PROMPT, DATA_ENTRY_HINT — primary identity and presentation attributes of the collection element.
• CHAR_TYPE_CODE / CHAR_TYPE_MEANING / CHAR_TYPE_DESCRIPTION — the element type and its decoded lookup text.
• DATATYPE / DATATYPE_MEANING, DISPLAY_LENGTH, DECIMAL_PRECISION — data typing and formatting rules.
• TARGET_VALUE, UPPER_SPEC_LIMIT, LOWER_SPEC_LIMIT, UPPER_REASONABLE_LIMIT, LOWER_REASONABLE_LIMIT, UPPER_USER_DEFINED_LIMIT, LOWER_USER_DEFINED_LIMIT — specification and control limits used in results evaluation.
• MANDATORY_FLAG / MANDATORY_MEANING, ENABLED_FLAG / ENABLED_MEANING, VALUES_EXIST_FLAG / VALUES_EXIST_MEANING — decoded status flags.
• UOM_CODE / UNIT_OF_MEASURE — unit of measure for numeric collection elements.
• SQL_VALIDATION_STRING and SQL_STRING_FLAG — the validation SQL, with the flag derived via DECODE (NULL yields 'N', otherwise 'Y').
• DEPENDENT_CHAR_ID, FK_LOOKUP_TYPE, FK_TABLE_NAME, FK_TABLE_SHORT_NAME, PK_ID, FK_ID (and PK_ID2/FK_ID2, PK_ID3/FK_ID3), FK_MEANING, FK_DESCRIPTION, FK_ADD_WHERE — foreign-key and dependent-element metadata.
• SEQUENCE_NUMBER, SEQUENCE_PREFIX, SEQUENCE_SUFFIX, SEQUENCE_SEPARATOR, SEQUENCE_START, SEQUENCE_LENGTH, SEQUENCE_INCREMENT, SEQUENCE_NEXTVAL, SEQUENCE_ZERO_PAD — the auto-numbering configuration for the element, including the starting value (SEQUENCE_START) referenced by the search term.
• ATTRIBUTE_CATEGORY, ATTRIBUTE1–ATTRIBUTE15 — descriptive flexfield context and segments.
• ROW_ID — the QA_CHARS ROWID, exposed for updatable/row-addressable access.

Common Use Cases and Queries

Typical scenarios include auditing collection element configuration across collection plans, extracting specification limits for laboratory or supplier-quality reporting, and validating auto-numbering settings for elements that generate sequential identifiers.

• List all enabled elements with their datatype and mandatory status.
• Inspect auto-numbering rules, particularly the start value.
• Extract specification limits for a given element.
• Identify elements carrying SQL validation logic.

Sample SQL:

SELECT CHAR_ID, NAME, DATATYPE_MEANING, MANDATORY_MEANING, SEQUENCE_PREFIX, SEQUENCE_START, SEQUENCE_INCREMENT, SEQUENCE_NEXTVAL, SEQUENCE_ZERO_PAD FROM APPS.QA_CHARS_V WHERE ENABLED_FLAG = 'Y' AND SEQUENCE_START IS NOT NULL ORDER BY NAME;

SELECT CHAR_ID, NAME, TARGET_VALUE, UPPER_SPEC_LIMIT, LOWER_SPEC_LIMIT, UOM_CODE, UNIT_OF_MEASURE FROM APPS.QA_CHARS_V WHERE CHAR_ID = :p_char_id;

SELECT CHAR_ID, NAME, SQL_VALIDATION_STRING FROM APPS.QA_CHARS_V WHERE SQL_STRING_FLAG = 'Y';