OIC - How to extract large data from Oracle Fusion using REST APIs | How to overcome REST APIs limitation which can return only 499 records in a single request

Usecase: Here, we will get Rest data in 500 records size chunks using limit and offset.

Limitation:

Oracle REST APIs can return only 499 (<500) records in a single request. But in Fusion, there may be more than 499+ records so we need to fetch all records

Solution:

We need to make use of Oracle REST APIs parameters like offset and limit and call REST API in the loop until hasMore attribute in the response is false.

Sample Request URL to test rest: 

https://servername.fa.us2.oraclecloud.com/fscmRestApi/resources/latest/invoices?fields=InvoiceNumber,InvoiceCurrency&onlyData=true&links=self&limit=1&offset=0

Operation: GET

Response

{
    "items": [
        {
            "InvoiceNumber": "test23",
            "InvoiceCurrency": "USD"
        }
    ],
    "count": 1,
    "hasMore": true,
    "limit": 1,
    "offset": 0,
    "links": [
        {
            "rel": "self",
            "href": "https://servername.fa.us2.oraclecloud.com:443/fscmRestApi/resources/11.13.18.05/invoices",
            "name": "invoices",
            "kind": "collection"
        }
    ]
}

Implementaion steps:

1. Create a new Scheduled integration and assign all the required variables offset=0, limit= 500, hasMore="true", onlyData="true", links="self", fields="InvoiceNumber,InvoiceCurrency"
2. Take a stage and write the headers
3. Take a while and loop till hasMore != "false"
4. Configure rest endpoint to get the invoice data with the qurey params
1. URI: fscmRestApi/resources/latest/invoices
2. Query params: fields,onlyData,links,limit, offset
3. Json response 
4. Map the fields
5. Take a stage and write invoices
6. Take a assign and update: 
1. hasMore= from the inboice response hasMore, 
2. offset = $offset + 500
7. Take a ftp adapter and configure to write final response.

Detailed steps with screenshots:

Integration flow:

Assign globals

Write headers

Loop 

Get rest call

Write to stage.

Update variable

Write to ftp directory

Generated file

OIC Javascript Function - Gen3 - the function definition contains unsupported synstax. The output parameter must be named.

While we are creating a javascript function in OIC Gen 3 version, we have received below error:

the function definition contains unsupported synstax. The output parameter must be named.

The javascript code used:

function replaceString(value, str1,str2){

output = value.replace(new RegExp(str1, "g"), str2)

return output;

}

Solution:

If we declare any variable like output here, we have to also add a var keyword before it.

Modified js code:

function replaceString(value, str1,str2){

var output = value.replace(new RegExp(str1, "g"), str2)

return output;

}

OIC ERP - FBDI ImportBulkData using Erpintegration REST API | Get import status using Erpintegration REST API

Usecase: Here, we will see how to call ERP rest API and import FBDI data and also check import status.

Here., we will import supplier data.

References:

Follow below oracle page for Rest api details:

https://docs.oracle.com/en/cloud/saas/financials/24b/farfa/op-erpintegrations-post.html

https://docs.oracle.com/en/cloud/saas/financials/24b/farfa/op-erpintegrations-operationname-get.html

ImportBulkData Operation:

Sample Request body:

