「ERP Debug SQL code」:修訂間差異

出自TSK WIKI
跳至導覽 跳至搜尋
Root​(留言 | 貢獻)
Root​(留言 | 貢獻)
 
(未顯示同一使用者於中間所作的 3 次修訂)
第1行: 第1行:
==查加工品零件BOM用量是否為整數==
*從BOM
<sql>
SELECT MD001,MD002,MD003,MB002,MB003,MD004,MD006,MD007,(MD006/MD007)%1 AS M67,MD017,MB007 FROM BOMMD
JOIN INVMB ON MD003=MB001
WHERE MB007='303'  AND (MD006/MD007)%1<>'0' AND MD004<>'M' AND (MD017='1' OR MD017='5')
</sql>
*從製令單身
<sql>
SELECT TB001,TB002,TB003,MB003,MB003,TB004,TB007,TB011,MB007,TB018 FROM MOCTB JOIN INVMB ON MB001=TB003
WHERE MB007='303' AND TB004%1<>'0' AND TB007<>'M' AND TB018<>'V' AND (TB011='1' OR TB001='5')
</sql>
*從領料單單身
<sql>
SELECT TE001,TE002,TE003,TE004,TE005,TE006,TE011,TE012,TE016,TE017,TE018,MB007,TE019 FROM MOCTE
JOIN INVMB ON MB001=TE004
WHERE MB007='303' AND TE005%1<>'0' AND TE006<>'M' AND TE019<>'V' AND (TE016='1' OR TE016='5')
</sql>
*查庫存
<sql>
SELECT MC001,MB003,MB003,MC002,MC007,MB004,MB007 FROM INVMC JOIN INVMB ON MB001=MC001 WHERE MB007='303' AND MB004<>'M' AND MC007%1<>'0'
</sql>
==訂單品號與製令品號不符SQL碼==
==訂單品號與製令品號不符SQL碼==
<sql>
<sql>
第6行: 第29行:
WHERE TA011<>'V' AND TA011<>'Y' AND TD004<>TA006 AND TA013<>'V' AND TA006 NOT LIKE'53%' ORDER BY TC006
WHERE TA011<>'V' AND TA011<>'Y' AND TD004<>TA006 AND TA013<>'V' AND TA006 NOT LIKE'53%' ORDER BY TC006
</sql>
</sql>
==已發製令未建BOM查詢==
==已發製令未建BOM查詢==
<sql>
<sql>
第16行: 第40行:
</sql>
</sql>
==BOM電線用量異常檢視==
==BOM電線用量異常檢視==
#以訂單品號
*以訂單品號
<sql>
<sql>
SELECT RTRIM(MD001) AS MD001,X.MB002,X.MB003,MD002,RTRIM(MD003)  
SELECT RTRIM(MD001) AS MD001,X.MB002,X.MB003,MD002,RTRIM(MD003)  
第26行: 第50行:
JOIN (SELECT TD004 FROM COPTD GROUP BY TD004)Z ON TD004=MD001
JOIN (SELECT TD004 FROM COPTD GROUP BY TD004)Z ON TD004=MD001
</sql>
</sql>
#以製令檢視
*以製令檢視
<sql>
<sql>
SELECT TA001,TA002,RTRIM(MD001) AS MD001,X.MB002,X.MB003,MD002,RTRIM(MD003)  
SELECT TA001,TA002,RTRIM(MD001) AS MD001,X.MB002,X.MB003,MD002,RTRIM(MD003)  

於 2015年11月25日 (三) 14:59 的最新修訂

查加工品零件BOM用量是否為整數

  • 從BOM

<sql> SELECT MD001,MD002,MD003,MB002,MB003,MD004,MD006,MD007,(MD006/MD007)%1 AS M67,MD017,MB007 FROM BOMMD JOIN INVMB ON MD003=MB001 WHERE MB007='303' AND (MD006/MD007)%1<>'0' AND MD004<>'M' AND (MD017='1' OR MD017='5') </sql>

  • 從製令單身

