This chapter covers the following topics:
Bulk import is the process of loading supplier data in bulk from legacy, third party, or other external sources into the Supplier Master, which includes TCA Registry. In Oracle Supplier Data Librarian responsibility, you can use Batch Import to transfer batches of data from the interface tables into the Supplier Master. A batch is a set of data to be loaded into the Supplier Master at one time. The data in one batch must be from the same data source. With Batch Import, you can review and control the import process. Supplier import process provides validations and optional de-duplication to ensure the quality of information being imported.
Using the Supplier Import feature, you can import the following supplier information:
Organization Party
Address
Contact
Classification
Relationships
User Defined Attributes
Additionally, you can import child entities including supplier product and services category associations, business (diversity) classifications, bank detail, and additional tax details.
The import process creates a party in the TCA registry and enables the created TCA Parties in the Supplier Master as suppliers. You can also use the supplier import process to enable existing TCA Organization parties as suppliers in the Supplier Master using the batch import functionality described above.
Follow these steps to import suppliers using the Batch import process:
Define Pre-requisites
Generate Batches
Load data into interface tables
Define Import
View and Update the Batch (Update works only if Pre-import only was run)
Purge Data from interface tables
Define Source Systems. This is required before creating batches since a batch is created for importing from a specific source system. See Source System Management in Oracle Trading Architecture Administration Guide.
Log in with Supplier Data Librarian or Supplier Data Librarian Super User responsibility and click on Supplier Home menu. Navigate to the Import tab to create batches for import. Import tab is also visible from the Supplier Management User and Supplier Management Administrator responsibilities.
Generate batches after setting up the source systems. Create import batches before loading data into the import interface tables. After a batch is generated, you can use any available loading tools to populate TCA interface tables. A generated batch initially has no batch status.
Click Generate Batch on the Import Batches page to open the Generate Batch page. See Generating Batches in Oracle Customer Data Librarian User Guide.
Use the batch ID of the import batch to load supplier data into interface tables. See Loading Data into the Interface Tables in Oracle Trading Architecture User Guide. Please note that File Load feature of TCA is not available in Supplier Hub.
Integrated supplier import program imports the data from several TCA and AP interface tables; therefore upload data into these interface tables. See: Defining Imports.
Supplier Hub enables you to import supplier details such as organization (HZ party), person, address, contact, supplier, supplier site, supplier contact, user defined attributes using a spreadsheet. Spreadsheet import uses display formats that enable you to load different set of data for a given batch into the interface tables.
While searching for supplier information, data librarians can select the search criteria and display format. The application retrieves the data in the selected format and data librarians can import data into the interface tables using these formats used for the spreadsheet. See Setting up Search Criteria and Display Formats in the Oracle Supplier Management Implementation and Administration Guide.
Use the Data Librarian Super User responsibility and the Import tab to process the batch.
To import supplier data using a spreadsheet, complete the following:
Click Generate Batch and enter a batch name and source system.
Click Apply and ensure to note the batch ID.
Click Advance Search available on the Supplier Home page.
Select Search Criteria, Display Format, and click Search.
Click on Export All button.
Note: Before you perform this step, ensure to check the Trust access to Visual Basic Project if you are using MS Office XP, 2003, 2007. See: Setting up Spreadsheet Import in Oracle Supplier Management Implementation and Administration Guide.
Select Excel version.
Save the exported excel file on your PC.
Open the saved excel file and click on Enable Macro.
Modify the record as required and save.
Click Add-Ins tab > Oracle > Upload or Oracle tab > Upload based on your MS Excel version to upload the modified excel file.
Click in the Result Format Usage ID field in the Upload Parameters window. The application displays the import batch ID in this field.
Click Upload. The application confirms the successful completion of the concurrent program. Note the number.
Navigate to Administration > Concurrent request > View request and search for the concurrent request ID. Click Refresh button till concurrent program gets completed with normal status.
Click Import and select the Batch number that you received after generating the batch.
Click Activate Batch.
Select the batch and click Define import. Verify the batch gets submitted for the import and complete with normal status. Search for the supplier and notice the modifications that you had made and imported using the spreadsheet import format.
After data is loaded into the interface tables for this batch, activate the batch on the Import Batches page, and then proceed to define the import. This step is optional, but active a batch to see the total number of records in the interface tables for that batch.
Select a batch in the Import Batches page and click Define Import to open the Define Import: Batch De-Duplication page.
Note: To perform address validation during import (one of the pre-processing steps), you must insert data into HZ addresses interface table (HZ_IMP_ADDRESSES_INT) and not in the AP addresses interface table (AP_SUPPLIER_SITES_INT).
See Defining Imports in Oracle Customer Data Librarian User Guide.
Import Program: Defining the batch automatically triggers batch import. In case of Supplier Hub, the concurrent program run is ‘Integrated Supplier Import program’ instead of Import Batch to TCA Registry program.
The ‘Integrated Supplier Import’ concurrent program first runs ‘Import Batch to TCA Registry’ program, which imports all the party information like party, address, contact, classification and relationships into the TCA registry. See Import Batch to TCA Registry, Oracle Trading Architecture User Guide for details on ‘Import Batch to TCA Registry’ program provided by TCA. When Party Import status is ‘Completed’, Integrated Supplier Import program automatically enables the imported party as a supplier in the Supplier Master.
The integrated import program processes the data from the TCA interface tables and creates the entities in the HZ tables. It also creates the corresponding supplier related entities in the AP tables and synchronizes them with the HZ data.
If the data is inserted into the HZ_PARTIES_INT table in the insert mode and the import program is executed, then a new party is created and it is enabled as a supplier. If the processing mode is update, then the corresponding party data is updated. If the party is already enabled as a supplier, then the supplier information would be synchronized with the updated information from the party tables. If the party being updated is not already enabled as a supplier, then a new supplier would be created with the updated information.
If the data is inserted into the HZ_IMP_CONTACTS_INT table, then the person would be associated as a contact to the party and it would also be enabled as a supplier contact. In the update mode the contact details would be updated. If the contact were not already enabled as a supplier contact then it would be enabled.
If the data is inserted into AP interface tables and not the HZ interface tables, then Integrated Supplier Import program will insert data from AP interface tables to HZ interface tables as a part of preprocessing (AP_SUPPLIERS_INT to HZ_IMP_PARTIES_INT, AP_SUPPLIER_SITES_INT to HZ_IMP_ADDRESSES_INT, AP_SUP_SITE_CONTACT_INT to HZ_IMP_CONTACTS_INT). The 'Integrated Supplier Import' concurrent program first runs 'Import Batch to TCA Registry' program, which imports all the party information like party, address, contact, classification, and relationships into the TCA registry. After the party import program is completed, it creates supplier and supplier related entities in AP tables.
When you run the Integrated Supplier Import program using Define Import > Submit, then this integrated program calls the AP interface import programs. The import process picks up the data in AP interface tables for processing based on the SDH_BATCH_ID column for which the import process is running. For further information on AP supplier interface tables, see Understanding the Supplier Open Interface Tables in Oracle Payable's Reference Guide.
The AP supplier interface tables have additional columns to support the Integrated Supplier Import program.
AP_SUPPLIERS_INT - the following columns are available in this table for storing batch id, party id, and original system references:
PARTY_ORIG_SYSTEM
PARTY_ORIG_SYSTEM_REFERENCE
SDH_BATCH_ID
PARTY_ID
AP_SUPPLIER_SITES_INT - the following columns are available in this table for storing batch id, party id, and original system references:
PARTY_ORIG_SYSTEM
PARTY_ORIG_SYSTEM_REFERENCE
PARTY_SITE_ORIG_SYSTEM
PARTY_SITE_ORIG_SYS_REFERENCE
SUPPLIER_SITE_ORIG_SYSTEM
SUP_SITE_ORIG_SYSTEM_REFERENCE
SDH_BATCH_ID
PARTY_ID
LOCATION_ID
AP_SUP_SITE_CONTACT_INT - the following columns are available in this table for storing batch id, party ids, and original system references:
PARTY_ORIG_SYSTEM
PARTY_ORIG_SYSTEM_REFERENCE
PARTY_SITE_ORIG_SYSTEM
PARTY_SITE_ORIG_SYS_REFERENCE
SUPPLIER_SITE_ORIG_SYSTEM
SUP_SITE_ORIG_SYSTEM_REFERENCE
CONTACT_ORIG_SYSTEM
CONTACT_ORIG_SYSTEM_REFERENCE
SDH_BATCH_ID
PARTY_ID
PER_PARTY_ID
REL_PARTY_ID
RELATIONSHIP_ID
ORG_CONTACT_ID
Mandatory columns for HZ_IMP_PARTIES_INT
BATCH_ID
PARTY_ORIG_SYSTEM
PARTY_ORIG_SYSTEM_REFERENCE
PARTY_TYPE
ORGANIZATION_NAME
PERSON_FIRST_NAME
PERSON_LAST_NAME
CREATION_DATE (Needed in case of de duplication; this date can be sysdate)
Mandatory columns for AP_SUPPLIERS_INT
VENDOR_INTERFACE_ID
SDH_BATCH_ID
PARTY_ORIG_SYSTEM
PARTY_ORIG_SYSTEM_REFERENCE
VENDOR_NAME
SEGMENT1 (Same as PARTY_ORIG_SYSTEM_REFERENCE)
Mandatory columns for AP_SUPPLIER_SITES_INT
VENDOR_SITE_INTERFACE_ID
VENDOR_SITE_CODE
ADDRESS_LINE1
COUNTRY
ORG_ID and/or OPERATING_UNIT_NAME
PARTY_ORIG_SYSTEM
PARTY_ORIG_SYSTEM_REFERENCE
PARTY_SITE_ORIG_SYSTEM
PARTY_SITE_ORIG_SYS_REFERENCE
SDH_BATCH_ID
Mandatory columns for HZ_IMP_ADDRESSES_INT
BATCH_ID
PARTY_ORIG_SYSTEM
PARTY_ORIG_SYSTEM_REFERENCE
SITE_ORIG_SYSTEM
SITE_ORIG_SYSTEM_REFERENCE
ADDRESS1
CITY
COUNTRY
PARTY_SITE_NAME (this is same as VENDOR_SITE_CODE)
Mandatory columns for AP_SUP_SITE_CONTACT_INT
VENDOR_SITE_CODE (if it is site contact)
ORG_ID and/or OPERATING_UNIT_NAME
FIRST_NAME
LAST_NAME
AREA_CODE (if present)
PHONE (if present)
EMAIL_ADDRESS (if present)
FAX_AREA_CODE (if present)
FAX (if present)
VENDOR_CONTACT_INTERFACE_ID
PARTY_ORIG_SYSTEM
PARTY_ORIG_SYSTEM_REFERENCE
PARTY_SITE_ORIG_SYSTEM (if it is site contact)
PARTY_SITE_ORIG_SYS_REFERENCE (if it is site contact)
CONTACT_ORIG_SYSTEM
CONTACT_ORIG_SYSTEM_REFERENCE
SDH_BATCH_ID
Mandatory columns for POS_PRODUCT_SERVICE_INT
SDH_BATCH_ID
SOURCE_SYSTEM
SOURCE_SYSTEM_REFERENCE
PS_INTERFACE_ID (unique for table)
REQUEST_STATUS
SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, SEGMENT5, SEGMENT6, SEGMENT7, SEGMENT8, SEGMENT9, SEGMENT10, SEGMENT11, SEGMENT12, SEGMENT13, SEGMENT14, SEGMENT15, SEGMENT16, SEGMENT17, SEGMENT18, SEGMENT19, SEGMENT20 (Depending upon segment definition)
SEGMENT_DEFINITION
Mandatory columns for POS_BANK_ACCOUNT_DET_INT - at Supplier Level
BANK_ACCOUNT_INTERFACE_ID
COUNTRY_CODE
ALLOW_INTERNATIONAL_PAYMENTS
BATCH_ID
SOURCE_SYSTEM
SOURCE_SYSTEM_REFERENCE
BANK_NAME
BRANCH_NAME
BANK_ACCOUNT_NUMBER
ACCOUNT_NAME
Mandatory columns for POS_BANK_ACCOUNT_DET_INT - at Party Site Level
BANK_ACCOUNT_INTERFACE_ID
COUNTRY_CODE
ALLOW_INTERNATIONAL_PAYMENTS
BATCH_ID
SOURCE_SYSTEM
SOURCE_SYSTEM_REFERENCE
BANK_NAME
BRANCH_NAME
BANK_ACCOUNT_NUMBER
PARTY_SITE_ORIG_SYS
PARTY_SITE_ORIG_SYS_REF
ACCOUNT_NAME
Mandatory columns for POS_BANK_ACCNT_OWNERS_INT
BANK_NAME
BANK_COUNTRY_CODE
BRANCH_NAME
ACCOUNT_NUMBER
SOURCE_SYSTEM
SOURCE_SYSTEM_REFERENCE
ACCOUNT_OWNER_NAME
BATCH_ID
PRIMARY_FLAG
BANK_ACCOUNT_INTERFACE_ID
BANK_ACCT_OWNER_INTERFACE_ID
To view or update import batch requests:
Search for the batch on the Batch Import page.
Select the batch in the search results region to review import results after the import process completes.
To view import errors, click the View Party Errors and View Supplier Errors buttons on the batch detail page.
A concurrent request is submitted for generating the error report for the batch. Click the Refresh button, until the concurrent request gets completed and then click the Output icon to view the error report.
To update a batch, select the batch in the search results region, and click the Update icon. You can only update a batch if the batch has a status of Action Required.
See Viewing or Updating Import Batches in Oracle Customer Data Librarian User Guide. Additionally see Resolving Import Errors in Oracle Customer Data Librarian User Guide.
Purge import batches to permanently remove all records in the batch from the import interface tables, as well as internal tables such as staging and error tables. You can purge any batch, imported or not, except those that have pre-import processes or the import process running.
You usually purge batches:
After a satisfactory number of records are successfully imported into the TCA Registry from that batch.
If the batch is rejected and you do not plan to import that set of data using another import batch.
Purging the interface tables improves import performance. To archive imported data, you should copy the data to a set of custom tables. See TCA Import Batch Purge in Oracle Trading Architecture User Guide.
Use the batch load process to update D&B information for a large number of parties on a regular schedule or whenever you need. You can update all parties, gather data only for new parties, or update parties that have not been updated since a specified date. Batch retrieval and loading of D&B information is usually a regularly scheduled, automated process that you set up and run. See Batch Loading in Oracle Trading Community Architecture User Guide. Instead of running "Import Batch to TCA Registry", run the Integrated Supplier Import from Supplier Data Librarian responsibility to transfer data from interface tables into the Supplier Master.
Additionally, see the following:
Searching for Import Batches in Oracle Customer Data Librarian User Guide
Managing Import Batches in Oracle Customer Data Librarian User Guide
Viewing Batch Import History in Oracle Customer Data Librarian User Guide