UPDATE t3 SET
fOrderedQTY = ISNULL(t3.fOrderedQTY, 0) - t10.fCancelQTY,--已订货(已下达)数量
fStatus = CASE WHEN (ISNULL(t30.fCount, 0) > 0 AND ISNULL(t20.fCount, 0) < 1)--存在已下达的审批记录且没有未下达的审批记录为完成
THEN 'F'
WHEN (ISNULL(t30.fCount, 0) > 0 AND ISNULL(t20.fCount, 0) > 0)--存在已下达的审批记录且存在未下达的审批记录为部分完成
THEN 'G'
WHEN (ISNULL(t30.fCount, 0) < 1)--不存在已下达的审批单记录
THEN 'Y'
END
FROM t_PurOrderQueue_Delete t1
INNER JOIN t_PurOrder t5 ON (t1.fEntityNO = t5.fEntityNO AND t1.fPONO = t5.fPONO)
INNER JOIN t_PurReqItemToVendor t2 ON (t1.fEntityNO = t2.fEntityNO AND t1.fPONO = t2.fPONO AND t1.fLineID = t2.fPOLineID AND t1.fSeqID = t2.fPOSeqID)
INNER JOIN t_PurRequireEntry t3 ON (t2.fEntityNO = t3.fEntityNO AND t2.fPRNO = t3.fPRNO AND t2.fLineID = t3.fLineID)
INNER JOIN (SELECT t2.fEntityNO, t2.fPRNO, t2.fLineID,
SUM(ISNULL(t2.fCheckQTY, 0) * ISNULL(t2.fRation, 1)) AS fCancelQTY--累计删除数量(以基准单位计量)
FROM #t_PurOrderQueue_Delete t1
INNER JOIN t_PurReqItemToVendor t2 ON (t1.fEntityNO = t2.fEntityNO AND t1.fPONO = t2.fPONO AND t1.fLineID = t2.fPOLineID AND t1.fSeqID = t2.fPOSeqID)
GROUP BY t2.fEntityNO, t2.fPRNO, t2.fLineID
) t10 ON (t2.fEntityNO = t10.fEntityNO AND t2.fPRNO = t10.fPRNO AND t2.fLineID = t10.fLineID)
LEFT JOIN (SELECT t1.fEntityNO, t1.fPRNO, t1.fLineID, t2.fCount
FROM t_PurReqItemToVendor t1
LEFT JOIN (SELECT fEntityNO, fPRNO, fLineID, COUNT(*) AS fCount
FROM t_PurReqItemToVendor
WHERE fStatus IN ('I', 'D', 'A')--对应此请购单明细记录的未下达的审批单记录数
GROUP BY fEntityNO, fPRNO, fLineID
) t2 ON (t1.fEntityNO = t2.fEntityNO AND t1.fPRNO = t2.fPRNO AND t1.fLineID = t2.fLineID)
) t20 ON (t2.fEntityNO = t20.fEntityNO AND t2.fPRNO = t20.fPRNO AND t2.fLineID = t20.fLineID)
LEFT JOIN (SELECT t1.fEntityNO, t1.fPRNO, t1.fLineID, t2.fCount
FROM t_PurReqItemToVendor t1
LEFT JOIN (SELECT fEntityNO, fPRNO, fLineID, COUNT(*) AS fCount
FROM t_PurReqItemToVendor
WHERE fStatus IN ('E')--对应此请购单明细记录的已下达的审批单记录数
GROUP BY fEntityNO, fPRNO, fLineID
) t2 ON (t1.fEntityNO = t2.fEntityNO AND t1.fPRNO = t2.fPRNO AND t1.fLineID = t2.fLineID)
) t30 ON (t2.fEntityNO = t30.fEntityNO AND t2.fPRNO = t30.fPRNO AND t2.fLineID = t30.fLineID)
WHERE ISNULL(t5.fSourceType, '') = '2'--fSourceType:订单来源(1-手工录入,2-请购单,3-合同,4-询价单)
fOrderedQTY = ISNULL(t3.fOrderedQTY, 0) - t10.fCancelQTY,--已订货(已下达)数量
fStatus = CASE WHEN (ISNULL(t30.fCount, 0) > 0 AND ISNULL(t20.fCount, 0) < 1)--存在已下达的审批记录且没有未下达的审批记录为完成
THEN 'F'
WHEN (ISNULL(t30.fCount, 0) > 0 AND ISNULL(t20.fCount, 0) > 0)--存在已下达的审批记录且存在未下达的审批记录为部分完成
THEN 'G'
WHEN (ISNULL(t30.fCount, 0) < 1)--不存在已下达的审批单记录
THEN 'Y'
END
FROM t_PurOrderQueue_Delete t1
INNER JOIN t_PurOrder t5 ON (t1.fEntityNO = t5.fEntityNO AND t1.fPONO = t5.fPONO)
INNER JOIN t_PurReqItemToVendor t2 ON (t1.fEntityNO = t2.fEntityNO AND t1.fPONO = t2.fPONO AND t1.fLineID = t2.fPOLineID AND t1.fSeqID = t2.fPOSeqID)
INNER JOIN t_PurRequireEntry t3 ON (t2.fEntityNO = t3.fEntityNO AND t2.fPRNO = t3.fPRNO AND t2.fLineID = t3.fLineID)
INNER JOIN (SELECT t2.fEntityNO, t2.fPRNO, t2.fLineID,
SUM(ISNULL(t2.fCheckQTY, 0) * ISNULL(t2.fRation, 1)) AS fCancelQTY--累计删除数量(以基准单位计量)
FROM #t_PurOrderQueue_Delete t1
INNER JOIN t_PurReqItemToVendor t2 ON (t1.fEntityNO = t2.fEntityNO AND t1.fPONO = t2.fPONO AND t1.fLineID = t2.fPOLineID AND t1.fSeqID = t2.fPOSeqID)
GROUP BY t2.fEntityNO, t2.fPRNO, t2.fLineID
) t10 ON (t2.fEntityNO = t10.fEntityNO AND t2.fPRNO = t10.fPRNO AND t2.fLineID = t10.fLineID)
LEFT JOIN (SELECT t1.fEntityNO, t1.fPRNO, t1.fLineID, t2.fCount
FROM t_PurReqItemToVendor t1
LEFT JOIN (SELECT fEntityNO, fPRNO, fLineID, COUNT(*) AS fCount
FROM t_PurReqItemToVendor
WHERE fStatus IN ('I', 'D', 'A')--对应此请购单明细记录的未下达的审批单记录数
GROUP BY fEntityNO, fPRNO, fLineID
) t2 ON (t1.fEntityNO = t2.fEntityNO AND t1.fPRNO = t2.fPRNO AND t1.fLineID = t2.fLineID)
) t20 ON (t2.fEntityNO = t20.fEntityNO AND t2.fPRNO = t20.fPRNO AND t2.fLineID = t20.fLineID)
LEFT JOIN (SELECT t1.fEntityNO, t1.fPRNO, t1.fLineID, t2.fCount
FROM t_PurReqItemToVendor t1
LEFT JOIN (SELECT fEntityNO, fPRNO, fLineID, COUNT(*) AS fCount
FROM t_PurReqItemToVendor
WHERE fStatus IN ('E')--对应此请购单明细记录的已下达的审批单记录数
GROUP BY fEntityNO, fPRNO, fLineID
) t2 ON (t1.fEntityNO = t2.fEntityNO AND t1.fPRNO = t2.fPRNO AND t1.fLineID = t2.fLineID)
) t30 ON (t2.fEntityNO = t30.fEntityNO AND t2.fPRNO = t30.fPRNO AND t2.fLineID = t30.fLineID)
WHERE ISNULL(t5.fSourceType, '') = '2'--fSourceType:订单来源(1-手工录入,2-请购单,3-合同,4-询价单)
兄弟们,帮忙改啊!
UPDATE t3 SET
fOrderedQTY = nvl(t3.fOrderedQTY, 0) - t10.fCancelQTY,--已订货(已下达)数量
fStatus = decode(sign(nvl(t30.fCount, 0)-1),-1,'Y',decode(sign(nvl(t20.fCount, 0)-1),-1,'F','G'))
FROM t_PurOrderQueue_Delete t1
INNER JOIN t_PurOrder t5 ON (t1.fEntityNO = t5.fEntityNO AND t1.fPONO = t5.fPONO)
INNER JOIN t_PurReqItemToVendor t2 ON (t1.fEntityNO = t2.fEntityNO AND t1.fPONO = t2.fPONO AND t1.fLineID = t2.fPOLineID AND t1.fSeqID = t2.fPOSeqID)
INNER JOIN t_PurRequireEntry t3 ON (t2.fEntityNO = t3.fEntityNO AND t2.fPRNO = t3.fPRNO AND t2.fLineID = t3.fLineID)
INNER JOIN (SELECT t2.fEntityNO, t2.fPRNO, t2.fLineID,
SUM(nvl(t2.fCheckQTY, 0) * nvl(t2.fRation, 1)) AS fCancelQTY--累计删除数量(以基准单位计量)
FROM #t_PurOrderQueue_Delete t1
INNER JOIN t_PurReqItemToVendor t2 ON (t1.fEntityNO = t2.fEntityNO AND t1.fPONO = t2.fPONO AND t1.fLineID = t2.fPOLineID AND t1.fSeqID = t2.fPOSeqID)
GROUP BY t2.fEntityNO, t2.fPRNO, t2.fLineID
) t10 ON (t2.fEntityNO = t10.fEntityNO AND t2.fPRNO = t10.fPRNO AND t2.fLineID = t10.fLineID)
LEFT JOIN (SELECT t1.fEntityNO, t1.fPRNO, t1.fLineID, t2.fCount
FROM t_PurReqItemToVendor t1
LEFT JOIN (SELECT fEntityNO, fPRNO, fLineID, COUNT(*) AS fCount
FROM t_PurReqItemToVendor
WHERE fStatus IN ('I', 'D', 'A')--对应此请购单明细记录的未下达的审批单记录数
GROUP BY fEntityNO, fPRNO, fLineID
) t2 ON (t1.fEntityNO = t2.fEntityNO AND t1.fPRNO = t2.fPRNO AND t1.fLineID = t2.fLineID)
) t20 ON (t2.fEntityNO = t20.fEntityNO AND t2.fPRNO = t20.fPRNO AND t2.fLineID = t20.fLineID)
LEFT JOIN (SELECT t1.fEntityNO, t1.fPRNO, t1.fLineID, t2.fCount
FROM t_PurReqItemToVendor t1
LEFT JOIN (SELECT fEntityNO, fPRNO, fLineID, COUNT(*) AS fCount
FROM t_PurReqItemToVendor
WHERE fStatus IN ('E')--对应此请购单明细记录的已下达的审批单记录数
GROUP BY fEntityNO, fPRNO, fLineID
) t2 ON (t1.fEntityNO = t2.fEntityNO AND t1.fPRNO = t2.fPRNO AND t1.fLineID = t2.fLineID)
) t30 ON (t2.fEntityNO = t30.fEntityNO AND t2.fPRNO = t30.fPRNO AND t2.fLineID = t30.fLineID)
WHERE nvl(t5.fSourceType, '') = '2'; --fSourceType:订单来源(1-手工录入,2-请购单,3-合同,4-询价单)
(t_PurRequireEntry.fOrderedQTY,t_PurRequireEntry.Fstatus )=(
SELECT nvl(t3.fOrderedQTY, 0) - t10.fCancelQTY,--已订货(已下达)数量
CASE WHEN (nvl(t30.fCount, 0) > 0 AND nvl(t20.fCount, 0) < 1)--存在已下达的审批记录且没有未下达的审批记录为完成
THEN 'F'
WHEN (ISNULL(t30.fCount, 0) > 0 AND ISNULL(t20.fCount, 0) > 0)--存在已下达的审批记录且存在未下达的审批记录为部分完成
THEN 'G'
WHEN (ISNULL(t30.fCount, 0) < 1)--不存在已下达的审批单记录
THEN 'Y'
END
FROM t_PurReqItemToVendor t2,t_PurRequireEntry t3,t_PurOrder t5--#t_PurOrderQueue_Delete t1
INNER JOIN t_PurOrder t5 ON (:old.fEntityNO = t5.fEntityNO AND :old.fPONO = t5.fPONO)
INNER JOIN t_PurReqItemToVendor t2 ON (:old.fEntityNO = t2.fEntityNO AND :old.fPONO = t2.fPONO
AND :old.fLineID = t2.fPOLineID AND t1.fSeqID = t2.fPOSeqID)
INNER JOIN t_PurRequireEntry t3 ON (t2.fEntityNO = t3.fEntityNO AND t2.fPRNO = t3.fPRNO AND t2.fLineID = t3.fLineID)
INNER JOIN (SELECT t2.fEntityNO, t2.fPRNO, t2.fLineID,
SUM(nvl(t2.fCheckQTY, 0) * nvl(t2.fRation, 1)) AS fCancelQTY--累计删除数量(以基准单位计量)
--FROM #t_PurOrderQueue_Delete t1
from t_PurReqItemToVendor t2
where (:old.fEntityNO = t2.fEntityNO AND
:old.fPONO = t2.fPONO AND t1.fLineID = t2.fPOLineID AND t1.fSeqID = t2.fPOSeqID)
GROUP BY t2.fEntityNO, t2.fPRNO, t2.fLineID
) t10 ON (t2.fEntityNO = t10.fEntityNO AND t2.fPRNO = t10.fPRNO AND t2.fLineID = t10.fLineID)
LEFT JOIN (SELECT t1.fEntityNO, t1.fPRNO, t1.fLineID, t2.fCount
FROM t_PurReqItemToVendor t1
LEFT JOIN (SELECT fEntityNO, fPRNO, fLineID, COUNT(*) AS fCount
FROM t_PurReqItemToVendor
WHERE fStatus IN ('I', 'D', 'A')--对应此请购单明细记录的未下达的审批单记录数
GROUP BY fEntityNO, fPRNO, fLineID
) t2 ON (t1.fEntityNO = t2.fEntityNO AND t1.fPRNO = t2.fPRNO AND t1.fLineID = t2.fLineID)
) t20 ON (t2.fEntityNO = t20.fEntityNO AND t2.fPRNO = t20.fPRNO AND t2.fLineID = t20.fLineID)
LEFT JOIN (SELECT t1.fEntityNO, t1.fPRNO, t1.fLineID, t2.fCount
FROM t_PurReqItemToVendor t1
LEFT JOIN (SELECT fEntityNO, fPRNO, fLineID, COUNT(*) AS fCount
FROM t_PurReqItemToVendor
WHERE fStatus IN ('E')--对应此请购单明细记录的已下达的审批单记录数
GROUP BY fEntityNO, fPRNO, fLineID
) t2 ON (t1.fEntityNO = t2.fEntityNO AND t1.fPRNO = t2.fPRNO AND t1.fLineID = t2.fLineID)
) t30 ON (t2.fEntityNO = t30.fEntityNO AND t2.fPRNO = t30.fPRNO AND t2.fLineID = t30.fLineID)
WHERE ISNULL(t5.fSourceType, '') = '2')--fSourceType:订单来源(1-手工录入,2-请购单,3-合同,4-询价单)
where exists
(select 1
FROM t_PurReqItemToVendor t2,t_PurRequireEntry t3,t_PurOrder t5,--#t_PurOrderQueue_Delete t1
INNER JOIN t_PurOrder t5 ON (:old.fEntityNO = t5.fEntityNO AND :old.fPONO = t5.fPONO)
INNER JOIN t_PurReqItemToVendor t2 ON (:old.fEntityNO = t2.fEntityNO AND :old.fPONO = t2.fPONO
AND :old.fLineID = t2.fPOLineID AND t1.fSeqID = t2.fPOSeqID)
INNER JOIN t_PurRequireEntry t3 ON (t2.fEntityNO = t3.fEntityNO AND t2.fPRNO = t3.fPRNO AND t2.fLineID = t3.fLineID)
INNER JOIN (SELECT t2.fEntityNO, t2.fPRNO, t2.fLineID,
SUM(nvl(t2.fCheckQTY, 0) * nvl(t2.fRation, 1)) AS fCancelQTY--累计删除数量(以基准单位计量)
--FROM #t_PurOrderQueue_Delete t1
from t_PurReqItemToVendor t2
where (:old.fEntityNO = t2.fEntityNO AND
:old.fPONO = t2.fPONO AND t1.fLineID = t2.fPOLineID AND t1.fSeqID = t2.fPOSeqID)
GROUP BY t2.fEntityNO, t2.fPRNO, t2.fLineID
) t10 ON (t2.fEntityNO = t10.fEntityNO AND t2.fPRNO = t10.fPRNO AND t2.fLineID = t10.fLineID)
LEFT JOIN (SELECT t1.fEntityNO, t1.fPRNO, t1.fLineID, t2.fCount
FROM t_PurReqItemToVendor t1
LEFT JOIN (SELECT fEntityNO, fPRNO, fLineID, COUNT(*) AS fCount
FROM t_PurReqItemToVendor
WHERE fStatus IN ('I', 'D', 'A')--对应此请购单明细记录的未下达的审批单记录数
GROUP BY fEntityNO, fPRNO, fLineID
) t2 ON (t1.fEntityNO = t2.fEntityNO AND t1.fPRNO = t2.fPRNO AND t1.fLineID = t2.fLineID)
) t20 ON (t2.fEntityNO = t20.fEntityNO AND t2.fPRNO = t20.fPRNO AND t2.fLineID = t20.fLineID)
LEFT JOIN (SELECT t1.fEntityNO, t1.fPRNO, t1.fLineID, t2.fCount
FROM t_PurReqItemToVendor t1
LEFT JOIN (SELECT fEntityNO, fPRNO, fLineID, COUNT(*) AS fCount
FROM t_PurReqItemToVendor
WHERE fStatus IN ('E')--对应此请购单明细记录的已下达的审批单记录数
GROUP BY fEntityNO, fPRNO, fLineID
) t2 ON (t1.fEntityNO = t2.fEntityNO AND t1.fPRNO = t2.fPRNO AND t1.fLineID = t2.fLineID)
) t30 ON (t2.fEntityNO = t30.fEntityNO AND t2.fPRNO = t30.fPRNO AND t2.fLineID = t30.fLineID)
WHERE ISNULL(t5.fSourceType, '') = '2');--fSourceType:订单来源(1-手工录入,2-请购单,3-合同,4-询价单) 我改成上面的语句,但是plsql提示
Compilation errors for TRIGGER SA.TRI_PURORDERQUEUE_DELError: PL/SQL: ORA-00904: "T_PURREQUIREENTRY"."FSTATUS": 无效的标识符
Line: 127
Text: (t_PurRequireEntry.fOrderedQTY,t_PurRequireEntry.Fstatus )=(Error: PL/SQL: SQL Statement ignored
Line: 126
Text: UPDATE t_PurRequireEntry t3 SETError: PL/SQL: ORA-00904: "T2"."FWOTYPE": 无效的标识符
Line: 261
Text: and t2.fWOType = 'I'--ImportError: PL/SQL: SQL Statement ignored
Line: 253
Text: UPDATE t_WorkOrder t1 SETError: PL/SQL: ORA-00904: "T3"."FWOTYPE": 无效的标识符
Line: 290
Text: t3.fEntityNO = t4.fEntityNO AND t3.fPONO = t4.fPONO AND t5.fEntrustNO = t4.fVendorNO andError: PL/SQL: SQL Statement ignored
Line: 282
Text: update t_WORoute t1Error: PLS-00306: 调用 'RAISE_APPLICATION_ERROR' 时参数个数或类型错误
Line: 324
Text: raise_application_error(ls_Msg);Error: PL/SQL: Statement ignored
Line: 324
Text: raise_application_error(ls_Msg);给我的感觉可能问题出在
nvl(t3.fOrderedQTY, 0) - t10.fCancelQTY 这里。
就是说,在set语句中,无法使用
SELECT t2.fEntityNO, t2.fPRNO, t2.fLineID,
SUM(nvl(t2.fCheckQTY, 0) * nvl(t2.fRation, 1)) AS fCancelQTY--累计删除数量(以基准单位计量)
FROM #t_PurOrderQueue_Delete t1
INNER JOIN t_PurReqItemToVendor t2 ON (t1.fEntityNO = t2.fEntityNO AND t1.fPONO = t2.fPONO AND t1.fLineID = t2.fPOLineID AND t1.fSeqID = t2.fPOSeqID)
GROUP BY t2.fEntityNO, t2.fPRNO, t2.fLineID
) t10
这样的方式创建出来的表。感觉t10是临时表,但是又不像。
update t set (...)= (select ... from t1 where t.id = t1.id) where exists(...)
语法结构是没问题的。就是t10表的问题了。
FROM t_PurReqItemToVendor t2,t_PurRequireEntry t3,t_PurOrder t5
结贴了,感谢大家的参与。