<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: CAC User-Defined and Rolled Up Costs -->
 <REPORTS_ROW>
  <GUID>C1BE24025092C29EE053BB6B63586215</GUID><ENABLED>Y</ENABLED>
  <SQL_TEXT>select
nvl(gl.short_name,gl.name) ledger,
haouv_ou.name operating_unit,
mp.organization_code org_code,
cct.cost_type,
msiv.concatenated_segments item_number,
msiv.description item_description,
msiv.primary_uom_code uom_code,
xxen_util.meaning(msiv.item_type,&apos;ITEM_TYPE&apos;,3) item_type,
msiv.inventory_item_status_code item_status,
xxen_util.meaning(msiv.planning_make_buy_code,&apos;MTL_PLANNING_MAKE_BUY&apos;,700) make_buy_code,
&amp;category_columns
(select max(xxen_util.meaning(cicd.rollup_source_type,&apos;CST_SOURCE_TYPE&apos;,700)) from cst_item_cost_details cicd, bom_resources br where cic.inventory_item_id=cicd.inventory_item_id and cic.organization_id=cicd.organization_id and cic.cost_type_id=cicd.cost_type_id and cicd.item_cost&lt;&gt;0 and cicd.cost_element_id=1 and cicd.resource_id=br.resource_id) rollup_source_type,
(select max(br.resource_code) from cst_item_cost_details cicd, bom_resources br where cic.inventory_item_id=cicd.inventory_item_id and cic.organization_id=cicd.organization_id and cic.cost_type_id=cicd.cost_type_id and cicd.item_cost&lt;&gt;0 and cicd.cost_element_id=1 and cicd.resource_id=br.resource_id) material_sub_element,
xxen_util.yes(case when exists (select null from bom_structures_b bsb where msiv.inventory_item_id=bsb.assembly_item_id and msiv.organization_id=bsb.organization_id and bsb.alternate_bom_designator is null) then &apos;Y&apos; end) bom,
xxen_util.yes((select &apos;Y&apos; from bom_operational_routings bor where msiv.inventory_item_id=bor.assembly_item_id and msiv.organization_id=bor.organization_id and bor.alternate_routing_designator is null)) routing,
xxen_util.yes(case when exists (select null from mrp_sr_assignments msa, mrp_assignment_sets mas, mrp_sr_receipt_org msro, mrp_sr_source_org msso where msiv.inventory_item_id=msa.inventory_item_id and msiv.organization_id=msa.organization_id and msa.assignment_set_id=mas.assignment_set_id and 2=2 and msa.sourcing_rule_id=msro.sourcing_rule_id and msro.sr_receipt_id=msso.sr_receipt_id and msso.source_organization_id is not null) then &apos;Y&apos; end) sourcing_rule,
xxen_util.meaning(cic.based_on_rollup_flag,&apos;CST_BONROLLUP_VAL&apos;,700) based_on_rollup,
xxen_util.yes(decode(cic.inventory_asset_flag,1,&apos;Y&apos;)) inventory_asset,
gl.currency_code,
cic.item_cost,
nvl((select sum(cicd.item_cost) from cst_item_cost_details cicd, bom_resources br where cic.inventory_item_id=cicd.inventory_item_id and cic.organization_id=cicd.organization_id and cic.cost_type_id=cicd.cost_type_id and cicd.item_cost&lt;&gt;0 and cicd.cost_element_id=1 and cicd.level_type=1 and cicd.rollup_source_type=1 and cicd.resource_id=br.resource_id),0) manual_material_costs,
nvl((select sum(cicd.item_cost) from cst_item_cost_details cicd where cic.inventory_item_id=cicd.inventory_item_id and cic.organization_id=cicd.organization_id and cic.cost_type_id=cicd.cost_type_id and cicd.item_cost&lt;&gt;0 and cicd.cost_element_id=1 and cicd.level_type=2 and cicd.rollup_source_type=3),0) rolled_up_material_costs,
nvl((select sum(cicd.item_cost) from cst_item_cost_details cicd where cic.inventory_item_id=cicd.inventory_item_id and cic.organization_id=cicd.organization_id and cic.cost_type_id=cicd.cost_type_id and cicd.item_cost&lt;&gt;0 and cicd.cost_element_id&lt;&gt;1 and cicd.level_type=1 and cicd.rollup_source_type=1),0) manual_other_costs,
nvl((select sum(cicd.item_cost) from cst_item_cost_details cicd where cic.inventory_item_id=cicd.inventory_item_id and cic.organization_id=cicd.organization_id and cic.cost_type_id=cicd.cost_type_id and cicd.item_cost&lt;&gt;0 and cicd.cost_element_id&lt;&gt;1 and cicd.rollup_source_type in (2,3)),0) rolled_up_other_costs,
(select sum(moqd.primary_transaction_quantity) from mtl_onhand_quantities_detail moqd where msiv.inventory_item_id=moqd.inventory_item_id and msiv.organization_id=moqd.organization_id and moqd.is_consigned=2) onhand_quantity,
cic.creation_date cost_creation_date,
msiv.creation_date item_creation_date
from
mtl_parameters mp,
hr_organization_information hoi,
hr_all_organization_units haou,
hr_all_organization_units_vl haouv_ou,
gl_ledgers gl,
cst_item_costs cic,
cst_cost_types cct,
mtl_system_items_vl msiv
where
1=1 and
3=3 and
mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;INV&apos;) and
mp.organization_id&lt;&gt;mp.master_organization_id and
mp.organization_id=hoi.organization_id and
hoi.org_information_context=&apos;Accounting Information&apos; and
hoi.organization_id=haou.organization_id and
sysdate&lt;nvl(haou.date_to,sysdate+1) and
to_number(hoi.org_information3)=haouv_ou.organization_id and
to_number(hoi.org_information1)=gl.ledger_id and
mp.organization_id=cic.organization_id and
cic.cost_type_id=cct.cost_type_id and
cic.based_on_rollup_flag=1 and
cic.organization_id=msiv.organization_id and
cic.inventory_item_id=msiv.inventory_item_id and
msiv.inventory_item_status_code&lt;&gt;&apos;Inactive&apos; and
exists (select null from cst_item_cost_details cicd where cic.inventory_item_id=cicd.inventory_item_id and cic.organization_id=cicd.organization_id and cic.cost_type_id=cicd.cost_type_id and cicd.item_cost&lt;&gt;0 and cicd.cost_element_id=1 and cicd.level_type=1 and cicd.rollup_source_type=1) and
exists (select null from cst_item_cost_details cicd where cic.inventory_item_id=cicd.inventory_item_id and cic.organization_id=cicd.organization_id and cic.cost_type_id=cicd.cost_type_id and cicd.item_cost&lt;&gt;0 and cicd.cost_element_id=1 and cicd.level_type=2 and cicd.rollup_source_type=3) and
(
exists (select null from bom_structures_b bsb where cic.inventory_item_id=bsb.assembly_item_id and cic.organization_id=bsb.organization_id and bsb.alternate_bom_designator is null) or
exists (select null from bom_operational_routings bor where cic.inventory_item_id=bor.assembly_item_id and cic.organization_id=bor.organization_id) or
exists (select null from mrp_sr_assignments msa, mrp_assignment_sets mas, mrp_sr_receipt_org msro, mrp_sr_source_org msso where cic.inventory_item_id=msa.inventory_item_id and cic.organization_id=msa.organization_id and msa.assignment_set_id=mas.assignment_set_id and 2=2 and msa.sourcing_rule_id=msro.sourcing_rule_id and msro.sr_receipt_id=msso.sr_receipt_id and msso.source_organization_id is not null)
)
order by
ledger,
operating_unit,
org_code,
cct.cost_type,
item_number</SQL_TEXT>
  <VERSION_COMMENTS>Rebuilt to the Blitz Report SQL standards. Onhand Quantity in the primary unit of measure, flags show Yes or blank. Organization security from the Blitz Report organization access, with operating unit and ledger security through profiles Blitz Report Use Operating Unit Security and Blitz Report Use Ledger Security.</VERSION_COMMENTS>
  <REPORT_TRANSLATIONS>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>US</LANGUAGE>
    <REPORT_NAME>CAC User-Defined and Rolled Up Costs</REPORT_NAME>
    <DESCRIPTION>Use this report to find items with both user-defined (manually entered) and rolled up costs, for material and other cost elements.  Useful to find rolled up assemblies where the item costs have been accidentally doubled-up.

