-- =============================================
-- Author:      Vitacore Anatoliy
-- Create date: 2021 03 09
-- =============================================
DROP PROCEDURE IF EXISTS stor.sp_DeleteInvoiceItemForShipConteinerProcess;

CREATE PROCEDURE stor.sp_DeleteInvoiceItemForShipConteinerProcess
(
	"InvoiceId" uuid, 
	"ShipConteinerId" uuid
)
    language plpgsql
as
$$
Begin 
	if exists(select * from STOR.T_STORAGE_INVOICE where InvoiceID = "InvoiceId" and State = 0)
	then
		DROP TABLE IF EXISTS "DELETE";
		CREATE TEMP TABLE "DELETE"(
		    ElementID uuid
		);

		INSERT INTO "DELETE"(ElementID)
		select ii.ElementID
		from STOR.T_INVOICE_ITEM ii
		where StorageInvoice = "InvoiceId" and SSCC = "ShipConteinerId";
		
		update STOR.T_MARK_MESSAGE mm
		set ObjId = null
		from "DELETE" d 
		where d.ElementID = mm.ObjId;

		delete
		from STOR.T_INVOICE_ITEM ii
			using "DELETE" d 
		where d.ElementID = ii.ElementID;

		drop table "DELETE";

	end if;
End;
$$;