IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'stor.[sp_UpdateInvoiceItemFormSSCCProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE stor.[sp_UpdateInvoiceItemFormSSCCProcess]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.[sp_UpdateInvoiceItemFormSSCCProcess]') AND type in (N'P', N'PC'))
DROP PROCEDURE dbo.[sp_UpdateInvoiceItemFormSSCCProcess]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Vitacore (с) Anatoliy
-- Create date: 2023 06 05
-- Description: Обновление INVOICE_ITEM из SSCC
-- =============================================
CREATE PROCEDURE stor.[sp_UpdateInvoiceItemFormSSCCProcess]
    @Id uniqueidentifier
AS
BEGIN
    update ii
	set ii.BillOfGoods  = ISNULL(sc.BillOfGoods, ii.BillOfGoods),
		ii.Series = ISNULL(sc.Serial, ii.Series),
		ii.LotNumber = ISNULL(sc.LotNumber, ii.LotNumber),
		ii.SupplierContract = ISNULL(sc.SupplierContract, ii.SupplierContract),
		ii.SupplierContractAbr = ISNULL(sc.SupplierContractAbr, ii.SupplierContractAbr),
		ii.Comment = ISNULL(sc.Comment, ii.Comment),
		ii.ExpirationDate = ISNULL(sc.ExpirationDate, ii.ExpirationDate),
		ii.Price = ISNULL(sc.Price, ii.Price),
		ii.PackageType = ISNULL(pf.PackageType, ii.PackageType),
		ii.TotalPackageType = ISNULL(pff.PackageType, ii.TotalPackageType),
		ii.VAT = (case when sc.Vat_Value is null or sc.Price is null then ii.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_INVOICE_ITEM ii
	join STOR.T_SHIPPING_CONTAINER sc on sc.ShippingContainerID = ii.SSCC
	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 = @Id

END