商云方的技术博客 (沪ICP备14046774号-1)

物料事务处理接口日志一例

/*******************************************************************************
date : 2007-05-06
处理者: yunfang.shang
*******************************************************************************/
现象：库存事务处理接口中堆积了大量记录未被处理
现象：接口表中很多记录处理结果为Error;原因是组织层不允许负库存，而库存现有量不足，导致无法扣减库存成功
      大量出库记录由此被堵塞在接口表。
处理日志：
 select count(*) from MTL_TRANSACTIONS_INTERFACE
 select count(*) from Mtl_Material_Transactions_Temp
 
 –事务处理数量
 SELECT
   — *
   –transaction_quantity
   –distinct transaction_source_type_id
   FROM MTL_TRANSACTIONS_INTERFACE
  WHERE transaction_interface_id = 321706
    AND error_code IS NOT NULL
   
 SELECT *
   –transaction_quantity
   FROM Mtl_Material_Transactions_Temp where trx_source_line_id =22964
  
 –保留
 select * from mtl_demand_v a where a.demand_id =  22964
   
 –现有量
 SELECT sum(transaction_quantity)
   FROM mtl_onhand_quantities moq
  WHERE moq.INVENTORY_ITEM_ID = 3489
    AND moq.ORGANIZATION_ID =108
    AND moq.SUBINVENTORY_CODE =111215001
    –物料编码
    SELECT segment1
      FROM mtl_system_items_b
     WHERE organization_id = 108
       AND inventory_item_id = 3489
      
 — 查找错误记录的现有量
 SELECT a.transaction_interface_id,
       msi.segment1,
       a.organization_id,
       a.subinventory_code,
       a.transaction_quantity,
       XH_COMMON_UTL.get_onhand_qty(a.inventory_item_id,
                                    a.organization_id,
                                    a.subinventory_code) on_hand_qty
  FROM mtl_transactions_interface a,
       mtl_system_items_b msi
 WHERE a.inventory_item_id = msi.inventory_item_id
 and   a.organization_id = msi.organization_id
 and   a.error_explanation IS NOT NULL  

 
 –汇总接口中的需求量> 现有量的情况
 create or replace view xh_mtliface_negative_onhand_v as
 SELECT *
   FROM (SELECT bb.inventory_item_id,
                bb.segment1,
                bb.organization_id,
                bb.subinventory_code,
                bb.transaction_quantity,
                XH_COMMON_UTL.get_onhand_qty(bb.inventory_item_id,
                                             bb.organization_id,
                                             bb.subinventory_code) on_hand_qty
           FROM (SELECT a.inventory_item_id,
                        msi.segment1,
                        a.organization_id,
                        a.subinventory_code,
                        SUM(a.transaction_quantity) transaction_quantity
                   FROM mtl_transactions_interface a, mtl_system_items_b msi
                  WHERE a.inventory_item_id = msi.inventory_item_id
                    AND a.organization_id = msi.organization_id
                    AND a.error_explanation IS NOT NULL
                    and a.transaction_source_type_id in (2,8)
                  GROUP BY a.inventory_item_id,
                           msi.segment1,
                           a.organization_id,
                           a.subinventory_code) bb)
  WHERE abs(transaction_quantity) > on_hand_qty
 
  select   * from xh_mtliface_negative_onhand_v
 
— 查询接口中 涉及现有量不足以出库的记录
SELECT a.transaction_interface_id,
       msi.segment1,
       a.organization_id,
       a.subinventory_code,
       a.transaction_quantity
  FROM mtl_transactions_interface a,
       mtl_system_items_b msi,
       xh_mtliface_negative_onhand_v xmnov
 WHERE a.inventory_item_id = msi.inventory_item_id
 and   a.organization_id = msi.organization_id
 and   a.inventory_item_id = xmnov.inventory_item_id
 and   a.organization_id= xmnov.organization_id
 and   a.subinventory_code = xmnov.subinventory_code
 –and   a.error_explanation IS NOT NULL  
 
 

