234 lines
8.5 KiB
PL/PgSQL
234 lines
8.5 KiB
PL/PgSQL
-- =====================================================================
|
|
-- 0020_void_reverses_movements.sql
|
|
--
|
|
-- Up to and including 0019, app.void_transaction() only flipped the
|
|
-- transactions.status flag to 'voided'. The original cash_movements,
|
|
-- float_movements, stock_movements and voucher_inventory rows that the
|
|
-- record_* functions had posted stayed in place, so the till expected
|
|
-- balance, OMT/Whish float balance and stock-on-hand were never
|
|
-- corrected. A cashier could record a $500 OMT_SEND, pocket the $500,
|
|
-- then void the txn five minutes later and the books would still show
|
|
-- $500 received in the till.
|
|
--
|
|
-- This migration makes void a true accounting reversal:
|
|
-- * for every cash_movements row tied to the txn we post an opposite-
|
|
-- signed `adjustment` row (sign guard from 0019 allows either sign
|
|
-- for adjustment),
|
|
-- * for every float_movements row we post an opposite-signed row,
|
|
-- * for every stock_movements row we post an opposite type
|
|
-- (sale_out -> adjustment_in, return_in -> adjustment_out, etc.)
|
|
-- with the void approver as approved_by,
|
|
-- * any voucher_inventory marked sold by the txn is returned to
|
|
-- `in_stock` so the serial can be re-sold.
|
|
--
|
|
-- The reversals reference the same ref_txn_id so reconciliation views
|
|
-- and the receipts trail keep them paired with the original posting.
|
|
-- =====================================================================
|
|
|
|
set search_path = app, public;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Helper: post compensating cash + float + stock + voucher rows for a
|
|
-- transaction that is being voided. Returns nothing; the caller is
|
|
-- responsible for flipping the txn status itself.
|
|
-- ---------------------------------------------------------------------
|
|
create or replace function app._reverse_movements_for_txn(
|
|
p_txn_id uuid,
|
|
p_approver uuid,
|
|
p_reason text
|
|
) returns void
|
|
language plpgsql
|
|
security definer
|
|
set search_path = app, public
|
|
as $$
|
|
declare
|
|
t app.transactions%rowtype;
|
|
r_cash record;
|
|
r_flt record;
|
|
r_stk record;
|
|
v_reverse_type app.stock_movement_type;
|
|
v_note text;
|
|
begin
|
|
select * into t from app.transactions where id = p_txn_id;
|
|
if t.id is null then
|
|
raise exception 'reverse: txn % not found', p_txn_id;
|
|
end if;
|
|
|
|
v_note := 'void reversal: ' || coalesce(p_reason, '');
|
|
|
|
-- ----- Cash legs --------------------------------------------------
|
|
-- Re-post each existing cash_movements row with opposite sign as an
|
|
-- 'adjustment' (the only cash_movement_type that the 0019 sign guard
|
|
-- lets carry either sign).
|
|
for r_cash in
|
|
select id, shift_id, currency, amount
|
|
from app.cash_movements
|
|
where ref_txn_id = p_txn_id
|
|
and type <> 'adjustment' -- don't reverse prior reversals
|
|
loop
|
|
insert into app.cash_movements(
|
|
shift_id, type, currency, amount, ref_txn_id, note, created_by
|
|
) values (
|
|
r_cash.shift_id,
|
|
'adjustment'::app.cash_movement_type,
|
|
r_cash.currency,
|
|
-r_cash.amount,
|
|
p_txn_id,
|
|
v_note,
|
|
p_approver
|
|
);
|
|
end loop;
|
|
|
|
-- ----- Float legs -------------------------------------------------
|
|
for r_flt in
|
|
select id, float_id, shift_id, amount
|
|
from app.float_movements
|
|
where ref_txn_id = p_txn_id
|
|
and coalesce(reason,'') not like 'void reversal%'
|
|
loop
|
|
insert into app.float_movements(
|
|
float_id, shift_id, amount, ref_txn_id, reason, created_by
|
|
) values (
|
|
r_flt.float_id,
|
|
r_flt.shift_id,
|
|
-r_flt.amount,
|
|
p_txn_id,
|
|
v_note,
|
|
p_approver
|
|
);
|
|
end loop;
|
|
|
|
-- ----- Stock legs -------------------------------------------------
|
|
-- For physical goods sold via record_goods_sale, reverse the
|
|
-- sale_out by posting an adjustment_in of equal magnitude (positive),
|
|
-- and vice versa for any in-bound rows tied to this txn.
|
|
for r_stk in
|
|
select id, sku, shop_id, shift_id, type, qty_delta, ref_lot_id
|
|
from app.stock_movements
|
|
where ref_txn_id = p_txn_id
|
|
and type not in ('adjustment_in','adjustment_out')
|
|
loop
|
|
if r_stk.qty_delta < 0 then
|
|
v_reverse_type := 'adjustment_in'::app.stock_movement_type;
|
|
else
|
|
v_reverse_type := 'adjustment_out'::app.stock_movement_type;
|
|
end if;
|
|
|
|
insert into app.stock_movements(
|
|
sku, shop_id, shift_id, type, qty_delta,
|
|
ref_txn_id, ref_lot_id, approved_by, reason, created_by
|
|
) values (
|
|
r_stk.sku, r_stk.shop_id, r_stk.shift_id,
|
|
v_reverse_type,
|
|
-r_stk.qty_delta,
|
|
p_txn_id, r_stk.ref_lot_id,
|
|
p_approver,
|
|
v_note,
|
|
p_approver
|
|
);
|
|
end loop;
|
|
|
|
-- ----- Voucher serials -------------------------------------------
|
|
-- Any voucher marked sold by this txn returns to in_stock so it can
|
|
-- be sold again. (The voucher_status_consistency CHECK clears
|
|
-- sold_txn_id / sold_at when status becomes 'in_stock'.)
|
|
update app.voucher_inventory
|
|
set status = 'in_stock',
|
|
sold_txn_id = null,
|
|
sold_at = null,
|
|
status_changed_by = p_approver,
|
|
status_change_reason = v_note
|
|
where sold_txn_id = p_txn_id
|
|
and status = 'sold';
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function app._reverse_movements_for_txn(uuid, uuid, text) from public;
|
|
-- Internal helper only — callable from void_transaction (security definer).
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Re-define void_transaction to reverse movements before flipping
|
|
-- status. We keep the same signature as 0003 so the existing UI calls
|
|
-- continue to work.
|
|
-- ---------------------------------------------------------------------
|
|
create or replace function app.void_transaction(
|
|
p_txn_id uuid,
|
|
p_reason text,
|
|
p_approver_pin text default null
|
|
) returns void
|
|
language plpgsql
|
|
security definer
|
|
set search_path = app, public
|
|
as $$
|
|
declare
|
|
t app.transactions%rowtype;
|
|
s app.shifts%rowtype;
|
|
window_min int;
|
|
needs_manager boolean;
|
|
v_approver uuid;
|
|
begin
|
|
select * into t from app.transactions where id = p_txn_id;
|
|
if t.id is null then raise exception 'transaction not found'; end if;
|
|
if t.status = 'voided' then raise exception 'transaction already voided'; end if;
|
|
if p_reason is null or length(btrim(p_reason)) < 5 then
|
|
raise exception 'a reason of at least 5 characters is required';
|
|
end if;
|
|
|
|
select * into s from app.shifts where id = t.shift_id;
|
|
if s.status <> 'open' then
|
|
raise exception 'cannot void a transaction whose shift is no longer open';
|
|
end if;
|
|
|
|
select coalesce(value::int, 10) into window_min
|
|
from app.system_settings where key = 'void_self_window_minutes';
|
|
|
|
needs_manager := (auth.uid() <> t.user_id)
|
|
or (now() - t.created_at > make_interval(mins => window_min));
|
|
|
|
if needs_manager then
|
|
if not app.has_role_in_shop(t.shop_id, 'manager') then
|
|
raise exception 'manager approval required to void this transaction';
|
|
end if;
|
|
if p_approver_pin is null or not app.verify_my_pin(p_approver_pin) then
|
|
raise exception 'manager PIN required and must be valid';
|
|
end if;
|
|
end if;
|
|
|
|
v_approver := auth.uid();
|
|
|
|
-- Reverse the money / stock / voucher legs FIRST. If any of these
|
|
-- inserts fails (e.g. stock would go negative because more vouchers
|
|
-- have been sold from the lot since), the whole void is rolled back
|
|
-- and the books stay consistent.
|
|
if t.status = 'completed' then
|
|
perform app._reverse_movements_for_txn(p_txn_id, v_approver, p_reason);
|
|
end if;
|
|
|
|
-- Flip the status (only this function may UPDATE app.transactions).
|
|
perform set_config('app.txn_internal', 'on', true);
|
|
update app.transactions
|
|
set status = 'voided',
|
|
voided_at = now(),
|
|
voided_by = v_approver,
|
|
void_reason = p_reason,
|
|
void_approved_by = case when needs_manager then v_approver else null end
|
|
where id = p_txn_id;
|
|
perform set_config('app.txn_internal', 'off', true);
|
|
|
|
-- Recompute the row's hash so the chain reflects the new state.
|
|
perform set_config('app.txn_internal', 'on', true);
|
|
update app.transactions tt
|
|
set row_hash = app.txn_compute_hash(tt, tt.prev_row_hash)
|
|
where id = p_txn_id;
|
|
perform set_config('app.txn_internal', 'off', true);
|
|
|
|
perform app.log_auth_event('txn_voided', t.shop_id, null,
|
|
jsonb_build_object('txn_id', p_txn_id,
|
|
'manager_path', needs_manager,
|
|
'reversed', t.status = 'completed'));
|
|
end;
|
|
$$;
|
|
|
|
revoke all on function app.void_transaction(uuid, text, text) from public;
|
|
grant execute on function app.void_transaction(uuid, text, text) to authenticated;
|