DROP VIEW IF EXISTS STOR.V_GROUP_REMAINS_ON_MNN;
CREATE VIEW STOR.V_GROUP_REMAINS_ON_MNN
AS
SELECT
	gen_random_uuid() AS GroupRemainsOnMnnID
,	r.Storage
,	trn.CMnn
,	SUM(r.Amount) AS Amount
,	bg.MedicationForms
,	dv.value as DoseView	
, (case when dv.value like '%№%' then SUBSTRING(dv.value, 0, strpos(dv.value,'№') -1) else dv.value end) as Dose			
,	r.ExpirationDate
,	bg.EhrRus9a9g09e8kl
,	r.PackageType
,	r.SupplierContractAbr
,	rlpt.PackageType as AppointmentPackageType
,	rlpt.Amount as AppointmentVolume
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
	left join PHARM.T_PRODUCT_FORM pf on pf.BillOfGoods = r.BillOfGoods and pf.Appointment = '1'
	left join STOR.T_REMAINS_LINK_PACKAGE_TYPE rlpt on rlpt.Remains = r.RemainsID and rlpt.Appointment = '1'
WHERE r.Amount > 0
GROUP BY Storage, trn.CMnn, bg.MedicationForms, dv.value, r.ExpirationDate,bg.EhrRus9a9g09e8kl, r.PackageType, r.SupplierContractAbr, rlpt.PackageType, rlpt.Amount;