DROP VIEW LD0VAA0UnreadEAApplicationItems
;
CREATE VIEW LD0VAA0UnreadEAApplicationItems AS 
SELECT
App.AKTUALNR,
App.ADATUM,
App.PNR,
Inv.AKTPNR AS 'AKTPNR_Investigation',
InvPerson.PNR AS 'PNR_InvestigationPerson',
Serv.AKTPNR AS 'AKTPNR_Service',
ServPerson.PNR AS 'PNR_Service',
Calculation.BERAKNR,
Decision.BESLUTNR,
Payment.BETALNR,
C11_Decision.HTYP,
C11_Decision.HTEXT1 as a,
C11_Payment.HTEXT1 as b,
C11_Calculation.HTEXT1 as c
FROM
LD0VYH AS App
INNER JOIN LD0V0Q01 AS AppType ON App.AKTUAL = AppType.KOD
	AND AppType.KODTYP = 'AKTUAL'
	AND AppType.XNUM24 = 1
LEFT JOIN LD0VYC AS Inv ON Inv.AEVERKNR = App.AEVERKNR
LEFT JOIN LD0VAA0InvestigationPerson AS InvPerson ON InvPerson.AEVERKNR = App.AEVERKNR
	AND InvPerson.MEDSOKANDE = 1
LEFT JOIN LD0VYC20 AS Serv ON Serv.INSNR = App.INSNR
LEFT JOIN LD0VAA0ServicePerson AS ServPerson ON ServPerson.INSNR = App.INSNR
	AND ServPerson.MEDSOKANDE = 1
LEFT JOIN LD0VYE AS Decision ON Decision.AKTUALNR = App.AKTUALNR
	AND Decision.[STATUS] = 0
LEFT JOIN LD0VYC11 C11_Decision ON C11_Decision.HAGNR = Decision.BESLUTNR
	AND C11_Decision.HTYP IN (120, 125)
LEFT JOIN LD0VAA0Payment AS Payment ON Payment.AKTUALNR = App.AKTUALNR
	AND Payment.VISASEJ_PA_MINSIDA <> 1
LEFT JOIN LD0VYC11 C11_Payment ON C11_Payment.HAGNR = Payment.BETALNR
	AND C11_Payment.HTYP = 160
LEFT JOIN LD0VYD AS Calculation ON Calculation.AKTUALNR = App.AKTUALNR
	AND Calculation.SLUTLIG = 1
LEFT JOIN LD0VYC11 C11_Calculation ON C11_Calculation.HAGNR = Calculation.BERAKNR
	AND C11_Calculation.HTYP = 110
WHERE
(Calculation.BERAKNR IS NOT NULL OR Decision.BESLUTNR IS NOT NULL OR Payment.BETALNR IS NOT NULL)
AND ((C11_Decision.HTEXT1 IS NULL OR C11_Decision.HTEXT1 NOT LIKE '%Läst av klienten%') 
	AND (C11_Payment.HTEXT1 IS NULL OR C11_Payment.HTEXT1 NOT LIKE '%Läst av klienten%')
	AND (C11_Calculation.HTEXT1 IS NULL OR C11_Calculation.HTEXT1 NOT LIKE '%Läst av klienten%'))
;