salix/db/routines/stock/procedures/log_refreshOrder.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `stock`.`log_refreshOrder`(
	`vTableName` VARCHAR(255),
	`vTableId` INT)
BEGIN
	DECLARE vExpireTime INT DEFAULT 20;
	DECLARE vExpired DATETIME DEFAULT TIMESTAMPADD(MINUTE, -vExpireTime, util.VN_NOW());

	DROP TEMPORARY TABLE IF EXISTS tValues;
	CREATE TEMPORARY TABLE tValues
		ENGINE = MEMORY
		SELECT
				r.id rowFk,
				r.itemFk,
				r.warehouseFk,
				r.shipment shipped,
				r.amount quantity,
				r.created
			FROM hedera.orderRow r
				JOIN hedera.`order` o ON o.id = r.orderFk
			WHERE (
					vTableId IS NULL
					OR (vTableName = 'order' AND o.id = vTableId)
					OR (vTableName = 'orderRow' AND r.id = vTableId)
				)
				AND !o.confirmed
				AND r.shipment >= vn.getInventoryDate()
				AND r.created >= vExpired
				AND r.amount != 0;

	REPLACE INTO outbound (
			tableName, tableId, warehouseFk, dated,
			itemFk, created, expired, quantity
		)
		SELECT 'orderRow',
				rowFk,
				warehouseFk,
				shipped,
				itemFk,
				created,
				TIMESTAMPADD(MINUTE, vExpireTime, created),
				quantity
			FROM tValues;

	DROP TEMPORARY TABLE tValues;
END$$
DELIMITER ;