Parameters:
===========
Cost Type: enter the cost type to report (mandatory).
Category Set 1:  any item category you wish, typically the Cost or Product Line category set (optional).
Category Set 2:  any item category you wish, typically the Inventory category set (optional).
Assignment Set:  enter the sourcing rule assignment set, used when transferring items between inventory organizations on internal requisitions.
Item Number:  enter the specific item number(s) you wish to report (optional).
Organization Code:  enter the specific inventory organization(s) you wish to report (optional).
Operating Unit:  enter the specific operating unit(s) you wish to report (optional).
Ledger:  enter the specific ledger(s) you wish to report (optional).

/* +=============================================================================+
-- |  Copyright 2010-2023 Douglas Volz Consulting, Inc.
-- |  All rights reserved.
-- |  Permission to use this code is granted provided the original author is
-- |  acknowledged.  No warranties, express or otherwise is included in this
-- |  permission.
-- +=============================================================================+
-- |
-- |  Original Author: Douglas Volz (doug@volzconsulting.com)
-- |
-- |  Program Name:  xxx_user_defined_rolled_up_cost_rept.sql
-- | 
-- |  Version Modified on Modified  by   Description
-- |  ======= =========== ============== =========================================
-- |  1.0     29 Dec 2010 Douglas Volz   Create new report to find items with
-- |                                     both manually entered and rolled up material costs
-- |  1.1     24 May 2011 Douglas Volz   Bug fix for the resource code column
-- |  1.2     19 Oct 2019 Douglas Volz   Add columns for non-material costs
-- |  1.3     27 Jan 2020 Douglas Volz   Added Operating_Unit parameter and outer
-- |                                     join for Item_Type.
-- |  1.4     05 May 2021 Douglas Volz   Modify for multi-language tables.
-- |  1.5     12 Jan 2023 Douglas Volz   Correction for definition of manually entered 
-- |                                     material costs.
-- |  1.6     20 Nov 2023 Douglas Volz   Fix for Manual Other Costs and Rolled Up Other Costs columns.
-- |  1.7     05 Dec 2023 Douglas Volz   Added G/L and Operating Unit security restrictions.
-- |  1.8     07 Jan 2024 Douglas Volz   Add onhand quantities, to help find valuation issues.  Remove tabs.
-- |  1.10    23 Oct 2026 Andy Haack    Rebuilt to the Blitz Report SQL standards.
-- +=============================================================================+*/</DESCRIPTION>
   </REPORT_TRANSLATIONS_ROW>
  </REPORT_TRANSLATIONS>
  <CATEGORY_ASSIGNMENTS>
   <CATEGORY_ASSIGNMENTS_ROW>
    <CATEGORY>Enginatics</CATEGORY>
   </CATEGORY_ASSIGNMENTS_ROW>
  </CATEGORY_ASSIGNMENTS>
  <ANCHORS>
   <ANCHORS_ROW>
    <ANCHOR>&amp;category_columns</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>1=1</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>2=2</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>3=3</ANCHOR>
   </ANCHORS_ROW>
  </ANCHORS>
  <PARAMETERS>
   <PARAMETERS_ROW>
    <SORT_ORDER>1</SORT_ORDER>
    <DISPLAY_SEQUENCE>10</DISPLAY_SEQUENCE>
    <ANCHOR>1=1</ANCHOR>
    <SQL_TEXT>cct.cost_type=:p_cost_type</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>CST Cost Type (No AvgRates)</LOV_NAME>
    <LOV_GUID>91D3167A335B1204E053BB6B63585EE3</LOV_GUID>
    <LOV_QUERY_DSP>select
