-- UPDATE SCRIPT CONTAINING ONLY VIEWS THAT HAVE CHANGED SINCE PREVIOUS RELEASE

-- THE FOLLOWING VIEWS NEED TO BE EXECUTED ONLY IN CASE BELLA VITA OR LEISURE IS INSTALLED


CREATE OR REPLACE VIEW V8_REP_BV_BookingDetails_FUT AS
SELECT 
DECODE(a.CREATEDFROM,1,'SPA', 2, 'PMS') OriginatedSystem,
TRUNC(a.BOOKINGSTARTDATETIME) DayofSpa,
a.PARTGLASTNAME||', '||a.PARTGFIRSTNAME GuestName,
(SELECT n.NAME2 FROM v8_rep_name n WHERE n.XCMS_ID = a.PARTICIPATINGGUESTKEY) GuestName_Suite8,
a.BOOKINGDESCRIPTION_en BOOKINGDESCRIPTION,
a.BOOKINGSTARTDATETIME Starts,
a.BOOKINGENDDATETIME Ends,
a.BOOKINGDURATION Duration,
a.PRICE,
a.REMARK_en Remark,
a.CREATED,
a.Facility_en Facility,
a.Facility_Count,
a.Employee,
a.Employee_Count
FROM BookingDetailList_FUT a
WHERE 
 a.BOOKINGACTIVE = 1;


CREATE OR REPLACE FORCE VIEW BOOKINGDETAILLIST AS 
SELECT bk.ReferenceNumber AS RefNumber,
       bk.ID AS BOOKING_ID,
       NVL (bk.OLCREATED, 0) AS OLCREATED,
       (CASE bk.OfferInCustomizedPackage_ID WHEN NULL THEN 0 ELSE 1 END)
          AS IsPartOfPackage,
       ofr.description_EN AS BookingDescription,
       ofr.description_de AS BookingDescription_de,
       ofr.description_fr AS BookingDescription_fr,
       ofr.description_it AS BookingDescription_it,
       ofr.description_en AS BookingDescription_en,
       ofr.description_ru AS BookingDescription_ru,
       ofr.description_pl AS BookingDescription_pl,
       ofr.description_uk AS BookingDescription_uk,
       ofr.description_es AS BookingDescription_es,
       ofr.description_sk AS BookingDescription_sk,
       ofr.description_hu AS BookingDescription_hu,
       ofr.description_fi AS BookingDescription_fi,
       ofr.description_cs AS BookingDescription_cs,
       ofr.description_ee AS BookingDescription_ee,
       bk.remark_EN AS remark,
       bk.remark_de,
       bk.remark_fr,
       bk.remark_it,
       bk.remark_en,
       bk.remark_ru,
       bk.remark_pl,
       bk.remark_uk,
       bk.remark_es,
       bk.remark_sk,
       bk.remark_hu,
       bk.remark_fi,
       bk.remark_cs,
       bk.remark_ee,
       bk.StartTime AS BookingStartDateTime,
       bk.accounted AS BookingAccounted,
       bk.EndTime AS BookingEndDateTime,
       bk.Duration AS BookingDuration,
       cd.code AS BookingStateCode,
       (CASE cd.code
           WHEN 0
           THEN
              'reserved'
           ELSE
              (CASE cd.code
                  WHEN 1 THEN 'consumed'
                  ELSE (CASE cd.code WHEN 2 THEN 'missed' END)
               END)
        END)
          AS BookingState,
       bk.TENTATIVEUNTIL AS TentativeUntil,
       bk.active AS BookingActive,
       le_inactive.CreationTimestamp AS BookingActiveChangedTimestamp,
       (CASE le_inactive.bellavitausertext WHEN 'bellavita' THEN 2 ELSE 1 END)
          AS BookingActiveChangedFrom,
       le_inactive.useremployee_id AS BookingActiveChangedByEmployee,
       bk.BookingKeyNote_Id AS BookingKeyNote_Id,
       payg.ForeignID AS PayingGuestKey,
       partg.ForeignID AS ParticipatingGuestKey,
       bk.LeisurePrice AS Price,
       bk.Price AS NonLeisurePrice,
       bk.ReservationID AS YRES_ID,
       dc.ID AS ZDCO_ID,
       (CASE le.bellavitausertext WHEN 'bellavita' THEN 2 ELSE 1 END)
          AS createdfrom,
       le.CreationTimestamp AS created,
       PARTG.ID partg_id,
       partg.firstname AS partgfirstname,
       partg.lastname AS partglastname,
       PAYG.ID payg_id,
       payg.firstname AS paygfirstname,
       payg.lastname AS payglastname,
       bk.CancellationCategory_ID AS BookingCancellationCategory_ID,
       bk.BVReservation_ID AS BookingReservation_ID,
       bk.MarketCode_ID AS BookingMarketCode_ID,
       marketcode.code AS marketcode
  FROM booking bk
       LEFT JOIN cmn_marketcode marketcode
          ON bk.marketcode_id = marketcode.id
       INNER JOIN Offer ofr
          ON bk.Offer_ID = ofr.id AND ofr.taid = 0 AND ofr.state = 0
       INNER JOIN loggingentry le
          ON le.booking_id = bk.ID
             AND le.id =
                    (SELECT MIN (id)
                       FROM loggingentry le2
                      WHERE     le2.livestate = 'N'
                            AND le2.taid = 0
                            AND le2.state = 0
                            AND le2.booking_id = bk.ID)
       LEFT JOIN loggingentry le_inactive
          ON     le_inactive.booking_id = bk.id
             AND le_inactive.messagecode = 27
             AND le_inactive.taid = 0
             AND le_inactive.state = 0
       LEFT JOIN Code cd
          ON bk.BookingState_ID = cd.id AND cd.taid = 0 AND cd.state = 0
       LEFT JOIN GuestInBooking gib
          ON gib.Booking_ID = bk.id AND gib.taid = 0 AND gib.state = 0
       LEFT JOIN Guest partg
          ON gib.guest_id = partg.id AND partg.taid = 0
       LEFT JOIN Guest payg
          ON bk.payingguest_id = payg.id AND payg.taid = 0
       LEFT JOIN ChargeType ct
          ON ct.id = bk.ChargeType_ID AND ct.taid = 0 AND ct.state = 0
       LEFT OUTER JOIN DepartmentCode dc
          ON ct.DepartmentCode_ID = dc.id AND dc.taid = 0 AND dc.state = 0
 WHERE bk.taid = 0 AND bk.state = 0;

