Re: ODBC performance with UPS Worldship

[Dave Murvin <davem@xxxxxxxx>]

At 06:29 AM 3/15/2002, Doug wrote:

>When they scan a label, does the import mapping occur fast?  IOW, does it
>quickly display the customer name/address, shipment method, referecne numbers,
>etc.?  You only mention a delay on the export side, when "Process Shipment" is
>pressed.

At the present time, they do a batch import for the addresses once per day.

>On the export side, do you have Worldship set to export each package
>immediately
>as it is weighed, or to wait and send all packages at once when the "Process
>Shipment" is performed?
>
>Does the Worldship export directly update the customer order freight
>charges, or
>does it export to an intermediate table which you use to update the order?

It is set to send package information when the "process shipment" button is
pressed.  I am not sure what happens for multiple packages, but I can work
with the shipping people to see how they process multiple shipments.

>In my case, I have it set to export to a simple table used exclusively to
>collect package data from the scale systems.  I have both Worldship and Ship
>Manager only performing INSERTs, never an UPDATE even for Voids etc.  The
>scale
>simply does an INSERT to a physical file with the transaction date.  The table
>has a trigger on it which is reposible for adjusting the order charges and
>doing
>the rest of the business rules processing.

I assume my process is similar.  The UPS machine just writes the shipment
information to an AS/400 physical file via ODBC as the transactions
occur.  I have a batch program that is linked to the billing process that
just sweeps through the exported UPS shipping info and updates the shipping
charges for any unprocessed UPS records (it also handles voids and voided
transactions that have already been invoiced).  The client wants to use the
immediate UPS shipping updates because they run billing periodically
throughout the day.



>My client is still on V4R5, and tried the V4R4, V4R5, and V5R1 versions of CA
>Express ODBC drivers, with service packs.  In each case, the import performed
>flawlessly, but the export gave us lots of grief with mapping some of the
>numeric amount and weight fields.  We'd get data truncation and/or mapping
>errors.  We tried all kinds of permutations on using over sized fields,
>exporting to a character field and interpreting the string data, etc.  The
>result was always inconsistent.
>
>So I installed a demo copy of a third-party ODBC driver, and it worked
>flawlessly from the very first test, even with putting all numeric fields in
>their original design specs.  OTOH, the Fed Ex Ship Manager software never
>had a
>problem using the Client Access ODBC driver, even though it inserts into the
>exact same table and populates almost exactly the same set of fields (they
>send
>me more than UPS does).

I believe I have solved the mapping problems by exporting the UPS "numeric"
type fields into their default size alpha fields on the AS/400, then
parsing the alpha field to get a true numeric value. For example, the
shipping charge is received in a 35 character alpha field which I parse and
turn into a numeric field with 2 decimal positions.  I was hoping I
wouldn't have to do this, but this was the only way I could see to get it
to work correctly.  At least, it seems to be returning the expected values
correctly.

If I can't solve the problem with blocking adjustments, I will probably try
the 3rd party ODBC driver mentioned in another response.