SELECT DISTINCT
    HM.COUNTRY_CODE,
    BCC.COUNTRY_NAME_EN AS COUNTRY_NAME,
    HM.HOTEL_CODE,
    IFNULL(HM.LEGACY_HOTEL_CODE, VHC.LEGACY_HOTEL_CODE) AS LEGACY_HOTEL_CODE,
    HM.HOTEL_NAME_EN AS HOTEL_NAME,
    HM.REGION_CODE,
    BRC.REGION_NAME_EN AS REGION_NAME,
    
    VHC.VENDOR_COMP_CODE AS TRADER_CODE,
    CM.COMP_NAME_OFFICIAL AS TRADER_NAME,
    (SELECT DETAIL_CODE_NAME_EN FROM BS_DETAIL_CODE WHERE DETAIL_CODE = VHC.HOTEL_CONTRACT_TYPE_CODE AND MASTER_CODE = 'HO023' LIMIT 1) AS ACTIVE_CONTRACT_TYPE,
    VHC.HOTEL_CONTRACT_SEQ AS ACTIVE_CONTRACT_NUMBER
FROM HO_HOTEL_MASTER HM
INNER JOIN VD_VENDOR_HOTEL_CONTRACT VHC ON HM.HOTEL_CODE = VHC.HOTEL_CODE AND VHC.CONTRACT_STATUS_CODE = 'A'
INNER JOIN VD_VENDOR_ROOM_TYPE VRT ON HM.HOTEL_CODE = VRT.HOTEL_CODE AND VRT.SALES_STOP_YN = 'N'
LEFT JOIN BS_COUNTRY_CODE BCC ON HM.COUNTRY_CODE = BCC.COUNTRY_CODE
LEFT JOIN BS_REGION_CODE BRC ON BRC.REGION_CODE = HM.REGION_CODE
LEFT JOIN BS_DETAIL_CODE DC ON DC.MASTER_CODE = 'BS018' AND HM.REGISTER_STATUS_CODE = DC.DETAIL_CODE
LEFT JOIN US_COMP_MASTER CM ON VHC.VENDOR_COMP_CODE = CM.COMP_CODE