Reports2017-11-18T12:27:27+00:00

INV Inventory Items

Description
Categories: Enginatics, Logistics, Manufacturing
Inventory item master setup including main item attributes such as item type, uom, status, serial control, physical and various other attributes

select
mp.organization_code,
haou.name organization_name,
xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) user_item_type,
msiv.concatenated_segments item_number,
msiv.description,
msiv.long_description,
&columns
misv.inventory_item_status_code_tl item_status,
muot.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.comms_nl_trackable_flag,'YES_NO',0) track_ib,
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,
(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,
gcc.concatenated_segments cost_of_goods_sold_account,
msiv.inventory_item_id
from
hr_all_organization_units haou,
mtl_parameters mp,
mtl_system_items_vl msiv,
mtl_parameters mp2,
mtl_units_of_measure_tl muot,
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,
mcb.concatenated_segments
from
mtl_item_categories mic,
mtl_category_sets_v mcsv,
mtl_categories_kfv mcb
where
'&enable_categories'='Y' and
2=2 and
mic.category_set_id=mcsv.category_set_id and
mic.category_id=mcb.category_id
) x,
&xrrpv_table
mtl_atp_rules mar,
mtl_planners mpl
where
1=1 and
msiv.organization_id=haou.organization_id and
msiv.organization_id=mp.organization_id and
msiv.default_shipping_org=mp2.organization_id(+) and
msiv.primary_uom_code=muot.uom_code(+) and
muot.language(+)=userenv('lang') 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
Created By
msiv.created_by in (select fu.user_id from fnd_user fu where fu.user_name=:created_by)
LOV
Last Updated By
msiv.last_updated_by in (select fu.user_id from fnd_user fu where fu.user_name=:last_updated_by)
LOV
Exclude Item Type
not (
msiv.item_type in (
select
flv.lookup_code
from
fnd_lookup_values flv
where
flv.meaning=:item_type and
flv.lookup_type='ITEM_TYPE' and
flv.language=userenv('lang') and
flv.view_application_id=3 and
flv.security_group_id=0
)
)
LOV
Item Type
msiv.item_type in (
select
flv.lookup_code
from
fnd_lookup_values flv
where
flv.meaning=:item_type and
flv.lookup_type='ITEM_TYPE' and
flv.language=userenv('lang') and
flv.view_application_id=3 and
flv.security_group_id=0
)
LOV
Item Status
misv.inventory_item_status_code_tl=:item_status
LOV
Master only
mp.organization_id=mp.master_organization_id
LOV
Item
msiv.concatenated_segments like :item
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_item_categories mic,
mtl_category_sets mcs,
mtl_categories_kfv mck
where
mck.concatenated_segments=:category and
mck.structure_id=mcs.structure_id and
mck.category_id=mic.category_id and
mcs.category_set_id=mic.category_set_id
)
LOV
Buyer
msiv.buyer_id in (select ppx.person_id from per_people_x ppx where ppx.full_name=:buyer_name)
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
Planner
msiv.planner_code=:planner_code
LOV
Category
Y
Category
x.category_set_name,
x.concatenated_segments category,
organization_id|inventory_item_id
xrrpv.organization_id=msiv.organization_id and
xrrpv.inventory_item_id=msiv.inventory_item_id
Creation Date To
msiv.creation_date<=:creation_date_to
DateTime
Creation Date From
msiv.creation_date>=:creation_date_from
DateTime
Last Update Date To
msiv.last_update_date<=:last_update_date_to
DateTime
Last Update Date From
msiv.last_update_date>=:last_update_date_from
DateTime
Organization Code
msiv.organization_id=:organization_id
LOV Oracle
BOM Item Type
msiv.bom_item_type=:bom_item_type
LOV Oracle
organization_id|inventory_item_id
(
select distinct
dbms_lob.substr(xrrpv.value,instr(xrrpv.value,'|')-1) organization_id,
dbms_lob.substr(xrrpv.value,20,instr(xrrpv.value,'|')+1) inventory_item_id
from
xxen_report_run_param_values xrrpv
where
xrrpv.run_id=:run_id
) xrrpv,

By continuing to use the site, you agree to the use of cookies. Accept