VOC

SQL Script for VOC #2597:

```sql
-- =====================================================
-- VOC #2597: Change payment status for K25121010894H01
-- Reason: Hotel charged and cancelled this settle
-- Author: Tom Dong
-- Date: 2025-12-11
-- =====================================================

-- Step 1: Update Vendor Payment Status to Not Paid
UPDATE BK_BOOKING_HOTEL_ITEM
SET VENDOR_PAYMENT_STATUS_CODE = 'VPS01', -- Not Paid
VENDOR_BALANCE_AMOUNT = 7620000.00, -- Vendor Balance: 7,620,000
LAST_UPDATE_UNO = 10000,
LAST_UPDATE_DATETIME = NOW
WHERE BOOKING_ITEM_CODE = 'K25121010894H01'
AND VENDOR_PAYMENT_STATUS_CODE = 'VPS03'; -- Safety: only if currently Fully Paid

-- Step 2: Update VCC Status to Fully Cancelled
UPDATE PM_PAYMENT_INFO
SET CARD_PAYMENT_STATUS_CODE = 'CCS04', -- Fully Cancelled
APPROVAL_OR_CANCEL = 'C', -- C = Cancel
LAST_UPDATE_UNO = 10000,
LAST_UPDATE_DATETIME = NOW
WHERE PAYMENT_SEQ = 3545537
AND BOOKING_ITEM_CODE = 'K25121010894H01'
AND CARD_PAYMENT_STATUS_CODE = 'CCS02'; -- Safety: only if currently Approved

-- =====================================================
-- ROLLBACK SCRIPT (in case of error)
-- =====================================================
/*
-- Rollback Step 1: Restore original values
UPDATE BK_BOOKING_HOTEL_ITEM
SET VENDOR_PAYMENT_STATUS_CODE = 'VPS03',
VENDOR_BALANCE_AMOUNT = 0.00,
LAST_UPDATE_UNO = 10000,
LAST_UPDATE_DATETIME = NOW
WHERE BOOKING_ITEM_CODE = 'K25121010894H01';

-- Rollback Step 2: Restore VCC status
UPDATE PM_PAYMENT_INFO
SET CARD_PAYMENT_STATUS_CODE = 'CCS02',
APPROVAL_OR_CANCEL = 'A', -- A = Approval
LAST_UPDATE_UNO = 10000,
LAST_UPDATE_DATETIME = NOW
WHERE PAYMENT_SEQ = 3545537;
*/

-- =====================================================
-- VERIFICATION QUERY (run after update)
-- =====================================================
SELECT
bhi.BOOKING_ITEM_CODE,
bhi.VENDOR_PAYMENT_STATUS_CODE AS V_PAYMENT,
bhi.VENDOR_BALANCE_AMOUNT AS VENDOR_BALANCE,
pi.PAYMENT_SEQ,
pi.CARD_PAYMENT_STATUS_CODE AS VCC_STATUS,
pi.APPROVAL_OR_CANCEL
FROM BK_BOOKING_HOTEL_ITEM bhi
LEFT JOIN PM_PAYMENT_INFO pi ON bhi.BOOKING_ITEM_CODE = pi.BOOKING_ITEM_CODE AND pi.SALES_OR_VENDOR = 'V'
WHERE bhi.BOOKING_ITEM_CODE = 'K25121010894H01';
```