Update Salesrep Assigned Accounts
Creation date: 7/5/2022 3:00 PM
Updated: 12/29/2022 4:44 PM
The salesrep requesting the change will either send a range of salesrep IDs, customer IDs, or a territory to update. If the salesrep on the Ship to does not match the customer it will be updated to the new customers salesrep by this query. Current salesrep has to be accurate for this process to work. Steps 1 and 2 are to verify you are working with correct/current data. If you have current data you can skip to step 3.
1.
Pull current data using the below queries:
-- find salesrep for range of customers
select customer_id, salesrep_id--,*
from customer
where customer_id IN (
XXXX
)
-- find salesrep for territory
select customer.customer_id, salesrep_id--,*
from customer
join customer_ud on customer_ud.customer_id =customer.customer_id
WHERE
customer_ud.default_sales_loc ='XXXXX'
--find customer accounts for range of salesrep IDs
select customer_id, name customer_name, salesrep_id current_salesrep_id
from customer_salesrep join address
on address.id = customer_salesrep.customer_id
where salesrep_id IN (
'1014',
'1046' ,
'1071'
) and primary_salesrep_flag = 'y'
2.
Add a column titled 'New Salesrep ID' so they can fill out new information if it is not a one-to-one swap.
3.
Once you have the new information update @old_salesrep, @new_salesrep and @customer_ls udpated information and run the query. Test in Play first:
DECLARE @old_salesrep VARCHAR(16)
DECLARE @new_salesrep VARCHAR(16)
DECLARE @customer_ls VARCHAR(MAX)
DECLARE @delimiter VARCHAR(10)
SET @old_salesrep = '1071'
SET @new_salesrep = '20924'
SET @customer_ls = '142144,142760,141633,141683,143758,143700,141993,142673,141730,148286,144019,148291,141710,143307,148833,143458,148816,144398,141475,141477,142593,141865,143132,143200,143524,148995,144178'
SET @delimiter = ','
----------------- SHIPTO -----------------
----- If the new salesrep dont exist just change the existing.
UPDATE ship_to_salesrep
SET salesrep_id = @new_salesrep,
date_last_modified = CURRENT_TIMESTAMP,
last_maintained_by = 'raguirre'
FROM ship_to_salesrep
INNER JOIN ship_to ON ship_to_salesrep.ship_to_id = ship_to.ship_to_id AND ship_to.delete_flag = 'N'
INNER JOIN customer ON ship_to.customer_id = customer.customer_id
INNER JOIN p21_fn_split(@customer_ls, @delimiter) AS customer_ls ON customer_ls.value = customer.customer_id_string
WHERE ship_to_salesrep.salesrep_id = @old_salesrep
AND NOT EXISTS
(
SELECT 1
FROM ship_to_salesrep t
WHERE t.ship_to_id = ship_to_salesrep.ship_to_id
AND t.salesrep_id = @new_salesrep
AND t.company_id = ship_to_salesrep.company_id
)
--- Set other salesreps as secondary
UPDATE ship_to_salesrep
SET primary_salesrep = 'N',
date_last_modified = CURRENT_TIMESTAMP,
last_maintained_by = 'raguirre'
FROM ship_to_salesrep
INNER JOIN ship_to ON ship_to_salesrep.ship_to_id = ship_to.ship_to_id AND ship_to.delete_flag = 'N'
INNER JOIN customer ON ship_to.customer_id = customer.customer_id
INNER JOIN p21_fn_split(@customer_ls, @delimiter) AS customer_ls ON customer_ls.value = customer.customer_id_string
WHERE ship_to_salesrep.salesrep_id <> @new_salesrep
--- Set main salesreps as primary
UPDATE ship_to_salesrep
SET primary_salesrep = 'Y',
delete_flag = 'N',
date_last_modified = CURRENT_TIMESTAMP,
last_maintained_by = 'raguirre'
FROM ship_to_salesrep
INNER JOIN ship_to ON ship_to_salesrep.ship_to_id = ship_to.ship_to_id AND ship_to.delete_flag = 'N'
INNER JOIN customer ON ship_to.customer_id = customer.customer_id
INNER JOIN p21_fn_split(@customer_ls, @delimiter) AS customer_ls ON customer_ls.value = customer.customer_id_string
WHERE ship_to_salesrep.salesrep_id = @new_salesrep
----------------- CUSTOMER -----------------
---- If the new salesrep dont exist just change the existing.
UPDATE customer_salesrep
SET salesrep_id = @new_salesrep,
date_last_modified = CURRENT_TIMESTAMP,
last_maintained_by = 'raguirre'
FROM customer_salesrep
INNER JOIN customer ON customer_salesrep.customer_id = customer.customer_id
INNER JOIN p21_fn_split(@customer_ls, @delimiter) AS customer_ls ON customer_ls.value = customer.customer_id_string
WHERE customer_salesrep.salesrep_id = @old_salesrep
AND NOT EXISTS
(
SELECT 1
FROM customer_salesrep t
WHERE t.customer_id = customer_salesrep.customer_id
AND t.salesrep_id = @new_salesrep
AND t.company_id = customer_salesrep.company_id
)
--- Set other salesreps as secondary
UPDATE customer_salesrep
SET primary_salesrep_flag = 'N',
date_last_modified = CURRENT_TIMESTAMP,
last_maintained_by = 'raguirre'
FROM customer_salesrep
INNER JOIN customer ON customer_salesrep.customer_id = customer.customer_id
INNER JOIN p21_fn_split(@customer_ls, @delimiter) AS customer_ls ON customer_ls.value = customer.customer_id_string
WHERE customer_salesrep.salesrep_id <> @new_salesrep
--- Set main salesreps as primary
UPDATE customer_salesrep
SET primary_salesrep_flag = 'Y',
row_status_flag = 704,
date_last_modified = CURRENT_TIMESTAMP,
last_maintained_by = 'raguirre'
FROM customer_salesrep
INNER JOIN customer ON customer_salesrep.customer_id = customer.customer_id
INNER JOIN p21_fn_split(@customer_ls, @delimiter) AS customer_ls ON customer_ls.value = customer.customer_id_string
WHERE customer_salesrep.salesrep_id = @new_salesrep
UPDATE customer
SET salesrep_id = @new_salesrep,
date_last_modified = CURRENT_TIMESTAMP,
last_maintained_by = 'raguirre'
FROM customer
INNER JOIN p21_fn_split(@customer_ls, @delimiter) AS customer_ls ON customer_ls.value = customer.customer_id_string
WHERE salesrep_id = @old_salesrep
If successful you will get a message like the below
"(0 rows affected)
(33 rows affected)
(7 rows affected)
(0 rows affected)
(25 rows affected)
(4 rows affected)
(0 rows affected)
Completion time: 2022-12-29T16:31:55.3065691-06:00"
This query can be
NOTE: the delimiter has to be a space with no commas. If you have a large volume of changes to make, you will have to use Find and Replace to update them. You might have to transpose the data in Excel first then use Find and Replace in Notepad++.
To check the data take the original query you used to send the data to the requestor and confirm the current salesrep matches what the requestor filled out in the 'New Salesrep' Column
4.
To check update was made successfully
select distinct customer.salesrep_id--,*
from customer join customer_salesrep on customer.customer_id = customer_salesrep.customer_id
where primary_salesrep_flag = 'y'
and customer.customer_id IN (
xxxx,
xxxx
)