VOC



SELECT HM.HOTEL_CODE AS HotelCode,

    HM.HOTEL_NAME_EN AS HotelName,

    CC.COUNTRY_CODE AS CountryCode,

    CC.COUNTRY_NAME_EN AS CountryName,

    REGION.REGION_CODE AS RegionCode,

    REGION.REGION_NAME_EN AS RegionName,

    VVHC.HOTEL_CONTRACT_SEQ AS ContractSeq,

    CASE WHEN VVHC.CONTRACT_STATUS_CODE = 'A' THEN 'Active'

      ELSE 'Pending' END AS ContractStatus,

    (

      SELECT DETAIL_CODE_NAME_EN

      FROM BS_DETAIL_CODE

      WHERE MASTER_CODE = 'HO023'

        AND DETAIL_CODE = VVHC.HOTEL_CONTRACT_TYPE_CODE

      LIMIT 1

    ) AS ContractType,

    E.EMAIL AS Email

FROM HO_HOTEL_MASTER HM

INNER JOIN VD_VENDOR_HOTEL_CONTRACT VVHC ON VVHC.HOTEL_CODE = HM.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

LEFT JOIN LATERAL (

  SELECT GROUP_CONCAT(A.EMAIL_ADDRESS_LIST) AS EMAIL

  FROM (

    SELECT DISTINCT VVBS.EMAIL_ADDRESS_LIST

    FROM VD_VENDOR_BOOKING_SEND VVBS

    WHERE VVBS.HOTEL_CONTRACT_SEQ = VVHC.HOTEL_CONTRACT_SEQ

  ) A

) E ON TRUE

WHERE 1 = 1

  AND HM.COUNTRY_CODE = 'VN'

ORDER BY HM.COUNTRY_CODE

-- LIMIT 10

;