Primary Key Field Type
 ID uniqueidentifier

Field Field Type Null Notes Description
 VEHICLECODE tinyint   Default = 0 0=Charitable gift annuity, 1=Charitable remainder unitrust, 2=Charitable remainder annuity trust, 3=Charitable lead unitrust, 4=Charitable lead annuity trust, 5=Pooled income fund, 6=Life insurance, 7=Retained life estate, 8=Bargain sale, 9=Outright gift, 10=Bequest, 11=Retirement plan assets, 12=Testamentary charitable trust, 13=Other, 14=Living trust
 SUBTYPECODE tinyint   Default = 0 0=, 1=Deferred, 2=Flexible, 3=Commuted payment
 EXPECTEDMATURITY UDT_YEAR   Default = ((0))
 ISREVOCABLE bit   Default = 0
 ISANONYMOUS bit   Default = 0
 EXPECTEDGIFTAMOUNT money   Default = 0
 GIFTAMOUNT money (Computed) case when dbo.UFN_PLANNEDGIFT_DESIGNATIONAMOUNT(ID)>0 then dbo.UFN_PLANNEDGIFT_DESIGNATIONAMOUNT(ID) else EXPECTEDGIFTAMOUNT end
 GIFTDATE datetime
 RECOGNITIONAMOUNT money   Default = 0
 NETPRESENTVALUE money   Default = 0
 NETPRESENTVALUEDATE datetime
 REMAINDERVALUE money   Default = 0
 REMAINDERVALUEDATE datetime
 DISCOUNTRATE decimal(6, 5)   Default = 0
 PAYOUTRATE decimal(6, 5)   Default = 0
 PAYOUTAMOUNT money   Default = 0
 PAYMENTPERIODSTART datetime
 PAYMENTPERIODEND datetime
 PAYMENTFREQUENCYCODE tinyint   Default = 0 0=Unspecified, 1=Annually, 2=Semiannually, 3=Quarterly, 4=Bimonthly, 5=Monthly, 6=Semimonthly, 7=Biweekly, 8=Weekly
 TERMTYPECODE tinyint   Default = 0 0=Fixed term, 1=Lives only, 2=Shorter of fixed term or lives, 3=Longer of fixed term or lives, 4=Lives then shorter of fixed term or lives
 TERMENDDATE datetime
 YEARSINTERM tinyint   Default = 0
 TRUSTTAXIDNUMBER nvarchar(100)   Default = ''
 POOLEDINCOMEFUNDUNITS int   Default = 0
 POOLEDINCOMEFUNDTOTALUNITS int   Default = 0
 LIFEINSURANCEPREMIUM money   Default = 0
 LIFEINSURANCEPREMIUMFREQUENCYCODE tinyint   Default = 0 0=Unspecified, 1=Annually, 2=Semiannually, 3=Quarterly, 4=Bimonthly, 5=Monthly, 6=Semimonthly, 7=Biweekly, 8=Weekly
 LIFEINSURANCEPREMIUMDUEDATE datetime
 STATUSCODE tinyint   Default = 0 0=Proposal, 1=Response pending, 2=Accepted, 3=Rejected, 4=Withdrawn, 5=Matured
 DATEADDED datetime   Default = getdate() Indicates the date this record was added.
 DATECHANGED datetime   Default = getdate() Indicates the date this record was last changed.
 TS timestamp   Timestamp.
 TSLONG bigint (Computed) CONVERT(bigint, TS) Numeric representation of the timestamp.
 REMAINDERMANPERCENT decimal(6, 5)   Default = 0
 DISCOUNTDATE datetime
 PROBATEDATE datetime
 PROBATESTATUSCODE tinyint   Default = 0 0=Pending, 1=In probate, 2=Matured, 3=Final distribution
 ISTESTAMENTARY bit   Default = 0
 ISLIVINGTRUST bit   Default = 0
 GIFTVALUEISNOMINAL bit   Default = 0
 ISCONTINGENT bit   Default = 0
 ORGISPOLICY bit   Default = 0
 ORGISBENEFICIARY bit   Default = 0
 TRUSTHELDOUTSIDE bit   Default = 0
 TOTALPAYOUT money   Default = 0
 SUBTYPE nvarchar(16) (Computed) CASE [SUBTYPECODE] WHEN 0 THEN N'' WHEN 1 THEN N'Deferred' WHEN 2 THEN N'Flexible' WHEN 3 THEN N'Commuted payment' END Provides a translation for the 'SUBTYPECODE' field.
 PAYMENTFREQUENCY nvarchar(12) (Computed) CASE [PAYMENTFREQUENCYCODE] WHEN 0 THEN N'Unspecified' WHEN 1 THEN N'Annually' WHEN 2 THEN N'Semiannually' WHEN 3 THEN N'Quarterly' WHEN 4 THEN N'Bimonthly' WHEN 5 THEN N'Monthly' WHEN 6 THEN N'Semimonthly' WHEN 7 THEN N'Biweekly' WHEN 8 THEN N'Weekly' END Provides a translation for the 'PAYMENTFREQUENCYCODE' field.
 TERMTYPE nvarchar(41) (Computed) CASE [TERMTYPECODE] WHEN 0 THEN N'Fixed term' WHEN 1 THEN N'Lives only' WHEN 2 THEN N'Shorter of fixed term or lives' WHEN 3 THEN N'Longer of fixed term or lives' WHEN 4 THEN N'Lives then shorter of fixed term or lives' END Provides a translation for the 'TERMTYPECODE' field.
 STATUS nvarchar(16) (Computed) CASE [STATUSCODE] WHEN 0 THEN N'Proposal' WHEN 1 THEN N'Response pending' WHEN 2 THEN N'Accepted' WHEN 3 THEN N'Rejected' WHEN 4 THEN N'Withdrawn' WHEN 5 THEN N'Matured' END Provides a translation for the 'STATUSCODE' field.
 PROBATESTATUS nvarchar(18) (Computed) CASE [PROBATESTATUSCODE] WHEN 0 THEN N'Pending' WHEN 1 THEN N'In probate' WHEN 2 THEN N'Matured' WHEN 3 THEN N'Final distribution' END Provides a translation for the 'PROBATESTATUSCODE' field.
 REALIZEDVALUE money   Default = 0
 AUTOCALCULATEREALIZEDAMOUNT bit   Default = 0
 TRANSACTIONEXPECTEDGIFTAMOUNT money   Default = 0 EXPECTEDGIFTAMOUNT in transaction currency.
 TRANSACTIONRECOGNITIONAMOUNT money   Default = 0 RECOGNITIONAMOUNT in transaction currency.
 TRANSACTIONNETPRESENTVALUE money   Default = 0 NETPRESENTVALUE in transaction currency.
 TRANSACTIONREMAINDERVALUE money   Default = 0 REMAINDERVALUE in transaction currency.
 TRANSACTIONPAYOUTAMOUNT money   Default = 0 PAYOUTAMOUNT in transaction currency.
 TRANSACTIONLIFEINSURANCEPREMIUM money   Default = 0 LIFEINSURANCEPREMIUM in transaction currency.
 TRANSACTIONTOTALPAYOUT money   Default = 0 TOTALPAYOUT in transaction currency.
 TRANSACTIONREALIZEDVALUE money   Default = 0 REALIZEDVALUE in transaction currency.
 ORGANIZATIONEXPECTEDGIFTAMOUNT money   Default = 0 EXPECTEDGIFTAMOUNT in organization currency.
 ORGANIZATIONRECOGNITIONAMOUNT money   Default = 0 RECOGNITIONAMOUNT in organization currency.
 ORGANIZATIONNETPRESENTVALUE money   Default = 0 NETPRESENTVALUE in organization currency.
 ORGANIZATIONREMAINDERVALUE money   Default = 0 REMAINDERVALUE in organization currency.
 ORGANIZATIONPAYOUTAMOUNT money   Default = 0 PAYOUTAMOUNT in organization currency.
 ORGANIZATIONLIFEINSURANCEPREMIUM money   Default = 0 LIFEINSURANCEPREMIUM in organization currency.
 ORGANIZATIONTOTALPAYOUT money   Default = 0 TOTALPAYOUT in organization currency.
 ORGANIZATIONREALIZEDVALUE money   Default = 0 REALIZEDVALUE in organization currency.
 TRANSACTIONGIFTAMOUNT money (Computed) case when dbo.UFN_PLANNEDGIFT_DESIGNATIONTRANSACTIONAMOUNT(ID)>0 then dbo.UFN_PLANNEDGIFT_DESIGNATIONTRANSACTIONAMOUNT(ID) else TRANSACTIONEXPECTEDGIFTAMOUNT end
 POOLEDINCOMEFUNDPERCENT decimal(37, 19) (Computed) case when POOLEDINCOMEFUNDTOTALUNITS = 0 or POOLEDINCOMEFUNDTOTALUNITS < POOLEDINCOMEFUNDUNITS then null else convert(decimal, POOLEDINCOMEFUNDUNITS) / convert(decimal, POOLEDINCOMEFUNDTOTALUNITS) end
 ORGANIZATIONGIFTAMOUNT money (Computed) case when dbo.UFN_PLANNEDGIFT_DESIGNATIONORGANIZATIONAMOUNT(ID)>0 then dbo.UFN_PLANNEDGIFT_DESIGNATIONORGANIZATIONAMOUNT(ID) else ORGANIZATIONEXPECTEDGIFTAMOUNT end
 TRIGGERDATE datetime
 ISFLIP bit   Default = 0
 VEHICLE nvarchar(34) (Computed) CASE [VEHICLECODE] WHEN 0 THEN N'Charitable gift annuity' WHEN 1 THEN N'Charitable remainder unitrust' WHEN 2 THEN N'Charitable remainder annuity trust' WHEN 3 THEN N'Charitable lead unitrust' WHEN 4 THEN N'Charitable lead annuity trust' WHEN 5 THEN N'Pooled income fund' WHEN 6 THEN N'Life insurance' WHEN 7 THEN N'Retained life estate' WHEN 8 THEN N'Bargain sale' WHEN 9 THEN N'Outright gift' WHEN 10 THEN N'Bequest' WHEN 11 THEN N'Retirement plan assets' WHEN 12 THEN N'Testamentary charitable trust' WHEN 13 THEN N'Other' WHEN 14 THEN N'Living trust' END Provides a translation for the 'VEHICLECODE' field.
 EXCL_RATIO decimal(6, 3)   Default = 0 Exclusion ratio
 EXCL_EXPDATE datetime Expiration date for the exclusion ratio
 DONOTRECEIPT bit   Default = 1
 LIFEINSURANCEPREMIUMFREQUENCY nvarchar(12) (Computed) CASE [LIFEINSURANCEPREMIUMFREQUENCYCODE] WHEN 0 THEN N'Unspecified' WHEN 1 THEN N'Annually' WHEN 2 THEN N'Semiannually' WHEN 3 THEN N'Quarterly' WHEN 4 THEN N'Bimonthly' WHEN 5 THEN N'Monthly' WHEN 6 THEN N'Semimonthly' WHEN 7 THEN N'Biweekly' WHEN 8 THEN N'Weekly' WHEN 9 THEN N'Paid up' END
 LIFEINSURANCEPOLICYNUMBER nvarchar(50)   Default = ''
 LIFEINSURANCEDATEISSUED datetime
 LIFEINSURANCEFACEVALUE money   Default = 0
 TRANSACTIONLIFEINSURANCEFACEVALUE money   Default = 0
 ORGANIZATIONLIFEINSURANCEFACEVALUE money   Default = 0
 LIFEINSURANCEDOESINCLUDEDIVIDENDPARTICIPATION bit   Default = 0
 LIFEINSURANCEISLOANALLOWED bit   Default = 0
 LIFEINSURANCEOUTSTANDINGLOANAMOUNT money   Default = 0
 TRANSACTIONLIFEINSURANCEOUTSTANDINGLOANAMOUNT money   Default = 0
 ORGANIZATIONLIFEINSURANCEOUTSTANDINGLOANAMOUNT money   Default = 0

