USP_DATAFORMTEMPLATE_EDITLOAD_PLANNEDGIFTREVENUE

The load procedure used by the edit dataform template "Planned Gift Revenue Edit 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.
@CONSTITUENTID	uniqueidentifier	INOUT
@CONSTITUENTNAME	nvarchar(700)	INOUT	Constituent
@DATE	datetime	INOUT	Date
@AMOUNT	money	INOUT	Amount
@POSTSTATUSCODE	tinyint	INOUT	Post status
@POSTDATE	datetime	INOUT	Post date
@SPLITS	xml	INOUT	Designations
@FINDERNUMBER	bigint	INOUT	Finder number
@SOURCECODE	nvarchar(50)	INOUT	Source code
@APPEALID	uniqueidentifier	INOUT	Appeal
@BENEFITS	xml	INOUT	Benefits
@BENEFITSWAIVED	bit	INOUT	Benefits waived
@GIVENANONYMOUSLY	bit	INOUT	Planned gift is anonymous
@MAILINGID	uniqueidentifier	INOUT	Effort
@CHANNELCODEID	uniqueidentifier	INOUT	Inbound channel
@DONOTACKNOWLEDGE	bit	INOUT	Do not acknowledge
@PLEDGESUBTYPEID	uniqueidentifier	INOUT	Subtype
@MAXSOFTCREDITAMOUNT	money	INOUT	Max soft credit amount
@MAXSOLICITORAMOUNT	money	INOUT	Max solicitor amount
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@MAINSINGLEDESIGNATIONID	uniqueidentifier	INOUT	Designation
@FINDERNUMBERVALID	bit	INOUT	FINDERNUMBERVALID
@OPPORTUNITYID	uniqueidentifier	INOUT	Opportunity
@OPPORTUNITYCONSTITUENTNAME	nvarchar(154)	INOUT
@OPPORTUNITYASKDATE	datetime	INOUT
@OPPORTUNITYASKAMOUNT	money	INOUT
@MAXSOLICITORSPLITAMOUNTS	xml	INOUT	Max solicitor split amounts
@REFERENCE	nvarchar(255)	INOUT	Reference
@CATEGORYCODEID	uniqueidentifier	INOUT	Revenue category
@OPPORTUNITYSTATUS	nvarchar(32)	INOUT
@PERCENTAGEBENEFITS	xml	INOUT	Benefits
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Transaction currency
@BASEEXCHANGERATEID	uniqueidentifier	INOUT	Exchange rate ID
@EXCHANGERATE	decimal(20, 8)	INOUT	Exchange rate
@HADSPOTRATE	bit	INOUT	Had spot rate
@RATECHANGED	bit	INOUT	Rate changed
@BASEDECIMALDIGITS	tinyint	INOUT	Decimal digits
@BASEROUNDINGTYPECODE	tinyint	INOUT	Rounding type
@TRANSACTIONCURRENCYDESCRIPTION	nvarchar(110)	INOUT	Transaction currency description
@HASPOSTEDPAYMENTS	bit	INOUT	Has posted payments
@DONOTRECEIPT	bit	INOUT	Do not receipt
@DONOTRECEIPT_LOCKED	bit	INOUT
@RECEIPTAMOUNT	money	INOUT	Receipt amount
@ALLOWGLDISTRIBUTIONS	bit	INOUT
@UPDATESOLICITORS	bit	INOUT

Definition

