declare @source table(documentID uniqueidentifier, itemID uniqueidentifier
, correctedItemPrice decimal(18,2), sysCorrectedItemPrice decimal(18,2))
;with prices as (
select itemID, isnull(nullif(nullif(d.currency, ''), 'NULL'), 'PLN') currency, correctedItemPrice
, isnull(cd.documentDate, d.documentDate) documentDate, isnull(cd.accDate, d.accDate) accDate, isnull(cd.modDate, d.modDate) modDate
, ROW_NUMBER() OVER(PARTITION BY itemID, isnull(nullif(nullif(d.currency, ''), 'NULL'), 'PLN') ORDER BY isnull(cd.documentDate, d.documentDate) DESC, isnull(cd.accDate, d.accDate) DESC, dl.documentOrdinalNumber DESC, d.documentFullNR DESC) rowno
from document.documentLine dl
inner join document.document d on d.documentID = dl.documentID
inner join document.documentType dt on dt.documentType = d.documentType and dt.masterDocumentCategory = 'warehouse' and dt.altersLastPrice = 1
left join document.document cd on d.initialCorrectedDocumentId = cd.documentID
where dl.[status] = 1
)
, pricesMaxNoPerDay as (
select itemID, currency, documentDate, max(rowno) maxrowno from prices group by itemID, currency, documentDate
)
insert into @source
select documentID, itemID, correctedItemPrice, sysCorrectedItemPrice
from (select itemID, isnull(nullif(nullif(d.currency, ''), 'NULL'), 'PLN') currency, correctedItemPrice, sysCorrectedItemPrice, dl.documentID
, isnull(cd.documentDate, d.documentDate) documentDate, isnull(cd.accDate, d.accDate) accDate, isnull(cd.modDate, d.modDate) modDate
, ROW_NUMBER() OVER(PARTITION BY itemID, isnull(nullif(nullif(d.currency, ''), 'NULL'), 'PLN') ORDER BY isnull(cd.documentDate, d.documentDate) DESC, isnull(cd.accDate, d.accDate) DESC, dl.documentOrdinalNumber DESC, d.documentFullNR DESC) rowno
from document.documentLine dl
inner join document.document d on d.documentID = dl.documentID
inner join document.documentType dt on dt.documentType = d.documentType and dt.masterDocumentCategory = 'warehouse' and dt.altersLastPrice = 1
left join document.document cd on d.initialCorrectedDocumentId = cd.documentID
where dl.[status] = 1
and dl.itemID in
(
select lpp.itemID from (
select * from prices src where src.rowno = 1
) lpp
inner join pricesMaxNoPerDay pmax on pmax.itemID = lpp.itemID and pmax.currency = lpp.currency and lpp.documentDate = pmax.documentDate
inner join prices pfirst on pfirst.itemID = lpp.itemID and pfirst.currency = lpp.currency and pfirst.rowno = pmax.maxrowno
left join prices p2 on p2.itemID = lpp.itemID and p2.currency = lpp.currency and p2.rowno = pmax.maxrowno - 1 and p2.rowno > 1
full join [item].[pricing] pr
on pr.itemID = lpp.itemID and pr.currency = lpp.currency
where isnull(lpp.correctedItemPrice, -1) != isnull(pr.lastPurchasePrice, -1)
and isnull(pfirst.correctedItemPrice, -1) != isnull(pr.lastPurchasePrice, -1)
and (p2.correctedItemPrice is null or isnull(p2.correctedItemPrice, -1) != isnull(pr.lastPurchasePrice, -1))
--and datediff(minute, lpp.modDate, lpp.accDate) < -1
)
) srcsrc where rowno = 1
select count(*) from @source
declare @itemID uniqueidentifier
declare @documentID uniqueidentifier
declare @sysCorrectedItemPrice decimal(18,2)
declare @correctedItemPrice decimal(18,2)
declare @username varchar(50)
while exists(select 1 from @source) begin
select top 1 @itemID = itemID
, @documentID = documentID
, @sysCorrectedItemPrice = sysCorrectedItemPrice
, @correctedItemPrice = correctedItemPrice
from @source
delete from @source where itemID = @itemID and documentID = @documentID
select @username = modUsername from wms.item where itemID = @itemID
exec [document].[documentLine_updateLastPrice]
-- Add the parameters for the stored procedure here
@username = @username,
@documentID = @documentID,
@itemID = @itemID,
@sysItemPrice = @sysCorrectedItemPrice,
@itemPrice = @correctedItemPrice,
@sysAdditionalPrice = null
end
