1. Implementing Product Management
  2. Organize Your Data in the FBDI Spreadsheet Import Template

Organize Your Data in the FBDI Spreadsheet Import Template

To organize the data in the FBDI import template:
  1. Download the FBDI template for the object you want to import.
    1. Open the File-Based Data Import for SCM guide on Oracle Help Center.
    2. Within the Contents panel in the guide, scroll to the object you need to import and click the name (Example: Item). You can also use the search bar to search within the book. For example, search for Item Import to navigate directly to the appropriate page.
    3. In the topic for the object, click the XLSM file link to download the template (for example, ItemImportTemplate.xlsm).
  2. After downloading the appropriate template, open it and confirm that macros are enabled.
  3. Fill in all the required data and any optional data as desired on any or all worksheets.

    The first tab in the file contains the instructions for using the template and generating the CSV ZIP file. The other tabs, also called Control Files, correspond to each of the item interface tables.

Tips for entering data into the template:
  • The first row in each sheet contains column headers that represent the interface table columns. The columns are in the order that the control file expects them to be in the data file.
  • Don't change the order of the columns in the Excel sheets. Changing the order of the columns will cause the load process to fail.
  • You can hide columns that you don't intend to use, but you can't delete columns. Deleting columns will cause the load process to fail.
  • You must enter data that conforms to what the control file can accept and process for the associated database column:
    • Date fields must be entered in YYYY/MM/DD format.
    • Amount columns must not contain a thousand separator and must use a period (.) as the decimal separator.
    • Columns that must be whole numbers have data validation to allow only whole numbers to be entered.
  • See the bubble text on each column header for information on the data and data type the column requires.
  • The predefined source system code PIMDH (for Product Information Management Data Hub) is used when you want to import items that don't originate from an external source system, such as a supplier.
  • The provided item class is called Root Item Class, this item class will be used for imports.
  • Items are always created in a master organization. Organizations are created during initial setup of the environment using the Setup and Maintenance task.
  • Provide a numeric Batch ID. Non-Product Hub customers don't have access to create batches, so any ID is enough.
  • The system identifies the items that must be updated based on the Transaction Type column. If Transaction Type is set to Sync, during import if the item exists in the database, then the item is updated. If the item doesn't exist in the database, then it’s created. The other key columns for updating the item are:
    • Item Number
    • Organization Code
    • Batch ID
    • Batch Number
  • To delete values of attributes, including individual values of multirow attributes, set the Transaction Type to Delete for the values you want to delete.
As the details of the mapping between the data of a source system and Product Hub might vary significantly based on the source system, the scope of these recommendations is limited to a best practices approach.
  • Build an export file in the same format as the Product Hub interface described in the template. You'll need to create staging tables and a spreadsheet that mirrors the tabs contained in this template. Then, a mapping needs to be decided as to which data of the source system goes into which Product Hub interface columns and tables. Based on the mapping, you should extract the relevant data from the source system. You can use SQL, ODI, or a similar tool to extract data into your staging tables.
  • Your spreadsheet must have the same columns, and the columns must be in the same order, as in the template. The value in the first column is always the interface table name.
  • Once you've extracted the data into your temporary spreadsheet, cut and paste the data into the relevant sheets provided in the template.
  • For ID columns, you'll need to develop a mapping between existing values and Oracle Fusion values. Use the implementation pages in the Setup and Maintenance work area to extract the identifier. For columns where you'll need to use the Setup and Maintenance work area to get the Oracle Fusion values, the comments in the column header of the spreadsheet mentions the task name which you should navigate to in the Setup and Maintenance work area.
  • For key flexfields, you'll also need to develop a way to map the values in the source system that correspond to the unique combinations in Product Hub. For key flexfield columns in the spreadsheet that require internal IDs, use the related Oracle Fusion implementation pages to export the internal IDs and the segment values to map to the source system values. The Items interface table has six columns that get their values from key flexfields:
    • Process Supply Locator
    • Process Yield Locator
    • WIP Supply Locator
    • Sales Account
    • Expense Account
    • Asset Category

FBDI Template to Import Items

You can use the FBDI Item Import template to import items along with its child entities such as revisions, relationships, associations, and category assignments. Download the ItemImportTemplate.xlsm template available in the Item section in the Oracle Fusion Cloud SCM: File-Based Data Import (FBDI) for SCM Guide.

Interface Tables and Related Control Files for Item Import

The set of interface tables include the main EGP_SYSTEM_ITEMS_INTERFACE table, in which you can insert item data. You can also insert 13 other interface tables including, data of the item's child entities such as revisions, categories, and extensible flexfields.

