「銷貨單確認」:修訂間差異

出自TSK WIKI
跳至導覽 跳至搜尋
Root​(留言 | 貢獻)
Root​(留言 | 貢獻)
第34行: 第34行:
==隨貨附發票無發票號碼==
==隨貨附發票無發票號碼==
<sql>
<sql>
SELECT TH001,TH002,TH003,TH004,TH005,TH006,TH007,TH008,
SELECT TG003,TH001,TH002,TH003,TH004,TH005,TH006,TH007,TH008,MB064,
MB064-TH008 AS DMB064,MC007-TH008 AS DMC007,(SELECT CASE  
(SELECT CASE WHEN (MB064-TH008) < '0' THEN 'CHECK' ELSE 'OK' END ) AS DMB064,
(SELECT CASE WHEN MC007-TH008 < '0' THEN 'CHECK' ELSE 'OK' END ) AS DMC007,
(SELECT CASE  
WHEN TA006 IS NOT NULL AND TH004<>TA006 THEN 'CHECK'  
WHEN TA006 IS NOT NULL AND TH004<>TA006 THEN 'CHECK'  
WHEN TA006 IS NOT NULL AND TH004=TA006 THEN 'OK'  
WHEN TA006 IS NOT NULL AND TH004=TA006 THEN 'OK'  
第41行: 第43行:
(SELECT CASE WHEN TG061='Y' AND TG098='' THEN 'CHECK'
(SELECT CASE WHEN TG061='Y' AND TG098='' THEN 'CHECK'
WHEN TG061='Y' AND TG098<>'' OR TG061='N' THEN 'OK' END) AS CKCHECK,
WHEN TG061='Y' AND TG098<>'' OR TG061='N' THEN 'OK' END) AS CKCHECK,
TA001,TH014,TH015,TH016 FROM COPTH  
TH014,TH015,TH016 FROM COPTH  
JOIN INVMB ON MB001=TH004
JOIN INVMB ON MB001=TH004
LEFT JOIN INVMC ON MC001=TH004 AND TH007=MC002
LEFT JOIN INVMC ON MC001=TH004 AND TH007=MC002
LEFT JOIN MOCTA ON TA002=COPTH.UDF01
LEFT JOIN MOCTA ON TA002=COPTH.UDF01
JOIN COPTG ON TH001=TG001 AND TH002=TG002
JOIN COPTG ON TH001=TG001 AND TH002=TG002
WHERE TH020='N' AND TH004 LIKE '5%' AND TA001='5102'
WHERE TH020='N' AND TH004 LIKE '5%' AND TA001='5102'
</sql>
</sql>



於 2015年11月19日 (四) 09:20 的修訂

主程式碼

<sql> SELECT TH001,TH002,TH003,TH004,TH005,TH006,TH007,TH008,UDF01,TH014,TH015,TH016 FROM COPTH WHERE TH020='N' AND TH004 LIKE '5%' </sql>

銷貨數量是否足夠

<sql> SELECT TH001,TH002,TH003,TH004,TH005,TH006,TH007,MB064-TH008 AS DMB064,TH014,TH015,TH016 FROM COPTH JOIN INVMB ON MB001=TH004 WHERE TH020='N' AND TH004 LIKE '5%' </sql>

  • DMB064若小於0則數量不足

庫別數量不足

<sql> SELECT TH001,TH002,TH003,TH004,TH005,TH006,TH007,TH008,MB064-TH008 AS DMB064,MC007-TH008 AS DMC007,TH014,TH015,TH016 FROM COPTH JOIN INVMB ON MB001=TH004 LEFT JOIN INVMC ON MC001=TH004 AND TH007=MC002 WHERE TH020='N' AND TH004 LIKE '5%' </sql>

  • DMC007若小於0則數量不足

製令品號是否不同

<sql> SELECT TH001,TH002,TH003,TH004,TH005,TH006,TH007,TH008, MB064-TH008 AS DMB064,MC007-TH008 AS DMC007,(SELECT CASE WHEN TA006 IS NOT NULL AND TH004<>TA006 THEN 'CHECK' WHEN TA006 IS NOT NULL AND TH004=TA006 THEN 'OK' END) AS CKTH004, TA001,TH014,TH015,TH016 FROM COPTH JOIN INVMB ON MB001=TH004 LEFT JOIN INVMC ON MC001=TH004 AND TH007=MC002 LEFT JOIN MOCTA ON TA002=COPTH.UDF01 WHERE TH020='N' AND TH004 LIKE '5%' AND TA001='5102' </sql>

隨貨附發票無發票號碼

<sql> SELECT TG003,TH001,TH002,TH003,TH004,TH005,TH006,TH007,TH008,MB064, (SELECT CASE WHEN (MB064-TH008) < '0' THEN 'CHECK' ELSE 'OK' END ) AS DMB064, (SELECT CASE WHEN MC007-TH008 < '0' THEN 'CHECK' ELSE 'OK' END ) AS DMC007, (SELECT CASE WHEN TA006 IS NOT NULL AND TH004<>TA006 THEN 'CHECK' WHEN TA006 IS NOT NULL AND TH004=TA006 THEN 'OK' END) AS CKTH004, (SELECT CASE WHEN TG061='Y' AND TG098= THEN 'CHECK' WHEN TG061='Y' AND TG098<> OR TG061='N' THEN 'OK' END) AS CKCHECK, TH014,TH015,TH016 FROM COPTH JOIN INVMB ON MB001=TH004 LEFT JOIN INVMC ON MC001=TH004 AND TH007=MC002 LEFT JOIN MOCTA ON TA002=COPTH.UDF01 JOIN COPTG ON TH001=TG001 AND TH002=TG002 WHERE TH020='N' AND TH004 LIKE '5%' AND TA001='5102' </sql>

品號異動資訊

  • 生產入庫單

<sql> SELECT TG001,TG002,TG003,TG004,TG005,TG006,TG007,TG010,TG011,TG014,TG015,TG022 FROM MOCTG WHERE TG004 LIKE'5%' </sql>

  • 異動單

<sql> SELECT TB001,TB002,TB003,TB004,TB005,TB006,TB008,TB012,TB007,TB018,'000' AS TBTA01,'000' AS TBTA02 FROM INVTB WHERE TB004 LIKE '5%' </sql>

  • 銷貨

<sql> SELECT TH001,TH002,TH003,TH004,TH005,TH006,TH007,MB064-TH008 AS DMB064,TH014,TH015,TH016,TH020 FROM COPTH JOIN INVMB ON MB001=TH004 WHERE TH020='Y' AND TH004 LIKE '5%' </sql>

  • 合併(v_INOUT)

<sql> SELECT TG001,TG002,TG003,TG004,TG005,TG006,TG007,TG010,TG011,TG014,TG015, AS SMOCTG,TG022 FROM MOCTG WHERE TG004 LIKE '5%' AND TG022='Y' UNION ALL SELECT TB001,TB002,TB003,TB004,TB005,TB006,TB008,TB012,TB007,'000' AS TBTA01,'000' AS TBTA02, AS SINVTB,TB018 FROM INVTB WHERE TB004 LIKE '5%' AND TB018='Y' UNION ALL SELECT TH001,TH002,TH003,TH004,TH005,TH006,TH009,TH007,TH008,TH014,TH015,TH016,TH020 FROM COPTH JOIN INVMB ON MB001=TH004 WHERE TH020='Y' AND TH004 LIKE '5%' </sql>

製令狀態

SELECT TA001,TA002,TA006,TA034,TA035,TA015,TA017,TA007,TA026,TA027,TA028,TA013,TA011 FROM MOCTA WHERE TA006 LIKE '5%'