Copy



                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_PLANNEDGIFTREVENUE
                    (
                        @ID uniqueidentifier,
                        @DATALOADED bit = 0 output,
                        @CONSTITUENTID uniqueidentifier = null output,
                        @CONSTITUENTNAME nvarchar(700) = null output,
                        @DATE datetime = null output,
                        @AMOUNT money = null output,
                        @POSTSTATUSCODE tinyint = null output,
                        @POSTDATE datetime = null output,
                        @SPLITS xml = null output,
                        @FINDERNUMBER bigint = null output,
                        @SOURCECODE nvarchar(50) = null output,
                        @APPEALID uniqueidentifier = null output,
                        @BENEFITS xml = null output,
                        @BENEFITSWAIVED bit = null output,
                        @GIVENANONYMOUSLY bit = null output,
                        @MAILINGID uniqueidentifier = null output,
                        @CHANNELCODEID uniqueidentifier = null output,
                        @DONOTACKNOWLEDGE bit = null output,
                        @PLEDGESUBTYPEID uniqueidentifier = null output,
                        @MAXSOFTCREDITAMOUNT money = null output,
                        @MAXSOLICITORAMOUNT money = null output,
                        @TSLONG bigint = 0 output,
                        @MAINSINGLEDESIGNATIONID uniqueidentifier = null output,
                        @FINDERNUMBERVALID bit = null output,
                        @OPPORTUNITYID uniqueidentifier = null output,
                        @OPPORTUNITYCONSTITUENTNAME nvarchar(154) = null output,
                        @OPPORTUNITYASKDATE datetime = null output,
                        @OPPORTUNITYASKAMOUNT money = null output,
                        @MAXSOLICITORSPLITAMOUNTS xml = null output,
                        @REFERENCE nvarchar(255) = null output,
                        @CATEGORYCODEID uniqueidentifier = null output,
                        @OPPORTUNITYSTATUS nvarchar(32) = null output,
                        @PERCENTAGEBENEFITS xml = null output,
                        @BASECURRENCYID uniqueidentifier = null output,
                        @TRANSACTIONCURRENCYID uniqueidentifier = null output,
                        @BASEEXCHANGERATEID uniqueidentifier = null output,
                        @EXCHANGERATE decimal(20,8) = null output,
                        @HADSPOTRATE bit = null output,
                        @RATECHANGED bit = null output,
                        @BASEDECIMALDIGITS tinyint = null output,
                        @BASEROUNDINGTYPECODE tinyint = null output,
                        @TRANSACTIONCURRENCYDESCRIPTION nvarchar(110) = null output,
                        @HASPOSTEDPAYMENTS bit = null output,
                        @DONOTRECEIPT bit = null output,
                        @DONOTRECEIPT_LOCKED bit = null output,
                        @RECEIPTAMOUNT money = null output,
                        @ALLOWGLDISTRIBUTIONS bit = null output,
                        @UPDATESOLICITORS bit = null output
                    )
                    as
                    begin
                        set nocount on;

                        set @DATALOADED = 0;
                        set @TSLONG = 0;

                        declare @LASTINSTALLMENT uniqueidentifier;

                        select 
                            @DATALOADED = 1,
                            @CONSTITUENTID = REVENUE.CONSTITUENTID,
                            @CONSTITUENTNAME = NF.NAME,
                            @DATE = REVENUE.DATE,
                            @AMOUNT = REVENUE.TRANSACTIONAMOUNT,
                            @POSTSTATUSCODE = 
                                case 
                                    when REVENUE.DONOTPOST = 1 
                                        then 2 
                                    when REVENUEPOSTED.ID is not null 
                                        then 0 
                                    else 1 
                                end,
                            @MAXSOFTCREDITAMOUNT = 0,
                            @MAXSOLICITORAMOUNT = 0,
                            @POSTDATE = REVENUE.POSTDATE,
                            @TSLONG = REVENUE.TSLONG,
                            @FINDERNUMBER = FINDERNUMBER,
                            @SOURCECODE = SOURCECODE,
                            @APPEALID = APPEALID,
                            @BENEFITSWAIVED = BENEFITSWAIVED,
                            @GIVENANONYMOUSLY = GIVENANONYMOUSLY,
                            @MAILINGID = MAILINGID,
                            @CHANNELCODEID = CHANNELCODEID,
                            @DONOTACKNOWLEDGE = DONOTACKNOWLEDGE,
                            @PLEDGESUBTYPEID = PLEDGESUBTYPEID,
                            @MAXSOLICITORSPLITAMOUNTS = dbo.UFN_REVENUEDETAIL_GETMAXSOLICITORSPLITAMOUNTS_TOITEMLISTXML(@ID),
                            @REFERENCE = REVENUEREFERENCE.REFERENCE,
                            @CATEGORYCODEID = (
                                select top 1 
                                    GLREVENUECATEGORYMAPPINGID 
                                from dbo.REVENUECATEGORY 
                                    inner join dbo.REVENUESPLIT on REVENUECATEGORY.ID = REVENUESPLIT.ID 
                                where REVENUEID = REVENUE.ID
                            ),
                            @BASECURRENCYID = REVENUE.BASECURRENCYID,
                            @TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
                            @BASEEXCHANGERATEID = REVENUE.BASEEXCHANGERATEID,
                            @EXCHANGERATE = CURRENCYEXCHANGERATE.RATE,
                            @HADSPOTRATE =  
                                case
                                    when CURRENCYEXCHANGERATE.TYPECODE = 2
                                        then 1
                                    else 0
                                end,
                            @RATECHANGED = 0,
                            @BASEDECIMALDIGITS = CURRENCY.DECIMALDIGITS,
                            @BASEROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE,
                            @TRANSACTIONCURRENCYDESCRIPTION = dbo.UFN_CURRENCY_GETDESCRIPTION(REVENUE.TRANSACTIONCURRENCYID),
                            @DONOTRECEIPT = REVENUE.DONOTRECEIPT,
                            @DONOTRECEIPT_LOCKED =
                                case when REVENUE.DONOTRECEIPT != 0
                                    and (
                                        PLANNEDGIFT.ISREVOCABLE != 0
                                        or PLANNEDGIFT.TRUSTHELDOUTSIDE != 0
                                        or dbo.UFN_PLANNEDGIFT_ISRECEIPTABLE(PLANNEDGIFT.VEHICLECODE) = 0
                                    )
                                    then 1
                                    else 0
                                end,
                            @RECEIPTAMOUNT = REVENUE.RECEIPTAMOUNT,
                            @UPDATESOLICITORS = 0
                        from dbo.REVENUE
                            inner join dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
                            cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUE.CONSTITUENTID) NF
                            inner join dbo.CURRENCY on CURRENCY.ID = REVENUE.BASECURRENCYID
                            left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
                            left join dbo.REVENUEREFERENCE on REVENUEREFERENCE.ID = REVENUE.ID
                            left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = REVENUE.BASEEXCHANGERATEID
                            left join dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PLANNEDGIFTREVENUE on REVENUE.ID = PLANNEDGIFTREVENUE.REVENUEID
                            left join dbo.PLANNEDGIFT on PLANNEDGIFTREVENUE.PLANNEDGIFTID = PLANNEDGIFT.ID
                        where REVENUE.ID = @ID and REVENUE.TRANSACTIONTYPECODE = 4;

                        if @DATALOADED = 1
                        begin

                            select top 1
                                @OPPORTUNITYID = RO.OPPORTUNITYID,
                                @OPPORTUNITYCONSTITUENTNAME = OC.NAME,
                                @OPPORTUNITYASKDATE = O.ASKDATE,
                                @OPPORTUNITYASKAMOUNT = O.ASKAMOUNT,
                                @OPPORTUNITYSTATUS = O.STATUS
                            from dbo.REVENUESPLIT
                                left outer join dbo.REVENUEOPPORTUNITY RO on RO.ID = REVENUESPLIT.ID
                                left outer join dbo.OPPORTUNITY O on O.ID = RO.OPPORTUNITYID
                                left outer join dbo.PROSPECTPLAN PP on PP.ID = O.PROSPECTPLANID
                                left outer join dbo.CONSTITUENT OC on OC.ID = PP.PROSPECTID
                            where REVENUESPLIT.REVENUEID = @ID

                            --Multicurrency - AdamBu 5/21/10 - Break apart the splits and build our own XML so that

                            --    TRANSACTIONAMOUNT can be passed as AMOUNT.

                            set @SPLITS = (
                                select 
                                    SPLITS.TRANSACTIONAMOUNT AMOUNT, 
                                    SPLITS.APPLICATIONCODE, 
                                    SPLITS.CATEGORYCODEID, 
                                    SPLITS.DECLINESGIFTAID, 
                                    SPLITS.DESIGNATIONID, 
                                    SPLITS.ID, 
                                    SPLITS.TYPECODE,
                                    SPLITS.TRANSACTIONCURRENCYID
                                from dbo.UFN_REVENUE_GETSPLITS_2(@ID) SPLITS
                                    inner join dbo.DESIGNATION on DESIGNATION.ID = SPLITS.DESIGNATIONID
                                for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64
                            );
                            exec dbo.USP_REVENUE_GETBENEFITSSPLIT @ID, @BENEFITS output, @PERCENTAGEBENEFITS output

                            if @FINDERNUMBER = 0
                                set @FINDERNUMBER = null;

                            if (not @FINDERNUMBER is null)
                                set @FINDERNUMBERVALID = 1;
                            else
                                set @FINDERNUMBERVALID = 0;

                            set @HASPOSTEDPAYMENTS = dbo.UFN_PLEDGEHASPOSTEDPAYMENTS(@ID)                                

                        end

                        -- Check GL business rule for this account system and set to 'Do not post' if needed.

                        declare @PDACCOUNTSYSTEMID uniqueidentifier;
                        select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID from dbo.PDACCOUNTSYSTEMFORREVENUE where ID = @ID;
                        set @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);

                        return 0;

                    end