SELECT distinct
  HM.HOTEL_CODE AS AliceHotelCode
  , (
    SELECT VHM.VENDOR_HOTEL_CODE
    FROM VD_VENDOR_HOTEL_MAPPING VHM
    INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
      AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
      AND VVHM.DELETE_YN = 'N'
    INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
    WHERE 1=1
      AND VHM.HOTEL_CODE = HM.HOTEL_CODE
      AND VHM.VENDOR_COMP_CODE = 110000
    LIMIT 1
  ) AS OHMYHOTEL_HQ_HOTEL_CODE
  , HM.HOTEL_NAME_EN AS AliceHotelName
  , CC.COUNTRY_CODE AS CountryCode
  , CC.COUNTRY_NAME_EN AS CountryName
  , (
    SELECT VHM.VENDOR_HOTEL_CODE
    FROM VD_VENDOR_HOTEL_MAPPING VHM
    INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
      AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
      AND VVHM.DELETE_YN = 'N'
    INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
    WHERE 1=1
      AND VHM.HOTEL_CODE = HM.HOTEL_CODE
      AND VHM.VENDOR_COMP_CODE != 110000
      AND VHM.VENDOR_COMP_CODE = 910001
    LIMIT 1
  ) AS ExpediaCode
  , (
    SELECT VHM.VENDOR_HOTEL_CODE
    FROM VD_VENDOR_HOTEL_MAPPING VHM
    INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
      AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
      AND VVHM.DELETE_YN = 'N'
    INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
    WHERE 1=1
      AND VHM.HOTEL_CODE = HM.HOTEL_CODE
      AND VHM.VENDOR_COMP_CODE != 110000
      AND VHM.VENDOR_COMP_CODE = 910002
    LIMIT 1
  ) AS HotelbedCode
  , (
    SELECT VHM.VENDOR_HOTEL_CODE
    FROM VD_VENDOR_HOTEL_MAPPING VHM
    INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
      AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
      AND VVHM.DELETE_YN = 'N'
    INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
    WHERE 1=1
      AND VHM.HOTEL_CODE = HM.HOTEL_CODE
      AND VHM.VENDOR_COMP_CODE != 110000
      AND VHM.VENDOR_COMP_CODE = 910003
    LIMIT 1
  ) AS AgodaCode
  , (
    SELECT VHM.VENDOR_HOTEL_CODE
    FROM VD_VENDOR_HOTEL_MAPPING VHM
    INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
      AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
      AND VVHM.DELETE_YN = 'N'
    INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
    WHERE 1=1
      AND VHM.HOTEL_CODE = HM.HOTEL_CODE
      AND VHM.VENDOR_COMP_CODE != 110000
      AND VHM.VENDOR_COMP_CODE = 910004
    LIMIT 1
  ) AS DOTWCode
  , (
    SELECT VHM.VENDOR_HOTEL_CODE
    FROM VD_VENDOR_HOTEL_MAPPING VHM
    INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
      AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
      AND VVHM.DELETE_YN = 'N'
    INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
    WHERE 1=1
      AND VHM.HOTEL_CODE = HM.HOTEL_CODE
      AND VHM.VENDOR_COMP_CODE != 110000
      AND VHM.VENDOR_COMP_CODE = 910005
    LIMIT 1
  ) AS FitruumsCode
  , (
    SELECT VHM.VENDOR_HOTEL_CODE
    FROM VD_VENDOR_HOTEL_MAPPING VHM
    INNER JOIN VD_VENDOR_HOTEL_MASTER VVHM ON VVHM.VENDOR_HOTEL_CODE = VVHM.VENDOR_HOTEL_CODE
      AND VVHM.VENDOR_COMP_CODE = VHM.VENDOR_COMP_CODE
      AND VVHM.DELETE_YN = 'N'
    INNER JOIN US_COMP_MASTER CM ON VHM.VENDOR_COMP_CODE = CM.COMP_CODE
    WHERE 1=1
      AND VHM.HOTEL_CODE = HM.HOTEL_CODE
      AND VHM.VENDOR_COMP_CODE != 110000
      AND VHM.VENDOR_COMP_CODE = 910016
    LIMIT 1
  ) AS DidaCode
FROM HO_HOTEL_MASTER HM
INNER JOIN (
    SELECT DISTINCT HM.HOTEL_CODE
    FROM HO_HOTEL_MASTER HM
    WHERE 1 = 1
      AND EXISTS (SELECT 1 FROM VD_VENDOR_HOTEL_CONTRACT VVHC WHERE VVHC.HOTEL_CODE = HM.HOTEL_CODE)
) D_HOTEL ON HM.HOTEL_CODE = D_HOTEL.HOTEL_CODE
INNER JOIN BS_COUNTRY_CODE CC ON HM.COUNTRY_CODE = CC.COUNTRY_CODE
LEFT JOIN BS_REGION_CODE REGION ON HM.REGION_CODE = REGION.REGION_CODE
WHERE 1 = 1
ORDER BY HM.COUNTRY_CODE
;