Cannot add products to a cart in Sales Center if the postal code is XXXXX-XXXX

Technote (troubleshooting)

Problem(Abstract)

In the Sales Center, if the customer's shipping zipcode is XXXXX-XXXX, the products cannot be added to the shopping cart.

For example, consider if the zipcode for the customer's shipping address is 96707-2044. If you have JURST.ZIPCODEEND = 96707, it throws an insufficient inventory error (which is described in the Symptom section below). The error does not allow you to add the products to the shopping cart. However, if you have JURST.ZIPCODEEND = 96708, the products can be added to the shopping cart.

Symptom

In the trace, with WC_ORDER, WC_CALCULATION, and WC_INVENTORY enabled, you can see similar errors as below:

Cause

For a jurisdiction to match, essentially the zipcode, z, must be between the zipcodestart and zipcodeend:

zipcodestart <= z <= zipcodeend

Diagnosing the problem

This section shows how to diagnose the problem using the example above where the zipcode for the customer's shipping address is 96707-2044,

z= 96707-2044

In the working case: 96706 <= 96707-2044 <= 96708 true

The non-working case: 96706 <= 96707-2044 <= 96707 false

Why does the engine say this is false? It might help to look at the database schema for this field:

ZIPCODESTART VARCHAR(40) is the starting ZIP or postal code of a range of codes. Its definition should always match the one in the ADDRESS table. ZIPCODEEND VARCHAR(40) is the ending ZIP or postal code of a range of codes. Its definition should always match the one in the ADDRESS table.

Notice that the zipcode is a character. So, the ordering has nothing to do with 'numbers' or numeric ranges.. it's actually an alphabetic ordering. So alphabetically 96706 <= 96707-2044 True 96707-2044 <= 96707 False!

The matching itself happens in a database query. The clause in the query against the JURST table has (T1.ZIPCODESTART <= ? or T1.ZIPCODESTART is null) and (T1.ZIPCODEEND >= ? or T1.ZIPCODEEND is null)

Resolving the problem

Set the JURST.ZIPCODEEND = "96761-XXXX" where XXXX should be equal to or greater than the zipcode, for example, 2044.