Analytics Software

Hello Folks,

I have been asked to pull unspsc code into BI and went through the configuration guide for the same.(attached document)

As per my understanding the ultimate goal should be to populate w_product_d with unspsc codes which is currently empty and then drag this into the required subject area. 

And I see a join between the w_product_d and w_product_dh. The fact table that is joining w_product_d is w_gl_other_f which will be my fact table

I am having a question with respect to the integration_id that we pull from the product and match it with the unspsc code.  I am getting confused as to how I can populate the unspsc code in the FILE_ITEM_TO_UNSPSC.csv file corresponding to the integration_id. I suppose after this is done the mapping will take care of looking up the code and uploading it in the corresponding W_PRODUCT_D table.

We have the unspsc codes and details available so can someone help with proceeding with this task further.

Note:  I have not done this kind of task before and needs some experienced person's advice to proceed further on the complete flow from the EBS source system to BIApps DW.

I also have the below queries:  Kindly suggest on the flow of the codes from ebs to DW:

---GET THE FLEX VALUE SET ID OF PRODUCT..THIS LIST ALL THE SEGMENTS FILTER THE GL SEGMENT FOR THE PRODUCT
select SEGMENT_NAME,flex_value_set_id from
fnd_id_flex_segments where id_flex_num = 101 and id_flex_code = 'GL#'

---USE THE FLEX VALUE SET ID TO FIND THE FLEX_VALUE_SET_NAME/GL PRODUCT SEGMENT NAME
select * from fnd_flex_value_sets where flex_value_set_id='1015931'

---USE THE FLEX VALUE SET ID TO FIND THE FLEX_VALUE_ID/GL PRODUCT CODES
select * from fnd_flex_values where flex_value_set_id='1015931'

---USE THE FLEX VALUE ID TO FIND THE FLEX_VALUE_ID/GL PRODUCT DESCRIPTION IN DIFFERENT CONFIGURED LANGUAGES
select * from fnd_flex_values_tl where flex_value_id='60598' AND LANG='US'

The below is the document I am referring to

1. 3.2.1.2 How to Assign UNSPSC Codes to Products

This section explains how to assign United Nations Standard Products and Services

Code (UNSPSC) codes to products and commodities. The United Nations Standard

Products and Services Code® (UNSPSC®) provides an open, global multi-sector

standard for efficient, accurate classification of products and services.

You can assign UNSPSC codes to your Products by adding the UNSPSC codes to the

file_unspsc.csv file. The codes are then loaded into the W_PROD_CAT_DH table by

the SDE_UNSPSC process. You must then perform manual classification by mapping

the codes in FILE_ITEM_TO_UNSPSC.csv to provide the link between the product (D)

and product category (DH) tables, using the load process PLP_ItemToUNSPSC_

1. Classification.

Note the following about UNSPSC codes:

■ You must buy your own UNSPSC data directly from UNSPSC.org, and then map

the data in file_unspsc.csv for loading into Oracle BI Applications.

■ The file_unspsc.csv file is used by the SDE_UNSPSC process and contains sample

1. data. Replace the sample data with your own UNSPSC data. Make sure to perform

this task before performing the manual classification steps described later in this

1. section.

■ The UNSPSC_CODE column must be unique for each record in the file. This

column is specific to Oracle BI Applications and does not come from the

purchased UNSPSC data. You can map this column to the COMMODITY_CODE

1. column.

■ The FUNCTION_CODE and FUNCTION_NAME columns are additional columns

that might be included in the purchased UNSPSC data. If you do not see these

columns in the purchased UNSPSC data, you can map these columns to the

COMMODITY_CODE and COMMODITY_NAME columns, respectively. These

columns are used to copy the levels in the hierarchy down to the base level.

■ Ensure that the DATASOURCE_NUM_ID in the file is the same as that defined for

the Oracle EBS Adapter. The default values are '9' for Oracle EBS R12 and '4' for

Oracle EBS R11510.

To assign UNSPSC codes to products:

1. 1. To identify the Products used in your deployment, run a select statement on the

W_PRODUCT_D table.

For example, you might use Oracle SQLDeveloper to run the following SQL

command:

SELECT INTEGRATION_ID, PROD_NAME, PART_NUM FROM JPOLAPUSR.W_PRODUCT_D

Note: In this example SQL statement, the INTEGRATION_ID is the product that

needs classification. The PROD_NAME and PART_NUM are additional attributes

to assist in classifying the UNSPSC Codes.

1. 2. Add the product IDs and UNSPSC codes to the FILE_ITEM_TO_UNSPSC.csv file.

Oracle EBS-Specific Configuration Steps

3-34 Oracle Business Intelligence Applications Configuration Guide for Informatica PowerCenter Users

The FILE_ITEM_TO_UNSPSC.csv file is located in the $PMServer\SrcFiles

directory (for example, INFA_HOME\server\infa_shared\SrcFiles).

Populate the PRODUCT_ID field with the INTEGRATION_ID values you

retrieved in Step 1. Populate the UNSPSC_CODE field with the UNSPSC Code

values you purchased from UNSPSC.org. Also, ensure that the DATASOURCE_

NUM_ID is the same as your Oracle EBS Adapter.

1. 3. In Informatica PowerCenter Designer, run the \PLP\PLP_ItemToUNSPSC_

Classification workflow to update the rows in the W_PRODUCT_D table.

Thanks

Prashanth