Item Master Sql Query in Oracle Apps R12
In this post , We will discuss about the item master sql query in oracle apps r12. This query will help to extract the inventory item details in oracle apps .We will get the complete item master informations having all the item parameters using this sql query. Please find below the detail item master sql query and tables used in this sql query in oracle apps r12.
Important Tables Used by Item Master Sql Query in Oracle Apps R12
1.mtl_system_items2.mtl_secondary_locators
3.mtl_item_locations
4.per_all_people_f
5.mtl_item_sub_inventories
6.org_organization_definitions
7.mtl_item_categories
8.mtl_categories_vl
9.mtl_category_sets
Detail Item Master Sql Query in Oracle Apps R12
SELECT msi.segment1, msi.segment2, msi.segment3, msi.segment4, msi.segment5,
msi.segment6, msi.segment7, 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,
mcv.segment7 cat_seg7, mcv.segment8 cat_seg8, mcv.segment9 cat_seg9,
mcv.segment10 cat_seg10, mcv.segment11 cat_seg11,
mcv.segment12 cat_seg12, mcv.segment13 cat_seg13
FROM mtl_system_items msi,
mtl_secondary_locators msl,
mtl_item_locations mil,
per_all_people_f pap,
mtl_item_sub_inventories misi,
org_organization_definitions ood,
mtl_item_categories mic,
mtl_categories_vl mcv,
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'
