VOC

@Vin Daniel Do run script to check and update all HardBlock hotel have different allotments

We haven't identified the root cause.
We'll need to monitor more cases in the future to see if it happens again.

SET @checkInFrom = '20250501';
SET @checkInTo = '20250430';

SELECT
RS.HOTEL_CODE,
RS.VENDOR_ROOM_TYPE_SEQ,
RS.APPLIED_DATE,
AL.ALLOTMENT_COUNT_SUM AS TOTAL_COUNT,
AL.LEFT_COUNT_SUM AS REMAIN_COUNT,
RS.CONFIRM_COUNT,
RS.CANCEL_COUNT,
RS.BOOKINGS,
AL.FIRST_INSERT_DATETIME,
AL.LAST_UPDATE_DATETIME
FROM VD_VENDOR_ROOM_ALLOTMENT AL
INNER JOIN (
SELECT DISTINCT
B.HOTEL_CODE,
B.HOTEL_CONTRACT_SEQ,
T.VENDOR_ROOM_TYPE_SEQ,
BR.APPLIED_DATE,
SUM THEN 1 ELSE 0 END) AS CONFIRM_COUNT,
SUM THEN 1 ELSE 0 END) AS CANCEL_COUNT,
GROUP_CONCAT(B.BOOKING_ITEM_CODE SEPARATOR ', ') AS BOOKINGS
FROM BK_BOOKING_HOTEL_ITEM B
INNER JOIN BK_BOOKING_ROOM_AMOUNT BR ON BR.BOOKING_ITEM_CODE = B.BOOKING_ITEM_CODE
INNER JOIN VD_VENDOR_ROOM_PLAN R ON B.HOTEL_CONTRACT_SEQ = R.HOTEL_CONTRACT_SEQ AND R.ROOM_PLAN_SEQ = (SUBSTRING_INDEX(SUBSTRING_INDEX(B.ROOM_PLAN_SEQ, '|',1),'^',1) + 0)
INNER JOIN VD_VENDOR_HOTEL_CONTRACT C ON R.HOTEL_CONTRACT_SEQ = C.HOTEL_CONTRACT_SEQ AND C.HOTEL_CONTRACT_TYPE_CODE = 'HOC01'
INNER JOIN VD_VENDOR_ROOM_TYPE T ON R.VENDOR_ROOM_TYPE_SEQ = T.VENDOR_ROOM_TYPE_SEQ AND T.HARD_BLOCK_YN = 'Y'
WHERE -- date(B.FIRST_INSERT_DATETIME) < date(B.CHECK_IN_DATE) -- remove booking un-sold
DATE IN (SELECT DATE_ADD(date(@checkInFrom), INTERVAL ID DAY) AS GEN_DATE FROM Z_ZID WHERE ID < 31)
-- AND B.BOOKING_ITEM_CODE = 'K25050710062H01'
AND B.HOTEL_CODE = 739324
-- AND T.VENDOR_ROOM_TYPE_SEQ = 1274597
-- AND B.BOOKING_STATUS_CODE IN ('BKS03', 'BKS06')
GROUP BY B.HOTEL_CONTRACT_SEQ, T.VENDOR_ROOM_TYPE_SEQ, BR.APPLIED_DATE

) RS ON AL.VENDOR_ROOM_TYPE_SEQ  = RS.VENDOR_ROOM_TYPE_SEQ AND AL.APPLIED_DATE = RS.APPLIED_DATE
WHERE AL.HOTEL_CONTRACT_SEQ = RS.HOTEL_CONTRACT_SEQ AND  AL.LEFT_COUNT_SUM <> AL.ALLOTMENT_COUNT_SUM - RS.CONFIRM_COUNT
ORDER BY RS.APPLIED_DATE;

Follow up to get more cases and investigation

Hi a Joseph Vo, apologise for my late response on the ticket.

The test result of the ticket was mentioned from the resolved one http://redmine.ohmytrip.com/issues/495#note-18

Hence I will change status of the ticket into Resolved

Thanks