Query to get item details in Oracle Apps

Query to get item details in Oracle Apps

In this post , we will be discuss about the Query to get item details in Oracle Apps.This query will help to extract the complete Inventory Item details having all item attributes values in oracle apps. We will get the complete Item Information's using this sql query. We can refer this sql query for inventory item master. Please find below the detail sql Query to get item details in Oracle Apps.

9 Most Important Tables used by Query to get item details in Oracle Apps

1.apps.mtl_system_items
2.apps.mtl_secondary_locators
3.apps.mtl_item_locations
4.apps.per_all_people_f
5.apps.mtl_item_sub_inventories
6.apps.org_organization_definitions
7.apps.mtl_item_categories
8.apps.mtl_categories_vl
9.apps.mtl_category_sets

Detail SQL Query to get item details in Oracle Apps

SELECT msi.segment1, msi.description, msi.primary_uom_code,
msi.cycle_count_enabled_flag, msi.inspection_required_flag,
msi.attribute14 legacy_code, msi.attribute13 legacy_description,
msi.stock_enabled_flag stockable,
msi.mtl_transactions_enabled_flag transactable, msi.lot_control_code,
msi.location_control_code, msi.purchasing_enabled_flag,
msi.must_use_approved_vendor_flag, msi.buyer_id,pap.full_name buyer_name,
msi.list_price_per_unit,
(msi.expense_account) expense_account,
msi.receiving_routing_id, misi.secondary_inventory subinventory,
mil.segment1 loc_seg1, mil.segment11 loc_seg11,
mil.segment2 loc_seg2, mil.segment3 loc_seg3, mil.segment4 loc_seg4,
mil.segment5 loc_seg5, mil.segment6 loc_seg6, mil.segment7 loc_seg7,
mil.segment8 loc_seg8, mil.segment9 loc_seg9,
mil.segment10 loc_seg10, ood.organization_name,
mcv.segment1 cat_seg1, mcv.segment2 cat_seg2, mcv.segment3 cat_seg3,
mcv.segment4 cat_seg4, mcv.segment5 cat_seg5, mcv.segment6 cat_seg6
FROM apps.mtl_system_items msi,
apps.mtl_secondary_locators msl,
apps.mtl_item_locations mil,
apps.per_all_people_f pap,
apps.mtl_item_sub_inventories misi,
apps.org_organization_definitions ood,
apps.mtl_item_categories mic,
apps.mtl_categories_vl mcv,
apps.mtl_category_sets mcs
WHERE msi.inventory_item_id = msl.inventory_item_id(+)
AND msi.organization_id = msl.organization_id(+)
AND msl.secondary_locator = mil.inventory_location_id(+)
AND msl.organization_id = mil.organization_id(+)
AND pap.person_id(+) = msi.buyer_id
AND msi.inventory_item_id = misi.inventory_item_id(+)
AND msi.organization_id = misi.organization_id(+)
AND ood.organization_id = msi.organization_id
AND mic.inventory_item_id = msi.inventory_item_id
AND mic.organization_id = msi.organization_id
AND mcs.category_set_id = mic.category_set_id
AND mcs.structure_id = mcv.structure_id
AND mcv.category_id = mic.category_id
AND msi.inventory_item_status_code = 'Active'