6Manage Setup Using CSV File Packages

This chapter contains the following:

Export and Import of CSV File Packages

When you enter or update a large volume of setup data, using the setup task UI to mange them individually may not be the most efficient method.

To create or update setup data in bulk, use the export and import of CSV file packages. Use CSV file packages to manage setup data of a specific task or a group of tasks associated with a functional area or offering.

To export and import CSV file packages, you must have the Application Implementation Consultant role. You also require additional roles as specified in the Additional Access Required for Exporting and Importing Setup Data topic if you're exporting tasks related to specific business objects.

Alternatively, you can export and import CSV file packages if you have these privileges:

  • Submit Export Setup Data Process (ASM_SUBMIT_EXPORT_SETUP_DATA_PROCESS_PRIV)

  • Submit Import Setup Data Process (ASM_SUBMIT_IMPORT_SETUP_DATA_PROCESS_PRIV)

  • Task-specific privileges

For detailed information about task-specific privileges, refer to the Oracle Applications Cloud Security Reference for Common Features guide.

Export Setup Data to a CSV File

To export setup data to a CSV file, follow these steps:

  1. Click Navigator > Setup and Maintenance.

  2. On the Setup page, select the offering whose task requires setup data entry using CSV file.

  3. Identify the setup data as appropriate:

    • For all tasks associated with the offering, click Actions > Export to CSV File.

    • For all tasks associated with a functional area, select the functional area and click Actions > Export to CSV File.

    • For specific tasks:

      1. Select the appropriate functional area and go to the Tasks table.

      2. Select Columns > View > Actions to make the applicable task actions visible.

      3. Select the task, and from the corresponding Actions menu, select Export to CSV File.

        Note: The Export to CSV file option is disabled if the task doesn't support export.
  4. Select Create New. You can export all existing setup data represented by the task.

    • If Scope is supported for any of the selected setup tasks, you can select appropriate scope values to filter the setup data you want to export.

    • You can also download the template for managing setup data without exporting the existing setup data in the environment. To do so, select the Export only template files check box.

  5. Click Submit to start the export process.

  6. Select Export to CSV file for the offering, or functional area, or task as appropriate, to see the latest process. Monitor the process at any level until it completes.

  7. Select the process after it's complete and review the results. Verify that the process completed without any error before you download the CSV file package.

    Note: If there are more than one business objects associated with the task, you may find more than one CSV files.

Modify Setup Data in a CSV File

To enter or modify setup data before importing it into the target environment, you need to edit the CSV file. While editing, ensure that you maintain the exact format for dates, numbers, and any other value that's present in the original exported CSV file.

If your file contains setup data with Date attributes, you must specify the correct date format that appears in the exported setup data file.

  1. Open the original exported file using a CSV editor such as WordPad and review the date format attribute (for example, 2015/10/25).

  2. Open the CSV file using Microsoft Excel.

  3. Select the Date attribute and click Format Cells. Change the Category to Custom and provide the same format that appeared in the original file. In the same way, provide the format for all date columns.

    Note: Depending on the setup data, you must use the custom format to match the correct data format in columns as suggested in the following table.
    Columns Data in CSV Custom Format Cells

    Date

    2018/03/02

    yyyy/mm/dd

    Date and Time

    2018/02/28 12:07:48:48

    yyyy/mm/dd hh:mm:ss.SSS

    Long Number

    123456789123

    Custom > 0

  4. Modify the remaining setup data as per your requirement.

    Tip: If you require further information to understand how to fill up the CSV file, refer to the Help associated with the task.
  5. Save the file in CSV format and immediately close it to prevent any other change. Ensure that the CSV file size doesn't exceed 10MB. The CSV file is ready for import.

If your file contains setup data with special characters for other languages such as Chinese, you might need to specify Text as the column format to represent such information.

  1. Open Microsoft Excel and click the Data tab.

  2. In the Get External Data group, click From Text.

  3. Navigate to and select the CSV file you downloaded. .

  4. Select Delimited as Data Type and click Next.

  5. Select Comma as delimiter and click Next.

  6. Select Text as column data format for all columns and click Finish. You can now edit the file.

  7. Save the file as file type Unicode Text (.txt) and close it.

  8. Open the file again using Notepad++:

    1. Find and replace all tab characters with commas.

    2. Change the encoding to Encode in UTF-8.

  9. Save and close the file.

  10. Change the file extension from .txt to .csv.

  11. Save the CSV into a .zip file. Ensure that the CSV file size doesn't exceed 10MB. You can now import the file.

