<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 Cost vs. Planning Item Controls -->
 <REPORTS_ROW>
  <GUID>C1B7B7403A5D7839E053BB6B6358045B</GUID><ENABLED>Y</ENABLED>
  <SQL_TEXT>select
decode(rowgen.column_value,
1,&apos;Based on Rollup Yes - No BOMs&apos;,
2,&apos;Based on Rollup Yes - No Routing&apos;,
3,&apos;Based on Rollup Yes - No Rollup&apos;,
4,&apos;Based on Rollup Yes - Buy Items&apos;,
5,&apos;Based on Rollup No - With BOMs&apos;,
6,&apos;Based on Rollup No - With Sourcing Rules&apos;,
7,&apos;Based on Rollup No - Make Items&apos;,
8,&apos;Lot-Based Resources With Lot Size 1&apos;,
9,&apos;BOMs With No Components&apos;,
10,&apos;Item Costing vs. Item Asset Controls&apos;,
11,&apos;Item Asset vs. Costing Asset Controls&apos;,
12,&apos;Based on Rollup No - Defaulted Costs&apos;) report_type,
nvl(gl.short_name,gl.name) ledger,
haouv_ou.name operating_unit,
x.organization_code org_code,
x.cost_type,
x.item_number,
x.item_description,
x.uom_code,
xxen_util.meaning(x.item_type,&apos;ITEM_TYPE&apos;,3) item_type,
x.item_status,
xxen_util.meaning(x.planning_make_buy_code,&apos;MTL_PLANNING_MAKE_BUY&apos;,700) make_buy_code,
&amp;category_columns
xxen_util.yes(x.bom) bom,
xxen_util.yes(x.routing) routing,
xxen_util.yes(x.sourcing_rule) sourcing_rule,
xxen_util.meaning(x.based_on_rollup_flag,&apos;CST_BONROLLUP_VAL&apos;,700) based_on_rollup,
xxen_util.yes(decode(x.defaulted_flag,1,&apos;Y&apos;)) defaulted_flag,
xxen_util.yes(x.costing_enabled_flag) costing_enabled,
xxen_util.yes(x.inventory_asset_flag) item_inventory_asset,
xxen_util.yes(decode(x.cost_asset_flag,1,&apos;Y&apos;)) cost_inventory_asset,
x.std_lot_size item_std_lot_size,
x.cost_lot_size,
gl.currency_code,
x.item_cost,
x.onhand_quantity,
x.cost_creation_date,
x.item_creation_date
from
(
select
mp.organization_code,
cct.cost_type,
cic.cost_type_id,
msiv.organization_id,
msiv.inventory_item_id,
msiv.concatenated_segments item_number,
msiv.description item_description,
msiv.primary_uom_code uom_code,
msiv.item_type,
misv.inventory_item_status_code_tl item_status,
msiv.planning_make_buy_code,
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; else &apos;N&apos; end bom,
case when exists (select null 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) then &apos;Y&apos; else &apos;N&apos; end routing,
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 3=3 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; else &apos;N&apos; end sourcing_rule,
cic.based_on_rollup_flag,
cic.defaulted_flag,
msiv.costing_enabled_flag,
msiv.inventory_asset_flag,
cic.inventory_asset_flag cost_asset_flag,
msiv.std_lot_size,
cic.lot_size cost_lot_size,
cic.item_cost,
(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,
cst_item_costs cic,
cst_cost_types cct,
mtl_system_items_vl msiv,
mtl_item_status_vl misv
where
2=2 and
4=4 and
mp.organization_id&lt;&gt;mp.master_organization_id and
mp.organization_id=cic.organization_id and
cic.cost_type_id=cct.cost_type_id 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
msiv.inventory_item_status_code=misv.inventory_item_status_code
) x,
table(xxen_util.rowgen(12)) rowgen,
hr_organization_information hoi,
hr_all_organization_units haou,
hr_all_organization_units_vl haouv_ou,
gl_ledgers gl
where
1=1 and
x.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
case rowgen.column_value
when 1 then case when x.costing_enabled_flag=&apos;Y&apos; and x.planning_make_buy_code=1 and x.based_on_rollup_flag=1 and x.bom=&apos;N&apos; then &apos;Y&apos; end
when 2 then case when x.costing_enabled_flag=&apos;Y&apos; and x.planning_make_buy_code=1 and x.based_on_rollup_flag=1 and x.routing=&apos;N&apos; then &apos;Y&apos; end
when 3 then case when x.costing_enabled_flag=&apos;Y&apos; and (x.planning_make_buy_code=1 or x.planning_make_buy_code=2 and x.sourcing_rule=&apos;Y&apos;) and x.based_on_rollup_flag=1 and not exists (select null from cst_item_cost_details cicd where x.inventory_item_id=cicd.inventory_item_id and x.organization_id=cicd.organization_id and x.cost_type_id=cicd.cost_type_id and cicd.rollup_source_type=3) then &apos;Y&apos; end
when 4 then case when x.costing_enabled_flag=&apos;Y&apos; and x.planning_make_buy_code=2 and x.based_on_rollup_flag=1 and x.sourcing_rule=&apos;N&apos; then &apos;Y&apos; end
when 5 then case when x.costing_enabled_flag=&apos;Y&apos; and x.planning_make_buy_code=1 and x.based_on_rollup_flag=2 and (x.bom=&apos;Y&apos; or x.routing=&apos;Y&apos;) then &apos;Y&apos; end
when 6 then case when x.costing_enabled_flag=&apos;Y&apos; and x.based_on_rollup_flag=2 and x.sourcing_rule=&apos;Y&apos; then &apos;Y&apos; end
when 7 then case when x.costing_enabled_flag=&apos;Y&apos; and x.planning_make_buy_code=1 and x.based_on_rollup_flag=2 then &apos;Y&apos; end
when 8 then case when x.costing_enabled_flag=&apos;Y&apos; and nvl(x.cost_lot_size,1)=1 and x.planning_make_buy_code=1 and exists (select null from cst_item_cost_details cicd where x.inventory_item_id=cicd.inventory_item_id and x.organization_id=cicd.organization_id and x.cost_type_id=cicd.cost_type_id and cicd.basis_type=2) then &apos;Y&apos; end
when 9 then case when x.costing_enabled_flag=&apos;Y&apos; and x.based_on_rollup_flag=1 and x.bom=&apos;Y&apos; and not exists (select null from bom_structures_b bsb, bom_components_b bcb where x.inventory_item_id=bsb.assembly_item_id and x.organization_id=bsb.organization_id and bsb.bill_sequence_id=bcb.bill_sequence_id and bcb.effectivity_date&lt;=sysdate and nvl(bcb.disable_date,sysdate+1)&gt;sysdate) then &apos;Y&apos; end
when 10 then case when x.costing_enabled_flag&lt;&gt;x.inventory_asset_flag then &apos;Y&apos; end
when 11 then case when x.inventory_asset_flag&lt;&gt;decode(x.cost_asset_flag,1,&apos;Y&apos;,&apos;N&apos;) then &apos;Y&apos; end
when 12 then case when x.defaulted_flag=1 and x.based_on_rollup_flag=2 then &apos;Y&apos; end
end=&apos;Y&apos;
order by
report_type,
ledger,
operating_unit,
org_code,
x.cost_type,
x.item_number</SQL_TEXT>
  <VERSION_COMMENTS>Rebuilt to the Blitz Report SQL standards: the checks on BOMs, routings and sourcing rules return their items (the BOM, routing and sourcing values never matched them), Onhand Quantity in the primary unit of measure, flags show Yes or blank. Setup report without organization security, so that every user sees the setup of all organizations.</VERSION_COMMENTS>
  <REPORT_TRANSLATIONS>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>US</LANGUAGE>
    <REPORT_NAME>CAC Cost vs. Planning Item Controls</REPORT_NAME>
    <DESCRIPTION>Compare item make/buy controls vs. costing based on rollup controls, to find errors with your cost rollup results.  There are twelve included reports, see below description for more information.

Available Reports:
1.  Based on Rollup Yes - No BOMS
     Find make items where the item is set to be rolled up but there are no BOMs.  May roll up to a zero cost.
2.  Based on Rollup Yes - No Routing
     Find make items costs are based on the cost rollup, but there are no routings.
3.  Based on Rollup Yes - No Rollup
     Find make items where it is set to be rolled up but there are no rolled up costs
4.  Based on Rollup Yes - Buy Items
     Find buy items where the item is set to rolled up 
5.  Based on Rollup No - With BOMS
     Find make items where the item is not set to be rolled up but BOMS or routings exist.
6.  Based on Rollup No - With Sourcing Rules
     Find buy items where costs are not based on the cost rollup, but sourcing rules exist.
7.  Based on Rollup No - Make Items
     Find make items where the item is not set to rolled up, whether or not BOMs or routings exist. 
8.  Lot-Based Resources With Lot Size One
     Find make items where there are charges based on Lot but the lot size is one.  Duplicates the setup charges for each item you make.
9.  BOMs With No Components
     Find make items with BOMS that have no components.
10.  Item Costing vs. Item Asset Controls
        Find items where the item master costed flag (costed enabled) and the item asset flag do not match.
11.  Item Asset vs. Costing Asset Controls
        Find items where the item master asset and the costing asset flags do not match.
 12.  Based on Rollup No - Defaulted Costs
        Find items where the item is not rolled up but the defaulted flag says Yes

Parameters:
===========
Cost Type:  the Frozen or Pending cost type you wish to report (mandatory).
Assignment Set:  for your organization sourcing rules, enter an assignment set (optional).
Category Sets 1 - 3:  any item category you wish, typically the Cost or Product Line category sets (optional).
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 2008-2025 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.
-- +=============================================================================+
-- |  1.0     15 Oct 2008 Douglas Volz   Initial Coding
-- |  1.34    06 May 2021 Douglas Volz   Using a with statement, summarized report type queries for efficiency.
-- |  1.35    23 Apr 2022 Douglas Volz   Add new column &quot;Defaulted Costs&quot; and new report type &quot;Based on
-- |                                     Rollup No - Defaulted Costs&quot;.  The defaulted flag indicates
-- |                                     whether the cost of the item is defaulted from the default cost
-- |                                     type during cost rollup.
-- |  1.36   07 Jan 2024 Douglas Volz    Add current onhand quantities, to help find valuation issues.  Remove
-- |                                     tabs, add operating unit and ledger security and inventory access controls.
-- |  1.37   10 Apr 2025 Douglas Volz    Added in new GL and OU security profiles.
-- |  1.38   13 Apr 2025 Douglas Volz    Fix ORA-43916 Collation Error for character expressions &apos;Y&apos;, &apos;N&apos;.
-- |  1.39    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_ROW>
    <ANCHOR>4=4</ANCHOR>
   </ANCHORS_ROW>
  </ANCHORS>
  <PARAMETERS>
   <PARAMETERS_ROW>
    <SORT_ORDER>1</SORT_ORDER>
    <DISPLAY_SEQUENCE>10</DISPLAY_SEQUENCE>
    <ANCHOR>2=2</ANCHOR>
    <SQL_TEXT>cct.cost_type=:p_cost_type</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>CST Cost Type</LOV_NAME>
    <LOV_GUID>92C63F4C20A21E85E053BB6B63585CC8</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.organization_id is null or
 cct.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;INV&apos;) 
)
order by
cct.cost_type</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;,p_table_alias=&gt;&apos;x&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;,p_table_alias=&gt;&apos;x&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;,p_table_alias=&gt;&apos;x&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>3=3</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>2=2</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>4=4</ANCHOR>
    <SQL_TEXT>mp.organization_code=:p_org_code</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>INV Organization Code (all)</LOV_NAME>
    <LOV_GUID>8E2FF36EDEA279D2E0530100007F1FF2</LOV_GUID>
    <LOV_QUERY_DSP>select
