USP_DATALIST_DONORCHALLENGEDESIGNATIONS

CREATE procedure dbo.USP_DATALIST_DONORCHALLENGEDESIGNATIONS
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier
)
as                 
  set nocount on;

  declare @BASECURRENCYID uniqueidentifier;
  select
    @BASECURRENCYID = BASECURRENCYID
  from
    dbo.DONORCHALLENGE
  where
    ID = @ID;

  with DESIGNATIONAMOUNTS_CTE as
  (
    --Note this shows all funds given to a designation regardless of if they were all mapped to the same designation

    select
      RS.DESIGNATIONID,
      DCE.STATUSTYPECODE,
      coalesce(sum(
        case
            when @BASECURRENCYID = RS.TRANSACTIONCURRENCYID
                then RS.TRANSACTIONAMOUNT
            else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RS.ID, @BASECURRENCYID)
        end
      ),0) GIVENAMOUNT,
      coalesce(sum(DCE.AMOUNT),0) CHALLENGEAMOUNT
    from
      dbo.DONORCHALLENGEENCUMBERED DCE
    inner join
      dbo.REVENUESPLIT RS on RS.ID = DCE.REVENUESPLITID
    inner join
      dbo.DESIGNATION D on D.ID = RS.DESIGNATIONID
    inner join
      dbo.REVENUE R on RS.REVENUEID = R.ID
    where
      DCE.DONORCHALLENGEID = @ID
      and (DCE.STATUSTYPECODE = 0 or (DCE.STATUSTYPECODE = 1 and R.TRANSACTIONTYPECODE = 0))
    group by
      RS.DESIGNATIONID, DCE.STATUSTYPECODE
  )
  select
    DCDM.ID,
    DCDM.DESIGNATIONID,
    dbo.UFN_DESIGNATION_BUILDNAME(DCDM.DESIGNATIONID) as DESIGNATIONNAME,
    OD.USERID as DESIGNATIONLOOKUPID,
    (select coalesce(sum(GIVENAMOUNT),0) from DESIGNATIONAMOUNTS_CTE where DESIGNATIONAMOUNTS_CTE.DESIGNATIONID = DCDM.DESIGNATIONID) as AMOUNTGIVEN,
    (select coalesce(sum(CHALLENGEAMOUNT),0) from DESIGNATIONAMOUNTS_CTE where DESIGNATIONAMOUNTS_CTE.DESIGNATIONID = DCDM.DESIGNATIONID and STATUSTYPECODE = 0) as AMOUNTENCUMBERED,
    (select coalesce(sum(CHALLENGEAMOUNT),0) from DESIGNATIONAMOUNTS_CTE where DESIGNATIONAMOUNTS_CTE.DESIGNATIONID = DCDM.DESIGNATIONID and STATUSTYPECODE = 1) as AMOUNTMATCHED,
    DCDM.MATCHINGDESIGNATIONID,
    dbo.UFN_DESIGNATION_BUILDNAME(DCDM.MATCHINGDESIGNATIONID) as MATCHINGDESIGNATIONNAME,
    MD.USERID as MATCHINGDESIGNATIONLOOKUPID,
    @BASECURRENCYID
  from
    dbo.DONORCHALLENGEDESIGNATIONMAP DCDM
  inner join
    dbo.DESIGNATION OD on OD.ID = DCDM.DESIGNATIONID
  left join
    dbo.DESIGNATION MD on MD.ID = DCDM.MATCHINGDESIGNATIONID
  where
    DONORCHALLENGEID = @ID
  order by
    DESIGNATIONNAME