待定资源处理接口中存在错误记录，无法关闭会计期

rongren

1、查找以前的问题报告，了解wip_cost_txn_interface处理完后会把记录分配到wip_move_txn_allocations、wip_txn_allocations两个表中；

2、迁移环境至测试环境：

3、
备份接口表：备份 wip_cost_txn_interface 表
4、确认“成本管理器”没有业务在运行

5、执行【a】查看wip_cost_txn_interface接口表中数据是否分配到wip_move_txn_allocations，如果存在记录就要求执行【b】，不存在就直接到【6】：

A、Select move_transaction_id
from wip_cost_txn_interface wmta
where
not
exists(
select
1
from wip_move_txn_allocations tt

where tt.transaction_id=wmta.move_transaction_id
)

B、insert into wip_move_txn_allocations
( transaction_id,
repetitive_schedule_id,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
transaction_quantity,
primary_quantity
)
( select
wcti.move_transaction_id,
wcti.repetitive_schedule_id,
wcti.organization_id,
wcti.last_update_date,
wcti.last_updated_by,
wcti.creation_date,
wcti.created_by,
wcti.last_update_login,
wcti.request_id,
wcti.program_application_id,
wcti.program_id,
wcti.program_update_date,
wmt.transaction_quantity,
wmt.primary_quantity
from wip_cost_txn_interface wcti,
wip_move_transactions wmt
where wcti.move_transaction_id=wmt.transaction_id
and wcti.organization_id = wmt.organization_id
and wcti.transaction_id =(a查询结果)
);

6、执行【c】查看wip_cost_txn_interface接口表中数据是否分配到wip_txn_allocations，如果存在记录就要求执行【d】,如果不存在就直接到【7】

C、select * from wip_cost_txn_interface wcti
where process_status = 3
and not exists (select 1
from wip_txn_allocations wta
where wta.transaction_id = wcti.transaction_id
)

D、INSERT INTO WIP_TXN_ALLOCATIONS
(
TRANSACTION_ID,
REPETITIVE_SCHEDULE_ID,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY
)
(
SELECT WCTI.TRANSACTION_ID,
WMTA.REPETITIVE_SCHEDULE_ID,
WCTI.ORGANIZATION_ID,
WMTA.last_update_date,
WCTI.LAST_UPDATED_BY,
WMTA.creation_date,
WCTI.CREATED_BY,
WCTI.LAST_UPDATE_LOGIN,
WCTI.REQUEST_ID,
WCTI.PROGRAM_APPLICATION_ID,
WCTI.PROGRAM_ID,
WMTA.program_update_date,
WCTI.TRANSACTION_QUANTITY *
DECODE(SIGN(WMT.TO_OPERATION_SEQ_NUM - WMT.FM_OPERATION_SEQ_NUM), 0,
DECODE(SIGN(WMT.FM_INTRAOPERATION_STEP_TYPE-2),1,
1,DECODE(SIGN(WMT.TO_INTRAOPERATION_STEP_TYPE-2), 1, 1, -1)), 1, 1, -1, -1),
WCTI.PRIMARY_QUANTITY *
DECODE(SIGN(WMT.TO_OPERATION_SEQ_NUM - WMT.FM_OPERATION_SEQ_NUM), 0,
DECODE(SIGN(WMT.FM_INTRAOPERATION_STEP_TYPE-2), 1,
-1,DECODE(SIGN(WMT.TO_INTRAOPERATION_STEP_TYPE-2), 1, 1, -1)), 1, 1, -1, -1)
FROM WIP_MOVE_TXN_ALLOCATIONS WMTA,
      WIP_MOVE_TRANSACTIONS WMT,
      WIP_COST_TXN_INTERFACE WCTI
WHERE WCTI.TRANSACTION_ID = 【c查询的TRANSACTION_ID】
   AND WCTI.MOVE_TRANSACTION_ID = WMT.TRANSACTION_ID
   AND WCTI.MOVE_TRANSACTION_ID = WMTA.TRANSACTION_ID
   AND WCTI.ORGANIZATION_ID = WMTA.ORGANIZATION_ID
)

7、通过以上方法补全数据：

UPDATE WIP_COST_TXN_INTERFACE
SET PROCESS_STATUS = 1,
PROCESS_PHASE = 2,
GROUP_ID = NULL,
REPETITIVE_SCHEDULE_ID = NULL
where process_status = 3

commit;

处理结果：

等下一次成本管理器执行完后，查询WIP_COST_TXN_INTERFACE，确认接口中没有数据，业务正常，问题结束，迁移到测试环境。

jordeny

这类问题在系统上线初期是会遇到的，不错的解决方法。