Agile PLM: 9.3.0.2中ACS错误：Cannot move to Complete

问题描述

在用户的ACS模块中，由于系统升级9.3.0.2导致ACS出现故障。ATO的历史记录错误显示为“Destination failed during an earlier extraction”，而系统log中却显示"Cannot move to Complete"错误。

分析

查看系统log，找出对应的错误如下。

<2011-01-18 02:30:30,063>  Caught the following exception
com.agile.util.exception.CMAppException: Cannot move to Complete. 
The current status is Complete. Please refresh your object.
at com.agile.pc.cmserver.base.BaseServiceRoute.doAuditStatus (BaseServiceRoute.java:1583)
at com.agile.pc.cmserver.base.BaseServiceRoute.validateDoChangeStatus (BaseServiceRoute.java:2076)
at com.agile.pc.cmserver.base.BaseServiceRoute.doChangeStatus (BaseServiceRoute.java:2027)
at com.agile.pc.cmserver.base.BaseServiceRoute.handleChangeStatus (BaseServiceRoute.java:7492)
at com.agile.acs.PCExtractTask.tmpExtractionProcess(PCExtractTask.java:793)
at com.agile.acs.PCExtractTask.run(PCExtractTask.java:442)
at java.util.TimerThread.mainLoop(Timer.java:512)
at java.util.TimerThread.run(Timer.java:462)

ACS的工作原理是一旦Workflow或者Scheduler触发一个ACS任务，就会有一个新的ATO产生并置为Release状态。Agile再查询数据库中所有Release的ATO，循环处理。等到该ATO数据被导出到目标地址后，状态自动变更为Complete。从上述日志初步判断，Agile当前处理的这个ATO已经处于Complete状态，理论上这个情况不该出现。 
继续排错，先将相关表备份，再清空ACS所有的数据。

delete from outbound;
delete from outbound_wheresent;
delete from outbound_objects;
delete from outbound_track;
delete from outbound_lock;
commit;

重新测试一个新的ATO，发现能正常工作，目标地址能接收到数据。测试第二个ATO，问题又出现，依然是Cannot move to Complete。打开SQL Logger，检查相关SQL，找出如下SQL

select OUTBOUND_P2P3.ID,OUTBOUND_P2P3.CLASS,OUTBOUND_P2P3.SUBCLASS,
  OUTBOUND_P2P3.FLAGS,NULL,NULL,NULL,OUTBOUND_P2P3.OUTBOUND_NUMBER,
  OUTBOUND_P2P3.SUBCLASS,OUTBOUND_P2P3.OUTBOUND_NUMBER,
  OUTBOUND_P2P3.ORIGINATOR,OUTBOUND_P2P3.CREATE_USER,
  OUTBOUND_P2P3.CREATE_USER,NULL,NULL,OUTBOUND_P2P3.OUTBOUND_NUMBER 
  from OUTBOUND_P2P3 
  where (NVL(OUTBOUND_P2P3.DELETE_FLAG,0) = 0) ORDER BY 16

很明显，这个SQL会查找系统中所有的ATO，包括已经Complete的状态的。由于SQL是动态产生的，ACS的查找Release的ATO（也包括CTO）都是通过Query对象动态组建而成。
查找ACS相关的Query对象

--查找Query表：
select id, name, type, is_public from query where id=250;
id  name                type    is_public
--------------------------------------------
250	ACS PC EXTRACTION	12650	1

--查找Criteria表
select id, query_id, attr_id, relational_op, value from criteria where query_id=250
no record returned

--查找select_list
select * from select_list where query_id=250;
no record returned

错误很明显，ACS的Query对象的条件数据丢失，导致ACS去查找所有的ATO/CTO。

解决方法

delete select_list where query_id=250;
delete criteria where query_id=250;
delete query where id=250;
commit;
insert into select_list values (250, 0, 12672, 0, null, null, sysdate, sysdate);
insert into select_list values (250, 1, 12673, 0, null, null, sysdate, sysdate);
insert into select_list values (250, 2, 12671, 0, null, null, sysdate, sysdate);
insert into criteria values (5940, 0, 250, 12674, 23, -17, 0, 0, 0, '00000000000000000000000000000000', 0, 0, null, null, sysdate, sysdate);
insert into query values (250,'ACS PC EXTRACTION',12650,0,1,0,0,0,0,0,'00000000000000000000000000000000', null,null,null,null,-1,sysdate,sysdate);
commit;

重启Agile Server，抓出相关的SQL，可以发现SQL已经修正为如下正确的内容

select /*+ ALL_ROWS */ OUTBOUND_P2P3.ID,OUTBOUND_P2P3.CLASS,OUTBOUND_P2P3.SUBCLASS,
  OUTBOUND_P2P3.FLAGS,NULL,NULL,NULL,OUTBOUND_P2P3.OUTBOUND_NUMBER,
  OUTBOUND_P2P3.SUBCLASS,OUTBOUND_P2P3.OUTBOUND_NUMBER,OUTBOUND_P2P3.ORIGINATOR,
  OUTBOUND_P2P3.CREATE_USER,OUTBOUND_P2P3.CREATE_USER,NULL,NULL,
  OUTBOUND_P2P3.OUTBOUND_NUMBER  
  from OUTBOUND_P2P3 where (((OUTBOUND_P2P3.STATUSTYPE) IN ('3'))) 
  AND (NVL(OUTBOUND_P2P3.DELETE_FLAG,0) = 0) ORDER BY 16