COMMENT ON TABLE BOOKINGDETAILLIST IS 'Booking details with booking details and a moderate set of guest details attributes<p/>Remark: This view is provided as PMS view too !';

COMMENT ON COLUMN BOOKINGDETAILLIST.PAYGLASTNAME IS 'Last name of paying guest';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGRESERVATION_ID IS 'Identifies instance of BVReservation (see BVRESERVATION.ID)';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGMARKETCODE_ID IS 'code value identifying the market code of this booking<br/>see CMN_MARKETCODE.ID';

COMMENT ON COLUMN BOOKINGDETAILLIST.MARKETCODE IS 'Code value identifying the market code of this sale<br/>see CMN_MARKETCODE.CODE ';

COMMENT ON COLUMN BOOKINGDETAILLIST.REFNUMBER IS 'Unique booking reference number (visible, printable).   <br>   <br>Remark:   <br>Each booking record is listed to a maximum of 4 entries.<br/>   <ul>   <li>   <ul>  <li>   first pair:  skill-type = 1:   </li>   <li>   1. employee_ID is not null, facility_ID is null </li>   <li>   2. employee_ID is null , facility_ID is null </li>   </ul>  </li>      <li>   <ul>  <li>   first pair:  skill-type =2:   </li>   <li>   1. employee_ID is null , facility_ID is not null </li>   <li>   2. employee_ID is null , facility_ID is null </li>   </ul>  </li>         </ul>';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKING_ID IS 'unique reference id booking used as reference to the ID field in CMN_BOOKING or similar';

