加工庫存盤點

出自TSK WIKI
於 2015年11月27日 (五) 12:42 由 Root​(留言 | 貢獻) 所做的修訂 (→400號製令連結)
跳至導覽 跳至搜尋

製令庫存

<sql> SELECT MA002,TA002,TD004,TD005,TD006,製庫存,MB064,TD010,TA014,TD013 FROM (SELECT MA002,TA002,TD004,TD005,TD006,TA017-TD009 AS 製庫存,TD010,TA014,TD013 FROM v_ORDER WHERE MB007='301' AND TD016='N' AND TA017-TD009 >'0')X JOIN INVMB ON MB001=TD004 WHERE TD004='521000327' ORDER BY MA002 </sql>

400號製令連結

  • LRP開製令 製令 TA003計劃批號 TA028 序號
  • 訂單 TD006計畫批號 TD015預測代號 TD015預測序號
    • 400-20151023-001 <->

各項進出

<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 v_NEWTA002 ON TH014=TD001 AND TH015=TD002 AND TH016=TD003 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%' AND TA011='Y' </sql>

<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, (SELECT CASE WHEN CHARINDEX('銷',TH018)>'0' AND CHARINDEX('APR',TH018)='0' AND (CHARINDEX('製令',TH018)>'0' OR CHARINDEX('工單',TH018)>'0') THEN (SELECT CASE WHEN SUBSTRING(SUBSTRING(REPLACE(TH018,':',':'),CHARINDEX(':',REPLACE(TH018,':',':'))+1,9),3,1)>'1' THEN 'APR' ELSE SUBSTRING(REPLACE(TH018,':',':'),CHARINDEX(':',REPLACE(TH018,':',':'))+1,9)END) WHEN CHARINDEX('APR',TH018)<>'0' THEN 'APR' ELSE TA002 END)AS TA002

,TH020 FROM COPTH JOIN INVMB ON MB001=TH004 LEFT JOIN v_NEWTA002 ON TH014=TD001 AND TH015=TD002 AND TH016=TD003 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%' AND TA011='Y' </sql>