–汇总tmp表中 需求量> 现有量的情况
create or replace view xh_mtltmp_negative_onhand_v as
 SELECT *
   FROM (SELECT bb.inventory_item_id,
                bb.segment1,
                bb.organization_id,
                bb.subinventory_code,
                bb.transaction_quantity,
                XH_COMMON_UTL.get_onhand_qty(bb.inventory_item_id,
                                             bb.organization_id,
                                             bb.subinventory_code) on_hand_qty
           FROM (SELECT a.inventory_item_id,
                        msi.segment1,
                        a.organization_id,
                        a.subinventory_code,
                        SUM(a.transaction_quantity) transaction_quantity
                   FROM mtl_material_transactions_temp a, mtl_system_items_b msi
                  WHERE a.inventory_item_id = msi.inventory_item_id
                    AND a.organization_id = msi.organization_id
                    –AND a.error_explanation IS NOT NULL
                    and a.transaction_source_type_id in (2,8)
                  GROUP BY a.inventory_item_id,
                           msi.segment1,
                           a.organization_id,
                           a.subinventory_code) bb)
  WHERE abs(transaction_quantity) > on_hand_qty
 
  select * from xh_mtltmp_negative_onhand_v
     
–查询tmp表中 涉及现有量不足以出库的记录
SELECT a.transaction_temp_id,
       a.transaction_header_id,
       msi.segment1,
       a.organization_id,
       a.subinventory_code,
       a.transaction_quantity
  FROM mtl_material_transactions_temp a,
       mtl_system_items_b msi,
       xh_mtltmp_negative_onhand_v xmnov
 WHERE a.inventory_item_id = msi.inventory_item_id
 and   a.organization_id = msi.organization_id
 and   a.inventory_item_id = xmnov.inventory_item_id
 and   a.organization_id= xmnov.organization_id
 and   a.subinventory_code = xmnov.subinventory_code
 –and   a.error_explanation IS NOT NULL    
 
 –查询 tmp表中 涉及现有量不足以出库的记录 涉及的transaction_header_id
 
SELECT distinct
       a.transaction_header_id
  FROM mtl_material_transactions_temp a,
       mtl_system_items_b msi,
       xh_mtltmp_negative_onhand_v xmnov
 WHERE a.inventory_item_id = msi.inventory_item_id
 and   a.organization_id = msi.organization_id
 and   a.inventory_item_id = xmnov.inventory_item_id
 and   a.organization_id= xmnov.organization_id
 and   a.subinventory_code = xmnov.subinventory_code
  –有6条
/* 
387395
390197
390199
390201
390202
392799
*/

SELECT DISTINCT a.transaction_header_id
  FROM mtl_material_transactions_temp a
–有9条
/*
    333352
387395
390197
    390198
390199
    390200
390201
390202
392799
*/
–物料事务处理程序每次最大提取200行作为一个数据批，下面的Sql可以看出来
–行数大可以提高性能，但是也容易被堵住，因为一个批中只要有一条有问题，整个批就处理不过去了
select transaction_header_id, count(*) from  mtl_material_transactions_temp  group by transaction_header_id 
select * from mtl_material_transactions_temp
/*
1 333352 1
2 387395 200
3 390197 200
4 390198 200
5 390199 200
6 390200 200
7 390201 200
8 390202 200
9 392799 46
*/

–按照上面的分析，333352，390198，390200 这三批数据不涉及库存现有量不足的问题，应该能被处理过去。
select * from mtl_material_transactions_temp where transaction_header_id = 333352
这个批就一条记录，

      
–备份
create table MTL_TRX_INTERFACE_BAK070504
as select * from MTL_TRANSACTIONS_INTERFACE WHERE error_code IS NOT NULL
      
–更新错误记录
UPDATE MTL_TRANSACTIONS_INTERFACE
   SET process_flag           = 1,
       lock_flag              = 2,
       TRANSACTION_MODE       = 3,
       –last_update_login      = NULL,
       –request_id             = NULL,
       –program_application_id = NULL,
       –program_id             = NULL,
       –program_update_date    = NULL,
       –primary_quantity       = NULL,
      — acct_period_id         = NULL,
       error_explanation      = NULL,
       –shippable_flag         = NULL,
       error_code             = NULL,
       –overcompletion_primary_qty = NULL      
 WHERE error_code IS NOT NULL
 AND   transaction_interface_id = 321706  
 
 
— 查找重复的记录   Note:294391.1
1. View to check if the records are duplicated between MMTT and MTI.

create or replace view mmtt_mti_records_v as
select a.transaction_interface_id ,
a.picking_line_id from
mtl_material_transactions_temp b, mtl_transactions_interface a
where
–a.picking_line_id = b.picking_line_id and
a.trx_source_line_id = b.trx_source_line_id
and a.inventory_item_id = b.inventory_item_id
and b.transaction_type_id = a.transaction_type_id
and b.transaction_source_type_id in (2,8)
and b.source_line_id = a.source_line_id   –add
and b.transaction_reference = a.source_header_id  –add
–and b.picking_line_id is not null ;

