<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 Subinventory Accounts Setup -->
 <REPORTS_ROW>
  <GUID>A3BFC166CC6C7F77E053BB6B6358CAD3</GUID><ENABLED>Y</ENABLED>
  <SQL_TEXT>select
nvl(gl.short_name,gl.name) ledger,
haouv_ou.name operating_unit,
mp.organization_code org_code,
haou.name organization_name,
(select ccg.cost_group from cst_cost_groups ccg where x.default_cost_group_id=ccg.cost_group_id) cost_group,
x.secondary_inventory_name subinventory,
xxen_util.yes(decode(x.asset_inventory,1,&apos;Y&apos;)) inventory_asset,
xxen_util.yes(decode(x.quantity_tracked,1,&apos;Y&apos;)) quantity_tracked,
decode(x.account_number,6,xxen_util.meaning(3,&apos;CST_ACCOUNT_TYPE&apos;,700),xxen_util.meaning(x.account_number,&apos;CST_COST_CODE_TYPE&apos;,700)) account_type,
&amp;segment_columns
x.creation_date,
x.disable_date,
xxen_util.user_name(x.created_by) created_by,
xxen_util.user_name(x.last_updated_by) last_updated_by
from
mtl_parameters mp,
hr_organization_information hoi,
hr_all_organization_units_vl haou,
hr_all_organization_units_vl haouv_ou,
gl_ledgers gl,
(
select
y.*,
rowgen.column_value account_number,
decode(rowgen.column_value,1,y.material_account,2,y.material_overhead_account,3,y.resource_account,4,y.outside_processing_account,5,y.overhead_account,6,y.expense_account) account_id
from
(
select
msi.organization_id,
msi.secondary_inventory_name,
msi.asset_inventory,
msi.quantity_tracked,
msi.default_cost_group_id,
msi.creation_date,
msi.disable_date,
msi.created_by,
msi.last_updated_by,
msi.material_account,
msi.material_overhead_account,
msi.resource_account,
msi.outside_processing_account,
msi.overhead_account,
msi.expense_account
from
mtl_parameters mp,
mtl_secondary_inventories msi
where
mp.organization_id=msi.organization_id and
nvl(mp.cost_group_accounting,2)=2 and
mp.primary_cost_method=1
union all
select
msi.organization_id,
msi.secondary_inventory_name,
msi.asset_inventory,
msi.quantity_tracked,
msi.default_cost_group_id,
msi.creation_date,
msi.disable_date,
msi.created_by,
msi.last_updated_by,
mp.material_account,
mp.material_overhead_account,
mp.resource_account,
mp.outside_processing_account,
mp.overhead_account,
mp.expense_account
from
mtl_parameters mp,
mtl_secondary_inventories msi
where
mp.organization_id=msi.organization_id and
nvl(mp.cost_group_accounting,2)=2 and
mp.primary_cost_method&lt;&gt;1
union all
select
msi.organization_id,
msi.secondary_inventory_name,
msi.asset_inventory,
msi.quantity_tracked,
msi.default_cost_group_id,
msi.creation_date,
msi.disable_date,
msi.created_by,
msi.last_updated_by,
ccga.material_account,
ccga.material_overhead_account,
ccga.resource_account,
ccga.outside_processing_account,
ccga.overhead_account,
ccga.expense_account
from
mtl_parameters mp,
mtl_secondary_inventories msi,
cst_cost_group_accounts ccga
where
mp.organization_id=msi.organization_id and
mp.cost_group_accounting=1 and
nvl(msi.default_cost_group_id,mp.default_cost_group_id)=ccga.cost_group_id
) y,
table(xxen_util.rowgen(6)) rowgen
) x,
gl_code_combinations gcc
where
1=1 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=x.organization_id and
x.account_id=gcc.code_combination_id(+)
order by
ledger,
operating_unit,
org_code,
subinventory,
x.account_number</SQL_TEXT>
  <VERSION_COMMENTS>Rebuilt to the Blitz Report SQL standards. Inventory Asset and Quantity Tracked show Yes or blank, Created By and Last Updated By show the user name with the person. 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 Subinventory Accounts Setup</REPORT_NAME>
    <DESCRIPTION>Report to show accounts used for the subinventories; these valuation and expense accounts are used with Standard Costing.

