「ERP數量異常查詢」:修訂間差異

出自TSK WIKI
跳至導覽 跳至搜尋
Root​(留言 | 貢獻)
新頁面: ==電線01庫存數量不符合== <sql> SELECT X.MF001,X.MF010,SMF010 FROM (SELECT MF001,SUM(MF010*MF008) AS MF010 FROM INVMF JOIN INVMB ON MB001=MF001 WHERE MF001 NOT LIKE '6%' AND...
 
 
(未顯示同一使用者於中間所作的 4 次修訂)
第1行: 第1行:
==電線01庫存數量不符合==
==電線01庫存數量不符合==
*原因為加工誤領,或修正誤領時批號選錯,前狀況應選批號000來修正
*暴力解決 直接做異動單 用下列sql查到的Q值寫入異動單中(已用-1校對正負值了)
<sql>
<sql>
SELECT * FROM(
SELECT RTRIM(MF001) MF001,SUM(MF008*MF010*-1) AS Q FROM INVMF
WHERE MF010<>0 AND MF002='000' AND MF007='01'
GROUP BY MF001)X
WHERE Q<>0
/*查特定誤領品號歷程*/
SELECT RTRIM(MF001) MF001,MF003,MF008*MF010 AS Q ,MF002,MF007,MF008
FROM INVMF WHERE MF001='TSS-522-BU' AND MF010<>0 AND MF002='000' AND MF007='01'
/*
SELECT X.MF001,X.MF010,SMF010 FROM  
SELECT X.MF001,X.MF010,SMF010 FROM  
(SELECT MF001,SUM(MF010*MF008) AS MF010 FROM INVMF  
(SELECT MF001,SUM(MF010*MF008) AS MF010 FROM INVMF  
第12行: 第25行:
WHERE MF010<>SMF010
WHERE MF010<>SMF010
//由電線01庫之批號000排除與不排除之進出數量合來判斷
//由電線01庫之批號000排除與不排除之進出數量合來判斷
*/
</sql>
</sql>

於 2021年9月8日 (三) 13:36 的最新修訂

電線01庫存數量不符合

  • 原因為加工誤領,或修正誤領時批號選錯,前狀況應選批號000來修正
  • 暴力解決 直接做異動單 用下列sql查到的Q值寫入異動單中(已用-1校對正負值了)

<sql> SELECT * FROM( SELECT RTRIM(MF001) MF001,SUM(MF008*MF010*-1) AS Q FROM INVMF WHERE MF010<>0 AND MF002='000' AND MF007='01' GROUP BY MF001)X WHERE Q<>0

/*查特定誤領品號歷程*/ SELECT RTRIM(MF001) MF001,MF003,MF008*MF010 AS Q ,MF002,MF007,MF008 FROM INVMF WHERE MF001='TSS-522-BU' AND MF010<>0 AND MF002='000' AND MF007='01'

/* SELECT X.MF001,X.MF010,SMF010 FROM (SELECT MF001,SUM(MF010*MF008) AS MF010 FROM INVMF JOIN INVMB ON MB001=MF001 WHERE MF001 NOT LIKE '6%' AND MF007='01' AND MF002<>'000' AND MB007='302' GROUP BY MF001)X JOIN (SELECT MF001,SUM(MF010*MF008) AS SMF010 FROM INVMF JOIN INVMB ON MB001=MF001 WHERE MF001 NOT LIKE '6%' AND MF007='01' AND MB007='302' GROUP BY MF001)Y ON X.MF001=Y.MF001 WHERE MF010<>SMF010 //由電線01庫之批號000排除與不排除之進出數量合來判斷

  • /

</sql>