Fusion BIP – Oracle Fusion BI Publisher Reports Training Manual

1)      Oracle Transaction Business Intelligence (OTBI):

OTBI is a robust real-time business intelligence reporting tool. It runs on Ad-hoc query. It uses the web services to communicate with database.  OTBI is user friendly tool which is very easy to use. OTBI reports can be developed by selecting the subject area and drag and drop of columns. Users are not allowed to add/delete/modify the subject area in OTBI. OTBI is available with Oracle Fusion Application. OTBI can be used attach reports on various dashboards on fusion application. All fusion application data security gets applied on OTBI reports.

2)      Oracle Business Intelligence Publisher (BI Publisher):

BIP is fully integrated as a component inside the OBIEE server(s) in Fusion Application. BIP is flexible business reporting tool. BIP is used to extract, format and deliver the data from fusion to third party system. User can create report using BIP by writing SQL query and then attaching a template to format the data from XML to consumable format. Different types of templates can be attached to BIP reports to transform the data format like e-text, RTF, Excel XSLT etc.

BIP is a combination of two objects:

a)       Data Model: It is used to write the SQL query to fetch the data from database based in XML format. Data model mainly comprises of data sets where user writes SQL query and select data source.

b)      Report: Reports are sued to format the data from data model like comma separated values, Excel, PDF etc. Report basically comprises of template like e-text, RTF Excel etc.

Creating the BIP Report with Data Model

1.       Log into the application and click on the Reports and Analysis.

Nav : Home àReports and Analysis

Please click on the below link for more details:

Fusion BIP – Oracle Fusion BI Publisher Reports Training Manual

https://drive.google.com/file/d/1WmvC744bJxTRQpcfNdVL6s8LIaJRnQCY

Please Like and Share our FaceBook page to get more details on Oracle Applications

https://www.facebook.com/pages/Oracle-Applications/712588392100243

Fusion OTBI - Oracle Fusion Transactional Business Intelligence Reports Training Manual

With Oracle Fusion Transactional Business Intelligence embedded analytics, role-based dashboards, and on-the- fly ad hoc reporting capabilities make data access and interpretation easier than ever before. Users are able to see updates in real-time, and their impact, through embedded analytics. For example, during the compensation budgeting process, a manager is able to see a running tally for reference right on the page. This eliminates the guesswork with dashboards that deliver in-line information while you do your work. Pre-delivered operational reports are enriched with robust and flexible ad hoc query capabilities that access real-time transactional data – no need to push data to a separate warehouse or engage the IT department for a custom report: it is easy, and right at your fingertips

Business Intelligence Composer

The Business Intelligence Composer is a simple-to-use wizard that allows organizations to quickly and easily create, edit, or view data without requiring an engineering background. With this capability Human Resource organizations can build and deliver reports that the business needs whenever they need it, without waiting for IT support. It is built for the business user.

Real Time Ad Hoc Query

Traditional ad-hoc reporting solutions require a person to have deep domain in their subject area and an understanding of data objects in the relevant tables they needed to report from.

OTBI hides that complexity and transforms the 9000+ reportable data objects into everyday business terminology. Which means if you are looking to see a worker’s average performance rating you will find the reporting object named as “Worker’s Average Performance Rating”.

This is all done by using the standard Oracle query and reporting tool (OBIEE). OBIEE has end user tools (Answers, Dashboards) to provide an easy-to-use interface for business users to perform current state analysis of their business applications. Constructed queries and reports are executed real time against the transactional schema supported by a layer of view objects.

In OTBI, business users can also easily drill on predefined hierarchies, enable action links, compose a dashboard, schedule a report or dashboard for delivery and export data or reports to a variety of file formats. Fusion users can also easily embed an OTBI report or dashboard in a Fusion page through Personalization

Integrated with Fusion Applications

OTBI is fully integrated with Fusion applications (roles, flex fields, lookups, trees). There is no need for additional implementation steps to map security or provide configuration inputs. OTBI needs minimum setup and is ready for use once Fusion Applications are configured and installed.

We are going to develop the Below OTBI reports

1. Navigating to and Viewing a Standard Report

2. Creating Custom Report with Wizard Template

3. Creating Custom Report with Custom Template

4. Creating Analysis Reports for Suppliers

5. Creating Ad Hoc Reports for Expenses

1. Navigating to and Viewing a Standard Report

Select Reports and Analytics under Tools in the Navigator.

Please click on the below link for more details:

Fusion OTBI - Oracle Fusion Transactional Business Intelligence Reports Training Manual

https://drive.google.com/file/d/1cZ5HTXDZqC4gvN7DzFZcXqbXHZ4OfmnQ

Please Like and Share our FaceBook page to get more details on Oracle Applications

https://www.facebook.com/pages/Oracle-Applications/712588392100243

Date Functions in Oracle XMLP Reports

