UFN_SALESORDER_PROGRAMTICKETSELECT_DATETIMEOFFSET

        CREATE function dbo.UFN_SALESORDER_PROGRAMTICKETSELECT_DATETIMEOFFSET
            (
            @SALESMETHODID uniqueidentifier = null,
            @CONSTITUENTID uniqueidentifier = null,
            @DATESELECTTYPE tinyint = null,
            @STARTDATETIMEWITHOFFSET datetimeoffset = null,
            @ENDDATETIMEWITHOFFSET datetimeoffset = null,
            @NAME nvarchar(512) = null,
            @SHOWPAST bit = null,
            @CURRENTDATETIMEWITHOFFSET datetimeoffset = null
            )
        returns table
        as return

            --Combo

            select 
              [COMBINATION].[ID], 
              null [PROGRAMID],
              [COMBINATION].[NAME] as [NAME],
              null as [AVAILABILITY],
              null as [STARTDATE],
              null as [STARTTIME], 
              null as [ENDTIME],
              null as PROGRAMCATEGORY,                    
              0 as [ISDAILYADMISSION],
              1 as [ISCOMBINATION],
              0 as [EVENTSTATUSCODE],
              null as [EVENTSTARTDATETIMEWITHOFFSET]
            from dbo.[COMBINATION]
            where
              -- Combination level check

              [COMBINATION].[ISACTIVE] = 1 and
              dbo.UFN_COMBINATION_AVAILABLEFORORDER(COMBINATION.ID, @SALESMETHODID) = 1 and
              -- Combination eligibility 

              dbo.UFN_COMBINATION_ELIGIBLEFORORDER(COMBINATION.ID, @CONSTITUENTID) = 1 and
              -- Combination has valid price options

              exists (
                select [COMBINATIONPRICETYPE].[ID]
                from dbo.[COMBINATIONPRICETYPE]
                cross apply (
                    select 1 as [HAS]
                    where not exists (
                        select 1
                        from dbo.[SALESMETHODEXCLUDEDPRICETYPE]
                        where 
                            [SALESMETHODID] = @SALESMETHODID and
                            [PRICETYPECODEID] = [COMBINATIONPRICETYPE].PRICETYPECODEID
                    )
                ) as [VALIDPRICETYPES]
                where [COMBINATIONPRICETYPE].[COMBINATIONID] = [COMBINATION].[ID]
              ) and
              -- Combination Name filter

              (
                @NAME is null or
                [COMBINATION].[NAME] like @NAME escape '\'
              ) and
              -- Program group level check

              -- Each program group has at least one available daily admission program or event

              dbo.UFN_COMBINATION_PROGRAMSAVAILABLE_DATETIMEOFFSET(COMBINATION.ID, @SALESMETHODID, @DATESELECTTYPE, @STARTDATETIMEWITHOFFSET, @ENDDATETIMEWITHOFFSET, @SHOWPAST) = 1

            union all

            --Daily Admission

            select 
                [PROGRAM].[ID], 
                null as [PROGRAMID],
                [PROGRAM].[NAME] as [NAME],
                null as [AVAILABILITY],
                null as [STARTDATE],
                null as [STARTTIME], 
                null as [ENDTIME],
                [PROGRAMCATEGORYCODE].[DESCRIPTION] as PROGRAMCATEGORY,                    
                [ISDAILYADMISSION],
                0 as [ISCOMBINATION],
                0 as [EVENTSTATUSCODE],
                null as [EVENTSTARTDATETIMEWITHOFFSET]
            from dbo.[PROGRAM]
            inner join dbo.[PROGRAMSALESMETHOD] 
                on [PROGRAM].[ID] = [PROGRAMSALESMETHOD].[PROGRAMID]
            left join dbo.[PROGRAMCATEGORYCODE] 
                on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
            where 
                (
                    @NAME is null or
                    [PROGRAM].[NAME] like @NAME escape '\'
                ) and
                [PROGRAM].[ISACTIVE] = 1 and
                [PROGRAM].[ISDAILYADMISSION] = 1 and
                [PROGRAMSALESMETHOD].[SALESMETHODID] = @SALESMETHODID
                and exists ( --Has valid price options

                    select [PROGRAMPRICE].[ID]
                    from dbo.[PROGRAMPRICE]
                    cross apply (
                        select 1 as [HAS]
                        where not exists (
                            select 1
                            from dbo.[SALESMETHODEXCLUDEDPRICETYPE]
                            where 
                                [SALESMETHODID] = @SALESMETHODID and
                                [PRICETYPECODEID] = [PROGRAMPRICE].PRICETYPECODEID
                        )
                    ) as [VALIDPRICETYPES]
                    where [PROGRAMID] = [PROGRAM].[ID]
                ) and
                (
                    @DATESELECTTYPE = 0 or
                    @STARTDATETIMEWITHOFFSET <= @ENDDATETIMEWITHOFFSET
                )

            union all 
            --Scheduled programs

            select 
                [EVENT].[ID], 
                [EVENT].[PROGRAMID] as [PROGRAMID],
                [EVENT].[NAME],
                AVAILABLE.QUANTITY as [AVAILABILITY],
                [EVENT].[STARTDATE],
                [EVENT].[STARTTIME], 
                [EVENT].[ENDTIME],
                [PROGRAMCATEGORYCODE].[DESCRIPTION] as PROGRAMCATEGORY,                    
                [ISDAILYADMISSION],
                0 as [ISCOMBINATION],
                [AVAILABLE].[EVENTSTATUSCODE],
                [EVENT].[STARTDATETIMEWITHOFFSET] as [EVENTSTARTDATETIMEWITHOFFSET]
            from dbo.[EVENT]
            inner join dbo.[PROGRAM] 
                on [EVENT].[PROGRAMID] = [PROGRAM].[ID]
            left join dbo.[PROGRAMCATEGORYCODE] on [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
            inner join dbo.UFN_EVENT_GETAVAILABILITYWITHEVENTS_BYDATE(
                @DATESELECTTYPE,
                @STARTDATETIMEWITHOFFSET,
                @ENDDATETIMEWITHOFFSET,
                @CURRENTDATETIMEWITHOFFSET,
                @SALESMETHODID,@SHOWPAST,
                cast(@STARTDATETIMEWITHOFFSET as date),
                cast(@ENDDATETIMEWITHOFFSET as date), 
                cast(@CURRENTDATETIMEWITHOFFSET as datetime)
            ) as AVAILABLE on EVENT.ID = AVAILABLE.EVENTID
            where
                [PROGRAM].[ISACTIVE] = 1 and
                (
                    @NAME is null or
                    [EVENT].[NAME] like @NAME escape '\' or
                    [PROGRAM].[NAME] like @NAME escape '\'
                ) and
                exists ( --Has valid prices types

                    select 1
                    from (
                        select
                            EVENT.ID as ID,
                            EVENT.PROGRAMID as PROGRAMID
                    ) as [EVENTS]
                    outer apply (
                        select [PRICETYPECODEID]
                        from dbo.[PROGRAMEVENTPRICE]
                        where [PROGRAMEVENTPRICE].[EVENTID] = [EVENTS].[ID]
                    ) as [EVENTPRICES]
                    outer apply (
                        select [PRICETYPECODEID]
                        from dbo.[PROGRAMPRICE]
                        where 
                            [EVENTPRICES].[PRICETYPECODEID] is null and
                            [PROGRAMPRICE].[PROGRAMID] = [EVENTS].[PROGRAMID]
                    ) as [PROGRAMPRICES]
                    cross apply (
                        select isnull([PROGRAMPRICES].[PRICETYPECODEID],[EVENTPRICES].[PRICETYPECODEID]) as [PRICETYPECODEID]
                    ) as [PRICECOALESCE]
                    cross apply (
                        select 1 as [HAS]
                        where not exists (
                            select 1
                            from dbo.[SALESMETHODEXCLUDEDPRICETYPE]
                            where 
                                [SALESMETHODID] = @SALESMETHODID and
                                [PRICETYPECODEID] = [PRICECOALESCE].PRICETYPECODEID
                        )
                    ) as [VALIDPRICETYPES]
                    where [PRICECOALESCE].PRICETYPECODEID is not null
                )