IF NOT EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'RESTYPGRP'
          AND Object_ID = Object_ID(N'LD0P0N900'))
BEGIN
ALTER TABLE LD0P0N900 ADD 
	RESTYPGRP  DECIMAL(9)
	CONSTRAINT LD0D0N900_RESTYPGRP DEFAULT 0 NOT NULL
END
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0V0N900')
  BEGIN
    DROP VIEW LD0V0N900
  END
GO

CREATE VIEW LD0V0N900
AS 
SELECT * FROM LD0P0N900 
GO

GRANT ALL ON LD0V0N900 TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VYN900')
  BEGIN
    DROP VIEW LD0VYN900
  END
GO

CREATE VIEW LD0VYN900
AS 
SELECT * FROM LD0P0N900 
GO

GRANT ALL ON LD0VYN900 TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAA0ASSIGNMENTIFO')
BEGIN
DROP VIEW LD0VAA0ASSIGNMENTIFO 
END
GO

CREATE VIEW LD0VAA0ASSIGNMENTIFO AS
 SELECT N900.*
,A.NAMN as PERSNAMN
,A.KUNDNUMMER as PERSONKEY
,E.DATUM as BESLUTDATUM
,I1.NAMN as UTFHAND_T
,I2.NAMN as UTFMEDHAND_T
,I3.NAMN as UTFMEDHAND2_T
,I4.NAMN as MOTHAND_T
,J01.NAMN as ORGNAMN_T
,Q01_1.KLARTEXT as STATUS_T
,Q01_2.KLARTEXT BESLUT_T
,Q01_3.KLARTEXT RESTYPGRP_T
,E.BESFATT as BESFATT
,IE.NAMN as BESFATT_T
,C.HAND AS INV_CASEWORKER
,C.MEDHAND AS INV_COCASEWORKER
,VI_INV1.NAMN AS INV_CASEWORKERNAME_T
,VI_INV2.NAMN AS INV_COCASEWORKERNAME_T
,C20.HAND AS SERV_CASEWORKER
,C20.MEDHAND AS SERV_COCASEWORKER
,VI_SERV1.NAMN AS SERV_CASEWORKERNAME_T
,VI_SERV2.NAMN AS SERV_COCASEWORKERNAME_T
FROM LD0V0N900 N900
JOIN LD0V0A A ON A.PNR = N900.PNR
JOIN LD0VYE E ON E.BESLUTNR = N900.BESLUTNR 
LEFT OUTER JOIN LD0V0I AS I1 ON I1.HAND = N900.UTFHAND
LEFT OUTER JOIN LD0V0I AS I2 ON I2.HAND = N900.UTFMEDHAND
LEFT OUTER JOIN LD0V0I AS I3 ON I3.HAND = N900.UTFMEDHAND2
LEFT OUTER JOIN LD0V0I AS I4 ON I4.HAND = N900.MOTHAND
LEFT OUTER JOIN LD0V0J01 AS J01 ON J01.ORGID = N900.ORGID AND J01.ORGENHNR = N900.ORGENHNR
LEFT OUTER JOIN LD0V0Q01 AS Q01_1 ON Q01_1.KODTYP = 'UIFOSTATUS' AND Q01_1.KOD = N900.STATUS 
LEFT OUTER JOIN LD0V0Q01 AS Q01_2 ON Q01_2.KODTYP = 'BESLUT' AND Q01_2.KOD = E.BESLUT
LEFT OUTER JOIN LD0V0Q01 AS Q01_3 ON Q01_3.KODTYP = 'RESTYPGRP' AND Q01_3.KOD = N900.RESTYPGRP
LEFT OUTER JOIN LD0V0I AS IE ON IE.HAND = E.BESFATT
LEFT OUTER JOIN LD0VYC AS C ON C.AEVERKNR = E.AEVERKNR
LEFT OUTER JOIN LD0V0I AS VI_INV1 ON VI_INV1.HAND = C.HAND
LEFT OUTER JOIN LD0V0I AS VI_INV2 ON VI_INV2.HAND = C.MEDHAND
LEFT OUTER JOIN LD0VYC20 AS C20 ON C20.INSNR = E.INSNR
LEFT OUTER JOIN LD0V0I AS VI_SERV1 ON VI_SERV1.HAND = C20.HAND
LEFT OUTER JOIN LD0V0I AS VI_SERV2 ON VI_SERV2.HAND = C20.MEDHAND
GO