cct.cost_type value,
cct.description
from
cst_cost_types cct
where
nvl(cct.disable_date,sysdate)&gt;=sysdate and
cct.cost_type_id not in (select mp.avg_rates_cost_type_id from mtl_parameters mp where mp.avg_rates_cost_type_id is not null)
order by cct.cost_type desc</LOV_QUERY_DSP>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Cost Type</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>2</SORT_ORDER>
    <DISPLAY_SEQUENCE>20</DISPLAY_SEQUENCE>
    <ANCHOR>&amp;category_columns</ANCHOR>
    <SQL_TEXT>select xxen_util.item_category_columns(p_category_set_name=&gt;&apos;&lt;parameter_value&gt;&apos;) sql_text from dual</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>INV Category Set</LOV_NAME>
    <LOV_GUID>8E2FF36EDECA79D2E0530100007F1FF2</LOV_GUID>
    <LOV_QUERY_DSP>select distinct
mcsv.category_set_name value,
mcsv.description
from
mtl_category_sets_vl mcsv
where
:$flex$.organization_code is null or
mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)=&apos;Y&apos; and mp.organization_id=mic.organization_id)
order by
mcsv.category_set_name</LOV_QUERY_DSP>
    <DEFAULT_VALUE>coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=11))</DEFAULT_VALUE>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Category Set 1</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>3</SORT_ORDER>
    <DISPLAY_SEQUENCE>30</DISPLAY_SEQUENCE>
    <ANCHOR>&amp;category_columns</ANCHOR>
    <SQL_TEXT>select xxen_util.item_category_columns(p_category_set_name=&gt;&apos;&lt;parameter_value&gt;&apos;) sql_text from dual</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>INV Category Set</LOV_NAME>
    <LOV_GUID>8E2FF36EDECA79D2E0530100007F1FF2</LOV_GUID>
    <LOV_QUERY_DSP>select distinct
