SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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

-- =============================================
-- Author:		Anatoliy Pokus
-- Create date: 2026-03
-- Description:	Заполнение элемента накладной из SGTINS
-- =============================================
CREATE PROCEDURE stor.[sp_FillInvoiceItemFromSGTINS]
	@StorageInvoice uniqueidentifier
AS 
BEGIN 
	set nocount on 
	
	update ii
	set ii.BillOfGoods = bg.BillOfGoodsID, 
		ii.PackageType = bg.ptU,
		ii.TotalPackageType = bg.ptW,
		ii.TotalApplicationCount = ii.Amount * bg.QuantityInPackage,
		ii.QuantityInPackage = bg.QuantityInPackage
	from STOR.T_INVOICE_ITEM ii
		join STOR.T_SGTINS sg on sg.IdInvoiceItem = ii.ElementID
		cross apply(select top 1 bg.BillOfGoodsID, 
								 pfU.PackageType as ptU, 
								 pfw.PackageType as ptW, 
								 Isnull((select exp(SUM(log(abs(Amount)))) 
										 from PHARM.T_PRODUCT_FORM pf
									     where pf.BillOfGoods = bg.BillOfGoodsID and pf.OrdinalNumber < pfU.OrdinalNumber and pf.OrdinalNumber >= pfW.OrdinalNumber
										 group by pf.BillOfGoods), 1) as QuantityInPackage
					from PHARM.T_BILL_OF_GOODS bg
						left join PHARM.T_PRODUCT_FORM pfW on pfW.BillOfGoods = bg.BillOfGoodsID and pfW.WriteOff = 1
						left join PHARM.T_PRODUCT_FORM pfU on pfU.BillOfGoods = bg.BillOfGoodsID and pfU.UnitPrice = 1
					where bg.Active = 1 and bg.Gtin = SUBSTRING(sg.Sgtin, 3, 14)
			) as bg
	where ii.StorageInvoice = @StorageInvoice and ii.BillOfGoods is null; 

END
GO