GRANT ALL ON LD0VAA0ASSIGNMENTIFO TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAAYASSIGNMENTIFO')
BEGIN
DROP VIEW LD0VAAYASSIGNMENTIFO 
END
GO

CREATE VIEW LD0VAAYASSIGNMENTIFO AS
 SELECT N900.*
,A.NAMN as PERSNAMN
,A.KUNDNUMMER as PERSONKEY
,E.DATUM as BESLUTDATUM
,I1.NAMN as UTFHAND_T
,I2.NAMN as UTFMEDHAND_T
,I3.NAMN as UTFMEDHAND2_T
,I4.NAMN as MOTHAND_T
,J01.NAMN as ORGNAMN_T
,Q01_1.KLARTEXT as STATUS_T
,Q01_2.KLARTEXT BESLUT_T
,Q01_3.KLARTEXT RESTYPGRP_T
,E.BESFATT as BESFATT
,IE.NAMN as BESFATT_T
,C.HAND AS INV_CASEWORKER
,C.MEDHAND AS INV_COCASEWORKER
,VI_INV1.NAMN AS INV_CASEWORKERNAME_T
,VI_INV2.NAMN AS INV_COCASEWORKERNAME_T
,C20.HAND AS SERV_CASEWORKER
,C20.MEDHAND AS SERV_COCASEWORKER
,VI_SERV1.NAMN AS SERV_CASEWORKERNAME_T
,VI_SERV2.NAMN AS SERV_COCASEWORKERNAME_T
FROM LD0VYN900 N900
LEFT OUTER JOIN LD0V0A A ON A.PNR = N900.PNR
JOIN LD0VYE E ON E.BESLUTNR = N900.BESLUTNR 
LEFT OUTER JOIN LD0V0I AS I1 ON I1.HAND = N900.UTFHAND
LEFT OUTER JOIN LD0V0I AS I2 ON I2.HAND = N900.UTFMEDHAND
LEFT OUTER JOIN LD0V0I AS I3 ON I3.HAND = N900.UTFMEDHAND2
LEFT OUTER JOIN LD0V0I AS I4 ON I4.HAND = N900.MOTHAND
LEFT OUTER JOIN LD0V0J01 AS J01 ON J01.ORGID = N900.ORGID AND J01.ORGENHNR = N900.ORGENHNR
LEFT OUTER JOIN LD0V0Q01 AS Q01_1 ON Q01_1.KODTYP = 'UIFOSTATUS' AND Q01_1.KOD = N900.STATUS 
LEFT OUTER JOIN LD0V0Q01 AS Q01_2 ON Q01_2.KODTYP = 'BESLUT' AND Q01_2.KOD = E.BESLUT
LEFT OUTER JOIN LD0V0Q01 AS Q01_3 ON Q01_3.KODTYP = 'RESTYPGRP' AND Q01_3.KOD = N900.RESTYPGRP
LEFT OUTER JOIN LD0V0I AS IE ON IE.HAND = E.BESFATT
LEFT OUTER JOIN LD0VYC AS C ON C.AEVERKNR = E.AEVERKNR
LEFT OUTER JOIN LD0V0I AS VI_INV1 ON VI_INV1.HAND = C.HAND
LEFT OUTER JOIN LD0V0I AS VI_INV2 ON VI_INV2.HAND = C.MEDHAND
LEFT OUTER JOIN LD0VYC20 AS C20 ON C20.INSNR = E.INSNR
LEFT OUTER JOIN LD0V0I AS VI_SERV1 ON VI_SERV1.HAND = C20.HAND
LEFT OUTER JOIN LD0V0I AS VI_SERV2 ON VI_SERV2.HAND = C20.MEDHAND
GO