2. View to check if the records are duplicated between MMT and MMTT.

create or replace view mmt_mmtt_records_v as
select a.transaction_temp_id,
a.picking_line_id from
mtl_material_transactions b, mtl_material_transactions_temp a
where
–a.picking_line_id = b.picking_line_id and
a.trx_source_line_id = b.trx_source_line_id
and a.inventory_item_id = b.inventory_item_id
and b.transaction_type_id = a.transaction_type_id
and b.transaction_source_type_id in ( 2,8)
–and b.picking_line_id is not null;

3. View to check if the records are duplicated between MMT and MTI

create or replace view mmt_mti_records_v as
select a.transaction_interface_id ,
a.picking_line_id from
mtl_material_transactions b, mtl_transactions_interface a
where
–a.picking_line_id = b.picking_line_id and
a.trx_source_line_id = b.trx_source_line_id
and a.inventory_item_id = b.inventory_item_id
and b.transaction_type_id = a.transaction_type_id
and b.transaction_source_type_id in (2,8)
–and b.picking_line_id is not null ;

The below scripts will provide the count of such duplicate records.

select count(*) from mmt_mmtt_records_v;
select count(*) from mmtt_mti_records_v;
select count(*) from mmt_mti_records_v;
    

–1创建重复记录的备份
create table mtl_trx_iface_bak0506 as
SELECT *
  FROM mtl_transactions_interface
 WHERE transaction_interface_id IN
       (SELECT transaction_interface_id FROM mmtt_mti_records_v)
      
–2从interface 表中删除重复记录
DELETE FROM mtl_transactions_interface
 WHERE transaction_interface_id IN
       (SELECT transaction_interface_id FROM mtl_trx_iface_bak0506)
–3更新temp表中的模式 8-> 3
update mtl_material_transactions_temp mmtt set mmtt.transaction_mode = 3
–select count(*) from mtl_material_transactions_temp mmtt
where
mmtt.transaction_mode = 8 and
mmtt.transaction_temp_id in
(select b.transaction_temp_id from mtl_material_transactions_temp b ,
                                 mtl_trx_iface_bak0506 a
                            where a.trx_source_line_id = b.trx_source_line_id
and a.inventory_item_id = b.inventory_item_id
and b.transaction_type_id = a.transaction_type_id
and b.transaction_source_type_id in (2,8)
and b.source_line_id = a.source_line_id   –add
and b.transaction_reference = a.source_header_id
and b.organization_id = a.organization_id )

–4 到前台界面上找出所有待定事务处理，然后全部提交
–提交后发现没有反应，查数据库发现mmtt表中数据的lock_flag  被标记为’Y’
–但是这些pengding的事务依然被pending 者，再怎么重新提交也无反应

select count(*) from mtl_material_transactions_temp mmtt where
mmtt.transaction_mode in (3,8) and lock_flag =’Y’ and error_code is not null
–1200行
–备份 lock_flag = ‘Y’的行
create table mtl_trx_temp_bak0506 as
select * from  mtl_material_transactions_temp mmtt where
mmtt.transaction_mode =3  and lock_flag =’Y’
—
select count(*) from mtl_trx_temp_bak0506
select distinct transaction_header_id from mtl_trx_temp_bak0506
–更新 备份记录的lock_flag
update mtl_material_transactions_temp set lock_flag = NULL where
transaction_temp_id in (select transaction_temp_id from mtl_trx_temp_bak0506)
–更新未处理的重新提交
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
   SET PROCESS_FLAG      = ‘Y’,
       LOCK_FLAG         = ‘N’,
       TRANSACTION_MODE  = 3,
       ERROR_CODE        = NULL,
       ERROR_EXPLANATION = NULL
 WHERE transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0506)

—
SELECT *
  –distinct request_id
  FROM MTL_MATERIAL_TRANSACTIONS_TEMP
 WHERE transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0506)
–更新后发现 过了1分钟 lock_flag 又自动被设置成’Y’ 但是记录依然没有被处理
–再次检查 Transaction Manager 发现其定义的Workshift 的 Process数=0
–把process 数改成2 ，保存。

