USP_DATAFORMTEMPLATE_PLANNEDGIFT_EDITSAVE_4

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@VEHICLECODE	tinyint	IN	Planned gift vehicle
@SUBTYPECODE	tinyint	IN	Subtype
@EXPECTEDGIFTAMOUNT	money	IN	Gift amount
@ISREVOCABLE	bit	IN	Revocable
@ISANONYMOUS	bit	IN	Anonymous gift
@EXPECTEDMATURITY	UDT_YEAR	IN	Expected maturity year
@DISCOUNTRATE	decimal(6, 3)	IN	Discount rate
@NETPRESENTVALUE	money	IN	Net present value
@NETPRESENTVALUEDATE	datetime	IN	As of
@REMAINDERVALUE	money	IN	Remainder value
@REMAINDERVALUEDATE	datetime	IN	As of
@RECOGNITIONAMOUNT	money	IN	Recognition amount
@GIFTDATE	datetime	IN	Date
@PAYOUTRATE	decimal(6, 3)	IN	Payout percentage
@PAYOUTAMOUNT	money	IN	Payout amount
@PAYMENTPERIODSTART	datetime	IN	Payments start
@PAYMENTPERIODEND	datetime	IN	Payments end
@PAYMENTFREQUENCYCODE	tinyint	IN	Payment frequency
@TERMTYPECODE	tinyint	IN	Term type
@TERMENDDATE	datetime	IN	Term end date
@YEARSINTERM	tinyint	IN	Years in term
@TRUSTTAXIDNUMBER	nvarchar(100)	IN	Tax ID number
@POOLEDINCOMEFUNDCODEID	uniqueidentifier	IN	Name
@POOLEDINCOMEFUNDUNITS	int	IN	Units
@POOLEDINCOMEFUNDTOTALUNITS	int	IN	Total units
@LIFEINSURANCEPREMIUM	money	IN	Amount
@LIFEINSURANCEPREMIUMFREQUENCYCODE	tinyint	IN	Frequency
@LIFEINSURANCEPREMIUMDUEDATE	datetime	IN	Due date
@STATUSCODE	tinyint	IN	Status
@DESIGNATION	xml	IN	Planned Gift Details
@ASSETS	xml	IN	Assets
@BENEFICIARIES	xml	IN	Beneficiaries
@RELATIONSHIPS	xml	IN	Relationships
@PROBATEDATE	datetime	IN	As of
@DISCOUNTDATE	datetime	IN	As of
@REMAINDERMANPERCENT	decimal(6, 3)	IN	Remainderman %
@PROBATESTATUSCODE	tinyint	IN	Probate status
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SITES	xml	IN	Sites
@ISTESTAMENTARY	bit	IN	Testamentary
@OTHERSUBTYPECODEID	uniqueidentifier	IN	Subtype
@ISLIVINGTRUST	bit	IN	Living trust
@GIFTVALUEISNOMINAL	bit	IN	Gift value is nominal
@ISCONTINGENT	bit	IN	Gift is contingent
@ORGISPOLICY	bit	IN	Organization is policy
@ORGISBENEFICIARY	bit	IN	Organization is beneficiary
@TRUSTHELDOUTSIDE	bit	IN	Trust held outside
@TOTALPAYOUT	money	IN	Total payout
@REALIZEDVALUE	money	IN	Realized amount

Definition