mcsv.category_set_name value,
mcsv.description
from
mtl_category_sets_vl mcsv
where
:$flex$.organization_code is null or
mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)=&apos;Y&apos; and mp.organization_id=mic.organization_id)
order by
mcsv.category_set_name</LOV_QUERY_DSP>
    <DEFAULT_VALUE>coalesce(xxen_util.previous_parameter_value(:parameter_id),(select mdsv.category_set_name from mtl_default_sets_view mdsv where mdsv.functional_area_id=5))</DEFAULT_VALUE>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Category Set 2</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>4</SORT_ORDER>
    <DISPLAY_SEQUENCE>40</DISPLAY_SEQUENCE>
    <ANCHOR>&amp;category_columns</ANCHOR>
    <SQL_TEXT>select xxen_util.item_category_columns(p_category_set_name=&gt;&apos;&lt;parameter_value&gt;&apos;) sql_text from dual</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>INV Category Set</LOV_NAME>
    <LOV_GUID>8E2FF36EDECA79D2E0530100007F1FF2</LOV_GUID>
    <LOV_QUERY_DSP>select distinct
mcsv.category_set_name value,
mcsv.description
from
mtl_category_sets_vl mcsv
where
:$flex$.organization_code is null or
mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)=&apos;Y&apos; and mp.organization_id=mic.organization_id)
order by
mcsv.category_set_name</LOV_QUERY_DSP>
    <DEFAULT_VALUE>xxen_util.previous_parameter_value(:parameter_id)</DEFAULT_VALUE>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Category Set 3</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>5</SORT_ORDER>
    <DISPLAY_SEQUENCE>50</DISPLAY_SEQUENCE>
    <ANCHOR>2=2</ANCHOR>
    <SQL_TEXT>mas.assignment_set_name=:p_assignment_set</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>MRP Assignment Set</LOV_NAME>
    <LOV_GUID>92EDCA82621F0FBDE053BB6B6358DEED</LOV_GUID>
    <LOV_QUERY_DSP>select
mas.assignment_set_name value,
mas.description 
from
mrp_assignment_sets mas 
order by
mas.assignment_set_name</LOV_QUERY_DSP>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Assignment Set</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>msiv.concatenated_segments=:p_item_number</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>INV Item Number (costing enabled only items)</LOV_NAME>
    <LOV_GUID>EC5CECA520510FC8E0530100007F5EB3</LOV_GUID>
    <LOV_QUERY_DSP>select distinct
msiv.concatenated_segments value,
msiv.description
from
mtl_system_items_vl msiv,
mtl_parameters mp
where
msiv.organization_id = mp.organization_id and
(xxen_util.contains(:$flex$.organization_code,mp.organization_code) = &apos;Y&apos; or
 (:$flex$.organization_code is null and
  mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;INV&apos;)
 )
) and
(msiv.costing_enabled_flag=&apos;Y&apos; or mp.process_enabled_flag=&apos;Y&apos;)
order by
msiv.concatenated_segments</LOV_QUERY_DSP>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Item Number</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>7</SORT_ORDER>
    <DISPLAY_SEQUENCE>70</DISPLAY_SEQUENCE>
    <ANCHOR>3=3</ANCHOR>
    <SQL_TEXT>mp.organization_code=:p_org_code</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV custom</PARAMETER_TYPE_DSP>
    <LOV_QUERY_DSP>select
