This blog post is a part of series with special consideration for high-level data migrations to Oracle Cloud ERP. Previously, we shared a blog post Oracle Cloud ERP Data Migration Recommendations and Best Practices focusing on the data migration across the Cloud ERP. The blog post and an associated whitepaper provided guidance on the approach to convert master and transactional data for business objects in Oracle Cloud Applications, across business functions in Financials, Procurement, Project Management, and related HCM business objects (Employees, Locations). It provided an overview of the methods and techniques to convert the intended business objects across the various Cloud ERP products.

In this post, we will discuss additional considerations for your high-volume data migrations, focusing on Project Management. There are multiple use cases and related configurations that contribute to the shape, scope, and performance of transaction imports. The considerations discussed in this post are intended to be generic and do not relate to a specific organization or industry.

For the purposes of this blog post, we included the following PPM conversion data objects:

  • Projects and tasks
  • Project financial plans and budgets
  • Cost transactions
  • Revenue and billing events
  • Project assets and assignments

Convert only “active” projects and tasks. However, also make sure to include any projects referenced by other data conversions like requisitions, POs, AP invoices, cost transactions, or programs.

For large conversion volumes, create .CSV files instead of using the FBDI templates for projects, tasks, budgets, or cost transactions. The CSV files do not have row limitations whereas MS Excel has row limits.

Instead of using a single large file for the conversion, use multiple smaller files via the Interface File for Import Process.

 

Project and Tasks Import

You can import projects, tasks, transaction controls, project team members, and other project details from third-party applications using the Projects Interface macro-enabled Excel workbook template and existing import processes.

To import the projects data:

  • Prepare your data in the Projects Interface macro-enabled Excel workbook template. Generate CSV File in the template to create a comma-separated values file of projects.
  • For large conversion of projects, tasks, create CSV files instead of using the FBDI templates.                      
  • Submit the Load Interface File for Import process to load the projects data from your CSV file into the applications related open interface table.
  • Submit the Import Projects process to bring the data into the PPM Cloud application from the open interface table.

Other considerations when importing the project data:

  • The import process only supports creation of new projects, their tasks, team members, transaction controls, and project classifications.
  • Updating attributes of the existing projects, adding transaction controls, team members, or project classifications for existing projects is not supported. If you need to do subsequent updates to converted data, then use REST APIs either programmatically or using an Excel-based tool like VBAFE.
  • The exception is the Import Project Tasks process, which creates project tasks in the existing projects. The process creates project tasks based on data you loaded into the open interface table.
  • Decide on what project status should be set for converted projects to allow for project costs to be migrated. For instance, if a converted project is migrated as Closed, and status action prevents new transactions, then you won’t be able to convert the costs. You will also need to reset the status of the converted projects (by REST API) once the related conversions have been completed.
  • You should enable project auto numbering if you require it for converted projects. You can’t apply automatic numbers to projects once they are already created in Cloud PPM. This is unlike project references or DFF attributes where you can modify values for existing projects via REST service.

The recommended approach is to import 10,000 to 15,000 projects with 5 parallel child jobs enabled.

For large number of projects and tasks, use the process parallelization. The parallelization and maximum child threads per job can be controlled by the “ORA_PJO_INTERFACE_OPTIONS“ lookup values:

  • ORA_PJO_JOB_MAX_RUNTIME_MINS – Parallel threads will be triggered if the estimation to complete import goes beyond this value.
  • Default Value: 60 mins. Valid values are 5 to 600 mins.
  • ORA_PJO_MAX_SUB_JOBS_LIMIT – Description: The max number of sub jobs allowed when an import process decides to trigger parallel jobs
  • Default Value: 3 and restricted to a max value of 10.

Interface table data is purged immediately after the reporting job completes. Reporting table records from previous jobs which are older than 24 hours are deleted in the current job. The import job consolidates the results and reports the summary and details in pdf format. Another report lists error records in Excel file format which can be used for creating a CSV file for the next upload with data corrections.

Project Financial Plans Import

