Inventory Lerated Queries
INVENTORY RELATED QUERIES
--- get item attributes NOT UNDER status control
SELECT meaning1 attrib_group, user_attribute_name_gui,
-- ,control_level, status_control_code,attribute_name,
-- attribute_group_id,data_type,
-- user_attribute_name,level_updateable_flag,
-- validation_code ,lookup_type1,
-- lookup_code1,enabled_flag1,lookup_type2,lookup_code2,
meaning2 control_level,
-- ,enabled_flag2,
-- lookup_type3,lookup_code3,
meaning3 status_control,
-- enabled_flag3,lookup_type4,lookup_code4,
meaning4 VALIDATION
-- ,enabled_flag4
FROM mtl_item_attributes_v
WHERE control_level IN (1, 2)
AND status_control_code IS NULL
AND user_attribute_name_gui IS NOT NULL
AND attribute_name IN (SELECT attribute_name
FROM mtl_item_attr_appl_inst_v)
ORDER BY attribute_group_id_gui, sequence_gui
/
-- get item status attribute controls
SELECT ia.attribute_group_id GROUP_ID, ia.user_attribute_name_gui,
lk.meaning controlled_at, ia.attribute_name,
-- ia.user_attribute_name,
ia.status_control_code,
ia.validation_code
FROM fnd_lookup_values lk, mtl_item_attributes ia
WHERE ia.control_level = lk.lookup_code
AND lk.lookup_type = 'ITEM_CONTROL_LEVEL_GUI'
ORDER BY ia.attribute_group_id, 1
/
-- find item status attributes :
SELECT mis.inventory_item_status_code item_status, mis.description,
mis.disable_date, av.attribute_name, av.attribute_value VALUE
FROM mtl_item_status mis, mtl_status_attribute_values av
WHERE mis.inventory_item_status_code = av.inventory_item_status_code
ORDER BY 1
/
-- get item attributes UNDER status control :
SELECT meaning1 attrib_group, user_attribute_name_gui,
-- ,control_level, status_control_code,attribute_name,
-- attribute_group_id,data_type,
-- user_attribute_name,level_updateable_flag,
-- validation_code ,lookup_type1, lookup_code1,enabled_flag1,lookup_type2,lookup_code2,
meaning2 control_level,
-- ,enabled_flag2,
-- lookup_type3,lookup_code3,
meaning3 status_control,
-- enabled_flag3,lookup_type4,lookup_code4,
meaning4 VALIDATION
-- ,enabled_flag4
FROM mtl_item_attributes_v
WHERE control_level IN (1, 2)
AND status_control_code IS NOT NULL
AND user_attribute_name_gui IS NOT NULL
AND attribute_name IN (SELECT attribute_name
FROM mtl_item_attr_appl_inst_v)
ORDER BY attribute_group_id_gui, sequence_gui
/
--- find an Item attribute info :
SELECT segment1 item, msi.description, inventory_item_id,
ml.meaning item_type,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.inventory_item_status_code
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.inventory_item_status_code')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.purchasing_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.purchasing_item_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.shippable_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.shippable_item_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.mtl_transactions_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.mtl_transactions_enabled_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.so_transactions_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.so_transactions_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.internal_order_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.internal_order_enabled_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.customer_order_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.customer_order_enabled_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.purchasing_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.purchasing_enabled_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.inventory_asset_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.inventory_asset_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.eng_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) = 'mtl_system_items.eng_item_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.inventory_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.inventory_item_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name
|| '.'
|| msi.service_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.service_item_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.internal_order_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.internal_order_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.build_in_wip_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.build_in_wip_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.bom_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.bom_enabled_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.stock_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.stock_enabled_flag')
ATTRIBUTE
FROM fnd_lookup_values ml, mtl_system_items msi
WHERE msi.segment1 LIKE 'AS18947%'
AND msi.organization_id = 204
AND msi.item_type = ml.lookup_code(+)
AND ml.lookup_type(+) = 'ITEM_TYPE'
ORDER BY 1, 2
/
--- find Item template attribute VALUES :
SELECT it.template_name, ita.attribute_name, ita.attribute_value
FROM mtl_item_templates it, mtl_item_templ_attributes ita
WHERE it.template_name LIKE 'xxx%'
AND it.template_id = ita.template_id
AND ita.attribute_value IS NOT NULL
ORDER BY 1, 2
/
--- find item cross-REFERENCES :
/* Formatted on 2010/08/24 11:27 (Formatter Plus v4.8.0) */
SELECT msi.segment1 item, mcr.cross_reference_type reference_type,
mcr.cross_reference, mcr.description
FROM mtl_cross_references mcr, mtl_system_items msi
WHERE mcr.cross_reference_type = 'Vendor'
AND mcr.inventory_item_id = msi.inventory_item_id
AND mcr.organization_id = msi.organization_id
ORDER BY 1, 2
/
-- find Customer items :
/* Formatted on 2010/08/24 11:27 (Formatter Plus v4.8.0) */
SELECT hp.party_name customer, ci.customer_item_number,
ci.customer_item_desc, msi.segment1 item, msi.description item_desc,
ci.customer_category_code, ci.item_definition_level,
ci.commodity_code_id, ci.address_id
FROM hz_parties hp,
hz_cust_accounts hca,
mtl_system_items msi,
mtl_customer_items ci,
mtl_customer_item_xrefs ix
WHERE ci.customer_item_id = ix.customer_item_id
AND ix.inventory_item_id = msi.inventory_item_id
AND ix.master_organization_id = msi.organization_id
AND ci.customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
ORDER BY 1, 2
/
---find Manufacturer items :
SELECT mm.manufacturer_name, mp.mfg_part_num, mp.description,
msi.segment1 inv_item, msi.description item_desc
FROM mtl_system_items msi, mtl_mfg_part_numbers mp, mtl_manufacturers mm
WHERE mm.manufacturer_id = mp.manufacturer_id
AND mp.inventory_item_id = msi.inventory_item_id
AND mp.organization_id = msi.organization_id
ORDER BY 1, 2
/
--find related items :
SELECT ito.segment1 item, ito.description, itr.segment1 related_item,
itr.description, ml.meaning relation, ri.reciprocal_flag
FROM mfg_lookups ml,
mtl_system_items itr,
mtl_system_items ito,
mtl_related_items ri
WHERE ri.inventory_item_id = ito.inventory_item_id
AND ri.organization_id = ito.organization_id
AND ri.related_item_id = itr.inventory_item_id
AND ri.organization_id = itr.organization_id
AND ri.relationship_type_id = ml.lookup_code
AND ml.lookup_type(+) = 'MTL_RELATIONSHIP_TYPES'
ORDER BY 1, 2
/
-- find DEFAULT category FOR a category SET :
/* Formatted on 2010/08/24 11:28 (Formatter Plus v4.8.0) */
SELECT mcats.category_set_name, mcat.segment1 default_category,
mcat.description cat_desc, mcat.category_id, mcats.category_set_id
FROM mtl_category_sets mcats, mtl_categories mcat
WHERE mcats.category_set_name LIKE '%'
AND mcat.category_id = mcats.default_category_id
ORDER BY 1, 2
/
-- find ALL items assigned TO categories OF a category SET :
SELECT mcats.category_set_name,
mcat.segment1 || '.' || mcat.segment2 CATEGORY, msi.segment1 item,
msi.description item_desc
FROM mtl_item_categories micat,
mtl_category_sets mcats,
mtl_categories mcat,
mtl_system_items_vl msi
WHERE mcats.category_set_name LIKE 'Inv%'
AND micat.category_set_id = mcats.category_set_id
AND micat.category_id = mcat.category_id
AND mcat.segment1 LIKE 'N%'
AND msi.inventory_item_id = micat.inventory_item_id
AND msi.organization_id = micat.organization_id
AND msi.organization_id = 204
ORDER BY 1, 2, 3
/
--- get item attributes NOT UNDER status control
SELECT meaning1 attrib_group, user_attribute_name_gui,
-- ,control_level, status_control_code,attribute_name,
-- attribute_group_id,data_type,
-- user_attribute_name,level_updateable_flag,
-- validation_code ,lookup_type1,
-- lookup_code1,enabled_flag1,lookup_type2,lookup_code2,
meaning2 control_level,
-- ,enabled_flag2,
-- lookup_type3,lookup_code3,
meaning3 status_control,
-- enabled_flag3,lookup_type4,lookup_code4,
meaning4 VALIDATION
-- ,enabled_flag4
FROM mtl_item_attributes_v
WHERE control_level IN (1, 2)
AND status_control_code IS NULL
AND user_attribute_name_gui IS NOT NULL
AND attribute_name IN (SELECT attribute_name
FROM mtl_item_attr_appl_inst_v)
ORDER BY attribute_group_id_gui, sequence_gui
/
-- get item status attribute controls
SELECT ia.attribute_group_id GROUP_ID, ia.user_attribute_name_gui,
lk.meaning controlled_at, ia.attribute_name,
-- ia.user_attribute_name,
ia.status_control_code,
ia.validation_code
FROM fnd_lookup_values lk, mtl_item_attributes ia
WHERE ia.control_level = lk.lookup_code
AND lk.lookup_type = 'ITEM_CONTROL_LEVEL_GUI'
ORDER BY ia.attribute_group_id, 1
/
-- find item status attributes :
SELECT mis.inventory_item_status_code item_status, mis.description,
mis.disable_date, av.attribute_name, av.attribute_value VALUE
FROM mtl_item_status mis, mtl_status_attribute_values av
WHERE mis.inventory_item_status_code = av.inventory_item_status_code
ORDER BY 1
/
-- get item attributes UNDER status control :
SELECT meaning1 attrib_group, user_attribute_name_gui,
-- ,control_level, status_control_code,attribute_name,
-- attribute_group_id,data_type,
-- user_attribute_name,level_updateable_flag,
-- validation_code ,lookup_type1, lookup_code1,enabled_flag1,lookup_type2,lookup_code2,
meaning2 control_level,
-- ,enabled_flag2,
-- lookup_type3,lookup_code3,
meaning3 status_control,
-- enabled_flag3,lookup_type4,lookup_code4,
meaning4 VALIDATION
-- ,enabled_flag4
FROM mtl_item_attributes_v
WHERE control_level IN (1, 2)
AND status_control_code IS NOT NULL
AND user_attribute_name_gui IS NOT NULL
AND attribute_name IN (SELECT attribute_name
FROM mtl_item_attr_appl_inst_v)
ORDER BY attribute_group_id_gui, sequence_gui
/
--- find an Item attribute info :
SELECT segment1 item, msi.description, inventory_item_id,
ml.meaning item_type,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.inventory_item_status_code
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.inventory_item_status_code')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.purchasing_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.purchasing_item_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.shippable_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.shippable_item_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.mtl_transactions_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.mtl_transactions_enabled_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.so_transactions_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.so_transactions_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.internal_order_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.internal_order_enabled_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.customer_order_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.customer_order_enabled_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.purchasing_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.purchasing_enabled_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.inventory_asset_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.inventory_asset_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.eng_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) = 'mtl_system_items.eng_item_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.inventory_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.inventory_item_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name
|| '.'
|| msi.service_item_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.service_item_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.internal_order_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.internal_order_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.build_in_wip_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.build_in_wip_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.bom_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.bom_enabled_flag')
ATTRIBUTE,
(SELECT ia.user_attribute_name_gui
|| '.'
|| msi.stock_enabled_flag
FROM mtl_item_attributes_v ia
WHERE LOWER (ia.attribute_name) =
'mtl_system_items.stock_enabled_flag')
ATTRIBUTE
FROM fnd_lookup_values ml, mtl_system_items msi
WHERE msi.segment1 LIKE 'AS18947%'
AND msi.organization_id = 204
AND msi.item_type = ml.lookup_code(+)
AND ml.lookup_type(+) = 'ITEM_TYPE'
ORDER BY 1, 2
/
--- find Item template attribute VALUES :
SELECT it.template_name, ita.attribute_name, ita.attribute_value
FROM mtl_item_templates it, mtl_item_templ_attributes ita
WHERE it.template_name LIKE 'xxx%'
AND it.template_id = ita.template_id
AND ita.attribute_value IS NOT NULL
ORDER BY 1, 2
/
--- find item cross-REFERENCES :
/* Formatted on 2010/08/24 11:27 (Formatter Plus v4.8.0) */
SELECT msi.segment1 item, mcr.cross_reference_type reference_type,
mcr.cross_reference, mcr.description
FROM mtl_cross_references mcr, mtl_system_items msi
WHERE mcr.cross_reference_type = 'Vendor'
AND mcr.inventory_item_id = msi.inventory_item_id
AND mcr.organization_id = msi.organization_id
ORDER BY 1, 2
/
-- find Customer items :
/* Formatted on 2010/08/24 11:27 (Formatter Plus v4.8.0) */
SELECT hp.party_name customer, ci.customer_item_number,
ci.customer_item_desc, msi.segment1 item, msi.description item_desc,
ci.customer_category_code, ci.item_definition_level,
ci.commodity_code_id, ci.address_id
FROM hz_parties hp,
hz_cust_accounts hca,
mtl_system_items msi,
mtl_customer_items ci,
mtl_customer_item_xrefs ix
WHERE ci.customer_item_id = ix.customer_item_id
AND ix.inventory_item_id = msi.inventory_item_id
AND ix.master_organization_id = msi.organization_id
AND ci.customer_id = hca.cust_account_id
AND hca.party_id = hp.party_id
ORDER BY 1, 2
/
---find Manufacturer items :
SELECT mm.manufacturer_name, mp.mfg_part_num, mp.description,
msi.segment1 inv_item, msi.description item_desc
FROM mtl_system_items msi, mtl_mfg_part_numbers mp, mtl_manufacturers mm
WHERE mm.manufacturer_id = mp.manufacturer_id
AND mp.inventory_item_id = msi.inventory_item_id
AND mp.organization_id = msi.organization_id
ORDER BY 1, 2
/
--find related items :
SELECT ito.segment1 item, ito.description, itr.segment1 related_item,
itr.description, ml.meaning relation, ri.reciprocal_flag
FROM mfg_lookups ml,
mtl_system_items itr,
mtl_system_items ito,
mtl_related_items ri
WHERE ri.inventory_item_id = ito.inventory_item_id
AND ri.organization_id = ito.organization_id
AND ri.related_item_id = itr.inventory_item_id
AND ri.organization_id = itr.organization_id
AND ri.relationship_type_id = ml.lookup_code
AND ml.lookup_type(+) = 'MTL_RELATIONSHIP_TYPES'
ORDER BY 1, 2
/
-- find DEFAULT category FOR a category SET :
/* Formatted on 2010/08/24 11:28 (Formatter Plus v4.8.0) */
SELECT mcats.category_set_name, mcat.segment1 default_category,
mcat.description cat_desc, mcat.category_id, mcats.category_set_id
FROM mtl_category_sets mcats, mtl_categories mcat
WHERE mcats.category_set_name LIKE '%'
AND mcat.category_id = mcats.default_category_id
ORDER BY 1, 2
/
-- find ALL items assigned TO categories OF a category SET :
SELECT mcats.category_set_name,
mcat.segment1 || '.' || mcat.segment2 CATEGORY, msi.segment1 item,
msi.description item_desc
FROM mtl_item_categories micat,
mtl_category_sets mcats,
mtl_categories mcat,
mtl_system_items_vl msi
WHERE mcats.category_set_name LIKE 'Inv%'
AND micat.category_set_id = mcats.category_set_id
AND micat.category_id = mcat.category_id
AND mcat.segment1 LIKE 'N%'
AND msi.inventory_item_id = micat.inventory_item_id
AND msi.organization_id = micat.organization_id
AND msi.organization_id = 204
ORDER BY 1, 2, 3
/
