USP_DATAFORMTEMPLATE_PLANNEDGIFTDATALISTVIEWDETAIL2

The load procedure used by the view dataform template "Planned Gift Datalist View Form 2"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	nvarchar(75)	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.
@VEHICLECODE	tinyint	INOUT	Planned gift vehicle
@GIFTAMOUNT	money	INOUT	Gift amount
@ISREVOCABLE	bit	INOUT	Revocable
@ISANONYMOUS	bit	INOUT	Anonymous gift
@EXPECTEDMATURITY	UDT_YEAR	INOUT	Expected maturity year
@GIFTDATE	datetime	INOUT	Date
@STATUS	nvarchar(16)	INOUT	Status
@SITES	nvarchar(1024)	INOUT	Site
@DISPLAYCURRENCYID	uniqueidentifier	INOUT	Display currency ID
@DESIGNATIONS	xml	INOUT	Designations
@CAMPAIGNS	nvarchar(max)	INOUT	Campaigns

Definition

Copy



        CREATE procedure dbo.USP_DATAFORMTEMPLATE_PLANNEDGIFTDATALISTVIEWDETAIL2 (
          @ID nvarchar(75),
          @DATALOADED bit = 0 output,
          @VEHICLECODE tinyint = null output,
          @GIFTAMOUNT money = null output,
          @ISREVOCABLE bit = null output,
          @ISANONYMOUS bit = null output,
          @EXPECTEDMATURITY dbo.UDT_YEAR = null output,
          @GIFTDATE datetime = null output,
          @STATUS nvarchar(16) = null output,
          @SITES nvarchar(1024) = null output,
          @DISPLAYCURRENCYID uniqueidentifier = null output,
          @DESIGNATIONS xml = null output,
          @CAMPAIGNS nvarchar(max) = null output
        ) as

          set nocount on;

          set @DATALOADED = 0;

          declare @PLANNEDGIFTID uniqueidentifier;
          declare @CURRENCYCODE tinyint;
          declare @CURRENTAPPUSERID uniqueidentifier;

          select
            @PLANNEDGIFTID = cast(substring(@ID,1,36) as uniqueidentifier),
            @CURRENCYCODE = cast(substring(@ID,38,1) as tinyint),
            @CURRENTAPPUSERID = cast(substring(@ID,40,36) as uniqueidentifier)

          declare @CURRENCYID uniqueidentifier;

          declare @MULTICURRENCYENABLED bit;
          set @MULTICURRENCYENABLED = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency'); 
          if @MULTICURRENCYENABLED = 0 
            set @CURRENCYCODE = 1;

          if @CURRENCYCODE = 1
            set @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

          if @CURRENCYCODE = 3
          begin
            set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);
            if @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
            begin     
              set @CURRENCYCODE = 1
            end
          end;            

          select
              @DATALOADED = 1,
              @VEHICLECODE = VEHICLECODE,
              @GIFTAMOUNT = dbo.UFN_PLANNEDGIFTREVENUE_GETTOTALAMOUNT(PG.ID, @CURRENCYCODE, @CURRENCYID),
              @ISREVOCABLE = ISREVOCABLE,
              @ISANONYMOUS = ISANONYMOUS,
              @EXPECTEDMATURITY = EXPECTEDMATURITY,
              @GIFTDATE = GIFTDATE,
              @STATUS = STATUS,
              @SITES = dbo.UFN_PLANNEDGIFT_GETSITELIST(PG.ID),
              @DISPLAYCURRENCYID = case @CURRENCYCODE
                              when 2 then PG.TRANSACTIONCURRENCYID
                              when 0 then PG.BASECURRENCYID
                              else @CURRENCYID
                            end,
            @CAMPAIGNS = (
                select dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME)
                from dbo.PLANNEDGIFTDESIGNATION
                    inner join dbo.PLANNEDGIFTDESIGNATIONCAMPAIGN on PLANNEDGIFTDESIGNATION.ID = PLANNEDGIFTDESIGNATIONCAMPAIGN.PLANNEDGIFTDESIGNATIONID
                    inner join dbo.CAMPAIGN on CAMPAIGN.ID = PLANNEDGIFTDESIGNATIONCAMPAIGN.CAMPAIGNID
                where PLANNEDGIFTDESIGNATION.PLANNEDGIFTID = PG.ID
            )
        from dbo.PLANNEDGIFT PG    
        left outer join dbo.PLANNEDGIFTREVENUE PGR on PGR.ID = PG.ID
        where
            PG.ID = @PLANNEDGIFTID;

        set @DESIGNATIONS = 
          (select 
            PLANNEDGIFTDESIGNATION.ID,
            coalesce(DESIGNATION.VANITYNAME, DESIGNATION.NAME) NAME
              from dbo.PLANNEDGIFTDESIGNATION
          inner join dbo.DESIGNATION on DESIGNATION.ID = PLANNEDGIFTDESIGNATION.DESIGNATIONID
          where PLANNEDGIFTDESIGNATION.PLANNEDGIFTID = @PLANNEDGIFTID
          for xml raw('ITEM'),type,elements,root('DESIGNATIONS'),BINARY BASE64)

        return 0;