OIC - Invoke ERP BI report | Create ExternalReportWSSService wsdl connection in oracle integration

Usecase: Here, we will see the steps to invoke a BI Report in an Integration

• Create ERP SOAP Connection
• Create an integration and configure BI report Invoke
• Mapping the required fields
• Decode the BI base64 response

Create Connection : 

Here we will configure  " ExternalReportWSSService WSDL URL " which can be used to run a report. The same connection we will use in below steps while developing integration.

1. Navigate to connection window and select SOAP adapter, give any meaningful name for connection.
2. Enter the ExternalReportWSSService WSDL URL  and Select the TLS Version. TLS Version depends on your ERP Cloud Application Instance. Mostly it is TLSv1.1WSDL URL Format : https://<your oracle cloud application instance domain>/xmlpserver/services/ExternalReportWSSService?WSDL
3. Choose Security policy as Username Password token and Enter the *Username and *Password of the Oracle Cloud Application Instance. Click SAVE and TEST the Connection.

Steps with screenshots:

Create Integration :

Create one Scheduled Integration by giving some meaningful name >>  Search for the connection which we have created above and select it. Enter some meaningfull name(ex: invoke BI Report) and then click NEXT. >>  Select the operation = runReport and then click NEXT >> In headers configuration window, select Accept attachments in response. Click Next

SOAP Endpoint configuration completed. Click Done.

Mapping the required fields:

Now let's do the MAPPINGS. We will pass all the required fields to invoke BI report ( like absolute Report Path, size of data chunk download, Report Parameters Name and its Value ,attribute template etc.)

For my case I have used the following:

reportAbsolutePath* element =Custom\Integrations\Inbound\POC\eTEXT_INV_ID_SEQ.xdo

sizeOfDataChunkDownload*="-1"

You can also use the following paramters as per your requirement:

Expand the "parameterNameValues" element

Expand "Item" and then enter your BI Report Parameter name in name* element 

Now expand values* and map or pass Parameter Values in Item element .

Parameter names and its values configuration completed

*Note : suppose you have multiple parameters for your BI Report then you have to repeat the Item tag and then pass the Parameter name and its Values.

Decode Base64 BI report response:

Use decodeBase64(reportBytes) and store in a variable and then it can contain carriage return so we need to use a custom javascript and remove the carriage returns to get the actual data.

Use my post to know how to remove carriage returns using JS - oic-javascript-to-remove-carriage-return

Inside this step we will enable the tracking and do one round of testing - Enable the Tracking - BI Report call configuration completed. Click save and Close the integration edit window - Activate the integration - Submit the integration by clicking Submit now and then click on Instance Id to track the flow - Once processing succeeded , open the flow by clicking start time - Click on active stream and check the payload received as output from report

OIC - ERP - How to get Job Package and Name

Perform the following steps: 

• From Fusion Apps main page, Click the Navigator, and others then click Setup and Maintenance.

• On the Setup page, select your setup offering such as Financials

• Click on right icon as shown above to search for "Manage Enterprise Scheduler Job Definition and Job Sets for Financial, Supply Chain Management and Related Applications":

• Select and Click "Manage Enterprise Scheduler Job Definition and Job Sets for Financial, Supply Chain Management and Related Applications"

•  and search for "%Invoice%" in display name.

• Select "Import Payables Invoices" row and click edit (please do not change anything as our goal is to get job package and name):

• Now you can see the job package is the PATH and job name is the NAME.

•  These will be the first two columns in your property file for "Import Payables Invoices" with third column the sample prefix name of the zip file as suggested earlier: 

oracle/apps/ess/financials/payables/invoices/transactions/,APXIIMPT,APInvoices, <list of parameter separated by comma>

From User Properties tab, we can see the number of arguments to send. For invoice , it is 14.

OIC - how to show the import response id to the activity stream for better support

 To show the import response id to the activity stream for better support, 

we just need to perform following steps:

• Add teh logger activity and provide a proper name like "CaptureImportId" 
• Then mention Log as "Always" 
• Mapthe response import result.

