IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0ApiCalculation')
BEGIN
DROP VIEW LD0ApiCalculation
END
GO
CREATE VIEW LD0ApiCalculation
AS
SELECT 
D.*,
D.STARTTIME,
D.ENDTIME,
D10.NORM,
CASE WHEN AEVERKNR>0 THEN
	(SELECT  DISTINCT KUNDNUMMER FROM LD0P0A A 
		WHERE EXISTS (SELECT * FROM LD0V0CT C WHERE C.AKTPNR=A.PNR 
			AND C.AEVERKNR=D.AEVERKNR))
	 WHEN INSNR>0 THEN
		(SELECT DISTINCT KUNDNUMMER FROM LD0P0A A 
		WHERE EXISTS (SELECT * FROM LD0V0C20T C20 WHERE C20.AKTPNR=A.PNR 
			AND C20.INSNR=D.INSNR))
	 END AS KUNDNUMMER
		,
CASE WHEN AEVERKNR>0 THEN
	(SELECT DISTINCT PNR FROM LD0P0A A 
		WHERE EXISTS (SELECT * FROM LD0V0CT C WHERE C.AKTPNR=A.PNR 
			AND C.AEVERKNR=D.AEVERKNR))
	 WHEN INSNR>0 THEN
		(SELECT DISTINCT PNR FROM LD0P0A A 
		WHERE EXISTS (SELECT * FROM LD0V0C20T C20 WHERE C20.AKTPNR=A.PNR 
			AND C20.INSNR=D.INSNR))
	 END AS PNR
FROM LD0P0D D
LEFT OUTER JOIN LD0P0D10 D10 
ON D.NORMNR = D10.NORMNR
GO
GRANT ALL ON LD0ApiCalculation TO LD0ROANV
GO