GRANT ALL ON LD0VAAYASSIGNMENTIFO TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAA0SEARCHASSIGNMENTIFO')
BEGIN
DROP VIEW LD0VAA0SEARCHASSIGNMENTIFO 
END
GO

CREATE VIEW LD0VAA0SEARCHASSIGNMENTIFO
AS SELECT 
N900.*
,A.NAMN as PERSNAMN
,A.KUNDNUMMER as PERSONKEY
,A.ADRESS as ADRESS
,A.COADR as COADR
,A.POSTNR as POSTNR
,A.ORT as ORT
,A.FODDAT as FODDAT
,A.KON as KON
,I1.NAMN as UTFHAND_T
,I2.NAMN as UTFMEDHAND_T
,I3.NAMN as UTFMEDHAND2_T
,I4.NAMN as MOTHAND_T
,J01.NAMN as ORGNAMN_T
,Q01_1.KLARTEXT as STATUS_T
,Q01_3.KLARTEXT RESTYPGRP_T
,C.ORGID AS INV_ORGANIZATIONID
,C.ORGENHNR AS INV_ORGANIZATIONUNITID
,C.HAND AS INV_CASEWORKER
,C.MEDHAND AS INV_COCASEWORKER
,J01_INV.NAMN AS INV_ORGNAMN_T
,VI_INV1.NAMN AS INV_CASEWORKERNAME_T
,VI_INV2.NAMN AS INV_COCASEWORKERNAME_T
,C20.ORGID AS SERV_ORGANIZATIONID
,C20.ORGENHNR AS SERV_ORGANIZATIONUNITID
,C20.HAND AS SERV_CASEWORKER
,C20.MEDHAND AS SERV_COCASEWORKER
,J01_SERV.NAMN AS SERV_ORGNAMN_T
,VI_SERV1.NAMN AS SERV_CASEWORKERNAME_T
,VI_SERV2.NAMN AS SERV_COCASEWORKERNAME_T
FROM LD0V0N900 N900
INNER JOIN LD0VYE E ON E.BESLUTNR = N900.BESLUTNR
LEFT OUTER JOIN LD0V0A A ON A.PNR = N900.PNR
LEFT OUTER JOIN LD0V0I AS I1 ON I1.HAND = N900.UTFHAND
LEFT OUTER JOIN LD0V0I AS I2 ON I2.HAND = N900.UTFMEDHAND
LEFT OUTER JOIN LD0V0I AS I3 ON I3.HAND = N900.UTFMEDHAND2
LEFT OUTER JOIN LD0V0I AS I4 ON I4.HAND = N900.MOTHAND
LEFT OUTER JOIN LD0V0J01 AS J01 ON J01.ORGID = N900.ORGID AND J01.ORGENHNR = N900.ORGENHNR
LEFT OUTER JOIN LD0V0Q01 AS Q01_1 ON Q01_1.KODTYP = 'UIFOSTATUS' AND Q01_1.KOD = N900.STATUS
LEFT OUTER JOIN LD0V0Q01 AS Q01_3 ON Q01_3.KODTYP = 'RESTYPGRP' AND Q01_3.KOD = N900.RESTYPGRP
LEFT OUTER JOIN LD0VYC AS C ON C.AEVERKNR = E.AEVERKNR
LEFT OUTER JOIN LD0V0J01 AS J01_INV ON J01_INV.ORGENHNR = C.ORGENHNR AND J01_INV.ORGID = C.ORGID
LEFT OUTER JOIN LD0V0I AS VI_INV1 ON VI_INV1.HAND = C.HAND
LEFT OUTER JOIN LD0V0I AS VI_INV2 ON VI_INV2.HAND = C.MEDHAND
LEFT OUTER JOIN LD0VYC20 AS C20 ON C20.INSNR = E.INSNR
LEFT OUTER JOIN LD0P0J01 AS J01_SERV ON J01_SERV.ORGENHNR = C20.ORGENHNR AND J01_SERV.ORGID = C20.ORGID
LEFT OUTER JOIN LD0V0I AS VI_SERV1 ON VI_SERV1.HAND = C20.HAND
LEFT OUTER JOIN LD0V0I AS VI_SERV2 ON VI_SERV2.HAND = C20.MEDHAND
GO

