1. Using Product Master Data Management
  2. How You Import Item Data Using Import Maps

How You Import Item Data Using Import Maps

You can use import maps to help upload item data from a variety of external sources into Oracle Product Hub.

To use external data sources whose layout doesn't exactly correspond to the master data for Product Hub, you can map source data from comma-separated value (CSV) files and Extensible Markup Language (XML) files to the master data.

When you specify a CSV, XML, or Nested XML source data file while creating or editing import maps in the Managing Import Maps page, the file data isn't directly uploaded to Product Hub, but is first uploaded to an item batch. You can upload source data using import maps from the Manage Item Batches page.

You can create an import map or edit an existing import map using the following procedure.

Note: You can also import the setup data for an import map from another instance of Oracle Fusion Applications. For details, see the topics cited here about importing setup data using an offering or functional area.

Create or Edit an Import Map

Create or edit an import map as follows.

  1. Depending on your access privileges, you can create or edit import maps in these ways:

    • In the Product Information Management work area, select the Manage Import Maps task from the panel drawer.

    • In the Setup and Maintenance work area, use the Manage Import Maps task:

      • Offering: Product Management

      • Functional Area: Items

      • Task: Manage Import Maps

  2. To create an import map, click the Create icon on the toolbar or select Actions > Create from the menu.

    To edit an existing map, search for and select the map, then click the Edit icon on the toolbar or select Actions > Edit from the menu.

  3. On the Create Import Map or Edit Import Map page, specify the following import map information.

    UI Element

    Description

    Import Map

    Enter a name for the import map. This field is required.

    Description

    Enter a description of the import map.

    Item Class

    Select an item class to associate with the import map.

    This item class is associated to items that don't have an item class specified in the data file uploaded to an item batch for import. The item class is also used to display this import map in Oracle Product Hub Portal if you select the External check box.

    Source Map

    Optionally, select an existing import map to inherit its import map details.

    If a source map is selected, the Data Map section of the current map is populated with inherited item mapping information. You can also map additional source data, but you can't edit or delete the inherited item mapping details.

    External

    Select to specify that the mapping can be used for third-party, external suppliers.

    Suppliers

    This option appears if you select External.

    Click the Manage Suppliers icon to open the Manage Suppliers dialog box, and select the supplier for which the import map is defined, and who can view the import map.

    You can specify multiple suppliers, by adding more in the Manage Suppliers dialog box. If no supplier is specified, the import map is visible to all suppliers.

    Active

    Select to enable the import map for use.

Specify Source File Information

Specify information in the Source File section as specified in the following table.

UI Element

Description

File Type

Specify the source file type to be used to upload data. Options are: Text, XML, Nested XML.

Delimiter

Specify the type of delimiter used in the source data file. Options are: Comma, Tab, Colon, Semicolon, Pipe, Space, Other.

Date Format

Select a format for the date.

Time Stamp Format

Select a format for the time stamp.

Encoding Type

Select an option to specify the file encoding format. Options are: US-ASCII, ISO-8859-1, UTF-8, UTF-16BE, UTF-16LE, UTF-16.

Upload From

Select Desktop, URL, or Network from which to upload source data.

Specify the source data file location:

  • Desktop: Click Browse and select the source data file.

  • URL: Enter the URL for the source data.

  • Network: Specify whether the file is a recent file or a specific file. Enter the path of the source data file.

Templates

Displays the Microsoft Excel templates generated for the import map.

Map Source Data and Master Data Attributes

