USP_DATALIST_SALESORDER_EVENTREGISTRATIONS2

CREATE procedure dbo.USP_DATALIST_SALESORDER_EVENTREGISTRATIONS2
(
    @CONTEXTID uniqueidentifier
)
as
    set nocount on;

    --The top node

    select
        SALESORDERITEM.ID,
        null as SALESORDERITEMID,
        NF.NAME as REGISTRANTNAME,
        EVENT.NAME as DESCRIPTION,
        EVENT.STARTDATE,
        EVENT.STARTTIME,
        REGISTRANTAMOUNTS.QUANTITY,
        REGISTRANTAMOUNTS.AMOUNT,
        SALESORDERITEM.TOTAL as ORDERAMOUNT,
        SALESORDERITEMEVENTREGISTRATION.REGISTRANTID as REGISTRANTID,
        FINANCIALTRANSACTIONLINEITEM.ID as REVENUEAPPLICATIONID
    from dbo.SALESORDERITEM
    inner join dbo.SALESORDERITEMEVENTREGISTRATION on SALESORDERITEM.ID = SALESORDERITEMEVENTREGISTRATION.ID
    inner join dbo.REGISTRANT on SALESORDERITEMEVENTREGISTRATION.REGISTRANTID = REGISTRANT.ID
    inner join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
    inner join dbo.SALESORDER on SALESORDERITEM.SALESORDERID = SALESORDER.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM on SALESORDER.REVENUEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
    inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
    left join dbo.CREDITITEM_EXT as EXT on EXT.SALESORDERITEMID = SALESORDERITEM.ID
    left join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = EXT.ID
    left join dbo.FINANCIALTRANSACTION as FT on FT.ID = LI.FINANCIALTRANSACTIONID
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REGISTRANT.CONSTITUENTID) as NF
    outer apply (
        select
            isnull(sum(SALESORDERITEMEVENTREGISTRANTREGISTRATION.QUANTITY), 0) as QUANTITY,
            isnull(sum(SALESORDERITEMEVENTREGISTRANTREGISTRATION.AMOUNT), 0) as AMOUNT
        from dbo.SALESORDERITEMEVENTREGISTRANTREGISTRATION
        where SALESORDERITEMEVENTREGISTRANTREGISTRATION.SALESORDERITEMEVENTREGISTRATIONID = SALESORDERITEM.ID
    ) as REGISTRANTAMOUNTS
    where
        SALESORDERITEM.SALESORDERID = @CONTEXTID and
        REVENUESPLIT_EXT.TYPECODE = 1 and
        REVENUESPLIT_EXT.APPLICATIONCODE = 1
    group by
        EXT.GROUPID,
        SALESORDERITEM.ID,
        EVENT.NAME,
        EVENT.STARTDATE,
        EVENT.STARTTIME,
        SALESORDERITEM.TOTAL,
        SALESORDERITEMEVENTREGISTRATION.REGISTRANTID,
        FT.DATE,
        NF.NAME,
        REGISTRANTAMOUNTS.QUANTITY,
        REGISTRANTAMOUNTS.AMOUNT,
        FINANCIALTRANSACTIONLINEITEM.ID

    union all

    --The leaf nodes

    select
        null,
        SALESORDERITEM.ID as SALESORDERITEMID,
        null as REGISTRANTNAME,
        EVENT.NAME + ' - ' + EVENTPRICE.NAME as DESCRIPTION,
        null as STARTDATE,
        null as STARTTIME,
        SALESORDERITEMEVENTREGISTRANTREGISTRATION.QUANTITY,
        SALESORDERITEMEVENTREGISTRANTREGISTRATION.AMOUNT as REGISTRATIONAMOUNT,
        null as ORDERAMOUNT,
        SALESORDERITEMEVENTREGISTRATION.REGISTRANTID as REGISTRANTID,
        null as REVENUEAPPLICATIONID
    from dbo.SALESORDERITEM
    inner join dbo.SALESORDERITEMEVENTREGISTRATION on SALESORDERITEM.ID = SALESORDERITEMEVENTREGISTRATION.ID
    inner join dbo.SALESORDERITEMEVENTREGISTRANTREGISTRATION on SALESORDERITEMEVENTREGISTRATION.ID = SALESORDERITEMEVENTREGISTRANTREGISTRATION.SALESORDERITEMEVENTREGISTRATIONID
    inner join dbo.REGISTRANT on SALESORDERITEMEVENTREGISTRATION.REGISTRANTID = REGISTRANT.ID
    inner join dbo.EVENT on REGISTRANT.EVENTID = EVENT.ID
    inner join dbo.EVENTPRICE on SALESORDERITEMEVENTREGISTRANTREGISTRATION.EVENTPRICEID = EVENTPRICE.ID
    where
        SALESORDERITEM.SALESORDERID = @CONTEXTID
    order by
        DESCRIPTION;

    return 0;