Overview

The customer is experiencing an issue with duplicate billing addresses for a single customer account in the Everest system. This issue seems to be recurring, as the customer has reached out multiple times for the same problem. The root cause of the issue appears to be related to the customer's practice of restarting SQL services while Everest is still running, which could be leading to the creation of multiple billing addresses for a single customer.

Solution

To resolve the issue of duplicate billing addresses for a single customer account in the Everest system, follow these steps:

1. Identify Multiple Billing Addresses and Customers Affected:
   • See the SQL query below. This will return a list of the customers with multiple billing addresses and a list of said addresses.
     

     --This query returns two results
     --The first result will list every Customer with multiple Billing Addressees
     --The second result will list the ACTIVE Billing Addresses for those Customers
     
     --First, check if the BILLCODE in the first results corresponds to the Billing Address you wish to keep, if not, execute the Query to update the correct billing address
     --Next, check the ADDR_CODE in the second result for the Billing Addresses you wish to Deativate, and execute the Query to deactivate those addresses
     
     SELECT * FROM CUST
     INNER JOIN ADDRESS ON ADDRESS.CUST_CODE = CUST.CUST_CODE
     WHERE ADDRESS.TYPE = 4
     AND ADDRESS.ACTIVE = 'T'
     AND CUST.ACTIVE = 'T'
     AND ADDRESS.CUST_CODE IN (
     SELECT CUST.CUST_CODE FROM ADDRESS, CUST
     WHERE ADDRESS.TYPE = 4
     AND ADDRESS.ACTIVE = 'T'
     AND ADDRESS.CUST_CODE = CUST.CUST_CODE
     AND ADDRESS.ADDR_CODE <> CUST.BILLCODE
     AND CUST.ACTIVE = 'T')
     
     SELECT * FROM ADDRESS
     INNER JOIN CUST ON ADDRESS.CUST_CODE = CUST.CUST_CODE
     WHERE ADDRESS.TYPE = 4
     AND ADDRESS.ACTIVE = 'T'
     AND CUST.ACTIVE = 'T'
     AND ADDRESS.CUST_CODE IN (
     SELECT CUST.CUST_CODE FROM ADDRESS, CUST
     WHERE ADDRESS.TYPE = 4
     AND ADDRESS.ACTIVE = 'T'
     AND ADDRESS.CUST_CODE = CUST.CUST_CODE
     AND ADDRESS.ADDR_CODE <> CUST.BILLCODE
     AND CUST.ACTIVE = 'T')

   • Collect the Customer Code (CUST_CODE) and their registered Billing Address (BILLCODE) from the first result window.
   • Collect the Address Codes (ADDR_CODE) and their information from the second result window, which correlates to the BILLCODE from the customer.
   • Check if the Customer has the Desired Billing Address Registered. If they do, skip to Step 3 to deactivate the other Billing Address. If not, proceed to Step 2 to change the Billing Address.
   • Repeat Step 2 for Each Customer you wish to change the assigned Billing Address.
   • Repeat Step 3 for Each Billing Address you wish to deactivate.
2. Changing the Billing Address Assigned to the Customer:
   • See the SQL query below:
     

     --Query Template: Replace the values including the brackets
     
     --UPDATE CUST SET CUST.BILLCODE = ADDRESS.ADDR_CODE
     --FROM CUST INNER JOIN ADDRESS ON ADDRESS.CUST_CODE = CUST.CUST_CODE
     --WHERE CUST.CUST_CODE = '[CUSTOMER CODE]'
     --AND ADDRESS.CUST_CODE = CUST.CUST_CODE
     --AND ADDR_CODE = '[ADDRESS CODE]'
     --AND ADDRESS.tYPE = 4
     --AND ADDRESS.ACTIVE = 'T'
     
     UPDATE CUST SET CUST.BILLCODE = ADDRESS.ADDR_CODE
     FROM CUST INNER JOIN ADDRESS ON ADDRESS.CUST_CODE = CUST.CUST_CODE
     WHERE CUST.CUST_CODE = '3148' --Specify the customer to be updated
     AND ADDR_CODE = '213999' --Specify the Billing address to be applied to the customer
     AND ADDRESS.CUST_CODE = CUST.CUST_CODE --Ensures the address belongs to this customer
     AND ADDRESS.tYPE = 4 --Ensures it's a Billing Address, not a Shipping Address
     AND ADDRESS.ACTIVE = 'T' --Ensures it's an ACTIVE address

   • Input the Customer Code and the code for the desired Billing Address in their respective fields.
   • Execute the Query to assign to that customer the specified Billing Address to them. A successful result should state "(1 row affected)".
3. Deactivating the Additional Billing Addresses:
   • See the SQL query below:
     

     --Query Template: Replace the values including the brackets
     
     --DECLARE @CODE AS varchar(100)= '176740'
     --UPDATE ADDRESS SET ACTIVE = 'D'
     --FROM ADDRESS INNER JOIN CUST ON ADDRESS.CUST_CODE = CUST.CUST_CODE
     --WHERE ADDR_CODE = @CODE
     --AND ADDRESS.tYPE = 4 
     --AND EXISTS (
     --SELECT * FROM ADDRESS
     --INNER JOIN CUST ON ADDRESS.CUST_CODE = CUST.CUST_CODE
     --WHERE ADDRESS.tYPE = 4
     --AND ADDRESS.ACTIVE = 'T'
     --AND CUST.ACTIVE = 'T'
     --AND ADDRESS.CUST_CODE in (SELECT CUST_CODE FROM ADDRESS WHERE ADDR_CODE = @CODE)
     --AND ADDR_CODE <> @CODE)
     
     DECLARE @CODE AS varchar(100)= '176740' --Specify the Address being Deactivated
     UPDATE ADDRESS SET ACTIVE = 'D'
     FROM ADDRESS INNER JOIN CUST ON ADDRESS.CUST_CODE = CUST.CUST_CODE
     WHERE ADDR_CODE = @CODE
     AND ADDRESS.tYPE = 4 --Ensures it's a billing address
     AND CUST.BILLCODE <> @CODE --Ensures you are not deactivating the currently selected Billing Address for this customer
     AND EXISTS (
     SELECT * FROM ADDRESS
     INNER JOIN CUST ON ADDRESS.CUST_CODE = CUST.CUST_CODE
     WHERE ADDRESS.tYPE = 4
     AND ADDRESS.ACTIVE = 'T'
     AND CUST.ACTIVE = 'T'
     AND ADDRESS.CUST_CODE in (SELECT CUST_CODE FROM ADDRESS WHERE ADDR_CODE = @CODE)
     AND ADDR_CODE <> @CODE) --Ensures there is another active Billing address for this customer

   • Input the Address Code to be deactivated in the @CODE variable at the beginning of the code.
   • Execute the Query to Deactivate the specified Address. A successful result should state "(1 row affected)".

Summary

After executing the queries with the instructions above, the issue should be resolved. If you receive any result that states that more than one row has been affected, contact the support team and reference the ticket number in the description.

FAQ

1. What if I receive a result that states more than one row has been affected?
   If you receive any result that states that more than one row has been affected, contact the support team and reference the ticket number in the description.
2. What if the customer does not have the desired billing address registered?
   If the customer does not have the desired billing address registered, proceed to Step 2 to change the Billing Address.
3. What if the customer has the desired billing address registered?
   If the customer has the desired billing address registered, skip to Step 3 to deactivate the other Billing Address.