USPS Rates

[fcmlmt1]

I have been using the USPS Integrated carrier for the shipping of our international packages. Everything has been working great until today when the postal service updated the rates. I can now only see 1 of the shipping options that I had selected to be visible for my customers and not the others. Is there a bug with the new update? How do I fix this? I need a fix ASAP.

[jmestep]

I was afraid Able might not notice this post, so I called their attention to it in a different manner and sent them the email I had received on 1/2/10 from USPS. That's ridiculous for the Post Office to give only a two day notice of the changes that might require coding changes. Their email starts out

Dear Web Tools integrator,

Effective January 4, 2010, USPS will enact a Price Change. While there will be no structural changes to existing Web Tools requests and responses, there could be other changes that might affect your interface with Web Tools.
A summary of changes is listed below. Please direct any questions to the USPS Internet Customer Care Center (icustomercare@usps.com or 1-800-344-7779).

[fcmlmt1]

Do you know if Able is currently working on a fix for this?

[Logan Rhodehamel]

Thanks to Judy's alert this has our attention now. It's the top priority right now to investigate and fix.

[jmestep]

I was able to do a little patching by changing the entry in the database table ac_ShipMethods in the ServiceCode field to the new one that USPS had in their email. The email was a little hard to follow and I don't know that they put all the changes in it. For example, I changed Express Mail International (EMS) to Express Mail International.
This is a do-it-at-your-own risk change, but at least it got shipping methods back up for a client. There might be other changes needed on weight and size.

[Logan Rhodehamel]

I was able to do a little patching by changing the entry in the database table ac_ShipMethods in the ServiceCode field to the new one that USPS had in their email. The email was a little hard to follow and I don't know that they put all the changes in it. For example, I changed Express Mail International (EMS) to Express Mail International.

This appears to be the breaking change. I am having trouble deciding on a strategy that will allow the update to continue working with configured services without any changes. My attempt to implement a fix didn't work. I am not sure I can solve the issue without requiring either a) a sql script to upgrade the service codes in the database or b) requiring merchants to reconfigure the USPS shipping methods.

What we need to do is switch from a name based matching to matching against the service codes. That way we do not encounter this in the future should USPS change the service descriptions.

EDIT: Judy's fix will work. If anyone has a service that isn't working but needs to, I can tell you the name translation that would need to be made in the database. Just tell me the name of the service as it currently exists.

[Logan Rhodehamel]

While I continue to look for a solution, here is a SQL script that should reactivate your rates for existing configured services.

EDIT: SCRIPT REMOVED - NEW SCRIPT BELOW

As always, back up your database before running.

[krittleb]

Could somebody point me in the right direction on where the file that Judy was referring to is located?? I have searched for the place to make the change that she describes but have not been able to find it.

[GrfxDan]

I'm sure you're all probably on top of this already but I'll post this anyway just to be sure:

The USPS changes did not just break international shipping options, domestic USPS shipping options are also not showing up.

Thanks to Judy some of the international options are working on my site again but I still don't have regular First Class Mail as an option for domestic shipments. Unfortunately for me 95% of my customers use choose First Class Mail...

[krittleb]

I'm sure you're all probably on top of this already but I'll post this anyway just to be sure:

The USPS changes did not just break international shipping options, domestic USPS shipping options are also not showing up.

Thanks to Judy some of the international options are working on my site again but I still don't have regular First Class Mail as an option for domestic shipments. Unfortunately for me 95% of my customers use choose First Class Mail...

That's odd, as our site is still showing the first class domestic rates (thank goodness as that is what we mostly use as well!) Maybe it's a difference in AC versions??

[GrfxDan]

My site is AC7
VERSION: 7.0.3
BUILD: 12458

[krittleb]

Our software is also 7.0.3, build 12458, so am not sure why the difference in that ours displays the first class and yours does not. We are not showing first class international. Of course both Priority options are working fine (great for the post office!)

[Logan Rhodehamel]

Hello GrfxDan and krittleb... If you have a minute, go into the configuration for USPS shipping gateway and turn on the debug option. Then run a sample order where the shipping option should show but doesn't. You don't have to check out - just get to the shipping estimate screen. Then go to the folder App_Data/logs and get the U.S. Postal Service log file. If you could open the file and get the last Send: and Receive: pair, and send it to me in a PM, I might be able to troubleshoot why it's not showing up for you.

The documentation for the USPS web tools is not consistent with the actual values being returned by their server. I am trying my best to get the service names accurate. I thought the query above was correct but I am going to run some additional tests now to look for name differences.

