Application Composer Series: Importing Custom Object Data
By Richard Bingham-Oracle on Apr 24, 2014
The topic of importing data can be quite complex, especially as the
rich functionality and list of options continues to develop with each
new release. This article focuses on getting actual data into the system for your custom objects, for either testing or for actual production use.
Option 1: Import via the Web Service
The first method follows on from my previous post on using the web services provided for use with custom objects. The createEntity operation can be used for each of the web service interfaces, and passing in a payload for each custom object is relatively simple to do. The following video illustrates this.
One drawback of this method is that it's not intended to support bulk loading of data, so if you needed to create hundreds or thousands of records in one go then making repeated requests is not ideal. Fortunately there is another option in this case.
Option 2: Import via File Loading
There are many reasons and options for getting files loaded into Fusion Applications, such as:
- Import of Standard Object data for different products, such as these for Oracle Sales.
- Adding files into the WebCenter Content repository for use as a standard set of attachments.
- Importing your customizations as MDS metadata from you test or dev environment.
- Importing and migrating of the functional setup data as part of implementation.
- Import of additional transaction data (e.g. Purchase Orders), both as an initial load and as part of ongoing integration solutions.
Over the last few releases the file import process has been standardized, ensuring that a single robust solution is available for multiple purposes. This standard process is used for adding custom object data and can be summarized by the following diagram, with the corresponding task to be used in Setup and Maintenance in red:
Finally, some data (such as transactions) requires special processing in moving it from the interface tables into the application tables, something usually done by submitting a specific final ESS job. This is not required for custom objects however.
The first thing to note is that for Custom Objects and Custom Fields there is a difference between the customization and the import technology components, and as such an extra initial step is needed to get the object from the extensibility architecture into the import architecture (namely into Oracle Data Integrator). As shown in the screenshot below, this process is available in Application Composer under the Export and Import task and is entitled "Generate Import and Export Artifacts". Once run it will pick up all newly created objects and fields.
Once the generate process is complete, next is optionally creating a reusable map between your object (contained by a generic object for each application) and the fields in the file you are about to import. These generic custom object containers simply give you access to the custom objects held inside each related application (such as a Ticket custom object created in the Sales application, and therefore contained in the 'Sales Custom Object'). You use this in creating the mapping and importing definitions, and the following container objects are available for use as of Release 8:
- Sales Custom Object
- Common Custom Object (for mapping only)
- Customer Center Custom Object
- Marketing Custom Object
- Sales Performance Management Custom Object
As mentioned, the mapping step is optional, since when creating the import definition (via the Manage File Import Activities task) you can create a map on the fly, however for reuse and consistency a map is recommended.
Obviously the web service method supports SOAP-based XML payloads only.
The file import process supports both CSV and XML formats, and can be transferred from either your desktop, a URL, or a shared file server. If you make a formatting mistake the load will either show a status of "Complete with Errors" or "Complete" and you should click the status field to see the details page.
All custom objects require at least the following standard fields in the import file, in addition to your own custom fields.
- ObjectKey - a unique 15 digit numerical identifier
- RowType - your object name (API Name), always ending in '_c'
- RecordName - your unique object name field
One other thing to note is that custom child objects can be imported in the same file and process as their parent objects.The following video from our YouTube channel gives a very simple example, where a custom 'Ticket' object has been created and three records are imported via a file.
File Loading Tips and Tricks
- Be careful to make sure MS Excel doesn't auto-format your ObjectKey with the exponent format, such as 1.23E+14. Set the field to a number type (from general type), and always double-check the CSV file in a text editor to be sure of the content.
- File-based imports always bypass any Groovy validation and trigger logic that might exist on an object.
- Your custom objects need to be published from a sandbox to the MDS mainline to be available for data import.
- When you submit the file import process, you need to close and reopen the Manage File Import Activities page to get a proper refresh of the status.
- If you get format errors in your file import look for an attachment in the status page. Match the detailed error messages in there with MyOracleSupport for solutions to common issues.
- In Release 7 for objects created in the Sales Application you should create mapping using the generic custom object
"Opportunity Custom Object". Similarly for objects created in Customer
Center application the mapping needs to use the "Sales Account
Custom Object". From Release8 onwards the mapping is consistent with the import, against the "Sales Custom Object" and "Customer Center Custom Object"
The following resources has some additional content related to the options for importing custom objects:
- Developer Relations YouTube Channel
- Sales Cloud Implementation Guide
- The CRM Extensibility Guide
- The public Fusion Help Portal - Importing Custom Objects
- Oracle Enterprise Repository