INV Inventory Items

➜ Back to Library
Description
Categories: Enginatics
Repository: Github
Master data report that lists item master attributes such as item type, UOM, status, serial control, account numbers and various other attributes
Run INV Inventory Items and other Oracle EBS reports with Blitz Report™ on our demo environment
select
mp.organization_code,
haouv.name organization_name,
xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) user_item_type,
msiv.concatenated_segments item,
msiv.description,
msiv.long_description,
&category_columns
misv.inventory_item_status_code_tl item_status,
muomv.unit_of_measure_tl primary_uom,
msiv.unit_weight,
msiv.weight_uom_code weight_uom,
msiv.unit_volume,
msiv.volume_uom_code volume_uom,
msiv.unit_length,
msiv.unit_width,
msiv.unit_height,
msiv.dimension_uom_code dimension_uom,
xxen_util.meaning(msiv.lot_control_code,'MTL_LOT_CONTROL',700) lot_control,
msiv.shelf_life_days,
nvl(xxen_util.meaning(msiv.serial_number_control_code,'CSP_INV_ITEM_SERIAL_CONTROL',0),xxen_util.meaning(msiv.serial_number_control_code,'MTL_SERIAL_NUMBER',700)) serial_control,
xxen_util.meaning(msiv.bom_item_type,'BOM_ITEM_TYPE',700) bom_item_type,
xxen_util.meaning(msiv.costing_enabled_flag,'YES_NO',0) costing_enabled,
xxen_util.meaning(msiv.inventory_asset_flag,'YES_NO',0) inventory_asset_value,
xxen_util.meaning(msiv.default_include_in_rollup_flag,'YES_NO',0) include_in_rollup,
gcc.concatenated_segments cost_of_goods_sold_account,
xxen_util.meaning(msiv.serv_req_enabled_code,'CS_SR_SERV_REQ_ENABLED_TYPE',170) serv_req_enabled_code,
xxen_util.meaning(msiv.serviceable_product_flag,'YES_NO',0) enable_contract_coverage,
xxen_util.meaning(msiv.comms_nl_trackable_flag,'YES_NO',0) track_in_installed_base,
xxen_util.meaning(msiv.serviceable_product_flag,'YES_NO',0) enable_contract_coverage,
xxen_util.meaning(msiv.serviceable_component_flag,'YES_NO',0) serviceable_component_flag,
xxen_util.meaning(msiv.contract_item_type_code,'OKB_CONTRACT_ITEM_TYPE',0) contract_item_type,
msiv.service_duration,
(select muomv.unit_of_measure from mtl_units_of_measure_vl muomv where msiv.service_duration_period_code=muomv.uom_code) service_duration_period,
(select oklv.name from okc_k_lines_v oklv where msiv.coverage_schedule_id=oklv.id) coverate_template,
msiv.service_starting_delay service_starting_delay_days,
(select xxen_util.meaning('Y','YES_NO',0) from csi_item_instances cii where msiv.inventory_item_id=cii.inventory_item_id and cii.accounting_class_code='CUST_PROD' and rownum=1) ib_exists,
ccg.name counter_group,
xxen_util.user_name(msiv.created_by) created_by,
xxen_util.client_time(msiv.creation_date) creation_date,
xxen_util.user_name(msiv.last_updated_by) last_updated_by,
xxen_util.client_time(msiv.last_update_date) last_update_date,
mm.manufacturer_name manufaturer,
mmpn.mfg_part_num manufacturer_part_number,
fcbk.concatenated_segments asset_category,
xxen_util.meaning(msiv.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700) make_or_buy,
ppx.full_name buyer,
msiv.planner_code planner,
mpl.description planner_description,
xxen_util.meaning(msiv.inventory_planning_code,'MTL_MATERIAL_PLANNING',700) inventory_planning_method,
(select distinct max(mss.safety_stock_quantity) keep (dense_rank last order by mss.effectivity_date) over (partition by mss.organization_id,mss.inventory_item_id) safety_stock from mtl_safety_stocks mss where msiv.organization_id=mss.organization_id and msiv.inventory_item_id=mss.inventory_item_id and mss.effectivity_date<=sysdate) safety_stock,
msiv.min_minmax_quantity,
msiv.max_minmax_quantity,
msiv.minimum_order_quantity,
msiv.maximum_order_quantity,
msiv.fixed_order_quantity,
msiv.fixed_lot_multiplier,
xxen_util.meaning(msiv.mrp_planning_code,'MRP_PLANNING_CODE',700) planning_method,
xxen_util.meaning(msiv.ato_forecast_control,'MRP_ATO_FORECAST_CONTROL',700) forecast_control,
xxen_util.meaning(msiv.end_assembly_pegging_flag,'ASSEMBLY_PEGGING_CODE',0) pegging,
xxen_util.meaning(msiv.planning_time_fence_code,'MTL_TIME_FENCE',700) planning_time_fence,
msiv.planning_time_fence_days,
msiv.full_lead_time processing_lead_time,
xxen_util.meaning(msiv.atp_flag,'ATP_FLAG',3) check_atp,
mar.rule_name atp_rule,
xxen_util.meaning(msiv.atp_components_flag,'ATP_FLAG',3) atp_components,
xxen_util.meaning(msiv.internal_order_flag,'YES_NO',0) internal_order_flag,
xxen_util.meaning(msiv.pick_components_flag,'YES_NO',0) pick_components,
xxen_util.meaning(msiv.replenish_to_order_flag,'YES_NO',0) assemble_to_order,
xxen_util.meaning(msiv.returnable_flag,'YES_NO',0) returnable,
xxen_util.meaning(msiv.customer_order_enabled_flag,'YES_NO',0) customer_orders_enabled,
xxen_util.meaning(msiv.shippable_item_flag,'YES_NO',0) shippable,
mp2.organization_code default_shipping_org,
msiv.default_so_source_type default_so_source_type,
xxen_util.meaning(msiv.inventory_item_flag,'YES_NO',0) inventory_item_flag,
xxen_util.meaning(msiv.stock_enabled_flag,'YES_NO',0) stock_enabled_flag,
xxen_util.meaning(msiv.mtl_transactions_enabled_flag,'YES_NO',0) transactable,
xxen_util.meaning(msiv.so_transactions_flag,'YES_NO',0) oe_transactable,
xxen_util.meaning(msiv.purchasing_enabled_flag,'YES_NO',0) purchasable,
xxen_util.meaning(msiv.customer_order_flag,'YES_NO',0) customer_ordered,
xxen_util.meaning(msiv.internal_order_enabled_flag,'YES_NO',0) internal_orders_enabled,
xxen_util.meaning(msiv.invoiceable_item_flag,'YES_NO',0) invoiceable_item,
xxen_util.meaning(msiv.invoice_enabled_flag,'YES_NO',0) invoice_enabled,
&flexfield_columns
msiv.inventory_item_id
from
hr_all_organization_units_vl haouv,
mtl_parameters mp,
mtl_system_items_vl msiv,
mtl_parameters mp2,
mtl_units_of_measure_vl muomv,
mtl_item_status_vl misv,
cs_ctr_associations cca,
cs_counter_groups ccg,
per_people_x ppx,
fa_categories_b_kfv fcbk,
mtl_mfg_part_numbers mmpn,
mtl_manufacturers mm,
gl_code_combinations_kfv gcc,
(
select
mic.organization_id,
mic.inventory_item_id,
mcsv.category_set_name,
mck.concatenated_segments
from
mtl_item_categories mic,
mtl_category_sets_v mcsv,
mtl_categories_kfv mck
where
mcsv.category_set_name=:category_set_name and
mck.concatenated_segments=nvl(:category,mck.concatenated_segments) and
2=2 and
mic.category_set_id=mcsv.category_set_id and
mic.category_id=mck.category_id
) x,
mtl_atp_rules mar,
mtl_planners mpl
where
1=1 and
msiv.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and
msiv.organization_id=haouv.organization_id and
msiv.organization_id=mp.organization_id and
msiv.default_shipping_org=mp2.organization_id(+) and
msiv.primary_uom_code=muomv.uom_code(+) and
msiv.inventory_item_status_code=misv.inventory_item_status_code(+) and
msiv.inventory_item_id=cca.source_object_id(+) and
cca.counter_group_id=ccg.counter_group_id(+) and
msiv.buyer_id=ppx.person_id(+) and
msiv.asset_category_id=fcbk.category_id(+) and
msiv.inventory_item_id=mmpn.inventory_item_id(+) and
msiv.organization_id=mmpn.organization_id(+) and
mmpn.manufacturer_id=mm.manufacturer_id(+) and
msiv.cost_of_sales_account=gcc.code_combination_id(+) and
msiv.organization_id=x.organization_id(+) and
msiv.inventory_item_id=x.inventory_item_id(+) and
msiv.atp_rule_id=mar.rule_id(+) and
msiv.planner_code=mpl.planner_code(+) and
msiv.organization_id=mpl.organization_id(+)
order by
mp.organization_code,
msiv.concatenated_segments,
x.category_set_name
Parameter Name SQL text Validation
Organization Code 
mp.organization_code=:organization_code
 LOV
