OIC - Invoke OTBI report in Oracle Integration and save the report in a ftp location.

Usecase: Here, we will invoke the OTBI report from OIC integration and save the xml report to a ftp location.

OTBI webservice:

https://<fusion_instance/analytics-ws/saw.dll/wsdl/v12?wsdl

Steps to follow:

1. First create a SOAP invoke connection using above wsdl with security user name and password.
2. Take a scheduled or app driven integration and create schedule params for Logon user, password etc.
3. Drag and drop created soap connection and configure for SAWSessionService >> logon operation >> provide logon user name and password >> it will give session id as output.
4. Drag and drop created soap connection and choose XMLViewService >> choose executeXMLQuery operation >> provide following information as input:
1. Session id , which we received from previous step.
2. Variable name value pair if any parameter to pass
3. Report path : which we will get form the report >> more >> properties >> Location and name. For example if location:  /shared/Custom and name: XXOTBIMultiParam then report path will be : shared/Custom/XXOTBIMultiParam
4. Execute options 
1. Async : true
2. Maxrowsperpage: 10
3. Refresh: false
4. PresentationInfo: false
5. Type: 1
5. We get the ouput in CDATA xml format as response
6. Take a stage and opaque schema to  write the xml string as a xml file. In mapper, map the encodeBase64 data of rowset
7. Take another stage and otbi response schema to read the xml file from the stage write filereference.
8. Configure ftp adapter and write the file using a target schema or csv file. 
9. Map the read xml contents to target xml file.

Note: we can also mention outputFormat as. SAWRowsetSchemaAndData if it is giving output format is missing error.

Detailed screnshots:

HCM/ERP - Invoke OTBI report webservice from SOAP UI Tool

Usecase: 

Here, we will see how to invoke an OTBI report webservice using SOAP UI Tool.

OTBI webservice:

https://<fusion_instance/analytics-ws/saw.dll/wsdl/v12?wsdl

Steps to follow:

1. Open soap ui tool and create a soap project using the above otbi wsdl.
2. Open SAWSessionService >> logon operation >> provide logon user name and password >> it will give session id as output.
3. Open XMLViewService >> choose executeXMLQuery operation >> provide following information as input:
1. Session id , which we received from previous step.
2. Variable name value pair if any parameter to pass
3. Report path : which we will get form the report >> more >> properties >> Location and name. For example if location:  /shared/Custom and name: XXOTBIMultiParam then report path will be : shared/Custom/XXOTBIMultiParam
4. We get the ouput in CDATA xml format

Detailed screenshots:

Reference:

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=4569601291952&parent=EXTERNAL_SEARCH&sourceId=HOWTO&id=2705306.1&_afrWindowMode=0&_adf.ctrl-state=9n4a27dyb_4

OIC OTBI report links

OTBI links:

1. ERP / HCM - BIP VS OTBI
   
2. ERP/HCM - Create OTBI report with parameter
   
3. HCM / ERP - Invoke OTBI webservice using SOAP UI tool
   
4. OIC - Invoke OTBI report in Oracle Integration and save the report in a ftp location.
   
5. OTBI - How to use OTBI analysis in BI publisher
   
6. OTBI - How to create OTBI dashboard report
   

ERP/HCM - Create OTBI report with parameter

Usecase: Here, we will create an OTBI Payable invoice report with a invoince number parameter named as P_INVOICE_NUM.

Implemented steps:

1. Navigation >> Tools >> Reports & Analytics >> browse Catalog >> Create >> Analysis 
2. Select Subject area >> Payables Invoices - Transactions Real Time
3. Select the required columns or fields from subject areas to selected columns sections
4. Go to the Results tab and see the output.
5. Go to Prompts tab >> create a variable prompt >> provide variable name as P_INVOICE_NUM >> user input as Choice List >> select invoice number as column >> ok
6. Go back to Criteria tab >> add filter >> Invoice Number >> value as Presentation Variable >>  put created Parameter name: P_INVOICE_NUM in Variable_Expr >> put a default value >> ok
7.  Go to the Results tab and see the output
8. Save the report
9. Go th catalog >>open report >> select value >> ok >> we can see the output.

Detailed screenshots: