USP_DATAFORMTEMPLATE_VIEW_PLEDGE

The load procedure used by the view dataform template "Pledge 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.
@SOURCECODE	nvarchar(50)	INOUT	Source code
@MAILING	nvarchar(100)	INOUT	Effort
@CHANNEL	nvarchar(100)	INOUT	Inbound channel
@APPEAL	nvarchar(100)	INOUT	Appeal
@SENDPLEDGEREMINDER	bit	INOUT	Send reminders
@PLEDGESUBTYPE	nvarchar(100)	INOUT	Subtype
@REFERENCE	nvarchar(255)	INOUT	Revenue reference
@OPPORTUNITYNAME	nvarchar(500)	INOUT	Associated with
@OPPORTUNITYID	uniqueidentifier	INOUT	Opportunity ID
@TAXCLAIMELIGIBILITY	nvarchar(20)	INOUT	Tax claim eligibility
@QUALIFICATIONSTATUS	nvarchar(30)	INOUT	Gift Aid qualification status
@CONSTITUENTISGROUP	bit	INOUT	Constituent is group
@ORIGINALAMOUNT	money	INOUT	Original amount
@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
@APPLIEDID	uniqueidentifier	INOUT
@APPLIEDDATE	datetime	INOUT
@APPLIEDTYPE	nvarchar(100)	INOUT
@APPLIEDNAME	nvarchar(700)	INOUT
@GIVENTO	nvarchar(100)	INOUT
@APPLIEDEARNEDINCOMEAMOUNT	money	INOUT
@APPLIEDEARNEDINCOMEBALANCE	money	INOUT
@APPLIEDCONTRIBUTEDINCOMEAMOUNT	money	INOUT
@APPLIEDCONTRIBUTEDINCOMEBALANCE	money	INOUT
@ISMEMBERSHIPPLEDGE	bit	INOUT
@REVENUECATEGORYCODE	nvarchar(100)	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PLEDGE
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @SOURCECODE nvarchar(50) = null output,
    @MAILING nvarchar(100) = null output,
    @CHANNEL nvarchar(100) = null output,
    @APPEAL nvarchar(100) = null output,
    @SENDPLEDGEREMINDER bit = null output,
    @PLEDGESUBTYPE nvarchar(100) = null output,
    @REFERENCE nvarchar(255) = null output,
    @OPPORTUNITYNAME nvarchar(500) = null output,
    @OPPORTUNITYID uniqueidentifier = null output,
    -- @TAXCLAIMELIGIBILITY is deprecated.  Should use @TAXCLAIMELIGIBILITYSTATUSCODE instead.

    @TAXCLAIMELIGIBILITY nvarchar(20) = null output,
    @QUALIFICATIONSTATUS nvarchar(30) = null output,
    @CONSTITUENTISGROUP bit = null output,
    @ORIGINALAMOUNT money = 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,
    @APPLIEDID uniqueidentifier = null output,
    @APPLIEDDATE datetime = null output,
    @APPLIEDTYPE nvarchar(100) = null output,
    @APPLIEDNAME nvarchar(700) = null output,
    @GIVENTO nvarchar(100) = null output,
    @APPLIEDEARNEDINCOMEAMOUNT money = null output,
    @APPLIEDEARNEDINCOMEBALANCE money = null output,
    @APPLIEDCONTRIBUTEDINCOMEAMOUNT money = null output,
    @APPLIEDCONTRIBUTEDINCOMEBALANCE money = null output,
    @ISMEMBERSHIPPLEDGE bit = null output,
    @REVENUECATEGORYCODE nvarchar(100) = null output
)
as
  set nocount on;

  set @DATALOADED = 0;

  select @CONSTITUENTISGROUP = (ISGROUP|ISORGANIZATION)
  from dbo.CONSTITUENT
  inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID
  inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
  where FINANCIALTRANSACTION.ID = @ID
    and FINANCIALTRANSACTION.DELETEDON is null;

  select
    @DATALOADED = 1,
    @SOURCECODE = REVENUE_EXT.SOURCECODE,
    @MAILING = dbo.UFN_MKTSEGMENTATION_GETNAME(REVENUE_EXT.MAILINGID),
    @CHANNEL = CHANNELCODE.DESCRIPTION,
    @SOURCECODE = REVENUE_EXT.SOURCECODE,
    @APPEAL = dbo.UFN_APPEAL_GETNAME(REVENUE_EXT.APPEALID),
    @SENDPLEDGEREMINDER = REVENUESCHEDULE.SENDPLEDGEREMINDER,
    @PLEDGESUBTYPE = PLEDGESUBTYPE.NAME,
    @REFERENCE = REVENUEREFERENCE.REFERENCE,
    @ORIGINALAMOUNT = case when FINANCIALTRANSACTION.TYPECODE = 15 then MEMBERSHIPINSTALLMENTPLANORIGINALAMOUNT.TRANSACTIONAMOUNT else PLEDGEORIGINALAMOUNT.TRANSACTIONAMOUNT end,
    @BASECURRENCYID = CURRENCYSET.BASECURRENCYID,
    @TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
    @ORGANIZATIONAMOUNT = FINANCIALTRANSACTION.ORGAMOUNT,
    @APPEALID = REVENUE_EXT.APPEALID,
    @ISMEMBERSHIPPLEDGE = case when FINANCIALTRANSACTION.TYPECODE = 15 then 1 else 0 end
  from dbo.FINANCIALTRANSACTION
  inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
  inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
  inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
  left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = FINANCIALTRANSACTION.ID
  inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = FINANCIALTRANSACTION.ID
  left join dbo.PLEDGESUBTYPE on PLEDGESUBTYPE.ID = REVENUESCHEDULE.PLEDGESUBTYPEID
  left join dbo.CHANNELCODE on CHANNELCODE.ID = REVENUE_EXT.CHANNELCODEID
  left join dbo.REVENUEREFERENCE on FINANCIALTRANSACTION.ID = REVENUEREFERENCE.ID
  left join dbo.PLEDGEORIGINALAMOUNT on FINANCIALTRANSACTION.ID = PLEDGEORIGINALAMOUNT.ID and FINANCIALTRANSACTION.TYPECODE <> 15
  left join dbo.MEMBERSHIPINSTALLMENTPLANORIGINALAMOUNT on FINANCIALTRANSACTION.ID = MEMBERSHIPINSTALLMENTPLANORIGINALAMOUNT.ID and FINANCIALTRANSACTION.TYPECODE = 15
  where FINANCIALTRANSACTION.ID = @ID and FINANCIALTRANSACTION.TYPECODE in (1,6,15)
    and FINANCIALTRANSACTION.DELETEDON is null;

  select
    @BASEEXCHANGERATE = CURRENCYEXCHANGERATE.RATE
  from dbo.FINANCIALTRANSACTION
  left join dbo.CURRENCYEXCHANGERATE on FINANCIALTRANSACTION.BASEEXCHANGERATEID = CURRENCYEXCHANGERATE.ID
  where FINANCIALTRANSACTION.ID = @ID
    and FINANCIALTRANSACTION.DELETEDON is null;

  select
    @ORGANIZATIONEXCHANGERATE = CURRENCYEXCHANGERATE.RATE
  from dbo.FINANCIALTRANSACTION
  left join dbo.CURRENCYEXCHANGERATE on FINANCIALTRANSACTION.ORGEXCHANGERATEID = CURRENCYEXCHANGERATE.ID
  where FINANCIALTRANSACTION.ID = @ID
    and FINANCIALTRANSACTION.DELETEDON is null;

    if @DATALOADED = 1
    select
      @OPPORTUNITYID = RO.OPPORTUNITYID,
            @OPPORTUNITYNAME = dbo.UFN_OPPORTUNITY_GETDESCRIPTION(RO.OPPORTUNITYID)
    from
      dbo.FINANCIALTRANSACTIONLINEITEM
      inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
    left outer join
      dbo.REVENUEOPPORTUNITY RO on RO.ID = FINANCIALTRANSACTIONLINEITEM.ID
    where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
        and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1;

    select top 1
        @APPLIEDID = MEMBERSHIP.ID,
        @APPLIEDDATE = MEMBERSHIPTRANSACTION.TRANSACTIONDATE,
        @APPLIEDTYPE = dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID),
        @APPLIEDNAME = NF.NAME,
        @GIVENTO = case
                      when MEMBERSHIP.GIVENBYID is null then null
                      else NF.NAME
                   end,
        @APPLIEDEARNEDINCOMEAMOUNT = dbo.UFN_PLEDGE_CALCULATEEARNEDINCOMEAMOUNT(FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID),
        @APPLIEDEARNEDINCOMEBALANCE = dbo.UFN_PLEDGE_CALCULATEEARNEDINCOMEBALANCE(FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID),
        @APPLIEDCONTRIBUTEDINCOMEAMOUNT = dbo.UFN_PLEDGE_CALCULATECONTRIBUTEDINCOMEAMOUNT(FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID),
        @APPLIEDCONTRIBUTEDINCOMEBALANCE = dbo.UFN_PLEDGE_CALCULATECONTRIBUTEDINCOMEBALANCE(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 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;

 --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);

      select @QUALIFICATIONSTATUS = dbo.UFN_RECURRINGGIFTQUALIFICATIONSTATUS(@ID);
    end

  return 0;