USP_DATALIST_PROSPECTREQUESTS

                CREATE procedure dbo.USP_DATALIST_PROSPECTREQUESTS(@ID uniqueidentifier)
                as
                    set nocount on;

                    select 
                        PROSPECTASSIGNMENTREQUEST.ID,
                        PROSPECTASSIGNMENTREQUEST.TYPE,
                        NF_PROSPECTNAME.NAME,
                        PROSPECT.ID,
                        isnull(NF_SUBMITTEDBY.NAME,APPUSER.DISPLAYNAME) as REQUESTEDBY,
                        PROSPECTASSIGNMENTREQUEST.DATEADDED,
                        0,
                        'Prospect',
                        'b6ac8d8f-be02-4861-9119-cfcad2c1f361'
                    from 
                        PROSPECTASSIGNMENTREQUEST
                        inner join PROSPECTASSIGNMENTREQUESTPROSPECT on PROSPECTASSIGNMENTREQUEST.ID = PROSPECTASSIGNMENTREQUESTPROSPECT.ID
                        inner join APPUSER on PROSPECTASSIGNMENTREQUEST.SUBMITTEDBYID = APPUSER.ID
                        inner join dbo.PROSPECT on PROSPECTASSIGNMENTREQUESTPROSPECT.PROSPECTID = PROSPECT.ID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(APPUSER.CONSTITUENTID) NF_SUBMITTEDBY
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECT.ID) NF_PROSPECTNAME
                    where  
                        PROSPECTASSIGNMENTREQUEST.TYPECODE = 0 and
                        PROSPECTASSIGNMENTREQUEST.STATUSCODE = 0 and 
                        (
                            PROSPECT.PROSPECTMANAGERFUNDRAISERID = @ID 
                            or 
                            (
                                PROSPECT.PROSPECTMANAGERFUNDRAISERID is null 
                                and 
                                PROSPECTASSIGNMENTREQUEST.NEWFUNDRAISERID = @ID
                            )
                        )

            union all                    

          select 
                        PROSPECTASSIGNMENTREQUEST.ID,
                        PROSPECTASSIGNMENTREQUEST.TYPE,
                        NF_PROSPECTNAME.NAME,
                        PROSPECT.ID,
                        isnull(NF_SUBMITTEDBY.NAME,APPUSER.DISPLAYNAME) as REQUESTEDBY,
                        PROSPECTASSIGNMENTREQUEST.DATEADDED,
                        0,
                        'Prospect',
                        'b6ac8d8f-be02-4861-9119-cfcad2c1f361'
                    from 
                        dbo.PROSPECTASSIGNMENTREQUEST
                        inner join PROSPECTASSIGNMENTREQUESTPROSPECTPLAN on PROSPECTASSIGNMENTREQUEST.ID = PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.ID
                        inner join dbo.PROSPECTPLAN on PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.PROSPECTPLANID = PROSPECTPLAN.ID
                        inner join APPUSER on PROSPECTASSIGNMENTREQUEST.SUBMITTEDBYID = APPUSER.ID
                        inner join dbo.PROSPECT on PROSPECTPLAN.PROSPECTID = PROSPECT.ID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(APPUSER.CONSTITUENTID) NF_SUBMITTEDBY
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECT.ID) NF_PROSPECTNAME
                    where  
                        PROSPECTASSIGNMENTREQUEST.TYPECODE <> 0 and
                        PROSPECTASSIGNMENTREQUEST.STATUSCODE = 0 and 
                        PROSPECT.PROSPECTMANAGERFUNDRAISERID = @ID 

            union all

          select 
            PROSPECTPLANREQUEST.ID,
            PROSPECTPLANTYPECODE.DESCRIPTION,
            NF_PROSPECTNAME.NAME,
            PROSPECTPLANREQUEST.PROSPECTID,
            isnull(NF_SUBMITTEDBY.NAME,APPUSER.DISPLAYNAME) as REQUESTEDBY,
            PROSPECTPLANREQUEST.DATEADDED,
            1,
            'Plan',
            '090fbe43-d8ef-435f-9d5f-5441da7c0256'
          from dbo.PROSPECTPLANREQUEST
            inner join dbo.PROSPECTPLANTYPECODE on PROSPECTPLANREQUEST.PROSPECTPLANTYPECODEID = PROSPECTPLANTYPECODE.ID
            inner join dbo.APPUSER on PROSPECTPLANREQUEST.SUBMITTEDBYID = APPUSER.ID
            inner join dbo.PROSPECT on PROSPECTPLANREQUEST.PROSPECTID = PROSPECT.ID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(APPUSER.CONSTITUENTID) NF_SUBMITTEDBY
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLANREQUEST.PROSPECTID) NF_PROSPECTNAME
          where
            PROSPECTPLANREQUEST.STATUSCODE = 0 and
            PROSPECT.PROSPECTMANAGERFUNDRAISERID = @ID