Spaces:
Running
Running
File size: 2,440 Bytes
1e111cb 9798804 1e111cb | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 | -- FUNCTION: trans.apply_bulk_stock_movements(jsonb)
-- DROP FUNCTION IF EXISTS trans.apply_bulk_stock_movements(jsonb);
CREATE OR REPLACE FUNCTION trans.apply_bulk_stock_movements(
p_movements jsonb)
RETURNS jsonb
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
v_movement JSONB;
v_results JSONB := '[]'::JSONB;
v_result JSONB;
v_success BOOLEAN := TRUE;
v_error_msg TEXT;
BEGIN
-- Process each movement in the array
FOR v_movement IN SELECT * FROM jsonb_array_elements(p_movements)
LOOP
BEGIN
-- Apply individual stock movement using the 9-parameter function
PERFORM trans.apply_stock_movement(
(v_movement->>'merchant_id')::TEXT,
(v_movement->>'warehouse_id')::TEXT,
(v_movement->>'sku')::TEXT,
(v_movement->>'batch_no')::TEXT,
(v_movement->>'catalogue_id')::TEXT,
(v_movement->>'expiry_date')::DATE,
(v_movement->>'uom')::TEXT,
(v_movement->>'qty')::NUMERIC,
(v_movement->>'txn_type')::TEXT,
(v_movement->>'ref_type')::TEXT,
(v_movement->>'ref_id')::UUID,
(v_movement->>'created_by')::TEXT
);
-- Build success result object
v_result := jsonb_build_object(
'ledger_id', gen_random_uuid(),
'sku', v_movement->>'sku',
'qty', v_movement->>'qty',
'success', true,
'error', null
);
EXCEPTION WHEN OTHERS THEN
-- Handle individual movement errors
v_success := FALSE;
v_error_msg := SQLERRM;
v_result := jsonb_build_object(
'ledger_id', null,
'sku', v_movement->>'sku',
'qty', v_movement->>'qty',
'success', false,
'error', v_error_msg
);
END;
-- Add to results array
v_results := v_results || v_result;
END LOOP;
-- If any movement failed, rollback the entire transaction
IF NOT v_success THEN
RAISE EXCEPTION 'One or more stock movements failed. Transaction rolled back.';
END IF;
RETURN v_results;
END;
$BODY$;
ALTER FUNCTION trans.apply_bulk_stock_movements(jsonb)
OWNER TO trans_owner;
|