app/sql/release_stock_reservation.sql

-- FUNCTION: trans.release_stock_reservation(character varying, character varying, character varying, character varying, numeric, uuid, character varying)

-- DROP FUNCTION IF EXISTS trans.release_stock_reservation(character varying, character varying, character varying, character varying, numeric, uuid, character varying);

CREATE OR REPLACE FUNCTION trans.release_stock_reservation(
	p_merchant_id character varying,
	p_warehouse_id character varying,
	p_sku character varying,
	p_batch_no character varying,
	p_qty numeric,
	p_ref_id uuid,
	p_created_by character varying)
    RETURNS boolean
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
BEGIN
    -- Update stock reservation
    UPDATE trans.scm_stock
    SET qty_reserved = GREATEST(qty_reserved - p_qty, 0),
        qty_available = qty_on_hand - GREATEST(qty_reserved - p_qty, 0),
        last_updated_at = NOW()
    WHERE merchant_id = p_merchant_id
      AND warehouse_id = p_warehouse_id
      AND sku = p_sku
      AND batch_no = p_batch_no;
    
    RETURN TRUE;
END;
$BODY$;

ALTER FUNCTION trans.release_stock_reservation(character varying, character varying, character varying, character varying, numeric, uuid, character varying)
    OWNER TO trans_owner;