Import project financial plans from 3rd party applications into the Fusion Project Control work area by using the Import Financial Project Plans FDBI template and running existing import processes.

To import the project financial plans:

  • Prepare your data in the Projects Interface macro-enabled Excel workbook template. Generate CSV File in the template to create a comma-separated values file of project financial plans.
  • For large conversion data files, create CSV files instead of using the FBDI templates.
  • Submit the Load Interface File for Import process to load the projects data from your CSV file into the applications related open interface table.
  • Submit the Import Project Financial Plans process to bring the data into the PPM Cloud application from the open interface table.

Project Budgets Import

Import project budgets from 3rd party applications into the Oracle Project Control work area by using the Import Financial Project Plans FDBI template and running existing import processes.

To import the project budgets:

  • Prepare your data in the Projects Interface macro-enabled Excel workbook template. Generate CSV File in the template to create a comma-separated values file of project budgets.
  • For large conversion data files, create CSV files instead of using the FBDI templates for project budgets.
  • Submit the Load Interface File for Import process to load the projects data from your CSV file into the applications related open interface table.
  • Submit the Import Project Budgets process to bring the data into the PPM Cloud application from the open interface table.

Project Financial Plans and Budgets Import – Other Considerations

  • The project budget data can be imported in two levels: Periodic and Non-Periodic
  • The recommended import volume is 100,000 periodic lines. The import process can handle up to 500,000 periodic lines and the import should take around 60 minutes using 3 parallel jobs.
  • Existing budget versions can’t be updated. Only new versions can be imported via file-based import process. Also, the data is picked as per the project names range given by you at the time of submission.
  • The data segregation is done at the project level while distributing the data between sub jobs.
  • Create budgets for award projects from the Awards Overview page to manage spending for expenditures associated with funded awards.

For large files, the parallelization can be controlled by the following PJO Lookup Type ORA_PJO_INTERFACE_OPTIONS:

  • ORA_PJO_JOB_MAX_RUNTIME_MINS – Parallel threads will be triggered if the estimation to complete import goes beyond this value. Default Value: 60 mins. Valid values are 5 to 600 mins.
  • ORA_PJO_MAX_SUB_JOBS_LIMIT – The max number of sub jobs allowed when an import process decides to trigger parallel jobs. Default Value: 3 and restricted to a max value of 10.

Project Budgets Import Reconciliation

The import summary of successful or failed versions will be available as a single PDF report, even though the data was processed using multiple child jobs. Also, the information is stored in FUN stats for monitoring the job performance.

Interface table data is purged immediately after the reporting job completes. Reporting table records from previous jobs which are older than 24 hours are deleted in the current job. For optimal performance, do not keep more than 300,000 rows in the interface table as full scans are executed on this table.

Project Costs Import

You can import transactions from 3rd party applications, validate them, and process them to create project costs and cost distributions. To import the project costs:

  • Prepare your data in the Projects Interface macro-enabled Excel workbook template. Generate CSV File in the template to create a comma-separated values file of cost transactions.
  • For large conversion data files, create CSV files instead of using the FBDI template for cost transactions.
  • Submit the Load Interface File for Import process to load the projects data from your CSV file into the applications related open interface table.
  • Submit the Import Costs process to bring the data into the PPM Cloud application from the open interface table.

For large volumes of conversion data (more than 500,000 records) follow the implementation guidance below:

  • Create csv files for all cost transactions to be loaded and upload using the Load Interface File for Import Process. Instead of using a single large file for the conversion, use multiple smaller files.
  • Setup Profile Option “PJC_IMPORT_PARALLEL_WORKERS” to value 10. In Setup and Maintenance > Manage Project Transaction Sources task, set the Batch Set Size limit to 25,000 for your conversion source(s).
  • If the transactions are migrated as ‘Accounted’, pass the Account CCID value as opposed to concatenated segments.
  • Use record IDs (project_id, task_id, expenditure_id) in your data files whenever possible
  • Submit the Import Costs Process with appropriate values for the mandatory parameters which match the data uploaded using Load Interface File for Import.

