Archive

Author Archive

BI Publisher Subtemplates

September 12, 2016 Leave a comment

We are using BI Publisher Subtemplates to change our printed PO terms and conditions based on a supplier.  Subtemplates allow us to split the terms content from the formatting template.

I had the toughest time getting this to work; the documentation for subtemplates proved to be a bit rough.  I found a few errors in both the formal BI Publisher documentation and Oracle provided white paper.  Here are the basics for getting BI Publisher subtemplates to work.

 

Subtemplate

Create a new BI Publisher template the same way the main template is configured, but use a different template name/code, and make sure subtemplate is set to “Yes”.

Contents of subtemplate RTF:

<?template:standard_terms?>
Standard Terms Body
<?end template?>
<?template:100001_terms?>
Special Supplier Terms Body
<?end template?>

 

Report Calling the Subtemplate

You must first import the subtemplate file from the main template, then call the section of the file you want to display.


Import the Subtemplate file

When Using Server to Render Output
Syntax:
<?import:xdo://[APPLICATION_CODE].[TEMPLATE_CODE].[LANGUAGE_CODE].[TERRITORY]/?>

<?import:xdo://XXPO.XXPO_TERMS_R100.en.US/?>


When Using Desktop Viewer to Render Output

<?import:file:C:/XXPO_TERMS_EGS_R100.rtf?>


Call the subtemplate with a condition

<?choose:?>
<?when:SUPPLIER_NUMBER=’100001′?>
<?call:100001_terms?>
<?end when?>
<?otherwise:?>
<?call:standard_terms?>
<?end otherwise?>
<?end choose?>

Things to Note

  • Language code is always lower case
  • Territory is always upper case
  • Don’t forget the forward  slash at the end of the server based import string
  • Some formatting in your subtemplate will cause the subtemplate import to fail
    • I couldn’t get the subtemplate to be two columns using built in RTF/Word functionality
    • I did get the main document to put the subtemplate text into two columns
    • Intended bullets and numbering work fine

Oracle Documentation

Print Labels Directly From Oracle to Zebra Printers

June 7, 2016 Leave a comment

Labeling is a very important business process that usually confuses even the best of us.  It involves many moving parts; including, but not limited to, ERP systems, printer hardware, bar-code symbologies, and label templates. It’s seemingly rare in corporate IT to find a single person who knows enough about all of these technologies to create a simple labeling solution. Third party companies like Bartender and Loftware have swooped in to provide “turnkey solutions” which have their own complexities. From my point of view, these third party solutions provide little, if any, value. I’d even go as far to say that they detract value from the overall solution.

Here are a few points which a third party software vendor may bring up which will seem enticing to you:

  • With a third party solution, you can have one centralized label template repository
  • We offer a visual label template editor
  • We can direct printing based on business data

Here is what the sales people won’t tell you about third party labeling solutions:

  • License costs are extraordinary high and repeat annually
  • Each server requires a license
    • Each server in a load balancing cluster will require a license (A license per IP address)
    • Each test/development server requires a license
  • The software requires hardware/virtual machines (VMs) to run on (which also has an additional cost)
    • Multiply it for each test/development instance
  • The software provides an additional point of failure which can be difficult to troubleshoot
  • Additional desktop software is also required, and may not be very robust
    • Precise formatting can be very difficult, or impossible
    • Even basic ZPL functions are difficult or impossible to implement
  • Some key features, like linking to data from external sources, is limited in capability
  • There’s a conflict of interest between the software quality and desire to sell consulting services
  • Zebra offers a WYSIWYG visual label template editor, for a lower price

Let me be the first to tell you; third party software is not going to make your labeling solution simple. In practice, it will likely make you want to pull your hair out.

Luckily, Oracle comes ready to print labels out of the box!  Don’t let the Loftware salesperson tell you otherwise!  Here are the steps you can take to print a label directly from Oracle; no third party systems involved.

Design a ZPL Template:

^XA
^MNW^POI^PMN^LH0,0^JMA^MD25^PQ1,0,0,N^CI0^PW812^MMT^LL406^LRY^FO20,25^GB773,0,70^FS
^FT340,80^A0N,60,60^FH^FN10^FDITEM^FS
^MCY^XZ

Save the template on your Zebra printer’s flash memory
ZebraTemplateZPL[1]

Configure your device IP address.
DeviceIP[1]

Set your profile option: ‘WMS: Label Print Mode’ = ‘Synchronous – TCP/IP’
Profile[1]

Set up your label Format and fields
(make sure the name matches the storage path on the printer)
LabelFormat

 

 

Back to the third party software benefits; are any of them true?  I don’t think so.

For one, Zebra offers software (ZebraNet Bridge Enterprise) that will push label templates to all your zebra printers in just a few clicks; making centralized label templates a moot point.  The Zebra software is only a few hundred dollars.  Once.

As mentioned before, Zebra offers label template design software (Zebra Designer) that is, in my experience, far better than a third parties software. Think of it this way, the easier Zebra makes printing labels, the more hardware/supplies your company will buy from them. On the flip side, with third party vendors like Loftware, the more complex the software, the more consulting services they can sell you.

Lastly, the ERP system, in my case, Oracle, should be robust enough to direct labels to desired printers.  If it’s not, you will likely be better off designing a custom solution in the ERP system rather than implementing a third party package, which itself will require much technical work.

With just a little work, you can be printing labels, directly from Oracle, without any need for virtual machines or complex middleware.

References

ERPschools – Oracle MSCA Label Printing
Zebra – Barcode Printing from Oracle WMS

Freezer Lightbulb

December 9, 2015 Leave a comment

I recently had to replace my freezer (KFFS20EYMS) light bulb and thought it would be an ideal time to switch to an LED bulb. I couldn’t believe how difficult it was to replace the bulb!

The bulb had no markings on it, other than the voltage and wattage. I had no way of knowing the socket size. I reviewed the manual, which was no help (it actually gave me a good laugh telling me to use the correct size, but didn’t tell me the size to use):

IMPORTANT:
Not all appliance bulbs will fit your refrigerator. Be sure to
replace the bulb with an appliance bulb of the same size,
shape and wattage.

Replace the burned-out bulb with an appliance bulb(s) no
greater than 25 watts.

I contacted Whirlpool, makers of my fine KitchenAid appliance, and they couldn’t tell me the socket type, but they were more than happy to pass me on to their 3rd party parts retailer which would sell me the incandescent light bulb for way more than it was worth.

I tried a standard Candelabra bulb (size E12), but it was too small.

I took a trip to my local Home Depot; none of the bulbs there looked right.  I almost took a bulb from a floor model, but I thought I didn’t want to steel from them. So I turned, again, to the internet.

Apparently Whirlpool decided to use a non-standard light bulb socket size. From some deep digging and assumptions I guessed that the socket they used was a European socket size E14; no wonder I couldn’t find a replacement bulb at Home Depot.  Funny thing, that E14 socket is supposed to be for 220 volt European power, and this light was 120 volt. That doesn’t make any sense to me at all. What was Whrilpool thinking?

I ordered this lovely E14 to E12 adapter at amazon that would let me use any standard candelabra bulb! Problem solved.
Adapter

…Now to get an LED bulb that will fit in the protective housing.

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 Oracle.com 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:

<?for-each:LIST_TRIP_STOP/TRIP_STOP/LIST_DELIVERY/DELIVERY[position()=1]?>

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 Leave a 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-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!