Spaces:
Running
Running
File size: 7,095 Bytes
733dc16 9798804 733dc16 9798804 733dc16 9798804 733dc16 9798804 733dc16 9798804 733dc16 9798804 733dc16 9798804 733dc16 9798804 733dc16 9798804 733dc16 9798804 733dc16 9798804 733dc16 9798804 733dc16 9798804 733dc16 9798804 733dc16 9798804 733dc16 9798804 733dc16 9798804 733dc16 9798804 733dc16 | 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 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 | -- Stock Management Stored Procedures
-- Centralized inventory mutation logic in PostgreSQL
-- Simplified stock movement function with idempotency
CREATE OR REPLACE FUNCTION apply_stock_movement(
p_merchant_id TEXT,
p_warehouse_id TEXT,
p_sku TEXT,
p_batch_no TEXT,
p_qty NUMERIC,
p_txn_type TEXT,
p_ref_type TEXT,
p_ref_id UUID,
p_ref_no TEXT,
p_user TEXT
) RETURNS VOID AS $$
BEGIN
-- Idempotency check - prevent duplicate processing
IF EXISTS (
SELECT 1 FROM scm_stock_ledger
WHERE ref_type = p_ref_type AND ref_id = p_ref_id AND sku = p_sku AND batch_no = p_batch_no
) THEN
RETURN;
END IF;
-- Insert ledger entry (immutable audit trail)
INSERT INTO scm_stock_ledger (
ledger_id, merchant_id, warehouse_id, sku, batch_no,
qty, txn_type, ref_type, ref_id, ref_no, created_by
) VALUES (
gen_random_uuid(), p_merchant_id, p_warehouse_id, p_sku, p_batch_no,
p_qty, p_txn_type, p_ref_type, p_ref_id, p_ref_no, p_user
);
-- Stock snapshot upsert (create or update)
INSERT INTO scm_stock (
stock_id, merchant_id, warehouse_id, sku, batch_no,
qty_on_hand, qty_reserved, qty_available
) VALUES (
gen_random_uuid(), p_merchant_id, p_warehouse_id, p_sku, p_batch_no,
p_qty, 0, p_qty
)
ON CONFLICT (merchant_id, warehouse_id, sku, batch_no)
DO UPDATE SET
qty_on_hand = scm_stock.qty_on_hand + p_qty,
qty_available = scm_stock.qty_available + p_qty,
last_updated_at = NOW();
END;
$$ LANGUAGE plpgsql;
-- Function to process multiple stock movements atomically (for GRN completion)
CREATE OR REPLACE FUNCTION apply_bulk_stock_movements(
p_movements JSONB
) RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
v_movement JSONB;
v_ledger_id UUID;
v_results JSONB := '[]'::JSONB;
v_result JSONB;
BEGIN
-- Process each movement in the array
FOR v_movement IN SELECT * FROM jsonb_array_elements(p_movements)
LOOP
-- Apply individual stock movement
SELECT apply_stock_movement(
(v_movement->>'merchant_id')::VARCHAR(64),
(v_movement->>'warehouse_id')::VARCHAR(64),
(v_movement->>'catalogue_id')::UUID,
(v_movement->>'sku')::VARCHAR(64),
(v_movement->>'batch_no')::VARCHAR(50),
(v_movement->>'exp_dt')::DATE,
(v_movement->>'qty')::NUMERIC(14,3),
(v_movement->>'uom')::VARCHAR(10),
(v_movement->>'txn_type')::VARCHAR(30),
(v_movement->>'ref_type')::VARCHAR(30),
(v_movement->>'ref_id')::UUID,
(v_movement->>'ref_no')::VARCHAR(50),
(v_movement->>'remarks')::TEXT,
(v_movement->>'created_by')::VARCHAR(64)
) INTO v_ledger_id;
-- Build result object
v_result := jsonb_build_object(
'ledger_id', v_ledger_id,
'sku', v_movement->>'sku',
'qty', v_movement->>'qty',
'success', true
);
-- Add to results array
v_results := v_results || v_result;
END LOOP;
RETURN v_results;
END;
$$;
-- Function to reserve stock (for sales orders)
CREATE OR REPLACE FUNCTION reserve_stock(
p_merchant_id VARCHAR(64),
p_warehouse_id VARCHAR(64),
p_sku VARCHAR(64),
p_batch_no VARCHAR(50),
p_qty NUMERIC(14,3),
p_ref_id UUID,
p_created_by VARCHAR(64)
) RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
DECLARE
v_available_qty NUMERIC(14,3);
BEGIN
-- Get current available quantity
SELECT qty_available
INTO v_available_qty
FROM scm_stock
WHERE merchant_id = p_merchant_id
AND warehouse_id = p_warehouse_id
AND sku = p_sku
AND batch_no = p_batch_no;
-- Check if sufficient stock available
IF v_available_qty IS NULL OR v_available_qty < p_qty THEN
RAISE EXCEPTION 'Insufficient available stock for SKU: %. Available: %, Required: %',
p_sku, COALESCE(v_available_qty, 0), p_qty;
END IF;
-- Update stock reservation
UPDATE scm_stock
SET qty_reserved = qty_reserved + p_qty,
qty_available = qty_available - p_qty,
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;
$$;
-- Function to release stock reservation
CREATE OR REPLACE FUNCTION release_stock_reservation(
p_merchant_id VARCHAR(64),
p_warehouse_id VARCHAR(64),
p_sku VARCHAR(64),
p_batch_no VARCHAR(50),
p_qty NUMERIC(14,3),
p_ref_id UUID,
p_created_by VARCHAR(64)
) RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
-- Update stock reservation
UPDATE 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;
$$;
-- Function to get current stock summary
CREATE OR REPLACE FUNCTION get_stock_summary(
p_merchant_id VARCHAR(64),
p_warehouse_id VARCHAR(64) DEFAULT NULL,
p_sku VARCHAR(64) DEFAULT NULL
) RETURNS TABLE (
stock_id UUID,
warehouse_id VARCHAR(64),
catalogue_id UUID,
sku VARCHAR(64),
batch_no VARCHAR(50),
qty_on_hand NUMERIC(14,3),
qty_reserved NUMERIC(14,3),
qty_available NUMERIC(14,3),
uom VARCHAR(10),
last_updated_at TIMESTAMP
)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
SELECT s.stock_id, s.warehouse_id, s.catalogue_id, s.sku, s.batch_no,
s.qty_on_hand, s.qty_reserved, s.qty_available, s.uom, s.last_updated_at
FROM scm_stock s
WHERE s.merchant_id = p_merchant_id
AND (p_warehouse_id IS NULL OR s.warehouse_id = p_warehouse_id)
AND (p_sku IS NULL OR s.sku = p_sku)
ORDER BY s.sku, s.batch_no;
END;
$$;
-- Function to get stock ledger history
CREATE OR REPLACE FUNCTION get_stock_ledger(
p_merchant_id VARCHAR(64),
p_sku VARCHAR(64) DEFAULT NULL,
p_batch_no VARCHAR(50) DEFAULT NULL,
p_limit INTEGER DEFAULT 100
) RETURNS TABLE (
ledger_id UUID,
warehouse_id VARCHAR(64),
catalogue_id UUID,
sku VARCHAR(64),
batch_no VARCHAR(50),
txn_type VARCHAR(30),
qty NUMERIC(14,3),
uom VARCHAR(10),
ref_type VARCHAR(30),
ref_id UUID,
ref_no VARCHAR(50),
remarks TEXT,
created_by VARCHAR(64),
created_at TIMESTAMP
)
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
SELECT l.ledger_id, l.warehouse_id, l.catalogue_id, l.sku, l.batch_no,
l.txn_type, l.qty, l.uom, l.ref_type, l.ref_id, l.ref_no,
l.remarks, l.created_by, l.created_at
FROM scm_stock_ledger l
WHERE l.merchant_id = p_merchant_id
AND (p_sku IS NULL OR l.sku = p_sku)
AND (p_batch_no IS NULL OR l.batch_no = p_batch_no)
ORDER BY l.created_at DESC
LIMIT p_limit;
END;
$$; |