Oracle Forums Community of Oracle Professionals including Fusion/Cloud Application Consultants, ERP Experts, Oracle Apps Functional Consultants, Apps DBAs, DBAs, Cloud DBAs, Technical Consultants, Fusion Middleware Experts, SQL, PL/SQL Developers and Project Managers. Welcome to OraERP.com Social Community, a friendly and active community of Oracle Technology Professionals who believe that technology can ‘make the world a better place’. By joining Oracle ERP Community you will have the ability to Post Topics, Receive our Newsletter, subscribe to threads and access many other special features. Registration is Quick and Simple.
Get unlimited access to Oracle Tutorials, Articles, eBooks, Tools and Tips by upgrading to "Premium Membership".
Oracle Training

Thread Rating:
  • 1 Vote(s) - 5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How Can I Use BI Publisher to Run SQL Against the Fusion Applications Database ?
#1
Hi I have written a post about How Can I Use BI Publisher to Run SQL Against the Fusion Applications Database ?

Please read and provide your feedback if this is useful. 

How Can I Use BI Publisher to Run SQL Against the Fusion Applications Database ?

I'm sure most of you must be wondering how can I run a SQL script in fusion applications because we don't have the direct access to Fusion application databases. 
Therefore I thought of sharing this very useful document on how to do that. I have used here the screenshots from Release 13 ! Which is awesome isn't it if you are wondering how the Release 13 GUI looks like !

Here are steps that you have been looking for to run the SQL scripts in fusion applications !!

In order to run a SQL query against the Fusion Applications Database the user is required to add the SQL to a BI Publisher SQL Data Model and then to execute the Data Model.
The process is a two stage one, the first stage being to create the data model containing the appropriate SQL and the second being to display the result set in a BI Publisher report.

Stage 1 - Creating the Data Model  


There are two approaches to access BI Publisher dependent on the starting point.The most straightforward approach is to directly access BI Publisher via its own URL, this being /xmlpserver. For users working in an existing Fusion environment this extension can be added to the existing Fusion Application host, for example :

If the Fusion Application is being accessed through a URL such as 'https://fs-aufsn4x0cxf.oracleoutsourcing.com/homePage' then the equivalent URL to access BI Publisher directly would be '
https://fs-aufsn4x0cxf.oracleoutsourcing.com/xmlpserver'
This can be seen in the screenshot below ;
[Image: Direct%2BBI%2BPublisher.JPG]
The second approach is to access BI Publisher via the Reports & Analytics link in the Fusion Applications Navigator as shown below :
[Image: Reports%2Band%2BAnalytics.JPG]

Once the user has access the Reports & Analytics link, clicking on Create > Report will take the user into Report creation in BI Publisher, cancelling this leaves the user in BI Publisher mode (the URL will reflect as ..../analytics/saw.dll?bipublisherEntry as you are working in BI Publisher implicitly) :

 [Image: Reports.JPG]

From this point on the user can then click on New > Data Model [Image: Datamodel.JPG]
[size=undefined]
The Data Model defines the SQL which we are going to issue. For the purposes of this document and the functionality detailed within it it may be useful to define and save a data model which you can then re-use to issue SQL statements as required. There is no need to create and save multiple data models, one for each SQL statement, unless you have an expectation that a given SQL statement may be issued regularly, in which case having a static data model for that statement may be useful. In this case we are going to create and save a dummySQLDataModel which we will use to issue our SQL statement.
The user will be taken to the default Data Model definition page. Click on the Data Set dropdown highlighted in the screenshot and select 'SQL Query' :
[Image: poheadersSQL.JPG]
Once the SQL is defined, click on OK, and the screen will then change to show a diagram and the fields which were included in the SQL statement :
[Image: SQLStatementfields.JPG]
Click on the 'View Data' link in the top right hand corner of the screen, set 'Rows' to a value like 200 (note this is the max value allowed in the Data Model), click on the 'View' button, and finally, select 'Table View', at which point the user will be presented with the result set :
[Image: Tabledata.JPG]
"You can use the Query Builder button to view all the tables and columns however; you have to select the FUSION schema rather than the FUSION_RUNTIME schema,    which is what the Query Builder defaults to."
Stage 2 - Viewing the full output
The second part of the process involves the creation of a report template which will then allow generation of the full set of output for the query being issued.
Continuing from the preceeding steps, assuming the output is as required the next step would be to save the data model which we can then use for the template creation later :
[Image: Saving%2Bthe%2BSQL%2BData%2BModel.JPG]
With the data model saved at a suitable location we should then perform  a 'View Data' should then be performed again and the output saved as sample data for the data model, this is then used to build the report template :

[Image: SaveAsSampleData.JPG]
Once the data is saved as Sample Data you can then click on 'Create Report' and will be taken to the Report Creation wizard, the data model you have just created will be shown, click on Next :
[Image: CreateReport.JPG]

Select a basic Landscape layout and Table output to aid presentation of the results, and then click Next :
[Image: ReportLayout.JPG]
A preview of the results will be displayed at which point you can adjust the layout and drag and drop columns from and to the table, and then click Next :
[Image: ReportPreview.JPG]

Select View Report and then click 'Finish' :
[/size]
Reply
Thanks given by:


Possibly Related Threads...
Thread Author Replies Views Last Post
  Oracle Fusion Financials Cloud Interview Questions and Answers eBook M Irfan 0 622 10-27-2017, 07:45 PM
Last Post: M Irfan
  Oracle Fusion Online Training in Canada & USA M Irfan 0 504 08-03-2017, 10:58 PM
Last Post: M Irfan
  Fusion Application Accrual Clearing Rules Hassanmalik 1 491 07-19-2017, 07:33 PM
Last Post: M Irfan
  Fusion Financials Receipt import through webservice Hassanmalik 1 484 05-24-2017, 07:50 PM
Last Post: Gordon Victor
  Fusion financial Cloud- AP Payment Term Service Hassanmalik 1 450 05-22-2017, 08:45 PM
Last Post: M Irfan
  Fusion Inventory: Default GL Accounts for an Org Hassanmalik 1 385 05-19-2017, 08:45 PM
Last Post: M Irfan
  Fusion GL Order of "journal lines" when uploaded from a spreadsheet Ahmad Mujeeb 1 373 05-16-2017, 09:33 PM
Last Post: M Irfan
  Oracle Fusion GL Beginning Adjusting periods Ahmad Mujeeb 1 283 05-15-2017, 10:21 PM
Last Post: M Irfan
  Oracle Fusion Financials ERD Database Table Structure Ahmad Mujeeb 1 698 05-09-2017, 10:49 PM
Last Post: M Irfan
  Fusion GL Upload budget and actual via Smartview Gordon Victor 1 324 05-09-2017, 08:37 PM
Last Post: M Irfan



Users browsing this thread: 1 Guest(s)
Get Premium Membership Get Oracle Tutorials, Tips! Post a Question!