--Oracle EBS R12 Active Item Master Query with Category, Organization and Buyer
SELECT msi.segment1 "Item Code",
msi.description "Item Description",
msi.primary_uom_code "UOM Code",
misi.secondary_inventory_name "Sub Inventory",
ood.organization_name "Inv Organization Name",
mcv.segment1 "Categorie seg1", mcv.segment2 "Categorie seg2", mcv.segment3 "Categorie seg3",
msi.cycle_count_enabled_flag "Cycle Count",
msi.inspection_required_flag "Inspection",
msi.stock_enabled_flag "Stockable",
msi.mtl_transactions_enabled_flag "Transactable",
msi.lot_control_code "Lot Control",
msi.location_control_code "Location",
msi.purchasing_enabled_flag "Purchasable",
msi.must_use_approved_vendor_flag "Use Approve Supplier List",
--msi.buyer_id,
pap.full_name "Buyer Name",
msi.list_price_per_unit "List Price",
msi.expense_account "Expense Account",
msi.RESERVABLE_TYPE "Reservable",
msi.PURCHASING_ITEM_FLAG "Purchased",
msi.OUTSIDE_OPERATION_FLAG "OSP item",
msi.OUTSIDE_OPERATION_UOM_TYPE "Unit Type",
msi.RECEIVING_ROUTING_ID "Receive Routing",
msi.INVENTORY_PLANNING_CODE "Inventory Planning method",
msi.MIN_MINMAX_QUANTITY "Min Max(Minimum Qty)",
msi.MAX_MINMAX_QUANTITY "Min Max(Maximum Qty)",
msi.MINIMUM_ORDER_QUANTITY "Order Qty (Minimum Qty)",
msi.MAXIMUM_ORDER_QUANTITY "Order Qty (Maximum Qty)",
msi.SOURCE_TYPE "Source Type",
msi.SOURCE_SUBINVENTORY "Source Sub inventory",
msi.PLANNING_MAKE_BUY_CODE "MAKE BUY",
msi.FIXED_LOT_MULTIPLIER "Fixed lot multiplier",
msi.PLANNER_CODE "Planner",
msi.MRP_PLANNING_CODE "Planning method",
msi.ATO_FORECAST_CONTROL "Forecast control",
msi.END_ASSEMBLY_PEGGING_FLAG "Pegging",
msi.RELEASE_TIME_FENCE_CODE "Release Time Fence",
msi.PLANNING_TIME_FENCE_DAYS "Planning time fence",
msi.PREPROCESSING_LEAD_TIME "Preprocessing",
msi.FULL_LEAD_TIME "Processing",
msi.POSTPROCESSING_LEAD_TIME "Post processing"
FROM mtl_system_items msi,
mtl_secondary_locators msl,
mtl_item_locations mil,
per_all_people_f pap,
mtl_secondary_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.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'
AND ood.organization_id IN
(SELECT DISTINCT master_organization_id FROM mtl_parameters)
Order By 1