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

/****** Object:  View [dbo].[V_GROUP_REMAINS_ON_MNN]    Script Date: 20211018 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [STOR].[V_GROUP_REMAINS_ON_MNN]
AS
SELECT			newid() AS GroupRemainsOnMnnID, 
				Storage,
				trn.CMnn, 
				SUM(r.Amount) AS Amount, 
				bg.MedicationForms
				, dv.value as 'DoseView'
				, (case when dv.value like '%¹%' then SUBSTRING(dv.value, 0,Charindex('¹', dv.value) -1) end) as 'Dose'
				, r.ExpirationDate
				,bg.EhrRus9a9g09e8kl
				, r.PackageType
FROM            [STOR].T_REMAINS AS r
				join PHARM.T_BILL_OF_GOODS bg on bg.BillOfGoodsID = r.BillOfGoods
				join PHARM.T_C_TRN trn on trn.CTrnID = bg.CTrn
				left join PHARM.S_DLO_DoseView dv on dv.Id = bg.DoseView
WHERE        (r.Amount > 0) and trn.CMnn is not null
GROUP BY Storage, trn.CMnn, bg.MedicationForms, dv.value, r.ExpirationDate,bg.EhrRus9a9g09e8kl, r.PackageType
GO
