Actions
VOC support #2939
closedREQUEST FOR ISSUE BILLING BOOKINGS - PANDANUS RESORT DEC 2025
Added by Tracy Tran about 2 months ago. Updated about 2 months ago.
Start date:
01/19/2026
Due date:
01/19/2026
% Done:
100%
Estimated time:
Part:
Build env.:
Prod
Description
Dear VOC,
Please assist us in issuing a billing for these bookings as attached file.
Thank you for your support!
Files
| Pandanus 12.2025.xlsx (11.2 KB) Pandanus 12.2025.xlsx | Tracy Tran, 01/16/2026 06:33 AM |
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
Updated by Tom Dong about 2 months ago
VOC Analysis - Issue Billing for Pandanus Resort DEC 2025¶
Summary¶
| Field | Value |
|---|---|
| Hotel | Pandanus Resort (Code: 179835) |
| Vendor Code | 600715 |
| Applied Period | 2025-12-01 to 2026-01-12 |
| Total Bookings | 41 items |
| Currency | VND |
| Bank Account | 0071002978730 |
| Deposit Due Date | 2026-01-14 |
Booking Item Codes (41 total)¶
- K25120411709H01
- K25121112811H01
- K25121112822H01
- K25121210161H01
- K25121211921H01
- K25121310006H01
- K25121310882H01
- K25121010161H01
- K25112111936H01
- V25120310105H01
- K25121110346H01
- K25110713911H01
- K25121511455H01
- K25121910832H01
- K25121910876H01
- K25121912283H01
- K25122011425H01
- K25110410579H01
- K25110512775H01
- V25110710009H01
- V25110710013H01
- K25110612781H01
- K25112012586H01
- V25110110043H01
- K25122310896H01
- V25110510075H01
- K25101211415H01
- K25102811695H01
- V25110310043H01
- V25111310077H01
- V25120410074H01
- K25120912327H01
- K25121210067H01
- V25121210056H01
- K25122211971H01
- K25120412455H01
- K25121310386H01
- K25121310912H01
- V25122310004H01
- V25122310054H01
- K25122810485H01
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
('2025-12-01', '2026-01-12', 'K25120411709H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25121112811H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25121112822H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25121210161H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25121211921H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25121310006H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25121310882H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25121010161H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25112111936H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'V25120310105H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25121110346H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25110713911H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25121511455H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25121910832H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25121910876H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25121912283H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25122011425H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25110410579H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25110512775H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'V25110710009H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'V25110710013H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25110612781H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25112012586H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'V25110110043H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25122310896H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'V25110510075H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25101211415H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25102811695H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'V25110310043H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'V25111310077H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'V25120410074H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25120912327H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25121210067H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'V25121210056H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25122211971H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25120412455H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25121310386H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25121310912H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'V25122310004H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'V25122310054H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14'),
('2025-12-01', '2026-01-12', 'K25122810485H01', 179835, 600715, '0071002978730', 'VND', '2026-01-14');
-- 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;
Updated by Tom Dong about 2 months ago
- Status changed from 진행(Doing) to QA test
- Assignee changed from Tom Dong to Tracy Tran
- % Done changed from 0 to 100
- Build env. set to Prod
Billing No: 759915
Updated by Tracy Tran about 2 months ago
- Status changed from QA test to 완료성공(Resolve)
- Assignee changed from Tracy Tran to Tom Dong
checked
Actions
