<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 Item List Price vs. Item Cost -->
 <REPORTS_ROW>
  <GUID>DEA56CF31AD712BFE0530100007FD1CA</GUID><ENABLED>Y</ENABLED>
  <SQL_TEXT>select
x.ledger,
x.operating_unit,
x.org_code,
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.costing_enabled_flag) allow_costs,
xxen_util.yes(x.inventory_asset_flag) inventory_asset,
x.based_on_rollup,
x.shrinkage_rate,
x.from_currency_code,
:p_currency_code to_currency_code,
x.conversion_rate,
round(x.market_price*x.conversion_rate,5) market_price,
round(x.master_list_price*x.conversion_rate,5) master_target_or_list_price,
round(x.list_price*x.conversion_rate,5) bu_target_or_list_price,
round(x.unburdened_cost1*x.conversion_rate,5) &quot;&amp;Cost_Type1 Unburdened Cost&quot;,
round((nvl(x.list_price,0)-nvl(x.unburdened_cost1,0))*x.conversion_rate,5) target_price_difference,
case
when round(nvl(x.list_price,0)-nvl(x.unburdened_cost1,0),5)=0 then 0
when round(nvl(x.unburdened_cost1,0),5)=0 then 100
else round((nvl(x.list_price,0)-nvl(x.unburdened_cost1,0))/x.unburdened_cost1*100,1)
end percent_difference,
x.last_purchase_order,
x.last_po_line,
x.last_po_date,
x.last_po_currency_code,
x.last_po_price,
x.last_po_price*x.po_conversion_rate converted_last_po_price,
x.cost_type_1,
round(x.material_cost1*x.conversion_rate,5) material_cost1,
round(x.material_overhead_cost1*x.conversion_rate,5) material_overhead_cost1,
round(x.resource_cost1*x.conversion_rate,5) resource_cost1,
round(x.outside_processing_cost1*x.conversion_rate,5) outside_processing_cost1,
round(x.overhead_cost1*x.conversion_rate,5) overhead_cost1,
round(x.item_cost1*x.conversion_rate,5) item_cost1,
x.cost_creation_date1,
x.last_cost_update_date1,
x.cost_type_2,
round(x.unburdened_cost2*x.conversion_rate,5) unburdened_cost2,
round(x.material_cost2*x.conversion_rate,5) material_cost2,
round(x.material_overhead_cost2*x.conversion_rate,5) material_overhead_cost2,
round(x.resource_cost2*x.conversion_rate,5) resource_cost2,
round(x.outside_processing_cost2*x.conversion_rate,5) outside_processing_cost2,
round(x.overhead_cost2*x.conversion_rate,5) overhead_cost2,
round(x.item_cost2*x.conversion_rate,5) item_cost2,
x.cost_creation_date2,
x.last_cost_update_date2
from
(
select
nvl(gl.short_name,gl.name) ledger,
haouv_ou.name operating_unit,
mp.organization_code org_code,
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,
msiv.inventory_item_status_code item_status,
msiv.planning_make_buy_code,
msiv.costing_enabled_flag,
decode(cic1.inventory_item_id,null,msiv.inventory_asset_flag,decode(cic1.inventory_asset_flag,1,&apos;Y&apos;)) inventory_asset_flag,
decode(cic1.inventory_item_id,null,&apos;N/A&apos;,xxen_util.meaning(cic1.based_on_rollup_flag,&apos;CST_BONROLLUP_VAL&apos;,700)) based_on_rollup,
cic1.shrinkage_rate,
gl.currency_code from_currency_code,
decode(gl.currency_code,:p_currency_code,1,(select gdr.conversion_rate from gl_daily_rates gdr, gl_daily_conversion_types gdct where gl.currency_code=gdr.from_currency and gdr.to_currency=:p_currency_code and gdr.conversion_date=:conversion_date and gdr.conversion_type=gdct.conversion_type and gdct.user_conversion_type=:user_conversion_type)) conversion_rate,
msiv.market_price,
msib_mas.list_price_per_unit master_list_price,
msiv.list_price_per_unit list_price,
po.last_purchase_order,
po.last_po_line,
po.last_po_date,
po.last_po_currency_code,
po.last_po_price,
decode(po.last_po_currency_code,:p_currency_code,1,(select gdr.conversion_rate from gl_daily_rates gdr, gl_daily_conversion_types gdct where po.last_po_currency_code=gdr.from_currency and gdr.to_currency=:p_currency_code and gdr.conversion_date=:conversion_date and gdr.conversion_type=gdct.conversion_type and gdct.user_conversion_type=:user_conversion_type)) po_conversion_rate,
cic1.cost_type cost_type_1,
cic1.unburdened_cost unburdened_cost1,
cic1.material_cost material_cost1,
cic1.material_overhead_cost material_overhead_cost1,
cic1.resource_cost resource_cost1,
cic1.outside_processing_cost outside_processing_cost1,
cic1.overhead_cost overhead_cost1,
cic1.item_cost item_cost1,
cic1.creation_date cost_creation_date1,
cic1.last_update_date last_cost_update_date1,
cic2.cost_type cost_type_2,
cic2.unburdened_cost unburdened_cost2,
cic2.material_cost material_cost2,
cic2.material_overhead_cost material_overhead_cost2,
cic2.resource_cost resource_cost2,
cic2.outside_processing_cost outside_processing_cost2,
cic2.overhead_cost overhead_cost2,
cic2.item_cost item_cost2,
cic2.creation_date cost_creation_date2,
cic2.last_update_date last_cost_update_date2
from
mtl_parameters mp,
hr_organization_information hoi,
hr_all_organization_units haou,
hr_all_organization_units_vl haouv_ou,
gl_ledgers gl,
mtl_system_items_vl msiv,
mtl_system_items_b msib_mas,
(
select
cic.organization_id,
cic.inventory_item_id,
cct1.cost_type,
cic.inventory_asset_flag,
cic.based_on_rollup_flag,
cic.shrinkage_rate,
nvl(cic.unburdened_cost,0) unburdened_cost,
nvl(cic.material_cost,0) material_cost,
nvl(cic.material_overhead_cost,0) material_overhead_cost,
nvl(cic.resource_cost,0) resource_cost,
nvl(cic.outside_processing_cost,0) outside_processing_cost,
nvl(cic.overhead_cost,0) overhead_cost,
nvl(cic.item_cost,0) item_cost,
cic.creation_date,
cic.last_update_date
from
cst_item_costs cic,
cst_cost_types cct1
where
4=4 and
cic.cost_type_id=cct1.cost_type_id
) cic1,
(
select
cic.organization_id,
cic.inventory_item_id,
cct2.cost_type,
nvl(cic.unburdened_cost,0) unburdened_cost,
nvl(cic.material_cost,0) material_cost,
nvl(cic.material_overhead_cost,0) material_overhead_cost,
nvl(cic.resource_cost,0) resource_cost,
nvl(cic.outside_processing_cost,0) outside_processing_cost,
nvl(cic.overhead_cost,0) overhead_cost,
nvl(cic.item_cost,0) item_cost,
cic.creation_date,
cic.last_update_date
from
cst_item_costs cic,
cst_cost_types cct2
where
5=5 and
cic.cost_type_id=cct2.cost_type_id
) cic2,
(
select
y.organization_id,
y.inventory_item_id,
y.last_purchase_order,
y.last_po_line,
y.last_po_date,
y.last_po_currency_code,
round(y.unit_price/mucv.conversion_rate,6) last_po_price
from
(
select
plla.ship_to_organization_id organization_id,
pla.item_id inventory_item_id,
pha.segment1 last_purchase_order,
pla.line_num last_po_line,
coalesce(plla.promised_date,plla.need_by_date,plla.last_update_date) last_po_date,
pha.currency_code last_po_currency_code,
nvl(plla.price_override,pla.unit_price) unit_price,
pla.unit_meas_lookup_code,
row_number() over (partition by plla.ship_to_organization_id,pla.item_id order by coalesce(plla.promised_date,plla.need_by_date,plla.last_update_date) desc,pda.po_distribution_id desc) rn
from
po_headers_all pha,
po_lines_all pla,
po_line_locations_all plla,
po_distributions_all pda
where
11=11 and
pha.authorization_status=&apos;APPROVED&apos; and
pha.po_header_id=pla.po_header_id and
pla.item_id is not null and
pla.closed_code not in (&apos;CANCELLED&apos;,&apos;INCOMPLETE&apos;,&apos;ON HOLD&apos;,&apos;REJECTED&apos;,&apos;RETURNED&apos;) and
pla.po_line_id=plla.po_line_id and
plla.ship_to_organization_id is not null and
plla.line_location_id=pda.line_location_id and
pda.destination_type_code=&apos;INVENTORY&apos;
) y,
mtl_uom_conversions_view mucv
where
y.rn=1 and
y.organization_id=mucv.organization_id(+) and
y.inventory_item_id=mucv.inventory_item_id(+) and
y.unit_meas_lookup_code=mucv.unit_of_measure(+)
) po
where
1=1 and
6=6 and
7=7 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&lt;&gt;mp.master_organization_id 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=msiv.organization_id and
mp.master_organization_id=msib_mas.organization_id and
msiv.inventory_item_id=msib_mas.inventory_item_id and
msiv.organization_id=cic1.organization_id(+) and
msiv.inventory_item_id=cic1.inventory_item_id(+) and
(cic1.inventory_item_id is not null or msiv.costing_enabled_flag=&apos;N&apos; and 10=10) and
msiv.organization_id=cic2.organization_id(+) and
msiv.inventory_item_id=cic2.inventory_item_id(+) and
msiv.organization_id=po.organization_id(+) and
msiv.inventory_item_id=po.inventory_item_id(+)
) x
order by
x.ledger,
x.operating_unit,
x.org_code,
x.item_number,
x.item_description</SQL_TEXT>
  <VERSION_COMMENTS>The last PO price per primary unit of measure is divided by the unit of measure conversion rate instead of multiplied.</VERSION_COMMENTS>
  <NUMBER_FORMAT>#,##0.00000;[Red](#,##0.00000)</NUMBER_FORMAT>
  <REPORT_TRANSLATIONS>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>US</LANGUAGE>
    <REPORT_NAME>CAC Item List Price vs. Item Cost</REPORT_NAME>
    <DESCRIPTION>Compare item master list price against any cost type (Cost Type 1) and also list the Standard Price (Market Price), Last PO Price, Converted Last PO Price and a secondary cost type (Cost Type 2).  All item costs and prices are in the primary UOM, using the To Currency Code.

