USP_REPORT_CAMPAIGNPRIORITYPROGRESSBYQUARTERINCURRENCY

            CREATE procedure dbo.USP_REPORT_CAMPAIGNPRIORITYPROGRESSBYQUARTERINCURRENCY
            (
                @CAMPAIGNID uniqueidentifier,
                @CAMPAIGNPRIORITYTYPECODEID uniqueidentifier = null,
                @STARTDATE datetime,
                @ENDDATE datetime,
                @CURRENCYCODE tinyint,
                @CURRENCYID uniqueidentifier
            )
            as
                set nocount on;

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

        set @SELECTEDCURRENCYID = @CURRENCYID;

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

            WITH CAMPAIGNPRIORITY_CTE as (
            select
              CAMPAIGNPRIORITY.ID as CAMPAIGNPRIORITYID,
              CAMPAIGNSUBPRIORITY.ID as CAMPAIGNSUBPRIORITYID,
              CAMPAIGNPRIORITY.CAMPAIGNPRIORITYTYPECODEID as CAMPAIGNPRIORITYTYPECODEID,
              PRIORITYPROGRESS.YEARNAME as CAMPAIGNPRIORITYYEAR,
              PRIORITYPROGRESS.QUARTER as CAMPAIGNPRIORITYQUARTER,
              SUBPRIORITYPROGRESS.YEARNAME as CAMPAIGNSUBPRIORITYYEAR,
              SUBPRIORITYPROGRESS.QUARTER as CAMPAIGNSUBPRIORITYQUARTER,
              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
            inner join dbo.UFN_CAMPAIGNPRIORITY_GETQUARTERLYPROGRESS(@CAMPAIGNID,@STARTDATE,@ENDDATE) as PRIORITYPROGRESS on PRIORITYPROGRESS.CAMAPIGNPRIORITYID = CAMPAIGNPRIORITY.ID
            left join dbo.CAMPAIGNSUBPRIORITY on CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID = CAMPAIGNPRIORITY.ID
            left join dbo.UFN_CAMPAIGNSUBPRIORITY_GETQUARTERLYPROGRESS(@CAMPAIGNID,@STARTDATE,@ENDDATE) as SUBPRIORITYPROGRESS on SUBPRIORITYPROGRESS.CAMAPIGNSUBPRIORITYID = CAMPAIGNSUBPRIORITY.ID and (PRIORITYPROGRESS.YEARNAME = SUBPRIORITYPROGRESS.YEARNAME and PRIORITYPROGRESS.QUARTER = SUBPRIORITYPROGRESS.QUARTER)
            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,
                PRIORITYPROGRESS.YEARNAME,
                PRIORITYPROGRESS.QUARTER,
                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
            inner join dbo.UFN_CAMPAIGNPRIORITY_GETQUARTERLYPROGRESS(@CAMPAIGNID,@STARTDATE,@ENDDATE) as PRIORITYPROGRESS on PRIORITYPROGRESS.CAMAPIGNPRIORITYID = CAMPAIGNPRIORITY.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, 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) and PRIORITYPROGRESS.SEQUENCE =1 and PRIORITYPROGRESS.TOTALQUARTERS=1)
                    or
                    ((FINANCIALTRANSACTION.DATE >= @STARTDATE or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= (DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,PRIORITYPROGRESS.STARTDATE)+ 1, 0))) or @ENDDATE is null) and PRIORITYPROGRESS.SEQUENCE =1 and PRIORITYPROGRESS.TOTALQUARTERS<>1)
                    or
                    ((FINANCIALTRANSACTION.DATE >= (DATEADD(qq, DATEDIFF(qq,0,PRIORITYPROGRESS.STARTDATE), 0)) or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= @ENDDATE or @ENDDATE is null) and PRIORITYPROGRESS.SEQUENCE = PRIORITYPROGRESS.TOTALQUARTERS)
                    or
                    ((FINANCIALTRANSACTION.DATE >= (DATEADD(qq, DATEDIFF(qq,0,PRIORITYPROGRESS.STARTDATE), 0)) or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= (DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,PRIORITYPROGRESS.STARTDATE)+ 1, 0))) or @ENDDATE is null) and PRIORITYPROGRESS.SEQUENCE <> PRIORITYPROGRESS.TOTALQUARTERS)
                )        
            group by CAMPAIGNPRIORITY.ID,PRIORITYPROGRESS.YEARNAME,PRIORITYPROGRESS.QUARTER
            ), CPS_TOTALRECEIVED as (
            select
            REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID as CAMPAIGNSUBPRIORITYID,
            SUBPRIORITYPROGRESS.YEARNAME,
            SUBPRIORITYPROGRESS.QUARTER,
            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.UFN_CAMPAIGNPRIORITY_GETQUARTERLYPROGRESS(@CAMPAIGNID,@STARTDATE,@ENDDATE) as PRIORITYPROGRESS on PRIORITYPROGRESS.CAMAPIGNPRIORITYID = CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID
            left join dbo.UFN_CAMPAIGNSUBPRIORITY_GETQUARTERLYPROGRESS(@CAMPAIGNID,@STARTDATE,@ENDDATE) as SUBPRIORITYPROGRESS on SUBPRIORITYPROGRESS.CAMAPIGNSUBPRIORITYID = REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID and (PRIORITYPROGRESS.YEARNAME = SUBPRIORITYPROGRESS.YEARNAME and PRIORITYPROGRESS.QUARTER = SUBPRIORITYPROGRESS.QUARTER)
            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) and SUBPRIORITYPROGRESS.SEQUENCE =1 and SUBPRIORITYPROGRESS.TOTALQUARTERS=1)
                    or
                    ((FINANCIALTRANSACTION.DATE >= @STARTDATE or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= (DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,SUBPRIORITYPROGRESS.STARTDATE)+ 1, 0))) or @ENDDATE is null) and SUBPRIORITYPROGRESS.SEQUENCE =1 and SUBPRIORITYPROGRESS.TOTALQUARTERS<>1)
                    or
                  ((FINANCIALTRANSACTION.DATE >= (DATEADD(qq, DATEDIFF(qq,0,SUBPRIORITYPROGRESS.STARTDATE), 0)) or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= @ENDDATE or @ENDDATE is null) and SUBPRIORITYPROGRESS.SEQUENCE = SUBPRIORITYPROGRESS.TOTALQUARTERS)
                    or
                    ((FINANCIALTRANSACTION.DATE >= (DATEADD(qq, DATEDIFF(qq,0,SUBPRIORITYPROGRESS.STARTDATE), 0)) or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= (DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,SUBPRIORITYPROGRESS.STARTDATE)+ 1, 0))) or @ENDDATE is null) and SUBPRIORITYPROGRESS.SEQUENCE <> SUBPRIORITYPROGRESS.TOTALQUARTERS)
                )        
            group by REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID,SUBPRIORITYPROGRESS.YEARNAME,SUBPRIORITYPROGRESS.QUARTER
            ), CP_PAYMENTS as (
            select
                CAMPAIGNPRIORITY.ID as CAMPAIGNPRIORITYID,
                PRIORITYPROGRESS.YEARNAME,
                PRIORITYPROGRESS.QUARTER,
                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
            inner join dbo.UFN_CAMPAIGNPRIORITY_GETQUARTERLYPROGRESS(@CAMPAIGNID,@STARTDATE,@ENDDATE) as PRIORITYPROGRESS on PRIORITYPROGRESS.CAMAPIGNPRIORITYID = CAMPAIGNPRIORITY.ID
            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) and PRIORITYPROGRESS.SEQUENCE =1 and PRIORITYPROGRESS.TOTALQUARTERS=1)
                    or
                    ((FINANCIALTRANSACTION.DATE >= @STARTDATE or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= (DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,PRIORITYPROGRESS.STARTDATE)+ 1, 0))) or @ENDDATE is null) and PRIORITYPROGRESS.SEQUENCE =1 and PRIORITYPROGRESS.TOTALQUARTERS<>1)
                    or
                    ((FINANCIALTRANSACTION.DATE >= (DATEADD(qq, DATEDIFF(qq,0,PRIORITYPROGRESS.STARTDATE), 0)) or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= @ENDDATE or @ENDDATE is null) and PRIORITYPROGRESS.SEQUENCE = PRIORITYPROGRESS.TOTALQUARTERS)
                    or
                    ((FINANCIALTRANSACTION.DATE >= (DATEADD(qq, DATEDIFF(qq,0,PRIORITYPROGRESS.STARTDATE), 0)) or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= (DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,PRIORITYPROGRESS.STARTDATE)+ 1, 0))) or @ENDDATE is null) and PRIORITYPROGRESS.SEQUENCE <> PRIORITYPROGRESS.TOTALQUARTERS)
            )        
            group by CAMPAIGNPRIORITY.ID,PRIORITYPROGRESS.YEARNAME,PRIORITYPROGRESS.QUARTER
            ), CP_WRITEOFFS as (
            select
                CAMPAIGNPRIORITY.ID as CAMPAIGNPRIORITYID,
                PRIORITYPROGRESS.YEARNAME,
                PRIORITYPROGRESS.QUARTER,
                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
            inner join dbo.UFN_CAMPAIGNPRIORITY_GETQUARTERLYPROGRESS(@CAMPAIGNID,@STARTDATE,@ENDDATE) as PRIORITYPROGRESS on PRIORITYPROGRESS.CAMAPIGNPRIORITYID = CAMPAIGNPRIORITY.ID
            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) and PRIORITYPROGRESS.SEQUENCE =1 and PRIORITYPROGRESS.TOTALQUARTERS=1)
                    or
                    ((FINANCIALTRANSACTION.DATE >= @STARTDATE or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= (DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,PRIORITYPROGRESS.STARTDATE)+ 1, 0))) or @ENDDATE is null) and PRIORITYPROGRESS.SEQUENCE =1 and PRIORITYPROGRESS.TOTALQUARTERS<>1)
                    or
                    ((FINANCIALTRANSACTION.DATE >= (DATEADD(qq, DATEDIFF(qq,0,PRIORITYPROGRESS.STARTDATE), 0)) or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= @ENDDATE or @ENDDATE is null) and PRIORITYPROGRESS.SEQUENCE = PRIORITYPROGRESS.TOTALQUARTERS)
                    or
                    ((FINANCIALTRANSACTION.DATE >= (DATEADD(qq, DATEDIFF(qq,0,PRIORITYPROGRESS.STARTDATE), 0)) or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= (DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,PRIORITYPROGRESS.STARTDATE)+ 1, 0))) or @ENDDATE is null) and PRIORITYPROGRESS.SEQUENCE <> PRIORITYPROGRESS.TOTALQUARTERS)
                )        
            group by CAMPAIGNPRIORITY.ID,PRIORITYPROGRESS.YEARNAME,PRIORITYPROGRESS.QUARTER
            ),CP_TOTALBALANCE as (
                select 
                CP_PAYMENTS.CAMPAIGNPRIORITYID as CAMPAIGNPRIORITYID,
                CP_PAYMENTS.YEARNAME,
                CP_PAYMENTS.QUARTER,
                (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,
                SUBPRIORITYPROGRESS.YEARNAME,
                SUBPRIORITYPROGRESS.QUARTER,
                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.UFN_CAMPAIGNPRIORITY_GETQUARTERLYPROGRESS(@CAMPAIGNID,@STARTDATE,@ENDDATE) as PRIORITYPROGRESS on PRIORITYPROGRESS.CAMAPIGNPRIORITYID = CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID
            left join dbo.UFN_CAMPAIGNSUBPRIORITY_GETQUARTERLYPROGRESS(@CAMPAIGNID,@STARTDATE,@ENDDATE) as SUBPRIORITYPROGRESS on SUBPRIORITYPROGRESS.CAMAPIGNSUBPRIORITYID = REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID and (PRIORITYPROGRESS.YEARNAME = SUBPRIORITYPROGRESS.YEARNAME and PRIORITYPROGRESS.QUARTER = SUBPRIORITYPROGRESS.QUARTER)
            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) and SUBPRIORITYPROGRESS.SEQUENCE =1 and SUBPRIORITYPROGRESS.TOTALQUARTERS=1)
                    or
                    ((FINANCIALTRANSACTION.DATE >= @STARTDATE or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= (DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,SUBPRIORITYPROGRESS.STARTDATE)+ 1, 0))) or @ENDDATE is null) and SUBPRIORITYPROGRESS.SEQUENCE =1 and SUBPRIORITYPROGRESS.TOTALQUARTERS<>1)
                    or
                    ((FINANCIALTRANSACTION.DATE >= (DATEADD(qq, DATEDIFF(qq,0,SUBPRIORITYPROGRESS.STARTDATE), 0)) or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= @ENDDATE or @ENDDATE is null) and SUBPRIORITYPROGRESS.SEQUENCE = SUBPRIORITYPROGRESS.TOTALQUARTERS)
                    or
                    ((FINANCIALTRANSACTION.DATE >= (DATEADD(qq, DATEDIFF(qq,0,SUBPRIORITYPROGRESS.STARTDATE), 0)) or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= (DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,SUBPRIORITYPROGRESS.STARTDATE)+ 1, 0))) or @ENDDATE is null) and SUBPRIORITYPROGRESS.SEQUENCE <> SUBPRIORITYPROGRESS.TOTALQUARTERS)        
                )        
            group by CAMPAIGNSUBPRIORITY.ID,SUBPRIORITYPROGRESS.YEARNAME,SUBPRIORITYPROGRESS.QUARTER
            ), CPS_WRITEOFFS as (
            select
                CAMPAIGNSUBPRIORITY.ID as CAMPAIGNSUBPRIORITYID,
                SUBPRIORITYPROGRESS.YEARNAME,
                SUBPRIORITYPROGRESS.QUARTER,
                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
            inner join dbo.UFN_CAMPAIGNPRIORITY_GETQUARTERLYPROGRESS(@CAMPAIGNID,@STARTDATE,@ENDDATE) as PRIORITYPROGRESS on PRIORITYPROGRESS.CAMAPIGNPRIORITYID = CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID
            left join dbo.UFN_CAMPAIGNSUBPRIORITY_GETQUARTERLYPROGRESS(@CAMPAIGNID,@STARTDATE,@ENDDATE) as SUBPRIORITYPROGRESS on SUBPRIORITYPROGRESS.CAMAPIGNSUBPRIORITYID = REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID and (PRIORITYPROGRESS.YEARNAME = SUBPRIORITYPROGRESS.YEARNAME and PRIORITYPROGRESS.QUARTER = SUBPRIORITYPROGRESS.QUARTER)
            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) and SUBPRIORITYPROGRESS.SEQUENCE =1 and SUBPRIORITYPROGRESS.TOTALQUARTERS=1)
                or
                ((FINANCIALTRANSACTION.DATE >= @STARTDATE or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= (DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,SUBPRIORITYPROGRESS.STARTDATE)+ 1, 0))) or @ENDDATE is null) and SUBPRIORITYPROGRESS.SEQUENCE =1 and SUBPRIORITYPROGRESS.TOTALQUARTERS<>1)
                or
                ((FINANCIALTRANSACTION.DATE >= (DATEADD(qq, DATEDIFF(qq,0,SUBPRIORITYPROGRESS.STARTDATE), 0)) or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= @ENDDATE or @ENDDATE is null) and SUBPRIORITYPROGRESS.SEQUENCE = SUBPRIORITYPROGRESS.TOTALQUARTERS)
                or
                ((FINANCIALTRANSACTION.DATE >= (DATEADD(qq, DATEDIFF(qq,0,SUBPRIORITYPROGRESS.STARTDATE), 0)) or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= (DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,SUBPRIORITYPROGRESS.STARTDATE)+ 1, 0))) or @ENDDATE is null) and SUBPRIORITYPROGRESS.SEQUENCE <> SUBPRIORITYPROGRESS.TOTALQUARTERS)        
            )        
            group by CAMPAIGNSUBPRIORITY.ID,SUBPRIORITYPROGRESS.YEARNAME,SUBPRIORITYPROGRESS.QUARTER
            ), CPS_TOTALBALANCE as (
            select 
                CPS_PAYMENTS.CAMPAIGNSUBPRIORITYID as CAMPAIGNSUBPRIORITYID,
                CPS_PAYMENTS.YEARNAME,
                CPS_PAYMENTS.QUARTER,
                (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,
                PRIORITYPROGRESS.YEARNAME,
                PRIORITYPROGRESS.QUARTER,
                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
            inner join dbo.UFN_CAMPAIGNPRIORITY_GETQUARTERLYPROGRESS(@CAMPAIGNID,@STARTDATE,@ENDDATE) as PRIORITYPROGRESS on PRIORITYPROGRESS.CAMAPIGNPRIORITYID = CAMPAIGNPRIORITY.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) and PRIORITYPROGRESS.SEQUENCE =1 and PRIORITYPROGRESS.TOTALQUARTERS=1)
                    or
                    ((FINANCIALTRANSACTION.DATE >= @STARTDATE or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= (DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,PRIORITYPROGRESS.STARTDATE)+ 1, 0))) or @ENDDATE is null) and PRIORITYPROGRESS.SEQUENCE =1 and PRIORITYPROGRESS.TOTALQUARTERS<>1)
                    or
                    ((FINANCIALTRANSACTION.DATE >= (DATEADD(qq, DATEDIFF(qq,0,PRIORITYPROGRESS.STARTDATE), 0)) or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= @ENDDATE or @ENDDATE is null) and PRIORITYPROGRESS.SEQUENCE = PRIORITYPROGRESS.TOTALQUARTERS)
                    or
                    ((FINANCIALTRANSACTION.DATE >= (DATEADD(qq, DATEDIFF(qq,0,PRIORITYPROGRESS.STARTDATE), 0)) or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= (DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,PRIORITYPROGRESS.STARTDATE)+ 1, 0))) or @ENDDATE is null) and PRIORITYPROGRESS.SEQUENCE <> PRIORITYPROGRESS.TOTALQUARTERS)
                )        
            group by CAMPAIGNPRIORITY.ID,PRIORITYPROGRESS.YEARNAME,PRIORITYPROGRESS.QUARTER
            ), CPS_TOTALPLANNEDGIFTS as (
            select
                REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID as CAMPAIGNSUBPRIORITYID,
                SUBPRIORITYPROGRESS.YEARNAME,
                SUBPRIORITYPROGRESS.QUARTER,
                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.UFN_CAMPAIGNPRIORITY_GETQUARTERLYPROGRESS(@CAMPAIGNID,@STARTDATE,@ENDDATE) as PRIORITYPROGRESS on PRIORITYPROGRESS.CAMAPIGNPRIORITYID = CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID
            left join dbo.UFN_CAMPAIGNSUBPRIORITY_GETQUARTERLYPROGRESS(@CAMPAIGNID,@STARTDATE,@ENDDATE) as SUBPRIORITYPROGRESS on SUBPRIORITYPROGRESS.CAMAPIGNSUBPRIORITYID = REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID and (PRIORITYPROGRESS.YEARNAME = SUBPRIORITYPROGRESS.YEARNAME and PRIORITYPROGRESS.QUARTER = SUBPRIORITYPROGRESS.QUARTER)
            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) and SUBPRIORITYPROGRESS.SEQUENCE =1 and SUBPRIORITYPROGRESS.TOTALQUARTERS=1)
                or
                ((FINANCIALTRANSACTION.DATE >= @STARTDATE or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= (DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,SUBPRIORITYPROGRESS.STARTDATE)+ 1, 0))) or @ENDDATE is null) and SUBPRIORITYPROGRESS.SEQUENCE =1 and SUBPRIORITYPROGRESS.TOTALQUARTERS<>1)
                or
                ((FINANCIALTRANSACTION.DATE >= (DATEADD(qq, DATEDIFF(qq,0,SUBPRIORITYPROGRESS.STARTDATE), 0)) or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= @ENDDATE or @ENDDATE is null) and SUBPRIORITYPROGRESS.SEQUENCE = SUBPRIORITYPROGRESS.TOTALQUARTERS)
                or
                ((FINANCIALTRANSACTION.DATE >= (DATEADD(qq, DATEDIFF(qq,0,SUBPRIORITYPROGRESS.STARTDATE), 0)) or @STARTDATE is null) and (FINANCIALTRANSACTION.DATE <= (DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,SUBPRIORITYPROGRESS.STARTDATE)+ 1, 0))) or @ENDDATE is null) and SUBPRIORITYPROGRESS.SEQUENCE <> SUBPRIORITYPROGRESS.TOTALQUARTERS)            
            )        
            group by REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID,SUBPRIORITYPROGRESS.YEARNAME,SUBPRIORITYPROGRESS.QUARTER
            )

            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.CAMPAIGNPRIORITYYEAR as CAMPAIGNPRIORITY_DATEYEAR,
                Q.CAMPAIGNPRIORITYQUARTER 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.CAMPAIGNPRIORITYYEAR as CAMPAIGNSUBPRIORITY_DATEYEAR,
                Q.CAMPAIGNSUBPRIORITYQUARTER 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.YEARNAME = Q.CAMPAIGNPRIORITYYEAR and CP_TOTALRECEIVED.QUARTER=Q.CAMPAIGNPRIORITYQUARTER
            left join CPS_TOTALRECEIVED on CPS_TOTALRECEIVED.CAMPAIGNSUBPRIORITYID = Q.CAMPAIGNSUBPRIORITYID and CPS_TOTALRECEIVED.YEARNAME =Q.CAMPAIGNSUBPRIORITYYEAR and CPS_TOTALRECEIVED.QUARTER =Q.CAMPAIGNSUBPRIORITYQUARTER
            left join CP_TOTALBALANCE on CP_TOTALBALANCE.CAMPAIGNPRIORITYID= Q.CAMPAIGNPRIORITYID and CP_TOTALBALANCE.YEARNAME=Q.CAMPAIGNPRIORITYYEAR and CP_TOTALBALANCE.QUARTER=Q.CAMPAIGNPRIORITYQUARTER
            left join CPS_TOTALBALANCE on CPS_TOTALBALANCE.CAMPAIGNSUBPRIORITYID= Q.CAMPAIGNSUBPRIORITYID and CPS_TOTALBALANCE.YEARNAME = Q.CAMPAIGNSUBPRIORITYYEAR and CPS_TOTALBALANCE.QUARTER = Q.CAMPAIGNSUBPRIORITYQUARTER
            left join CP_TOTALPLANNEDGIFTS on CP_TOTALPLANNEDGIFTS.CAMPAIGNPRIORITYID= Q.CAMPAIGNPRIORITYID and CP_TOTALPLANNEDGIFTS.YEARNAME = Q.CAMPAIGNPRIORITYYEAR and CP_TOTALPLANNEDGIFTS.QUARTER = Q.CAMPAIGNPRIORITYQUARTER
            left join CPS_TOTALPLANNEDGIFTS on CPS_TOTALPLANNEDGIFTS.CAMPAIGNSUBPRIORITYID= Q.CAMPAIGNSUBPRIORITYID and CPS_TOTALPLANNEDGIFTS.YEARNAME = Q.CAMPAIGNSUBPRIORITYYEAR and CPS_TOTALPLANNEDGIFTS.QUARTER=Q.CAMPAIGNSUBPRIORITYQUARTER
            order by CAMPAIGNPRIORITY_DATEYEAR, CAMPAIGNPRIORITY_DATEQUARTER;