

/*
SRS 905-document-unlock

document.document - tabela z naglowakmi dokumentow
document.documentline - tabela z pozycjami
wms.item - dane indeksów materiałowych

document.documentline.status = -1   - usuniety 
document.documentline.status = 0	- szkic 
document.documentline.status = 1	- zatwierdzony

document.documentline.itemquantity		- ilosc na dokumencie
document.documentline.itemquantityleft	- ilosc pozostala na dokumencie

document.documentline.sysItemValue		- wartosc pozycji na dokumencie
document.documentline.sysItemValueLeft	- wartosc  pozostala na dokumencie


*/



declare @documentid uniqueidentifier 
select @documentid=documentid from document.document where  documentFullNR='PZI/1/001/1121'

  
-- Sprawdzenie czy dokument istnieje 
if not exists(select * from document.document where documentid = @documentid and status>0) begin
	raiserror('#error.document.not.exist', 16, 1);
	return;
end

begin transaction XXX

		-- usuniecie smieci draftow
		delete from document.documentLine  where status =-1 and documentID = @documentid

		select * from document.documentLine where documentID = @documentid 
		declare @documentLineToRemove table(documentLineID uniqueidentifier, sourceDocumentLineID uniqueidentifier, itemID uniqueidentifier
		, itemQuantity decimal(18,3), sysItemValue decimal(18,2))

		-- utworzenie tabelki tymczasowej z pozycjami dokumentów do odtwierdzenia
		insert into @documentLineToRemove
		select documentLineID, sourceDocumentLineID, itemID, itemQuantity, sysItemValue from document.documentLine 
		where [status] = 1 and documentID =@documentid 

		-- jakie itemy sa w bazie
		declare @itemIDTable table(itemID uniqueidentifier)
		insert into @itemIDTable select distinct itemID from @documentLineToRemove


		declare @currentDocumentLineID uniqueidentifier
		declare @currentSourceDocumentLineID uniqueidentifier
		declare @currentItemID uniqueidentifier
		declare @currentItemQuantity decimal(18,3)
		declare @currentItemValue decimal(18,2)

		-- rekurencja
		while exists(select * from @documentLineToRemove) begin
			select top 1 @currentDocumentLineID = documentLineID, @currentSourceDocumentLineID = sourceDocumentLineID,
			@currentItemQuantity = itemQuantity, @currentItemValue = sysItemValue
			from @documentLineToRemove

			delete from @documentLineToRemove where documentLineID = @currentDocumentLineID

			set nocount off

			--odblokowanie pozycji na dokumencie i przywrocenie ilsci na parii zrodlowej
			update document.documentLine set itemQuantityLeft = itemQuantityLeft + @currentItemQuantity,
			sysItemValueLeft = sysItemValueLeft + @currentItemValue
			where documentLineID = @currentSourceDocumentLineID
			and itemQuantityLeft + @currentItemQuantity <= itemQuantity
			and sysItemValueLeft + @currentItemValue <= sysItemValue
			and outcomeShiftLineID is null


			set nocount on
	
		end

		-- ustawienie dokuemntu jako szkic
		update document.documentLine set status = 0 where documentID =@documentid  
		update document.document set status = 0 where documentID = @documentid 

		-- naprawia stany dla indeksu 
		while exists(select * from @itemIDTable) begin
			select top 1 @currentItemID = itemID from @itemIDTable
			delete from @itemIDTable where itemID = @currentItemID

			set nocount off

			exec wms.stockBalanceFix @username = 'sys', @itemID = @currentItemID

			set nocount on
		end
  
 commit TRAN XXX
  
