SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE LD0SPListAttachedPdfDocumentsForClient(@PersonId varchar(12), @Take int)
AS
BEGIN
  set nocount on;

  create table #Documents (
    INFOGANR decimal(9,0), -- DocumentId
    AGARTYP decimal(9,0), -- OwnerType
    AGARNR decimal(9,0), -- OwnerId
    INFOGADATUM varchar(8), -- CreatedDate
    INFDOKTYP decimal(9,0), -- DocumentTypeCode
    DOKTYPTXT varchar(180), -- DocumentTypeText
    INFDOKTYPTXT varchar(250), -- DocumentTypeTitle
    INFANVTYP decimal(9,0), -- Sender
    INFANVTYPTEXT varchar(180), -- SenderText
    INFANVTYPTXT varchar(250), -- SenderTitle
    FILEID varchar(38), -- FileId
    PNR varchar(12), -- PersonId
    UPDDAT varchar(8), -- UpdateTimestamp
    UPDSIGN varchar(10), -- UpdateSignature
    SERVICECATEGORY varchar(180), -- ServiceCategory
    ORGANIZATIONNAME varchar(110), -- OrganizationName
    ORGANIZATIONID varchar(10) -- OrganizationId
  );

  insert into #Documents
  SELECT
    T43.INFOGANR,T43.AGARTYP,T43.AGARNR,T43.INFOGADATUM,T43.INFDOKTYP,T43.DOKTYPTXT,T43.INFDOKTYPTXT,
    T43.INFANVTYP,T43.INFANVTYPTEXT,T43.INFANVTYPTXT,T43.FILEID,PERSON.PNR,T43.UPDDAT,T43.UPDSIGN,
    Q1.KLARTEXT AS SERVICECATEGORY,ORG.NAMN AS ORGANIZATIONNAME,ORG.ORGENHNR AS ORGANIZATIONID
  FROM LD0VAA0T43 T43
    INNER JOIN LD0V0H H ON T43.AGARNR = H.AKTUALNR AND T43.AGARTYP = 7040
    LEFT OUTER JOIN LD0V0C AS C ON C.AEVERKNR = H.AEVERKNR
    LEFT OUTER JOIN LD0V0B AS B ON B.AKTNR = C.AKTNR
    LEFT OUTER JOIN LD0V0Q01 AS Q1 ON Q1.KODTYP = 'AKTTYP' AND Q1.KOD = B.TYPNR
    INNER JOIN LD0V0A PERSON ON PERSON.PNR = H.PNR
    LEFT JOIN LD0V0J01 ORG ON H.ORGENHNR = ORG.ORGENHNR
  WHERE PERSON.PNR = @PersonId;

  insert into #Documents
  SELECT
    T43.INFOGANR,T43.AGARTYP,T43.AGARNR,T43.INFOGADATUM,T43.INFDOKTYP,T43.DOKTYPTXT,T43.INFDOKTYPTXT,
    T43.INFANVTYP,T43.INFANVTYPTEXT,T43.INFANVTYPTXT,T43.FILEID,PERSON.PNR,T43.UPDDAT,T43.UPDSIGN,
    Q1.KLARTEXT AS SERVICECATEGORY,ORG.NAMN AS ORGANIZATIONNAME,ORG.ORGENHNR AS ORGANIZATIONID
  FROM LD0VAA0T43 T43
    INNER JOIN LD0VLC INV ON T43.AGARNR = INV.AEVERKNR AND T43.AGARTYP = 7017
    LEFT OUTER JOIN LD0V0C AS C ON C.AEVERKNR = INV.AEVERKNR
    LEFT OUTER JOIN LD0V0B AS B ON B.AKTNR = C.AKTNR
    LEFT OUTER JOIN LD0V0Q01 AS Q1 ON Q1.KODTYP = 'AKTTYP' AND Q1.KOD = B.TYPNR
    INNER JOIN LD0V0A PERSON ON PERSON.PNR = INV.AKTPNR
    LEFT JOIN LD0V0J01 ORG ON INV.ORGENHNR = ORG.ORGENHNR
  WHERE PERSON.PNR = @PersonId;

  insert into #Documents
  SELECT
    T43.INFOGANR,T43.AGARTYP,T43.AGARNR,T43.INFOGADATUM,T43.INFDOKTYP,T43.DOKTYPTXT,T43.INFDOKTYPTXT,
    T43.INFANVTYP,T43.INFANVTYPTEXT,T43.INFANVTYPTXT,T43.FILEID,PERSON.PNR,T43.UPDDAT,T43.UPDSIGN,
    Q1.KLARTEXT AS SERVICECATEGORY,ORG.NAMN AS ORGANIZATIONNAME,ORG.ORGENHNR AS ORGANIZATIONID
  FROM LD0VAA0T43 T43
    INNER JOIN LD0VLC20 C20 ON T43.AGARNR = C20.INSNR AND T43.AGARTYP = 7083
    INNER JOIN LD0VLC INSATS ON INSATS.AEVERKNR = C20.VERKNR AND C20.VERKNR <> 0
    LEFT JOIN LD0V0B B1 ON INSATS.AKTNR = B1.AKTNR
    LEFT JOIN LD0V0Q01 Q1 ON Q1.KODTYP = 'AKTTYP' AND Q1.KOD = B1.TYPNR
    INNER JOIN LD0V0A PERSON ON PERSON.PNR = C20.AKTPNR
    LEFT JOIN LD0V0J01 ORG ON C20.ORGENHNR = ORG.ORGENHNR
  WHERE PERSON.PNR = @PersonId;

  insert into #Documents
  SELECT
    T43.INFOGANR,T43.AGARTYP,T43.AGARNR,T43.INFOGADATUM,T43.INFDOKTYP,T43.DOKTYPTXT,T43.INFDOKTYPTXT,
    T43.INFANVTYP,T43.INFANVTYPTEXT,T43.INFANVTYPTXT,T43.FILEID,PERSON.PNR,T43.UPDDAT,T43.UPDSIGN,
    Q1.KLARTEXT AS SERVICECATEGORY,ORG.NAMN AS ORGANIZATIONNAME,ORG.ORGENHNR AS ORGANIZATIONID
  FROM LD0VAA0T43 T43
    INNER JOIN LD0VLC20 C20 ON T43.AGARNR = C20.INSNR AND T43.AGARTYP = 7083
    INNER JOIN LD0VLC INSATS ON INSATS.AEVERKNR = C20.ARENDENR AND C20.ARENDENR <> 0
    LEFT JOIN LD0V0B B1 ON INSATS.AKTNR = B1.AKTNR
    LEFT JOIN LD0V0Q01 Q1 ON Q1.KODTYP = 'AKTTYP' AND Q1.KOD = B1.TYPNR
    INNER JOIN LD0V0A PERSON ON PERSON.PNR = C20.AKTPNR
    LEFT JOIN LD0V0J01 ORG ON C20.ORGENHNR = ORG.ORGENHNR
  WHERE PERSON.PNR = @PersonId;

  insert into #Documents
  SELECT
    T43.INFOGANR,T43.AGARTYP,T43.AGARNR,T43.INFOGADATUM,T43.INFDOKTYP,T43.DOKTYPTXT,T43.INFDOKTYPTXT,
    T43.INFANVTYP,T43.INFANVTYPTEXT,T43.INFANVTYPTXT,T43.FILEID,PERSON.PNR,T43.UPDDAT,T43.UPDSIGN,
    Q1.KLARTEXT AS SERVICECATEGORY,ORG.NAMN AS ORGANIZATIONNAME,ORG.ORGENHNR AS ORGANIZATIONID
  FROM LD0VAA0T43 T43
    INNER JOIN LD0VYB01 ACT ON ACT.HAGNR = T43.AGARNR AND T43.AGARTYP = 7027
    LEFT JOIN LD0V0B B ON B.AKTNR = ACT.AKTNR
    LEFT JOIN LD0V0Q01 Q1 ON Q1.KODTYP = 'AKTTYP' AND Q1.KOD = B.TYPNR
    INNER JOIN LD0V0A PERSON ON PERSON.PNR = B.AKTPNR
    LEFT JOIN LD0V0J01 ORG ON B.ORGENHNR = ORG.ORGENHNR
  WHERE PERSON.PNR = @PersonId;

  IF @Take > 0
    select TOP (@Take)
      INFOGANR,AGARTYP,AGARNR,INFOGADATUM,INFDOKTYP,DOKTYPTXT,INFDOKTYPTXT,
      INFANVTYP,INFANVTYPTEXT,INFANVTYPTXT,FILEID,PNR,UPDDAT,UPDSIGN,
      SERVICECATEGORY,ORGANIZATIONNAME,ORGANIZATIONID
    from #Documents
    ORDER BY INFOGADATUM DESC;
  ELSE
    select
      INFOGANR,AGARTYP,AGARNR,INFOGADATUM,INFDOKTYP,DOKTYPTXT,INFDOKTYPTXT,
      INFANVTYP,INFANVTYPTEXT,INFANVTYPTXT,FILEID,PNR,UPDDAT,UPDSIGN,
      SERVICECATEGORY,ORGANIZATIONNAME,ORGANIZATIONID
    from #Documents;

  drop table #Documents;
END
GO

GRANT ALL ON LD0SPListAttachedPdfDocumentsForClient TO LD0ROANV
GO
