Actions
VOC support #3137
closedREQUEST FOR ISSUE BILLING BOOKINGS - PANDANUS RESORT FEB 2026
Added by Tracy Tran 25 days ago. Updated 13 days ago.
Start date:
02/25/2026
Due date:
02/25/2026
% Done:
100%
Estimated time:
Part:
Build env.:
Prod
Description
Dear Team,
Please support to issue billing for these bookings as attached file due to VNAC team request.
Payment method has been changed.
Files
| Pandanus 02.26.xlsx (12.3 KB) Pandanus 02.26.xlsx | Tracy Tran, 02/13/2026 04:59 AM |
Updated by Tom Dong 13 days ago
-- =====================================================
-- VOC #3137 - ISSUE BILLING - PANDANUS RESORT FEB 2026
-- Hotel: 179835 | Vendor: 600715 | Currency: VND
-- Total: 70 bookings
-- =====================================================
-- Step 1: Clear temp table
DELETE FROM temp_voc_issue_billing WHERE BillingNo IS NOT NULL;
-- Step 2: Insert data into temp table
INSERT INTO temp_voc_issue_billing (AppliedFromDate, AppliedToDate, BookingItemCode, HotelCode, VendorCode, BankAccountNumber, Currency, DepositDueDate)
VALUES
('2026-01-20', '2026-02-12', 'K25120810669H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K25121511871H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K25122811635H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26010411295H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26011010808H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26011111118H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26010310188H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26010410620H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26010912244H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26010310668H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26010311828H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K25121911623H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K25123111246H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26010311318H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26010312080H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26010510261H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26010511180H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26010910880H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K25121210080H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'V25122110045H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K25123111237H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26010312079H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26010510981H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26010812790H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'V25122510056H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'V25122510093H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'V25122710062H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'V26010810090H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K25121311213H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'V26010410061H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26012011984H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'V26012510098H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'V26012510100H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K25121910358H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'V26010310042H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'V26012210011H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26012213121H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'V26012310047H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26012410787H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26012410803H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K25120911399H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'V25121710040H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K25122512130H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K25123010730H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K25123010780H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'V26010310057H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26010511043H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26010612093H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'V26010610036H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26010710431H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26011110550H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26012911516H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'V26013110084H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'V26020210019H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26011912885H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'V26010410014H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26011111776H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26012911764H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'V25121310064H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'V25122410105H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26010411226H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26010712322H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26010712343H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26010810110H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26011211750H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'V26011210114H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26011313261H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26011912827H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26012611599H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12'),
('2026-01-20', '2026-02-12', 'K26020410877H01', 179835, 600715, '0071002978730', 'VND', '2026-02-12');
-- Step 3: Insert PM_INVOICE_MASTER (with encrypted bank account join)
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, REMIT_DATE, BILLING_STATUS_CODE,
CONTROL_REMARK, FIRST_INSERT_UNO, FIRST_INSERT_DATETIME, LAST_UPDATE_UNO, LAST_UPDATE_DATETIME, ADJUSTMENT_AMOUNT
)
SELECT DISTINCT
'V' AS SaleOrVendor,
tvib.VendorCode,
tvib.VendorCode,
'I' AS CheckInOrOut,
tvib.AppliedFromDate,
tvib.AppliedToDate,
tvib.Currency,
0 AS invocieAmount,
0 AS PaidAmount,
0 AS BalanceAmount,
ucba.BANK_ACCOUNT_SEQ AS BankAccountSeq,
NOW(),
tvib.DepositDueDate,
NULL AS RemitDate,
'VBS01',
NULL AS ControlRemark,
90049 AS FirstInsertUno,
NOW(),
90049 AS LastUpdateUno,
NOW(),
NULL AS AdjustmentAmount
FROM temp_voc_issue_billing tvib
JOIN US_COMP_BANK_ACCOUNT ucba ON tvib.BankAccountNumber = CAST(AES_DECRYPT(UNHEX(ucba.BANK_ACCOUNT_NO),'b3d0a62f-a5ba-406d-b150-cb3eb62268bc') AS char)
AND tvib.VendorCode = ucba.COMP_CODE
AND ucba.USE_YN = 'Y'
WHERE tvib.BillingNo IS NULL;
-- Step 4: Update temp table with BillingNo
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 5: Insert PM_INVOICE_BOOKING_ITEM
INSERT INTO PM_INVOICE_BOOKING_ITEM (INVOICE_SEQ, BOOKING_ITEM_CODE, FIRST_INSERT_UNO, LAST_UPDATE_UNO)
SELECT
tvib.BillingNo,
tvib.BookingItemCode,
90049 AS FirstInsertUno,
90049 AS LastUpdateUno
FROM temp_voc_issue_billing tvib
WHERE tvib.BillingNo IS NOT NULL
ON DUPLICATE KEY UPDATE LAST_UPDATE_DATETIME = NOW();
-- Step 6: Update invoice amounts
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 PM_INVOICE_MASTER.SALES_OR_VENDOR = 'V'
AND PM_INVOICE_MASTER.INVOICE_SEQ IN (SELECT tvib.BillingNo FROM temp_voc_issue_billing tvib WHERE tvib.BillingNo IS NOT NULL);
-- Step 7: Verify result
SELECT * FROM temp_voc_issue_billing tvib;
| Field | Value |
|---|---|
| Hotel | Pandanus Resort (179835) |
| Vendor | 600715 |
| Bank Account | 0071002978730 |
| Currency | VND |
| Applied Period | 2026-01-20 ~ 2026-02-12 |
| Deposit Due Date | 2026-02-12 |
| Total Bookings | 70 |
Updated by Tracy Tran 13 days ago
- Status changed from QA test to 완료성공(Resolve)
- Assignee changed from Tracy Tran to Tom Dong
sent to VNAC
Actions
