VOC

-- =====================================================
-- VOC #2833: Amend buying rate K25123011224H01
-- Hotel: Hotel Park Habio
-- Reason: Hotel agreed free cancellation for 1 of 2 rooms
-- Original VCC cancelled by KRAC
-- =====================================================

-- Step 1: Update BK_BOOKING_HOTEL_ITEM
UPDATE BK_BOOKING_HOTEL_ITEM
SET 
    VENDOR_PAYMENT_STATUS_CODE = 'VPS01',  -- Not Paid (was VPS03 Fully Paid)
    VENDOR_ADD_AMOUNT = -94001,            -- was 0.00
    VENDOR_SUM_AMOUNT = 94001,             -- was 188002
    VENDOR_BALANCE_AMOUNT = 94001,         -- was 0.00
    VENDOR_REVENUE_AMOUNT = 102436,        -- was -89714
    BILLING_REVENUE_AMOUNT = 11241,        -- was -9845
    LAST_UPDATE_UNO = 10000,
    LAST_UPDATE_DATETIME = NOW()
WHERE BOOKING_ITEM_CODE = 'K25123011224H01'
  AND VENDOR_PAYMENT_STATUS_CODE = 'VPS03'
LIMIT 1;

-- Step 2: Update PM_PAYMENT_INFO (VCC Status)
UPDATE PM_PAYMENT_INFO
SET 
    CARD_PAYMENT_STATUS_CODE = 'CCS04',    -- Fully Cancelled (was CCS02 Approved)
    APPROVAL_OR_CANCEL = 'C',              -- Cancelled (was 'A' Approved)
    LAST_UPDATE_UNO = 10000,
    LAST_UPDATE_DATETIME = NOW()
WHERE BOOKING_ITEM_CODE = 'K25123011224H01'
  AND DEPOSIT_TYPE_CODE = 'CCVP'
  AND PAYMENT_SEQ = 3553678
  AND CARD_PAYMENT_STATUS_CODE = 'CCS02'
LIMIT 1;

Rollback SQL¶

-- Rollback Step 1: BK_BOOKING_HOTEL_ITEM
UPDATE BK_BOOKING_HOTEL_ITEM
SET 
    VENDOR_PAYMENT_STATUS_CODE = 'VPS03',
    VENDOR_ADD_AMOUNT = 0.00,
    VENDOR_SUM_AMOUNT = 188002,
    VENDOR_BALANCE_AMOUNT = 0.00,
    VENDOR_REVENUE_AMOUNT = -89714,
    BILLING_REVENUE_AMOUNT = -9845,
    LAST_UPDATE_UNO = 10000,
    LAST_UPDATE_DATETIME = NOW()
WHERE BOOKING_ITEM_CODE = 'K25123011224H01'
LIMIT 1;

-- Rollback Step 2: PM_PAYMENT_INFO
UPDATE PM_PAYMENT_INFO
SET 
    CARD_PAYMENT_STATUS_CODE = 'CCS02',
    APPROVAL_OR_CANCEL = 'A',
    LAST_UPDATE_UNO = 10000,
    LAST_UPDATE_DATETIME = NOW()
WHERE BOOKING_ITEM_CODE = 'K25123011224H01'
  AND DEPOSIT_TYPE_CODE = 'CCVP'
  AND PAYMENT_SEQ = 3553678
LIMIT 1;