{

   "OperationName":"importBulkData",

   "DocumentContent":"UEsDBBQAAAAIAHSsJUWkOHOwEwEAAHgDAAAXAAAAQXBJbnZvaWNlc0ludGVyZmFjZS5jc3bV0LFOwzAQBuAdiXewOjBdU5/ts+MxNICCIEKEIjEG4SEijUrsCMHT47ZEqpCyIBj6Tzf8urM/JETOORSXV+x88E3nvGerrgmwvMmK2wrGgraWJwIER7VAseAGgvPBdcH1m77xjj3X3Ss7Y6EffAAtSBmEVFhjJKyqHN4AqoeszLP7HID0HEmmljRk5RMU67V7aergIGa8IHE/x1NxPhwngsBjBBdG8jlCAn+XizI/PdlJSDtNNRZQUnzI90f4SLWph5aFiOR637qPSJRK5JBKo8SeqD4kknoutFWpmiCKa39LhAqJ4vp/IlJymmgskP5JNNsaPTauZXcJW9b+3bUtsOs+mW2ljKQolRKpndTn0Ut9AVBLAwQUAAAACAAze2pIXdvnRHQAAACpAAAAEQAAAEFQVEVTVC5QUk9QRVJUSUVTdYrBCoMwEER/Reg1sPQTQgklYFVoatvjNqSwEDYhK4J/b9CrXmYeMy8V9DEA5iwQROBPjOwJo0DGBX8xCBDPiXyFqSAL+okSi9LDx9rH4Co483Tq0r3aVo0k9W36HApu3r6fpu3G3t5Mc9fOvPX3wLjuvQJQSwECFAAUAAAACAB0rCVFpDhzsBMBAAB4AwAAFwAAAAAAAAAAACAAAAAAAAAAQXBJbnZvaWNlc0ludGVyZmFjZS5jc3ZQSwECFAAUAAAACAAze2pIXdvnRHQAAACpAAAAEQAAAAAAAAAAACAAAABIAQAAQVBURVNULlBST1BFUlRJRVNQSwUGAAAAAAIAAgCEAAAA6wEAAAAA",

   "ContentType":"zip",

   "FileName":"APTEST_0310.zip",

   "DocumentAccount":"fin$/payables$/import$",

   "JobName":"oracle/apps/ess/financials/payables/invoices/transactions,APXIIMPT",

   "ParameterList":"#NULL,Vision Operations,#NULL,#NULL,#NULL,#NULL,#NULL,INVOICE GATEWAY,#NULL,#NULL,#NULL,1,#NULL",

   "CallbackURL":"#NULL",

   "NotificationCode":"10",

   "JobOptions":"InterfaceDetails=1,ImportOption=Y ,PurgeOption = Y,ExtractFileType=ALL"

}

Sample Response body:

{

   "OperationName":"importBulkData",

   "DocumentId":null,

   "DocumentContent":"UEsDBBQAAAAIAHSsJUWkOHOwEwEAAHgDAAAXAAAAQXBJbnZvaWNlc0ludGVyZmFjZS5jc3bV0LFOwzAQBuAdiXewOjBdU5/ts+MxNICCIEKEIjEG4SEijUrsCMHT47ZEqpCyIBj6Tzf8urM/JETOORSXV+x88E3nvGerrgmwvMmK2wrGgraWJwIER7VAseAGgvPBdcH1m77xjj3X3Ss7Y6EffAAtSBmEVFhjJKyqHN4AqoeszLP7HID0HEmmljRk5RMU67V7aergIGa8IHE/x1NxPhwngsBjBBdG8jlCAn+XizI/PdlJSDtNNRZQUnzI90f4SLWph5aFiOR637qPSJRK5JBKo8SeqD4kknoutFWpmiCKa39LhAqJ4vp/IlJymmgskP5JNNsaPTauZXcJW9b+3bUtsOs+mW2ljKQolRKpndTn0Ut9AVBLAwQUAAAACAAze2pIXdvnRHQAAACpAAAAEQAAAEFQVEVTVC5QUk9QRVJUSUVTdYrBCoMwEER/Reg1sPQTQgklYFVoatvjNqSwEDYhK4J/b9CrXmYeMy8V9DEA5iwQROBPjOwJo0DGBX8xCBDPiXyFqSAL+okSi9LDx9rH4Co483Tq0r3aVo0k9W36HApu3r6fpu3G3t5Mc9fOvPX3wLjuvQJQSwECFAAUAAAACAB0rCVFpDhzsBMBAAB4AwAAFwAAAAAAAAAAACAAAAAAAAAAQXBJbnZvaWNlc0ludGVyZmFjZS5jc3ZQSwECFAAUAAAACAAze2pIXdvnRHQAAACpAAAAEQAAAAAAAAAAACAAAABIAQAAQVBURVNULlBST1BFUlRJRVNQSwUGAAAAAAIAAgCEAAAA6wEAAAAA",

   "FileName":"APTEST_0310.zip",

   "ContentType":"zip",

   "FileType":null,

   "DocumentAccount":"fin$/payables$/import$",

   "Comments":null,

   "ProcessName":null,

   "LoadRequestId":null,

   "JobPackageName":null,

   "JobDefName":null,

   "ReqstId":"14258",

   "RequestStatus":null,

   "JobName":"oracle/apps/ess/financials/payables/invoices/transactions,APXIIMPT",

   "ParameterList":"#NULL,Vision Operations,#NULL,#NULL,#NULL,#NULL,#NULL,INVOICE GATEWAY,#NULL,#NULL,#NULL,1,#NULL",

   "NotificationCode":"10",

   "CallbackURL":"#NULL",

   "JobOptions":"InterfaceDetails=1,ImportOption=Y ,PurgeOption = Y,ExtractFileType=ALL",

   "StatusCode":null,

   "ESSParameters":null,

   "links":[

      {

         "rel":"self",

         "href":"https://servername.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/erpintegrations/importBulkData",

         "name":"erpintegrations",

         "kind":"item"

      },

      {

         "rel":"canonical",

         "href":"https://servername.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/erpintegrations/importBulkData",

         "name":"erpintegrations",

         "kind":"item"

      }

   ]

}

