DROP VIEW LD0V0CPAYMENTEVENTS
;
CREATE VIEW LD0V0CPAYMENTEVENTS
AS 
SELECT * 
FROM 
(SELECT P.[BETALNR] PAYMENTID,
	   P.[INBTYP] INCOMETYPE,
	   P.[BELOPP] AMOUNT,
	   P.[BETTYP] PAYMENTTYPE,
	   P.[BOKDAT] BOOKINGDATE,
	   P.[BETDAT] PAYMENTDATE,
	   P.[STATUS] STATUS,
	   P.[VERNR] VERIFICATIONNUMBER,	   
	   P.[BETSATT] PAYMENTMETHOD,	   
	   P.[KONTONR] BANKACCOUNTNUMBER,
	   P.[NAMN] PAYEE,
	   P.[BETMED] MESSAGE,
	   P.[NOTERING] NOTICES,
	   P.[RETURBELOPP] RETURNAMOUNT,
	   P.[REFERENSNR] REFERENENCENUMBER,
	   C20.AKTPNR PERSONID,
	   C20.FOMDAT SERVICESTARTDATE,
	   Q1.KLARTEXT SERVICECATEGORY,
	   Q2.KLARTEXT PAYMENTTYPETEXT,
	   Q3.KLARTEXT PAYMENTMETHODTEXT,
	   Q4.KLARTEXT INCOMETYPETEXT,
	   G4.REKVNR REQUISITIONID,
	   0 AS ISOTHERPARTICIPANT 
FROM LD0V0F P
INNER JOIN LD0V0C20 C20 ON P.INSNR = C20.INSNR
LEFT JOIN LD0V0Q01 Q1 ON C20.INSTYP = Q1.KOD AND Q1.KODTYP = 'INSATS'
LEFT JOIN LD0V0Q01 Q2 ON P.BETTYP = Q2.KOD AND Q2.KODTYP = 'BETALTYP'
LEFT JOIN LD0V0Q01 Q3 ON P.BETSATT = Q3.KOD AND Q3.KODTYP = 'BETALSATT'
LEFT JOIN LD0V0Q01 Q4 ON p.INBTYP = Q4.KOD AND Q4.KODTYP = 'INBTYP'
LEFT JOIN LD0V0G04 G4 ON p.BETALNR = G4.BETALNR

UNION ALL

SELECT P.[BETALNR] PAYMENTID,
	   P.[INBTYP] INCOMETYPE,
	   P.[BELOPP] AMOUNT,
	   P.[BETTYP] PAYMENTTYPE,
	   P.[BOKDAT] BOOKINGDATE,
	   P.[BETDAT] PAYMENTDATE,
	   P.[STATUS] STATUS,
	   P.[VERNR] VERIFICATIONNUMBER,	   
	   P.[BETSATT] PAYMENTMETHOD,	   
	   P.[KONTONR] BANKACCOUNTNUMBER,
	   P.[NAMN] PAYEE,
	   P.[BETMED] MESSAGE,
	   P.[NOTERING] NOTICES,
	   P.[RETURBELOPP] RETURNAMOUNT,
	   P.[REFERENSNR] REFERENENCENUMBER,
	   F1.PNR PERSONID,
	   C20.FOMDAT SERVICESTARTDATE,
	   Q1.KLARTEXT SERVICECATEGORY,
	   Q2.KLARTEXT PAYMENTTYPETEXT,
	   Q3.KLARTEXT PAYMENTMETHODTEXT,
	   Q4.KLARTEXT INCOMETYPETEXT,
	   G4.REKVNR REQUISITIONID,
	   1 AS ISOTHERPARTICIPANT 
FROM LD0V0F P
INNER JOIN LD0V0F01 F1 ON P.BETALNR = F1.BETALNR
INNER JOIN LD0V0C20 C20 ON P.INSNR = C20.INSNR
LEFT JOIN LD0V0Q01 Q1 ON C20.INSTYP = Q1.KOD AND Q1.KODTYP = 'INSATS'
LEFT JOIN LD0V0Q01 Q2 ON P.BETTYP = Q2.KOD AND Q2.KODTYP = 'BETALTYP'
LEFT JOIN LD0V0Q01 Q3 ON P.BETSATT = Q3.KOD AND Q3.KODTYP = 'BETALSATT'
LEFT JOIN LD0V0Q01 Q4 ON p.INBTYP = Q4.KOD AND Q4.KODTYP = 'INBTYP'
LEFT JOIN LD0V0G04 G4 ON p.BETALNR = G4.BETALNR) AS PAYMENTS

WHERE PAYMENTS.PAYMENTTYPE<> 2
;