USP_DATAFORMTEMPLATE_VIEW_PLANNEDGIFTPAGEDATA
The load procedure used by the view dataform template "Planned Gift Page Expression 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. |
| @PROSPECTID | uniqueidentifier | INOUT | PROSPECTID |
| @PROSPECTPLANID | uniqueidentifier | INOUT | PROSPECTPLANID |
| @PROSPECT | nvarchar(700) | INOUT | PROSPECT |
| @PROSPECTPLAN | nvarchar(100) | INOUT | PROSPECTPLAN |
| @ISACTIVEPROSPECT | bit | INOUT | ISACTIVEPROSPECT |
| @VEHICLECODE | tinyint | INOUT | VEHICLECODE |
| @STATUSCODE | tinyint | INOUT | STATUSCODE |
| @DATE | datetime | INOUT | DATE |
| @GIFTAMOUNT | money | INOUT | GIFTAMOUNT |
| @VEHICLETRANSLATION | nvarchar(100) | INOUT | VEHICLETRANSLATION |
| @CANADDREVENUE | bit | INOUT | CANADDREVENUE |
| @CANADDPAYMENT | bit | INOUT | CANADDPAYMENT |
| @HASREVENUE | bit | INOUT | HASREVENUE |
| @REVENUEID | uniqueidentifier | INOUT | REVENUEID |
| @ATTRIBUTEDEFINED | bit | INOUT | ATTRIBUTEDEFINED |
| @VEHICLEAUTOCALCULATEREALIZEDAMOUNT | bit | INOUT | VEHICLEAUTOCALCULATEREALIZEDAMOUNT |
| @AUTOCALCULATEREALIZEDAMOUNT | bit | INOUT | AUTOCALCULATEREALIZEDAMOUNT |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @TRANSACTIONGIFTAMOUNT | money | INOUT | TRANSACTIONGIFTAMOUNT |
| @TRANSACTIONCURRENCYID | uniqueidentifier | INOUT | TRANSACTIONCURRENCYID |
| @ISELIGIBLEFORADDITIONS | bit | INOUT | ISELIGIBLEFORADDITIONS |
| @TRANSACTIONTOTALGIFTAMOUNT | money | INOUT | TRANSACTIONTOTALGIFTAMOUNT |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PLANNEDGIFTPAGEDATA(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@PROSPECTID uniqueidentifier = null output,
@PROSPECTPLANID uniqueidentifier = null output,
@PROSPECT nvarchar(700) = null output,
@PROSPECTPLAN nvarchar(100) = null output,
@ISACTIVEPROSPECT bit = null output,
@VEHICLECODE tinyint = null output,
@STATUSCODE tinyint = null output,
@DATE datetime = null output,
@GIFTAMOUNT money = null output,
@VEHICLETRANSLATION nvarchar(100) = null output,
@CANADDREVENUE bit = null output,
@CANADDPAYMENT bit = null output,
@HASREVENUE bit = null output,
@REVENUEID uniqueidentifier = null output,
@ATTRIBUTEDEFINED bit = null output,
@VEHICLEAUTOCALCULATEREALIZEDAMOUNT bit = null output,
@AUTOCALCULATEREALIZEDAMOUNT bit = null output,
@CURRENTAPPUSERID uniqueidentifier = null,
@TRANSACTIONGIFTAMOUNT money = null output,
@TRANSACTIONCURRENCYID uniqueidentifier = null output,
@ISELIGIBLEFORADDITIONS bit = null output,
@TRANSACTIONTOTALGIFTAMOUNT money = null output
) as
set nocount on;
set @DATALOADED = 0;
select
@DATALOADED = 1,
@PROSPECTID = PG.CONSTITUENTID,
@PROSPECTPLANID = PG.PROSPECTPLANID,
@PROSPECT = NF.NAME,
@PROSPECTPLAN = dbo.UFN_PROSPECTPLANTYPECODE_GETDESCRIPTION(PP.PROSPECTPLANTYPECODEID),
@ISACTIVEPROSPECT = dbo.UFN_CONSTITUENT_ISPROSPECT(PG.CONSTITUENTID),
@VEHICLECODE = PG.VEHICLECODE,
@STATUSCODE = STATUSCODE,
@DATE = PG.GIFTDATE,
@GIFTAMOUNT = PG.GIFTAMOUNT,
@ATTRIBUTEDEFINED = dbo.UFN_ATTRIBUTECATEGORY_DEFINEDFORRECORDTYPE_FORAPPUSER('PLANNED GIFT',@CURRENTAPPUSERID),
@VEHICLETRANSLATION = case PG.VEHICLECODE
when 0 then 'Charitable gift annuity'
when 1 then 'Charitable remainder unitrust'
when 2 then 'Charitable remainder annuity trust'
when 3 then 'Charitable lead unitrust'
when 4 then 'Charitable lead annuity trust'
when 5 then 'Pooled income fund'
when 6 then 'Life insurance'
when 7 then 'Retained life estate'
when 8 then 'Bargain sale'
when 9 then 'Outright gift'
when 10 then 'Bequest'
when 11 then 'Retirement plan assets'
when 12 then 'Testamentary charitable trust'
when 13 then 'Other'
when 14 then 'Living trust' end,
@VEHICLEAUTOCALCULATEREALIZEDAMOUNT = isnull(PGVO.AUTOCALCULATEREALIZEDAMOUNT, 0),
@AUTOCALCULATEREALIZEDAMOUNT = PG.AUTOCALCULATEREALIZEDAMOUNT,
@TRANSACTIONGIFTAMOUNT = PG.TRANSACTIONGIFTAMOUNT,
@TRANSACTIONCURRENCYID = PG.TRANSACTIONCURRENCYID,
@TRANSACTIONTOTALGIFTAMOUNT = dbo.UFN_PLANNEDGIFT_GETTOTALAMOUNT(@ID, 2, PG.TRANSACTIONCURRENCYID)
from
dbo.PLANNEDGIFT PG
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PG.CONSTITUENTID) NF
left outer join dbo.PROSPECTPLAN PP on PP.ID=PG.PROSPECTPLANID
left join dbo.PLANNEDGIFTVEHICLEOPTIONS PGVO on PGVO.VEHICLECODE = PG.VEHICLECODE
where
PG.ID = @ID;
set @CANADDREVENUE = 0;
if @VEHICLECODE = 0 or @VEHICLECODE = 1 or @VEHICLECODE = 2
select @CANADDREVENUE = case when count(*) > 0 then 1 else 0 end
from dbo.PLANNEDGIFT PG
where PG.ID = @ID
and not PG.TRANSACTIONREMAINDERVALUE = 0
and PG.STATUSCODE = 2
and (select count(*) from dbo.PLANNEDGIFTDESIGNATION pgd
where pgd.PLANNEDGIFTID = PG.ID) > 0
and not exists (select ID from dbo.PLANNEDGIFTREVENUE pgr
where pgr.ID = @ID);
if @VEHICLECODE = 3 or @VEHICLECODE = 4
select @CANADDREVENUE = case when count(*) > 0 then 1 else 0 end
from dbo.PLANNEDGIFT PG
where PG.ID = @ID
and not PG.TRANSACTIONPAYOUTAMOUNT = 0
and PG.STATUSCODE = 2
and (select count(*) from dbo.PLANNEDGIFTDESIGNATION pgd
where pgd.PLANNEDGIFTID = PG.ID) > 0
and not exists (select ID from dbo.PLANNEDGIFTREVENUE pgr
where pgr.ID = @ID);
if @VEHICLECODE = 5
select @CANADDREVENUE = case when count(*) > 0 then 1 else 0 end
from dbo.PLANNEDGIFT PG
where PG.ID = @ID
and not PG.TRANSACTIONGIFTAMOUNT = 0
and not PG.TRANSACTIONREMAINDERVALUE = 0
and PG.STATUSCODE = 2
and (select count(*) from dbo.PLANNEDGIFTDESIGNATION pgd
where pgd.PLANNEDGIFTID = PG.ID) > 0
and not exists (select ID from dbo.PLANNEDGIFTREVENUE pgr
where pgr.ID = @ID);
if @VEHICLECODE = 6 or @VEHICLECODE = 7 or @VEHICLECODE = 10 or @VEHICLECODE = 11 or @VEHICLECODE = 12 or @VEHICLECODE = 13 or @VEHICLECODE = 14
select @CANADDREVENUE = case when count(*) > 0 then 1 else 0 end
from dbo.PLANNEDGIFT PG
where PG.ID = @ID
and not PG.TRANSACTIONGIFTAMOUNT = 0
and PG.STATUSCODE = 2
and (select count(*) from dbo.PLANNEDGIFTDESIGNATION pgd
where pgd.PLANNEDGIFTID = PG.ID) > 0
and not exists (select ID from dbo.PLANNEDGIFTREVENUE pgr
where pgr.ID = @ID);
if @VEHICLECODE = 6 and dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('0E85C527-E6E9-4C5F-A8E8-105FD0E18FE7') = 1
select @CANADDREVENUE = case when count(*) > 0 then 1 else 0 end
from dbo.PLANNEDGIFT PG
where PG.ID = @ID
and not PG.TRANSACTIONGIFTAMOUNT = 0
and not PG.TRANSACTIONLIFEINSURANCEPREMIUM = 0
and PG.STATUSCODE = 2
and (select count(*) from dbo.PLANNEDGIFTDESIGNATION pgd
where pgd.PLANNEDGIFTID = PG.ID) > 0
and not exists (select ID from dbo.PLANNEDGIFTREVENUE pgr
where pgr.ID = @ID);
if (@VEHICLECODE = 0 or @VEHICLECODE = 5) and dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('0E85C527-E6E9-4C5F-A8E8-105FD0E18FE7') = 1
select @CANADDREVENUE = case when count(*) > 0 then 1 else 0 end
from dbo.PLANNEDGIFT PG
where PG.ID = @ID
and not PG.TRANSACTIONPAYOUTAMOUNT = 0
and PG.STATUSCODE = 2
and (select count(*) from dbo.PLANNEDGIFTDESIGNATION pgd
where pgd.PLANNEDGIFTID = PG.ID) > 0
and not exists (select ID from dbo.PLANNEDGIFTREVENUE pgr
where pgr.ID = @ID);
set @CANADDPAYMENT = 0;
-- Make sure the planned gift is either an Outright Gift or a Bargain Sale
-- and that it has a designation
if (@VEHICLECODE = 8 or @VEHICLECODE = 9)
begin
if exists (select 1 from dbo.PLANNEDGIFTDESIGNATION where PLANNEDGIFTID = @ID) and
not exists (select ID from dbo.PLANNEDGIFTREVENUESPLIT where PLANNEDGIFTID = @ID) and
exists (select 1 from dbo.plannedgift pg where pg.id=@ID and pg.statuscode=2)
begin
set @CANADDPAYMENT = 1;
end
select top 1
@REVENUEID = REVENUESPLIT.REVENUEID,
@HASREVENUE = 1
from dbo.PLANNEDGIFTREVENUESPLIT
inner join dbo.REVENUESPLIT on PLANNEDGIFTREVENUESPLIT.REVENUESPLITID = REVENUESPLIT.ID
where PLANNEDGIFTREVENUESPLIT.PLANNEDGIFTID = @ID
end
else
select @REVENUEID = REVENUEID,
@HASREVENUE = case when count(*) > 0 then 1 else 0 end
from dbo.PLANNEDGIFTREVENUE pgr
where pgr.ID = @ID
group by REVENUEID;
if (@VEHICLECODE in (1,3,5,6,10,11,12,13,14))
set @ISELIGIBLEFORADDITIONS = 1
return 0;