USP_DATALIST_CONSTITUENTRECOGNITIONREVENUE

CREATE procedure dbo.USP_DATALIST_CONSTITUENTRECOGNITIONREVENUE
(
  @CONSTITUENTRECOGNITIONID uniqueidentifier,
  @YEARID uniqueidentifier = null,
  @FROMDATE datetime = null,
  @TODATE datetime = null
)
as
  set nocount on;

  declare @ORGANIZATIONCURRENCYID uniqueidentifier;
  declare @DECIMALDIGITS tinyint;
  declare @ROUNDINGTYPECODE tinyint;
  declare @CURRENCYID uniqueidentifier;

  select @CURRENCYID = BASECURRENCYID from dbo.CONSTITUENTRECOGNITION where ID = @CONSTITUENTRECOGNITIONID;
  if @CURRENCYID is null
    set @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

  select 
    @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),
    @DECIMALDIGITS = DECIMALDIGITS,
    @ROUNDINGTYPECODE = ROUNDINGTYPECODE
  from 
    dbo.CURRENCY
  where
    CURRENCY.ID = @CURRENCYID;

  declare @RECOGNITIONPROGRAMID uniqueidentifier;
  declare @CONSTITUENTID uniqueidentifier;

  declare @STARTDATE datetime;
  declare @RECOGNITIONTYPE tinyint;
  declare @EXPIRESONCODE tinyint;
  declare @YEARSTART datetime;
  declare @REVENUESELECTIONID uniqueidentifier;
  declare @EXPDATE datetime;
  declare @PLANNEDGIFTCODE tinyint;

  select @STARTDATE = STARTDATE,
    @RECOGNITIONTYPE = TYPECODE,
    @EXPIRESONCODE = EXPIRESONCODE,
    @REVENUESELECTIONID = SELECTIONID,
    @CONSTITUENTID = CR.CONSTITUENTID,
    @RECOGNITIONPROGRAMID = RP.ID,
    @PLANNEDGIFTCODE = RP.PLANNEDGIFTCODE
  from dbo.RECOGNITIONPROGRAM RP
  inner join dbo.CONSTITUENTRECOGNITION CR on RP.ID = CR.RECOGNITIONPROGRAMID
  where CR.ID = @CONSTITUENTRECOGNITIONID;

  if @YEARID is not null
  --if @RECOGNITIONTYPE = 0 --annual program

    begin
    select @EXPDATE = EXPIRATIONDATE
    from dbo.CONSTITUENTRECOGNITION
    where ID = @YEARID;

    if @EXPIRESONCODE = 1 --calendar year

      begin
      set @YEARSTART = dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@EXPDATE,0);
      set @EXPDATE = dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(@EXPDATE,1);
      end
    else    --fiscal year

      begin
      set @YEARSTART = dbo.UFN_DATE_THISFISCALYEAR_FIRSTDAY(@EXPDATE,0);
      set @EXPDATE = dbo.UFN_DATE_THISFISCALYEAR_LASTDAY(@EXPDATE,1);
      end

    if @STARTDATE < @YEARSTART or @STARTDATE is null
      set @STARTDATE = @YEARSTART;

    end

  if @STARTDATE < @FROMDATE or @STARTDATE is null
    set @STARTDATE = @FROMDATE;

  if @EXPDATE > @TODATE or @EXPDATE is null 
    set @EXPDATE = @TODATE;

  select
    RC.ID as ID,
    CRR.EFFECTIVEDATE,
    RC.DESCRIPTION AS TYPE,
    CRR.AMOUNTINCURRENCY as AMOUNT,
    RS.AMOUNTINCURRENCY as GIFTAMOUNT,
    RC.DONOR,
    RC.TRANSACTIONTYPE as TRANSACTIONTYPE,
    case when RC.RECOGNITIONCREDITTYPECODE <> -1
      then null --disable go to revenue for internal donor challenge credits

      else R.ID 
    end as RECORDID,
    RC.DATEADDED,
    dbo.UFN_DESIGNATION_BUILDNAME(RC.DESIGNATIONID) as DESIGNATIONNAME,
    @CURRENCYID as BASECURRENCYID
  from dbo.UFN_RECOGNITIONCREDIT_GETRECOGNITIONS_BYCONSTITUENT(@CONSTITUENTID, 1, null, @CURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @ORGANIZATIONCURRENCYID) RC
    inner join dbo.UFN_CONSTITUENTRECOGNITIONREVENUE_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) AS CRR on CRR.REVENUERECOGNITIONID = RC.ID
    left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RS
      on RC.REVENUESPLITID = RS.ID
    left join dbo.FINANCIALTRANSACTION as R on RS.REVENUEID = R.ID
  where CRR.RECOGNITIONPROGRAMID = @RECOGNITIONPROGRAMID
    AND (CRR.EFFECTIVEDATE >= @STARTDATE or @STARTDATE is null)
    AND (CRR.EFFECTIVEDATE <= @EXPDATE or @EXPDATE is null)
    and R.DELETEDON is null
  order by CRR.EFFECTIVEDATE desc, RC.DATEADDED desc, CRR.AMOUNTINCURRENCY desc