Advanced Retail (UK) Ltd
Company Wiki

If a movement is required from a closed store then the only way is to create movement adjustment from a file. Note that this is not the same as transfers into Pets Corner Verwood which is a different process.

The first step is to put the source Data into #Data table. The source can either be via an Excel Spreadsheet or Mobile Download.

Create a temp table on the destination - if its a mobile download then

sp_generate_inserts mobile_data_hdr, @from='from mobile_data_hdr where mobile_data_id = ?'
sp_generate_inserts mobile_data_det, @from='from mobile_data_det where mobile_data_id = ?'

CREATE TABLE [dbo].[_mobile_data_hdr](
[stamp] [timestamp] NOT NULL,
[mobile_data_id] [int] IDENTITY(1,1) NOT NULL,
[terminal_id] [smallint] NOT NULL,
[user_no] [int] NOT NULL,
[date_time] [datetime] NOT NULL,
[download_type] [tinyint] NOT NULL,
[description] [varchar](50) NULL,
[complete] [bit] NOT NULL,
[used_for] [tinyint] NOT NULL,
[doc_terminal_id] [smallint] NULL,
[doc_no] [int] NULL)

CREATE TABLE [dbo].[_mobile_data_det](
[stamp] [timestamp] NOT NULL,
[row_id] [int] IDENTITY(1,1) NOT NULL,
[mobile_data_id] [int] NOT NULL,
[quantity] [decimal](13, 3) NOT NULL,
[quantity_adjusted] [decimal](13, 3) NULL,
[barcode] [varchar](30) NOT NULL,
[location] [varchar](10) NOT NULL,
[product] [int] NULL,
[whs_location] [smallint] NULL,
[location2] [varchar](10) NULL,
[whs_location2] [smallint] NULL,
[manual_addition] [bit] NOT NULL,
[deleted] [bit] NOT NULL,
[complete] [bit] NOT NULL)

Replace with _mobile_data_hdr and _mobile_data_hdr!

set identity_insert _mobile_data_hdr on 
INSERT INTO [_mobile_data_hdr] (...) VALUES (...)
set identity_insert _mobile_data_hdr off

set identity_insert _mobile_data_det on
INSERT INTO [_mobile_data_det] (...) VALUES (...)
set identity_insert _mobile_data_det off

Next step is to create and add to the #Data. There may be issues because the product might be NULL

CREATE TABLE #Data (
 [line_no] [smallint] NOT NULL IDENTITY(1, 1),
 [whse_no] [smallint] NOT NULL ,
 [product] [int] NULL ,
 [quantity] [decimal](11, 3) NOT NULL ,
 [cost] [decimal](11, 4) NULL ,
 [price] [decimal](13, 3) NULL ,
 [vat_code] [tinyint] NULL ,
 [barcode] [varchar] (20) NULL ,
 [description] [varchar] (50) NULL ,
 [guid_batch] [uniqueidentifier] NULL)

INSERT INTO #Data
 ( whse_no,
   d.product,
   d.quantity,
   d.barcode,
   p.description)
select ?, product,  quantity, barcode, description
from _mobile_data_det d
inner join product p on p.product = d.product
--left outer join product p on p.product = d.product
where mobile_data_id = ?

It might be that the barcode could be found so add product and description

update d set product = p.product, description = p.description from #data d
inner join barcode b on b.barcode collate Latin1_General_CI_AS = d.barcode collate Latin1_General_CI_AS
inner join product p on p.product = b.product
where d.product is null 

Finally add the data as a movement adjustment

EXEC  [dbo].[traxs_Utility_MovementFrom#Data] @message= 'Transfer from Verwood'
select * from movements where date_time > getdate()-1 and tran_type = 8