Working with Drop Ship Errors (WDSE) | Contents | SCVs | Search | Glossary | Reports | Solutions | XML | Index | Installation |
Load USPS Zip Code File (LZPS)
Purpose: Use this menu option to upload city/state/zip data from the U.S. Postal Service into the City/State/Zip (postal code) table.
Overview: The City State product, available from the U.S. Postal Service, provides a comprehensive list of ZIP codes with corresponding city and county names. Updates are available on a periodic basis. Uploading this information to Order Management System provides an automated means to create records in your City/State/Zip table and keep the data in the table complete and accurate.
In this chapter:
• Fields in the USPS Zip/City/State Table
• Fields in the Zip/City/State Table
• Troubleshooting the Zip/City/State Upload
1. Download the City State Product from the USPS web site.
2. When you receive the City State product:
- If you use the File Storage API, use the putFile request message to upload the file to the OMS-IMPORTS container in the FILE_STORAGE table; otherwise,
- If you do not use the file storage API, use the Work with File Uploads (WUPL) menu option to copy the data file to the location on your application server specified in the CWDIRECTCP_ USPS_UPLOAD_ FILE property. An example directory is /domain/conf/OMSFiles/LZIP/ctystate.txt/, where domain is the WebLogic domain directory for Order Management System. The file name matching is case-sensitive.
Note that even if you use the file storage API, the process uses the folder specified in the CWDIRECTCP_ USPS_UPLOAD_ FILE, so you must set up this property.
3. Use the Load USPS File Screen to submit the LZPS job, which uses the contents of the ctystate.txt data file to create records in the Zip/City/State table if these records do not already exist. The job performs the following steps:
- Deletes any existing records in the USPS Zip/City/State work table and creates a record in this table for each eligible entry from the ctystate.txt data file. A record in the data file is eligible for upload if it is flagged with a D (detail) and is also flagged as mailable. See the Fields in the USPS Zip/City/State Table for mapping details.
- Compares the data in the USPS Zip/City/State work table with the existing records in the Zip/City/State table. If a record in the work table does not exist in the Zip/City/State table, the job creates it.
- If your current logging level supports it, the job uses the Application Log to track data such as the total number of records found in the data file and the total number of new records created in the.Zip/City/State table.
Note:
• The ctystate.txt data file as delivered by the U.S. Postal Service does not include delimiters between records, but the U.S. Postal Service does provide a utility to insert line breaks to make the data more readable. Important: Do not use this utility to transform the presentation of the records in the data file; if you do, the Load USPS File job will not work.
• The job does not create City/State/Zip Tax records. To create tax records at the postal code level if needed, use Setting Up the Zip/City/State (Postal Code) Table (WZIP).
• The entire process can take several minutes to a half hour to run. You can expect the job to take longer to run if you are creating records in the City/State/Zip table for the first time.
• The job tracks activity in the application log only if your current logging level (as specified in the MQ_LOG_LEVEL in Logging Properties) is set to INFO or higher.
• The job deletes the file after processing it successfully.
• The job does not perform a partial update. If there are any errors, processing fails for the entire file, and the process moves the entire file in the OMS-ERRORS container of the FILE_STORAGE table if you use file storage; otherwise, the file is moved to the ERRORS subfolder of the folder specified in the CWDIRECTCP_ USPS_UPLOAD_ FILE property.
For more information: See Troubleshooting the Zip/City/State Upload.
Before you start: Before you advance to this screen, confirm that you have used either the File Storage API or the Work with File Uploads (WUPL) menu option to upload the file. See the Upload Process for details.
If you are not using the file storage API, when you select the LZPS menu option, make sure you are logged onto the application server specified in the CWDIRECTCP_ USPS_UPLOAD_ FILE property.
How to display this screen: Select Load USPS Zip Code FIle from a menu, or enter LZPS in the Fast path field at the top of any menu.
City State data file found? When you select this menu option, the screen indicates whether there is a data file with the correct name in the location indicated by the properties file:
Message |
Explanation |
***ERROR: File cannot be located. |
Indicates that there is no file with the correct name in either the FILE_STORAGE table if you use the File Storage API, or the CWDIRECTCP_ USPS_UPLOAD_ FILE folder if you do not use the file storage API. See Upload Process for details. Note that file name matching is case-sensitive. |
***ERROR: File is empty. |
Indicates that there is a file using the correct name in the directory or container indicated, but it contains no data. |
File has been found and is ready to be loaded. Click Accept to submit. |
Indicates that you can proceed with the upload process. Note: This message indicates that the system has located a file with the correct name and location, based on whether the file storage API is in use, and that it contains data. It does not indicate whether the file includes postal code data or whether it is in the correct format. See Troubleshooting the Zip/City/State Upload for possible problems and steps to correct. |
Completing this screen: If the file has been found and is ready to be loaded:
1. Optionally, deselect the Submit on hold option if you would like the job to run immediately; otherwise, you will need to select Release for the LZPS job at the Job Management Screen when you are ready to run it.
2. Click Accept to begin the upload process and launch the LZPS job. You might expect the job to take several minutes to a half hour to complete, depending on how many new postal code records you are loading.
Updates: See the Upload Process for an overview of the processing steps.
Fields in the USPS Zip/City/State Table
Purpose: The LZPS job reads each record in the file in the FILE_STORAGE table or specified by the CWDIRECTCP_ USPS_UPLOAD_ FILE property (the City State product file from the U.S. Postal Service) that is:
• flagged with a detail code of D
• identified as mailable by a setting of N at byte 56
The job clears the previous contents of the USPS Zip/City/State work table and then writes the selected records from the City State data file into the USPS Zip/City/State work table as follows:
USPS Zip/City/State work table |
City State data file from U.S. Postal Service |
Copyright detail code |
D |
Zip code |
Bytes 2-6 |
City state key |
Bytes 7-12 |
City state name |
Bytes 14-39 (last 3 bytes are truncated) |
City st mail name ind |
Byte 56 |
State abbrev |
Bytes 100-101 |
The remaining fields in the USPS City/State/Zip work table are left blank.
Fields in the Zip/City/State Table
After creating the records in the USPS City/State/Zip table, the LZPS job creates new records in the City/State/Zip table if they do not already exist, mapping the data from the USPS City/State/Zip table as follows:
City/State/Zip table |
Source |
Zip |
Zip code from USPS Zip/City/State work table |
State |
State abbrev from USPS Zip/City/State work table |
City |
City state name from USPS Zip/City/State work table |
Company |
The company number where you ran the LZPS job |
Country |
The Default Country for Customer Address (B17) in the company where you ran the LZPS job |
The remaining fields in the City/State/Zip table are left blank.
Troubleshooting the Zip/City/State Upload
Question |
Answer |
How long does it take to upload postal codes? |
It can take several minutes to a half hour to upload postal codes for the first time. |
Where can I obtain the postal code data? |
The U.S. Postal Service can provide the City State product, which includes a comprehensive list of ZIP codes and related cities and states. |
Where does the LZPS job look for the City State data file I obtained from the U.S. Postal Service? |
If you are using the File Storage API, the job looks in the OMS-IMPORTS container in the FILE_STORAGE table. Otherwise, if you are not using the file storage API, the job checks the file name and directory specified in the CWDIRECTCP_ USPS_UPLOAD_ FILE property. An example directory is /domain/conf/OMSFiles/LZIP/ctystate.txt/, where domain is the WebLogic domain directory for Order Management System. |
How can I check on whether the postal codes were uploaded successfully? |
If you are uploading the information for the first time, you can select the Setting Up the Zip/City/State (Postal Code) Table (WZIP) option to see if records have been created. If you are performing a periodic update, you can check the application log, provided your logging level supports it; see below for more information. |
I just ran the LZPS job. Why aren’t there any entries in the application log? |
The job tracks activity in the application log only if your current logging level (as specified in the MQ_LOG_LEVEL in Logging Properties) is set to INFO or higher. See Logging Properties for information on applying a different logging level. |
What do log entries look like for a successful upload? |
Sample log entries indicating a successful upload: 10:41:44,267 INFO APP - LZPS total records in delivered file = 466079 10:41:44,267 INFO APP - LZPS total detail records = 80174 10:41:44,267 INFO APP - LZPS total detail records flagged as Do Not Mail = 25035 10:41:44,267 INFO APP - LZPS total detail records flagged as Mail =55139 10:41:44,267 INFO APP - LZPS total detail records written to USPS file = 55139 Note: The total number of detail records written reflects those detail records in the data file that were eligible for selection as described in the Upload Process, and were created in the USPS City/State/Zip work file. This number might not be the same as the total number of records added to the City/State/Zip table, since the job does not add a record to the City/State/Zip table if the record already exists. The trace, app, and CWDirect log files contain entries if the upload was not successful, depending on the current logging level. |
What is the importance of the total number of records noted in the application log? |
total records in delivered file: the total number of records in the data file located at the file name and directory specified in the CWDIRECTCP_ USPS_UPLOAD_ FILE property or in the FILE_STORAGE table. total detail records: the total number of records in the data file flagged with a D (detail). total detail records flagged as Do Not Mail: the total number of records in the data file flagged with a D (detail) but also flagged as Do Not Mail (not mailable), and so are not eligible for selection. total detail records flagged as Mail: the total number of records in the data file flagged as mailable and that were also flagged with a D (detail). Note: Only records in the data file that are flagged with a D and are also flagged as mailable are eligible for selection. total detail records written to USPS file: the total number of records in the data file that were eligible for selection and were added to the USPS City/State/Zip work table. This total may differ from the number of records added to the City/State/Zip table, since some or all of the records written to the USPS City/State/Zip work table might already exist in the City/State/Zip table. |
I uploaded the ctystate.txt file from the U.S. Postal Service. Why didn’t it create any records? |
Possible explanations: • The U.S. Postal Service provides a utility to add delimiters between each record in the data file and make the file more readable. If you ran this utility against the data file, the LZPS job will not be able to read any records in the file. Re-copy the data file from the CD and start the upload process again. • If you have recently run an upload and are attempting to upload the same data again, there will be no new records eligible to be copied to the City/State/Zip table. |
How often do I need to upload new postal code data? |
The U.S. Postal Service provides updated data files on a periodic basis. See their web site for more information. |
Does the postal code data upload also create tax rate information? |
The LZPS job does not create Zip/City/State Tax records. You can use the Setting Up the Zip/City/State (Postal Code) Table (WZIP) option to create tax records at the postal code level if needed; however, you might not need these records at the postal code level if you set tax rates at the SCF level, or if you use the generic tax integration to calculate tax. |
There are still records in the USPS City/State/Zip work table. Does this indicate a problem? |
This does not indicate a problem. The LZPS job does not clear the records in the work table after processing an upload; instead, it clears the records when it begins a new upload. |
What if there is a file both in the FILE_STORAGE table and the CWDIRECTCP_ USPS_UPLOAD_ FILE? |
If you use the File Storage API, you should not ordinarily have an upload file in both the FILE_STORAGE table and the location specified in the CWDIRECTCP_ USPS_UPLOAD_ FILE property; however, if you do, the file in the FILE_STORAGE table is processed and the file in the CWDIRECTCP_ USPS_UPLOAD_ FILE location is deleted. |
Working with Drop Ship Errors (WDSE) | Contents | SCVs | Search | Glossary | Reports | Solutions | XML | Index | Installation |
LZPS OMSCS 18.1 April 2019 OTN