IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VLONGOINGSERVCAT')
BEGIN
DROP VIEW LD0VLONGOINGSERVCAT
END
;
CREATE VIEW LD0VLONGOINGSERVCAT AS
SELECT H.AKTUALNR, H.PNR, ISNULL(SW.ID, 0) AS WELFAREID, 
KANTA.ORG_OID AS SERVCATCODE, H.CASEID FROM LD0P0H H
JOIN ld0v0q01 Q01 ON H.AKTTYP = Q01.KOD AND KODTYP = 'AKTTYP' AND Q01.XTXT1 <> '' AND H.BESLUTNR = 0 AND H.AEVERKNR <> 0
JOIN LD0P0SOCIALWELFARECUSTSHIP SW ON SW.PNR = H.PNR
JOIN LD0V0KANTA KANTA ON KANTA.OID = Q01.XTXT1
JOIN LD0P0C C ON C.AEVERKNR = H.AEVERKNR AND C.STOPDAT = '99999999'
UNION ALL
SELECT H.AKTUALNR, H.PNR, ISNULL(SW.ID, 0) AS WELFAREID, 
KANTA.ORG_OID AS SERVCATCODE, H.CASEID FROM LD0P0H H
JOIN ld0v0q01 Q01 ON H.AKTTYP = Q01.KOD AND KODTYP = 'AKTTYP' AND Q01.XTXT1 <> '' AND H.BESLUTNR = 0 AND H.INSNR <> 0
JOIN LD0P0SOCIALWELFARECUSTSHIP SW ON SW.PNR = H.PNR
JOIN LD0V0KANTA KANTA ON KANTA.OID = Q01.XTXT1
JOIN LD0P0C20 C20 ON C20.INSNR = H.INSNR AND C20.TOMDAT = '99999999'
UNION ALL
SELECT H.AKTUALNR, H.PNR, ISNULL(SW.ID, 0) AS WELFAREID, 
KANTA.ORG_OID AS SERVCATCODE, H.CASEID FROM LD0P0H H
JOIN ld0v0q01 Q01 ON H.AKTTYP = Q01.KOD AND KODTYP = 'AKTTYP' AND Q01.XTXT1 <> '' AND H.BESLUTNR = 0 AND H.AEVERKNR = 0 AND H.INSNR = 0
JOIN LD0P0SOCIALWELFARECUSTSHIP SW ON SW.PNR = H.PNR
JOIN LD0V0KANTA KANTA ON KANTA.OID = Q01.XTXT1
;