Residential Flag Conversion

×

We changed where the "Residential" flags are stored in the database.  If you're running a newer version and the system is not rating freight correctly because of Residential / Commercial issues, you'll need to run the queries below to move the Residential flags from the old tables to the new tables.

THE FOLLOWING IS FOR MACOLA ES ONLY.  DO NOT RUN ON PROGRESSION DATABASES.

BEFORE RUNNING THESE UPDATE QUERIES, BACKUP THE BDCUSTOMERSUPPORT & BDSHIPTOSUPPORT TABLES.

-------------------------

CUSTOMER DEFAULT SHIP-TO ADDRESSES:

This query does the update:

update bdcustomersupport
set bdcustomersupport.residential = cicmpy.yesnofield5
from bdcustomersupport (nolock)
inner join cicmpy (nolock) on bdcustomersupport.cus_no = cicmpy.debcode

---------------------------

Use this query to view the results (you can run it before the conversion to see the before & after):

select cs.cus_no, cs.residential, c.yesnofield5 from bdcustomersupport cs (nolock)
inner join cicmpy c (nolock) on cs.cus_no = c.debcode

 

SAVED SHIP-TO ADDRESSES:

This query does the update:

update bdshiptosupport
set bdshiptosupport.residential = a.yesnofield5
from cicmpy c (nolock)
join addresses a (nolock) on a.account = c.cmp_wwn and a.type = 'DEL'
join bdshiptosupport s (nolock) on s.cus_no = c.debcode and s.ShipTo_No=a.AddressCode
where c.debcode is not null

---------------------------

Use this query to view the results (you can run it before the conversion to see the before & after):

select c.debcode, s.cus_no, a.AddressCode, a.yesnofield5, s.residential
from cicmpy c (nolock)
join addresses a (nolock) on a.account = c.cmp_wwn and a.type = 'DEL'
join bdshiptosupport s (nolock) on s.cus_no = c.debcode and s.ShipTo_No=a.AddressCode
where c.debcode is not null
order by c.debcode