Oracle ERP - How to download ESS job's output and log file using ErpIntegrationService web service with SOAP UI?

ErpIntegrationService web service can be used to start an ESS job in Fusion Applications, to monitor status and to download the output and log file. 

In order to retrieve the output and log file of an ESS request following two operations are available:

1. downloadESSJobExecutionDetails
2. downloadExportOutput

As a response when invoking the web service with any of these operations, a payload containing a list of document id, file name, content type, file title, author, security group and account, file content is returned. In this payload the file content is contained as an attachment.

Steps with Screenshots:

First fetch the wsdl url from ERP.

Navigation >> Tools >> Developer connect >> search with ERP Integration Service >> click the service and select the wsdl

https://<erp public host>/fscmService/ErpIntegrationService?WSDL

Open the SOAP UI >> New Soap Project >> provide the wsdl url and name >> select operation , here downloadESSJobExecutionDetails >> Request 1

Provide the request id in the payload >> auth >> add new authorizaton >> basic >> provide user and pwd

Run >> select attachments tab in the response window and select the attachement >> select the button which "Exports the selected attachment to a file" >> save with .zip extension at the desired location.

Oracle ERP supplier Callback BI report SQL for control report

Once we have done the FBDI import, we can generate a BI report to show which details successfully processed or rejected and etc.

Here, showing supplier sample sql query for such. We can improve more to the query as per required.

Sample sql query:

With Details 

AS

