ERP - How to Use OTBI Analysis in BI Publisher

Usecase: Here. We will see how to use OTBI analysis report SQL query in BIP republisher report.

Steps followed:

1. Create OTBI analysis tabular report using Payable Invoices - Transactios Real time subject area. >> select few columns like Invoice Number, Invoice date, Invoice Type Name, Invoice amount, Supplier name >>Advanced tab >> copy SQL issued.
2. Create a data model >> add data set using SQL Query fetched from step1.  Data source is Oracle BI EE>> add a where clause and add P_Supplier_name parameter. >> add alias and display names for all the columns. 
3. Data tab >> view data , we can see parameter tag also showing >> go to properties and deselect "Include Parameter Tags" and select "include empty Tags for Null Elements." Save as sample data
4.  Create report >> select the columns >> customize report >> delete header part >> save >> view report

Detailed screenshots:

OTBI - Create OTBI dashboard report

Usecase: We will create an OTBI dashboard report where we can view Invoice details based on supplier data.

Steps:

1. Create a tabular demo_ap_analysis report (New >> Analysis >> select Subject Area >> Payables Invoices - Transactions Real Time >> select fields like Invoice Number, Invoice date, Invoice Description, Invoicr Amount, Supplier >> Add a supplier filtet as prompted.
2. Create a Dashboard prompt( New >> Dashboard Prompt >> select Subject Area >> Payables Invoices - Transactions Real Time >> select Column prompt >> add supplier >> option >> include all cloumns values choice in the list
3. Create a Dashboard >> select created dashboard prompt >> select Demo_ap_analysis report
4. Save and run

Detailed Screenshots:

Create invoice tabular report

Create dashboard prompt

Create Dashboard

OIC ERP - How to enable Oracle ERP Cloud Business Events | Subscribe Business Events Raised within ERP Cloud

Usecase: 

Here. We will see different options how we can enable Oracle ERP Cloud Business Events.

• Oracle ERP Cloud Business events is one of the core features that all applications is interested in to get the real time data from ERP.
• Oracle integration has the declarative support to subscribe the Oracle ERP Cloud busienss events using the Oracle Cloud adapter.
• For Expenses / Financial Common Module / GL / Receivable / Revenue Management Business events are enabled using Rest API , but for Payables and Payments there is a facility to do it from Front end.

Method1: Using REST API:

Get all Business events records:

GET

https://<erp instance>/fscmRestApi/resources/11.13.18.05/erpBusinessEvents

Update the Enabled Indicator for a Business Event

PATCH

/fscmRestApi/resources/11.13.18.05/erpBusinessEvents/<ErpBusinessEventId>

{

    "EnabledFlag": true

}

Reference:

https://docs.oracle.com/en/cloud/saas/financials/24b/farfa/Enable_Business_Events.html

https://docs.oracle.com/en/cloud/paas/integration-cloud/erp-adapter/oracle-erp-cloud-adapter-capabilities.html

Method 2: From ERP console Front End :

Go to Setup and Maintenance > Find Manage Administrator Profile Values > Search Enable Business Events >> select AP event >> toggle the profile value from No to Yes.

Here, we will test AP - Invoice created events.

Notes: Roles needed :-

• Application Implementation Consultant
• Financial Administrator

Subscribe from OIC:

First create ERP connection:

https://soalicious.blogspot.com/2023/03/oic-create-erp-cloud-adapter-connection.html

Then create an appdriven orchestration integration >> add configure ERP adapter >> Choose Receive Business Events raised within Cloud ERP >> selecting Payable invoice created event.

Create an invoice from ERP:

Payables >> invoices >> create invoice >> add header and line details.

Monitoring: we have received the event raised from ERP

We can check Subscriptions using below Rest API:

https://<Your_fusion_host_name>/soa-infra/PublicEvent/subscriptions

Run "Retrieve Latest LDAP Changes" to reflect the changes

Reference:

https://dobbylearns.wordpress.com/2021/06/25/enable-business-events-for-payables-business-events-payments-in-oracle-fusion-finance-cloud/

ERP ESS - ESS job with parameters using value set

Usecase: Here, we will create a value set for invoice source and then link to ess job parameter and then run the ess job to invoke the BI report.

Steps to follow:

1. Tools >> Reports and Anlytics >> browse catalog >> create data model >> create two LOVs for source and invoice number >>create 2 parameters and link to the LOVS >> Create data set >> go to data tab and test >> save as sample data >> create report
2. My enterprise >> Setup and Maintenance >> golbal search with Manage value Sets >> Create & Manage Value set : XXSOURCE_VS  , module: Application Toolkit>> add values like ISP, ERS. Same repeat for ISP and ERS.
3. Search with Manage Enterprise Schedule >> Manage List of Values sources >> application name: application Toolkit , LOV source name: ValueSetPVO, LOV Source definition name: oracle.apps.fnd.applcore.flex.vst.model.publicView.ValueSetValuePVO
4. Manage Job Definitions >> provide ESS job details. >>create paramter P_source and assign page element as Choice list. LoV source : ValueSetPVO
5. Link the valusset with the parameter Source. Default value: 'XXSOURCE_VS'. And mapped parameter as P_source for p_invoiceNum
6. Tools >> Scheduled Processes >> schedule new process >> XXDEMO_ESS_JOB1 TAB >>> select values and submit

Detailed screenshots:

Full LOV Source Definition name: