Spaces:
Running
Running
File size: 4,824 Bytes
1e111cb 9798804 1e111cb 9798804 1e111cb 9798804 1e111cb 9798804 1e111cb 9798804 1e111cb 9798804 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 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 | -- FUNCTION: trans.apply_stock_movement(text, text, text, text, text, date, text, numeric, text, text, uuid, text)
-- DROP FUNCTION IF EXISTS trans.apply_stock_movement(text, text, text, text, text, date, text, numeric, text, text, uuid, text);
CREATE OR REPLACE FUNCTION trans.apply_stock_movement(
p_merchant_id text,
p_warehouse_id text,
p_sku text,
p_batch_no text,
p_catalogue_id text,
p_expiry_date date,
p_uom text,
p_qty numeric,
p_txn_type text,
p_ref_type text,
p_ref_id uuid,
p_user text)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
v_ledger_id uuid := gen_random_uuid();
v_qty_change numeric;
BEGIN
----------------------------------------------------------------
-- Idempotency check
----------------------------------------------------------------
IF EXISTS (
SELECT 1
FROM trans.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;
----------------------------------------------------------------
-- Determine quantity change based on transaction type
----------------------------------------------------------------
CASE p_txn_type
WHEN 'GRN_IN' THEN
v_qty_change := p_qty; -- Add stock
WHEN 'ADJUST_IN' THEN
v_qty_change := p_qty; -- Add stock
WHEN 'RETURN_IN' THEN
v_qty_change := p_qty; -- Add stock
WHEN 'TRANSFER_IN' THEN
v_qty_change := p_qty; -- Add stock
WHEN 'ADJUST_OUT' THEN
v_qty_change := -p_qty; -- Subtract stock (negate the positive qty)
WHEN 'SALE_OUT' THEN
v_qty_change := -p_qty; -- Subtract stock
WHEN 'TRANSFER_OUT' THEN
v_qty_change := -p_qty; -- Subtract stock
ELSE
RAISE EXCEPTION 'Unknown transaction type: %', p_txn_type;
END CASE;
Raise Notice 'in';
RAISE NOTICE 'Transaction type: %, Input qty: %, Calculated change: %',
p_txn_type, p_qty, v_qty_change;
RAISE NOTICE 'Inserting ledger: %, %, %, %, %, %, %, %, %, %, %',
v_ledger_id,
p_merchant_id,
p_warehouse_id,
p_sku,
p_batch_no,
p_txn_type,
v_qty_change, -- Store the calculated change in ledger
p_ref_type,
p_ref_id,
p_user,
NOW();
----------------------------------------------------------------
-- Ledger insert (store the ACTUAL qty change with proper sign)
----------------------------------------------------------------
INSERT INTO trans.scm_stock_ledger (
ledger_id,
merchant_id,
warehouse_id,
sku,
batch_no,
txn_type,
qty,
ref_type,
ref_id,
created_by,
created_at
) VALUES (
v_ledger_id,
p_merchant_id,
p_warehouse_id,
p_sku,
p_batch_no,
p_txn_type,
v_qty_change, -- Store with correct sign
p_ref_type,
p_ref_id,
p_user,
NOW()
);
RAISE NOTICE 'Stock ledger done';
----------------------------------------------------------------
-- Stock snapshot upsert (ALL TABLE COLUMNS)
----------------------------------------------------------------
INSERT INTO trans.scm_stock (
merchant_id,
warehouse_id,
sku,
batch_no,
catalogue_id,
uom,
qty_on_hand,
qty_reserved,
qty_available,
cost_price,
expiry_date,
ledger_id,
created_at,
updated_at
) VALUES (
p_merchant_id,
p_warehouse_id,
p_sku,
p_batch_no,
p_catalogue_id,
p_uom,
GREATEST(v_qty_change, 0), -- Use calculated change (don't allow negative on insert)
0, -- qty_reserved
GREATEST(v_qty_change, 0), -- qty_available
NULL, -- cost_price
p_expiry_date, -- expiry_date
v_ledger_id,
NOW(),
NOW()
)
ON CONFLICT (merchant_id, warehouse_id, catalogue_id, batch_no)
DO UPDATE SET
qty_on_hand = GREATEST(trans.scm_stock.qty_on_hand + v_qty_change, 0),
qty_available = GREATEST((trans.scm_stock.qty_on_hand + v_qty_change) - trans.scm_stock.qty_reserved, 0),
ledger_id = v_ledger_id,
updated_at = NOW();
RAISE NOTICE 'Stock updated. Final qty change applied: %', v_qty_change;
END;
$BODY$;
ALTER FUNCTION trans.apply_stock_movement(text, text, text, text, text, date, text, numeric, text, text, uuid, text)
OWNER TO trans_owner;
|