<sql> SELECT TB001,TB002,TB003,MB003,MB003,TB004,TB007,TB011,MB007,TB018 FROM MOCTB JOIN INVMB ON MB001=TB003 WHERE MB007='303' AND TB004%1<>'0' AND TB007<>'M' AND TB018<>'V' AND (TB011='1' OR TB001='5') </sql>

  • 從領料單單身

<sql> SELECT TE001,TE002,TE003,TE004,TE005,TE006,TE011,TE012,TE016,TE017,TE018,MB007,TE019 FROM MOCTE JOIN INVMB ON MB001=TE004 WHERE MB007='303' AND TE005%1<>'0' AND TE006<>'M' AND TE019<>'V' AND (TE016='1' OR TE016='5') </sql>

  • 查庫存

<sql> SELECT MC001,MB003,MB003,MC002,MC007,MB004,MB007 FROM INVMC JOIN INVMB ON MB001=MC001 WHERE MB007='303' AND MB004<>'M' AND MC007%1<>'0' </sql>

訂單品號與製令品號不符SQL碼

<sql> SELECT TA011,TC006,TC004,TD001,TD002,TD003,TA001,TA002,TD004,TA006,TD005,TA034,TD006,TA035 FROM COPTD JOIN MOCTA ON TD001=TA026 AND TD002=TA027 AND TD003=TA028 JOIN COPTC ON TD001=TC001 AND TD002=TC002 WHERE TA011<>'V' AND TA011<>'Y' AND TD004<>TA006 AND TA013<>'V' AND TA006 NOT LIKE'53%' ORDER BY TC006 </sql>

已發製令未建BOM查詢

<sql> SELECT TA001,TA002,TA004,AMOUNT,TA006,TA034,TA035,TA013,TA011,TA010,TD020,COPTD.UDF01 FROM MOCTA LEFT JOIN (SELECT TB002,COUNT(TB003) AS AMOUNT FROM MOCTB WHERE TB001='5102' AND TB003<>'Z001' AND TB002 >='151109001' GROUP BY TB002)X ON TA002=TB002 JOIN COPTD ON TA026=TD001 AND TA027=TD002 AND TA028=TD003 WHERE TA001='5102' AND TA002 >='151109001' ORDER BY AMOUNT </sql>

BOM電線用量異常檢視

  • 以訂單品號

<sql> SELECT RTRIM(MD001) AS MD001,X.MB002,X.MB003,MD002,RTRIM(MD003) AS MD003,Y.MB002 AS YMB002,MD006/MD007 AS AMOUNT,MD007,MD004,Y.MB007,MD017, (SELECT CASE WHEN CHARINDEX('x1C',Y.MB003)>0 THEN '1' ELSE '2' END) AS CORE ,BOMMD.MODI_DATE FROM BOMMD JOIN INVMB X ON MD001=X.MB001 JOIN INVMB Y ON MD003=Y.MB001 JOIN (SELECT TD004 FROM COPTD GROUP BY TD004)Z ON TD004=MD001 </sql>

  • 以製令檢視

<sql> SELECT TA001,TA002,RTRIM(MD001) AS MD001,X.MB002,X.MB003,MD002,RTRIM(MD003) AS MD003,Y.MB002 AS YMB002,MD006/MD007 AS AMOUNT,MD007,MD004,Y.MB007,MD017, (SELECT CASE WHEN CHARINDEX('x1C',Y.MB003)>0 THEN '1' ELSE '2' END) AS CORE ,BOMMD.MODI_DATE FROM BOMMD JOIN INVMB X ON MD001=X.MB001 JOIN INVMB Y ON MD003=Y.MB001 JOIN (SELECT TA001,TA002,TA006 FROM MOCTA)Z ON TA006=MD001 WHERE TA001='5102' AND TA002 >'151101001' AND MD017='1' AND MD009='HM01' AND Y.MB007='302' </sql>