DROP VIEW IF EXISTS PHARM.V_PRESCRIPTION_GROUPED_BILL_OF_GOODS;
CREATE VIEW PHARM.V_PRESCRIPTION_GROUPED_BILL_OF_GOODS
AS
SELECT	uuid_generate_v4() as PrescriptionGroupedBillOfGoodsID, 
		bg.CTrn,
		trn.CMnn,
		MAx(bg.EhrRus9a9g09e8kl) as EhrRus9a9g09e8kl,
		dv.Value 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, dv.value, bg.MedicationForms, bg.EhrRus9a9g09e8kl, pf.PackageType, pf.Amount;
