libeufin-nexus-procedures.sql (19809B)
1 -- 2 -- This file is part of TALER 3 -- Copyright (C) 2023-2025 Taler Systems SA 4 -- 5 -- TALER is free software; you can redistribute it and/or modify it under the 6 -- terms of the GNU General Public License as published by the Free Software 7 -- Foundation; either version 3, or (at your option) any later version. 8 -- 9 -- TALER is distributed in the hope that it will be useful, but WITHOUT ANY 10 -- WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR 11 -- A PARTICULAR PURPOSE. See the GNU General Public License for more details. 12 -- 13 -- You should have received a copy of the GNU General Public License along with 14 -- TALER; see the file COPYING. If not, see <http://www.gnu.org/licenses/> 15 16 BEGIN; 17 SET search_path TO public; 18 CREATE EXTENSION IF NOT EXISTS pgcrypto; 19 20 SET search_path TO libeufin_nexus; 21 22 -- Remove all existing functions 23 DO 24 $do$ 25 DECLARE 26 _sql text; 27 BEGIN 28 SELECT INTO _sql 29 string_agg(format('DROP %s %s CASCADE;' 30 , CASE prokind 31 WHEN 'f' THEN 'FUNCTION' 32 WHEN 'p' THEN 'PROCEDURE' 33 END 34 , oid::regprocedure) 35 , E'\n') 36 FROM pg_proc 37 WHERE pronamespace = 'libeufin_nexus'::regnamespace; 38 39 IF _sql IS NOT NULL THEN 40 EXECUTE _sql; 41 END IF; 42 END 43 $do$; 44 45 CREATE FUNCTION amount_normalize( 46 IN amount taler_amount 47 ,OUT normalized taler_amount 48 ) 49 LANGUAGE plpgsql IMMUTABLE AS $$ 50 BEGIN 51 normalized.val = amount.val + amount.frac / 100000000; 52 IF (normalized.val > 1::INT8<<52) THEN 53 RAISE EXCEPTION 'amount value overflowed'; 54 END IF; 55 normalized.frac = amount.frac % 100000000; 56 57 END $$; 58 COMMENT ON FUNCTION amount_normalize 59 IS 'Returns the normalized amount by adding to the .val the value of (.frac / 100000000) and removing the modulus 100000000 from .frac.' 60 'It raises an exception when the resulting .val is larger than 2^52'; 61 62 CREATE FUNCTION amount_add( 63 IN l taler_amount 64 ,IN r taler_amount 65 ,OUT sum taler_amount 66 ) 67 LANGUAGE plpgsql IMMUTABLE AS $$ 68 BEGIN 69 sum = (l.val + r.val, l.frac + r.frac); 70 SELECT normalized.val, normalized.frac INTO sum.val, sum.frac FROM amount_normalize(sum) as normalized; 71 END $$; 72 COMMENT ON FUNCTION amount_add 73 IS 'Returns the normalized sum of two amounts. It raises an exception when the resulting .val is larger than 2^52'; 74 75 CREATE FUNCTION register_outgoing( 76 IN in_amount taler_amount 77 ,IN in_debit_fee taler_amount 78 ,IN in_subject TEXT 79 ,IN in_execution_time INT8 80 ,IN in_credit_payto TEXT 81 ,IN in_end_to_end_id TEXT 82 ,IN in_msg_id TEXT 83 ,IN in_acct_svcr_ref TEXT 84 ,IN in_wtid BYTEA 85 ,IN in_exchange_url TEXT 86 ,OUT out_tx_id INT8 87 ,OUT out_found BOOLEAN 88 ,OUT out_initiated BOOLEAN 89 ) 90 LANGUAGE plpgsql AS $$ 91 DECLARE 92 init_id INT8; 93 local_amount taler_amount; 94 local_subject TEXT; 95 local_credit_payto TEXT; 96 local_wtid BYTEA; 97 local_exchange_base_url TEXT; 98 local_end_to_end_id TEXT; 99 BEGIN 100 -- Check if already registered 101 SELECT outgoing_transaction_id, subject, credit_payto, (amount).val, (amount).frac, 102 wtid, exchange_base_url 103 INTO out_tx_id, local_subject, local_credit_payto, local_amount.val, local_amount.frac, 104 local_wtid, local_exchange_base_url 105 FROM outgoing_transactions LEFT JOIN talerable_outgoing_transactions USING (outgoing_transaction_id) 106 WHERE end_to_end_id = in_end_to_end_id OR acct_svcr_ref = in_acct_svcr_ref; 107 out_found=FOUND; 108 IF out_found THEN 109 -- Check metadata 110 -- TODO take subject if missing and more detailed credit payto 111 IF in_subject IS NOT NULL AND local_subject != in_subject THEN 112 RAISE NOTICE 'outgoing tx %: stored subject is ''%'' got ''%''', in_end_to_end_id, local_subject, in_subject; 113 END IF; 114 IF in_credit_payto IS NOT NULL AND local_credit_payto != in_credit_payto THEN 115 RAISE NOTICE 'outgoing tx %: stored subject credit payto is % got %', in_end_to_end_id, local_credit_payto, in_credit_payto; 116 END IF; 117 IF local_amount IS DISTINCT FROM in_amount THEN 118 RAISE NOTICE 'outgoing tx %: stored amount is % got %', in_end_to_end_id, local_amount, in_amount; 119 END IF; 120 IF local_wtid IS DISTINCT FROM in_wtid THEN 121 RAISE NOTICE 'outgoing tx %: stored wtid is % got %', in_end_to_end_id, local_wtid, in_wtid; 122 END IF; 123 IF local_exchange_base_url IS DISTINCT FROM in_exchange_url THEN 124 RAISE NOTICE 'outgoing tx %: stored exchange base url is % got %', in_end_to_end_id, local_exchange_base_url, in_exchange_url; 125 END IF; 126 END IF; 127 128 -- Check if initiated 129 SELECT initiated_outgoing_transaction_id, subject, credit_payto, (amount).val, (amount).frac, 130 wtid, exchange_base_url 131 INTO init_id, local_subject, local_credit_payto, local_amount.val, local_amount.frac, 132 local_wtid, local_exchange_base_url 133 FROM initiated_outgoing_transactions LEFT JOIN transfer_operations USING (initiated_outgoing_transaction_id) 134 WHERE end_to_end_id = in_end_to_end_id; 135 out_initiated=FOUND; 136 IF out_initiated AND NOT out_found THEN 137 -- Check metadata 138 -- TODO take subject if missing and more detailed credit payto 139 IF in_subject IS NOT NULL AND local_subject != in_subject THEN 140 RAISE NOTICE 'outgoing tx %: initiated subject is ''%'' got ''%''', in_end_to_end_id, local_subject, in_subject; 141 END IF; 142 IF local_credit_payto IS DISTINCT FROM in_credit_payto THEN 143 RAISE NOTICE 'outgoing tx %: initiated subject credit payto is % got %', in_end_to_end_id, local_credit_payto, in_credit_payto; 144 END IF; 145 IF local_amount IS DISTINCT FROM in_amount THEN 146 RAISE NOTICE 'outgoing tx %: initiated amount is % got %', in_end_to_end_id, local_amount, in_amount; 147 END IF; 148 IF in_wtid IS NOT NULL AND local_wtid != in_wtid THEN 149 RAISE NOTICE 'outgoing tx %: initiated wtid is % got %', in_end_to_end_id, local_wtid, in_wtid; 150 END IF; 151 IF in_exchange_url IS NOT NULL AND local_exchange_base_url != in_exchange_url THEN 152 RAISE NOTICE 'outgoing tx %: initiated exchange base url is % got %', in_end_to_end_id, local_exchange_base_url, in_exchange_url; 153 END IF; 154 END IF; 155 156 IF NOT out_found THEN 157 -- Store the transaction in the database 158 INSERT INTO outgoing_transactions ( 159 amount 160 ,debit_fee 161 ,subject 162 ,execution_time 163 ,credit_payto 164 ,end_to_end_id 165 ,acct_svcr_ref 166 ) VALUES ( 167 in_amount 168 ,in_debit_fee 169 ,in_subject 170 ,in_execution_time 171 ,in_credit_payto 172 ,in_end_to_end_id 173 ,in_acct_svcr_ref 174 ) 175 RETURNING outgoing_transaction_id 176 INTO out_tx_id; 177 178 -- Register as talerable if contains wtid and exchange URL 179 IF in_wtid IS NOT NULL OR in_exchange_url IS NOT NULL THEN 180 SELECT end_to_end_id INTO local_end_to_end_id 181 FROM talerable_outgoing_transactions 182 JOIN outgoing_transactions USING (outgoing_transaction_id) 183 WHERE wtid=in_wtid; 184 IF FOUND THEN 185 IF local_end_to_end_id != in_end_to_end_id THEN 186 RAISE NOTICE 'wtid reuse: tx % and tx % have the same wtid %', in_end_to_end_id, local_end_to_end_id, in_wtid; 187 END IF; 188 ELSE 189 INSERT INTO talerable_outgoing_transactions(outgoing_transaction_id, wtid, exchange_base_url) 190 VALUES (out_tx_id, in_wtid, in_exchange_url); 191 PERFORM pg_notify('nexus_outgoing_tx', out_tx_id::text); 192 END IF; 193 END IF; 194 195 IF out_initiated THEN 196 -- Reconciles the related initiated transaction 197 UPDATE initiated_outgoing_transactions 198 SET 199 outgoing_transaction_id = out_tx_id 200 ,status = 'success' 201 ,status_msg = null 202 WHERE initiated_outgoing_transaction_id = init_id 203 AND status != 'late_failure'; 204 205 -- Reconciles the related initiated batch 206 UPDATE initiated_outgoing_batches 207 SET status = 'success', status_msg = null 208 WHERE message_id = in_msg_id AND status NOT IN ('success', 'permanent_failure', 'late_failure'); 209 END IF; 210 END IF; 211 END $$; 212 COMMENT ON FUNCTION register_outgoing 213 IS 'Register an outgoing transaction and optionally reconciles the related initiated transaction with it'; 214 215 CREATE FUNCTION register_incoming( 216 IN in_amount taler_amount 217 ,IN in_credit_fee taler_amount 218 ,IN in_subject TEXT 219 ,IN in_execution_time INT8 220 ,IN in_debit_payto TEXT 221 ,IN in_uetr UUID 222 ,IN in_tx_id TEXT 223 ,IN in_acct_svcr_ref TEXT 224 ,IN in_type taler_incoming_type 225 ,IN in_metadata BYTEA 226 -- Error status 227 ,OUT out_reserve_pub_reuse BOOLEAN 228 -- Success return 229 ,OUT out_found BOOLEAN 230 ,OUT out_completed BOOLEAN 231 ,OUT out_talerable BOOLEAN 232 ,OUT out_tx_id INT8 233 ,OUT out_bounce_id TEXT 234 ) 235 LANGUAGE plpgsql AS $$ 236 DECLARE 237 local_ref TEXT; 238 local_amount taler_amount; 239 local_subject TEXT; 240 local_debit_payto TEXT; 241 BEGIN 242 IF in_credit_fee = (0, 0)::taler_amount THEN 243 in_credit_fee = NULL; 244 END IF; 245 246 -- Check if already registered 247 SELECT incoming_transaction_id, tx.subject, debit_payto, (tx.amount).val, (tx.amount).frac, metadata IS NOT NULL, end_to_end_id 248 INTO out_tx_id, local_subject, local_debit_payto, local_amount.val, local_amount.frac, out_talerable, out_bounce_id 249 FROM incoming_transactions AS tx 250 LEFT JOIN talerable_incoming_transactions USING (incoming_transaction_id) 251 LEFT JOIN bounced_transactions USING (incoming_transaction_id) 252 LEFT JOIN initiated_outgoing_transactions USING (initiated_outgoing_transaction_id) 253 WHERE uetr = in_uetr OR tx_id = in_tx_id OR acct_svcr_ref = in_acct_svcr_ref; 254 out_found=FOUND; 255 IF out_found THEN 256 local_ref=COALESCE(in_uetr::text, in_tx_id, in_acct_svcr_ref); 257 -- Check metadata 258 IF in_subject != local_subject THEN 259 RAISE NOTICE 'incoming tx %: stored subject is ''%'' got ''%''', local_ref, local_subject, in_subject; 260 END IF; 261 IF in_debit_payto != local_debit_payto THEN 262 RAISE NOTICE 'incoming tx %: stored subject debit payto is % got %', local_ref, local_debit_payto, in_debit_payto; 263 END IF; 264 IF local_amount != in_amount THEN 265 RAISE NOTICE 'incoming tx %: stored amount is % got %', local_ref, local_amount, in_amount; 266 END IF; 267 UPDATE incoming_transactions 268 SET subject=COALESCE(subject, in_subject), 269 debit_payto=COALESCE(debit_payto, in_debit_payto), 270 uetr=COALESCE(uetr, in_uetr), 271 tx_id=COALESCE(tx_id, in_tx_id), 272 acct_svcr_ref=COALESCE(acct_svcr_ref, in_acct_svcr_ref) 273 WHERE incoming_transaction_id = out_tx_id; 274 out_completed=local_debit_payto IS NULL AND in_debit_payto IS NOT NULL; 275 IF out_completed THEN 276 PERFORM pg_notify('nexus_revenue_tx', out_tx_id::text); 277 END IF; 278 ELSE 279 out_reserve_pub_reuse=in_type = 'reserve' AND EXISTS(SELECT FROM talerable_incoming_transactions WHERE metadata = in_metadata AND type = 'reserve'); 280 IF out_reserve_pub_reuse THEN 281 RETURN; 282 END IF; 283 -- Store the transaction in the database 284 INSERT INTO incoming_transactions ( 285 amount 286 ,credit_fee 287 ,subject 288 ,execution_time 289 ,debit_payto 290 ,uetr 291 ,tx_id 292 ,acct_svcr_ref 293 ) VALUES ( 294 in_amount 295 ,in_credit_fee 296 ,in_subject 297 ,in_execution_time 298 ,in_debit_payto 299 ,in_uetr 300 ,in_tx_id 301 ,in_acct_svcr_ref 302 ) RETURNING incoming_transaction_id INTO out_tx_id; 303 IF in_subject IS NOT NULL AND in_debit_payto IS NOT NULL THEN 304 PERFORM pg_notify('nexus_revenue_tx', out_tx_id::text); 305 END IF; 306 out_talerable=FALSE; 307 END IF; 308 309 -- Register as talerable if not already registered as such and not already bounced 310 IF in_type IS NOT NULL AND NOT out_talerable AND out_bounce_id IS NULL THEN 311 -- We cannot use ON CONFLICT here because conversion use a trigger before insertion that isn't idempotent 312 INSERT INTO talerable_incoming_transactions ( 313 incoming_transaction_id 314 ,type 315 ,metadata 316 ) VALUES ( 317 out_tx_id 318 ,in_type 319 ,in_metadata 320 ); 321 PERFORM pg_notify('nexus_incoming_tx', out_tx_id::text); 322 out_talerable=TRUE; 323 END IF; 324 END $$; 325 326 CREATE FUNCTION register_and_bounce_incoming( 327 IN in_amount taler_amount 328 ,IN in_credit_fee taler_amount 329 ,IN in_subject TEXT 330 ,IN in_execution_time INT8 331 ,IN in_debit_payto TEXT 332 ,IN in_uetr UUID 333 ,IN in_tx_id TEXT 334 ,IN in_acct_svcr_ref TEXT 335 ,IN in_bounce_amount taler_amount 336 ,IN in_now_date INT8 337 ,IN in_bounce_id TEXT 338 ,IN in_cause TEXT 339 -- Error status 340 ,OUT out_talerable BOOLEAN 341 -- Success return 342 ,OUT out_found BOOLEAN 343 ,OUT out_completed BOOLEAN 344 ,OUT out_tx_id INT8 345 ,OUT out_bounce_id TEXT 346 ) 347 LANGUAGE plpgsql AS $$ 348 DECLARE 349 init_id INT8; 350 bounce_amount taler_amount; 351 BEGIN 352 -- Register incoming transaction 353 SELECT reg.out_found, reg.out_completed, reg.out_tx_id, reg.out_talerable 354 FROM register_incoming(in_amount, in_credit_fee, in_subject, in_execution_time, in_debit_payto, in_uetr, in_tx_id, in_acct_svcr_ref, NULL, NULL) as reg 355 INTO out_found, out_completed, out_tx_id, out_talerable; 356 -- Cannot bounce a transaction registered as talerable 357 IF out_talerable THEN 358 RETURN; 359 END IF; 360 -- Bounce incoming transaction 361 SELECT bounce.out_bounce_id INTO out_bounce_id FROM bounce_incoming(out_tx_id, in_bounce_amount, in_bounce_id, in_now_date, in_cause) AS bounce; 362 END $$; 363 364 CREATE FUNCTION bounce_incoming( 365 IN in_tx_id INT8 366 ,IN in_bounce_amount taler_amount 367 ,IN in_bounce_id TEXT 368 ,IN in_now_date INT8 369 ,IN in_cause TEXT 370 ,OUT out_bounce_id TEXT 371 ) 372 LANGUAGE plpgsql AS $$ 373 DECLARE 374 local_bank_id TEXT; 375 payto_uri TEXT; 376 init_id INT8; 377 BEGIN 378 -- Check if already bounced 379 SELECT end_to_end_id INTO out_bounce_id 380 FROM libeufin_nexus.initiated_outgoing_transactions 381 JOIN libeufin_nexus.bounced_transactions USING (initiated_outgoing_transaction_id) 382 WHERE incoming_transaction_id = in_tx_id; 383 384 -- Else initiate the bounce transaction 385 IF NOT FOUND THEN 386 out_bounce_id = in_bounce_id; 387 -- Get incoming transaction bank ID and creditor 388 SELECT COALESCE(uetr::text, tx_id, acct_svcr_ref), debit_payto 389 INTO local_bank_id, payto_uri 390 FROM libeufin_nexus.incoming_transactions 391 WHERE incoming_transaction_id = in_tx_id; 392 -- Initiate the bounce transaction 393 INSERT INTO libeufin_nexus.initiated_outgoing_transactions ( 394 amount 395 ,subject 396 ,credit_payto 397 ,initiation_time 398 ,end_to_end_id 399 ) VALUES ( 400 in_bounce_amount 401 ,'bounce ' || local_bank_id || ': ' || in_cause 402 ,payto_uri 403 ,in_now_date 404 ,in_bounce_id 405 ) 406 RETURNING initiated_outgoing_transaction_id INTO init_id; 407 -- Register the bounce 408 INSERT INTO libeufin_nexus.bounced_transactions (incoming_transaction_id, initiated_outgoing_transaction_id) 409 VALUES (in_tx_id, init_id); 410 END IF; 411 END$$; 412 413 CREATE FUNCTION taler_transfer( 414 IN in_request_uid BYTEA, 415 IN in_wtid BYTEA, 416 IN in_subject TEXT, 417 IN in_amount taler_amount, 418 IN in_exchange_base_url TEXT, 419 IN in_credit_account_payto TEXT, 420 IN in_end_to_end_id TEXT, 421 IN in_timestamp INT8, 422 -- Error status 423 OUT out_request_uid_reuse BOOLEAN, 424 OUT out_wtid_reuse BOOLEAN, 425 -- Success return 426 OUT out_tx_row_id INT8, 427 OUT out_timestamp INT8 428 ) 429 LANGUAGE plpgsql AS $$ 430 BEGIN 431 -- Check for idempotence and conflict 432 SELECT (amount != in_amount 433 OR credit_payto != in_credit_account_payto 434 OR exchange_base_url != in_exchange_base_url 435 OR wtid != in_wtid) 436 ,transfer_operations.initiated_outgoing_transaction_id, initiation_time 437 INTO out_request_uid_reuse, out_tx_row_id, out_timestamp 438 FROM transfer_operations 439 JOIN initiated_outgoing_transactions 440 ON transfer_operations.initiated_outgoing_transaction_id=initiated_outgoing_transactions.initiated_outgoing_transaction_id 441 WHERE transfer_operations.request_uid = in_request_uid; 442 IF FOUND THEN 443 RETURN; 444 END IF; 445 out_wtid_reuse = EXISTS(SELECT FROM transfer_operations WHERE wtid = in_wtid); 446 IF out_wtid_reuse THEN 447 RETURN; 448 END IF; 449 out_timestamp=in_timestamp; 450 -- Initiate bank transfer 451 INSERT INTO initiated_outgoing_transactions ( 452 amount 453 ,subject 454 ,credit_payto 455 ,initiation_time 456 ,end_to_end_id 457 ) VALUES ( 458 in_amount 459 ,in_subject 460 ,in_credit_account_payto 461 ,in_timestamp 462 ,in_end_to_end_id 463 ) RETURNING initiated_outgoing_transaction_id INTO out_tx_row_id; 464 -- Register outgoing transaction 465 INSERT INTO transfer_operations( 466 initiated_outgoing_transaction_id 467 ,request_uid 468 ,wtid 469 ,exchange_base_url 470 ) VALUES ( 471 out_tx_row_id 472 ,in_request_uid 473 ,in_wtid 474 ,in_exchange_base_url 475 ); 476 out_timestamp = in_timestamp; 477 PERFORM pg_notify('nexus_outgoing_tx', out_tx_row_id::text); 478 END $$; 479 480 CREATE FUNCTION batch_outgoing_transactions( 481 IN in_timestamp INT8, 482 IN batch_ebics_id TEXT, 483 IN require_ack BOOLEAN 484 ) 485 RETURNS void 486 LANGUAGE plpgsql AS $$ 487 DECLARE 488 pending BOOLEAN; 489 batch_id INT8; 490 local_sum taler_amount DEFAULT (0, 0)::taler_amount; 491 tx record; 492 BEGIN 493 IF require_ack THEN 494 pending = EXISTS(SELECT FROM initiated_outgoing_transactions WHERE initiated_outgoing_batch_id IS NULL AND NOT awaiting_ack); 495 ELSE 496 pending = EXISTS(SELECT FROM initiated_outgoing_transactions WHERE initiated_outgoing_batch_id IS NULL); 497 END IF; 498 -- Create a new batch only if some transactions are not batched 499 IF (pending) THEN 500 -- Create batch 501 INSERT INTO initiated_outgoing_batches (creation_date, message_id) 502 VALUES (in_timestamp, batch_ebics_id) 503 RETURNING initiated_outgoing_batch_id INTO batch_id; 504 -- Link batched payment while computing the sum of amounts 505 FOR tx IN UPDATE initiated_outgoing_transactions 506 SET initiated_outgoing_batch_id=batch_id 507 WHERE initiated_outgoing_batch_id IS NULL 508 RETURNING amount 509 LOOP 510 SELECT sum.val, sum.frac 511 INTO local_sum.val, local_sum.frac 512 FROM amount_add(local_sum, tx.amount) AS sum; 513 END LOOP; 514 -- Update the batch with the sum of amounts 515 UPDATE initiated_outgoing_batches SET sum=local_sum WHERE initiated_outgoing_batch_id=batch_id; 516 END IF; 517 END $$; 518 519 CREATE FUNCTION batch_status_update( 520 IN in_message_id text, 521 IN in_status submission_state, 522 IN in_status_msg text, 523 OUT out_ok BOOLEAN 524 ) 525 LANGUAGE plpgsql AS $$ 526 DECLARE 527 local_batch_id INT8; 528 BEGIN 529 -- Check if there is a batch for this message id 530 SELECT initiated_outgoing_batch_id INTO local_batch_id 531 FROM initiated_outgoing_batches 532 WHERE message_id = in_message_id; 533 out_ok=FOUND; 534 IF FOUND THEN 535 -- Update unsettled batch status 536 UPDATE initiated_outgoing_batches 537 SET status = in_status, status_msg = in_status_msg 538 WHERE initiated_outgoing_batch_id = local_batch_id 539 AND status NOT IN ('success', 'permanent_failure', 'late_failure'); 540 541 -- When a batch succeed it doesn't mean that individual transaction also succeed 542 IF in_status = 'success' THEN 543 in_status = 'pending'; 544 END IF; 545 546 -- Update unsettled batch's transaction status 547 UPDATE initiated_outgoing_transactions 548 SET status = in_status, status_msg = in_status_msg 549 WHERE initiated_outgoing_batch_id = local_batch_id 550 AND status NOT IN ('success', 'permanent_failure', 'late_failure'); 551 END IF; 552 END $$; 553 554 CREATE FUNCTION tx_status_update( 555 IN in_end_to_end_id text, 556 IN in_message_id text, 557 IN in_status submission_state, 558 IN in_status_msg text, 559 OUT out_ok BOOLEAN 560 ) 561 LANGUAGE plpgsql AS $$ 562 DECLARE 563 local_status submission_state; 564 local_tx_id INT8; 565 BEGIN 566 -- Check current tx status 567 SELECT initiated_outgoing_transaction_id, status INTO local_tx_id, local_status 568 FROM initiated_outgoing_transactions 569 WHERE end_to_end_id = in_end_to_end_id; 570 out_ok=FOUND; 571 IF FOUND THEN 572 -- Update unsettled transaction status 573 IF in_status = 'permanent_failure' OR local_status NOT IN ('success', 'permanent_failure', 'late_failure') THEN 574 IF in_status = 'permanent_failure' AND local_status = 'success' THEN 575 in_status = 'late_failure'; 576 END IF; 577 UPDATE initiated_outgoing_transactions 578 SET status = in_status, status_msg = in_status_msg 579 WHERE initiated_outgoing_transaction_id = local_tx_id; 580 END IF; 581 582 -- Update unsettled batch status 583 UPDATE initiated_outgoing_batches 584 SET status = 'success', status_msg = NULL 585 WHERE message_id = in_message_id 586 AND status NOT IN ('success', 'permanent_failure', 'late_failure'); 587 END IF; 588 END $$;