VOC

Finish export data

file link: https://ohmylab-my.sharepoint.com/:x:/g/personal/dat_nt_ohmyhotel_com/EQG5LSyeDqNPiu5KNLzm3PgBgplYmzCNBss_vVhn6SfC6A?e=N3l0gh

Script query

SELECT
HM.HOTEL_CODE AS HotelCode
, HM.HOTEL_NAME_EN AS HotelNameEn
, (SELECT DC.DETAIL_CODE_NAME_EN FROM BS_DETAIL_CODE DC WHERE DC.DETAIL_CODE = VHC.HOTEL_CONTRACT_TYPE_CODE AND DC.MASTER_CODE = 'HO023' LIMIT 1) AS ContractType
, (SELECT DC.DETAIL_CODE_NAME_EN FROM BS_DETAIL_CODE DC WHERE DC.DETAIL_CODE = VHC.CONTRACT_STATUS_CODE AND DC.MASTER_CODE = 'VD007' LIMIT 1) AS ContractStatus
, VHC.VENDOR_COMP_CODE AS CompCode
, VHC.HOTEL_CONTRACT_SEQ AS ContractCode
, (SELECT DC.DETAIL_CODE_NAME_EN FROM BS_DETAIL_CODE DC WHERE DC.DETAIL_CODE = VHC.SEND_AMOUNT_TYPE_CODE AND DC.MASTER_CODE = 'VD006' LIMIT 1) AS NotifyRateType
, VHC.VENDOR_COMM_RATE AS CommRate
, CASE WHEN VHC.LOCAL_PRICE_YN = 'Y' THEN 'Alice'
ELSE (SELECT CM.COMP_NAME_OFFICIAL FROM US_COMP_MASTER CM WHERE CM.COMP_CODE = VHC.PRICE_CMS_PMS_COMP_CODE LIMIT 1)
END AS Rate
, CASE WHEN VHC.LOCAL_ALLOTMENT_YN = 'Y' THEN 'Alice'
ELSE (SELECT CM.COMP_NAME_OFFICIAL FROM US_COMP_MASTER CM WHERE CM.COMP_CODE = VHC.ALLOTMENT_CMS_PMS_COMP_CODE LIMIT 1)
END AS Allotment
, (SELECT DC.DETAIL_CODE_NAME_EN FROM BS_DETAIL_CODE DC WHERE DC.DETAIL_CODE = VHC.CMS_PMS_MAPPING_STATUS_CODE AND DC.MASTER_CODE = 'VD008' LIMIT 1) AS CMSMappingStatus
, CASE WHEN VHC.SEND_CMS_PMS_COMP_CODE IS NOT NULL AND VHC.SEND_CMS_PMS_COMP_CODE != ''
THEN (SELECT CM.COMP_NAME_OFFICIAL FROM US_COMP_MASTER CM WHERE CM.COMP_CODE = VHC.SEND_CMS_PMS_COMP_CODE LIMIT 1)
ELSE '' END AS NotifyCMS
FROM HO_HOTEL_MASTER HM
INNER JOIN VD_VENDOR_HOTEL_CONTRACT VHC ON HM.HOTEL_CODE = VHC.HOTEL_CODE
WHERE 1=1
AND HM.COUNTRY_CODE IN ('KR')

;