CREATE OR ALTER PROCEDURE HM.hm_DP_PozycjeKontraktu
@id INT
AS
DECLARE @tblPoz TABLE (lp INT, NewPosCorr SMALLINT)
INSERT @tblPoz
	SELECT DISTINCT t.lp, MIN(t.NewPosCorr)
	FROM
	(
		SELECT DISTINCT(DP.lp)
		,CASE WHEN DP.superkoryg = DP.super THEN 0 ELSE 1 END AS NewPosCorr
		FROM hm.DP
		WHERE DP.superkoryg=@id and (DP.flag & 0x4000)=0
		UNION
		SELECT DISTINCT(DP.lp)
		,1 AS NewPosCorr
		FROM hm.DP
		JOIN hm.ZZ ON ZZ.typ = 152 AND ZZ.baza1 = 16 AND ZZ.id1 = @id AND ZZ.subtyp IN (136, 137, 152, 153)
		WHERE superkoryg = ZZ.id2
	) AS t
	GROUP BY t.lp;

with FN as (
SELECT min(tp.lp) as lp, 
	   min(kz.flag) as flag, 
	   min(kz.idtw) as idtw, 
	   max(kz.kod) as kod,
	   MAX(kz.opis) as opis,
	   min(kz.stvat) as stvat,
	   sum(kz.nettoKon)as nettoKon, 
	   sum(kz.vatKon)as vatKon,  
	   sum(kz.nettoZal) as nettoZal, 
	   sum(kz.vatZal) as vatZal,
	   SUM(kz.walNettoKon) AS walNettoKon, 
	   SUM(kz.walBruttoKon) AS walBruttoKon, 
	   SUM(kz.walNettoZal) AS walNettoZal, 
	   SUM(kz.walBruttoZal) AS walBruttoZal,
	   max(ISNULL(TW.nazwa,'')) as nazwa, 
	   max(ISNULL(TW.sww,'')) as sww, 
	   min(kz.odwrotneObc) as odwrotneObc, 
	   min(kz.JPK_V7) as JPK_V7,
	   MIN(tp.NewPosCorr) AS NewPosCorr
FROM @tblPoz as tp
JOIN(
	 SELECT min(DP.lp) as lp , 
			min(DP.flag) as flag, 
			min(ISNULL(DP.idtw,0)) as idtw, 
			max(DP.kod) as kod,
			max(DP.opis) as opis,
			min(ISNULL(DP.stvat,0)) as stvat,
			sum(DP.wartNetto) as nettoKon, sum(DP.wartVAT) as vatKon, 0.0 as nettoZal, 0.0 as vatZal,
			SUM(DP.walNetto) AS walNettoKon, SUM(DP.walBrutto) AS walBruttoKon, 0.0 AS walNettoZal, 0.0 AS walBruttoZal,
			min(DP.odwrotneObc) as odwrotneObc, min(DP.JPK_V7) as JPK_V7
	FROM HM.DP
	WHERE DP.superkoryg = @id and (DP.flag & 0x4000)=0
	GROUP BY DP.lp
UNION
	SELECT min(DP.lp) as lp, 
			min(DP.flag) as flag,
			min(ISNULL(DP.idtw,0)) as idtw, 
			max(DP.kod) as kod,
			max(DP.opis) as opis,
			min(ISNULL(DP.stvat,0)) as stvat,
			0.0 as nettoKon, 0.0 as vatKon, sum(DP.wartnetto) as nettoZal, sum(DP.wartVAT)  as vatZal,
			0.0 AS walNettoKon, 0.0 AS walBruttoKon, SUM(DP.walNetto) AS walNettoZal, SUM(DP.walBrutto) AS walBruttoZal,
			min(DP.odwrotneObc) as odwrotneObc, min(DP.JPK_V7) as JPK_V7
	FROM HM.DP
	JOIN hm.ZZ ON ZZ.typ = 152 AND ZZ.baza1 = 16 AND ZZ.id1 = @id AND ZZ.subtyp IN (136, 137, 152, 153)
	WHERE superkoryg = ZZ.id2
	GROUP BY DP.lp
 ) kz ON tp.lp=kz.lp
LEFT JOIN hm.TW ON TW.id = kz.idtw
GROUP BY tp.lp
 )
Select
	   fn.lp, 
	   fn.flag, 
	   fn.idtw, 
	   xx.kod,
	   xx.opis,
	   fn.stvat,
	   fn.nettoKon, 
	   fn.vatKon,  
	   fn.nettoZal, 
	   fn.vatZal,
	   fn.walNettoKon, 
	   fn.walBruttoKon, 
	   fn.walNettoZal, 
	   fn.walBruttoZal,
	   fn.nazwa, 
	   fn.sww,
	   fn.odwrotneObc, 
	   fn.JPK_V7,fn.NewPosCorr
FROM FN fn
JOIN ( Select x.kod, x.opis, x.lp
		From (SELECT Row_Number() over(partition by lp order by lp desc, id desc)as  row_number, * FROM HM.DP where superkoryg = @id ) x
		Where row_number = 1 ) xx on xx.lp = fn.lp