-- =============================================
-- Author:      Vitacore (с) Anatoliy
-- Create date: 2023 06 05
-- Description: Обновление INVOICE_ITEM из SSCC
-- =============================================
DROP PROCEDURE IF EXISTS stor.sp_UpdateInvoiceItemFormSSCCProcess;

CREATE PROCEDURE stor.sp_UpdateInvoiceItemFormSSCCProcess
(
	"id" uuid
)
    language plpgsql
as
$$
Begin 
    update STOR.T_INVOICE_ITEM ii
	set BillOfGoods = COALESCE(sc.BillOfGoods, BillOfGoods),
		Series = COALESCE(sc.Serial, Series),
		LotNumber = COALESCE(sc.LotNumber, LotNumber),
		SupplierContract = COALESCE(sc.SupplierContract, SupplierContract),
		SupplierContractAbr = COALESCE(sc.SupplierContractAbr, SupplierContractAbr),
		Comment = COALESCE(sc.Comment, Comment),
		ExpirationDate = COALESCE(sc.ExpirationDate, ExpirationDate),
		Price = COALESCE(sc.Price, Price),
		PackageType = COALESCE(pf.PackageType, PackageType),
		TotalPackageType = COALESCE(pff.PackageType, TotalPackageType),
		VAT = (case when sc.Vat_Value is null or sc.Price is null then VAT
					   when sc.Vat_Value = 0 and sc.Price = 0 then 0
					  else  (case when ROUND((sc.Price - sc.Vat_Value) / sc.Price, 2) between '0.89' and '0.91' then 1
								  when ROUND((sc.Price - sc.Vat_Value) / sc.Price, 2) between '0.81' and '0.83' then 2
								  when ROUND((sc.Price - sc.Vat_Value) / sc.Price, 2) between '0.79' and '0.81' then 3
							 end)
					  end)
	from STOR.T_SHIPPING_CONTAINER sc 
	left join PHARM.T_PRODUCT_FORM pf on pf.BillOfGoods = sc.BillOfGoods and pf.UnitPrice = '1'
	left join PHARM.T_PRODUCT_FORM pff on pff.BillOfGoods = sc.BillOfGoods and pf.WriteOff = '1'
	where sc.ShippingContainerID = ii.SSCC and sc.ShippingContainerID = "id";
End;
$$;
