Downloading FBDI Template

 FBDI stands for File Based Data Import

FBDI can be used to load data in large volumes.

It is an offline application.

To get an FBDI template, follow these steps

Search for Oracle Fusion Financials FBDI templates > open the following link

Scroll down to required one, consider for AP_Invoices

For AP_Invoices, select Payables Standard Invoice Import. Highlighted .xlsm file is required fbdi template, download it.

If you scroll bottom to the page,

   we can see scheduled process and Interface Tables used in this load.

Differences between File Adapter and FTP Adapter in OIC Integrations

 Following are the differences between File and FTP Adapter

FTP Adapter:

We can create encrypted by using PGP

We can use decryption using PGP

For Trigger connection requires connectivity agent or else use a scheduled orchestration which can use ftp as invoke connection.

Supports Invoke connection

Supports Read, Write, Read in Segments, List, Download, Move, Delete operations

Can be used for FTP or SFTP server except if they are private requires Connectivity agent.

File Adapter:

We cannot create an encrypted file 

We cannot create a decrypted file

Supports trigger connection

Supports Invoke connection as well.

Supports only Read and Write operations

Requires OIC Connectivity agent

Printing parameter values in Excel Template when All is selected

 Hi Everyone,

In this post let us see how we can show values in Excel Output when parameter value is empty.

We dont get any value in parameter when we select All values in the Report and in the report All is linked with Null Values.

Let us see sample XML

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

<DATA_DS><BU/>

<G_1>

<INVOICE_NUMBER>1</INVOICE_NUMBER><BUSINESS_UNIT>test1</BUSINESS_UNIT>

</G_1>

</DATA_DS>

If we see above xml data BU tag dont have any value.

Let us see how we can we handle this in Excel Template.

<xsl:choose>

        <xsl:when test="DATA_DS/BU!="">

            <xsl:value-of select="DATA_DS/BU"/>

        </xsl:when>

        <xsl:otherwise>Print required value

        </xsl:otherwise>

</xsl:choose>

Above code checks if BU value not equal to Null.

 <xsl:when test="DATA_DS/BU!="">

If it is not equal to Null, then prints Bu value

<xsl:value-of select="DATA_DS/BU"/>

If it is Null, then print whatever value we need

<xsl:otherwise>Print required value

oracle.xdo.servlet.data.dataexception: your data is more than 65531 rows in a sheet, that exceeds Microsoft Excel (.xls) limitation

Hi Everyone,

 oracle.xdo.servlet.data.dataexception:oracle.xdo.servlet.data.dataexception:oracle.xdo.XDORuntimeException: your data is more than 65531 rows in a sheet, that exceeds Microsoft Excel (.xls) limitation. Stop Processing. Suggest to turn on Excel Template Scalable Mode to true from Runtime Properties

If you see this issue while generating Excel output in BIP Report, it is due to the reason that you have large amount of data in your report. In an Excel sheet, it can accommodate only upto 65531 records, if more that count of data comes this issue arises.

Solution to this problem:

Go to the concerned Report and open properties

Formatting

Excel Templates > Enable Scalable Mode. Set this to True

Now go and check if report is working for large data. If you check the report output, we can see data comes in multiple sheets.

AP Payments Query

Basic AP Payments Query:

This query contains most of the fields in Payments header screen.

Select

aca.check_number payment_number,

aca.vendor_name payee,

aca.check_date payment_date,

aca.status_lookup_code status,

ap_checks_pkg.get_posting_status(check_id) Accounting_status,

aca.recon_flag  reconciled,

alc.displayed_field payment_type,

aca.amount payment_amount,

aca.currency_code,

hou.name bu,

xep.name le,

aca.stopped_Date stop_date,

aca. void_date

from

ap_checks_All aca,

xle_entity_profiles xep,

hr_operating_units hou,

ap_lookup_code alc

where

aca.legal_entity_id=xep.legal_Entity_id

and aca.org_id=hou.organization_id

and alc.lookup_type='PAYMENT TYPE'

and aca.payment_type_flag=alc.lookup_code

Scheduling a Report

Scheduling a Report

Today we will see some of the tabs present in the Scheduling page of a Report.

To schedule a report, in Analytics Home page go to 

New > Report Job  or select the report > more > Schedule

First tab on this page is General Tab

First option is Report, here you can see the path of the report or you can select a report using search icon.

Second important in this page is Parameters, if your report has parameters it will be shown here. you can enter values for those parameters or can have default values.

Next tab is Output 

Output tab is where output formats, destinations like FTP, Email can be mentioned.

First check box is Use Bursting Definition to Determine Output & Delivery Destination. This option is automatically populated if your report has bursting query defined already. 

Next is Save Date for Republishing. This option is used for printing output from Report Jobs History in required formats.

Next important part is Output, here you specify different types of output based on different combinations of layouts, formats, locale.

Save Output option here is a check box which is used to see output from Report Jobs History page.

Last is Destination, where we can define delivery location, path, file name at destination or email ids based on the type of the destination

Next will be continued in the next post..

OTBI - Filters Vs. Selection Steps

 OTBI - Filters Vs. Selection Steps

Hi Everyone,

This is a small post explaining differences between Selection steps and Filters in OTBI Reports.

OTBI stands for Oracle Transactional Business Intelligence, an analysis tool and low code approach to develop reports for real time data.

It provides us with numerous options, today we will discuss one of those:

Filters:

Filters are used to limit data based on the requirement like we want to see particular item data or particular state and so on..

We can compare Filter to Where Clause so first filters are considered then query will be fired.

Selection Steps:

Selection Steps are different from Filters.

We can compare Selection Steps to Having Clause.

They affect only field but not values.

And Selection steps can be applied to Dimensional Data like Attributes, Hierarchical Columns, Groups and Calculations but not Measures.

Data appear in the order they are selected in Selection List.