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

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [PHARM].[V_PRESCRIPTION_GROUPED_BILL_OF_GOODS]
AS
SELECT	newid() 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
GO
