USP_DATALIST_RECONCILE_MATCHINGGIFTSCLAIMS

CREATE procedure dbo.USP_DATALIST_RECONCILE_MATCHINGGIFTSCLAIMS
(
  @CONSTITUENTID uniqueidentifier = null,
  @COMMITMENTS smallint = 0,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  declare @NOW datetime = getdate();

  declare @STARTDATE datetime;
  declare @ENDDATE datetime;

  if @COMMITMENTS = 0 --Past 6 months

  begin
    set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@NOW);
    set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(DATEADD(month,-6, @NOW));
  end
  else if @COMMITMENTS = 1 --This year

  begin
    set @STARTDATE = dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@NOW,0);
    set @ENDDATE = dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@NOW,1);
  end
  else if @COMMITMENTS = 2 --Last year

  begin
    set @STARTDATE = dbo.UFN_DATE_LASTCALENDARYEAR_FIRSTDAY(@NOW,0);
    set @ENDDATE = dbo.UFN_DATE_LASTCALENDARYEAR_LASTDAY(@NOW,1);
  end

  declare @FILTERED_REVENUESPLIT table
  (
    REVENUEID uniqueidentifier,
    REVENUESPLITID uniqueidentifier,
    CONSTITUENTID uniqueidentifier,
    TRANSACTIONTYPECODE tinyint,
    CONSTITUENTNAME nvarchar(400),
    APPLIED money,
    CLAIMAMOUNT money,
    DATE datetime,
    DESIGNATIONLOOKUPID nvarchar(512),
    DESIGNATIONID uniqueidentifier,
    CONSTITUENTLOOKUPID nvarchar(200),
    TRANSACTIONCURRENCYID uniqueidentifier,
    DATEADDED datetime
  );

  insert into @FILTERED_REVENUESPLIT
  (
    REVENUEID,
    REVENUESPLITID,
    CONSTITUENTID,
    TRANSACTIONTYPECODE,
    CONSTITUENTNAME,
    APPLIED,
    DATE,
    DESIGNATIONLOOKUPID,
    DESIGNATIONID,
    CONSTITUENTLOOKUPID,
    TRANSACTIONCURRENCYID,
    DATEADDED
  )
  select
    R.ID,
    RS.ID as REVENUESPLITID,
    C.ID as CONSTITUENTID,
    R.TRANSACTIONTYPECODE,
    C1.NAME as CONSTITUENTNAME, 
    0 as APPLIED,
    R.DATE as DATE,
    D.USERID as DESIGNATIONLOOKUPID,
    RS.DESIGNATIONID,
    C1.LOOKUPID as CONSTITUENTLOOKUPID,
    RS.TRANSACTIONCURRENCYID,
    RS.DATEADDED
  from
    dbo.REVENUE R
  inner join
    dbo.REVENUESPLIT RS on R.ID = RS.REVENUEID
  inner join
    dbo.CONSTITUENT C on R.CONSTITUENTID = C.ID
  inner join
    dbo.DESIGNATION D on RS.DESIGNATIONID = D.ID
  left join
    dbo.REVENUEMATCHINGGIFT MG1 on MG1.ID = R.ID
  left join
    dbo.REVENUE R1 on R1.ID = MG1.MGSOURCEREVENUEID
  left join
    dbo.CONSTITUENT C1 on C1.ID = R1.CONSTITUENTID
  where
    R.TRANSACTIONTYPECODE = 3 and
    R.DATE >= @STARTDATE and
    R.DATE <= @ENDDATE and
    (
      C.ID = @CONSTITUENTID 
      -- AdamBu - Bug 16530 - Include matching gift claims from orgs to which the given constituent is a parent corp.

      or c.ID in
      (
        select ID
        from ORGANIZATIONDATA
        where PARENTCORPID = @CONSTITUENTID
      )
    );

  select
    RS.REVENUESPLITID,
    RS.CONSTITUENTID,
    RS.TRANSACTIONTYPECODE,
    RS.CONSTITUENTNAME,
    RS.APPLIED,
    INSTALLMENTSPLIT.BALANCE as CLAIMAMOUNT,
    RS.DATE,
    RS.DESIGNATIONLOOKUPID,
    RS.DESIGNATIONID,
    RS.CONSTITUENTLOOKUPID,
    RS.TRANSACTIONCURRENCYID,
    RS.DATEADDED
  from
    @FILTERED_REVENUESPLIT RS
  --JamesWill WI180281 2012-01-03 Only return records to which the user has site access

  inner join
    dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS_BULK(@CURRENTAPPUSERID) ALLOWED on ALLOWED.ID = RS.REVENUEID
  inner join
    dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITBALANCE_BULK() as INSTALLMENTSPLIT
      on INSTALLMENTSPLIT.PLEDGEID = RS.REVENUEID and INSTALLMENTSPLIT.DESIGNATIONID = RS.DESIGNATIONID
  where
    ALLOWED.ACCESSGRANTED = 1 and
    INSTALLMENTSPLIT.BALANCE > 0
  order by
    RS.DATE,RS.DATEADDED;