Foreign Key Field Type Null Notes Description
 CONSTITUENTID uniqueidentifier   CONSTITUENT.LOCALID FK to CONSTITUENT
 PROSPECTPLANID uniqueidentifier PROSPECTPLAN.ID FK to PROSPECTPLAN
 POOLEDINCOMEFUNDCODEID uniqueidentifier POOLEDINCOMEFUNDCODE.ID FK to POOLEDINCOMEFUNDCODE
 ADDEDBYID uniqueidentifier   CHANGEAGENT.ID FK to CHANGEAGENT.
 CHANGEDBYID uniqueidentifier   CHANGEAGENT.ID FK to CHANGEAGENT.
 OTHERSUBTYPECODEID uniqueidentifier PLANNEDGIFTOTHERSUBTYPECODE.ID FK to PLANNEDGIFTOTHERSUBTYPECODE
 BASECURRENCYID uniqueidentifier CURRENCY.ID The base currency associated with this planned gift.
 ORGANIZATIONEXCHANGERATEID uniqueidentifier CURRENCYEXCHANGERATE.ID The exchange rate used to calculate the organization amounts.
 TRANSACTIONCURRENCYID uniqueidentifier CURRENCY.ID The transaction currency associated with this planned gift.
 BASEEXCHANGERATEID uniqueidentifier CURRENCYEXCHANGERATE.ID The exchange rate used to calculate the base amounts.
 TRIGGEREVENTCODEID uniqueidentifier PLANNEDGIFTTRIGGEREVENTCODE.ID FK to PLANNEDGIFTTRIGGEREVENTCODE

