USP_DATALIST_FAF_GIFT_ACKNOWLEDGEMENT

CREATE procedure dbo.USP_DATALIST_FAF_GIFT_ACKNOWLEDGEMENT
(
    @CLIENTUSERSID int,
    @EVENTID uniqueidentifier,
    @REVENUESPLITID uniqueidentifier = null
)
as
    set nocount on;  

    DECLARE @CONSTITUENTID uniqueidentifier  
    DECLARE @APPEALID uniqueidentifier  

    SELECT @APPEALID=APPEALID FROM dbo.[EVENT] (NOLOCK) WHERE ID=@EVENTID   

    IF @REVENUESPLITID IS NOT NULL  
        SELECT @CONSTITUENTID = R.CONSTITUENTID  
        FROM dbo.REVENUERECOGNITION R (NOLOCK)  
        INNER JOIN dbo.REVENUESPLIT RS (NOLOCK)  
        ON R.REVENUESPLITID = RS.ID  
        WHERE RS.ID = @REVENUESPLITID  

    IF ISNULL(@CLIENTUSERSID,-1) > 0  
        SET @CONSTITUENTID = dbo.UFN_CONSTITUENT_GET_BY_CLIENTUSERID(@CLIENTUSERSID)  

    declare @REVENUEIDS as table 
    (
        REVENUESPLITID uniqueidentifier, 
        REVENUEID uniqueidentifier
    )

    insert into @REVENUEIDS
        select REVENUESPLIT.ID as REVENUESPLITID,  REVENUESPLIT.REVENUEID  from dbo.REVENUESPLIT (NOLOCK) 
        where ID in (select REVENUESPLITID from dbo.REVENUERECOGNITION (NOLOCK) where CONSTITUENTID = @CONSTITUENTID)

    ;with [R] as
    (
        select ID, CONSTITUENTID, DATE, TRANSACTIONTYPE 
        from dbo.REVENUE (NOLOCK) 
        where REVENUE.ID in (select distinct REVENUEID from @REVENUEIDS)
        and REVENUE.APPEALID = @APPEALID 
    ),
    [RS] as  
    (  
     select   
      REVENUESPLIT.ID as REVENUESPLITID,   
      REVENUESPLIT.REVENUEID,   
      REVENUESPLIT.AMOUNT as DONATIONAMOUNT,  
      R.CONSTITUENTID as DONORCONSTITUENTID,  
      R.DATE as [DONATIONDATE],  
      R.TRANSACTIONTYPE  
     from dbo.REVENUESPLIT (NOLOCK)
     inner join [R] ON REVENUESPLIT.REVENUEID = R.ID 
     where 
      REVENUESPLIT.ID in (select REVENUESPLITID from @REVENUEIDS) and REVENUESPLIT.TYPECODE=0 and ISNULL(REVENUESPLIT.AMOUNT,0) > 0  
      and REVENUESPLIT.APPLICATION <> 'matching gift' --remove payments associated to matching gift, since the pledge is already there.

    )  

    SELECT  RS.DONORCONSTITUENTID,    
          RS.DONATIONDATE,   
          RS.DONATIONAMOUNT,   
          @CONSTITUENTID as CONSTITUENTID,       -- Registrant / Team constituent  

          ISNULL(GA.ISACKNOWLEDGED,0) as ISACKNOWLEDGED,  
          RS.REVENUEID,  
          RS.REVENUESPLITID,  
          C.NAME,  
          RS.TRANSACTIONTYPE  
    FROM [RS]  
    LEFT JOIN [dbo].[INSTALLMENTPAYMENT] IP (NOLOCK)   
    ON RS.REVENUESPLITID = IP.PAYMENTID  
    LEFT OUTER JOIN dbo.FAFGIFTACKNOWLEDGEMENT GA (NOLOCK)  
    ON GA.REVENUESPLITID = RS.REVENUESPLITID AND GA.EVENTID = @EVENTID  
    LEFT OUTER JOIN dbo.CONSTITUENT C (NOLOCK)  
    ON C.ID = DONORCONSTITUENTID  
    where DONORCONSTITUENTID <> @CONSTITUENTID  
    AND RS.REVENUESPLITID = ISNULL(@REVENUESPLITID,RS.REVENUESPLITID)  
    AND IP.INSTALLMENTID IS NULL