<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 Department Overhead Rates -->
 <REPORTS_ROW>
  <GUID>91E8680353167CCEE053BB6B635868C2</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,
bd.department_code department,
br.resource_code overhead,
xxen_util.meaning(cdo.basis_type,&apos;CST_BASIS_SHORT&apos;,700) overhead_basis,
cdo.rate_or_amount overhead_rate,
&amp;segment_columns
cdo.last_update_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_department_overheads cdo,
cst_cost_types cct,
bom_departments bd,
bom_resources br,
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
nvl(haou.date_to,sysdate+1)&gt;sysdate and
to_number(hoi.org_information3)=haouv_ou.organization_id and
to_number(hoi.org_information1)=gl.ledger_id and
mp.organization_id=cdo.organization_id and
cdo.cost_type_id=cct.cost_type_id and
cdo.department_id=bd.department_id and
cdo.overhead_id=br.resource_id and
br.absorption_account=gcc.code_combination_id
order by
ledger,
operating_unit,
org_code,
cost_type,
department,
overhead</SQL_TEXT>
  <VERSION_COMMENTS>Rebuilt to the Blitz Report SQL standards. Operating Unit shows the operating unit instead of the organization name. 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 Department Overhead Rates</REPORT_NAME>
    <DESCRIPTION>Report to show departmental overheads and rates

/* +=============================================================================+
-- |  Copyright 2016 - 2020 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.5     23 Oct 2026 Andy Haack    Rebuilt to the Blitz Report SQL standards.
-- +=============================================================================+
-- |
-- |  Original Author: Douglas Volz (doug@volzconsulting.com)
-- |
-- |  Program Name:  xxx_dept_ovhd_rates.sql
-- |
-- |  Parameters:
-- |  p_ledger	    -- Ledger you wish to report, enter a null or blank for all
-- |                   ledgers.
-- |  p_cost_type   -- Cost Type you wish to report, enter a null or blank for all
-- |                   cost types.
-- | 
-- |  Description:
-- |  Report to show departmental overheads and rates.
-- | 
-- |  Version Modified on Modified  by   Description
-- |  ======= =========== ============== =========================================
-- |  1.0     17 Sep 2013 Douglas Volz   Initial Coding
-- |  1.1     18 Oct 2016 Douglas Volz   Added organization information
-- |  1.2      6 Sep 2019 Douglas Volz   Added last update date
-- |  1.3     27 Jan 2020 Douglas Volz   Added Operating Unit parameter
-- |  1.4     26 Apr 2020 Douglas Volz   Changed to multi-language views for 
-- |                                     operating units.+=============================================================================+*/</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>cct.cost_type=:p_cost_type</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>CST Cost Type (No ICP PII)</LOV_NAME>
    <LOV_GUID>A486BD05719B2A82E053BB6B6358FC8E</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 and
(cct.cost_type not like &apos;%PII%&apos; or cct.cost_type like &apos;%ICP%&apos;)
order by
cct.cost_type</LOV_QUERY_DSP>
    <DEFAULT_VALUE>select cct.cost_type value,
 cct.description
from cst_cost_types cct,
 mtl_parameters mp
where nvl(cct.disable_date,sysdate + 1) &gt; sysdate
and cct.organization_id is null
and cct.cost_type_id = decode(mp.primary_cost_method, 1, 1, mp.avg_rates_cost_type_id)
and (mp.organization_id=fnd_profile.value(&apos;MFG_ORGANIZATION_ID&apos;)
  or
  (fnd_profile.value(&apos;MFG_ORGANIZATION_ID&apos;) is null and cct.cost_type_id = decode(mp.primary_cost_method, 1, 1, mp.avg_rates_cost_type_id))
 )
and rownum &lt; 2
group by cct.cost_type, cct.description
order by cct.cost_type</DEFAULT_VALUE>
    <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>3</SORT_ORDER>
    <DISPLAY_SEQUENCE>20</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>4</SORT_ORDER>
    <DISPLAY_SEQUENCE>30</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>5</SORT_ORDER>
    <DISPLAY_SEQUENCE>40</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>
