aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_tr_deposit_status_changed
Parameters
Name
Type
Mode
Definition
DECLARE _post_result json; _folio_id int; _folio_seq int; _refund_payment_id int; _balance t_money; _cancel_folio_item int; system_date date = fn_system_date(); user_name TEXT; shift t_shift; BEGIN /* Status: R = Receive, X = Cancelled, C = Clearing, H = Hold, N = No-Show */ /* Deposit Cancel */ IF (OLD.status IN ('R','H')) AND (NEW.status = 'X') AND (NEW.deposit_tran_id IS NOT NULL) THEN user_name := coalesce(user_name, fn_current_user()); shift := coalesce(shift, fn_work_shift(user_name), fn_online_shift()); IF system_date = (SELECT charge_date FROM transactions t WHERE t.id = NEW.deposit_tran_id) THEN WITH v AS ( UPDATE transactions t SET voided = TRUE WHERE t.id = NEW.deposit_tran_id RETURNING folio_item ) SELECT sp_recalc_folio_balance(v.folio_item) FROM v INTO _balance; PERFORM sp_log_trans(NEW.deposit_tran_id, 'Void', 'Void transaction (cancel deposit)', user_name, shift); --_post_result := sp_void_transaction(NEW.deposit_tran_id, 'Deposit cancelled'); --PERFORM sp_raise_error_if(_post_result->>'result_code' = '4', 'Cannot void deposit transaction, %s', _post_result->>'result_msg'); ELSE SELECT fi.folio_id, fi.folio_seq FROM transactions t LEFT JOIN folio_items fi ON fi.item_id = t.folio_item WHERE t.id = NEW.deposit_tran_id INTO _folio_id, _folio_seq; _refund_payment_id = fn_sys_param('DEPOSIT', 'REFUND_PAYMENT_ID', -1); IF _refund_payment_id = -1 THEN RAISE EXCEPTION SQLSTATE '99999' USING message = E'Cancel deposit error, deposit refund''s payment code is not specified in system parameter'; END IF; _post_result := sp_make_payment(_folio_id, _folio_seq, _refund_payment_id, NEW.amount, shift, user_name, NEW.id::TEXT, post_flag => 'D'); IF (_post_result->>'result_code' <> '4') THEN NEW.cancel_tran_id := (_post_result->'result_data'->>'id')::int; -- Moved deposit transactions to new folio item and close it _cancel_folio_item = sp_create_folio_item(_folio_id, _folio_seq, 'Deposit cancellation ('||NEW.id::TEXT||')'); UPDATE transactions SET folio_item = _cancel_folio_item WHERE id IN (NEW.deposit_tran_id, NEW.cancel_tran_id); UPDATE folio_items SET closed = TRUE, close_date = system_date, close_time = current_timestamp, close_user = user_name, close_shift = shift WHERE item_id = _cancel_folio_item; ELSE RAISE EXCEPTION SQLSTATE '99999' USING message = 'Cannot post deposit refund, '|| _post_result->>'result_msg'; END IF; END IF; END IF; -- Deposit Cancel RETURN NEW; END