-- =============================================
-- Author:		Anatoliy Pokus
-- Create date: 2026-03
-- Description:	Заполнение элемента накладной
-- =============================================

DROP PROCEDURE IF EXISTS stor.sp_fillinvoiceitemfromsgtins;
CREATE PROCEDURE stor.sp_fillinvoiceitemfromsgtins
(
	"StorageInvoice" uuid
)
    language plpgsql
as
$$
Begin 
	
	update STOR.T_INVOICE_ITEM ii
	set 
		BillOfGoods = bg.BillOfGoodsID, 
		PackageType = bg.ptU,
		TotalPackageType = bg.ptW,
		TotalApplicationCount = ii.Amount * bg.QuantityInPackage,
		QuantityInPackage = bg.QuantityInPackage
	from STOR.T_SGTINS sg 
		CROSS JOIN LATERAL(select  bg.BillOfGoodsID, 
								 pfU.PackageType as ptU, 
								 pfW.PackageType as ptW, 
								 Coalesce((select EXP(SUM(LN(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 = true
						left join PHARM.T_PRODUCT_FORM pfU on pfU.BillOfGoods = bg.BillOfGoodsID and pfU.UnitPrice = true
					where bg.Active = true and bg.Gtin = SUBSTRING(sg.Sgtin FROM 3 FOR 14) limit 1
			) as bg
	where sg.IdInvoiceItem = ii.ElementID and ii.StorageInvoice = "StorageInvoice" and ii.BillOfGoods is null; 

End;
$$;


