VOC support #3137: REQUEST FOR ISSUE BILLING BOOKINGS - PANDANUS RESORT FEB 2026 - VOC - Redmine

Actions

Copy link

VOC support #3137

closed

REQUEST FOR ISSUE BILLING BOOKINGS - PANDANUS RESORT FEB 2026

Added by Tracy Tran 25 days ago. Updated 13 days ago.

Status:

완료성공(Resolve)

Priority:

보통(Normal)

Assignee:

Tom Dong

Target version:

v1.0.0

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

Actions

Copy link

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