OIC - How to handle more than 10 mb file or large file poll

How to handle more than 10  mb file or large file poll???

If we use ftp or file adapter to poll files from a directory, it can only allow to poll or read file content max 10MB file.

So in order to read large files(>10 MB),  we’ll have to download the file to a Virtual file system or a stage location as it supports 1 GB data poll but  if you want to read the content, again it supports 10MB , so using file read in segment , best option to read contents in segments or smaller chunks and then marge it to create full target specific file.

Basically, we need to follow the following sequences:

1. First list the files using ftp.
2. Then for each file
3. Download each file to stage location
4. Using stage activity, read file in segments. In esch segment it support 200 records to process.
5. Create the required file format and marge each segment to create full file.

OIC - Import to ERP Technique 1 - Import Payables Invoices using import bulk data into Oracle ERP Cloud option | Enbale default callback | Subscribe callback

Usecase: Here, we will create a reusable child integration which will take the importable zip file(header file, line file, properties file) as base64 encoded and import to ERP and enable callback and we will also create a callback integration to subscribe the callback event upon ERP job completion. This is resuable integration so not only AP Invoice, we can also use for other import jobs also like GL, Fixed asset etc using a simple swich block.

High level Steps:

• Create FBDI Invoice header and line csv files
• Create .properties file
• Create zip file
• Upload to ERP using bulk import option
• Create a callback integration in order to get callback upon ERP job completion

Create FBDI file:

• Build a Scheduled integration
• List all the files from SFTP
• Iterate over the files and Download file on staging
• Read the file from staging in chunks and create the FBDI file.

Create .properties file:

APInvoices.properties

Syntax:

<job package name>,<job definition name>,<zip file prefix>,<Param1>,.....<ParamN>

/oracle/apps/ess/financials/payables/invoices/transactions/,APXIIMPT,APInvoices,#NULL,$BusinesUnitId,N,#NULL,#NULL,#NULL,1000, Source,#NULL,N,N,LedgerId,#NULL,1

Create zip file:

create zip file with following 3 files

APInvoices.zip

    APInvoicesInterface.csv

    APInvoiceLinesInterface.csv

    APInvoices.properties

Upload to ERP using bulk Import:

Configure oracle ERP cloud endpoint >> import bulk data into Oracle ERP Cloud >> Import Payables Invoices>>Enable callback>>Done

Mapping:

Zip file reference >> reference

zip file properties file name >> file name

Create a callback integration in order to get callback upon ERP job completion:

Configure oracle ERP cloud endpoint >>select option - receive callback message upon completion of FBDI bulk Import job submitted via another integration >>Import payables Invoices

Detailed screenshots:

Step1: create a reusable integration to import to ERP and enable callback

Flow diagram:

Configure rest trigger :

Put a condition branch for each type of job import.

Configure ERP cloud adapter to import and enable callback

Map the zip file as reference and file name

Map the scope level faults to rest trigger response.

Subscribe to callback events:

Testing:

What we can do after subscribing call back:

• Success Scenario:
• First we check if summary status succeeded
• Then assign the import payables request it to a variable
• Download ess jobs execution logs or execution report for the import payables req id.
• Write the zip file to stage and unzip.
• Share the reports or logs over a Notification mail
• Failure Scenario
• When the summary status not succeeded, just send a notification mail with the callback file reference as attachment. The attachment will include all the processes log files, zipped file omported, properties file, and any error files. Then the concerned team or support team can take care of the issue.

Detailed Screenshots:

Note: 

1. Sometimes we observe that import to ERP main table does not happen with different kind of failures but still we are getting summary status as success in callback, in such scenario, we suggest to implement a custom callback. 
2. If we use import payables invoice request id to download pdf report file, it does not, it simply provides a general import log file. To get the overall invoice import report, we can schedule the report ESS job and get the report. Follow my blog to downlaod the teport - https://soalicious.blogspot.com/2023/12/oic-erp-how-to-get-import-payables.html
3. Like for GL, import journal child process we are easily can download out file which includes all the status report.