USP_DATAFORMTEMPLATE_VIEW_FINANCIALTRANSACTION_DISTRIBUTIONS

CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_FINANCIALTRANSACTION_DISTRIBUTIONS
(
    @ID uniqueidentifier
    ,@DATALOADED bit = 0 output
    ,@DISTRIBUTIONTYPES xml = null output
)
as
    set nocount on;

    --If we're coming from the order page, it sends in the order id
    if exists (select ID from dbo.SALESORDER where SALESORDER.ID = @ID)
        select @ID = REVENUEID from dbo.SALESORDER where SALESORDER.ID = @ID

    -- be sure to set this, in case the select returns no rows
    set @DATALOADED = 1;

    set @DISTRIBUTIONTYPES = (select distinct
        case JE.TYPECODE
            when 0 then
                case when isnull(ORIGINAL_JEX.TABLENAMECODE, JEX.TABLENAMECODE) is null then 999
                else case isnull(ORIGINAL_JEX.TABLENAMECODE, JEX.TABLENAMECODE)
                    when 0 then 1
                    when 5 then case isnull(ORIGINAL_JEX.BENEFITTYPECODE, JEX.BENEFITTYPECODE) 
                        when 1 then 5 
                        when 2 then 105 
                        when 3 then 205 
                        when 4 then 998 end
                    when 6 then case LI.TYPECODE when 5 then 6 else 106 end
                    else isnull(ORIGINAL_JEX.TABLENAMECODE, JEX.TABLENAMECODE) end end
            else 997 end [TABLENAMECODE]
        ,RS.APPLICATIONCODE
        ,RS.TYPECODE RSTYPECODE
        ,FT.TYPECODE FTTYPECODE
    from dbo.JOURNALENTRY JE
    inner join dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = JE.FINANCIALTRANSACTIONLINEITEMID
    inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
    left join dbo.REVENUESPLIT_EXT RS on RS.ID = isnull(LI.REVERSEDLINEITEMID, LI.ID)
    left join dbo.JOURNALENTRY_EXT ORIGINAL_JEX on JEX.REVERSEDGLTRANSACTIONID = ORIGINAL_JEX.ID
    where
        (FT.TYPECODE = 23 and FT.ID = @ID)
        or (isnull(FT.PARENTID, FT.ID) = @ID)
    order by FT.TYPECODE asc, TABLENAMECODE asc
    for xml raw('ITEM'),type,elements,root('DISTRIBUTIONTYPES'),BINARY BASE64);

    return 0;