You can use the control files to load data from a .csv file into interface tables using the Load Interface File for Import scheduled process.

The following table lists the interface table and the related control files and objects.

Control File

Interface Table

Object

EgpSystemItemsInterface.ctl

EGP_SYSTEM_ITEMS_INTERFACE

Items

Assign Items to Child Orgs

EgpItemRevisionsInterface.ctl

EGP_ITEM_REVISIONS_INTERFACE

Item Revisions

EgpItemCategoriesInterface.ctl

EGP_ITEM_CATEGORIES_INTERFACE

Item Categories

EgoItemAssociationsIntf.ctl

EGO_ITEM_ASSOCIATIONS_INTF

Item Supplier Associations

EgpItemRelationshipsIntf.ctl

EGP_ITEM_RELATIONSHIPS_INTF

Item Relationships

EgoItemIntfEffb.ctl

EGO_ITEM_INTF_EFF_B

Item level flexfields

EgoItemIntfEfftl.ctl

EGO_ITEM_INTF_EFF_TL

Item level flexfields

EgoItemRevisionIntfEffb.ctl

EGO_ITEM_REVISION_INTF_EFF_B

Item Revision flexfields

EgoItemRevisionIntfEfftl.ctl

EGO_ITEM_REVISION_INTF_EFF_TL

Item Revision flexfields

EgoItemSupplierIntfEffb.ctl

EGO_ITEM_SUPPLIER_INTF_EFF_B

Item Supplier flexfields

EgoItemSupplierIntfEfftl.ctl

EGO_ITEM_SUPPLIER_INTF_EFF_TL

Item Supplier flexfields

EgoStyleVariantAttrvsIntf.ctl

EGO_STYLE_VARIANT_ATTR_VS_INTF

Style Variant Attribute

EgpTradingPartnerItemsIntf.ctl

EGP_TRADING_PARTNER_ITEMS_INT

Trading Partner Items

EgoItemAttachments.ctl

EGP_ITEM_ATTACHMENTS_INTF

Item Attachments

FBDI Template to Assign Items to Definition and Reference Organizations

You can use the FBDI Reference Org Item Import template to assign items to definition and reference organizations. Download the ItemImportReferenceOrgTemplate.xlsm template available in the Reference Org Item Import section in the Oracle Fusion Cloud SCM: File-Based Data Import (FBDI) for SCM Guide.

This template has three sheets:
  • EGP_IMPORT_REF_ORG_ITEMS_INT: This sheet is used to identify the list of items that needs to be assigned to various definition and reference organization. Specify a batch ID (a dummy batch ID that’s used for processing) and the item numbers.
  • EGP_IMPORT_REF_ORG_STRUCT_INT: This sheet is used to identify the structures that belong to each item, that will be common to the identified reference organizations.
    • Specify the same batch ID (that was specified in the previous sheet).
    • If the item number and structure name is provided in this sheet, then the structures will be made common to those reference organizations listed in the EGP_IMPORT_REF_ORG_ORGS_INT sheet.
    • If the item number isn’t provided and structure name is provided in this sheet, then the structures for the items listed in the EGP_IMPORT_REF_ORG_ITEMS_INT sheet will be made common to all the reference organizations listed in the EGP_IMPORT_REF_ORG_ORGS_INT sheet.
    • If the item number is provided and structure name isn’t provided, then all the structures are made common for the items listed in the EGP_IMPORT_REF_ORG_ITEMS_INT sheet.
  • EGP_IMPORT_REF_ORG_ORGS_INT: This sheet identifies the list of definition organizations and reference organizations to which the item will be assigned.
    • Provide the same batch ID that was identified in the first sheet.
    • If the definition organization is provided and not the reference organization, then the items in the EGP_IMPORT_REF_ORG_ITEMS_INT sheet will be assigned to all the associated reference organizations.
    • If the definition organization isn’t provided and the reference organization is provided, the items in the EGP_IMPORT_REF_ORG_ITEMS_INT sheet will be assigned to only these reference organizations.

Interface Tables and Related Control Files for Reference Organization Item Import

You can use the control files to load data from a .csv file into interface tables using the Load Interface File for Import scheduled process.

The following table lists the interface table and the related control files and objects.

Control File

Interface Table

Object

EgpImportRefOrgItemsInt.ctl

EGP_IMPORT_REF_ORG_ITEMS_INT

Items

EgpImportRefOrgOrgsInt.ctl

EGP_IMPORT_REF_ORG_ORGS_INT

Organizations

EgpImportRefOrgStructInt.ctl

EGP_IMPORT_REF_ORG_STRUCT_INT

Structures

Related Topics