USP_DATAFORMTEMPLATE_PLANNEDGIFTPROFILETA_VIEW

The load procedure used by the view dataform template "Planned Gift Profile Target Associates 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.
@VEHICLECODE	tinyint	INOUT	Planned gift vehicle
@SUBTYPE	nvarchar(100)	INOUT	Subtype
@GIFTAMOUNT	money	INOUT	Gift amount
@ISREVOCABLE	bit	INOUT	Revocable
@ISANONYMOUS	bit	INOUT	Anonymous gift
@EXPECTEDMATURITY	UDT_YEAR	INOUT	Expected maturity year
@DISCOUNTRATE	decimal(6, 3)	INOUT	Discount rate
@NETPRESENTVALUE	money	INOUT	Net present value
@NETPRESENTVALUEDATE	datetime	INOUT	As of
@REMAINDERVALUE	money	INOUT	Remainder value
@REMAINDERVALUEDATE	datetime	INOUT	As of
@RECOGNITIONAMOUNT	money	INOUT	Recognition amount
@GIFTDATE	datetime	INOUT	Date
@PAYOUTRATE	decimal(6, 3)	INOUT	Payout percentage
@PAYOUTAMOUNT	money	INOUT	Payout amount
@PAYMENTPERIODSTART	datetime	INOUT	Payment period start date
@PAYMENTPERIODEND	datetime	INOUT	Payment period end date
@PAYMENTFREQUENCY	nvarchar(100)	INOUT	Payment frequency
@TERMTYPE	nvarchar(100)	INOUT	Term type
@TERMENDDATE	datetime	INOUT	Term end date
@TRUSTTAXIDNUMBER	nvarchar(100)	INOUT	Tax ID number
@POOLEDINCOMEFUNDNAME	nvarchar(100)	INOUT	Name
@POOLEDINCOMEFUNDUNITS	int	INOUT	Units
@POOLEDINCOMEFUNDTOTALUNITS	int	INOUT	Total units
@POOLEDINCOMEFUNDPERCENT	decimal(6, 3)	INOUT	PIF percent
@LIFEINSURANCEPREMIUM	money	INOUT	Amount
@LIFEINSURANCEPREMIUMFREQUENCY	nvarchar(100)	INOUT	Frequency
@LIFEINSURANCEPREMIUMDUEDATE	datetime	INOUT	Due date
@PROBATEDATE	datetime	INOUT	Probate date
@DISCOUNTDATE	datetime	INOUT	As of
@REMAINDERMANPERCENT	decimal(6, 3)	INOUT	Remainderman %
@RECEIVEDAMOUNT	money	INOUT	Received amount
@PROBATESTATUSCODE	tinyint	INOUT	Probate status
@ISTESTAMENTARY	bit	INOUT	Testamentary
@ISLIVINGTRUST	bit	INOUT	Living trust
@GIFTVALUEISNOMINAL	bit	INOUT	Gift value is nominal
@ISCONTINGENT	bit	INOUT	Gift is contingent
@ORGISPOLICY	bit	INOUT	Organization is policy
@ORGISBENEFICIARY	bit	INOUT	Organization is beneficiary
@TRUSTHELDOUTSIDE	bit	INOUT	Trust held outside
@TOTALPAYOUT	money	INOUT	Total payout
@REALIZEDVALUE	money	INOUT	Realized value

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_PLANNEDGIFTPROFILETA_VIEW
                (
                    @ID uniqueidentifier,
                    @DATALOADED bit = 0 output,
                    @VEHICLECODE tinyint = null output,
                    @SUBTYPE nvarchar(100) = null output,
                    @GIFTAMOUNT money = null output,
                    @ISREVOCABLE bit = null output,
                    @ISANONYMOUS bit = null output,
                    @EXPECTEDMATURITY dbo.UDT_YEAR = null output,
                    @DISCOUNTRATE decimal(6,3) = null output,
                    @NETPRESENTVALUE money = null output,
                    @NETPRESENTVALUEDATE datetime = null output,
                    @REMAINDERVALUE money = null output,
                    @REMAINDERVALUEDATE datetime = null output,
                    @RECOGNITIONAMOUNT money = null output,
                    @GIFTDATE datetime = null output,
                    @PAYOUTRATE decimal(6,3) = null output,
                    @PAYOUTAMOUNT money = null output,
                    @PAYMENTPERIODSTART datetime = null output,
                    @PAYMENTPERIODEND datetime = null output,
                    @PAYMENTFREQUENCY nvarchar(100) = null output,
                    @TERMTYPE nvarchar(100) = null output,
                    @TERMENDDATE datetime = null output,
                    @TRUSTTAXIDNUMBER nvarchar(100) = null output,
                    @POOLEDINCOMEFUNDNAME nvarchar(100) = null output,
                    @POOLEDINCOMEFUNDUNITS int = null output,
                    @POOLEDINCOMEFUNDTOTALUNITS int = null output,
                    @POOLEDINCOMEFUNDPERCENT decimal(6,3) = null output,
                    @LIFEINSURANCEPREMIUM money = null output,
                    @LIFEINSURANCEPREMIUMFREQUENCY nvarchar(100) = null output,
                    @LIFEINSURANCEPREMIUMDUEDATE datetime = null output,
                    @PROBATEDATE datetime = null output,
                    @DISCOUNTDATE datetime = null output,
                    @REMAINDERMANPERCENT decimal(6,3) = null output,
                    @RECEIVEDAMOUNT money = null output,
                    @PROBATESTATUSCODE tinyint = null output,
                    @ISTESTAMENTARY bit = null output,
                    @ISLIVINGTRUST bit = null output,
                    @GIFTVALUEISNOMINAL bit = null output,
                    @ISCONTINGENT bit = null output,
                    @ORGISPOLICY bit = null output,
                    @ORGISBENEFICIARY bit = null output,
                    @TRUSTHELDOUTSIDE bit = null output,
                    @TOTALPAYOUT money = null output,
                    @REALIZEDVALUE money = null output
                ) as

                    set nocount on;

                    set @DATALOADED = 0;

                    select
                        @DATALOADED = 1,
                        @VEHICLECODE = VEHICLECODE,
                        @SUBTYPE = coalesce(OST.DESCRIPTION, PG.SUBTYPE),
                        @GIFTAMOUNT = GIFTAMOUNT,
                        @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,
                        @PAYMENTFREQUENCY = PAYMENTFREQUENCY,
                        @TERMTYPE = TERMTYPE,
                        @TERMENDDATE = TERMENDDATE,
                        @TRUSTTAXIDNUMBER = TRUSTTAXIDNUMBER,
                        @POOLEDINCOMEFUNDNAME = dbo.UFN_POOLEDINCOMEFUNDCODE_GETDESCRIPTION(POOLEDINCOMEFUNDCODEID),
                        @POOLEDINCOMEFUNDUNITS = POOLEDINCOMEFUNDUNITS,
                        @POOLEDINCOMEFUNDTOTALUNITS = POOLEDINCOMEFUNDTOTALUNITS,
                        @POOLEDINCOMEFUNDPERCENT = POOLEDINCOMEFUNDPERCENT*100,
                        @LIFEINSURANCEPREMIUM = LIFEINSURANCEPREMIUM,
                        @LIFEINSURANCEPREMIUMFREQUENCY = LIFEINSURANCEPREMIUMFREQUENCY,
                        @LIFEINSURANCEPREMIUMDUEDATE = LIFEINSURANCEPREMIUMDUEDATE,
                        @PROBATEDATE = PROBATEDATE,
                        @DISCOUNTDATE = DISCOUNTDATE,
                        @REMAINDERMANPERCENT = REMAINDERMANPERCENT*100,
                        @RECEIVEDAMOUNT = PGTA.RECEIVEDAMOUNT,
                        @PROBATESTATUSCODE = PROBATESTATUSCODE,
                        @ISTESTAMENTARY = ISTESTAMENTARY,
                        @ISLIVINGTRUST = ISLIVINGTRUST,
                        @ISCONTINGENT = ISCONTINGENT,
                        @GIFTVALUEISNOMINAL = GIFTVALUEISNOMINAL,
                        @ORGISBENEFICIARY = ORGISBENEFICIARY,
                        @ORGISPOLICY = ORGISPOLICY,
                        @TRUSTHELDOUTSIDE = TRUSTHELDOUTSIDE,
                        @TOTALPAYOUT = TOTALPAYOUT,
                        @REALIZEDVALUE = REALIZEDVALUE
                    from
                        dbo.PLANNEDGIFT PG
                        inner join dbo.CONSTITUENT C on C.ID=PG.CONSTITUENTID
                        left outer join dbo.PLANNEDGIFTTAINTEGRATION PGTA on PG.ID = PGTA.ID
                        left outer join dbo.PROSPECTPLAN PP on PP.ID=PG.PROSPECTPLANID
                        left join dbo.PLANNEDGIFTOTHERSUBTYPECODE OST on OST.ID = PG.OTHERSUBTYPECODEID
                    where
                        PG.ID = @ID;

                    return 0;