Assigning UNSPSC Codes to Products

You can assign United Nations Standard Products and Services Code (UNSPSC) codes to products and commodities in Oracle Business Analytics Warehouse. The UNSPSC provides an open, global multi-sector standard for efficient, accurate classification of products and services.

This figure shows an extract from a file containing UNSPSC data, where the UNSPSC Code for Cats is 10101501 and the UNSPSC Code for Dogs is 10101502.

This screenshot is described in surrounding text.
Description of the illustration GUID-5C7FAD6D-2D78-43D3-BCD4-73E75CAB9131-default.png

About Configuring the UNSPSC Parameters

In Oracle BI Applications Configuration Manager, you configure how UNSPSC data is loaded using two Data Load Parameters named UNSPSC_DATALOAD and UNSPSC_WHERE_CLAUSE, (see example screenshot below).

This graphic is described in surrounding text.
Description of the illustration GUID-D01D8A77-8AA4-4207-9B98-700FF12729F3-default.png
  1. In Oracle BI Applications Configuration Manager, edit the Data Load Parameter named UNSPSC_DATALOAD:
    1. Click Manage Data Load Parameters in the Tasks pane to display the Manage Data Load Parameters page.
    2. Use the Search pane to locate UNSPSC_DATALOAD, then select UNSPSC_DATALOAD in the Data Load Parameters list.
    3. In the Group Specific Parameter Values for area, edit the value of the UNSPSC_DATALOAD parameter to specify one of the following values:

      • NONE (Default) — No UNSPSC data will be loaded.

      • FULL — the SIL process will always insert all data from W_PRODUCT_D into the file FILE_ITEM_TO_UNSPSC.csv.

      • EMPTY — the SIL process loads only those items whose UNSPSC_CODE is NULL.

      • OWN_SQL — You provide a custom condition using the UNSPSC_WHERE_CLAUSE Data Load Parameter (see Step 1d).

      • INCR — Bring new and updated Item records into the file FILE_ITEM_TO_UNSPSC.csv.

      • NCR_NEW — Load only new items into the file FILE_ITEM_TO_UNSPSC.csv.

    4. If you set the UNSPSC_DATALOAD value to OWN_SQL, then you must edit the UNSPSC_WHERE_CLAUSE Data Load Parameter to specify a SQL statement to define a custom condition. For example: 
      W_PRODUCT_D.INTEGRATION_ID <>0
  2. In Oracle BI Applications Configuration Manager, create a Load Plan for this Offering and Functional Area and execute.

    When the Load Plan is executed, the process SIL_Product_ItemtoUNSPSC_File_Load loads data into the file file_item_to_unspsc.csv, which is written to the following location:

    <Oracle Home for BI>\biapps\etl\data_files\src_files\

    For example, the file file_item_to_unspsc.csv might contain the following data:

    PRODUCT_ID  PRODUCT_NAME  PART_NUMBER  UNSPSC_CODE DATASOURCE_NUM_ID
1-2IBV      TEST          5142                     200
1002A       TEST          5142                     200
  3. Copy the contents of the file_item_to_unspsc.csv and use it to replace the contents in file_item_to_unspsc_update.csv.

    The file file_item_to_unspsc_update.csv is installed with sample content.

  4. In the file file_item_to_unspsc_update.csv, for each product that you want to classify, add a UNSPSC Code to the UNSPSC_CODE column.

    For example, you might assign UNSPSC Code 10124708 to PRODUCT1 and UNSPSC Code 1010A to PRODUCT2. After editing, the file file_item_to_unspsc_update.csv might contain the following data:

    PRODUCT_ID  PRODUCT_NAME  PART_NUMBER  UNSPSC_CODE DATASOURCE_NUM_ID
1-2IBV      PRODUCT1          5142     10124708    200
1002A       PRODUCT2          5142     1010A       200

    A list of UNSPSC Codes can be found in file_unspsc.csv. If you change the value of UNSPSC_DATALOAD and perform Step 1 and Step 2 again, then you must perform Step 3 and Step 4 again. For example, you might change the value of UNSPSC_DATALOAD to INCR in order to load new or updated products.