Procedura za poravnanje negativnih zaliha

create procedure [dbo].[sp_izr]
as
--Dokument za interno poravnanje
set nocount on

insert into _log
values (GetDate(), 'START')

-- Obrada artikala

update tHE_SetItem
set anBuyPrice = anRTPrice - anRTPrice * 10 / 100, anPrice = anRTPrice - anRTPrice * 10 / 100, anPriceSupp = anRTPrice - anRTPrice * 10 / 100
where anBuyPrice = 0 and anRTPrice <> 0

if object_id('tempdb..#i') is not null
drop table #i

if object_id('tempdb..#p') is not null
drop table #p

select acIdent
into #i
from tHE_SetItem
where anBuyPrice = 0

select p.acIdent, max(p.anRTPrice) as anRTPrice
into #p
from tHE_Move g
inner join tHE_MoveItem p on g.acKey = p.acKey
inner join tPA_SetDocType d on g.acDocType = d.acDocType and d.acDocType <> 'C'
inner join #i i on p.acIdent = i.acIdent
group by p.acIdent
having max(p.anRTPrice) > 0

update m
set m.anPriceSupp = p.anRTPrice - p.anRTPrice * 10 / 100, m.anPrice = p.anRTPrice - p.anRTPrice * 10 / 100, m.anBuyPrice = p.anRTPrice - p.anRTPrice * 10 / 100
from tHE_SetItem m
inner join #p p on m.acIdent = p.acIdent

------

update tPA_setDocType
set acEvidence = 'F'
where acDocType = 'IZR0'

declare @adStart as datetime,
@anNo int,
@nCount int,
@adDate datetime

set @adStart = (select adMoveOpenDate from tPA_SysParam)
set @anNo = (select datediff(dd, @adStart, getdate()))

set @nCount = 0

while @nCount <= @anNo
begin
set @adDate = (DATEADD (dd , @nCount , @adStart))

if object_id('tempdb..#z')is not null
drop table #z

create table #z
(adDate smalldatetime,
acWareHouse char (30),
acIdent char (16),
anQty float)

insert into #z (adDate, acWareHouse, acIdent, anQty)
select @adDate, acWarehouse, acIdent, sum(anQty) as anQty from
(select g.acReceiver as acWarehouse, p.acIdent, sum(p.anQty) as anQty
from tHE_Move g
inner join tHE_MoveItem p on g.acKey = p.acKey and g.adDate <= @adDate
inner join tPA_setDocType d on g.acDocType = d.acDoctype and d.acEvidence = 'F'
inner join tHE_SetItem m on p.acIdent = m.acIdent and m.acSetOfItem = '200'
where g.acReceiverStock = 'Y'
group by g.acReceiver, p.acIdent
union all
select g.acIssuer as acWarehouse, p.acIdent, sum(p.anQty*-1) as anQty
from tHE_Move g
inner join tHE_MoveItem p on g.acKey = p.acKey and g.adDate <= @adDate
inner join tPA_setDocType d on g.acDocType = d.acDoctype and d.acEvidence = 'F'
inner join tHE_SetItem m on p.acIdent = m.acIdent and m.acSetOfItem = '200'
where g.acIssuerStock = 'Y'
group by g.acIssuer, p.acIdent) as tbl
group by acWarehouse, acIdent
having sum(anQty) < 0

declare @acWarehouse char (30),
@adDateinv smalldatetime,
@cKey char (13),
@acIdent char (16),
@anQty float,
@cOK varchar(2), -- status of adding
@nPoz int, -- addid position
@cError VarChar(1024)

declare btsPoz cursor local fast_forward for
select acWarehouse from #z group by acWarehouse
open btsPoz
fetch from btsPoz into @acWarehouse
while @@fetch_status = 0
begin
-- Kreiraj glavu
exec pHE_MoveCreAll 'IZR0', @acWarehouse, @acWarehouse, @adDate, 2, '', @cKey output, 'F'

if object_id('tempdb..#mitem') is not null
drop table #mitem

create table #mitem
(acIdent char (16),
anQty float,
anNo int identity (1,1) not null)

insert into #mitem (acIdent, anQty)
select acIdent, anQty * -1 from #z where acWarehouse = @acWarehouse

insert into tHE_MoveItem (anPVValue, anPVOCVATBase, anPVOCValue, anPVOCStockValue, anPVOCVATBaseWoExc,
anPVOCForPay, anPVOCForPayWoExc,
anPVOCIncVAT, anPVOCBeatShare, anPVOCIncVATWoExc, anPVOCBeatShareWoExc,
anPriceCurrency, anQty, acKey, anNo, acIdent, acName, acUm, anPrice, anRTPrice, anSalePrice,
acVATCode, anVAT, anStockPrice, anInRTPrice, anInSalePrice,
acVATCodeTR, anVATIn)
select m.anBuyPrice * t.anQty, m.anBuyPrice * t.anQty, m.anBuyPrice * t.anQty, m.anBuyPrice * t.anQty, m.anBuyPrice * t.anQty,
(m.anBuyPrice * t.anQty) * (m.anVAT / 100 + 1 ), (m.anBuyPrice * t.anQty) * (m.anVAT / 100 + 1 ),
(m.anBuyPrice * t.anQty) * m.anVAT / 100 , (m.anBuyPrice * t.anQty) * m.anVAT / 100 , (m.anBuyPrice * t.anQty) * m.anVAT / 100 , (m.anBuyPrice * t.anQty) * m.anVAT / 100 ,
m.anBuyPrice, t.anQty, @cKey, t.anNo, m.acIdent, m.acName, m.acUM, m.anBuyPrice, case when c.anRTPrice is null then m.anRTPrice else c.anRTPrice end, case when c.anSalePrice is null then m.anSalePrice else c.anSalePrice end,
m.acVATCode, m.anVAT, m.anBuyPrice, case when c.anRTPrice is null then m.anRTPrice else c.anRTPrice end, case when c.anSalePrice is null then m.anSalePrice else c.anSalePrice end,
m.acVATCode, m.anVAT
from tHE_SetItem m
inner join #mitem t on m.acIdent = t.acIdent
left join tHE_SetItemPriceForWrh c on m.acIdent = c.acIdent and c.acWarehouse = @acWarehouse

fetch from btsPoz into @acWarehouse
end
close btsPoz
deallocate btsPoz

-- End While
set @nCount = @nCount + 1
end

exec pPA_PriceCalc '', null, null, ''

update tPA_setDocType set acEvidence = 'T' where acDocType = 'IZR0'

exec pPA_StockRecalc

insert into _log
values (GetDate(), 'END')

declare @l smalldatetime,
@t smalldatetime

set @l = (DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1))
set @t = (CAST(FLOOR( CAST( GETDATE() AS FLOAT ))AS DATETIME ))

if DATEADD(DD, 10, @l) <= @t
begin
update tPA_SysParam
set adMoveOpenDate = @l
end

set nocount off