— metalink bug : 3182708 描述了同样的问题，没有答案

      
select * from v$locked_object a,all_objects b
              where a.object_id = b.object_id
              and b.object_name = ‘mtl_material_transactions_temp’

–已经被处理的记录
select b.order_number||’.’|| ‘SH_标准销售_T17.ORDER ENTRY’ ,a.* from mtl_trx_iface_bak0506 a,
 oe_order_headers_all b
 where  a.source_header_id = b.header_id
 and a.transaction_interface_id not in
(select a.transaction_interface_id from mtl_material_transactions_temp b ,
                                 mtl_trx_iface_bak0506 a
                            where a.trx_source_line_id = b.trx_source_line_id
and a.inventory_item_id = b.inventory_item_id
and b.transaction_type_id = a.transaction_type_id
and b.transaction_source_type_id in (2,8)
and b.source_line_id = a.source_line_id   –add
and b.transaction_reference = a.source_header_id
and b.organization_id = a.organization_id )
–备份已经处理完的记录，这些记录可以不再关注，并且需要从mtl_trx_iface_bak0506中删除
create table mtl_trx_iface_bak0506_prced as
select * from mtl_trx_iface_bak0506  a where
a.transaction_interface_id not in
(select a.transaction_interface_id from mtl_material_transactions_temp b ,
                                 mtl_trx_iface_bak0506 a
                            where a.trx_source_line_id = b.trx_source_line_id
and a.inventory_item_id = b.inventory_item_id
and b.transaction_type_id = a.transaction_type_id
and b.transaction_source_type_id in (2,8)
and b.source_line_id = a.source_line_id   –add
and b.transaction_reference = a.source_header_id
and b.organization_id = a.organization_id )

select count(*) from mtl_trx_iface_bak0506_prced
–从mtl_trx_iface_bak0506中删除 已经处理完的记录
delete from mtl_trx_iface_bak0506 where transaction_interface_id in
(select transaction_interface_id from mtl_trx_iface_bak0506_prced)
      
—
–查询 tmp表中 涉及现有量不足以出库的记录 涉及的transaction_header_id
 
SELECT distinct
       a.transaction_header_id
  FROM mtl_material_transactions_temp a,
       mtl_system_items_b msi,
       xh_mtltmp_negative_onhand_v xmnov
 WHERE a.inventory_item_id = msi.inventory_item_id
 and   a.organization_id = msi.organization_id
 and   a.inventory_item_id = xmnov.inventory_item_id
 and   a.organization_id= xmnov.organization_id
 and   a.subinventory_code = xmnov.subinventory_code
  –有6条
/* 
387395
390197
390199
390201
390202
392799
*/

— 查看未处理记录的批ID
select transaction_header_id ,count(*) from mtl_material_transactions_temp
where  transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0506)
       group by transaction_header_id

/*
1 390197 200
   2 390198 200
3 390199 200
   4 390200 200
5 390201 200
6 390202 200
*/

— 批 390198 和 390200 不涉及库存现有量不足的问题，先解决这两个批
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
   SET PROCESS_FLAG      = ‘Y’,
       LOCK_FLAG         = ‘N’,
       TRANSACTION_MODE  = 3,
       ERROR_CODE        = NULL,
       ERROR_EXPLANATION = NULL
 WHERE transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0506)
       and transaction_header_id in (390198,390200)
–再查看 查看未处理记录的批ID
select transaction_header_id ,count(*) from mtl_material_transactions_temp
where  transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0506)
       group by transaction_header_id
/*
1 390197 200
2 390199 200
3 390201 200
4 390202 200
5 397502 200
6 397503 200
*/
–发现 390198,390200 被处理了一下，批ID被标记成 397502，397503 但是依然没有被处理过去。
–在接口管理器，把物料事务处理的工人行改成50
–再次更新tmp表，重新提交
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
   SET PROCESS_FLAG      = ‘Y’,
       LOCK_FLAG         = ‘N’,
       TRANSACTION_MODE  = 3,
       ERROR_CODE        = NULL,
       ERROR_EXPLANATION = NULL
 WHERE transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0506)
      
–立刻 再查看 查看未处理记录的批ID
select transaction_header_id ,count(*) from mtl_material_transactions_temp
where  transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0506)
       group by transaction_header_id
–发现变成了 24批，每批50个.但依然是一条也未处理过去。    

–几分种后再查询 
select transaction_header_id ,count(*) from mtl_material_transactions_temp
where  transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0506)
       group by transaction_header_id