Copy



            CREATE procedure dbo.USP_DATAFORMTEMPLATE_PLANNEDGIFT_EDITSAVE_4 (
                @ID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @VEHICLECODE tinyint,
                @SUBTYPECODE tinyint,
                @EXPECTEDGIFTAMOUNT money,
                @ISREVOCABLE bit,
                @ISANONYMOUS bit,
                @EXPECTEDMATURITY dbo.UDT_YEAR,
                @DISCOUNTRATE decimal(6,3),
                @NETPRESENTVALUE money,
                @NETPRESENTVALUEDATE datetime,
                @REMAINDERVALUE money,
                @REMAINDERVALUEDATE datetime,
                @RECOGNITIONAMOUNT money,
                @GIFTDATE datetime,
                @PAYOUTRATE decimal(6,3),
                @PAYOUTAMOUNT money,
                @PAYMENTPERIODSTART datetime,
                @PAYMENTPERIODEND datetime,
                @PAYMENTFREQUENCYCODE tinyint,
                @TERMTYPECODE tinyint,
                @TERMENDDATE datetime,
                @YEARSINTERM tinyint,
                @TRUSTTAXIDNUMBER nvarchar(100),
                @POOLEDINCOMEFUNDCODEID uniqueidentifier,
                @POOLEDINCOMEFUNDUNITS int,
                @POOLEDINCOMEFUNDTOTALUNITS int,
                @LIFEINSURANCEPREMIUM money,
                @LIFEINSURANCEPREMIUMFREQUENCYCODE tinyint,
                @LIFEINSURANCEPREMIUMDUEDATE datetime,
                @STATUSCODE tinyint,
                @DESIGNATION xml,
                @ASSETS xml,
                @BENEFICIARIES xml,
                @RELATIONSHIPS xml,
                @PROBATEDATE datetime,
                @DISCOUNTDATE datetime,
                @REMAINDERMANPERCENT decimal(6,3),
                @PROBATESTATUSCODE tinyint,
                @CURRENTAPPUSERID uniqueidentifier,
                @SITES xml,
                @ISTESTAMENTARY bit,
                @OTHERSUBTYPECODEID uniqueidentifier,
                @ISLIVINGTRUST bit,
                @GIFTVALUEISNOMINAL bit,
                @ISCONTINGENT bit,
                @ORGISPOLICY bit,
                @ORGISBENEFICIARY bit,
                @TRUSTHELDOUTSIDE bit,
                @TOTALPAYOUT money,
                @REALIZEDVALUE money
            ) as

                set nocount on;

                if @SITES is null begin 
                    if dbo.UFN_SITEREQUIREDFORUSER(@CURRENTAPPUSERID) = 1 begin
                        raiserror('Site is required.',13,1)
                        return
                    end
                end

                exec dbo.USP_PLANNEDGIFTSITE_VALIDATESITES @SITES;

                if @ID is null
                    set @ID = newid();

                if @CHANGEAGENTID is null
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                if @SUBTYPECODE is null
                    set @SUBTYPECODE = 0;

                declare @CHANGEDATE datetime
                set @CHANGEDATE = getdate()

                begin try

                    declare @OLDINACTIVEDESIGNATIONTABLE table (
                      DESIGNATIONID uniqueidentifier
                    );

                    declare @DESIGNATIONS table (
                      DESIGNATIONID uniqueidentifier
                    );

                    insert into @OLDINACTIVEDESIGNATIONTABLE(DESIGNATIONID)
                      select DESIGNATIONID 
                      from dbo.UFN_PLANNEDGIFT_DESIGNATION(@ID)

                    insert into @DESIGNATIONS 
                      select DESIGNATIONID 
                      from dbo.UFN_PLANNEDGIFT_DESIGNATION_FROMITEMLISTXML(@DESIGNATION);

                    if (select count(*)
                          from @DESIGNATIONS [DES]
                          inner join dbo.DESIGNATION on [DES].DESIGNATIONID = DESIGNATION.ID
                          where DESIGNATION.ISACTIVE = 0
                          and [DES].DESIGNATIONID not in (
                            select DESIGNATIONID from @OLDINACTIVEDESIGNATIONTABLE)) > 0
                      raiserror('Revenue cannot be added to inactive designations.', 13, 2);


                    update dbo.PLANNEDGIFT set
                        VEHICLECODE = @VEHICLECODE,
                        SUBTYPECODE = @SUBTYPECODE,
                        EXPECTEDGIFTAMOUNT = @EXPECTEDGIFTAMOUNT,
                        ISREVOCABLE = @ISREVOCABLE,
                        ISANONYMOUS = @ISANONYMOUS,
                        EXPECTEDMATURITY = @EXPECTEDMATURITY,
                        DISCOUNTRATE = @DISCOUNTRATE/100,
                        NETPRESENTVALUE = @NETPRESENTVALUE,
                        NETPRESENTVALUEDATE = @NETPRESENTVALUEDATE,
                        REMAINDERVALUE = @REMAINDERVALUE,
                        REMAINDERVALUEDATE = @REMAINDERVALUEDATE,
                        RECOGNITIONAMOUNT = @RECOGNITIONAMOUNT,
                        GIFTDATE = @GIFTDATE,
                        PAYOUTRATE = @PAYOUTRATE/100,
                        PAYOUTAMOUNT = @PAYOUTAMOUNT,
                        PAYMENTPERIODSTART = @PAYMENTPERIODSTART,
                        PAYMENTPERIODEND = @PAYMENTPERIODEND,
                        PAYMENTFREQUENCYCODE = @PAYMENTFREQUENCYCODE,
                        TERMTYPECODE = @TERMTYPECODE,
                        TERMENDDATE = @TERMENDDATE,
                        YEARSINTERM = @YEARSINTERM,
                        TRUSTTAXIDNUMBER = @TRUSTTAXIDNUMBER,
                        POOLEDINCOMEFUNDCODEID = @POOLEDINCOMEFUNDCODEID,
                        POOLEDINCOMEFUNDUNITS = @POOLEDINCOMEFUNDUNITS,
                        POOLEDINCOMEFUNDTOTALUNITS = @POOLEDINCOMEFUNDTOTALUNITS,
                        LIFEINSURANCEPREMIUM = @LIFEINSURANCEPREMIUM,
                        LIFEINSURANCEPREMIUMFREQUENCYCODE = @LIFEINSURANCEPREMIUMFREQUENCYCODE,
                        LIFEINSURANCEPREMIUMDUEDATE = @LIFEINSURANCEPREMIUMDUEDATE,
                        STATUSCODE = @STATUSCODE,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CHANGEDATE,
                        PROBATEDATE = @PROBATEDATE,
                        DISCOUNTDATE = @DISCOUNTDATE,
                        REMAINDERMANPERCENT = @REMAINDERMANPERCENT/100,
                        PROBATESTATUSCODE = @PROBATESTATUSCODE,
                        ISTESTAMENTARY = @ISTESTAMENTARY,
                        OTHERSUBTYPECODEID = @OTHERSUBTYPECODEID,
                        ISLIVINGTRUST = @ISLIVINGTRUST,
                        GIFTVALUEISNOMINAL = @GIFTVALUEISNOMINAL,
                        ISCONTINGENT = @ISCONTINGENT,
                        ORGISPOLICY = @ORGISPOLICY,
                        ORGISBENEFICIARY = @ORGISBENEFICIARY,
                        TRUSTHELDOUTSIDE = @TRUSTHELDOUTSIDE,
                        TOTALPAYOUT = @TOTALPAYOUT,
                        REALIZEDVALUE = case dbo.UFN_PLANNEDGIFT_AUTOCALCULATE_REALIZEDAMOUNT(@ID)
                                            when 0 then @REALIZEDVALUE
                                            when 1 then REALIZEDVALUE
                                        end
                    where
                        ID = @ID;

                    declare @DESIGNATIONSCHANGED bit
                    set @DESIGNATIONSCHANGED = dbo.UFN_PLANNEDGIFT_DESIGNATIONSCHANGED(@ID, @DESIGNATION);

                    exec dbo.USP_PLANNEDGIFT_DESIGNATION_UPDATEFROMXML @ID, @DESIGNATION, @CHANGEAGENTID, @CHANGEDATE;
                    exec dbo.USP_PLANNEDGIFT_ASSETS_UPDATEFROMXML @ID, @ASSETS, @CHANGEAGENTID, @CHANGEDATE;
                    exec dbo.USP_PLANNEDGIFT_BENEFICIARIES_UPDATEFROMXML @ID, @BENEFICIARIES, @CHANGEAGENTID, @CHANGEDATE;
                    exec dbo.USP_PLANNEDGIFT_RELATIONSHIPS_UPDATEFROMXML @ID, @RELATIONSHIPS, @CHANGEAGENTID, @CHANGEDATE;

                    if @DESIGNATIONSCHANGED = 1
                    begin
                        exec dbo.USP_PLANNEDGIFT_CLEARANDADDCAMPAIGNS @PLANNEDGIFTID = @ID,
                            @CHANGEAGENTID = @CHANGEAGENTID,
                            @CHANGEDATE = @CHANGEDATE;                                
                    end

                    exec dbo.USP_PLANNEDGIFT_GETSITES_UPDATEFROMXML @ID, @SITES, @CHANGEAGENTID, @CHANGEDATE;
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;