USP_DATAFORMTEMPLATE_PLANNEDGIFTADDITIONDATALISTVIEWDETAIL2

The load procedure used by the view dataform template "Planned Gift Addition 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_PLANNEDGIFTADDITIONDATALISTVIEWDETAIL2 (
                @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 @PLANNEDGIFTADDITIONID uniqueidentifier;
                declare @CURRENCYCODE tinyint;
                declare @CURRENTAPPUSERID uniqueidentifier;

                select
                  @PLANNEDGIFTADDITIONID = 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_PLANNEDGIFTADDITIONREVENUE_GETTOTALAMOUNT(PGA.ID, @CURRENCYCODE, @CURRENCYID),
                    @ISREVOCABLE = ISREVOCABLE,
                    @ISANONYMOUS = ISANONYMOUS,
                    @EXPECTEDMATURITY = EXPECTEDMATURITY,
                    @GIFTDATE = PGA.GIFTDATE,
                    @STATUS = STATUS,
                    @SITES = dbo.UFN_PLANNEDGIFT_GETSITELIST(PG.ID),
                    @DISPLAYCURRENCYID = case @CURRENCYCODE
                                    when 2 then PGA.TRANSACTIONCURRENCYID
                                    when 0 then PGA.BASECURRENCYID
                                    else @CURRENCYID
                                  end,
                    @CAMPAIGNS = (
                        select dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME)
                        from dbo.PLANNEDGIFTADDITIONDESIGNATION
                            inner join dbo.PLANNEDGIFTADDITIONDESIGNATIONCAMPAIGN on PLANNEDGIFTADDITIONDESIGNATION.ID = PLANNEDGIFTADDITIONDESIGNATIONCAMPAIGN.PLANNEDGIFTADDITIONDESIGNATIONID
                            inner join dbo.CAMPAIGN on CAMPAIGN.ID = PLANNEDGIFTADDITIONDESIGNATIONCAMPAIGN.CAMPAIGNID
                        where PLANNEDGIFTADDITIONDESIGNATION.PLANNEDGIFTADDITIONID = PGA.ID
                    )
                from dbo.PLANNEDGIFT PG
                inner join dbo.PLANNEDGIFTADDITION PGA on PG.ID = PGA.PLANNEDGIFTID
                left outer join dbo.PLANNEDGIFTADDITIONREVENUE PGAR on PGAR.ID = PGA.ID
                where
                    PGA.ID = @PLANNEDGIFTADDITIONID;

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

              return 0;