USP_DATALIST_CAMPAIGNPLANNEDGIFTS

List of planned gifts with a designation associated with a campaign

Parameters

Parameter	Parameter Type	Mode	Description
@CAMPAIGNID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@STATUSCODE	tinyint	IN	Status
@VEHICLECODE	tinyint	IN	Vehicle
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.

Definition

Copy



CREATE procedure dbo.USP_DATALIST_CAMPAIGNPLANNEDGIFTS
(
    @CAMPAIGNID uniqueidentifier,
    @STATUSCODE tinyint = null,
    @VEHICLECODE tinyint = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null
)
as
    set nocount on;

    select distinct PG.ID,
           CONSTITUENT.NAME PROSPECTNAME,
           [PLAN].NAME PLANNAME,
           PG.VEHICLE,
           PG.STATUS,
           dbo.UFN_PLANNEDGIFT_GETSITELIST(PG.ID) SITES,
           dbo.UFN_PLANNEDGIFT_GETTOTALAMOUNT(PG.ID, 3, PG.BASECURRENCYID) GIFTAMOUNT,
           PG.BASECURRENCYID
    from dbo.PLANNEDGIFT PG
    inner join dbo.CONSTITUENT on PG.CONSTITUENTID = CONSTITUENT.ID
    left join dbo.PLANNEDGIFTDESIGNATION PGD on PGD.PLANNEDGIFTID = PG.ID
    left join dbo.PLANNEDGIFTDESIGNATIONCAMPAIGN PGDCAMPAIGN on PGDCAMPAIGN.PLANNEDGIFTDESIGNATIONID = PGD.ID
    left join dbo.PLANNEDGIFTADDITION PGA on PG.ID = PGA.PLANNEDGIFTID
    left join dbo.PLANNEDGIFTADDITIONDESIGNATION PGAD on PGAD.PLANNEDGIFTADDITIONID = PGA.ID
    left join dbo.PLANNEDGIFTADDITIONDESIGNATIONCAMPAIGN PGADCAMPAIGN on PGADCAMPAIGN.PLANNEDGIFTADDITIONDESIGNATIONID = PGAD.ID
    left join dbo.PROSPECTPLAN [PLAN] on PG.PROSPECTPLANID = [PLAN].ID
    where
        (PGDCAMPAIGN.CAMPAIGNID = @CAMPAIGNID or PGADCAMPAIGN.CAMPAIGNID = @CAMPAIGNID) and
        (@VEHICLECODE is null or PG.VEHICLECODE = @VEHICLECODE) and
        (@STATUSCODE is null or PG.STATUSCODE = @STATUSCODE) and
        (
            select 
                count(*)
            from 
                dbo.UFN_SITEID_MAPFROM_PLANNEDGIFTID(PG.ID) as PLANNEDGIFTSITE
            where 
                (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PLANNEDGIFTSITE].[SITEID] or (SITEID is null and [PLANNEDGIFTSITE].[SITEID] is null)))
        ) > 0 and
        (
            @SITEFILTERMODE = 0
            or PG.ID in ( 
                select 
                    PLANNEDGIFTSITE.PLANNEDGIFTID
                from 
                    dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) as SITEFILTER
                    inner join dbo.PLANNEDGIFTSITE on PLANNEDGIFTSITE.SITEID = SITEFILTER.SITEID
            )
        )