DROP VIEW IF EXISTS PHARM.V_PRESCRIPTION_GROUPED_BILL_OF_GOODS;
CREATE VIEW PHARM.V_PRESCRIPTION_GROUPED_BILL_OF_GOODS
AS
SELECT	gen_random_uuid() as PrescriptionGroupedBillOfGoodsID, 
		bg.CTrn,
		trn.CMnn,
		MAx(bg.EhrRus9a9g09e8kl) as EhrRus9a9g09e8kl,
		(CASE WHEN STRPOS(dv.Value, '№') <> 0 THEN BTRIM(SUBSTRING(dv.Value FROM 1 FOR STRPOS(dv.Value, '№') - 1)) else dv.Value END) AS DoseView,
		bg.MedicationForms,
		trn.ProductType,
		pf.PackageType as AppointmentPackageType,
		pf.Amount as AppointmentVolume
FROM  PHARM.T_BILL_OF_GOODS bg
		join PHARM.T_C_TRN trn on trn.CTrnID = bg.CTrn
		join PHARM.T_C_MNN mnn on mnn.CMnnId = trn.CMnn
		join PHARM.T_MEDICATION_FORMS mf on mf.MedicationFormsID = bg.MedicationForms
		left join PHARM.S_DLO_DoseView dv on dv.Id = bg.DoseView
		left join PHARM.T_PRODUCT_FORM pf on pf.BillOfGoods = bg.BillOfGoodsID and pf.Appointment = '1'
WHERE bg.Active = '1' and trn.ProductType not in (2,3,4)
GROUP BY bg.CTrn, trn.CMnn, trn.ProductType, (CASE WHEN STRPOS(dv.Value, '№') <> 0 THEN BTRIM(SUBSTRING(dv.Value FROM 1 FOR STRPOS(dv.Value, '№') - 1)) else dv.Value END), bg.MedicationForms, bg.EhrRus9a9g09e8kl, pf.PackageType, pf.Amount;
