<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 WIP Period Balances to Accounting Activity Reconciliation -->
 <REPORTS_ROW>
  <GUID>BF6B2BB8A56B2447E0530100007FAE1C</GUID><ENABLED>Y</ENABLED>
  <SQL_TEXT>with o as (
select /*+ materialize*/
mp.organization_id,
mp.organization_code,
oap.acct_period_id,
oap.period_name,
oap.period_start_date,
oap.schedule_close_date
from
mtl_parameters mp,
org_acct_periods oap
where
2=2 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=oap.organization_id
)
select
x.period_name,
nvl(gl.short_name,gl.name) ledger,
haouv_ou.name operating_unit,
x.organization_code org_code,
x.organization_id org_id,
msiv.concatenated_segments assembly_number,
msiv.description,
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
x.class_code wip_class,
xxen_util.meaning(wac.class_type,&apos;WIP_CLASS_TYPE&apos;,700) class_type,
xxen_util.meaning(x.entity_type,&apos;WIP_ENTITY&apos;,700) wip_type,
x.wip_entity_name wip_job,
x.date_closed,
x.status job_status,
x.date_released,
x.date_completed,
msiv.primary_uom_code uom_code,
x.start_quantity,
x.quantity_completed,
x.quantity_scrapped,
y.wip_costs_in,
y.wip_costs_out,
y.wip_close_var wip_relief,
y.wip_value,
y.wip_acct_in wip_accounted_costs_in,
y.wip_acct_out wip_accounted_costs_out,
y.wip_acct_var wip_accounted_relief,
y.wip_acct_value wip_accounted_value,
y.wip_costs_in-y.wip_acct_in wip_costs_in_difference,
y.wip_costs_out-y.wip_acct_out wip_costs_out_difference,
y.wip_close_var-y.wip_acct_var wip_relief_difference,
y.wip_value-y.wip_acct_value wip_value_difference
from
(
select
o.organization_id,
o.organization_code,
o.acct_period_id,
o.period_name,
we.wip_entity_name,
wdj.wip_entity_id,
we.entity_type,
wdj.class_code,
wdj.date_closed,
xxen_util.meaning(wdj.status_type,&apos;WIP_JOB_STATUS&apos;,700) status,
wdj.date_released,
wdj.date_completed,
wdj.start_quantity,
wdj.quantity_completed,
wdj.quantity_scrapped,
wdj.primary_item_id
from
o,
wip_discrete_jobs wdj,
wip_entities we
where
o.organization_id=wdj.organization_id and
(
wdj.date_closed is null and trunc(wdj.creation_date)&lt;=o.schedule_close_date or
trunc(wdj.date_closed)&gt;o.schedule_close_date or
wdj.date_closed&gt;=o.period_start_date and trunc(wdj.date_closed)&lt;o.schedule_close_date+1
) and
wdj.wip_entity_id=we.wip_entity_id
union all
select
o.organization_id,
o.organization_code,
o.acct_period_id,
o.period_name,
we.wip_entity_name,
wfs.wip_entity_id,
we.entity_type,
wfs.class_code,
wfs.date_closed,
xxen_util.meaning(wfs.status,&apos;WIP_FLOW_SCHEDULE_STATUS&apos;,700) status,
wfs.creation_date date_released,
wfs.date_closed date_completed,
wfs.planned_quantity start_quantity,
wfs.quantity_completed,
0 quantity_scrapped,
wfs.primary_item_id
from
o,
wip_flow_schedules wfs,
wip_entities we
where
o.organization_id=wfs.organization_id and
(
wfs.date_closed is null and trunc(wfs.creation_date)&lt;=o.schedule_close_date or
trunc(wfs.date_closed)&gt;o.schedule_close_date or
wfs.date_closed&gt;=o.period_start_date and trunc(wfs.date_closed)&lt;o.schedule_close_date+1
) and
wfs.wip_entity_id=we.wip_entity_id and
we.entity_type=4
) x,
(
select
z.organization_id,
z.wip_entity_id,
z.acct_period_id,
sum(z.wip_costs_in) wip_costs_in,
sum(z.wip_costs_out) wip_costs_out,
sum(z.wip_close_var) wip_close_var,
sum(z.wip_value) wip_value,
sum(round(z.wip_acct_in,2)) wip_acct_in,
sum(round(z.wip_acct_out,2)) wip_acct_out,
sum(round(z.wip_acct_var,2)) wip_acct_var,
sum(round(z.wip_acct_value,2)) wip_acct_value
from
(
select
o.organization_id,
wpb.wip_entity_id,
o.acct_period_id,
sum(nvl(wpb.tl_scrap_in,0)+nvl(wpb.pl_material_in,0)+nvl(wpb.pl_material_overhead_in,0)+nvl(wpb.tl_resource_in,0)+nvl(wpb.pl_resource_in,0)+nvl(wpb.tl_outside_processing_in,0)+nvl(wpb.pl_outside_processing_in,0)+nvl(wpb.tl_overhead_in,0)+nvl(wpb.pl_overhead_in,0)) wip_costs_in,
sum(nvl(wpb.tl_material_out,0)+nvl(wpb.tl_scrap_out,0)+nvl(wpb.pl_material_out,0)+nvl(wpb.tl_material_overhead_out,0)+nvl(wpb.pl_material_overhead_out,0)+nvl(wpb.tl_resource_out,0)+nvl(wpb.pl_resource_out,0)+nvl(wpb.tl_outside_processing_out,0)+nvl(wpb.pl_outside_processing_out,0)+nvl(wpb.tl_overhead_out,0)+nvl(wpb.pl_overhead_out,0)) wip_costs_out,
sum(nvl(wpb.tl_material_var,0)+nvl(wpb.tl_scrap_var,0)+nvl(wpb.pl_material_var,0)+nvl(wpb.tl_material_overhead_var,0)+nvl(wpb.pl_material_overhead_var,0)+nvl(wpb.tl_resource_var,0)+nvl(wpb.pl_resource_var,0)+nvl(wpb.tl_outside_processing_var,0)+nvl(wpb.pl_outside_processing_var,0)+nvl(wpb.tl_overhead_var,0)+nvl(wpb.pl_overhead_var,0)) wip_close_var,
sum(nvl(wpb.tl_scrap_in,0)+nvl(wpb.pl_material_in,0)-nvl(wpb.tl_material_out,0)-nvl(wpb.tl_scrap_out,0)-nvl(wpb.pl_material_out,0)-nvl(wpb.tl_material_var,0)-nvl(wpb.tl_scrap_var,0)-nvl(wpb.pl_material_var,0)+
nvl(wpb.pl_material_overhead_in,0)-nvl(wpb.tl_material_overhead_out,0)-nvl(wpb.pl_material_overhead_out,0)-nvl(wpb.tl_material_overhead_var,0)-nvl(wpb.pl_material_overhead_var,0)+
nvl(wpb.tl_resource_in,0)+nvl(wpb.pl_resource_in,0)-nvl(wpb.tl_resource_out,0)-nvl(wpb.pl_resource_out,0)-nvl(wpb.tl_resource_var,0)-nvl(wpb.pl_resource_var,0)+
nvl(wpb.tl_outside_processing_in,0)+nvl(wpb.pl_outside_processing_in,0)-nvl(wpb.tl_outside_processing_out,0)-nvl(wpb.pl_outside_processing_out,0)-nvl(wpb.tl_outside_processing_var,0)-nvl(wpb.pl_outside_processing_var,0)+
nvl(wpb.tl_overhead_in,0)+nvl(wpb.pl_overhead_in,0)-nvl(wpb.tl_overhead_out,0)-nvl(wpb.pl_overhead_out,0)-nvl(wpb.tl_overhead_var,0)-nvl(wpb.pl_overhead_var,0)) wip_value,
0 wip_acct_in,
0 wip_acct_out,
0 wip_acct_var,
0 wip_acct_value
from
o,
wip_period_balances wpb
where
o.organization_id=wpb.organization_id and
o.acct_period_id=wpb.acct_period_id
group by
o.organization_id,
wpb.wip_entity_id,
o.acct_period_id
union all
select
o.organization_id,
mmt.transaction_source_id,
o.acct_period_id,
0,
0,
0,
0,
sum(decode(mmt.transaction_type_id,35,mta.base_transaction_value,38,mta.base_transaction_value,43,mta.base_transaction_value,48,mta.base_transaction_value,55,mta.base_transaction_value,56,mta.base_transaction_value,57,mta.base_transaction_value,0)),
sum(decode(mmt.transaction_type_id,17,-mta.base_transaction_value,44,-mta.base_transaction_value,90,-mta.base_transaction_value,91,-mta.base_transaction_value,92,-mta.base_transaction_value,1002,-mta.base_transaction_value,1003,-mta.base_transaction_value,0)),
0,
sum(decode(mmt.transaction_type_id,35,mta.base_transaction_value,38,mta.base_transaction_value,43,mta.base_transaction_value,48,mta.base_transaction_value,55,mta.base_transaction_value,56,mta.base_transaction_value,57,mta.base_transaction_value,17,mta.base_transaction_value,44,mta.base_transaction_value,90,mta.base_transaction_value,91,mta.base_transaction_value,92,mta.base_transaction_value,1002,mta.base_transaction_value,1003,mta.base_transaction_value,0))
from
o,
mtl_material_transactions mmt,
mtl_transaction_accounts mta
where
o.acct_period_id=mmt.acct_period_id and
mmt.transaction_date&gt;=o.period_start_date and
mmt.transaction_date&lt;o.schedule_close_date+1 and
mmt.transaction_id=mta.transaction_id and
o.organization_id=mta.organization_id and
mta.transaction_source_type_id=5 and
mta.accounting_line_type=7 and
mta.transaction_date&gt;=o.period_start_date and
mta.transaction_date&lt;o.schedule_close_date+1
group by
o.organization_id,
mmt.transaction_source_id,
o.acct_period_id
union all
select
o.organization_id,
wt.wip_entity_id,
o.acct_period_id,
0,
0,
0,
0,
sum(decode(wt.transaction_type,1,wta.base_transaction_value,2,wta.base_transaction_value,3,wta.base_transaction_value,11,wta.base_transaction_value,12,wta.base_transaction_value,13,wta.base_transaction_value,14,wta.base_transaction_value,0)),
sum(decode(wt.transaction_type,15,wta.base_transaction_value,16,wta.base_transaction_value,17,wta.base_transaction_value,0)),
sum(decode(wt.transaction_type,5,-wta.base_transaction_value,6,-wta.base_transaction_value,7,-wta.base_transaction_value,0)),
sum(decode(wt.transaction_type,1,wta.base_transaction_value,2,wta.base_transaction_value,3,wta.base_transaction_value,11,wta.base_transaction_value,12,wta.base_transaction_value,13,wta.base_transaction_value,14,wta.base_transaction_value,15,wta.base_transaction_value,16,wta.base_transaction_value,17,wta.base_transaction_value,5,wta.base_transaction_value,6,wta.base_transaction_value,7,wta.base_transaction_value,0))
from
o,
wip_transactions wt,
wip_transaction_accounts wta
where
o.acct_period_id=wt.acct_period_id and
wt.transaction_date&gt;=o.period_start_date and
wt.transaction_date&lt;o.schedule_close_date+1 and
wt.transaction_id=wta.transaction_id and
o.organization_id=wta.organization_id and
wta.accounting_line_type=7 and
wta.transaction_date&gt;=o.period_start_date and
wta.transaction_date&lt;o.schedule_close_date+1
group by
o.organization_id,
wt.wip_entity_id,
o.acct_period_id
union all
select
o.organization_id,
cscav.wip_entity_id,
o.acct_period_id,
0,
0,
0,
0,
round(sum(decode(cscav.transaction_type,3,1,6,1,7,1,8,1,9,1,10,1,0)*cscav.adjustment_quantity*(cscav.new_unit_cost-cscav.old_unit_cost)),2),
round(sum(decode(cscav.transaction_type,4,1,5,1,0)*cscav.adjustment_quantity*(cscav.new_unit_cost-cscav.old_unit_cost)),2),
0,
round(sum(decode(cscav.transaction_type,3,1,6,1,7,1,8,1,9,1,10,1,4,cscav.in_out_flag,5,cscav.in_out_flag,0)*cscav.adjustment_quantity*(cscav.new_unit_cost-cscav.old_unit_cost)),2)
from
o,
cst_cost_updates ccu,
cst_std_cost_adj_values cscav
where
o.organization_id=ccu.organization_id and
ccu.update_date&gt;=o.period_start_date and
ccu.update_date&lt;o.schedule_close_date+1 and
ccu.cost_update_id=cscav.cost_update_id
group by
o.organization_id,
cscav.wip_entity_id,
o.acct_period_id
) z
group by
z.organization_id,
z.wip_entity_id,
z.acct_period_id
) y,
wip_accounting_classes wac,
mtl_system_items_vl msiv,
hr_organization_information hoi,
hr_all_organization_units haou,
hr_all_organization_units_vl haouv_ou,
gl_ledgers gl
where
1=1 and
x.wip_entity_id=y.wip_entity_id and
x.acct_period_id=y.acct_period_id and
x.organization_id=wac.organization_id and
x.class_code=wac.class_code and
x.organization_id=msiv.organization_id and
x.primary_item_id=msiv.inventory_item_id 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
order by
x.period_name,
ledger,
operating_unit,
org_code,
assembly_number,
wip_job</SQL_TEXT>
  <VERSION_COMMENTS>Rebuilt to the Blitz Report SQL standards: the Category Set parameters show their category columns (they had no effect), the Item Number parameter matches the whole item number. 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>
  <NUMBER_FORMAT>#,##0.00;[Red](#,##0.00)</NUMBER_FORMAT>
  <REPORT_TRANSLATIONS>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>US</LANGUAGE>
    <REPORT_NAME>CAC WIP Period Balances to Accounting Activity Reconciliation</REPORT_NAME>
    <DESCRIPTION>Report to compare the monthly WIP Period Balances with the pre-Create Accounting WIP accounting entries for material, resource, overhead, outside processing, job close variance and standard cost update transactions.  With WIP class, job status, name and other details.  This report shows both WIP jobs which were open during the accounting period as well as jobs closed during the accounting period.  If the stored WIP period balances agree to the period WIP accounting activity, the &quot;Difference&quot; columns have a zero amount.

