This chapter helps to create and maintain Selection Sets, Extracts, Transforms and Loads.
This chapter covers the following topics:
Data migration is a four-step process:
Create a Selection Set.
Extract data from the source instance, using the selection sets.
Transform the extracted data, if required. (Optional)
Load the extracted or transformed data to the target instance.
Selection sets are the building blocks for the migration process. Oracle iSetup provides a set of templates allowing you to create selection sets assisted with existing templates. You can also create custom selection sets without using existing templates.
You can Create, Search, View, Update, Duplicate, or Delete a selection set.
Selection Sets
You can either do a Simple Search or an Advanced Search.
In Advanced Search, you can search on the following attributes of the selection set:
Name
Source Instance
Description
Last Updated On
In addition, you can have the search results match any or all the criteria you specify by selecting an appropriate button.
Advanced Search for Selection Sets
All selection sets that match your search criteria are listed in a table format. Use the search results page to:
View a list of selection sets with name, description, last updated on, source instance, and active status
Create a selection set using an existing template by clicking Create Standard
Create a custom selection set by clicking Create Custom if you have appropriate privileges
Update a selection setby clicking an icon to access the Selection Set: Update page
Delete a selection set by clicking an icon
View the details of a selection set by clicking the name link
Create a duplicate copy of a selection set by first selecting a selection set for copy and then clicking Duplicate
Extract an active selection set for data migration by first selecting a selection set and then clicking Extract
An Active flag in the search results indicates that a selection set is created without using the templates from earlier releases. If the selection set is inactive, with the Active flag unchecked, then the selection set cannot be used for any further data migration.
To view an existing selection set, click the desired name link on the search results page. This takes you to the Selection Set Details page which displays read-only information, including source instance, description, and a list of data objects that forms part of the selected selection set; and if it is a Custom Selection Set then one extra column, Sequence, is displayed. With the Custom Selection Set, the page displays buttons for Extract, Duplicate, Update to navigate from the view page to any of these flows.
Selection Set Details
Each data object of the selection set can have the following attributes:
Filters: If the data object has filterable values set, then the Filter icon appears. By clicking the Filter icon, you can access the View Filters page to view filter details associated with the data object.
Note: You can filter FND APIs in the selection set, and these APIs will be extracted in all languages as in the source instance.
See: Viewing Filters.
Supports Update: If the data object updates the target data during load, then the Support Update flag appears.
Sequence: Number given by user while creating Custom Selection Set for giving the order for loading the selection set.
You can view filter information associated with a selection set or an extract from the Selection Set Details page or the Extract Details page. The View Filters page displays read-only view of the filter name and its value associated with the given selection set or extract.
View Filters
Oracle iSetup allows you to create selection sets in the following ways:
Creating a Selection Set with a Template: Click Create Standard in the Selection Sets Search page to select a template for the selection set creation
Creating a Custom Selection Set: Click Create Custom in the Selection Sets Search page to create a selection set without using a template
Important: The Create Custom button appears only if the user is granted the iSetup Super User role along with the iSetup responsibility.
From the Migrations tab > Selection Sets subtab, click Create Standard to access the Create Selection Set: Select Template page. You must first select a selection set template and then set necessary parameters for the selection set.
You can View Selection Sets templates by selecting any of the available categories from View By list.
Following are the steps to create a selection set with a template:
Select a Template: Select a template from the available list of selection set templates. Each template consists of a specific set of data objects. For more information on objects within each selection set, see Selection Sets.
Important: In Release 12, all the selection set templates from earlier releases will no longer be available for the creation of a selection set. Selection sets created using templates from earlier releases will be inactive; that is, the flag is unchecked in the Active column. If an extract was created from the earlier releases of a selection set (an inactive selection set), then this extract is inactive and cannot be used for a Transform, Load, or Report process unless it has the Report icon allowing you to create reports.
Create Selection Set: Select Template
Name: Enter a name for the selection set. This is a required field.
Source Instance: Select the source instance. This helps you to set filters for the data objects during extraction. This is a required field.
Description: Provide informative description that could help in identifying the selection set.
Create Selection Set: Set Parameters
Set the Filter Criteria: This is optional. Filters can be set on some attributes of a data object. All data objects do not support filters.
Create Selection Set: Set Filters
Following are the steps to set filters:
Click the Filter icon. All filterable attributes appear on the page.
You can also search for values to set the filter on. To search, click the Search icon. In the new window, search and select the value you want to filter on. These values are retrieved from the selected source instance.
You can filter on more than one condition and the result is a filter of all the conditions.
Click Apply. You are routed back to the Create Selection Set: Set Parameters page.
Click Apply to Create Selection Set.
This takes you to the Selection Set page with a confirmation.
Click Apply and Extract to save the selection set and navigate to the Extract Window.
Note:
The Supports Update flag indicates if the data object updates the target data during load.
The Active flag indicates if the selection set is created from an earlier releases with an old template.
Only instances mapped using the Administration > Instance Mapping page are available as source instance during creation of the selection set.
Some FND objects have predefined filters already set. You can view filter details by clicking an icon in the Selection Set Details and Extracts Details pages.
Instead of creating a selection set using a template, users with the iSetup Super User role can create a custom selection set by adding the data objects available in the iSetup Interface Repository. See: Registering an Interface and Example of Data Migration Using Registered Interfaces.
From the Migrations tab > Selection Sets subtab, click Create Custom in the Selection Sets Search window to access the Create Custom Selection Set page.
Important: Only users with the iSetup Super User role along with the iSetup responsibility can see Create Custom displayed in the Selection Sets Search window.
Create Custom Selection Sets
Following are the steps to create a custom selection set:
Name: Enter a name for your selection set. This is a required field.
Source Instance: Select a source instance.
Description: Enter descriptive information for your selection set.
Click on Add Data Object. This action will take you to the LOV search page. Search for and select one or more Data Objects for which you want to create a Custom Selection Set.
After selecting a data object, you can see the following associated information populates:
Application Name: The application name that owns this interface.
Supports Update: Indicates whether this interface supports update during load.
Load Sequence: Indicates the order in which loading of data objects will occur.
Click Apply to get the confirmation page and return to the Selection Set Search page.
To better understand how to use the custom selection set creation feature, see Example of Data Migration Using Registered Interfaces for details.
You can update both Standard and Custom Selection Sets.
You can change the source instance, description, or filter criteria for a selection set. However, changing the source instance may invalidate previous filter settings.
Click Apply to save your update.
Click Apply & Extract to save and navigate to the Extract Window.
Selection Set: Update Standard
You can change the source instance, description, sequence, or filter criteria for a selection set. However, changing the source instance may invalidate previous filter settings.
Click Apply to save the update.
Click Apply & Extract to save and navigate to the Extract Window.
Selection Set: Update Custom
You can create a copy of a selection set, modify it to suit your business requirement and save it as a new selection set. By using the Duplicate feature, you save the time and effort of creating a new selection set with similar criteria.
Click Apply to save the selection set.
Click Apply & Extract to save and navigate to the Extract Window.
Selection Set: Duplicate
You can delete a selection set. Once it is deleted, it cannot be used to extract data from a source instance.
An extract is a snapshot of the data that you have extracted from a source instance. You can use the extract to perform migration. You extract data from the source instance using a selection set. You can Create, Upload, Download, Search, View, Transform, Load, or Report on an extract.
Extracts
You have created a selection set. For more information, see Selection Sets.
You can either do a Simple Search or an Advanced Search.
In Advanced Search, you can search on the following attributes of an extract:
Name
Request ID
Source Instance
Phase
Status
Submitted On
In addition, you can have the search results match any or all the criteria you specify by selecting an appropriate option.
Advanced Search for Extracts
All extracts that match your search criteria are listed in a table format. Use the search result page to:
View a list of extracts with name, request ID, source instance, submitted date, phase, extract status, download file, and active status
View the details of an extract by clicking the name link
Create an extract by clicking Create
Upload an extract by first selecting an extract and then clicking Upload
Download an extract by clicking the ZIP file
Delete an extract by clicking an icon
Transform an active extract by first selecting an extract and then clicking Transform
Load an active extract by first selecting an extract and then clicking Load
Create a report for an active or reportable extract by first selecting an extract and then clicking Report
An extract is active if it is of current release with phase as Completed and status as Normal.
Note: The Report icon in the Active column indicates this extract contains one or more data objects which can be reported upon.
To view Extract details, click the desired extract name link on the Extracts search results page. This takes you to the Extract Details page which displays read-only extract information such as status, phase, active, download file name, and other details. If the extract completed with a status of Normal, then a list of data objects for the extract is also displayed.
Extract Details
From the Extract Details page, you can:
View the number of master records for each extracted data object.
Note: Record count will be displayed for data objects wherever available.
View the filters set by clicking the Filter icon
Note: You cannot view filter values if the extracts were created from previous releases. However, the Filter icon will appear if the filter had been set.
View the log information generated during the extract process by clicking View Log
Download the extract
Transform, load, or report the extract by clicking appropriate buttons if it is an active or reportable extract
The Active column can have either one of the following indicators:
Legend | Description |
---|---|
This active extract can be used for the Transform, Load, or Report process. | |
This extract cannot be loaded or transformed but contains at least one data object which can be reported. | |
Blank (No Indicator) | This extract is considered to be inactive; therefore, it cannot be used for transform, load, or report. You can view its details or download it. |
From the Migrations tab > Extracts subtab, click Create to create an extract.
Alternatively, select a selection set from the Selection Sets window and click Extract. This populates details of the selection set for the Extract process.
Create Extract: Set Parameters
Following are the steps to create an extract:
Name: Enter a unique name for the extract. This is a required field.
Selection Set: Select the required selection set for extraction. To do this, click the search icon. This is a required field.
After a selection, the data objects for the chosen selection set are automatically displayed in a table format.
Source Instance: Select the source instance from the list. This is a required field.
Important: If the selected source instance is different from that used at the time of creating the selection set, then some of the filters, if set, may become invalid and may not return any data. By default, the source instance used during creation of the selection set is populated.
Note: If the language(s) selected in the profile AZ:Export Languages are not present in the source instance, you will get an error.
The list of languages specified for the extract must include either the target base language or the language 'US' because at least one of these is required to maintain multilingual tables in the target instance.
Description: Provide informative description that could help in identifying the extract.
Filter Set: Indicate if any filters are set to the data object.
Supports Update: Indicate if data object interface supports updating the existing record in the target.
Schedule: Create the extract as soon as possible or schedule it for a later date and time.
Create Extract: Schedule
Finish: Click Finish to submit the extract creation process.
This takes you to the Extract page with a confirmation.
You can upload an extract. The extract could be one that was stored in an external repository or downloaded from another instance. This extract must be an output of an extract process on an instance that was at the same patch set level as the current central instance.
Extracts: Upload
To upload an extract:
Extract Name: Enter the unique name of the extract to be uploaded.
File Name: Browse and select the extract file.
Description: Provide informative description about the extract being uploaded.
Upload: Click Upload to upload the file.
Uploaded extracts are validated for the current release. If the extract is active, then it can be used in the transform, load or report process.
You can submit an extract for transformation. For more information, see Transforms.
You can load an extract to a target instance. For more information, see Loads.
You can generate a report on an extract. For more information, see Reports.
You can delete an extract by clicking the Delete icon.
Note: Loads are dependent on respective extract records. Deleting an extract would result in failure to restart a load dependent on that extract.
You can download an extract by clicking the Download link on the search result page or download it from the Extract Details page.
Oracle iSetup allows you to transform extracted data in the following ways:
Individually transform certain attributes of the data objects in an extract
Simultaneously transform group of attributes for the data objects in an extract
Note: All data objects do not support transformation.
Transforms
Transforming data is a three step process.
Create: Submit the extract for transformation.
Update: Modify the attributes of the data objects.
Freeze: Freeze the transform to prevent any further changes before it is loaded. By freezing a transform, you ensure that it will not be changed while it is being loaded.
Important: Only frozen transforms can be loaded.
An active extract which has at least one data object supporting transformation. Refer to Selection Set Templates, to get more information on transformable data objects.
You can either do a Simple Search or an Advanced Search.
In Advanced Search, you can search on the following attributes of the transform, which are:
Name
Extract Name
Request ID
Phase
Status
Submitted On
In addition, you can have the search results match any or all the criteria you specify by selecting an appropriate option.
Advanced Search on Transforms
All transforms that match your search criteria are listed in a table format. Use the search results page to:
View a list of transforms with name, request ID, extracts, submitted date, phase, transform status, frozen, and active status
View the details of a transform by clicking the name link
Create a transform by clicking Create
Load an active and frozen transform by selecting the transform first and clicking Load
Delete a transform by clicking the Delete icon
Update a transform by the Update icon
The check in the Active column indicates that the transform is created from an extract of the current release.
To view transform details, click the desired transform name link on the Transforms search results page. This takes you to the Transform Details page which displays read-only information in the header and a hierarchical view of the data objects. This hierarchical view also displays the number of extracted records and transformed records for the corresponding data objects.
Transform Details
From the Transform Details page, you can perform the following tasks:
Load the transform if it is active and frozen
Update the transform if it is not frozen
View the transform log to see the log information generated during the transformation
View data object details by clicking the View icon of the data object you want to view
The object details page allows you to view a list of records that comprises the selected data object and the contents of each record with extracted value and transformed value. You can have an option to view all the data objects or only the transformed data objects.
To transform certain attributes of a data object in an extract, you can initiate the transform process in either of the following ways:
From the Migrations tab > Transforms subtab, click Create to create a transform
From the Migrations tab > Extracts subtab, select an extract and click Transform or click Transform on the Extract Details page
Following are the steps to create, update and freeze a transform:
Creating a Transform
Create Transform: Set Parameters
Following are the steps to create a transform:
Name: Enter a name for the transform. This is a required field.
Extract: Select the required extract. You can perform a search to locate available extracts. This is a required field. Only active extracts will be displayed for selection.
If this process is initiated from the Extracts page, details of the extract are populated.
Description: Provide informative description that could help in identifying the transform.
Click Next. This takes you to the Create Transform: Choose Objects window if the extract contained at least one transformable object.
Choose Objects: Select the objects on which you want to perform transformation.
Create Transform: Choose Objects
Schedule: Create the transform as soon as possible or schedule it for a later date and time.
Create Transform: Schedule
Updating a Transform
The Update icon is enabled for active transforms only. You can update the data within the transform by clicking the Update icon.
Following are the steps to update the transform:
Click the Update icon of the required transform. This takes you to the update page of that particular transform.
Update a Transform
Note: If the transform is frozen, you must unfreeze it for updates.
Click the Transform icon of the data object that you want to transform. Only data objects marked for transform, during creation, are enabled. This takes you to a detail page where you can:
Search for the record to be transformed by using some attributes of the data object.
Enter the new values for attributes that allow transformation against the existing values.
Access the Transform All page where you can mass modify a specific attribute of all the records for an data object in one step. See: Transforming All Attributes for a Data Object.
Save the transform by clicking Save and continue to transform other records. On Apply, the details page displays the transformed data objects and the number of records that have been transformed.
Details: Period Sets
Freezing a transform prevents any further changes to the transform. By doing this, you ensure that the selected transform will not be changed during the load process.
Freeze a Transform
Following are the steps to freeze a transform:
From the Migrations tab > Transforms subtab, click the Update icon of the transform. This takes you to the Update page of the transform.
Click Freeze.
Note: You can also unfreeze a frozen transform from the Update page. If a transform is unfrozen and changes are made to it, you cannot restart the dependent load which was submitted on the frozen transform.
Instead of transforming data object attributes row by row, you can use the Transform All feature to mass modify a specific attribute of all the records for a data object simultaneously. If the object to be transformed is a hierarchical selection set, then all values set at the parent level will be cascading down to the child data objects.
The Transform All window lists transformable attributes. You can enter values for these attributes of the selected data object and submit the request to transform them all at once. This immediately launches a concurrent request and it cannot be scheduled. While the Transform All request is in progress, the transform will be frozen. Once the request is completed, changes made with Transform All are not reversible. Transformed records will be equal to the total number of extracted records.
Note: The values you enter for transformable attributes cannot all be empty (null). You must enter at least one attribute in order to submit the Transform All request.
This feature can only be used in the update mode and the transform must be in unfrozen state.
Transform All Screen
Use the following steps to access the Transform All window:
From the Migration tab > Transform subtab, select a Transform from the transform search results region and click Update to access the Transform Details page.
Note: You can also click the transform name link from the search results region to get to the view only Transform Details page and then click Update to enable the Transform icon and Freeze button.
Click the Transform icon for a specific attribute in the Transform Details page to access the Attribute Details page.
Click Transform All to access the Transform All page. The selected attribute name displays as the region name.
Enter an appropriate value for this attribute and click Apply to submit the request. You are directed back to the Transform Search page with confirmation of the request submitted.
You can load a transform from either the Transforms page or the details page of a particular transform.
Note: Only a frozen and active transform can be loaded.
Click the Delete icon in the Transforms page to delete a transform. This takes you to the Warning page where you must confirm the delete action. Once you confirm the deletion, you return to the Transforms page.
Note: After deleting a transform, you cannot restart any dependent loads.
You can load an extract or a frozen transform to the target instance.
The database character set of the extract or the frozen transform must match the database character set of the target instance.
Note: If there is a mismatch of the data's language(s) in the extract (or transform) and the languages installed in the target, you will get an error message. For example, if the extract contains Japanese data from the source instance but the target instance does not have the Japanese language installed, you will get an error when you try to load the extract.
Note: After you load an extract, run adadmin:"Maintain multilingual tables" as a post-migration step.
Loads
You can either do a Simple Search or an Advanced Search.
In Advanced Search, you can search on the following attributes of load:
Name
Target Instance
Request ID
Phase
Status
Submitted On
In addition, you can have the search results match any or all the criteria you specify by selecting an appropriate option.
Advanced Search for Loads
All loads that match your search criteria are listed in a table format. Use the search results page to:
View a list of loads with name, request ID, data source, target instance, submitted date, phase, and status
The possible values in the Status field are Pending, Running, Warning, Normal, and Error.
View the details of a load by clicking the name link
Create a load by clicking Create
Delete a load by clicking the Delete icon
To view existing loads, click the desired name link on the search results page. This takes you to the Load Details page which displays read-only information for the load, such as load status, phase, and data source type, as well as a hierarchical view of the load status for each data object.
The hierarchical view lists the following information for each data object:
Extracted - denotes that the records for this data object has been extracted.
Status - Status of deployment for that particular entity.
Deployment report - whether deployment report has been selected for this data object
Loaded - number of records loaded successfully.
Partially Loaded - number of records loaded with business validation exception at current or child level.
Skipped - number of records skipped as a result of errors.
Total - total number of records.
Load Details
If the load is completed, you can see load details by clicking View Log. Click Refresh if the status for the load is scheduled.
The downloadable deployment record gets generated only if any of the data objects selected for Deployment report generation have errors or warning. However, the report always contains an index.html file providing detailed summary of the load status. It lists the number of records created, created with warnings, updated, updated with warnings and skipped due to errors. If there are warnings or errors, this document also has links to the deployment report of each data object.
The deployment report contains lists the following details for each record that was not loaded as a result of warning/exception.
ID/Parent ID: A separate Microsoft Excel file is generated for data object. The ID and PID are provided to match the records in the child data object file to their corresponding master records in the parent data object file. The child records in the child data object deployment report have a PID value equal to the ID of the parent data object record in the parent deployment report Microsoft Excel file.
Status: This column gives the reason why these records were loaded or not loaded with warnings. The reasons could be any of the following:
Created - One or more child records were skipped: The master record has been loaded. However, some of the child records were not inserted due to errors.
Updated - One or more child records were skipped: The master record has been updated. However, some of the child records were not inserted or updated due to errors.
Skipped - One or more records had errors: One or more records had errors and 'Commit if Warning' is set to False. Therefore, the current record was skipped without processing and the load resulted in an error.
Skipped - Business validation error: This record was not loaded due to a business validation error. This record was skipped but this error will not cause the load to error out.
Skipped - One or more child records had errors: One of its child records encountered a fatal exception and the load failed.
Skipped - Error: This record had a fatal exception and the load failed.
Skipped - 'Update Records' was not enabled: Exclusive Insert option was set to True by a user. A record was found in the target instance having the same primary keys as the record to be loaded, causing the insert to fail.
Log Message: Gives the actual exception encountered when loading this record.
Data Columns: The rest of the columns list the data in the record that was to be loaded.
From the Migrations tab > Loads subtab, click Create to create a load. You can also initiate this process by:
Clicking Load from the Extract Search page or the Extract Details page.
Clicking Load from the Load Details page.
Create Load: Set Parameters
Following are the steps to create a load:
Name: Enter a unique name for the load. This is a required field.
Data Source: Select a data source (an extract or a transform) from a search. Only active extracts and transforms will be displayed for selection. This is a required field.
Target Instance: Select the Target instance from the pick list.
Description: Provide informative description that could help in identifying the load.
Update Existing: Select the data objects that you want to overwrite during load. By default, this is checked for objects that support update.
Deployment Report: Select the data objects for which you want the Deployment report to be generated. The Deployment report is a downloadable Microsoft Excel sheet generated for each API that has warnings or errors. The status of the data objects in the report gives you information on why the warning or error occurred during the load process.
Sequence: If the extract being loaded was created from a custom Selection Set, the additional sequence column is also displayed. This allows you to modify the order of the data objects being loaded. By default, this column will be populated with the sequence specified by the user when the selection set was created. However, you can edit it to reflect the order in which you want the data objects to be loaded.
Create Load: Set Object Details
Schedule: Create a load as soon as possible or schedule it for a later date and time.
Create Load: Schedule
Note:
The load is only allowed on extracts that are active and in Completed phase with a status of Normal.
A transform has to be active and frozen, as well as in Completed phase with a status of Normal to initiate a load process.
Some of the data objects must be updated during load and the Update flag would be checked for these data objects by default.
Some data objects do not support updates.
You can restart the load process if the current process ends in error. However, if an extract or a transform associated with the load has been deleted or the associated extract becomes inactive in the process of upgrade from earlier releases, then the Restart option will not appear for that particular load even though the load process completes with a status of Error.
Following are the steps to restart a load:
From the Migrations tab > Loads subtab, click the load which resulted in an error.
This takes you to the details page of the load.
Click Restart.
This takes you to the Set Parameters page where you specify a Name and Description for the load.
Here you have two options:
To restart from the failed step: The application will start running from the API that resulted in an error. You must use this option if you have corrected the reason of the error in the target instance.
Skip failed step and its dependencies: The application skips the load for the data object that resulted in an error and its dependent data objects.
Click Continue.
This takes you to the Schedule page where you can schedule the load.
Click the Delete icon in the Loads page to delete a load. This takes you to the Warning page where you must confirm the delete action. Once you confirm the deletion, you return to the Loads page.