ERP BI REPORT - XSLT vs BIP Tags | BI publishers

XSLT vs BIP Tags:

• Simple BI Publisher RTF template can be developed with BIP tags. BIP Tags are easier to use even who does not have XML or XSLT experience.
• Behind the scene, BIP tags are translated to XSL code at runtime.
• To develop rich functionality such as Charts, Graps XSLT is used, wizards can be used to define simple charts and graps but to customize the only option is XSLT coding.
• XSLT and BIP Tags can co-exist in the RTF template. Custom functions can be created in XSL template and can be invoked from RTF template.

Example of XSL :

<OrderNum>

  <xsl:value-of select="/Data_DS/G_1/Order_Number"/>

</OrderNum>

<xsl:for-each select="DATA_DS/G_1">

  <SalesOrder>

    <OrderNum>

      <xsl:value-of select="Order_Number"/>

    </OrderNum>

  </SalesOrder>

</xsl:for-each>

Example of BIP tags :

<OrderNum>

  <?/Data_DS/G_1/Order_Number?>

</OrderNum>

<?for-each select="DATA_DS/G_1"?>

  <SalesOrder>

    <OrderNum>

      <?Order_Number?>

    </OrderNum>

  </SalesOrder>

<?end for-each?>

ERP - Create BI reports with Parameters and Dependent LOVs

Usecase: Here, we will create a BI report with parameters and also create dependent LOVs. We will fetch invoice_num,source and description from ap_invoices_all table using p_source and p_inv_num parameters and here, LOV of invoice num will be depended on the parameter p_source.

logic steps:

• Create 2 parameters - p_source and p_inv_num which will be pointing to the following 2 LOVs
• p_source => source_lov
• p_inv_num => inv_num_lov
• Create 2 LOVs where inv_num_lov depended on p_source
• source_lov = select distinct source from ap_invoives_all
• inv_num_lov = select invoice_num from ap_invoices_all where source =:p_source
• Create data set using sql query
• Select invoice_num,source, description from ap_invoices_all where source=nvl(:p_source,source) and invoice_num=nvl(:p_inv_num,invoice_num)
• Create report based on this data model.

Detailed steps with screenshots:

Navigation >> Tools >> Reports and Analytics >> Browse Catalog >> create >> Data Model

Parameters >> Create 2 parameters(initially with text type)

List of Values >> create 2 LOVs,

Parameters >> update paramter type with Menu and select the respective LOVs.

Data sets >> Click + sign >> SQL Query

Provide Name, data source and SQL query.

Click data

Select the source and dependend invoice >> view

Note: if you want to create custom RTF template, export the data. 

Save as sample data >> save data model

Create report

Cancel >> select Data model >> generate RTF

Save the report name

Save Report >> View Report

Test

We can also export data.

Oracle Cloud ERP links

Followig blogs are related to ERP technical activities like BIP/OTBI, ESS, FRS, SmartView etc.

1. ERP BI Roles & Configurations
   
2. How to create a simple BI publisher Report | get a sequence number using bip report
3. ERP - Create BI reports with Parameters and Dependent LOVs
4. ERP - BI Report Output Export | Send to UCM | Send as Email Attachment
5. ERP BI Report Migration
6. ERP Secure BI report
7. ERP BI - Create Master details BI report using Group Link
   
8. ERP BI - Create Master details BI report using element link
   
9. ERP BI - create master details bi report using subgroup
   
10. ERP BI - How to Print Barcode in BI report
    
11. ERP BI - How to secure your pdf BI report output using static method
    
12. ERP BI - How to secure your pdf bi report | Dynamic method
    
13. RTF templates related:
1. ERP - BI publisher desktop tool installation and Enable add Ins the word | RTF Template
2. How to create BI report in Oracle ERP using RTF template
3. ERP BI - Case study 1 - Create RTF Template
4. ERP BI - Case Study 2 - Create RTF template in MS WORD BI Publisher
5. ERP BI- Case Study 3 - create RTF template fot master detail bi report
   
14. Create BI report using eText template
    
1. ERP BI - Case study 1 - Create EText EFT template based BI report | Create etext fixed position based BI Report
2. ERP BI - Case study 2 - Create Etext fixed postion based BI report
3. ERP BI - Case Study 1 - Create EDI template based BI report | Create Delimiter based report
4. ERP BI - Case Study 2 - Create ERP BI report using ETEXT template
15. BI Excel template
1. Create BI report using Excel template
16. BI XSL template
1. Create ERP BI report text output using XSL template
2. Create ERP BI report xml output using XSL template
3. Create ERP control file BI report using XSL template
17. ERP BI REPORT - XSLT vs BIP Tags | BI publishers
18. BI Bursting:
1. About ERP BI Bursting
2. ERP BI Bursting using FTP delivery channel
3. OIC - ERP - BI report bursting using email delivery channel
4. Oracle fusion ERP - Multi file bursting | Send Multiple files by using Bursting
   
5. Oracle fusion ERP - Multi file bursting part2 | Send Multiple files by using Bursting
   
