Archive
Code 128 Barcode Issues with Oracle BI Publisher
We were recently having problems with Code 128 barcodes in Oracle BI Publisher (AKA: XML Publisher; AKA: Analytics Publisher) within Oracle EBS (E-Business Suite). After a patch, the barcodes would show these accented I (Ì Î) characters at the beginning and end of the Code128 barcode.
Our instance used the Oracle Out-of-Box, or seeded, Code128 barcode font: 128R00.ttf
When investigating the issue, we ran into another bug where Microsoft Word would show these windings type symbols when displaying the properties of the barcode element:
Oracle’s support had a document that describes a solution.
Barcode Font File 128R00.ttf Properties in Template Show Junk Characters (Doc ID 2317123.1)
The document says to fix the windings symbols, recreate the barcode element in another font, and then apply the Code128 font again.
But that’s only temporary. Saving and re-opening the template causes the problem to resurface. Not acceptable.
I found that the IDAutomation Code128 font does not have these issues. Adding the font to EBS, adding into my Windows system, and creating a template with this alternate font, solved the problem.
Another Alternative font is Libre Barcode 128 which is functional in EBS and Word. Licensing for this font is free for commercial use. It also seems that this is supported by Oracle.
Solution: BI Publisher QR Codes
I saw a post on StackOverflow asking How to use variable in chart.apis.google link for QR Code and I was quite intrigued. I didn’t think it could be done at first. But then I figured it out.
I do know that BI Publisher has built in Chart support, so my first thought was to just use that. But BI Publisher charting cant generate QR Codes. None the less, I linked to BI Publisher Report Designers guide to explain those details if anyone stumbled upon this post looking for actual charts.
My second thought was to follow a blog entry that an Oracle developer posted about using QR Codes in BI Publisher. It mentioned making a java class, and using an IDAutomation QR Font. It was quite complex. This is probably the method to go after if you want all the data to remain internal to the Oracle server in the enterprises data-center. I posted the details to that too.
Finally, I remember using dynamic images at Emerson for insertion of Casting images into Work-orders/Shop-packets. I figured why not give it a shot. I created a quick XML test file, and a simple RTF file. I use the concat (concatenate) function in BI Publisher to combined the Google Charts API for QR codes and the variable I wanted encoded.
Test XML
<test_header>
<test_row>
<row_num>1</row_num>
<value>TESTA</value>
</test_row>
<test_row>
<row_num>2</row_num>
<value>TESTB</value>
</test_row>
</test_header>
Test RTF Template
RTF Template
Image ALT TEXT
url:{concat(‘https://chart.apis.google.com/chart?cht=qr&chs=500×500&choe=UTF-8&chld=H&chl=’,value)}
[when copy/pasting from this page, replace the x in the URL with one hand-typed.]
My first attempt failed because of a space in my test data. If you have spaces or special characters in the data you want encoded in the QR code, make sure you URL encode the data that you want to pass into the Google API URL. Oracle allows you to do this with the UTL_URL function. Also note: this post is being made with WordPress, the single quotes are improperly being replaced with smart quotes and/or HTML entities… use care if copying and pasting, and make sure to use the single quote as per screen shot.
Example use of UTL_URL.ESCAPE
select rownum row_num, utl_url.escape(record_value) value from test_table
Once I removed the space from my test data, it worked! I was shocked how easy it could be.
Test Output
PDF Output
Other Considerations
One last thing to note, if you expect this to work on your enterprise server/network/data-center, you’ll probably have to open up the firewall.
Generating XML in PL/SQL
At work we have historically been using concatenation for generating XML Data. I stumbled upon these XML functions the other day, and thought it was worth sharing. They seem to be the proper, and I’m sure more efficient, way to generate XML using Oracle PL/SQL. I’m dumbfounded that our developers didn’t use this method, but who am I to judge!
Oracle Documentation:
http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb13gen.htm#ADXDB1600
Easier to Understand Guide:
http://allthingsoracle.com/generating-xml-from-sql-and-pl-sql-part-1/
http://allthingsoracle.com/generating-xml-from-sql-and-plsql-part-2/
Example Query for Delivery Lines
Should produce XML for a Delivery with its lines, where lines contain the Item Number, Description, Shipped Quantity, and Requested Quantity.
SELECT XMLSERIALIZE (
DOCUMENT (SELECT XMLELEMENT (
"EXTRACT",
(SELECT XMLAGG (
XMLELEMENT (
"DELIVERY",
XMLELEMENT ("DELIVERY_ID",
DELIVERY_ID),
XMLELEMENT (
"LIST_LINE",
(SELECT XMLAGG (
XMLELEMENT (
"LINE",
XMLFOREST (
msib.segment1 AS "ITEM_NUMBER",
XMLCDATA ( MSIBTL.DESCRIPTION) AS DESCRIPTION,
NVL ( WDD.SHIPPED_QUANTITY, 0) AS "SHIPPED_QUANTITY",
WDD.REQUESTED_QUANTITY AS "REQUESTED_QUANTITY",
wdd.delivery_detail_id)))
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd,
mtl_system_items_b msib,
mtl_system_items_TL msibTL
WHERE wda.delivery_id = wnd.delivery_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd.inventory_item_id = msib.inventory_item_id
AND msib.organization_id = WND.ORGANIZATION_ID
AND wdd.inventory_item_id = msibTL.inventory_item_id
AND msibTL.organization_id = WND.ORGANIZATION_ID
AND MSIBTL.LANGUAGE = 'US'))))
AS XML
FROM wsh_new_deliveries wnd
WHERE delivery_id = :delivery_id))
FROM DUAL) AS CLOB
VERSION 1.1 INDENT)
AS xmlserialize_doc
FROM DUAL;
Oracle WMS Cycle Count Bug
We recently completed a Physical Inventory using Oracle WMS Cycle Count functionality and some custom programming. It was a arguably a success. I’ll post something about that in the future, hopefully, but here is something that is really aggravating me at the moment.
Two of the biggest problems we had appeared to be bugs with seeded forms/functionality. We had two different symptoms, that seemed like seeded (built in/out of the box) functionality was broken, and it took us quite some time to identify the root cause.
- The Approve Cycle Counts form would not show us a portion of the cycle count entries
- The counts were in the database
- The counts were in our report
- The counts would not show up on the form
- Recounts would not be given to users
- Initial counts would be made
- Recounts would be manually queued to users using the Warehouse Control Board
- As soon as the user would go to the telnet Directed Cycled Count Tasks screen, the task would go directly back to pending
- The user would never see the task to work on
I logged two separate SRs with Oracle. Both made little progress for several weeks.
Luckily, one of our developers identified the commonality between the records. There was an EXPORT_FLAG on the MTL_CYCLE_COUNT_ENTRIES table which was set to 1 for the offending records. I updated the two SRs with these details.
Oracle Value Set: Allow Only Alphanumeric Values
We have a problem at work which required us to only allow alphanumeric values in a DFF. No spaces, punctuation, or special characters. Here is what I came up with:
Function (WordPress will change quotes to smart quotes, you will need to change back)
FND PLSQL “declare
l_value varchar2( 150 ) := :!value ;
l_valid NUMBER := NULL ;
BEGIN
SELECT (LENGTH(TRIM(TRANSLATE(REPLACE(l_value,’ ‘,’.’), ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’, ‘ ‘))))
INTO l_valid
FROM dual;
IF (l_valid IS NOT NULL) THEN
fnd_message.set_name( ‘FND’, ‘FND_GENERIC_MESSAGE’ ) ;
fnd_message.set_token( ‘MESSAGE’, ‘Value must be alpha-numeric’ );
fnd_message.raise_error ;
END IF ;
END;
“
Regular Expression Version:
FND PLSQL “declare
l_value varchar2( 150 ) := :!value ;
BEGIN
IF REGEXP_SUBSTR(l_value, ‘^[a-zA-Z0-9]+$’) IS NULL THEN
fnd_message.set_name( ‘FND’, ‘FND_GENERIC_MESSAGE’ ) ;
fnd_message.set_token( ‘MESSAGE’, ‘(‘||l_value||’) must be alphanumeric.’);
fnd_message.raise_error ;
END IF ;
END;
“
Continuing Poor Oracle Support
I communicate with Oracle support regularly. Here are some of the recent response I have gotten from them.
Example 1
This is an example from a severity 1 SR. We are unable to drop a picked LPN/pallet in the warehouse. The SR was originally severity 2, but because we had received no constructive responses for a shipment that wouldn’t ship for 3 days, we escalated it to severity 1.
From Development
———————————–
The LPN ##### is showing in context 5 so looks like the cartonization_id
stamping of this using custom program might have caused this. The LPN that
got used for transfer is in status defined but not used. At this point the
best step right now would be to unload and remove the allocations from the
Transact Move Orders form and backorder the line. A datafix would essentially
do the same and then require the customer to re-release/pick load and drop
the lines.
.
Pl. try the action plan to backout and cancel the allocations from UI and let
us know the results. If issue persists pl. get the details of the error
customer is facing when trying to backout and we can review accordingly.
- This message was sent from Oracle Development to Oracle Support. Oracle Support then copied/pasted the message to pass it on to us.
- LPN Context 5 is used on all LPNs that were used in cartonization. We have been live for 1.5 years and have been doing this without a problem. It seems we (the customer) know about the software the Oracle does
- Why are they abbreviating “please” to “Pl.”? That seems lazy and unprofessional to me.
- They are asking us to backorder and re-release the whole delivery, essentially giving up on the problem.
- By giving up, we must put material back into the warehouse, and repick it. Causing us to use our resources 3x normal usage to pick an order. Luckily this delivery is only one LPN/Pallet.
- If you can’t tell, their internal system puts arbitrary new lines into the text. And to force a new line, they must put a “.” in so that the new line isn’t truncated. How antiquated is there support system? They market themselves as being a technology company.
ED HAYES 