· 5 years ago · Mar 10, 2020, 02:08 PM
1DO
2$$
3BEGIN
4 ALTER TABLE experience_extranet_payment_out.invoice ADD COLUMN ticket_type TEXT NULL;
5 ALTER TABLE experience_extranet_payment_out.invoice ADD COLUMN ticket_has_commission BOOLEAN NULL;
6 ALTER TABLE experience_extranet_payment_out.invoice ADD COLUMN vat_out_rate BIGINT NULL;
7 ALTER TABLE experience_extranet_payment_out.invoice ADD COLUMN vat_in_rate BIGINT NULL;
8 ALTER TABLE experience_extranet_payment_out.invoice ADD COLUMN recommended_rate BIGINT NULL;
9 ALTER TABLE experience_extranet_payment_out.invoice ADD COLUMN net_rate BIGINT NULL;
10 ALTER TABLE experience_extranet_payment_out.invoice ADD COLUMN net_commission_rate BIGINT NULL;
11 EXCEPTION
12 WHEN duplicate_column
13 THEN RAISE NOTICE 'column ticket type, ticket has commission, vat out rate, vat in rate, recommended rate, net rate, net commission rate';
14END;
15$$;
16
17DO
18$$
19BEGIN
20 ALTER TABLE experience_extranet_payment_out.payment_list_detail_invoice ADD COLUMN ticket_type TEXT NULL;
21 ALTER TABLE experience_extranet_payment_out.payment_list_detail_invoice ADD COLUMN ticket_has_commission BOOLEAN NULL;
22 ALTER TABLE experience_extranet_payment_out.payment_list_detail_invoice ADD COLUMN vat_out_rate BIGINT NULL;
23 ALTER TABLE experience_extranet_payment_out.payment_list_detail_invoice ADD COLUMN vat_in_rate BIGINT NULL;
24 ALTER TABLE experience_extranet_payment_out.payment_list_detail_invoice ADD COLUMN recommended_rate BIGINT NULL;
25 ALTER TABLE experience_extranet_payment_out.payment_list_detail_invoice ADD COLUMN net_rate BIGINT NULL;
26 ALTER TABLE experience_extranet_payment_out.payment_list_detail_invoice ADD COLUMN net_commission_rate BIGINT NULL;
27 EXCEPTION
28 WHEN duplicate_column
29 THEN RAISE NOTICE 'column ticket type, ticket has commission, vat out rate, vat in rate, recommended rate, net rate, net commission rate';
30END;
31$$;
32
33DO $$
34BEGIN
35 BEGIN
36 ALTER TABLE experience_extranet_payment_deposit.deposit_history
37 ADD COLUMN total_vat_in_rate BIGINT;
38
39 ALTER TABLE experience_extranet_payment_deposit.deposit_history
40 ADD COLUMN total_vat_out_rate BIGINT;
41
42 ALTER TABLE experience_extranet_payment_deposit.deposit_history
43 ADD COLUMN total_net_rate BIGINT;
44 EXCEPTION
45 WHEN duplicate_column
46 THEN RAISE NOTICE 'column total vat & net rate already exists in experience_extranet_payment_deposit.deposit_history';
47 END;
48END;
49$$;
50
51CREATE OR REPLACE FUNCTION experience_extranet_payment_deposit_v1_helper.insert_deposit_transaction(deposit_spec jsonb, new_balance bigint, new_balance_guarantee bigint) returns boolean
52 security definer
53 language sql
54as $$
55 WITH query AS (INSERT INTO experience_extranet_payment_deposit.deposit_history (
56 transaction_id,
57 transaction_date,
58 transaction_type,
59 booking_id,
60 voucher_id,
61 supplier_id,
62 supplier_name,
63 payment_type,
64 note,
65 currency,
66 transaction_amount,
67 decimal_point,
68 movement_type,
69 last_balance_amount,
70 last_balance_guarantee,
71 reference_id,
72 contract_owner,
73 bank_id,
74 bank_other_id,
75 bank_name,
76 total_vat_in_rate,
77 total_vat_out_rate,
78 total_net_rate
79 )
80 VALUES (
81 (deposit_spec ->> 'transaction_id') :: BIGINT,
82 (deposit_spec ->> 'transaction_date') :: TIMESTAMPTZ,
83 (deposit_spec ->> 'transaction_type') :: experience_extranet_payment_deposit.DEPOSIT_TYPE,
84 deposit_spec ->> 'booking_id',
85 deposit_spec ->> 'voucher_id',
86 (deposit_spec ->> 'supplier_id') :: BIGINT,
87 deposit_spec ->> 'supplier_name',
88 deposit_spec ->> 'payment_type',
89 deposit_spec ->> 'note',
90 deposit_spec ->> 'currency',
91 (deposit_spec ->> 'transaction_amount') :: BIGINT,
92 (deposit_spec ->> 'decimal_point') :: INTEGER,
93 (deposit_spec ->> 'movement_type') :: experience_extranet_payment_deposit.MOVEMENT_TYPE,
94 new_balance,
95 new_balance_guarantee,
96 deposit_spec ->> 'reference_id',
97 deposit_spec->>'contract_owner',
98 deposit_spec->>'bank_id',
99 deposit_spec->>'bank_other_id',
100 deposit_spec->>'bank_name',
101 COALESCE(deposit_spec->>'total_vat_in_rate', '0') :: BIGINT,
102 COALESCE(deposit_spec->>'total_vat_out_rate', '0') :: BIGINT,
103 COALESCE(deposit_spec->>'total_net_rate', '0') :: BIGINT
104 )
105 RETURNING 1
106 )
107 SELECT COUNT(*) > 0 FROM query;
108$$;
109
110CREATE OR REPLACE FUNCTION experience_extranet_payment_out_v1_api.insert_payment_list(data_spec JSONB)
111 RETURNS BOOLEAN
112SECURITY DEFINER
113LANGUAGE plpgsql
114AS $$
115DECLARE
116 payment_list_detail_data JSONB;
117 payment_list_detail_invoice_data JSONB;
118 reference_no_spec TEXT;
119BEGIN
120 reference_no_spec := experience_extranet_payment_out_v1_helper.get_next_payment_list_reference_no(
121 (data_spec ->> 'reference_date') :: TIMESTAMPTZ) || '-' || (data_spec ->> 'product_owner')
122 || '-' || (data_spec ->> 'tvlk_entity_code');
123
124 INSERT INTO experience_extranet_payment_out.payment_list (
125 payment_list_id,
126 reference_date,
127 reference_no,
128 currency,
129 country,
130 bank_id,
131 total_payment_amount,
132 decimal_point,
133 conversion_table_id,
134 payment_method,
135 status,
136 input_timestamp,
137 automatic,
138 username,
139 payment_out_request_id,
140 total_payment_amount_before_adjustment,
141 product_owner,
142 tvlk_entity_code
143 )
144 VALUES (
145 (data_spec ->> 'payment_list_id') :: BIGINT,
146 (data_spec ->> 'reference_date') :: TIMESTAMPTZ,
147 reference_no_spec,
148 data_spec ->> 'currency',
149 data_spec ->> 'country',
150 data_spec ->> 'bank_id',
151 (data_spec ->> 'total_payment_amount') :: BIGINT,
152 (data_spec ->> 'decimal_point') :: INTEGER,
153 (data_spec ->> 'conversion_table_id') :: BIGINT,
154 data_spec ->> 'payment_method',
155 (data_spec ->> 'status') :: experience_extranet_payment_out.PAYMENT_LIST_STATUS,
156 (data_spec ->> 'input_timestamp') :: TIMESTAMPTZ,
157 (data_spec ->> 'automatic') :: BOOLEAN,
158 data_spec ->> 'username',
159 CASE
160 WHEN data_spec -> 'payment_out_request_id' IS NOT NULL
161 THEN
162 (data_spec ->> 'payment_out_request_id') :: BIGINT
163 ELSE
164 NULL
165 END,
166 (data_spec ->> 'total_payment_amount_before_adjustment') :: BIGINT,
167 data_spec ->> 'product_owner',
168 data_spec ->> 'tvlk_entity_code'
169 );
170
171 FOR payment_list_detail_data IN SELECT jsonb_array_elements(data_spec -> 'payment_list_detail') LOOP
172 INSERT INTO experience_extranet_payment_out.payment_list_detail (
173 payment_list_detail_id,
174 payment_list_id,
175 supplier_id,
176 supplier_name,
177 bank_id,
178 bank_other_id,
179 bank_short_name,
180 bank_name,
181 bank_acc_no,
182 bank_acc_beneficiary_name,
183 bank_branch,
184 bank_swift_code,
185 currency,
186 country,
187 total_payment_amount,
188 decimal_point,
189 conversion_table_id,
190 payment_method,
191 status,
192 note,
193 transfer_date,
194 payment_out_request_detail_id,
195 total_payment_amount_before_adjustment,
196 tvlk_entity_code
197 )
198 VALUES (
199 (payment_list_detail_data ->> 'payment_list_detail_id') :: BIGINT,
200 (data_spec ->> 'payment_list_id') :: BIGINT,
201 (payment_list_detail_data ->> 'supplier_id') :: BIGINT,
202 payment_list_detail_data ->> 'supplier_name',
203 payment_list_detail_data ->> 'bank_id',
204 payment_list_detail_data ->> 'bank_other_id',
205 payment_list_detail_data ->> 'bank_short_name',
206 payment_list_detail_data ->> 'bank_name',
207 payment_list_detail_data ->> 'bank_acc_no',
208 payment_list_detail_data ->> 'bank_acc_beneficiary_name',
209 payment_list_detail_data ->> 'bank_branch',
210 payment_list_detail_data ->> 'bank_swift_code',
211 payment_list_detail_data ->> 'currency',
212 payment_list_detail_data ->> 'country',
213 (payment_list_detail_data ->> 'total_payment_amount') :: BIGINT,
214 (payment_list_detail_data ->> 'decimal_point') :: INTEGER,
215 (payment_list_detail_data ->> 'conversion_table_id') :: BIGINT,
216 payment_list_detail_data ->> 'payment_method',
217 (payment_list_detail_data ->> 'status') :: experience_extranet_payment_out.PAYMENT_LIST_DETAIL_STATUS,
218 payment_list_detail_data ->> 'note',
219 (payment_list_detail_data ->> 'transfer_date') :: TIMESTAMPTZ,
220 NULL,
221 (payment_list_detail_data ->> 'total_payment_amount_before_adjustment') :: BIGINT,
222 (payment_list_detail_data ->> 'tvlk_entity_code')
223 );
224 PERFORM experience_extranet_payment_out_v1_api.insert_payment_list_detail_state(
225 (payment_list_detail_data ->> 'payment_list_detail_id') :: BIGINT,
226 (payment_list_detail_data ->> 'status') :: experience_extranet_payment_out.PAYMENT_LIST_DETAIL_STATUS
227 );
228
229 IF payment_list_detail_data -> 'payment_list_detail_invoice' IS NOT NULL
230 THEN
231 FOR payment_list_detail_invoice_data IN SELECT jsonb_array_elements(
232 payment_list_detail_data -> 'payment_list_detail_invoice') LOOP
233 INSERT INTO experience_extranet_payment_out.payment_list_detail_invoice (
234 payment_list_detail_id,
235 invoice_id,
236 booking_id,
237 partner_booking_id,
238 guest_name,
239 booking_date,
240 invoice_date,
241 invoice_due_date,
242 invoice_type,
243 currency,
244 invoice_amount,
245 decimal_point,
246 conversion_table_id,
247 payment_date,
248 tvlk_entity_code,
249 experience_id,
250 experience_name,
251 ticket_id,
252 ticket_name,
253 ticket_date,
254 voucher_codes,
255 allotment_used,
256 ticket_type,
257 ticket_has_commission,
258 vat_out_rate,
259 vat_in_rate,
260 recommended_rate,
261 net_rate,
262 net_commission_rate
263 )
264 VALUES (
265 (payment_list_detail_data ->> 'payment_list_detail_id') :: BIGINT,
266 (payment_list_detail_invoice_data ->> 'invoice_id') :: BIGINT,
267 payment_list_detail_invoice_data ->> 'booking_id',
268 payment_list_detail_invoice_data ->> 'partner_booking_id',
269 payment_list_detail_invoice_data ->> 'guest_name',
270 (payment_list_detail_invoice_data ->> 'booking_date') :: TIMESTAMPTZ,
271 (payment_list_detail_invoice_data ->> 'invoice_date') :: TIMESTAMPTZ,
272 (payment_list_detail_invoice_data ->> 'invoice_due_date') :: TIMESTAMPTZ,
273 payment_list_detail_invoice_data ->> 'invoice_type',
274 payment_list_detail_invoice_data ->> 'currency',
275 (payment_list_detail_invoice_data ->> 'invoice_amount') :: BIGINT,
276 (payment_list_detail_invoice_data ->> 'decimal_point') :: INTEGER,
277 (payment_list_detail_invoice_data ->> 'conversion_table_id') :: BIGINT,
278 (payment_list_detail_invoice_data ->> 'payment_date') :: TIMESTAMPTZ,
279 (payment_list_detail_invoice_data ->> 'tvlk_entity_code'),
280 (payment_list_detail_invoice_data ->> 'experience_id'),
281 (payment_list_detail_invoice_data ->> 'experience_name'),
282 (payment_list_detail_invoice_data ->> 'ticket_id'),
283 (payment_list_detail_invoice_data ->> 'ticket_name'),
284 (payment_list_detail_invoice_data ->> 'ticket_date') :: TIMESTAMPTZ,
285 (payment_list_detail_invoice_data ->> 'voucher_codes'),
286 (payment_list_detail_invoice_data ->> 'allotment_used') :: INTEGER,
287 (payment_list_detail_invoice_data ->> 'ticket_type'),
288 (COALESCE(payment_list_detail_invoice_data ->> 'ticket_has_commission', 'false')) :: BOOLEAN,
289 (COALESCE(payment_list_detail_invoice_data ->> 'vat_out_rate', '0')) :: BIGINT,
290 (COALESCE(payment_list_detail_invoice_data ->> 'vat_in_rate', '0')) :: BIGINT,
291 (COALESCE(payment_list_detail_invoice_data ->> 'recommended_rate', '0')) :: BIGINT,
292 (COALESCE(payment_list_detail_invoice_data ->> 'net_rate', '0')) :: BIGINT,
293 (COALESCE(payment_list_detail_invoice_data ->> 'net_commission_rate', '0')) :: BIGINT
294 );
295
296 UPDATE experience_extranet_payment_out.invoice
297 SET status = 'IN_PAYMENT_LIST' :: experience_extranet_payment_out.INVOICE_STATUS,
298 payment_list_id = (data_spec ->> 'payment_list_id') :: BIGINT
299 WHERE invoice_id = (payment_list_detail_invoice_data ->> 'invoice_id') :: BIGINT;
300
301 END LOOP;
302 END IF;
303
304 END LOOP;
305
306 RETURN FOUND;
307END
308$$;
309
310CREATE OR REPLACE FUNCTION experience_extranet_payment_out_v1_api.insert_invoice(invoice_spec jsonb) returns boolean
311 security definer
312 language sql
313as $$
314WITH
315 check_exists AS (SELECT *
316 FROM experience_extranet_payment_out.invoice
317 WHERE booking_id = invoice_spec ->> 'booking_id'),
318 insert_result AS (
319 INSERT INTO experience_extranet_payment_out.invoice (
320 invoice_id,
321 booking_id,
322 partner_booking_id,
323 supplier_id,
324 supplier_name,
325 invoice_date,
326 invoice_due_date,
327 invoice_type,
328 country,
329 currency,
330 invoice_amount,
331 decimal_point,
332 conversion_table_id,
333 guest_name,
334 booking_date,
335 bank_id,
336 bank_other_id,
337 bank_short_name,
338 bank_name,
339 bank_acc_no,
340 bank_acc_beneficiary_name,
341 bank_branch,
342 bank_swift_code,
343 payment_method,
344 payment_date,
345 status,
346 payment_list_id,
347 product_owner,
348 tvlk_entity_code,
349 experience_id,
350 experience_name,
351 ticket_id,
352 ticket_name,
353 ticket_date,
354 voucher_codes,
355 allotment_used,
356 ticket_type,
357 ticket_has_commission,
358 vat_out_rate,
359 vat_in_rate,
360 recommended_rate,
361 net_rate,
362 net_commission_rate
363 )
364 SELECT
365 (invoice_spec ->> 'invoice_id') :: BIGINT,
366 invoice_spec ->> 'booking_id',
367 invoice_spec ->> 'partner_booking_id',
368 (invoice_spec ->> 'supplier_id') :: BIGINT,
369 invoice_spec ->> 'supplier_name',
370 (invoice_spec ->> 'invoice_date') :: TIMESTAMPTZ,
371 (invoice_spec ->> 'invoice_due_date') :: TIMESTAMPTZ,
372 invoice_spec ->> 'invoice_type',
373 invoice_spec ->> 'country',
374 invoice_spec ->> 'currency',
375 (invoice_spec ->> 'invoice_amount') :: BIGINT,
376 (invoice_spec ->> 'decimal_point') :: INTEGER,
377 (invoice_spec ->> 'conversion_table_id') :: BIGINT,
378 invoice_spec ->> 'guest_name',
379 (invoice_spec ->> 'booking_date') :: TIMESTAMPTZ,
380 invoice_spec ->> 'bank_id',
381 invoice_spec ->> 'bank_other_id',
382 invoice_spec ->> 'bank_short_name',
383 invoice_spec ->> 'bank_name',
384 invoice_spec ->> 'bank_acc_no',
385 invoice_spec ->> 'bank_acc_beneficiary_name',
386 invoice_spec ->> 'bank_branch',
387 invoice_spec ->> 'bank_swift_code',
388 invoice_spec ->> 'payment_method',
389 (invoice_spec ->> 'payment_date') :: TIMESTAMPTZ,
390 (invoice_spec ->> 'status') :: experience_extranet_payment_out.INVOICE_STATUS,
391 (invoice_spec ->> 'payment_list_id') :: BIGINT,
392 invoice_spec ->> 'product_owner',
393 invoice_spec ->> 'tvlk_entity_code',
394 invoice_spec ->> 'experience_id',
395 invoice_spec ->> 'experience_name',
396 invoice_spec ->> 'ticket_id',
397 invoice_spec ->> 'ticket_name',
398 (invoice_spec ->> 'ticket_date') :: timestamptz,
399 invoice_spec ->> 'voucher_codes',
400 (invoice_spec ->> 'allotment_used') :: INTEGER,
401 (invoice_spec ->> 'ticket_type'),
402 (COALESCE(invoice_spec ->> 'ticket_has_commission', 'false')) :: BOOLEAN,
403 (COALESCE(invoice_spec ->> 'vat_out_rate', '0')) :: BIGINT,
404 (COALESCE(invoice_spec ->> 'vat_in_rate', '0')) :: BIGINT,
405 (COALESCE(invoice_spec ->> 'recommended_rate', '0')) :: BIGINT,
406 (COALESCE(invoice_spec ->> 'net_rate', '0')) :: BIGINT,
407 (COALESCE(invoice_spec ->> 'net_commission_rate', '0')) :: BIGINT
408 WHERE NOT EXISTS(SELECT *
409 FROM check_exists)
410 RETURNING 1
411 )
412SELECT sum(result) > 0
413FROM (
414 SELECT count(*) AS "result"
415 FROM insert_result
416 UNION ALL
417 SELECT count(*) AS "result"
418 FROM check_exists
419 ) res
420$$;