Archive

Archive for the ‘Uncategorized’ Category

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-currency:ELEMENT_NAME;’currency-format-code’;’display-symbol’?>

Format Number
<?format-number:ELEMENT_NAME;’format-mask’?>

Round
<?xdoxslt:round(number(./ELEMENT_NAME),decimal_precision)?>

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
<?format-number:ELEMENT_NAME;’999G999D99′?>

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.

2015-09-23_1558

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

DataMatrix

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

^XA
^FO100,100
^BXN,10,200
^FH_^FDED_5FHAYES_09Oracle123^FS
^XZ

Data Matrix Barcode

^BXN,10,200

Field Hexadecimal Indicator = _

^FH_

Hexadecimal for Underscore (_)

_5F

Hexadecimal for a TAB

_09

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!