–发现只有17个批了，而且只有5个批还是50条，其他批都变成1条两条了.说明已经有大部分被处理过去了。
SELECT COUNT(*)
  FROM mtl_material_transactions_temp
 WHERE transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0506)
–计数=271 。说明已经有1200-271条被处理过去了

–再看这些未被处理的记录的详细情况
select * from  mtl_material_transactions_temp
 WHERE transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0506)

 

/*select count(*) from mmt_mmtt_records_v;
select count(*) from mmtt_mti_records_v;
select count(*) from mmt_mti_records_v;

select * from mmtt_mti_records_v*/

–1创建重复记录的备份
create table mtl_trx_iface_bak0506_2 as
SELECT *
  FROM mtl_transactions_interface
 WHERE transaction_interface_id IN
       (SELECT transaction_interface_id FROM mmtt_mti_records_v)
–select count(*) from mtl_trx_iface_bak0506_2
      
–select distinct transaction_source_type_id from  mtl_trx_iface_bak0506      
      
–2 备份tmp表记录
create table mtl_trx_temp_bak0506_2 as
select
*
–count(*)
from mtl_material_transactions_temp mmtt
where
mmtt.transaction_mode = 8 and
mmtt.transaction_temp_id in
(select b.transaction_temp_id from mtl_material_transactions_temp b ,
                                 mtl_trx_iface_bak0506_2 a
                            where a.trx_source_line_id = b.trx_source_line_id
and a.inventory_item_id = b.inventory_item_id
and b.transaction_type_id = a.transaction_type_id
and b.transaction_source_type_id in (2,8)
and b.source_line_id = a.source_line_id   –add
and b.transaction_reference = a.source_header_id
and b.organization_id = a.organization_id )

select count(*) from mtl_trx_temp_bak0506_2
–3 从interface 表中删除重复记录
DELETE FROM mtl_transactions_interface
 WHERE transaction_interface_id IN
       (SELECT transaction_interface_id FROM mtl_trx_iface_bak0506_2)

–4更新temp表中的模式 8-> 3
update mtl_material_transactions_temp mmtt set mmtt.transaction_mode = 3
where
mmtt.transaction_mode = 8 and
mmtt.transaction_temp_id in
(select transaction_temp_id from mtl_trx_temp_bak0506_2 )

–1分钟后检查temp表
SELECT *
  FROM mtl_material_transactions_temp mmtt
 WHERE mmtt.transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0506_2)

–没有效果，错误记录依然存在

—
–查询 tmp表中 涉及现有量不足以出库的记录 涉及的transaction_header_id
 
SELECT distinct
       a.transaction_header_id
  FROM mtl_material_transactions_temp a,
       mtl_system_items_b msi,
       xh_mtltmp_negative_onhand_v xmnov
 WHERE a.inventory_item_id = msi.inventory_item_id
 and   a.organization_id = msi.organization_id
 and   a.inventory_item_id = xmnov.inventory_item_id
 and   a.organization_id= xmnov.organization_id
 and   a.subinventory_code = xmnov.subinventory_code
  –有6条
/* 
1 387395
2 392799
3 398837
4 398842
5 398844
6 398856
*/

— 查看未处理记录的批ID
select transaction_header_id ,count(*) from mtl_material_transactions_temp
where  transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0506_2)
       group by transaction_header_id
/*1 387395 200*/
–这批数据存在库存现有量不足的情况。
–在接口管理器，原来的工人行是200,现在是50
–再次更新tmp表，重新提交
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
   SET PROCESS_FLAG      = ‘Y’,
       LOCK_FLAG         = ‘N’,
       TRANSACTION_MODE  = 3,
       ERROR_CODE        = NULL,
       ERROR_EXPLANATION = NULL
 WHERE transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0506_2)
–立刻 再查看 查看未处理记录的批ID
select transaction_header_id ,count(*) from mtl_material_transactions_temp
where  transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0506_2)
       group by transaction_header_id
–发现变成了 4批，每批50个.
/*402443 50
402444 50
402445 50
402446 50*/
–几分种后再查询 
select transaction_header_id ,count(*) from mtl_material_transactions_temp
where  transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0506_2)
       group by transaction_header_id
/*
402444 2
402445 1 
*/   
–发现只剩下2批了，且2批加起来也只有3条。

