-- =============================================
-- Author:		Anatoliy Pokus
-- Create date: 2017-06-13
-- Description:	Удаление пустого остатка на складе
-- =============================================
DROP PROCEDURE IF EXISTS stor.sp_deleteremain;

CREATE PROCEDURE stor.sp_deleteremain
(
	"invoiceID" uuid
)
    language plpgsql
as
$$
Begin 

		DROP TABLE IF EXISTS tempData;
			CREATE TEMP TABLE tempData(
			    ID uuid
			);
            
        insert into tempData
        select  pt.RemainsLinkPackageTypeID as ID
	    from STOR.T_REMAINS_LINK_PACKAGE_TYPE pt
	        join STOR.T_REMAINS r on r.RemainsID = pt.Remains
	    Where r.StorageInvoiceAdd = "invoiceID" and r.Amount = 0;
                     
        delete 
		from stor.T_REMAINS_LINK_PACKAGE_TYPE pt
			using tempData d
        where dd.ID = pt.RemainsLinkPackageTypeID;

        truncate table tempData;

        insert into tempData
	    select pdd.PatientDrugDeliveryID
	    from stor.T_PATIENT_DRUG_DELIVERY pdd
	    	join STOR.T_REMAINS r on r.RemainsID = pdd.Remains
	    Where r.StorageInvoiceAdd = "invoiceID" and r.Amount = 0;

	    delete 
	    from stor.T_PATIENT_DRUG_DELIVERY pdd
			using tempData d
        where dd.ID = pdd.PatientDrugDeliveryID;

        truncate table tempData;
        
        insert into tempData
	    select r.RemainsID
	    from  STOR.T_REMAINS r
	    Where r.StorageInvoiceAdd = "invoiceID" and r.Amount = 0;
        
	    delete 
	    from  STOR.T_REMAINS r
			using tempData d
        where dd.ID = r.RemainsID;

        drop table tempData;

	--if exists(select* from VCLib.T_OBJECT_MODIFICATION_ACTION where Name = 'SP_GetSetStorRemains' and Active = '1')
	--then
	--	call SP_GetSetStorRemains(null, 0,0, "remainsID");
	--end if;
End;
$$;
