<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 Inventory Lot and Locator OPM Value (Period-End) -->
 <REPORTS_ROW>
  <GUID>1A573DC3B7D90377E0630100007FE5A2</GUID><ENABLED>Y</ENABLED>
  <SQL_TEXT>with mp as (
select /*+ materialize*/
nvl(gl.short_name,gl.name) ledger,
haouv_ou.name operating_unit,
mp.organization_code,
mp.organization_id,
to_number(hoi.org_information2) legal_entity_id,
nvl(cwa.cost_organization_id,mp.organization_id) cost_organization_id,
mp.intransit_inv_account,
gl.currency_code
from
mtl_parameters mp,
hr_organization_information hoi,
hr_all_organization_units_vl haouv,
hr_all_organization_units_vl haouv_ou,
gl_ledgers gl,
cm_whse_asc cwa
where
1=1 and
2=2 and
mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;INV&apos;) and
mp.process_enabled_flag=&apos;Y&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=haouv.organization_id and
sysdate&lt;nvl(haouv.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=cwa.organization_id(+)
),
c as (
select /*+ materialize*/
mp.organization_id,
gic.inventory_item_id,
round(nvl(gic.acctg_cost,0),9) item_cost
from
mp,
gmf_fiscal_policies gfp,
gmf_period_statuses gps,
gl_item_cst gic
where
:p_cost_type is null and
7=7 and
8=8 and
mp.legal_entity_id=gfp.legal_entity_id and
gfp.legal_entity_id=gps.legal_entity_id and
gfp.cost_type_id=gps.cost_type_id and
gps.period_id=gic.period_id and
gps.cost_type_id=gic.cost_type_id and
mp.cost_organization_id=gic.organization_id and
gic.delete_mark=0
union all
select
mp.organization_id,
ccd.inventory_item_id,
round(sum(nvl(ccd.cmpnt_cost,0)),9) item_cost
from
mp,
gmf_period_statuses gps,
cm_mthd_mst cmm,
cm_cmpt_dtl ccd,
cm_cmpt_mst_b ccmb
where
:p_cost_type is not null and
5=5 and
7=7 and
8=8 and
mp.legal_entity_id=gps.legal_entity_id and
gps.cost_type_id=cmm.cost_type_id and
gps.period_id=ccd.period_id and
gps.cost_type_id=ccd.cost_type_id and
mp.cost_organization_id=ccd.organization_id and
ccd.delete_mark=0 and
ccd.cost_cmpntcls_id=ccmb.cost_cmpntcls_id and
ccmb.product_cost_ind=1
group by
mp.organization_id,
ccd.inventory_item_id
)
select
mp.ledger,
mp.operating_unit,
mp.organization_code org_code,
onhand.period_name,
&amp;segment_columns
msiv.concatenated_segments item_number,
msiv.description item_description,
xxen_util.meaning(msiv.item_type,&apos;ITEM_TYPE&apos;,3) item_type,
misv.inventory_item_status_code_tl item_status,
xxen_util.meaning(msiv.planning_make_buy_code,&apos;MTL_PLANNING_MAKE_BUY&apos;,700) make_buy_code,
&amp;category_columns
mp.currency_code,
c.item_cost,
onhand.subinventory_code subinventory_or_intransit,
regexp_replace(msub.description,&apos;[^[:alnum:]&apos;&apos; &apos;&apos;]&apos;,null) description,
&amp;p_lot_number_opm
&amp;p_locator_opm
xxen_util.meaning(msub.asset_inventory,&apos;SYS_YES_NO&apos;,700) asset,
msiv.primary_uom_code uom_code,
sum(onhand.primary_quantity) onhand_quantity,
round(sum(onhand.primary_quantity*c.item_cost),2) onhand_value
from
mp,
(
select
gpb.organization_id,
gpb.inventory_item_id,
oap.period_name,
gpb.subinventory_code,
gpb.lot_number,
gpb.locator_id,
sum(gpb.primary_quantity) primary_quantity
from
mp,
org_acct_periods oap,
gmf_period_balances gpb
where
4=4 and
mp.organization_id=oap.organization_id and
oap.organization_id=gpb.organization_id and
oap.acct_period_id=gpb.acct_period_id
group by
gpb.organization_id,
gpb.inventory_item_id,
oap.period_name,
gpb.subinventory_code,
gpb.lot_number,
gpb.locator_id
having
sum(gpb.primary_quantity)&lt;&gt;0
) onhand,
c,
mtl_system_items_vl msiv,
mtl_item_status_vl misv,
mtl_secondary_inventories msub,
gl_code_combinations gcc,
mtl_item_locations_kfv milk,
mtl_material_statuses_vl mmsv,
mtl_lot_numbers mln
where
3=3 and
6=6 and
mp.organization_id=onhand.organization_id and
onhand.organization_id=c.organization_id and
onhand.inventory_item_id=c.inventory_item_id and
onhand.organization_id=msiv.organization_id and
onhand.inventory_item_id=msiv.inventory_item_id and
msiv.inventory_asset_flag=&apos;Y&apos; and
msiv.inventory_item_status_code=misv.inventory_item_status_code and
onhand.organization_id=msub.organization_id and
onhand.subinventory_code=msub.secondary_inventory_name and
msub.asset_inventory=1 and
msub.material_account=gcc.code_combination_id(+) and
onhand.organization_id=milk.organization_id(+) and
onhand.locator_id=milk.inventory_location_id(+) and
milk.status_id=mmsv.status_id(+) and
onhand.organization_id=mln.organization_id(+) and
onhand.inventory_item_id=mln.inventory_item_id(+) and
onhand.lot_number=mln.lot_number(+)
group by
mp.ledger,
mp.operating_unit,
mp.organization_code,
onhand.period_name,
&amp;segment_columns_grp
msiv.concatenated_segments,
msiv.description,
msiv.item_type,
misv.inventory_item_status_code_tl,
msiv.planning_make_buy_code,
mp.currency_code,
c.item_cost,
onhand.subinventory_code,
msub.description,
&amp;p_lot_number_opm_grp
&amp;p_locator_opm_grp
msub.asset_inventory,
msiv.primary_uom_code,
onhand.inventory_item_id,
onhand.organization_id
union all
select
mp.ledger,
mp.operating_unit,
mp.organization_code org_code,
oap.period_name,
&amp;segment_columns
msiv.concatenated_segments item_number,
msiv.description item_description,
xxen_util.meaning(msiv.item_type,&apos;ITEM_TYPE&apos;,3) item_type,
misv.inventory_item_status_code_tl item_status,
xxen_util.meaning(msiv.planning_make_buy_code,&apos;MTL_PLANNING_MAKE_BUY&apos;,700) make_buy_code,
&amp;category_columns
mp.currency_code,
round(onhand.itr_item_cost,9) item_cost,
xxen_util.meaning(3,&apos;MSC_CALENDAR_TYPE&apos;,700) subinventory_or_intransit,
xxen_util.meaning(2,&apos;CST_UPDATE_TXN_TYPE&apos;,700) description,
&amp;p_lot_number_opm
&amp;p_locator_opm
xxen_util.meaning(1,&apos;SYS_YES_NO&apos;,700) asset,
msiv.primary_uom_code uom_code,
round(sum(onhand.intransit_quantity),3) onhand_quantity,
round(sum(onhand.itr_intransit_value),2) onhand_value
from
mp,
org_acct_periods oap,
(
select
x.organization_id,
x.from_organization_id,
x.inventory_item_id,
x.lot_number,
x.locator_id,
x.code_combination_id,
sum(x.itr_intransit_value)/decode(sum(x.intransit_quantity),0,1,sum(x.intransit_quantity)) itr_item_cost,
sum(x.intransit_quantity) intransit_quantity,
sum(x.itr_intransit_value) itr_intransit_value
from
(
select
y.organization_id,
y.from_organization_id,
y.inventory_item_id,
y.lot_number,
y.locator_id,
nvl(mip.intransit_inv_account,y.intransit_inv_account) code_combination_id,
y.intransit_quantity,
decode(:p_cost_type,null,decode(y.opm_processed_flag,&apos;Y&apos;,nvl(y.itr_intransit_value,0),nvl(c.item_cost*y.intransit_quantity,0)),nvl(c.item_cost*y.intransit_quantity,0)) itr_intransit_value
from
(
select
decode(mmt.fob_point,1,mmt.transfer_organization_id,2,mmt.organization_id) organization_id,
decode(mmt.fob_point,1,mmt.organization_id,2,mmt.transfer_organization_id) from_organization_id,
mmt.inventory_item_id,
mtln.lot_number,
0 locator_id,
mp.intransit_inv_account,
decode(mmt.fob_point,1,inv_convert.inv_um_convert(mmt.inventory_item_id,null,-nvl(mtln.primary_quantity,mmt.primary_quantity),mmt.transaction_uom,msiv_to.primary_uom_code,null,null),2,-nvl(mtln.primary_quantity,mmt.primary_quantity)) intransit_quantity,
case
when mmt.fob_point=1 and mmt.transaction_id in (select mmt_child.parent_transaction_id from mtl_material_transactions mmt_child, gmf_xla_extract_headers gxeh where mmt_child.transaction_id=gxeh.transaction_id) then &apos;Y&apos;
when mmt.fob_point=2 and mmt.transaction_id in (select gxeh.transaction_id from gmf_xla_extract_headers gxeh) then &apos;Y&apos;
else &apos;N&apos;
end opm_processed_flag,
decode(mmt.fob_point,
1,(select nvl(sum(gxel.component_cost),0) from mtl_material_transactions mmt_child, gmf_xla_extract_headers gxeh, gmf_xla_extract_lines gxel, gmf_fiscal_policies gfp where mmt.transaction_id=mmt_child.parent_transaction_id and mmt_child.transaction_id=gxeh.transaction_id and gxeh.header_id=gxel.header_id and gxel.journal_line_type=&apos;ITR&apos; and gxeh.legal_entity_id=gfp.legal_entity_id and gxeh.valuation_cost_type_id=gfp.cost_type_id)*inv_convert.inv_um_convert(mmt.inventory_item_id,null,-nvl(mtln.primary_quantity,mmt.primary_quantity),mmt.transaction_uom,msiv_to.primary_uom_code,null,null),
2,(select nvl(sum(gxel.component_cost),0) from gmf_xla_extract_headers gxeh, gmf_xla_extract_lines gxel, gmf_fiscal_policies gfp where mmt.transaction_id=gxeh.transaction_id and gxeh.header_id=gxel.header_id and gxel.journal_line_type=&apos;ITR&apos; and gxeh.legal_entity_id=gfp.legal_entity_id and gxeh.valuation_cost_type_id=gfp.cost_type_id)*-nvl(mtln.primary_quantity,mmt.primary_quantity)
) itr_intransit_value
from
mp,
org_acct_periods oap,
mtl_material_transactions mmt,
mtl_transaction_lot_numbers mtln,
mtl_system_items_vl msiv_to
where
4=4 and
mp.organization_id=oap.organization_id and
mmt.transaction_action_id=21 and
mmt.transaction_date&gt;=oap.period_start_date-730 and
mmt.transaction_date&lt;oap.schedule_close_date+1 and
decode(mmt.fob_point,1,mmt.transfer_organization_id,2,mmt.organization_id)=mp.organization_id and
mmt.transaction_id=mtln.transaction_id(+) and
mp.organization_id=msiv_to.organization_id and
mmt.inventory_item_id=msiv_to.inventory_item_id
union all
select
decode(mmt.fob_point,1,mmt.organization_id,2,mmt.transfer_organization_id) organization_id,
decode(mmt.fob_point,1,mmt.transfer_organization_id,2,mmt.organization_id) from_organization_id,
mmt.inventory_item_id,
mtln.lot_number,
0 locator_id,
mp.intransit_inv_account,
decode(mmt.fob_point,1,-nvl(mtln.primary_quantity,mmt.primary_quantity),2,inv_convert.inv_um_convert(mmt.inventory_item_id,null,-nvl(mtln.primary_quantity,mmt.primary_quantity),mmt.transaction_uom,msiv_from.primary_uom_code,null,null)) intransit_quantity,
case
when mmt.fob_point=1 and mmt.transaction_id in (select mmt_child.parent_transaction_id from mtl_material_transactions mmt_child, gmf_xla_extract_headers gxeh where mmt_child.transaction_id=gxeh.transaction_id) then &apos;Y&apos;
when mmt.fob_point=2 and mmt.transaction_id in (select gxeh.transaction_id from gmf_xla_extract_headers gxeh) then &apos;Y&apos;
else &apos;N&apos;
end opm_processed_flag,
decode(mmt.fob_point,1,-nvl(mtln.primary_quantity,mmt.primary_quantity),2,inv_convert.inv_um_convert(mmt.inventory_item_id,null,-nvl(mtln.primary_quantity,mmt.primary_quantity),mmt.transaction_uom,msiv_from.primary_uom_code,null,null))*
(select nvl(sum(gxel.component_cost),0) from gmf_xla_extract_headers gxeh, gmf_xla_extract_lines gxel, gmf_fiscal_policies gfp where mmt.transaction_id=gxeh.transaction_id and gxeh.header_id=gxel.header_id and gxel.journal_line_type=&apos;ITR&apos; and gxeh.legal_entity_id=gfp.legal_entity_id and gxeh.valuation_cost_type_id=gfp.cost_type_id) itr_intransit_value
from
mp,
org_acct_periods oap,
mtl_material_transactions mmt,
mtl_transaction_lot_numbers mtln,
mtl_system_items_vl msiv_from
where
4=4 and
mp.organization_id=oap.organization_id and
mmt.transaction_action_id=12 and
mmt.transaction_date&lt;oap.schedule_close_date+1 and
decode(mmt.fob_point,1,mmt.organization_id,2,mmt.transfer_organization_id)=mp.organization_id and
mmt.transfer_transaction_id in (select mmt_ship.transaction_id from mtl_material_transactions mmt_ship where mmt_ship.transaction_action_id=21 and mmt_ship.transaction_date&gt;=oap.period_start_date-730 and mmt_ship.transaction_date&lt;oap.schedule_close_date+1 and decode(mmt_ship.fob_point,1,mmt_ship.transfer_organization_id,2,mmt_ship.organization_id)=mp.organization_id) and
mmt.transaction_id=mtln.transaction_id(+) and
mmt.transfer_organization_id=msiv_from.organization_id and
mmt.inventory_item_id=msiv_from.inventory_item_id
) y,
c,
mtl_interorg_parameters mip
where
y.organization_id=c.organization_id(+) and
y.inventory_item_id=c.inventory_item_id(+) and
y.from_organization_id=mip.from_organization_id(+) and
y.organization_id=mip.to_organization_id(+)
) x
group by
x.organization_id,
x.from_organization_id,
x.inventory_item_id,
x.lot_number,
x.locator_id,
x.code_combination_id
having
sum(x.intransit_quantity)&lt;&gt;0
) onhand,
mtl_system_items_vl msiv,
mtl_item_status_vl misv,
gl_code_combinations gcc,
mtl_item_locations_kfv milk,
mtl_material_statuses_vl mmsv,
mtl_lot_numbers mln
where
4=4 and
6=6 and
mp.organization_id=oap.organization_id and
mp.organization_id=onhand.organization_id and
onhand.organization_id=msiv.organization_id and
onhand.inventory_item_id=msiv.inventory_item_id and
msiv.inventory_asset_flag=&apos;Y&apos; and
msiv.inventory_item_status_code=misv.inventory_item_status_code and
onhand.code_combination_id=gcc.code_combination_id(+) and
onhand.organization_id=milk.organization_id(+) and
onhand.locator_id=milk.inventory_location_id(+) and
milk.status_id=mmsv.status_id(+) and
onhand.organization_id=mln.organization_id(+) and
onhand.inventory_item_id=mln.inventory_item_id(+) and
onhand.lot_number=mln.lot_number(+)
group by
mp.ledger,
mp.operating_unit,
mp.organization_code,
oap.period_name,
&amp;segment_columns_grp
msiv.concatenated_segments,
msiv.description,
msiv.item_type,
misv.inventory_item_status_code_tl,
msiv.planning_make_buy_code,
mp.currency_code,
round(onhand.itr_item_cost,9),
&amp;p_lot_number_opm_grp
&amp;p_locator_opm_grp
msiv.primary_uom_code,
onhand.inventory_item_id,
onhand.organization_id
order by
ledger,
operating_unit,
org_code,
item_number,
subinventory_or_intransit</SQL_TEXT>
  <VERSION_COMMENTS>Rebuilt to the Blitz Report SQL standards: the Subinventory parameter restricts the onhand rows (it had no effect), a subinventory without a description no longer shows the make or buy code as description, an organization with category accounts in more than one category set is no longer counted twice, intransit items of an organization costed through another cost organization get their item cost, organizations, item costs and periods are read once for the onhand and intransit sections. 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.00000;[Red](#,##0.00000)</NUMBER_FORMAT>
  <REPORT_TRANSLATIONS>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>US</LANGUAGE>
    <REPORT_NAME>CAC Inventory Lot and Locator OPM Value (Period-End)</REPORT_NAME>
    <DESCRIPTION>Report showing amount of inventory at the end of the month for Process Manufacturing (OPM) inventory organizations, for both onhand and intransit inventory.  If you enter a cost type this report uses the item costs from the cost type; if you leave the cost type blank it uses the item costs from the month-end snapshot.  In either case this report uses the month-end quantities, based on the entered period name, calendar code and period code.  As these quantities come from the month-end snapshot, this also also allows you to specify the lot number and locator (row/rack/bin) for your onhand quantities. And as a default valuation account, this report uses the Material Account from your subinventory setups and your Intransit Account from your Shipping Network setups.

Note:  OPM intransit balances based upon last two years of Intransit Shipments.  As of Release 12.2.13, OPM does not have a month-end snapshot for intransit quantities or balances.

General Parameters:
===================
Period Name (Closed):  the closed inventory accounting period you wish to report (mandatory).
Cost Type:  enter a Cost Type to value the quantities using the Cost Type item costs; or, if Cost Type is not entered the report will use the stored month-end snapshot values (optional).
Show OPM Lot Number:  choose Yes to show the OPM lot number for the inventory quantities.  Otherwise choose No (mandatory).
Show OPM Locator:  choose Yes to show the OPM locator and Lot Expiration Date for the inventory quantities.  Otherwise choose No (mandatory).
OPM Calendar Code:  Choose the OPM Calendar Code which corresponds to the inventory accounting period you wish to report (mandatory).
OPM Period Code:  enter the OPM Period Code related to the inventory accounting period and OPM Calendar Code you wish to report (mandatory).
Category Set 1:  the first item category set to report, typically the Cost or Product Line Category Set (optional).
Category Set 2:  The second item category set to report, typically the Inventory Category Set (optional).
Item Number:  specific buy or make item you wish to report (optional).
Subinventory:  specific area within the warehouse or inventory area you wish to report (optional).
Organization Code:  any inventory organization, defaults to your session&apos;s inventory organization (optional).
Operating Unit:  specific operating unit (optional).
Ledger:  specific ledger (optional).

/* +=============================================================================+
-- | Copyright 2024 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.                                                                           
-- +=============================================================================+
-- |
-- | Version Modified on Modified by Description
-- | ======= =========== ============== =========================================
-- | 1.0     10 May 2024 Douglas Volz Initial Coding.
-- | 1.1     30 Jun 2024 Douglas Volz Cumulative fixes for OPM intransit balances and accounts.
-- | 1.2     02 Aug 2024 Douglas Volz Add OPM Cost Organizations to get correct item costs and qtys.
-- | 1.3     30 Sep 2024 Douglas Volz Add Lot expiration date to report.
-- |  1.4     24 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_ROW>
    <CATEGORY>R12 only</CATEGORY>
   </CATEGORY_ASSIGNMENTS_ROW>
  </CATEGORY_ASSIGNMENTS>
  <ANCHORS>
   <ANCHORS_ROW>
    <ANCHOR>&amp;category_columns</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>&amp;p_locator_opm</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>&amp;p_locator_opm_grp</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>&amp;p_lot_number_opm</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>&amp;p_lot_number_opm_grp</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>&amp;segment_columns</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>&amp;segment_columns_grp</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_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>8=8</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>:p_cost_type</ANCHOR>
   </ANCHORS_ROW>
  </ANCHORS>
  <PARAMETERS>
   <PARAMETERS_ROW>
    <SORT_ORDER>1</SORT_ORDER>
    <DISPLAY_SEQUENCE>-10</DISPLAY_SEQUENCE>
    <ANCHOR>&amp;segment_columns</ANCHOR>
    <SQL_TEXT>select
&apos;gcc.&apos;||lower(fifsv.application_column_name)||&apos; &quot;&apos;||fifsv.form_left_prompt||&apos;&quot;,&apos; text
from
fnd_id_flex_segments_vl 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>
    <ANCHOR>&amp;segment_columns_grp</ANCHOR>
    <SQL_TEXT>select
&apos;gcc.&apos;||lower(fifsv.application_column_name)||&apos;,&apos; text
from
fnd_id_flex_segments_vl 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_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>3</SORT_ORDER>
    <DISPLAY_SEQUENCE>10</DISPLAY_SEQUENCE>
    <ANCHOR>4=4</ANCHOR>
    <SQL_TEXT>oap.period_name=:p_period_name</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>INV Period (closed and summarized)</LOV_NAME>
    <LOV_GUID>91E8680353217CCEE053BB6B635868C2</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_close_date is not null and
oap.summarized_flag=&apos;Y&apos; and
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 (oap.period_close_date is not null and oap.summarized_flag = &apos;Y&apos;)
  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 (Closed)</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>4</SORT_ORDER>
    <DISPLAY_SEQUENCE>20</DISPLAY_SEQUENCE>
    <ANCHOR>5=5</ANCHOR>
    <SQL_TEXT>cmm.cost_mthd_code=:p_cost_type</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>Cost Type (OPM Orgs Only)</LOV_NAME>
    <LOV_GUID>184A903C40A16B3FE0630100007F2880</LOV_GUID>
    <LOV_QUERY_DSP>select
x.value, 
x.description
from
(
 select cmm.cost_mthd_code value, cmm.cost_mthd_desc description
 from   cm_mthd_mst cmm
 where exists 
 (select &apos;x&apos;
  from  gmf_calendar_assignments gca,
        org_organization_definitions ood,
        mtl_parameters mp
  where gca.legal_entity_id = ood.legal_entity
  and   gca.cost_type_id    = cmm.cost_type_id 
  and   (ood.organization_id = fnd_profile.value(&apos;MFG_ORGANIZATION_ID&apos;) or mp.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   mp.organization_id = ood.organization_id
  and   nvl(ood.disable_date,sysdate) &gt;= sysdate
  and   mp.organization_id &lt;&gt; mp.master_organization_id
  and   mp.process_enabled_flag = &apos;Y&apos;
 )
) x
group by
       x.value,
       x.description
order by value
</LOV_QUERY_DSP>
    <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>5</SORT_ORDER>
    <ANCHOR>:p_cost_type</ANCHOR>
    <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>6</SORT_ORDER>
    <DISPLAY_SEQUENCE>30</DISPLAY_SEQUENCE>
    <ANCHOR>&amp;p_lot_number_opm</ANCHOR>
    <SQL_TEXT>onhand.lot_number,
mln.expiration_date,</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>
    <MATCHING_VALUE>Y</MATCHING_VALUE>
    <DEFAULT_VALUE>Y</DEFAULT_VALUE>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Show Lot Number</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>7</SORT_ORDER>
    <ANCHOR>&amp;p_lot_number_opm_grp</ANCHOR>
    <SQL_TEXT>onhand.lot_number,
mln.expiration_date,</SQL_TEXT>
    <MATCHING_VALUE>Y</MATCHING_VALUE>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Show Lot Number</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>8</SORT_ORDER>
    <DISPLAY_SEQUENCE>40</DISPLAY_SEQUENCE>
    <ANCHOR>&amp;p_locator_opm</ANCHOR>
    <SQL_TEXT>milk.concatenated_segments locator,
mmsv.status_code,</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>
    <MATCHING_VALUE>Y</MATCHING_VALUE>
    <DEFAULT_VALUE>Y</DEFAULT_VALUE>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Show Locator</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>9</SORT_ORDER>
    <ANCHOR>&amp;p_locator_opm_grp</ANCHOR>
    <SQL_TEXT>milk.concatenated_segments,
mmsv.status_code,</SQL_TEXT>
    <MATCHING_VALUE>Y</MATCHING_VALUE>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Show Locator</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>10</SORT_ORDER>
    <DISPLAY_SEQUENCE>50</DISPLAY_SEQUENCE>
    <ANCHOR>7=7</ANCHOR>
    <SQL_TEXT>gps.calendar_code=:p_calendar_code</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>OPM Calendar Code</LOV_NAME>
    <LOV_GUID>19508EAA42357240E0630100007FD049</LOV_GUID>
    <LOV_QUERY_DSP>select  ccht.calendar_code value, ccht.calendar_desc description
from    cm_cldr_hdr_tl ccht
where   ccht.language = userenv(&apos;lang&apos;)
and exists
(select &apos;x&apos;
 from   gmf_calendar_assignments gca,
        gmf_period_statuses gps,
        org_organization_definitions ood,
        mtl_parameters mp 
 where  gca.calendar_code = gps.calendar_code
 and    gca.legal_entity_id = gps.legal_entity_id
 and    gca.legal_entity_id = ood.legal_entity
 and    gca.calendar_code = ccht.calendar_code
 and    (ood.organization_id = fnd_profile.value(&apos;MFG_ORGANIZATION_ID&apos;) or mp.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    mp.organization_id = ood.organization_id
 and    nvl(ood.disable_date,sysdate) &gt;= sysdate
 and    mp.organization_id &lt;&gt; mp.master_organization_id
)
order by value desc</LOV_QUERY_DSP>
    <DEFAULT_VALUE>select  ccht.calendar_code value, ccht.calendar_desc description
from    cm_cldr_hdr_tl ccht
where   ccht.language = userenv(&apos;lang&apos;)
and     ccht.calendar_code =
(select gps.calendar_code
 from   gmf_calendar_assignments gca,
        gmf_period_statuses gps,
        ic_cldr_dtl icd,
        org_acct_periods oap,
        org_organization_definitions ood,
        mtl_parameters mp 
 where  gca.calendar_code = gps.calendar_code
 and    gca.legal_entity_id = gps.legal_entity_id
 and    gca.legal_entity_id = ood.legal_entity
 and    gca.calendar_code = ccht.calendar_code
 -- added to tie the OPM period_id with the inventory acct_period_id
 and    icd.fiscal_year = oap.period_year
 and    icd.period_end_date = oap.schedule_close_date
 and    icd.period = oap.period_num
 and    icd.period_id = gps.period_id
 and    oap.organization_id = mp.organization_id
 and    oap.period_name = :$flex$.period_name_closed
 and    (ood.organization_id = fnd_profile.value(&apos;MFG_ORGANIZATION_ID&apos;) or mp.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    mp.organization_id = ood.organization_id
 and    nvl(ood.disable_date,sysdate) &gt;= sysdate
 and    mp.organization_id &lt;&gt; mp.master_organization_id
 group by gps.calendar_code
)
order by value desc</DEFAULT_VALUE>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>OPM Calendar Code</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>11</SORT_ORDER>
    <DISPLAY_SEQUENCE>60</DISPLAY_SEQUENCE>
    <ANCHOR>8=8</ANCHOR>
    <SQL_TEXT>gps.period_code=:p_period_code</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>OPM Period Code</LOV_NAME>
    <LOV_GUID>19508EAA42367240E0630100007FD049</LOV_GUID>
    <LOV_QUERY_DSP>select  ccd.period_code value, ccd.period_desc description
from    cm_cldr_dtl ccd
where exists
        (select 1 
         from   gmf_period_statuses gps
         where  gps.calendar_code   = :$flex$.opm_calendar_code
         and    gps.legal_entity_id in (select ood.legal_entity from org_organization_definitions ood 
                                        where  (ood.organization_id = fnd_profile.value(&apos;MFG_ORGANIZATION_ID&apos;)
                                        or      ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;INV&apos;)))
        and    gps.period_code      = ccd.period_code
        and    gps.calendar_code    = ccd.calendar_code
       )
order by ccd.end_date desc</LOV_QUERY_DSP>
    <DEFAULT_VALUE>
</DEFAULT_VALUE>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>OPM Period Code</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>12</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;onhand&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>13</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;onhand&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>14</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;onhand&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>15</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 Number (costing enabled only items)</LOV_NAME>
    <LOV_GUID>EC5CECA520510FC8E0530100007F5EB3</LOV_GUID>
    <LOV_QUERY_DSP>select distinct
msiv.concatenated_segments value,
msiv.description
from
mtl_system_items_vl msiv,
mtl_parameters mp
where
msiv.organization_id = mp.organization_id and
(xxen_util.contains(:$flex$.organization_code,mp.organization_code) = &apos;Y&apos; or
 (:$flex$.organization_code 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; or mp.process_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>16</SORT_ORDER>
    <DISPLAY_SEQUENCE>110</DISPLAY_SEQUENCE>
    <ANCHOR>3=3</ANCHOR>
    <SQL_TEXT>msub.secondary_inventory_name=:p_subinventory</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>INV Subinventory</LOV_NAME>
    <LOV_GUID>8E2FF36EDF2A79D2E0530100007F1FF2</LOV_GUID>
    <LOV_QUERY_DSP>select
msi.secondary_inventory_name value,
msi.description||&apos; (&apos;||mp.organization_code||&apos;)&apos; description
from
mtl_parameters mp,
mtl_secondary_inventories msi
where
(
:$flex$.organization_code is null and (
fnd_profile.value(&apos;MFG_ORGANIZATION_ID&apos;) is null and msi.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;INV&apos;) or
msi.organization_id=fnd_profile.value(&apos;MFG_ORGANIZATION_ID&apos;)
) or
xxen_util.contains(:$flex$.organization_code,mp.organization_code)=&apos;Y&apos; or
xxen_util.contains(:$flex$.organization_code,mp.organization_id)=&apos;Y&apos;
) and
mp.organization_id=msi.organization_id
order by
mp.organization_code,
msi.secondary_inventory_name</LOV_QUERY_DSP>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Subinventory</PARAMETER_NAME>
      <DESCRIPTION>Restricts the onhand rows to this subinventory; intransit rows are listed regardless.</DESCRIPTION>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>17</SORT_ORDER>
    <DISPLAY_SEQUENCE>120</DISPLAY_SEQUENCE>
    <ANCHOR>2=2</ANCHOR>
    <SQL_TEXT>mp.organization_code=:p_org_code</SQL_TEXT>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>INV Organization Code (excluding master)</LOV_NAME>
    <LOV_GUID>91D3167A33531204E053BB6B63585EE3</LOV_GUID>
    <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
ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type=&apos;INV&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>18</SORT_ORDER>
    <DISPLAY_SEQUENCE>130</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>19</SORT_ORDER>
    <DISPLAY_SEQUENCE>140</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_ROW>
    <SORT_ORDER>20</SORT_ORDER>
    <ANCHOR>&amp;p_lot_number_grp</ANCHOR>
    <SQL_TEXT>    onhand.lot_number,</SQL_TEXT>
    <MATCHING_VALUE>Y</MATCHING_VALUE>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Show OPM Lot Number</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>21</SORT_ORDER>
    <ANCHOR>&amp;p_lot_number_opm</ANCHOR>
    <SQL_TEXT>    decode (:p_lot_number, &apos;Y&apos;, onhand.lot_number, null) Lot_Number,</SQL_TEXT>
    <MATCHING_VALUE>Y</MATCHING_VALUE>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Show OPM Lot Number</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
  </PARAMETERS>
  <TEMPLATES>
   <TEMPLATES_ROW>
    <GUID>52BE05026A1D94C2163626C9C09B403B</GUID>
    <TEMPLATE_NAME>Pivot by Org</TEMPLATE_NAME>
    <DYNAMIC_COLUMNS>Y</DYNAMIC_COLUMNS>
    <OWNER>MFG</OWNER>
    <TEMPLATE_COLUMNS>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>23</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ASSET</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>7</DISPLAY_SEQUENCE>
      <COLUMN_NAME>Account</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>19</DISPLAY_SEQUENCE>
      <COLUMN_NAME>CURRENCY_CODE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>17</DISPLAY_SEQUENCE>
      <COLUMN_NAME>Class</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>5</DISPLAY_SEQUENCE>
      <COLUMN_NAME>Company</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>22</DISPLAY_SEQUENCE>
      <COLUMN_NAME>DESCRIPTION</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>6</DISPLAY_SEQUENCE>
      <COLUMN_NAME>Department</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>16</DISPLAY_SEQUENCE>
      <COLUMN_NAME>Family</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>20</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ITEM_COST</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>11</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ITEM_DESCRIPTION</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>10</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ITEM_NUMBER</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>13</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ITEM_STATUS</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>12</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ITEM_TYPE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>18</DISPLAY_SEQUENCE>
      <COLUMN_NAME>Inv.Items Description</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>1</DISPLAY_SEQUENCE>
      <COLUMN_NAME>LEDGER</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>25</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ONHAND_QUANTITY</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>26</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ONHAND_VALUE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>2</DISPLAY_SEQUENCE>
      <COLUMN_NAME>OPERATING_UNIT</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>3</DISPLAY_SEQUENCE>
      <COLUMN_NAME>ORG_CODE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>4</DISPLAY_SEQUENCE>
      <COLUMN_NAME>PERIOD_NAME</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>9</DISPLAY_SEQUENCE>
      <COLUMN_NAME>Product</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>14</DISPLAY_SEQUENCE>
      <COLUMN_NAME>Product Cat</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>15</DISPLAY_SEQUENCE>
      <COLUMN_NAME>Product Description</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>21</DISPLAY_SEQUENCE>
      <COLUMN_NAME>SUBINVENTORY_OR_INTRANSIT</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>8</DISPLAY_SEQUENCE>
      <COLUMN_NAME>Sub-Account</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
     <TEMPLATE_COLUMNS_ROW>
      <DISPLAY_SEQUENCE>24</DISPLAY_SEQUENCE>
      <COLUMN_NAME>UOM_CODE</COLUMN_NAME>
     </TEMPLATE_COLUMNS_ROW>
    </TEMPLATE_COLUMNS>
    <TEMPLATE_PIVOT>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>LEDGER</COLUMN_NAME>
      <FIELD_TYPE>ROW</FIELD_TYPE>
      <DISPLAY_SEQUENCE>1</DISPLAY_SEQUENCE>
     </TEMPLATE_PIVOT_ROW>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>ONHAND_QUANTITY</COLUMN_NAME>
      <FIELD_TYPE>VALUE</FIELD_TYPE>
      <DISPLAY_SEQUENCE>1</DISPLAY_SEQUENCE>
      <AGGREGATION>SUM</AGGREGATION>
     </TEMPLATE_PIVOT_ROW>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>ONHAND_VALUE</COLUMN_NAME>
      <FIELD_TYPE>VALUE</FIELD_TYPE>
      <DISPLAY_SEQUENCE>2</DISPLAY_SEQUENCE>
      <AGGREGATION>SUM</AGGREGATION>
     </TEMPLATE_PIVOT_ROW>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>OPERATING_UNIT</COLUMN_NAME>
      <FIELD_TYPE>ROW</FIELD_TYPE>
      <DISPLAY_SEQUENCE>2</DISPLAY_SEQUENCE>
     </TEMPLATE_PIVOT_ROW>
     <TEMPLATE_PIVOT_ROW>
      <COLUMN_NAME>ORG_CODE</COLUMN_NAME>
      <FIELD_TYPE>ROW</FIELD_TYPE>
      <DISPLAY_SEQUENCE>3</DISPLAY_SEQUENCE>
     </TEMPLATE_PIVOT_ROW>
    </TEMPLATE_PIVOT>
    <TEMPLATE_SHARED_STRINGS>
    </TEMPLATE_SHARED_STRINGS>
    <TEMPLATE_PARAMETER_DEFAULTS>
    </TEMPLATE_PARAMETER_DEFAULTS>
    <TEMPLATE_STYLES>
    </TEMPLATE_STYLES>
    <TEMPLATE_SHARING>
     <TEMPLATE_SHARING_ROW>
      <SHARING_LEVEL>S</SHARING_LEVEL>
      <LEVEL_VALUE>Site</LEVEL_VALUE>
     </TEMPLATE_SHARING_ROW>
    </TEMPLATE_SHARING>
    <PARAMETER_EXCLUSION>
    </PARAMETER_EXCLUSION>
    <TEMPLATE_CONDITIONS>
    </TEMPLATE_CONDITIONS>
   </TEMPLATES_ROW>
  </TEMPLATES>
  <DEFAULT_TEMPLATES>
   <DEFAULT_TEMPLATES_ROW>
    <USER_NAME>MFG</USER_NAME>
    <TEMPLATE_GUID>52BE05026A1D94C2163626C9C09B403B</TEMPLATE_GUID>
   </DEFAULT_TEMPLATES_ROW>
   <DEFAULT_TEMPLATES_ROW>
    <TEMPLATE_GUID>52BE05026A1D94C2163626C9C09B403B</TEMPLATE_GUID>
   </DEFAULT_TEMPLATES_ROW>
  </DEFAULT_TEMPLATES>
  <UPLOAD_COLUMNS>
  </UPLOAD_COLUMNS>
  <UPLOAD_PARAMETERS>
  </UPLOAD_PARAMETERS>
  <UPLOAD_SQLS>
  </UPLOAD_SQLS>
 </REPORTS_ROW>
</REPORTS>
</ROOT>
