leohickstein
4/13/2017 - 4:59 PM

Monteserrat - Criação Manual Inventário #tags: monteserrat

Monteserrat - Criação Manual Inventário #tags: monteserrat

--BEGIN TRAN

/*
InventoryAlert => http://54.94.177.227:8081/MRS.Services/CoreService/InventoryAlert
CreateScheduledTasks => http://54.94.177.227:8081/MRS.Services/TaskJobService/CreateScheduledTasks
*/

/* Declaration of variables */
declare @CycleCount as int, @SeqNo as int, @QtyCountZones as int
declare @StoreId as varchar(5)
declare @inventoryName as varchar(40)
declare @stockTakeDate as datetime
 
/* Setting the parameters */
set @CycleCount = ( select isnull(max(cast(CycleCount as int))+1, 1) from [stin].[st_in_inventory] )
set @SeqNo = ( select isnull(max(cast(seqno as int))+1, 1) from [stin].[st_in_inventory] )
set @StoreId = 'S0002'
set @QtyCountZones = 6
set @inventoryName = 'Inv 19/07/2017 - Refrescos em Pó'
set @stockTakeDate = '2017-07-19'
 
 
/* Creating inventory */
insert into [stin].[st_in_inventory]
values (@SeqNo, 'StaCtnCre', @CycleCount, @inventoryName, @stockTakeDate, 'A', getdate())
 
/* Creating the items */
insert into [stin].[st_in_inventoryItem]
select @SeqNo, @CycleCount, i.ItemId, 'A', getdate()
from ra.Item i
	inner join ra.HierarchicalStructure hs on hs.HierarchicalStructureId = substring(i.HierarchicalStructureId,0, 6)
	inner join ra.ItemRetailStore irs on irs.ItemId = i.ItemId and irs.RetailStoreId = @StoreId
where 
   i.HierarchicalStructureId like '1.021.040.070%'
OR i.HierarchicalStructureId like '1.021.040.018%'
 
/* Creating count zones */
declare @QtyCountZone as int
set @QtyCountZone = 1
 
WHILE (@QtyCountZone <= @QtyCountZones)
BEGIN
 
        insert into [stin].[st_in_inventoryStoreWhZone]
        -- Com a nomeclatura dos departamentos
        -- select @SeqNo, @CycleCount, @StoreId, null, substring(i.HierarchicalStructureId, 0, 6), 'Count Zone ' + substring(replace(hs.Name, '/', ''), 0, 25), 'N', 'N', 'A', getdate()
 
        -- Com a nomeclatura das zonas incremental
        select @SeqNo, @CycleCount, @StoreId, null, REPLACE(STR(@QtyCountZone, 3), SPACE(1), '0'), @inventoryName, 'N', 'N', 'A', getdate()
                from ra.Item i
                inner join ra.HierarchicalStructure hs on hs.HierarchicalStructureId = substring(i.HierarchicalStructureId,0, 6)
                inner join ra.ItemRetailStore irs on irs.ItemId = i.ItemId and irs.RetailStoreId = @StoreId
                where 
   i.HierarchicalStructureId like '1.021.040.070%'
OR i.HierarchicalStructureId like '1.021.040.018%'
				group by substring(i.HierarchicalStructureId,0, 6), hs.Name
 
        --values (@SeqNo, @CycleCount, @StoreId, null, REPLACE(STR(@QtyCountZone, 3), SPACE(1), '0'), 'Count Zone ' + cast(@QtyCountZone as varchar), 'N', 'N', 'A', getdate())
 
        set @QtyCountZone = @QtyCountZone + 1
END
 
/*
List data inventory created
select * from [stin].[st_in_inventory] where CycleCount in (@CycleCount)
select * from [stin].[st_in_inventoryItem] where CycleCount  in (@CycleCount)
select * from [stin].[st_in_inventoryStoreWhZone] where CycleCount  in (@CycleCount)
select top 100 * from [tm].[Task] with(nolock) order by 1 desc
--ROLLBACK
--COMMIT
*/