<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 and Intransit Value (Period-End) - Discrete/OPM -->
 <REPORTS_ROW>
  <GUID>FC6F256BF2E4159FE0530100007F928F</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,
nvl(mp.process_enabled_flag,&apos;N&apos;) process_enabled_flag,
mp.default_cost_group_id,
case
when mp.cost_group_accounting=1 then &apos;Y&apos;
when mp.organization_id in (select pop.organization_id from pjm_org_parameters pop) then &apos;Y&apos;
when mp.primary_cost_method in (2,5,6) then &apos;Y&apos;
when mp.process_enabled_flag=&apos;Y&apos; then null
when mp.wms_enabled_flag=&apos;Y&apos; then &apos;Y&apos;
end cost_group_accounting,
(select max(mca.category_set_id) from mtl_category_accounts mca where mp.organization_id=mca.organization_id) category_set_id,
mp.intransit_inv_account,
gl.currency_code
from
mtl_parameters mp,
hr_organization_information hoi,
hr_all_organization_units haou,
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.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=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_show_opm_inventory=&apos;Y&apos; and
:p_cost_type is null and
7=7 and
8=8 and
mp.process_enabled_flag=&apos;Y&apos; 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_show_opm_inventory=&apos;Y&apos; and
cmm.cost_mthd_code=upper(:p_cost_type) and
7=7 and
8=8 and
mp.process_enabled_flag=&apos;Y&apos; 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
onhand.ledger,
onhand.operating_unit,
onhand.org_code,
onhand.period_name,
&amp;segment_columns
onhand.item_number,
onhand.item_description,
xxen_util.meaning(onhand.item_type,&apos;ITEM_TYPE&apos;,3) item_type,
misv.inventory_item_status_code_tl item_status,
xxen_util.meaning(onhand.planning_make_buy_code,&apos;MTL_PLANNING_MAKE_BUY&apos;,700) make_buy_code,
&amp;category_columns
onhand.currency_code,
onhand.item_cost,
onhand.subinventory_or_intransit,
onhand.description,
onhand.asset,
onhand.uom_code,
onhand.onhand_quantity,
onhand.onhand_value
from
(
select
y.ledger,
y.operating_unit,
y.org_code,
y.period_name,
y.organization_id,
y.inventory_item_id,
y.item_number,
y.item_description,
y.item_type,
y.inventory_item_status_code,
y.planning_make_buy_code,
y.currency_code,
round(decode(cic.inventory_item_id,null,y.rollback_value/decode(y.rollback_quantity,0,1,y.rollback_quantity),cic.item_cost),5) item_cost,
y.subinventory_code,
nvl(y.subinventory_code,xxen_util.meaning(3,&apos;MSC_CALENDAR_TYPE&apos;,700)) subinventory_or_intransit,
decode(y.subinventory_code,null,xxen_util.meaning(3,&apos;MSC_CALENDAR_TYPE&apos;,700),y.subinventory_description) description,
xxen_util.yes(decode(y.asset_inventory,2,null,&apos;Y&apos;)) asset,
y.primary_uom_code uom_code,
round(y.rollback_quantity,3) onhand_quantity,
round(decode(cic.inventory_item_id,null,y.rollback_value,y.rollback_quantity*cic.item_cost),2) onhand_value,
y.material_account
from
(
select
x.*,
msub.description subinventory_description,
msub.asset_inventory,
case
when x.subinventory_code is null then (select min(mip.intransit_inv_account) from mtl_interorg_parameters mip where mip.fob_point=1 and mip.to_organization_id=x.organization_id or mip.fob_point=2 and mip.from_organization_id=x.organization_id)
when x.category_set_id is not null then
(
select
max(mca.material_account) keep (dense_rank first order by mca.subinventory_code nulls last)
from
mtl_item_categories mic,
mtl_category_accounts mca
where
x.organization_id=mic.organization_id and
x.inventory_item_id=mic.inventory_item_id and
x.category_set_id=mic.category_set_id and
mic.organization_id=mca.organization_id and
mic.category_id=mca.category_id and
(mca.subinventory_code=x.subinventory_code or mca.subinventory_code is null)
)
when x.cost_group_accounting=&apos;Y&apos; then (select ccga.material_account from cst_cost_group_accounts ccga where nvl(msub.default_cost_group_id,x.default_cost_group_id)=ccga.cost_group_id and x.organization_id=ccga.organization_id)
else msub.material_account
end material_account
from
(
select
mp.ledger,
mp.operating_unit,
mp.organization_code org_code,
oap.period_name,
mp.currency_code,
mp.organization_id,
mp.default_cost_group_id,
mp.cost_group_accounting,
mp.category_set_id,
msiv.inventory_item_id,
msiv.concatenated_segments item_number,
msiv.description item_description,
msiv.item_type,
msiv.inventory_item_status_code,
msiv.planning_make_buy_code,
msiv.primary_uom_code,
cpcs.subinventory_code,
sum(cpcs.rollback_quantity) rollback_quantity,
sum(cpcs.rollback_value) rollback_value
from
mp,
org_acct_periods oap,
cst_period_close_summary cpcs,
mtl_system_items_vl msiv
where
4=4 and
6=6 and
mp.process_enabled_flag=&apos;N&apos; and
mp.organization_id=oap.organization_id and
oap.acct_period_id=cpcs.acct_period_id and
cpcs.rollback_quantity&lt;&gt;0 and
cpcs.organization_id=msiv.organization_id and
cpcs.inventory_item_id=msiv.inventory_item_id and
msiv.inventory_asset_flag=&apos;Y&apos;
group by
mp.ledger,
mp.operating_unit,
mp.organization_code,
oap.period_name,
mp.currency_code,
mp.organization_id,
mp.default_cost_group_id,
mp.cost_group_accounting,
mp.category_set_id,
msiv.inventory_item_id,
msiv.concatenated_segments,
msiv.description,
msiv.item_type,
msiv.inventory_item_status_code,
msiv.planning_make_buy_code,
msiv.primary_uom_code,
cpcs.subinventory_code
) x,
mtl_secondary_inventories msub
where
x.organization_id=msub.organization_id(+) and
x.subinventory_code=msub.secondary_inventory_name(+)
) y,
(select cic.organization_id, cic.inventory_item_id, nvl(cic.item_cost,0) item_cost from cst_cost_types cct, cst_item_costs cic where cct.cost_type=:p_cost_type and cct.cost_type_id=cic.cost_type_id) cic
where
y.organization_id=cic.organization_id(+) and
y.inventory_item_id=cic.inventory_item_id(+) and
(cic.inventory_item_id is not null or :p_cost_type is null)
union all
select
mp.ledger,
mp.operating_unit,
mp.organization_code org_code,
q.period_name,
q.organization_id,
q.inventory_item_id,
msiv.concatenated_segments item_number,
msiv.description item_description,
msiv.item_type,
msiv.inventory_item_status_code,
msiv.planning_make_buy_code,
mp.currency_code,
c.item_cost,
q.subinventory_code,
q.subinventory_code subinventory_or_intransit,
msub.description,
xxen_util.yes(&apos;Y&apos;) asset,
msiv.primary_uom_code uom_code,
q.primary_quantity onhand_quantity,
round(q.primary_quantity*c.item_cost,2) onhand_value,
msub.material_account
from
mp,
(
select
gpb.organization_id,
gpb.inventory_item_id,
oap.period_name,
gpb.subinventory_code,
sum(gpb.primary_quantity) primary_quantity
from
mp,
org_acct_periods oap,
gmf_period_balances gpb
where
:p_show_opm_inventory=&apos;Y&apos; and
4=4 and
mp.process_enabled_flag=&apos;Y&apos; 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
having
sum(gpb.primary_quantity)&lt;&gt;0
) q,
c,
mtl_system_items_vl msiv,
mtl_secondary_inventories msub
where
6=6 and
mp.organization_id=q.organization_id and
q.organization_id=c.organization_id and
q.inventory_item_id=c.inventory_item_id and
q.organization_id=msiv.organization_id and
q.inventory_item_id=msiv.inventory_item_id and
msiv.inventory_asset_flag=&apos;Y&apos; and
q.organization_id=msub.organization_id and
q.subinventory_code=msub.secondary_inventory_name and
msub.asset_inventory=1
union all
select
mp.ledger,
mp.operating_unit,
mp.organization_code org_code,
oap.period_name,
x.organization_id,
x.inventory_item_id,
msiv.concatenated_segments item_number,
msiv.description item_description,
msiv.item_type,
msiv.inventory_item_status_code,
msiv.planning_make_buy_code,
mp.currency_code,
round(x.itr_item_cost,9) item_cost,
null subinventory_code,
xxen_util.meaning(3,&apos;MSC_CALENDAR_TYPE&apos;,700) subinventory_or_intransit,
xxen_util.meaning(3,&apos;MSC_CALENDAR_TYPE&apos;,700) description,
xxen_util.yes(&apos;Y&apos;) asset,
msiv.primary_uom_code uom_code,
round(sum(x.intransit_quantity),3) onhand_quantity,
round(sum(x.itr_intransit_value),2) onhand_value,
x.code_combination_id material_account
from
mp,
org_acct_periods oap,
(
select
y.organization_id,
y.from_organization_id,
y.inventory_item_id,
y.code_combination_id,
sum(y.itr_intransit_value)/decode(sum(y.intransit_quantity),0,1,sum(y.intransit_quantity)) itr_item_cost,
sum(y.intransit_quantity) intransit_quantity,
sum(y.itr_intransit_value) itr_intransit_value
from
(
select
z.organization_id,
z.from_organization_id,
z.inventory_item_id,
nvl(mip.intransit_inv_account,z.intransit_inv_account) code_combination_id,
z.intransit_quantity,
decode(:p_cost_type,null,decode(z.opm_processed_flag,&apos;Y&apos;,nvl(z.itr_intransit_value,0),nvl(c.item_cost*z.intransit_quantity,0)),nvl(c.item_cost*z.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,
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
:p_show_opm_inventory=&apos;Y&apos; and
4=4 and
mp.process_enabled_flag=&apos;Y&apos; 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,
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
:p_show_opm_inventory=&apos;Y&apos; and
4=4 and
mp.process_enabled_flag=&apos;Y&apos; 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
) z,
c,
mtl_interorg_parameters mip
where
z.organization_id=c.organization_id(+) and
z.inventory_item_id=c.inventory_item_id(+) and
z.from_organization_id=mip.from_organization_id(+) and
z.organization_id=mip.to_organization_id(+)
) y
group by
y.organization_id,
y.from_organization_id,
y.inventory_item_id,
y.code_combination_id
having
sum(y.intransit_quantity)&lt;&gt;0
) x,
mtl_system_items_vl msiv
where
4=4 and
6=6 and
mp.organization_id=oap.organization_id and
mp.organization_id=x.organization_id and
x.organization_id=msiv.organization_id and
x.inventory_item_id=msiv.inventory_item_id and
msiv.inventory_asset_flag=&apos;Y&apos;
group by
mp.ledger,
mp.operating_unit,
mp.organization_code,
oap.period_name,
x.organization_id,
x.inventory_item_id,
msiv.concatenated_segments,
msiv.description,
msiv.item_type,
msiv.inventory_item_status_code,
msiv.planning_make_buy_code,
mp.currency_code,
round(x.itr_item_cost,9),
msiv.primary_uom_code,
x.code_combination_id
) onhand,
mtl_item_status_vl misv,
gl_code_combinations gcc
where
3=3 and
onhand.inventory_item_status_code=misv.inventory_item_status_code and
onhand.material_account=gcc.code_combination_id(+)
order by
onhand.ledger,
onhand.operating_unit,
onhand.org_code,
onhand.item_number,
onhand.subinventory_or_intransit</SQL_TEXT>
  <VERSION_COMMENTS>Rebuilt to the Blitz Report SQL standards: discrete organizations without cost group or category accounting are reported with their subinventory onhand (the valuation account join filtered the outer joined accounts without (+), and its standard cost branch only covered OPM organizations, so only their intransit rows were listed), the discrete rows use the logic of CAC Inventory and Intransit Value (Period-End), the Subinventory parameter applies to all sections, a subinventory without a description no longer shows the make or buy code, descriptions keep their special characters, Asset shows Yes or blank, intransit items of an OPM organization costed through another cost organization get their item cost. 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 and Intransit Value (Period-End) - Discrete/OPM</REPORT_NAME>
    <DESCRIPTION>Report showing amount of inventory at the end of the month for both Discrete and Process Manufacturing (OPM) inventory organizations.  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, OPM calendar code and OPM period code.  And as these quantities come from the month-end snapshot (created when you close the inventory accounting period or run the GMF Period Close Process) and this snapshot is only by inventory organization, subinventory and item and not split out by cost element, this report only shows the Material Account, based upon your Costing Method.

Notes:  
1)  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.
2)  This report assumes you use both OPM and Discrete Manufacturing.  If you only use Discrete Manufacturing, use the CAC Inventory and Intransit Value (Period-End) report instead.

