42 lines
909 B
SQL
42 lines
909 B
SQL
DELIMITER $$
|
|
CREATE OR REPLACE DEFINER=`vn`@`localhost` FUNCTION `vn`.`itemPacking`(vBarcode VARCHAR(22), vWarehouseFk INT)
|
|
RETURNS int(11)
|
|
DETERMINISTIC
|
|
BEGIN
|
|
|
|
DECLARE vPacking INTEGER DEFAULT 0;
|
|
DECLARE vItemFk INTEGER;
|
|
|
|
SELECT vn.barcodeToItem(vBarcode) INTO vItemFk;
|
|
|
|
SELECT IFNULL(packing,0) INTO vPacking
|
|
FROM vn.buy
|
|
WHERE id = CAST(vBarcode AS DECIMAL(18,0));
|
|
|
|
|
|
|
|
|
|
IF NOT vPacking THEN
|
|
CALL cache.last_buy_refresh(FALSE);
|
|
SELECT IFNULL(packing,1) INTO vPacking
|
|
FROM
|
|
(SELECT packing , created
|
|
FROM vn.itemShelving
|
|
WHERE itemFk = vItemFk
|
|
UNION ALL
|
|
SELECT b.packing, landing
|
|
FROM vn.buy b
|
|
JOIN cache.last_buy lb ON lb.buy_id = b.id
|
|
WHERE lb.warehouse_id = vWarehouseFk
|
|
AND b.itemFk = vItemFk
|
|
) packings
|
|
ORDER BY created desc
|
|
LIMIT 1;
|
|
|
|
END IF;
|
|
|
|
RETURN vPacking;
|
|
|
|
END$$
|
|
DELIMITER ;
|