ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 0B22DF18E239D64AE0630100007FBD85 INV Organization Code (including master) Y select ood.organization_code value, ood.organization_name description from org_organization_definitions ood where ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate order by ood.organization_code US All active inventory organization codes, restricted to the current user's org access view 52162B9B8C221F1DE0630100007FADC0 QA Collection Plan Y select qp.name value, qp.description from qa_plans qp, mtl_parameters mp where qp.organization_id=mp.organization_id(+) and (:$flex$.Organization is null or xxen_util.contains(:$flex$.Organization,mp.organization_code)='Y') and (qp.organization_id=0 or qp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)) order by qp.name US 58FCCAD35C455CCEE0630100007FC259Y select mp_p.organization_code parent_organization_code, qp_p.name parent_plan, qp_p.description parent_plan_description, xxen_util.meaning(qp_p.plan_type_code,'COLLECTION_PLAN_TYPE',3) parent_plan_type, mp_c.organization_code child_organization_code, qp_c.name child_plan, qp_c.description child_plan_description, xxen_util.meaning(qp_c.plan_type_code,'COLLECTION_PLAN_TYPE',3) child_plan_type, xxen_util.meaning(qppr.plan_relationship_type,'QA_PC_PLAN_RELATIONSHIP',250) relationship_type, xxen_util.meaning(qppr.data_entry_mode,'QA_PC_DATA_ENTRY_MODE',250) data_entry_mode, xxen_util.meaning(qppr.layout_mode,'QA_PC_LAYOUT_MODE',250) layout_mode, qppr.auto_row_count, xxen_util.yes(case when qppr.default_parent_spec=1 then 'Y' end) default_parent_spec, qc_p.name parent_element, (select qpc_p.prompt from qa_plan_chars qpc_p where qppr.parent_plan_id=qpc_p.plan_id and qper.parent_char_id=qpc_p.char_id) parent_element_prompt, xxen_util.meaning(qper.element_relationship_type,'QA_PC_ELEMENT_RELATIONSHIP',250) element_relationship, qc_c.name child_element, (select qpc_c.prompt from qa_plan_chars qpc_c where qppr.child_plan_id=qpc_c.plan_id and qper.child_char_id=qpc_c.char_id) child_element_prompt, xxen_util.yes(case when qper.link_flag=1 then 'Y' end) linked, (select count(*) from qa_pc_criteria qpcr where qppr.plan_relationship_id=qpcr.plan_relationship_id) criteria_count, (select listagg(qc_cr.name||' '||xxen_util.meaning(qpcr.operator,'QA_PC_OPERATOR',700)||nvl2(qpcr.low_value,' '||qpcr.low_value,null)||nvl2(qpcr.high_value,' and '||qpcr.high_value,null),', ') within group (order by qc_cr.name) from qa_pc_criteria qpcr, qa_chars qc_cr where qppr.plan_relationship_id=qpcr.plan_relationship_id and qpcr.char_id=qc_cr.char_id(+)) criteria, xxen_util.user_name(nvl(qper.created_by,qppr.created_by)) created_by, xxen_util.client_time(nvl(qper.creation_date,qppr.creation_date)) creation_date, xxen_util.user_name(nvl(qper.last_updated_by,qppr.last_updated_by)) last_updated_by, xxen_util.client_time(nvl(qper.last_update_date,qppr.last_update_date)) last_update_date from qa_pc_plan_relationship qppr, qa_plans qp_p, mtl_parameters mp_p, qa_plans qp_c, mtl_parameters mp_c, qa_pc_element_relationship qper, qa_chars qc_p, qa_chars qc_c where qppr.parent_plan_id=qp_p.plan_id and qp_p.organization_id=mp_p.organization_id(+) and qppr.child_plan_id=qp_c.plan_id and qp_c.organization_id=mp_c.organization_id(+) and qppr.plan_relationship_id=qper.plan_relationship_id(+) and qper.parent_char_id=qc_p.char_id(+) and qper.child_char_id=qc_c.char_id(+) and (qp_p.organization_id=0 or qp_p.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)) and 1=1 order by mp_p.organization_code, qp_p.name, qp_c.name, qc_p.name, qc_c.name Review corrections. Four changes, none of which alters the row set the report returns. First, the Collection Element and Criteria Element pickers now cascade. They referenced the shared QA Collection Element list of values, whose plan filter is written against a parameter called Collection Plan, a name this report does not use because its plan parameters are Parent Plan and Child Plan; the token therefore matched no parameter, bound null silently and derived no dependency, so choosing a plan did not narrow either picker and both offered every element of every visible plan. Both parameters are now custom lists of values carrying the same query with the filter rewritten against the two parameters that exist, combined so an element used on either side of the relationship is offered, which restores the cascade and derives the missing dependencies. Second, the child plan alias in the main query is renamed from qpc to qp_c and the parent from qpp to qp_p, matching the mp_p and mp_c pattern already used for the two organizations; qpc denotes qa_plan_chars everywhere else in this report set, including inside this report's own element pickers, so the collision was a genuine trap for the next reader. The two element prompt subquery aliases are renamed to qpc_p and qpc_c for the same reason. Third, a form assignment on the Q_PLANS block of QLTPLMDF is added, mapping the form's plan to Parent Plan, so the report is reachable by zoom from the Collection Plans form like its five siblings; the previously recorded decision not to assign the report was reasoned from the absence of a parent child block on that form, which is factually correct and was verified again, but it is the wrong test, because the entity the user is looking at in Q_PLANS is a plan and this report is what states how that plan nests. Fourth, the description now states that inventory organization security is applied to the parent plan only, so the fact that a child plan in an inaccessible organization is still named is a recorded decision rather than an unstated asymmetry. Design decisions worth recording. The criteria are aggregated onto the relationship header rather than emitted as their own rows because a criteria is a header level gate on whether the child is created at all, not an attribute of one element mapping; that choice also makes the element by criteria cartesian carried by the shipped QA Plan Relationship Upload structurally impossible here, and it is why a filterable Criteria Element parameter and a Criteria Count column are provided in place of filtering on the aggregated text. qa_pc_element_relationship is outer joined so a relationship that has no element mapping yet still returns exactly one row, and the audit columns fall back from the mapping to the relationship with nvl for that case. The plan level element prompts are deliberately scalar subqueries rather than joins to qa_plan_chars: a joined alias would have to reference both the relationship and the element relationship, which raises ORA-01417 on any database before 12c, and the plan and element pair is unique in qa_plan_chars so a scalar subquery is safe. qa_pc_plan_relationship, qa_pc_element_relationship and qa_pc_criteria were dictionary diffed against 11i and carry no R12 only columns at all, so this ships as one dual use definition with no 11i variant and no translate rule. Element Relationship and Data Entry Mode are custom LOVs only because the seeded QA_SRS value sets contain no equivalent; both expose the lookup code as the id so the bind and the SQL_TEXT compare codes rather than a translated meaning. qa_pc_results_relationship is deliberately not included: it links actual result occurrences rather than setup and belongs with a quality results report. US QA Collection Plan Relationships 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. Enginatics 1=1 1 10 1=1 (mp_p.organization_code=:organization or mp_c.organization_code=:organization) LOV INV Organization Code (including master) 0B22DF18E239D64AE0630100007FBD85 Y select ood.organization_code value, ood.organization_name description from org_organization_definitions ood where ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate order by ood.organization_code xxen_util.previous_parameter_value(:parameter_id) US Organization 2 20 1=1 qp_p.name=:parent_plan LOV QA Collection Plan 52162B9B8C221F1DE0630100007FADC0 Y select qp.name value, qp.description from qa_plans qp, mtl_parameters mp where qp.organization_id=mp.organization_id(+) and (:$flex$.Organization is null or xxen_util.contains(:$flex$.Organization,mp.organization_code)='Y') and (qp.organization_id=0 or qp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)) order by qp.name US Parent Plan 3 30 1=1 qp_c.name=:child_plan LOV QA Collection Plan 52162B9B8C221F1DE0630100007FADC0 Y select qp.name value, qp.description from qa_plans qp, mtl_parameters mp where qp.organization_id=mp.organization_id(+) and (:$flex$.Organization is null or xxen_util.contains(:$flex$.Organization,mp.organization_code)='Y') and (qp.organization_id=0 or qp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)) order by qp.name US Child Plan 4 40 1=1 (qc_p.name=:collection_element or qc_c.name=:collection_element) LOV custom Y select distinct qc.name value, qc.prompt description from qa_plans qp, mtl_parameters mp, qa_plan_chars qpc, qa_chars qc where qp.organization_id=mp.organization_id(+) and qp.plan_id=qpc.plan_id and qpc.char_id=qc.char_id and (:$flex$.Organization is null or xxen_util.contains(:$flex$.Organization,mp.organization_code)='Y') and (:$flex$.Parent_Plan is null and :$flex$.Child_Plan is null or xxen_util.contains(:$flex$.Parent_Plan,qp.name)='Y' or xxen_util.contains(:$flex$.Child_Plan,qp.name)='Y') and (qp.organization_id=0 or qp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)) order by qc.name US Collection Element 5 50 1=1 qper.element_relationship_type=:element_relationship LOV custom Y select flvv.lookup_code id, flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='QA_PC_ELEMENT_RELATIONSHIP' and flvv.view_application_id=250 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by to_number(flvv.lookup_code) US Element Relationship 6 60 1=1 qppr.data_entry_mode=:data_entry_mode LOV custom Y select flvv.lookup_code id, flvv.meaning value, flvv.description from fnd_lookup_values_vl flvv where flvv.lookup_type='QA_PC_DATA_ENTRY_MODE' and flvv.view_application_id=250 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by to_number(flvv.lookup_code) US Data Entry Mode 7 70 1=1 qppr.plan_relationship_id in (select qpcr.plan_relationship_id from qa_pc_criteria qpcr, qa_chars qc_cr where qpcr.char_id=qc_cr.char_id and qc_cr.name=:criteria_element) LOV custom Y select distinct qc.name value, qc.prompt description from qa_plans qp, mtl_parameters mp, qa_plan_chars qpc, qa_chars qc where qp.organization_id=mp.organization_id(+) and qp.plan_id=qpc.plan_id and qpc.char_id=qc.char_id and (:$flex$.Organization is null or xxen_util.contains(:$flex$.Organization,mp.organization_code)='Y') and (:$flex$.Parent_Plan is null and :$flex$.Child_Plan is null or xxen_util.contains(:$flex$.Parent_Plan,qp.name)='Y' or xxen_util.contains(:$flex$.Child_Plan,qp.name)='Y') and (qp.organization_id=0 or qp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)) order by qc.name US Criteria Element