USP_DATALIST_EVENTPROFILEREPORT_LODGINGANDTRAVEL

CREATE procedure dbo.USP_DATALIST_EVENTPROFILEREPORT_LODGINGANDTRAVEL
(
    @EVENTID uniqueidentifier,
    @ISVISIBLE bit = 1,
    @CURRENTAPPUSERID uniqueidentifier
)
as
    if @ISVISIBLE = 1
    begin
        select
            coalesce(REGISTRANTTRAVEL.REGISTRANTCONSTITUENTID, REGISTRANTLODGING.REGISTRANTCONSTITUENTID) as CONSTITUENTID,
            NF.NAME as CONSTITUENTNAME,
            EVENTLODGINGLOCATION.NAME as LODGINGNAME,
            EVENTLODGINGROOMTYPECODE.DESCRIPTION as ROOMTYPE,
            EVENTLODGINGROOMINSTANCE.ROOMNUMBER as ROOMNUMBER,
            REGISTRANTLODGING.CONFIRMATIONNUMBER as CONFIRMATIONNUMBER,
            REGISTRANTLODGING.CHECKINDATE as CHECKINDATE,
            dbo.UFN_HOURMINUTE_DISPLAYTIME(REGISTRANTLODGING.CHECKINTIME) as CHECKINTIME,
            REGISTRANTLODGING.CHECKOUTDATE as CHECKOUTDATE,
            dbo.UFN_HOURMINUTE_DISPLAYTIME(REGISTRANTLODGING.CHECKOUTTIME) as CHECKOUTTIME,
            (select EVENTREGISTRANTTRAVELTYPECODE.DESCRIPTION from dbo.EVENTREGISTRANTTRAVELTYPECODE where EVENTREGISTRANTTRAVELTYPECODE.ID = REGISTRANTTRAVEL.REGISTRANTARRIVALTRAVELTYPECODEID) as ARRIVALTYPE,
            (select EVENTREGISTRANTTRAVELNAMECODE.DESCRIPTION from dbo.EVENTREGISTRANTTRAVELNAMECODE where EVENTREGISTRANTTRAVELNAMECODE.ID = REGISTRANTTRAVEL.REGISTRANTARRIVALTRAVELNAMECODEID) as ARRIVALNAME,
            REGISTRANTTRAVEL.ARRIVALNUMBER as ARRIVALNUMBER,
            REGISTRANTTRAVEL.ARRIVALDATE AS ARRIVALDATE,
            dbo.UFN_HOURMINUTE_DISPLAYTIME(REGISTRANTTRAVEL.ARRIVALTIME) as ARRIVALTIME,
            (select EVENTREGISTRANTTRAVELTYPECODE.DESCRIPTION from dbo.EVENTREGISTRANTTRAVELTYPECODE where EVENTREGISTRANTTRAVELTYPECODE.ID = REGISTRANTTRAVEL.REGISTRANTDEPARTURETRAVELTYPECODEID) as DEPATURETYPE,
            (select EVENTREGISTRANTTRAVELNAMECODE.DESCRIPTION from dbo.EVENTREGISTRANTTRAVELNAMECODE where EVENTREGISTRANTTRAVELNAMECODE.ID = REGISTRANTTRAVEL.REGISTRANTDEPARTURETRAVELNAMECODEID) as DEPARTURENAME,
            REGISTRANTTRAVEL.DEPARTURENUMBER as DEPATURENUMBER,
            REGISTRANTTRAVEL.DEPARTUREDATE as DEPATUREDATE,
            dbo.UFN_HOURMINUTE_DISPLAYTIME(REGISTRANTTRAVEL.DEPARTURETIME) as DEPARTURETIME
        from
            dbo.REGISTRANTTRAVEL
        full outer join
            dbo.REGISTRANTLODGING on REGISTRANTTRAVEL.REGISTRANTCONSTITUENTID = REGISTRANTLODGING.REGISTRANTCONSTITUENTID
        left join
            dbo.EVENTLODGING on EVENTLODGING.ID = REGISTRANTLODGING.EVENTLODGINGID
        left join
            dbo.EVENTLODGINGLOCATION on EVENTLODGINGLOCATION.ID = EVENTLODGING.EVENTLODGINGLOCATIONID
        left join
            dbo.EVENTLODGINGROOMINSTANCE on EVENTLODGINGROOMINSTANCE.ID = REGISTRANTLODGING.EVENTLODGINGROOMINSTANCEID
        left join
            dbo.EVENTLODGINGROOM on EVENTLODGINGROOM.ID = EVENTLODGINGROOMINSTANCE.EVENTLODGINGROOMID
        left join
            dbo.EVENTLODGINGROOMTYPECODE on EVENTLODGINGROOMTYPECODE.ID = EVENTLODGINGROOM.ROOMTYPECODEID
        outer apply
            dbo.UFN_CONSTITUENT_DISPLAYNAME(coalesce(REGISTRANTTRAVEL.REGISTRANTCONSTITUENTID, REGISTRANTLODGING.REGISTRANTCONSTITUENTID)) NF    
        where
            REGISTRANTLODGING.MAINEVENTID = @EVENTID
            or
            REGISTRANTTRAVEL.MAINEVENTID = @EVENTID
        order by
            CONSTITUENTNAME
    end