BI Publisher: XDOXSLT:IS_NUMERIC Not Handling Negative Numbers

We have an inbuilt function IS_NUMERIC in BI Publisher template builder.  This function is mainly used to validate whether a given value is a numeric value or not. Based on that, this function will return either TRUE or FALSE.

Read more »

BI Publisher: Handling NULL XML Value

There will be three possible scenarios for the value of an element in a XML data, 

1. XML tag value present in the XML data, and it has a value which is not NULL
for ex:
<PO_NUMBER>123</PO_NUMBER>

Suppose you want to give some condition to check not null similar to the one we give in PL/SQL as,
IF value IS NOT NULL THEN
---
---
END IF;
BI Publisher syntax for this is,

Read more »

BI Publisher: Formatting Date

We can format dates in BI Publishes in the following three different ways,

1. Formatting Date using Microsoft Word’s native date format mask.
2. Formatting Date using Oracle’s format-date function.
3. Formatting Date using Oracle’s abstract date format masks.
4. Format with Calendar.

Lets me now explain little details about the above three ways, and below is the XML I am going to use in the below examples,
<ROWSET>
    <NORMAL_DATE>2011-01-26</NORMAL_DATE>
    <DATE_UTC>2011-01-26T18:00:00-08:00</DATE_UTC>
    <DATE_TEXT>26/Jan/2011</DATE_TEXT>
</ROWSET>

Read more »

OAF: Error testing toolbox.jws

Today I connect my Jdeveloper 10.1.3.3.0.3 to a R12.0.6 instance and tried to test the connectivity using toolbox.jws from myproject folder.

But unfortunately I got the error oracle.apps.fnd.framework.OAException: Application: FND, Message Name: FND_GENERIC_MESSAGE. Tokens: MESSAGE = java.lang.NullPointerException;

Read more »

BI Publisher: Conditional Formatting

Conditional formatting can be used in the layout builder for various scenarios. Some of the scenario I could think of is,
1. Display/hide layout regions based on condition.
2. Display/hide boilerplate text based on condition.
3. Apply visual attributes based on condition.
4. Hide table row or column based on condition.

In the below paragraphs we will see how above scenarios can he achieved in BI Publisher.

Read more »

How-TO: Get XML file for AP Check Print Program

Here in this post I would like to tell my way of getting the XML for a Format Payment Instruction Program or other words AP Check Print Program.

For every new Format Payment Instruction program run, the XML is getting stored in IBY_TRXN_DOCUMENTS table (at least in R12.0.6). We need to either query this table and get the last created record column DOCUMENT to get the XML or if you know the payment instruction ID, then you can query by payment instruction ID.

Read more »

How-To: Configure New Font On BI Desktop Publisher

In most of the projects we will come across building a report having Barcode or MICR font.

So once you install your new font under Windows/Fonts/ directory you will still not be seeing that font when you preview PDF output.

What to do in that case?
Here are the steps to follow on installing new font on your PC/Laptop.

Read more »

Cross Tab / Column wrapping

Today got a interesting issue/challenge from good friend and colleague Mr. Pinaaki krishnasharma. Issue is if we use a for loop @column and print the rows as column for say Cross Tab reports, the columns that were not able to fit in the page width is going out of the page. See below for example,

Read more »

Inline If Else Statment

This week I had come across an issue where I had a situation in the template to put 2 inline if statements one after the other.

 

Some statement if@inline:1=2 Result 1 end if if@inline:1=1 Result 1 end if

 

But in the output it printed both results of if statements. I do not know the reason for this. So as usual went for google and landed in our guru Tim Dexter's Dec 2007 post.

Read more »

Printing Check Amount With Decimal Part

Yesterday was not really a good day for me. because a simple fix on the check amount dragged my entire day till 10pm. But it kinda helped me to explore lot of stuff.

Let me come straight to the actual problem.

Currently we are showing the checkamount padded with asterisk(*) like *******1200 (I will use this value for by below examples). Here is how I pad the asterisk

<?xdofx:lpad(PaymentAmount/Value,12,'*')?>

Since there is no decimal part in this value the bank some times considers the last 2 digits from the right as the decimal part. In this case they consider 00 as decimal part and paid only 12 dollar. To avoid this confusion we decided to print the decimal part also so that the amount will look like ****1200.00

Read more »

Handling Text Wrapping with Fixed Rows in BI Desktop Publisher

After seeing Tim Dexter post about fixed line and row filler decided to post about the handling string wrapping in the template.

Ok let me start explaining how I handle the string wrapping. Before that let me let me tell you the requirement.

1. Need to display fixed lines.

2. If there are only few lines and the page is not filled then we need to print blank lines to fill the page.

3. At the end of the report we need to display the summary as shown above.

4. At the end of the rows including the rows with values and blank rows we need to print a line to end the box.

Read more »

Check Amount not printing the Units and Subunits

Some of you must have noticed the check amount that is getting printing words may not be printing the units and subunits of the amount.

For example, if you have the check currency code in USD, then the amount in words will be printing some thing like "One Thousand Four Hundred Twenty-Two Dollars And Eighty-Two Cents*****"

Read more »

How-To: Disable the preference menu from SSA page

Today I remembered one my task which I had done few days ago. The actual given to me is to disable the Preferences menu option from few responsibilities which is actually a Self Service Application page. So as-usual I started searched metalink and google and found three solutions for this. Let me list all the three type of solutions for this.

Solution 1:

Updating the profile option "General Preferences Show Flag" value to 'No'.

Read more »

Getting the application URL

In some cases you might be having the access to the application database but not the URL to access the application. If you had come across any of this situation then no worries here is a table ICX_PARAMETERS which gives you the information of the application URL. But keep in mind you need the username and password for the application ;)

API to insert GL Code Combination

I had seen in many projects during the initial instance setup the functionals used to use the data loader to insert the GL code combinations. In fact if its just 1000 or 2000 records then it may be fine. But I had seen them using the data-loader to insert 70k - 200k records, which will take at-least 2 days for them to insert. Apart from this there will be some value set values might be missing which will interrupt there data loader.

Read more »

Useful Sysadmin SQL statements

As I mentioned in my last post, here in this post I would like to share some SQL queries which will/might be useful for the sysadmins.

1. SQL Statement to find the list of responsibilities, which got the access to a particular function.

Read more »

How-To: Find all the menus to which a function is attached?

I was wondering how to find the list of menus to which a function is attached in the oracle application. Everybody will think of writing a SQL query to find it. Even I did the same before knowing about Functional Administrator responsibility. Thanks to my colleague and good friend Vivek Srinivasan who told me about this responsibility.

Read more »

XSL function to convert number to word

For long time we have been searching for one function which can be used to convert the number into words. You all might know why this function is mandatory.

Yes this helps a lot when you are doing a check print report. Lot of them posted questions on oracle forum. The great Gareth, gave a suggestion to use package from database & convert into the words. Everybody thought they need to create some custom function for this. But AP module have its own function ap_amount_utilities_pkg.ap_convert_number to do this.

Read more »

FND_PROFILE API

In oracle user Profile functionality is provided in the FND_PROFILE package and the FNDSQF library.

What is inside this API:

• Retrieve user profile values for the current run-time environment


• Set user profile values for the current run-time environment

Read more »