Introduction
Automate your HCM Data Loader (HDL) and HCM Spreadsheet Data Loader (HSDL) integrations using the dataLoadDataSets REST API.
Objectives
In this tutorial, you will:
- Understand how to upload files to the Oracle WebCenter Content server.
- Initiate HCM Data Loader and HCM Spreadsheet Data Loader.
- Monitor data set and business object status, retrieve messages and process information.
Prerequisites
To complete the steps in this tutorial, you will need:
- Access to the use the HDL REST API.
- To complete the steps for initiating HSDL via REST, you'll need to assign your role to the ORA_PER_JOB HSDL template, or the template you'll be initiating HSDL for.
- The ability to encode files with Base 64 encoding.
- Postman to submit the REST API calls.
- If you want the ability to generate failed line files using REST, you'll also need to enable Oracle WebCenter content access to error files.
- From Setup and Maintenance, find the HCM Data Loader functional area and click Configure HCM Data Loader.
- Search for the Enable Oracle WebCenter Content Access to Error Files parameter and set to the override to anything other than No.
- Save and close.
Follow the REST Access steps in the Configure HCM Data Loader (HDL) Access and Understand HDL Security Options tutorial.
Follow the Assign a Role's Data Set Access steps in the Grant Access to Run HCM Spreadsheet Data Loader tutorial.
The examples in this tutorial use Postman variables to reference the environment and other values that change.
Variable | Description |
---|---|
{{env}} | The environment path. |
{{user}} | The username. |
{{RequestId}} | The value used to uniquely identify a data set. |
{{DataSetBusObjId}} | The value used to uniquely identify a business object record within a data set. |
{{ProcessId}} | The value used to uniquely identify a process submitted for a data set. |
Note:
Other values you'll need to change in the body of your REST calls are displayed in blue. For example, <file_name>.Task 1: Upload Your Data File to the Oracle WebCenter Content Server
There are multiple ways of uploading files to the Oracle WebCenter Content server, this task provides two different REST options: the uploadFile custom action available on the HCM Data Loader dataLoadDataSets resource, and the Oracle WebCenter REST resource.
If you want to complete the steps that follow you can download and use these files:
- New Grades and Jobs.zip for HCM Data Loader.
- Jobs.csv for HCM Spreadsheet Data Loader.
HCM Data Loader uploadFile Action
- Open Postman and create a new POST request for this URL:
- Specify the following JSON Body:
- Configure the Authorization for the request. The user must have the Upload data for Human Capital Management file based import role hierarchy.
- Set the Content-Type header to the value:
application/vnd.oracle.adf.action+json
- Click Send.
- Review the response and make note of the Content ID. It's needed to identify the file when initiating HDL and HSDL.
- Base64 encode the Jobs.csv file and use these steps to upload the csv file.
https://{{env}}/hcmRestApi/resources/11.13.18.05/dataLoadDataSets/action/uploadFile
{ "content" : "UEsDBBQAAAAIAJF+Qle0P8ZqHgEAAAQFAAAJAAAAR3JhZGUuZGF0ldPNaoQwEAfw+4LvsG+wmhhKDz2ERGyg2S269LqkGovQKqRxT3n4xu6Hxj3IQECZTH4h/BmZHSmnR+pyo2rtsqbRlW3PurTKWK7srJR19Vi4dLLed5fa+u+1slc/2tHbYTv8urIfTKUL3cRx8vLfsx3bo43Mijy73pg8k2QXj8ulTwna+YUT98rfTnnBT9KX2UHKw95J1akvbbaJo/NtmIaWGgo0BNPwUsOBhmFautTSQEthGllqJNBItIF4gt1zEF3dntt6UN8+zc6a9nOw/SWWqRsUi2BoBUcBDkpJMLyC4wAHhSZYuoKnAQ7KUDCygpMAB0bKp9HirdHVMkQOmy0eowcOzTnYcPEYB9w9qGkf9tyP95vn/+isHG3+AFBLAwQUAAAACAAbSlJXX/ch5FMCAAC9CgAABwAAAEpvYi5kYXTNldFumzAYhe8r9R14g5Fut7uwwElYY0LBq7SryCNOZI1AZUy1SH74GQdDnJCY9mpSpIjkP+ezzzGAIAYhwED+qH5LuNvRXLB3mgnCRUgElRkVQbWl7d/6OyYHfTEnB1Yc9U/ASERTy3lTFIkSY6bmUrpvCsIxPbxVnPCjFjalmq9KLUWkJHvKV/SdFjKrGp7TlO58f/ZdTXrtyOMDgukC6uU9+b7/xZ+pjwzWCK1juQxXmwysYLYJ1nHWXnoZKWitpGXdFIKUwhuGJJDzn6vVBkcIylTKC/kEVJxe4WjJKn5FdUEto/8ZrGfRIj3LtivNJmGFQCAGCzmzhVMZYXTOCBlXJ7HiN7dzYj3ZBm4WSp/xkCEi/A8VrNxfBohA+gxxFC9GQ+xN3Lw2b5vZ9fZJtOXnxutR096APG/wFu+8TGMzEWiqHIBWnfeJT5aPm5ik61CtDcVYMxNebZtcdFs80C5VM4VHUx083Ly2gQtmV+mn0LadGz/M9r1ec62Cb/DhwqrY9v3QOkzdI+uwb+P27F7ncF677epexWuiya8spwpPa7Y1qd+Ffe3EbgDsCPAvzZv2Nfdh1jfj44a9BBsMMwxPcb40pGDi2D4fBK8KD9NaqEpHT1GvnAQxB+eSYE6NBAG++Sw46d2cCG9AiCJ94UXYA9sDK1ktOGnPw+gujMRtvlRvsQQG2nuZetkbzZnaSy3GjbvxSb7DopXxhEUbySRzc6so6/7WuOU66QZIwK8hiYQcVY2FMw4jmmY/BGL8J6TSC6cx+kdIR7gfTqd4fPgHUEsBAhQAFAAAAAgAkX5CV7Q/xmoeAQAABAUAAAkAJAAAAAAAAAAgAAAAAAAAAEdyYWRlLmRhdAoAIAAAAAAAAQAYANn4TRNA9dkBAAAAAAAAAAAAAAAAAAAAAFBLAQIUABQAAAAIABtKUldf9yHkUwIAAL0KAAAHACQAAAAAAAEAIAAAAEUBAABKb2IuZGF0CgAgAAAAAAABABgAjuYejz/12QEAAAAAAAAAAAAAAAAAAAAAUEsFBgAAAAACAAIAtAAAAL0DAAAAAA==", "fileName" : "Grades and Jobs.zip" }
Note:
The value of the "content" is the Base64 encoded version of the New Grades and Jobs.zip file.The content and fileName parameters are required. The following parameters are available for the uploadFile custom action:
Parameter | Description |
---|---|
content | Base64 encoded file to be loaded. |
fileName | Name of the file. |
contentId | Unique identifier of the file on the Oracle WebCenter content server. Automatically generated, when not supplied. |
The response will have this format:
{ "result": { "Status": "SUCCESS", "ContentId": "UCMFA00078879" } }
Oracle WebCenter REST
The advantage of the Oracle WebCenter REST resource is that you don't have to first encode your file and therefore this REST service will be able to load larger source files. However, it's a bit more complex to use.
The HCM Data Loader uploadFile action will only upload files to the HCM Data Loader import account on the Oracle WebCenter. This API will allow you to upload to any Oracle WebCenter account your user has access to.
Create the Request
- Open Postman and create a new POST request for this URL:
- Specify the following form-data in the Body:
- Hover over the primaryFile key to expose the Text/File choice list and set the value to File.
- Configure the Authorization for the request. The user must have the Upload data for Human Capital Management file based import role hierarchy.
- Save the request. You can now upload files.
https://{{env}}/documents/files/data
Key | Value |
---|---|
metadataValues | { "dDocAuthor":"{{user}}", "dDocTitle":"<file_name>", "dSecurityGroup":"FAFusionImportExport", "dDocAccount":"hcm$/dataloader$/import$", "dDocType":"Document" } Note: Replace <file_name> with your file name. |
primaryFile |
Upload Files
In this step you'll use the request created above to upload zip and csv files to the Oracle WebCenter Content server.
- In the Body form-data of the request, click Select Files
Tip:
The Select File button is only available when the primaryFile choice list is set to File. - Use the File Browser to find and select your file.
- Edit the metadataValues key dDocTitle value to specify the name for your file.
Tip:
When you initiate HCM Data Loader the data set name is defaulted to the value you supply here. - Click Send.
- Review the response and make note of the Content ID. It's needed to identify the file when initiating HDL and HSDL.
- Repeat the Upload File steps for the csv file.
The response will have this format:
{ "dWebExtension": "zip", "dRevClassID": "213206", "createdBy": "VISION_INTEGRATION", "modifiedBy": "VISION_INTEGRATION", "XFND_RANDOM": "4641327459804635554", "dReleaseState": "N", "dPublishState": "", "dID": "213551", "xComments": "", "errorCode": "0", "title": "Grades and Jobs.zip", "size": "1125", "createdTime": "10/3/23 7:27 AM", "dDocName": "UCMFA00213206", "mimeType": "application/zip", "dRevRank": "0", "dDocID": "426034", "name": "New Grades and Jobs.zip", "dDocAccount": "hcm$/dataloader$/import$", "ownedBy": "VISION_INTEGRATION", "xStorageRule": "FusionStorageRule", "dStatus": "DONE", "modifiedTime": "10/3/23 7:27 AM", "XFND_SIGNATURE": "Y-Qs7rQwyYANlmfo-....", "errorKey": "!csServiceStatusMessage_checkin,UCMFA00213206", "dExtension": "zip", "dWorkflowState": "", "XFND_SCHEME_ID": "1", "XFND_CERT_FP": "901B32887DDC81F780757624", "dPublishType": "", "dUser": "VISION_INTEGRATION", "dSecurityGroup": "FAFusionImportExport", "errorMessage": "Successfully checked in content item 'UCMFA00213206'.", "dRevisionID": "1", "version": "1" }
Note:
The Oracle WebCenter Content REST API uses the dDocName label for returning the Content ID.Task 2: Initiate Bulk Data Loading
Upload Data with HCM Data Loader
In this step you'll call the createFileDataSet custom action to initiate HDL to process a file previously loaded to the hcm$/dataloader$/import$ account on the Oracle WebCenter Content server.
- In Postman create a new POST request for this URL:
- Specify the following JSON Body:
- Configure the Authorization for the request. The user must have the Use REST Service - Data Load Data Sets role hierarchy.
- Set the Content-Type header to the value:
application/vnd.oracle.adf.action+json
- Click Send.
https://{{env}}/hcmRestApi/resources/11.13.18.05/dataLoadDataSets/action/createFileDataSet
{ "contentId" : "<content_id>" }
Tip:
Replace the <content_id> value with the Content ID of your file.The only required parameter is contentId. The following parameters are available for the createFileDataSet custom action:
Parameter | Description | Configure HCM Data Loader parameter that provides the default value |
---|---|---|
contentId | Unique identifier of the file on the Oracle WebCenter content server. | |
fileAction | Valid values are IMPORT_AND_LOAD and IMPORT_ONLY. | File Action |
dataSetName | Name of the data set. Defaults to the document title of the file. | |
importConcurrentThreads | Maximum number of concurrent threads to assign to the import process. | Maximum Concurrent Threads for Import |
loadConcurrentThreads | Maximum number of concurrent threads to assign to the load process. | Maximum Concurrent Threads for Load |
importMaxErrorPercentage | Maximum percentage of records in error before the import ceases. | Maximum Percentage of Import Errors |
loadMaxErrorPercentage | Maximum percentage of records in error before the load ceases. | Maximum Percentage of Load Errors |
fileEncryption | Encryption used for the source file.
Supply NONE, PGPUNSIGNED or PGPSIGNED. Tip: You're always recommended to encrypt your files before uploading them to the Oracle WebCenter. The HCM Data Loader import account is often accessed by various users, all of which can download and review the content of your unencrypted files. |
File Encryption |
verificationKey | The verification key used for the source file encryption. Supply when fileEncryption is PGPSIGNED. | |
deleteSourceFile | Indicates if the source file should be deleted after the data is imported into the staging tables. | Delete Source File |
Note:
The last column in the table specifies the parameter name in the Configure HCM Data Loader task that defines the default for each REST API parameter.The response will have this format:
{ "result": { "Status": "SUCCESS", "RequestId": "107468" } }
Note:
The RequestId from the response is used to retrieve data set information using the dataLoadDataSets resource.Upload Data with HCM Spreadsheet Data Loader
In this step you'll call the createSpreadsheetDataSet custom action to initiate HSDL to process a file previously loaded to the hcm$/dataloader$/import$ account on the Oracle WebCenter Content server.
The file must be shaped for the HSDL template you're using to upload the file with.
- In Postman create a new POST request for this URL:
- Specify the following JSON Body:
- Configure the Authorization for the request. The user must have the Use REST Service - Data Load Data Sets role hierarchy.
- Set the Content-Type header to the value:
application/vnd.oracle.adf.action+json
- Click Send. The response will have this format:
https://{{env}}/hcmRestApi/resources/11.13.18.05/dataLoadDataSets/action/createSpreadsheetDataSet
{ "contentId" : "<content_id>", "templateCode" : "ORA_PER_JOB" }
Tip:
Replace the <content_id> value with the Content ID of your file.Tip:
The ORA_PER_JOB template is a preconfigured template available for loading jobs. You can find template codes using the Run Spreadsheet Data Loader task.The only required parameters are contentId and templateCode. The following parameters are available for the createSpreadsheetDataSet custom action:
Parameter | Description | Configure HCM Data Loader parameter that provides the default value |
---|---|---|
contentId | Unique identifier of the file on the Oracle WebCenter content server. | |
templateCode | Spreadsheet template code to upload the file with. | |
fileAction | Valid values are IMPORT_AND_LOAD and IMPORT_ONLY.
Tip: If you only import the file, you can't monitor or load it with REST, instead generate a spreadsheet for the template, fetch the data set and upload it from the spreadsheet. |
"IMPORT_AND_LOAD" |
dataSetName | Name of the data set. | Defaults to the template code and timestamp. |
importConcurrentThreads | Maximum number of concurrent threads to assign to the import process. | Maximum Concurrent Threads for Import |
loadConcurrentThreads | Maximum number of concurrent threads to assign to the load process. | Maximum Concurrent Threads for Load |
importMaxErrorPercentage | Maximum percentage of records in error before the import ceases. | Maximum Percentage of Import Errors |
loadMaxErrorPercentage | Maximum percentage of records in error before the load ceases. | Maximum Percentage of Load Errors |
fileEncryption | Encryption used for the source file.
Supply NONE, PGPUNSIGNED or PGPSIGNED. Tip: You're always recommended to encrypt your files before uploading them to the Oracle WebCenter. The HCM Data Loader import account is often accessed by various users, all of which can download and review the content of your unencrypted files. |
File Encryption |
verificationKey | The verification key used for the source file encryption. Supply when fileEncryption is PGPSIGNED. | |
deleteSourceFile | Indicates if the source file should be deleted after the data is imported into the staging tables. | Delete Source File |
headerIncludedFlag | Indicates if a header is included in the source file to name the attributes included in the file. | "Y" |
attributeDelimiter | Characters used to separate values in the file. | "," (comma). |
newLineIndicator | Characters used to indicate a new line. | "n", prefixed with the escape character. |
escapeIndicator | Characters used to escape the delimiter characters within an attribute value. | "/" (backslash) |
dateFormat | Date format used for attributes with the date data type | The default format is YYYY/MM/DD. |
{ "result": { "Status": "SUCCESS", "RequestId": "107483", "UserInfo": "", "Review": "https://{env}/hcmUI/oracle/apps/hcm/enterpriseSetup/hdlSpreadsheetLoader/di/GenericHdlSpreadsheet.xlsx?layoutCode=ORA_PER_JOB&dataSetName="Job#2024-08-06 11:40:56"", "FileAction": "IMPORT_AND_LOAD", "DataSetName": ""Job#2024-08-06 11:40:56"" } }
Note:
Use the RequestId from the response to monitor the status of the data set using the dataLoadDataSets resource.Tip:
Use the Review link to generate a spreadsheet from the HSDL template to review the data set.Task 3: Monitor Data Set Status
In this step you'll monitor data set status using the dataLoadDataSets resource. This resource can be used to monitor both HDL and HSDL data sets.
Tip:
HSDL files that are only imported don't create a data set in the HCM Data Loader staging tables and can't be monitored using REST.Monitor a Specific Data Set
- In Postman create a new GET request for this URL:
- Configure the Authorization for the request and click Send.
- Repeat for the csv file, the response will return information for the Spreadsheet related elements.
https://{{env}}/hcmRestApi/resources/11.13.18.05/dataLoadDataSets/{{RequestId}}
Note:
Replace {{RequestId}} with the RequestId value returned when you submitted your file.The response will have this format:
{ "RequestId": 107468, "DataSetId": 300100621962415, "ContentId": "UCMFA00103266", "DataSetName": "Grades and Jobs.zip", "DataSetStatusCode": "ORA_IN_ERROR", "DataSetStatusMeaning": "Error", "TransferStatusCode": "SUCCESS", "TransferStatusMeaning": "Success", "ImportStatusCode": "SUCCESS", "ImportStatusMeaning": "Success", "LoadStatusCode": "ERROR", "LoadStatusMeaning": "Error", "SourceTypeCode": "ZIP_DAT_FILE", "SourceTypeMeaning": "Compressed DAT file", "IntegrationTypeCode": null, "IntegrationTypeMeaning": null, "ImportPercentageComplete": 100, "LoadPercentageComplete": 100, "ImportSuccessPercentage": 100, "LoadSuccessPercentage": 59, "FileLineTotalCount": 38, "FileLineImportErrorCount": 0, "FileLineImportSuccessCount": 38, "ObjectTotalCount": 37, "ObjectLoadErrorCount": 15, "ObjectRollbackErrorCount": 0, "ObjectSuccessCount": 22, "ObjectUnprocessedCount": 0, "SpreadsheetTemplateCode": null, "SpreadsheetTemplateName": null, "SpreadsheetTemplateUserTypeCode": null, "SpreadsheetTemplateUserTypeMeaning": null, "SpreadsheetMessage": null, "FileSize": 1175, "CreatedBy": "HCM_USER10", "CreationDate": "2024-08-06T11:38:28+00:00", "LastUpdatedBy": "HCM_USER10", "LastUpdateDate": "2024-08-06T11:40:04.127+00:00", "ProtectedFlag": false, "Review": null, "FailedLinesFileContentId": null, "FailedLinesFileEncryptionType": null, "FailedLinesFileEncryptionKey": null, "links": [ ... ] }
Identify Data Sets in Error
Use this URL to retrieve summary information for all data sets in error:
https://{{env}}/hcmRestApi/resources/11.13.18.05/dataLoadDataSets?q=DataSetStatusCode=ORA_IN_ERROR&fields=RequestId,DataSetName,DataSetStatusCode,ImportStatusCode,LoadStatusCode,SourceTypeCode,FileLineTotalCount,FileLineImportErrorCount,ObjectTotalCount,ObjectLoadErrorCount,LastUpdateDate&onlyData=true
The response will have this format:
{ "items": [ { "RequestId": 107483, "DataSetName": "Job#2024-08-06 11:40:56", "DataSetStatusCode": "ORA_IN_ERROR", "ImportStatusCode": "SUCCESS", "LoadStatusCode": "ERROR", "SourceTypeCode": "SPREADSHEET", "FileLineTotalCount": 6, "FileLineImportErrorCount": 0, "ObjectTotalCount": 6, "ObjectLoadErrorCount": 2, "LastUpdateDate": "2024-08-06T11:42:13.333+00:00" }, { "RequestId": 107468, "DataSetName": "Grades and Jobs.zip", "DataSetStatusCode": "ORA_IN_ERROR", "ImportStatusCode": "SUCCESS", "LoadStatusCode": "ERROR", "SourceTypeCode": "ZIP_DAT_FILE", "FileLineTotalCount": 38, "FileLineImportErrorCount": 0, "ObjectTotalCount": 37, "ObjectLoadErrorCount": 15, "LastUpdateDate": "2024-08-06T11:40:04.127+00:00" },...
Monitor Data Sets for a Specific HSDL Template
Use this URL to retrieve summary information for all data sets loaded with a specific spreadsheet template:
https://{{env}}/hcmRestApi/resources/11.13.18.05/dataLoadDataSets?q=SpreadsheetTemplateCode=ORA_PER_JOB&fields=RequestId,DataSetName,ImportStatusCode,LoadStatusCode,SourceTypeCode,ObjectTotalCount,ObjectLoadErrorCount,Review&onlyData=true
The response will have this format:
{ "items": [ { "RequestId": 107483, "DataSetName": "Job#2024-08-06 11:40:56", "ImportStatusCode": "SUCCESS", "LoadStatusCode": "ERROR", "SourceTypeCode": "SPREADSHEET", "ObjectTotalCount": 6, "ObjectLoadErrorCount": 2, "Review": "https://cptapniqy.fusionapps.ocs.oc-test.com:443/hcmUI/oracle/apps/hcm/enterpriseSetup/hdlSpreadsheetLoader/di/GenericHdlSpreadsheet.xlsx?layoutCode=ORA_PER_JOB&dataSetName=Job#2024-08-06 11:40:56" } ], "count": 1, "hasMore": false, "limit": 25, "offset": 0, "links": [ ...
Task 4: Monitor Business Object Status
When your HDL data set has multiple business object files you may want to monitor the status of the individual business objects.
Review All Business Objects in a Data Set
- In Postman create a new GET request for this URL:
- Configure the Authorization for the request and Click Send.
https://{{env}}/hcmRestApi/resources/11.13.18.05/dataLoadDataSets/{{RequestId}}/child/businessObjects?onlyData=true
Note:
Replace {{RequestId}} with the RequestId value returned when you submitted your file.The response will have this format:
{ "items": [ { "DataSetBusObjId": 300100621962438, "BusinessObjectDiscriminator": "Grade", "BusinessObjectName": "Grade", "DatFileName": "Grade.dat", "LoadOrder": 1, "BusinessObjectId": 300100028324439, "TransferStatusCode": "SUCCESS", "TransferStatusMeaning": "Success", "ImportStatusCode": "SUCCESS", "ImportStatusMeaning": "Success", "LoadStatusCode": "SUCCESS", "LoadStatusMeaning": "Success", "ImportPercentageComplete": 100, "LoadPercentageComplete": 100, "ImportSuccessPercentage": 100, "LoadSuccessPercentage": 100, "FileLineTotalCount": 14, "FileLineImportErrorCount": 0, "FileLineImportSuccessCount": 14, "ObjectTotalCount": 14, "ObjectLoadErrorCount": 0, "ObjectRollbackErrorCount": 0, "ObjectSuccessCount": 14, "ObjectUnprocessedCount": 0, "DataSetId": 300100621962415, "RequestId": 107468, "CreatedBy": "HCM_USER10", "CreationDate": "2024-08-06T11:38:29+00:00", "LastUpdateDate": "2024-08-06T11:39:24.490+00:00", "LastUpdatedBy": "FUSION_APPS_HCM_ESS_LOADER_APPID", "RollbackEnabledFlag": false, "FailedLinesFileContentId": null, "FailedLinesFileEncryptionType": null, "FailedLinesFileEncryptionKey": null }, { "DataSetBusObjId": 300100621962439, "BusinessObjectDiscriminator": "Job", "BusinessObjectName": "Job", "DatFileName": "Job.dat", "LoadOrder": 2, "BusinessObjectId": 300100028327309, "TransferStatusCode": "SUCCESS", "TransferStatusMeaning": "Success", "ImportStatusCode": "SUCCESS", "ImportStatusMeaning": "Success", "LoadStatusCode": "ERROR", "LoadStatusMeaning": "Error", "ImportPercentageComplete": 100, "LoadPercentageComplete": 100, "ImportSuccessPercentage": 100, "LoadSuccessPercentage": 34, "FileLineTotalCount": 24, "FileLineImportErrorCount": 0, "FileLineImportSuccessCount": 24, "ObjectTotalCount": 23, "ObjectLoadErrorCount": 15, "ObjectRollbackErrorCount": 0, "ObjectSuccessCount": 8, "ObjectUnprocessedCount": 0, "DataSetId": 300100621962415, "RequestId": 107468, "CreatedBy": "HCM_USER10", "CreationDate": "2024-08-06T11:38:29.004+00:00", "LastUpdateDate": "2024-08-06T11:39:58.914+00:00", "LastUpdatedBy": "FUSION_APPS_HCM_ESS_LOADER_APPID", "RollbackEnabledFlag": false, "FailedLinesFileContentId": null, "FailedLinesFileEncryptionType": null, "FailedLinesFileEncryptionKey": null } ], "count": 2, "hasMore": false, "limit": 25, "offset": 0, "links": [ ...
Monitor a Specific Business Object in a Data Set
- In Postman create a new GET request for this URL:
- Configure the Authorization for the request and Click Send.
https://{{env}}/hcmRestApi/resources/11.13.18.05/dataLoadDataSets/{{RequestId}}/child/businessObjects/{{DataSetBusObjId}}?onlyData=true&fields=DataSetBusObjId,BusinessObjectName,LoadOrder,TransferStatusCode,ImportStatusCode,LoadStatusCode,ImportSuccessPercentage,LoadSuccessPercentage,FileLineTotalCount,FileLineImportErrorCount,ObjectTotalCount,ObjectSuccessCount,ObjectLoadErrorCount
Note:
Replace {{RequestId}} with the RequestId value returned when you submitted your file and {{DataSetBusObjId}} with the DataSetBusObjId for the business object record you want retrieve.The response will have this format:
{ "DataSetBusObjId": 300100621962439, "BusinessObjectName": "Job", "LoadOrder": 2, "TransferStatusCode": "SUCCESS", "ImportStatusCode": "SUCCESS", "LoadStatusCode": "ERROR", "ImportSuccessPercentage": 100, "LoadSuccessPercentage": 34, "FileLineTotalCount": 24, "FileLineImportErrorCount": 0, "ObjectTotalCount": 23, "ObjectSuccessCount": 8, "ObjectLoadErrorCount": 15 }
Identify Business Objects in Error
- Use this URL to retrieve summary information for all business objects in the data set that errored during load:
- Configure the Authorization for the request and Click Send.
https://{{env}}/hcmRestApi/resources/11.13.18.05/dataLoadDataSets/{{RequestId}}/child/businessObjects?q=LoadStatusCode=ERROR&fields=DataSetBusObjId,LoadOrder,BusinessObjectName,TransferStatusCode,ImportStatusCode,LoadStatusCode,FileLineTotalCount,FileLineImportErrorCount,ObjectTotalCount,ObjectLoadErrorCount&onlyData=true
The response will have this format:
{ "items": [ { "DataSetBusObjId": 300100621962439, "LoadOrder": 2, "BusinessObjectName": "Job", "TransferStatusCode": "SUCCESS", "ImportStatusCode": "SUCCESS", "LoadStatusCode": "ERROR", "FileLineTotalCount": 24, "FileLineImportErrorCount": 0, "ObjectTotalCount": 23, "ObjectLoadErrorCount": 15 } ]...
Task 5: Retrieve Messages
When your data set has errors, you can retrieve the messages raised for it along with identifiers for the records each message is reported against.
Retrieve All Messages for a Data Set
- In Postman create a new GET request for this URL:
- Configure the Authorization for the request and click Send.
https://{{env}}/hcmRestApi/resources/11.13.18.05/dataLoadDataSets/{{RequestId}}/child/messages?totalResults=true&orderBy=DatFileName,FileLine&fields=DatFileName,BusinessObjectDiscriminator,OriginatingProcessCode,FileLine,ConcatenatedUserKey,SourceSystemOwner,SourceSystemId,SourceReference001,MessageTypeCode,MessageText,MessageUserDetails&onlyData=true
Note:
Replace {{RequestId}} with the RequestId value returned when you submitted your file.The response will have this format:
{ "items": [ { "DatFileName": "Job.dat", "BusinessObjectDiscriminator": "Job", "OriginatingProcessCode": "LOAD", "FileLine": 4, "ConcatenatedUserKey": "HDL_SNR_SALES_CONS-COMMON", "SourceSystemOwner": null, "SourceSystemId": null, "SourceReference001": "HDL_SNR_SALES_CONS", "MessageTypeCode": "ERROR", "MessageText": "You need to enter a valid value for the JobFamilyId attribute. The current values are HDL_SALES.", "MessageUserDetails": null }, { "DatFileName": "Job.dat", "BusinessObjectDiscriminator": "Job", "OriginatingProcessCode": "LOAD", "FileLine": 10, "ConcatenatedUserKey": "HDL_MRKT_DIR-COMMON", "SourceSystemOwner": null, "SourceSystemId": null, "SourceReference001": "HDL_MRKT_DIR", "MessageTypeCode": "ERROR", "MessageText": "You need to enter a valid value for the JobFamilyId attribute. The current values are HDL_MARKETING.", "MessageUserDetails": null }, { "DatFileName": "Job.dat", "BusinessObjectDiscriminator": "Job", "OriginatingProcessCode": "LOAD", "FileLine": 5, "ConcatenatedUserKey": "HDL_SALES_MGR-COMMON", "SourceSystemOwner": null, "SourceSystemId": null, "SourceReference001": "HDL_SALES_MGR", "MessageTypeCode": "ERROR", "MessageText": "The FT value for the FullPartTime attribute is invalid and doesn't exist in the list of values.", "MessageUserDetails": null }, ...
Task 6: Generate Failed Lines File
In addition to extracting message information directly, you can initiate the generation a failed lines file. These files include all data lines with errors and optionally include the message text.
Tip:
Generated failed lines files can be downloaded from the hcm$/dataloader$/export$ account on Oracle WebCenter.Generate a Failed Lines File for the Data Set
- In Postman create a new POST request for this URL:
- Specify the following JSON Body:
- Set the Content-Type header to the value:
application/vnd.oracle.adf.action+json
- Configure the Authorization for the request and click Send. The response will have this format:
- Use this GET URL to retrieve the file details from the dataLoadDataSets resource:
https://{{env}}/hcmRestApi/resources/11.13.18.05/dataLoadDataSets/{{RequestId}}?onlyData=true&fields=FailedLinesFileContentId,FailedLinesFileEncryptionType,FailedLinesFileEncryptionKey
Note:
Replace {{RequestId}} with the RequestId value returned when you submitted your file.{ "FailedLinesFileContentId": "107468_ERRFILE", "FailedLinesFileEncryptionType": "PGPUNSIGNED", "FailedLinesFileEncryptionKey": "customer-key" }
https://{{env}}/hcmRestApi/resources/11.13.18.05/dataLoadDataSets/{{RequestId}}/action/generateFailedLinesFile
Note:
Replace {{RequestId}} with the RequestId value returned when you submitted your file.{ "includeMessagesFlag" : "Y", "messageLanguage" : "US", "fileEncryption" : "PGPUNSIGNED", "encryptionKey" : "<customer-key>" }
Tip:
Replace <customer-key> with the name of the PGP certificate you'll use to encrypt your files. Supply the name specified found in the Certificates page of the Security Console.The following parameters are available for this custom action:
Parameter | Description | Default Value | Required |
---|---|---|---|
includeMessagesFlag | Determines whether warning and error messages are included in the file. Supply Y or N. | "N" | |
messageLanguage | The two-letter code to identify the language to provide messages in. | "US" | |
fileEncryption | The type of encryption to encrypt the generated file with. Valid values are PGPUNSIGNED, PGPSIGNED and NONE. |
Always. When not encrypting your files you must specify NONE. | |
encryptionKey | The PGP certificate to encrypt the generated file with. This must be available in the list of PGP certificates in the security console. HDL will encrypt your file with the public key for you to decrypt with your private key. | When fileEncryption is PGPUNSIGNED or PGPSIGNED. |
{ "result": { "Status": "SUCCESS", "RequestId": "107640", "ErrorFileContentID": "107468_ERRFILE", "UserInfo": "The file will be available on the Oracle WebCenter on completion of this process." } }
Generate a Failed Lines File for a Business Object
The generateFailedLinesFile custom action is also available on the child businessObjects resource. It has the same parameters as the data set level custom action.
- In Postman create a new POST request for this URL:
- Specify the following JSON Body:
- Set the Content-Type header to the value:
application/vnd.oracle.adf.action+json
- Configure the Authorization for the request and click Send. The response will have this format:
- Use this GET URL to retrieve the file details from the dataLoadDataSets resource:
https://{{env}}/hcmRestApi/resources/11.13.18.05/dataLoadDataSets/{{RequestId}}/child/businessObjects/{{DataSetBusObjId}}?onlyData=true&fields=FailedLinesFileContentId,FailedLinesFileEncryptionType,FailedLinesFileEncryptionKey
The response will look like:You can also get the latest file details from the businessObjects resource, as described in Task 4. The response will include these fields:
{ "FailedLinesFileContentId": "107468_JOB_ERRFILE", "FailedLinesFileEncryptionType": "PGPUNSIGNED", "FailedLinesFileEncryptionKey": "customer-key" }
https://{{env}}/hcmRestApi/resources/11.13.18.05/dataLoadDataSets/{{RequestId}}/child/businessObjects/{{DataSetBusObjId}}/action/generateFailedLinesFile
Note:
Replace {{RequestId}} with the RequestId value returned when you submitted your file and {{DataSetBusObjId}} with the DataSetBusObjId for the business object in the data set that you want to generate the file for.{ "includeMessagesFlag" : "Y", "messageLanguage" : "US", "fileEncryption" : "PGPUNSIGNED", "encryptionKey" : "customer-key" }
{ "result": { "Status": "SUCCESS", "RequestId": "107669", "ErrorFileContentID": "107468_JOB_ERRFILE", "UserInfo": "The file will be available on the Oracle WebCenter on completion of this process." } }
Task 7: Retrieve Submitted Process Details
You can retrieve information about the processes and subprocesses submitted for your data set.
Retrieve Process Details
Typically, you would see a record for the transfer, import and load processes for each business object in your data set, along with any business object and data set post processes. There will be additional processes if you have submitted further processing of your data set, such as resubmit load, submit roll back or generate a failed lines line.
- In Postman create a new GET request for this URL:
- Configure the Authorization for the request and click Send.
https://{{env}}/hcmRestApi/resources/11.13.18.05/dataLoadDataSets/{{RequestId}}/child/processes?onlyData=true&fields=ProcessId,ProcessCode,BusinessObjectName,FileActionCode,TotalCount,ErrorCount,ElapsedTime,ThreadsAllocated,ThreadsUsed
This is how the response might look for a data set containing the Worker business object. In this example, Load has been submitted twice for the business object.
{ "items": [ { "ProcessId": 300100618948404, "ProcessCode": "ORA_TRANSFER_OBJECT", "BusinessObjectName": "Worker", "FileActionCode": null, "TotalCount": 540, "ErrorCount": 0, "ElapsedTime": "+000000000 00:00:40.347000", "ThreadsAllocated": 8, "ThreadsUsed": 1 }, { "ProcessId": 300100618948432, "ProcessCode": "ORA_IMPORT_OBJECT", "BusinessObjectName": "Worker", "FileActionCode": null, "TotalCount": 540, "ErrorCount": 0, "ElapsedTime": "+000000000 00:00:13.475000", "ThreadsAllocated": 8, "ThreadsUsed": 8 }, { "ProcessId": 300100618950528, "ProcessCode": "ORA_LOAD_OBJECT", "BusinessObjectName": "Worker", "FileActionCode": "IMPORT_AND_LOAD", "TotalCount": 54, "ErrorCount": 54, "ElapsedTime": "+000000000 00:00:43.242000", "ThreadsAllocated": 8, "ThreadsUsed": 3 }, { "ProcessId": 300100618950575, "ProcessCode": "ORA_HDL_POST_PROCESS", "BusinessObjectName": "Worker", "FileActionCode": null, "TotalCount": 0, "ErrorCount": 0, "ElapsedTime": "+000000000 00:00:43.444000", "ThreadsAllocated": null, "ThreadsUsed": null }, { "ProcessId": 300100619031844, "ProcessCode": "ORA_LOAD_OBJECT", "BusinessObjectName": "Worker", "FileActionCode": "ORA_LOAD", "TotalCount": 54, "ErrorCount": 11, "ElapsedTime": "+000000000 00:14:56.706000", "ThreadsAllocated": 8, "ThreadsUsed": 3 }, { "ProcessId": 300100619039767, "ProcessCode": "ORA_HDL_POST_PROCESS", "BusinessObjectName": "Worker", "FileActionCode": null, "TotalCount": 0, "ErrorCount": 0, "ElapsedTime": "+000000000 00:00:34.738000", "ThreadsAllocated": null, "ThreadsUsed": null }, { "ProcessId": 300100619039775, "ProcessCode": "ORA_ORACLE_SEARCH", "BusinessObjectName": "Worker", "FileActionCode": null, "TotalCount": 0, "ErrorCount": 0, "ElapsedTime": "+000000000 00:01:41.009000", "ThreadsAllocated": null, "ThreadsUsed": null } ...
Retrieve Subprocess Information
The subprocesses is a child resource of the processes resource. It retrieves details of the subprocesses submitted by a specified process. For the transfer, import, load processes this will be details of the multi-threaded processes. For the ORA_HDL_POST_PROCESS this will detail the individual post processes submitted.
- In Postman create a new GET request for this URL:
- Configure the Authorization for the request and click Send.
https://{{env}}/hcmRestApi/resources/11.13.18.05/dataLoadDataSets/{{RequestId}}/child/processes/{{ProcessId}}/child/subprocesses?onlyData=true&fields=ProcessName,ProcessId,ProcessCode,Elapsedtime,ProcessStatusMeaning
Note:
Replace {{RequestId}} with the RequestId value returned when you submitted your file and {ProcessId}} with the ProcessId for the process you want the subprocesses for.This is how the response might look for the ORA_HDL_POST_PROCESS process submitted for the Worker business object.
{ "items": [ { "ProcessName": "Refresh Manager Hierarchy", "ProcessId": 300100619039790, "ProcessCode": "WORKER_REFRESHMANAGERHIERARCHY", "Elapsedtime": "+000000000 00:00:08.216000", "ProcessStatusMeaning": "Succeeded" }, { "ProcessName": "Update Person Search Keywords", "ProcessId": 300100619037746, "ProcessCode": "WORKER_UPDATEPERSONSEARCHKEYWORDS", "Elapsedtime": "+000000000 00:00:09.093000", "ProcessStatusMeaning": "Succeeded" }, { "ProcessName": "EMEA Localization Postprocessing", "ProcessId": 300100619037756, "ProcessCode": "WORKER_EMEALOCALIZATIONPOSTPROCESSING", "Elapsedtime": "+000000000 00:00:02.959000", "ProcessStatusMeaning": "Succeeded" }, { "ProcessName": "Global Tax Reporting Unit Maintenance", "ProcessId": 300100619037764, "ProcessCode": "WORKER_GLOBALTAXREPORTINGUNITMAINTENANCE", "Elapsedtime": "+000000000 00:00:02.702000", "ProcessStatusMeaning": "Succeeded" } ...
Task 8: Submit Processing of an Existing Data Set
If your HCM Data Loader file was only imported or you want to attempt to load previously failed records, you can submit load for your data set. If you have successfully loaded records, you can submit roll back for your data set. Roll back is only initiated for business objects that support the roll back action.
Tip:
The RollbackEnabledFlag element on the businessObjects resource indicates if the business object supports being rolled back.Submit Processing of a Data Set
- In Postman create a new POST request for this URL:
- Specify the following JSON Body:
- Set the Content-Type header to the value:
application/vnd.oracle.adf.action+json
- Configure the Authorization for the request and click Send. The response will have this format:
https://{{env}}/hcmRestApi/resources/11.13.18.05/dataLoadDataSets/{{RequestId}}/action/submit
Note:
Replace {{RequestId}} with the RequestId value returned when you submitted your file.{ "fileAction" : "LOAD" }
The following parameters are available for this custom action:
Parameter | Description | Default Value |
---|---|---|
fileAction | Valid values are LOAD and ROLLBACK.
Tip: For HSDL files, if you only import the file, you can't load it with REST, instead generate a spreadsheet for the template, fetch the data set and upload it from the spreadsheet. |
"LOAD" |
loadConcurrentThreads | Maximum number of concurrent threads to assign to the load process. | Defaulted from the Maximum Concurrent Threads for Load parameter. |
{ "result": { "Status": "SUCCESS", "RequestId": "107683" } }
Submit Processing for a Business Object within a Data Set
The submit custom action is also available at the business object level where it has the same parameters but only submits processing of the business object specified by the URL.
Use this URL to initiate processing for a specific business object.
https://{{env}}/hcmRestApi/resources/11.13.18.05/dataLoadDataSets/{{RequestId}}/child/businessObjects/{{DataSetBusObjId}}/action/submit
Note:
Replace {{DataSetBusObjId}} with the DataSetBusObjId value returned when you reviewed the business object within the data set.Tip:
You can test this by Base64 encoding, uploading and submitting the JobFamily.zip file which creates the job families referenced by the Job.dat file in error above.Once the job families are loaded successfully, initiate LOAD for the data set or Job business object within the data set as described above.
Task 9: Stop an In Progress Data Set or Business Object
You can request to stop in progress data sets and business objects.
Stop Processing of a Data Set
- In Postman create a new POST request for this URL:
- Set the Content-Type header to the value:
application/vnd.oracle.adf.action+json
- Configure the Authorization for the request and click Send.
https://{{env}}/hcmRestApi/resources/11.13.18.05/dataLoadDataSets/{{RequestId}}/action/stop
There are no parameters for this action.
If the data set isn't currently in progress, you will instead get this response:
{ "result": "You can't stop processing this data set because it isn't being processed. Check the current status of the data set." }
Stop Processing of a Business Object
The stop custom action is also available at the business object level. Use this URL to request processing to be stopped for a specific business object:
https://{{env}}/hcmRestApi/resources/11.13.18.05/dataLoadDataSets/{{RequestId}}/child/businessObjects/{{DataSetBusObjId}}/action/stop
Note:
Replace {{DataSetBusObjId}} with the DataSetBusObjId value returned when you reviewed the business object within the data set.Task 10: Delete a Data Set
You can delete the staging table data for your data sets if they are not being processed.
Delete HDL Stage Table Data
- In Postman create a new POST request for this URL:
- Set the Content-Type header to the value:
application/vnd.oracle.adf.action+json
- Configure the Authorization for the request and click Send.
https://{{env}}/hcmRestApi/resources/11.13.18.05/dataLoadDataSets/{{RequestId}}/action/deleteDataSet
There are no parameters for this action.
Delete HSDL Stage Table Data
Submitting this action will delete your data set from both the HSDL and HDL staging tables.
- In Postman create a new POST request for this URL:
- Set the Content-Type header to the value:
application/vnd.oracle.adf.action+json
- Configure the Authorization for the request and click Send.
https://{{env}}/hcmRestApi/resources/11.13.18.05/dataLoadDataSets/{{RequestId}}/action/deleteSpreadsheetDataSet
There are no parameters for this action.
Related Links
- Solution Playbook: Load data into Oracle Fusion Cloud Human Capital Management (HCM)
- User Guide: HCM Data Loader
- HCM Data Loader Oracle by Example Tutorials
The Operate section describes other ways of loading files to the Oracle WebCenter Content server.
More Learning Resources
Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.
For product documentation, visit Oracle Help Center.
Using the HCM Data Loader REST API
F88233-04
August 2024