USP_DATAFORMTEMPLATE_VIEW_RECURRINGGIFT

The load procedure used by the view dataform template "Recurring Gift View Form"

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.
@ENDDATE	datetime	INOUT	End date
@STARTDATE	datetime	INOUT	Start date
@SOURCECODE	nvarchar(50)	INOUT	Source code
@MAILING	nvarchar(100)	INOUT	Effort
@CHANNEL	nvarchar(100)	INOUT	Inbound channel
@APPEAL	nvarchar(100)	INOUT	Appeal
@REVENUEREFERENCE	nvarchar(255)	INOUT	Revenue reference
@TAXCLAIMELIGIBILITY	nvarchar(30)	INOUT	Tax claim eligibility
@QUALIFICATIONSTATUS	nvarchar(30)	INOUT	Gift Aid qualification status
@CONSTITUENTISGROUP	bit	INOUT	Constituent is group
@TAXCLAIMELIGIBILITYSTATUSCODE	tinyint	INOUT	Tax claim eligibility
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Transaction currency ID
@ORGANIZATIONAMOUNT	money	INOUT	Organization amount
@BASEEXCHANGERATE	decimal(20, 8)	INOUT	Transaction to base exchange rate
@ORGANIZATIONEXCHANGERATE	decimal(20, 8)	INOUT	Base to organization exchange rate
@APPEALID	uniqueidentifier	INOUT	Appeal ID
@MEMBERSHIPID	uniqueidentifier	INOUT
@MEMBERSHIP	nvarchar(100)	INOUT
@MEMBERSHIPDATE	date	INOUT
@MEMBERCONSTITUENTID	uniqueidentifier	INOUT
@MEMBERCONSTITUENT	nvarchar(100)	INOUT
@MEMBERSHIPEARNEDINCOMEAMOUNT	money	INOUT
@MEMBERSHIPCONTRIBUTEDINCOMEAMOUNT	money	INOUT
@LOOKUPID	nvarchar(100)	INOUT
@VATAMOUNT	money	INOUT
@BATCHNUMBER	nvarchar(100)	INOUT
@REVENUECATEGORYCODE	nvarchar(100)	INOUT
@ACKNOWLEDGEMENTSTATUS	nvarchar(50)	INOUT
@ACKNOWLEDGEMENTDATE	datetime	INOUT
@SPONSORSHIPID	uniqueidentifier	INOUT
@SPONSORSHIP	nvarchar(100)	INOUT
@SPONSORSHIPDATE	date	INOUT
@SPONSORSHIPOPPORTUNITY	nvarchar(154)	INOUT
@CONSTITUENTACCOUNTID	uniqueidentifier	INOUT
@DDISOURCE	nvarchar(100)	INOUT
@DDISOURCEDATE	date	INOUT
@SENDPMINSTRUCTION	bit	INOUT
@PMINSTRUCTIONTOSEND	nvarchar(10)	INOUT
@PMINSTRUCTIONDATE_NEW	date	INOUT
@PMINSTRUCTIONDATE_CANCEL	date	INOUT
@PMINSTRUCTIONDATE_SETUP	date	INOUT
@PMADVANCENOTICESENTDATE	date	INOUT
@SEPAMANDATELOOKUPID	nvarchar(35)	INOUT
@SEPAMANDATESIGNATUREDATE	datetime	INOUT
@SEPAMANDATESTATUSCODE	tinyint	INOUT
@CURRENTAPPUSERID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_RECURRINGGIFT
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @ENDDATE datetime = null output,
  @STARTDATE datetime = null output,
  @SOURCECODE nvarchar(50) = null output,
  @MAILING nvarchar(100) = null output,
  @CHANNEL nvarchar(100) = null output,
  @APPEAL nvarchar(100) = null output,
  @REVENUEREFERENCE nvarchar(255) = null output,
  @TAXCLAIMELIGIBILITY nvarchar(30) = null output,
  @QUALIFICATIONSTATUS nvarchar(30) = null output,
  @CONSTITUENTISGROUP bit = null output,
  @TAXCLAIMELIGIBILITYSTATUSCODE tinyint = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @TRANSACTIONCURRENCYID uniqueidentifier = null output,
  @ORGANIZATIONAMOUNT money = null output,
  @BASEEXCHANGERATE decimal(20,8) = null output,
  @ORGANIZATIONEXCHANGERATE decimal(20,8) = null output,
  @APPEALID uniqueidentifier = null output,
  @MEMBERSHIPID uniqueidentifier = null output,
  @MEMBERSHIP nvarchar(100) = null output,
  @MEMBERSHIPDATE date = null output,
  @MEMBERCONSTITUENTID uniqueidentifier = null output,
  @MEMBERCONSTITUENT nvarchar(100) = null output,
  @MEMBERSHIPEARNEDINCOMEAMOUNT money = null output,
  @MEMBERSHIPCONTRIBUTEDINCOMEAMOUNT money = null output,
  @LOOKUPID nvarchar(100) = null output,
  @VATAMOUNT money = null output,
  @BATCHNUMBER nvarchar(100) = null output,
  @REVENUECATEGORYCODE nvarchar(100) = null output,
  @ACKNOWLEDGEMENTSTATUS nvarchar(50) = null output,
  @ACKNOWLEDGEMENTDATE datetime = null output,
  @SPONSORSHIPID uniqueidentifier = null output,
  @SPONSORSHIP nvarchar(100) = null output,
  @SPONSORSHIPDATE date = null output,
  @SPONSORSHIPOPPORTUNITY nvarchar(154) = null output,
  @CONSTITUENTACCOUNTID uniqueidentifier = null output,
  @DDISOURCE nvarchar(100) = null output,
  @DDISOURCEDATE date = null output,
  @SENDPMINSTRUCTION bit = null output,
  @PMINSTRUCTIONTOSEND nvarchar(10) = null output,
  @PMINSTRUCTIONDATE_NEW date = null output,
  @PMINSTRUCTIONDATE_CANCEL date = null output,
  @PMINSTRUCTIONDATE_SETUP date = null output,
  @PMADVANCENOTICESENTDATE date = null output,
  @SEPAMANDATELOOKUPID nvarchar(35) = null output,
  @SEPAMANDATESIGNATUREDATE datetime = null output,
  @SEPAMANDATESTATUSCODE tinyint = null output,
  @CURRENTAPPUSERID uniqueidentifier = null 
)
as
  set nocount on;

  set @DATALOADED = 0;

  select @CONSTITUENTISGROUP = (ISGROUP|ISORGANIZATION)
  from dbo.CONSTITUENT
  inner join dbo.REVENUE on REVENUE.CONSTITUENTID = CONSTITUENT.ID
  where REVENUE.ID = @ID;

  declare @PAYMENTMETHODCODE tinyint;

  select
    @DATALOADED = 1,
    @STARTDATE = REVENUESCHEDULE.STARTDATE,
    @ENDDATE = REVENUESCHEDULE.ENDDATE,
    @SOURCECODE = REXT.SOURCECODE,
    @MAILING = dbo.UFN_MKTSEGMENTATION_GETNAME(REXT.MAILINGID),
    @APPEAL = dbo.UFN_APPEAL_GETNAME(REXT.APPEALID),
    @CHANNEL = CHANNELCODE.DESCRIPTION,
    @REVENUEREFERENCE = REVENUEREFERENCE.REFERENCE,
    @BASECURRENCYID = case when REVENUE.DELETEDON is null then isnull(REXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) else null end,
    @TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
    @ORGANIZATIONAMOUNT = REVENUE.ORGAMOUNT,
    @APPEALID = REXT.APPEALID,
    @LOOKUPID = REVENUE.CALCULATEDUSERDEFINEDID,
    @BATCHNUMBER = REXT.BATCHNUMBER,
    @PAYMENTMETHODCODE = REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE
  from dbo.FINANCIALTRANSACTION REVENUE
  inner join dbo.REVENUE_EXT REXT ON REXT.ID = REVENUE.ID
  left outer join  dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on REVENUE.ID = V.FINANCIALTRANSACTIONID
  inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
  left join dbo.CHANNELCODE on CHANNELCODE.ID = REXT.CHANNELCODEID
  left join dbo.REVENUEREFERENCE on REVENUEREFERENCE.ID = REVENUE.ID
  inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
  where REVENUE.ID = @ID and REVENUE.TYPECODE = 2;

  -- Pull membership data

  select top 1
    @MEMBERSHIPID = MEMBERSHIP.ID,
    @MEMBERSHIPDATE = MEMBERSHIPTRANSACTION.TRANSACTIONDATE,
    @MEMBERSHIP = dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID),
    @MEMBERCONSTITUENT = NF.NAME,
    @MEMBERCONSTITUENTID = case
        when MEMBERSHIP.GIVENBYID is not null then MEMBER.CONSTITUENTID
        else null
    end,
    @MEMBERSHIPEARNEDINCOMEAMOUNT = dbo.UFN_PLEDGE_CALCULATEEARNEDINCOMEAMOUNT(FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID),
    @MEMBERSHIPCONTRIBUTEDINCOMEAMOUNT = dbo.UFN_PLEDGE_CALCULATECONTRIBUTEDINCOMEAMOUNT(FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID)
  from
    dbo.MEMBERSHIPTRANSACTION
        inner join dbo.MEMBERSHIP on MEMBERSHIPTRANSACTION.MEMBERSHIPID = MEMBERSHIP.ID
        inner join dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
        inner join dbo.FINANCIALTRANSACTIONLINEITEM on MEMBERSHIPTRANSACTION.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
        inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(MEMBER.CONSTITUENTID) NF
  where
    FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID and MEMBER.ISPRIMARY = 1
    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
    and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1;

  select
    @BASEEXCHANGERATE = CURRENCYEXCHANGERATE.RATE
  from dbo.REVENUE
  left join dbo.CURRENCYEXCHANGERATE on REVENUE.BASEEXCHANGERATEID = CURRENCYEXCHANGERATE.ID
  where REVENUE.ID = @ID;

  select
    @ORGANIZATIONEXCHANGERATE = CURRENCYEXCHANGERATE.RATE
  from dbo.REVENUE
  left join dbo.CURRENCYEXCHANGERATE on REVENUE.ORGANIZATIONEXCHANGERATEID = CURRENCYEXCHANGERATE.ID
  where REVENUE.ID = @ID;

  --Gift Aid is for UK only

  if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
    begin
      -- @TAXCLAIMELIGIBILITY is deprecated.  Should use @TAXCLAIMELIGIBILITYSTATUSCODE instead.

      select @TAXCLAIMELIGIBILITY = dbo.UFN_RECURRINGGIFTELIGIBILITY(@ID);
      select @TAXCLAIMELIGIBILITYSTATUSCODE = dbo.UFN_RECURRINGGIFTELIGIBILITYSTATUS(@ID);

      set @QUALIFICATIONSTATUS = dbo.UFN_RECURRINGGIFTQUALIFICATIONSTATUS(@ID);
      set @VATAMOUNT = isnull((select REVENUEVAT.TRANSACTIONVATAMOUNT from dbo.REVENUEVAT where dbo.REVENUEVAT.ID = @ID), 0);
    end

  select top 1 @REVENUECATEGORYCODE = GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME
  from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
  inner join dbo.REVENUECATEGORY on REVENUECATEGORY.ID = REVENUESPLIT.ID
  inner join dbo.GLREVENUECATEGORYMAPPING on REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID = GLREVENUECATEGORYMAPPING.ID
  where REVENUESPLIT.FINANCIALTRANSACTIONID = @ID;

  set @ACKNOWLEDGEMENTSTATUS = dbo.UFN_REVENUETRANSACTION_GETACKNOWLEDGESTATUS(@ID);

  select top 1 @ACKNOWLEDGEMENTDATE = ACKNOWLEDGEDATE 
  from dbo.REVENUELETTER 
  where REVENUELETTER.REVENUEID = @ID;

  --Pull sponsorship Data

  select top 1 @SPONSORSHIPID = SPONSORSHIP.ID,
               @SPONSORSHIPDATE = SPONSORSHIP.STARTDATE,
               @SPONSORSHIPOPPORTUNITY = OPNAME.NAME,
               @SPONSORSHIP = 'X'
  from dbo.SPONSORSHIP 
  inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI ON SPONSORSHIP.REVENUESPLITID = FTLI.ID
  cross apply (select SPONSORSHIPOPPORTUNITYCHILD.NAME
      from dbo.SPONSORSHIPOPPORTUNITYCHILD
      where SPONSORSHIPOPPORTUNITYCHILD.ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID
      union all
      select SPONSORSHIPOPPORTUNITYPROJECT.NAME 
      from SPONSORSHIPOPPORTUNITYPROJECT
      where SPONSORSHIPOPPORTUNITYPROJECT.ID = SPONSORSHIP.SPONSORSHIPOPPORTUNITYID) OPNAME
  WHERE SPONSORSHIP.ISMOSTRECENTFORCOMMITMENT = 1
  and FTLI.FINANCIALTRANSACTIONID = @ID;

  if @PAYMENTMETHODCODE = 3 --Direct Debit

  begin
    --If you have permission, do the select otherwise account id will be null so you won't see this information in the detail.

    declare @USER_GRANTED_PAYMENTINFORMATION_VIEW bit
    if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
        set @USER_GRANTED_PAYMENTINFORMATION_VIEW = 1;
    else
        set @USER_GRANTED_PAYMENTINFORMATION_VIEW = dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE(@CURRENTAPPUSERID,'bafca6f5-4cdb-4173-aece-113713d38bfb');

    if  @USER_GRANTED_PAYMENTINFORMATION_VIEW = 1
        select 
          @CONSTITUENTACCOUNTID = REVENUESCHEDULEDIRECTDEBITPAYMENT.CONSTITUENTACCOUNTID,
          @DDISOURCE = coalesce((select DESCRIPTION from dbo.DDISOURCECODE where ID = DDISOURCECODEID), N''),
          @DDISOURCEDATE = REVENUESCHEDULEDIRECTDEBITPAYMENT.DDISOURCEDATE,
          @SENDPMINSTRUCTION = REVENUESCHEDULEDIRECTDEBITPAYMENT.SENDPMINSTRUCTION,
          @PMINSTRUCTIONTOSEND = REVENUESCHEDULEDIRECTDEBITPAYMENT.PMINSTRUCTIONTOSEND,
          @PMINSTRUCTIONDATE_NEW = REVENUESCHEDULEDIRECTDEBITPAYMENT.PMINSTRUCTIONDATE_NEW,
          @PMINSTRUCTIONDATE_CANCEL = REVENUESCHEDULEDIRECTDEBITPAYMENT.PMINSTRUCTIONDATE_CANCEL,
          @PMINSTRUCTIONDATE_SETUP = REVENUESCHEDULEDIRECTDEBITPAYMENT.PMINSTRUCTIONDATE_SETUP,
          @PMADVANCENOTICESENTDATE = REVENUESCHEDULEDIRECTDEBITPAYMENT.PMADVANCENOTICESENTDATE,
          @SEPAMANDATELOOKUPID = SEPAMANDATE.LOOKUPID,
          @SEPAMANDATESIGNATUREDATE = SEPAMANDATE.SIGNATUREDATE,
          @SEPAMANDATESTATUSCODE = SEPAMANDATE.STATUSCODE
        from dbo.REVENUESCHEDULEDIRECTDEBITPAYMENT
        left join dbo.SEPAMANDATE on SEPAMANDATE.ID = REVENUESCHEDULEDIRECTDEBITPAYMENT.SEPAMANDATEID
        where REVENUESCHEDULEDIRECTDEBITPAYMENT.ID = @ID;
  end
  return 0;