create procedure procedure2(
in p1 VARCHAR(50),
in p2 VARCHAR(50)
)
begin
SELECT
max(d.opName) AS opName,
max(d.scrapQty) AS scrapQty,
max(d.scrapName) AS scrapName,
max(d.finishQty) AS finishQty,
max(d.ngRate) AS ngRate,
max(d.batchNo) AS batchNo,
ROUND(sum(e.scrapQty) / max(d.totalScrapQty), 4) * 100 AS allRate
FROM
(
SELECT
@rownum :=@rownum + 1 AS rownum,
a.*, c.*
FROM
(
SELECT
op.OP_NAME AS opName,
sum(s.SCRAP_CELL_QTY) AS scrapQty,
s.SCRAP_NAME AS scrapName,
round(nvl(SUM(o.FINISH_QTY), 0), 0) AS finishQty,
round(sum(s.SCRAP_CELL_QTY) /(sum(s.SCRAP_CELL_QTY) + nvl(SUM(o.FINISH_QTY), 0)), 4) * 100 AS ngRate,
s.BATCH_NO AS batchNo
FROM
tt_wo_batch_scrap s
LEFT JOIN tm_operation op ON op.PK_ID = s.OP_ID
LEFT JOIN tt_wo_order o ON o.BATCH_NO = s.BATCH_NO
AND o.OP_ID = s.OP_ID
WHERE
s.DEL_FLAG = '0'
AND s.BATCH_NO = p1
AND s.OP_ID = p2
GROUP BY
s.SCRAP_NAME
ORDER BY
s.SCRAP_CELL_QTY DESC
) a,
(SELECT @rownum := 0) t,
(
SELECT
sum(s.SCRAP_CELL_QTY) AS totalScrapQty
FROM
tt_wo_batch_scrap s
WHERE
s.DEL_FLAG = '0'
AND s.BATCH_NO = p1
AND s.OP_ID = p2
) c
) d
LEFT JOIN (
SELECT
@rownum2 :=@rownum2 + 1 AS rownum,
a.*
FROM
(
SELECT
sum(s.SCRAP_CELL_QTY) AS scrapQty
FROM
tt_wo_batch_scrap s
WHERE
s.DEL_FLAG = '0'
AND s.BATCH_NO = p1
AND s.OP_ID = p2
GROUP BY
s.SCRAP_NAME
ORDER BY
s.SCRAP_CELL_QTY DESC
) a,
(SELECT @rownum2 := 0) t
) e ON d.rownum >= e.rownum
GROUP BY
d.rownum
ORDER BY
d.rownum ASC;
end;
call procedure2('201852302', '40288a65627533840162756b7b780047');
