Update Lead Source for doc types and sales rep

I needed to be able to change a custom field we created “lead Source” as we needed a way to track where the leads are coming from. so this code check the document type in GP and the sales rep. and updates the xSource so that all the old documents at least have a value


--Update S Document
update spxSalesDocument
set xSource = 'SchoolLockers.com'
where Sales_Doc_Num like 'S%'
--Update B Document
update spxSalesDocument
set xSource = 'BuyUsedLockers.com'
where Sales_Doc_Num like 'B%'
--Update M Document
update spxSalesDocument
set xSource = 'BuyMetalShelving.com'
where Sales_Doc_Num like 'M%'
--Update G Document
update spxSalesDocument
set xSource = 'GSA'
where Sales_Doc_Num like 'G%'
--Update J Document
update spxSalesDocument
set xSource = 'Material Handling'
where Sales_Doc_Num like 'J%'
--Update Sales Rep Schoollockers
UPDATE spxsalesdocument
SET xsource = 'SchoolLockers.com'
WHERE sales_doc_num in (SELECT sd.sales_doc_num
FROM spv3salesdocument AS sd
LEFT JOIN spxsalesdocument AS xsd
ON xsd.sales_doc_num = sd.sales_doc_num
WHERE sales_person_id LIKE '%schoollock%'
UNION
SELECT xsd.sales_doc_num
FROM spv3salesdocumenthistory AS sdh
LEFT JOIN spxsalesdocument AS xsd
ON xsd.sales_doc_num = sdh.sales_doc_num
WHERE sales_person_id LIKE '%schoollock%')

--Update Sales Rep Etsy
UPDATE spxsalesdocument
SET xsource = 'Etsy.com'
WHERE sales_doc_num in (SELECT sd.sales_doc_num
FROM spv3salesdocument AS sd
LEFT JOIN spxsalesdocument AS xsd
ON xsd.sales_doc_num = sd.sales_doc_num
WHERE sales_person_id LIKE '%ETSY%'
UNION
SELECT xsd.sales_doc_num
FROM spv3salesdocumenthistory AS sdh
LEFT JOIN spxsalesdocument AS xsd
ON xsd.sales_doc_num = sdh.sales_doc_num
WHERE sales_person_id LIKE '%ETSY%')

--Update Sales Rep KSL
UPDATE spxsalesdocument
SET xsource = 'KSL'
WHERE sales_doc_num in (SELECT sd.sales_doc_num
FROM spv3salesdocument AS sd
LEFT JOIN spxsalesdocument AS xsd
ON xsd.sales_doc_num = sd.sales_doc_num
WHERE sales_person_id LIKE '%KSL%'
UNION
SELECT xsd.sales_doc_num
FROM spv3salesdocumenthistory AS sdh
LEFT JOIN spxsalesdocument AS xsd
ON xsd.sales_doc_num = sdh.sales_doc_num
WHERE sales_person_id LIKE '%KSL%')
--Update Sales Rep Amazon
UPDATE spxsalesdocument
SET xsource = 'Amazon.com'
WHERE sales_doc_num in (SELECT sd.sales_doc_num
FROM spv3salesdocument AS sd
LEFT JOIN spxsalesdocument AS xsd
ON xsd.sales_doc_num = sd.sales_doc_num
WHERE sales_person_id LIKE '%amazon%'
UNION
SELECT xsd.sales_doc_num
FROM spv3salesdocumenthistory AS sdh
LEFT JOIN spxsalesdocument AS xsd
ON xsd.sales_doc_num = sdh.sales_doc_num
WHERE sales_person_id LIKE '%amazon%')
--Update Sales Rep BuyMetalShelvin
UPDATE spxsalesdocument
SET xsource = 'BuyMetalShelving.com'
WHERE sales_doc_num in (SELECT sd.sales_doc_num
FROM spv3salesdocument AS sd
LEFT JOIN spxsalesdocument AS xsd
ON xsd.sales_doc_num = sd.sales_doc_num
WHERE sales_person_id LIKE '%BuyMetalShelvin%'
UNION
SELECT xsd.sales_doc_num
FROM spv3salesdocumenthistory AS sdh
LEFT JOIN spxsalesdocument AS xsd
ON xsd.sales_doc_num = sdh.sales_doc_num
WHERE sales_person_id LIKE '%BuyMetalShelvin%')
--Update Sales Rep GSA
UPDATE spxsalesdocument
SET xsource = 'GSA'
WHERE sales_doc_num in (SELECT sd.sales_doc_num
FROM spv3salesdocument AS sd
LEFT JOIN spxsalesdocument AS xsd
ON xsd.sales_doc_num = sd.sales_doc_num
WHERE sales_person_id LIKE '%GSA AD%'
UNION
SELECT xsd.sales_doc_num
FROM spv3salesdocumenthistory AS sdh
LEFT JOIN spxsalesdocument AS xsd
ON xsd.sales_doc_num = sdh.sales_doc_num
WHERE sales_person_id LIKE '%GSA AD%')
--Update Sales Rep Ebay
UPDATE spxsalesdocument
SET xsource = 'eBay.com'
WHERE sales_doc_num in (SELECT sd.sales_doc_num
FROM spv3salesdocument AS sd
LEFT JOIN spxsalesdocument AS xsd
ON xsd.sales_doc_num = sd.sales_doc_num
WHERE sales_person_id LIKE '%E-BAY%'
UNION
SELECT xsd.sales_doc_num
FROM spv3salesdocumenthistory AS sdh
LEFT JOIN spxsalesdocument AS xsd
ON xsd.sales_doc_num = sdh.sales_doc_num
WHERE sales_person_id LIKE '%E-BAY%')
[/ code]