GRANT ALL ON LD0VAA0SEARCHASSIGNMENTIFO TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAA0SearchAssignmentIfoProvider')
BEGIN
DROP VIEW LD0VAA0SearchAssignmentIfoProvider
END
GO

CREATE VIEW LD0VAA0SearchAssignmentIfoProvider
AS SELECT DISTINCT
N900.*
,A.NAMN as PERSNAMN
,A.KUNDNUMMER as PERSONKEY
,A.ADRESS as ADRESS
,A.COADR as COADR
,A.POSTNR as POSTNR
,A.ORT as ORT
,A.FODDAT as FODDAT
,A.KON as KON
,I1.NAMN as UTFHAND_T
,I2.NAMN as UTFMEDHAND_T
,I3.NAMN as UTFMEDHAND2_T
,I4.NAMN as MOTHAND_T
,J01.NAMN as ORGNAMN_T
,Q01_1.KLARTEXT as STATUS_T
,C.ORGID AS INV_ORGANIZATIONID
,C.ORGENHNR AS INV_ORGANIZATIONUNITID
,C.HAND AS INV_CASEWORKER
,C.MEDHAND AS INV_COCASEWORKER
,J01_INV.NAMN AS INV_ORGNAMN_T
,VI_INV1.NAMN AS INV_CASEWORKERNAME_T
,VI_INV2.NAMN AS INV_COCASEWORKERNAME_T
,C20.ORGID AS SERV_ORGANIZATIONID
,C20.ORGENHNR AS SERV_ORGANIZATIONUNITID
,C20.HAND AS SERV_CASEWORKER
,C20.MEDHAND AS SERV_COCASEWORKER
,J01_SERV.NAMN AS SERV_ORGNAMN_T
,VI_SERV1.NAMN AS SERV_CASEWORKERNAME_T
,VI_SERV2.NAMN AS SERV_COCASEWORKERNAME_T
,CASE WHEN N901.DELUPPDRAGNR IS NULL THEN '0' ELSE '1' END DELUPPDRAG_FINNS
FROM LD0V0N900 N900
LEFT OUTER JOIN LD0V0N901 N901
ON N900.UPPDRAGIFONR=N901.UPPDRAGIFONR
INNER JOIN LD0VYE E ON E.BESLUTNR = N900.BESLUTNR
LEFT OUTER JOIN LD0V0A A ON A.PNR = N900.PNR
LEFT OUTER JOIN LD0V0I AS I1 ON I1.HAND = N900.UTFHAND
LEFT OUTER JOIN LD0V0I AS I2 ON I2.HAND = N900.UTFMEDHAND
LEFT OUTER JOIN LD0V0I AS I3 ON I3.HAND = N900.UTFMEDHAND2
LEFT OUTER JOIN LD0V0I AS I4 ON I4.HAND = N900.MOTHAND
LEFT OUTER JOIN LD0V0J01 AS J01 ON J01.ORGID = N900.ORGID AND J01.ORGENHNR = N900.ORGENHNR
LEFT OUTER JOIN LD0V0Q01 AS Q01_1 ON Q01_1.KODTYP = 'UIFOSTATUS' AND Q01_1.KOD = N900.STATUS
LEFT OUTER JOIN LD0VYC AS C ON C.AEVERKNR = E.AEVERKNR
LEFT OUTER JOIN LD0V0J01 AS J01_INV ON J01_INV.ORGENHNR = C.ORGENHNR AND J01_INV.ORGID = C.ORGID
LEFT OUTER JOIN LD0V0I AS VI_INV1 ON VI_INV1.HAND = C.HAND
LEFT OUTER JOIN LD0V0I AS VI_INV2 ON VI_INV2.HAND = C.MEDHAND
LEFT OUTER JOIN LD0VYC20 AS C20 ON C20.INSNR = E.INSNR
LEFT OUTER JOIN LD0P0J01 AS J01_SERV ON J01_SERV.ORGENHNR = C20.ORGENHNR AND J01_SERV.ORGID = C20.ORGID
LEFT OUTER JOIN LD0V0I AS VI_SERV1 ON VI_SERV1.HAND = C20.HAND
LEFT OUTER JOIN LD0V0I AS VI_SERV2 ON VI_SERV2.HAND = C20.MEDHAND
GO

