DROP VIEW IF EXISTS STOR.V_GROUPED_INVOICE_ITEM;
CREATE VIEW STOR.V_GROUPED_INVOICE_ITEM
AS
SELECT
	MAX(ii.ElementID::varchar(36))::uuid AS GroupedInvoiceItemID
,	si.Storage
,	ii.BillOfGoods
,	SUM(ii.TotalApplicationCount)/ ii.QuantityInPackage AS Amount
,	(SUM(ii.TotalApplicationCount) / ii.QuantityInPackage) * ii.Price AS Sum
,	ii.Price
,	coalesce(ptt.PackageType, pt.PackageType) as PackageType
,	ii.Series
,	ii.SupplierContract
,	ii.ExpirationDate
,	ii.StorageInvoiceAdd
,	ii.SupplierContractAbr
,	ii.Comment
,	ii.StorageInvoice
FROM STOR.T_INVOICE_ITEM ii
join STOR.T_STORAGE_INVOICE si on si.InvoiceID = ii.StorageInvoice
left join lateral
(
	select pf.PackageType 
	from STOR.T_REMAINS_LINK_PACKAGE_TYPE pf
	where pf.UnitPrice = '1'
	and pf.Remains = coalesce(ii.Remains, ii.PrepareRemains)
	limit 1
) ptt on true
left join lateral
(
	select pf.PackageType 
	from PHARM.T_PRODUCT_FORM pf
	where pf.UnitPrice = '1' and pf.BillOfGoods = ii.BillOfGoods
	limit 1
) pt on true
WHERE ii.Amount > 0
GROUP BY si.Storage, ii.BillOfGoods, ii.Price, coalesce(ptt.PackageType, pt.PackageType), ii.Series, ii.SupplierContract
		 , ii.ExpirationDate, ii.StorageInvoiceAdd, ii.SupplierContractAbr, ii.Comment,ii.StorageInvoice, ii.QuantityInPackage;