SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

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

  create table #Documents (
    DECISIONDATE varchar(8), -- Date
    DECISIONSTATUS decimal(1,0), -- Status
    DECISIONTYPEID decimal(9,0), -- DecisionTypeId
    DECISIONID decimal(9,0), -- DecisionId
    DECISIONMAKERID varchar(10), -- DecisionMakerId
    CASEWORKERID varchar(10), -- CaseworkerId
    COCASEWORKERID varchar(10), -- CoCaseworkerId
    SERVICENUMBER decimal(9,0), -- ServiceId
    INVESTIGATIONNUMBER decimal(9,0), -- InvestigationId
    STOPDAT varchar(8), -- InvestigationStopDate
    DOSSIERTYPECODE decimal(9,0), -- DossierTypeCode
    DECISIONLEADSTOSERVICE decimal(9,0), -- DecisionLeadsToService
    DECISIONNAME varchar(180), -- DecisionName
    PERSONID varchar(12), -- PersonId
    PERSONNAME varchar(180), -- PersonName
    PERSONKEY decimal(9,0), -- PersonKey
    UPDSIGN varchar(10), -- UpdateSignature
    UPDDAT varchar(6), -- UpdateTimestamp
    REFDECISIONID decimal(9,0), -- RefDecisionId
    ORGANIZATIONNAME varchar(250), -- OrganizationName
    ORGANIZATIONID varchar(10) -- OrganizationId
  );

  -- 1.UnfinishedDecisionWithServiceInExecution
  insert into #Documents
  SELECT TOP (case @Take when 0 then 2147483647 else @Take end)
    DECISIONDATE,DECISIONSTATUS,DECISIONTYPEID,DECISIONID,DECISIONMAKERID,CASEWORKERID,COCASEWORKERID,
    SERVICENUMBER,INVESTIGATIONNUMBER,STOPDAT,DOSSIERTYPECODE,DECISIONLEADSTOSERVICE,DECISIONNAME,
    PERSONID,PERSONNAME,PERSONKEY,UPDSIGN,UPDDAT,REFDECISIONID,ORGANIZATIONNAME,ORGANIZATIONID
  FROM LD0V0CUNFINISHEDDECISIONS_WITH_SERVICE_IN_EXE
  WHERE PERSONID = @PERSONID
  ORDER BY DECISIONDATE DESC;

  -- 2.UnfinishedDecisionWithServiceNotInExecution
  insert into #Documents
  SELECT TOP (case @Take when 0 then 2147483647 else @Take end)
    DECISIONDATE,DECISIONSTATUS,DECISIONTYPEID,DECISIONID,DECISIONMAKERID,CASEWORKERID,COCASEWORKERID,
    SERVICENUMBER,INVESTIGATIONNUMBER,STOPDAT,DOSSIERTYPECODE,DECISIONLEADSTOSERVICE,DECISIONNAME,
    PERSONID,PERSONNAME,PERSONKEY,UPDSIGN,UPDDAT,REFDECISIONID,ORGANIZATIONNAME,ORGANIZATIONID
  FROM LD0V0CUNFINISHEDDECISIONS_WITH_SERVICE_NOTIN_EXE
  WHERE PERSONID = @PERSONID
  ORDER BY DECISIONDATE DESC;

  -- 3.UnfinishedDecisionWithoutService
  insert into #Documents
  SELECT TOP (case @Take when 0 then 2147483647 else @Take end)
    DECISIONDATE,DECISIONSTATUS,DECISIONTYPEID,DECISIONID,DECISIONMAKERID,CASEWORKERID,COCASEWORKERID,
    SERVICENUMBER,INVESTIGATIONNUMBER,STOPDAT,DOSSIERTYPECODE,DECISIONLEADSTOSERVICE,DECISIONNAME,
    PERSONID,PERSONNAME,PERSONKEY,UPDSIGN,UPDDAT,REFDECISIONID,ORGANIZATIONNAME,ORGANIZATIONID
  FROM LD0V0CUNFINISHEDDECISIONS_WITHOUT_SERVICE
  WHERE PERSONID = @PERSONID
  ORDER BY DECISIONDATE DESC;

  select
    DECISIONDATE,DECISIONSTATUS,DECISIONTYPEID,DECISIONID,DECISIONMAKERID,CASEWORKERID,COCASEWORKERID,
    SERVICENUMBER,INVESTIGATIONNUMBER,STOPDAT,DOSSIERTYPECODE,DECISIONLEADSTOSERVICE,DECISIONNAME,
    PERSONID,PERSONNAME,PERSONKEY,UPDSIGN,UPDDAT,REFDECISIONID,ORGANIZATIONNAME,ORGANIZATIONID
  from #Documents;

  drop table #Documents;
END
GO

GRANT ALL ON LD0SPListDecisionsForClient TO LD0ROANV
GO