Discrete and OPM 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).
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 2009 - 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.                                                                           
-- +=============================================================================+
-- | 1.0     27 Sep 2009 Douglas Volz Initial Coding
-- | 1.29    04 Jun 2024 Douglas Volz Fix SQL error for Intransit code section, due to duplicate transactions in GXEH
-- |                                  (ORA-01427: single-row subquery returns more than one row).
-- | 1.30    09 Jun 2024 Douglas Volz Fix Intransit joins for UOMs, organizations and for duplicate Intransit quantities.
-- | 1.31    25 Jun 2024 Douglas Volz Add Period Code join for Intransit, to avoid cross-joining.
-- | 1.32    27 Jun 2024 Douglas Volz Get intransit account based on the Shipping Network, to avoid duplicate quantities.
-- | 1.33    28 Jun 2024 Douglas Volz Base Intransit SQL logic on two years of Intransit Shipments, not on mtl_supply.
-- | 1.34    30 Jun 2024 Douglas Volz Get default intransit account in case the Shipping Network has been deleted or changed.
-- | 1.35    08 Aug 2024 Douglas Volz Add OPM Cost Organizations to get correct item costs and qtys.
-- |  1.36    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>
  <ANCHORS>
   <ANCHORS_ROW>
    <ANCHOR>&amp;category_columns</ANCHOR>
   </ANCHORS_ROW>
   <ANCHORS_ROW>
    <ANCHOR>&amp;segment_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_ROW>
    <ANCHOR>4=4</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_ROW>
    <ANCHOR>:p_show_opm_inventory</ANCHOR>
   </ANCHORS_ROW>
  </ANCHORS>
  <PARAMETERS>
   <PARAMETERS_ROW>
    <SORT_ORDER>1</SORT_ORDER>
    <DISPLAY_SEQUENCE>-20</DISPLAY_SEQUENCE>
    <ANCHOR>:p_show_opm_inventory</ANCHOR>
    <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>select nvl(&apos;Y&apos;,&apos;N&apos;)
