USP_DATAFORMTEMPLATE_PLANNEDGIFT_EDITLOAD_4

The load procedure used by the edit dataform template "Planned Gift Edit Form 4"

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.
@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.
@PROSPECTPLANID	uniqueidentifier	INOUT	Prospect plan
@VEHICLECODE	tinyint	INOUT	Planned gift vehicle
@SUBTYPECODE	tinyint	INOUT	Subtype
@EXPECTEDGIFTAMOUNT	money	INOUT	Gift amount
@ISREVOCABLE	bit	INOUT	Revocable
@ISANONYMOUS	bit	INOUT	Anonymous gift
@EXPECTEDMATURITY	UDT_YEAR	INOUT	Expected maturity year
@DISCOUNTRATE	decimal(6, 1)	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	Payments start
@PAYMENTPERIODEND	datetime	INOUT	Payments end
@PAYMENTFREQUENCYCODE	tinyint	INOUT	Payment frequency
@TERMTYPECODE	tinyint	INOUT	Term type
@TERMENDDATE	datetime	INOUT	Term end date
@YEARSINTERM	tinyint	INOUT	Years in term
@TRUSTTAXIDNUMBER	nvarchar(100)	INOUT	Tax ID number
@POOLEDINCOMEFUNDCODEID	uniqueidentifier	INOUT	Name
@POOLEDINCOMEFUNDUNITS	int	INOUT	PIF units
@POOLEDINCOMEFUNDTOTALUNITS	int	INOUT	Total PIF units
@LIFEINSURANCEPREMIUM	money	INOUT	Amount
@LIFEINSURANCEPREMIUMFREQUENCYCODE	tinyint	INOUT	Frequency
@LIFEINSURANCEPREMIUMDUEDATE	datetime	INOUT	Due date
@SSN	nvarchar(36)	INOUT	SSN
@ISORGANIZATION	bit	INOUT
@CONSTITUENTID	uniqueidentifier	INOUT
@STATUSCODE	tinyint	INOUT	Status
@CONSTITUENTLOOKUPID	nvarchar(36)	INOUT
@CONSTITUENTFIRSTNAME	nvarchar(50)	INOUT
@CONSTITUENTKEYNAME	nvarchar(100)	INOUT
@DESIGNATION	xml	INOUT	Planned Gift Details
@ASSETS	xml	INOUT	Assets
@BENEFICIARIES	xml	INOUT	Beneficiaries
@RELATIONSHIPS	xml	INOUT	Relationships
@PROBATEDATE	datetime	INOUT	As of
@DISCOUNTDATE	datetime	INOUT	As of
@REMAINDERMANPERCENT	decimal(7, 4)	INOUT	Remainderman %
@PROBATESTATUSCODE	tinyint	INOUT	Probate status
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SITES	xml	INOUT	Sites
@SITEREQUIRED	bit	INOUT	Site required
@ISTESTAMENTARY	bit	INOUT	Testamentary
@OTHERSUBTYPECODEID	uniqueidentifier	INOUT	Subtype
@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
@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
@ADDEDASREVENUE	bit	INOUT	Added as revenue
@TRIGGERDATE	datetime	INOUT	Trigger date
@ISFLIP	bit	INOUT	FLIP situation
@TRIGGEREVENTCODEID	uniqueidentifier	INOUT	Trigger event
@EXCL_RATIO	decimal(6, 3)	INOUT	Exclusion ratio
@EXCL_EXPDATE	datetime	INOUT	Expiration date
@DONOTRECEIPT	bit	INOUT	Do not receipt
@LIFEINSURANCEPOLICYNUMBER	nvarchar(50)	INOUT	Policy number
@LIFEINSURANCEDATEISSUED	datetime	INOUT	Issue date
@LIFEINSURANCEFACEVALUE	money	INOUT	Face value
@LIFEINSURANCEDOESINCLUDEDIVIDENDPARTICIPATION	bit	INOUT	Policy includes dividend participation
@LIFEINSURANCEISLOANALLOWED	bit	INOUT	Loan is allowed
@LIFEINSURANCEOUTSTANDINGLOANAMOUNT	money	INOUT	Outstanding loan amount

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_PLANNEDGIFT_EDITLOAD_4 (
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @TSLONG bigint = 0 output,
  @PROSPECTPLANID uniqueidentifier = null output,
  @VEHICLECODE tinyint = null output,
  @SUBTYPECODE tinyint = null output,
  @EXPECTEDGIFTAMOUNT money = null output,
  @ISREVOCABLE bit = null output,
  @ISANONYMOUS bit = null output,
  @EXPECTEDMATURITY dbo.UDT_YEAR = null output,
  @DISCOUNTRATE decimal(6,1) = 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,
  @PAYMENTFREQUENCYCODE tinyint = null output,
  @TERMTYPECODE tinyint = null output,
  @TERMENDDATE datetime = null output,
  @YEARSINTERM tinyint = null output,
  @TRUSTTAXIDNUMBER nvarchar(100) = null output,
  @POOLEDINCOMEFUNDCODEID uniqueidentifier = null output,
  @POOLEDINCOMEFUNDUNITS int = null output,
  @POOLEDINCOMEFUNDTOTALUNITS int = null output,
  @LIFEINSURANCEPREMIUM money = null output,
  @LIFEINSURANCEPREMIUMFREQUENCYCODE tinyint = null output,
  @LIFEINSURANCEPREMIUMDUEDATE datetime = null output,
  @SSN nvarchar(36) = null output,
  @ISORGANIZATION bit = null output,
  @CONSTITUENTID uniqueidentifier = null output,
  @STATUSCODE tinyint = null output,
  @CONSTITUENTLOOKUPID nvarchar(36) = null output,
  @CONSTITUENTFIRSTNAME nvarchar(50) = null output,
  @CONSTITUENTKEYNAME nvarchar(100) = null output,
  @DESIGNATION xml = null output,
  @ASSETS xml = null output,
  @BENEFICIARIES xml = null output,
  @RELATIONSHIPS xml = null output,
  @PROBATEDATE datetime = null output,
  @DISCOUNTDATE datetime = null output,
  @REMAINDERMANPERCENT decimal(7,4) = null output,
  @PROBATESTATUSCODE tinyint = null output,
  @CURRENTAPPUSERID uniqueidentifier,
  @SITES xml = null output,
  @SITEREQUIRED bit = null output,
  @ISTESTAMENTARY bit = null output,
  @OTHERSUBTYPECODEID uniqueidentifier = 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,
  @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,
  @ADDEDASREVENUE bit = null output,
  @TRIGGERDATE datetime = null output,
  @ISFLIP bit = null output,
  @TRIGGEREVENTCODEID uniqueidentifier = null output,
  @EXCL_RATIO decimal(6,3) = null output,
  @EXCL_EXPDATE datetime = null output,
  @DONOTRECEIPT bit = 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;
  set @TSLONG = 0;

  select
    @DATALOADED = 1,
    @TSLONG  = PG.TSLONG,
    @PROSPECTPLANID = PG.PROSPECTPLANID,
    @VEHICLECODE = PG.VEHICLECODE,
    @SUBTYPECODE = PG.SUBTYPECODE,
    @EXPECTEDGIFTAMOUNT = PG.TRANSACTIONEXPECTEDGIFTAMOUNT,
    @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,
    @PAYMENTFREQUENCYCODE = PG.PAYMENTFREQUENCYCODE,
    @TERMTYPECODE = PG.TERMTYPECODE,
    @TERMENDDATE = PG.TERMENDDATE,
    @YEARSINTERM = PG.YEARSINTERM,
    @TRUSTTAXIDNUMBER = PG.TRUSTTAXIDNUMBER,
    @POOLEDINCOMEFUNDCODEID = PG.POOLEDINCOMEFUNDCODEID,
    @POOLEDINCOMEFUNDUNITS = PG.POOLEDINCOMEFUNDUNITS,
    @POOLEDINCOMEFUNDTOTALUNITS = PG.POOLEDINCOMEFUNDTOTALUNITS,
    @LIFEINSURANCEPREMIUM = PG.TRANSACTIONLIFEINSURANCEPREMIUM,
    @LIFEINSURANCEPREMIUMFREQUENCYCODE = PG.LIFEINSURANCEPREMIUMFREQUENCYCODE,
    @LIFEINSURANCEPREMIUMDUEDATE = PG.LIFEINSURANCEPREMIUMDUEDATE,
    @SSN = N'', /* C.SSN, */
    @ISORGANIZATION = case when O.ID is null then 0 else 1 end,
    @CONSTITUENTID = C.ID,
    @STATUSCODE = PG.STATUSCODE,
    @CONSTITUENTLOOKUPID = C.LOOKUPID,
    @CONSTITUENTFIRSTNAME = C.FIRSTNAME,
    @CONSTITUENTKEYNAME = C.KEYNAME,
    @BENEFICIARIES = dbo.UFN_PLANNEDGIFT_BENEFICIARIES_TOITEMLISTXML(@ID),
    @RELATIONSHIPS = dbo.UFN_PLANNEDGIFT_RELATIONSHIPS_TOITEMLISTXML(@ID),
    @PROBATEDATE = PG.PROBATEDATE,
    @DISCOUNTDATE = PG.DISCOUNTDATE,
    @REMAINDERMANPERCENT = PG.REMAINDERMANPERCENT*100.0,
    @PROBATESTATUSCODE = PG.PROBATESTATUSCODE,
    @SITES = dbo.UFN_PLANNEDGIFT_GETSITES_TOITEMLISTXML(PG.ID),
    @SITEREQUIRED = dbo.UFN_SITEREQUIREDFORUSER(@CURRENTAPPUSERID),
    @ISTESTAMENTARY = PG.ISTESTAMENTARY,
    @OTHERSUBTYPECODEID = PG.OTHERSUBTYPECODEID,
    @ISLIVINGTRUST = PG.ISLIVINGTRUST,
    @GIFTVALUEISNOMINAL = PG.GIFTVALUEISNOMINAL,
    @ISCONTINGENT = PG.ISCONTINGENT,
    @ORGISPOLICY = PG.ORGISPOLICY,
    @ORGISBENEFICIARY = PG.ORGISBENEFICIARY,
    @TRUSTHELDOUTSIDE = PG.TRUSTHELDOUTSIDE,
    @TOTALPAYOUT = PG.TRANSACTIONTOTALPAYOUT,
    @REALIZEDVALUE = PG.TRANSACTIONREALIZEDVALUE,
    @AUTOCALCULATEREALIZEDAMOUNT = dbo.UFN_PLANNEDGIFT_AUTOCALCULATE_REALIZEDAMOUNT(@ID),
    @BASECURRENCYID = PG.BASECURRENCYID,
    @TRANSACTIONCURRENCYID = PG.TRANSACTIONCURRENCYID,
    @BASEEXCHANGERATEID = PG.BASEEXCHANGERATEID,
    @EXCHANGERATE = CURRENCYEXCHANGERATE.RATE,
    @HADSPOTRATE =  
      case
        when CURRENCYEXCHANGERATE.TYPECODE = 2
          then 1
        else 0
      end,
    @RATECHANGED = 0,
    @TRIGGERDATE = TRIGGERDATE,
    @ISFLIP = ISFLIP,
    @TRIGGEREVENTCODEID = TRIGGEREVENTCODEID,
    @EXCL_RATIO = EXCL_RATIO,
    @EXCL_EXPDATE = EXCL_EXPDATE,
    @DONOTRECEIPT = DONOTRECEIPT,
    @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.ORGANIZATIONDATA O on O.ID=C.ID
    left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = PG.BASEEXCHANGERATEID
  where
    PG.ID = @ID;

  -- Check whether this planned gift is associated with revenue.  If it is,

  -- don't allow the currencies to be changed.

  set @ADDEDASREVENUE = 0;
  select @ADDEDASREVENUE = 1 from dbo.PLANNEDGIFTREVENUE where ID = @ID;
  select @ADDEDASREVENUE = 1 from dbo.PLANNEDGIFTREVENUESPLIT where PLANNEDGIFTID = @ID;

  set @DESIGNATION = (
    select
      DESIGNATION.ID,
      DESIGNATION.DESIGNATIONID,
      DESIGNATION.TRANSACTIONAMOUNT as AMOUNT,
      DESIGNATION.CATEGORYCODEID,
      DESIGNATION.TYPECODEID,
      DESIGNATION.USECODEID,
      DESIGNATION.DATE,
      DESIGNATION.SEQUENCE,
      DESIGNATION.TRANSACTIONCURRENCYID
    from dbo.UFN_PLANNEDGIFT_DESIGNATION_2(@ID) DESIGNATION
    order by DESIGNATION.SEQUENCE
    for xml raw('ITEM'),type,elements,root('DESIGNATION'),BINARY BASE64
  );

  set @ASSETS = (
    select
      ASSETS.ID,
      ASSETS.ASSETTYPECODEID,
      ASSETS.DESCRIPTION,
      ASSETS.TRANSACTIONVALUE as VALUE,
      ASSETS.TRANSACTIONCOSTBASIS as COSTBASIS,
      ASSETS.ASSETVALUATIONMETHODCODEID,
      ASSETS.VALUATIONSOURCE,
      (
        select 
          ID, PLANNEDGIFTASSETID, HOLDERCONSTITUENTID
        from
          dbo.PLANNEDGIFTASSETHOLDER
        where
          PLANNEDGIFTASSETHOLDER.PLANNEDGIFTASSETID = ASSETS.ID
        for xml raw('ITEM'), type, elements, binary base64
      ) ASSETHOLDERS,
      ASSETS.ADDRESSID,
      ASSETS.SEQUENCE,
      ASSETS.TRANSACTIONCURRENCYID
    from dbo.UFN_PLANNEDGIFT_ASSETS_2(@ID) ASSETS
    order by ASSETS.SEQUENCE
    for xml raw('ITEM'),type,elements,root('ASSETS'),BINARY BASE64
  );                        

  return 0;