Organize Your Data in the FBDI Spreadsheet Import Template
- Download the FBDI template for the object you want to import.
- Open the File-Based Data Import for SCM guide on Oracle Help Center.
- 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.
- In the topic for the object, click the XLSM file link to download the template (for example, ItemImportTemplate.xlsm).
- After downloading the appropriate template, open it and confirm that macros are enabled.
- 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.
- 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.
- Refer to 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 sufficient.
- 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.
Interface Tables and Related Control Files
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.
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_INTF |
Trading Partner Items |
EgoItemAttachments.ctl |
EGP_ITEM_ATTACHMENTS_INTF |
Item Attachments |
- Build an export file in the same format as the Product Hub interface described in the template. You will 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 have extracted the data into your temporary spreadsheet, cut and paste the data into the relevant sheets provided in the template.
- For ID columns, you will 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 will 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 will 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