//* +=============================================================================+
-- | Copyright 2022 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_reconcile_wip_balances.sql
-- |
-- |  Parameters:
-- |  p_period_name          -- The desired accounting period you wish to report
-- |  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 compare the monthly WIP transactions against the WIP period balances.
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0     07 Apr 2021 Douglas Volz   Initial Coding based on item_cost_history.sql
-- | 1.1     10 Jul 2022 Douglas Volz   Add Ledger and Operating Unit columns
-- | 1.2     19 Oct 2022 Douglas Volz   Bug fix for missing organization join
-- |  1.3     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>3=3</ANCHOR>
    <SQL_TEXT>oap.period_name=:p_period_name</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>INV Period</LOV_NAME>
    <LOV_GUID>8E2FF36EDF2279D2E0530100007F1FF2</LOV_GUID>
    <LOV_QUERY_DSP>select distinct
oap.period_name value,
max(oap.period_year||&apos;-&apos;||oap.period_num||&apos;, &apos;||xxen_util.meaning(nvl2(oap.period_close_date,decode(oap.open_flag,&apos;P&apos;,2,&apos;N&apos;,decode(oap.summarized_flag,&apos;N&apos;,65,66),4),3),&apos;MTL_ACCT_PERIOD_STATUS&apos;,700)||&apos; (&apos;||oap.period_start_date||&apos; - &apos;||oap.schedule_close_date||&apos;)&apos;) over (partition by oap.period_name) description,
max(oap.period_start_date) over (partition by oap.period_name) period_start_date,
max(oap.effective_period_num) over (partition by oap.period_name) effective_period_num
from
gl_ledgers gl,
org_organization_definitions ood,
mtl_parameters mp,
(select oap.period_year*10000+oap.period_num effective_period_num, oap.* from org_acct_periods oap) oap
where
oap.period_start_date&lt;=sysdate and
(:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)=&apos;Y&apos;) and
(:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,ood.organization_code)=&apos;Y&apos;) and
ood.organization_id=mp.organization_id and
mp.organization_id&lt;&gt;mp.master_organization_id and 
gl.period_set_name=oap.period_set_name and
gl.ledger_id=ood.set_of_books_id and
ood.organization_id=oap.organization_id
order by
period_start_date desc,
effective_period_num desc,
oap.period_name</LOV_QUERY_DSP>
    <DEFAULT_VALUE>select distinct oap.period_name value
