USP_DATAFORMTEMPLATE_PLANNEDGIFT_VIEW

The load procedure used by the view dataform template "Planned Gift 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	Original 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	As of
@DISCOUNTDATE	datetime	INOUT	As of
@REMAINDERMANPERCENT	decimal(7, 4)	INOUT	Remainderman %
@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 amount
@AUTOCALCULATEREALIZEDAMOUNT	bit	INOUT	Auto-calculate realized amount
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT	Transaction currency ID
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID
@TRIGGERDATE	datetime	INOUT	Trigger date
@TOTALGIFTAMOUNT	money	INOUT	Total gift amount
@ISFLIP	bit	INOUT	FLIP situation
@TRIGGEREVENT	nvarchar(100)	INOUT	Trigger event
@EXCL_RATIO	decimal(6, 3)	INOUT	Exclusion ratio
@EXCL_EXPDATE	datetime	INOUT	Expiration date
@STATUS	nvarchar(100)	INOUT	Status
@LIFEINSURANCEPOLICYNUMBER	nvarchar(50)	INOUT	Policy number
@LIFEINSURANCEDATEISSUED	datetime	INOUT	Issue date
@LIFEINSURANCEFACEVALUE	money	INOUT	Face value
@LIFEINSURANCEDOESINCLUDEDIVIDENDPARTICIPATION	bit	INOUT	Dividend participation
@LIFEINSURANCEISLOANALLOWED	bit	INOUT	Loan is allowed
@LIFEINSURANCEOUTSTANDINGLOANAMOUNT	money	INOUT	Outstanding loan amount

Definition

