
begin tran

declare @warehouseID uniqueidentifier = (select locationID from wms.location where locationCode = '112')

--Dokumenty

update dl set dl.locationID = null from document.documentLine dl inner join document.document d on d.documentID = dl.documentID where dl.locationID is not null and d.warehouseID = @warehouseID
update dl set dl.locationIDB = null from document.documentLine dl inner join document.document d on d.documentID = dl.documentID where dl.locationIDB is not null and d.warehouseIDB = @warehouseID

select l.locationCode, d.documentFullNR, dl.addDate addedDate, * from document.documentLine dl 
inner join document.document d on d.documentID = dl.documentID 
inner join wms.location l on l.locationID = dl.locationID
where dl.locationID is not null and d.warehouseID = @warehouseID
order by dl.addDate

--Stany magazynowe

update sb set locationID = null 
from wms.stockBalance sb
left join wms.stockBalance sbnl on sb.itemID = sbnl.itemID and sbnl.warehouseID = sb.warehouseID and sbnl.locationID is null
where sb.warehouseID = @warehouseID and sb.locationID is not null and sbnl.stockBalanceID is null

delete from wms.stockBalance
where warehouseID = @warehouseID and locationID is not null and quantity = 0 and lockedInventorySheetLineID is null

update sb set
quantity = sb.quantity + sbnl.quantity,
value = sb.value + sbnl.value,
lastLocationID = null
from wms.stockBalance sb
left join wms.stockBalance sbnl on sb.itemID = sbnl.itemID and sbnl.warehouseID = sb.warehouseID and sbnl.locationID is null
where sb.warehouseID = @warehouseID and sb.locationID is not null and sbnl.stockBalanceID is not null

delete from sbnl
from wms.stockBalance sb
left join wms.stockBalance sbnl on sb.itemID = sbnl.itemID and sbnl.warehouseID = sb.warehouseID and sbnl.locationID is null
where sb.warehouseID = @warehouseID and sb.locationID is not null and sbnl.stockBalanceID is not null

update sb set
locationID = null
from wms.stockBalance sb
left join wms.stockBalance sbnl on sb.itemID = sbnl.itemID and sbnl.warehouseID = sb.warehouseID and sbnl.locationID is null
where sb.warehouseID = @warehouseID and sb.locationID is not null

select sb.locationID, sb.quantity sbQuantity, sbnl.quantity sbnlQuantity, sb.value sbValue, sbnl.value sbnlValue, sb.lastLocationId sbLastLocationID, sbnl.lastLocationId sbnlLastLocationID
, sb.minQuantity sbMinQuantity, sbnl.minQuantity sbnlMinQuantity, sb.lockedInventorySheetLineID sbLockedInventorySheetLineID, sbnl.lockedInventorySheetLineID sbnlLockedInventorySheetLineID
from wms.stockBalance sb 
inner join wms.location l on l.locationID = sb.locationID
left join wms.stockBalance sbnl on sb.itemID = sbnl.itemID and sbnl.warehouseID = sb.warehouseID and sbnl.locationID is null
where sb.warehouseID = @warehouseID and sb.locationID is not null 

--Stany magazynowe dzienne

update sb set
locationID = null
from wms.stockBalanceDate sb
left join wms.stockBalanceDate sbnl on sb.stockBalanceDate = sbnl.stockBalanceDate 
and sb.itemID = sbnl.itemID and sbnl.warehouseID = sb.warehouseID and sbnl.locationID is null
where sb.warehouseID = @warehouseID and sb.locationID is not null and sbnl.stockBalanceDate is null

update sbnl set
quantityIN = sbnl.quantityIN + sb.quantityIN,
valueIN = sbnl.valueIN + sb.valueIN,
quantityOUT = sbnl.quantityOUT + sb.quantityOUT,
valueOUT = sbnl.valueOUT + sb.valueOUT
from wms.stockBalanceDate sb
left join wms.stockBalanceDate sbnl on sb.stockBalanceDate = sbnl.stockBalanceDate 
and sb.itemID = sbnl.itemID and sbnl.warehouseID = sb.warehouseID and sbnl.locationID is null
where sb.warehouseID = @warehouseID and sb.locationID is not null and sbnl.stockBalanceDate is not null

delete from sb
from wms.stockBalanceDate sb
left join wms.stockBalanceDate sbnl on sb.stockBalanceDate = sbnl.stockBalanceDate 
and sb.itemID = sbnl.itemID and sbnl.warehouseID = sb.warehouseID and sbnl.locationID is null
where sb.warehouseID = @warehouseID and sb.locationID is not null and sbnl.stockBalanceDate is not null


select l.locationCode, sb.*, sbnl.*, 
sb.quantityIN + sbnl.quantityIN sumQuantityIN, sb.valueIN + sbnl.valueIN sumValueIN,  
sb.quantityOUT + sbnl.quantityOUT sumQuantityIN, sb.valueOUT + sbnl.valueOUT sumValueOUT  
from wms.stockBalanceDate sb
inner join wms.location l on l.locationID = sb.locationID
left join wms.stockBalanceDate sbnl on sb.stockBalanceDate = sbnl.stockBalanceDate 
and sb.itemID = sbnl.itemID and sbnl.warehouseID = sb.warehouseID and sbnl.locationID is null
where sb.warehouseID = @warehouseID and sb.locationID is not null 

--inwentaryzacja

update isl
set isl.locationID = null
from item.inventorySheetLine isl inner join item.inventorySheet ins on ins.inventorySheetID = isl.inventorySheetID
where ins.inventorySheetName = 'MAGAZYN 112'
and isl.locationID is not null


select * from item.inventorySheetLine isl inner join item.inventorySheet ins on ins.inventorySheetID = isl.inventorySheetID
where ins.inventorySheetName = 'MAGAZYN 112'
and isl.locationID is not null

select documentFullNR, addDate addedDate, * from document.documentLine 
where itemID = '9c7db8eb-ae48-418c-b7ae-1bded6cbc47f' and [status] >= 0 and locationID is not null order by addDate

select * from wms.location where locationID = 'D8CA5336-EE6E-E411-940B-D89D6719E0C9'

rollback tran