BI Bublisher: Selecting first record only

October 16, 2015 2 comments

I was looking for a way to only select data from the first record of a group to use in my Master Bill of Lading.  I only needed the address of one of the deliveries on the trip, so I wanted to select the address from the first delivery.  The other deliveries on the trip would be have the same addresses, at least how we are configuring the system.

I found this on a blog post at which seemed to be the answer:

<?for-each:EMPLOYEES[position()='1' or position()=last()]?>

I tried it, and different combinations, for quite some time.  I tried single quotes, double quotes, I tried it in an IF statement. It turns out the single quotes were causing problems.  This is the way it finally worked:


I wonder if Oracle tested this before they published the blog entry.  At least they pointed me in the right direction.

Oracle WMS RUP 12

September 30, 2015 1 comment

Logistics Consolidated RUP12 (VERSION 12.1.1 TO 12.1.3 [RELEASE 12.1])

Patch: 21220763

Fixed Bugs:

Read more…

Oracle BI Publisher Functions Round Differently, Depending On Function

September 23, 2015 Leave a comment

I ran into a problem the other day trying to fix a printed Commercial Invoice calculation we are doing in BI Publisher. Turns out the issue was that the Format Number and Format Currency functions use a different rounding algorithm than the Round function! Here is the story of how I came to find this out.

We have some prices that are in 10ths of a cent; three decimal of precision on currency. When we add up a line total on a Commercial Invoice, we format the answer using the format-currency function.  At each line we also add to a running total variable which is displayed at the end of the document.  When we add to the running total, we have to make sure not to add the 3rd decimal, as that would likely cause a rounding error in the total verse what is showed on the lines.

Here are all the function definitions for the applicable functions we are using:

Format Currency

Format Number


Set Variable
<?xdoxslt:set_variable($_XDOCTX, ‘VARIABLE_NAME’, VARIABLE_VALUE)?>

Get Variable
<?xdoxslt:get_variable($_XDOCTX, ‘VARIABLE_NAME’)?>

How we are using the functions:

Set Variable for line total:
<?xdoxslt:set_variable($_XDOCTX, ‘v_line_total’, xdoxslt:get_variable($_XDOCTX, ‘v_quantity’) *ORDER_LINE_PRICE )?>

Display line total
<?format-currency:xdoxslt:get_variable($_XDOCTX, ‘v_line_total’);ORDER_LINE_PRICE_CURRENCY_CODE;’true’?>

Add to running sum
<?xdoxslt:set_variable($_XDOCTX, ‘v_total’,xdoxslt:get_variable($_XDOCTX,’v_total’)+xdoxslt:round(xdoxslt :get_variable($_XDOCTX,’v_line_total’),2))?>

Display running total
<?format-currency:xdoxslt:get_variable($_XDOCTX, ‘v_total’)   ;../../LIST_CUSTOMER_ORDER/CUSTOMER_ORDER/CURRENCY_CODE;’true’?>

For reference and testing; not actually used on the document

The problem we are having is that the line totals are not adding up to the running total.

I did some tests and apparently the format based functions round using the “round-to-even” method, and the standard round function rounds using what you would expect, and learned in elementary school.


Appears as though I’m going to have to round the line total before I use the currency formatting function.

What an irritating situation.  It took a few hours to figure this out; huge waste of my time.  It would have been nice if they put the option of how to round as a parameter in the function, or documented this nonsense.

Data Matrix 2-D barcode with Underscore and TAB

June 17, 2015 1 comment


Our corporate overlords have decided an underscore as the ideal separation character between our first and last name within our usernames for Oracle E-Business Application. And we have some pretty horrendous password requirements (Contains a capital letter, number, 8+ characters long). If you have used a handheld scanner like the Intermec CK71 before, you know how much of a pain it is to type in text, especially text with an underscore or capital letters.

I got pretty sick of signing into the handheld scanners, so I decided to make a barcode to log me in. None of the 1-D barcodes would work since they are either not dense enough, or are not compatible with special characters/keystrokes like the underscore or TAB.

Our scanners are pretty high tech and have a 2-D imager on them, so Data Matrix or QR (Quick Response) codes were acceptable alternatives. I chose Data Matrix as QR was not enabled by default, where Data Matrix was.

It was much more difficult to encode the 2-D barcode than a 1-D Code 3 of 9 barcode; here is how I did it.

Full Label ZPL


Data Matrix Barcode


Field Hexadecimal Indicator = _


Hexadecimal for Underscore (_)


Hexadecimal for a TAB


It is important to use the Field Separator (^FS) at the end of the encoded text to prevent your application from putting in a line feed or carriage return into the encoded text, which would then be sent to the printer. That line feed or carriage return would manifest itself as an unprintable character that would make your login sequence not function correctly.

When the scanner reads the text and puts int into the Telnet application for the username, it will seem like it puts the password into the field, but after the scanner reads the text a second time, it will correctly pass the password into the password field. Our scanner is set to send an enter command after a successful scan, so all it takes is the scan of one barcode to log into Oracle. What a huge improvement!

Happy Barcodeing!

Outlook 2010 PDF Handler

May 8, 2015 1 comment

For several months I have been getting the message in Outlook 2010 that there was no PDF Handler.  I decided to do something about it today. I tried for about an hour.  One of the steps I took was to “repair” Office.  That required me to restart my computer, something I seldom do because of  how long it takes to do so, even with a Core i7.  The repair didn’t solve anything.

One thing I read online was to go into Adobe Acrobat reader and make sure some check-box was unchecked.  When I opened it up from the start menu, it starting going through some finalizing installation steps.  That seemed strange since I have been opening PDFs for some time now. I guess opening it directly is slightly different than opening the program by double-clicking a PDF file.  I got to the check-box and it was already unchecked.

I opened Outlook again, and my PDF Handler worked!  Apparently all it took was opening Adobe Acrobat Reader XI from the start menu.  What a crazy simple fix!

Oracle Support Quality

March 25, 2015 1 comment

I have had an Oracle Service Request (SR) open for 3 years now. The request has to do with how slow several security based objects are to be displayed when a user is set up using Oracle’s newer RBAC (Role Base Access Control) security structure.  Right now we are working on is the list of concurrent requests.

The SR was originally opened as a severity 2.  About a year into the SR, one of my DBAs had some work to do on it and was getting poor response from Oracle support, so he raised the severity to 1.  It has been at severity 1 for about 2 years now.

I have gotten several patches from Oracle.  The first one we applied showed no change in performance at all.  When they released the second patch, I insisted they provide details of what the improvement was so I could confirm A) it actually did improve, and B) they have actually replicated the problem internally.  I didn’t want to waste our DBA team’s time applying an alpha stage patch that has been untested.

I never got the proof that the patch was tested.  I gave up and had the patch applied. The patch broke the security all together; a user would have no access to concurrent requests at all.  Yes it was fast, but it didn’t work.

A third patch was released; it fixed the security, so at least the user would get access, but the performance was 50% slower than an un-patched system.

For several months I have been asking for updates on the SR every week or so, and it get the standard “we are working on it” or “requested status from development” response.

I have poked the bear, per-say, this last week by asking for some manger interaction.  Take a look at the latest response I got from them:


Note in the title of the SR I mentioned “W/RBAC.”  The first question in this SR update is “Are you using RBAC model to define security?”  ARE YOU KIDDING ME?