9. 資料庫更新 SQL
9.1 檢查製令是否存在
SELECT COUNT(1) FROM MODETAIL WHERE CMOID = N'{wo_no}'
9.2 INSERT MODETAIL（新增製令主檔）
INSERT INTO MODETAIL (
CMOID, MO004, MO005, ITEMID, MO021, MO022, UNIT,
PLANPROCESSST, DUEDATETIME, QTY, STATUS, DOID,
SEQUENCE, FACTORYID, WAREHOUSEID, DESCRIPTION,
MO008, MO012, MO016, MO032, PLANRELEASEQTY,
FLAG, LASTMAINTAINUSER, LASTMAINTAINDATETIME
) VALUES (
:CMOID,
:MO004,
:MO005,
:ITEMID,
:MO021,
:MO022,
:UNIT,
:PLANPROCESSST,
:DUEDATETIME,
:QTY,
1,
:DOID,
:SEQUENCE,
:FACTORYID,
:WAREHOUSEID,
:DESCRIPTION,
1,
:MO012,
0,
:MO032,
:QTY,
1,
:userid,
N'{當前時間}'
)
9.3 INSERT LOT（新增批次記錄）
INSERT INTO LOT (
ID, TYPE, MOID, ITEMID, LOTSIZE, STATUS, UNIT,
QTYPER, LOCATION, ERP_OPSEQ, ERP_OPID, ERP_WSID,
LOT004, PKQTY, PKQTYPER, LOT007, LOT011,
LASTMAINTAINUSER, LASTMAINTAINDATETIME
) VALUES (
:CMOID,
1,
:CMOID,
:ITEMID,
:QTY,
0,
:UNIT,
1,
'release',
'', '', '',
1, 0, 0,
'0', 'N',
:userid,
N'{當前時間}'
)
9.4 初始化製程 SFT_OP_REALRUN（從 SFT_BOMMF 讀取）
SELECT ME007 FROM SFT_BOMME, MODETAIL, ITEM
WHERE ITEM.ID = MODETAIL.ITEMID
AND CMOID = N'{wo_no}'
AND RTRIM(ME001) = ITEM.STDROUTEITEMID
AND RTRIM(ME002) = ITEM.STDROUTEID
DELETE SFT_OP_REALRUN WHERE ID = N'{wo_no}'
INSERT INTO SFT_OP_REALRUN (
ID, SEQUENCE, OPID, OPDESCRIPTION,
ARRIVEQTY, OUTQTY, REWORKQTY, DEFECTQTY,
ERP_OPSEQ, PERLOTWORKTIME, STANDARDEQWORKTIME, STANDARDMANWORKTIME,
ERP_OPID, ERP_WSID, ERP_FIXEDLEADTIME, ERP_TRANSQTY, ...
)
SELECT
CMOID,
0,
RTRIM(MF004)+'---'+RTRIM(MF006),
MF008,
0, 0, 0, 0,
RTRIM(MF003),
MF019,
...
FROM SFT_BOMMF, MODETAIL, ITEM
WHERE ITEM.ID = MODETAIL.ITEMID
AND CMOID = N'{wo_no}'
AND RTRIM(MF001) = ITEM.STDROUTEITEMID
AND RTRIM(MF002) = ITEM.STDROUTEID
9.5 初始化前後道關聯 SFT_OP_BACK_RUN（若有網狀途程）
DELETE SFT_OP_BACK_RUN WHERE SOB001 = N'{wo_no}'
INSERT INTO SFT_OP_BACK_RUN (
SOB001, SOB002, SOB003, SOB004, SOB005, SOB007, SOB008
)
SELECT
N'{wo_no}',
MFL003,
MFL004,
MFL005,
MFL006,
MFL008,
MFL009
FROM SFT_BOMMF_LINE
JOIN SFT_BOMME ON ME001 = MFL001 AND ME002 = MFL002
WHERE MFL001 = :MF001 AND MFL002 = :MF002 AND MES004 = 'Y'
UPDATE SFT_OP_REALRUN SET
OR002 = CASE ... END,
OR003 = CASE ... END,
OR004 = CASE ... END
WHERE ID = N'{wo_no}' AND SEQUENCE = 0
9.6 更新製令途程標記
UPDATE MODETAIL SET
MO040 = CASE WHEN ME007 = 'Y' THEN 'Y' ELSE 'N' END,
STDROUTEITEMID = :STDROUTEITEMID,
STDROUTEID = :STDROUTEID
WHERE CMOID = N'{wo_no}'
9.7 計算製程時間
dataMantain.initProcessTime(companyID, CMOID)
SFT_OP_REALRUN.sqlUpdate_RecalculateAll("insertMODETAIL")
新增工單影響的資料表清單
| 資料表 | 操作 | 說明 |
|---|
| MODETAIL | INSERT | 製令主檔 |
| LOT | INSERT | 批次記錄 |
| SFT_OP_REALRUN | DELETE + INSERT | 製程實績（從 SFT_BOMMF 複製） |
| SFT_OP_BACK_RUN | DELETE + INSERT | 前後道製程關聯（網狀途程） |
程式位置
SQL 執行：ESBapi.java:1007-1046
製程初始化：dataMantain.initOpRealRun_SftBommf()
前後道關聯：UpdateData.InsertOpBackRun()
9.8 更新 material_requisition（工單用料維護單身）
相關聯的 API：upMaterial
ERP 開立工單後，可能會同時呼叫 upMaterial API 來同步工單用料資料至 material_requisition 表。
SELECT COUNT(1) FROM order_material
WHERE material_requisition = N'{material_requisition}'
AND material_number = N'{material_number}'
AND serial_number = N'{serial_number}'
INSERT INTO order_material (
material_requisition, material_number, serial_number,
material_id, material_quantity, craftsmanship,
batch_number, category, order_number, type,
batch_description, om01, om02, om03, om04
) VALUES (
:material_requisition, :material_number, :serial_number,
:material_id, :material_quantity, :craftsmanship,
:batch_number, :category, :order_number, :type,
:batch_description, N'{當前時間}', :om02, :om03, :om04
)
SELECT COUNT(1) FROM material_requisition
WHERE modetail_cmoid = N'{工單編號}'
AND material_id = N'{物料編號}'
AND opid = N'{製程代號}'
AND erp_wsid = N'{工作站代號}'
AND material_batch = N'{物料批號}'
AND (remark IS NULL OR remark = '')
INSERT INTO material_requisition (
serial, modetail_cmoid, material_id,
opid, erp_wsid, material_batch,
withdrawn_quantity, mr01
) VALUES (
:serial,
:modetail_cmoid,
:material_id,
:opid,
:erp_wsid,
:material_batch,
:withdrawn_quantity,
N'{當前時間}'
)
UPDATE material_requisition SET
withdrawn_quantity = withdrawn_quantity + :新增領料量
WHERE modetail_cmoid = N'{工單編號}'
AND material_id = N'{物料編號}'
AND opid = N'{製程代號}'
AND erp_wsid = N'{工作站代號}'
AND material_batch = N'{物料批號}'
AND (remark IS NULL OR remark = '')
material_requisition 表結構
| 欄位 | 類型 | 說明 |
|---|
| serial | INT | 流水號 |
| modetail_cmoid | NVARCHAR(40) | 工單編號 |
| material_id | NVARCHAR(40) | 物料編號 |
| opid | NVARCHAR(20) | 製程代號 |
| erp_wsid | NVARCHAR(20) | 工作站代號 |
| material_batch | NVARCHAR(40) | 物料批號 |
| demand_quantity | FLOAT | 需求數量 |
| withdrawn_quantity | FLOAT | 實際領料量 |
| remark | NVARCHAR(500) | 備註 |
| mr01 | DATETIME | 建立時間 |
新增工單影響的資料表清單（完整）
| 資料表 | 操作 | 說明 | API |
|---|
| MODETAIL | INSERT | 製令主檔 | wo.create |
| LOT | INSERT | 批次記錄 | wo.create |
| SFT_OP_REALRUN | DELETE + INSERT | 製程實績（從 SFT_BOMMF 複製） | wo.create |
| SFT_OP_BACK_RUN | DELETE + INSERT | 前後道製程關聯（網狀途程） | wo.create |
| order_material | INSERT/UPDATE | 用料訂單資料 | upMaterial |
| material_requisition | INSERT/UPDATE | 工單用料維護單身 | upMaterial |
程式位置
wo.create SQL 執行：ESBapi.java:1007-1046
製程初始化：dataMantain.initOpRealRun_SftBommf()
前後道關聯：UpdateData.InsertOpBackRun()
用料資料同步：ESBapi.java:1252-1394 (upMaterial)