CREATE DEFINER=`root`@`%` PROCEDURE `RTX_RECEIPT_INSPECT_DATA`()
BEGIN
-- 声明变量
DECLARE RESULT_CODE INT DEFAULT FALSE; -- 默认false
DECLARE done INT DEFAULT FALSE; -- 默认false
DECLARE log_message TEXT;
DECLARE V_COUNT INT;
DECLARE V_QCREQUIRED VARCHAR(10);-- 收货需质检
DECLARE V_PLANEMOVETASK VARCHAR(10);-- 生成平移任务
DECLARE V_RECEIVESENDERP VARCHAR(10);-- 生成收货触发ERP
DECLARE V_RULEKEY VARCHAR(30);-- 入库规则编号
DECLARE V_POSTATUS VARCHAR(2); -- PO明细状态
DECLARE V_APICHANGED VARCHAR(2); -- 接口指令更改标记
DECLARE V_ERPLOC VARCHAR(50); -- ERP库位
DECLARE V_LOTTABLE01 VARCHAR(50); -- 源ERP库位
DECLARE V_INSPECTTYPE VARCHAR(10);
DECLARE V_RECEIPTKEY VARCHAR(20);
DECLARE V_RECEIPTLINENUMBER VARCHAR(20);
DECLARE V_STORERKEY VARCHAR(50);
DECLARE V_SKU VARCHAR(50);
DECLARE V_QTYRECEIVED DECIMAL(22, 5);
DECLARE V_UOM VARCHAR(5);
DECLARE V_LOTTABLE02 VARCHAR(50);
DECLARE V_RTXLOTTABLE14 VARCHAR(50);
DECLARE V_TOLOC VARCHAR(20);
DECLARE V_RTXMINPACKQTY DECIMAL(22, 5);
DECLARE V_FIRSTQC VARCHAR(50);
DECLARE V_rtxRcErpTaskStatus VARCHAR(1);
DECLARE V_POKEY VARCHAR(50);
DECLARE V_POLINENUMBER VARCHAR(15);
DECLARE V_TYPE VARCHAR(10);
DECLARE V_SUSR4 VARCHAR(50);
DECLARE V_RTXRECEIVESLOCATION VARCHAR(50);
-- 声明游标
DECLARE receiptDetail_cursor CURSOR FOR
SELECT CASE
WHEN R.TYPE = '7' OR (R.TYPE = '3' AND PS.DEPARTMENT = 'SP') THEN '1'
WHEN R.TYPE = '3' AND PS.DEPARTMENT = 'OM' THEN '4'
WHEN R.TYPE = '17' AND R.REFERENCELOCATION = 'RDM' THEN '3'
WHEN R.TYPE = '3' AND R.REFERENCELOCATION = 'RDM' THEN '3'
WHEN R.TYPE = '3' AND PS.DEPARTMENT <> 'OM' AND PS.DEPARTMENT <> 'SP' THEN '5'
WHEN R.TYPE = '15' OR R.TYPE = '40' THEN '12'
ELSE 'NONE' END AS INSPECTTYPE,
RD.RECEIPTKEY,
RD.RECEIPTLINENUMBER,
RD.STORERKEY,
RD.SKU,
RD.QTYRECEIVED,
RD.UOM,
RD.LOTTABLE02,
RD.RTXLOTTABLE14, -- 生产条码 || 收货的 供应商批号(次) || 条码信息表的供应商批次
RD.LOTTABLE01,
RD.TOLOC,
SKU.RTXMINPACKQTY,
RD.RTXFIRSTDELV AS FIRSTQC,
RD.rtxRcErpTaskStatus,
RD.POKEY,
RD.POLINENUMBER,
R.TYPE,
COALESCE(R.SUSR4, ' ') AS SUSR4,
RD.RTXRECEIVESLOCATION
FROM (SELECT * FROM RECEIPTDETAIL rdt WHERE rdt.editdate > DATE_SUB(NOW(), INTERVAL 60 DAY)) RD
INNER JOIN RECEIPT R ON RD.RECEIPTKEY = R.RECEIPTKEY
INNER JOIN SKU ON SKU.SKU = RD.SKU AND SKU.STORERKEY = RD.STORERKEY
LEFT JOIN RTXPLANTSLOACTION PS ON PS.PLANT = RD.RTXSHIPPLANT AND PS.SLOCATION = RD.RTXSHIPSLOCATION
WHERE R.RTXISVIRTUAL = '0'
AND RD.QTYRECEIVED > 0
AND RD.RECEIPTKEY <> '0000049014'
AND RD.STATUS NOT IN ('20')
AND (R.TYPE = '15' OR (R.TYPE <> '15' AND R.STATUS = '11'))
AND RD.QCSTATUS = 'N'
AND RD.QCREQUIRED = '1'
AND (RD.rtxRcErpTaskStatus = '1' OR EXISTS (SELECT 1
FROM TASKDETAIL
WHERE TASKTYPE = 'RTXPMV'
AND STATUS IN ('9', 'R', 'X')
AND SOURCEKEY = RD.RECEIPTKEY
AND ORDERLINENUMBER = RD.RECEIPTLINENUMBER));
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET RESULT_CODE = TRUE; #在执行过程中出任何异常设置result_code为true
-- 声明异常处理
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- 没值设置true
-- 开始事务
START TRANSACTION;
-- 删除不存在收货明细送检记录。
DELETE
FROM RTXQCSEND X
WHERE X.SOURCETYPE = 'RECEIPT'
AND STATUS = '0'
AND NOT EXISTS (SELECT 1
FROM RECEIPTDETAIL Y
WHERE X.ORDERKEY = Y.RECEIPTKEY
AND X.ORDERLINENUMBER = Y.RECEIPTLINENUMBER);
-- 打开游标
OPEN receiptDetail_cursor;
-- 循环读取数据
read_loop:
LOOP
-- 读取游标数据
FETCH receiptDetail_cursor INTO V_INSPECTTYPE, V_RECEIPTKEY, V_RECEIPTLINENUMBER, V_STORERKEY, V_SKU, V_QTYRECEIVED, V_UOM, V_LOTTABLE02, V_RTXLOTTABLE14, V_LOTTABLE01, V_TOLOC, V_RTXMINPACKQTY, V_FIRSTQC, V_rtxRcErpTaskStatus, V_POKEY, V_POLINENUMBER, V_TYPE, V_SUSR4, V_RTXRECEIVESLOCATION;
-- 判断是否到达游标末尾
IF done THEN
LEAVE read_loop;
END IF;
SET V_ERPLOC = IFNULL(V_LOTTABLE01, ' ');
-- 获取入库规则配置
SET V_RULEKEY = (SELECT RULEKEY FROM RECEIPT INNER JOIN RTXRECEIVERULERELATION ON WMSRECEIPTYPE = TYPE AND ERPRECEIPTYPE = RTXEXTTYPE WHERE RECEIPTKEY = V_RECEIPTKEY LIMIT 1);
SET log_message = CONCAT(log_message, '; 获取入库规则配置 = ', V_RULEKEY);
SELECT QCREQUIRED, PLANEMOVETASK, RECEIVESENDERP INTO V_QCREQUIRED, V_PLANEMOVETASK, V_RECEIVESENDERP FROM RTXRECEIVERULECONFIG WHERE RULEKEY = V_RULEKEY;
SET log_message = CONCAT(log_message, '; 收货需质检 = ', V_QCREQUIRED, '; 生成平移任务 = ', V_PLANEMOVETASK, '; 收货触发ERP = ', V_RECEIVESENDERP, '; 是否首次收货 = ', IFNULL(V_FIRSTQC, ''));
-- 入库规则必须已启用质检
IF V_QCREQUIRED = 'Y' THEN
-- 如果ASN明细有关联PO明细，则验证PO明细状态不能为取消或接口已更改
SELECT COUNT(1), MAX(STATUS), MAX(RTXCHANGED) INTO V_COUNT, V_POSTATUS, V_APICHANGED FROM PODETAIL WHERE POKEY = V_RECEIPTKEY AND POLINENUMBER = V_RECEIPTLINENUMBER;
SET log_message = CONCAT(log_message, '; ASN明细有关联PO明细大小 = ', V_COUNT, '; PO明细状态 = ', IFNULL(V_POSTATUS, ''), '; PO明细指令变更标识 = ', IFNULL(V_APICHANGED, ''));
IF V_COUNT > 0 THEN
IF V_POSTATUS > 11 OR V_APICHANGED = 'Y' THEN
SET log_message = CONCAT(log_message, 'PO明细状态 = ', IFNULL(V_POSTATUS, ''), '; PO明细指令变更标识 = ', IFNULL(V_APICHANGED, ''), '; 不满足业务跳出本次循环！');
LEAVE read_loop;
END IF;
END IF;
-- 如果入库规则已启用平移且收货明细已生成平移任务，则验证平移任务必须执行完毕
SET log_message = CONCAT(log_message, '生成平移任务是否为【Y】 = ', IFNULL(V_PLANEMOVETASK, ''), '; 生成平移任务是否为【1】 = ', IFNULL(V_FIRSTQC, ''));
IF V_PLANEMOVETASK = 'Y' AND V_FIRSTQC = '1' THEN
SELECT COUNT(1) INTO V_COUNT FROM TASKDETAIL WHERE TASKTYPE = 'RTXPMV' AND STATUS NOT IN ('9', 'R', 'X') AND SOURCEKEY = V_RECEIPTKEY AND ORDERLINENUMBER = V_RECEIPTLINENUMBER;
IF V_COUNT > 0 THEN
SET log_message = CONCAT(log_message, '; 收货明细启用平移切已生成平移任务，不存在不满足业务的数据，跳出本次循环！');
LEAVE read_loop;
END IF;
ELSEIF V_PLANEMOVETASK = 'Y' AND V_FIRSTQC <> '1' THEN
SET log_message = CONCAT(log_message, '; 收货明细启用平移切未生成平移任务。跳出本次循环！');
LEAVE read_loop;
END IF;
-- 如果入库规则已启用收货发送ERP过账，则收货明细必须已完成移动类型为103的ERP过账
-- IF V_RECEIVESENDERP = 'Y' THEN SELECT COUNT(1) INTO V_COUNT FROM RTXINPOSTTASK WHERE MOVETYPE = '103' AND ERPPOSTSTATUS IN ('PS', 'PC') AND ORDERKEY = V_RECEIPTKEY AND ORDERLINENUMBER = V_RECEIPTLINENUMBER; -- 如果收货单单头的UDF4为NO103QC时，未103过账，也允许送检
-- IF V_COUNT = 0 AND V_SUSR4 <> 'NO103QC' THEN LEAVE read_loop; END IF;
-- END IF;
-- 如果WMS订单类型 = ‘7’（采购订单），且工厂+ERP库位在工厂库存地配置的仓库类型为‘OCFB’(O厂分包)，则取收货明细 RTXRECEIVESLOCATION
SET log_message = CONCAT(log_message, 'WMS订单类型是否为【7】 = ', IFNULL(V_TYPE, ''), '; 仓库类型为【OCFB】 = ', IFNULL(V_ERPLOC, ''));
IF V_TYPE = '7' AND LENGTH(V_ERPLOC) > 4 THEN
SELECT COUNT(1) INTO V_COUNT FROM RTXPLANTSLOACTION WHERE PLANT = V_STORERKEY AND SLOCATION = V_ERPLOC AND TYPE = 'OCFB';
IF V_COUNT > 0 THEN
SET V_ERPLOC := V_RTXRECEIVESLOCATION;
SET log_message = CONCAT(log_message, '; WMS订单类型 = 7，接收库存地仓库类型为OCFB，不存在不满足业务的数据，跳出本次循环！');
END IF;
SET log_message = CONCAT(log_message, '; WMS订单类型 = 7，且工厂+ERP库位在工厂库存地配置的仓库类型为OCFB = ', V_COUNT);
END IF;
-- 插入QC送检记录
SET log_message = CONCAT(log_message, '; 插入QC送检记录');
INSERT INTO RTXQCSEND
(WHSEID, INSPECTTYPE, ORDERKEY, ORDERLINENUMBER, STORERKEY, SKU, QCTOINSPECT, UOM, SUPPLIER, SUPPLIERLOT, PLANT, SLOCATION, QCLOC, PACKQTY, ISFIRSTRECEIVE, STATUS, SOURCETYPE, ADDWHO, EDITWHO, QCRESULT, QCQTYREJECTED, QCQTYCONCEDE)
VALUES
(DATABASE(), V_INSPECTTYPE, V_RECEIPTKEY, V_RECEIPTLINENUMBER, V_STORERKEY, V_SKU, V_QTYRECEIVED, V_UOM, IFNULL(V_LOTTABLE02, ' '), IFNULL(V_RTXLOTTABLE14, ' '), V_STORERKEY, V_ERPLOC, V_TOLOC, IFNULL(V_RTXMINPACKQTY, '0'), IFNULL(V_FIRSTQC, ' '), '0', 'RECEIPT', 'mysqlJob', 'mysqlJob', '', 0, 0);
-- 更新收货明细的QC状态为已发送
SET log_message = CONCAT(log_message, '; 更新收货明细的QC状态为已发送');
UPDATE RECEIPTDETAIL SET QCSTATUS ='S' WHERE RECEIPTKEY = V_RECEIPTKEY AND RECEIPTLINENUMBER = V_RECEIPTLINENUMBER;
END IF;
END LOOP read_loop;
CLOSE receiptDetail_cursor;
IF RESULT_CODE THEN
-- 异常发生时进行回滚
ROLLBACK;
ELSE
-- 提交事务
COMMIT;
END IF;
SET log_message = CONCAT('RTX_RECEIPT_INSPECT_DATA 不满足条件 = ', done, '； 异常状态 = ', RESULT_CODE);
SELECT log_message;
INSERT INTO procedure_log
(procedure_name, execution_time, log_message)
VALUES
('RTX_RECEIPT_INSPECT_DATA', NOW(), log_message);
END关于在存储中获取编号
-- 获取下一个任务键值
SELECT CONCAT('TK_JOB_', DATE_FORMAT(NOW(), '%y%m%d'), '_',
CONCAT(LPAD(IFNULL(MAX(CAST(SUBSTRING(TASKDETAILKEY, 15) AS UNSIGNED)) + 1, 1), 6, '0')))
INTO V_TASKDETAIL
FROM TASKDETAIL
WHERE TASKDETAILKEY LIKE CONCAT('TK_JOB_', DATE_FORMAT(NOW(), '%y%m%d'), '%');
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