-- | Program Name: xxx_item_target_cost_review_rept.sql
-- |
-- |  Parameters:
-- |  p_cost_type1             -- The first comparison cost type you wish to report
-- |  p_cost_type2             -- The second comparison cost type you wish to report
-- |  p_item_number            -- Enter the specific item number you wish to report
-- |  p_org_code               -- specific organization code, works with
-- |                              null or valid organization codes
-- |  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)
-- |  p_include_uncosted_items -- Yes/No flag to include or not include non-costed items
-- |  p_category_set1          -- The first item category set to report, typically the
-- |                              Cost or Product Line Category Set
-- |  p_category_set2          -- The second item category set to report, typically the
-- |                              Inventory Category Set
-- |
-- | Description:
-- | Report to show item costs in any cost type
-- | 
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- |  1.0    29 Sep 2020 Douglas Volz   Initial Coding based on Item Cost Summary Report
-- |  1.1    22 Oct 2020 Douglas Volz   Added the Master Target Price
-- |  1.2    26 Oct 2020 Douglas Volz   Changed as List Price is always in a common, global
-- |                                    currency, translate to this &quot;to_currency&quot; currency
-- |  1.3    28 Oct 2020 Douglas Volz   Fixes for the last PO, need to exclude Cancelled
-- |                                    PO Lines.  
-- |  1.4    30 Oct 2020 Douglas Volz   Add To Currency Code as a visible parameter and
-- |                                    show all item costs in that currency
-- |  1.5    04 Dec 2020 Douglas Volz   Outer join fix for PO joins to item master and
-- |                                    currency exchange rates.
-- |  1.6    06 Dec 2020 Douglas Volz   Added Target Price Percent Difference column.
+=============================================================================+*/
-- |  1.7     24 Oct 2026 Andy Haack    Rebuilt to the Blitz Report SQL standards, price per primary unit of measure divided by the conversion rate.
</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>&amp;cost_type1</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>10=10</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>11=11</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>1=1</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>4=4</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>5=5</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>6=6</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>7=7</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>:conversion_date</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>:p_currency_code</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>:user_conversion_type</ANCHOR>
   </ANCHORS_ROW>
  </ANCHORS>
  <PARAMETERS>
   <PARAMETERS_ROW>
    <SORT_ORDER>1</SORT_ORDER>
    <DISPLAY_SEQUENCE>10</DISPLAY_SEQUENCE>
    <ANCHOR>4=4</ANCHOR>
    <SQL_TEXT>cct1.cost_type=:p_cost_type1</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>CST Cost Type (No AvgRates)</LOV_NAME>
    <LOV_GUID>91D3167A335B1204E053BB6B63585EE3</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.cost_type_id not in (select mp.avg_rates_cost_type_id from mtl_parameters mp where mp.avg_rates_cost_type_id is not null)
