USP_DATALIST_REGISTRANTRECOGINITIONTRANSACTIONS

CREATE procedure dbo.USP_DATALIST_REGISTRANTRECOGINITIONTRANSACTIONS
(
  @CONTEXTID uniqueidentifier,
  @TRANSACTIONTYPECODE tinyint = null,
  @NAME nvarchar(210) = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
as

  select R.ID, CDONOR.ID as DONORCONSTITUENTID, CDONOR.NAME, 
      RS.AMOUNT, 
    R.[DATE], 
    R.TRANSACTIONTYPE,
    R.LOOKUPID, 
    RPM.PAYMENTMETHOD,
    RS.[TYPE],
        RS.APPLICATION

      from REGISTRANT RG
    join EVENT E on RG.EVENTID = E.ID
      join REVENUERECOGNITION RR on RG.CONSTITUENTID = RR.CONSTITUENTID
      join REVENUESPLIT RS on RR.REVENUESPLITID = RS.ID 
      join REVENUE R on RS.REVENUEID = R.ID and E.APPEALID = R.APPEALID and (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 1, 7, 15, 17) OR R.TRANSACTIONTYPECODE IN (1,2) )
    left join REVENUEPAYMENTMETHOD RPM on RPM.REVENUEID = R.ID
      left join CONSTITUENT CDONOR on CDONOR.ID = R.CONSTITUENTID    
      where RG.ID = @CONTEXTID
    and (R.TRANSACTIONTYPECODE = @TRANSACTIONTYPECODE or @TRANSACTIONTYPECODE is null)
    and (CDONOR.NAME like '%' + @NAME + '%' or @NAME is null)
    and (
          @SITEFILTERMODE = 0
          or (
                exists(
                        select 1 from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RS.ID) REVENUESITE
                        where REVENUESITE.SITEID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
                      )
              )
        )
  -- Check site security
    and (
          exists(
            select 1 from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RS.ID) REVENUESITE
            where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVENUESITE].[SITEID] or (SITEID is null and [REVENUESITE].[SITEID] is null))) )
        )


    union

     select R.ID, CDONOR.ID as DONORCONSTITUENTID, CDONOR.NAME, 
      RS.AMOUNT, 
    R.[DATE], 
    R.TRANSACTIONTYPE,
    R.LOOKUPID, 
    RPM.PAYMENTMETHOD,
    RS.[TYPE],
    RS.APPLICATION

     from REGISTRANT RG
   join dbo.EVENT E on RG.EVENTID = E.ID
     join REVENUE R on R.CONSTITUENTID = RG.CONSTITUENTID and R.APPEALID = E.APPEALID
     join REVENUESPLIT RS on RS.REVENUEID= R.ID and (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0, 1, 7, 15, 17) OR R.TRANSACTIONTYPECODE IN (1,2) )
   left join REVENUEPAYMENTMETHOD RPM on RPM.REVENUEID = R.ID
     left join CONSTITUENT CDONOR on CDONOR.ID = R.CONSTITUENTID    
     where RG.ID = @CONTEXTID
    and (R.TRANSACTIONTYPECODE = @TRANSACTIONTYPECODE or @TRANSACTIONTYPECODE is null)
    and (CDONOR.NAME like '%' + @NAME + '%' or @NAME is null)
    and (
          @SITEFILTERMODE = 0
          or (
                exists(
                        select 1 from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RS.ID) REVENUESITE
                        where REVENUESITE.SITEID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
                      )
              )
        )
  -- Check site security
    and (
          exists(
            select 1 from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RS.ID) REVENUESITE
            where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVENUESITE].[SITEID] or (SITEID is null and [REVENUESITE].[SITEID] is null))) )
        )