|
ON ERROR com_Mesg=MESSAGE()
SET SAFETY OFF
ALTER TABLE ALIAS() ADD COLUMN dpv_desc C(112)
ALTER TABLE ALIAS() ADD COLUMN nostat_rsn C(188)
ALTER TABLE ALIAS() ADD COLUMN meaning C(40)
ALTER TABLE ALIAS() ADD COLUMN mailable C(10)
ALTER TABLE ALIAS() ADD COLUMN supp_flag C(10)
ALTER TABLE ALIAS() ADD COLUMN next_steps C(141)
***POPULATE NEW FIELDS***
REPLACE dpv_desc WITH "Input address matched to the ZIP + 4 product",nostat_rsn WITH "",meaning WITH "ZIP+4 matched",mailable WITH "YES",supp_flag WITH "0",next_steps WITH "Fully standardized. No suppression. Eligible for all mail classes." FOR "AA" $ dpvnotes_
REPLACE dpv_desc WITH "Input address not matched to the ZIP + 4 product",nostat_rsn WITH "",meaning WITH "ZIP+4 not matched",mailable WITH "REVIEW",supp_flag WITH "0",next_steps WITH "Most likely not deliverable. AccuZIP typically allows mailing but flags as undeliverable. Consider customer confirmation for high-value mail." FOR "A1" $ dpvnotes_
REPLACE dpv_desc WITH "Input address matched to DPV to both primary and secondary numbers necessary to determine a valid delivery point",nostat_rsn WITH "",meaning WITH "DPV confirmed (primary + secondary)",mailable WITH "YES",supp_flag WITH "0",next_steps WITH "Gold-standard DPV. Never suppress." FOR "BB" $ dpvnotes_
REPLACE dpv_desc WITH "Input address primary number matched. Secondary number not matched but not required",nostat_rsn WITH "",meaning WITH "Primary matched. Secondary not required",mailable WITH "YES",supp_flag WITH "0",next_steps WITH "Valid single-unit delivery point. Mail normally." FOR "CC" $ dpvnotes_
REPLACE dpv_desc WITH "Input address primary number matched. Secondary number not matched. Secondary number required",nostat_rsn WITH "",meaning WITH "Secondary required but not matched",mailable WITH "NO",supp_flag WITH "1",next_steps WITH "Hard suppress. USPS requires unit. AccuZIP DQ flags as undeliverable. Request Apt/Suite/Unit from customer." FOR "C1" $ dpvnotes_
REPLACE dpv_desc WITH "Input address matched to a military address",nostat_rsn WITH "",meaning WITH "Military (APO/FPO/DPO)",mailable WITH "YES",supp_flag WITH "0",next_steps WITH "Mailable if formatted correctly. AccuZIP does not suppress." FOR "F1" $ dpvnotes_
REPLACE dpv_desc WITH "Input address matched to a general delivery address",nostat_rsn WITH "",meaning WITH "General Delivery",mailable WITH "REVIEW",supp_flag WITH "0",next_steps WITH "Mailable but discouraged for marketing mail. AccuZIP leaves unsuppressed but flagged. " FOR "G1" $ dpvnotes_
REPLACE dpv_desc WITH "Informed address identified indicating the address has been identified with additional information to enhance its accuracy and delivery.",nostat_rsn WITH "",meaning WITH "Informed Address",mailable WITH "REVIEW",supp_flag WITH "1",next_steps WITH "IA is not fully supported by USPS. This current experimental phase enables mail to be sent and delivered without a physical address" FOR "IA" $ dpvnotes_
REPLACE dpv_desc WITH "Input address primary number matched to DPV but address missing required secondary number",nostat_rsn WITH "",meaning WITH "Missing required secondary",mailable WITH "NO",supp_flag WITH "1",next_steps WITH "Hard suppress. Unit required for delivery." FOR "N1" $ dpvnotes_
REPLACE dpv_desc WITH "Input address primary number missing",nostat_rsn WITH "",meaning WITH "Primary number missing",mailable WITH "NO",supp_flag WITH "1",next_steps WITH "Hard suppress. Not a valid delivery point." FOR "M1" $ dpvnotes_
REPLACE dpv_desc WITH "Input address primary number invalid",nostat_rsn WITH "",meaning WITH "Primary number invalid",mailable WITH "NO",supp_flag WITH "1",next_steps WITH "Hard suppress. Fails DPV logic." FOR "M3" $ dpvnotes_
REPLACE dpv_desc WITH "Identified PO Box Street Address",nostat_rsn WITH "",meaning WITH "PO Box Street Address",mailable WITH "REVIEW",supp_flag WITH "0",next_steps WITH "Mailable depending on class and USPS rules. AccuZIP does not auto-suppress." FOR "PB" $ dpvnotes_
REPLACE dpv_desc WITH "Input address missing the PO or RR or HC box number",nostat_rsn WITH "",meaning WITH "Input address missing the PO or RR or HC box number",mailable WITH "NO",supp_flag WITH "1",next_steps WITH "Hard suppress. Incomplete address." FOR "P1" $ dpvnotes_
REPLACE dpv_desc WITH "Input address PO or RR or HC box number invalid",nostat_rsn WITH "",meaning WITH "PO/RR/HC box invalid",mailable WITH "NO",supp_flag WITH "1",next_steps WITH "Hard suppress. Invalid delivery point." FOR "P3" $ dpvnotes_
REPLACE dpv_desc WITH "Input address matched to CMRA but PMB designator present (PMB 123 or # 123)",nostat_rsn WITH "",meaning WITH "CMRA with PMB present",mailable WITH "REVIEW",supp_flag WITH "0",next_steps WITH "USPS-compliant if PMB present. AccuZIP allows mailing but flags CMRA. Some businesses suppress by policy." FOR "RR" $ dpvnotes_
REPLACE dpv_desc WITH "Input address matched to CMRA but PMB designator not present (PMB 123 or # 123)",nostat_rsn WITH "",meaning WITH "CMRA missing PMB",mailable WITH "NO",supp_flag WITH "1",next_steps WITH "Hard suppress. USPS requires PMB for CMRA delivery." FOR "R1" $ dpvnotes_
REPLACE dpv_desc WITH "Addresses that are assigned to a phantom route of R777 or R779",nostat_rsn WITH "",meaning WITH "Phantom route (R777/R779)",mailable WITH "NO",supp_flag WITH "1",next_steps WITH "Always suppress. Not a real delivery route." FOR "R7" $ dpvnotes_
REPLACE dpv_desc WITH "Input address primary number matched by dropping trailing alpha",nostat_rsn WITH "",meaning WITH "Trailing alpha dropped",mailable WITH "REVIEW",supp_flag WITH "0",next_steps WITH "Address corrected by normalization. AccuZIP allows mailing but flags for review." FOR "TA" $ dpvnotes_
REPLACE dpv_desc WITH "Input address matched to a unique ZIP Code",nostat_rsn WITH "",meaning WITH "Unique ZIP Code",mailable WITH "YES",supp_flag WITH "0",next_steps WITH "Fully deliverable. Often government or large organizations." FOR "U1" $ dpvnotes_
REPLACE dpv_desc WITH "",nostat_rsn WITH "IDA (Internal Drop Address) – Addresses that do not receive mail directly from the USPS but are delivered to a drop address that services them.",meaning WITH "IDA (Internal Drop Address)",mailable WITH "REVIEW",supp_flag WITH "0",next_steps WITH "Deliverable via drop point. AccuZIP allows mail but flags as indirect delivery. " FOR "01" $ dpvnotes_
REPLACE dpv_desc WITH "",nostat_rsn WITH "CDS No-Stat – Addresses that have not yet become deliverable. For example, a new subdivision where lots and primary numbers have been determined, but no structure exists yet for occupancy.",meaning WITH "CDS No-Stat (Not yet deliverable)",mailable WITH "NO",supp_flag WITH "1",next_steps WITH "Suppress. Address not active yet. Recheck in future USPS updates." FOR "02" $ dpvnotes_
REPLACE dpv_desc WITH "",nostat_rsn WITH "Collision – Addresses that do not actually DPV confirm.",meaning WITH "Collision (No true DPV)",mailable WITH "NO",supp_flag WITH "1",next_steps WITH "Suppress. Conflicting records. High UAA risk." FOR "03" $ dpvnotes_
REPLACE dpv_desc WITH "",nostat_rsn WITH "CMZ (College or Military or Other Types) – ZIP + 4 records USPS has incorporated into the data.",meaning WITH "CMZ (College/Military/Other)",mailable WITH "REVIEW",supp_flag WITH "0",next_steps WITH "Mailable depending on institution rules and mail class. AccuZIP leaves unsuppressed." FOR "04" $ dpvnotes_
REPLACE dpv_desc WITH "",nostat_rsn WITH "Regular No-Stat – Indicates addresses not receiving delivery and the addresses are not counted as possible deliveries.",meaning WITH "Regular No-Stat (Not receiving delivery)",mailable WITH "NO",supp_flag WITH "1",next_steps WITH "Suppress. USPS indicates no delivery." FOR "05" $ dpvnotes_
REPLACE dpv_desc WITH "",nostat_rsn WITH "Secondary Required – The address requires secondary information.",meaning WITH "Secondary Required",mailable WITH "NO",supp_flag WITH "1",next_steps WITH "Suppress. Same logic as C1/N1. Unit required." FOR "06" $ dpvnotes_
***END OF POPULATING NEW FIELDS***
***To permanently remove addresses that have a value of 1 in the supp_flag field, remove the asterisks from the beginning of the two lines below***
*DELETE FOR "1" $ supp_flag
*PACK
|