GRANT ALL ON LD0VAA0SearchAssignmentIfoProvider TO LD0ROANV
GO

IF EXISTS (SELECT 1 FROM SYS.VIEWS WHERE NAME='LD0VAA0SearchNotes')
BEGIN
DROP VIEW LD0VAA0SearchNotes 
END
GO

CREATE VIEW LD0VAA0SearchNotes AS 
SELECT T41.*,
CASE
                             WHEN T41.AGARTYP = 68 THEN UPPDRAGIFO.ORGID
                             WHEN T41.AGARTYP = 69 THEN UPPDRAGLM.MOTORGID
                             WHEN T41.AGARTYP = 70 THEN INSKRIVNING.ORGID
                             ELSE 0
END AS ORGID,
CASE
                             WHEN T41.AGARTYP = 68 THEN UPPDRAGIFO.ORGENHNR
                             WHEN T41.AGARTYP = 69 THEN UPPDRAGLM.MOTORGENHNR
                             WHEN T41.AGARTYP = 70 THEN INSKRIVNING.ORGENHNR
                             ELSE ''
END AS ORGENHNR,
CASE
                             WHEN T41.AGARTYP = 68 THEN UPPDRAGIFO.ORGNAMN_T
                             WHEN T41.AGARTYP = 69 THEN UPPDRAGLM.MOTORGNAMN_T
                             WHEN T41.AGARTYP = 70 THEN INSKRIVNING.ORGENH_T
                             ELSE ''
END AS ORGNAMN_T,
CASE
                             WHEN T41.AGARTYP = 68 THEN 'Uppdrag IFO'
                             WHEN T41.AGARTYP = 69 THEN 'Uppdrag AM'
                             WHEN T41.AGARTYP = 70 THEN 'Inskrivning'
                             ELSE ''
END AS KOMPONENT_T,

A.NAMN as PERSONNAMN,
A.KUNDNUMMER as KUNDNUMMER,
Q01.KLARTEXT as ANTECKNTYP_T
FROM LD0V1T41 T41
JOIN LD0V0A A ON A.PNR = T41.PERSONNR
JOIN LD0V0Q01 Q01 ON Q01.KOD = T41.ANTECKNTYP AND Q01.KODTYP = 'ANTECKNTYP'
LEFT JOIN LD0VAA0ASSIGNMENTIFO AS UPPDRAGIFO ON T41.AGARNR = UPPDRAGIFO.UPPDRAGIFONR AND T41.AGARTYP = 68
LEFT JOIN LD0VAA0LMASSIGNMENT AS UPPDRAGLM ON T41.AGARNR = UPPDRAGLM.LNUPPDRAGNR AND T41.AGARTYP = 69
LEFT JOIN LD0VAA0LMENROLLMENT AS INSKRIVNING ON T41.AGARNR = INSKRIVNING.INSKRIVNINGSNR AND T41.AGARTYP = 70
WHERE ANTECKNTYP > 0
GO

GRANT ALL ON LD0VAA0SearchNotes TO LD0ROANV
GO
