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

Oracle Cloud – Supplier FBDI Interface, Base Table, Error Table Details

Reference:

http://oratrainings.com/oracle-cloud-supplier-fbdi-interface-base-table-error-table-details/

Entity	UCM	Import Process	Interface Table	Base Table/View
Suppliers	prc/supplier/import	Import Suppliers	POZ_SUPPLIERS_INT	POZ_SUPPLIERS
Supplier Addresses	prc/supplier/import	Import Supplier Addresses	POZ_SUP_ADDRESSES_INT	POZ_SUPPLIER_ADDRESS_V
Supplier Site Import	prc/supplier/import	Import Supplier Sites	POZ_SUPPLIER_SITES_INT	POZ_SUPPLIER_SITES_ALL_M
Supplier Site Assignments	prc/supplier/import	Import Supplier Site Assignments	POZ_SITE_ASSIGNMENTS_INT	poz_site_assignments_all_m
Supplier Contacts	prc/supplier/import	Import Supplier Contacts	POZ_SUP_CONTACTS_INT POZ_SUP_CONTACT_ ADDRESSES_INT	poz_supplier_contacts

Oracle Cloud ERP - Payable invoice FBDI interface, Error and Base tables

Here, we will share the details for different ERP tables used for Payable invoice FBDI import.

As follows:

Entity	UCM	Import Process	Interface Table	Base Table/View
Headers	fin/payables/import	Import Payables Invoices	AP_INVOICES_INTERFACE	AP_INVOICES_ALL
Lines	fin/payables/import	Import Payables Invoices	AP_INVOICE_LINES_INTERFACE	AP_INVOICE_LINES_ALL
			*Interface table not there**	AP_INVOICE_DISTRIBUTIONS_ALL
Error Table				AP_INTERFACE_REJECTIONS

ERP - Sample supplier callback payload from Oracle cloud erp

Sample supplier callback:

<ns01:onEvent xmlns:ns01="http://xmlns.oracle.com/cloud/adapter/erp/receiveSuppliersCallbackSD_REQUEST/types">

  <ns0:ErpImportBulkDataEvent xmlns:ns0="http://xmlns.oracle.com/apps/financials/commonModules/shared">

  <ns0:SUMMARYSTATUS>SUCCEEDED</ns0:SUMMARYSTATUS>    <ns0:DOCUMENTID>20208094</ns0:DOCUMENTID>

    <ns0:JOBS>

      <ns0:CHILD>      <ns0:JOBPATH>/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader</ns0:JOBPATH>       

<ns0:REQUESTID>20948508</ns0:REQUESTID>

       <ns0:JOBNAME>Transfer File</ns0:JOBNAME>

        <ns0:STATUS>SUCCEEDED</ns0:STATUS>

      </ns0:CHILD>

      <ns0:CHILD>

        <ns0:JOBPATH>/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader</ns0:JOBPATH>

        <ns0:REQUESTID>20948509</ns0:REQUESTID>

        <ns0:JOBNAME>Load File to Interface</ns0:JOBNAME>

        <ns0:STATUS>SUCCEEDED</ns0:STATUS>

      </ns0:CHILD>

      <ns0:JOBPATH>/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader</ns0:JOBPATH>

      <ns0:REQUESTID>20948507</ns0:REQUESTID>

      <ns0:JOBNAME>Load Interface File for Import</ns0:JOBNAME>

      <ns0:DOCUMENTNAME>PozSuppliersInt2023-03-10T07:31:44.756Z.zip</ns0:DOCUMENTNAME>

      <ns0:STATUS>SUCCEEDED</ns0:STATUS>

    </ns0:JOBS>

    <ns0:JOBS>

      <ns0:JOBPATH>/oracle/apps/ess/prc/poz/supplierImport</ns0:JOBPATH>

      <ns0:REQUESTID>20948510</ns0:REQUESTID>

      <ns0:JOBNAME>Import Suppliers</ns0:JOBNAME>

      <ns0:STATUS>SUCCEEDED</ns0:STATUS>

    </ns0:JOBS>

    <ns0:JOBS>

      <ns0:JOBPATH>/oracle/apps/ess/financials/commonModules/shared/common/interfaceLoader</ns0:JOBPATH>

      <ns0:REQUESTID>20948512</ns0:REQUESTID>

      <ns0:JOBNAME>Upload Interface Error and Job Output File to Universal Content Management</ns0:JOBNAME>

      <ns0:STATUS>SUCCEEDED</ns0:STATUS>

    </ns0:JOBS>

    <ICSFile xmlns="http://xmlns.oracle.com/cloud/ics/file/v1/types">

      <FileReference>file:/5dc58116-b7eb-4275-82b8-4cb202ee6dc5@_@FWuOVOF4UhKKvvPWIeKnwpHZg3AqNgjOdJL7GmRMald1FkKH0AU2ieloHd8JJgbKOkUK0TIag6REcUqA2J81kw==</FileReference>

    </ICSFile>

  </ns0:ErpImportBulkDataEvent>

</ns01:onEvent>

Oracle Cloud ERP - which role required to show suppliers section under procurement

Usecase: Here, we will discuss which roles are required to be able to create suppliers or to show suppliers section under procurement.

Resolution:

User with "Supplier Administrator" role is able to access Supplier link in the Navogator but the create supplier option is not available in the Tasks ot Actions. User should have "Supplier Manager" role.

Note: we can add "account Payable Manager" role for payable invoice section.

Steps to add role to the assigned user:

Navigation >> Tools >> Security console >> users >> search user and click the user >> edit >> add role 

Added " account Payable Manager" role for payable invoice section.

OIC - ERP - Supplier import failed due to Supplier type is inactive or invalid | VENDOR_TYPE_LOOKUP_CODE

Usecase: We were trying to import a supplier to oracle cloud ERP and we have observed its failed due to the follow error : 

• Rejection Remarks: " Supplier  Type is inactive or invalid " 
• Attribute: VENDOR_TYPE_LOOKUP_CODE
• Value: Supplier

Here, we will show you how to overcome this issue.

Error details:

Navigation >> Tools >> Scheduled Processes >> Import Suppliers in error

ERP setup / configuration:

Home Navigation >> others >> setup and maintenance >> setup : Procurement >> Functional areas: Suppliers >> Manage Supplier Type Lookup >> enable the supplier code which is required(in my case i have enabled supplier).

Success import status:

Navigation >> Tools >> Scheduled Processes >> Import Suppliers as succeeded.

We can also track imported supplier

Procurement >> Suppliers >> Manage Suppliers >> search with Supplier number or other details.

OIC - Create an ERP cloud adapter connection in oracle integration

Usecase: Here, we will show you how to create an ERP cloud connection in OIC instance.

Steps:

OIC instance >> Integrations >> Connections >> Search Oracle Cloud ERP >> provide connection name >> provide connection details(ERP Host, Security policy as Username password token)

Test >> save