46 lines
1.1 KiB
MySQL
46 lines
1.1 KiB
MySQL
|
DELIMITER $$
|
||
|
|
CREATE OR REPLACE DEFINER=`root`@`localhost` FUNCTION `vn`.`ekt_getTravel`(vEntryAssignFk INT, vEktFk INT)
|
||
|
|
RETURNS int(11)
|
||
|
|
NOT DETERMINISTIC
|
||
|
|
READS SQL DATA
|
||
|
|
BEGIN
|
||
|
|
/**
|
||
|
|
* Devuelve un vn.travel.id
|
||
|
|
*
|
||
|
|
* @param vEntryAssignFk Identificador de vn.entryAssign
|
||
|
|
*/
|
||
|
|
DECLARE vTravelFk INT;
|
||
|
|
|
||
|
|
IF vEntryAssignFk IS NOT NULL THEN
|
||
|
|
SELECT MAX(tr.id) INTO vTravelFk
|
||
|
|
FROM vn.travel tr
|
||
|
|
JOIN vn.ektEntryAssign ea ON ea.id = vEntryAssignFk
|
||
|
|
JOIN edi.ekt e ON e.id = vEktFk
|
||
|
|
WHERE ea.warehouseOutFk = tr.warehouseOutFk
|
||
|
|
AND ea.warehouseInFk = tr.warehouseInFk
|
||
|
|
AND ea.agencyModeFk = tr.agencyModeFk
|
||
|
|
AND tr.landed = e.fec;
|
||
|
|
|
||
|
|
IF vTravelFk IS NULL THEN
|
||
|
|
|
||
|
|
INSERT INTO vn.travel(landed, shipped, warehouseInFk, warehouseOutFk, agencyModeFk)
|
||
|
|
SELECT e.fec, e.fec, ea.warehouseInFk, ea.warehouseOutFk, ea.agencyModeFk
|
||
|
|
FROM edi.ekt e
|
||
|
|
JOIN vn.ektEntryAssign ea ON ea.kop = e.kop
|
||
|
|
WHERE e.id = vEktFk
|
||
|
|
ORDER BY
|
||
|
|
IF(ea.sub,1,0) * 1000 +
|
||
|
|
IF(ea.kop,1,0) * 100 +
|
||
|
|
IF(ea.pro,1,0) * 10 +
|
||
|
|
IF(ea.auction,1,0) DESC
|
||
|
|
LIMIT 1;
|
||
|
|
|
||
|
|
SET vTravelFk = LAST_INSERT_ID();
|
||
|
|
|
||
|
|
END IF;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
RETURN vTravelFk;
|
||
|
|
END$$
|
||
|
|
DELIMITER ;
|