Copy


        CREATE procedure dbo.USP_DATAFORMTEMPLATE_PLANNEDGIFT_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(7,4) = 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,
                    @AUTOCALCULATEREALIZEDAMOUNT bit = null output,
                    @TRANSACTIONCURRENCYID uniqueidentifier = null output,
                    @BASECURRENCYID uniqueidentifier = null output,
                    @TRIGGERDATE datetime = null output,
                    @TOTALGIFTAMOUNT money = null output,
                    @ISFLIP bit = null output,
                    @TRIGGEREVENT nvarchar(100) = null output,
                    @EXCL_RATIO decimal(6,3) = null output,
                    @EXCL_EXPDATE datetime = null output,
                    @STATUS nvarchar(100) = null output,
                    @LIFEINSURANCEPOLICYNUMBER nvarchar(50) = null output,
                    @LIFEINSURANCEDATEISSUED datetime = null output,
                    @LIFEINSURANCEFACEVALUE money = null output,
                    @LIFEINSURANCEDOESINCLUDEDIVIDENDPARTICIPATION bit = null output,
                    @LIFEINSURANCEISLOANALLOWED bit = null output,
                    @LIFEINSURANCEOUTSTANDINGLOANAMOUNT money = null output
        )
        as
                    set nocount on;
                    set @DATALOADED = 0;

                    select
                        @DATALOADED = 1,
                        @VEHICLECODE = PG.VEHICLECODE,
                        @SUBTYPE = coalesce(OST.DESCRIPTION, PG.SUBTYPE),
                        @GIFTAMOUNT = PG.TRANSACTIONGIFTAMOUNT,
                        @ISREVOCABLE = PG.ISREVOCABLE,
                        @ISANONYMOUS = PG.ISANONYMOUS,
                        @EXPECTEDMATURITY = PG.EXPECTEDMATURITY,
                        @DISCOUNTRATE = PG.DISCOUNTRATE*100,
                        @NETPRESENTVALUE = PG.TRANSACTIONNETPRESENTVALUE,
                        @NETPRESENTVALUEDATE = PG.NETPRESENTVALUEDATE,
                        @REMAINDERVALUE = PG.TRANSACTIONREMAINDERVALUE,
                        @REMAINDERVALUEDATE = PG.REMAINDERVALUEDATE,
                        @RECOGNITIONAMOUNT = PG.TRANSACTIONRECOGNITIONAMOUNT,
                        @GIFTDATE = PG.GIFTDATE,
            @PAYOUTRATE = PG.PAYOUTRATE * 100,
                        @PAYOUTAMOUNT = PG.TRANSACTIONPAYOUTAMOUNT,
                        @PAYMENTPERIODSTART = PG.PAYMENTPERIODSTART,
                        @PAYMENTPERIODEND = PG.PAYMENTPERIODEND,
                        @PAYMENTFREQUENCY = PG.PAYMENTFREQUENCY,
                        @TERMTYPE = PG.TERMTYPE,
                        @TERMENDDATE = PG.TERMENDDATE,
                        @TRUSTTAXIDNUMBER = PG.TRUSTTAXIDNUMBER,
                        @POOLEDINCOMEFUNDNAME = dbo.UFN_POOLEDINCOMEFUNDCODE_GETDESCRIPTION(PG.POOLEDINCOMEFUNDCODEID),
                        @POOLEDINCOMEFUNDUNITS = PG.POOLEDINCOMEFUNDUNITS,
                        @POOLEDINCOMEFUNDTOTALUNITS = PG.POOLEDINCOMEFUNDTOTALUNITS,
                        @POOLEDINCOMEFUNDPERCENT = PG.POOLEDINCOMEFUNDPERCENT*100,
                        @LIFEINSURANCEPREMIUM = PG.TRANSACTIONLIFEINSURANCEPREMIUM,
                        @LIFEINSURANCEPREMIUMFREQUENCY = PG.LIFEINSURANCEPREMIUMFREQUENCY,
                        @LIFEINSURANCEPREMIUMDUEDATE = PG.LIFEINSURANCEPREMIUMDUEDATE,
                        @PROBATEDATE = PG.PROBATEDATE,
                        @DISCOUNTDATE = PG.DISCOUNTDATE,
                        @REMAINDERMANPERCENT = PG.REMAINDERMANPERCENT*100,
                        @PROBATESTATUSCODE = PG.PROBATESTATUSCODE,
                        @ISTESTAMENTARY = PG.ISTESTAMENTARY,
                        @ISLIVINGTRUST = PG.ISLIVINGTRUST,
                        @ISCONTINGENT = PG.ISCONTINGENT,
                        @GIFTVALUEISNOMINAL = PG.GIFTVALUEISNOMINAL,
                        @ORGISBENEFICIARY = PG.ORGISBENEFICIARY,
                        @ORGISPOLICY = PG.ORGISPOLICY,
                        @TRUSTHELDOUTSIDE = PG.TRUSTHELDOUTSIDE,
                        @TOTALPAYOUT = PG.TRANSACTIONTOTALPAYOUT,
                        @REALIZEDVALUE = dbo.UFN_PLANNEDGIFT_GETTRANSACTIONREALIZEDAMOUNT(@ID),
            @AUTOCALCULATEREALIZEDAMOUNT = dbo.UFN_PLANNEDGIFT_AUTOCALCULATE_REALIZEDAMOUNT(@ID),
                        @TRANSACTIONCURRENCYID = PG.TRANSACTIONCURRENCYID,
                        @BASECURRENCYID = PG.BASECURRENCYID,
                        @TRIGGERDATE = PG.TRIGGERDATE,
                        @ISFLIP = PG.ISFLIP,
                        @TRIGGEREVENT = TET.DESCRIPTION,
                        @TOTALGIFTAMOUNT = dbo.UFN_PLANNEDGIFT_GETTOTALAMOUNT(@ID, 2, PG.TRANSACTIONCURRENCYID),
                        @EXCL_RATIO = PG.EXCL_RATIO,
                        @EXCL_EXPDATE = PG.EXCL_EXPDATE,
                        @STATUS = PG.STATUS,
                        @LIFEINSURANCEPOLICYNUMBER = PG.LIFEINSURANCEPOLICYNUMBER,
                        @LIFEINSURANCEDATEISSUED = PG.LIFEINSURANCEDATEISSUED,
                        @LIFEINSURANCEFACEVALUE = PG.TRANSACTIONLIFEINSURANCEFACEVALUE,
                        @LIFEINSURANCEDOESINCLUDEDIVIDENDPARTICIPATION = PG.LIFEINSURANCEDOESINCLUDEDIVIDENDPARTICIPATION,
                        @LIFEINSURANCEISLOANALLOWED = PG.LIFEINSURANCEISLOANALLOWED,
                        @LIFEINSURANCEOUTSTANDINGLOANAMOUNT = PG.TRANSACTIONLIFEINSURANCEOUTSTANDINGLOANAMOUNT
          from dbo.PLANNEDGIFT PG
                        inner join dbo.CONSTITUENT C on C.ID=PG.CONSTITUENTID
                        left outer join dbo.PROSPECTPLAN PP on PP.ID=PG.PROSPECTPLANID
                        left join dbo.PLANNEDGIFTOTHERSUBTYPECODE OST on OST.ID = PG.OTHERSUBTYPECODEID
                        left join dbo.PLANNEDGIFTTRIGGEREVENTCODE TET on TET.ID = PG.TRIGGEREVENTCODEID
          where PG.ID = @ID;

                    return 0;