USP_DATAFORMTEMPLATE_VIEW_PLANNEDGIFTADDITIONPAGEDATA
The load procedure used by the view dataform template "Planned Gift Addition 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. |
| @PLANNEDGIFTID | uniqueidentifier | INOUT | PLANNEDGIFTID |
| @PROSPECTID | uniqueidentifier | INOUT | PROSPECTID |
| @PROSPECTPLANID | uniqueidentifier | INOUT | PROSPECTPLANID |
| @PROSPECT | nvarchar(154) | INOUT | PROSPECT |
| @PROSPECTPLAN | nvarchar(100) | INOUT | PROSPECTPLAN |
| @VEHICLETRANSLATION | nvarchar(100) | INOUT | VEHICLETRANSLATION |
| @CANADDREVENUE | bit | INOUT | CANADDREVENUE |
| @HASREVENUE | bit | INOUT | HASREVENUE |
| @REVENUEID | uniqueidentifier | INOUT | REVENUEID |
| @TRANSACTIONGIFTAMOUNT | money | INOUT | TRANSACTIONGIFTAMOUNT |
| @TRANSACTIONCURRENCYID | uniqueidentifier | INOUT | TRANSACTIONCURRENCYID |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PLANNEDGIFTADDITIONPAGEDATA
(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@PLANNEDGIFTID uniqueidentifier = null output,
@PROSPECTID uniqueidentifier = null output,
@PROSPECTPLANID uniqueidentifier = null output,
@PROSPECT nvarchar(154) = null output,
@PROSPECTPLAN nvarchar(100) = null output,
@VEHICLETRANSLATION nvarchar(100) = null output,
@CANADDREVENUE bit = null output,
@HASREVENUE bit = null output,
@REVENUEID uniqueidentifier = null output,
@TRANSACTIONGIFTAMOUNT money = null output,
@TRANSACTIONCURRENCYID uniqueidentifier = null output,
@CURRENTAPPUSERID uniqueidentifier = null
)
as
set nocount on;
set @DATALOADED = 0;
declare @VEHICLECODE int;
select
@DATALOADED = 1,
@PLANNEDGIFTID = PG.ID,
@PROSPECTID = PG.CONSTITUENTID,
@PROSPECTPLANID = PG.PROSPECTPLANID,
@PROSPECT = NF_C.NAME,
@PROSPECTPLAN = dbo.UFN_PROSPECTPLANTYPECODE_GETDESCRIPTION(PP.PROSPECTPLANTYPECODEID),
@VEHICLECODE = PG.VEHICLECODE,
@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,
@TRANSACTIONGIFTAMOUNT = PGA.TRANSACTIONGIFTAMOUNT,
@TRANSACTIONCURRENCYID = PGA.TRANSACTIONCURRENCYID
from dbo.PLANNEDGIFTADDITION PGA
inner join dbo.PLANNEDGIFT PG on PGA.PLANNEDGIFTID = PG.ID
left outer join dbo.PROSPECTPLAN PP on PP.ID=PG.PROSPECTPLANID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PG.CONSTITUENTID) NF_C
where
PGA.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.PLANNEDGIFTADDITION PGA
inner join dbo.PLANNEDGIFT PG on PGA.PLANNEDGIFTID = PG.ID
where
PGA.ID = @ID
and not PG.REMAINDERVALUE = 0
and PG.STATUSCODE = 2
and (select count(*) from dbo.PLANNEDGIFTADDITIONDESIGNATION pgd
where pgd.PLANNEDGIFTADDITIONID = PGA.ID) > 0
and not exists (select ID from dbo.PLANNEDGIFTADDITIONREVENUE pgr
where pgr.ID = @ID);
if @VEHICLECODE = 3 or @VEHICLECODE = 4
select @CANADDREVENUE = case when count(*) > 0 then 1 else 0 end
from dbo.PLANNEDGIFTADDITION PGA
inner join dbo.PLANNEDGIFT PG on PGA.PLANNEDGIFTID = PG.ID
where
PGA.ID = @ID
and not PG.PAYOUTAMOUNT = 0
and PG.STATUSCODE = 2
and (select count(*) from dbo.PLANNEDGIFTADDITIONDESIGNATION pgd
where pgd.PLANNEDGIFTADDITIONID = PGA.ID) > 0
and not exists (select ID from dbo.PLANNEDGIFTADDITIONREVENUE pgr
where pgr.ID = @ID);
if @VEHICLECODE = 5
select @CANADDREVENUE = case when count(*) > 0 then 1 else 0 end
from dbo.PLANNEDGIFTADDITION PGA
inner join dbo.PLANNEDGIFT PG on PGA.PLANNEDGIFTID = PG.ID
where
PGA.ID = @ID
and not PG.GIFTAMOUNT = 0
and not PG.REMAINDERVALUE = 0
and PG.STATUSCODE = 2
and (select count(*) from dbo.PLANNEDGIFTADDITIONDESIGNATION pgd
where pgd.PLANNEDGIFTADDITIONID = PGA.ID) > 0
and not exists (select ID from dbo.PLANNEDGIFTADDITIONREVENUE 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.PLANNEDGIFTADDITION PGA
inner join dbo.PLANNEDGIFT PG on PGA.PLANNEDGIFTID = PG.ID
where
PGA.ID = @ID
and not PG.GIFTAMOUNT = 0
and PG.STATUSCODE = 2
and (select count(*) from dbo.PLANNEDGIFTADDITIONDESIGNATION pgd
where pgd.PLANNEDGIFTADDITIONID = PGA.ID) > 0
and not exists (select ID from dbo.PLANNEDGIFTADDITIONREVENUE 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.PLANNEDGIFTADDITION PGA
inner join dbo.PLANNEDGIFT PG on PGA.PLANNEDGIFTID = PG.ID
where
PGA.ID = @ID
and not PG.GIFTAMOUNT = 0
and not PG.LIFEINSURANCEPREMIUM = 0
and PG.STATUSCODE = 2
and (select count(*) from dbo.PLANNEDGIFTADDITIONDESIGNATION pgd
where pgd.PLANNEDGIFTADDITIONID = PGA.ID) > 0
and not exists (select ID from dbo.PLANNEDGIFTADDITIONREVENUE 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.PLANNEDGIFTADDITION PGA
inner join dbo.PLANNEDGIFT PG on PGA.PLANNEDGIFTID = PG.ID
where
PGA.ID = @ID
and not PG.PAYOUTAMOUNT = 0
and PG.STATUSCODE = 2
and (select count(*) from dbo.PLANNEDGIFTADDITIONDESIGNATION pgd
where pgd.PLANNEDGIFTADDITIONID = PGA.ID) > 0
and not exists (select ID from dbo.PLANNEDGIFTADDITIONREVENUE pgr
where pgr.ID = @ID);
-- PG additions are not allowed for either an Outright Gift or a Bargain Sale
if (@VEHICLECODE = 8 or @VEHICLECODE = 9)
begin
set @REVENUEID = null
set @HASREVENUE = 0
end
else
select
@REVENUEID = REVENUEID,
@HASREVENUE = case when count(*) > 0 then 1 else 0 end
from dbo.PLANNEDGIFTADDITIONREVENUE pgr
where pgr.ID = @ID
group by REVENUEID;
return 0;