VOC

VOC Analysis - Issue Billing for InterContinental Nha Trang GIT Bookings¶

Summary¶

Booking Item Codes (18 total)¶

1. K25112611494H01
2. K25112611497H01
3. K25112611502H01
4. K25112611506H01
5. K25112611513H01
6. K25112611516H01
7. K25112611517H01
8. K25112611519H01
9. K25112611527H01
10. K25112611530H01
11. K25112611532H01
12. K25112611533H01
13. K25112611535H01
14. K25112611540H01
15. K25112611542H01
16. K25112611544H01
17. K25112611547H01
18. K25121811255H01

SQL Script¶

-- Step 1: Prepare temp table with booking data
DELETE FROM temp_voc_issue_billing WHERE BillingNo IS NOT NULL;
INSERT INTO temp_voc_issue_billing (AppliedFromDate, AppliedToDate, BookingItemCode, HotelCode, VendorCode, BankAccountNumber, Currency, DepositDueDate)
VALUES
('2026-01-08', '2026-01-10', 'K25112611494H01', 654455, 603864, '0061005686868', 'VND', '2026-01-16'),
('2026-01-08', '2026-01-10', 'K25112611497H01', 654455, 603864, '0061005686868', 'VND', '2026-01-16'),
('2026-01-08', '2026-01-10', 'K25112611502H01', 654455, 603864, '0061005686868', 'VND', '2026-01-16'),
('2026-01-08', '2026-01-10', 'K25112611506H01', 654455, 603864, '0061005686868', 'VND', '2026-01-16'),
('2026-01-08', '2026-01-10', 'K25112611513H01', 654455, 603864, '0061005686868', 'VND', '2026-01-16'),
('2026-01-08', '2026-01-10', 'K25112611516H01', 654455, 603864, '0061005686868', 'VND', '2026-01-16'),
('2026-01-08', '2026-01-10', 'K25112611517H01', 654455, 603864, '0061005686868', 'VND', '2026-01-16'),
('2026-01-08', '2026-01-10', 'K25112611519H01', 654455, 603864, '0061005686868', 'VND', '2026-01-16'),
('2026-01-08', '2026-01-10', 'K25112611527H01', 654455, 603864, '0061005686868', 'VND', '2026-01-16'),
('2026-01-08', '2026-01-10', 'K25112611530H01', 654455, 603864, '0061005686868', 'VND', '2026-01-16'),
('2026-01-08', '2026-01-10', 'K25112611532H01', 654455, 603864, '0061005686868', 'VND', '2026-01-16'),
('2026-01-08', '2026-01-10', 'K25112611533H01', 654455, 603864, '0061005686868', 'VND', '2026-01-16'),
('2026-01-08', '2026-01-10', 'K25112611535H01', 654455, 603864, '0061005686868', 'VND', '2026-01-16'),
('2026-01-08', '2026-01-10', 'K25112611540H01', 654455, 603864, '0061005686868', 'VND', '2026-01-16'),
('2026-01-08', '2026-01-10', 'K25112611542H01', 654455, 603864, '0061005686868', 'VND', '2026-01-16'),
('2026-01-08', '2026-01-10', 'K25112611544H01', 654455, 603864, '0061005686868', 'VND', '2026-01-16'),
('2026-01-08', '2026-01-10', 'K25112611547H01', 654455, 603864, '0061005686868', 'VND', '2026-01-16'),
('2026-01-08', '2026-01-10', 'K25121811255H01', 654455, 603864, '0061005686868', 'VND', '2026-01-16');

-- Step 2: Insert Invoice Master (Vendor Billing)
INSERT INTO PM_INVOICE_MASTER (
    SALES_OR_VENDOR, ISSUE_COMP_CODE, TRADER_COMP_CODE, CHECK_IN_OR_OUT,
    APPLIED_FROM_DATE, APPLIED_TO_DATE, CURRENCY_CODE, INVOICE_AMOUNT, PAID_AMOUNT, BALANCE_AMOUNT,
    BANK_ACCOUNT_SEQ, ISSUE_DATETIME, DEPOSIT_DUE_DATE, BILLING_STATUS_CODE,
    FIRST_INSERT_UNO, FIRST_INSERT_DATETIME, LAST_UPDATE_UNO, LAST_UPDATE_DATETIME
)
SELECT DISTINCT 'V', tvib.VendorCode, tvib.VendorCode, 'I',
    tvib.AppliedFromDate, tvib.AppliedToDate, tvib.Currency, 0, 0, 0,
    ucba.BANK_ACCOUNT_SEQ, NOW(), tvib.DepositDueDate, 'VBS01',
    90049, NOW(), 90049, NOW()
