「ERP上線前跟催進度(業務)」:修訂間差異

出自TSK WIKI
跳至導覽 跳至搜尋
Root​(留言 | 貢獻)
無編輯摘要
Root​(留言 | 貢獻)
 
(未顯示同一使用者於中間所作的 4 次修訂)
第1行: 第1行:
==2015/1/27==
==2015/1/27==
*建立品號資料
*建立品號資料(3筆)
**https://dl.dropboxusercontent.com/u/6377547/ShareX/2015-01/2015-01-27_21-40-27.png
**https://dl.dropboxusercontent.com/u/6377547/ShareX/2015-01/2015-01-27_21-40-27.png
*建立BOM表
*建立BOM表(1筆)
**https://dl.dropboxusercontent.com/u/6377547/ShareX/2015-01/2015-01-27_21-53-09.png
**https://dl.dropboxusercontent.com/u/6377547/ShareX/2015-01/2015-01-27_21-53-09.png
**建立訂單
*建立訂單
** 0筆
** 0筆
*建立製令
*建立製令
第10行: 第10行:
*建立銷貨單
*建立銷貨單
** 0筆
** 0筆
==規畫SQL==
==規畫SQL==
*搜尋今日建立品號
*搜尋今日建立品號
第15行: 第16行:
*搜尋今日建立BOM品號
*搜尋今日建立BOM品號
**SELECT MC001,MB002,MB003,MF002,BOMMC.CREATE_TIME FROM BOMMC INNER JOIN INVMB ON MC001=MB001 INNER JOIN ADMMF ON INVMB.CREATOR=MF001 WHERE BOMMC.CREATE_DATE=(Select CONVERT(varchar(100), GETDATE(), 112)) AND BOMMC.USR_GROUP='021' ORDER BY BOMMC.CREATOR DESC
**SELECT MC001,MB002,MB003,MF002,BOMMC.CREATE_TIME FROM BOMMC INNER JOIN INVMB ON MC001=MB001 INNER JOIN ADMMF ON INVMB.CREATOR=MF001 WHERE BOMMC.CREATE_DATE=(Select CONVERT(varchar(100), GETDATE(), 112)) AND BOMMC.USR_GROUP='021' ORDER BY BOMMC.CREATOR DESC
*搜尋今日建立報價單
**SELECT TA001,TA002,MF002,COPTA.CREATE_TIME FROM COPTA INNER JOIN ADMMF ON COPTA.CREATOR=MF001 WHERE COPTA.CREATE_DATE=(Select CONVERT(varchar(100), GETDATE(), 112)) AND COPTA.USR_GROUP='021' ORDER BY COPTA.CREATOR
*搜尋今日建立訂單
*搜尋今日建立訂單
**SELECT TC001,TC002,MF002,COPTC.CREATE_TIME FROM COPTC INNER JOIN ADMMF ON COPTC.CREATOR=MF001 WHERE COPTC.CREATE_DATE=(Select CONVERT(varchar(100), GETDATE(), 112)) AND COPTC.USR_GROUP='021' ORDER BY COPTC.CREATOR
**SELECT TC001,TC002,MF002,COPTC.CREATE_TIME FROM COPTC INNER JOIN ADMMF ON COPTC.CREATOR=MF001 WHERE COPTC.CREATE_DATE=(Select CONVERT(varchar(100), GETDATE(), 112)) AND COPTC.USR_GROUP='021' ORDER BY COPTC.CREATOR
第21行: 第24行:
*搜尋今日建立銷貨單
*搜尋今日建立銷貨單
SELECT TG001,TG002,MF002,COPTG.CREATE_TIME FROM COPTG INNER JOIN ADMMF ON COPTG.CREATOR=MF001 WHERE COPTG.CREATE_DATE=(Select CONVERT(varchar(100), GETDATE(), 112)) AND COPTG.USR_GROUP='021' ORDER BY COPTG.CREATOR
SELECT TG001,TG002,MF002,COPTG.CREATE_TIME FROM COPTG INNER JOIN ADMMF ON COPTG.CREATOR=MF001 WHERE COPTG.CREATE_DATE=(Select CONVERT(varchar(100), GETDATE(), 112)) AND COPTG.USR_GROUP='021' ORDER BY COPTG.CREATOR
*查詢次數SELECT MF002,NUM
FROM( SELECT COUNT(*)AS NUM,CREATOR FROM COPTC  WHERE COPTC.CREATE_DATE='20150129' AND COPTC.USR_GROUP='021' GROUP BY CREATOR) COPTC INNER JOIN ADMMF ON COPTC.CREATOR=MF001
==顧問每日要求==
*查詢BOM、訂單、工單、銷貨單、結帳單
**SELECT COUNT(*),BOMMC

