QA Collection Plan Relationships – 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

Every Oracle Quality parent child collection plan relationship, at one row per parent element to child element mapping. Each row names both plans with their descriptions and plan types, the relationship type, the data entry mode and layout mode that govern how the child rows are presented, the automatic row count and default parent specification flag, and the element relationship operator that says what the parent value does to the child value, together with the plan level prompt of each mapped element. A relationship that has no element mapping yet is still listed, as a single row.

Oracle Quality lets one collection plan drive another: when results are entered against the parent, a child plan is created and selected element values are copied, summed, averaged or otherwise derived into it. That behaviour is held in three tables which no single Oracle window exposes together - qa_pc_plan_relationship for the pairing and its data entry behaviour, qa_pc_element_relationship for the element by element mapping, and qa_pc_criteria for the conditions under which the child is created at all. This report is the one place the whole chain is visible, which is what makes it the reference when documenting a Quality setup, auditing which plans feed which, or specifying an interface that has to reproduce the derived values a parent plan writes into its children.

Criteria are a gate on the relationship rather than an attribute of one element mapping, so they are aggregated onto every row of that relationship: Criteria Count gives the number of conditions and Criteria renders them as readable expressions. Because aggregated text cannot be filtered, the Criteria Element parameter restricts to the relationships that are gated by a given collection element.

Filter with the optional Organization, Parent Plan, Child Plan and Collection Element parameters, or narrow on the relationship behaviour itself with Element Relationship and Data Entry Mode. Organization and Collection Element match either side of the relationship, so a single value returns everything that touches that organization or that element as parent or as child. Leaving Organization blank returns every relationship whose parent plan the responsibility has inventory organization access to, plus the relationships between seeded template plans, which are not owned by an inventory organization and therefore show a blank Organization Code.

Inventory organization security is applied to the parent plan only. A relationship whose parent plan the responsibility can see is listed in full, including the name, description and plan type of a child plan in an organization the responsibility has no access to, because a relationship spans two plans and suppressing the child would leave the row meaningless.

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, Parent Plan, Child Plan, Collection Element, Element Relationship, Data Entry Mode, Criteria Element

Oracle EBS Tables Used

qa_pc_plan_relationship, qa_plan_chars, qa_pc_element_relationship, qa_pc_criteria, qa_chars, qa_plans, mtl_parameters, org_access_view

Report Categories

Enginatics

QA Collection Plans, QA Plan Relationship Upload, QA Collection Plan Element Values, QA Collection Plan Actions, QA Quality Results, QA Collection Plan Elements, QA Collection Plan Transactions, QA Collection Plan Upload

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 Plan Relationships 14-Aug-2026 112012.xlsx
Blitz Report™ XML Import QA_Collection_Plan_Relationships.xml
SQL Source QA Collection Plan Relationships.sql
Full Report on Enginatics www.enginatics.com/reports/qa-collection-plan-relationships/

Case Study & Technical Analysis: QA Collection Plan Relationships

Executive Summary

This report lists every parent and child collection plan relationship in Oracle Quality, at one row per parent element to child element mapping. Quality engineers and metrology specialists use it when documenting a quality setup, auditing which plans feed which, or specifying an interface that must reproduce the values a parent plan writes into its children. Each row names both plans with their descriptions and types, the relationship behavior, the data entry and layout modes, the criteria that gate the child, and the element relationship operator. The report runs in Excel through Blitz Report.

Business Challenge

The Solution

Technical Architecture (High Level)

Primary Tables

Logical Relationships

  1. QA_PC_PLAN_RELATIONSHIP joins to QA_PLANS twice, once as the parent plan and once as the child plan, on plan id.
  2. Each plan joins to MTL_PARAMETERS on organization id as an outer join, which gives the organization code.
  3. QA_PC_PLAN_RELATIONSHIP joins to QA_PC_ELEMENT_RELATIONSHIP on plan relationship id as an outer join, so relationships without mappings remain.
  4. The parent and child characters join to QA_CHARS on character id, and their prompts come from QA_PLAN_CHARS for the matching plan.
  5. Criteria are read from QA_PC_CRITERIA on plan relationship id, and they are summarized per relationship in the criteria count and the criteria list.

Parameters & Filtering

Performance & Optimization

FAQ

Q: Why does a row show a blank Organization Code? A: Seeded template plans are not owned by an inventory organization, so their organization code is blank.

Q: Why can I see a child plan in an organization I cannot access? A: Inventory organization security applies to the parent plan only. The child plan is shown in full so the relationship is not left incomplete.

Q: How do I find all relationships gated by one element? A: Enter the element in the Criteria Element parameter. The criteria list then shows the conditions for each relationship that uses it.

© Enginatics GmbH