–再看这些未被处理的记录的详细情况
select * from  mtl_material_transactions_temp
 WHERE transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0506_2)

–这三条记录的 error_code =  事务处理处理器错误  ; error_explanation=不允许负余量 ;process_flag=E
–这个结果实际上否定了我原来的设想，我原来设想，如果一个批的记录中有一条出现错误，则整个批都不会被处理。
–而现在的事实是4个批中，有两个批是部分被处理了，也就是说一个批中，没有问题的记录会被处理，有问题的记录会被留下。不是同生同死的。
–这就奇怪了，为什么昨天没有做到这一点呢？昨天和今天相比较，我只是把工人行从200改成50而已，其他没有改变啊。
–另外的改变就是可能功能顾问补充了一些子库的现有量. 要这么说的话，昨天至少也有些记录是现有量足够的。怎么就一条都没有被处理呢？
–看这三条记录 的request_id,查请求，发现对应的并发程序是 transaction worker process.说明这三条记录最后是被transaction worker process处理的。
–这才是正常现象。而昨天就没有走到这一步。而是Process transaction interface处理了以后没有调用transaction worker process
–是谁修改了设置，或者启动了一个什么服务，把这个程序又给弄正常了？ 问 现场的功能顾问，有没有改什么设置。

/*******************************************************************************
date : 2007-05-07
处理者: yunfang.shang
*******************************************************************************/

/*select count(*) from mmt_mmtt_records_v;
select count(*) from mmtt_mti_records_v;
select count(*) from mmt_mti_records_v;

select * from mmtt_mti_records_v*/

–1创建重复记录的接口备份
create table mtl_trx_iface_bak0507 as
SELECT *
  FROM mtl_transactions_interface
 WHERE transaction_interface_id IN
       (SELECT transaction_interface_id FROM mmtt_mti_records_v)
select count(*) from mtl_trx_iface_bak0507
–46条
 
  

–查询这些记录是否存在 现有量不足的问题
SELECT a.transaction_interface_id,
       msi.segment1,
       a.organization_id,
       a.subinventory_code,
       a.transaction_quantity
  FROM mtl_trx_iface_bak0507 a,
       mtl_system_items_b msi,
       xh_mtliface_negative_onhand_v xmnov
 WHERE a.inventory_item_id = msi.inventory_item_id
 and   a.organization_id = msi.organization_id
 and   a.inventory_item_id = xmnov.inventory_item_id
 and   a.organization_id= xmnov.organization_id
 and   a.subinventory_code = xmnov.subinventory_code
 and   a.error_explanation IS NOT NULL       

–检查结果为： 存在

–2 备份tmp表记录
create table mtl_trx_temp_bak0507 as
select
*
–count(*)
from mtl_material_transactions_temp mmtt
where
mmtt.transaction_mode = 8 and
mmtt.transaction_temp_id in
(select b.transaction_temp_id from mtl_material_transactions_temp b ,
                                   mtl_trx_iface_bak0507 a
                            where a.trx_source_line_id = b.trx_source_line_id
and a.inventory_item_id = b.inventory_item_id
and b.transaction_type_id = a.transaction_type_id
and b.transaction_source_type_id in (2,8)
and b.source_line_id = a.source_line_id   –add
and b.transaction_reference = a.source_header_id
and b.organization_id = a.organization_id )

select count(*) from mtl_trx_temp_bak0507
–46条

–删除接口表中已经备份的部分
delete from mtl_transactions_interface 
 WHERE transaction_interface_id IN
       (SELECT transaction_interface_id FROM mtl_trx_iface_bak0507)

–46行被删除
 
–再查看 查看未处理记录的批ID
select transaction_header_id ,count(*) from mtl_material_transactions_temp
where  transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0507)
       group by transaction_header_id
/*      
392799 46  */    

–再次更新tmp表，重新提交
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
   SET PROCESS_FLAG      = ‘Y’,
       LOCK_FLAG         = ‘N’,
       TRANSACTION_MODE  = 3,
       ERROR_CODE        = NULL,
       ERROR_EXPLANATION = NULL
 WHERE transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0507)
–46 行被更新

–立刻 再查看 查看未处理记录的批ID
select transaction_header_id ,count(*) from mtl_material_transactions_temp
where  transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0507)
       group by transaction_header_id
—    
/*402985 46*/
–几分种后再查询 
select transaction_header_id ,count(*) from mtl_material_transactions_temp
where  transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0507)
       group by transaction_header_id
