USP_DATALIST_RESERVATION_NONFLATRATETICKETS

            CREATE procedure dbo.USP_DATALIST_RESERVATION_NONFLATRATETICKETS
            (
                @CONTEXTID uniqueidentifier
            )
            as

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

                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],
                    (
                        select coalesce(sum([AMOUNT]),0.0)
                        from dbo.[SALESORDERITEMITEMDISCOUNT]
                        where [SALESORDERITEMID] = [SALESORDERITEM].[ID]
                    ) as [DISCOUNTS]
                from dbo.[SALESORDERITEM]
                where [SALESORDERITEM].[SALESORDERID] = @CONTEXTID

            select
                        [ID],
                        [DESCRIPTION],
                        [STARTDATE],
                        [STARTTIME],
                        [LOCATION],
                        [QUANTITY],
                        [PRICE],
                        [DISCOUNTS],
                        [FEES],
                        [TOTAL],
                        [EVENTID],
                        [PROGRAMID],
                        [PRICETYPECODEID],
                        case PRICINGSTRUCTURECODE
                            when 2 then 1  -- when structure is override rate

                            else 0
                        end as [ISPRICEOVERRIDDEN]
                from(
                    --Tickets

                    select 
                        [SALESORDERITEM].[ID],
                        [SALESORDERITEM].[DESCRIPTION],
                        [EVENT].[STARTDATE],
                        [EVENT].[STARTTIME],
                        dbo.UFN_EVENT_GETLOCATIONNAME([EVENT].[ID]) as [LOCATION],
                        [SALESORDERITEM].[QUANTITY] as [QUANTITY],
                        [SALESORDERITEM].[PRICE],
                        [FEESDISCOUNTSANDREFUNDS].[DISCOUNTS],
                        [FEESDISCOUNTSANDREFUNDS].[FEES],
                        SALESORDERITEM.TOTAL + [FEESDISCOUNTSANDREFUNDS].[FEES] - [FEESDISCOUNTSANDREFUNDS].[DISCOUNTS] as [TOTAL],
                        [EVENT].[ID] as [EVENTID],
                        [PROGRAM].[ID] as [PROGRAMID],
                        [SALESORDERITEMTICKET].[PRICETYPECODEID],
                        0 as [ISREFUND],
                        [SALESORDERITEM].[DATEADDED],
                        SALESORDERITEM.PRICINGSTRUCTURECODE
                    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]
                    left join dbo.[EVENT] on [SALESORDERITEMTICKET].[EVENTID] = [EVENT].[ID]
                    left join dbo.[PROGRAM] on [SALESORDERITEMTICKET].[PROGRAMID] = [PROGRAM].[ID]
                    where 
                        [SALESORDERITEM].[PRICINGSTRUCTURECODE] <> 1

        ) as [TICKETS] 
        order by [DATEADDED], [TICKETS].[ID]