USP_DATAFORMTEMPLATE_VIEW_APPEALPROFILEREPORT_SECTIONSHAVEDATA

The load procedure used by the view dataform template "Appeal Profile Sections Have Data"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@HASBENEFITS	bit	INOUT	Has benefits
@HASDESIGNATIONS	bit	INOUT	Has designations
@HASGIFTHISTORY	bit	INOUT	Has gift history
@HASMAILINGS	bit	INOUT	Has mailings
@HASSOLICITORS	bit	INOUT	Has solicitors
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_APPEALPROFILEREPORT_SECTIONSHAVEDATA
(
  @ID uniqueidentifier,
  @HASBENEFITS bit = null output,
  @HASDESIGNATIONS bit = null output,
  @HASGIFTHISTORY bit = null output,
  @HASMAILINGS bit = null output,
  @HASSOLICITORS bit = null output,
  @DATALOADED bit =  0 output
)
as
  set nocount on;

  if exists (
    select 
      APPEALBENEFITDETAIL.ID
    from dbo.APPEALBENEFIT
    inner join dbo.APPEALBENEFITDETAIL
    on APPEALBENEFIT.ID = APPEALBENEFITDETAIL.APPEALBENEFITID
    inner join dbo.BENEFIT
    on APPEALBENEFITDETAIL.BENEFITID = BENEFIT.ID
    where APPEALBENEFIT.APPEALID = @ID)
  begin
    select @HASBENEFITS = 1;
  end

  if exists (
    select
      DESIGNATION.ID
    from dbo.DESIGNATION
    inner join dbo.APPEALDESIGNATION
    on DESIGNATION.ID = APPEALDESIGNATION.DESIGNATIONID
    where APPEALID = @ID)
  begin
    select @HASDESIGNATIONS = 1;
  end

  if exists (
    select
      REVENUE.ID
    from dbo.REVENUE
    inner join dbo.CONSTITUENT
    on REVENUE.CONSTITUENTID = CONSTITUENT.ID
    inner join dbo.REVENUESPLIT
    on REVENUE.ID = REVENUESPLIT.REVENUEID
    inner join dbo.DESIGNATION
    on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
    where 
    (REVENUE.TRANSACTIONTYPECODE = 1 or --Pledge
    (REVENUE.TRANSACTIONTYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE in (0,1,3))) and --Payment (Gift or Recurring gift payment)
    REVENUE.APPEALID = @ID)
  begin
    select @HASGIFTHISTORY = 1;
  end

  if exists (
    select *
    from dbo.[MKTSEGMENTATIONACTIVATE]
    inner join dbo.[MKTSEGMENTATION] on [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
    where [MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID] = cast(@ID as nvarchar(36))
    and [MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = 'DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0')
  begin
    select @HASMAILINGS = 1;
  end

  if exists (
    select
      TEAMFUNDRAISINGTEAM.ID
    from
    dbo.TEAMFUNDRAISINGTEAM
    where TEAMFUNDRAISINGTEAM.APPEALID = @ID

    union all

    select 
      TEAMFUNDRAISER.ID
    from 
    dbo.TEAMFUNDRAISER
    inner join dbo.CONSTITUENT
    on TEAMFUNDRAISER.CONSTITUENTID = CONSTITUENT.ID
    left join TEAMFUNDRAISINGTEAMMEMBER
    on TEAMFUNDRAISER.ID = TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISERID
    left join TEAMFUNDRAISINGTEAMCAPTAIN
    on TEAMFUNDRAISINGTEAMMEMBER.TEAMFUNDRAISINGTEAMID = TEAMFUNDRAISINGTEAMCAPTAIN.TEAMFUNDRAISINGTEAMID
    and CONSTITUENT.ID = TEAMFUNDRAISINGTEAMCAPTAIN.CONSTITUENTID
    where TEAMFUNDRAISER.APPEALID = @ID
    -- Exclude captains from this section since they will be returned below.
    and TEAMFUNDRAISINGTEAMCAPTAIN.ID is null

    union all

    select 
      TEAMFUNDRAISINGTEAMCAPTAIN.ID
    from 
    dbo.TEAMFUNDRAISINGTEAMCAPTAIN
    inner join dbo.CONSTITUENT
    on TEAMFUNDRAISINGTEAMCAPTAIN.CONSTITUENTID = CONSTITUENT.ID
    -- Include for APPEALID
    inner join dbo.TEAMFUNDRAISINGTEAM
    on TEAMFUNDRAISINGTEAMCAPTAIN.TEAMFUNDRAISINGTEAMID = TEAMFUNDRAISINGTEAM.ID
    -- Include to get goal
    left join dbo.TEAMFUNDRAISER
    on TEAMFUNDRAISER.CONSTITUENTID = CONSTITUENT.ID
    and TEAMFUNDRAISER.APPEALID = TEAMFUNDRAISINGTEAM.APPEALID
    where TEAMFUNDRAISINGTEAM.APPEALID = @ID)
  begin
    select @HASSOLICITORS = 1;
  end

  select @DATALOADED = 1;

  return 0;