USP_DATALIST_ORDERDETAILS_FORGROUPEDVIEW

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

    declare @ORDERSUBTOTAL money;
    declare @ORDERTOTAL money;
    declare @TOTALTAXES money;
    declare @AMOUNTTENDERED money;
    declare @CHANGEDUE money;

    select
        @ORDERSUBTOTAL = SUBTOTAL,
        @ORDERTOTAL = TOTAL,
        @TOTALTAXES = TAXES,
        @AMOUNTTENDERED = AMOUNTTENDERED,
        @CHANGEDUE = CHANGEDUE
    from
        dbo.UFN_SALESORDER_TOTALS(@CONTEXTID)

    declare @FEESDISCOUNTSANDREFUNDS table (
        SALESORDERITEMID uniqueidentifier,
        FEES money,
        DISCOUNTS money,
        QUANTITYREFUNDED int
    )

    insert into @FEESDISCOUNTSANDREFUNDS
    select 
        [SALESORDERITEM].[ID],
        (
            select coalesce(sum([FEESALESORDERITEM].[TOTAL]),0.0)
            from dbo.[SALESORDERITEM] as [FEESALESORDERITEM]
            inner join dbo.[SALESORDERITEMFEE] on [FEESALESORDERITEM].[ID] = [SALESORDERITEMFEE].[ID]
            where [SALESORDERITEMFEE].[SALESORDERITEMID] = [SALESORDERITEM].[ID]
        ) as [FEES],
        (
        case
          when [SALESORDERITEM].[TYPECODE] = 1 then
            (select coalesce(sum([AMOUNT]), 0.0)
            from dbo.[SALESORDERITEMMEMBERSHIPITEMPROMOTION]
            where [SALESORDERITEMMEMBERSHIPITEMPROMOTION].[SALESORDERITEMID] = [SALESORDERITEM].[ID])
          else
                (select coalesce(sum([AMOUNT]),0.0)
                from dbo.[SALESORDERITEMITEMDISCOUNT]
                where [SALESORDERITEMID] = [SALESORDERITEM].[ID])
          end
        ) as [DISCOUNTS],
        (
            select coalesce(sum(LI.QUANTITY), 0)
            from dbo.FINANCIALTRANSACTIONLINEITEM as LI
            inner join dbo.CREDITITEM_EXT as EXT on EXT.ID = LI.ID
            where EXT.SALESORDERITEMID = SALESORDERITEM.ID
        ) as [REFUNDEDQUANTITY]
    from dbo.[SALESORDERITEM]
    where [SALESORDERITEM].[SALESORDERID] = @CONTEXTID

    declare @FEESOFCOMBINATION table (
        TICKETCOMBINATIONID uniqueidentifier,
        PRICETYPECODEID uniqueidentifier,
        FEES money
    )

    insert into @FEESOFCOMBINATION 
    select
        TICKETCOMBINATIONID, [PRICETYPECODEID], SUM(FEES) 
    from (
        select
            [SALESORDERITEMTICKETCOMBINATION].TICKETCOMBINATIONID,
            [SALESORDERITEMTICKET].[PRICETYPECODEID],
            (
                select coalesce(sum([FEESALESORDERITEM].[TOTAL]),0.0)
                from dbo.[SALESORDERITEM] as [FEESALESORDERITEM]
                inner join dbo.[SALESORDERITEMFEE] on [FEESALESORDERITEM].[ID] = [SALESORDERITEMFEE].[ID]
                inner join dbo.[SALESORDERITEMTICKET] as [FEESALESORDERITEMTICKET] on [SALESORDERITEMFEE].[SALESORDERITEMID] = [FEESALESORDERITEMTICKET].[ID]
                where [SALESORDERITEMFEE].[SALESORDERITEMID] = [SALESORDERITEM].[ID] and [SALESORDERITEMTICKET].[PRICETYPECODEID] = [FEESALESORDERITEMTICKET].[PRICETYPECODEID]
            ) as [FEES]
        from dbo.SALESORDERITEM
        inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
        inner join dbo.[SALESORDERITEMTICKETCOMBINATION] on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETCOMBINATION].[ID]
        where SALESORDERITEM.SALESORDERID = @CONTEXTID
    ) as ComboFee
    group by TICKETCOMBINATIONID , [PRICETYPECODEID]

    select
        @CONTEXTID as ORDERID, ITEMID, ID, DESCRIPTION, QUANTITY, TOTAL, ITEMTYPE, ITEMSUBTYPE, GROUPBY, GROUPCAPTION, ISBOLD, ISRED
    from (
        -- Daily admission tickets

        select
            [PROGRAM].[ID] as [ITEMID],
            [SALESORDERITEM].[ID],
            [PRICETYPECODE].[DESCRIPTION] as [DESCRIPTION],
            [SALESORDERITEM].[QUANTITY] as [QUANTITY],
            [SALESORDERITEM].[TOTAL] + [FEESDISCOUNTSANDREFUNDS].[FEES] - [FEESDISCOUNTSANDREFUNDS].[DISCOUNTS] as [TOTAL],
            0 as [ITEMTYPE],
            0 as [ITEMSUBTYPE],
            '00' + [PROGRAM].[NAME] as [GROUPBY],
            [PROGRAM].[NAME] as [GROUPCAPTION],
            0 as ISBOLD,
            0 as ISRED
        from dbo.[SALESORDERITEM]
        inner join @FEESDISCOUNTSANDREFUNDS as [FEESDISCOUNTSANDREFUNDS] on [SALESORDERITEM].[ID] = [FEESDISCOUNTSANDREFUNDS].[SALESORDERITEMID]
        inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
        inner join dbo.[PRICETYPECODE] on [SALESORDERITEMTICKET].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
        inner join dbo.[PROGRAM] on [SALESORDERITEMTICKET].[PROGRAMID] = [PROGRAM].[ID]
        where
            [SALESORDERITEM].[SALESORDERID] = @CONTEXTID and 
            [SALESORDERITEM].[QUANTITY] > 0 and 
            [PROGRAM].[ISDAILYADMISSION] = 1
            -- check if tickets are part of combo

            and SALESORDERITEM.ID not in (
                select ID from dbo.SALESORDERITEMTICKETCOMBINATION
            )

        -- Combo tickets

        union all
        select 
            [SALESORDERITEMTICKETCOMBINATION].[TICKETCOMBINATIONID] as [ITEMID],
            SALESORDERITEM.ID as [ID],
            case
                when SALESORDERITEMTICKET.EVENTID is not null then
                    [EVENT].[NAME] + ' (' + convert(nvarchar(10), [EVENT].[STARTDATE], 101) + ' - ' +  dbo.UFN_HOURMINUTE_DISPLAYTIME([EVENT].[STARTTIME]) + ')'
                else
                    PROGRAM.NAME
            end as [DESCRIPTION],
            null as [QUANTITY],
            null as [TOTAL],
            1 as [ITEMTYPE],
            0 as [ITEMSUBTYPE],
            '01' + convert(nvarchar(100), [SALESORDERITEMTICKETCOMBINATION].[TICKETCOMBINATIONID]) as [GROUPBY],
            [COMBINATION].[NAME] + ' ' + dbo.UFN_SALESORDERITEMTICKETCOMBINATION_GETCOMBINATIONNUMBER([TICKETCOMBINATIONID]) as [GROUPCAPTION],
            0 as ISBOLD,
            0 as ISRED
        from dbo.SALESORDERITEM
        inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
        inner join dbo.[SALESORDERITEMTICKETCOMBINATION] on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETCOMBINATION].[ID]
        inner join dbo.[COMBINATION] on [SALESORDERITEMTICKETCOMBINATION].[COMBINATIONID] = [COMBINATION].[ID]
        inner join dbo.[COMBINATIONPRICETYPE] on [COMBINATIONPRICETYPE].PRICETYPECODEID  = [SALESORDERITEMTICKET].[PRICETYPECODEID]
                                                and [COMBINATION].[ID] = [COMBINATIONPRICETYPE].[COMBINATIONID] 
        left join dbo.EVENT on EVENT.ID = SALESORDERITEMTICKET.EVENTID
        left join dbo.PROGRAM on PROGRAM.ID = SALESORDERITEMTICKET.PROGRAMID
        where
            [SALESORDERITEM].[SALESORDERID] = @CONTEXTID and 
            SALESORDERITEMTICKET.PRICETYPECODEID in (
                select top 1 COMBINATIONPRICETYPE.PRICETYPECODEID
                from dbo.COMBINATIONPRICETYPE
                inner join dbo.SALESORDERITEMTICKETCOMBINATION SOITC on COMBINATIONPRICETYPE.COMBINATIONID = SOITC.COMBINATIONID
                inner join dbo.SALESORDERITEMTICKET on COMBINATIONPRICETYPE.PRICETYPECODEID = SALESORDERITEMTICKET.PRICETYPECODEID 
                where SOITC.ID = SALESORDERITEMTICKET.ID and SOITC.TICKETCOMBINATIONID = SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID 
            )

        union all
        select
            [SALESORDERITEMTICKETCOMBINATION].[TICKETCOMBINATIONID] as [ITEMID],
            SALESORDERITEM.ID as [ID],
            [PRICETYPECODE].[DESCRIPTION] as [DESCRIPTION],
            [SALESORDERITEM].[QUANTITY] as [QUANTITY],
            [SALESORDERITEM].[QUANTITY] * dbo.UFN_SALESORDER_GETTICKETCOMBINATIONPRICE([SALESORDERITEM].ID) + [FEESOFCOMBINATION].[FEES] as [TOTAL],
            1 as [ITEMTYPE],
            1 as [ITEMSUBTYPE],
            '01' + convert(nvarchar(100), [SALESORDERITEMTICKETCOMBINATION].[TICKETCOMBINATIONID]) as [GROUPBY],
            [COMBINATION].[NAME] + ' ' + dbo.UFN_SALESORDERITEMTICKETCOMBINATION_GETCOMBINATIONNUMBER(SALESORDERITEMTICKETCOMBINATION.[TICKETCOMBINATIONID]) as [GROUPCAPTION],
            0 as ISBOLD,
            0 as ISRED
        from dbo.[SALESORDERITEM]
        inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
        inner join dbo.[SALESORDERITEMTICKETCOMBINATION] on [SALESORDERITEMTICKET].[ID] = [SALESORDERITEMTICKETCOMBINATION].[ID]
        inner join @FEESOFCOMBINATION as [FEESOFCOMBINATION] on [SALESORDERITEMTICKETCOMBINATION].[TICKETCOMBINATIONID] = [FEESOFCOMBINATION].[TICKETCOMBINATIONID]
                                                            and [SALESORDERITEMTICKET].[PRICETYPECODEID] = [FEESOFCOMBINATION].[PRICETYPECODEID]
        inner join dbo.[COMBINATION] on [SALESORDERITEMTICKETCOMBINATION].[COMBINATIONID] = [COMBINATION].[ID]
        inner join dbo.[COMBINATIONPRICETYPE] on [COMBINATIONPRICETYPE].PRICETYPECODEID  = [SALESORDERITEMTICKET].[PRICETYPECODEID]
                                                and [COMBINATION].[ID] = [COMBINATIONPRICETYPE].[COMBINATIONID] 
        inner join dbo.[PRICETYPECODE] on [SALESORDERITEMTICKET].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
        where
            [SALESORDERITEM].[SALESORDERID] = @CONTEXTID and 
            [SALESORDERITEM].[QUANTITY] > 0 and
            [SALESORDERITEMTICKETCOMBINATION].PROGRAMGROUPID in (
                select ID
                from dbo.PROGRAMGROUP  
                where PROGRAMGROUP.COMBINATIONID = [SALESORDERITEMTICKETCOMBINATION].COMBINATIONID
            )

        -- Scheduled program tickets

        union all
        select
            [EVENT].[ID] as [ITEMID],
            [SALESORDERITEM].[ID],
            [PRICETYPECODE].[DESCRIPTION] as [DESCRIPTION],
            [SALESORDERITEM].[QUANTITY] as [QUANTITY],
            [SALESORDERITEM].[TOTAL] + [FEESDISCOUNTSANDREFUNDS].[FEES] - [FEESDISCOUNTSANDREFUNDS].[DISCOUNTS] as [TOTAL],
            2 as [ITEMTYPE],
            0 AS [ITEMSUBTYPE],
            '02' + [EVENT].[NAME] + ' (' + convert(nvarchar(10), [EVENT].[STARTDATE], 101) + ' - ' + dbo.UFN_HOURMINUTE_DISPLAYTIME([EVENT].[STARTTIME]) + ')' as [GROUPBY],
            [EVENT].[NAME] + ' (' + convert(nvarchar(10), [EVENT].[STARTDATE], 101) + ' - ' + dbo.UFN_HOURMINUTE_DISPLAYTIME([EVENT].[STARTTIME]) + ')' as [GROUPCAPTION],
            0 as ISBOLD,
            0 as ISRED
        from dbo.[SALESORDERITEM]
        inner join @FEESDISCOUNTSANDREFUNDS as [FEESDISCOUNTSANDREFUNDS] on [SALESORDERITEM].[ID] = [FEESDISCOUNTSANDREFUNDS].[SALESORDERITEMID]
        inner join dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
        inner join dbo.[PRICETYPECODE] on [SALESORDERITEMTICKET].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
        inner join dbo.[EVENT] on [SALESORDERITEMTICKET].[EVENTID] = [EVENT].[ID]
        where
            [SALESORDERITEM].[SALESORDERID] = @CONTEXTID and 
            [SALESORDERITEM].[QUANTITY] > 0
            -- check if tickets are part of combo

            and SALESORDERITEM.ID not in (select ID from dbo.SALESORDERITEMTICKETCOMBINATION)
            and SALESORDERITEM.ID not in (select SALESORDERITEMTICKETID from dbo.SALESORDERITEMTICKETREGISTRANT)

        -- Memberships

        union all
        select
            [SALESORDERITEMMEMBERSHIP].[MEMBERSHIPID] as [ITEMID],
            [SALESORDERITEM].[ID] as [ID],
            right([SALESORDERITEM].[DESCRIPTION], len([SALESORDERITEM].[DESCRIPTION]) - charindex('-', [SALESORDERITEM].[DESCRIPTION]) - 1) as [DESCRIPTION],
            null as [QUANTITY],
            [SALESORDERITEM].[TOTAL] - [FEESDISCOUNTSANDREFUNDS].[DISCOUNTS] as TOTAL,
            3 as [ITEMTYPE],
            0 AS [ITEMSUBTYPE],
            '03' + [MEMBERSHIPPROGRAM].[NAME],
            [MEMBERSHIPPROGRAM].[NAME],
            0 as ISBOLD,
            0 as ISRED
        from dbo.[SALESORDER]
        inner join dbo.[SALESORDERITEM] on [SALESORDER].[ID] = [SALESORDERITEM].[SALESORDERID]
        inner join dbo.[SALESORDERITEMMEMBERSHIP] on [SALESORDERITEM].[ID] = [SALESORDERITEMMEMBERSHIP].[ID]    
        inner join dbo.[MEMBERSHIPPROGRAM] on [MEMBERSHIPPROGRAM].[ID] = [SALESORDERITEMMEMBERSHIP].[MEMBERSHIPPROGRAMID]
        inner join @FEESDISCOUNTSANDREFUNDS as [FEESDISCOUNTSANDREFUNDS] on [SALESORDERITEM].[ID] = [FEESDISCOUNTSANDREFUNDS].[SALESORDERITEMID]
        where [SALESORDER].[ID] = @CONTEXTID

        -- Event registrant

        union all
        select 
            [SALESORDERITEM].[ID] as [ITEMID],
            [SALESORDERITEMEVENTREGISTRATION].[REGISTRANTID] as [ID],
            dbo.UFN_CONSTITUENT_BUILDNAME([REGISTRANT].[CONSTITUENTID]) as [DESCRIPTION],
            null,
            coalesce([SALESORDERITEM].[TOTAL],0) as [TOTAL],
            4 as [ITEMTYPE],
            0 AS [ITEMSUBTYPE],
            '04' + [EVENT].[NAME] + ' (' + convert(nvarchar(10), [EVENT].[STARTDATE], 101) + ' - ' + dbo.UFN_HOURMINUTE_DISPLAYTIME([EVENT].[STARTTIME]) + ')',
            [EVENT].[NAME] + ' (' + convert(nvarchar(10), [EVENT].[STARTDATE], 101) + ' - ' + dbo.UFN_HOURMINUTE_DISPLAYTIME([EVENT].[STARTTIME]) + ')',
            0 as ISBOLD,
            0 as ISRED
        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]
        where [SALESORDERITEM].[SALESORDERID] = @CONTEXTID

        -- Donations

        union all
        select
            [SALESORDERITEM].[ID] as [ITEMID],
            [SALESORDERITEM].[ID],
            [SALESORDERITEM].[DESCRIPTION],
            null,
            [SALESORDERITEM].[TOTAL],
            5 as [ITEMTYPE],
            0 AS [ITEMSUBTYPE],
            '05',
            'Donations',
            0 as ISBOLD,
            0 as ISRED
        from dbo.[SALESORDERITEM]
        inner join dbo.[SALESORDERITEMDONATION] on [SALESORDERITEM].[ID] = [SALESORDERITEMDONATION].[ID]
        where [SALESORDERITEM].[SALESORDERID] = @CONTEXTID

        -- Delivery method and fees

        union all
        select
            [SALESORDER].[DELIVERYMETHODID] as [ITEMID],
            [SALESORDERITEM].[ID],
            case
                when [SALESORDERITEM].[ID] is null then dbo.UFN_DELIVERYMETHOD_GETNAME([SALESORDER].[DELIVERYMETHODID])
                else [SALESORDERITEM].[DESCRIPTION] 
            end as [DESCRIPTION],
            null as [QUANTITY],
            [SALESORDERITEM].[TOTAL] as [TOTAL],
            6 as [ITEMTYPE],
            0 AS [ITEMSUBTYPE],
            '06',
            case
                when [SALESORDERITEM].[ID] is null then 'Delivery method'
                else 'Delivery method - ' + dbo.UFN_DELIVERYMETHOD_GETNAME([SALESORDER].[DELIVERYMETHODID])
            end,
            0 as ISBOLD,
            0 as ISRED
        from dbo.[SALESORDER]
        left join (
            dbo.[SALESORDERITEM] 
            inner join dbo.[SALESORDERITEMFEE]
                on [SALESORDERITEM].[ID] = [SALESORDERITEMFEE].[ID]
                and [SALESORDERITEMFEE].[APPLIESTOCODE] = 0
                and [SALESORDERITEMFEE].[TYPECODE] = 0
        ) on [SALESORDER].[ID] = [SALESORDERITEM].[SALESORDERID] and [SALESORDERITEM].[TYPECODE] = 3
        where [SALESORDER].[ID] = @CONTEXTID 

        -- Fees

        union all
        select
            [SALESORDERITEM].[ID] as [ITEMID],
            [SALESORDERITEM].[ID],
            [SALESORDERITEM].[DESCRIPTION] as [DESCRIPTION],
            null as [QUANTITY],
            [SALESORDERITEM].[TOTAL] as [TOTAL],
            7 as [ITEMTYPE],
            0 AS [ITEMSUBTYPE],
            '07',
            'Other adjustments',
            0 as ISBOLD,
            0 as ISRED
        from dbo.[SALESORDERITEM]
        inner join dbo.[SALESORDERITEMFEE] on [SALESORDERITEM].[ID] = [SALESORDERITEMFEE].[ID]
        where
            [SALESORDERITEM].[SALESORDERID] = @CONTEXTID and
            [SALESORDERITEM].[TYPECODE] = 3 and
            [SALESORDERITEMFEE].[APPLIESTOCODE] = 0 and
            [SALESORDERITEMFEE].[TYPECODE] <> 0

        -- Discounts

        union all
        select
            [SALESORDERITEM].[ID] as [ITEMID],
            [SALESORDERITEM].[ID],
            [SALESORDERITEM].[DESCRIPTION],
            null as [QUANTITY],
            -[SALESORDERITEM].[TOTAL] as [TOTAL],
            8 as [ITEMTYPE],
            case
                when [SALESORDERITEMORDERDISCOUNT].[DISCOUNTID] is null then
                    1
                else
                    0 
            end
            [ITEMSUBTYPE],
            '07',
            'Other adjustments',
            0 as ISBOLD,
            0 as ISRED
        from dbo.[SALESORDERITEM]
        inner join dbo.[SALESORDERITEMORDERDISCOUNT] on SALESORDERITEM.ID = SALESORDERITEMORDERDISCOUNT.ID
        where [SALESORDERITEM].[SALESORDERID] = @CONTEXTID and
            [SALESORDERITEM].[TYPECODE] = 5

        -- Subtotal

        union all
        select 
            null as [ITEMID],
            @CONTEXTID as [ID],
            'Subtotal' as [DESCRIPTION],
            null as [QUANTITY],
            @ORDERSUBTOTAL as [TOTAL],
            9 as [ITEMTYPE],
            1 AS [ITEMSUBTYPE],
            '09',
            'Totals',
            1 as ISBOLD,
            0 as ISRED
        where @ORDERSUBTOTAL > 0

        -- Taxes

        union all
        select
            [SALESORDERITEM].[ID] as [ITEMID],
            [SALESORDERITEM].[ID],
            [SALESORDERITEM].[DESCRIPTION],
            null as [QUANTITY],
            [SALESORDERITEM].[TOTAL],
            10 as [ITEMTYPE],
            0 AS [ITEMSUBTYPE],
            '09',
            'Totals',
            0 as ISBOLD,
            0 as ISRED
        from dbo.[SALESORDERITEM]
        where
            [SALESORDERITEM].[SALESORDERID] = @CONTEXTID and
            [SALESORDERITEM].[TYPECODE] = 4 and 
            [SALESORDERITEM].[TOTAL] > 0

        -- Total

        union all
        select 
            null as [ITEMID],
            @CONTEXTID as [ID],
            'Total' as [DESCRIPTION],
            null as [QUANTITY],
            @ORDERTOTAL as [TOTAL],
            11 as [ITEMTYPE],
            1 AS [ITEMSUBTYPE],
            '09',
            'Totals',
            1 as ISBOLD,
            0 as ISRED
        where
            @ORDERTOTAL > 0
            or (
                @ORDERTOTAL = 0 and
                exists (select ID from dbo.SALESORDERITEM where SALESORDERID = @CONTEXTID)
            )

        -- Payments

        union all
        select 
            SALESORDERPAYMENT.ID as [ITEMID],
            SALESORDERPAYMENT.ID as [ID],
            case REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE
                when 0 then  -- Cash

                    REVENUEPAYMENTMETHOD.PAYMENTMETHOD
                when 1 then (  -- Check

                    select
                        'Check' + 
                            case isnull(CHECKNUMBER, '')
                                when '' then ''
                                else ': #' + CHECKNUMBER
                            end 
                    from dbo.CHECKPAYMENTMETHODDETAIL
                    where ID = REVENUEPAYMENTMETHOD.ID
                )
                when 2 then (  -- Credit card

                    select dbo.UFN_CREDITTYPECODE_GETDESCRIPTION(CREDITTYPECODEID) + ': ' + CREDITCARDPAYMENTMETHODDETAIL.CREDITCARDPARTIALNUMBER + ' - ' + CREDITCARDPAYMENTMETHODDETAIL.AUTHORIZATIONCODE
                    from dbo.CREDITCARDPAYMENTMETHODDETAIL 
                    where ID = REVENUEPAYMENTMETHOD.ID)
                when 10 then  -- Other

                    REVENUEPAYMENTMETHOD.PAYMENTMETHOD + ': ' +  (
                        select dbo.UFN_OTHERPAYMENTMETHODCODE_GETDESCRIPTION(OTHERPAYMENTMETHODCODEID)
                        from dbo.OTHERPAYMENTMETHODDETAIL
                        where ID = REVENUEPAYMENTMETHOD.ID
                    )
            end as [DESCRIPTION],
            null as [QUANTITY],
            SALESORDERPAYMENT.AMOUNTTENDERED as [TOTAL],
            14 as [ITEMTYPE],
            0 AS [ITEMSUBTYPE],
            '10',
            'Paid',
            0 as ISBOLD,
            0 as ISRED
        from dbo.SALESORDERPAYMENT
        inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = SALESORDERPAYMENT.PAYMENTID
        left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = SALESORDERPAYMENT.PAYMENTID
        where SALESORDERPAYMENT.SALESORDERID = @CONTEXTID

        -- Payment total label

        union all
        select 
            null as [ITEMID],
            @CONTEXTID as [ID],
            'Total Paid' as [DESCRIPTION],
            null as [QUANTITY],
            @AMOUNTTENDERED as [TOTAL],
            15 as [ITEMTYPE],
            1 AS [ITEMSUBTYPE],
            '10',
            'Paid',
            1 as ISBOLD,
            0 as ISRED
        where @AMOUNTTENDERED > 0

        -- Change due label

        union all
        select 
            null as [ITEMID],
            @CONTEXTID as [ID],
            'Change Due' as [DESCRIPTION],
            null as [QUANTITY],
            @CHANGEDUE as [TOTAL],
            16 as [ITEMTYPE],
            0 AS [ITEMSUBTYPE],
            '10',
            'Paid',
            0 as ISBOLD,
            0 as ISRED
        where @CHANGEDUE > 0

        -- Preregistered Program Event Tickets

        union all
        select
            [EVENT].[ID] as [ITEMID],
            [SALESORDERITEM].[ID],
            [PRICETYPECODE].[DESCRIPTION] as [DESCRIPTION],
            [SALESORDERITEM].[QUANTITY] as [QUANTITY],
            [SALESORDERITEM].[TOTAL] + [FEESDISCOUNTSANDREFUNDS].[FEES] - [FEESDISCOUNTSANDREFUNDS].[DISCOUNTS] as [TOTAL],
            17 as [ITEMTYPE],
            0 AS [ITEMSUBTYPE],
            '02' + [EVENT].[NAME] + ' (' + convert(nvarchar(10), [EVENT].[STARTDATE], 101) + ' - ' + dbo.UFN_HOURMINUTE_DISPLAYTIME([EVENT].[STARTTIME]) + ')' as [GROUPBY],
            [EVENT].[NAME] + ' (' + convert(nvarchar(10), [EVENT].[STARTDATE], 101) + ' - ' + dbo.UFN_HOURMINUTE_DISPLAYTIME([EVENT].[STARTTIME]) + ')' as [GROUPCAPTION],
            0 as ISBOLD,
            0 as ISRED
        from
            dbo.[SALESORDERITEM]
        inner join
            @FEESDISCOUNTSANDREFUNDS as [FEESDISCOUNTSANDREFUNDS] on [SALESORDERITEM].[ID] = [FEESDISCOUNTSANDREFUNDS].[SALESORDERITEMID]
        inner join
            dbo.[SALESORDERITEMTICKET] on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
        inner join
            dbo.[PRICETYPECODE] on [SALESORDERITEMTICKET].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
        inner join
            dbo.[EVENT] on [SALESORDERITEMTICKET].[EVENTID] = [EVENT].[ID]
        where
            [SALESORDERITEM].[SALESORDERID] = @CONTEXTID
            and [SALESORDERITEM].[QUANTITY] > 0
            and SALESORDERITEM.ID in (select SALESORDERITEMTICKETID from dbo.SALESORDERITEMTICKETREGISTRANT)
    ) as SALESORDERITEMS

    order by ITEMTYPE asc

    return 0;