from org_acct_periods oap,
 (select max(oap.schedule_close_date) default_date
  from org_acct_periods oap,
  org_organization_definitions ood
  where ood.organization_id = oap.organization_id
  and nvl(ood.disable_date, sysdate + 1) &gt; sysdate) max
where oap.schedule_close_date = max.default_date
and rownum = 1
</DEFAULT_VALUE>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Period Name</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>1=1</ANCHOR>
    <SQL_TEXT>msiv.concatenated_segments=:p_item_number</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>INV Item (master, costing enabled only)</LOV_NAME>
    <LOV_GUID>91D022B15B12FFB9E053BB6B63587F0B</LOV_GUID>
    <LOV_QUERY_DSP>select
msiv.concatenated_segments value,
msiv.description
from
mtl_system_items_vl msiv
where
msiv.organization_id=(
select
max(mp.master_organization_id) master_organization_id
from
mtl_parameters mp
where
mp.organization_id=fnd_profile.value(&apos;MFG_ORGANIZATION_ID&apos;) or
fnd_profile.value(&apos;MFG_ORGANIZATION_ID&apos;) 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;
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>6</SORT_ORDER>
    <DISPLAY_SEQUENCE>60</DISPLAY_SEQUENCE>
    <ANCHOR>2=2</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>7</SORT_ORDER>
    <DISPLAY_SEQUENCE>70</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>8</SORT_ORDER>
    <DISPLAY_SEQUENCE>80</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>
  <DEFAULT_TEMPLATES>
  </DEFAULT_TEMPLATES>
  <UPLOAD_COLUMNS>
  </UPLOAD_COLUMNS>
  <UPLOAD_PARAMETERS>
  </UPLOAD_PARAMETERS>
  <UPLOAD_SQLS>
  </UPLOAD_SQLS>
 </REPORTS_ROW>
</REPORTS>
</ROOT>
