<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 Category Accounts Setup -->
 <REPORTS_ROW>
  <GUID>C9AE7AF806C8EA8FE0530100007FBF9F</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,
mcv.category_concat_segs category,
(select ccg.cost_group from cst_cost_groups ccg where x.cost_group_id=ccg.cost_group_id) cost_group,
x.subinventory_code subinventory,
(select xxen_util.yes(decode(msi.asset_inventory,1,&apos;Y&apos;)) from mtl_secondary_inventories msi where x.organization_id=msi.organization_id and x.subinventory_code=msi.secondary_inventory_name) inventory_asset,
decode(x.account_number,1,&apos;Material Account&apos;,2,&apos;Material Overhead Account&apos;,3,&apos;Resource Account&apos;,4,&apos;Outside Processing Account&apos;,5,&apos;Overhead Account&apos;,6,&apos;Expense Account&apos;,7,&apos;Encumbrance Account&apos;,8,&apos;Bridging Account&apos;) account_type,
&amp;segment_columns
x.creation_date,
x.last_update_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
mca.organization_id,
mca.category_id,
mca.cost_group_id,
mca.subinventory_code,
mca.creation_date,
mca.last_update_date,
mca.created_by,
mca.last_updated_by,
rowgen.column_value account_number,
decode(rowgen.column_value,1,mca.material_account,2,mca.material_overhead_account,3,mca.resource_account,4,mca.outside_processing_account,5,mca.overhead_account,6,mca.expense_account,7,mca.encumbrance_account,8,mca.bridging_account) account_id
from
mtl_category_accounts mca,
table(xxen_util.rowgen(8)) rowgen
) x,
mtl_categories_v mcv,
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.category_id=mcv.category_id and
(x.account_number&lt;=6 or x.account_id is not null) and
x.account_id=gcc.code_combination_id(+)
order by
ledger,
operating_unit,
org_code,
category,
subinventory,
x.account_number</SQL_TEXT>
  <VERSION_COMMENTS>Rebuilt to the Blitz Report SQL standards. Inventory Asset shows 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 Category Accounts Setup</REPORT_NAME>
    <DESCRIPTION>Report to show the category accounts in use.  If category accounts have been set up with your Subledger Accounting Rules, the Inventory Cost Processor can use them and bypass the organization accounts (Average, LIFO, FIFO Costing) or the subinventory accounts (Standard Costing).

/* +=============================================================================+
-- |  Copyright 2021 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_category_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     16 Aug 2021 Douglas Volz   Initial Coding
-- |  1.1     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>
