UFN_REPORT_ACCOUNTDISTRIBUTION

CREATE function dbo.UFN_REPORT_ACCOUNTDISTRIBUTION
(
    @STARTDATE datetime = null
    ,@ENDDATE datetime = null
    ,@CURRENCYCODE tinyint = 0
    ,@SELECTEDCURRENCYID uniqueidentifier = null
    ,@POSTSTATUSCODE tinyint
    ,@GLACCOUNTID uniqueidentifier = null
    ,@GLACCOUNTQUERY uniqueidentifier = null
    ,@PDACCOUNTSYSTEMID uniqueidentifier
    ,@TRANSACTIONTYPES nvarchar(max) = null
) returns table as
return
(
    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
        ,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 (@STARTDATE is null or LI.POSTDATE >= @STARTDATE) and (@ENDDATE is null or LI.POSTDATE <= @ENDDATE)
        and ((@POSTSTATUSCODE = 0) or (LI.POSTSTATUSCODE =  @POSTSTATUSCODE))
        and (LI.POSTSTATUSCODE != 3)
        and (@GLACCOUNTID is null or GLACCOUNT.ID = @GLACCOUNTID)
        and (@GLACCOUNTQUERY is null or GLACCOUNT.ID in (select [ID] from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@GLACCOUNTQUERY)))
        and (@CURRENCYCODE = 2 and not JE.TRANSACTIONCURRENCYID is null or @CURRENCYCODE in (0,1))
        and (GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
        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 (@TRANSACTIONTYPES is null or 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)))
)