Import Setup Data from a CSV File

To upload the new or modified setup data, follow these steps:

  1. Select Import from CSV file from the Actions menu of an offering, a functional area, or a task as appropriate.

  2. Select Create New.

  3. Select the appropriate .zip file. The application validates that the file corresponds to the selected offering, functional area, or task before you can submit the process.

  4. Click Submit.

  5. Select Import from CSV file to see the latest processes. Monitor the process until it completes.

  6. Select the process once it completes to review the results. If any error occurs, review the details listed in Status Details in the Business Objects section. Fix the issues and import again, if necessary.

  7. After you import the setup data successfully, you can verify the loaded data by selecting the task from the Setup page.

Import Offering Setup Data from a CSV File

This procedure shows you how to import offering setup data from a CSV file. You must have the Application Implementation Consultant (ORA_ASM_APPLICATION_IMPLEMENTATION_CONSULTANT_JOB) role to import setup data.

To import offering setup data from a CSV file, follow these steps.

  1. In the Setup and Maintenance work area, select the offering into which you want to import the setup data.

  2. Click Actions and select Import from CSV File > Create New to begin the import process.

  3. On the Import Setup Data from CSV File page, click Browse.

  4. Select the CSV file package containing the exported setup data and click Open. The details of the package appear.

  5. Verify the details to make sure you selected the correct CSV file. If the details appear to be from another CSV file, click Update and select the correct file.

  6. Click Submit. The import process begins.

  7. To review the import summary after the process completes, go to Actions > Import from CSV File and click the process that you submitted. The Import Setup Data from CSV File Results page appears with details.

  8. In the Post-Import Tasks section, check and complete if any task is listed.

    Caution: If post-import tasks are listed and you don't complete them, the imported data won't be ready for use.
  9. In the Business Objects section, review the status of the imported business objects and take suggested action if any errors are reported.

Compare Task Setup Data

This procedure shows you how to compare exported setup data of a task before you import it. For example, you have made some changes to the setup data in a test environment and exported it. Now, you want to import the same data into a production environment. But before you import, you can compare the exported data of the test environment with the existing production data. You can preview how the setup data in production will change after the import.

To compare setup data, you must have the Application Implementation Consultant role (ORA_ASM_APPLICATION_IMPLEMENTATION_CONSULTANT_JOB).

To compare task setup data, follow these steps.

  1. In the production environment, go to the Setup and Maintenance work area.

  2. In your offering, navigate to the setup task for which you want to compare the setup data.

    Tip: If you can't see your task, click Show > All Tasks. If you don't see the Actions column, click View > Columns > Actions to enable it.
  3. Select the task and click Actions > Compare Setup Data > Create New. The compare setup data process begins.

  4. On the Compare Setup Data page, click CSV File Package and select Upload New.

  5. On the Upload Setup Data page, select the CSV file package containing the exported task setup data.

  6. In the CSV File Package Details section, verify the details to be sure of selecting the correct exported file.

  7. Click Submit. The comparison process starts.

  8. To review the comparison summary after the process completes, go to Actions > Compare Setup Data and click the process that you submitted. The Comparison Process Results page appears with details.

  9. Review the details in each section. The details from the CSV file package and the production environment are separately displayed so that you can compare the results.

  10. Examine the contents and check for discrepancies if any.

    Note: Configuration 1 refers to the contents of the CSV file package, and Configuration 2 refers to the data in the production environment.
  11. Click the value in the relevant column. A spreadsheet appears listing the discrepant details.

  12. Scroll though the listed entries to examine the details and manually fix them as appropriate.

    Note: Importing the CSV file doesn't update or delete the values in the production environment. Therefore, if you want any change in the production data, you must manually update or delete the values.
  13. Once you're satisfied with comparison results and have made the necessary changes in the CSV file package and the production environment, you can import the CSV file package.

Automate Export and Import of CSV File Packages

You can use the SOAP web services or the REST APIs if you want to export and import CSV file packages outside of the Setup and Maintenance work area.

