Advanced Retail (UK) Ltd
Company Wiki

The following steps should be taken:

1. create this script at Pets HO

2. Use SQLUpdater to insert the rows at every PPP branch

3. At each branch call sproc EXEC traxs_Custom_PPPSwitchStock @guid = ? –where @guid is the guid_session value from the #Data table

drop table #data
CREATE TABLE #data (code_good varchar(30), product_good int, code_bad varchar(30),  product_bad int)

INSERT INTO #data (code_good, code_bad) VALUES ('<code_good>,<code_bad>) -- #37398 - thornbury PPP

select * from #data

UPDATE #data SET product_good = B.product
FROM 
#data D INNER JOIN barcode B ON B.barcode COLLATE Latin1_General_CI_AS = D.code_good COLLATE Latin1_General_CI_AS  
  
UPDATE #data SET product_bad = B.product
FROM 
#data D INNER JOIN barcode B ON B.barcode COLLATE Latin1_General_CI_AS = D.code_bad COLLATE Latin1_General_CI_AS  

SELECt P1.description d1, P2.description d2, P1.ledger_code l1, P2.ledger_code l2, P2.barcode, D.* /*INTO #pppswitch1 */ FROM #data D
INNER JOIN vw_Product1 P1 ON P1.product = D.product_good
INNER JOIN vw_Product1 P2 ON P2.product = D.product_bad
where p1.product = p2.product -- check if products are the same

  
drop table #pppswitch1
SELECt P1.description d1, P2.description d2, P1.ledger_code l1, P2.ledger_code l2, P2.barcode, D.* INTO #pppswitch1 FROM #data D
INNER JOIN vw_Product1 P1 ON P1.product = D.product_good
INNER JOIN vw_Product1 P2 ON P2.product = D.product_bad

Check which stores have stock:

select w.description, s.* from stock s
inner join warehouse w on w.whse_no = s.whse_no 
where product = 129432 and quantity <> 0 and w.archive = 0

To create the inserts use:

ALTER TABLE #pppswitch1 ADD multiplier int DEFAULT(1) NOT NULL
DECLARE @guid uniqueidentifier = NEWID()
SELECT 'INSERT INTO ppp_switchstock (guid_session, product_good, product_bad, multiplier,stock) VALUES (''' + CONVERT(varchar(50), @guid) + ''',' + CONVERT(varchar(30), product_good) + ',' + CONVERT(varchar(30), product_bad) + ',' + CONVERT(varchar(30), multiplier) + ',0)' FROM #pppswitch1

Then copy generated inserts and run at store

followed by

EXEC traxs_Custom_PPPSwitchStock @guid = ?