—
/*402985 46*/
SELECT COUNT(*)
  FROM mtl_material_transactions_temp
 WHERE transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0507)
–计数=46 。说明没有任何记录被处理

–再看这些未被处理的记录的详细情况
select * from  mtl_material_transactions_temp
 WHERE transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0507)
–request_id = 363987 对应的并发程序是： Process transaction interface
–lock_flag =Y
–没有任何一条记录的error_code 和 error_explanation 有内容
–这个情况与昨天的情况很类似，处理过程没有走到 transaction worker process这一步
–不过昨天的问题，在今天更改了 工人行数后，已经被处理了。不过昨天的记录在今天之所以能被处理
–好像 是因为 这些记录被重新分组的原因。因为昨天是200条一组，而今天是50条一组，46条正好在一组内，再次提交没有被重新分组。

–假设就是因为没有重新分组的原因，现在到前台界面 把工人行改成40 这样会分成2组，必然要重新分组
–再次更新tmp表，重新提交
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
   SET PROCESS_FLAG      = ‘Y’,
       LOCK_FLAG         = ‘N’,
       TRANSACTION_MODE  = 3,
       ERROR_CODE        = NULL,
       ERROR_EXPLANATION = NULL
 WHERE transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0507)
–46 行被更新
–立刻 再查看 查看未处理记录的批ID
select transaction_header_id ,count(*) from mtl_material_transactions_temp
where  transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0507)
       group by transaction_header_id
—  
/*
403453 40
403454 6  */
–可见，被分亨2组了。

–几分种后再查询 
select transaction_header_id ,count(*) from mtl_material_transactions_temp
where  transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0507)
       group by transaction_header_id
/*
403453 40
403454 2  */      
–再看这些未被处理的记录的详细情况
select * from  mtl_material_transactions_temp
 WHERE transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0507)
      
–组  403453 的最后处理请求是 Process transaction interface  。这组中的所有数据都没有使用 transaction worker process 进一步处理。
–组  403454 的最后处理请求是 transaction worker process  ，这组中有4条被处理，剩下2条错误记录。

–把  把工人行改成20 ，强制拆原来的 组  403453

–再次更新tmp表，重新提交
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
   SET PROCESS_FLAG      = ‘Y’,
       LOCK_FLAG         = ‘N’,
       TRANSACTION_MODE  = 3,
       ERROR_CODE        = NULL,
       ERROR_EXPLANATION = NULL
 WHERE transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0507)
      
 –42 行被更新     
 –立刻 再查看 查看未处理记录的批ID
select transaction_header_id ,count(*) from mtl_material_transactions_temp
where  transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0507)
       group by transaction_header_id
—  
/*
403606 20
403607 20
403608 2
 */
–可见，被分亨2组了。

–几分种后再查询 
select transaction_header_id ,count(*) from mtl_material_transactions_temp
where  transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0507)
       group by transaction_header_id
/*
403606 20
403607 20
403608 2  */ 

select transaction_header_id ,count(*) from mtl_material_transactions_temp  group by transaction_header_id
/*
333352 1
402444 2
402445 1
403606 20
403607 20
403608 2
404554 20
404556 20
404557 2
404558 2
404559 20
404560 1
404561 20
404562 20
404563 1
404564 20
404565 20
404566 2
404567 11
*/
— 那些 count数=设置的工人行数 的记录  都是些只处理了一半的记录，那么这些记录究竟有什么特点呢？ 我估计可能是这种批的记录里面有一条记录
— 在处理这条记录的时候，transaction worker process 遇到了无法预见的错误，然后就直接回滚了，看起来就是transaction worker process没有处理
— 这个批的记录。
— 下面的事实可以证实这个想法 :
— 以系统管理员职责 在导航->并发->请求 按名称查询请求 库存事务处理工作流程(transaction worker process) 查询条数设置5天
— 可以看到请求的参数中有tmp表的组ID(transaction_header_id) 而且这些组ID不跳号。 比如组ID 404565 也能找到对应的并发请求ID 364115
— 364115的运行结果是错误，但日志中没有错误信息。错误以后，这个请求也没有去更新tmp表。所以tmp表也没有记录曾经运行过这请求的ID
— 但是在tmp表中记录的request_id 不是 364115 ，而是 Process transaction interface 的request_id
select request_id from mtl_material_transactions_temp where transaction_header_id = 404565
–364089
–364089 对应的请求是 处理事务处理接口(Process transaction interface)

