QA Plan Relationship Upload – Oracle EBS SQL Report

Oracle E-Business Suite SQL report from the Enginatics Library powered by Blitz Report™.

Overview

Upload to create, update, or delete Quality Collection Plan parent-child relationships using the standard Oracle API qa_ss_parent_child_pkg.

This upload manages:

• Parent-Child Plan Relationships (QA_PC_PLAN_RELATIONSHIP)
• Element Relationships between parent and child plans (QA_PC_ELEMENT_RELATIONSHIP)
• Criteria for automatic child record creation (QA_PC_CRITERIA)

Entry Modes:

• Immediate: Child data entered immediately after parent record
• Delayed: Child records entered manually later
• Automatic: Child records auto-created when parent matches criteria
• History: Child records auto-created when changes made to parent

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

Upload Mode, Organization, Parent Plan, Child Plan

Oracle EBS Tables Used

qa_pc_element_relationship, mtl_parameters, qa_plans, qa_pc_plan_relationship, qa_chars, qa_pc_criteria

Report Categories

Enginatics, Upload

Related Reports

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

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	Example
Blitz Report™ XML Import	QA_Plan_Relationship_Upload.xml
SQL Source	QA Plan Relationship Upload.sql
Full Report on Enginatics	www.enginatics.com/reports/qa-plan-relationship-upload/

Case Study & Technical Analysis: QA Plan Relationship Upload

Executive Summary

The QA Plan Relationship Upload creates, updates and deletes parent and child relationships between Oracle Quality collection plans. It is written for quality administrators who set up linked collection plans, where results captured on a parent plan automatically or manually lead to child plan records. The upload maintains three things through the standard Oracle API for quality parent-child packages: the plan relationship, the element relationships between the parent and child plans, and the criteria that decide when child records are created automatically. It sits in the Quality module and is shown in Excel through Blitz Report.

Business Challenge

• Linked inspection plans: Some inspections need a child plan to record detail results once a parent plan has been completed, and each link must be defined for every parent plan.
• Choosing the entry mode: The team must decide whether child data is entered immediately, later by hand, created automatically when the parent matches criteria, or created whenever the parent changes.
• Mapping elements between plans: Parent and child plans need elements mapped to each other, so that values flow correctly between them.
• Setting up criteria: Automatic child creation depends on criteria tests against parent elements, and these must be maintained consistently across many relationships.

The Solution

• Relationship in one row: Each row shows the organisation, parent plan, child plan, entry mode, layout mode, auto row count and default parent specification.
• Element mapping: The parent element, child element and relationship type of each element pair are shown, with the link flag.
• Criteria detail: Each criteria test shows the element it tests, its operator and the from and to values.
• Identifiers for maintenance: The plan relationship, element relationship and criteria identifiers are returned, so an update or deletion targets the right record.
• Delete marker: A delete record column allows a row to be flagged for removal through the API.

Technical Architecture (High Level)

Primary Tables

• QA_PC_PLAN_RELATIONSHIP: the relationship between a parent plan and a child plan, with entry mode, layout mode and automatic row count.
• QA_PC_ELEMENT_RELATIONSHIP: the mapping of elements between a parent and a child plan.
• QA_PC_CRITERIA: the criteria that decide automatic child record creation.
• QA_PLANS: the parent and child collection plans.
• QA_CHARS: the collection elements of the parent, child and criteria columns.
• MTL_PARAMETERS: the inventory organisation that owns the parent plan.

Logical Relationships

1. MTL_PARAMETERS joins to the parent plan in QA_PLANS on the organisation identifier.
2. The parent plan joins to QA_PC_PLAN_RELATIONSHIP on the parent plan identifier, and the child plan joins to the relationship on the child plan identifier.
3. Element relationships join to the plan relationship as an outer join, so a relationship without element mappings is still listed.
4. The parent and child elements join to QA_CHARS as outer joins through the element relationship.
5. Criteria join to the plan relationship as an outer join, and the criteria element joins to QA_CHARS as an outer join.

Parameters & Filtering

• Upload Mode: Chooses between create, update and delete operations, which determines the use of the delete record column.
• Organization: Restricts the relationships to parent plans in one inventory organisation.
• Parent Plan: Restricts the output to relationships from one parent plan.
• Child Plan: Restricts the output to relationships to one child plan.

Performance & Optimization

• Parent-driven query: The query starts from the parent plans of the selected organisation, so the relationship table is read only for those plans.
• Outer joins for dependent records: Element relationships and criteria are attached with outer joins, so a plan relationship without elements or criteria still appears for maintenance.
• Small configuration set: Plan relationships are configuration records, so the output is typically small, and the main cost is the join chain across the plan and element tables.

FAQ

Q: How do I set up a child plan that is created automatically from a parent plan? A: Set the entry mode to automatic in the file, specify the criteria for the child creation, and upload the file. The description lists Automatic as the mode where child records are created when the parent matches the criteria.

Q: What is the difference between Immediate and Delayed entry modes? A: Immediate means the child data is entered straight after the parent record, while Delayed means the child records are entered manually later.

Q: Can this upload remove a parent-child relationship? A: Yes. The delete record column allows a relationship row to be deleted through the standard API, as the description states for the create, update and delete operations.

---

Useful Links

• Blitz Report™ – World’s Fastest Oracle EBS Reporting Tool
• Oracle Discoverer Replacement – Import Worksheets into Blitz Report™
• Oracle EBS Reporting Toolkits by Blitz Report™
• Blitz Report™ FAQ & Community Q&A
• Supply Chain Hub by Blitz Report™
• Blitz Report™ Customer Case Studies
• Oracle EBS Reporting Blog
• Oracle EBS Reporting Resource Centre

© Enginatics GmbH