Advanced Retail (UK) Ltd
Company Wiki

https://www.youtube.com/watch?v=Kbu5RkUo6-k

Firstly you need to download and install the PowerQuery for Excel 2013 (PowerQuery_2.38.4491.181 (64-bit) [en-us].msi) addon from https://www.microsoft.com/en-gb/download/details.aspx?id=39379 Note you can't have Excel runing whilst installing. You may need to check Task Manager for a background process. If you are running Excel 2016 PowerQuery is built in and under the Data > Get and Transform Tab

To unpivot or normalise a table

1) Add the data into excel and then insert as a table by Insert > From Table

2) PowerQuery > From Table

3) Transform Tab > select the all branch code columns you wish to pivot (not the barcode) > Unpivot Columns

4) Copy to a new work sheet on the original workbook Home > Close and Load

To add the data for the Xmas picks its the same as creating any table:

At the required warehouse:

create table #data(
  barcode varchar(30),
  product int,
  description varchar(100),
  branch_code varchar(50),
  branch_no int,
  whse_no int,
  quantity int)

insert into #data(barcode, description, branch_code, quantity) values('120','description','002',12)

update d set branch_no = s.branch_no, whse_no = w.whse_no, product = b.product from #data d
left outer join barcode b on b.barcode collate Latin1_General_CI_AS = d.barcode collate Latin1_General_CI_AS 
left outer join branch s on s.branch_code collate Latin1_General_CI_AS = substring(d.branch_code collate Latin1_General_CI_AS,1,3)
inner join warehouse w on w.branch_no = s.branch_no


select distinct t.barcode, t.description, p.description from #data t
inner join product p on p.product = t.product
where t.description collate SQL_Latin1_General_CP1_CI_AS <> p.description collate SQL_Latin1_General_CP1_CI_AS

--select top 10 * from #Data where product is null or branch_no is null

select * into _FelipurePicks_2018_01_25 from #data d
where quantity > 0

Make absolutely sure that the Xmas barcodes quoted are the correct Xmas Products - Sue has been known to quote barcodes without the _xmas suffix!!

Because the picks are all in one then a script is required to separate them. Ensure that the source file is changes in 2 separate places and the correct param is passed to traxs_ReplenishmentFrom#Data:

CREATE TABLE #Data(
	guid uniqueidentifier NOT NULL DEFAULT (NEWID()),
	line_no smallint IDENTITY(1,1),
	quantity int,
	product int)
  
BEGIN TRANSACTION

DECLARE @whse_no smallint
DECLARE db_cursor CURSOR FOR  
SELECT DISTINCT whse_no
FROM _Verwood_XmasPicks_2016_Hatchwells   -- CHANGE ACCORDING TO SOURCE FILE

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @whse_no
  
WHILE @@FETCH_STATUS = 0   
BEGIN   
 
   TRUNCATE TABLE #Data
  
   INSERT INTO #Data
	(product, quantity)
	SELECT
		product, quantity
	FROM
		_Verwood_XmasPicks_2016_Hatchwells   -- CHANGE ACCORDING TO SOURCE FILE
	WHERE
		whse_no = @whse_no

	EXEC [dbo].[traxs_ReplenishmentFrom#Data]
		@requesting_whse_no = @whse_no
		-- ,@destination_whse_no = 276 -- RUNNING AT VERWOOD NEEDS AN ADDITIONAL PARAMETER!!!!

     FETCH NEXT FROM db_cursor INTO @whse_no
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

COMMIT TRANSACTION

Following successful, execution check replenishments have been created using the following scripts:

select * from replenishment_request_session where date_time > '2016-11-02 17:00' order by request_no
select * from replenishment_request where request_no >= 1336 order by request_no

Finally, from the Warehouse's Desktop run the normal allocation job:

traxs_warehouse_allocation