SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'stor.[sp_DeleteRemain]') and xtype = N'P')
drop procedure stor.[sp_DeleteRemain]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.[sp_DeleteRemain]') and xtype = N'P')
drop procedure dbo.[sp_DeleteRemain]
GO

-- =============================================
-- Author:		Anatoliy Pokus
-- Create date: 2017-06-13
-- Description:	Удаление пустого остатка на складе
-- =============================================
CREATE PROCEDURE stor.[sp_DeleteRemain]
	@remainsID uniqueidentifier
AS 
BEGIN 
	set nocount on 
	
	Delete  pt
	from [STOR].T_REMAINS_LINK_PACKAGE_TYPE pt
	join STOR.T_REMAINS r on r.RemainsID = pt.Remains
	Where r.RemainsID = @remainsID
				 and r.Amount = 0  
				 and not exists (select * from STOR.T_INVOICE_ITEM ii where (ii.remains = r.RemainsID or ii.PrepareRemains = r.RemainsID)) 	
				 and not exists (select * from [STOR].T_STORAGE_REST_ON_DAY rest where rest.Remain = @remainsID)

	delete pdd
	from stor.T_PATIENT_DRUG_DELIVERY pdd
		join STOR.T_REMAINS r on r.RemainsID = pdd.Remains
	where pdd.Remains = @remainsID 
		and r.Amount = 0
		and not exists (select * from STOR.T_INVOICE_ITEM ii where (ii.remains = r.RemainsID or ii.PrepareRemains = r.RemainsID)) 	
		and not exists (select * from STOR.T_STORAGE_REST_ON_DAY rest where rest.Remain = @remainsID)	

	delete r 
	from  STOR.T_REMAINS r
	Where r.RemainsID = @remainsID 
	and r.Amount = 0 
	and not exists (select * from STOR.T_INVOICE_ITEM ii where (ii.remains = r.RemainsID or ii.PrepareRemains = r.RemainsID))
	and not exists (select * from [STOR].T_STORAGE_REST_ON_DAY rest where rest.Remain = @remainsID)

	if(exists (select * from VCLib.T_OBJECT_MODIFICATION_ACTION where Name = 'SP_GetSetStorRemains' and Active = 1))
	begin
		exec dbo.SP_GetSetStorRemains  null, 0, 0, @remainsID
	end

END
GO