SQL/XSL	Usage	Description
to_date	<?xdofx:to_date ( char [, fmt [, 'nlsparam']])	TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype. Thefmt is a date format specifying the format ofchar. If you omit fmt, then char must be in the default date format. If fmt is 'J', for Julian, then char must be an integer.
sysdate()	<?xdofx:sysdate()?>	SYSDATE returns the current date and time. The datatype of the returned value is DATE. The function requires no arguments.
current_date()	<?xdoxslt:current_date($_XDOLOCALE,$_XDOTIMEZONE)?> Example: <?xdoxslt:current_date('ja-P','Asia/Tokyo')?>	Returns the current date in "yyyy-MM-dd" format in the given locale and timezone. This function supports only the Gregorian calendar.
current_time()	<?xdoxslt:current_time($_XDOLOCALE,$_XDOTIMEZONE)?> Example: <?xdoxslt:current_time('ja-JP', 'Asia/Tokyo')?>	Returns the current time in the given locale and timezone. This function supports only the Gregorian calendar.
date_diff	<?xdoxslt:date_diff(‘y', ‘YYYY-MM-DD’, ‘YYYY-MM-DD’,$_XDOLOCALE,$_XDOTIMEZONE)?>	This function provides a method to get the difference between two dates in the given locale. The dates need to be in "yyyy-MM-dd" format. This function supports only the Gregorian calendar. The syntax is as follows: <?xdoxslt:date_diff(‘format’,‘YYYY-MM-DD’, ‘YYYY-MM-DD’,$_XDOLOCALE, $_XDOTIMEZONE)?> where format is the time value for which the difference is to be calculated. Valid values are : ·         y - for year ·         m - for month ·         w - for week ·         d - for day ·         h - for hour ·         mi - for minute ·         s - for seconds ·         ms - for milliseconds Example: <?xdoxslt:date_diff(‘y’,‘2000-04-08’, ‘2001-05-01’,$_XDOLOCALE, $_XDOTIMEZONE)?> returns 1 Example: <?xdoxslt:date_diff(‘m’,‘2001-04-08’, ‘2000-02-01’,$_XDOLOCALE, $_XDOTIMEZONE)?> returns -14 Example: <?xdoxslt:date_diff(‘d’,‘2006-04-08’, ‘2006-04-01’,$_XDOLOCALE,‘America/Los_Angeles’)?> returns -7
sec_diff	<?xdoxslt:sec_diff(‘2000-04-08T20:00:00’,‘2000-04-8T21:00:00’, $_XDOLOCALE, $_XDOTIMEZONE?>	This function provides a method to get the difference between two dates in seconds in the given locale. The dates need to be in "yyyy-MM-dd'T'HH:mm:ss". This function supports only Gregorian calendar. Example: <?xdoxslt:sec_diff(‘2000-04-08T20:00:00’,‘2000-04-08T21:00:00’, $_XDOLOCALE,$_XDOTIMEZONE?> returns 3600
get_day	<?xdoxslt:get_day(‘2000-04-08’,$_XDOLOCALE)?>	This function provides a method to get the day value of a date in "yyyy-MM-dd" format in the given locale. This function supports only the Gregorian calendar. Example: <?xdoxslt:get_day(‘2000-04-08’, $_XDOLOCALE)?> returns 8
get_month	<?xdoxslt:get_month(‘2000-04-08’,$_XDOLOCALE)?>	This function provides a method to get the month value of a date in "yyyy-MM-dd" format in the given locale. This function supports only the Gregorian calendar. Example: <?xdoxslt:get_month(‘2000-04-08’,$_XDOLOCALE)?> returns 4
get_year	<?xdoxslt:get_year(‘2000-04-08’,$_XDOLOCALE)?>	This function provides a method to get the year value of a date in "yyyy-MM-dd" format in the given locale. This function supports only the Gregorian calendar. Example: <?xdoxslt:get_year(‘2000-04-08’,$_XDOLOCALE)?> returns 2000
month_name		This function provides a method to get the name of the month in the given locale. This function supports only the Gregorian calendar. The syntax for this function is: <?xdoxslt:month_name(month, [abbreviate?], $_XDOLOCALE)?> where month is the numeric value of the month (Januany = 1) and [abbreviate?] is the value 0 for do not abbreviate or 1 for abbreviate. Example: <?xdoxslt:month_name(12, 1,‘fr-FR’)?> returns dec. Example" <?xdoxslt:month_name(1, 0, $_XDOLOCALE)?> returns January

Setup Remittance Advice Email Notification PDF Attachment for Suppliers

1. Login using the SYSADMIN user
2. Responsibility: Workflow Administrator Web ApplicationsàAdministrator Workflow àBusiness Events.
3. Search Criteria  = oracle.apps.ap.payment ,

4. Click Go.
5. Click the "Subscriptions" icon for the Payment Event.
6. Click the Edit icon for ap_payment_event_wf_pkg.rule_function
7. Change the Status from "Enabled" to "Disabled" in Execution Condition Region.
8. Click Apply.

Configure BI (XML) Publisher Separate Remittance Advice (SRA) Delivery.

1. Create the instance specific xdodelivery.cfg file

<?xml version='1.0' encoding='UTF-8'?>

<config xmlns="http://xmlns.oracle.com/oxp/delivery/config">

<!-- ======================================================== -->

<!-- servers section -->

<!-- List your pre-defined servers here. -->

<!-- ======================================================== -->

<servers>

     <server name="myprinter1" type="ipp_printer" default="true">

          <uri>ipp://myprinter1.oracle.com:631/printers/myprinter1</uri>

     </server>

     <server name="myprinter2" type="ipp_printer" >

          <host>myprinter2.oracle.com</host>

          <port>631</port>

          <uri>ipp://myprinter2.oracle.com:631/printers/myprinter2</uri>

          <authType>basic</authType>

          <username>xdo</username>

          <password>xdo</password>

     </server>

     <server name="myfax1" type="ipp_fax" default="true" >

          <host>myfax1.oracle.com</host>

          <port>631</port>

          <uri>ipp://myfax1.oracle.com:631/printers/myfax1</uri>

     </server>

     <server name="mysmtp1" type="smtp_email" default="true">

          <host>myprinter1.oracle.com</host>

          <port>25</port>

     </server>

     <server name="mysmtp2" type="smtp_email" >

          <host>mysmtp12.oracle.com</host>

          <port>25</port>

          <username>xdo</username>

          <password>xdo</password>

     </server>

</servers>

<!-- ======================================================== -->

<!-- properties section -->

<!-- List the system properties here. -->

<!-- ======================================================== -->

<properties>

     <property name="ds-temp-dir">/tmp</property>

     <property name="ds-buffering">true</property>

</properties>

<!-- ======================================================== -->

<!-- channels section -->

<!-- List the custom delivery channels here. -->

<!-- ======================================================== -->

<channels>

     <channel name="file">oracle.apps.xdo.delivery.file.FileDeliveryRequestFactory</channel>

</channels>

</config>

The Separate Remittance Advice will use the e-mail host, printer and fax details which are set up in this file.

Note that the SRA log file will show following error with above xdodelivery.cfg example:
   oracle.apps.xdo.delivery.MissingRequiredPropertyException: Required property missing. Property name :[HOST]

2. Save the xdodelivery.cfg into the $XDO_TOP/resource directory.
$XDO_TOP/resource directory will need to be created in case it does not exist already.

3. Set the Profile Option IBY: XML Publisher Delivery Manager Configuration File

Ensure the filename xdodelivery.cfg is in lowercase and include the full path and file name in the Profile Setting

Note: Do not use environment variables or relative paths while specifying the location of xdodelivery.cfg file in the profile option IBY_XDO_DELIVERY_CFG_FILE. Please use ABSOLUTE path instead.

4. Configure Profile options
IEX: SMTP From
Should Specify the E-mail From Address

IEX: SMTP Host
Should Specify the E-Mail host.

5.  Login to the application.
Responsibility = Application Developer
Navigate : Application -> Messages

Query
IBY_FD_SRA%

Two Messages are returned.
IBY_FD_SRA_EMAIL_FROM
The Seeded message is...
account_payables@visioncorp.com

and the other
IBY_FD_SRA_EMAIL_SUBJ
The Seeded message is...
Separate Remittance Advice: payment reference number - &PMT_REF_NUM

IBY_FD_SRA_EMAIL_BODY -  This is for configurable message body and this will
go as inline content for emails that have output attached as a pdf.

IBY_FD_SRA_ATTACHMENT - This value is by-default set to FALSE and should be
set to TRUE for using the new enhancement, if this is set to TRUE then the
output will be sent as PDF attachment, else the same behavior(inline content)
is retained.

Modify those messages to meet your requirement.

6. Configure the Separate Remittance Advice Delivery Method at Supplier and Supplier Site. You also specify the e-mail address for the supplier if you want to e-mail the remittance advice.

1. Go into the responsibility: Payables

2. Navigate to MENUPATH > Supplier > Entry

3. Query supplier

4. Click on Payment Details

5. In Supplier Sites region click on 'Update Payment Details'

6. Go to tab Separate Remittance Advise Delivery

7. Select 'E-mail' from Delivery Method LOV

8. Enter E-Mail adress

9. Save

10.Run query below:

select payee_party_id, REMIT_ADVICE_DELIVERY_METHOD, REMIT_ADVICE_EMAIL

from iby_external_payees_all

where payee_party_id = <payee_party_id>;

7. Specify the Separate Remittance Advice Format when configuring the Payment Process Profile.
Reporting Tab
Format - Specify The Remittance Advice Format you wish to use for this PPP Automatically Submit at Payment Completion Point - Check the Box for Automatic Submission
Allow Multiple Copies for Payment Instruction - Check The Box if you want the user to be able to print more than once.
Condition

All Payments or Number of Documents or Payment Detail Length - You may Choose to Create a Remittance Advice Under Certain Conditions Only
Delivery Method
E-mail or Fax or Printed - The Default for the PPP of how the Remitance Advice will be delivered
Override Payee Delivery Method Preference - Check the box if the PPP Delivery Method takes precedence over the Delivery Method configured for the Supplier Site.
 

Note : Apply the patch (R12.2 20526954:R12.IBY.C) to send the Remittance Advice as Email attachment in PDF