order by cct.cost_type desc</LOV_QUERY_DSP>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Comparison Cost Type</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>2</SORT_ORDER>
    <ANCHOR>&amp;cost_type1</ANCHOR>
    <SQL_TEXT>&lt;parameter_value&gt;</SQL_TEXT>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Comparison Cost Type</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>3</SORT_ORDER>
    <DISPLAY_SEQUENCE>20</DISPLAY_SEQUENCE>
    <ANCHOR>5=5</ANCHOR>
    <SQL_TEXT>cct2.cost_type=:p_cost_type2</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>CST Cost Type (No AvgRates)</LOV_NAME>
    <LOV_GUID>91D3167A335B1204E053BB6B63585EE3</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.cost_type_id not in (select mp.avg_rates_cost_type_id from mtl_parameters mp where mp.avg_rates_cost_type_id is not null)
order by cct.cost_type desc</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.cost_type_id = mp.primary_cost_method
and  mp.organization_id in
 (select min(mp2.organization_id) 
  from mtl_parameters mp2
  where mp2.organization_id = mp2.master_organization_id
 )
and cct.organization_id is null
group by cct.cost_type, cct.description
order by cct.cost_type</DEFAULT_VALUE>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Additional Cost Type</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>4</SORT_ORDER>
    <DISPLAY_SEQUENCE>30</DISPLAY_SEQUENCE>
    <ANCHOR>:p_currency_code</ANCHOR>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>FND Currency</LOV_NAME>
    <LOV_GUID>8E2FF36EDE9079D2E0530100007F1FF2</LOV_GUID>
    <LOV_QUERY_DSP>select