[GrfxDan]

Log file sent.

[jmestep]

KrittleB- it is in the database and you might not have access to that- you will have to ask your web hoster.
Logan, here is what GrfxDan emailed when I had fixed the Domestic First Class to show up:

I'm now getting First Class Mail International Large Envelope …even when the product dimensions are such that it would not ship in an envelope of any size. For example, if I add a Side Body Graphic product to the cart (dimensions: 16" x 10" x 3", weight: 21 ounces) I get the 'large envelope' option. I would think that it should not return the large envelope option but instead either the 'parcel' or 'package' option for First Class Mail International.

[jmestep]

Logan- First class mail is now showing up for Dan with no changes to the method name. There might have been a allowable weight change, but I found an order on his site that someone had shipped First Class earlier and when I put that product into the cart, I got First Class rates to show.
I found documentation for the rate calculator at USPS and the one they have up is showing that it was revised on 5/11/09, so it looks like their docs still don't show correct information.
Here is a section of the USPS logging where first class shows up.
Send: API=RateV3&XML=<?xml version="1.0"?><RateV3Request USERID="142DECAL8346"><Package ID="0"><Service>ALL</Service><ZipOrigination>62549</ZipOrigination><ZipDestination>60803</ZipDestination><Pounds>0</Pounds><Ounces>2.0</Ounces><Container>FLAT RATE ENVELOPE</Container><Size>REGULAR</Size><Machinable>true</Machinable></Package></RateV3Request>

Receive: <?xml version="1.0"?>
<RateV3Response><Package ID="0"><ZipOrigination>62549</ZipOrigination><ZipDestination>60803</ZipDestination><Pounds>0</Pounds><Ounces>2.0</Ounces><Size>REGULAR</Size><Machinable>TRUE</Machinable><Zone>3</Zone><Postage CLASSID="23"><MailService>Express Mail Sunday/Holiday Guarantee</MailService><Rate>28.40</Rate></Postage><Postage CLASSID="25"><MailService>Express Mail Flat-Rate Envelope Sunday/Holiday Guarantee</MailService><Rate>30.80</Rate></Postage><Postage CLASSID="3"><MailService>Express Mail</MailService><Rate>15.90</Rate></Postage><Postage CLASSID="13"><MailService>Express Mail Flat Rate Envelope</MailService><Rate>18.30</Rate></Postage><Postage CLASSID="1"><MailService>Priority Mail</MailService><Rate>4.95</Rate></Postage><Postage CLASSID="16"><MailService>Priority Mail Flat Rate Envelope</MailService><Rate>4.90</Rate></Postage><Postage CLASSID="28"><MailService>Priority Mail Small Flat Rate Box</MailService><Rate>4.95</Rate></Postage><Postage CLASSID="17"><MailService>Priority Mail Medium Flat Rate Box</MailService><Rate>10.70</Rate></Postage><Postage CLASSID="22"><MailService>Priority Mail Large Flat Rate Box</MailService><Rate>14.50</Rate></Postage><Postage CLASSID="0"><MailService>First-Class Mail</MailService><Rate>0.61</Rate></Postage><Postage CLASSID="0"><MailService>First-Class Mail Flat</MailService><Rate>1.05</Rate></Postage><Postage CLASSID="0"><MailService>First-Class Mail Parcel</MailService><Rate>1.39</Rate></Postage><Postage CLASSID="4"><MailService>Parcel Post</MailService><Rate>4.90</Rate></Postage><Postage CLASSID="5"><MailService>Bound Printed Matter</MailService><Rate>2.37</Rate></Postage><Postage CLASSID="6"><MailService>Media Mail</MailService><Rate>2.38</Rate></Postage><Postage CLASSID="7"><MailService>Library Mail</MailService><Rate>2.26</Rate></Postage></Package></RateV3Response>

[fcmlmt1]

The script was run but it only updated the Express Mail International option. I'm still missing the First Class Mail International Package option from the list of shipping options for international customers. What is the new name this option should be? Any ideas on how to update that?

[Logan Rhodehamel]

First-Class Mail International Package
First-Class Mail International Flat

[Logan Rhodehamel]

There was one bug in my prior script. This line:

Code: Select all

UPDATE ac_ShipMethods SET ServiceCode = 'First Class Mail International Package' WHERE ServiceCode = 'First Class Mail International Parcels';

Is inaccurate. Firstly, it changed the service from Parcel to Package. Secondly, the converted name was incorrect. If you ran the prior script, this SQL script is corrective:

Code: Select all

