Archive

Archive for the ‘ERP’ Category

Generating XML in PL/SQL

January 17, 2017 Leave a comment

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;

 

Advertisements

Oracle WMS Cycle Count Bug

December 5, 2016 Leave a comment

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.

Read more…

Oracle Value Set: Allow Only Alphanumeric Values

November 10, 2016 Leave a comment

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:

valueset
valueset2

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

November 8, 2016 Leave a comment

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.

  1. This message was sent from Oracle Development to Oracle Support.  Oracle Support then copied/pasted the message to pass it on to us.
  2. 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
  3. Why are they abbreviating “please” to “Pl.”?  That seems lazy and unprofessional to me.
  4. They are asking us to backorder and re-release the whole delivery, essentially giving up on the problem.
  5. 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.
  6. 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.

Read more…

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

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.