Project

General

Profile

Actions
Copy link

VOC support #2939

closed

REQUEST FOR ISSUE BILLING BOOKINGS - PANDANUS RESORT DEC 2025

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,

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
Actions
Copy link
 #1

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
 #2

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)

  1. K25120411709H01
  2. K25121112811H01
  3. K25121112822H01
  4. K25121210161H01
  5. K25121211921H01
  6. K25121310006H01
  7. K25121310882H01
  8. K25121010161H01
  9. K25112111936H01
  10. V25120310105H01
  11. K25121110346H01
  12. K25110713911H01
  13. K25121511455H01
  14. K25121910832H01
  15. K25121910876H01
  16. K25121912283H01
  17. K25122011425H01
  18. K25110410579H01
  19. K25110512775H01
  20. V25110710009H01
  21. V25110710013H01
  22. K25110612781H01
  23. K25112012586H01
  24. V25110110043H01
  25. K25122310896H01
  26. V25110510075H01
  27. K25101211415H01
  28. K25102811695H01
  29. V25110310043H01
  30. V25111310077H01
  31. V25120410074H01
  32. K25120912327H01
  33. K25121210067H01
  34. V25121210056H01
  35. K25122211971H01
  36. K25120412455H01
  37. K25121310386H01
  38. K25121310912H01
  39. V25122310004H01
  40. V25122310054H01
  41. 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;
Actions
Copy link
 #3

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

Actions
Copy link
 #4

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
Copy link

Also available in: Atom PDF

Add picture from clipboard (Maximum size: 50 MB)