from
fnd_lookups fl,
mtl_parameters mp,
org_organization_definitions ood
where fl.lookup_type=&apos;YES_NO&apos;
 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    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;
and     rownum &lt; 2</DEFAULT_VALUE>
    <REQUIRED>Y</REQUIRED>
    <PARAMETER_TRANSLATIONS>
     <PARAMETER_TRANSLATIONS_ROW>
      <LANGUAGE>US</LANGUAGE>
      <PARAMETER_NAME>Show OPM Inventory</PARAMETER_NAME>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>2</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>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>:p_cost_type</ANCHOR>
    <PARAMETER_TYPE_DSP>LOV</PARAMETER_TYPE_DSP>
    <LOV_NAME>CST Cost Type (Discrete and OPM orgs, no AvgRates)</LOV_NAME>
    <LOV_GUID>FC6F255D5E1115A1E0530100007FEC4E</LOV_GUID>
    <LOV_QUERY_DSP>select
x.value, 
x.description
from
(select cct.cost_type value, cct.description
 from   cst_cost_types cct
 where  nvl(cct.disable_date,sysdate) &gt;= sysdate
 and    cct.organization_id is null
 and    cct.cost_type_id not in (select mp.avg_rates_cost_type_id from mtl_parameters mp where mp.avg_rates_cost_type_id is not null)
 union
 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;
 )
 and not exists 
 (select &apos;x&apos;
 from   cst_cost_types cct
 where  cct.cost_type = cmm.cost_mthd_code
 and    cct.organization_id is null
 and nvl(cct.disable_date,sysdate) &gt;= sysdate
 )
) 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>
    <DISPLAY_SEQUENCE>30</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>6</SORT_ORDER>
    <DISPLAY_SEQUENCE>40</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>7</SORT_ORDER>
    <DISPLAY_SEQUENCE>50</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>8</SORT_ORDER>
    <DISPLAY_SEQUENCE>60</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>9</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>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>10</SORT_ORDER>
    <DISPLAY_SEQUENCE>80</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>11</SORT_ORDER>
    <DISPLAY_SEQUENCE>90</DISPLAY_SEQUENCE>
    <ANCHOR>3=3</ANCHOR>
    <SQL_TEXT>onhand.subinventory_code=: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>
     </PARAMETER_TRANSLATIONS_ROW>
    </PARAMETER_TRANSLATIONS>
   </PARAMETERS_ROW>
   <PARAMETERS_ROW>
    <SORT_ORDER>12</SORT_ORDER>
    <DISPLAY_SEQUENCE>100</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>13</SORT_ORDER>
    <DISPLAY_SEQUENCE>110</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>14</SORT_ORDER>
    <DISPLAY_SEQUENCE>120</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_ROW>
    <GUID>72166F01A51B3B9E1C8A44AC1FF5FC1D</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>72166F01A51B3B9E1C8A44AC1FF5FC1D</TEMPLATE_GUID>
   </DEFAULT_TEMPLATES_ROW>
   <DEFAULT_TEMPLATES_ROW>
    <TEMPLATE_GUID>72166F01A51B3B9E1C8A44AC1FF5FC1D</TEMPLATE_GUID>
   </DEFAULT_TEMPLATES_ROW>
  </DEFAULT_TEMPLATES>
  <UPLOAD_COLUMNS>
  </UPLOAD_COLUMNS>
  <UPLOAD_PARAMETERS>
  </UPLOAD_PARAMETERS>
  <UPLOAD_SQLS>
  </UPLOAD_SQLS>
 </REPORTS_ROW>
</REPORTS>
</ROOT>