COMMENT ON COLUMN BOOKINGDETAILLIST.OLCREATED IS 'Creation timestamp for an online booking.';

COMMENT ON COLUMN BOOKINGDETAILLIST.ISPARTOFPACKAGE IS 'If the booking was sold/consumed as part of a package';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGDESCRIPTION_DE IS 'Booking description<br/> Remark: If the original booking description (field DESCRIPTION) is empty, this contains the description of the CMN_OFFER';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGDESCRIPTION_FR IS 'Booking description<br/> Remark: If the original booking description (field DESCRIPTION) is empty, this contains the description of the CMN_OFFER';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGDESCRIPTION_IT IS 'Booking description<br/> Remark: If the original booking description (field DESCRIPTION) is empty, this contains the description of the CMN_OFFER';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGDESCRIPTION_EN IS 'Booking description<br/> Remark: If the original booking description (field DESCRIPTION) is empty, this contains the description of the CMN_OFFER';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGDESCRIPTION_RU IS 'Booking description<br/> Remark: If the original booking description (field DESCRIPTION) is empty, this contains the description of the CMN_OFFER';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGDESCRIPTION_PL IS 'Booking description<br/> Remark: If the original booking description (field DESCRIPTION) is empty, this contains the description of the CMN_OFFER';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGDESCRIPTION_UK IS 'Booking description<br/> Remark: If the original booking description (field DESCRIPTION) is empty, this contains the description of the CMN_OFFER';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGDESCRIPTION_ES IS 'Booking description<br/> Remark: If the original booking description (field DESCRIPTION) is empty, this contains the description of the CMN_OFFER';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGDESCRIPTION_SK IS 'Booking description<br/> Remark: If the original booking description (field DESCRIPTION) is empty, this contains the description of the CMN_OFFER';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGDESCRIPTION_HU IS 'Booking description<br/> Remark: If the original booking description (field DESCRIPTION) is empty, this contains the description of the CMN_OFFER';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGDESCRIPTION_FI IS 'Booking description<br/> Remark: If the original booking description (field DESCRIPTION) is empty, this contains the description of the CMN_OFFER';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGDESCRIPTION_CS IS 'Booking description<br/> Remark: If the original booking description (field DESCRIPTION) is empty, this contains the description of the CMN_OFFER';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGDESCRIPTION_EE IS 'Booking description<br/> Remark: If the original booking description (field DESCRIPTION) is empty, this contains the description of the CMN_OFFER';

COMMENT ON COLUMN BOOKINGDETAILLIST.REMARK_DE IS 'Booking remarks';

COMMENT ON COLUMN BOOKINGDETAILLIST.REMARK_FR IS 'Booking remarks';

COMMENT ON COLUMN BOOKINGDETAILLIST.REMARK_IT IS 'Booking remarks';

COMMENT ON COLUMN BOOKINGDETAILLIST.REMARK_EN IS 'Booking remarks';

COMMENT ON COLUMN BOOKINGDETAILLIST.REMARK_RU IS 'Booking remarks';

COMMENT ON COLUMN BOOKINGDETAILLIST.REMARK_PL IS 'Booking remarks';

COMMENT ON COLUMN BOOKINGDETAILLIST.REMARK_UK IS 'Booking remarks';

COMMENT ON COLUMN BOOKINGDETAILLIST.REMARK_ES IS 'Booking remarks';

COMMENT ON COLUMN BOOKINGDETAILLIST.REMARK_SK IS 'Booking remarks';

COMMENT ON COLUMN BOOKINGDETAILLIST.REMARK_HU IS 'Booking remarks';

COMMENT ON COLUMN BOOKINGDETAILLIST.REMARK_FI IS 'Booking remarks';

COMMENT ON COLUMN BOOKINGDETAILLIST.REMARK_CS IS 'Booking remarks';

