<ROOT>
 <APPS_INITIALIZE_DATA>
  <USER_NAME>ENGINATICS</USER_NAME>
  <RESPONSIBILITY_KEY>SYSTEM_ADMINISTRATOR</RESPONSIBILITY_KEY>
  <APPLICATION_SHORT_NAME>SYSADMIN</APPLICATION_SHORT_NAME>
 </APPS_INITIALIZE_DATA>
<REPORTS>
<!-- loader xml for Enginatics Blitz Report: QA Collection Plan Relationships -->
 <REPORTS_ROW>
  <GUID>58FCCAD35C455CCEE0630100007FC259</GUID><ENABLED>Y</ENABLED>
  <SQL_TEXT>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,&apos;COLLECTION_PLAN_TYPE&apos;,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,&apos;COLLECTION_PLAN_TYPE&apos;,3) child_plan_type,
xxen_util.meaning(qppr.plan_relationship_type,&apos;QA_PC_PLAN_RELATIONSHIP&apos;,250) relationship_type,
xxen_util.meaning(qppr.data_entry_mode,&apos;QA_PC_DATA_ENTRY_MODE&apos;,250) data_entry_mode,
xxen_util.meaning(qppr.layout_mode,&apos;QA_PC_LAYOUT_MODE&apos;,250) layout_mode,
qppr.auto_row_count,
xxen_util.yes(case when qppr.default_parent_spec=1 then &apos;Y&apos; 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,&apos;QA_PC_ELEMENT_RELATIONSHIP&apos;,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 &apos;Y&apos; 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||&apos; &apos;||xxen_util.meaning(qpcr.operator,&apos;QA_PC_OPERATOR&apos;,700)||nvl2(qpcr.low_value,&apos; &apos;||qpcr.low_value,null)||nvl2(qpcr.high_value,&apos; and &apos;||qpcr.high_value,null),&apos;, &apos;) 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 xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;INV&apos;)) and
1=1
order by
mp_p.organization_code,
qp_p.name,
qp_c.name,
qc_p.name,
qc_c.name</SQL_TEXT>
  <VERSION_COMMENTS>Organization security from the Blitz Report organization access, which follows profiles Blitz Report Organization Access All Responsibilities, Blitz Report Use Operating Unit Security and Blitz Report Use Ledger Security.</VERSION_COMMENTS>
  <REPORT_TRANSLATIONS>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>US</LANGUAGE>
    <REPORT_NAME>QA Collection Plan Relationships</REPORT_NAME>
    <DESCRIPTION>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.</DESCRIPTION>
   </REPORT_TRANSLATIONS_ROW>
  </REPORT_TRANSLATIONS>
  <CATEGORY_ASSIGNMENTS>
   <CATEGORY_ASSIGNMENTS_ROW>
    <CATEGORY>Enginatics</CATEGORY>
   </CATEGORY_ASSIGNMENTS_ROW>
  </CATEGORY_ASSIGNMENTS>
  <ANCHORS>
   <ANCHORS_ROW>
    <ANCHOR>1=1</ANCHOR>
   </ANCHORS_ROW>
  </ANCHORS>
  <PARAMETERS>
   <PARAMETERS_ROW>
    <SORT_ORDER>1</SORT_ORDER>
    <DISPLAY_SEQUENCE>10</DISPLAY_SEQUENCE>
    <ANCHOR>1=1</ANCHOR>
    <SQL_TEXT>(mp_p.organization_code=:organization or mp_c.organization_code=:organization)</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>INV Organization Code (including master)</LOV_NAME>
    <LOV_GUID>0B22DF18E239D64AE0630100007FBD85</LOV_GUID>
    <LOV_QUERY_DSP>select
ood.organization_code value,
ood.organization_name description
from
org_organization_definitions ood
where
ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;INV&apos;) 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)=&apos;Y&apos;)) 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)=&apos;Y&apos;)) and
nvl(ood.disable_date,sysdate)&gt;=sysdate
order by
ood.organization_code</LOV_QUERY_DSP>
    <DEFAULT_VALUE>xxen_util.previous_parameter_value(:parameter_id)</DEFAULT_VALUE>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Organization</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>2</SORT_ORDER>
    <DISPLAY_SEQUENCE>20</DISPLAY_SEQUENCE>
    <ANCHOR>1=1</ANCHOR>
    <SQL_TEXT>qp_p.name=:parent_plan</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>QA Collection Plan</LOV_NAME>
    <LOV_GUID>52162B9B8C221F1DE0630100007FADC0</LOV_GUID>
    <LOV_QUERY_DSP>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)=&apos;Y&apos;) and