Index Name Field(s) Unique Primary Clustered
 IX_PLANNEDGIFT_BASECURRENCYID BASECURRENCYID      
 IX_PLANNEDGIFT_BASEEXCHANGERATEID BASEEXCHANGERATEID      
 IX_PLANNEDGIFT_CONSTITUENTID CONSTITUENTID      
 IX_PLANNEDGIFT_DATEADDED DATEADDED    
 IX_PLANNEDGIFT_DATECHANGED DATECHANGED      
 IX_PLANNEDGIFT_ID ID      
 IX_PLANNEDGIFT_ORGANIZATIONEXCHANGERATEID ORGANIZATIONEXCHANGERATEID      
 IX_PLANNEDGIFT_TRANSACTIONCURRENCYID TRANSACTIONCURRENCYID      
 PK_PLANNEDGIFT ID  

Trigger Name Description
 TR_PLANNEDGIFT_AUDIT_UPDATE
 TR_PLANNEDGIFT_AUDIT_DELETE
 TR_PLANNEDGIFT_INSERTUPDATE_CURRENCY

Referenced by Field
 PLANNEDGIFTADDITION PLANNEDGIFTID
 PLANNEDGIFTASSET PLANNEDGIFTID
 PLANNEDGIFTATTACHMENT PLANNEDGIFTID
 PLANNEDGIFTBENEFICIARY PLANNEDGIFTID
 PLANNEDGIFTDESIGNATION PLANNEDGIFTID
 PLANNEDGIFTFUNDINGTYPE PLANNEDGIFTID
 PLANNEDGIFTLETTER PLANNEDGIFTID
 PLANNEDGIFTLINK ID
 PLANNEDGIFTMEDIALINK PLANNEDGIFTID
 PLANNEDGIFTNOTE PLANNEDGIFTID
 PLANNEDGIFTRECONCILE PLANNEDGIFTID
 PLANNEDGIFTRELATIONSHIP PLANNEDGIFTID
 PLANNEDGIFTREVENUE ID
 PLANNEDGIFTREVENUESPLIT PLANNEDGIFTID
 PLANNEDGIFTSITE PLANNEDGIFTID

Entity-Relationship diagram of this table (CONSTITUENT-PLANNEDGIFTREVENUESPLIT)
Entity-Relationship diagram of this table (PLANNEDGIFTSITE-PROSPECTPLAN)