IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'STOR.V_GROUPED_INVOICE_ITEM'))
DROP VIEW [STOR].[V_GROUPED_INVOICE_ITEM]
GO

IF  EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(N'STOR.V_GROUPED_INVOICE_ITEM'))
DROP TABLE [STOR].[V_GROUPED_INVOICE_ITEM]
GO

/****** Object:  View [dbo].[V_GROUPED_INVOICE_ITEM]    Script Date: 05.08.2019 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [STOR].[V_GROUPED_INVOICE_ITEM]
AS
SELECT			CAST(MAX(CAST(ii.ElementID AS varchar(36))) AS uniqueidentifier) AS GroupedInvoiceItemID, 
				si.Storage,
				ii.BillOfGoods, 
				SUM(ii.TotalApplicationCount)/ ii.QuantityInPackage AS Amount, 
				(SUM(ii.TotalApplicationCount) / ii.QuantityInPackage) * ii.Price AS Sum, 
				ii.Price, 
				ISNULL(ptt.PackageType, pt.PackageType) as 'PackageType',
				ii.Series,
				ii.SupplierContract,
				ii.ExpirationDate,
				StorageInvoiceAdd,
				SupplierContractAbr,
				ii.Comment,
				ii.StorageInvoice
FROM            [STOR].T_INVOICE_ITEM ii
join			STOR.T_STORAGE_INVOICE si on si.InvoiceID = ii.StorageInvoice
outer apply(
	select top 1 pf.PackageType 
	from STOR.T_REMAINS_LINK_PACKAGE_TYPE pf
	where pf.UnitPrice = 1 and pf.Remains = ISNULL(ii.Remains, ii.PrepareRemains)
) ptt
outer apply(
	select top 1 pf.PackageType 
	from PHARM.T_PRODUCT_FORM pf
	where pf.UnitPrice = 1 and pf.BillOfGoods = ii.BillOfGoods
) pt
WHERE        (ii.Amount > 0)
GROUP BY si.Storage, ii.BillOfGoods, ii.Price, ISNULL(ptt.PackageType, pt.PackageType), ii.Series, ii.SupplierContract
		 , ii.ExpirationDate, ii.StorageInvoiceAdd, ii.SupplierContractAbr, ii.Comment,ii.StorageInvoice, ii.QuantityInPackage
