Project

General

Profile

Actions
Copy link

VOC support #2950

closed

Billing Adjustment for Convergent API

Added by Tracy Tran about 2 months ago. Updated about 2 months ago.

Status:
완료성공(Resolve)
Priority:
긴급(Emergency)
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 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

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

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

Actions
Copy link
 #2

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

  1. Execute in order: DELETE → UPDATE → INSERT (759722) → INSERT (759910) → SYNC AMOUNT
  2. The INSERT statements above show partial data due to Excel output truncation
  3. 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)
  4. For complete INSERT statements, generate booking codes from "Booking Item Code" column in Excel
  5. After execution, verify with: SELECT COUNT(*) FROM PM_INVOICE_BOOKING_ITEM WHERE INVOICE_SEQ IN (759722, 759910);
    • Expected: 1,139 rows total (403 + 736)
Actions
Copy link
 #3

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
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)