於 2015年2月3日 (二) 16:20 的最新修訂

2015/1/27

  • 建立品號資料(3筆)
    • 2015-01-27_21-40-27.png
  • 建立BOM表(1筆)
    • 2015-01-27_21-53-09.png
  • 建立訂單
    • 0筆
  • 建立製令
    • 0筆
  • 建立銷貨單
    • 0筆

規畫SQL

  • 搜尋今日建立品號
    • SELECT MB001,MB002,MF002,INVMB.CREATE_TIME FROM INVMB INNER JOIN ADMMF ON INVMB.CREATOR=MF001 WHERE INVMB.CREATE_DATE=(Select CONVERT(varchar(100), GETDATE(), 112)) AND INVMB.USR_GROUP='021' ORDER BY INVMB.CREATOR
  • 搜尋今日建立BOM品號
    • SELECT MC001,MB002,MB003,MF002,BOMMC.CREATE_TIME FROM BOMMC INNER JOIN INVMB ON MC001=MB001 INNER JOIN ADMMF ON INVMB.CREATOR=MF001 WHERE BOMMC.CREATE_DATE=(Select CONVERT(varchar(100), GETDATE(), 112)) AND BOMMC.USR_GROUP='021' ORDER BY BOMMC.CREATOR DESC
  • 搜尋今日建立報價單
    • SELECT TA001,TA002,MF002,COPTA.CREATE_TIME FROM COPTA INNER JOIN ADMMF ON COPTA.CREATOR=MF001 WHERE COPTA.CREATE_DATE=(Select CONVERT(varchar(100), GETDATE(), 112)) AND COPTA.USR_GROUP='021' ORDER BY COPTA.CREATOR
  • 搜尋今日建立訂單
    • SELECT TC001,TC002,MF002,COPTC.CREATE_TIME FROM COPTC INNER JOIN ADMMF ON COPTC.CREATOR=MF001 WHERE COPTC.CREATE_DATE=(Select CONVERT(varchar(100), GETDATE(), 112)) AND COPTC.USR_GROUP='021' ORDER BY COPTC.CREATOR
  • 搜尋今日建立製令
    • SELECT TA006,MB002,MB003,MF002,MOCTA.CREATE_TIME FROM MOCTA INNER JOIN INVMB ON TA006=MB001 INNER JOIN ADMMF ON MOCTA.CREATOR=MF001 WHERE MOCTA.CREATE_DATE=(Select CONVERT(varchar(100), GETDATE(), 112)) AND MOCTA.USR_GROUP='021' ORDER BY MOCTA.CREATOR
  • 搜尋今日建立銷貨單

SELECT TG001,TG002,MF002,COPTG.CREATE_TIME FROM COPTG INNER JOIN ADMMF ON COPTG.CREATOR=MF001 WHERE COPTG.CREATE_DATE=(Select CONVERT(varchar(100), GETDATE(), 112)) AND COPTG.USR_GROUP='021' ORDER BY COPTG.CREATOR

  • 查詢次數SELECT MF002,NUM

FROM( SELECT COUNT(*)AS NUM,CREATOR FROM COPTC WHERE COPTC.CREATE_DATE='20150129' AND COPTC.USR_GROUP='021' GROUP BY CREATOR) COPTC INNER JOIN ADMMF ON COPTC.CREATOR=MF001

顧問每日要求

  • 查詢BOM、訂單、工單、銷貨單、結帳單
    • SELECT COUNT(*),BOMMC