Advanced Retail (UK) Ltd
Company Wiki

Schedule Stocktakes have changed and are now using different tables and also become more flexible:

https://docs.google.com/document/d/17k_0YQXjxHiON1hRfj8BWYc9YyfmWOzebBa4U04POPM/edit

The plan is that the customer will be able to add and maintain the schedules themselves.

Firstly, it is important to note that in the short term the existing schedule stocktake process will run in parallel to enable the weekly negative stocktakes, Top 10s (for Pets) and Stocktake reminders to continue.

Also note that the existing Pets corner SPROCs for creating stocktakes has not yet been changed or equivalents created.

Finally when adding new stocktakes be very wary of whether the stocktake status should be set to 'started' in the case of Pets or 'snapshot created' in the case of MA There are 3 table involved

[stocktake_create_schedule_hdr] - which will always exist [stocktake_create_schedule_filter] and [stocktake_create_schedule_products] = =one or both will exist. If both exist then an intersection of products affected by both tables wil be created in [traxstemp].[__StocktakeScheduleProducts_Combined]

There are 3 useful views which help explain what is been set up:

[vw_StocktakeCreateScheduleFilter]
[vw_StocktakeCreateSchedule_LogicRules]
[vw_StocktakeCreateScheduleProducts]

Examples of scripts to set up a scheduled stocktake for Pets (see https://advancedretail.freshdesk.com/a/tickets/122910)

--- for category code 1
INSERT INTO [stocktake_create_schedule_hdr] ([user_no_created],[date_time_created],[description],[stocktake_date],[stocktake_hour],[whse_no],[create_snapshot],[status],[complete_by],[message],[stocktake_id])
VALUES(0,getdate(),'Xmas Stocktakes','2019-02-01',6,0,0,1,'2019-02-28',NULL,NULL)

insert into stocktake_create_schedule_products ([hdr_id], [identifier_type], [identifier_int], [identifier_guid], [product_grouping], [archive])
select ?, 101, category_code, NULL, 1, 0
from category1 where description in ( 'XMAS','XMAS 11','XMAS12','XMAS13','XMAS14','XMAS15','XMAS16','XMAS-OLD')
    
-- for product category
INSERT INTO [stocktake_create_schedule_hdr] ([user_no_created],[date_time_created],[description],stocktake_date],[stocktake_hour],[whse_no],[create_snapshot],[status],[complete_by],[message],[stocktake_id])
VALUES(0,getdate(),'February - DOG BISCUITS','2019-02-01',8,0,0,1,'2019-02-28',NULL,NULL)
  
insert into stocktake_create_schedule_products ([hdr_id], [identifier_type], [identifier_int], [identifier_guid], [product_grouping], [archive])
VALUES(?, 3, 9, NULL, 1, 0)

-- for a single product
INSERT INTO [stocktake_create_schedule_hdr] ([user_no_created],[date_time_created],[description],stocktake_date],[stocktake_hour],[whse_no],[create_snapshot],[status],[complete_by],[message],[stocktake_id])
VALUES(0,getdate(),'February - BUNPLA','2019-02-01',8,0,0,1,'2019-02-28',NULL,NULL)

insert into stocktake_create_schedule_products ([hdr_id], [identifier_type], [identifier_int], [identifier_guid], [product_grouping], [archive])
VALUES(?, 1, 116435, NULL, 1, 0)

IT'S VERY IMPORTANT THAT THE CORRECT hdr_id IS PUT INTO stocktake_create_schedule_products

If filters needed to be added then the following SPROCS can be added to make life easier (see documentation for examples):

– stock EXEC [dbo].[traxs_StocktakeCreateSchedule_Filter_SimpleInsert] – movements EXEC [dbo].[traxs_StocktakeCreateSchedule_Filter_SimpleInsert] – sales EXEC [dbo].[traxs_StocktakeCreateSchedule_Filter_Type2Insert]

In a similar way to the old process you create the stocktake for whse_no = 0 and then clone for all required stores

  
drop table #data
CREATE TABLE #Data (whse_no smallint)
  
-- insert all stores that you want the pick at in here
INSERT INTO #Data (whse_no)
select whse_no from branch b
inner join warehouse w on w.branch_no = b.branch_no
where branch_status = 2 
and primary_warehouse = 1
and allow_sales = 1
and archive = 0
and description not like '%dogwood%'
and whse_no not in (1,44,50,59,102)

EXEC [traxs_StocktakeSchedule_CreateStocktake_CloneFor#Data] 1

Then once polled to Store this needs to be run at the store

traxs_StocktakeSchedule_CreateStocktake_Wrap