V_QUERY_PLANSOLICITORS

Fields

Field	Field Type	Null
OPPORTUNITYID	uniqueidentifier
FUNDRAISERID	uniqueidentifier	yes
FUNDRAISERTYPE	varchar(19)
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
ADDEDBYID	uniqueidentifier	yes
CHANGEDBYID	uniqueidentifier	yes

Definition

Copy

/*
Generated by Blackbaud AppFx Platform
Date:  5/3/2024 2:16:29 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.3700.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_PLANSOLICITORS AS



    --Primary managers on opportunities with no selected solicitors

select
    OPPORTUNITY.ID as OPPORTUNITYID,
    PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID as FUNDRAISERID,
    'Primary manager' as FUNDRAISERTYPE,
    OPPORTUNITY.DATEADDED as DATEADDED ,
    OPPORTUNITY.DATECHANGED as DATECHANGED,
    OPPORTUNITY.TSLONG as TSLONG,
    ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
    ADDEDBY.USERNAME as ADDEDBY_USERNAME,
    CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
    CHANGEDBY.USERNAME as CHANGEDBY_USERNAME,
    ADDEDBY.ID as ADDEDBYID,
    CHANGEDBY.ID as CHANGEDBYID
from
    dbo.PROSPECTPLAN
inner join
    dbo.OPPORTUNITY on OPPORTUNITY.PROSPECTPLANID = PROSPECTPLAN.ID
left join
    dbo.CHANGEAGENT ADDEDBY on ADDEDBY.ID = OPPORTUNITY.ADDEDBYID
left join
    dbo.CHANGEAGENT CHANGEDBY on CHANGEDBY.ID = OPPORTUNITY.CHANGEDBYID
where
    PROSPECTPLAN.PRIMARYMANAGERFUNDRAISERID is not null and
    (PROSPECTPLAN.PRIMARYMANAGERENDDATE is null or PROSPECTPLAN.PRIMARYMANAGERENDDATE > getdate())

union all

    --Secondary managers on opportunities with no selected solicitors

select
    OPPORTUNITY.ID as OPPORTUNITYID,
    PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID as FUNDRAISERID,
    'Secondary manager' as FUNDRAISERTYPE,
    OPPORTUNITY.DATEADDED as DATEADDED ,
    OPPORTUNITY.DATECHANGED as DATECHANGED,
    OPPORTUNITY.TSLONG as TSLONG,
    ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
    ADDEDBY.USERNAME as ADDEDBY_USERNAME,
    CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
    CHANGEDBY.USERNAME as CHANGEDBY_USERNAME,
    ADDEDBY.ID as ADDEDBYID,
    CHANGEDBY.ID as CHANGEDBYID
from
    dbo.PROSPECTPLAN
inner join
    dbo.OPPORTUNITY on OPPORTUNITY.PROSPECTPLANID = PROSPECTPLAN.ID
left join
    dbo.CHANGEAGENT ADDEDBY on ADDEDBY.ID = OPPORTUNITY.ADDEDBYID
left join
    dbo.CHANGEAGENT CHANGEDBY on CHANGEDBY.ID = OPPORTUNITY.CHANGEDBYID
where
    PROSPECTPLAN.SECONDARYMANAGERFUNDRAISERID is not null and
    (PROSPECTPLAN.SECONDARYMANAGERENDDATE is null or PROSPECTPLAN.SECONDARYMANAGERENDDATE > getdate())

union all

    --Secondary fundraisers on opportunities with no selected solicitors

select
    OPPORTUNITY.ID as OPPORTUNITYID,
    SF.FUNDRAISERID as FUNDRAISERID,
    'Secondary solicitor' as FUNDRAISERTYPE,
    OPPORTUNITY.DATEADDED as DATEADDED ,
    OPPORTUNITY.DATECHANGED as DATECHANGED,
    OPPORTUNITY.TSLONG as TSLONG,
    ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
    ADDEDBY.USERNAME as ADDEDBY_USERNAME,
    CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
    CHANGEDBY.USERNAME as CHANGEDBY_USERNAME,
    ADDEDBY.ID as ADDEDBYID,
    CHANGEDBY.ID as CHANGEDBYID
from
    dbo.SECONDARYFUNDRAISER SF
inner join
    dbo.OPPORTUNITY on OPPORTUNITY.PROSPECTPLANID = SF.PROSPECTPLANID
left join
    dbo.CHANGEAGENT ADDEDBY on ADDEDBY.ID = OPPORTUNITY.ADDEDBYID
left join
    dbo.CHANGEAGENT CHANGEDBY on CHANGEDBY.ID = OPPORTUNITY.CHANGEDBYID
where
    SF.DATETO is null or SF.DATETO > getdate()