SELECT 
  BRC.REGION_CODE AS RegionCode
  , BRC.REGION_NAME_EN AS RegionNameEn
  , HHM.HOTEL_CODE AS HotelCode
  , HHM.HOTEL_NAME_EN AS HotelNameEn
  , (
    SELECT DC.DETAIL_CODE_NAME_EN
    FROM BS_DETAIL_CODE DC
    WHERE DC.DETAIL_CODE = UCM.TRADE_STATUS_CODE
      AND DC.MASTER_CODE = 'US002'
    LIMIT 1
  ) AS TradeStatusNameEn
  , VVHC.VENDOR_COMP_CODE AS CompCode
  , VVHC.HOTEL_CONTRACT_SEQ AS HotelContractSeq
  , (
    SELECT DC.DETAIL_CODE_NAME_EN
    FROM BS_DETAIL_CODE DC
    WHERE DC.DETAIL_CODE = VVHC.HOTEL_CONTRACT_TYPE_CODE
      AND DC.MASTER_CODE = 'HO023'
    LIMIT 1
  ) AS ContractTypeNameEn

  , VVHC.ALLOTMENT_CMS_PMS_COMP_CODE AS AllotmentCmsPmsCompCode
  , (
    SELECT COMP_NAME_EN
    FROM US_COMP_MASTER
    WHERE COMP_CODE = VVHC.ALLOTMENT_CMS_PMS_COMP_CODE
  ) AS AllotmentCmsPmsName

  , VVHC.PRICE_CMS_PMS_COMP_CODE AS PriceCmsPmsCompCode
  , (
    SELECT COMP_NAME_EN
    FROM US_COMP_MASTER
    WHERE COMP_CODE = VVHC.PRICE_CMS_PMS_COMP_CODE
  ) AS PriceCmsPmsName
  
  , VVHC.SEND_CMS_PMS_COMP_CODE AS NotifyCmsPmsCompCode
  , (
    SELECT COMP_NAME_EN
    FROM US_COMP_MASTER
    WHERE COMP_CODE = VVHC.SEND_CMS_PMS_COMP_CODE
  ) AS NotifyCmsPmsName
  , VVHC.CMS_PMS_HOTEL_CODE AS CmsPmsHotelCode
  , VVHC.VENDOR_COMM_RATE AS VCommRate
  , (
    SELECT DC.DETAIL_CODE_NAME_EN
    FROM BS_DETAIL_CODE DC
    WHERE DC.DETAIL_CODE = PC.PAYMENT_TYPE_CODE
      AND DC.MASTER_CODE = 'VD001'
  )  AS PaymentType
  , PC.PAYMENT_METHOD_CODE AS PaymentMethod
--   , UCM.COMP_TYPE_CODE1
--   , UCM.COMP_TYPE_CODE2
--   , UCM.COMP_TYPE_CODE3
FROM HO_HOTEL_MASTER HHM
LEFT JOIN BS_REGION_CODE BRC ON HHM.REGION_CODE = BRC.REGION_CODE
INNER JOIN VD_VENDOR_HOTEL_CONTRACT VVHC ON HHM.HOTEL_CODE = VVHC.HOTEL_CODE
  AND VVHC.CONTRACT_STATUS_CODE = 'A'
INNER JOIN US_COMP_MASTER UCM ON VVHC.VENDOR_COMP_CODE = UCM.COMP_CODE
LEFT JOIN VD_VENDOR_PAYMENT_CONDITION PC ON PC.VENDOR_COMP_CODE = VVHC.VENDOR_COMP_CODE
WHERE HHM.COUNTRY_CODE = 'VN'
  AND (
    (PC.APPLIED_TO_DATE IS NULL)
    OR 
    (PC.APPLIED_TO_DATE IS NOT NULL AND PC.APPLIED_TO_DATE >= date(NOW()))
  )

-- LIMIT 100
;