「生產入庫單規劃」:修訂間差異
| 第21行: | 第21行: | ||
==SQL== | ==SQL== | ||
<sql> | <sql> | ||
SELECT TA001,TA002,TA015-TA017 AS MADEAMOUNT,TA015-TA016 AS SETS,ISNULL(UNUSE,'0')AS UNUSE,ISNULL(UNCKMOCTE,'0') AS UNCKMOCTE,TA013,TA011, | SELECT TA001,TA002,TA015,TA015-TA017 AS MADEAMOUNT,TA015-TA016 AS SETS,ISNULL(UNUSE,'0')AS UNUSE,ISNULL(UNCKMOCTE,'0') AS UNCKMOCTE,TA013,TA011, | ||
(SELECT CASE WHEN TA015-TA017>'0' AND TA015-TA016='0' AND UNUSE IS NULL AND UNCKMOCTE IS NULL AND TA013='Y' THEN 'OK' | (SELECT CASE WHEN TA015-TA017>'0' AND TA015-TA016='0' AND UNUSE IS NULL AND UNCKMOCTE IS NULL AND TA013='Y' THEN 'OK' | ||
WHEN TA015-TA017 | WHEN TA015-TA017='0' AND TA015-TA016='0' AND UNUSE IS NULL AND UNCKMOCTE IS NULL AND TA013='Y' THEN 'DONE' | ||
ELSE ' | WHEN TA017='0' AND TA016='0' AND UNUSE IS NULL AND UNCKMOCTE IS NULL AND TA011='y' THEN 'DONE' | ||
WHEN TA017='0' AND TA016='0' AND UNUSE IS NULL AND UNCKMOCTE IS NULL AND TA013='V' THEN 'DONE' | |||
WHEN TA013='N' THEN 'NO' | |||
ELSE 'CHECK' END)AS CK | |||
FROM MOCTA | FROM MOCTA | ||
LEFT JOIN | LEFT JOIN | ||
(SELECT TB001,TB002,COUNT(TB002) AS UNUSE FROM MOCTB WHERE TB004-TB005<>'0' AND TB011='1' AND TB001='5102' GROUP BY TB002,TB001) X | (SELECT TB001,TB002,COUNT(TB002) AS UNUSE FROM MOCTB WHERE TB004-TB005<>'0' AND TB011='1' AND TB001='5102' GROUP BY TB002,TB001) X | ||
ON TA001=TB001 AND TA002=TB002 | ON TA001=TB001 AND TA002=TB002 | ||
LEFT JOIN | LEFT JOIN | ||
(SELECT TE011,TE012,COUNT(TE012) AS UNCKMOCTE FROM MOCTE WHERE TE003='0001' AND TE011='5102' AND TE019='N' GROUP BY TE012,TE011) Y | (SELECT TE011,TE012,COUNT(TE012) AS UNCKMOCTE FROM MOCTE WHERE TE003='0001' AND TE011='5102' AND TE019='N' GROUP BY TE012,TE011) Y | ||
ON TA001=TE011 AND TA002=TE012 | ON TA001=TE011 AND TA002=TE012 | ||
LEFT JOIN | LEFT JOIN | ||
(SELECT TG014,TG015,COUNT(TG015) AS UNCKMOCTG FROM MOCTG WHERE TG014='5102' AND TG022='N' GROUP BY TG014,TG015) Z | (SELECT TG014,TG015,COUNT(TG015) AS UNCKMOCTG FROM MOCTG WHERE TG014='5102' AND TG022='N' GROUP BY TG014,TG015) Z | ||
於 2015年11月10日 (二) 11:54 的最新修訂
領料是否足夠
<sql> SELECT TB001,TB002,COUNT(TB002) AS UNUSE FROM MOCTB WHERE TB004-TB005<>'0' AND TB011='1' AND TB001='5102' GROUP BY TB002,TB001 </sql>
是否有未確認的領料單
<sql> SELECT TE011,TE012,COUNT(TE012) AS UCKMOCTE FROM MOCTE WHERE TE003='0001' AND TE011='5102' AND TE019='N' GROUP BY TE012,TE011 </sql>
是否有未確認的生產入庫單
<sql> SELECT TG014,TG015,COUNT(TG015) AS UCKMOCTG FROM MOCTG WHERE TG014='5102' AND TG022='N' GROUP BY TG014,TG015 </sql>
是否已有入庫數及需領套數是否足夠
<sql> SELECT TA001,TA002,TA015-TA017 AS MADEAMOUNT,TA015-TA016 AS SETS,TA013,TA011 FROM MOCTA WHERE TA015-TA017<>'0' AND TA001='5102' </sql>
SQL
<sql> SELECT TA001,TA002,TA015,TA015-TA017 AS MADEAMOUNT,TA015-TA016 AS SETS,ISNULL(UNUSE,'0')AS UNUSE,ISNULL(UNCKMOCTE,'0') AS UNCKMOCTE,TA013,TA011, (SELECT CASE WHEN TA015-TA017>'0' AND TA015-TA016='0' AND UNUSE IS NULL AND UNCKMOCTE IS NULL AND TA013='Y' THEN 'OK'
WHEN TA015-TA017='0' AND TA015-TA016='0' AND UNUSE IS NULL AND UNCKMOCTE IS NULL AND TA013='Y' THEN 'DONE' WHEN TA017='0' AND TA016='0' AND UNUSE IS NULL AND UNCKMOCTE IS NULL AND TA011='y' THEN 'DONE'
WHEN TA017='0' AND TA016='0' AND UNUSE IS NULL AND UNCKMOCTE IS NULL AND TA013='V' THEN 'DONE'
WHEN TA013='N' THEN 'NO'
ELSE 'CHECK' END)AS CK FROM MOCTA LEFT JOIN (SELECT TB001,TB002,COUNT(TB002) AS UNUSE FROM MOCTB WHERE TB004-TB005<>'0' AND TB011='1' AND TB001='5102' GROUP BY TB002,TB001) X ON TA001=TB001 AND TA002=TB002
LEFT JOIN (SELECT TE011,TE012,COUNT(TE012) AS UNCKMOCTE FROM MOCTE WHERE TE003='0001' AND TE011='5102' AND TE019='N' GROUP BY TE012,TE011) Y ON TA001=TE011 AND TA002=TE012
LEFT JOIN (SELECT TG014,TG015,COUNT(TG015) AS UNCKMOCTG FROM MOCTG WHERE TG014='5102' AND TG022='N' GROUP BY TG014,TG015) Z ON TA001=TG014 AND TA002=TG015 WHERE TA001='5102' </sql>