VOC

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