Advanced Retail (UK) Ltd
Company Wiki

PLEASE NOTE THAT THERE IS A NEW PROCESS FOR SETTING UP SCHEDULED STOCKTAKES WHICH CAN BE FOUND HERE:

http://advanced-retail.a2hosted.com/doku.php?id=creating_new_scheduled_stocktakes_-_new_process

Every month Matt Murtagh provides a list of Scheduled Stocktakes which need to be manually created and then cloned. The intention is for HO to create themselves but the maintenance isn't in place yet.

The process is to first create a stocktake for each product category for whse_no = 0 and then to clone these stocktakes for the stores.

Create the stocktake using changing the month_name (put on the description), create date and complete_by (last date in month) as appropriate:

traxs_Stocktake_Schedule_CreateByProductCategory
@product_category_name='F<product category description as stored on table',
@month_name='July',
@create_date='2016-07-02',
@create_hour=8,
@complete_by='2016-07-31'
  
select * from barcode where barcode like '%BUNPLA%' --116435 
select * from product where product = 116435

traxs_Stocktake_Schedule_CreateByProduct
@product = 116435,
@month_name='August',
@create_date='2016-08-02',
@create_hour=8,
@complete_by='2016-08-31'

If nothing is created then check the product category table for the description SELECT * FROM prod_category WHERE description LIKE '%frozen%'

Then clone each stocktake for each store, one per product category:

You need the guid from the original whse_no = 0 created in the previous step
select guid, * from stocktake_schedule_hdr where datetime_created > '2016-07-01 12:30' and whse_no = 0

For every product category created
traxs_Stocktake_Schedule_Clone_Wrap @guid = '<guid>'

Sometimes you might need to change the times of a scheduled stocktake which can be changed:

EXEC traxs_Engineer_StocktakeScheduled_Reset
@guid = '<stocktake_schedule_hdr guid>',
@create_date = 'CCYY-MM-DD',
@create_hour = HH

The following sequence of events are used for Pets Corner (non-hosted only). The hosted version does the use the y_process_queue to distrbute and create a scheduled job:

1. A model stocktake created based on a previous template

2. Scheduled stocktake created on HO for whse_no = 0 using traxs_Stocktake_Schedule_CreateByProductCategory or traxs_Stocktake_Schedule_CreateByProduct

3. Stocktake cloned for all relevant stores using traxs_Stocktake_Schedule_Clone_Wrap

4. Stocktake created at HO which then polls to the store

5. At the store, insertion of stocktake to stokctake_scheduled_hdr causes a trigger tr_ScheduledStocktake_Implement (INSERT only) to add to y_queue

6. A job traxs_JobAgent_10MinJobs runs every 10 mins to pick up from the y_process_queue and writes to job_queue using traxs_Stocktake_Schedule_JobQueue_Insert.

7. This job is picked up at the time of create_date / create_time to create the stocktake.dbo.stocktake_log and stocktake.dbo.stocktake_count (or stocktake_hdr/stocktake_det for hosted)

Some useful tickets #28304 / #23190

When creating a new stocktake from scratch the process uses traxs_Stocktake_Schedule_Create with the following parameters:

select * into #stock_rule from [dbo].[x_stocktake_schedule_stock_rule]
1	All
2	Exclude Zero
3	Greater Than Zero
4	Less than Zero
5	Exclude zero quantities except where moved in last 30 days
6	Exclude zero quantities except where moved since last stocktake

select * into #criteria_rule from [dbo].[x_stocktake_schedule_criteria_rule] where row_id in (1,2,3)
1	All
2	Top X selling lines by value
3	Top X selling lines by quantity

select * into #criteria_type from [dbo].[x_stocktake_schedule_criteria_type]
1	Product Group
2	Prod Category
3	Supplier
4	Product
5	Status