COMMENT ON COLUMN BOOKINGDETAILLIST.REMARK_EE IS 'Booking remarks';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGSTARTDATETIME IS 'Booking start time';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGACCOUNTED IS 'If the booking was already accounted, i.e. if an invoice  exist for that booking';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGENDDATETIME IS 'Booking end time';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGDURATION IS 'Booking duration in minutes';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGSTATECODE IS 'Booking state code:<br><br><ul> <li> <br>RESERVED: 0<br></li> <li> <br>CONSUMED: 1<br></li> <li> <br>MISSED: 2 <br></li> <li> <br>IRRELEVANT: 3<br></li> </ul>';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGSTATE IS 'Booking state <ul> <li> reserved </li> <li> consumed </li> <li> billed </li> </ul>';

COMMENT ON COLUMN BOOKINGDETAILLIST.TENTATIVEUNTIL IS 'The date and time the booking is tentative until ';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGACTIVE IS '<> 0: booking is active   <br>= 0: booking is inactive (if the booking can not be consumed anymore)';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGACTIVECHANGEDTIMESTAMP IS 'The time when the booking active state was changed by a user.';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGACTIVECHANGEDFROM IS 'If the booking active state was changed by a user or by <i>Bellavita</i> (a local UI User, created by the application).<br/>.<br><br>If the former is the case then <b>2</b>, if the latter is the case then <b>1</b>.';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGACTIVECHANGEDBYEMPLOYEE IS 'Employee who changed the booking active state.';

COMMENT ON COLUMN BOOKINGDETAILLIST.BOOKINGKEYNOTE_ID IS 'Booking Notes when cancelling a booking (deactivated)<br>Query to get the notes: <code>SELECT T.* FROM BOOKINGDETAILLIST BK, TNOTE T WHERE BK.BOOKINGKEYNOTE_ID = T.ID</code>';

COMMENT ON COLUMN BOOKINGDETAILLIST.PAYINGGUESTKEY IS 'Foreign ID of the paying guest, i.e. XCMS_ID of guest entry in V8 table XCMS';

COMMENT ON COLUMN BOOKINGDETAILLIST.PARTICIPATINGGUESTKEY IS 'Foreign ID of the participating guest, i.e. XCMS_ID of guest entry in V8 table XCMS';

COMMENT ON COLUMN BOOKINGDETAILLIST.PRICE IS 'Booking price incl. VAT ';

COMMENT ON COLUMN BOOKINGDETAILLIST.YRES_ID IS 'The reservation id in the V8 YRES reservation table.';

COMMENT ON COLUMN BOOKINGDETAILLIST.ZDCO_ID IS 'ID of the related ZDCO record.';

COMMENT ON COLUMN BOOKINGDETAILLIST.CREATEDFROM IS 'If the booking active state was changed by a user or by <i>Bellavita</i> (a local UI User, created by the application).<br/>.<br><br>If the former is the case then <b>2</b>, if the latter is the case then <b>1</b>.';

COMMENT ON COLUMN BOOKINGDETAILLIST.CREATED IS 'Creation date and time ';

COMMENT ON COLUMN BOOKINGDETAILLIST.PARTG_ID IS 'GUEST_ID  of participating guest';

COMMENT ON COLUMN BOOKINGDETAILLIST.PARTGFIRSTNAME IS 'First name of participating guest';

COMMENT ON COLUMN BOOKINGDETAILLIST.PARTGLASTNAME IS 'Last name of participating guest';

COMMENT ON COLUMN BOOKINGDETAILLIST.PAYG_ID IS 'GUEST_ID of paying guest';

COMMENT ON COLUMN BOOKINGDETAILLIST.PAYGFIRSTNAME IS 'First name of paying guest';