The Source Data table displays the data columns from the source file. The Master Data table displays the item attributes available in Oracle Product Hub. You can map attributes from different item classes in a single import map. You can map one source map attribute to multiple master data attributes, but not the other way around. You can create expressions on source data attributes to combine multiple source data attributes and map the expression to a master data attribute.

  1. In the Master Data table, select an option in the Item Class field to specify the item class from which to view and map the master data attributes. The default item class is Root Item Class.

  2. In the Master Data table, locate a master data attribute to which you want to map a source data attribute.

    Expand the hierarchical attribute tree to locate an attribute. You can also search for an attribute by entering all or part of the attribute's name in the search field above the master data list. Click the Next and Previous controls to locate multiple occurrences of the attribute.

  3. To map a source data attribute to a master data attribute, drag and drop an unmapped row from the Source Data Column of the Source Data table onto an attribute in the Master Data table.

    Alternatively, select a data column in the Source Data table, select Actions > Map Source Columns, search for and select a master data attribute in the Select Master Column dialog box, and click OK.

  4. If you're using transformation expressions to operate on source data:

    1. Select a source data column and click Create Expressions on the toolbar, or Actions > Create Expressions from the menu. The Create Expression page displays.

    2. Click Select to specify the master data column with which to map the expression. The Select Master Column dialog box displays.

    3. Search for and select the master data column and click OK.

    4. Select a function in the Functions tab and click Insert. The expression template text appears in the pane at the bottom of the Create Expression page.

    5. In the expression template, position the cursor where you want to add an attribute, select the source attribute from the Source Columns tab, and click Insert. The column is added to the expression.

    6. Insert the required columns to the expression format to create the required expression. Delete the placeholder text from the expression template.

    7. Click OK to create the expression and map it to the master data attribute. The Create Expression page closes, and the expression you created appears in the Expression column in the Source Data table of the Create Import Map page.

      Note: Alternatively, to save an expression and create another expression on the same source data column, click Apply and Create Another on the Create Expression page.

  5. In the Preview Data section, click the Refresh icon or select Actions > Refresh from the menu. The table is populated with mapped data. You can preview the result of the mapping and make changes if required.

  6. Click Generate Templates if you want to create a Microsoft Excel template, also referred to as Smart Spreadsheet, for the import map.

  7. Click Save.

Generate Smart Spreadsheets

Product data stewards can generate Smart Spreadsheets for import maps. These spreadsheets help users in reducing data entry errors while creating the data files that are to be used for importing data into Product Hub. These spreadsheets contain the attributes that are mapped in the import map, with their respective list of valid values and metadata information, such as data type, maximum length, and precision. The data entered in the spreadsheet is validated against the attribute metadata. Data files can then be generated from the populated spreadsheets, which can then be used to import data into Product Hub.The Smart Spreadsheets that are generated take only the attribute metadata that's available at that point in time when they're generated. So if any attribute metadata is edited after the Smart Excel is generated, then you need to regenerate the Smart Spreadsheet.

Here's how you create Smart Spreadsheets:

  1. Click the Generate Templates button in the Source Data region of the Import Map.

  2. In the Generate Templates dialog box, select the languages for which the spreadsheets are to be generated.

  3. Click Generate. Language-specific Smart Spreadsheets are generated and attached to the import map. Use the link at the Templates field of the Source File region of the map to download the templates.

Keep these points in mind:

  • We strongly recommend that you don't create Smart Spreadsheet templates with more than 256 columns. If more than 256 columns exist in a template, then you will be asked to save the spreadsheet as a macro-enabled workbook (.xlsm file) when you open it.

  • A Smart Spreadsheet takes longer to open if there are several columns containing a large list of values.

You can regenerate Smart Spreadsheets in one of these ways:

  • Clicking the Generate button on the Edit Import Map page and saving the Import Map

  • Running the Regenerate Import Map Templates scheduled process.

    When scheduling this process, select these options:

    • Item Class: If blank, the templates are generated according to the selections in the Import Maps and Languages fields. If you select an item class, then templates are generated for the import maps for that item class and also for its child item classes. The default is no selection of item class.

    • Import Maps: The default is With templates.

      • Select With templates to regenerate templates only for import maps that have templates.

      • Select All to regenerate templates for all import maps.

    • Languages: Select the languages for which the spreadsheets are to be regenerated, or for all installed languages. If you don't make any selection, then templates are only generated for languages that exist for the import maps affected by the Import Maps field. The default is no selection of languages.

    The selections you make for these process options are considered as a set of criteria for whether to generate templates (meaning Item Class AND Import Maps AND Languages).

