USP_REPORT_ACCOUNTDISTRIBUTION

CREATE procedure dbo.USP_REPORT_ACCOUNTDISTRIBUTION
(
    @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
)
with execute as owner
as

    set nocount on;

    declare @SELECTEDCURRENCYID uniqueidentifier;

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

    --Set PDACCOUNTSYSTEMID for binary compatibility

    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 = coalesce(@POSTSTATUSCODE, 2); --Default for binary compatibility


    select 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36),R.ID) as REVENUEID,
        R.TRANSACTIONTYPE as [REVENUETYPE],
        R.LOOKUPID as TRANSACTIONNUMBER,
        R.[DATE],
        null as DEPOSITCORRECTIONID, 
        null as ADJUSTMENTID,
        null as CREDITITEMID,
        null as CREDITPAYMENTID,
        GLTRANSACTION.AMOUNT,
        GLTRANSACTION.POSTDATE,
        GLTRANSACTION.POSTSTATUS,
        case when GL2.ID is not null then 'Reversal - ' + GLTRANSACTION.REFERENCE else GLTRANSACTION.REFERENCE end as [REFERENCE],
        GLTRANSACTION.TRANSACTIONTYPECODE,
        GLTRANSACTION.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 GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end as DEBITAMOUNT,
        case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end as CREDITAMOUNT,
        REVENUEGLDISTRIBUTIONREVENUESPLITMAP.REVENUESPLITID AS REVENUESPLITID,
        null as REVENUEBENEFITID,
        null as PROPERTYDETAILID,
        null as BANKACCOUNTDEPOSITCORRECTIONID,
        null as BANKACCOUNTTRANSACTIONID,
        null as WRITEOFFID,
        null as STOCKSALEID,
        null as PLANNEDGIFTPAYOUTID,
        CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
        CURRENCYPROPERTIES.CURRENCYSYMBOL,
        CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, 
        CURRENCYPROPERTIES.DECIMALDIGITS,
        GLACCOUNT.ACCOUNTDESCRIPTION
    from GLTRANSACTION
    inner join dbo.GLACCOUNT on GLTRANSACTION.GLACCOUNTID = GLACCOUNT.ID
    left join dbo.REVENUEGLDISTRIBUTION on GLTRANSACTION.ID = REVENUEGLDISTRIBUTION.GLTRANSACTIONID
    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then GLTRANSACTION.BASECURRENCYID when 2 then GLTRANSACTION.TRANSACTIONCURRENCYID else @SELECTEDCURRENCYID end) CURRENCYPROPERTIES
    left join dbo.REVENUEGLDISTRIBUTION GL2 on GL2.GLTRANSACTIONID = GLTRANSACTION.REVERSEDGLTRANSACTIONID
    left join dbo.REVENUEGLDISTRIBUTIONREVENUESPLITMAP on REVENUEGLDISTRIBUTION.ID = REVENUEGLDISTRIBUTIONREVENUESPLITMAP.ID
    left join dbo.REVENUE R on R.ID = coalesce(REVENUEGLDISTRIBUTION.REVENUEID, GL2.REVENUEID)
    where
        (REVENUEGLDISTRIBUTION.ID is not null or GL2.ID is not null)
        and (@STARTDATE is null or GLTRANSACTION.POSTDATE >= @STARTDATE) and (@ENDDATE is null or GLTRANSACTION.POSTDATE <= @ENDDATE)
        and ((@POSTSTATUSCODE = 2) or (GLTRANSACTION.POSTSTATUSCODE =  @POSTSTATUSCODE))
        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 GLTRANSACTION.TRANSACTIONCURRENCYID is null or @CURRENCYCODE in (0,1))
        and (GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
        and (GLTRANSACTION.TRANSACTIONTYPECODE = 0 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0 
            or GLTRANSACTION.TRANSACTIONTYPECODE=1 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0)

    union all

    select 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36),R.ID) as REVENUEID,
        R.TRANSACTIONTYPE as [REVENUETYPE],
        R.LOOKUPID as TRANSACTIONNUMBER,
        R.[DATE],
        null as DEPOSITCORRECTIONID, 
        null as ADJUSTMENTID,
        null as CREDITITEMID,
        null as CREDITPAYMENTID,
        GLTRANSACTION.AMOUNT,
        GLTRANSACTION.POSTDATE,
        GLTRANSACTION.POSTSTATUS,
        case when GL2.ID is not null then 'Reversal - ' + GLTRANSACTION.REFERENCE else GLTRANSACTION.REFERENCE end as [REFERENCE],
        GLTRANSACTION.TRANSACTIONTYPECODE,
        GLTRANSACTION.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 GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end as DEBITAMOUNT,
        case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end as CREDITAMOUNT,
        null AS REVENUESPLITID,
        BENEFITGLDISTRIBUTION.REVENUEBENEFITID,
        null as PROPERTYDETAILID,
        null as BANKACCOUNTDEPOSITCORRECTIONID,
        null as BANKACCOUNTTRANSACTIONID,
        null as WRITEOFFID,
        null as STOCKSALEID,
        null as PLANNEDGIFTPAYOUTID,
        CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
        CURRENCYPROPERTIES.CURRENCYSYMBOL,
        CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, 
        CURRENCYPROPERTIES.DECIMALDIGITS,
        GLACCOUNT.ACCOUNTDESCRIPTION
    from GLTRANSACTION
    inner join dbo.GLACCOUNT on GLTRANSACTION.GLACCOUNTID = GLACCOUNT.ID
    left join dbo.BENEFITGLDISTRIBUTION on GLTRANSACTION.ID = BENEFITGLDISTRIBUTION.GLTRANSACTIONID
    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then GLTRANSACTION.BASECURRENCYID when 2 then GLTRANSACTION.TRANSACTIONCURRENCYID else @SELECTEDCURRENCYID end) CURRENCYPROPERTIES
    left join dbo.BENEFITGLDISTRIBUTION GL2 on GL2.GLTRANSACTIONID = GLTRANSACTION.REVERSEDGLTRANSACTIONID
    left join dbo.REVENUE R on R.ID = coalesce(BENEFITGLDISTRIBUTION.REVENUEID, GL2.REVENUEID)
    where
        (BENEFITGLDISTRIBUTION.ID is not null or GL2.ID is not null)
        and (@STARTDATE is null or GLTRANSACTION.POSTDATE >= @STARTDATE) and (@ENDDATE is null or GLTRANSACTION.POSTDATE <= @ENDDATE)
        and ((@POSTSTATUSCODE = 2) or (GLTRANSACTION.POSTSTATUSCODE =  @POSTSTATUSCODE))
        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 GLTRANSACTION.TRANSACTIONCURRENCYID is null or @CURRENCYCODE in (0,1))
        and (GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
        and (GLTRANSACTION.TRANSACTIONTYPECODE = 0 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0 
            or GLTRANSACTION.TRANSACTIONTYPECODE=1 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0)

    union all

    select 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36),R.ID) as REVENUEID,
        isnull(CREDIT.TYPE, R.TRANSACTIONTYPE) as [REVENUETYPE],
        isnull(convert(nvarchar(100), SALESORDER.SEQUENCEID), R.LOOKUPID) as TRANSACTIONNUMBER,
        coalesce(DISCOUNTCREDIT.TRANSACTIONDATE, CREDIT.TRANSACTIONDATE, R.[DATE]) as [DATE],
        null as DEPOSITCORRECTIONID, 
        null as ADJUSTMENTID,
        null as CREDITITEMID,
        'http://www.blackbaud.com/CREDITPAYMENTID?CREDITPAYMENTID=' + CONVERT(nvarchar(36), isnull(DISCOUNTCREDIT.ID, REFUND.CREDITID)) as CREDITPAYMENTID,
        GLTRANSACTION.AMOUNT,
        GLTRANSACTION.POSTDATE,
        GLTRANSACTION.POSTSTATUS,
        case when GL2.ID is not null then 'Reversal - ' + GLTRANSACTION.REFERENCE else GLTRANSACTION.REFERENCE end as [REFERENCE],
        GLTRANSACTION.TRANSACTIONTYPECODE,
        GLTRANSACTION.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 GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end as DEBITAMOUNT,
        case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end 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,
        CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
        CURRENCYPROPERTIES.CURRENCYSYMBOL,
        CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, 
        CURRENCYPROPERTIES.DECIMALDIGITS,
        GLACCOUNT.ACCOUNTDESCRIPTION
    from GLTRANSACTION
    inner join dbo.GLACCOUNT on GLTRANSACTION.GLACCOUNTID = GLACCOUNT.ID
    left join dbo.CREDITGLDISTRIBUTION on GLTRANSACTION.ID = CREDITGLDISTRIBUTION.GLTRANSACTIONID
    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then GLTRANSACTION.BASECURRENCYID when 2 then GLTRANSACTION.TRANSACTIONCURRENCYID else @SELECTEDCURRENCYID end) CURRENCYPROPERTIES
    left join dbo.CREDITGLDISTRIBUTION GL2 on GL2.GLTRANSACTIONID = GLTRANSACTION.REVERSEDGLTRANSACTIONID
    left join dbo.CREDITPAYMENT REFUND on REFUND.ID = CREDITGLDISTRIBUTION.CREDITPAYMENTID
    left join dbo.CREDITITEM DISCOUNT on DISCOUNT.ID = CREDITGLDISTRIBUTION.DISCOUNTCREDITITEMID
    left join dbo.CREDIT on CREDIT.ID = REFUND.CREDITID or CREDIT.ID = DISCOUNT.CREDITID
    left join dbo.CREDITITEM DISCOUNTREFUND on DISCOUNTREFUND.ID = CREDITGLDISTRIBUTION.CREDITITEMID and DISCOUNT.ID is not null
    left join dbo.CREDIT DISCOUNTCREDIT on DISCOUNTCREDIT.ID = DISCOUNTREFUND.CREDITID
    left join dbo.REVENUE R on R.ID = CREDITGLDISTRIBUTION.REVENUEID
    left join dbo.SALESORDER on CREDIT.SALESORDERID = SALESORDER.ID
    where
        (CREDITGLDISTRIBUTION.ID is not null or GL2.ID is not null)
        and (@STARTDATE is null or GLTRANSACTION.POSTDATE >= @STARTDATE) and (@ENDDATE is null or GLTRANSACTION.POSTDATE <= @ENDDATE)
        and ((@POSTSTATUSCODE = 2) or (GLTRANSACTION.POSTSTATUSCODE =  @POSTSTATUSCODE))
        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 GLTRANSACTION.TRANSACTIONCURRENCYID is null or @CURRENCYCODE in (0,1))
        and (GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
        and (GLTRANSACTION.TRANSACTIONTYPECODE = 0 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0 
            or GLTRANSACTION.TRANSACTIONTYPECODE=1 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0)

    union all

    select 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36),R.ID) as REVENUEID,
        R.TRANSACTIONTYPE as [REVENUETYPE],
        R.LOOKUPID as TRANSACTIONNUMBER,
        R.[DATE],
        null as DEPOSITCORRECTIONID, 
        null as ADJUSTMENTID,
        null as CREDITITEMID,
        null as CREDITPAYMENTID,
        GLTRANSACTION.AMOUNT,
        GLTRANSACTION.POSTDATE,
        GLTRANSACTION.POSTSTATUS,
        case when GL2.ID is not null then 'Reversal - ' + GLTRANSACTION.REFERENCE else GLTRANSACTION.REFERENCE end as [REFERENCE],
        GLTRANSACTION.TRANSACTIONTYPECODE,
        GLTRANSACTION.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 GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end as DEBITAMOUNT,
        case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end as CREDITAMOUNT,
        null AS REVENUESPLITID,
        null as REVENUEBENEFITID,
        PROPERTYDETAILGLDISTRIBUTION.PROPERTYDETAILID,
        null as BANKACCOUNTDEPOSITCORRECTIONID,
        null as BANKACCOUNTTRANSACTIONID,
        null as WRITEOFFID,
        null as STOCKSALEID,
        null as PLANNEDGIFTPAYOUTID,
        CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
        CURRENCYPROPERTIES.CURRENCYSYMBOL,
        CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, 
        CURRENCYPROPERTIES.DECIMALDIGITS,
        GLACCOUNT.ACCOUNTDESCRIPTION
    from GLTRANSACTION
    inner join dbo.GLACCOUNT on GLTRANSACTION.GLACCOUNTID = GLACCOUNT.ID
    left join dbo.PROPERTYDETAILGLDISTRIBUTION on GLTRANSACTION.ID = PROPERTYDETAILGLDISTRIBUTION.GLTRANSACTIONID
    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then GLTRANSACTION.BASECURRENCYID when 2 then GLTRANSACTION.TRANSACTIONCURRENCYID else @SELECTEDCURRENCYID end) CURRENCYPROPERTIES
    left join dbo.PROPERTYDETAILGLDISTRIBUTION GL2 on GL2.GLTRANSACTIONID = GLTRANSACTION.REVERSEDGLTRANSACTIONID
    left join dbo.REVENUE R on R.ID = coalesce(PROPERTYDETAILGLDISTRIBUTION.REVENUEID, GL2.REVENUEID)
    where
        (PROPERTYDETAILGLDISTRIBUTION.ID is not null or GL2.ID is not null)
        and (@STARTDATE is null or GLTRANSACTION.POSTDATE >= @STARTDATE) and (@ENDDATE is null or GLTRANSACTION.POSTDATE <= @ENDDATE)
        and ((@POSTSTATUSCODE = 2) or (GLTRANSACTION.POSTSTATUSCODE =  @POSTSTATUSCODE))
        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 GLTRANSACTION.TRANSACTIONCURRENCYID is null or @CURRENCYCODE in (0,1))
        and (GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
        and (GLTRANSACTION.TRANSACTIONTYPECODE = 0 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0 
            or GLTRANSACTION.TRANSACTIONTYPECODE=1 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0)

    union all

    select null as REVENUEID, --'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36),R.ID) as REVENUEID,

        BANKACCOUNTDEPOSITCORRECTION.CORRECTIONTYPE as REVENUETYPE,
        convert(nvarchar(36),BANKACCOUNTTRANSACTION.TRANSACTIONNUMBER) as TRANSACTIONNUMBER,
        BANKACCOUNTDEPOSITCORRECTION.[DATE],
        'http://www.blackbaud.com/DEPOSITCORRECTIONID?DEPOSITCORRECTIONID=' + CONVERT(nvarchar(36),BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.BANKACCOUNTDEPOSITCORRECTIONID) as DEPOSITCORRECTIONID,
        null as ADJUSTMENTID,
        null as CREDITITEMID,
        null as CREDITPAYMENTID,
        GLTRANSACTION.AMOUNT,
        GLTRANSACTION.POSTDATE,
        GLTRANSACTION.POSTSTATUS,
        GLTRANSACTION.REFERENCE,
        GLTRANSACTION.TRANSACTIONTYPECODE,
        GLTRANSACTION.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 GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end as DEBITAMOUNT,
        case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end as CREDITAMOUNT,
        null AS REVENUESPLITID,
        null as REVENUEBENEFITID,
        null as PROPERTYDETAILID,
        BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.BANKACCOUNTDEPOSITCORRECTIONID,
        null as BANKACCOUNTTRANSACTIONID,
        null as WRITEOFFID,
        null as STOCKSALEID,
        null as PLANNEDGIFTPAYOUTID,
        CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
        CURRENCYPROPERTIES.CURRENCYSYMBOL,
        CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, 
        CURRENCYPROPERTIES.DECIMALDIGITS,
        GLACCOUNT.ACCOUNTDESCRIPTION
    from GLTRANSACTION
    inner join dbo.GLACCOUNT on GLTRANSACTION.GLACCOUNTID = GLACCOUNT.ID
    inner join dbo.BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION on GLTRANSACTION.ID = BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.GLTRANSACTIONID
    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then GLTRANSACTION.BASECURRENCYID when 2 then GLTRANSACTION.TRANSACTIONCURRENCYID else @SELECTEDCURRENCYID end) CURRENCYPROPERTIES
    inner join dbo.BANKACCOUNTDEPOSITCORRECTION on BANKACCOUNTDEPOSITCORRECTION.ID = BANKACCOUNTDEPOSITCORRECTIONGLDISTRIBUTION.BANKACCOUNTDEPOSITCORRECTIONID
    inner join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTION.ID = BANKACCOUNTDEPOSITCORRECTION.DEPOSITID
    where
        (@STARTDATE is null or GLTRANSACTION.POSTDATE >= @STARTDATE) and (@ENDDATE is null or GLTRANSACTION.POSTDATE <= @ENDDATE)
        and ((@POSTSTATUSCODE = 2) or (GLTRANSACTION.POSTSTATUSCODE =  @POSTSTATUSCODE))
        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 GLTRANSACTION.TRANSACTIONCURRENCYID is null or @CURRENCYCODE in (0,1))
        and (GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
        and (GLTRANSACTION.TRANSACTIONTYPECODE = 0 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0 
            or GLTRANSACTION.TRANSACTIONTYPECODE=1 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0)

    union all

    select null as REVENUEID, --'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36),R.ID) as REVENUEID,

        BANKACCOUNTTRANSACTION.TRANSACTIONTYPE as REVENUETYPE,
        convert(nvarchar(36),BANKACCOUNTTRANSACTION.TRANSACTIONNUMBER) as TRANSACTIONNUMBER,
        BANKACCOUNTTRANSACTION.TRANSACTIONDATE as [DATE],
        null as DEPOSITCORRECTIONID, 
        'http://www.blackbaud.com/ADJUSTMENTID?ADJUSTMENTID=' + CONVERT(nvarchar(36), BANKACCOUNTTRANSACTIONGLDISTRIBUTION.BANKACCOUNTTRANSACTIONID) as ADJUSTMENTID,
        null as CREDITITEMID,
        null as CREDITPAYMENTID,
        GLTRANSACTION.AMOUNT,
        GLTRANSACTION.POSTDATE,
        GLTRANSACTION.POSTSTATUS,
        GLTRANSACTION.REFERENCE,
        GLTRANSACTION.TRANSACTIONTYPECODE,
        GLTRANSACTION.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 GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end as DEBITAMOUNT,
        case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end as CREDITAMOUNT,
        null AS REVENUESPLITID,
        null as REVENUEBENEFITID,
        null as PROPERTYDETAILID,
        null as BANKACCOUNTDEPOSITCORRECTIONID,
        BANKACCOUNTTRANSACTIONGLDISTRIBUTION.BANKACCOUNTTRANSACTIONID,
        null as WRITEOFFID,
        null as STOCKSALEID,
        null as PLANNEDGIFTPAYOUTID,
        CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
        CURRENCYPROPERTIES.CURRENCYSYMBOL,
        CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, 
        CURRENCYPROPERTIES.DECIMALDIGITS,
        GLACCOUNT.ACCOUNTDESCRIPTION
    from GLTRANSACTION
    inner join dbo.GLACCOUNT on GLTRANSACTION.GLACCOUNTID = GLACCOUNT.ID
    inner join dbo.BANKACCOUNTTRANSACTIONGLDISTRIBUTION on GLTRANSACTION.ID = BANKACCOUNTTRANSACTIONGLDISTRIBUTION.GLTRANSACTIONID
    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then GLTRANSACTION.BASECURRENCYID when 2 then GLTRANSACTION.TRANSACTIONCURRENCYID else @SELECTEDCURRENCYID end) CURRENCYPROPERTIES
    inner join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTTRANSACTION.ID = BANKACCOUNTTRANSACTIONGLDISTRIBUTION.BANKACCOUNTTRANSACTIONID
    where
        (@STARTDATE is null or GLTRANSACTION.POSTDATE >= @STARTDATE) and (@ENDDATE is null or GLTRANSACTION.POSTDATE <= @ENDDATE)
        and ((@POSTSTATUSCODE = 2) or (GLTRANSACTION.POSTSTATUSCODE =  @POSTSTATUSCODE))
        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 GLTRANSACTION.TRANSACTIONCURRENCYID is null or @CURRENCYCODE in (0,1))
        and (GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
        and (GLTRANSACTION.TRANSACTIONTYPECODE = 0 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0 
            or GLTRANSACTION.TRANSACTIONTYPECODE=1 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0)

    union all

    select 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36),R.ID) as REVENUEID,
        R.TRANSACTIONTYPE as [REVENUETYPE],
        R.LOOKUPID as TRANSACTIONNUMBER,
        R.[DATE],
        null as DEPOSITCORRECTIONID, 
        null as ADJUSTMENTID,
        null as CREDITITEMID,
        null as CREDITPAYMENTID,
        GLTRANSACTION.AMOUNT,
        GLTRANSACTION.POSTDATE,
        GLTRANSACTION.POSTSTATUS,
        case when GL2.ID is not null then 'Reversal - ' + GLTRANSACTION.REFERENCE else GLTRANSACTION.REFERENCE end as [REFERENCE],
        GLTRANSACTION.TRANSACTIONTYPECODE,
        GLTRANSACTION.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 GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end as DEBITAMOUNT,
        case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end as CREDITAMOUNT,
        null AS REVENUESPLITID,
        null as REVENUEBENEFITID,
        null as PROPERTYDETAILID,
        null as BANKACCOUNTDEPOSITCORRECTIONID,
        null as BANKACCOUNTTRANSACTIONID,
        WRITEOFFGLDISTRIBUTION.WRITEOFFID,        
        null as STOCKSALEID,
        null as PLANNEDGIFTPAYOUTID,
        CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
        CURRENCYPROPERTIES.CURRENCYSYMBOL,
        CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, 
        CURRENCYPROPERTIES.DECIMALDIGITS,
        GLACCOUNT.ACCOUNTDESCRIPTION
    from GLTRANSACTION
    inner join dbo.GLACCOUNT on GLTRANSACTION.GLACCOUNTID = GLACCOUNT.ID
    left join dbo.WRITEOFFGLDISTRIBUTION on GLTRANSACTION.ID = WRITEOFFGLDISTRIBUTION.GLTRANSACTIONID
    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then GLTRANSACTION.BASECURRENCYID when 2 then GLTRANSACTION.TRANSACTIONCURRENCYID else @SELECTEDCURRENCYID end) CURRENCYPROPERTIES
    left join dbo.WRITEOFFGLDISTRIBUTION GL2 on GL2.GLTRANSACTIONID = GLTRANSACTION.REVERSEDGLTRANSACTIONID
    left join dbo.REVENUE R on R.ID = coalesce(WRITEOFFGLDISTRIBUTION.REVENUEID, GL2.REVENUEID)
    where
        (WRITEOFFGLDISTRIBUTION.ID is not null or GL2.ID is not null)
        and (@STARTDATE is null or GLTRANSACTION.POSTDATE >= @STARTDATE) and (@ENDDATE is null or GLTRANSACTION.POSTDATE <= @ENDDATE)
        and ((@POSTSTATUSCODE = 2) or (GLTRANSACTION.POSTSTATUSCODE =  @POSTSTATUSCODE))
        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 GLTRANSACTION.TRANSACTIONCURRENCYID is null or @CURRENCYCODE in (0,1))
        and (GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
        and (GLTRANSACTION.TRANSACTIONTYPECODE = 0 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0 
            or GLTRANSACTION.TRANSACTIONTYPECODE=1 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0)

    union all

    select 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36),R.ID) as REVENUEID,
        R.TRANSACTIONTYPE as [REVENUETYPE],
        R.LOOKUPID as TRANSACTIONNUMBER,
        R.[DATE],
        null as DEPOSITCORRECTIONID, 
        null as ADJUSTMENTID,
        null as CREDITITEMID,
        null as CREDITPAYMENTID,
        GLTRANSACTION.AMOUNT,
        GLTRANSACTION.POSTDATE,
        GLTRANSACTION.POSTSTATUS,
        case when GL2.ID is not null then 'Reversal - ' + GLTRANSACTION.REFERENCE else GLTRANSACTION.REFERENCE end as [REFERENCE],
        GLTRANSACTION.TRANSACTIONTYPECODE,
        GLTRANSACTION.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 GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end as DEBITAMOUNT,
        case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end as CREDITAMOUNT,
        null AS REVENUESPLITID,
        null as REVENUEBENEFITID,
        null as PROPERTYDETAILID,
        null as BANKACCOUNTDEPOSITCORRECTIONID,
        null as BANKACCOUNTTRANSACTIONID,
        null as WRITEOFFID,
        STOCKSALEGLDISTRIBUTION.STOCKSALEID,
        null as PLANNEDGIFTPAYOUTID,
        CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
        CURRENCYPROPERTIES.CURRENCYSYMBOL,
        CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, 
        CURRENCYPROPERTIES.DECIMALDIGITS,
        GLACCOUNT.ACCOUNTDESCRIPTION
    from GLTRANSACTION
    inner join dbo.GLACCOUNT on GLTRANSACTION.GLACCOUNTID = GLACCOUNT.ID
    left join dbo.STOCKSALEGLDISTRIBUTION on GLTRANSACTION.ID = STOCKSALEGLDISTRIBUTION.GLTRANSACTIONID
    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then GLTRANSACTION.BASECURRENCYID when 2 then GLTRANSACTION.TRANSACTIONCURRENCYID else @SELECTEDCURRENCYID end) CURRENCYPROPERTIES
    left join dbo.STOCKSALEGLDISTRIBUTION GL2 on GL2.GLTRANSACTIONID = GLTRANSACTION.REVERSEDGLTRANSACTIONID
    left join dbo.REVENUE R on R.ID = coalesce(STOCKSALEGLDISTRIBUTION.REVENUEID, GL2.REVENUEID)
    where
        (STOCKSALEGLDISTRIBUTION.ID is not null or GL2.ID is not null)
        and (@STARTDATE is null or GLTRANSACTION.POSTDATE >= @STARTDATE) and (@ENDDATE is null or GLTRANSACTION.POSTDATE <= @ENDDATE)
        and ((@POSTSTATUSCODE = 2) or (GLTRANSACTION.POSTSTATUSCODE =  @POSTSTATUSCODE))
        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 GLTRANSACTION.TRANSACTIONCURRENCYID is null or @CURRENCYCODE in (0,1))
        and (GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
        and (GLTRANSACTION.TRANSACTIONTYPECODE = 0 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0 
            or GLTRANSACTION.TRANSACTIONTYPECODE=1 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0)

    union all

    select 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36),R.ID) as REVENUEID,
        R.TRANSACTIONTYPE as [REVENUETYPE],
        R.LOOKUPID as TRANSACTIONNUMBER,
        R.[DATE],
        null as DEPOSITCORRECTIONID, 
        null as ADJUSTMENTID,
        null as CREDITITEMID,
        null as CREDITPAYMENTID,
        GLTRANSACTION.AMOUNT,
        GLTRANSACTION.POSTDATE,
        GLTRANSACTION.POSTSTATUS,
        case when GL2.ID is not null then 'Reversal - ' + GLTRANSACTION.REFERENCE else GLTRANSACTION.REFERENCE end as [REFERENCE],
        GLTRANSACTION.TRANSACTIONTYPECODE,
        GLTRANSACTION.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 GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end as DEBITAMOUNT,
        case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end 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,
        CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
        CURRENCYPROPERTIES.CURRENCYSYMBOL,
        CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, 
        CURRENCYPROPERTIES.DECIMALDIGITS,
        GLACCOUNT.ACCOUNTDESCRIPTION
    from GLTRANSACTION
    inner join dbo.GLACCOUNT on GLTRANSACTION.GLACCOUNTID = GLACCOUNT.ID
    left join dbo.AUCTIONPURCHASEGLDISTRIBUTION on GLTRANSACTION.ID = AUCTIONPURCHASEGLDISTRIBUTION.GLTRANSACTIONID
    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then GLTRANSACTION.BASECURRENCYID when 2 then GLTRANSACTION.TRANSACTIONCURRENCYID else @SELECTEDCURRENCYID end) CURRENCYPROPERTIES
    left join dbo.AUCTIONPURCHASEGLDISTRIBUTION GL2 on GL2.GLTRANSACTIONID = GLTRANSACTION.REVERSEDGLTRANSACTIONID
    left join dbo.REVENUE R on R.ID = coalesce(AUCTIONPURCHASEGLDISTRIBUTION.REVENUEPURCHASEID, GL2.REVENUEPURCHASEID)
    where
        (AUCTIONPURCHASEGLDISTRIBUTION.ID is not null or GL2.ID is not null)
        and (@STARTDATE is null or GLTRANSACTION.POSTDATE >= @STARTDATE) and (@ENDDATE is null or GLTRANSACTION.POSTDATE <= @ENDDATE)
        and ((@POSTSTATUSCODE = 2) or (GLTRANSACTION.POSTSTATUSCODE =  @POSTSTATUSCODE))
        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 GLTRANSACTION.TRANSACTIONCURRENCYID is null or @CURRENCYCODE in (0,1))
        and (GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
        and (GLTRANSACTION.TRANSACTIONTYPECODE = 0 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0 
            or GLTRANSACTION.TRANSACTIONTYPECODE=1 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0)

    union all

    select 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36),R.ID) as REVENUEID,
        R.TRANSACTIONTYPE as [REVENUETYPE],
        R.LOOKUPID as TRANSACTIONNUMBER,
        R.[DATE],
        null as DEPOSITCORRECTIONID, 
        null as ADJUSTMENTID,
        null as CREDITITEMID,
        null as CREDITPAYMENTID,
        GLTRANSACTION.AMOUNT,
        GLTRANSACTION.POSTDATE,
        GLTRANSACTION.POSTSTATUS,
        case when GL2.ID is not null then 'Reversal - ' + GLTRANSACTION.REFERENCE else GLTRANSACTION.REFERENCE end as [REFERENCE],
        GLTRANSACTION.TRANSACTIONTYPECODE,
        GLTRANSACTION.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 GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end as DEBITAMOUNT,
        case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end as CREDITAMOUNT,
        GIFTFEEGLDISTRIBUTION.REVENUESPLITGIFTFEEID AS REVENUESPLITID,
        null as REVENUEBENEFITID,
        null as PROPERTYDETAILID,
        null as BANKACCOUNTDEPOSITCORRECTIONID,
        null as BANKACCOUNTTRANSACTIONID,
        null as WRITEOFFID,
        null as STOCKSALEID,
        null as PLANNEDGIFTPAYOUTID,
        CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
        CURRENCYPROPERTIES.CURRENCYSYMBOL,
        CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, 
        CURRENCYPROPERTIES.DECIMALDIGITS,
        GLACCOUNT.ACCOUNTDESCRIPTION
    from GLTRANSACTION
    inner join dbo.GLACCOUNT on GLTRANSACTION.GLACCOUNTID = GLACCOUNT.ID
    left join dbo.GIFTFEEGLDISTRIBUTION on GLTRANSACTION.ID = GIFTFEEGLDISTRIBUTION.GLTRANSACTIONID
    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then GLTRANSACTION.BASECURRENCYID when 2 then GLTRANSACTION.TRANSACTIONCURRENCYID else @SELECTEDCURRENCYID end) CURRENCYPROPERTIES
    left join dbo.GIFTFEEGLDISTRIBUTION GL2 on GL2.GLTRANSACTIONID = GLTRANSACTION.REVERSEDGLTRANSACTIONID
    left join dbo.REVENUE R on R.ID = coalesce(GIFTFEEGLDISTRIBUTION.REVENUEID, GL2.REVENUEID)
    where
        (GIFTFEEGLDISTRIBUTION.ID is not null or GL2.ID is not null)
        and (@STARTDATE is null or GLTRANSACTION.POSTDATE >= @STARTDATE) and (@ENDDATE is null or GLTRANSACTION.POSTDATE <= @ENDDATE)
        and ((@POSTSTATUSCODE = 2) or (GLTRANSACTION.POSTSTATUSCODE =  @POSTSTATUSCODE))
        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 GLTRANSACTION.TRANSACTIONCURRENCYID is null or @CURRENCYCODE in (0,1))
        and (GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
        and (GLTRANSACTION.TRANSACTIONTYPECODE = 0 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0 
            or GLTRANSACTION.TRANSACTIONTYPECODE=1 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0)

    union all

    select 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36),R.ID) as REVENUEID,
        R.TRANSACTIONTYPE as [REVENUETYPE],
        R.LOOKUPID as TRANSACTIONNUMBER,
        R.[DATE],
        null as DEPOSITCORRECTIONID, 
        null as ADJUSTMENTID,
        null as CREDITITEMID,
        null as CREDITPAYMENTID,
        GLTRANSACTION.AMOUNT,
        GLTRANSACTION.POSTDATE,
        GLTRANSACTION.POSTSTATUS,
        case when GL2.ID is not null then 'Reversal - ' + GLTRANSACTION.REFERENCE else GLTRANSACTION.REFERENCE end as [REFERENCE],
        GLTRANSACTION.TRANSACTIONTYPECODE,
        GLTRANSACTION.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 GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end as DEBITAMOUNT,
        case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end 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,
        CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
        CURRENCYPROPERTIES.CURRENCYSYMBOL,
        CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, 
        CURRENCYPROPERTIES.DECIMALDIGITS,
        GLACCOUNT.ACCOUNTDESCRIPTION
    from GLTRANSACTION
    inner join dbo.GLACCOUNT on GLTRANSACTION.GLACCOUNTID = GLACCOUNT.ID
    left join dbo.UNREALIZEDGAINLOSSGLDISTRIBUTION on GLTRANSACTION.ID = UNREALIZEDGAINLOSSGLDISTRIBUTION.GLTRANSACTIONID
    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then GLTRANSACTION.BASECURRENCYID when 2 then GLTRANSACTION.TRANSACTIONCURRENCYID else @SELECTEDCURRENCYID end) CURRENCYPROPERTIES
    left join dbo.UNREALIZEDGAINLOSSGLDISTRIBUTION GL2 on GL2.GLTRANSACTIONID = GLTRANSACTION.REVERSEDGLTRANSACTIONID
    left join dbo.REVENUE R on R.ID = coalesce(UNREALIZEDGAINLOSSGLDISTRIBUTION.REVENUEID, GL2.REVENUEID)
    where
        (UNREALIZEDGAINLOSSGLDISTRIBUTION.ID is not null or GL2.ID is not null)
        and (@STARTDATE is null or GLTRANSACTION.POSTDATE >= @STARTDATE) and (@ENDDATE is null or GLTRANSACTION.POSTDATE <= @ENDDATE)
        and ((@POSTSTATUSCODE = 2) or (GLTRANSACTION.POSTSTATUSCODE =  @POSTSTATUSCODE))
        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 GLTRANSACTION.TRANSACTIONCURRENCYID is null or @CURRENCYCODE in (0,1))
        and (GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
        and (GLTRANSACTION.TRANSACTIONTYPECODE = 0 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0 
            or GLTRANSACTION.TRANSACTIONTYPECODE=1 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0)

    union all

    select 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36),R.ID) as REVENUEID,
        R.TRANSACTIONTYPE as [REVENUETYPE],
        R.LOOKUPID as TRANSACTIONNUMBER,
        R.[DATE],
        null as DEPOSITCORRECTIONID, 
        null as ADJUSTMENTID,
        null as CREDITITEMID,
        null as CREDITPAYMENTID,
        GLTRANSACTION.AMOUNT,
        GLTRANSACTION.POSTDATE,
        GLTRANSACTION.POSTSTATUS,
        case when GL2.ID is not null then 'Reversal - ' + GLTRANSACTION.REFERENCE else GLTRANSACTION.REFERENCE end as [REFERENCE],
        GLTRANSACTION.TRANSACTIONTYPECODE,
        GLTRANSACTION.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 GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end as DEBITAMOUNT,
        case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end 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,
        CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
        CURRENCYPROPERTIES.CURRENCYSYMBOL,
        CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, 
        CURRENCYPROPERTIES.DECIMALDIGITS,
        GLACCOUNT.ACCOUNTDESCRIPTION
    from GLTRANSACTION
    inner join dbo.GLACCOUNT on GLTRANSACTION.GLACCOUNTID = GLACCOUNT.ID
    left join dbo.PLANNEDGIFTPAYOUTGLDISTRIBUTION on GLTRANSACTION.ID = PLANNEDGIFTPAYOUTGLDISTRIBUTION.GLTRANSACTIONID
    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then GLTRANSACTION.BASECURRENCYID when 2 then GLTRANSACTION.TRANSACTIONCURRENCYID else @SELECTEDCURRENCYID end) CURRENCYPROPERTIES
    left join dbo.PLANNEDGIFTPAYOUTGLDISTRIBUTION GL2 on GL2.GLTRANSACTIONID = GLTRANSACTION.REVERSEDGLTRANSACTIONID
    left join dbo.REVENUE R on R.ID = coalesce(PLANNEDGIFTPAYOUTGLDISTRIBUTION.REVENUEID, GL2.REVENUEID)
    where
        (PLANNEDGIFTPAYOUTGLDISTRIBUTION.ID is not null or GL2.ID is not null)
        and (@STARTDATE is null or GLTRANSACTION.POSTDATE >= @STARTDATE) and (@ENDDATE is null or GLTRANSACTION.POSTDATE <= @ENDDATE)
        and ((@POSTSTATUSCODE = 2) or (GLTRANSACTION.POSTSTATUSCODE =  @POSTSTATUSCODE))
        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 GLTRANSACTION.TRANSACTIONCURRENCYID is null or @CURRENCYCODE in (0,1))
        and (GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
        and (GLTRANSACTION.TRANSACTIONTYPECODE = 0 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0 
            or GLTRANSACTION.TRANSACTIONTYPECODE=1 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0)

    union all

    select 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36),R.ID) as REVENUEID,
        R.TRANSACTIONTYPE as [REVENUETYPE],
        R.LOOKUPID as TRANSACTIONNUMBER,
        R.[DATE],
        null as DEPOSITCORRECTIONID, 
        null as ADJUSTMENTID,
        null as CREDITITEMID,
        null as CREDITPAYMENTID,
        GLTRANSACTION.AMOUNT,
        GLTRANSACTION.POSTDATE,
        GLTRANSACTION.POSTSTATUS,
        case when GL2.ID is not null then 'Reversal - ' + GLTRANSACTION.REFERENCE else GLTRANSACTION.REFERENCE end as [REFERENCE],
        GLTRANSACTION.TRANSACTIONTYPECODE,
        GLTRANSACTION.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 GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end as DEBITAMOUNT,
        case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end 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,
        CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
        CURRENCYPROPERTIES.CURRENCYSYMBOL,
        CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, 
        CURRENCYPROPERTIES.DECIMALDIGITS,
        GLACCOUNT.ACCOUNTDESCRIPTION
    from GLTRANSACTION
    inner join dbo.GLACCOUNT on GLTRANSACTION.GLACCOUNTID = GLACCOUNT.ID
    left join dbo.GIFTINKINDSALEGLDISTRIBUTION on GLTRANSACTION.ID = GIFTINKINDSALEGLDISTRIBUTION.GLTRANSACTIONID
    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 0 then GLTRANSACTION.BASECURRENCYID when 2 then GLTRANSACTION.TRANSACTIONCURRENCYID else @SELECTEDCURRENCYID end) CURRENCYPROPERTIES
    left join dbo.GIFTINKINDSALEGLDISTRIBUTION GL2 on GL2.GLTRANSACTIONID = GLTRANSACTION.REVERSEDGLTRANSACTIONID
    left join dbo.REVENUE R on R.ID = coalesce(GIFTINKINDSALEGLDISTRIBUTION.REVENUEID, GL2.REVENUEID)
    where
        (GIFTINKINDSALEGLDISTRIBUTION.ID is not null or GL2.ID is not null)
        and (@STARTDATE is null or GLTRANSACTION.POSTDATE >= @STARTDATE) and (@ENDDATE is null or GLTRANSACTION.POSTDATE <= @ENDDATE)
        and ((@POSTSTATUSCODE = 2) or (GLTRANSACTION.POSTSTATUSCODE =  @POSTSTATUSCODE))
        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 GLTRANSACTION.TRANSACTIONCURRENCYID is null or @CURRENCYCODE in (0,1))
        and (GLACCOUNT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID)
        and (GLTRANSACTION.TRANSACTIONTYPECODE = 0 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 0 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0 
            or GLTRANSACTION.TRANSACTIONTYPECODE=1 and (case GLTRANSACTION.TRANSACTIONTYPECODE when 1 then case @CURRENCYCODE when 2 then GLTRANSACTION.TRANSACTIONAMOUNT  when 1 then GLTRANSACTION.ORGANIZATIONAMOUNT else GLTRANSACTION.AMOUNT end else 0 end) <> 0)