CREATE OR REPLACE VIEW V8_REP_LEI_INFOS AS 
select 
lb.REFERENCENUMBER, 
1 COUNT, 
to_char(LBD.BOOKINGSTARTDATETIME ,'HH24:MI') STARTTIME_TIME, 
trunc(LBD.BOOKINGSTARTDATETIME) STARTTIME_DATE, 
to_char(LBD.BOOKINGENDDATETIME ,'HH24:MI') ENDTIME_TIME,
trunc(LBD.BOOKINGENDDATETIME) ENDTIME_DATE,  
(SELECT LO.GROUP_ID FROM OFFER LO WHERE LB.OFFER_ID=LO.ID) OFFERGROUP_ID, 
(SELECT LG.DESCRIPTION_EN FROM OFFER LO, OFFERGROUP LG WHERE LO.GROUP_ID=LG.ID and LG.SPECIALID=0 AND LB.OFFER_ID=LO.ID) OFFERGROUP_DESC,
--(SELECT LG.DESCRIPTION_DE FROM OFFER LO, OFFERGROUP LG WHERE LO.GROUP_ID=LG.ID and LG.SPECIALID=0 AND LB.OFFER_ID=LO.ID) L_OFFER_GROUP_DESCRIPTION_DE,
--(SELECT LG.DESCRIPTION_FR FROM OFFER LO, OFFERGROUP LG WHERE LO.GROUP_ID=LG.ID and LG.SPECIALID=0 AND LB.OFFER_ID=LO.ID) L_OFFER_GROUP_DESCRIPTION_FR,
--(SELECT LG.DESCRIPTION_IT FROM OFFER LO, OFFERGROUP LG WHERE LO.GROUP_ID=LG.ID and LG.SPECIALID=0 AND LB.OFFER_ID=LO.ID) L_OFFER_GROUP_DESCRIPTION_IT, 
--(SELECT LG.DESCRIPTION_EN FROM OFFER LO, OFFERGROUP LG WHERE LO.GROUP_ID=LG.ID and LG.SPECIALID=0 AND LB.OFFER_ID=LO.ID) L_OFFER_GROUP_DESCRIPTION_EN,
(SELECT LG2.DESCRIPTION_EN FROM OFFER LO, OFFERGROUP LG1, OFFERGROUP LG2 WHERE LO.GROUP_ID=LG1.ID and LG1.SPECIALID=0 AND LG1.PARENT_ID=LG2.ID AND LG2.SPECIALID=1 AND LB.OFFER_ID=LO.ID) OFFERGROUP_MAINDESC, 
LB.BOOKINGSTATE_ID BOOKINGSTATE_ID, 
(SELECT LC.DESCRIPTION_EN from CODE LC WHERE LC.ID=LB.BOOKINGSTATE_ID) BOOKINGSTATE_DESC, 
--(SELECT LC.DESCRIPTION_DE from CODE LC WHERE LC.ID=LB.BOOKINGSTATE_ID) STATUS_DE, 
--(SELECT LC.DESCRIPTION_FR from CODE LC WHERE LC.ID=LB.BOOKINGSTATE_ID) STATUS_FR, 
--(SELECT LC.DESCRIPTION_IT from CODE LC WHERE LC.ID=LB.BOOKINGSTATE_ID) STATUS_IT, 
LBD.BOOKING_ID,
LBD.BOOKINGDESCRIPTION_EN BOOKINGDESCRIPTION,
--LBD.BOOKINGDESCRIPTION_DE BOOKINGDESCRIPTION_DE, 
--LBD.BOOKINGDESCRIPTION_FR BOOKINGDESCRIPTION_FR, 
--LBD.BOOKINGDESCRIPTION_IT BOOKINGDESCRIPTION_IT, 
LBD.REMARK_EN REMARKS,
--LBD.REMARK_DE REMARK_DE,  
--LBD.REMARK_FR REMARK_FR, 
--LBD.REMARK_IT REMARK_IT, 
LBD.BOOKINGDURATION BOOKINGDURATION, 
LBD.BOOKINGACTIVE BOOKINGACTIVE, 
LBD.BOOKINGACCOUNTED BOOKINGACCOUNTED, 
LBD.PAYG_ID PAYINGGUESTKEY, 
to_number(LBD.PAYINGGUESTKEY) PAY_XCMS_ID, 
(SELECT v8_rep_name.NAME from v8_rep_name where v8_rep_name.XCMS_ID=LBD.PAYINGGUESTKEY) PAY_NAME,
(SELECT v8_rep_name.NAME6 from v8_rep_name where v8_rep_name.XCMS_ID=LBD.PAYINGGUESTKEY) PAY_NAME6, 
LBD.PARTG_ID PARTICIPATINGGUESTKEY, 
LBD.PARTICIPATINGGUESTKEY PARTICI_XCMS_ID, 
(SELECT v8_rep_name.NAME6 from v8_rep_name where v8_rep_name.XCMS_ID=LBD.PARTICIPATINGGUESTKEY) NAME6, 
nvl(V8_Fnc_Xcno_Rep_Infos(nvl(LBD.PARTICIPATINGGUESTKEY,LBD.PAYINGGUESTKEY),'XCMS',1,0),V8_Fnc_Xcno_Rep_Infos(nvl(LBD.PARTICIPATINGGUESTKEY,LBD.PAYINGGUESTKEY),'XCMS',1,1)) XCMS_NOTES, 
nvl2(LBD.YRES_ID,1,0) LINKED_TO_ROOM_ACCOUNT, 
(CASE when exists (select 1 from yres where yres.YRES_ID=LBD.YRES_ID) then 'YRES' when exists (select 1 from zfac where zfac.ZFAC_ID=LBD.YRES_ID) then 'ZFAC' else 'NOLINK' end) LINKED_TO, 
(CASE when exists (select 1 from yres where yres.YRES_ID=LBD.YRES_ID) then (select yres.YRES_EXPARRTIME from yres where yres.YRES_ID=LBD.YRES_ID) when exists (select 1 from zfac where zfac.ZFAC_ID=LBD.YRES_ID) then (select zfac.ZFAC_EXPVALIDFROM from zfac where zfac.ZFAC_ID=LBD.YRES_ID) else to_date(null) end) ARRIVAL, 
(CASE when exists (select 1 from yres where yres.YRES_ID=LBD.YRES_ID) then (select yres.YRES_EXPDEPTIME from yres where yres.YRES_ID=LBD.YRES_ID) when exists (select 1 from zfac where zfac.ZFAC_ID=LBD.YRES_ID) then (select zfac.ZFAC_EXPVALIDUNTIL from zfac where zfac.ZFAC_ID=LBD.YRES_ID) else to_date(null) end) DEPARTURE, 
(CASE when exists (select 1 from yres where yres.YRES_ID=LBD.YRES_ID) then (select yrms.YRMS_SHORTDESC from yrms,yres,ydet where yrms.YRMS_ID=ydet.YDET_YRMS_ID and ydet.YDET_ID=yres.YRES_CURRENT_YDET_ID and yres.YRES_ID=LBD.YRES_ID) when exists (select 1 from zfac where zfac.ZFAC_ID=LBD.YRES_ID) then (select zfac.ZFAC_LONGDESC from zfac where zfac.ZFAC_ID=LBD.YRES_ID) else to_char(null) end) ROOM_ACCOUNT, 
LBD.YRES_ID YRES_ID, 
LBD.PRICE PRICE, 
LB.ACCOUNTED ACCOUNTED, 
(select lbc.RESDESCR_DE from bookingresourcelist LBC where lbc.REFNUMBER = lb.REFERENCENUMBER and lbc.RESSUBTYPE = 'Employee') EMPLOYEE, 
(select lbc.RES_ID from bookingresourcelist LBC where lbc.REFNUMBER = lb.REFERENCENUMBER and lbc.RESSUBTYPE = 'Employee') EMPLOYEE_ID, 
(select lbc.RESDESCR_EN from bookingresourcelist LBC where lbc.REFNUMBER = lb.REFERENCENUMBER and lbc.RESSUBTYPE = 'Facility') FACILITY, 
(select lbc.RES_ID from bookingresourcelist LBC where lbc.REFNUMBER = lb.REFERENCENUMBER and lbc.RESSUBTYPE = 'Facility') FACILITY_ID 
from BOOKINGDETAILLIST LBD, BOOKING LB 
where LBD.REFNUMBER=LB.REFERENCENUMBER;

