IF EXISTS (SELECT 1 FROM SYS.TABLES WHERE NAME='LD0P0DECISIONEHRDOCUMENT')
BEGIN
DROP TABLE LD0P0DECISIONEHRDOCUMENT
END
;
IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0DECISIONEHRDOCUMENT')
BEGIN
DROP VIEW LD0V0DECISIONEHRDOCUMENT
END
;
IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0CINVESTIGATIONS')
BEGIN
DROP VIEW LD0V0CINVESTIGATIONS
END
;
CREATE TABLE LD0P0DECISIONEHRDOCUMENT 
(
		ID INT IDENTITY(1,1) NOT NULL,
		DECISIONEHRDOCUMENTTYPEMAPPINGID INT NOT NULL,
		EHRWELFAREDOCUMENTID INT NOT NULL)
;
CREATE VIEW LD0V0DECISIONEHRDOCUMENT AS 
	SELECT * FROM LD0P0DECISIONEHRDOCUMENT
;

DROP INDEX LD0C0DECISIONEHRDOCUMENT ON LD0P0DECISIONEHRDOCUMENT
;
CREATE UNIQUE CLUSTERED INDEX LD0C0DECISIONEHRDOCUMENT ON LD0P0DECISIONEHRDOCUMENT
(
ID
)
;

CREATE  VIEW LD0V0CINVESTIGATIONS
AS 
SELECT						
C.AEVERKNR,
C.STARTDAT,
C.STOPDAT,
C.AKTPNR,
C.ORGID,
J01.NAMN ORGNAMN,
C.TMARK, 
H.HAND, 
H.NAMN, 
H.TELE1,
C.AEVERKTYP, 
Q.KLARTEXT AEVERKTYP_T, 
Q1.KLARTEXT  AKTI
FROM	
LD0V0C C 
INNER JOIN LD0V0B B 
ON C.AKTNR = B.AKTNR 
INNER JOIN LD0V0Q01 Q 
ON Q.KODTYP = 'UTREDTYP' 
AND Q.KOD = C.AEVERKTYP 
AND Q.XNUM20 = 0	
INNER JOIN LD0V0I H 
ON C.HAND = H.HAND 
INNER JOIN LD0V0Q01 Q1 
ON Q1.KODTYP ='AKTTYP' AND Q1.KOD = B.TYPNR
LEFT JOIN 
 LD0V0J01 J01 ON J01.ORGID=C.ORGID AND J01.ORGENHNR=C.ORGENHNR 
;