–所以需要设置debug模式，才能在日志中把错误原因显示出来。在待定事务处理界面上无法得到这种错误信息。
–打开如下系统配置文件
/*
          INV DEBUG TRACE  ==> ON
          INV DEBUG LEVEL  ==>  9
          INV DEBUG FILE   ==> File name including directory.( /tmp/invdebug.log)
         
*/

–然后到应用服务器上去找这样的文件 /tmp/invtrx.log-TRX4545454

–再次更新tmp表，重新提交
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
   SET PROCESS_FLAG      = ‘Y’,
       LOCK_FLAG         = ‘N’,
       TRANSACTION_MODE  = 3,
       ERROR_CODE        = NULL,
       ERROR_EXPLANATION = NULL
 WHERE transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0507)
 –42 行被更新
 select transaction_header_id ,count(*) from mtl_material_transactions_temp
where  transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0507)
       group by transaction_header_id
—  
/*
407389 20
407390 20
407391 2*/

— 查并发请求 这三个组ID对应的 transaction worker process 请求ID分别为

/*364255
364256
364257*/

–状态都是错误
–从服务器下载了几个debug文件，里面只是显示几点几分调用什么内容，不能看出哪个请求处理哪个组的数据，遇到什么问题，这些都没有记录。
–看来 debug 文件对这个问题帮助不大。

–作Trace
–对并发程序 库存事务处理工作流程 启用跟踪

–再次更新tmp表，重新提交
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
   SET PROCESS_FLAG      = ‘Y’,
       LOCK_FLAG         = ‘N’,
       TRANSACTION_MODE  = 3,
       ERROR_CODE        = NULL,
       ERROR_EXPLANATION = NULL
 WHERE transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0507)
 –42 行被更新
 select transaction_header_id ,count(*) from mtl_material_transactions_temp
where  transaction_temp_id IN
       (SELECT transaction_temp_id FROM mtl_trx_temp_bak0507)
       group by transaction_header_id

/*407525 20
407526 20
407527 2*/

— 查并发请求 这三个组ID对应的 transaction worker process 请求ID分别为

/*364267
364268
364269*/

— 查看请求ID 364268 对应的 trace文件 发现其中有三处rollback 操作
— 其中 ROLLBACK WORK 是关键，在这句话前，我们看到其实有很多记录已经被标注为错误记录，也有很少几条记录被处理成功，
— 都最终都被rollback 了 。
— Rollback前最后被执行的SQL 是：DELETE MTL_ONHAND_LOCK_TEMP 这句话有什么问题吗？
— 可惜Trace文件不能反映 ORA:错误，只能看到执行了那些SQL ,如何定位问题。要是能结合源代码或许能找到原因。

— 查可执行定义 知道  transaction worker process 对应的可执行文件是INCTCW . 但$INV_TOP/bin 目录下没有这个文件
— 不过 Process transaction interface 对应的可执行 INCTCM 在$INV_TOP/bin 目录下也没有
— INCTM 是否可能包含了这两个可执行？ 打开INCTM 发现里面确实有 INCTCW 和 INCTCM 两个字符串
— 改天继续研究…….
–晚上7:00多，李进顶不住压力，终于在组织层启用了 允许负库存。
–再次更正tmp表中的数据后，所有待定事务被处理完成。

–晚上 7:30
–再次检查
select * from mtl_material_transactions_temp
–发现还有一条 transaction_mode =8
— 1 333352 333277 ORDER ENTRY 8943 8  2007-5-4 10:42:18 0 2007-5-4 10:42:18 1137 55969 359196 401 32321 2007-5-4 10:42:18 90  108 111213001  -1 -1 Bu  33 1 2 8282  2007-4-27 15:28:23 3001 196840 5306                       32220                                                               Y N  Y                               N              0                                                                     
–更正数据
UPDATE MTL_MATERIAL_TRANSACTIONS_TEMP
   SET PROCESS_FLAG      = ‘Y’,
       LOCK_FLAG         = ‘N’,
       TRANSACTION_MODE  = 3,
       ERROR_CODE        = NULL,
       ERROR_EXPLANATION = NULL
 WHERE transaction_temp_id =333277

 –1行被更新
 –2分钟后再查
 select count(*) from mtl_material_transactions_temp
 –0 说明已经被处理过去了