V_QUERY_PROSPECTASSIGNMENTREQUEST
Query view prospect assignment requests
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| TYPE | nvarchar(20) | yes | Type |
| STATUS | nvarchar(8) | yes | Status |
| REVIEWDATE | datetime | yes | Review date |
| REQUESTEDPROSPECTMANAGERID | uniqueidentifier | yes | Requested prospect manager ID |
| REQUESTEDPROSPECTMANAGER | nvarchar(154) | yes | Requested prospect manager |
| REQUESTEDPRIMARYPLANMANAGERID | uniqueidentifier | yes | Requested plan primary manager ID |
| REQUESTEDPRIMARYPLANMANAGER | nvarchar(154) | yes | Requested plan primary manager |
| REQUESTEDSECONDARYPLANMANAGERID | uniqueidentifier | yes | Requested plan secondary manager ID |
| REQUESTEDSECONDARYPLANMANAGER | nvarchar(154) | yes | Requested plan secondary manager |
| REQUESTEDSECONDARYSOLICITORID | uniqueidentifier | yes | Requested secondary solicitor ID |
| REQUESTEDSECONDARYSOLICITOR | nvarchar(154) | yes | Requested secondary solicitor |
| REVIEWEDBYNAME | nvarchar(128) | yes | Reviewed by name |
| REVIEWEDBYID | uniqueidentifier | yes | Reviewed by system ID |
| SUBMITTEDBYNAME | nvarchar(128) | yes | Submitted by name |
| SUBMITTEDBYID | uniqueidentifier | Submitted by system ID | |
| REQUESTEDPROSPECTID | uniqueidentifier | yes | REQUESTEDPROSPECTID |
| REQUESTPROSPECTPLANID | uniqueidentifier | yes | REQUESTPROSPECTPLANID |
| REQUESTSECONDARYFUNDRAISERID | uniqueidentifier | yes | REQUESTSECONDARYFUNDRAISERID |
| SOLICITORROLECODE | nvarchar(100) | yes | Secondary solicitor role |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 8/17/2011 2:35:44 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_PROSPECTASSIGNMENTREQUEST AS
select
PROSPECTASSIGNMENTREQUEST.ID,
PROSPECTASSIGNMENTREQUEST.[TYPE],
PROSPECTASSIGNMENTREQUEST.[STATUS],
PROSPECTASSIGNMENTREQUEST.REVIEWDATE,
case when PROSPECTASSIGNMENTREQUEST.[TYPECODE] = 0 then PROSPECTASSIGNMENTREQUEST.NEWFUNDRAISERID else null end REQUESTEDPROSPECTMANAGERID,
case when PROSPECTASSIGNMENTREQUEST.[TYPECODE] = 0 then NF.NAME else null end REQUESTEDPROSPECTMANAGER,
case when PROSPECTASSIGNMENTREQUEST.[TYPECODE] = 2 then PROSPECTASSIGNMENTREQUEST.NEWFUNDRAISERID else null end REQUESTEDPRIMARYPLANMANAGERID,
case when PROSPECTASSIGNMENTREQUEST.[TYPECODE] = 2 then NF.NAME else null end REQUESTEDPRIMARYPLANMANAGER,
case when PROSPECTASSIGNMENTREQUEST.[TYPECODE] = 3 then PROSPECTASSIGNMENTREQUEST.NEWFUNDRAISERID else null end REQUESTEDSECONDARYPLANMANAGERID,
case when PROSPECTASSIGNMENTREQUEST.[TYPECODE] = 3 then NF.NAME else null end REQUESTEDSECONDARYPLANMANAGER,
case when PROSPECTASSIGNMENTREQUEST.[TYPECODE] = 4 then PROSPECTASSIGNMENTREQUEST.NEWFUNDRAISERID else null end REQUESTEDSECONDARYSOLICITORID,
case when PROSPECTASSIGNMENTREQUEST.[TYPECODE] = 4 then NF.NAME else null end REQUESTEDSECONDARYSOLICITOR,
dbo.UFN_APPUSER_GETNAME(PROSPECTASSIGNMENTREQUEST.REVIEWEDBYID) REVIEWEDBYNAME,
PROSPECTASSIGNMENTREQUEST.REVIEWEDBYID,
dbo.UFN_APPUSER_GETNAME(PROSPECTASSIGNMENTREQUEST.SUBMITTEDBYID) SUBMITTEDBYNAME,
PROSPECTASSIGNMENTREQUEST.SUBMITTEDBYID,
coalesce(PROSPECTASSIGNMENTREQUESTPROSPECT.PROSPECTID, PROSPECTPLAN.PROSPECTID) REQUESTEDPROSPECTID,
PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.PROSPECTPLANID REQUESTPROSPECTPLANID,
PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.SECONDARYFUNDRAISERID REQUESTSECONDARYFUNDRAISERID,
SOLICITORROLECODE.DESCRIPTION [SOLICITORROLECODE],
PROSPECTASSIGNMENTREQUEST.DATEADDED,
PROSPECTASSIGNMENTREQUEST.DATECHANGED,
PROSPECTASSIGNMENTREQUEST.TSLONG,
ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
ADDEDBY.USERNAME as ADDEDBY_USERNAME,
CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
CHANGEDBY.USERNAME as CHANGEDBY_USERNAME
/*#EXTENSION*/
from
dbo.PROSPECTASSIGNMENTREQUEST
left join dbo.CHANGEAGENT ADDEDBY on ADDEDBY.ID = PROSPECTASSIGNMENTREQUEST.ADDEDBYID
left join dbo.CHANGEAGENT CHANGEDBY on CHANGEDBY.ID = PROSPECTASSIGNMENTREQUEST.CHANGEDBYID
left outer join dbo.PROSPECTASSIGNMENTREQUESTPROSPECT on PROSPECTASSIGNMENTREQUEST.ID = PROSPECTASSIGNMENTREQUESTPROSPECT.ID
left outer join dbo.PROSPECTASSIGNMENTREQUESTPROSPECTPLAN on PROSPECTASSIGNMENTREQUEST.ID = PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.ID
left outer join dbo.PROSPECTPLAN on PROSPECTPLAN.ID = PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.PROSPECTPLANID
left join dbo.SOLICITORROLECODE on SOLICITORROLECODE.ID = PROSPECTASSIGNMENTREQUESTPROSPECTPLAN.SOLICITORROLECODEID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTASSIGNMENTREQUEST.NEWFUNDRAISERID) NF