fcv.currency_code id,
fcv.currency_code value,
fcv.description
from
fnd_currencies_vl fcv
where
fcv.enabled_flag=&apos;Y&apos;
order by
fcv.currency_code</LOV_QUERY_DSP>
    <DEFAULT_VALUE>select gl.currency_code
from mtl_parameters mp,
 hr_organization_information hoi,
 gl_ledgers gl
where hoi.org_information_context = &apos;Accounting Information&apos;
and hoi.organization_id         = mp.organization_id
and gl.ledger_id                = to_number(hoi.org_information1) -- get the ledger_id
and mp.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>To Currency Code</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>5</SORT_ORDER>
    <DISPLAY_SEQUENCE>40</DISPLAY_SEQUENCE>
    <ANCHOR>:user_conversion_type</ANCHOR>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>GL Currency Conversion Type</LOV_NAME>
    <LOV_GUID>91D3167A335F1204E053BB6B63585EE3</LOV_GUID>
    <LOV_QUERY_DSP>select
gdct.conversion_type id,
gdct.user_conversion_type value,
gdct.description
from
gl_daily_conversion_types gdct
order by
gdct.user_conversion_type</LOV_QUERY_DSP>
    <ID_OR_VALUE>value</ID_OR_VALUE>
    <DEFAULT_VALUE>Corporate</DEFAULT_VALUE>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Currency Conversion Type</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>6</SORT_ORDER>
    <DISPLAY_SEQUENCE>50</DISPLAY_SEQUENCE>
    <ANCHOR>:conversion_date</ANCHOR>
    <PARAMETER_TYPE_DSP>Date</PARAMETER_TYPE_DSP>
    <DEFAULT_VALUE>select oap.schedule_close_date value,
 oap.period_name description
