EBS:物料事务处理接口查询

EBS 事务处理接口

查询 语句

 

 -- 组织代码在指定范围 内，一般直接删除此接口表有错误的记录。 
SELECT OOD.ORGANIZATION_ID AS 组织ID,
       OOD.ORGANIZATION_CODE AS 组织代码,
       OOD.organization_name,
       MTI.ERROR_CODE AS "错误代码",
       MTI.ERROR_EXPLANATION AS "错误解释",
       MTST.TRANSACTION_SOURCE_TYPE_NAME AS "事务处理来源类型",
       MTT.TRANSACTION_TYPE_NAME  AS "事务处理类型",
       FLV.MEANING AS "事务处理活动名称",
       mti.*
  FROM INV.MTL_TRANSACTIONS_INTERFACE MTI ,
       APPS.ORG_ORGANIZATION_DEFINITIONS OOD,
        INV.MTL_TRANSACTION_TYPES MTT,
        INV.MTL_TXN_SOURCE_TYPES MTST ,
        APPS.FND_LOOKUP_VALUES_VL FLV   --  "事务处理活动名称",
 WHERE  MTI.ORGANIZATION_ID >= 1
   AND MTI.CREATION_DATE> TO_DATE('2023-10-01','YYYY-MM-DD')
   AND MTI.ORGANIZATION_ID = OOD.ORGANIZATION_ID
   AND MTI.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID 
   AND MTI.TRANSACTION_SOURCE_TYPE_ID = MTST.TRANSACTION_SOURCE_TYPE_ID 
   AND FLV.LOOKUP_TYPE = 'MTL_TRANSACTION_ACTION' 
   AND MTI.TRANSACTION_TYPE_ID = TO_NUMBER(FLV.LOOKUP_CODE) 
   AND OOD.ORGANIZATION_CODE LIKE '%' -- '132'
  --  AND OOD.ORGANIZATION_CODE IN ('113','120','123',,'124','132','181') 
 --AND TRANSACTION_INTERFACE_ID = 914878570 
 -- AND MTI.INVENTORY_ITEM_ID = 169804      
  AND MTI.ERROR_CODE IS NOT NULL     
 -- AND MTI.ERROR_CODE NOT IN ('分配帐户段无效','Invalid distribution account segments')  --这些不能界面上删除 
  -- AND MTI.ERROR_CODE = 'transaction_quantity'
  ORDER BY OOD.ORGANIZATION_CODE 
-- SELECT * FROM inv.mtl_transaction_interface
  
-- MTL_TRANSACTIONS_INTERFACE MTI

/*
-- 删除物料事务处理接口错误记录，共影响 14笔，
DELETE FROM INV.MTL_TRANSACTIONS_INTERFACE
WHERE  SOURCE_CODE = 'MOM_MISC'
AND PROCESS_FLAG = '3'
 AND ORGANIZATION_ID = 320 -- 132:XXX库存组织  , 帐户别名
 */
 

　　

记录一下查询语句 。