ood.organization_code value,
ood.organization_name description
from
org_organization_definitions ood
where
ood.organization_id not in (select mp.organization_id from mtl_parameters mp where mp.organization_id=mp.master_organization_id) and
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>select mp.organization_code from mtl_parameters mp where mp.organization_id=fnd_profile.value(&apos;MFG_ORGANIZATION_ID&apos;)</DEFAULT_VALUE>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Organization Code</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>8</SORT_ORDER>
    <DISPLAY_SEQUENCE>80</DISPLAY_SEQUENCE>
    <ANCHOR>1=1</ANCHOR>
    <SQL_TEXT>haouv_ou.name=:p_operating_unit</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>INV Operating Unit</LOV_NAME>
    <LOV_GUID>9B9627743E84607DE053BB6B635805FB</LOV_GUID>
    <LOV_QUERY_DSP>select
haouv.name value,
&apos;Operating Unit&apos; description
from
hr_all_organization_units_vl haouv
where
haouv.organization_id in
(
select
to_number(hoi.org_information3)
from
mtl_parameters mp,
hr_organization_information hoi,
hr_all_organization_units haou
where
mp.organization_id&lt;&gt;mp.master_organization_id and
mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;INV&apos;) and
mp.organization_id=hoi.organization_id and
hoi.org_information_context=&apos;Accounting Information&apos; and
(:$flex$.ledger is null or to_number(hoi.org_information1) in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)=&apos;Y&apos;)) and
hoi.organization_id=haou.organization_id and
nvl(haou.date_to,sysdate)&gt;=sysdate
)
order by 1</LOV_QUERY_DSP>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Operating Unit</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>9</SORT_ORDER>
    <DISPLAY_SEQUENCE>90</DISPLAY_SEQUENCE>
    <ANCHOR>1=1</ANCHOR>
    <SQL_TEXT>gl.name=:p_ledger</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>GL Ledger (inv org related)</LOV_NAME>
    <LOV_GUID>8E2FF36EDF3A79D2E0530100007F1FF2</LOV_GUID>
    <LOV_QUERY_DSP>select
