Results for “qa_specs”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

QA_SPECS is the master definition table for quality specifications within the Oracle Quality (QA) module of Oracle E-Business Suite 12.1.1 and 12.2.2. A quality specification defines the set of characteristics, target values, tolerances, and collection plan attributes that must be satisfied for a given item, lot, vendor, or customer. Each row in QA_SPECS represents a single specification header, and the table serves as the central hub from which specification characteristics, results, and associated collection plans are derived.

The table resides in the QA schema and is documented as VALID in the ETRM 12.2.2 physical schema, containing 38 columns. Its heuristic Data Vault classification, mined from the foreign key structure, is hub-leaning. This suggests a modeling approach in which QA_SPECS functions as a business hub, keyed by the surrogate SPEC_ID, with dependent characteristic and result tables acting as satellites or links. The classification is a modeling suggestion rather than a mandate; the physical table remains a conventional normalized master entity in the EBS data model.

Specifications are scoped by organization and are subject to effectivity dating, so the same logical specification can evolve over time without overwriting historical definitions. This supports auditability and reproducible quality decisions against past production.

Key Information Stored

The primary key is QA_SPECS_PK, defined on the surrogate column SPEC_ID. Three unique indexes document the business-key candidates. QA_SPECS_U1 uniquely constrains SPEC_ID; QA_SPECS_U2 uniquely constrains SPEC_NAME; and QA_SPECS_U3 defines a composite uniqueness rule across ORGANIZATION_ID, ITEM_ID, ITEM_REVISION, VENDOR_ID, CUSTOMER_ID, CATEGORY_SET_ID, CATEGORY_ID, CHAR_ID, SPEC_ELEMENT_VALUE, and SPEC_ID.

The most significant columns include:

• SPEC_ID — surrogate primary key identifying the specification.
• SPEC_NAME — the user-visible specification name, uniquely constrained.
• ORGANIZATION_ID — the inventory organization that owns the specification.
• EFFECTIVE_FROM and EFFECTIVE_TO — the effectivity window governing when the specification applies.
• ASSIGNMENT_TYPE — indicates how the specification is assigned (for example, to an item, vendor, or customer).
• ITEM_ID and ITEM_REVISION — the item and revision to which the specification is tied.
• VENDOR_ID and CUSTOMER_ID — supplier and customer scoping for incoming and outgoing quality checks.
• CATEGORY_SET_ID and CATEGORY_ID — category-based assignment scope.
• COMMON_SPEC_ID — reference to a shared or common specification definition.
• SPEC_STATUS — current lifecycle state of the specification.
• HIDE_PLAN_CHARS_FLAG — controls whether plan-level characteristics are suppressed in the specification.
• CHAR_ID and SPEC_ELEMENT_VALUE — the characteristic and expected element value in the composite business key.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1–15 — the standard EBS descriptive flexfield columns.

Common Use Cases and Queries

Typical reporting and integration scenarios include retrieving active specifications for an item at a given point in time, verifying vendor or customer scoping, and enumerating characteristics and results tied to a specification.

To list specifications effective on a given date for an organization:

• SELECT spec_id, spec_name, item_id, effective_from, effective_to FROM qa.qa_specs WHERE organization_id = :org_id AND TRUNC(:check_date) BETWEEN effective_from AND NVL(effective_to, :check_date) AND spec_status = 'ACTIVE';

To find specifications assigned to a specific vendor:

• SELECT spec_id, spec_name, vendor_id FROM qa.qa_specs WHERE vendor_id = :vendor_id;

To join specification headers with their characteristics:

• SELECT s.spec_id, s.spec_name, c.char_id FROM qa.qa_specs s JOIN qa.qa_spec_chars c ON c.spec_id = s.spec_id WHERE s.organization_id = :org_id;

Analysts also use QA_SPECS to reconcile quality results against the defining specification, comparing QA_RESULTS.SPEC_ID back to the authoritative header record.

Related Objects

QA_SPECS is referenced by two dependent tables through the SPEC_ID foreign key, and relates to additional QA entities used to compose complete specification definitions.

• QA_SPEC_CHARS — child table holding the characteristics (attributes) belonging to a specification, joined via QA_SPEC_CHARS.SPEC_ID = QA_SPECS.SPEC_ID.
• QA_RESULTS — records collected quality results, joined via QA_RESULTS.SPEC_ID = QA_SPECS.SPEC_ID, allowing results to be traced to their governing specification.
• MTL_SYSTEM_ITEMS_B — provides item and revision context for specifications scoped by ITEM_ID and ITEM_REVISION.
• PO_VENDORS / AP_SUPPLIERS — supply vendor identity for specifications scoped by VENDOR_ID.
• HZ_CUST_ACCOUNTS — customer identity for specifications scoped by CUSTOMER_ID.
• MTL_CATEGORIES_B — category definition used with CATEGORY_SET_ID and CATEGORY_ID.
• QA_SPECS_B (API/entity) — the Quality Specifications business entity and PL/SQL APIs that validate and insert specification headers and their characteristics.

Together these objects form the specification-to-result chain in Oracle Quality, with QA_SPECS acting as the hub binding item, vendor, customer, and category assignments to their measurable characteristics.