USP_REPORT_ACCOUNTDISTRIBUTION_FTM

create procedure dbo.USP_REPORT_ACCOUNTDISTRIBUTION_FTM
(
    @STARTDATE datetime = null, 
    @ENDDATE datetime = null,
    @REVENUETRANSACTIONQUERY uniqueidentifier = null,
    @CONSTITUENTID uniqueidentifier = null,
    @CURRENCYCODE tinyint = 0, --0 = Record base, (null, 1) = Organization

    @POSTSTATUSCODE tinyint = 0,
    @SHOWDETAIL bit = 1,          
    @GLACCOUNTID uniqueidentifier = null,
    @GLACCOUNTQUERY uniqueidentifier = null,
    @SHOWACCOUNTSYSTEM bit = 1 output,
    @GROUPBYACCOUNTALIAS bit = 0,
    @PDACCOUNTSYSTEMID uniqueidentifier = null,
    @ALTREPORTUSERID nvarchar(128) = null,
    @REPORTUSERID nvarchar(128) = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
    @TRANSACTIONTYPES nvarchar(max) = null
)
with execute as owner
as

    set nocount on;
    set transaction isolation level read uncommitted;

    declare @SELECTEDCURRENCYID uniqueidentifier;

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

    if @PDACCOUNTSYSTEMID is null
        set @PDACCOUNTSYSTEMID = dbo.UFN_PDACCOUNTSYSTEM_GETDEFAULTSYSTEMIDSFORUSER (@CURRENTAPPUSERID);

    if @STARTDATE is not null
        set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);

    if @ENDDATE is not null
        set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

    set @POSTSTATUSCODE = case coalesce(@POSTSTATUSCODE, 2) when 0 then 2 when 1 then 1 else 0 end;

    if len(@TRANSACTIONTYPES) = 0
        set @TRANSACTIONTYPES = null;
    else
    begin
        set @TRANSACTIONTYPES = REPLACE(@TRANSACTIONTYPES, '&lt;', '<');
        set @TRANSACTIONTYPES = REPLACE(@TRANSACTIONTYPES, '&gt;', '>');
    end

    declare @DBOBJECTNAME nvarchar(128);
    if @GLACCOUNTQUERY is not null begin
        if not exists(select ID from dbo.IDSETREGISTER where ID = @GLACCOUNTQUERY) raiserror('ID set does not exist in the database.', 15, 1);

        declare @DBOBJECTTYPE smallint;
        select 
            @DBOBJECTNAME = DBOBJECTNAME,
            @DBOBJECTTYPE = OBJECTTYPE 
        from dbo.IDSETREGISTER where ID = @GLACCOUNTQUERY;

        if @DBOBJECTTYPE = 1 set @DBOBJECTNAME = @DBOBJECTNAME + '()';
        else if @DBOBJECTTYPE = 2 set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @GLACCOUNTQUERY) + ''')';
    end    

    declare @SQLTOEXEC as nvarchar(max) = null
    declare @GROUPINGCOLUMNS as nvarchar(max) = null
    if @SHOWDETAIL = 1
    begin
        set @SQLTOEXEC = 'select top 75000
            T.REVENUEID
            ,T.REVENUETYPE
            ,T.TRANSACTIONNUMBER
            ,T.[DATE]
            ,T.DEPOSITCORRECTIONID
            ,T.ADJUSTMENTID
            ,T.CREDITITEMID
            ,T.CREDITPAYMENTID
            ,T.AMOUNT
            ,T.POSTDATE
            ,T.POSTSTATUS
            ,T.REFERENCE
            ,T.TRANSACTIONTYPECODE
            ,T.ID
            ,T.GLACCOUNTID
            ,T.ACCOUNTNUMBER
            ,T.ALIAS
            ,T.ACCOUNT
            ,T.DEBITAMOUNT
            ,T.CREDITAMOUNT
            ,T.REVENUESPLITID
            ,T.REVENUEBENEFITID
            ,T.PROPERTYDETAILID
            ,T.BANKACCOUNTDEPOSITCORRECTIONID
            ,T.BANKACCOUNTTRANSACTIONID
            ,T.WRITEOFFID
            ,T.STOCKSALEID
            ,T.PLANNEDGIFTPAYOUTID
            ,T.ISOCURRENCYCODE
            ,T.CURRENCYSYMBOL
            ,T.SYMBOLDISPLAYSETTINGCODE
            ,T.DECIMALDIGITS
            ,T.ACCOUNTDESCRIPTION'
    end
    else
    begin
        set @SQLTOEXEC = 'select 
            null as REVENUEID
            ,null as REVENUETYPE
            ,null as TRANSACTIONNUMBER
            ,null as [DATE]
            ,null as DEPOSITCORRECTIONID
            ,null as ADJUSTMENTID
            ,null as CREDITITEMID
            ,null as CREDITPAYMENTID
            ,null as AMOUNT
            ,null as POSTDATE
            ,null as POSTSTATUS
            ,null as REFERENCE
            ,T.TRANSACTIONTYPECODE
            ,null as ID
            ,T.GLACCOUNTID
            ,T.ACCOUNTNUMBER
            ,T.ALIAS
            ,T.ACCOUNT
            ,SUM(T.DEBITAMOUNT) as DEBITAMOUNT
            ,SUM(T.CREDITAMOUNT) as CREDITAMOUNT
            ,null as REVENUESPLITID
            ,null as REVENUEBENEFITID
            ,null as PROPERTYDETAILID
            ,null as BANKACCOUNTDEPOSITCORRECTIONID
            ,null as BANKACCOUNTTRANSACTIONID
            ,null as WRITEOFFID
            ,null as STOCKSALEID
            ,null as PLANNEDGIFTPAYOUTID
            ,T.ISOCURRENCYCODE
            ,T.CURRENCYSYMBOL
            ,T.SYMBOLDISPLAYSETTINGCODE
            ,T.DECIMALDIGITS
            ,T.ACCOUNTDESCRIPTION'
        set @GROUPINGCOLUMNS = 'group by T.GLACCOUNTID, T.ACCOUNTNUMBER, T.ALIAS, T.ACCOUNT, T.ACCOUNTDESCRIPTION, T.TRANSACTIONTYPECODE, T.ISOCURRENCYCODE, T.CURRENCYSYMBOL, T.SYMBOLDISPLAYSETTINGCODE, T.DECIMALDIGITS'
    end

    set @SQLTOEXEC = @SQLTOEXEC + '
            from (
            select 
                case when REVENUE_EXT.ID is null or FT.TYPECODE = 23 then null else ''http://www.blackbaud.com/REVENUEID?REVENUEID='' + CONVERT(nvarchar(36),REVENUE_EXT.ID) end as REVENUEID
                ,case FT.TYPECODE
                    when 24 then case when BADCE.CORRECTIONTYPECODE = 0 then N''Sales - Short'' else N''Treasury - Short'' end
                    when 25 then case when BADCE.CORRECTIONTYPECODE = 0 then N''Sales - Over''  else N''Treasury - Over'' end
                    else FT.[TYPE] end as [REVENUETYPE]
                ,case FT.TYPECODE
                    when 11 then convert(nvarchar(100),BATE.TRANSACTIONNUMBER)
                    when 12 then convert(nvarchar(100),BATE.TRANSACTIONNUMBER)
                    when 13 then convert(nvarchar(100),BATE.TRANSACTIONNUMBER)
                    when 14 then convert(nvarchar(100),BATE.TRANSACTIONNUMBER)
                    when 20 then ISNULL(FTPARENT.CALCULATEDUSERDEFINEDID, FT.CALCULATEDUSERDEFINEDID)
                    when 21 then ISNULL(FTPARENT.CALCULATEDUSERDEFINEDID, FT.CALCULATEDUSERDEFINEDID)
                    when 22 then ISNULL(FTPARENT.CALCULATEDUSERDEFINEDID, FT.CALCULATEDUSERDEFINEDID)
                    when 23 then isnull(nullif(isnull(convert(nvarchar(100), SALESORDER.SEQUENCEID), FT.CALCULATEDUSERDEFINEDID), ''''), FTPARENT.CALCULATEDUSERDEFINEDID)
                    when 24 then convert(nvarchar(100),BATE.TRANSACTIONNUMBER)
                    when 25 then convert(nvarchar(100),BATE.TRANSACTIONNUMBER)
                    when 26 then ISNULL(FTPARENT.CALCULATEDUSERDEFINEDID, FT.CALCULATEDUSERDEFINEDID)
                    when 27 then ISNULL(FTPARENT.CALCULATEDUSERDEFINEDID, FT.CALCULATEDUSERDEFINEDID)
                    else FT.CALCULATEDUSERDEFINEDID end as [TRANSACTIONNUMBER]
                ,coalesce(PE.SALEDATE, SE.SALEDATE, case when FT.TYPECODE = 26 then FTPARENT.[DATE] else FT.[DATE] end) [DATE]
                ,case when FT.TYPECODE in (24, 25) then ''http://www.blackbaud.com/DEPOSITCORRECTIONID?DEPOSITCORRECTIONID='' + CONVERT(nvarchar(36),FT.ID) else null end as DEPOSITCORRECTIONID
                ,case when FT.TYPECODE in (11, 12, 13, 14) then ''http://www.blackbaud.com/ADJUSTMENTID?ADJUSTMENTID='' + CONVERT(nvarchar(36), FT.ID) else null end as ADJUSTMENTID
                ,null as CREDITITEMID
                ,case when C.ID is not null then ''http://www.blackbaud.com/CREDITPAYMENTID?CREDITPAYMENTID=''  + CONVERT(nvarchar(36), C.ID) else null end as CREDITPAYMENTID
                ,JE.BASEAMOUNT as AMOUNT
                ,LI.POSTDATE
                ,LI.POSTSTATUS'

    set @SQLTOEXEC = @SQLTOEXEC + '
                ,case when LI.TYPECODE = 1 then LI.[TYPE] + '' - '' + JE.COMMENT else JE.COMMENT end as REFERENCE
                ,JE.TRANSACTIONTYPECODE
                ,JE.ID
                ,GLACCOUNT.ID as GLACCOUNTID
                ,GLACCOUNT.ACCOUNTNUMBER
                ,GLACCOUNT.ACCOUNTALIAS as ALIAS
                ,case GLACCOUNT.ACCOUNTALIAS when '''' then GLACCOUNT.ACCOUNTNUMBER else GLACCOUNT.ACCOUNTALIAS end as ACCOUNT
                ,case JE.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then JE.TRANSACTIONAMOUNT when 1 then JE.ORGAMOUNT else JE.BASEAMOUNT end else 0 end as DEBITAMOUNT
                ,case JE.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then JE.TRANSACTIONAMOUNT when 1 then JE.ORGAMOUNT else JE.BASEAMOUNT end else 0 end as CREDITAMOUNT
                ,isnull(REVENUEGLDISTRIBUTIONREVENUESPLITMAP.REVENUESPLITID, JOURNALENTRY_EXT.REVENUESPLITGIFTFEEID) REVENUESPLITID
                ,case when JOURNALENTRY_EXT.TABLENAMECODE = 5 then case when LI.DELETEDON is null then FT.ID else null end else null end as REVENUEBENEFITID
                ,case when JOURNALENTRY_EXT.TABLENAMECODE = 10 then ISNULL(FT.ID, JOURNALENTRY_EXT.PROPERTYDETAILID) else null end as PROPERTYDETAILID
                ,case when FT.TYPECODE in (24, 25) then FT.ID else null end BANKACCOUNTDEPOSITCORRECTIONID
                ,case when FT.TYPECODE in (11, 12, 13, 14) then FT.ID else null end BANKACCOUNTTRANSACTIONID
                ,case when JOURNALENTRY_EXT.TABLENAMECODE = 12 then JOURNALENTRY_EXT.WRITEOFFID else null end WRITEOFFID
                ,case when JOURNALENTRY_EXT.TABLENAMECODE = 11 then JOURNALENTRY_EXT.STOCKSALEID else null end STOCKSALEID
                ,null as PLANNEDGIFTPAYOUTID
                ,CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE
                ,CURRENCYPROPERTIES.CURRENCYSYMBOL
                ,CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE
                ,CURRENCYPROPERTIES.DECIMALDIGITS
                ,GLACCOUNT.ACCOUNTDESCRIPTION
            from dbo.JOURNALENTRY JE
            inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
            inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
            inner join dbo.GLACCOUNT on GLACCOUNT.ID = JE.GLACCOUNTID
            left join dbo.FINANCIALTRANSACTION FTPARENT on FTPARENT.ID = FT.PARENTID
            left join dbo.REVENUEGLDISTRIBUTIONREVENUESPLITMAP on JE.ID = REVENUEGLDISTRIBUTIONREVENUESPLITMAP.ID
            left join dbo.REVENUE_EXT on REVENUE_EXT.ID = FT.ID or REVENUE_EXT.ID = FTPARENT.ID
            left join dbo.JOURNALENTRY_EXT on JOURNALENTRY_EXT.ID = JE.ID
            left join dbo.CREDITPAYMENT CP on CP.ID = JOURNALENTRY_EXT.CREDITPAYMENTID
            left join dbo.CREDITITEM CI on CI.ID = JOURNALENTRY_EXT.CREDITITEMID
            left join dbo.CREDIT C on C.ID = CP.CREDITID or CI.CREDITID = C.ID
            left join dbo.SALESORDER on SALESORDER.ID = C.SALESORDERID
            left join dbo.BANKACCOUNTDEPOSITCORRECTION_EXT BADCE on BADCE.ID = FT.ID
            left join dbo.BANKACCOUNTTRANSACTION_EXT BATE on BATE.ID = FT.ID or BATE.ID = FT.PARENTID
            left join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FT.ID = V.FINANCIALTRANSACTIONID
            left join dbo.PROPERTYDETAIL_EXT PE on PE.ID = FT.ID
            left join dbo.STOCKSALE_EXT SE on SE.ID = FT.ID
            outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then V.BASECURRENCYID when 2 then JE.TRANSACTIONCURRENCYID else @SELECTEDCURRENCYID end) CURRENCYPROPERTIES
            where 
                FT.DELETEDON is null and  LI.Visible=1 and 
                (LI.POSTSTATUSCODE != 3)
                and (JE.TRANSACTIONTYPECODE = 0 and (case JE.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then JE.TRANSACTIONAMOUNT when 1 then JE.ORGAMOUNT else JE.BASEAMOUNT end else 0 end) <> 0 
                    or JE.TRANSACTIONTYPECODE=1 and (case JE.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then JE.TRANSACTIONAMOUNT when 1 then JE.ORGAMOUNT else JE.BASEAMOUNT end else 0 end) <> 0)
                and (GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)' +
                case when @STARTDATE is not null then ' and LI.POSTDATE >= @STARTDATE ' else '' end +
                case when @ENDDATE is not null then ' and LI.POSTDATE <= @ENDDATE ' else '' end +
                case when @POSTSTATUSCODE <> 0 then ' and LI.POSTSTATUSCODE = @POSTSTATUSCODE ' else '' end +
                case when @GLACCOUNTID is not null then ' and GLACCOUNT.ID = @GLACCOUNTID ' else '' end +
                case when @DBOBJECTNAME is not null then ' and GLACCOUNT.ID in (select [ID] from ' + @DBOBJECTNAME + ') ' else '' end +
                case when @CURRENCYCODE = 2 then ' and not JE.TRANSACTIONCURRENCYID is null ' else '' end +
                case 
                    when @TRANSACTIONTYPES is not null then ' 
                        and (FT.TYPECODE in (select T.TRANSACTIONTYPECODE from dbo.UFN_POST_TRANSACTIONTYPES_FROMITEMLISTXML(@TRANSACTIONTYPES) T where T.INCLUDE = 1) 
                        or ((not FT.TYPECODE in (20, 21, 22, 23, 27)) and FTPARENT.TYPECODE in (select T.TRANSACTIONTYPECODE from dbo.UFN_POST_TRANSACTIONTYPES_FROMITEMLISTXML(@TRANSACTIONTYPES) T where T.INCLUDE = 1)))' 
                    else '' 
                end + 
        ') as T ' + coalesce(@GROUPINGCOLUMNS, '')

        exec sp_executesql @SQLTOEXEC, 
            N'@STARTDATE datetime, @ENDDATE datetime, @CURRENCYCODE tinyint, @SELECTEDCURRENCYID uniqueidentifier, @PDACCOUNTSYSTEMID uniqueidentifier, @POSTSTATUSCODE tinyint, @GLACCOUNTID uniqueidentifier, @TRANSACTIONTYPES xml',
            @STARTDATE=@STARTDATE, @ENDDATE=@ENDDATE, @CURRENCYCODE=@CURRENCYCODE, @SELECTEDCURRENCYID=@SELECTEDCURRENCYID, @PDACCOUNTSYSTEMID=@PDACCOUNTSYSTEMID, @POSTSTATUSCODE=@POSTSTATUSCODE, @GLACCOUNTID=@GLACCOUNTID, @TRANSACTIONTYPES=@TRANSACTIONTYPES;