gl.name value,
fifsv.id_flex_structure_name||&apos;: &apos;||gl.description description
from
gl_ledgers gl,
fnd_id_flex_structures_vl fifsv
where
gl.ledger_id in (select ood.set_of_books_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate)&gt;=sysdate and ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;INV&apos;)) and
(:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)=&apos;Y&apos;) and
gl.chart_of_accounts_id=fifsv.id_flex_num and
fifsv.id_flex_code=&apos;GL#&apos; and
fifsv.application_id=101
order by
fifsv.id_flex_structure_name,
gl.name</LOV_QUERY_DSP>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Ledger</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
  </PARAMETERS>
  <TEMPLATES>
   <TEMPLATES_ROW>
    <GUID>179BBEF0E93DB29DA0862FDD053C6AE4</GUID>
    <TEMPLATE_NAME>Count by Org by Cost Type</TEMPLATE_NAME>
    <DYNAMIC_COLUMNS>Y</DYNAMIC_COLUMNS>
    <OWNER>MFG</OWNER>
    <TEMPLATE_COLUMNS>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>21</DISPLAY_SEQUENCE>
      <COLUMN_NAME>BASED_ON_ROLLUP</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>18</DISPLAY_SEQUENCE>
      <COLUMN_NAME>BOM</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>4</DISPLAY_SEQUENCE>
      <COLUMN_NAME>COST_TYPE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>23</DISPLAY_SEQUENCE>
      <COLUMN_NAME>CURRENCY_CODE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>14</DISPLAY_SEQUENCE>
      <COLUMN_NAME>Class</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>13</DISPLAY_SEQUENCE>
      <COLUMN_NAME>Family</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>22</DISPLAY_SEQUENCE>
      <COLUMN_NAME>INVENTORY_ASSET</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>24</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ITEM_COST</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>29</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ITEM_CREATION_DATE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>6</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ITEM_DESCRIPTION</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>5</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ITEM_NUMBER</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>9</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ITEM_STATUS</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>8</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ITEM_TYPE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>15</DISPLAY_SEQUENCE>
      <COLUMN_NAME>Inv.Items Description</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>1</DISPLAY_SEQUENCE>
      <COLUMN_NAME>LEDGER</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>10</DISPLAY_SEQUENCE>
      <COLUMN_NAME>MAKE_BUY_CODE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>25</DISPLAY_SEQUENCE>
      <COLUMN_NAME>MANUAL_MATERIAL_COSTS</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>27</DISPLAY_SEQUENCE>
      <COLUMN_NAME>MANUAL_OTHER_COSTS</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>17</DISPLAY_SEQUENCE>
      <COLUMN_NAME>MATERIAL_SUB_ELEMENT</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>2</DISPLAY_SEQUENCE>
      <COLUMN_NAME>OPERATING_UNIT</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>3</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ORG_CODE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>11</DISPLAY_SEQUENCE>
      <COLUMN_NAME>Product Cat</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>12</DISPLAY_SEQUENCE>
      <COLUMN_NAME>Product Description</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>26</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ROLLED_UP_MATERIAL_COSTS</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>28</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ROLLED_UP_OTHER_COSTS</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>16</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ROLLUP_SOURCE_TYPE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>19</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ROUTING</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>20</DISPLAY_SEQUENCE>
      <COLUMN_NAME>SOURCING_RULE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>7</DISPLAY_SEQUENCE>
      <COLUMN_NAME>UOM_CODE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
    </TEMPLATE_COLUMNS>
    <TEMPLATE_PIVOT>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>COST_TYPE</COLUMN_NAME>
      <FIELD_TYPE>ROW</FIELD_TYPE>
      <DISPLAY_SEQUENCE>4</DISPLAY_SEQUENCE>
     </TEMPLATE_PIVOT_ROW>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>ITEM_NUMBER</COLUMN_NAME>
      <FIELD_TYPE>VALUE</FIELD_TYPE>
      <DISPLAY_SEQUENCE>1</DISPLAY_SEQUENCE>
      <AGGREGATION>COUNT</AGGREGATION>
     </TEMPLATE_PIVOT_ROW>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>LEDGER</COLUMN_NAME>
      <FIELD_TYPE>ROW</FIELD_TYPE>
      <DISPLAY_SEQUENCE>1</DISPLAY_SEQUENCE>
     </TEMPLATE_PIVOT_ROW>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>OPERATING_UNIT</COLUMN_NAME>
      <FIELD_TYPE>ROW</FIELD_TYPE>
      <DISPLAY_SEQUENCE>2</DISPLAY_SEQUENCE>
     </TEMPLATE_PIVOT_ROW>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>ORG_CODE</COLUMN_NAME>
      <FIELD_TYPE>ROW</FIELD_TYPE>
      <DISPLAY_SEQUENCE>3</DISPLAY_SEQUENCE>
     </TEMPLATE_PIVOT_ROW>
    </TEMPLATE_PIVOT>
    <TEMPLATE_SHARED_STRINGS>
    </TEMPLATE_SHARED_STRINGS>
    <TEMPLATE_PARAMETER_DEFAULTS>
    </TEMPLATE_PARAMETER_DEFAULTS>
    <TEMPLATE_STYLES>
    </TEMPLATE_STYLES>
    <TEMPLATE_SHARING>
     <TEMPLATE_SHARING_ROW>
      <SHARING_LEVEL>S</SHARING_LEVEL>
      <LEVEL_VALUE>Site</LEVEL_VALUE>
     </TEMPLATE_SHARING_ROW>
    </TEMPLATE_SHARING>
    <PARAMETER_EXCLUSION>
    </PARAMETER_EXCLUSION>
    <TEMPLATE_CONDITIONS>
    </TEMPLATE_CONDITIONS>
   </TEMPLATES_ROW>
  </TEMPLATES>
  <DEFAULT_TEMPLATES>
   <DEFAULT_TEMPLATES_ROW>
    <TEMPLATE_GUID>179BBEF0E93DB29DA0862FDD053C6AE4</TEMPLATE_GUID>
   </DEFAULT_TEMPLATES_ROW>
  </DEFAULT_TEMPLATES>
  <UPLOAD_COLUMNS>
  </UPLOAD_COLUMNS>
  <UPLOAD_PARAMETERS>
  </UPLOAD_PARAMETERS>
  <UPLOAD_SQLS>
  </UPLOAD_SQLS>
 </REPORTS_ROW>
</REPORTS>
</ROOT>