To submit export and import processes using web services or APIs, you need the Application Implementation Consultant role. Even if you don't have the Application Implementation Consultant role, you can submit the processes if you have these privileges:

  • Submit Export Setup Data Process privilege

  • Submit Import Setup Data Process privilege

  • Task-specific privileges

Refer to the Additional Access Required for Exporting and Importing Setup Data topic to know if you need access to any additional roles. For detailed information about task-specific privileges, refer to the Oracle Applications Cloud Security Reference for Common Features guide.

The following graphic represents a high-level overview of the process to be followed for automating the export and import of CSV file packages.

CSV export and import process

SOAP Web Services and REST APIs

Refer to the following documents for detailed information about using web services and REST APIs to export and import CSV file packages.

  • In the SOAP Web Services for Common Features guide, go to Business Object Services > Setup Data Export and Import.

  • In the REST API for Common Features in Oracle Applications Cloud guide, go to the Tasks section and refer to these topics:

    • Setup Offering CSV Exports

    • Setup Offering CSV Imports

    • Setup Offering CSV Template Exports

    • Setup Task CSV Exports

    • Setup Task CSV Imports

    • Setup Task CSV Template Exports

Offering, Functional Area, or Task Code

To initiate the export or import process using an API, you must provide the code of the offering, functional area, or task. You must also have the Application Implementation Consultant role.

To find the relevant code, follow these steps:

  1. Click Navigator > Setup and Maintenance.

  2. In the Setup and Maintenance work area, click Manage Setup Content from the Tasks panel tab.

  3. Follow these instructions to obtain the required code:

    To search for the code of an offering or a functional area To search for the code of a task
    1. On the Manage Setup Content page, click Manage Offerings and Functional Area.

    2. In the Search field, select Offerings or Functional Area depending upon the code you're searching for, and click Search.

    3. In the search results, click the name of the offering or the functional area.

    4. On the Enter Basic Information page, look for value that appears against Code.

    5. Copy the code and use it where necessary.

    1. On the Manage Setup Content page, click Manage Task Lists and Tasks.

    2. In the Search field, select Tasks and click Search.

    3. In the search results, click the name of the task.

    4. On the Task page, look for value that appears against Code.

    5. Copy the code and use it where necessary.

Apply Scope to Filter Exported Setup Data

When you export setup data using web services or APIs, you can optionally apply scope to filter the data. To apply scope, you must provide values to the following parameter attributes.

  • BusinessObjectCode - Code of the business object whose attribute is used as the scope

  • AttributeName - Name of the attribute of the business object used as the scope

  • AttributeValue - Value of the named attribute that defines the filter criteria

Follow these steps to find values for required parameters.

  1. Follow the procedure in the Export and Import CSV File Packages topic and select these options.

    • The same offering, functional area or task for which you want to automate the export process.

    • The same scope-enabled business objects and their values to be used as the filter criteria in the automation process. Also note the names of the selected business objects.

  2. Complete the export and save the downloaded CSV file package. The attribute values from the downloaded files are used in the filter criteria when running the APIs.

  3. Complete these steps to find the code of the business object whose attribute is used as scope.

    1. In the Setup and Maintenance work area, select Manage Setup Content from the Tasks panel tab.

    2. On the Manage Setup Content page, click Manage Business Objects.

    3. Search for the business object that you identified in step 1 to set the scope and click that business object from the search results.

    4. Note down the code of the business object so that you can use it as the value for the BusinessObjectCode parameter in the API.

  4. Find the list of attributes that you want to use as filters and note down their values.

    1. From the CSV file packages exported and saved as instructed in step 2, find and open the file that has the same name as the business object used as scope.

    2. Note the column names and their values you want to use as filters. Use the column names as values for the AttributeName parameter and the column values as values for the AttributeValue parameter in the API.

Modify Setup Data in a CSV File

To enter or modify the setup data in the CSV file, follow these steps:

  1. Extract files from the downloaded CSV file package to a local folder..

  2. Open Microsoft Excel and click the Data tab.

  3. In the Get External Data group, click From Text.

  4. Navigate to and select the CSV file you downloaded.

  5. Select Delimited as Data Type and click Next.

  6. Select Comma as delimiter and click Next.

  7. Select Text as column data format for all columns and click Finish. You can now edit the file.

  8. Make the necessary changes and save the CSV into a .zip file. Make sure that the CSV file size doesn't exceed 10MB. You can now import the file.