1. Integrating Sales with Oracle E-Business Suite, Release 12.1.3
  2. Map Product Groups

Map Product Groups

Use the following tasks to create product group mappings from Oracle E-Business Suite to Oracle CX Sales.

Extracting the List of Oracle E-Business Suite Products

First you use the following SQL query to extract the list of products from Oracle E-Business Suite.

  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_duratio
		,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' 
		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 (
		((select fnd_profile.value('ASO_REQUIRE_SERVICE_REFERENCE') from dual) = 'Y' AND service_item_flag = 'N')
		OR
			((select fnd_profile.value('ASO_REQUIRE_SERVICE_REFERENCE') from 
			dual) is null OR (select fnd_profile.value('ASO_REQUIRE_SERVICE_REFERENCE') 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.

Extracting the List of Oracle CX Sales Product Groups

Use this task to extract a list of product groups from Oracle CX Sales.

  1. In Oracle CX Sales navigate to Setup and Maintenance.

    Oracle Functional Setup Manager appears.

  2. From the Setup drop down menu, select Sales.

  3. In the Search Tasks box, search for Manage Product Groups.

  4. Click the Manage Product Groups link in the search results.

  5. In the Sales Catalog and Products task list, click the Manage Product Groups link.

  6. In the Manage Product Groups area, click the View drop down list, and then click Columns, and from that Columns menu click the following columns to enable them:

    • Name

    • Reference Number

    • Active

    • Effective from Data

    • Effective to Date

  7. In the Manage Product Groups area click the Export to Excel icon to export the list.

Mapping Products to Each Other Manually

You can create product mappings by comparing the SQL results from Oracle E-Business Suite to the CSV file from Oracle CX Sales. The following is one example for mapping desktop computers. To map products manually, do the following:

  1. Using the Oracle E-Business Suite SQL results generated in Extracting the List of Oracle E-Business Suite Products, filter on descriptions containing Desktop.

  2. Note the values in the ITEM and INVENTORY_ITEM_ID columns.

  3. In the Oracle CX Sales Excel file, filter on descriptions containing Desktop.

  4. Note the values in the Reference Number column.

  5. Map the corresponding products to each other.

    System

    Column

    Value

    Oracle CX Sales

    Product Group Name

    WAVE Desktops

    Product Group Reference Number

    3-57FC-880

    Oracle E-Business Suite

    DESCRIPTION

    Sentinel Deluxe Desktop

    Reference Number

    AS18947

    INVENTORY_ITEM_ID

    155

Entering Mappings into Oracle CX Sales

You use the Field Mapping object in Oracle CX Sales to enter product mappings. The example listed in the following table uses the values from the example in Mapping Products to Each Other Manually.

Note: You can also import the product mappings with the Oracle CX Sales File Import function.To enter product mappings into Oracle CX Sales, do the following:

  1. Log into Oracle CX Sales as a user with the Administrator role.

  2. Navigate to Tools, and then Field Mapping.

  3. On the Field Mappings page, click the Create icon.

  4. Enter the values shown in the following table, then click Save and Close.

  5. Repeat steps 3 and 4 for other products.

  6. Click Save and Close when finished.

Parameter

Value

Remote System Name

Remote system you entered in Connecting Oracle CX Sales to Oracle E-Business Suite

Local Field Name

Product Group

Local Field Value

Reference Number column value in Oracle CX Sales, for example, AS54600

Remote Field Name

Product

Remote Field Value

ITEM column value in Oracle E-Business Suite, for example, AS18947

Remote Field ID

INVENTORY_ITEM_ID column value in Oracle E-Business Suite, for example, 155