(Select 'Source: supplier : ' || (select distinct psi.vebdor_id from poz_suppliers_int psi where psi.load_request_id = :p_load_request_id) AS source_name,

'Total number of Suppliers received: ' || (select to_char(count(vendor_id)) AS vendor_id from poz_suppliers psi where psi.load_request_id = :p_load_request_id) AS count_rcv,

'Total number of Suppliers loaded from staging table: ' || (select count(vendor_id) from poz_suppliers_int psi where psi.load_request_id = :p_load_request_id and psi.status = 'PROCESSED') AS count_loaded,

'Total number of Suppliers not loaded from staging table: ' || (select count(Vendor_id) from poz_suppliers_int psi where psi.load_request_id = :p_load_request_id and nvl(psi.status, 'REJECTED')= 'REJECTED') AS count_ignored,

'Total number of errored Suppliers: ' || (select count(vendor_id) from poz_suppliers.int psi where psi.load_request_id = :p_load_request_id and psi.status = 'REJECTED') AS count_rejected,

From dual

Select source_name columnsl1 from details

Union all

Select count_rcv from details

Union all

Select count_loaded from details

Union all

Select count_ignored from details

Union all

Select count_rejected from details

BI report generated:

Data from poz_suppliers_int for a load request id:

Oracle ERP - Soap UI - Run BI report using ExternalReportWSSService from Soapui

Usecase: Here, we will show how to run BI report from SOAP UI tool.

We have to call the following wsdl format to call the BI report:

https://<your oracle cloud application instance domain>/xmlpserver/services/ExternalReportWSSService?WSDL

Steps to follow:

File >> New SOAP Project

Add the wsdl and a project name.

runReport >> Request 1

Put the following data :

reportAbsolutePath

sizeOfDataChunkDownload

ParameterNameValues

Name: p_load_request_id

values >> item : <id>

Add basic Authrization > add provide user and password details and run.

See we got the response which is in base64 decoded format.

Cloud ERP - Custom folder is not showing under shared folder of Reports & Analytics | how to get the shared folders access to a user | get full control access to BI Consumer

If custom folder is not showing under shared folder of reports and analytics or you have only read access to the shared folders, this is simply a permission issues to the user.

To set permission on a folder:

In the shared folder tab, click on the folder to set permission

• To all users who Have BI Consumer Role assigned: BI Consumer Role >> make it Full Control
• To a specific user: Add user >> search the user and added the account with full control permission

Details Steps to follow:

Shared Folders >> Custom >> Permission >> BI Consumer Role >> make it Full Control >> ok

Also we can give access to specific user:

Add user >> search the user and added the account with full control petmission  >> select owner and ok

OIC - ERP - How to download Oracle FBDI template | Generate CSV files from the template file

Usecase: Here, we will show how to download FBDI template and get the CSV header and line files from the template 

Implementation Steps:

Step1: Download the Oracle FBDI template for Payable standard Invoice.

https://docs.oracle.com/en/cloud/saas/index.html

And follow the navigation:

ERP >> Financials >> Administer >> Import Data (Get started with file-based data import >> Payables (Payables Standard Invoice Import) >>XLSM template(PayablesStandardInvoiceImportTemplate.xlsm)

Step2 : Generate CSV files from the xlsm template.

In Invoice header, put unique Invoice id and Invoice number.

In Invoice Line , put that same invoice id for all the line Items.

In my caes, I have taken 1 Invoice header and 10 Invoice Line Items.

It will give a zip file which contains header and line files. The same format we have to send to erp for import.

Oracle Cloud ERP - What are the roles required to create data model in Oracle Fusion

If you dont see any option to create a new data model or update an existing one under Reports and Analytics, the reason is that your user account is missing the following required roles:

• BI Consumer Role
• BI Publisher Data Model Developer

Before adding the above roles:

Roles to add (navigation >> Tools >> Security Console >> Roles):

After adding the roles:

Note : after adding the roles to a user, you have to run the job - "Retrieve Latest LDAP Changes" to sync and reflect the roles to the user.

Navigation >> Tools >> Scheduled Processes >> Schedule New Process.

OIC - Cloud ERP - What we can do after FBDI import | Callback oracle control file

Use case: After the FBDI import, We can create a BI report to show which transactions or records are received, loaded from interface table, failed or rejected transactions.

For instance here, we will see the BI sql query to fetch AP payable invoice related details to the import using the load request id.

Highlevel steps:

• Create a BI report with data model and SQL query to fetch the import details.
• Create an ExternalReportWSSService wsdl soap connection.
• Create an app driven integration and peform following:
• Subscribe the callback
• Check if summarystatus is succeeded
• Check if load to interface is succeeded
• Check if import process succeeded
• Assign all the paramters to variables required for BI call.
• Call the created BI report soap connection and map the params.
• Call a sftp connection to store the fetched BI report response to a location.

Step1: Create BI report steps: 

Sql query used:

With Details 

AS

(Select 'Source: AP : ' || (select distinct aii.source from ap_invoices_interface aii where aii.load_request_id = :p_load_request_id) AS source_name,

'Total number of invoices received: ' || (select to_char(count(invoice_id)) AS invoice_id from ap_invoices_interface aii where aii.load_request_id = :p_load_request_id) AS count_rcv,

'Total dollar amount of invoices received: ' || (select nvl(sum(invoice_amount),0) AS invoice_amt from ap_invoices_interface aii where aii.load_request_id = :p_load_request_id) AS amount_rcv,

'Total number of invoices loaded from staging table: ' || (select count(invoice_id) from ap_invoices_interface aii where aii.load_request_id = :p_load_request_id and aii.status = 'PROCESSED') AS count_loaded,

'Total dollar amount of invoices loaded from staging table: ' || (select nvl(sum(invoice_amount),0) from ap_invoices_interface aii where aii.load_request_id = :p_load_request_id and aii.status = 'PROCESSED') AS amount_loaded,

'Total number of invoices not loaded from staging table: ' || (select count(invoice_id) from ap_invoices_interface aii where aii.load_request_id = :p_load_request_id and nvl(aii.status, 'REJECTED')= 'REJECTED') AS count_ignored,

'Total dollar amount of invoices not loaded from staging table: ' || (select nvl(sum(invoice_amount),0) from ap_invoices_interface aii where aii.load_request_id = :p_load_request_id and nvl(aii.status, 'REJECTED')= 'REJECTED') AS amount_ignored,

'Total number of errored invoices: ' || (select count(invoice_id) from ap_invoices_interface aii where aii.load_request_id = :p_load_request_id and aii.status = 'REJECTED') AS count_rejected,

'Total dollar amount of errored invoices: ' || (select nvl(sum(invoice_amount),0) from ap_invoices_interface aii where aii.load_request_id = :p_load_request_id and aii.status = 'REJECTED') AS amount_rejected

From dual

Where (select distinct aii.source from ap_invoices_interface aii where aii.load_request_id = :p_load_request_id ) is not null)

Select source_name columnsl1 from details

Union all

Select count_rcv from details

Union all

Select amount_rcv from details

Union all

Select count_loaded from details

Union all

Select amount_loaded from details

Union all

Select count_ignored from details

Union all

Select amount_ignored from details

Union all

Select count_rejected from details

Union all

Select amount_rejected from details

Step2: create a wsdl ExternalReportWSSService connection:

Follow below my blog for "create connection" part:

https://soalicious.blogspot.com/2021/05/oic-invoke-erp-bi-report.html

Step3: call the BI report from OIC Integration:

Subscribe the callback.

Check if summarystatus is succeeded.

Check if load status is succeeded.

Check if import status is succeeded.

Assign all the required params for BI call.

varReportPath: 'Custom/OIC_Integrations/ERP_ALL_CTRL_Report.xdo'

varParamName1: 'p_load_request_id'

VarParamvalue1: /nssrcmpr:onEvent/inp1:/ErpImportBulkDataEvent/inp1:JOBS[inp1:JOBNAME = 'Load Interface File for Import']/inp1:REQUESTID

varSizeOfDataChunkDownload: '-1'

Call the BI soap adpater connection.

Operation: runReport.

Map the required details.

Call a sftp adapter to store the bi report response 

Overall calback flow

For more details regarding BI call params . Follow my below page:

https://soalicious.blogspot.com/2021/05/oic-invoke-erp-bi-report.html