(qp.organization_id=0 or qp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;INV&apos;))
order by
qp.name</LOV_QUERY_DSP>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Parent Plan</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>3</SORT_ORDER>
    <DISPLAY_SEQUENCE>30</DISPLAY_SEQUENCE>
    <ANCHOR>1=1</ANCHOR>
    <SQL_TEXT>qp_c.name=:child_plan</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>QA Collection Plan</LOV_NAME>
    <LOV_GUID>52162B9B8C221F1DE0630100007FADC0</LOV_GUID>
    <LOV_QUERY_DSP>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)=&apos;Y&apos;) and
(qp.organization_id=0 or qp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;INV&apos;))
order by
qp.name</LOV_QUERY_DSP>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Child Plan</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>4</SORT_ORDER>
    <DISPLAY_SEQUENCE>40</DISPLAY_SEQUENCE>
    <ANCHOR>1=1</ANCHOR>
    <SQL_TEXT>(qc_p.name=:collection_element or qc_c.name=:collection_element)</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV custom</PARAMETER_TYPE_DSP>
    <LOV_QUERY_DSP>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)=&apos;Y&apos;) and
(:$flex$.Parent_Plan is null and :$flex$.Child_Plan is null or xxen_util.contains(:$flex$.Parent_Plan,qp.name)=&apos;Y&apos; or xxen_util.contains(:$flex$.Child_Plan,qp.name)=&apos;Y&apos;) and
(qp.organization_id=0 or qp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;INV&apos;))
order by
qc.name</LOV_QUERY_DSP>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Collection Element</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>5</SORT_ORDER>
    <DISPLAY_SEQUENCE>50</DISPLAY_SEQUENCE>
    <ANCHOR>1=1</ANCHOR>
    <SQL_TEXT>qper.element_relationship_type=:element_relationship</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>QA Element Relationship</LOV_NAME>
    <LOV_GUID>AFD59CAA8EEB4F0685AF61716F0CE133</LOV_GUID>
    <LOV_QUERY_DSP>select
flvv.lookup_code id,
flvv.meaning value,
flvv.description
from
fnd_lookup_values_vl flvv
where
flvv.lookup_type=&apos;QA_PC_ELEMENT_RELATIONSHIP&apos; and
flvv.view_application_id=250 and
flvv.security_group_id=0 and
flvv.enabled_flag=&apos;Y&apos;
order by
to_number(flvv.lookup_code)</LOV_QUERY_DSP>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Element Relationship</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>6</SORT_ORDER>
    <DISPLAY_SEQUENCE>60</DISPLAY_SEQUENCE>
    <ANCHOR>1=1</ANCHOR>
    <SQL_TEXT>qppr.data_entry_mode=:data_entry_mode</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>QA Data Entry Mode</LOV_NAME>
    <LOV_GUID>DD1D26DEAA3B4ECF9801F0931367FC2E</LOV_GUID>
    <LOV_QUERY_DSP>select
flvv.lookup_code id,
flvv.meaning value,
flvv.description
from
fnd_lookup_values_vl flvv
where
flvv.lookup_type=&apos;QA_PC_DATA_ENTRY_MODE&apos; and
flvv.view_application_id=250 and
flvv.security_group_id=0 and
flvv.enabled_flag=&apos;Y&apos;
order by
to_number(flvv.lookup_code)</LOV_QUERY_DSP>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Data Entry Mode</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>7</SORT_ORDER>
    <DISPLAY_SEQUENCE>70</DISPLAY_SEQUENCE>
    <ANCHOR>1=1</ANCHOR>
    <SQL_TEXT>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)</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV custom</PARAMETER_TYPE_DSP>
    <LOV_QUERY_DSP>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)=&apos;Y&apos;) and
(:$flex$.Parent_Plan is null and :$flex$.Child_Plan is null or xxen_util.contains(:$flex$.Parent_Plan,qp.name)=&apos;Y&apos; or xxen_util.contains(:$flex$.Child_Plan,qp.name)=&apos;Y&apos;) and
(qp.organization_id=0 or qp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;INV&apos;))
order by
qc.name</LOV_QUERY_DSP>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Criteria Element</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
  </PARAMETERS>
  <TEMPLATES>
  </TEMPLATES>
  <DEFAULT_TEMPLATES>
  </DEFAULT_TEMPLATES>
  <UPLOAD_COLUMNS>
  </UPLOAD_COLUMNS>
  <UPLOAD_PARAMETERS>
  </UPLOAD_PARAMETERS>
  <UPLOAD_SQLS>
  </UPLOAD_SQLS>
 </REPORTS_ROW>
</REPORTS>
</ROOT>
