QA Collection Plans – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™. Oracle EBS module: Quality · all Quality reports

Overview

One row per Oracle Quality collection plan, with the plan type, effective dates, instructions, specification assignment type, electronic signature mode and the template plan it was copied from, together with the names of the plan’s generated results view and import view and counts of the elements, element values, transactions, transaction triggers, action rules, actions, child plans and parent plans that hang off it.

Oracle Quality stores every collection result generically in qa_results, and each plan gets its own dynamically generated results view and import view that map that plan’s elements onto the qa_results character columns. This report shows both view names next to a flag stating whether the view actually exists in the database, which is the quickest way to find a plan whose generated view was never built or has since been dropped, because such a plan cannot be queried or imported through it. The counts turn the report into a one page map of a Quality setup: which plans carry elements but collect nothing, which plans fire actions on data entry, and which plans are nested into a parent child structure.

Active is derived from the effective dates and is Yes while the plan is in effect today, a plan with no effective dates always being active. Specification Type states which specification the plan matches its results against at collection time, taken from the item, the supplier or the customer, or none at all. Enabled Elements counts only the elements that are enabled on the plan, so a difference against Elements identifies a plan carrying elements the operator never sees.

Filter with the optional Organization, Collection Plan, Plan Type and Collection Element parameters, Collection Element restricting the report to the plans that use that element, or set Active Plans Only or Template Plans Only to Yes. Leaving Organization blank returns every collection plan the responsibility has inventory organization access to, plus the seeded template plans, which are not owned by an inventory organization and therefore show a blank Organization Code; Template Plans Only returns exactly those seeded plans.

Run this report in Excel in seconds: download Blitz Report™ free (your first 30 reports are free) or try it in the online demo.

Report Parameters

Organization, Collection Plan, Plan Type, Collection Element, Active Plans Only, Template Plans Only

Oracle EBS Tables Used

qa_plans, qa_plan_chars, qa_plan_char_value_lookups, qa_plan_transactions, qa_plan_collection_triggers, qa_plan_char_action_triggers, qa_plan_char_actions, qa_pc_plan_relationship, mtl_parameters, user_views, org_access_view

Report Categories

Enginatics

QA Collection Plan Upload, QA Collection Plan Actions, QA Collection Plan Transactions, QA Collection Plan Relationships, QA Plan Relationship Upload, QA Collection Plan Element Values, QA Collection Plan Elements

Running This SQL Without Blitz Report

The SQL source can be run in any SQL client. Some Oracle EBS SQL reports in this library use functions from the utility package xxen_util – install it first. Blitz Report™ adds parameters, Excel output with pivots, scheduling and security on top.

Download & Import Options

Resource Link
Excel Example Output QA Collection Plans 14-Aug-2026 195528.xlsx
Blitz Report™ XML Import QA_Collection_Plans.xml
SQL Source QA Collection Plans.sql
Full Report on Enginatics www.enginatics.com/reports/qa-collection-plans/

Case Study & Technical Analysis: QA Collection Plans

Executive Summary

The QA Collection Plans report lists one row per Oracle Quality collection plan, with its type, effective dates, instructions, specification type, electronic signature mode and template plan. Each row also names the plan’s generated results view and import view, shows whether those views exist in the database, and counts the elements, element values, transactions, triggers, action rules, actions, and parent and child plans attached to it. Quality engineers and Quality administrators use it as a one-page map of a Quality setup. It is the quickest way to find a plan whose generated view was never built or was dropped, because such a plan cannot be queried or imported through that view. The report runs in Excel via Blitz Report.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. QA_PLANS is outer joined to MTL_PARAMETERS on organization identifier.
  2. The results view and import view names of QA_PLANS are outer joined to USER_VIEWS, so a missing view returns a blank match.
  3. Each count is a correlated subquery on the plan identifier, with the chain of transactions, triggers, action rules and actions joined inside the subquery.
  4. The template plan name is a scalar lookup on the template plan identifier.
  5. Parent and child counts are subqueries on QA_PC_PLAN_RELATIONSHIP in each direction.

Parameters & Filtering

Performance & Optimization

FAQ

Q: How do I find a collection plan whose results view was never built? A: Look for plans where the Results View Exists column shows No; such a plan cannot be queried through its results view.

Q: What does Enabled Elements show compared with Elements? A: Elements counts all elements on the plan, and Enabled Elements counts only the elements enabled on the plan, so a difference shows elements the operator never sees.

Q: Which plans are template plans? A: The template plans are the seeded plans, which have a blank Organization Code. Setting Template Plans Only to Yes returns exactly those plans.

© Enginatics GmbH