ood.organization_code value,
ood.organization_name||&apos; (&apos;||ftv.territory_short_name||&apos;, &apos;||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,&apos;, &apos;||mp0.organization_code)||&apos;)&apos; description
from
org_organization_definitions ood,
mtl_parameters mp,
mtl_parameters mp0,
hr_all_organization_units haou,
hr_locations_all hla,
fnd_territories_vl ftv
where
ood.organization_id=mp.organization_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)=&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 and
mp.master_organization_id=mp0.organization_id and
ood.organization_id=haou.organization_id(+) and
haou.location_id=hla.location_id(+) and
hla.country=ftv.territory_code(+)
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>HR Operating Unit (all)</LOV_NAME>
    <LOV_GUID>8E2FF36EDE8879D2E0530100007F1FF2</LOV_GUID>
    <LOV_QUERY_DSP>select
hou.name value,
null description
from
hr_operating_units hou
where
sysdate between hou.date_from and nvl(hou.date_to,sysdate) and
(:$flex$.ledger is null or hou.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)=&apos;Y&apos;))
order by
hou.name</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 (all)</LOV_NAME>
    <LOV_GUID>A95C471001B20DD1E0530100007FBF83</LOV_GUID>
    <LOV_QUERY_DSP>select
gl.name value,
fifsv.id_flex_structure_name||&apos;: &apos;||decode(gl.ledger_category_code,&apos;NONE&apos;,xxen_util.meaning(gl.object_type_code,&apos;LEDGERS&apos;,101),xxen_util.meaning(gl.ledger_category_code,&apos;GL_ASF_LEDGER_CATEGORY&apos;,101))||&apos;: &apos;||gl.description description
from
gl_ledgers gl,
fnd_id_flex_structures_vl fifsv
where
(:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)=&apos;Y&apos;) and
(:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,&apos;GL_ASF_LEDGER_CATEGORY&apos;,101,&apos;Y&apos;)) and
gl.object_type_code=&apos;L&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,
decode(gl.ledger_category_code,&apos;NONE&apos;,1,&apos;PRIMARY&apos;,2,&apos;SECONDARY&apos;,3,&apos;ALC&apos;,4),
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>3B553ECA773852E56D3B7976734CA583</GUID>
    <TEMPLATE_NAME>Count by Report, Org and Cost Type</TEMPLATE_NAME>
    <DYNAMIC_COLUMNS>Y</DYNAMIC_COLUMNS>
    <DESCRIPTION>Count by Report Type, Ledger, OU, Org Code and Cost Type</DESCRIPTION>
    <OWNER>MFG</OWNER>
    <TEMPLATE_COLUMNS>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>20</DISPLAY_SEQUENCE>
      <COLUMN_NAME>BASED_ON_ROLLUP</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>17</DISPLAY_SEQUENCE>
      <COLUMN_NAME>BOM</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>22</DISPLAY_SEQUENCE>
      <COLUMN_NAME>COSTING_ENABLED</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>30</DISPLAY_SEQUENCE>
      <COLUMN_NAME>COST_CREATION_DATE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>24</DISPLAY_SEQUENCE>
      <COLUMN_NAME>COST_INVENTORY_ASSET</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>26</DISPLAY_SEQUENCE>
      <COLUMN_NAME>COST_LOT_SIZE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>5</DISPLAY_SEQUENCE>
      <COLUMN_NAME>COST_TYPE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>27</DISPLAY_SEQUENCE>
      <COLUMN_NAME>CURRENCY_CODE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>15</DISPLAY_SEQUENCE>
      <COLUMN_NAME>Class</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>21</DISPLAY_SEQUENCE>
      <COLUMN_NAME>DEFAULTED_FLAG</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>14</DISPLAY_SEQUENCE>
      <COLUMN_NAME>Family</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>28</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ITEM_COST</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>31</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ITEM_CREATION_DATE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>7</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ITEM_DESCRIPTION</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>23</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ITEM_INVENTORY_ASSET</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>6</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ITEM_NUMBER</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>10</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ITEM_STATUS</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>25</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ITEM_STD_LOT_SIZE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>9</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ITEM_TYPE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>16</DISPLAY_SEQUENCE>
      <COLUMN_NAME>Inv.Items Description</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>2</DISPLAY_SEQUENCE>
      <COLUMN_NAME>LEDGER</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>11</DISPLAY_SEQUENCE>
      <COLUMN_NAME>MAKE_BUY_CODE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>29</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ONHAND_QUANTITY</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>3</DISPLAY_SEQUENCE>
      <COLUMN_NAME>OPERATING_UNIT</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>4</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ORG_CODE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>12</DISPLAY_SEQUENCE>
      <COLUMN_NAME>Product Cat</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>13</DISPLAY_SEQUENCE>
      <COLUMN_NAME>Product Description</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>1</DISPLAY_SEQUENCE>
      <COLUMN_NAME>REPORT_TYPE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>18</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ROUTING</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>19</DISPLAY_SEQUENCE>
      <COLUMN_NAME>SOURCING_RULE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>8</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>5</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>2</DISPLAY_SEQUENCE>
     </TEMPLATE_PIVOT_ROW>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>OPERATING_UNIT</COLUMN_NAME>
      <FIELD_TYPE>ROW</FIELD_TYPE>
      <DISPLAY_SEQUENCE>3</DISPLAY_SEQUENCE>
     </TEMPLATE_PIVOT_ROW>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>ORG_CODE</COLUMN_NAME>
      <FIELD_TYPE>ROW</FIELD_TYPE>
      <DISPLAY_SEQUENCE>4</DISPLAY_SEQUENCE>
     </TEMPLATE_PIVOT_ROW>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>REPORT_TYPE</COLUMN_NAME>
      <FIELD_TYPE>ROW</FIELD_TYPE>
      <DISPLAY_SEQUENCE>1</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>3B553ECA773852E56D3B7976734CA583</TEMPLATE_GUID>
   </DEFAULT_TEMPLATES_ROW>
  </DEFAULT_TEMPLATES>
  <UPLOAD_COLUMNS>
  </UPLOAD_COLUMNS>
  <UPLOAD_PARAMETERS>
  </UPLOAD_PARAMETERS>
  <UPLOAD_SQLS>
  </UPLOAD_SQLS>
 </REPORTS_ROW>
</REPORTS>
</ROOT>
