VOC

SQL Update Script for K25091110805H01 (Sejong Hotel)

1. Changes Applied:

   Field	Current	New Value
   -------	---------	-----------
   VCC Status	Approved (CCS02)	Fully Cancelled (CCS04)
   V Payment	Fully Paid (VPS03)	Not Paid (VPS01)
   Vendor Add Amt	0	-1,610,000
   Vendor Sum Amt	1,800,000	190,000
   Vendor Balance	0	190,000
   Vendor Revenue Amt	-1,604,300	5,700
   Billing Revenue	-1,604,300	5,700

STEP 1: Verify Current Data

SELECT 
    BOOKING_ITEM_CODE,
    VENDOR_PAYMENT_STATUS_CODE,
    VENDOR_ADD_AMOUNT,
    VENDOR_SUM_AMOUNT,
    VENDOR_BALANCE_AMOUNT,
    VENDOR_REVENUE_AMOUNT,
    BILLING_REVENUE_AMOUNT
FROM BK_BOOKING_HOTEL_ITEM
WHERE BOOKING_ITEM_CODE = 'K25091110805H01';

SELECT 
    PAYMENT_SEQ,
    BOOKING_ITEM_CODE,
    CARD_PAYMENT_STATUS_CODE,
    PAID_AMOUNT,
    DEPOSIT_TYPE_CODE
FROM PM_PAYMENT_INFO
WHERE BOOKING_ITEM_CODE = 'K25091110805H01';

STEP 2: Update BK_BOOKING_HOTEL_ITEM

UPDATE BK_BOOKING_HOTEL_ITEM
SET 
    VENDOR_PAYMENT_STATUS_CODE = 'VPS01',
    VENDOR_ADD_AMOUNT = -1610000,
    VENDOR_SUM_AMOUNT = 190000,
    VENDOR_BALANCE_AMOUNT = 190000,
    VENDOR_REVENUE_AMOUNT = 5700,
    BILLING_REVENUE_AMOUNT = 5700,
    LAST_UPDATE_UNO = 10000,
    LAST_UPDATE_DATETIME = NOW()
WHERE BOOKING_ITEM_CODE = 'K25091110805H01'
AND VENDOR_PAYMENT_STATUS_CODE = 'VPS03';

STEP 3: Update PM_PAYMENT_INFO (VCC Status)

UPDATE PM_PAYMENT_INFO
SET 
    CARD_PAYMENT_STATUS_CODE = 'CCS04',
    LAST_UPDATE_UNO = 10000,
    LAST_UPDATE_DATETIME = NOW()
WHERE BOOKING_ITEM_CODE = 'K25091110805H01'
AND CARD_PAYMENT_STATUS_CODE = 'CCS02';

ROLLBACK (If Needed)

UPDATE BK_BOOKING_HOTEL_ITEM
SET 
    VENDOR_PAYMENT_STATUS_CODE = 'VPS03',
    VENDOR_ADD_AMOUNT = 0,
    VENDOR_SUM_AMOUNT = 1800000,
    VENDOR_BALANCE_AMOUNT = 0,
    VENDOR_REVENUE_AMOUNT = -1604300,
    BILLING_REVENUE_AMOUNT = -1604300,
    LAST_UPDATE_UNO = 10000,
    LAST_UPDATE_DATETIME = NOW()
WHERE BOOKING_ITEM_CODE = 'K25091110805H01';

UPDATE PM_PAYMENT_INFO
SET 
    CARD_PAYMENT_STATUS_CODE = 'CCS02',
    LAST_UPDATE_UNO = 10000,
    LAST_UPDATE_DATETIME = NOW()
WHERE BOOKING_ITEM_CODE = 'K25091110805H01';

checked