Extracting the List of Oracle E-Business Suite Products: Explained

First you use a SQL query to extract the list of products from Oracle E-Business Suite. To extract the list of Oracle E-Business Suite products, do the following:

1. Connect to the Oracle E-Business Suite database.

2. Run the following SQL query:

   SELECT msiv.inventory_item_id
   		,msiv.organization_id
   		,msiv.concatenated_segments		item
   		,msiv.description
   		,msiv.primary_uom_code
   		,msiv.serviceable_product_flag
   		,msiv.service_item_flag
   		,msiv.bom_item_type
   		,msiv.item_type
   		,msiv.service_duration
   		,msiv.service_duration_period_code
   		,msiv.shippable_item_flag
   		,msiv.returnable_flag
   		,msiv.web_status
   		,msiv.orderable_on_web_flag
   		,msiv.customer_order_enabled_flag
   FROM  mtl_system_items_b_kfv msiv, 		ibe_ct_imedia_search icis,
   		mtl_units_of_measure_vl muv
   WHERE msiv.organization_id = icis.organization_id
   AND msiv.inventory_item_id = icis.inventory_item_id
   AND icis.language = userenv('LANG')
   AND msiv.primary_uom_code <> 'ENR'
   AND (msiv.bom_item_type = 1 OR msiv.bom_item_type = 4)
   AND msiv.primary_uom_code = muv.uom_code
   AND (
   		((select fnd_profile.value('ASO_WEB_ATTR_FILTER') from dual) = 'Y'
   		AND msiv.web_status = 'PUBLISHED' AND orderable_on_web_flag = 'Y') 
   		OR
   			(select fnd_profile.value('ASO_WEB_ATTR_FILTER') from dual) is 
   			null OR (select fnd_profile.value('ASO_WEB_ATTR_FILTER') from dual) != 'Y')
   		)
   AND icis.category_set_id = nvl((select fnd_profile.value('ASO_CATEGORY_SET') from dual),
   (SELECT category_set_id FROM mtl_default_category_sets WHERE functional_area_id =7))
   AND msiv.organization_id = <organization_id>;

3. You can use the following optional AND condition to filter results based on specific item codes: AND msiv.concatenated_segments in ('<Item name>')

4. Save the results to a CSV file, then open in spreadsheet software.

   The item code is listed in the Item column. The item description is listed in the Description column.