|
1
|
CREATE TEMPORARY TABLE IF NOT EXISTS BK_TEMP AS
|
|
2
|
(
|
|
3
|
SELECT BBHI.BOOKING_ITEM_CODE AS BookingItemCode
|
|
4
|
, BBHI.HOTEL_CODE AS HotelCode
|
|
5
|
, BBHI.HOTEL_NAME_EN AS HotelName
|
|
6
|
, BBHI.CHECK_IN_DATE AS CheckInDate
|
|
7
|
, BBHI.CHECK_OUT_DATE AS CheckOutDate
|
|
8
|
, DATEDIFF(BBHI.CHECK_OUT_DATE , BBHI.CHECK_IN_DATE) AS NumberRoomNights
|
|
9
|
, BBHI.VENDOR_CURRENCY_CODE AS VendorCurrency
|
|
10
|
, ROUND(BBHI.VENDOR_SUM_AMOUNT, 2) AS VendorSumAmount
|
|
11
|
, ROUND(BBHI.VENDOR_REVENUE_AMOUNT, 2) AS VendorRevenueAmount
|
|
12
|
, BBM.BILLING_CURRENCY_CODE AS BillingCurrency
|
|
13
|
, ROUND(BBHI.BILLING_SUM_AMOUNT, 2) AS BillingSumAmount
|
|
14
|
, ROUND(BBHI.BILLING_REVENUE_AMOUNT, 2) AS BillingRevenueAmount
|
|
15
|
, HHM.COUNTRY_CODE AS HOTEL_COUNTRY_CODE
|
|
16
|
, BBHI.REGION_CODE
|
|
17
|
, IFNULL(BBHI.HOTEL_CONTRACT_TYPE_CODE, (
|
|
18
|
SELECT VHC.HOTEL_CONTRACT_TYPE_CODE
|
|
19
|
FROM VD_VENDOR_HOTEL_CONTRACT VHC
|
|
20
|
WHERE VHC.HOTEL_CONTRACT_SEQ = BBHI.HOTEL_CONTRACT_SEQ
|
|
21
|
LIMIT 1
|
|
22
|
)
|
|
23
|
) AS HOTEL_CONTRACT_TYPE_CODE
|
|
24
|
, BBHI.BOOKING_STATUS_CODE
|
|
25
|
, BBHI.CLIENT_PAYMENT_STATUS_CODE
|
|
26
|
, BBHI.VENDOR_PAYMENT_STATUS_CODE
|
|
27
|
, BBHI.DEVICE_TYPE_CODE
|
|
28
|
, BBM.STATION_TYPE_CODE
|
|
29
|
, BBHI.FIRST_INSERT_DATETIME
|
|
30
|
, BBHI.VENDOR_COMP_CODE
|
|
31
|
FROM BK_BOOKING_HOTEL_ITEM BBHI
|
|
32
|
INNER JOIN BK_BOOKING_MASTER BBM ON BBHI.BOOKING_CODE = BBM.BOOKING_CODE
|
|
33
|
INNER JOIN HO_HOTEL_MASTER HHM ON BBHI.HOTEL_CODE = HHM.HOTEL_CODE
|
|
34
|
LEFT JOIN VD_VENDOR_PLAN_PROMOTION P ON P.PLAN_PROMOTION_SEQ = BBHI.PLAN_PROMOTION_SEQ
|
|
35
|
WHERE 1 = 1
|
|
36
|
AND HHM.COUNTRY_CODE = 'VN'
|
|
37
|
-- AND BBHI.CHECK_OUT_DATE BETWEEN '20250101' AND '20250331'
|
|
38
|
-- AND BBHI.CHECK_OUT_DATE BETWEEN '20250401' AND '20250630'
|
|
39
|
-- AND BBHI.CHECK_OUT_DATE BETWEEN '20251001' AND '20251231'
|
|
40
|
AND BBHI.CHECK_OUT_DATE BETWEEN '20250101' AND '20251231'
|
|
41
|
AND BBHI.BOOKING_STATUS_CODE IN ('BKS02', 'BKS05')
|
|
42
|
-- AND BBHI.BOOKING_ITEM_CODE = '240324JJ04H01' -- '240430TT10H01' -- Empty ContractType
|
|
43
|
-- AND BBHI.BOOKING_ITEM_CODE = 'K25032611511H01'-- K25032611511H01 -- Empty PIC - Happy Life Grand Hotel & Sky Bar
|
|
44
|
-- AND BBHI.BOOKING_ITEM_CODE = 'K25091110409H01'-- K25091110409H01 -- Empty PIC & ContractType - Hotel Majestic Saigon
|
|
45
|
);
|
|
46
|
SELECT COUNTRY.COUNTRY_NAME_EN AS HotelCountry
|
|
47
|
, REGION.REGION_NAME_EN AS HotelRegion
|
|
48
|
, (
|
|
49
|
SELECT
|
|
50
|
GROUP_CONCAT(
|
|
51
|
CONCAT(
|
|
52
|
BDC.DETAIL_CODE_NAME_EN, ',',
|
|
53
|
UCP.PIC_UNO, '-',
|
|
54
|
UUM.NAME, (CASE WHEN DPT.COMP_BASIS_CODE_NAME_EN IS NOT NULL THEN ',' ELSE ''END),
|
|
55
|
IFNULL(DPT.COMP_BASIS_CODE_NAME_EN, ''), (CASE WHEN POS.COMP_BASIS_CODE_NAME_EN IS NOT NULL THEN ',' ELSE ''END),
|
|
56
|
IFNULL(POS.COMP_BASIS_CODE_NAME_EN, '')
|
|
57
|
)
|
|
58
|
ORDER BY UCP.PIC_TYPE_CODE
|
|
59
|
SEPARATOR ';'
|
|
60
|
) AS RESULT
|
|
61
|
FROM US_COMP_PIC UCP
|
|
62
|
JOIN US_USER_MASTER UUM
|
|
63
|
ON UCP.PIC_UNO = UUM.USER_NO
|
|
64
|
LEFT JOIN US_CONTROL_STAFF UCS
|
|
65
|
ON UUM.USER_NO = UCS.USER_NO
|
|
66
|
LEFT JOIN BS_DETAIL_CODE BDC
|
|
67
|
ON BDC.MASTER_CODE = 'US003'
|
|
68
|
AND BDC.DETAIL_CODE = UCP.PIC_TYPE_CODE
|
|
69
|
LEFT JOIN US_COMP_BASIS_CODE DPT
|
|
70
|
ON DPT.COMP_CODE = UUM.COMP_CODE
|
|
71
|
AND DPT.COMP_BASIS_CODE_TYPE = 'COB01'
|
|
72
|
AND DPT.COMP_BASIS_CODE_VALUE = UCS.DEPT_CODE
|
|
73
|
LEFT JOIN US_COMP_BASIS_CODE POS
|
|
74
|
ON POS.COMP_CODE = UUM.COMP_CODE
|
|
75
|
AND POS.COMP_BASIS_CODE_TYPE = 'COB02'
|
|
76
|
AND POS.COMP_BASIS_CODE_VALUE = UCS.POSITION_CODE
|
|
77
|
WHERE UCP.COMP_CODE = B.VENDOR_COMP_CODE
|
|
78
|
AND UCP.PIC_TYPE_CODE IN ('PIC01','PIC02')
|
|
79
|
) AS PIC
|
|
80
|
, B.HotelCode
|
|
81
|
, B.HotelName
|
|
82
|
, DETAIL_CODE.CONTRACT AS ContractType
|
|
83
|
, B.NumberRoomNights
|
|
84
|
, B.VendorCurrency
|
|
85
|
, B.VendorSumAmount AS BuyingAmount
|
|
86
|
, B.VendorRevenueAmount
|
|
87
|
, B.BillingCurrency
|
|
88
|
, B.BillingSumAmount AS SellingAmount
|
|
89
|
, B.BillingRevenueAmount
|
|
90
|
, B.BookingItemCode
|
|
91
|
, B.CheckInDate
|
|
92
|
, B.CheckOutDate
|
|
93
|
, ROOM_NIGHT.RoomNights AS RoomNights
|
|
94
|
, DETAIL_CODE.BOOKING_STATUS AS BookingStatus
|
|
95
|
FROM BK_TEMP B
|
|
96
|
LEFT JOIN BS_COUNTRY_CODE COUNTRY ON COUNTRY.COUNTRY_CODE = B.HOTEL_COUNTRY_CODE -- RECHECK LEFT JOIN
|
|
97
|
INNER JOIN LATERAL(SELECT REGION_NAME_EN FROM BS_REGION_CODE WHERE REGION_CODE = B.REGION_CODE LIMIT 1) REGION ON TRUE
|
|
98
|
INNER JOIN LATERAL(SELECT MAX(ROOM_SEQ) * DATEDIFF(B.CheckOutDate, B.CheckInDate) AS RoomNights, MAX(ROOM_SEQ) AS RoomCounts FROM BK_BOOKING_HOTEL_ROOM WHERE BOOKING_ITEM_CODE = B.BookingItemCode) ROOM_NIGHT ON TRUE
|
|
99
|
LEFT JOIN LATERAL(
|
|
100
|
SELECT
|
|
101
|
MAX(IF(MASTER_CODE = 'BK001' AND DETAIL_CODE = B.BOOKING_STATUS_CODE, DETAIL_CODE_NAME_EN, NULL)) AS BOOKING_STATUS
|
|
102
|
, MAX(IF(MASTER_CODE = 'BK002' AND DETAIL_CODE = B.CLIENT_PAYMENT_STATUS_CODE, DETAIL_CODE_NAME_EN, NULL)) AS CLIENT_PAYMENT_STATUS
|
|
103
|
, MAX(IF(MASTER_CODE = 'BK014' AND DETAIL_CODE = B.VENDOR_PAYMENT_STATUS_CODE, DETAIL_CODE_NAME_EN, NULL)) AS VENDOR_PAYMENT_STATUS
|
|
104
|
, MAX(IF(MASTER_CODE = 'BS037' AND DETAIL_CODE = B.DEVICE_TYPE_CODE, DETAIL_CODE_NAME_EN, NULL)) AS DEVICE
|
|
105
|
, MAX(IF(MASTER_CODE = 'BS002' AND DETAIL_CODE = B.STATION_TYPE_CODE, DETAIL_CODE_NAME_EN, NULL)) AS STATION
|
|
106
|
, MAX(IF(MASTER_CODE = 'HO023' AND DETAIL_CODE = B.HOTEL_CONTRACT_TYPE_CODE, DETAIL_CODE_NAME_EN, NULL)) AS CONTRACT
|
|
107
|
FROM BS_DETAIL_CODE WHERE MASTER_CODE IN ('BK001', 'BK002', 'BK014', 'BS037', 'BS002', 'HO023')
|
|
108
|
AND DETAIL_CODE IN (
|
|
109
|
B.BOOKING_STATUS_CODE,
|
|
110
|
B.CLIENT_PAYMENT_STATUS_CODE,
|
|
111
|
B.VENDOR_PAYMENT_STATUS_CODE,
|
|
112
|
B.DEVICE_TYPE_CODE,
|
|
113
|
B.STATION_TYPE_CODE,
|
|
114
|
B.HOTEL_CONTRACT_TYPE_CODE
|
|
115
|
)
|
|
116
|
) DETAIL_CODE ON TRUE
|
|
117
|
ORDER BY B.FIRST_INSERT_DATETIME;
|
|
118
|
DROP TEMPORARY TABLE IF EXISTS BK_TEMP;
|
