CREATE OR REPLACE FORCE VIEW V8_REP_ITINERARY AS
SELECT   "TOPIC", "TOPICNUM", "ITINERARYDATE", "STARTTIME", "ENDTIME", 
            "LOCATION", "DESCRIPTION", "YRES_ID" 
       FROM (SELECT 'Table Reservation' topic, 2 topicnum, 
                    a.rres_date itinerarydate, a.starttime starttime, 
                    a.endtime endtime, a.restaurant LOCATION, 
                    'Table ' || a.ALL_TABLES description, 
                    rrer.rrer_yres_id yres_id 
               FROM v8_rep_rres_infos a, rrer 
              WHERE a.rres_id = rrer.rrer_rres_id 
             UNION 
             SELECT 'Hotel Event' topic, 3 topicnum, 
                    TRUNC (b.ycev_fromdate) itinerarydate, 
                    CASE 
                       WHEN (TO_CHAR (b.ycev_fromdate, 'HH24:MI')) = 
                                                            '00:00' 
                          THEN ' ' 
                       ELSE TO_CHAR (b.ycev_fromdate, 'HH24:MI') 
                    END starttime, 
                    CASE 
                       WHEN (TO_CHAR (b.ycev_todate, 'HH24:MI')) = 
                                                              '00:00' 
                          THEN ' ' 
                       ELSE TO_CHAR (b.ycev_todate, 'HH24:MI') 
                    END endtime, 
                    'Hotel' LOCATION, b.ycev_longwebinfo description, 
                    yres.yres_id yres_id 
               FROM ycev b, yres, ycet 
              WHERE b.ycev_fromdate >= yres.yres_exparrtime 
                AND b.ycev_fromdate <= yres.yres_expdeptime 
                AND ycet.ycet_id = b.ycev_ycet_id 
                AND ycet.ycet_webenabled = 1 
                AND b.ycev_webenabled = 1 
             UNION 
             SELECT 'Arrival' topic, 1 topicnum, 
                    c.yres_exparrtime itinerarydate, 
                    CASE 
                       WHEN (TO_CHAR (c.yres_exparrhour, 'HH24:MI') 
                            ) = '00:00' 
                          THEN (SELECT SUBSTR (wuss_value, 12, 5) 
                                  FROM wuss 
                                 WHERE wuss.wuss_xcms_id = 0 
                                   AND wuss_name LIKE 'DefaultCheckInTime') 
                       ELSE TO_CHAR (c.yres_exparrhour, 'HH24:MI') 
                    END starttime, 
                    ' ' endtime, 'Hotel' LOCATION, 
                    'Scheduled Arrival' description, c.yres_id yres_id 
               FROM yres c 
             UNION 
             SELECT 'Departure' topic, 99 topicnum, 
                    d.yres_expdeptime itinerarydate, ' ' starttime, 
                    CASE 
                       WHEN (TO_CHAR (d.yres_expdephour, 'HH24:MI')) = 
                                                              '00:00' 
                          THEN (SELECT SUBSTR (wuss_value, 12, 5) 
                                  FROM wuss 
                                 WHERE wuss.wuss_xcms_id = 0 
                                   AND wuss_name LIKE 'DefaultCheckOutTime') 
                       ELSE TO_CHAR (d.yres_expdephour, 'HH24:MI') 
                    END endtime, 
                    'Hotel' LOCATION, 'Scheduled Departure' description, 
                    d.yres_id yres_id 
               FROM yres d 
--             UNION 
--             SELECT 'Spa Booking' topic, 4 topicnum, 
--                    TRUNC (a.bookingstartdatetime) itinerarydate, 
--                    TO_CHAR (a.bookingstartdatetime, 'HH24:MI') starttime, 
--                    TO_CHAR (a.bookingenddatetime, 'HH24:MI') endtime, 
--                    'SPA' LOCATION, bookingdescription_de description, 
--                    TO_NUMBER (a.yres_id) yres_id 
--               FROM BOOKINGDETAILLIST a 
--              WHERE a.bookingstate LIKE 'reserved' AND a.yres_id IS NOT NULL
) 
   ORDER BY itinerarydate ASC, starttime ASC;

CREATE OR REPLACE VIEW V8_REP_ITINERARY_CUST AS SELECT * FROM V8_REP_ITINERARY;
