VOC

VOC Issue Billing - SQL Script¶

Booking Details:¶

Issue:¶

The system normally cannot issue billing with negative amounts. This booking has VENDOR_SUM_AMOUNT = -12,895 JPY.

SQL to Issue Billing:¶

-- =====================================================
-- VOC ISSUE BILLING for K26012111843H01 (Negative Amount)
-- Task: #2987
-- =====================================================

-- Step 1: Clear temp table
DELETE FROM temp_voc_issue_billing WHERE BillingNo IS NOT NULL;

-- Step 2: Insert booking data into temp table
INSERT INTO temp_voc_issue_billing (AppliedFromDate, AppliedToDate, BookingItemCode, HotelCode, VendorCode, BankAccountNumber, Currency, DepositDueDate) 
VALUES
    ('2026-01-01', '2026-01-31', 'K26012111843H01', 687288, 601769, '普通　3463210', 'JPY', '2026-02-28');

-- Step 3: Insert PM_INVOICE_MASTER
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,
    121741 AS BankAccountSeq,
    NOW(),
    tvib.DepositDueDate,
    NULL AS RemitDate,
    'VBS01',
    'VOC #2987 - Issue billing for negative amount' AS ControlRemark,
    90049 AS FirstInsertUno,
    NOW(),
    90049 AS LastUpdateUno,
    NOW(),
    NULL AS AdjustmentAmount
FROM temp_voc_issue_billing tvib
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 (will set negative amount -12,895 JPY)
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;

SELECT pim.INVOICE_SEQ, pim.INVOICE_AMOUNT, pim.BALANCE_AMOUNT, pim.BILLING_STATUS_CODE
FROM PM_INVOICE_MASTER pim
JOIN PM_INVOICE_BOOKING_ITEM pibi ON pim.INVOICE_SEQ = pibi.INVOICE_SEQ
WHERE pibi.BOOKING_ITEM_CODE = 'K26012111843H01';

Notes:¶

• This booking has a negative amount (-12,895 JPY) which the system normally blocks
• The SQL manually inserts the billing record, bypassing the UI validation
• Bank Account Seq 121741 is used (the active account for vendor 601769)
• Applied period: 2026-01-01 to 2026-01-31, Deposit due: 2026-02-28