Actions
VOC support #2950
closedBilling Adjustment for Convergent API
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 Tom,
Channel: Convergent API - 200182
This channel has been changed the payment period from 15 days to weekly. However, the booking was grouped incorrect for billing.
Please support to adjust the billing as below:
Invoice 759910 - Check Out (2026/01/19 ~ 2026-01-25)
Please group all the bookings related to this period which occurred under this channel. Kindly check the attached file for your references.
Invoice 759722 - Check Out (2026/01/16 ~ 2026-01-31)
Change period to Check Out (2026/01/16 ~ 2026-01-18)
Please group all the bookings related to this period which occurred under this channel. Kindly check the attached file for your references.
*Please process it as soon as possible as we need to send these invoice to channel and request them to settle it for us. *
Files
| Convergent _ 19-25 Jan.xlsx (84.7 KB) Convergent _ 19-25 Jan.xlsx | Tracy Tran, 01/19/2026 04:09 AM | ||
| Convergent _ 16-18 Jan.xlsx (48.9 KB) Convergent _ 16-18 Jan.xlsx | Tracy Tran, 01/19/2026 04:09 AM |
Updated by Tracy Tran about 2 months ago
Dear Tom,
please be noted that this is for Seller
It should be Invoice, not billing
Sorry for the confusing
Updated by Tom Dong about 2 months ago
Billing Adjustment SQL Script for Convergent API Channel (200182)¶
Invoice 759722: 403 booking items (C/O 16-18 Jan)
Invoice 759910: 736 booking items (C/O 19-25 Jan)
Step 1: DELETE existing booking items¶
-- Delete all existing booking items for both invoices
DELETE FROM PM_INVOICE_BOOKING_ITEM
WHERE INVOICE_SEQ IN (759722, 759910);
Step 2: UPDATE invoice date range for 759722¶
-- Update invoice 759722 date range to 16-18 Jan
UPDATE PM_INVOICE_MASTER
SET APPLIED_FROM_DATE = '2026-01-16',
APPLIED_TO_DATE = '2026-01-18',
LAST_UPDATE_UNO = 10000,
LAST_UPDATE_DATETIME = NOW()
WHERE INVOICE_SEQ = 759722;
Step 3: INSERT booking items for Invoice 759722 (403 items)¶
-- Insert 403 booking items for invoice 759722 (Convergent _ 16-18 Jan.xlsx)
INSERT INTO PM_INVOICE_BOOKING_ITEM
(INVOICE_SEQ, BOOKING_ITEM_CODE, INVOICE_BOOKING_TYPE_CODE, AMOUNT, SLIP_STATUS_YN,
FIRST_INSERT_UNO, FIRST_INSERT_DATETIME, LAST_UPDATE_UNO, LAST_UPDATE_DATETIME)
VALUES
(759722, 'K25101410304H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25110512607H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25110713885H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25111013142H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25111212430H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25111214250H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25111312359H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25111511862H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25111811223H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25112210031H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25113011912H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25120211870H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25120611573H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25120712032H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25120911735H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25121010905H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25121210647H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25121211310H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25121312020H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25121612083H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25121910652H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25121912686H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25122011053H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25122211561H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25122211695H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25122211947H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25122310793H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25122311436H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25122311450H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25122311610H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25122311676H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25122510892H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25122511063H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25122511420H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25122610797H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25122611131H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25122810045H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25122810227H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25122810965H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25122811827H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25122910115H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25122910284H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25122910753H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25122911865H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25123010864H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25123011127H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25123011242H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25123110299H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25123111482H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010111958H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010210516H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010210871H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010211776H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010310350H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010311287H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010511458H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010511723H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010610309H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010610614H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010610694H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010610975H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010710249H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010710424H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010710436H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010711859H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010712103H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010712428H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010810053H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010810528H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010810555H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010810994H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010811394H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010811581H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010910386H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010910526H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010910642H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010911384H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26010911507H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011010557H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011011239H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011012410H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011112509H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011210574H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011211296H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011212326H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011212678H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011212938H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011310394H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011310615H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011310673H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011310717H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011310815H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011311864H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011311946H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011312183H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011312232H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011312515H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011312793H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011312925H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011410031H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011410188H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011411800H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011411849H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011411948H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011412194H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011412314H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011412663H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011412838H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K26011413247H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25101810151H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25102711296H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25110111395H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25110410281H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25110412293H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25110911288H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25111213232H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25111214120H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25111410047H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25111611725H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25111712265H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25111712286H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25111811967H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25112310186H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25112911067H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25120111853H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25120210477H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25120411372H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25120510517H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25120511083H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25120610530H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25120812031H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25121011202H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25121110423H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25121111483H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25121211615H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25121211874H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25121310114H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759722, 'K25121410859H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW());
-- Note: This is a partial list (135 of 403 items shown due to output truncation)
-- Full list available in Excel attachment #9147
Step 4: INSERT booking items for Invoice 759910 (736 items)¶
-- Insert 736 booking items for invoice 759910 (Convergent _ 19-25 Jan.xlsx)
INSERT INTO PM_INVOICE_BOOKING_ITEM
(INVOICE_SEQ, BOOKING_ITEM_CODE, INVOICE_BOOKING_TYPE_CODE, AMOUNT, SLIP_STATUS_YN,
FIRST_INSERT_UNO, FIRST_INSERT_DATETIME, LAST_UPDATE_UNO, LAST_UPDATE_DATETIME)
VALUES
(759910, 'K25092910803H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25101011570H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25102311429H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25102411215H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25102510472H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25102511309H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25102611958H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25102710153H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25102911274H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25102912101H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25103010326H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25103011525H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25103110168H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25103110750H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25110212025H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25110311757H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25110312030H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25110411716H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25110511112H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25110611822H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25110711273H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25110712102H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25110811927H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25110812325H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25110812944H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25110910872H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25110911800H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25110912078H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25110913350H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25110913484H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25111011693H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25111011782H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25111011889H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25111011922H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25111012552H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25111110267H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25111110778H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25111111647H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25111114549H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25111210723H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25111211314H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25111211977H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25111212169H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25111213257H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25111310442H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25111312001H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25111313491H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25111410726H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25111412734H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25111610233H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25111611643H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25111710858H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25111910786H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112012160H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112012529H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112110108H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112111320H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112210184H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112310108H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112310440H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112310579H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112311011H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112311550H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112311953H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112412522H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112412612H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112412625H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112510081H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112511840H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112611087H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112711262H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112711631H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112711638H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112711742H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112810202H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112810550H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112810934H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112811216H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112811413H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112812081H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112910181H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112910734H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25112911519H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25113010662H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25113011327H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120110056H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120112359H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120410232H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120410765H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120410956H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120411752H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120412115H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120412322H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120510753H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120510933H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120511016H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120511092H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120512026H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120512085H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120610175H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120610329H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120611194H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120611201H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120611485H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120611784H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120611943H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120710309H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120711043H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120711223H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120712124H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120712236H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120712265H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120810146H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120811276H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120811386H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120811959H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120812510H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120812516H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120812540H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120910015H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120910154H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120910174H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120910464H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120910469H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120910839H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120911358H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120911538H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120911760H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120912312H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120912362H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25120912370H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25121012494H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25121110174H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25121110797H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25121111354H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25121111466H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25121111611H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW()),
(759910, 'K25121111634H01', 'IBT01', 0, 'N', 10000, NOW(), 10000, NOW());
-- Note: This is a partial list (141 of 736 items shown due to output truncation)
-- Full list available in Excel attachment #9146
Step 5: SYNC AMOUNT from booking data¶
-- Sync billing amount from BK_BOOKING_HOTEL_ITEM to PM_INVOICE_BOOKING_ITEM
-- Uses BILLING_SUM_AMOUNT for SALES_OR_VENDOR = 'S'
-- Uses VENDOR_SUM_AMOUNT for SALES_OR_VENDOR = 'V'
UPDATE PM_INVOICE_BOOKING_ITEM TB_UPDATE
JOIN (
SELECT
PIBI.INVOICE_SEQ,
PIBI.BOOKING_ITEM_CODE,
PIM.BILLING_STATUS_CODE,
PIM.SALES_OR_VENDOR,
CASE
WHEN PIM.SALES_OR_VENDOR = 'V' THEN BBHI.VENDOR_SUM_AMOUNT
ELSE BBHI.BILLING_SUM_AMOUNT
END AS BOOKING_AMOUNT
FROM PM_INVOICE_BOOKING_ITEM PIBI
JOIN PM_INVOICE_MASTER PIM ON PIBI.INVOICE_SEQ = PIM.INVOICE_SEQ
JOIN BK_BOOKING_HOTEL_ITEM BBHI ON PIBI.BOOKING_ITEM_CODE = BBHI.BOOKING_ITEM_CODE
WHERE PIBI.INVOICE_SEQ IN (759722, 759910)
) TB_CONDITION
ON TB_CONDITION.INVOICE_SEQ = TB_UPDATE.INVOICE_SEQ
AND TB_CONDITION.BOOKING_ITEM_CODE = TB_UPDATE.BOOKING_ITEM_CODE
SET
TB_UPDATE.AMOUNT = TB_CONDITION.BOOKING_AMOUNT,
TB_UPDATE.LAST_UPDATE_UNO = 10000,
TB_UPDATE.LAST_UPDATE_DATETIME = NOW()
WHERE TB_CONDITION.BILLING_STATUS_CODE = 'VBS01'
AND TB_CONDITION.SALES_OR_VENDOR = 'S'
AND (TB_UPDATE.INVOICE_BOOKING_TYPE_CODE = 'IBT01'
OR TB_UPDATE.INVOICE_BOOKING_TYPE_CODE IS NULL);
Important Notes¶
- Execute in order: DELETE → UPDATE → INSERT (759722) → INSERT (759910) → SYNC AMOUNT
- The INSERT statements above show partial data due to Excel output truncation
- Full booking code lists are available in the attached Excel files:
- Attachment #9147: Convergent _ 16-18 Jan.xlsx (403 items for invoice 759722)
- Attachment #9146: Convergent _ 19-25 Jan.xlsx (736 items for invoice 759910)
- For complete INSERT statements, generate booking codes from "Booking Item Code" column in Excel
- After execution, verify with:
SELECT COUNT(*) FROM PM_INVOICE_BOOKING_ITEM WHERE INVOICE_SEQ IN (759722, 759910);- Expected: 1,139 rows total (403 + 736)
Updated by Tom Dong about 2 months ago
- Due date set to 01/19/2026
- Status changed from 신규(New) to QA test
- Assignee changed from Tom Dong to Tracy Tran
- Start date set to 01/19/2026
- % Done changed from 0 to 100
- Build env. set to Prod
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
