USP_DATALIST_PROSPECTREQUESTSBYAPPUSER

CREATE procedure dbo.USP_DATALIST_PROSPECTREQUESTSBYAPPUSER(
    @ID uniqueidentifier,
    @DATEFILTER smallint = 5)
as
    set nocount on;

    declare @STARTDATE datetime;
    declare @ENDDATE datetime;
    declare @SUBMITTEDBYID uniqueidentifier

    select @SUBMITTEDBYID = ID from dbo.APPUSER where CONSTITUENTID = @ID;

    if @DATEFILTER is null begin
        set @DATEFILTER = 10;  -- all dates

    end

    exec dbo.USP_RESOLVEDATEFILTER @DATEFILTER, @STARTDATE output, @ENDDATE output

    select 
        PROSPECTASSIGNMENTREQUEST.ID,
        PROSPECTASSIGNMENTREQUEST.TYPE,
        NF_PROSPECTNAME.NAME,
        PROSPECTASSIGNMENTREQUEST.STATUS,
        PROSPECTASSIGNMENTREQUEST.STATUSCODE,
        PROSPECT.ID,
        PROSPECTASSIGNMENTREQUEST.DATECHANGED,
        'Prospect',
        'b6ac8d8f-be02-4861-9119-cfcad2c1f361'
    from PROSPECTASSIGNMENTREQUEST
    inner join PROSPECTASSIGNMENTREQUESTPROSPECT on PROSPECTASSIGNMENTREQUEST.ID = PROSPECTASSIGNMENTREQUESTPROSPECT.ID
    inner join dbo.PROSPECT on PROSPECTASSIGNMENTREQUESTPROSPECT.PROSPECTID = PROSPECT.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECT.ID) NF_PROSPECTNAME
    where PROSPECTASSIGNMENTREQUEST.TYPECODE = 0  
      and PROSPECTASSIGNMENTREQUEST.STATUSCODE <> 3
      and PROSPECTASSIGNMENTREQUEST.SUBMITTEDBYID = @SUBMITTEDBYID 
            and PROSPECTASSIGNMENTREQUEST.DATEADDED between @STARTDATE and @ENDDATE

    union all

    select 
        PROSPECTASSIGNMENTREQUEST.ID,
        PROSPECTASSIGNMENTREQUEST.TYPE,
        NF_PROSPECTNAME.NAME,
        PROSPECTASSIGNMENTREQUEST.STATUS,
        PROSPECTASSIGNMENTREQUEST.STATUSCODE,
        PROSPECT.ID,
        PROSPECTASSIGNMENTREQUEST.DATEADDED,
        'Prospect',
        'b6ac8d8f-be02-4861-9119-cfcad2c1f361'
    from PROSPECTASSIGNMENTREQUEST
    inner join PROSPECTASSIGNMENTREQUESTPROSPECTPLAN on PROSPECTASSIGNMENTREQUEST.ID = PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.ID
    inner join dbo.PROSPECTPLAN on PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.PROSPECTPLANID = PROSPECTPLAN.ID
    inner join dbo.PROSPECT on PROSPECTPLAN.PROSPECTID = PROSPECT.ID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECT.ID) NF_PROSPECTNAME
    where PROSPECTASSIGNMENTREQUEST.TYPECODE <> 0
      and PROSPECTASSIGNMENTREQUEST.STATUSCODE <> 3
      and PROSPECTASSIGNMENTREQUEST.SUBMITTEDBYID = @SUBMITTEDBYID 
            and PROSPECTASSIGNMENTREQUEST.DATEADDED between @STARTDATE and @ENDDATE

   union all

    select 
      PROSPECTPLANREQUEST.ID,
      PROSPECTPLANTYPECODE.DESCRIPTION,
      NF_PROSPECTNAME.NAME,
      PROSPECTPLANREQUEST.STATUS,
      PROSPECTPLANREQUEST.STATUSCODE,
      PROSPECTPLANREQUEST.PROSPECTID,
      PROSPECTPLANREQUEST.DATEADDED,
      'Plan',
      '090fbe43-d8ef-435f-9d5f-5441da7c0256'
    from dbo.PROSPECTPLANREQUEST
      inner join dbo.PROSPECTPLANTYPECODE on PROSPECTPLANREQUEST.PROSPECTPLANTYPECODEID = PROSPECTPLANTYPECODE.ID
      outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLANREQUEST.PROSPECTID) NF_PROSPECTNAME
    where
      PROSPECTPLANREQUEST.STATUSCODE <> 3 and
      PROSPECTPLANREQUEST.SUBMITTEDBYID = @SUBMITTEDBYID and
      PROSPECTPLANREQUEST.DATEADDED between @STARTDATE and @ENDDATE