DROP VIEW LD0V0CCOMMITMENTS
;
CREATE VIEW LD0V0CCOMMITMENTS
AS 
  SELECT G.REKVNR COMMITMENTID, 
         G.DATUM COMMITMENTDATE, 
         CONVERT(VARCHAR, G.REKVAR)+ ' '+ CONVERT(VARCHAR, G.REKVARLNR)+ ' '+CONVERT(VARCHAR, G.REKVHOS) AS HEADLINE,
		 G.UPDSIGN, 
		 G.UPDDAT, 
		CASE 
		  WHEN SERV.AKTPNR IS NOT NULL THEN 23 
		  WHEN INV.AKTPNR IS NOT NULL THEN 21 
		  ELSE 20 
		END AS CONNECTION,
		ISNULL(SERV.AKTPNR, ISNULL(INV.AKTPNR, APP.PNR)) PERSONID, 
		ISNULL(A2.NAMN, ISNULL(A1.NAMN, A3.NAMN)) PERSONNAME,
		Q.KLARTEXT SERVICECATEGORY,
		Q1.KLARTEXT COMMITMENTTYPE
FROM   LD0V0G G 
       
       LEFT JOIN LD0V0C20 SERV ON SERV.INSNR = G.INSNR 
       LEFT JOIN LD0V0C INV ON INV.AEVERKNR = G.AEVERKNR 
       LEFT JOIN LD0V0H APP ON APP.BESLUTNR = G.REKVNR 
       LEFT JOIN LD0V0Q01 Q ON Q.KODTYP = 'AKTTYP' AND Q.KOD = INV.AEVERKTYP
       LEFT JOIN LD0V0A A1 ON INV.AKTPNR = A1.PNR 
       LEFT JOIN LD0V0A A2 ON SERV.AKTPNR = A2.PNR 
       LEFT JOIN LD0V0A A3 ON APP.PNR = A3.PNR 
       LEFT JOIN LD0V0Q01 Q1 ON Q1.KODTYP = 'REKVTYP' AND Q1.KOD = G.REKVTYP
;