FROM temp_voc_issue_billing tvib
JOIN US_COMP_BANK_ACCOUNT ucba
    ON tvib.BankAccountNumber = CAST(AES_DECRYPT(UNHEX(ucba.BANK_ACCOUNT_NO), @kmsKey) AS CHAR)
    AND tvib.VendorCode = ucba.COMP_CODE AND ucba.USE_YN = 'Y'
WHERE tvib.BillingNo IS NULL;

-- Step 3: Update temp table with generated Invoice SEQ
UPDATE temp_voc_issue_billing tvib
JOIN PM_INVOICE_MASTER pim
    ON tvib.VendorCode = pim.TRADER_COMP_CODE
    AND tvib.AppliedFromDate = pim.APPLIED_FROM_DATE
    AND tvib.AppliedToDate = pim.APPLIED_TO_DATE
    AND pim.FIRST_INSERT_UNO = 90049
    AND pim.SALES_OR_VENDOR = 'V'
    AND pim.BILLING_STATUS_CODE = 'VBS01'
SET tvib.BillingNo = pim.INVOICE_SEQ
WHERE tvib.BillingNo IS NULL;

-- Step 4: Link booking items to Invoice
INSERT INTO PM_INVOICE_BOOKING_ITEM (INVOICE_SEQ, BOOKING_ITEM_CODE, FIRST_INSERT_UNO, LAST_UPDATE_UNO)
SELECT tvib.BillingNo, tvib.BookingItemCode, 90049, 90049
FROM temp_voc_issue_billing tvib
WHERE tvib.BillingNo IS NOT NULL
ON DUPLICATE KEY UPDATE LAST_UPDATE_DATETIME = NOW();

-- Step 5: Update Invoice amounts based on linked bookings
UPDATE PM_INVOICE_MASTER SET
    INVOICE_AMOUNT = IFNULL((SELECT SUM(VENDOR_SUM_AMOUNT) FROM BK_BOOKING_HOTEL_ITEM
        JOIN PM_INVOICE_BOOKING_ITEM ON BK_BOOKING_HOTEL_ITEM.BOOKING_ITEM_CODE = PM_INVOICE_BOOKING_ITEM.BOOKING_ITEM_CODE
        WHERE PM_INVOICE_BOOKING_ITEM.INVOICE_SEQ = PM_INVOICE_MASTER.INVOICE_SEQ), 0),
    BALANCE_AMOUNT = IFNULL((SELECT SUM(VENDOR_SUM_AMOUNT) FROM BK_BOOKING_HOTEL_ITEM
        JOIN PM_INVOICE_BOOKING_ITEM ON BK_BOOKING_HOTEL_ITEM.BOOKING_ITEM_CODE = PM_INVOICE_BOOKING_ITEM.BOOKING_ITEM_CODE
        WHERE PM_INVOICE_BOOKING_ITEM.INVOICE_SEQ = PM_INVOICE_MASTER.INVOICE_SEQ), 0),
    LAST_UPDATE_UNO = 90049, LAST_UPDATE_DATETIME = NOW()
WHERE SALES_OR_VENDOR = 'V'
    AND INVOICE_SEQ IN (SELECT BillingNo FROM temp_voc_issue_billing WHERE BillingNo IS NOT NULL);

-- Verify Results
SELECT pim.INVOICE_SEQ, pim.TRADER_COMP_CODE, pim.INVOICE_AMOUNT, pim.BALANCE_AMOUNT, COUNT(pibi.BOOKING_ITEM_CODE) AS BOOKING_COUNT
FROM PM_INVOICE_MASTER pim
JOIN PM_INVOICE_BOOKING_ITEM pibi ON pim.INVOICE_SEQ = pibi.INVOICE_SEQ
WHERE pim.INVOICE_SEQ IN (SELECT BillingNo FROM temp_voc_issue_billing WHERE BillingNo IS NOT NULL)
GROUP BY pim.INVOICE_SEQ;