USR_UFN_QUERY_FAFDONATION_LISTBUILDER_2
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.USR_UFN_QUERY_FAFDONATION_LISTBUILDER_2
(
@ID uniqueidentifier
)
returns @results table (
[ID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[FIRSTNAME] [nvarchar](100) NOT NULL,
[LASTNAME] [nvarchar](200) NOT NULL,
[ADDRESS] [nvarchar](300) NOT NULL,
[ADDRESSTYPE] [nvarchar](200) NOT NULL,
[CITY] [nvarchar](100) NOT NULL,
[STATE] [nvarchar](10) NOT NULL,
[ZIP] [nvarchar](25) NOT NULL,
[COUNTRY] [nvarchar](200) NOT NULL,
[EMAIL] [dbo].[UDT_EMAILADDRESS] NOT NULL,
[PHONENUMBER] [nvarchar](200) NOT NULL,
[PHONETYPE] [nvarchar](200) NOT NULL,
[TITLE] [nvarchar](200) NOT NULL,
[SUFFIX] [nvarchar](200) NOT NULL,
[AMOUNT] [money] NOT NULL,
[ISANONYMOUSGIFT] [bit] NOT NULL,
[DONOTCALL] [bit] NOT NULL,
[DONOTMAIL] [bit] NOT NULL,
[DONOTEMAIL] [bit] NOT NULL,
[CREDITCARDTYPE] [nvarchar](200) NOT NULL,
[CHECKNUMBER] [nvarchar](40) NOT NULL,
[RECEIPTNUMBER] [int] NOT NULL,
[CHECKDATE] [datetime] NULL,
[GIFTDATE] [datetime] NULL,
[INBOUNDCHANNEL] [nvarchar](200) NOT NULL,
[SOLICITATIONCHANNNEL] [nvarchar](200) NOT NULL,
[ISONLINEGIFT] [nvarchar](50) NOT NULL,
[TRANSACTIONTYPE] [nvarchar](50) NOT NULL,
[PAYMENTMETHOD] [nvarchar](30) NOT NULL,
[POSTSTATUS] [nvarchar](25) NOT NULL,
[TRIBUTEE] [nvarchar](310) NOT NULL,
[TRIBUTEMESSAGE] [nvarchar](510) NOT NULL,
[MATCHINGGIFTCLAIMED] [money] NOT NULL,
[MATCHINGGIFTPAID] [money] NOT NULL,
[MATCHINGGIFTORGANIZATION] [nvarchar](310) NOT NULL,
[RECOGNITION] [nvarchar](310) NOT NULL,
[ISPREVIOUSDONOR] [bit] NOT NULL,
[REGISTRANTROLE] [nvarchar](50) NOT NULL,
[GROUPNAME] [nvarchar](200) NOT NULL,
[GROUPTYPE] [nvarchar](300) NOT NULL,
[COMPANYNAME] [nvarchar](310) NOT NULL,
[COMPANYADDRESS] [nvarchar](300) NOT NULL,
[COMPANYADDRESSTYPE] [nvarchar](200) NOT NULL,
[COMPANYCITY] [nvarchar](100) NOT NULL,
[COMPANYSTATE] [nvarchar](10) NOT NULL,
[COMPANYZIP] [nvarchar](25) NOT NULL,
[COMPANYPHONE] [nvarchar](200) NOT NULL,
[COMPANYEMAIL] [dbo].[UDT_EMAILADDRESS] NOT NULL,
[COMPANYWEBADDRESS] [dbo].[UDT_WEBADDRESS] NOT NULL,
[ISCOMPANYDONATION] [bit] NOT NULL,
[REVENUEDATECHANGED] [datetime] NULL,
[REVENUECHANGEDBY] [nvarchar](260) NOT NULL,
[DONORLOOKUPID] [nvarchar](200) NOT NULL,
[REVENUELOOKUPID] [nvarchar](200) NOT NULL,
[REVENUEID] [uniqueidentifier],
[ORGCONSTITUENTID] [uniqueidentifier],
[DESIGNATIONID] [uniqueidentifier]
)
as begin
if exists(select ID from dbo.FAFDONATION where eventID = @ID) begin
-- active events
insert @results (
ID,
GIFTDATE,
FIRSTNAME,
LASTNAME,
AMOUNT,
RECOGNITION,
TRANSACTIONTYPE,
GROUPNAME,
GROUPTYPE,
FAFDONATION.EMAIL,
REVENUELOOKUPID,
INBOUNDCHANNEL,
ADDRESS,
ADDRESSTYPE,
CITY,
STATE,
ZIP,
COUNTRY,
PHONENUMBER,
PHONETYPE,
TITLE,
SUFFIX,
ISANONYMOUSGIFT,
DONOTCALL,
DONOTMAIL,
DONOTEMAIL,
CREDITCARDTYPE,
CHECKNUMBER,
RECEIPTNUMBER,
CHECKDATE,
SOLICITATIONCHANNNEL,
ISONLINEGIFT,
PAYMENTMETHOD,
POSTSTATUS,
TRIBUTEE,
TRIBUTEMESSAGE,
MATCHINGGIFTCLAIMED,
MATCHINGGIFTPAID,
MATCHINGGIFTORGANIZATION,
ISPREVIOUSDONOR,
REGISTRANTROLE,
COMPANYNAME,
COMPANYADDRESS,
COMPANYADDRESSTYPE,
COMPANYCITY,
COMPANYSTATE,
COMPANYZIP,
COMPANYPHONE,
FAFDONATION.COMPANYEMAIL,
FAFDONATION.COMPANYWEBADDRESS,
ISCOMPANYDONATION,
REVENUEDATECHANGED,
REVENUECHANGEDBY,
DONORLOOKUPID,
REVENUEID,
ORGCONSTITUENTID,
DESIGNATIONID
)
select ID =
case isnull(foir.ID, '00000000-0000-0000-0000-000000000000')
when '00000000-0000-0000-0000-000000000000' then FAFDONATION.CONSTITUENTID
else foir.INDIVIDUALCONSTITUENTID
end
,
GIFTDATE,
FIRSTNAME,
LASTNAME,
FAFDONATION.AMOUNT,
RECOGNITION,
TRANSACTIONTYPE,
GROUPNAME,
GROUPTYPE,
FAFDONATION.EMAIL,
REVENUELOOKUPID,
INBOUNDCHANNEL,
ADDRESS,
ADDRESSTYPE,
CITY,
STATE,
ZIP,
COUNTRY,
PHONENUMBER,
PHONETYPE,
TITLE,
SUFFIX,
ISANONYMOUSGIFT,
DONOTCALL,
DONOTMAIL,
DONOTEMAIL,
CREDITCARDTYPE,
CHECKNUMBER,
RECEIPTNUMBER,
CHECKDATE,
SOLICITATIONCHANNNEL,
ISONLINEGIFT =
case ISONLINEGIFT
when 1 then 'Online'
else 'Offline'
end,
PAYMENTMETHOD,
POSTSTATUS,
TRIBUTEE,
TRIBUTEMESSAGE,
MATCHINGGIFTCLAIMED,
MATCHINGGIFTPAID,
MATCHINGGIFTORGANIZATION,
ISPREVIOUSDONOR,
REGISTRANTROLE,
COMPANYNAME,
COMPANYADDRESS,
COMPANYADDRESSTYPE,
COMPANYCITY,
COMPANYSTATE,
COMPANYZIP,
COMPANYPHONE,
FAFDONATION.COMPANYEMAIL,
FAFDONATION.COMPANYWEBADDRESS,
ISCOMPANYDONATION,
REVENUEDATECHANGED,
REVENUECHANGEDBY,
DONORLOOKUPID,
FAFDONATION.REVENUEID,
ORGCONSTITUENTID =
case isnull(foir.ID, '00000000-0000-0000-0000-000000000000')
when '00000000-0000-0000-0000-000000000000' then FAFDONATION.CONSTITUENTID
else foir.ORGANIZATIONCONSTITUENTID
end,
RS.DESIGNATIONID
from dbo.FAFDONATION
left join dbo.FAFORGANIZATIONINDIVIDUALRELATION foir
on foir.REVENUEID = FAFDONATION.REVENUEID
left join dbo.REVENUESPLIT RS
on RS.REVENUEID=FAFDONATION.REVENUEID and RS.APPLICATIONCODE in (0,7,17)
where FAFDONATION.EVENTID = @ID
end
-- inactive events
else begin
insert @results (
ID,
GIFTDATE,
FIRSTNAME,
LASTNAME,
AMOUNT,
RECOGNITION,
TRANSACTIONTYPE,
GROUPNAME,
GROUPTYPE,
FAFDONATION.EMAIL,
REVENUELOOKUPID,
INBOUNDCHANNEL,
ADDRESS,
ADDRESSTYPE,
CITY,
STATE,
ZIP,
COUNTRY,
PHONENUMBER,
PHONETYPE,
TITLE,
SUFFIX,
ISANONYMOUSGIFT,
DONOTCALL,
DONOTMAIL,
DONOTEMAIL,
CREDITCARDTYPE,
CHECKNUMBER,
RECEIPTNUMBER,
CHECKDATE,
SOLICITATIONCHANNNEL,
ISONLINEGIFT,
PAYMENTMETHOD,
POSTSTATUS,
TRIBUTEE,
TRIBUTEMESSAGE,
MATCHINGGIFTCLAIMED,
MATCHINGGIFTPAID,
MATCHINGGIFTORGANIZATION,
ISPREVIOUSDONOR,
REGISTRANTROLE,
COMPANYNAME,
COMPANYADDRESS,
COMPANYADDRESSTYPE,
COMPANYCITY,
COMPANYSTATE,
COMPANYZIP,
COMPANYPHONE,
FAFDONATION.COMPANYEMAIL,
FAFDONATION.COMPANYWEBADDRESS,
ISCOMPANYDONATION,
REVENUEDATECHANGED,
REVENUECHANGEDBY,
DONORLOOKUPID,
REVENUEID,
ORGCONSTITUENTID,
DESIGNATIONID
)
select ID =
case isnull(foir.ID, '00000000-0000-0000-0000-000000000000')
when '00000000-0000-0000-0000-000000000000' then FAFDONATION.CONSTITUENTID
else foir.INDIVIDUALCONSTITUENTID
end
,
GIFTDATE,
FIRSTNAME,
LASTNAME,
FAFDONATION.AMOUNT,
RECOGNITION,
TRANSACTIONTYPE,
GROUPNAME,
GROUPTYPE,
FAFDONATION.EMAIL,
REVENUELOOKUPID,
INBOUNDCHANNEL,
ADDRESS,
ADDRESSTYPE,
CITY,
STATE,
ZIP,
COUNTRY,
PHONENUMBER,
PHONETYPE,
TITLE,
SUFFIX,
ISANONYMOUSGIFT,
DONOTCALL,
DONOTMAIL,
DONOTEMAIL,
CREDITCARDTYPE,
CHECKNUMBER,
RECEIPTNUMBER,
CHECKDATE,
SOLICITATIONCHANNNEL,
ISONLINEGIFT =
case ISONLINEGIFT
when 1 then 'Online'
else 'Offline'
end,
PAYMENTMETHOD,
POSTSTATUS,
TRIBUTEE,
TRIBUTEMESSAGE,
MATCHINGGIFTCLAIMED,
MATCHINGGIFTPAID,
MATCHINGGIFTORGANIZATION,
ISPREVIOUSDONOR,
REGISTRANTROLE,
COMPANYNAME,
COMPANYADDRESS,
COMPANYADDRESSTYPE,
COMPANYCITY,
COMPANYSTATE,
COMPANYZIP,
COMPANYPHONE,
FAFDONATION.COMPANYEMAIL,
FAFDONATION.COMPANYWEBADDRESS,
ISCOMPANYDONATION,
REVENUEDATECHANGED,
REVENUECHANGEDBY,
DONORLOOKUPID,
FAFDONATION.REVENUEID,
ORGCONSTITUENTID =
case isnull(foir.ID, '00000000-0000-0000-0000-000000000000')
when '00000000-0000-0000-0000-000000000000' then FAFDONATION.CONSTITUENTID
else foir.ORGANIZATIONCONSTITUENTID
end,
RS.DESIGNATIONID
from dbo.V_FAF_DONATION as FAFDONATION
left join dbo.FAFORGANIZATIONINDIVIDUALRELATION foir
on foir.REVENUEID = FAFDONATION.REVENUEID
left join dbo.REVENUESPLIT RS
on RS.REVENUEID=FAFDONATION.REVENUEID and RS.APPLICATIONCODE in (0,7,17)
where FAFDONATION.EVENTID = @ID
end
return
end