V_QUERY_OPPORTUNITYSOLICITOR
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | ||
| OPPORTUNITYID | uniqueidentifier | ||
| FUNDRAISERID | uniqueidentifier | yes | |
| FUNDRAISERTYPE | nvarchar(19) | yes | |
| DATEADDED | datetime | ||
| DATECHANGED | datetime | ||
| TSLONG | bigint | yes | |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | |
| ADDEDBY_USERNAME | nvarchar(128) | yes | |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | |
| CHANGEDBY_USERNAME | nvarchar(128) | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/11/2014 4:17:04 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_OPPORTUNITYSOLICITOR AS
select
OS.ID,
OS.OPPORTUNITYID,
case OS.FUNDRAISERTYPECODE
when 0
then PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID
when 1
then PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID
when 2
then SF.FUNDRAISERID
end as FUNDRAISERID,
OS.FUNDRAISERTYPE,
OS.DATEADDED,
OS.DATECHANGED,
OS.TSLONG,
ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
ADDEDBY.USERNAME as ADDEDBY_USERNAME,
CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
CHANGEDBY.USERNAME as CHANGEDBY_USERNAME
/*#EXTENSION*/
from
dbo.OPPORTUNITYSOLICITOR OS
left join
dbo.OPPORTUNITY on OPPORTUNITY.ID = OS.OPPORTUNITYID
left join
dbo.PROSPECTPLAN on PROSPECTPLAN.ID = OPPORTUNITY.PROSPECTPLANID
left join
dbo.SECONDARYFUNDRAISER SF on SF.ID = OS.SECONDARYSOLICITORID
left join
dbo.CHANGEAGENT ADDEDBY on ADDEDBY.ID = OS.ADDEDBYID
left join
dbo.CHANGEAGENT CHANGEDBY on CHANGEDBY.ID = OS.CHANGEDBYID
where
(OS.FUNDRAISERTYPECODE = 0 and (PROSPECTPLAN.PRIMARYMANAGERENDDATE is null or PROSPECTPLAN.PRIMARYMANAGERENDDATE > getdate())) or
(OS.FUNDRAISERTYPECODE = 1 and (PROSPECTPLAN.SECONDARYMANAGERENDDATE is null or PROSPECTPLAN.SECONDARYMANAGERENDDATE > getdate())) or
(OS.FUNDRAISERTYPECODE = 2 and (SF.DATETO is null or SF.DATETO > getdate()))