UFN_QUERY_FAFDONATION_LISTBUILDER

CREATE function dbo.UFN_QUERY_FAFDONATION_LISTBUILDER
(
  @ID uniqueidentifier
)
returns table as return(
  SELECT
    FAFDONATIONCONSTITUENT.ID,
    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,
    TRIBUTETYPE,
    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

    /*#EXTENSION*/

  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)
  inner join (
      select
        FD2.ID AS RECORDID,
        case isnull(foir2.ID, '00000000-0000-0000-0000-000000000000') when '00000000-0000-0000-0000-000000000000' then FD2.CONSTITUENTID else foir2.INDIVIDUALCONSTITUENTID END as ID
      from FAFDONATION FD2
      left join dbo.FAFORGANIZATIONINDIVIDUALRELATION foir2
        on foir2.REVENUEID = FD2.REVENUEID
  ) as FAFDONATIONCONSTITUENT
    on FAFDONATIONCONSTITUENT.RECORDID=FAFDONATION.ID

  where FAFDONATION.EVENTID = @ID
    and exists(select 1 from dbo.FAFDONATION where eventID=@ID)

  union all

  select
    FAFDONATIONCONSTITUENT.ID,
    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,
    TRIBUTETYPE,
    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

        /*#EXTENSION*/

  from dbo.V_FAF_DONATION as FAFDONATION
  left join dbo.FAFORGANIZATIONINDIVIDUALRELATION foir
    on foir.REVENUEID = FAFDONATION.REVENUEID
  inner join (
      select
        FD2.ID as RECORDID,
        case isnull(foir2.ID, '00000000-0000-0000-0000-000000000000') when '00000000-0000-0000-0000-000000000000' then FD2.CONSTITUENTID else foir2.INDIVIDUALCONSTITUENTID END as ID
      from FAFDONATION FD2
      left join dbo.FAFORGANIZATIONINDIVIDUALRELATION foir2
        on foir2.REVENUEID = FD2.REVENUEID
  ) as FAFDONATIONCONSTITUENT
    on FAFDONATIONCONSTITUENT.RECORDID=FAFDONATION.ID
  left join dbo.REVENUESPLIT rs
    on rs.REVENUEID=FAFDONATION.REVENUEID and RS.APPLICATIONCODE in (0,7,17)    
  where FAFDONATION.EVENTID = @ID
  and not exists(select 1 from dbo.FAFDONATION where eventid = @ID)
)