VOC support #2941: Re: [OHMYHOTELVN] GIT Quotation Request – 35 Pax Korean group (Twin) | Check-in Thu, 8 Jan 2026 - Intercontinental Hotel Nha Trang - VOC - Redmine

Actions

Copy link

VOC support #2941

closed

Re: [OHMYHOTELVN] GIT Quotation Request – 35 Pax Korean group (Twin) | Check-in Thu, 8 Jan 2026 - Intercontinental Hotel Nha Trang

Added by Tracy Tran about 2 months ago. Updated about 2 months ago.

Status:

완료성공(Resolve)

Priority:

보통(Normal)

Assignee:

Tom Dong

Target version:

v1.0.0

Start date:

01/19/2026

Due date:

01/19/2026

% Done:

100%

Estimated time:

Part:

Build env.:

Prod

Description

Dear VOC,

GIT below at InterContinental Nha Trang has completed their stay.

Please assist us with the following:

Issue billing for the bookings as per the attached file, as our accounting team is unable to proceed due to the current hotel's payment method being set up as Prepay/VCC.

Thank you very much for your support!

Should you need any further information, kindly let me know.
Best Regards!

Files

INTERCON GIT KR JAN 26.xlsx (24.5 KB) INTERCON GIT KR JAN 26.xlsx

Tracy Tran, 01/16/2026 08:24 AM

Actions

Copy link

Updated by ziniy Kang about 2 months ago

Related to 기능(Feature) #2570: * ALIBABA CLOUD added

Actions

Copy link

Updated by ziniy Kang about 2 months ago

Related to deleted (기능(Feature) #2570: * ALIBABA CLOUD)

Actions

Copy link

Updated by Tom Dong about 2 months ago

Due date set to 01/19/2026
Status changed from 신규(New) to 진행(Doing)
Start date set to 01/19/2026

Actions

Copy link

Updated by Tom Dong about 2 months ago

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

Summary¶

Field	Value
Hotel	InterContinental Nha Trang (Code: 654455)
Vendor Code	603864
Stay Period	2026-01-08 to 2026-01-10
Total Bookings	18 items
Currency	VND
Bank Account	0061005686868
Deposit Due Date	2026-01-16

Booking Item Codes (18 total)¶

K25112611494H01
K25112611497H01
K25112611502H01
K25112611506H01
K25112611513H01
K25112611516H01
K25112611517H01
K25112611519H01
K25112611527H01
K25112611530H01
K25112611532H01
K25112611533H01
K25112611535H01
K25112611540H01
K25112611542H01
K25112611544H01
K25112611547H01
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;