Number to Word conversion in RTF

xdofx vs xdoxslt

<?xdofx:expression?> for extended SQL functions
<?xdoxslt:expression?> for extended XSL functions.

You cannot mix xdofx statements with XSL expressions in the same context

This function enables the conversion of numbers to words for RTF template output. This is a common requirement for check printing.

The new function is “to_check_number”. The syntax of this function is

<?xdofx:to_check_number(amount, precisionOrCurrency, caseType, decimalStyle)?>

The following table describes the function attributes:

 
Attribute Description Valid Value
amount The number to be transformed. Any number
precisionOrCurrency For this attribute you can specify either the precision, which is the number of digits after the decimal point; or the currency code, which will govern the number of digits after the decimal point. The currency code does not generate a currency symbol in the output. An integer, such as 2; or a currency code, such as ‘USD’.
caseType The case type of the output. Valid values are:
‘CASE_UPPER’,
‘CASE_LOWER’,
‘CASE_INIT_CAP’
decimalStyle Output type of the decimal fraction area. Valid values are:
‘DECIMAL_STYLE_FRACTION1’, ‘DECIMAL_STYLE_FRACTION2’,
‘DECIMAL_STYLE_WORD’

The following examples display the function as entered in an RTF template and the returned output:

 
RTF Template Entry Returned Output
<?xdofx:to_check_number(12345.67, 2)?> Twelve thousand three hundred forty-five and 67/100
<?xdofx:to_check_number(12345.67, ‘USD’)?> Twelve thousand three hundred forty-five and 67/100
<?xdofx:to_check_number(12345, ‘JPY’, ‘CASE_UPPER’)?> TWELVE THOUSAND THREE HUNDRED FORTY-FIVE
<?xdofx:to_check_number(12345.67, ‘EUR’, ‘CASE_LOWER’, ‘DECIMAL_STYLE_WORDS’)?> twelve thousand three hundred forty-five and sixty-seven

Please note this is only available for R12 and not currently present on 11i.

Alternative

<?xdoxslt:toWordsAmt(TEST_AMOUNT)?>

Then it will display amount in words in Indian currency means Rupees not millions.

Example:

<?xdoxslt: toWordsAmt(123454.879)?>

Then it will display as

One Lakh Twenty Three thousand Four hundred Fifty Four and paise Eighty Eight

Alternative

Use the following function in rdf level

IBY_AMOUNT_IN_WORDS.Get_Amount_In_Words(TEST_AMOUNT)

 

 

Advertisements

Data link in Data Template

If you have multiple queries, you must link them to create the appropriate data output. In the data template, there are two methods for linking queries: using bind variables or using the <link> element to define the link between queries.

One way for doing it is using Bind variable. But sometimes this method may not work.

Eaxample

<dataQuery>
<sqlStatement name=”Q1″>
<![CDATA[
SELECT DEPTNUM,DNAME,LOC
from scott.dept
order by deptno ]]>
</sqlStatement>
<sqlStatement name=”Q2″>
<![CDATA[
SELECT DEPTNO, EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,nvl(COMM,0) COMM
from scott.EMP
where DEPTNO = :DEPTNUM ]]>
</sqlStatement>
</dataQuery>

 

 

Using Link

<link name=”DEPTEMP_LINK” parentQuery=”Q1″ parentColumn=”DEPTNO” childQuery=”Q_2″ childColumn=”DEPARTMENTNO”/>

 

Example

<dataQuery>
<sqlStatement name=”Q1″>
<![CDATA[
SELECT DEPTNO,DNAME,LOC from scott.dept
order by deptno ]]>
</sqlStatement>
<sqlStatement name=”Q2″>
<![CDATA[
SELECT DEPARTMENTNO, EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,nvl(COMM,0) COMM
from scott.EMP ]]>
</sqlStatement>
<link name=”DEPTEMP_LINK” parentQuery=”Q1″ parentColumn=”DEPTNO” childQuery=”Q_2″ childColumn=”DEPARTMENTNO”/>
</dataQuery>

 

			

Data Template error :The uploaded file is invalid. the file should be in xml-data-template format

While defining Data Definition

Error:
The uploaded file is invalid. the file should be in xml-data-template format.

Solution : Check if the Data Template XML file that is being uploaded is in proper XML format. Try opening the file in IE and check if it is coming in proper format or erroring out(or not in proper format).

 

Caused by: oracle.xdo.parser.v2.XPathException: Cannot convert to number.

This error comes when we are trying to do some calculations based on a column that returns NULL value.

Capture

This can be handled in the following way

<?EXP2[.!=”]?>

<?xdoxslt:set_variable($_XDOCTX, ‘P’,xdoxslt:get_variable($_XDOCTX, ‘P’)+EXP2[.!=”])?>

 

Here EXP2 is a column that is returning NULL value.

 

To reference a parent level element in a child level table — BI Publisher

To reference a parent level element in a child level table, you can use the “../” notation.

For example , if you have a block Department and a child block as Employee

<?xml version=”1.0″ encoding=”UTF-8″?>
<!– Generated by Oracle Reports version 10.1.2.3.0 –>
<MPLDEPRICIATION>
<LIST_G_DEPT>
<G_DEPT>
<DEPT_NAME>Oracle</DEPT_NAME>
<LIST_G_EMP>
<G_EMP>
<EMP_NAME>Arun</EMP_NAME>
<ENO>1234</ENO>
</G_EMP>

</LIST_G_EMP>

</G_DEPT>

</LIST_G_DEPT>

</MPLDEPRICIATION>

Now to reference the value of DEPT_NAME in G_EMP group , we  can use the following

<?../DEPT_NAME?>

You can always use the absolute path to reference any extract element anywhere in the

template. The absolute path starts with a backslash “/”.

This notation goes up to any level for the parent elements.

For example the absolute path for the above example would be  /G_DEPT/DEPT_NAME

The absolute path syntax provides better performance.

If we have 3 levels of hierarchy then we can use <?../../DEPT_NAME?>

 

 

 

“REP-0118 unable to create a temporary file”

“REP-0118 unable to create a temporary file” error pops up when we install Oracle Report and open it for first time.

Cause – Issue with Temp file path and you don’t have access to this folder.

Fix – Navigate to Registry entry (Run -> Regedit -> and search (Ctrl+f) with “REPORTS_TMP”. Modify the path to your path or get the write access for this folder.