Oracle iStore's Product Search functionality allows customers to search the Customer Application product inventory. The product search is restricted to the site that the customer is logged in to. Product Search is implemented using:

• Oracle Inventory tables
• The interMedia text search utility of the Oracle8i database
• The concurrent program, iStore Search Insert

Customer Application Product Search Process Flow
Following is the process flow for product search in the Customer Application.

1. The product information (part number, description, and long description) is first loaded in an Oracle iStore table (IBE_CT_IMEDIA_SEARCH) via the concurrent program, iStore Search Insert.

This step is generally performed after the merchant has loaded products into Oracle Inventory.

The search table IBE_CT_IMEDIA_SEARCH is a denormalized table of MTL_SYSTEM_ITEMS_TL and MTL_ITEM_CATEGORIES.

The core text on which the search is performed is stored in a Clob called INDEXED_SEARCH. It stores a concatenation of part number, name, and description of products. The table also stores inventory_item_id, organization_id, category_id, category_set_id, and the web status field from MTL_SYSTEM_ITEMS_B table.

Searches are performed on the part number, name, and description of a product.

The name and description are stored as description and long description columns in the  MTL_SYSTEM_ITEMS_TL table.

2. Once the data is loaded, any change to product information is updated in the Oracle iStore table IBE_CT_IMEDIA_SEARCH through a database trigger call on the appropriate Inventory table. This keeps product information current in the search table.

3. Once the data is moved into the search table, the interMedia index is created to facilitate keyword searches.
Note: Searching by languages is enabled by launching the iStore Search Insert program in the enabled language.

Product Search Dependencies
Following are the dependencies to product search for the Customer Application:

• Oracle Inventory: You must ensure that Oracle Inventory is installed and configured properly before setting up the Customer Application product search. Refer to Oracle Inventory implementation documentation for details on how to set up Oracle Inventory for product searching.
• Oracle interMedia Text: You must ensure that Oracle interMedia Text is installed and configured properly before setting up the Customer Application product search. Refer to the Oracle interMedia Text documentation for details on how to set up and configure interMedia Text.
• Oracle Database: The product search requires version 8.1.7 of the Oracle database with the Oracle interMedia Text option installed. You can enable either category-level or section-level searches from the Customer Application Site Home Page Quick Search utility.

Running the iStore Search Insert Concurrent Program
To populate the search table used in the category-level product search, you must run the iStore Search Insert program one time. Thereafter, the table is updated through a database trigger call on the Inventory table.

Prerequisites
The profile option, IBE: Use Category Search, is set to Yes at the iStore application level.
The profile option, IBE: Use Synonym Search, must be set to No at the iStore application level, if the thesaurus file is not uploaded.
Optionally, set the profile option, IBE: Category Set Filter for Product Search.
Optionally, set the profile option, IBE: Web Status Filter for Product Search.

Populating the Section-Level Search Table
To enable section-level search on the Customer Application for the first time, run the iStore Search Insert program first, then run the iStore Section Search Refresh program to populate the search table IBE_SECTION_SEARCH. To switch from category-level to  section-level search, run only the iStore Section Search Refresh program.

Since the iStore Search Insert program can populate product data based on the category set profile value (IBE: Category Set Filter for Product Search), the section refresh program also populates product data into the IBE_CT_IMEDIA_SEARCH table, when necessary for product search. The profile option, IBE: Use Category Search, is used by iStore to determine whether product searches in the Customer Application are filterable by section or product category. The iStore Section Refresh concurrent program checks the value of IBE: Use Category Search. If it is set to No (meaning iStore is using section search rather than category search), and if the IBE: Category Set Filter for Product Search profile option is set to a specific category set, a query will be run to populate the IBE_CT_IMEDIA_SEARCH table with published products that are not in the specific category set but which are linked to iStore sections. This allows section data population in addition to category set data population when a specific category set is being used for data population.

Prerequisites
• Set the profile option IBE: Use Category Search to No.
• The profile option, IBE: Use Synonym Search, must be set to No at the iStore application level, if the thesaurus file is not uploaded.

Steps
1. Follow the procedure outlined in the section, "Running the iStore Search Insert Concurrent Program", to load data into the main search table IBE_CT_IMEDIA_SEARCH.

2. In the iStore Concurrent Programs Responsibility, choose Single Request and select OK. The Submit Request window opens.

3. Choose iStore Section Search Refresh from the Name LOV.

4. Click Submit to start the concurrent request. Note the request ID.
The concurrent manager calls the iStore Section Search Refresh program, which populates the search table, IBE_SECTION_SEARCH, with product data. The product search is still available to customers while the iStore Section Search Refresh program is running.

Once the request is complete, the pull-down search menu on the Site Home Page lists the top level sections, not the product categories.

Changing Between Category-Level Search and Section-Level Search
To change the listings in the pull-down search menu from categories to sections or vice versa, rerun the iStore Search Insert concurrent program to ensure that product listings will not be duplicated.

Change the IBE: Use Category Search profile option to Yes if you are changing to the category-level search. Change the profile option to No if you are changing to the section-level search.

If you are setting up category-level search, follow the steps in the section, "Running the iStore Search Insert Concurrent Program". If you are setting up section-level search, follow the steps in the section, "Populating the Section-Level Search Table".