Once process is complete verify the output Excel report generated for any rejections recorded when processing the FBDI data, fix issues and reupload. PDF output file is generated with summary of rejections. Successfully processed data can be verified in Manage Projects Costs UI.

You can review the errors for pending and rejected transactions in the Manage Unprocessed Costs page or in the Import Costs Report. After fixing the errors, you can submit the transactions for reprocessing. Instead of reinitiating the process, the application reprocesses transactions from the point of error. For example, if a transaction is rejected while determining the cost rate, the application reprocesses the transaction from the cost rate determination stage and not earlier.

All successfully processed transactions are moved to PJC_EXP_ITEMS_ALL table, and all rejected transactions are handled as below

  • Stage table rejections are reported in Excel output and both user-provided data and related errors are purged at the end of processing. You have to re-upload the rejected transactions.
  • Interface Table rejections are reported in output and not purged. You can review them using UI/REST Service and correct the data.

Import Costs Output report job prepares two Files

  • Excel: reporting all rejected transactions in same FBDI file format with rejection info, so user can correct and re-upload easily if needed.
  • PDF: reports on summary and details (controlled by an Import Costs process parameter). The FBDI data rejections details are not reported in the PDF report.

Project Billing Events Import

Import billing events from 3rd party applications into the Invoices work area by using the Create Billing Events Excel workbook template and running the import processes. The Excel template is part of the External Data Integration Services for Oracle Cloud feature. To import the billing events:

  • Prepare your data in the Projects Interface macro-enabled Excel workbook template. Generate CSV File in the template to create a comma-separated values file with project billing events.
  • For large conversion data files, create CSV files instead of using the FBDI template for project billing events.
  • Submit the Load Interface File for Import process to load the projects data from your CSV file into the applications related open interface table.
  • Submit the Import Billing Events process to bring the data into the PPM Cloud application from the open interface table.

For a large data imports, the Import Project Billing Events parallelization is controlled internally based on volume and number of contracts.

  • Up to 10,000 billing events, irrespective of number of contracts, is processed using a single thread.
  • If the number of events is more than 10,000 and spans more than one contract, then an additional parallelization thread is used. Maximum 2 threads are used.
  • If you do not wish to parallelize the import billing events job, you can configure to always have 1 thread.
  • Interface table data is purged by the report job spawned to the end of the import job.

Project Billing Events Import – Reconciliation

The import job consolidates the work done and reports the details in pdf format. Below mentioned details are displayed in the process summary section of the pdf report:

  • Number of Billing Event Creations Attempted
  • Number of Errors
  • Number of Billing Events created

Also, the output report lists the billing event records that were rejected during processing along with the appropriate error messages.

If any records were rejected during the upload, another report in Excel format is generated. This report lists the rejected records and is formatted to create a CSV file for the next upload of corrections. For more information on project billing data, see the MOS Doc ID 2410542.1.

Import Project Asset and Assignments

Import assets and assignments from 3rd party applications into the Invoices work area by using the Create Asset and Assignments Excel workbook template and running the import processes. The Excel template is part of the

External Data Integration Services for Oracle Cloud feature. To import the project assets and asset assignments:

  • Prepare your data in the Projects Interface macro-enabled Excel workbook template. Generate CSV File in the template to create a comma-separated values file with project asset and assignments.
  • For large conversion data files, create CSV files instead of using the FBDI template for project asset and asset assignments.
  • Submit the Load Interface File for Import process to load the projects data from your CSV file into the applications related open interface table.
  • Submit the Import Asset and Assignments process to bring the data into the PPM Cloud application from the open interface table.

For a large data imports, the Import Project Asset and Assignments parallelization is controlled internally based on volume. If you do not wish to parallelize the import asset and assignments job, you can configure to always have 1 thread. Interface table data is purged by the report job spawned to the end of the import job.

Conclusion

Carefully plan and test your project data migrations. If you are converting high data volumes, we recommend that you review and follow our recommendations above.