19. OTBI reports:
1. OIC - OTBI report creation | Invoke the otbi report from Oracle integration | Invoke otbi report webservice from soap ui tool
20. ESS jobs:
1. Create first ESS job on BI Report | ERP Cloud
2. ERP ESS - Securing an ESS Job | Grant priviledge to user to submit an ESS job
   
3. ERP ESS - ESS job with parameters using Lookup
   
4. ESS job with parameters using value set LOV
   
5. SOAP UI - ERP - download ESS job output and logs using erpIntegrationService from SOAPUI
21. ERP how to test sql query in OTBI | BI Publishers
22. ERP BI sql query to fetch output based on ESS Request History table last run date | Fetch Process start from ESS_Request_history table
23. ERP - How to stop auto run of a parameterized BI report
24. Oracle Cloud ERP - Fix for SQL query time out error in BI reports | How to increase SQL timeout for BI reports
    
25. ERP - About Oracle Payable Invoices
    
26. ERP - Import AP Invoice using FBDI files from ERP scheduled Processes
    
27. Oracle Cloud ERP - Create an Integration user with adequate roles and Priviledges | Assign Required roles to an Integration User
    
28. ERP - Register the CSF key | subscribe to Oracle Fusion Cloud | Subscribe to Fusion Business Events and FBDI Callback
    
29. Oracle Cloud ERP : Missing Scheduled Processes function while creating an integration user
    
30. Oracle Cloud ERP - What are the roles required to create data model in Oracle Fusion
31. Oracle ERP - Supplier callback BI report SQL query
    
32. SOAP UI - ERP - Run BI report using ExternalReportWSSService from SOAPUI
    
33. ERP Custom folder is not showing under shared folders in reports and analytics section | how to get the shared folders access using permission
    
34. OIC - ERP - Custom callback BI report | What we can do after the FBDI import
    
35. Oracle ERP - Supplier interface, base and error table details.
    
36. Oracle Payable invoice interface, base and error table details.
    
37. Oracle ERP - what roles required to show supplier under procurement section
    
38. Supplier import failed due to supplier type is inactive
    
39. ERP BI - Report service - How to call BI report using reportservice from Soap ui tool
    
40. Oracle Fusion ERP webservices and operations
    

ERP - BI Roles and Configurations

BI Roles & Configuration:

The user should have BI abstract role, attached with "Manage reports & analytics function security policy" priviledge and BI administration role. 

To configure follow below steps:

Navigator >> Tools >> Security console

Create a Role like XXDEMO_BI_ROLE where role category as BI - Abstract Roles.

Add function security policy as Manage Reports & Anlytics.

Add role membership to BI admin.

Add user to the created role.

Verify whether the user has assigned all the roles.

Users >> search user

To synchronizes users, roles and role grants with definitions in LDAP.

Navigator >> Tools >> Scheduled Processes

Schedule New process

Search Retrive Latest LDAP Changes.

Submit

We can see the job is running and will succeed and some time.

Now we are ready to create  or manage BI reports.

To create report;

Navigation to BI reports:

Navigator >> Tools >> Reports & Analytics >> Browse Catalog

Here we can see 2 folders:

My Folders: Reports for a particular user, is kept and not visible to any other user.

Shared folders: reports saved in this folder, will be visible to all users having accesa to Reports and Analytics.

Following roles need to show data model under Reports & Analytics:

https://soalicious.blogspot.com/2023/03/oracle-cloud-erp-what-are-roles.html

OIC - Twilio adapter - send SMS / WhatsApp from Oracle Integration

UseCase: Using Twilio adapter, we can send SMS / whatsapp from OIC. Here, we will send a sms and whatsapp messages as well using Twilio adapter.

Implementation detailed steps:

First step: Get a Twilio Trial account from Twilio.com . Then a Trial number - this will be used to send messages

Messaging >> get Setup >> create messageing service.

Second Step: Create a Twilio connection

My first Twilio account >> gather account sid, Auth token , required for OIC twilio connection.

Also take my twilio phone number which will be used as sender number.

Third step: Create an Integration and invoke Twilio adapter

Exposing a rest adapter to receive sender , receiver and sms details and send a status .

Select created twilio adapter and configure.

Select operation as Send SMS or MMS.

Complete the mapping 

Add tracking, save and activate and test.

Sms on phone:.

Fifth step: Add verified called Ids from Twilio trial account

For send Whatsapp messages, follow the below steps:

Twilio account >> Messaging >> Try it out >> Send a WhatsApp Message

Activate your sandbox

Save the number provided in the screenshot in your mobile device and write below code to connect:

Join <Sandboxname>

My case: Join page-giant

Sandbox is connected.

Test the same sms integration with "whatsapp:" prefix before the sender and receiver numbers as below:

Bang! We have received the whatsapp message.

To disconnect the sandbox, just write "stop".

Reference:

http://niallcblogs.blogspot.com/2020/12/820-oic-twilio-adapter.html