33 lines
827 B
SQL
33 lines
827 B
SQL
DELIMITER $$
|
|
CREATE OR REPLACE DEFINER=`vn`@`localhost` FUNCTION `vn`.`buy_getLastWithoutInventory`(
|
|
vItemFk INT,
|
|
vWarehouseFk INT
|
|
)
|
|
RETURNS int(11)
|
|
DETERMINISTIC
|
|
BEGIN
|
|
/**
|
|
* Retorna la última compra que no sea inventario.
|
|
*
|
|
* @param vItemFk Id del artículo
|
|
* @param vWarehouseFk Id del almacén
|
|
* @return Id de compra
|
|
*/
|
|
DECLARE vBuyFk INT;
|
|
|
|
SELECT b.id INTO vBuyFk
|
|
FROM buy b
|
|
JOIN entry e ON e.id = b.entryFk
|
|
JOIN travel t ON t.id = e.travelFk
|
|
WHERE e.id <> (SELECT defaultEntry FROM entryConfig)
|
|
AND e.supplierFk <> (SELECT supplierFk FROM inventoryConfig)
|
|
AND e.typeFk <> 'inventory'
|
|
AND b.itemFk = vItemFk
|
|
AND (t.warehouseInFk = vWarehouseFk OR t.warehouseInFk IS NULL)
|
|
ORDER BY ABS(DATEDIFF(util.VN_CURDATE(), t.landed)), e.created DESC
|
|
LIMIT 1;
|
|
|
|
RETURN vBuyFk;
|
|
END$$
|
|
DELIMITER ;
|