UFN_SALESORDER_GETCOMBINATIONITEMSFORDEFAULTITEMIZEDRECEIPT

            CREATE function dbo.UFN_SALESORDER_GETCOMBINATIONITEMSFORDEFAULTITEMIZEDRECEIPT(@ORDERID uniqueidentifier)
            returns table
            as return
            (
                select top 100 
                    ORDERITEMTYPE, 
                    SUBORDERITEMTYPE, 
                    ORDERITEMID, 
                    ITEMID, 
                    ORDERITEMDESCRIPTION, 
                    SUBORDERITEMDESCRIPTION, 
                    ORDERITEMTOTAL,
                    row_number() over (order by TICKETCOMBINATIONID, ORDERITEMTYPE, SUBORDERITEMTYPE, ITEMIDSORTFIELD, ORDERITEMSORTFIELD1, ORDERITEMSORTFIELD2) as ORDERITEMSORTFIELD1, 
                    ORDERITEMSORTFIELD2, 
                    SUBORDERITEMSORTFIELD1, 
                    SUBORDERITEMSORTFIELD2

                from (
                        --combination events

                        select 
                            1 ORDERITEMTYPE,
                            1 SUBORDERITEMTYPE,
                            SALESORDERITEM.ID ORDERITEMID,
                            coalesce(EVENT.ID, PROGRAM.ID) ITEMID,
                            [COMBINATION].[NAME] ORDERITEMDESCRIPTION,
                            coalesce([EVENT].[NAME] + ' (' + 
                                convert(nvarchar(10), [EVENT].[STARTDATE], 101) + ' - ' + 
                                dbo.UFN_HOURMINUTE_DISPLAYTIME([EVENT].[STARTTIME]) + ')', PROGRAM.NAME) SUBORDERITEMDESCRIPTION,
                            null ORDERITEMTOTAL,                            
                            0 ORDERITEMSORTFIELD1,
                            case
                                when [EVENT].ID is null then
                                    PROGRAM.NAME
                                else
                                    [EVENT].NAME 
                            end ORDERITEMSORTFIELD2,
                            case
                                when [EVENT].ID is null then
                                    PROGRAM.NAME
                                else
                                    [EVENT].NAME 
                            end SUBORDERITEMSORTFIELD1,
                            case
                                when [EVENT].ID is null then
                                    PROGRAM.NAME
                                else
                                    [EVENT].NAME 
                            end SUBORDERITEMSORTFIELD2,
                            [SALESORDERITEMTICKETCOMBINATION].TICKETCOMBINATIONID,
                            coalesce(EVENT.ID, PROGRAM.ID) ITEMIDSORTFIELD

                        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] = @ORDERID 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

                        --combination tickets

                        select
                            1 ORDERITEMTYPE,
                            2 SUBORDERITEMTYPE,
                            SALESORDERITEM.ID ORDERITEMID,
                            PRICETYPECODE.ID ITEMID,
                            [COMBINATION].[NAME] ORDERITEMDESCRIPTION,
                            cast(cast(SALESORDERITEM.QUANTITY as integer) as nvarchar) + ' ' + PRICETYPECODE.DESCRIPTION + ' @ ' + cast(dbo.UFN_SALESORDER_GETTICKETCOMBINATIONPRICE([SALESORDERITEM].ID) as nvarchar) SUBORDERITEMDESCRIPTION,
                            [SALESORDERITEM].[QUANTITY] * dbo.UFN_SALESORDER_GETTICKETCOMBINATIONPRICE([SALESORDERITEM].ID) ORDERITEMTOTAL,
                            1 ORDERITEMSORTFIELD1,
                            case
                                when [EVENT].ID is null then
                                    PROGRAM.NAME
                                else
                                    [EVENT].NAME 
                            end ORDERITEMSORTFIELD2,
                            case
                                when [EVENT].ID is null then
                                    PROGRAM.NAME
                                else
                                    [EVENT].NAME 
                            end SUBORDERITEMSORTFIELD1,
                            case
                                when [EVENT].ID is null then
                                    PROGRAM.NAME
                                else
                                    [EVENT].NAME 
                            end SUBORDERITEMSORTFIELD2,
                            [SALESORDERITEMTICKETCOMBINATION].TICKETCOMBINATIONID,
                            PRICETYPECODE.ID ITEMIDSORTFIELD

                        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] 
                            inner join dbo.[PRICETYPECODE] on [SALESORDERITEMTICKET].[PRICETYPECODEID] = [PRICETYPECODE].[ID]
                            left outer join dbo.[EVENT] on SALESORDERITEMTICKET.EVENTID = [EVENT].ID
                            left outer join dbo.PROGRAM on SALESORDERITEMTICKET.PROGRAMID = PROGRAM.ID                                    
                        where [SALESORDERITEM].[SALESORDERID] = @ORDERID and 
                            [SALESORDERITEM].[QUANTITY] > 0 and
                            [SALESORDERITEMTICKETCOMBINATION].PROGRAMGROUPID  in (
                                select top 1 id from dbo.PROGRAMGROUP  
                                where PROGRAMGROUP.COMBINATIONID = [SALESORDERITEMTICKETCOMBINATION].COMBINATIONID )

                        -- items fees

                        union all

    select
        1 ORDERITEMTYPE,
        2 SUBORDERITEMTYPE,
        SALESORDERITEM.ID ORDERITEMID,
        FEE.ID ITEMID,
        [COMBINATION].[NAME] as ORDERITEMDESCRIPTION,
        '     ' + FEE.NAME SUBORDERITEMDESCRIPTION,
        FEEPARENT.TOTAL ORDERITEMTOTAL,                            
        case when [EVENT].ID is null then
            dbo.UFN_DATE_GETEARLIESTTIME(getdate())
        else
            [EVENT].[STARTDATETIME] 
        end as ORDERITEMSORTFIELD1,
        case when [EVENT].ID is null then
            PROGRAM.NAME
        else
            [EVENT].NAME 
        end as ORDERITEMSORTFIELD2,
        PRICETYPECODE.DESCRIPTION SUBORDERITEMSORTFIELD1,
        FEE.NAME SUBORDERITEMSORTFIELD2,
        SALESORDERITEMTICKETCOMBINATION.TICKETCOMBINATIONID,
        PRICETYPECODE.ID ITEMIDSORTFIELD

    from dbo.SALESORDER
    inner join dbo.SALESORDERITEM on
        SALESORDER.ID = SALESORDERITEM.SALESORDERID
    inner join dbo.SALESORDERITEMTICKET on
        SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
    inner join dbo.PRICETYPECODE on
        SALESORDERITEMTICKET.PRICETYPECODEID = PRICETYPECODE.ID
    inner join dbo.SALESORDERITEMFEE on
        SALESORDERITEM.ID = SALESORDERITEMFEE.SALESORDERITEMID
    inner join dbo.FEE on
        SALESORDERITEMFEE.FEEID = FEE.ID
    inner join SALESORDERITEM FEEPARENT on
        SALESORDERITEMFEE.ID = FEEPARENT.ID
    left outer join dbo.[EVENT] on
        SALESORDERITEMTICKET.EVENTID = [EVENT].ID
    left outer join dbo.PROGRAM on
        SALESORDERITEMTICKET.PROGRAMID = PROGRAM.ID
    inner join dbo.SALESORDERITEMTICKETCOMBINATION on
        SALESORDERITEMTICKETCOMBINATION.ID = SALESORDERITEM.ID
    inner join dbo.[COMBINATION] on 
        [SALESORDERITEMTICKETCOMBINATION].[COMBINATIONID] = [COMBINATION].[ID]
    where SALESORDER.ID = @ORDERID

                ) as COMBINATIONITEMS             
                order by TICKETCOMBINATIONID, ORDERITEMTYPE, ORDERITEMSORTFIELD1, ORDERITEMSORTFIELD2
            )