/* +=============================================================================+
-- |  Copyright 2009 - 22 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_subinv_setup_accts_rept.sql
-- |
-- |  Parameters:
-- |  p_org_code         -- Specific inventory organization you wish to report (optional)
-- |  p_operating_unit   -- Operating Unit you wish to report, leave blank for all
-- |                        operating units (optional) 
-- |  p_ledger           -- general ledger you wish to report, leave blank for all
-- |                        ledgers (optional)
-- | 
-- |  Description:
-- |  Report to show accounts used for the subinventories
-- | 
-- |  Version Modified on Modified  by   Description
-- |  ======= =========== ============== =========================================
-- |  1.0     24 Nov 2009 Douglas Volz   Initial Coding
-- |  1.1     28 Mar 2011 Douglas Volz   Minor column heading changes
-- |  1.2     30 Mar 2011 Douglas Volz   Minor column heading changes for Inv Asset,
-- |                                     added quantity tracked and disable date
-- |                                     columns
-- |  1.3     23 Dec 2014 Douglas Volz   Added DFFs for &quot;Use Item Type Accounts&quot;.
-- |                                     For OPM orgs, the ICP valuation reports use
-- |                                     this to indicate if the Item Type accounts
-- |                                     or the subinventory valuation accounts are 
-- |                                     displayed on the report. 
-- |  1.4     07 Oct 2015 Douglas Volz   Removed above DFFs for &quot;Use Item Type Accounts&quot;,
-- |                                     changed COA to match new client.  Also added
-- |                                     Cost Group Name and accounts. Replaced OOD
-- |                                     with mtl_parameters and mp.organization_name with
-- |                                     haou.name.  And removed prior client&apos;s organization
-- |                                     restrictions.   
-- |  1.5     11 Nov 2016 Douglas Volz   Modified chart of accounts for client 
-- |  1.6     28 Mar 2017 Douglas Volz   Added Creation Date, Last Update Date, Created
-- |                                     By, Last Updated By  
-- |  1.7     02 Feb 2020 Douglas Volz   Added Operating Unit and Org Code Parameters
-- |                                     and added outer join to gcc.code_combinations_id 
-- |  1.8     29 Apr 2020 Douglas Volz   Changed to multi-language views for the item
-- |                                     master, inventory orgs and operating units.
-- |  1.9     10 Jul 2022 Douglas Volz   Account Type column now uses a lookup code.
-- |  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;segment_columns</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>1=1</ANCHOR>
   </ANCHORS_ROW>
  </ANCHORS>
  <PARAMETERS>
   <PARAMETERS_ROW>
    <SORT_ORDER>1</SORT_ORDER>
    <DISPLAY_SEQUENCE>-100</DISPLAY_SEQUENCE>
    <ANCHOR>&amp;segment_columns</ANCHOR>
    <SQL_TEXT>select
&apos;gcc.&apos;||lower(fifsv.application_column_name)||&apos; &quot;&apos;||substrb(fifsv.form_left_prompt_,1,xxen_report.max_column_length)||&apos;&quot;,&apos; text
from
(select xxen_util.init_cap(fifsv.form_left_prompt) form_left_prompt_, fifsv.* from fnd_id_flex_segments_vl fifsv) fifsv
where
fifsv.application_id=101 and
fifsv.id_flex_code=&apos;GL#&apos; and
fifsv.enabled_flag=&apos;Y&apos; and
fifsv.id_flex_num=(select fifsv0.id_flex_num from fnd_id_flex_structures_vl fifsv0 where fifsv0.application_id=101 and fifsv0.id_flex_code=&apos;GL#&apos; and fifsv0.id_flex_structure_name=:chart_of_accounts)
order by
fifsv.segment_num</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>GL Chart of Accounts (inv org related)</LOV_NAME>
    <LOV_GUID>8E2FF36EDEC879D2E0530100007F1FF2</LOV_GUID>
    <LOV_QUERY_DSP>select
fifsv.id_flex_structure_name value,
fifsv.id_flex_structure_code description
from
fnd_id_flex_structures_vl fifsv
where
fifsv.id_flex_code=&apos;GL#&apos; and
fifsv.application_id=101 and
fifsv.id_flex_num in (select ood.chart_of_accounts_id from org_organization_definitions ood where nvl(ood.disable_date,sysdate + 1) &gt; sysdate)
order by
fifsv.id_flex_structure_name</LOV_QUERY_DSP>
    <DEFAULT_VALUE>select
fifsv.id_flex_structure_name
from
fnd_id_flex_structures_vl fifsv
where
fifsv.id_flex_code=&apos;GL#&apos; and
fifsv.application_id=101 and
fifsv.id_flex_num=(select ood.chart_of_accounts_id from org_organization_definitions ood where ood.organization_id=fnd_profile.value(&apos;MFG_ORGANIZATION_ID&apos;))</DEFAULT_VALUE>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Chart of Accounts</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>2</SORT_ORDER>
    <DISPLAY_SEQUENCE>10</DISPLAY_SEQUENCE>
    <ANCHOR>1=1</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
(:$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>3</SORT_ORDER>
    <DISPLAY_SEQUENCE>20</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>4</SORT_ORDER>
    <DISPLAY_SEQUENCE>30</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>
  <DEFAULT_TEMPLATES>
  </DEFAULT_TEMPLATES>
  <UPLOAD_COLUMNS>
  </UPLOAD_COLUMNS>
  <UPLOAD_PARAMETERS>
  </UPLOAD_PARAMETERS>
  <UPLOAD_SQLS>
  </UPLOAD_SQLS>
 </REPORTS_ROW>
</REPORTS>
</ROOT>
