USP_DATALIST_UNMAPPEDTRANSACTIONS

CREATE procedure dbo.USP_DATALIST_UNMAPPEDTRANSACTIONS
(
    @ID uniqueidentifier
)
as
    set nocount on;

    declare @PDACCOUNTSYSTEMID uniqueidentifier
    declare @ERRORMESSAGE nvarchar(max)
    declare @ADDRESSED bit;

    select @PDACCOUNTSYSTEMID = FT.PDACCOUNTSYSTEMID
        ,@ERRORMESSAGE = ME.ERRORMESSAGE
        ,@ADDRESSED = ME.ADDRESSED
    from dbo.GLACCOUNTMAPPINGERROR ME
    left join dbo.SALESORDER SO on SO.ID = ME.TRANSACTIONID
    left join dbo.FINANCIALTRANSACTION FT on FT.ID = ME.TRANSACTIONID or FT.ID = SO.REVENUEID
    where ME.ID = @ID

    select ME.TRANSACTIONID as [ID]
        ,CASE
            WHEN BATX.ID is not null
                THEN
                    CASE
                        WHEN BADCX.ID is not null
                            THEN BA.ACCOUNTNAME + ' - ' + cast(BATX.TRANSACTIONNUMBER as nvarchar(10)) + ' - ' + BADCX.CORRECTIONTYPE
                        ELSE 
                            BA.ACCOUNTNAME + ' - ' + cast(BATX.TRANSACTIONNUMBER as nvarchar(10))
                    END
            ELSE
                coalesce(PAYMENTFT.CALCULATEDUSERDEFINEDID, ORDERFT.CALCULATEDUSERDEFINEDID, FT.CALCULATEDUSERDEFINEDID)
        END as [TRANSACTIONID]
        ,ME.TRANSACTIONTYPE
        ,CONVERT(Date, FT.DATE) as [TRANSACTIONDATE]
        ,CA.USERNAME as [ADDEDBY]
        ,ME.TRANSACTIONTYPECODE
    from dbo.GLACCOUNTMAPPINGERROR ME
    left join dbo.SALESORDER SO on SO.ID = ME.TRANSACTIONID
    left join dbo.FINANCIALTRANSACTION FT on FT.ID = ME.TRANSACTIONID or FT.ID = SO.REVENUEID
    left join dbo.CHANGEAGENT CA on CA.ID = FT.ADDEDBYID
    left join dbo.CREDIT_EXT CX on CX.ID = FT.ID
    left join dbo.SALESORDER CREDITSO on CREDITSO.ID = CX.SALESORDERID
    left join dbo.FINANCIALTRANSACTION ORDERFT on ORDERFT.ID = CREDITSO.REVENUEID
    left join dbo.CREDITPAYMENT CP on CP.CREDITID = FT.ID
    left join dbo.FINANCIALTRANSACTION PAYMENTFT on PAYMENTFT.ID = CP.REVENUEID
    left join dbo.BANKACCOUNTDEPOSITCORRECTION_EXT BADCX on BADCX.ID = FT.ID
    left join dbo.BANKACCOUNTTRANSACTION_EXT BATX on BATX.ID = FT.ID or (BATX.ID = FT.PARENTID and BADCX.ID is not null)
    left join dbo.BANKACCOUNT BA on BA.ID = BATX.BANKACCOUNTID
    where 
        FT.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID
        and ME.ERRORMESSAGE = @ERRORMESSAGE
        and ME.DELETED = 0
        and ME.ADDRESSED = @ADDRESSED;