tag:blogger.com,1999:blog-46325681450837875792024-02-01T23:03:31.940-08:00Flex FieldsAK Lakshmananhttp://www.blogger.com/profile/03268792511249548944noreply@blogger.comBlogger19125tag:blogger.com,1999:blog-4632568145083787579.post-31353628003263045072011-04-27T00:03:00.001-07:002011-04-27T00:05:09.909-07:00BI Publisher: XDOXSLT:IS_NUMERIC Not Handling Negative Numbers<div dir="ltr" style="text-align: left;" trbidi="on">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.<br />
<br />
<a name='more'></a><br />
But according to the recent bug:<a href="https://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=12401094">12401094</a>, this function treats the negative sign (-) as a character value and returns FALSE.<br />
<br />
for example –1245.00 will be considered as character value and it will be return FALSE. <br />
<br />
So developers, beware of this bug and do not waste time in thinking your code have some issue :)<br />
<br />
<b>Note</b>: I got this information in the XDO email group where, this issue was raised by Kavipriya Krishnaswamy and confirmed as bug by Kathy Gao and Pieter Breugelmans.</div>AK Lakshmananhttp://www.blogger.com/profile/03268792511249548944noreply@blogger.com0tag:blogger.com,1999:blog-4632568145083787579.post-66688633376848225402011-04-14T02:47:00.001-07:002011-04-14T02:49:12.068-07:00BI Publisher: Handling NULL XML Value<div dir="ltr" style="text-align: left;" trbidi="on">There will be three possible scenarios for the value of an element in a XML data,<b> </b><br />
<br />
<b>1. XML tag value present in the XML data, and it has a value which is not NULL</b><br />
for ex:<br />
<PO_NUMBER>123</PO_NUMBER><br />
<br />
Suppose you want to give some condition to check not null similar to the one we give in PL/SQL as,<br />
IF value IS NOT NULL THEN<br />
---<br />
---<br />
END IF;<br />
BI Publisher syntax for this is,<br />
<a name='more'></a><br />
<?if:XML_TAG_NAME!=?><br />
--<br />
--<br />
<?end if?><br />
<ul></ul><br />
<b>2. XML tag value present in the XML data, but it does not have a value which is NULL.</b><br />
for ex: <br />
<PO_NUMBER></PO_NUMBER><br />
(OR)<br />
<PO_NUMBER/><br />
<br />
Now you want to give some condition to check null similar to the one we give in PL/SQL as,<br />
IF value is NULL THEN<br />
--<br />
--<br />
END IF;<br />
BI Publisher syntax for this is,<br />
<?if:XML_TAG_NAME and XML_TAG_NAME="?> <br />
--<br />
--<br />
<?end if?><br />
<br />
<b> 3. XML tag is not present in the XML data, and therefore there is no value.</b><br />
Now you want to give a condition to check the existence of the tag in the XML data. Here is the BI Publisher syntax for this,<br />
<?if:not(XML_TAG_NAME)?> <br />
--<br />
--<br />
<?end if?></div>AK Lakshmananhttp://www.blogger.com/profile/03268792511249548944noreply@blogger.com2tag:blogger.com,1999:blog-4632568145083787579.post-12225746587928463042011-01-27T06:44:00.001-08:002011-01-27T06:46:02.745-08:00BI Publisher: Formatting Date<div dir="ltr" style="text-align: left;" trbidi="on">We can format dates in BI Publishes in the following three different ways,<br />
<br />
1. Formatting Date using Microsoft Word’s native date format mask.<br />
2. Formatting Date using Oracle’s <b>format-date</b> function.<br />
3. Formatting Date using Oracle’s abstract date format masks.<br />
4. Format with Calendar.<br />
<ol></ol>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,<br />
<ROWSET> <br />
<NORMAL_DATE>2011-01-26</NORMAL_DATE> <br />
<DATE_UTC>2011-01-26T18:00:00-08:00</DATE_UTC> <br />
<DATE_TEXT>26/Jan/2011</DATE_TEXT> <br />
</ROWSET> <br />
<br />
<a name='more'></a><br />
<b><u>1. Formatting Date using Microsoft Word’s native date format mask.</u></b><br />
In the BI Publisher Properties window, select type as Date and select the format from the drop down box.<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhj3Ia_Fuo1U0UU_kD8wZki5-sWvBJDMK_S3keDWrj40TKsrlGJ27Gc0kPAGbVX6-av1d9JFUyyhBrI6DF2tiEGTQ4HYD9gCEPIYwUTEFAOECXZYLUB3iq4zzSczD1SSI7sEJKlg7tUTha4/s1600-h/image6.png"><img alt="image" border="0" height="370" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhHBw3GsgoTrlReTvkh5BwtSg8E0bWHAD9_U3_H7iiV9t3Nk6B12U-5-OEuw9P_2pzeya6Q2gIg2lA-0nzPAuLOzo58Uw4RZla33N3WHe1C5W9B1AbV8OY2hrQGWQ0GMoUC7MS65ySc5b5v/?imgmax=800" style="border-width: 0px; display: inline;" title="image" width="514" /></a> <br />
<br />
Here is the output sample of all the three types of date’s we had in the XML. Format I selected is <br />
d-MMM-yy<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiRwdnkGh7LH9VUKLU9tsp7l-M5vm8O_goenCfBI0D0QgBmyl4W5ykyMa8itNN5T_haP01aBVHmwgkbPjy1nKq7XYFSVtxSYIHmZvuUDKKNntSoedfTZcXk4ghwoYeutzPi37A4PgKoiFvB/s1600-h/image13.png"><img alt="image" border="0" height="59" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1IP7Sx_1uBx1hw9evxm1WIvEzs0r8P332kGKhqh9AZr0putRSw_nt0QPuNQPgako-ZgunRNvCjvamr-3V3AbjohIqhn0dcoQXv20TgJP2AzJsrqLsrPbnwhSBb_IuxpBMmQkgsxKc-PPS/?imgmax=800" style="border-width: 0px; display: inline;" title="image" width="640" /></a> <br />
<br />
<b><u>2. Formatting Date using Oracle’s format-date function.</u></b><br />
format-date() is an inbuilt function in BI Publisher. <br />
<u>Syntax:</u><br />
<?format-date:fieldname;<b>MASK</b>)?><br />
In the above syntax MASK is an optional parameter. We will see more details about MASK later in this post.<br />
<br />
To use this format-date function, under the BI Publisher Properties window -> keep type as Regular Text -> click on Advanced tab –> call format-date function with the tag name. (For time being we will not pass the MASK parameter.)<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEimog0qfZmpjqW5OjddMBoWAXN7KsJGlSwOsrhjaA28QLwyAtCtFhyVkzDcslrE9GOf054ETXSYV8Ho43DIefz9-94-oJL3mtZnvcyMMc9a-OHBT2XmUIwyaYBZhY_nWVzJg1I_mkq0o0SI/s1600-h/image23.png"><img alt="image" border="0" height="360" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhRZE4CuVUK6qf8qIFgy2jcli26QIKz6-xm41NvsdlY1XcYci7V8velxBRW6ogHWUGeIDoDNMRsuwYaIIGErGWPJHYop4W3zLCJPh5zZDAsWHNliiertZkq7cntu-WG7UQ1Ow7_fcNhIgZS/?imgmax=800" style="border-width: 0px; display: inline;" title="image" width="483" /></a> <br />
Here is the output sample of all the three types of date’s we had in the XML. <br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgauhKGzvzIErPNBNNlDVU603ajccNffnhEpTnlJ4sO9MXn8_unBY9VcvRSLIXFS3x1WscIGzvbbpjkMAxl5YU-CPbktknjZKZ1zPZ7rH16J34Z7nhSBRx315ASlNt3l8cyy52CHH2mwSMN/s1600-h/image%5B5%5D.png"><img alt="image" border="0" height="45" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi6270rLxYz9BxiH8KuFFu5YHrc3XjZkz7GVhyphenhypheneu9d9dPPAyaf6OYtrSyN8qw7DwvniE5ja4dVLSFoufmhMwcVptKygFD_cED_s0w2Q7Yl7DU-qGQACSkUbVYiOhc9nmBtSIn2rMpA2yLbS/?imgmax=800" style="border: 0px none; display: inline;" title="image" width="640" /></a> <br />
<br />
<b><u>3. Formatting Date using Oracle’s abstract date format masks.</u></b> <br />
In this section we will seeing various MASK parameters that can be passed to <?format-date:tagname;’<b>MASK’</b>?> function.<br />
<br />
Out of box we have the following MASK available for various format of dates,<br />
<table border="1" cellpadding="2" cellspacing="0" style="width: 481px;"><tbody>
<tr> <td valign="top" width="206"><b>Mask Constant</b></td> <td valign="top" width="273"><b>Output</b></td> </tr>
<tr> <td valign="top" width="208">SHORT</td> <td valign="top" width="272">1/26/11</td> </tr>
<tr> <td valign="top" width="209">MEDIUM</td> <td valign="top" width="271">Jan 26, 2011</td> </tr>
<tr> <td valign="top" width="210">LONG</td> <td valign="top" width="271">Wednesday, January 1, 2011</td> </tr>
<tr> <td valign="top" width="210">SHORT_TIME</td> <td valign="top" width="271">1/26/11 8.10PM</td> </tr>
<tr> <td valign="top" width="210">MEDIUM_TIME</td> <td valign="top" width="271">Jan 26, 2011 8.10PM</td> </tr>
<tr> <td valign="top" width="210">LONG_TIME</td> <td valign="top" width="271">Wednesday, January 1, 2011 8.10PM</td> </tr>
<tr> <td valign="top" width="210">SHORT_TIME_TZ</td> <td valign="top" width="271">1/26/11 8.10PM GMT</td> </tr>
<tr> <td valign="top" width="210">MEDIUM_TIME_TZ</td> <td valign="top" width="271">Jan 26, 2011 8.10PM GMT</td> </tr>
<tr> <td valign="top" width="210">LONG_TIME_TZ</td> <td valign="top" width="271">Wednesday, January 1, 2011 8.10PM GMT</td> </tr>
</tbody></table><br />
The default MASK constant is MEDIUM.<br />
<br />
In order to user these abstract’s you need to have your XML date data in canonical format as,<br />
YYYY-MM-DD<b>T</b>hh:mm:ss(+/-)HH:MM <br />
(For example check out DATE_UTC tag value in the above XML.)<br />
<br />
Here,<br />
YYYY is the year<br />
MM is the month<br />
DD is the date<br />
T is the separator between the date and time component<br />
hh is the hour in 24-hour format<br />
mm is the minutes<br />
ss is the seconds<br />
(+/-)HH:MM is the time zone offset from Universal Time (UTC) or Greenwich Mean Time<br />
<br />
Here is the output for our XML data using the MEDIUM_TIME mask,<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZsPB8FBDv4I-VpaphdplgyOnmw84ynAiYuXvrbAPd-tm1uRGYfdEWuS0z2HbEfqQBPQGgNA6-eAukKyqtQ4lOTFCHnZ6-6hQ9AC_k_kKvM05XfO-4-W1TFT5gHELfrfnTQRPX1YgMSdbG/s1600-h/image%5B10%5D.png"><img alt="image" border="0" height="43" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0x0kJvFBQWduLsfpR2GLnK1cZB7vBT_S0KPCejxQU9oIqt4yAGx7nIxwh9oIV16cj3ZlbsYH5WpabwIdHATTEtcCwkiW0fUR-sSZNOMHRMxjO-9i4UJqPyqGVLUgf_LzgwVe4jepHSxgS/?imgmax=800" style="border: 0px none; display: inline;" title="image" width="640" /></a> <br />
<br />
Now if you notice the output that we got earlier while using format-date with the default parameter, column for date_utc alone is showing Jan 27 were as the date we have in the XML is 26. <br />
<br />
This is because of the time zone offset –08:00. <br />
<br />
Where this timezone offset is nothing but, the time zone difference of the date that we passed in the XML with respect to GMT. So BI Publisher will automatically understand the timezone of the XML data, and print/convert it as per the BI Publisher Local Server time.<br />
<br />
<b>How to get Time zone offset of the data?</b><br />
Ok now we know its important to pass the time zone offset in the XML data to use the MASK. Lets see how to get them in the XML data,<br />
<br />
<b>First Option:</b><br />
If your data source is a data definition file, then no need to worry, the standard BI Publisher data definition engine by default will give the data in the canonical format with time zone offset. So you are good. No need of any extra coding. <br />
<br />
If you do not want the template to change the date as per the time zone then, user the TO_CHAR function to print without the time zone offset. So that template engine will not modify the date as per the server time zone.<br />
<br />
<b>Second Option:</b><br />
Lets say your data source is an RDF file. In that case use TO_CHAR function in your SQL query and bring the data in required canonical format.<br />
<br />
<b>Third Option:</b><br />
Lets say, you do not have control over the data source. You have only access to the template and know what will be the time zone of the data that is going in come in the XML. <br />
<br />
In this case you can use the third optional parameter in the format-date function where you can pass the time zone name like this,<br />
<?format-date:NORMAL_DATE;’MEDIUM’;’America/Las_Angeles’?><br />
<br />
If do not want the time zone and still want to use the abstract format, then specify UTC in place of time zone.<br />
<?format-date:NORMAL_DATE;’MEDIUM’;’UTC’?><br />
<br />
<b><u>4. Format with Calendar</u></b><br />
Now that we had seen different types of formatting dates, let finally see how to use the above formats with various calendars.<br />
<br />
For this we again have one more buitin function,<br />
<?format-date-and-calender:tagname;’MASK’;’CALENDAR_NAME’?><br />
<br />
Here is the supported calendar types,<br />
- GREGORIAN<br />
- ARABIC_HIJRAH<br />
- ENGLISH_HIJRAH<br />
- JAPANESE_IMPERIAL<br />
- THAI_BUDDHA<br />
- ROC_OFFICIAL (Taiwan)<br />
<br />
<br />
I think, I had covered all the date formatting techniques in BI Publisher. If you feel I had missed anything or would like to explain some specific example, feel free to put on the comments.</div>AK Lakshmananhttp://www.blogger.com/profile/03268792511249548944noreply@blogger.com4tag:blogger.com,1999:blog-4632568145083787579.post-22848382019204205322011-01-14T06:03:00.001-08:002011-01-14T06:10:27.172-08:00OAF: Error testing toolbox.jwsToday 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.<br />
<br />
But unfortunately I got the error oracle.apps.fnd.framework.OAException: Application: FND, Message Name: FND_GENERIC_MESSAGE. Tokens: MESSAGE = java.lang.NullPointerException;<br />
<br />
<a name='more'></a><br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiiA_AENeXTC5f-sjV1eqqh0VanmWy0vDnbDQ2oLc6puRjXSHNrFtub_-ZNgU6Jf5bhuYDut2j1PWk2Zq1MxOpwbcytqAZJ2_WzP_uUvEhVXTwnwXo5cULo3EZvP7-u-BIQcoQieBpbXvSz/s1600-h/image%5B4%5D.png"><img alt="image" border="0" height="192" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiVS4W4FfEqGE7IVfRNeLOJNtjnk6hbOAitB_npC09rcJKdrthPwTgrWXi5KFVvjagEwzu8I3WBLEEmw7N3ha9ERROlIgTY89eNnAYwqsfXSsroLJy8_3GUdUK0lL91kdcXKn9Nceiof0zs/?imgmax=800" style="border: 0px none; display: inline;" title="image" width="640" /></a> <br />
<br />
So as usual, I googled and landed on the <a href="http://kr.forums.oracle.com/forums/thread.jspa?threadID=908069">oracle forum page</a> where it was clearly mentioned by Mukul Gupta as “There is the issue with newly released jdeveloper patch for 12.1. We have raised an SR with Oracle and they have responded back saying : Set profile "<b>Sign-On:Notification</b>" to "<b>No</b>" at site level, which resolves the issue.”<br />
<br />
So suggested I went and set that profile and able to run the toolbox.jws successfully. Hope this helps OAF beginners.<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh10ChHjIcZ-ZgpbrAVmHVVpgKFjUYzOaNoiFlkj0IEfGsaxpMLDZYJ-Md_L2jp7YmEhRPn5pkq2JWXz-JQP2gloSIGhUXMBYZYoOBl_oTMjuonbX__k-MsWHlSq4obpKb-uJsqprqU9iBW/s1600-h/image%5B9%5D.png"><img alt="image" border="0" height="246" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiezMlSc5otFuZzJ3V1sEjzVM-WUERisrVowQRg-izmwVzeHAUMpOGhwHbU5eBHjIDAb997ARwuDNr4D-Z1xBl3ZbbjqWdIK7qXueTK3adnfUcnfdSJXcEtsUZGwA8BaCRh-J-nHGDTxxaW/?imgmax=800" style="border: 0px none; display: inline;" title="image" width="640" /></a>AK Lakshmananhttp://www.blogger.com/profile/03268792511249548944noreply@blogger.com0tag:blogger.com,1999:blog-4632568145083787579.post-51433786559769942992010-12-22T02:33:00.001-08:002010-12-22T02:38:02.990-08:00BI Publisher: Conditional FormattingConditional formatting can be used in the layout builder for various scenarios. Some of the scenario I could think of is,<br />
1. Display/hide layout regions based on condition.<br />
2. Display/hide boilerplate text based on condition.<br />
3. Apply visual attributes based on condition.<br />
4. Hide table row or column based on condition.<br />
<br />
In the below paragraphs we will see how above scenarios can he achieved in BI Publisher. <br />
<br />
<a name='more'></a><br />
When it comes to conditions, obviously its going to be an IF statement just like any other programming language. So here is the syntax of BI Publishers if statement,<br />
<span style="background-color: #ffcc66;"> <?if:condition?><br />
…..<br />
<?end if?></span> <br />
<br />
<h5>1. Display/hide layout regions based on condition.</h5>By having the condition <b>if</b> and <b>end if </b>before and after the region can either display/hide a region in the template.<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEig155CAhNo0UK62cpe2eOr8A-gtSJDJVyztg4CUCfbOHAjruDIaRLBTfz6LyoL9HpJqrODx_9RquUxkdwtRmvmOuISyKA__cxG3z86he5s35FZkQagSvpunEGdHsRcEzIK-L857SgP9d2v/s1600-h/image%5B32%5D.png"><img alt="image" border="0" height="202" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXyCWj-pX06omyqA0qnn9BeecRZcxuH37Cp3z8aTTgn8fJVpmYniWmls5jTzV6-9aYhoMymZeYYv-S3nXI9mHYmhYRHvuu0VAX0bjktJ87e39RR00bu2GePtEsz0aHjlMg7PtdtpOQAdxu/?imgmax=800" style="border: 0px none; display: inline;" title="image" width="529" /></a> <br />
Same can be achieved using CHOOSE WHEN statement as below,<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhB5E3o0-P3a-1DYre7RnbUQVhfCcvWpha0NJ3sXjlZ1Y99Hyu5SkxkY9R4ccQW2FkS65SD_dUx3uwSQ1P8mocQneeHs9kUcaSdDy2UwrM-KnTGtWmaBNXBqdl7UGXXlZa3VHQd9Q4_oU7o/s1600-h/image%5B33%5D.png"><img alt="image" border="0" height="214" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgk0RvooGt_wQTtPHZcgNFRogiCYVBHX2LKsz2zRuxcQZbM0dKDluoOvIH41vA-RNNc7HXzTZWUVmLEVvWWj90ZGxZUrGkYOaizSM3CPHWUvc22a8FdAINv312J9Vj3ekxlBon0Q2tKrhuw/?imgmax=800" style="border: 0px none; display: inline;" title="image" width="435" /></a> <br />
<b>2. Display/hide boilerplate text based on condition.</b><br />
By using inline if condition we can hide/display a boiler plate text. syntax of inline if statement is,<br />
<span style="background-color: #ffff66;"> <<a href="mailto:?if@inline:condition?">?if@inline:condition?</a>><br />
..<br />
<?end if?></span> <br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqqZuCa-fX8IJDC1BQwTIOjwfC1utHS37fUajXOJgflPWe_HffHT_8SZjxBbHnlzWCZuHfapwxbMbvjpHRaLAuvswUe9YYKU9kb0tL6Vrbqg-EcrJQ9mXMHV4xy763KdrbTobRP-rsHfXc/s1600-h/image%5B38%5D.png"><img alt="image" border="0" height="199" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi67kdlC9B7TYnMwNo0NQcFW5l588RHI48CMEyr5s1gAvc_Oq25fLFWm2ZorCpBa_9UfRMLgXlCJ68GNNO9CrEt_DUgxlXlrBhSYo9D7yAHjX8BeyEREDgVIesWMT0Nw8ndYGa2B_zzZfCJ/?imgmax=800" style="border: 0px none; display: inline;" title="image" width="580" /></a><br />
Using inline if condition will avoid getting the hard next line. So that we will get the entire statement in one line or on the same line.<br />
<br />
<b>3. Apply visual attributes based on condition.</b><br />
<br />
i) Place the cursor on the table cell on which you want to make the condition.<br />
ii) Select Conditional Format from the BI Publisher toolbar/add Ins of word document.<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjDy92T5K0zBQIngAhPAehuaAuRegaRuU11cEu4zvEOCDpZg8SCgsxsaqGieAqZz-6cxYnF7PlEinG5co4qcKdw0qgPVaEGdamOKl_73SBW7Pz4hgAaIm_DOhhj45Y3uOxtlvSOJS_lvlRr/s1600-h/image%5B52%5D.png"><img alt="image" border="0" height="251" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg4SPuGBdveEEyFhEHV2bUhCsb3R5qZsEcCJFMRAlKBqhqALvWdb73KS0v3ujdlvyyJPNfBeZoiNxaaLdKtJrjT5Oph8uIp8ve8Sw3Sui0YfDuhQvjdxxc-nPJ-GYl7QSIbjTfJs7uhfzDS/?imgmax=800" style="border: 0px none; display: inline;" title="image" width="238" /></a> <br />
<br />
iii) Select the field under the data field box,<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhqcWXUCEPA-6cohLjM-h3sVLnI39k76lSMTpFnPKpNGVApmbPmL0QCGNNTp1Ba8tNcmvuhl2GlAW80w58-HUUJK-cTuJ2zEcsi_9v0BWOUPUT_qTIzJ2weqh6eTucuSdswD0I3eQBQ8PVx/s1600-h/image%5B50%5D.png"><img alt="image" border="0" height="73" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhoCQ9gpCbPWSqkIKhrS-DzfuEmg0A9JoNUM2vnS7_Qy4i6fulMLojd38FHU5PpXzR6V7PPdhbNeDDxoqFPsUYaSzXfR8Xl0IZR1V10roZJpNoJ-yYYFgVGK6XmvNDve7bgWvauBpYwXG6o/?imgmax=800" style="border: 0px none; display: inline;" title="image" width="432" /></a> <br />
<br />
iv) Enter the condition and click format button to format the text.<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhHrddALNkk84PG6CgpME_nSL_c108kiISy5butamv1qu4jkIABLLTsCYXmfAqlfn1YSHkio7fLjsFuNhaZmzTZt7JiUPkCjQdaKHvVMYPtWolFVWIcsaNv-uE3ldOSAsbbDqOAD0bfC1c9/s1600-h/image%5B57%5D.png"><img alt="image" border="0" height="386" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiL2j-kp0YEc7WrUbX1Id5qEvi-IIVtRWXC4MThgXWEf0EoW7NGXzL74jRbybm_3a7aWC6F0IAvbWkbPaB0WhmEIFYCVuYyS_3Z7ScQ32nYoLaXkgw67GSVJqU61mHc9pp138uO1m56mas5/?imgmax=800" style="border: 0px none; display: inline;" title="image" width="453" /></a> <br />
<br />
V) if you like to apply this visual attribute to the entire row, then select the checkbox “Apply to Entire Table Row”<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPF8Cq2txDjymZi_UE94nH8QoW3Uqi6uz1Hu81irG43Lkngyr2uIsGKrCfEAtoJDalx1OxJGSJ87J68eQrHJOlxBNPOllep8YPtt-ZP_mUkcFKWwsHUBRJwjjMaap_Yw7gGtbwhYdthUvm/s1600-h/image%5B62%5D.png"><img alt="image" border="0" height="75" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-VXJWwX7NwX7743DbJL8ZhAegC7vGxCa9HJwEdV0f2GB4ujDUiGEx53Q4YOiT8zthFCv2IAAKsSLLooq53nAMylJGFPPIkOOhE5P9jz67laAYtnQV_RRxSxwzopGEKd3MzfgFXIgIFtL_/?imgmax=800" style="border: 0px none; display: inline;" title="image" width="422" /></a> <br />
<br />
Once you apply this conditional formatting you will get some codes getting added as,<br />
<span style="background-color: #ffff66;"> <?if:number(SAL)>1000?><br />
<<a href="mailto:?attribute@incontext:color;%27red%27">?attribute@incontext:color;'red'?</a>><br />
<?end if?><br />
<br />
</span> <br />
<b>4. Hide table row or column based on condition.</b><br />
To hide/display a row based on a condition we just need to add the condition at the beginning and end of the row as shown below,<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdNFEaChy8r41VLpJo46jKi0Temh54spMkG3eBT8sW7AkSCKSnVB6ZTxS5_6tfckO7fhEUkVMcMEm7H9vIgUQnCSVnVefVIlD71rTWaCmCTjCPDBBTJYMvwtgjCfEqiKI5xzmB6ELEoX3t/s1600-h/image%5B67%5D.png"><img alt="image" border="0" height="60" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1yNVCtxvYAzugho0CTVkR9I8fcuCyUnqtO917mVDdcIGS1IdlnHan_6oNIqZGP3ejZWhbWNAx7W_WZPhSKEotXJ7UgnSygt8zHN3LsqDLnGrmcHis_HMntmRTjtioIY3hopK9fFVhJ98-/?imgmax=800" style="border: 0px none; display: inline;" title="image" width="640" /></a> <br />
<br />
<br />
To hide/display a column based on a condition we need @column with the if condition. Just like @inline we used earlier, user @column with the condition on the column you want to hide/display. Syntax,<br />
<span style="background-color: #ffff66;"> <<a href="mailto:?if@column:conition?">?if@column:conition?</a>><br />
..<br />
<?end if?></span> <br />
<br />
<br />
Apart from the above explained <b>if</b> conditions, we have if-then-else statement, syntax for this statement is,<br />
<?<b>xdofx:if</b> element_condition <b>then</b> result <b>else</b> result2 <b>end if</b>?>AK Lakshmananhttp://www.blogger.com/profile/03268792511249548944noreply@blogger.com11tag:blogger.com,1999:blog-4632568145083787579.post-3882902154516452232010-12-09T05:00:00.001-08:002010-12-09T05:02:44.236-08:00How-TO: Get XML file for AP Check Print ProgramHere 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.<br />
<br />
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. <br />
<a name='more'></a><br />
Since the column DOCUMENT is CLOB column, your TOAD may not show this column value. In my case I used SQLDEVELOPER to get the value of this column.<br />
<br />
Good luck. Here is the query,<br />
<br />
<span style="background-color: #ffff66;"> <b>SELECT</b> DOCUMENT <br />
<b>FROM</b> IBY_TRXN_DOCUMENTS <br />
<b>WHERE </b>PAYMENT_INSTRUCTION_ID = :P_PAYMENT_INSTRUCTION_ID;</span>AK Lakshmananhttp://www.blogger.com/profile/03268792511249548944noreply@blogger.com1tag:blogger.com,1999:blog-4632568145083787579.post-85811773013753898872010-12-01T00:33:00.000-08:002010-12-01T03:35:01.931-08:00How-To: Configure New Font On BI Desktop PublisherIn most of the projects we will come across building a report having Barcode or MICR font.<br />
<br />
So once you install your new font under Windows/Fonts/ directory you will still not be seeing that font when you preview PDF output.<br />
<br />
What to do in that case?<br />
Here are the steps to follow on installing new font on your PC/Laptop.<br />
<a name='more'></a>1. Copy new font file to Windows/Fonts directory.<br />
<br />
2. Copy xdo.cfg in C:\Program Files\Oracle\XML Publisher Desktop\Template Builder for Word\config directory. (if do not have one xdo.cfg, do not worry. xdo example.cfg will be avaialble by default in this folder. So rename this file to xdo.cfg or create one new file name xdo.cfg<br />
<br />
3. Right click the font file and go to properties, Copy the .ttf file name<br />
<br />
4. Open the xdo.cfg file and paste as follows (My filename is "Troy E-13B.ttf" and font family is "Troy E-13B")<br />
<ol></ol><br />
<br />
<pre name="code" class="xml"><config version="1.0.0" xmlns="http://xmlns.oracle.com/oxp/config/">
<fonts>
<!-- Windows Troy E-13B font -->
<font family="Troy E-13B" style="normal" weight="normal">
<truetype path="C:\WINDOWS\fonts\Troy E-13B.ttf" />
</font>
</fonts>
</config>
</pre>AK Lakshmananhttp://www.blogger.com/profile/03268792511249548944noreply@blogger.com1tag:blogger.com,1999:blog-4632568145083787579.post-10413545037065674342010-06-07T04:08:00.000-07:002010-06-07T05:13:32.703-07:00Cross Tab / Column wrapping<span xmlns=""></span><br />
<span xmlns="">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,</span><br />
<div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgD3MePTA_m8-RBbawEMNDREZ_9SDCK5_VDzbGXJYJ10LYjYNEJaufcogxeQ_kaPVFAViSJsYuGmh2aJKwIYqxYJn9NURAo8paXR93WESDpjZOQdWNg6P3ZjREF-x7KyUWWfp6M3qa3XkEV/s1600/image1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="50" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgD3MePTA_m8-RBbawEMNDREZ_9SDCK5_VDzbGXJYJ10LYjYNEJaufcogxeQ_kaPVFAViSJsYuGmh2aJKwIYqxYJn9NURAo8paXR93WESDpjZOQdWNg6P3ZjREF-x7KyUWWfp6M3qa3XkEV/s640/image1.png" width="640" /></a><a name='more'></a></div><br />
<span xmlns=""> </span><br />
<span xmlns=""> </span><br />
<span xmlns="">And XML file is </span><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBLbvV-wSnXDKqbliVs2mMnPDltdACV4a91zfsY4WHcxg3CQPBRMF29vqvyL0-XFoppIOENWbpgiGx-YbU3JvCYOvQ4u37Gd5JEGvCPlC-B0J68d15meu_qqrGaGjgbwZqLfuT6heOON4Q/s1600/image2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBLbvV-wSnXDKqbliVs2mMnPDltdACV4a91zfsY4WHcxg3CQPBRMF29vqvyL0-XFoppIOENWbpgiGx-YbU3JvCYOvQ4u37Gd5JEGvCPlC-B0J68d15meu_qqrGaGjgbwZqLfuT6heOON4Q/s400/image2.png" width="200" /></a></div><br />
<div class="separator" style="clear: both; text-align: center;"></div><span xmlns=""> </span><br />
<span xmlns=""> </span><br />
<span xmlns="">Now we need the column to get wrapped to the next line if there are more columns that cannot be fit in one single row. So the output should look like</span><br />
<div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjyiCpqv9SfaG3ibP_qe0uxUx0YoNp2i-3RKx4z05B3oYmZeBt3q121nrw-WZlooFBN9-kyvHI2t7YB7F7G_bMTZsKk6sGZI_sCwVAdSyS7XxPVubVslbUwYe-DXinY3uveEvAL_FzPhk7M/s1600/image3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="67" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjyiCpqv9SfaG3ibP_qe0uxUx0YoNp2i-3RKx4z05B3oYmZeBt3q121nrw-WZlooFBN9-kyvHI2t7YB7F7G_bMTZsKk6sGZI_sCwVAdSyS7XxPVubVslbUwYe-DXinY3uveEvAL_FzPhk7M/s640/image3.png" width="640" /></a></div><span xmlns=""><br />
</span><br />
<span xmlns=""> </span><br />
<span xmlns="">So What I did is I introduced an outer loop to and couple of variables to control the number of columns that should print on a single line. And here is my change</span><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjuEL805V7V1W8moGUhqyybzl1tY0Srqb3BKPSEVyW5opmDjhz7RslFxrn79xEEn8I8kHR8uTv4fm1TjRqNKcCmVV8ECjyA0e8HQDCC3_zISMp8beH7MHjBueaoTlJ6aFxAKao9yaEiI1Mm/s1600/image4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="96" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjuEL805V7V1W8moGUhqyybzl1tY0Srqb3BKPSEVyW5opmDjhz7RslFxrn79xEEn8I8kHR8uTv4fm1TjRqNKcCmVV8ECjyA0e8HQDCC3_zISMp8beH7MHjBueaoTlJ6aFxAKao9yaEiI1Mm/s640/image4.png" width="640" /></a></div><br />
<div class="separator" style="clear: both; text-align: center;"></div><span xmlns=""> </span><br />
<span xmlns="">V_from and V_to variables were initialized to 0 and 4 before the loops and incremented by 4 (which is fixed columns) inside the loop. On the first loop you must have noticed I have looped 5 times which is hard coded in the loop. This can be made generic by getting the number of rows expected from the XML source itself.<br />
</span><br />
<span xmlns="">The only issue with the above fix is if there are only 3 columns it will fill with an empty column as column 4. So that it will look something like below.</span><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizvj9rfzgbt_-ik6PwC9lwcolyhZ2jts_A095bbIyb3KHvjLIfBNq-OWx9lU36I8X2Ysg6avUv5o3k-ggxJSRhWWUvOuriow7_TAeBDiSir-VdneLC2gjCuyarrCqt7_FT4bIFY-QKdSlp/s1600/image5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="136" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizvj9rfzgbt_-ik6PwC9lwcolyhZ2jts_A095bbIyb3KHvjLIfBNq-OWx9lU36I8X2Ysg6avUv5o3k-ggxJSRhWWUvOuriow7_TAeBDiSir-VdneLC2gjCuyarrCqt7_FT4bIFY-QKdSlp/s640/image5.png" width="640" /></a></div><br />
<div class="separator" style="clear: both; text-align: center;"></div><span xmlns="">And here is the code that Mr. Pinaaki krishnasharma helped me with.</span><br />
<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7VWVtQpSfuFqACdaaHz8psAP15aq4Mn4g7OHhcNHbXQNktDQPIm1hSdQWdlhZxN6D6oz_cRicXvxVFHpeeAtjB_kk5ClP7-Ip0v0TNIJ_owE9Ds1kIe8-7JC9VOyvZpeZtCeUeABwDheZ/s1600/image6.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="156" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi7VWVtQpSfuFqACdaaHz8psAP15aq4Mn4g7OHhcNHbXQNktDQPIm1hSdQWdlhZxN6D6oz_cRicXvxVFHpeeAtjB_kk5ClP7-Ip0v0TNIJ_owE9Ds1kIe8-7JC9VOyvZpeZtCeUeABwDheZ/s640/image6.png" width="640" /></a></div><br />
<div class="separator" style="clear: both; text-align: center;"></div><span xmlns=""> </span><br />
<span xmlns=""> </span><br />
<span xmlns="">You can find these templates and sample XML <a href="http://www.box.net/shared/m11m4sr41j" style="color: red;">here</a>. You can also follow Mr. Pinaaki krishnasharma blog <a href="http://pinaakikrishnasharma.blogspot.com/" style="color: red;">here</a>. He will be posting regular tips and notes on various Oracle Apps modules.</span><br />
<span xmlns=""></span>AK Lakshmananhttp://www.blogger.com/profile/03268792511249548944noreply@blogger.com0tag:blogger.com,1999:blog-4632568145083787579.post-91439573390133577132010-02-24T03:12:00.001-08:002010-06-07T05:14:25.472-07:00Inline If Else Statment<span xmlns=''><p>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.<br />
</p><p><br />
</p><p>Some statement if@inline:1=2 Result 1 end if if@inline:1=1 Result 1 end if<br />
</p><p><br />
</p><p>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 <a href='http://blogs.oracle.com/xmlpublisher/2007/12/if_have_cold_then_doctor_else.html'>Tim Dexter's Dec 2007 post.</a><a name='more'></a><br />
</p><p><br />
</p><p>He had explained this <em>xdoxslt:ifelse(boolean expression ,true result, false result)</em><br />
</p><p><br />
</p><p>Example,<br />
</p><p>xdoxslt:ifelse(<a href='http://blogs.oracle.com/xmlpublisher/2007/12/WM_FLAG'><span style='color:#3a5a87'>.//WM_FLAG</span></a>='C','Canceled','Approved')<br />
</p><p><br />
</p><p>This xdoxslt command is looking exactly like a SQL Decode statement with only one level ;) and it's a inline statement, that the sexy thing about this statement. Thanks Tim. <br />
</p></span>AK Lakshmananhttp://www.blogger.com/profile/03268792511249548944noreply@blogger.com1tag:blogger.com,1999:blog-4632568145083787579.post-51065536902896544962009-11-04T19:59:00.000-08:002010-06-07T05:15:02.904-07:00Printing Check Amount With Decimal Part<div>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.</div><div><br />
</div><div>Let me come straight to the actual problem.</div><div><br />
</div><div>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 </div><blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; "><?xdofx:lpad(PaymentAmount/Value,12,'*')?></blockquote><div><br />
</div><div>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<a name='more'></a></div><div><br />
</div><div>Now here the problem starts. I initially thought of using to_char/to_number like </div><blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; "><?xdofx:lpad(to_char(PaymentAmount/Value,'999,999,99.99'),12,'*')?> </blockquote><div>and format the value with 2 decimal part. But for some reason this did not work. I still get the amount as *******1200. Even I tried </div><blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; "><?xdofx:lpad(format-number:format-number:PaymentAmount/Value;'999G999D99',12,'*')?> </blockquote><div>but no luck.</div><div><br />
</div><div>Then something sparked in mind to search about any existing configuration/setup for this decimal part just like the <a href="http://flexfields.blogspot.com/2009/04/check-amount-not-printing-units-and_20.html"><span class="Apple-style-span" style="color:#CC0000;">units and subunits</span></a>. So started googling, checking payment user guide, installation guide, digging the check document gendration code, etc... but no luck on this even after spending 4hrs on this.</div><div><br />
</div><div>I wonder whether we have such configuration/setup to define the number of decimal part. Because in europe they use comma(,) instead of dot(.) as the decimal seperator. Where do we do this setup in the application? Is there any profile option to do this? its still a blindspot for me?</div><div><br />
</div><div>Finally I decided to getback on resolving this issue programatically. So started trying all possible ways to do this and no luck. Even lot of forums have questions about this and there is no good solution suggested.</div><div><br />
</div><div>Around 9pm I decided not to think smarter and tried to do with some if and else conditions and came up with the below logic,</div><div><br />
</div><blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; ">xdoxslt:set_variable($_XDOCTX, 'ln_dot', xdoxslt:replace(PaymentAmount/Value,'.',''))</blockquote><blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; "><?choose:?></blockquote><blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; "><?when:xdoxslt:get_variable($_XDOCTX,'ln_dot')= xdoxslt :to_number(PaymentAmount/Value)?></blockquote><blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; "><?xdofx:lpad(PaymentAmount/Value||'.00'||PaymentAmount/Currency/Code,15,'*')?></blockquote><blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; "><?end when?></blockquote><blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; "><?otherwise:?></blockquote><blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; "><?choose:?></blockquote><blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; "><?when:xdoxslt:get_variable($_XDOCTX,'ln_dot') div 100 = xdoxslt :to_number(PaymentAmount/Value)?></blockquote><blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; "><?xdofx:lpad(PaymentAmount/Value||PaymentAmount/Currency/Code,15,'*')?></blockquote><blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; "><?end when?></blockquote><blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; "><?otherwise:?></blockquote><blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; "><?xdofx:lpad(PaymentAmount/Value||'0'||PaymentAmount/Currency/Code,15,'*')?></blockquote><blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; "><?end otherwise?></blockquote><blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; "><?end choose?></blockquote><blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; "><?end otherwise?></blockquote><blockquote class="gmail_quote" style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0.8ex; border-left-width: 1px; border-left-color: rgb(204, 204, 204); border-left-style: solid; padding-left: 1ex; "><?end choose?></blockquote><div><br />
</div><div><br />
</div><div>Above logic when statement handles the below data conditions,</div><div>1. Amount without decimals like 1200</div><div>2. Anount with one decimal value like 1200.9</div><div>3. Amoutn with 2 decimal value like 1200.98</div><div><br />
</div><div>How I handle contion 2 and 3? its simple math :) I am divding the replaced value with 100 to check whether I had 2 decimal digits in the original amount. So that I can concatenate one zero at the end.</div><div><br />
</div><div>I know this sounds like a crazy school kid logic :) but it helped me :D I know you readers will have great logic to handling this. If so please do share it in the comment section.</div><div><br />
</div><div>But even still I think there should be a setup for managing this decimal part, if not oracle should consider about having atleast in there future application release 12.X.X</div>AK Lakshmananhttp://www.blogger.com/profile/03268792511249548944noreply@blogger.com2tag:blogger.com,1999:blog-4632568145083787579.post-52320482621737909662009-10-04T16:19:00.000-07:002010-08-09T11:06:58.142-07:00Handling Text Wrapping with Fixed Rows in BI Desktop Publisher<div style="text-align: left;">After seeing Tim Dexter post about <a href="http://blogs.oracle.com/xmlpublisher/2009/10/row_filler.html"><span class="Apple-style-span" style="color: #cc0000;">fixed line and row filler</span></a> decided to post about the handling string wrapping in the template.</div><div class="MsoNormal"><o:p>Ok let me start explaining how I handle the string wrapping. Before that let me let me tell you the requirement.</o:p></div><div class="MsoNormal">1. Need to display fixed lines.</div><div class="MsoNormal"><o:p></o:p></div><div class="MsoNormal">2. If there are only few lines and the page is not filled then we need to print blank lines to fill the page.<o:p></o:p></div><div class="MsoNormal">3. At the end of the report we need to display the summary as shown above.<o:p></o:p></div><div class="MsoNormal">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. </div><a name='more'></a><div class="MsoNormal">Here is the invoice template screen shot,</div><div class="MsoNormal"><span class="Apple-style-span" style="color: #222222; font-size: 100%;"><span class="Apple-style-span" style="font-size: 12px;"><span class="Apple-style-span" style="color: black; font-size: 16px;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhkXQUT7-SrqxSM62JOnzhuf0mVwxh52nqZjNcQj_fe-CUJ_YYszlWz0P0CXqZKYgjyg83AeClIDgmaWwMW9vWvShF2Isk3JD1ehYoQ8xa3ZC1z4IFQrCt7BHfGoH0JMl6bivIe9gZiz1DO/s1600-h/invoice-print.GIF" onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}"><img alt="" border="0" id="BLOGGER_PHOTO_ID_5399295642396869810" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhkXQUT7-SrqxSM62JOnzhuf0mVwxh52nqZjNcQj_fe-CUJ_YYszlWz0P0CXqZKYgjyg83AeClIDgmaWwMW9vWvShF2Isk3JD1ehYoQ8xa3ZC1z4IFQrCt7BHfGoH0JMl6bivIe9gZiz1DO/s400/invoice-print.GIF" style="cursor: pointer; height: 199px; width: 400px;" /></a></span></span></span></div><div class="MsoNormal"><span class="Apple-style-span" style="font-weight: bold;">To handle the string wrapping I had taken the number of characters that will fit into item description field (this is expected to wrap), in my example 24 characters will fit in a single line. </span></div><div class="MsoNormal"><span class="Apple-style-span" style="font-weight: bold;">Here is the mini logic i have will also consider single word wrapping to next line.</span></div><div class="MsoNormal"><span class="Apple-style-span" style="font-weight: bold;"> </span></div><div class="MsoNormal"><span class="Apple-style-span" style="font-weight: bold;">ln_Act_lgth := length(lc_data);<br />
ln_lgth:=0;<br />
ln_row:=1; <br />
loop<br />
-- Get the first word of the string without the space. <br />
lc_word := substr(lc_data,1,instr(lc_data,' ')-1);<br />
--get the length of the existing word on the current line with the current word. <br />
ln_lgth := ln_lgth + NVL(length(lc_word),0); <br />
--Add 1 for space when calculating the total length so.<br />
ln_tot_lgth := ln_tot_lgth + NVL(length(lc_word),0)+1; <br />
--if length of the existing word on the current line with the current word <br />
--is more than 24 means then the line will break into two.<br />
IF ln_lgth >= 24 THEN<br />
ln_row := ln_row+1;<br />
ln_lgth:= NVL(length(lc_word),0);<br />
END IF;<br />
ln_lgth := ln_lgth+1;<br />
exit when ln_tot_lgth >= ln_Act_lgth;<br />
--get the balance remaining string apart from the current word.<br />
lc_data := substr(lc_data,instr(lc_data,' ')+1); <br />
end loop;</span></div><div class="MsoNormal"><span class="Apple-style-span" style="font-weight: bold;"> </span></div><div class="MsoNormal">And again as per my example 36 lines will fit into one page (including the summary).</div><div class="MsoNormal"><o:p></o:p></div><div class="MsoNormal">Now let me give you the pseudocode of my logic.<o:p></o:p></div><div class="MsoNormal"><b><span style="color: #993366;">START<o:p></o:p></span></b></div><div class="MsoNormal"><st1:place st="on"><span style="color: blue;">Loop</span></st1:place></div><div class="MsoNormal"><st1:place st="on"><span style="color: blue;">1. <span class="Apple-style-span" style="color: black;">Print the invoice row.</span></span></st1:place></div><div class="MsoNormal"><st1:place st="on"><span style="color: blue;"><span class="Apple-style-span" style="color: black;">2. Keep the count of number of lines each invoice row is taking. ex: v_rowcount = v_rowcount + length(ITEM_DESCRIPTION) MOD 24(this is the text wrapping handler)</span></span></st1:place></div><div><div><div class="MsoNormal" style="margin-left: 0.25in; text-indent: 0.25in;"><span class="Apple-style-span" style="color: blue;"></span></div><span class="Apple-style-span" style="color: blue;"></span><br />
<div class="MsoNormal" style="margin-left: 0.25in; text-indent: 0.25in;"><span class="Apple-style-span" style="color: blue;"><span style="color: blue;">IF</span> <span class="Apple-style-span" style="color: black;">v_rowcount >= 36</span> <span style="color: blue;">THEN</span></span></div><div class="MsoNormal" style="margin-left: 1.25in; text-indent: -0.25in;"><span class="Apple-style-span" style="color: blue;"><span style="font-family: Wingdings;"><span class="Apple-style-span" style="color: black;">§</span><span style="font: 7pt "Times New Roman";"><span class="Apple-style-span" style="color: black;"> </span></span></span><span class="Apple-style-span" style="color: black;">Print the line to close the box.</span></span></div><div class="MsoNormal" style="margin-left: 1.25in; text-indent: -0.25in;"><span class="Apple-style-span" style="color: blue;"><span class="Apple-style-span" style="color: black;"><span class="Apple-style-span" style="color: blue;"><span style="font-family: Wingdings;"><span class="Apple-style-span" style="color: black;">§</span><span style="font: 7pt "Times New Roman";"><span class="Apple-style-span" style="color: black;"> </span></span></span><span class="Apple-style-span" style="color: black;">Reset v_rowcount to zero.</span></span></span></span></div><span class="Apple-style-span" style="color: blue;"></span><br />
<div class="MsoNormal" style="text-indent: 0.5in;"><span style="color: blue;">END IF;<o:p></o:p></span></div><div class="MsoNormal"><span style="color: blue;">END <st1:place st="on">LOOP</st1:place></span></div><div class="MsoNormal"><span style="color: blue;"><st1:place st="on">3. <span class="Apple-style-span" style="color: black;">Take the balance rows available to print empty lines (ex v_balance_line = v_rowcount - 36)</span></st1:place></span></div><div class="MsoNormal"><span style="color: blue;"><st1:place st="on"><span class="Apple-style-span" style="color: black;">4. Now I will have to cases. First is there may not be sufficient lines available to print the summary. In this case we need to display empty lines and then print at the end of next page. So,</span></st1:place></span></div><span class="Apple-style-span" style="color: blue;"> </span><br />
<div class="MsoNormal" style="margin-left: 0.5in; text-align: left;"><span class="Apple-style-span" style="color: blue;"><span style="color: blue;">IF</span><span class="Apple-style-span" style="color: black;"> v_balance_lines <><span class="Apple-style-span" style="color: black;">)</span> <span style="color: blue;">THEN</span><o:p></o:p></span></span></div><div class="MsoNormal" style="margin-left: 1.25in; text-indent: -0.25in;"><span class="Apple-style-span" style="color: blue;"><span style="font-family: Wingdings;"><span class="Apple-style-span" style="color: black;">§</span><span style="font: 7pt "Times New Roman";"><span class="Apple-style-span" style="color: black;"> </span></span></span><span class="Apple-style-span" style="color: black;">Print blank lines for remaining lines to find the number of blank rows to be printed use the variable v_rowcount. so here the no of blank lines will be 36 - (v_rowcount)</span></span></div><div class="MsoNormal" style="margin-left: 1.25in; text-indent: -0.25in;"><span class="Apple-style-span" style="color: blue;"><span style="font-family: Wingdings;"><span class="Apple-style-span" style="color: black;">§</span><span style="font: 7pt "Times New Roman";"><span class="Apple-style-span" style="color: black;"> </span></span></span><span class="Apple-style-span" style="color: black;">Print the line to close the box.</span></span></div><div class="MsoNormal" style="margin-left: 1.25in; text-indent: -0.25in;"><span class="Apple-style-span" style="color: blue;"><span style="font-family: Wingdings;"><span class="Apple-style-span" style="color: black;">§</span><span style="font: 7pt "Times New Roman";"><span class="Apple-style-span" style="color: black;"> </span></span></span><span class="Apple-style-span" style="color: black;">Enable a flag to say we need to print empty rows in the 29 empty rows in the next page, So that the summary comes at the end.</span></span></div><div class="MsoNormal" style="text-indent: 0.5in;"><span class="Apple-style-span" style="color: blue;"><span style="color: blue;">ELSE IF</span> <span class="Apple-style-span" style="color: black;">v_balance_lines > 7 </span><span style="color: blue;">THEN</span></span></div><div class="MsoNormal" style="margin-left: 99pt; text-indent: -0.25in;"><span class="Apple-style-span" style="color: blue;"><span style="font-family: Wingdings;"><span class="Apple-style-span" style="color: black;">§</span><span style="font: 7pt "Times New Roman";"><span class="Apple-style-span" style="color: black;"> </span></span></span><span class="Apple-style-span" style="color: black;">Print blank lines by making sure you leave 7 lines to print summary. to find the number of blank rows to be printed use the variable v_rowcount. so here the no of blank lines will be 36 - (v_rowcount+ 7)</span></span></div><div class="MsoNormal" style="margin-left: 99pt; text-indent: -0.25in;"><span class="Apple-style-span" style="color: blue;"><span style="font-family: Wingdings;"><span class="Apple-style-span" style="color: black;">§</span><span style="font: 7pt "Times New Roman";"><span class="Apple-style-span" style="color: black;"> </span></span></span><span class="Apple-style-span" style="color: black;">Print the line to close the box.</span></span></div><div class="MsoNormal" style="margin-left: 99pt; text-indent: -0.25in;"><span class="Apple-style-span" style="color: blue;"><span style="font-family: Wingdings;"><span class="Apple-style-span" style="color: black;">§</span><span style="font: 7pt "Times New Roman";"><span class="Apple-style-span" style="color: black;"> </span></span></span><span class="Apple-style-span" style="color: black;">Print the summary</span></span></div><div class="MsoNormal" style="text-indent: 0.5in;"><span class="Apple-style-span" style="color: blue;"><span style="color: blue;">END IF;</span></span></div><div class="MsoNormal" style="text-indent: 0.5in;"><span class="Apple-style-span" style="color: blue;"><span style="color: blue;"><span class="Apple-style-span" style="color: black;">5. Check whether the flag is enables to print 29 blank lines. If yes,</span></span></span></div><div class="MsoNormal" style="margin-left: 1.25in; text-indent: -0.25in;"><span class="Apple-style-span" style="color: blue;"><span style="font-family: Wingdings;"><span class="Apple-style-span" style="color: black;">§</span><span style="font: 7pt "Times New Roman";"><span class="Apple-style-span" style="color: black;"> </span></span></span><span class="Apple-style-span" style="color: black;">Print 29 blank lines.</span></span></div><div class="MsoNormal" style="margin-left: 1.25in; text-indent: -0.25in;"><span class="Apple-style-span" style="color: blue;"><span style="font-family: Wingdings;"><span class="Apple-style-span" style="color: black;">§</span><span style="font: 7pt "Times New Roman";"><span class="Apple-style-span" style="color: black;"> </span></span></span><span class="Apple-style-span" style="color: black;">Print the line to close the box.</span></span></div><div class="MsoNormal" style="margin-left: 1.25in; text-indent: -0.25in;"><span class="Apple-style-span" style="color: blue;"><span style="font-family: Wingdings;"><span class="Apple-style-span" style="color: black;">§</span><span style="font: 7pt "Times New Roman";"><span class="Apple-style-span" style="color: black;"> </span></span></span><span class="Apple-style-span" style="color: black;">Print the summary</span><o:p></o:p></span></div><div class="MsoNormal"><span class="Apple-style-span" style="color: blue;"><b><span style="color: #993366;">END<o:p></o:p></span></b></span></div><span class="Apple-style-span" style="color: blue;"></span></div></div><div class="MsoNormal"><b><span style="color: blue;"><o:p> </o:p></span></b></div><div class="MsoNormal">That’s all. If you closely watch the above pseudocode you will get the logic of handling text wrapping.</div><div class="MsoNormal"><o:p>I know as usual I had not formatted this post well But for your convenience I had attached the sample RTF and XML file <a href="http://www.box.net/shared/macr8kkr76"><span class="Apple-style-span" style="color: #cc0000;">here</span></a></o:p></div>AK Lakshmananhttp://www.blogger.com/profile/03268792511249548944noreply@blogger.com6tag:blogger.com,1999:blog-4632568145083787579.post-21504529884548938402009-04-20T13:57:00.001-07:002009-11-03T12:50:48.431-08:00Check Amount not printing the Units and SubunitsSome of you must have noticed the check amount that is getting printing words may not be printing the units and subunits of the amount.<br /><br />For example, if you have the check currency code in <strong>USD</strong>, then the amount in words will be printing some thing like "<strong>One Thousand Four Hundred Twenty-Two Dollars And Eighty-Two Cents*****</strong>" <a name='more'></a><br /><br />but if you have the check currecy code in <strong>SGD</strong>, then the amount in words will be printing some thing like "<strong>One Thousand Four Hundred Twenty-Two And Eighty-Two *****</strong>"<br /><br />But some of the bank will be very particular about printing the units (dollar, euro,..) and subunits (cents,..) in the check print.<br /><br />Ok here is the solution for this, From Release 12.0.2 oracle had defined some messages which we can use to print these units and subunits.<br /><br /><strong><span style="text-decoration:underline;">Unit:</span></strong><br /><br /><strong><span style="font-weight:normal;">IBY_AMT_IN_WORD_UP_[CURR] (for plural eg. Dollars)</span></strong><br /><br />IBY_AMT_IN_WORD_US_[CURR] (for singular eg. Dollar)<br /><br /><strong><span style="text-decoration:underline;">Subunit:</span></strong><br /><br />IBY_AMT_IN_WORD_SUP_[CURR] (for plural eg. Cents)<br /><br />IBY_AMT_IN_WORD_SUS_[CURR] (for plural eg. Cent)<br /><br />Here [CURR] is the currency code.<br /><br />I know this may be a simple known solution for lot of you but this will be a grey area for lots and lot of new commers :)<div><br /></div><div>for more details refer metalink document ID <b>465244.1</b></div>AK Lakshmananhttp://www.blogger.com/profile/03268792511249548944noreply@blogger.com1tag:blogger.com,1999:blog-4632568145083787579.post-9421698339294079452008-08-27T02:45:00.001-07:002009-11-04T19:39:03.812-08:00How-To: Disable the preference menu from SSA pageToday 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.<br /><br /><strong><span style="text-decoration:underline;">Solution 1:</span></strong><br /><br />Updating the profile option "<strong>General Preferences Show Flag</strong>" value to 'No'.<br /><a name='more'></a><br />We can disable the preferences menu from the SSA page by setting the profile <em>General Preferences Show Flag </em> value to No. We can set this option on all the levels like Site, Application, Responsibility and User. So I can use this profile option to do my needful.<br /><br /><em>metalink note: 275876.1</em><br /><br /><strong><span style="text-decoration:underline;">Solution 2:</span></strong><br /><br />The second solution is my adding the <strong>General Preferences </strong>function in the menu exclusion list. But this solution will help only for responsibility level. But I can use this for my current scenario.<br /><br /><em>(I myself figured out this solution)</em><br /><br /><strong><span style="text-decoration:underline;">Solution 3:</span></strong><br /><br />The third solution is basically bit different which I got it from the metalink note: 299218.1<br /><br />As per this metalink note we need to remove the prompt of General Preferences function under the menus ICX_STANDARD_GLOBAL_MENU and ICX_GLOBAL_MENU. But this will remove the preferences menu from the entire SSA. So I will not consider this solution for my scenario.<br /><br />Anyhow whoever come across this requirement can pick one from the above mentioned solutions :)AK Lakshmananhttp://www.blogger.com/profile/03268792511249548944noreply@blogger.com0tag:blogger.com,1999:blog-4632568145083787579.post-11754192957593639412008-08-26T01:49:00.001-07:002009-11-01T08:04:38.891-08:00Getting the application URL<p style="text-align:justify;">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 <strong>ICX_PARAMETERS </strong>which gives you the information of the application URL. But keep in mind you need the username and password for the application ;)</p><br /><p style="text-align:justify;"> </p>AK Lakshmananhttp://www.blogger.com/profile/03268792511249548944noreply@blogger.com0tag:blogger.com,1999:blog-4632568145083787579.post-36123432938668430632008-08-26T01:34:00.001-07:002009-11-04T19:33:14.766-08:00API to insert GL Code CombinationI 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.<br /><a name='more'></a><br />Even I had come across this situation in one of my previous project. I have been asked to insert 100k code combinations using the data loader. First I tried data loader only. It took 3 hrs to insert 3k records :( But finally I decided to write an API which will insert all these code combinations in 5-10 mins. Then I get to know this API <strong>fnd_flex_keyval.validate_segs.</strong><br /><br />This API will return a boolean value 'TRUE' or 'FALSE'. This API is there in R12 instance also. Here is the sample call to this API which I used in my code,<br /><blockquote>SELECT chart_of_accounts_id<br /><br />INTO l_coa_id<br /><br />FROM gl_sets_of_books<br /><br />WHERE set_of_books_id = fnd_profile.VALUE ('GL_SET_OF_BKS_ID');<br /><br />l_keyval_status := fnd_flex_keyval.validate_segs ('CREATE_COMBINATION', 'SQLGL', 'GL#', l_coa_id, gl_code_rec.concatenated_segments, 'V', SYSDATE, 'ALL', NULL, NULL, NULL, NULL, FALSE, FALSE, NULL, NULL,NULL );</blockquote><br /><div><br /><div><br /><div>This is just an example how I used in my code and <em>gl_code_rec.concatenated_segments</em> is the altual code combination needs to be inserted. </div><br /><div>I am not sure this information is really informative. But thought of sharing. </div><br /></div><br /></div>AK Lakshmananhttp://www.blogger.com/profile/03268792511249548944noreply@blogger.com0tag:blogger.com,1999:blog-4632568145083787579.post-27949652566365432852008-08-23T14:56:00.001-07:002009-11-04T19:32:10.819-08:00Useful Sysadmin SQL statementsAs 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.<br /><br />1. SQL Statement to find the list of responsibilities, which got the access to a particular function.<a name='more'></a><br /><blockquote><br /><br />SELECT *<br /><br /> FROM fnd_responsibility_vl<br /><br /> WHERE menu_id IN<br /><br /> (SELECT menu_id<br /><br /> FROM fnd_menus_vl<br /><br /> WHERE menu_id IN<br /><br /> (SELECT menu_id<br /><br /> FROM fnd_menu_entries_vl START<br /><br /> WITH function_id =<br /><br /> (SELECT function_id FROM fnd_form_functions WHERE function_name='<strong><span style="color:#0000ff;">FUNCTION_NAME</span></strong>'<br /><br /> ) CONNECT BY PRIOR menu_id = sub_menu_id<br /><br /> )<br /><br /> )<br /><br /></blockquote><br />2. SQL Statement to find the list of responsibilities, which got the access to any of the function(s) of a menu.<br /><blockquote><br /><br /> SELECT *<br /><br /> FROM fnd_responsibility_vl<br /><br /> WHERE menu_id IN<br /><br /> (SELECT menu_id<br /><br /> FROM fnd_menus_vl<br /><br /> WHERE menu_id IN<br /><br /> (SELECT menu_id<br /><br /> FROM fnd_menu_entries_vl START<br /><br /> WITH function_id IN<br /><br /> (SELECT function_id<br /><br /> FROM fnd_form_functions<br /><br /> WHERE function_id IN<br /><br /> (SELECT function_id<br /><br /> FROM fnd_menu_entries_vl START<br /><br /> WITH menu_id = <strong><span style="color:#0000ff;">YOUR_MENU_ID</span></strong> CONNECT BY PRIOR sub_menu_id = menu_id<br /><br /> )<br /><br /> ) CONNECT BY PRIOR menu_id = sub_menu_id<br /><br /> )<br /><br /> )<br /><br /></blockquote><br />3. SQL Statement to find the list of users who got the access to any of the function(s) of a menu.<br /><blockquote><br /><br />SELECT user_name ,<br /><br /> USER_ORIG_SYSTEM_ID responsibility_id,<br /><br /> start_date ,<br /><br /> expiration_date<br /><br /> FROM wf_all_user_roles<br /><br /> WHERE USER_ORIG_SYSTEM_ID IN<br /><br /> (SELECT responsibility_id<br /><br /> FROM fnd_responsibility<br /><br /> WHERE menu_id IN<br /><br /> (SELECT menu_id<br /><br /> FROM fnd_menus<br /><br /> WHERE menu_id IN<br /><br /> (SELECT menu_id<br /><br /> FROM fnd_menu_entries START<br /><br /> WITH function_id IN<br /><br /> (SELECT function_id<br /><br /> FROM fnd_form_functions<br /><br /> WHERE function_id IN<br /><br /> (SELECT function_id<br /><br /> FROM fnd_menu_entries_vl START<br /><br /> WITH menu_id = <strong><span style="color:#0000ff;">YOUR_MENU_ID</span></strong> CONNECT BY PRIOR sub_menu_id = menu_id<br /><br /> )<br /><br /> ) CONNECT BY PRIOR menu_id = sub_menu_id<br /><br /> )<br /><br /> )<br /><br /> )<br /><br />AND sysdate BETWEEN start_date AND NVL(expiration_date,sysdate)<br /><br /></blockquote><br />Ok here the above mentioned statements were structured based on the 11.5.10 application. Since there is no data model chance in the sysadmin side for R12, these queries should work on the R12 application. You can cross verify the results of these statements using the Functional Administrator responsibility.<br /><br />Readers if you see any of the statement(s) mentioned above is not giving the expected result then do suggest your changes in the comments section and obviously if you have a better query you can post them in the comments section.AK Lakshmananhttp://www.blogger.com/profile/03268792511249548944noreply@blogger.com0tag:blogger.com,1999:blog-4632568145083787579.post-65573679892933528312008-08-22T13:51:00.001-07:002009-11-04T19:38:44.544-08:00How-To: Find all the menus to which a function is attached?<p style="text-align:justify;">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 <strong>Functional Administrator</strong> responsibility. Thanks to my colleague and good friend <em>Vivek Srinivasan</em> who told me about this responsibility.</p><a name='more'></a><br /><p style="text-align:justify;">In this responsibility you can search for all the menus to which a function is been attached. even you can see the responsibilities to which a menu is been attached. The good part is, it will list all the responsibility even if the menu is in the submenu of the main menu and same for function.</p><br /><br />This responsibility even have lot many features which the sysadmin and really make use of it on there daily job queries.<br /><br />I just posted this info thinking it might help some body. I will try to post some SQL queries which will do even more than what this responsibility gives ;). So keep looking this post for more updates.AK Lakshmananhttp://www.blogger.com/profile/03268792511249548944noreply@blogger.com0tag:blogger.com,1999:blog-4632568145083787579.post-70533516996618967532008-07-22T08:43:00.001-07:002009-11-01T09:05:25.918-08:00XSL function to convert number to word<p style="text-align:justify;">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. </p><br /><p class="MsoNormal" style="text-align:justify;">Yes this helps a lot when you are doing a check print report. Lot of them posted questions on oracle forum. The great <em>Gareth</em>, 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 <span><strong><span>ap_amount_utilities_pkg.ap_convert_number </span></strong></span><span><span>to do this. </span></span></p><a name='more'></a><br /><br /><blockquote><br /><p class="MsoNormal" style="text-align:justify;"><span><span><span style="color:#0000ff;">SQL> select ap_amount_utilities_pkg.ap_convert_number(trunc(12345678)) from dual;</span></span></span></p><br /><p class="MsoNormal" style="text-align:justify;"><span><span><span style="color:#0000ff;"> AP_AMOUNT_UTILITIES_PKG.AP_CONVERT_NUMBER(TRUNC(12345678))</span></span></span></p><br /><p class="MsoNormal" style="text-align:justify;"><span><span><span style="color:#0000ff;">----------------------------------------------------------------------------</span></span></span></p><br /><p class="MsoNormal" style="text-align:justify;"><span> <span><span style="color:#0000ff;">Twelve million three hundred forty-five thousand six hundred seventy-eight</span></span></span></p><br /></blockquote><br /><p class="MsoNormal" style="text-align:justify;"><span><span><span style="color:#0000ff;"><span style="color:#000000;">But here is a question…. Can we call a PL/SQL function in BI Publisher at runtime and return a value?………. </span></span></span></span></p><br /><p class="MsoNormal" style="text-align:left;"><span><span>If you are thinking how to do this then here is a our guru Tim got a sample extension to do this <span><a href="http://blogs.oracle.com/xmlpublisher/2007/12/03/"><span>http://blogs.oracle.com/xmlpublisher/2007/12/03/</span></a></span></span></span></p><br /><p class="MsoNormal" style="text-align:justify;"><span><span>And finally if this is not the one your looking for ;) and you need a direct XSL function to do. Then here it is, in the latest BI Publisher oracle BIP Team had added the function <strong>toWordsAmt.<span style="font-weight:normal;"> </span></strong></span></span></p><br /><p class="MsoNormal" style="text-align:justify;"><span><span>Here is an example to call this function </span></span></p><br /><br /><blockquote><br /><p class="MsoNormal" style="text-align:justify;"><span style="color:#0000ff;"><?xdoxslt:toWordsAmt(12345.98)?> </span></p><br /><p class="MsoNormal" style="text-align:justify;"><span style="color:#0000ff;"><span style="color:#000000;"><strong><span><span style="color:#0000ff;">Output: </span></span></strong><span><span style="color:#0000ff;">Twelve Thousand Three Hundred Fourty Five and paise Ninety Eight</span></span></span></span></p><br /></blockquote><br /><p class="MsoNormal" style="text-align:justify;">But here every body wonders why it shows paise and what if you want to show it in cent (<em>for USD</em>). Even I had the same question. So I posted my query in the oracle forum. But the answer from <em>Vitsrini </em>was little surprise for me. You know why. look the reply from Vitsrini</p><br /><br /><blockquote><br /><p class="MsoNormal" style="text-align:justify;"><em>i tried, its a bug, Paise has been hardcoded it seems, they should have coded by looking @ the currency locale settings. i guess, Tim will accept this :).</em></p><br /></blockquote><br /><p class="MsoNormal" style="text-align:justify;">and here is our guru Tim reply which gives me some hope to use this function in the future.</p><br /><br /><blockquote><br /><p class="MsoNormal" style="text-align:justify;"><em>I have to admit not knowing about this function until it arrived and checking the code its hardcoded!</em></p><br /><p class="MsoNormal" style="text-align:justify;"><em>These were some functions that were developed in India for one of Oracle's aquisitions that operates almost exclusively in India.</em></p><br /><br /><em> </em><em>I'll log an enhancement for this to get other languages! Might take some time as looking at the code it does not use any i18n library its just plain coded for rupees!</em></blockquote><br /><p class="MsoNormal" style="text-align:justify;">So all of you wait for the new upgraded version of this function in the next BIP release (just hoping ;) ). To know more about this conversion you can check the oracle BIP forum http://forums.oracle.com/forums/thread.jspa?threadID=656644&tstart=0</p>AK Lakshmananhttp://www.blogger.com/profile/03268792511249548944noreply@blogger.com0tag:blogger.com,1999:blog-4632568145083787579.post-62470674439378483682008-07-17T09:59:00.001-07:002009-11-04T19:32:36.060-08:00FND_PROFILE APIIn oracle user Profile functionality is provided in the <strong>FND_PROFILE</strong> package and the FNDSQF library.<br /><br />What is inside this API:<br /><ul><br /><li>Retrieve user profile values for the current run-time environment</li><br /><li>Set user profile values for the current run-time environment</li><br /></ul><a name='more'></a><br />There are various Objects that can be used with this API’s. These are discussed below:<br /><br /><strong>1. Put :</strong>This can be used to put a value to the specified user profile option.<br /><br />Usage:<br /><ul><br /><li>FND_Profile.Put('PROFILE_NAME', 'New_Value')</li><br /><li>FND_Profile.Put(’USERNAME’, Usr_Name)</li><br /><li>FND_Profile.Put(’RESP_ID’, Resp_ID)</li><br /><li>FND_Profile.Put(’RESP_APPL_ID’, Resp_App_ID)</li><br /><li>FND_Profile.Put(’USER_ID’, User_ID)</li><br /></ul><br /><strong>2.DEFINED :</strong> this is function returns TRUE if a value has been assigned to the specified profile option.<br /><br />Usage:<br /><ul><br /><li>SELECT fnd_profile.defined(’ACCOUNT_GENERATOR:DEBUG_MODE’) ACC_GEN_DEBUG_SESSION_MODE FROM DUAL;</li><br /></ul><br /><strong>3.GET :</strong>This is used to retrieve the current value of the specified user profile option<br /><br />Usage :<br /><br />Different type of options can be retrieved like<br /><ul><br /><li>FND_Profile.Get('PROFILENAME', Profile_name);</li><br /><li>FND_Profile.Get(’CONC_LOGIN_ID’, Conc_login_id);</li><br /><li>FND_Profile.Get(’LOGIN_ID’, loginid);</li><br /></ul><br /><strong>4.VALUE :</strong> This is function which returns a character string. Used to retrieve the current value of the specified user profile option.<br /><br />Usage:<br /><ul><br /><li>fnd_profile.value('PROFILEOPTION')</li><br /><li>fnd_profile.value(’MFG_ORGANIZATION_ID’)</li><br /><li>fnd_profile.value(’login_ID’)</li><br /><li>fnd_profile.value(’USER_ID’)</li><br /><li>fnd_profile.value(’USERNAME’)</li><br /><li>fnd_profile.value(’CONCURRENT_REQUEST_ID’)</li><br /><li>fnd_profile.value(’GL_SET_OF_BKS_ID’)</li><br /><li>fnd_profile.value(’ORG_ID’)</li><br /><li>fnd_profile.value(’SO_ORGANIZATION_ID’)</li><br /><li>fnd_profile.value(’APPL_SHRT_NAME’)</li><br /><li>fnd_profile.value(’RESP_NAME’)</li><br /><li>fnd_profile.value(’RESP_ID’)</li><br /></ul><br /><strong>5.VALUE_WNPS:</strong> This is a function, returns a character string. This is Used to retrieve the current value of the specified user profile option without caching it.<br /><br /><strong>6.SAVE_USER :</strong>This is function used to save a value for a profile option permanently to the database, for the current user level. It is necessary to explicitly issue a commit when using this function. Returns TRUE if profile option is successfully saved, otherwise FALSE.<br /><br /><strong>7.SAVE :</strong>This is function used to save a value for a profile option permanently to the database, for a specified level. It is necessary to explicitly issue a commit when using this function. Returns TRUE if profile option is successfully saved, otherwise FALSE.<br /><br />Usage<br /><ul><br /><li>fnd_profile.save(’GUEST_USER_PWD’, ‘GUEST/ORACLE’, ‘SITE’);</li><br /></ul><br /><strong>8.INITIALIZE</strong> :This is used by internal Applications Object Library to initialize the internal profile information at the level context.<br />The cache is first cleared of all database options.<br /><br />Usage:<br /><ul><br /><li>fnd_profile.initialize(user_id);</li><br /></ul><br /><strong>9.PUTMULTIPLE :</strong>This is used by internal Applications Object Library to set multiple pairs of profile options and values.AK Lakshmananhttp://www.blogger.com/profile/03268792511249548944noreply@blogger.com0