F0117 Postal (ZIP) code corrections/additions/deletes

[Eric Lehti]

How do you keep your F0117 Postal (ZIP) code file up-to-date and current with corrections/adds/deletes from the U.S. Postal Service and Canada? (cannot use Worksright.com database)

My current technique is that I CPYF from F55ZIP to F0117 with the *NOCHK parm. F55ZIP is our custom Zip code database formatted so that its first 8 columns are identical to the columns in F0117.

Last year we purchased from ZipInfo.com a simple text file in comma-separated value format with almost a million rows
of U.S. and Canadian Postal Codes (ZIP codes) for less than $200. I uploaded it to our IBM i into DB2 and created F55ZIP table. Added additional columns for data of interest to Ameristar (our sales rep names, etc.)

The CPYF technique to F0117 *NOCHK enables JDE programs to run unmodified. Custom appps use F55ZIP table as needed.

Now we want to refresh our database with the latest corrections and updates from the U.S. postal service and Canada.
As I consider the challenge of updating our custom database file with additions/deletions/changes provided by ZipInfo.com in .CSV format, I ask myself,

“What do other IBM i customers do for Postal Code and ZIP code validation?
How do you keep your ZIP code database current and up-to-date?
Eric Lehti

 

[ErpRob]

I pull my information from our tax software. It has a database that is updated monthly with tax rate and address changes, which I then feedback back to a custom table which has city, state, zip and county. If the user tries to enter an address which is not in the special table they get an error message. Since the tax company is on top of the address changes. Everything is good.

The other options are to pull the information from a shipping database or purchase it like you are doing.

Rob

 

[elans]

Same here. We use a third party software that includes all school codes, zip codes, tax information etc. for the United States. I'm pretty certain they do this for all areas around the world. We currently do not have any international only employees so this hasn't been a concern.

I believe we have used this for two reasons.
1. Near SOX compliance. I think they require you to have a third party regularly updating the information
2. ease of use. Base install was a breeze, and we just point JDe to it. Updates consists of obtaining a zipped file, and running a command on the server the software is installed on to update the database of codes. All updates are cumulative, so you have all the information the system needs.

Hope this helps.

 

[EmmanuelW]

Yes, we do basically the same thing - first step clears the F0117 file, then pull in the new file from the IFS (from a file which was copied from zip code CD) via CPYFRMIMPF to the input file for the update RPG program, which updates the F0117 file.

Using a web service would be another way to do this - they are available out there, and they are free. Would be nice if JDE supported this method natively.

Cheers,

Emmanuel

 

[cschwobe]

We are using JDE ERP 8.0. Is there someplace that needs to be turned on for this to do address validation against the F0117. Our system doesn't seem to do any address validation

thanks for any help you can give

 

[Eric Lehti]

Carol, You can modify your video and program code. Review your P01051 program to see if it has:

3114.00 C* If postal code is entered, default address information
3115.00 C*
3116.00 B1 CSR VDCTY1 IFEQ *BLANK
3117.00 CSR VDADDZ ANDNE*BLANK
3118.00 C*
3119.00 CSR CALL 'X0117'
~~~
3134.00 C* Warn if Zip Code is not in F0117.
3135.00 C*