USP_DATALIST_REVENUEOFFLINEDONATION

CREATE procedure dbo.USP_DATALIST_REVENUEOFFLINEDONATION
(
    @REGISTRANTID uniqueidentifier
)
as
    set nocount on;

    select ROD.ID, ROD.REGISTRANTID, ROD.ADDRESSBOOKFAFID, (case when ABF.FIRSTNAME IS null then '' else ABF.FIRSTNAME +' ' end) + ABF.LASTNAME as DONORNAME, R.AMOUNT, RPM.PAYMENTMETHODCODE,
        --COALESCE(tISP.PAYMENTMETHOD,RPM.PAYMENTMETHOD) AS PAYMENTTYPE, 

        --COALESCE(tISP.CHECKNUMBER, ROD.CHECKNUMBER) as CHECKNUMBER, 

        ROD.CHECKNUMBER,
        ROD.EXPECTEDDATE, 
        tISP.AMOUNT as AMOUNTCONFIRMED, 
        tISP.LOOKUPID as GIFTID, case when tISP.PLEDGEID is not null then 1 else 0 end as ISGIFTCONFIRMED,
        MERCH.ZIPCODE AS LOCALCORPZIP,
        ROD.LOCALCORPNAME,
        ABF.FIRSTNAME,
        ABF.LASTNAME,
        ABF.EMAILADDRESS
    FROM dbo.REVENUEOFFLINEDONATION ROD
  join dbo.REGISTRANT RG on RG.ID = ROD.REGISTRANTID
  join dbo.REVENUE R on ROD.ID = R.ID
  left join dbo.REVENUEPAYMENTMETHOD RPM on RPM.REVENUEID = R.ID  
  left join dbo.REVENUESPLIT RS on RS.REVENUEID = R.ID
  left join ( select 
      ISP.PLEDGEID, SUM(RS.AMOUNT) as AMOUNT, 
      dbo.UDA_BUILDLIST(pR.LOOKUPID) as LOOKUPID,
      dbo.UDA_BUILDLIST(pRPM.PAYMENTMETHOD) as PAYMENTMETHOD, 
      dbo.UDA_BUILDLIST(pCPMD.CHECKNUMBER) as CHECKNUMBER
          from dbo.INSTALLMENTSPLITPAYMENT ISP 
          join dbo.REVENUEOFFLINEDONATION ROD on ISP.PLEDGEID = ROD.ID and ROD.REGISTRANTID = @REGISTRANTID
          left join dbo.REVENUESPLIT RS on RS.ID = ISP.PAYMENTID
          left join dbo.REVENUE pR on pR.ID = RS.REVENUEID 
          left join dbo.REVENUEPAYMENTMETHOD pRPM on pRPM.REVENUEID = pR.ID
          left join dbo.CHECKPAYMENTMETHODDETAIL pCPMD on pCPMD.ID = pRPM.ID
          group by ISP.PLEDGEID
    ) tISP on tISP.PLEDGEID = R.ID
    LEFT JOIN
    (
        SELECT RS.REVENUEID , L.MERCHANTACCT, L.ZIPCODE 
        FROM dbo.LOCALCORP L(NOLOCK)
        JOIN dbo.BBNCDESIGNATIONIDMAP B(NOLOCK) ON (B.DESIGNATIONID = L.DESIGNATIONID)
        JOIN dbo.ClientMerchantAccounts M(NOLOCK) ON (M.Name = L.MERCHANTACCT)
        JOIN REVENUESPLIT RS (NOLOCK) on RS.DESIGNATIONID = B.DESIGNATIONID 
    ) MERCH ON MERCH.REVENUEID = R.ID
    LEFT JOIN
    ADDRESSBOOKFAF ABF ON ABF.ID = ROD.ADDRESSBOOKFAFID
    where ROD.REGISTRANTID = @REGISTRANTID