from org_acct_periods oap
where oap.acct_period_id =
 (select max(oap2.acct_period_id)
  from org_acct_periods oap2
  where oap2.schedule_close_date =
  (select max(oap3.schedule_close_date)
   from org_acct_periods oap3,
   org_organization_definitions ood
   where ood.organization_id = oap3.organization_id
   and nvl(ood.disable_date,sysdate + 1) &gt; sysdate
  ) 
 )</DEFAULT_VALUE>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Currency Conversion Date</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>7</SORT_ORDER>
    <DISPLAY_SEQUENCE>60</DISPLAY_SEQUENCE>
    <ANCHOR>10=10</ANCHOR>
    <SQL_TEXT>:p_include_uncosted_items=&apos;Y&apos;</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV Oracle</PARAMETER_TYPE_DSP>
    <LOV_NAME>Yes_No</LOV_NAME>
    <LOV_QUERY_DSP>select
lookup_code id,
meaning value,
null description
from
fnd_lookups
where fnd_lookups.lookup_type=&apos;YES_NO&apos;
order by value,description</LOV_QUERY_DSP>
    <DEFAULT_VALUE>N</DEFAULT_VALUE>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Include Uncosted Items</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>8</SORT_ORDER>
    <DISPLAY_SEQUENCE>70</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>9</SORT_ORDER>
    <DISPLAY_SEQUENCE>80</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>10</SORT_ORDER>
    <DISPLAY_SEQUENCE>90</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>11</SORT_ORDER>
    <DISPLAY_SEQUENCE>100</DISPLAY_SEQUENCE>
    <ANCHOR>6=6</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>12</SORT_ORDER>
    <ANCHOR>11=11</ANCHOR>
    <SQL_TEXT>pla.item_id in (select msiv.inventory_item_id from mtl_system_items_vl msiv where msiv.concatenated_segments=:p_item_number)</SQL_TEXT>
    <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>13</SORT_ORDER>
    <DISPLAY_SEQUENCE>110</DISPLAY_SEQUENCE>
    <ANCHOR>7=7</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>14</SORT_ORDER>
    <ANCHOR>11=11</ANCHOR>
    <SQL_TEXT>plla.ship_to_organization_id in (select mp.organization_id from mtl_parameters mp where mp.organization_code=:p_org_code)</SQL_TEXT>
    <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>15</SORT_ORDER>
    <DISPLAY_SEQUENCE>120</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>16</SORT_ORDER>
    <DISPLAY_SEQUENCE>130</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>