Item 
msiv.concatenated_segments like :item
 LOV
Item Description 
upper(msiv.description) like upper(:item_descr)
 LOV
Item Type 
msiv.item_type=xxen_util.lookup_code(:item_type,'ITEM_TYPE',3)
 LOV
BOM Item Type 
msiv.bom_item_type=:bom_item_type
 LOV Oracle
Exclude Item Type 
nvl(msiv.item_type,'x')<>xxen_util.lookup_code(:exclude_item_type,'ITEM_TYPE',3)
 LOV
Contract Item Type 
msiv.contract_item_type_code=xxen_util.lookup_code(:contract_item_type,'OKB_CONTRACT_ITEM_TYPE',0)
 LOV
Item Status 
misv.inventory_item_status_code_tl=:item_status
 LOV
Buyer 
ppx.full_name=:buyer_name
 LOV
Planner 
msiv.planner_code=:planner_code
 LOV
Category Set 
(msiv.organization_id, msiv.inventory_item_id) in (
select
mic.organization_id,
mic.inventory_item_id
from
mtl_category_sets_v mcsv,
mtl_item_categories mic
where
mcsv.category_set_name=:category_set_name and
mcsv.category_set_id=mic.category_set_id)
 LOV
Category 
(msiv.organization_id, msiv.inventory_item_id) in
(
select
mic.organization_id,
mic.inventory_item_id
from
mtl_category_sets_v mcsv,
mtl_categories_kfv mck,
mtl_item_categories mic
where
mck.concatenated_segments=:category and
mcsv.category_set_name=:category_set_name and
mcsv.category_set_id=mic.category_set_id and
mcsv.structure_id=mck.structure_id and
mck.category_id=mic.category_id
)
 LOV