If you're using Smart Spreadsheets on a Mac:

  • The data file automatically generated from the Smart Spreadsheet is saved to the folder containing the spreadsheet.

  • If more than 256 attributes are mapped in the import map, you need to save the Smart Spreadsheet as a macro-enabled workbook (.xlsm file) before using it.

Add Data, Generate Data File, or Upload Data Using Smart Spreadsheet

The smart spreadsheet contains two sheets-Instructions sheet and Data sheet. The Instructions sheet provides the information to validate data, generate a data file, and upload the data to the item batch for import. The Data sheet is where you will enter the data. It also provides the name of the item batch, spoke system, and the import map that was used to generate the spreadsheet. These attributes won't display in the smart spreadsheets generated for import maps marked as external.

If the spreadsheet was generated from the Manage Items or Browse Items pages, then:

  • Item batch name is autogenerated.

  • Spoke system name is the product information management data hub spoke system.

If the spreadsheet was downloaded from the Edit Import Map page, then the item batch name and spoke system name is blank.

Note:

  • If you change the import map name, the specified import map won't be used for generating the data file or uploading the data to an item batch. This attribute is for information purpose only

  • Item attributes with only 10000 values are displayed as drop-down lists in the smart spreadsheet

  • The smart spreadsheets doesn't work with 32-bit Microsoft Office on Mac and 64-bit Microsoft Office is required

  • Smart spreadsheet is certified on Microsoft Excel 365. The spreadsheet is supported only for those Excel versions that are currently supported by Microsoft Corporation.

Adding and Validating Data in the Smart Spreadsheet

To enter the data in the smart spreadsheet, refer to the bubble text on each column header for the description of the data and data type that the column requires. After adding the data, click Validate on the Add-ins tab to validate the data. If there are any errors, the respective cells are highlighted. Click the bubble text in the column header to understand and correct the error. If a value isn't entered for a mandatory attribute, warning is displayed. Resolve all the errors and warnings and then click Validate to confirm that there are no errors.

If the smart spreadsheet is generated from the Manage Items or Browse Items pages, then:

  • Enter the date attribute in the MM/DD/YYYY format.

  • Enter the timestamp attribute in the YYYY-MM-DDTHH:mm:ss.SSS-Z format. SSS indicates milliseconds and Z indicates the UTC time zone. For example, 2001-07-04T12:08:56.235-0700.

  • The revision effective date is displayed in the UTC time zone.

After validating the data, you can either generate a data file or upload the data to the item batch for import.

Generating a Data File

To generate a data file, click Generate Data File on the Add-ins tab. Use this data file to import the data into Product Hub.

Set Change Order and New Item Request Options in Smart Spreadsheet

You can set the change order and new item request options in the smart spreadsheet before uploading the item data from the smart spreadsheet to the item batch. The display of these options depends on the Item Batch Options in Smart Spreadsheets Enabled profile option. If this profile option is enabled, then after specifying the access token during upload, the Item Batch Options dialog box is displayed, where you can specify the change order and new item request settings. In case the access token was already provided and is still valid, then the Item Batch Options dialog box dialog is displayed directly.

These change order and new item request batch options are the same as the Edit Batch Options dialog that is displayed while editing an item batch from the Manage Item Batches page.

The initial values of these options are populated from the Spoke System settings of the item batch being created or updated.

The Item Batch Options dialog box won’t display if the value of the Item Data Conversion Mode Enabled profile option is set to Yes.

If both Item Batch Options in Smart Spreadsheets Enabled and Item Data Conversion Mode Enabled profile options are set to Yes, then the Item Data Conversion Mode Enabled profile option has higher precedence and the Item Batch Options dialog box won’t display in the smart spreadsheets.

The Item Batch Options in Smart Spreadsheets Enabled profile option is set to No by default.

Uploading Data to the Item Batch for Import

The data is uploaded to the item batch based on the batch name and spoke system name in the smart spreadsheet. You can change these names based on your requirement. If the item batch name doesn't exist, it will create the batch and then upload the data into the batch.

Note: You can only upload up to 10000 rows from the smart spreadsheet to an item batch. If there are more than 10000 rows, you must generate the data file from the smart spreadsheet and upload it to an item batch from the Manage Item Batches page.

