「銷貨單確認」:修訂間差異
新頁面: ==主程式碼== <sql> SELECT TH001,TH002,TH003,TH004,TH005,TH006,TH007,TH008,UDF01,TH014,TH015,TH016 FROM COPTH WHERE TH020='N' AND TH004 LIKE '5%' </sql> ==銷貨數量是否足夠=... |
無編輯摘要 |
||
| (未顯示同一使用者於中間所作的 11 次修訂) | |||
| 第19行: | 第19行: | ||
</sql> | </sql> | ||
*DMC007若小於0則數量不足 | *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> | |||
*銷退 | |||
<sql> | |||
SELECT TJ001,TJ002,TJ003,TJ004,TJ005,TJ006,TJ008,TJ013,TJ007,TA001,TA002,TJ021 FROM COPTJ | |||
LEFT JOIN MOCTA ON TJ018=TA026 AND TJ019=TA027 AND TJ020=TA028 | |||
WHERE TJ021='Y' AND TJ004 LIKE '5%' | |||
</sql> | |||
*合併(v_INOUT;銷貨單來源透過訂單號取得製令號) | |||
<sql> | |||
SELECT TG001,TG002,TG003,TG004,TG005,TG006,TG007,TG010,TG011,TG014,TG015,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,TB018 FROM INVTB WHERE TB004 LIKE '5%' AND TB018='Y' | |||
UNION ALL | |||
SELECT TH001,TH002,TH003,TH004,TH005,TH006,TH009,TH007,TH008*-1 AS TH008,TA001,TA002,TH020 FROM COPTH | |||
JOIN INVMB ON MB001=TH004 | |||
LEFT JOIN MOCTA ON TH014=TA026 AND TH015=TA027 AND TH016=TA028 | |||
WHERE TH020='Y' AND TH004 LIKE '5%' | |||
UNION ALL | |||
SELECT TJ001,TJ002,TJ003,TJ004,TJ005,TJ006,TJ008,TJ013,TJ007,TA001,TA002,TJ021 FROM COPTJ | |||
LEFT JOIN MOCTA ON TJ018=TA026 AND TJ019=TA027 AND TJ020=TA028 | |||
WHERE TJ021='Y' AND TJ004 LIKE '5%' | |||
</sql> | |||
==製令狀態== | |||
<sql>SELECT TA001,TA002,TA006,TA034,TA035,TA015,TA017,TA007,TA026,TA027,TA028,TA013,TA011 FROM MOCTA WHERE TA006 LIKE '5%'</sql> | |||
==銷退單、訂單查詢== | |||
<sql>SELECT MA002,TJ001,TJ002,TJ003,TI003,TJ004,TJ005,TJ006,TD001,TD002,TD003,TD008,TD009,TD013 FROM COPTJ | |||
JOIN COPTI ON TI001=TJ001 AND TI002=TJ002 | |||
JOIN COPTD ON TJ018=TD001 AND TJ019=TD002 AND TJ020=TD003 | |||
JOIN COPMA ON MA001=TI004 | |||
WHERE TJ021='Y' AND TD021='Y' AND TD016='N' | |||
ORDER BY TD013 DESC | |||
</sql> | |||
於 2017年11月9日 (四) 14:11 的最新修訂
主程式碼
<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>
- 銷退
<sql> SELECT TJ001,TJ002,TJ003,TJ004,TJ005,TJ006,TJ008,TJ013,TJ007,TA001,TA002,TJ021 FROM COPTJ LEFT JOIN MOCTA ON TJ018=TA026 AND TJ019=TA027 AND TJ020=TA028 WHERE TJ021='Y' AND TJ004 LIKE '5%' </sql>
- 合併(v_INOUT;銷貨單來源透過訂單號取得製令號)
<sql> SELECT TG001,TG002,TG003,TG004,TG005,TG006,TG007,TG010,TG011,TG014,TG015,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,TB018 FROM INVTB WHERE TB004 LIKE '5%' AND TB018='Y' UNION ALL SELECT TH001,TH002,TH003,TH004,TH005,TH006,TH009,TH007,TH008*-1 AS TH008,TA001,TA002,TH020 FROM COPTH JOIN INVMB ON MB001=TH004 LEFT JOIN MOCTA ON TH014=TA026 AND TH015=TA027 AND TH016=TA028 WHERE TH020='Y' AND TH004 LIKE '5%' UNION ALL SELECT TJ001,TJ002,TJ003,TJ004,TJ005,TJ006,TJ008,TJ013,TJ007,TA001,TA002,TJ021 FROM COPTJ LEFT JOIN MOCTA ON TJ018=TA026 AND TJ019=TA027 AND TJ020=TA028 WHERE TJ021='Y' AND TJ004 LIKE '5%' </sql>
製令狀態
<sql>SELECT TA001,TA002,TA006,TA034,TA035,TA015,TA017,TA007,TA026,TA027,TA028,TA013,TA011 FROM MOCTA WHERE TA006 LIKE '5%'</sql>
銷退單、訂單查詢
<sql>SELECT MA002,TJ001,TJ002,TJ003,TI003,TJ004,TJ005,TJ006,TD001,TD002,TD003,TD008,TD009,TD013 FROM COPTJ JOIN COPTI ON TI001=TJ001 AND TI002=TJ002 JOIN COPTD ON TJ018=TD001 AND TJ019=TD002 AND TJ020=TD003 JOIN COPMA ON MA001=TI004 WHERE TJ021='Y' AND TD021='Y' AND TD016='N' ORDER BY TD013 DESC </sql>