VOC

VOC Issue Billing - SQL Script¶

SQL Script:¶

-- =====================================================
-- VOC ISSUE BILLING - Task 2729
-- Booking: V25112110087H01 (Wyndham Soleil Da Nang)
-- =====================================================

-- 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
    ('2025-12-26', '2025-12-29', 'V25112110087H01', 1001398, 604951, '1057631599', 'VND', '2025-12-26');

-- Step 3: INSERT PM_INVOICE_MASTER (join with encrypted bank account)
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 invoiceAmount,
    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;

• SALES_OR_VENDOR = 'V' cho Vendor Billing
• Bank account được encrypt - SQL dùng AES_DECRYPT để join
• FIRST_INSERT_UNO = 90049 là system user cho VOC
• BILLING_STATUS_CODE = 'VBS01' = New billing status

Result¶

Status: SUCCESS

Billing No: 758238

Đã issue billing thành công cho booking V25112110087H01.