USP_REPORT_CAMPAIGNPRIORITY

            CREATE procedure dbo.USP_REPORT_CAMPAIGNPRIORITY
                        (
                @CAMPAIGNID uniqueidentifier,
                @CAMPAIGNPRIORITYTYPECODEID uniqueidentifier = null,
                @GROUPBY tinyint,
                @STARTDATE datetime,
                @ENDDATE datetime,
                @CURRENCYCODE tinyint = null --0 = Record base, (null, 1) = Organization

            )
            as
                set nocount on;

        declare @SELECTEDCURRENCYID uniqueidentifier;
        declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
        declare @DECIMALDIGITS tinyint;
        declare @ROUNDINGTYPECODE tinyint;

        if coalesce(@CURRENCYCODE, 1) = 1
        begin
            set @SELECTEDCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
        end

        if @SELECTEDCURRENCYID is null
        begin
          select
            @SELECTEDCURRENCYID = CAMPAIGN.BASECURRENCYID
          from dbo.CAMPAIGN where ID = @CAMPAIGNID
        end

        select
              @DECIMALDIGITS=CURRENCY.DECIMALDIGITS,
              @ROUNDINGTYPECODE=CURRENCY.ROUNDINGTYPECODE  
          from dbo.CURRENCY
          where ID = @SELECTEDCURRENCYID

        if @GROUPBY = 1
        begin
            exec dbo.USP_REPORT_CAMPAIGNPRIORITYPROGRESSBYQUARTERINCURRENCY @CAMPAIGNID, @CAMPAIGNPRIORITYTYPECODEID, @STARTDATE, @ENDDATE, @CURRENCYCODE, @SELECTEDCURRENCYID;
        end
        else
        begin                    
            if @GROUPBY = 2
            begin
                WITH CAMPAIGNPRIORITY_CTE as (
                select
                  CAMPAIGNPRIORITY.ID as CAMPAIGNPRIORITYID,
                  CAMPAIGNSUBPRIORITY.ID as CAMPAIGNSUBPRIORITYID,
                  CAMPAIGNPRIORITY.CAMPAIGNPRIORITYTYPECODEID as CAMPAIGNPRIORITYTYPECODEID,
                  PERIODS.STARTDATE as STARTDATE,
                  PERIODS.ENDDATE as ENDDATE,
                  PERIODS.PERIODYEAR as YEAR,
                  CAMPAIGNPRIORITY.GOAL as CAMPAIGNPRIORITYGOAL,
                  CAMPAIGNSUBPRIORITY.GOAL as CAMPAIGNSUBPRIORITYGOAL,
                  CAMPAIGNSUBPRIORITYNAMECODE.DESCRIPTION as DESCRIPTION,
                  CAMPAIGNPRIORITY.ORGANIZATIONAMOUNT as CAMPAIGNPRIORITYORGANIZATIONAMOUNT,
                  CAMPAIGNSUBPRIORITY.ORGANIZATIONAMOUNT as CAMPAIGNSUBPRIORITYORGANIZATIONAMOUNT,
                  CAMPAIGN.BASECURRENCYID as BASECURRENCYID,
                  CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
                  CURRENCYPROPERTIES.CURRENCYSYMBOL,
                  CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE
                from
                dbo.UFN_CAMPAIGNPRIORITYREPORT_GETPERIODS(@STARTDATE, @ENDDATE, 3) as PERIODS
                cross join dbo.CAMPAIGNPRIORITY
                inner join dbo.CAMPAIGN on CAMPAIGNPRIORITY.CAMPAIGNID = CAMPAIGN.ID
                left join dbo.CAMPAIGNSUBPRIORITY on CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID = CAMPAIGNPRIORITY.ID
                left join dbo.CAMPAIGNSUBPRIORITYNAMECODE on CAMPAIGNSUBPRIORITY.CAMPAIGNSUBPRIORITYNAMECODEID = CAMPAIGNSUBPRIORITYNAMECODE.ID
                outer apply
                            dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) as CURRENCYPROPERTIES  
                where
                CAMPAIGNPRIORITY.CAMPAIGNID = @CAMPAIGNID
                and ( (CAMPAIGNPRIORITY.CAMPAIGNPRIORITYTYPECODEID = @CAMPAIGNPRIORITYTYPECODEID) or (@CAMPAIGNPRIORITYTYPECODEID is null) )
                ),CP_TOTALRECEIVED as (
                select 
                    CAMPAIGNPRIORITY.ID as CAMPAIGNPRIORITYID,
                    PERIODS.STARTDATE,
                    PERIODS.ENDDATE,
                    coalesce(sum(RS.AMOUNTINCURRENCY),0) as AMOUNTINCURRENCY
                from
                    dbo.FINANCIALTRANSACTIONLINEITEM 
                inner join dbo.FINANCIALTRANSACTION with (nolock) on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                    inner join REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.id
                inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                inner join dbo.CAMPAIGNSUBPRIORITY on CAMPAIGNSUBPRIORITY.ID = REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID 
                inner join dbo.CAMPAIGNPRIORITY on CAMPAIGNPRIORITY.ID = CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID
                left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as RS on RS.ID = FINANCIALTRANSACTIONLINEITEM.ID
                cross join
                    dbo.UFN_CAMPAIGNPRIORITYREPORT_GETPERIODS(@STARTDATE, @ENDDATE, 3) as PERIODS
                where
                  FINANCIALTRANSACTION.TYPECODE = 0
                and    FINANCIALTRANSACTION.DELETEDON is null
                and REVENUESPLIT_EXT.APPLICATIONCODE in (0, 1, 5, 6, 7, 3, 10)
                and (REVENUESPLIT_EXT.APPLICATIONCODE <> 10 or REVENUESPLIT_EXT.TYPECODE = 0)
                and ((FINANCIALTRANSACTION.DATE >= PERIODS.STARTDATE or PERIODS.STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= PERIODS.ENDDATE or PERIODS.ENDDATE is null))        
                group by CAMPAIGNPRIORITY.ID,PERIODS.STARTDATE,PERIODS.ENDDATE
                ), CPS_TOTALRECEIVED as (
                select
                REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID as CAMPAIGNSUBPRIORITYID,
                PERIODS.STARTDATE,
                PERIODS.ENDDATE,
                coalesce(sum(RS.AMOUNTINCURRENCY), 0) as AMOUNTINCURRENCY
                from
                dbo.FINANCIALTRANSACTIONLINEITEM
                inner join dbo.FINANCIALTRANSACTION with (nolock) on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID 
                inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.id
                inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as RS on RS.ID = FINANCIALTRANSACTIONLINEITEM.ID
                cross join
                    dbo.UFN_CAMPAIGNPRIORITYREPORT_GETPERIODS(@STARTDATE, @ENDDATE, 3) as PERIODS
                where
                FINANCIALTRANSACTION.TYPECODE = 0
                and    FINANCIALTRANSACTION.DELETEDON is null
                and REVENUESPLIT_EXT.APPLICATIONCODE in (0, 6, 7, 3)
                and ((FINANCIALTRANSACTION.DATE >= PERIODS.STARTDATE or PERIODS.STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= PERIODS.ENDDATE or PERIODS.ENDDATE is null))        
                group by REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID,PERIODS.STARTDATE,PERIODS.ENDDATE
                ), CP_PAYMENTS as (
                select
                    CAMPAIGNPRIORITY.ID as CAMPAIGNPRIORITYID,
                    PERIODS.STARTDATE,
                    PERIODS.ENDDATE,
                    coalesce(sum(RS.AMOUNTINCURRENCY),0) as AMOUNTINCURRENCY
                from
                    dbo.FINANCIALTRANSACTIONLINEITEM
                inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID 
                inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                inner join dbo.CAMPAIGNSUBPRIORITY on CAMPAIGNSUBPRIORITY.ID = REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID
                inner join dbo.CAMPAIGNPRIORITY on CAMPAIGNPRIORITY.ID = CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID
                left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as RS on RS.ID = FINANCIALTRANSACTIONLINEITEM.ID
                cross join
                    dbo.UFN_CAMPAIGNPRIORITYREPORT_GETPERIODS(@STARTDATE, @ENDDATE, 3) as PERIODS
                where
                FINANCIALTRANSACTION.TYPECODE in (1, 6)
                and    FINANCIALTRANSACTION.DELETEDON is null
                and ((FINANCIALTRANSACTION.DATE >= PERIODS.STARTDATE or PERIODS.STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= PERIODS.ENDDATE or PERIODS.ENDDATE is null))        
                group by CAMPAIGNPRIORITY.ID, PERIODS.STARTDATE, PERIODS.ENDDATE
                ), CP_WRITEOFFS as (
                select
                    CAMPAIGNPRIORITY.ID as CAMPAIGNPRIORITYID,
                    PERIODS.STARTDATE,
                    PERIODS.ENDDATE,
                    coalesce(sum(WO.AMOUNTINCURRENCY),0) as AMOUNTINCURRENCY
                from
                    dbo.FINANCIALTRANSACTIONLINEITEM as WRITEOFFSPLIT
                inner join dbo.FINANCIALTRANSACTION as WRITEOFF on WRITEOFF.ID = WRITEOFFSPLIT.FINANCIALTRANSACTIONID
                inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = WRITEOFF.PARENTID 
                inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                inner join dbo.CAMPAIGNSUBPRIORITY on CAMPAIGNSUBPRIORITY.ID = REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID
                inner join dbo.CAMPAIGNPRIORITY on CAMPAIGNPRIORITY.ID = CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID
                left join dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as WO on WO.ID = WRITEOFFSPLIT.ID
                cross join
                    dbo.UFN_CAMPAIGNPRIORITYREPORT_GETPERIODS(@STARTDATE, @ENDDATE, 3) as PERIODS
                where
                FINANCIALTRANSACTION.TYPECODE in (1, 6)
                and    FINANCIALTRANSACTION.DELETEDON is null
                and ((FINANCIALTRANSACTION.DATE >= PERIODS.STARTDATE or PERIODS.STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= PERIODS.ENDDATE or PERIODS.ENDDATE is null))        
                group by CAMPAIGNPRIORITY.ID, PERIODS.STARTDATE, PERIODS.ENDDATE
                ),CP_TOTALBALANCE as (
                    select 
                    CP_PAYMENTS.CAMPAIGNPRIORITYID as CAMPAIGNPRIORITYID,
                    CP_PAYMENTS.STARTDATE,
                    CP_PAYMENTS.ENDDATE,
                    (coalesce(CP_PAYMENTS.AMOUNTINCURRENCY, 0) - coalesce(CP_WRITEOFFS.AMOUNTINCURRENCY, 0)) as TOTALBALANCE
                    from 
                    CP_PAYMENTS
                    left join CP_WRITEOFFS on CP_PAYMENTS.CAMPAIGNPRIORITYID= CP_WRITEOFFS.CAMPAIGNPRIORITYID and CP_PAYMENTS.STARTDATE=CP_WRITEOFFS.STARTDATE and CP_PAYMENTS.ENDDATE=CP_WRITEOFFS.ENDDATE
                ), CPS_PAYMENTS as (
                select
                    CAMPAIGNSUBPRIORITY.ID as CAMPAIGNSUBPRIORITYID,
                    PERIODS.STARTDATE,
                    PERIODS.ENDDATE,
                    coalesce(SUM(RS.AMOUNTINCURRENCY),0) as AMOUNTINCURRENCY
                from
                    dbo.FINANCIALTRANSACTIONLINEITEM
                inner join dbo.FINANCIALTRANSACTION with (nolock) on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID 
                inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID= FINANCIALTRANSACTIONLINEITEM.ID
                inner join dbo.CAMPAIGNSUBPRIORITY on CAMPAIGNSUBPRIORITY.ID = REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID
                left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as RS on RS.ID = FINANCIALTRANSACTIONLINEITEM.ID
                cross join
                    dbo.UFN_CAMPAIGNPRIORITYREPORT_GETPERIODS(@STARTDATE, @ENDDATE, 3) as PERIODS
                where 
                FINANCIALTRANSACTION.TYPECODE in (1, 6)
                and    FINANCIALTRANSACTION.DELETEDON is null    
                and ((FINANCIALTRANSACTION.DATE >= PERIODS.STARTDATE or PERIODS.STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= PERIODS.ENDDATE or PERIODS.ENDDATE is null))        
                group by CAMPAIGNSUBPRIORITY.ID, PERIODS.STARTDATE, PERIODS.ENDDATE
                ), CPS_WRITEOFFS as (
                select
                    CAMPAIGNSUBPRIORITY.ID as CAMPAIGNSUBPRIORITYID,
                    PERIODS.STARTDATE,
                    PERIODS.ENDDATE,
                    coalesce(SUM(WO.AMOUNTINCURRENCY),0) as AMOUNTINCURRENCY
                from
                    dbo.FINANCIALTRANSACTIONLINEITEM as WRITEOFFSPLIT
                inner join dbo.FINANCIALTRANSACTION as WRITEOFF on WRITEOFF.ID = WRITEOFFSPLIT.FINANCIALTRANSACTIONID
                inner join dbo.FINANCIALTRANSACTION with (nolock) on FINANCIALTRANSACTION.ID = WRITEOFF.PARENTID 
                inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID= FINANCIALTRANSACTIONLINEITEM.ID
                inner join dbo.CAMPAIGNSUBPRIORITY on CAMPAIGNSUBPRIORITY.ID = REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID
                left join dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as WO on WO.ID = WRITEOFFSPLIT.ID
                cross join
                    dbo.UFN_CAMPAIGNPRIORITYREPORT_GETPERIODS(@STARTDATE, @ENDDATE, 3) as PERIODS
                where 
                FINANCIALTRANSACTION.TYPECODE in (1, 6)
                and    FINANCIALTRANSACTION.DELETEDON is null
                and ((FINANCIALTRANSACTION.DATE >= PERIODS.STARTDATE or PERIODS.STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= PERIODS.ENDDATE or PERIODS.ENDDATE is null))        
                group by CAMPAIGNSUBPRIORITY.ID, PERIODS.STARTDATE, PERIODS.ENDDATE
                ), CPS_TOTALBALANCE as (
                select 
                    CPS_PAYMENTS.CAMPAIGNSUBPRIORITYID as CAMPAIGNSUBPRIORITYID,
                    CPS_PAYMENTS.STARTDATE,
                    CPS_PAYMENTS.ENDDATE,
                    (coalesce(CPS_PAYMENTS.AMOUNTINCURRENCY, 0) - coalesce(CPS_WRITEOFFS.AMOUNTINCURRENCY, 0)) as TOTALBALANCE
                    from 
                    CPS_PAYMENTS
                    left join CPS_WRITEOFFS on CPS_PAYMENTS.CAMPAIGNSUBPRIORITYID= CPS_WRITEOFFS.CAMPAIGNSUBPRIORITYID and CPS_PAYMENTS.STARTDATE=CPS_WRITEOFFS.STARTDATE and CPS_PAYMENTS.ENDDATE=CPS_WRITEOFFS.ENDDATE
                ),CP_TOTALPLANNEDGIFTS as (
                select
                    CAMPAIGNPRIORITY.ID as CAMPAIGNPRIORITYID,
                    PERIODS.STARTDATE,
                    PERIODS.ENDDATE,
                    coalesce(sum(RS.AMOUNTINCURRENCY),0) as AMOUNTINCURRENCY
                from
                    dbo.FINANCIALTRANSACTIONLINEITEM
                inner join dbo.FINANCIALTRANSACTION with (nolock) on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID 
                inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                inner join dbo.CAMPAIGNSUBPRIORITY on CAMPAIGNSUBPRIORITY.ID = REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID 
                inner join dbo.CAMPAIGNPRIORITY on CAMPAIGNPRIORITY.ID = CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID
                left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as RS on RS.ID = FINANCIALTRANSACTIONLINEITEM.ID
                cross join
                    dbo.UFN_CAMPAIGNPRIORITYREPORT_GETPERIODS(@STARTDATE, @ENDDATE, 3) as PERIODS
                where
                FINANCIALTRANSACTION.TYPECODE = 4
                and    FINANCIALTRANSACTION.DELETEDON is null
                and ((FINANCIALTRANSACTION.DATE >= PERIODS.STARTDATE or PERIODS.STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= PERIODS.ENDDATE or PERIODS.ENDDATE is null))        
                group by CAMPAIGNPRIORITY.ID,PERIODS.STARTDATE,PERIODS.ENDDATE
                ), CPS_TOTALPLANNEDGIFTS as (
                select
                    REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID as CAMPAIGNSUBPRIORITYID,
                    PERIODS.STARTDATE,
                    PERIODS.ENDDATE,
                    coalesce(sum(RS.AMOUNTINCURRENCY), 0) as AMOUNTINCURRENCY
                from
                dbo.FINANCIALTRANSACTIONLINEITEM
                inner join dbo.FINANCIALTRANSACTION with (nolock) on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
                left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as RS on RS.ID = FINANCIALTRANSACTIONLINEITEM.ID
                cross join
                    dbo.UFN_CAMPAIGNPRIORITYREPORT_GETPERIODS(@STARTDATE, @ENDDATE, 3) as PERIODS
                where
                FINANCIALTRANSACTION.TYPECODE = 4
               and    FINANCIALTRANSACTION.DELETEDON is null
                and ((FINANCIALTRANSACTION.DATE >= PERIODS.STARTDATE or PERIODS.STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= PERIODS.ENDDATE or PERIODS.ENDDATE is null))        
                group by REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID,PERIODS.STARTDATE,PERIODS.ENDDATE
                )

                select    
                    Q.CAMPAIGNPRIORITYID as CAMPAIGNPRIORITY_ID,
                    dbo.UFN_CAMPAIGNPRIORITYTYPECODE_GETDESCRIPTION(Q.CAMPAIGNPRIORITYTYPECODEID) as CAMPAIGNPRIORITY_TYPE,

                    coalesce(CP_TOTALRECEIVED.AMOUNTINCURRENCY,0) as CAMPAIGNPRIORITY_TOTALRECEIVED,
                    coalesce(CP_TOTALBALANCE.TOTALBALANCE,0) as CAMPAIGNPRIORITY_TOTALBALANCE,
                    case @CURRENCYCODE
                        when 0 then Q.CAMPAIGNPRIORITYGOAL
                        else Q.CAMPAIGNPRIORITYORGANIZATIONAMOUNT
                    end as CAMPAIGNPRIORITY_GOAL,
                    Q.YEAR as CAMPAIGNPRIORITY_DATEYEAR,
                    0 as CAMPAIGNPRIORITY_DATEQUARTER,

                    Q.CAMPAIGNSUBPRIORITYID as CAMPAIGNSUBPRIORITY_ID,
                    Q.DESCRIPTION as CAMPAIGNSUBPRIORITY_NAME,
                    coalesce(CPS_TOTALRECEIVED.AMOUNTINCURRENCY,0) as CAMPAIGNSUBPRIORITY_TOTALRECEIVED,
                    coalesce(CPS_TOTALBALANCE.TOTALBALANCE,0) as CAMPAIGNSUBPRIORITY_TOTALBALANCE,
                    case @CURRENCYCODE
                        when 0 then Q.CAMPAIGNSUBPRIORITYGOAL
                        else Q.CAMPAIGNSUBPRIORITYORGANIZATIONAMOUNT
                    end as CAMPAIGNSUBPRIORITY_GOAL,
                    Q.YEAR as CAMPAIGNSUBPRIORITY_DATEYEAR,
                    0 as CAMPAIGNSUBPRIORITY_DATEQUARTER,

                    coalesce(CP_TOTALPLANNEDGIFTS.AMOUNTINCURRENCY,0) as CAMPAIGNPRIORITY_TOTALPLANNEDGIFTS,
                    coalesce(CPS_TOTALPLANNEDGIFTS.AMOUNTINCURRENCY,0) as CAMPAIGNSUBPRIORITY_TOTALPLANNEDGIFTS,

                    Q.ISOCURRENCYCODE as ISOCURRENCYCODE,
                    Q.CURRENCYSYMBOL,
                    Q.CURRENCYSYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE
                from CAMPAIGNPRIORITY_CTE Q 
                left join CP_TOTALRECEIVED on CP_TOTALRECEIVED.CAMPAIGNPRIORITYID = Q.CAMPAIGNPRIORITYID and CP_TOTALRECEIVED.STARTDATE= Q.STARTDATE and CP_TOTALRECEIVED.ENDDATE=Q.ENDDATE
                left join CPS_TOTALRECEIVED on CPS_TOTALRECEIVED.CAMPAIGNSUBPRIORITYID = Q.CAMPAIGNSUBPRIORITYID and CPS_TOTALRECEIVED.STARTDATE=Q.STARTDATE and CPS_TOTALRECEIVED.ENDDATE=Q.ENDDATE
                left join CP_TOTALBALANCE on CP_TOTALBALANCE.CAMPAIGNPRIORITYID= Q.CAMPAIGNPRIORITYID and CP_TOTALBALANCE.STARTDATE=Q.STARTDATE and CP_TOTALBALANCE.ENDDATE=Q.ENDDATE
                left join CPS_TOTALBALANCE on CPS_TOTALBALANCE.CAMPAIGNSUBPRIORITYID= Q.CAMPAIGNSUBPRIORITYID and CPS_TOTALBALANCE.STARTDATE=Q.STARTDATE and CPS_TOTALBALANCE.ENDDATE=Q.ENDDATE
                left join CP_TOTALPLANNEDGIFTS on CP_TOTALPLANNEDGIFTS.CAMPAIGNPRIORITYID= Q.CAMPAIGNPRIORITYID and CP_TOTALPLANNEDGIFTS.STARTDATE=Q.STARTDATE and CP_TOTALPLANNEDGIFTS.ENDDATE=Q.ENDDATE
                left join CPS_TOTALPLANNEDGIFTS on CPS_TOTALPLANNEDGIFTS.CAMPAIGNSUBPRIORITYID= Q.CAMPAIGNSUBPRIORITYID and CPS_TOTALPLANNEDGIFTS.STARTDATE=Q.STARTDATE and CPS_TOTALPLANNEDGIFTS.ENDDATE=Q.ENDDATE
                order by Q.YEAR
        end
        else
        begin                           
            WITH CAMPAIGNPRIORITY_CTE as (
            select
              CAMPAIGNPRIORITY.ID as CAMPAIGNPRIORITYID,
              CAMPAIGNSUBPRIORITY.ID as CAMPAIGNSUBPRIORITYID,
              CAMPAIGNPRIORITY.CAMPAIGNPRIORITYTYPECODEID as CAMPAIGNPRIORITYTYPECODEID,
              0 as YEAR,
              CAMPAIGNPRIORITY.GOAL as CAMPAIGNPRIORITYGOAL,
              CAMPAIGNSUBPRIORITY.GOAL as CAMPAIGNSUBPRIORITYGOAL,
              CAMPAIGNSUBPRIORITYNAMECODE.DESCRIPTION as DESCRIPTION,
              CAMPAIGNPRIORITY.ORGANIZATIONAMOUNT as CAMPAIGNPRIORITYORGANIZATIONAMOUNT,
              CAMPAIGNSUBPRIORITY.ORGANIZATIONAMOUNT as CAMPAIGNSUBPRIORITYORGANIZATIONAMOUNT,
              CAMPAIGN.BASECURRENCYID as BASECURRENCYID,
              CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
              CURRENCYPROPERTIES.CURRENCYSYMBOL,
              CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE
            from
            dbo.CAMPAIGNPRIORITY
            inner join dbo.CAMPAIGN on CAMPAIGNPRIORITY.CAMPAIGNID = CAMPAIGN.ID
            left join dbo.CAMPAIGNSUBPRIORITY on CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID = CAMPAIGNPRIORITY.ID
            left join dbo.CAMPAIGNSUBPRIORITYNAMECODE on CAMPAIGNSUBPRIORITY.CAMPAIGNSUBPRIORITYNAMECODEID = CAMPAIGNSUBPRIORITYNAMECODE.ID
            outer apply
                dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) as CURRENCYPROPERTIES  
            where
            CAMPAIGNPRIORITY.CAMPAIGNID = @CAMPAIGNID
            and ( (CAMPAIGNPRIORITY.CAMPAIGNPRIORITYTYPECODEID = @CAMPAIGNPRIORITYTYPECODEID) or (@CAMPAIGNPRIORITYTYPECODEID is null) )
            ),CP_TOTALRECEIVED as (
            select 
                CAMPAIGNPRIORITY.ID as CAMPAIGNPRIORITYID,
                coalesce(sum(RS.AMOUNTINCURRENCY),0) as AMOUNTINCURRENCY
            from
                dbo.FINANCIALTRANSACTIONLINEITEM
            inner join dbo.FINANCIALTRANSACTION with (nolock) on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
            inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
            inner join dbo.CAMPAIGNSUBPRIORITY on CAMPAIGNSUBPRIORITY.ID = REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID 
            inner join dbo.CAMPAIGNPRIORITY on CAMPAIGNPRIORITY.ID = CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID
            left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as RS on RS.ID = FINANCIALTRANSACTIONLINEITEM.ID
            where
            FINANCIALTRANSACTION.TYPECODE = 0
            and    FINANCIALTRANSACTION.DELETEDON is null
            and REVENUESPLIT_EXT.APPLICATIONCODE in (0, 1, 5, 6, 7, 3, 10)
            and (REVENUESPLIT_EXT.APPLICATIONCODE <> 10 or REVENUESPLIT_EXT.TYPECODE = 0)
            and ((FINANCIALTRANSACTION.DATE >= @STARTDATE or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= @ENDDATE or @ENDDATE is null))        
            group by CAMPAIGNPRIORITY.ID
            ), CPS_TOTALRECEIVED as (
            select
            REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID as CAMPAIGNSUBPRIORITYID,
            coalesce(sum(RS.AMOUNTINCURRENCY), 0) as AMOUNTINCURRENCY
            from
            dbo.FINANCIALTRANSACTIONLINEITEM
            inner join dbo.FINANCIALTRANSACTION with (nolock) on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID 
            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
            inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
            left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as RS on RS.ID = FINANCIALTRANSACTIONLINEITEM.ID
            where
            FINANCIALTRANSACTION.TYPECODE = 0
            and    FINANCIALTRANSACTION.DELETEDON is null
            and REVENUESPLIT_EXT.APPLICATIONCODE in (0, 6, 7, 3)
            and ((FINANCIALTRANSACTION.DATE >= @STARTDATE or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= @ENDDATE or @ENDDATE is null))        
            group by REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID
            ), CP_PAYMENTS as (
            select
                CAMPAIGNPRIORITY.ID as CAMPAIGNPRIORITYID,
                coalesce(sum(RS.AMOUNTINCURRENCY),0) as AMOUNTINCURRENCY
            from
                dbo.FINANCIALTRANSACTIONLINEITEM
            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID 
            inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
            inner join dbo.CAMPAIGNSUBPRIORITY on CAMPAIGNSUBPRIORITY.ID = REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID
            inner join dbo.CAMPAIGNPRIORITY on CAMPAIGNPRIORITY.ID = CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID
            left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as RS on RS.ID = FINANCIALTRANSACTIONLINEITEM.ID
            where
            FINANCIALTRANSACTION.TYPECODE in (1, 6)
            and    FINANCIALTRANSACTION.DELETEDON is null
            and ((FINANCIALTRANSACTION.DATE >= @STARTDATE or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <=@ENDDATE or @ENDDATE is null))        
            group by CAMPAIGNPRIORITY.ID
            ), CP_WRITEOFFS as (
            select
                CAMPAIGNPRIORITY.ID as CAMPAIGNPRIORITYID,
                coalesce(sum(WO.AMOUNTINCURRENCY),0) as AMOUNTINCURRENCY
            from
                dbo.FINANCIALTRANSACTIONLINEITEM as WRITEOFFSPLIT
            inner join dbo.FINANCIALTRANSACTION as WRITEOFF on WRITEOFF.ID = WRITEOFFSPLIT.FINANCIALTRANSACTIONID
            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = WRITEOFF.PARENTID 
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
            inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
            inner join dbo.CAMPAIGNSUBPRIORITY on CAMPAIGNSUBPRIORITY.ID = REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID
            inner join dbo.CAMPAIGNPRIORITY on CAMPAIGNPRIORITY.ID = CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID
            left join dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as WO on WO.ID = WRITEOFFSPLIT.ID
            where
            FINANCIALTRANSACTION.TYPECODE in (1, 6)
            and    FINANCIALTRANSACTION.DELETEDON is null
            and ((FINANCIALTRANSACTION.DATE >= @STARTDATE or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= @ENDDATE or @ENDDATE is null))        
            group by CAMPAIGNPRIORITY.ID
            ),CP_TOTALBALANCE as (
                select 
                CP_PAYMENTS.CAMPAIGNPRIORITYID as CAMPAIGNPRIORITYID,
                (coalesce(CP_PAYMENTS.AMOUNTINCURRENCY, 0) - coalesce(CP_WRITEOFFS.AMOUNTINCURRENCY, 0)) as TOTALBALANCE
                from 
                CP_PAYMENTS
                left join CP_WRITEOFFS on CP_PAYMENTS.CAMPAIGNPRIORITYID= CP_WRITEOFFS.CAMPAIGNPRIORITYID
            ), CPS_PAYMENTS as (
            select
                CAMPAIGNSUBPRIORITY.ID as CAMPAIGNSUBPRIORITYID,
                coalesce(SUM(RS.AMOUNTINCURRENCY),0) as AMOUNTINCURRENCY
            from
                dbo.FINANCIALTRANSACTIONLINEITEM
            inner join dbo.FINANCIALTRANSACTION with (nolock) on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID 
            inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID= FINANCIALTRANSACTIONLINEITEM.ID
            inner join dbo.CAMPAIGNSUBPRIORITY on CAMPAIGNSUBPRIORITY.ID = REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID
            left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as RS on RS.ID = FINANCIALTRANSACTIONLINEITEM.ID
            where 
            FINANCIALTRANSACTION.TYPECODE in (1, 6)
            and    FINANCIALTRANSACTION.DELETEDON is null
            and ((FINANCIALTRANSACTION.DATE >= @STARTDATE or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= @ENDDATE or @ENDDATE is null))        
            group by CAMPAIGNSUBPRIORITY.ID
            ), CPS_WRITEOFFS as (
            select
                CAMPAIGNSUBPRIORITY.ID as CAMPAIGNSUBPRIORITYID,
                coalesce(SUM(WO.AMOUNTINCURRENCY),0) as AMOUNTINCURRENCY
            from
                dbo.FINANCIALTRANSACTIONLINEITEM as WRITEOFFSPLIT
            inner join dbo.FINANCIALTRANSACTION as WRITEOFF on WRITEOFF.ID = WRITEOFFSPLIT.FINANCIALTRANSACTIONID
            inner join dbo.FINANCIALTRANSACTION with (nolock) on FINANCIALTRANSACTION.ID = WRITEOFF.PARENTID 
            inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
            inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID= FINANCIALTRANSACTIONLINEITEM.ID
            inner join dbo.CAMPAIGNSUBPRIORITY on CAMPAIGNSUBPRIORITY.ID = REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID
            left join dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as WO on WO.ID = WRITEOFFSPLIT.ID
            where 
            FINANCIALTRANSACTION.TYPECODE in (1, 6)
            and    FINANCIALTRANSACTION.DELETEDON is null
            and ((FINANCIALTRANSACTION.DATE >= @STARTDATE or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= @ENDDATE or @ENDDATE is null))        
            group by CAMPAIGNSUBPRIORITY.ID
            ), CPS_TOTALBALANCE as (
            select 
                CPS_PAYMENTS.CAMPAIGNSUBPRIORITYID as CAMPAIGNSUBPRIORITYID,
                (coalesce(CPS_PAYMENTS.AMOUNTINCURRENCY, 0) - coalesce(CPS_WRITEOFFS.AMOUNTINCURRENCY, 0)) as TOTALBALANCE
                from 
                CPS_PAYMENTS
                left join CPS_WRITEOFFS on CPS_PAYMENTS.CAMPAIGNSUBPRIORITYID= CPS_WRITEOFFS.CAMPAIGNSUBPRIORITYID
            ),CP_TOTALPLANNEDGIFTS as (
            select
                CAMPAIGNPRIORITY.ID as CAMPAIGNPRIORITYID,
                coalesce(sum(RS.AMOUNTINCURRENCY),0) as AMOUNTINCURRENCY
            from
            dbo.FINANCIALTRANSACTIONLINEITEM
            inner join dbo.FINANCIALTRANSACTION with (nolock) on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID 
            inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
            inner join dbo.CAMPAIGNSUBPRIORITY on CAMPAIGNSUBPRIORITY.ID = REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID 
            inner join dbo.CAMPAIGNPRIORITY on CAMPAIGNPRIORITY.ID = CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID
            left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as RS on RS.ID = FINANCIALTRANSACTIONLINEITEM.ID
            where
            FINANCIALTRANSACTION.TYPECODE = 4
            and    FINANCIALTRANSACTION.DELETEDON is null
            and ((FINANCIALTRANSACTION.DATE >= @STARTDATE or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= @ENDDATE or @ENDDATE is null))        
            group by CAMPAIGNPRIORITY.ID
            ), CPS_TOTALPLANNEDGIFTS as (
            select
                REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID as CAMPAIGNSUBPRIORITYID,
                coalesce(sum(RS.AMOUNTINCURRENCY), 0) as AMOUNTINCURRENCY
            from
            dbo.FINANCIALTRANSACTIONLINEITEM
            inner join dbo.FINANCIALTRANSACTION with (nolock) on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID 
            inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
            left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID,@ORGANIZATIONCURRENCYID,@DECIMALDIGITS,@ROUNDINGTYPECODE) as RS on RS.ID = FINANCIALTRANSACTIONLINEITEM.ID
            where
            FINANCIALTRANSACTION.TYPECODE = 4
            and    FINANCIALTRANSACTION.DELETEDON is null
            and ((FINANCIALTRANSACTION.DATE >= @STARTDATE or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= @ENDDATE or @ENDDATE is null))        
            group by REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID
            )

            select    
                Q.CAMPAIGNPRIORITYID as CAMPAIGNPRIORITY_ID,
                dbo.UFN_CAMPAIGNPRIORITYTYPECODE_GETDESCRIPTION(Q.CAMPAIGNPRIORITYTYPECODEID) as CAMPAIGNPRIORITY_TYPE,

                coalesce(CP_TOTALRECEIVED.AMOUNTINCURRENCY,0) as CAMPAIGNPRIORITY_TOTALRECEIVED,
                coalesce(CP_TOTALBALANCE.TOTALBALANCE,0) as CAMPAIGNPRIORITY_TOTALBALANCE,
                case @CURRENCYCODE
             when 0 then Q.CAMPAIGNPRIORITYGOAL
                    else Q.CAMPAIGNPRIORITYORGANIZATIONAMOUNT
                end as CAMPAIGNPRIORITY_GOAL,
                Q.YEAR as CAMPAIGNPRIORITY_DATEYEAR,
                0 as CAMPAIGNPRIORITY_DATEQUARTER,

                Q.CAMPAIGNSUBPRIORITYID as CAMPAIGNSUBPRIORITY_ID,
                Q.DESCRIPTION as CAMPAIGNSUBPRIORITY_NAME,
                coalesce(CPS_TOTALRECEIVED.AMOUNTINCURRENCY,0) as CAMPAIGNSUBPRIORITY_TOTALRECEIVED,
                coalesce(CPS_TOTALBALANCE.TOTALBALANCE,0) as CAMPAIGNSUBPRIORITY_TOTALBALANCE,
                case @CURRENCYCODE
                    when 0 then Q.CAMPAIGNSUBPRIORITYGOAL
                    else Q.CAMPAIGNSUBPRIORITYORGANIZATIONAMOUNT
                end as CAMPAIGNSUBPRIORITY_GOAL,
                Q.YEAR as CAMPAIGNSUBPRIORITY_DATEYEAR,
                0 as CAMPAIGNSUBPRIORITY_DATEQUARTER,

                coalesce(CP_TOTALPLANNEDGIFTS.AMOUNTINCURRENCY,0) as CAMPAIGNPRIORITY_TOTALPLANNEDGIFTS,
                coalesce(CPS_TOTALPLANNEDGIFTS.AMOUNTINCURRENCY,0) as CAMPAIGNSUBPRIORITY_TOTALPLANNEDGIFTS,

                Q.ISOCURRENCYCODE as ISOCURRENCYCODE,
                Q.CURRENCYSYMBOL,
                Q.CURRENCYSYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE
            from CAMPAIGNPRIORITY_CTE Q 
            left join CP_TOTALRECEIVED on CP_TOTALRECEIVED.CAMPAIGNPRIORITYID = Q.CAMPAIGNPRIORITYID
            left join CPS_TOTALRECEIVED on CPS_TOTALRECEIVED.CAMPAIGNSUBPRIORITYID = Q.CAMPAIGNSUBPRIORITYID
            left join CP_TOTALBALANCE on CP_TOTALBALANCE.CAMPAIGNPRIORITYID= Q.CAMPAIGNPRIORITYID
            left join CPS_TOTALBALANCE on CPS_TOTALBALANCE.CAMPAIGNSUBPRIORITYID= Q.CAMPAIGNSUBPRIORITYID
            left join CP_TOTALPLANNEDGIFTS on CP_TOTALPLANNEDGIFTS.CAMPAIGNPRIORITYID= Q.CAMPAIGNPRIORITYID
            left join CPS_TOTALPLANNEDGIFTS on CPS_TOTALPLANNEDGIFTS.CAMPAIGNSUBPRIORITYID= Q.CAMPAIGNSUBPRIORITYID
            order by Q.YEAR
            end
        end