GetEssJobStatus Operation

Sample Response body:

{
  "items": [
    {
      "OperationName": "getESSJobStatus",
      "DocumentId": null,
      "DocumentContent": null,
      "FileName": null,
      "ContentType": null,
      "FileType": null,
      "DocumentAccount": null,
      "Comments": null,
      "ProcessName": null,
      "LoadRequestId": null,
      "JobPackageName": null,
      "JobDefName": null,
      "ReqstId": "14557",
      "RequestStatus": "SUCCEEDED",
      "JobName": null,
      "ParameterList": null,
      "NotificationCode": null,
      "CallbackURL": null,
      "JobOptions": null,
      "StatusCode": null,
      "ESSParameters": null,
      "links": [
        {
          "rel": "self",
          "href": "https://servername.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/erpintegrations/getESSJobStatus",
          "name": "erpintegrations",
          "kind": "item"
        },
        {
          "rel": "canonical",
          "href": "https://servername.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/erpintegrations/getESSJobStatus",
          "name": "erpintegrations",
          "kind": "item"
        }
      ]
    }
  ],
  "count": 1,
  "hasMore": false,
  "limit": 25,
  "offset": 0,
  "links": [
    {
      "rel": "self",
      "href": "https://servername.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/erpintegrations",
      "name": "erpintegrations",
      "kind": "collection"
    }
  ]
}

Implemented steps:

1. Create REST API connection using ERP rest URL and provide security as basic autentication : user name and password.
2. First list the files from a ftp directory using FTP connection
3. Put a for each action to repeat for each list files.
4. Read file using ftp adapter connection. Provide a sample csv file. Map the currentFile >> directory and filename to ftp read request.
5. Take a stage write supplier fbdi tample format file. Filename: PozSupplierint.csv. sample csv file C1 to C156. Map the required columns.
6. Zip the file using stage. PozSupplierInt.zip
7. Configure rest call for import bulk data.
1. Rest URI: /fscmRestApi/resources/11.13.18.05/erpintegrations
2. Verb: post
3. Configure request and response json files.
4. Map the required values.
8. Assign a status variable with value "NOT_SUCCEEDED".
9. While loop to check whether the import status success. Condition : $status != "SUCCEEDED"
10. Configure REST endpoint to check the import status:
1. URI:  /fscmRestApi/resources/11.13.18.05/erpintegrations
2. Verb: GET
3. Add a query parameter: finder
4. Provide a json payload for response.
11. Map the import bulk data response >> Reqst Id to finder 
1. Finder = concat("ESSJobStatusRF;requestId=",ReqstId)
12. Update status = RequestStatus

Detailed screenshots:  

Create Rest connection:

Integration flow:

List file

For each file

Read file

Write FBDI file

Zip file

Import bulk data

Get ess job status check.

Testing:

Notes:

 

1. while invoking for importBulk data, we need to keep in mind that interface details number = 24 for supplier import else it will not find the proper supplier control file in ERP.

2. Supplier fbdi template:

https://docs.oracle.com/en/cloud/saas/procurement/24b/oefbp/importsuppliers-3187.html#importsuppliers-3187

3. Suppllier source file sample:

SupplierSource.csv

Action,SupplierName,SupplierNumber,AlternateName,OrgType,InactiveDate,SupplierType,Relationship,Permanent,Country,TaxID

CREATE,3M,Company123,3221,3M,Corporation,09-10-2014,Supplier,SPEND_AUTHORIZED,N,US,410417775

4. We can also do other operations like uploadfiletoUCM, submitESSJobRequest,exportbulkdata, purge interface table etc.