USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTGROUPPLANNEDGIFTSUMMARY

The load procedure used by the view dataform template "Constituent Group Planned Gifts Summary View"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@NUMBERACCEPTED	int	INOUT	Total number accepted
@AMOUNTACCEPTED	int	INOUT	Total amount accepted
@NUMBERPROPOSED	int	INOUT	Number proposed
@NUMBERPENDINGRESPONSE	int	INOUT	Number pending response

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTGROUPPLANNEDGIFTSUMMARY
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @NUMBERACCEPTED int = null output,
  @AMOUNTACCEPTED int = null output,
  @NUMBERPROPOSED int = null output,
  @NUMBERPENDINGRESPONSE int = null output
)
as
begin
  set nocount on;

  declare @MULTICURRENCYENABLED bit = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency');

  declare @GROUPANDMEMBER_PLANNEDGIFTS table
  (
    ID uniqueidentifier,
    CONSTITUENTID uniqueidentifier,
    STATUSCODE tinyint,
    GIFTAMOUNT money,
    ISADDITION bit
  );

  insert into
    @GROUPANDMEMBER_PLANNEDGIFTS
  select distinct
    PLANNEDGIFT.ID,
    PLANNEDGIFT.CONSTITUENTID,
    PLANNEDGIFT.STATUSCODE,
    case @MULTICURRENCYENABLED
      when 0
        then
          PLANNEDGIFT.GIFTAMOUNT
        else
          PLANNEDGIFT.ORGANIZATIONGIFTAMOUNT
    end as GIFTAMOUNT,
    0 as ISADDITION
  from
    dbo.PLANNEDGIFT
  where
    --Proposal = 0; Response pending = 1; Accepted = 2
    PLANNEDGIFT.STATUSCODE in (0, 1, 2) and
    PLANNEDGIFT.CONSTITUENTID in
    (
      select
        @ID

      union all

      select
        ID
      from
        dbo.UFN_GROUP_GETCURRENTMEMBERSNOPERMISSIONCHECK(@ID)
    );

  insert into
    @GROUPANDMEMBER_PLANNEDGIFTS
  select distinct
    PLANNEDGIFTADDITION.ID,
    PG.CONSTITUENTID,
    PG.STATUSCODE,
    case @MULTICURRENCYENABLED
      when 0
        then
          PLANNEDGIFTADDITION.GIFTAMOUNT
        else
          PLANNEDGIFTADDITION.ORGANIZATIONGIFTAMOUNT
    end as GIFTAMOUNT,
    1 as ISADDITION
  from
    dbo.PLANNEDGIFTADDITION
  inner join
    @GROUPANDMEMBER_PLANNEDGIFTS PG on PG.ID = PLANNEDGIFTADDITION.PLANNEDGIFTID;

  select
    @NUMBERPROPOSED = (select count(ID) from @GROUPANDMEMBER_PLANNEDGIFTS where STATUSCODE = 0 and ISADDITION = 0),
    @NUMBERPENDINGRESPONSE = (select count(ID) from @GROUPANDMEMBER_PLANNEDGIFTS where STATUSCODE = 1 and ISADDITION = 0),
    @NUMBERACCEPTED = (select count(ID) from @GROUPANDMEMBER_PLANNEDGIFTS where STATUSCODE = 2 and ISADDITION = 0),
    @AMOUNTACCEPTED = (select sum(GIFTAMOUNT) from @GROUPANDMEMBER_PLANNEDGIFTS where STATUSCODE = 2),
    @DATALOADED = 1;

end