To upload data to the item batch for import:

  1. On the Add-ins tab, click Upload Data.

  2. In the Upload Data dialog box, click Generate Access Token. If you're not logged in to Product Hub, you will need to log in after clicking Generate Access Token.

  3. In the Generate Access Token page, click Generate and Copy Access Token. The access token is copied to the clipboard.

  4. Paste the access token in the Upload Data dialog box and click Upload Data.

    The upload progress is displayed. After the upload process is complete, the number of rows that are uploaded and the batch number is displayed.

    Note: If you're using Mac, click Paste Access Token in the Upload Data dialog box to paste the access token.

Important Points

  • If you upload the data without validating it and if there are any errors and warnings, then the respective cells are highlighted. Resolve all the errors and warnings and then upload the data again.

  • If you make any changes to the data and click Upload Data, the upload starts, and you don't have to provide the access token again. But, if you close the spreadsheet and open it, then you must generate the access token for uploading the data.

  • If a batch with the name specified in the Item Batch Name attribute doesn't exist, then a new batch with that name is created and the data is uploaded to that batch.

  • If the smart spreadsheet was generated using the Mass Update action, then the created batch is of the Mass Update type. If the smart spreadsheet was generated from the Edit Import Map page, then the created batch is of Import type.

  • Smart spreadsheets of external import maps don't allow data upload directly from the smart spreadsheets to an item batch.

  • When using a smart spreadsheet on Mac to upload data to an item batch, if the smart spreadsheet contains multi-byte characters, you'll be asked to grant additional permissions for generating and accessing the data file on your system before the data is uploaded. You must grant this permission for the data to get uploaded to the item batch.

  • Descriptive flexfield attributes are shown as free text in the template. Valid values aren't shown for descriptive flexfield attributes.
  • Dependent value sets don't show dependent values in the template. All values from the value set are shown in the drop-down of the template.
  • When uploading the data:
    • Ensure the date and date time formats match with the import map setup to minimize errors during upload.
    • The numeric decimal separator and grouping separator are honored based on the user locale. The application user preferences aren't considered.

Use Transaction Type

You can control imports by using the attribute Transaction Type in import maps.

  • For all spoke systems, you can create, update, sync, or delete an item entity when you import item data using import maps, by specifying the value for the Transaction Type attribute as CREATE, UPDATE, SYNC, or DELETE. Only those item entities that can be deleted through FBDI can be deleted through import maps as well.

  • If data is uploaded directly to an item batch using import maps in Oracle Product Hub, then the valid values for the Transaction Type attribute are: CREATE, UPDATE, SYNC, and DELETE. Note that the DELETE Transaction Type is only supported for item entities that can be deleted through FBDI.

  • If data is uploaded using product uploads in Product Hub Portal, then the valid values for the Transaction Type attribute are SYNC and DELETE. If the value for the Transaction Type attribute is specified as CREATE or UPDATE, then it's converted to SYNC during the data file upload process.

  • If the Transaction Type attribute isn't mapped, or is mapped but has no value provided in the data file, then the SYNC transaction type will be inserted by import maps in the interface tables.

  • Use the Delete transaction type in item structure import maps and FBDI templates, to delete the structure entities such as components, substitutes, and reference designators in an item structure. For details see the topic Item Batch Structure Options.

  • For translatable extensible flexfields, the Transaction Type attribute is displayed for mapping in import maps once per installed language.

  • You can manage attachments to items, item revisions, or trading partner items, by selecting the value for the Transaction Type attribute, as CREATE, UPDATE, SYNC, or DELETE. For details, see the topic on import of attachments.

  • If you upload data using the Supplier Products REST resource in Oracle Product Hub Portal Cloud, then the Transaction Type attribute:

    • Is available only for extensible flexfields, relationships, and categories.

    • Can only have the values SYNC and DELETE. A value of CREATE or UPDATE causes an error.

    • For single-row extensible flexfield data, can only have the value SYNC. A value of CREATE, UPDATE, or DELETE causes an error.

Related Topics