DROP VIEW IF EXISTS PHARM.V_GROUPED_BILL_OF_GOODS;
CREATE VIEW PHARM.V_GROUPED_BILL_OF_GOODS
AS
WITH CTE AS
(
SELECT	
	ROW_NUMBER() OVER( PARTITION BY 
		bg.MedicationForms, 
		bg.PackQuantity, 
		bg.DoseView, 
		pt.PaymentType, 
		--pt.DateBegin, 
		--pt.DateEnd,
		(case when trn.ProductType = 0 then trn.CMnn end),
		(case when (trn.ProductType = 0 and mnn.NameRus = '~') or trn.ProductType <> 0 then trn.CTrnID end)
		ORDER BY bg.EhrRus2m69i03gtd DESC ) AS ROW_N
,	(coalesce((case when trn.ProductType = 0 then trn.CMnn else null end)::varchar(36), '') ||
	coalesce((case when (trn.ProductType = 0 and mnn.NameRus = '~') or trn.ProductType <> 0 then trn.CTrnID else null end)::varchar(36), '') ||
	coalesce(bg.DoseView::varchar(36), '') ||
	coalesce(bg.PackQuantity::varchar(36), '') || 
	coalesce(bg.MedicationForms::varchar(36), '') ||
	coalesce(pt.PaymentType::varchar(36), '')
	--coalesce(to_char(pt.DateBegin, 'YYYY-MM-DD'), '') ||
	--coalesce(to_char(pt.DateEnd, 'YYYY-MM-DD'), '')
	)::varchar(256) AS GroupedBillOfGoodsID
,	bg.BillOfGoodsID
,	(case when trn.ProductType = 0 then trn.CMnn end) as CMnn
,	(case when (trn.ProductType = 0 and mnn.NameRus = '~') or trn.ProductType <> 0 then trn.CTrnID end) as CTrn
,	bg.DoseView
,	bg.PackQuantity
,	bg.MedicationForms
,	pt.PaymentType
,	pt.DateBegin
,	pt.DateEnd
FROM PHARM.T_BILL_OF_GOODS AS bg
join PHARM.T_C_TRN trn on trn.CTrnID = bg.CTrn
left join PHARM.T_C_MNN mnn on mnn.CMnnID = trn.CMnn
join PHARM.T_BILL_OF_GOODS_LINK_PAYMENT_TYPE pt on pt.BillOfGoods = bg.BillOfGoodsID
where bg.Active = '1'
and pt.DateBegin <= current_timestamp::timestamp
and (pt.DateEnd is null or pt.DateEnd >= current_timestamp::timestamp)
)
SELECT 
	GroupedBillOfGoodsID
,	BillOfGoodsID as BillOfGoods
,	CMnn
,	CTrn
,	DoseView
,	PackQuantity
,	MedicationForms
,	PaymentType
,	DateBegin
,	DateEnd 
FROM CTE
WHERE ROW_N = 1;