UPDATE ac_ShipMethods SET ServiceCode = 'First-Class Mail International Parcel' WHERE ServiceCode = 'First Class Mail International Package'';

EDIT: a final, combined corrective script will be made available shortly. A patched USPS DLL will be published ASAP as well.

[Logan Rhodehamel]

Code: Select all

UPDATE ac_ShipMethods SET ServiceCode = 'Express Mail Flat Rate Envelope' WHERE ServiceCode = 'Express Mail Flat-Rate Envelope';
UPDATE ac_ShipMethods SET ServiceCode = 'Priority Mail Flat Rate Envelope' WHERE ServiceCode = 'Priority Mail Flat-Rate Envelope';
UPDATE ac_ShipMethods SET ServiceCode = 'Priority Mail Medium Flat Rate Box' WHERE ServiceCode = 'Priority Mail Flat-Rate Box';
UPDATE ac_ShipMethods SET ServiceCode = 'Priority Mail Large Flat Rate Box' WHERE ServiceCode = 'Priority Mail Large Flat-Rate Box';
UPDATE ac_ShipMethods SET ServiceCode = 'Global Express Guaranteed (GXG)' WHERE ServiceCode = 'Global Express Guaranteed';
UPDATE ac_ShipMethods SET ServiceCode = 'Express Mail International' WHERE ServiceCode = 'Express Mail International (EMS)';
UPDATE ac_ShipMethods SET ServiceCode = 'Express Mail International Flat Rate Envelope' WHERE ServiceCode = 'Express Mail International (EMS) Flat-Rate Envelope';
UPDATE ac_ShipMethods SET ServiceCode = 'Priority Mail International Large Flat Rate Box' WHERE ServiceCode = 'Priority Mail International Large Flat-Rate Box';
UPDATE ac_ShipMethods SET ServiceCode = 'Priority Mail International Medium Flat Rate Box' WHERE ServiceCode = 'Priority Mail International Flat-Rate Box';
UPDATE ac_ShipMethods SET ServiceCode = 'Priority Mail International Flat Rate Envelope' WHERE ServiceCode = 'Priority Mail International Flat-Rate Envelope';
UPDATE ac_ShipMethods SET ServiceCode = 'First-Class Mail International Letter' WHERE ServiceCode = 'First Class Mail International Letters';
UPDATE ac_ShipMethods SET ServiceCode = 'First-Class Mail International Flat' WHERE ServiceCode = 'First Class Mail International Flats';
UPDATE ac_ShipMethods SET ServiceCode = 'First-Class Mail International Parcel' WHERE ServiceCode = 'First Class Mail International Parcels';
UPDATE ac_ShipMethods SET ServiceCode = 'First-Class Mail International Large Envelope' WHERE ServiceCode = 'First Class Mail International Large Envelope';
UPDATE ac_ShipMethods SET ServiceCode = 'First-Class Mail International Package' WHERE ServiceCode = 'First Class Mail International Package';

The SQL script above will correct the service names for existing USPS configured services so that rates will show in AbleCommerce again. Please back up database before running.

If you ran the prior script, please also use the corrective line of SQL in my comment immediately above.

[fcmlmt1]

The final script you provided has been run however I'm still not showing anything for the First Class Mail International Package option. Is there anything else that needs to be done?

[Logan Rhodehamel]

The final script you provided has been run however I'm still not showing anything for the First Class Mail International Package option. Is there anything else that needs to be done?

At this point, the only way to know is to look into the database. Can you access the data in the ac_ShipMethods table? If so, can you look for the record that represents the International Package method and tell me what is the value of the ServiceCode field?

It is supposed to be: First-Class Mail International Package

If it is already that value, then the next step would be to turn on the debug option at the USPS config page in AbleCommerce, then run a sample order (up to point of shipping quotes - checkout not necessary). Then in the App_Data/logs folder there will be a file called U.S. Postal Service.log that will contain the shipping rate response values from USPS. With that data we might be able to figure out why the package service isn't showing up.

[igavemybest]

I juat want to clarify that this fix is for 7.0.4 also, right?

[krittleb]

I was able to access our database and change the service code as Judy described for the first class international package and it is working perfectly now.

Thank you, thank you!!

Kristi

[Logan Rhodehamel]

I juat want to clarify that this fix is for 7.0.4 also, right?

The fix is identical for all versions 7.0.0 onward. Fixing the service names in the database for a configured shipping method will make the method appear when a rate is returned by USPS.

A patched USPS dll and a script to handle the SQL update have been created and are in the process of being published.