Results for “qa_skiplot_state_history”

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

Overview

QA_SKIPLOT_STATE_HISTORY is a Quality (QA) module table that stores the historical audit trail of skip lot inspection state transitions. In Oracle EBS 12.1.1 and 12.2.2, skip lot inspection allows receiving inspection to be bypassed for a defined number of lots (or a percentage of lots) once a supplier and item have demonstrated consistent quality conformance. Each time the skip lot sampling ratio changes — for example, moving from "skip 3, inspect 1" to "skip 5, inspect 1", or reverting to full inspection after a rejection — the prior and new frequency values are recorded in this table, tied to the governing skip lot process plan and receipt criteria.

The table is owned by the QA schema and is documented as VALID, with 12 physical columns. It functions as an insert-only historical ledger rather than a transactional work table, and is queried primarily for supplier quality trending, audit compliance, and root-cause analysis of sampling plan changes. From a heuristic Data Vault modeling perspective, the mined FK structure suggests classifying QA_SKIPLOT_STATE_HISTORY as a link table: it connects two parent entities (QA_SKIPLOT_PROCESS_PLANS and QA_SL_SP_RCV_CRITERIA) while carrying descriptive change metadata. This classification is a modeling suggestion derived from the foreign key topology, not a statement of design intent by Oracle.

Key Information Stored

The most significant columns are the following:

• PROCESS_PLAN_ID — Foreign key to QA_SKIPLOT_PROCESS_PLANS. Identifies the skip lot process plan whose state changed. This is the primary business-key candidate for grouping history rows by plan.
• CRITERIA_ID — Foreign key to QA_SL_SP_RCV_CRITERIA. Identifies the receiving inspection criteria that triggered or motivated the state change.
• CHANGE_DATE — The effective date and time the skip lot frequency transition occurred. This is the principal temporal key for chronological reporting and point-in-time reconstruction.
• OLD_FREQ_NUM / OLD_FREQ_DENOM — The numerator and denominator of the skip lot frequency in effect before the change. For example, a value of 1/4 indicates one inspection out of every four lots.
• NEW_FREQ_NUM / NEW_FREQ_DENOM — The numerator and denominator of the revised frequency after the change.
• LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — Standard Oracle EBS audit columns capturing who created and last touched the row and when.

The table does not expose a documented single-column surrogate primary key in the ETRM metadata; uniqueness is effectively implied by the combination of PROCESS_PLAN_ID, CRITERIA_ID, and CHANGE_DATE. The audit columns should not be treated as business keys.

Common Use Cases and Queries

The most frequent use cases involve reconstructing the sampling history of a supplier-item combination and auditing frequency changes over time. A typical query joins the history to its parent process plan:

• Frequency transition report: SELECT h.PROCESS_PLAN_ID, h.CHANGE_DATE, h.OLD_FREQ_NUM, h.OLD_FREQ_DENOM, h.NEW_FREQ_NUM, h.NEW_FREQ_DENOM FROM QA_SKIPLOT_STATE_HISTORY h WHERE h.CHANGE_DATE BETWEEN :from_date AND :to_date ORDER BY h.CHANGE_DATE DESC;
• Plan-level audit trail: join PROCESS_PLAN_ID to QA_SKIPLOT_PROCESS_PLANS to display supplier, item, and plan context alongside each change.
• Escalation analysis: filter rows where NEW_FREQ_DENOM is lower than OLD_FREQ_DENOM to identify plans that were tightened or returned to full inspection, often following a rejection.

These queries support supplier scorecarding, quality dashboards, and internal/external audit evidence requests.

Related Objects

The FK relationships documented in the ETRM metadata identify the principal related objects:

• QA_SKIPLOT_PROCESS_PLANS — joined on PROCESS_PLAN_ID; the parent plan definition.
• QA_SL_SP_RCV_CRITERIA — joined on CRITERIA_ID; the receiving inspection criteria driving the change.

Beyond these documented parents, reporting solutions typically join QA_SKIPLOT_STATE_HISTORY to skip lot setup and receiving inspection tables (such as the skip lot header/plan definitions used during receipt inspection) and, indirectly via the process plan, to supplier and item master tables. Because the table is a historical ledger, it should be treated as read-only by custom code, with inserts performed by the standard skip lot processing logic.