Last Update Date From 
msiv.last_update_date>=:last_update_date_from
 DateTime
Last Update Date To 
msiv.last_update_date<=:last_update_date_to
 DateTime
Last Updated By 
msiv.last_updated_by=xxen_util.user_id(:last_updated_by)
 LOV
Creation Date From 
msiv.creation_date>=:creation_date_from
 DateTime
Creation Date To 
msiv.creation_date<=:creation_date_to
 DateTime
Created By 
msiv.created_by=xxen_util.user_id(:created_by)
 LOV
Cross Reference Type 
msiv.inventory_item_id in (
select
mcrb.inventory_item_id
from
mtl_cross_references_b mcrb
where
mcrb.cross_reference_type=:cross_reference_type and
sysdate between nvl(mcrb.start_date_active,sysdate) and nvl(mcrb.end_date_active,sysdate)
)
 LOV
Cross Reference 
msiv.inventory_item_id in (
select
mcrb.inventory_item_id
from
mtl_cross_references_b mcrb
where
mcrb.cross_reference=:cross_reference and
mcrb.cross_reference_type=:cross_reference_type and
sysdate between nvl(mcrb.start_date_active,sysdate) and nvl(mcrb.end_date_active,sysdate)
)
 LOV
Master only 
mp.organization_id=mp.master_organization_id
 LOV
Flexfield Context 
select
'msiv.'||lower(fdfcuv.application_column_name)||' "'||substrb(fdfcuv.form_left_prompt,1,xxen_report.max_column_length)||'",' column_text
from
fnd_descr_flex_col_usage_vl fdfcuv
where
fdfcuv.application_id=401 and
fdfcuv.descriptive_flexfield_name='MTL_SYSTEM_ITEMS' and
fdfcuv.enabled_flag='Y' and
fdfcuv.display_flag='Y' and
fdfcuv.descriptive_flex_context_code=:descriptive_flex_context_code
order by
fdfcuv.column_seq_num
 LOV
Download
 
Related Blitz Reports
XXE4ARW Excel4apps Inventory Items RK: AR Transactions and Lines Avalara VAT LinesData Avalara VAT listing AHL Supplier Warranty Visit INV Inventory Item Upload COPY OF: INV Inventory Item Upload GL Account Analysis (Distributions) CSD Depot Repair Traveler
Blitz Report™

Blitz Report™ provides multiple benefits: