결함(Bug) #743: [VOC]HIGH: [VOC] Rate / Allotment information for each hotel's contract type. - VOC - Redmine

Actions

Copy link

결함(Bug) #743

closed

[VOC]HIGH: [VOC] Rate / Allotment information for each hotel's contract type.

Added by ziniy Kang 8 months ago. Updated 8 months ago.

Status:

완료성공(Resolve)

Priority:

보통(Normal)

Assignee:

Mon Nguyen

Target version:

v1.0.0

Start date:

07/03/2025

Due date:

07/03/2025

% Done:

100%

Estimated time:

1.00 h

Spent time:

1.00 h

Part:

Build env.:

Prod

Description

Please support to get data as below email.
They need contract information of all hotels follow the format as attached file.

Files

Rate Allotment information.xlsx (9.35 KB) Rate Allotment information.xlsx

ziniy Kang, 07/03/2025 08:43 AM

Actions

Copy link

Updated by Mon Nguyen 8 months ago

Due date set to 07/03/2025
Status changed from 신규(New) to 완료(Done)
Assignee changed from Mon Nguyen to ziniy Kang
Start date set to 07/03/2025
% Done changed from 0 to 100
Estimated time set to 1.00 h
Build env. set to Prod

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')