EBS 接口表的问题

sunjinhai

select *

from dba_objects db

where db.object_type = 'TABLE'

and db.object_name like '%INTERFACE%

select *

from dba_objects db

where db.object_type = 'TABLE'

and db.object_name like '%INTERFACE%' and owner like 'PO'

select *

from dba_objects db

where db.object_type = 'TABLE'

and db.object_name like '%INTERFACE%

-------------------------

WIP_MOVE_TXN_INTERFACE表请教

是处理车间物料移动的，使生产任务的物料从一个工序移到另一个工序，也可做完工入库的动作。

insert into WIP_MOVE_TXN_INTERFACE(ENTITY_TYPE

,PROCESS_PHASE

,PROCESS_STATUS

,TRANSACTION_TYPE

,LAST_UPDATE_DATE

,LAST_UPDATED_BY

,LAST_UPDATED_BY_NAME

,CREATION_DATE

,CREATED_BY

,CREATED_BY_NAME

,Last_Update_Login

,Wip_Entity_Id

,WIP_ENTITY_NAME

,Organization_Id

,Organization_Code

,Primary_Item_Id

,TRANSACTION_DATE

,Transaction_Quantity

,Transaction_Uom

,Primary_Quantity

,Primary_Uom

,Fm_Operation_Seq_Num

,Fm_Intraoperation_Step_Type

,To_Operation_Seq_Num

,To_Intraoperation_Step_Type

,Reference)

values(1
--Discrete job

,1
--Move validation

,1
--Pending

,1
--Move transaction

,sysdate

,111

,'TEST'

,sysdate

,111

,'TEST'

,-1

,66
--Wip_Entity_Id

,'ddd'
--WIP_ENTITY_NAME

,11
--Organization_Id

,111
--Organization_Code

,111
--Primary_Item_Id,

,
sysdate
--TRANSACTION_DATE

, 1

--Transaction_Quantity

,'PCS'
--Transaction_Uom

, 1
--Primary_Quantity

,'PCS'
--Primary_Uom

,10
--Fm_Operation_Seq_Num

, 1
--Fm_Intraoperation_Step_Type

,20
--To_Operation_Seq_Num

, 3
--To_Intraoperation_Step_Type

,'test');

----------------------------------------------------------------------

关于Inventory里的interface manager的研究

inventory/setup/transactions/interface managers

　　EBS里的Concurrent Manager实际上也是一个request的程序，而且如果ｒｅｑｕｅｓｔ　ｄｅｆｉｎｅ的类型是immediately　的类型的话，一定要运行在ｍａｍｎｇｅｒ中，这个是因为immediately类型的程序需要调用特定的库来执行的。其中ｉｎｔｅｒｆａｃｅ　ｍａｎａｇｅｒ里的程序就是其中的典型，因为他们都需要调用一个INVLIBR的库。

　　在ｉｎｔｅｒｆａｃｅ　ｍａｎａｇｅｒ里面有四个ｒｅｑｕｅｓｔ：

　　１．Cost Manager　这个是导入WIP_COST_TXN_INTERFACE　　中的数据的，如果这个ｍａｎａｇｅｒ的状态为ｉｎａｃｔｉｖｅ的话：可以手工提交：Cost Manager　这个ｃｏｎｃｕｒｒｅｎｔ。

　　２．Lot Move Transaction对应的接口不是很清楚，估计是MTL_TRANSACTION_LOTS_INTERFACE，对应的ｃｏｎｃｕｒｒｅｎｔ是：Manager: Lot Move Transactions

　　３．Material transaction　这个就想当的重要了，对应的接口是MTL_TRANSACTIONS_INTERFACE　对应的ｃｏｎｃｕｒｒｅｎｔ是：Manager: Process transaction interface

　　４．Move transaction对应的接口是：WIP_MOVE_TXN_INTERFACE　对应的ｃｏｎｃｕｒｒｅｎｔ是：WIP Move Transaction Manager

这四个程序都是处理Ｔａｎｓａｃｔｉｏｎｓ用的，可以把ｍａｎａｇｅｒ激活，定时的运行，也可以手工的提交。

　　这个ｉｎｔｅｒｆａｃｅ　ｍａｎｇｅｒ对应的ｍａｎｇｅｒ的ｎａｍｅ　是：Inventory Manager.可以在Ｓｙｓｔｅｍ　ａｄｍｉｎｉｓｔｒａｔｏｒ下的ｍａｎａｇｅｒ　的ｄｉｆｉｎｅ中详细查看里面的定义的程序的情况。

.

-----------------------------

INV中期末关帐时不计成本如何解决

INV在期末关帐的时候经常出现不计成本的错误,这些错误很大的原因时Cost manager运行有问题,或者没有起来,或者运行用错误,但是如果Cost manager也没有什么问题,就很少的几条记录因为其它不知名原因出现了不计成本的错误导致没有办法关帐.下面的SQL可以解决:

第一段處理(子庫移轉)

update mtl_material_transactions

set costed_flag = 'N',

transaction_group_id = NULL,

transaction_set_id = NULL,

error_code = null,

error_explanation = null,

cost_update_id = null

where costed_flag = 'E' or costed_flag = 'N' ;

第二段處理(WIP發放)

Update WIP_COST_TXN_INTERFACE

Set GROUP_ID = NULL,

TRANSACTION_ID = NULL,

REQUEST_ID = NULL,

PROCESS_STATUS = 1

Where PROCESS_STATUS = 3;

再做

commit;

最後再跑一次成本Cost manager OK

--------------------------------------------

在关闭Inventory会计期时pengding的数据对应的SQL:

A. Resolution Required

1 Unprocessed Material

SELECT COUNT(*)

FROM MTL_MATERIAL_TRANSACTIONS_TEMP

WHERE ORGANIZATION_ID = &OrgID AND TRANSACTION_DATE <= '&EndPeriodDate' AND NVL(TRANSACTION_STATUS,0) <> 2;

2 Uncosted Material

SELECT COUNT(*)

FROM MTL_MATERIAL_TRANSACTIONS

WHERE ORGANIZATION_ID = &OrgID AND TRANSACTION_DATE <= '&EndPeriodDate' AND COSTED_FLAG IS NOT NULL;

3 Pending WIP Transactions

SELECT COUNT(*) FROM WIP_COST_TXN_INTERFACE WHERE ORGANIZATION_ID = &OrgID AND TRANSACTION_DATE <= '&EndPeriodDate';

4 Uncosted WSM

SELECT COUNT(*)

FROM WSM_SPLIT_MERGE_TRANSACTIONS

WHERE ORGANIZATION_ID = &OrgID AND COSTED <> 4 AND TRANSACTION_DATE <= '&EndPeriodDate';

5 Pending WMS Interface

SELECT COUNT(*)

FROM WSM_SPLIT_MERGE_TXN_INTERFACE

WHERE ORGANIZATION_ID = &OrgID AND PROCESS_STATUS <> 4 AND TRANSACTION_DATE <= '&EndPeriodDate';

B. Resolution Recommended

6 Pending Receiving

SELECT COUNT(*)

FROM RCV_TRANSACTIONS_INTERFACE

WHERE TO_ORGANIZATION_ID = &OrgID AND TRANSACTION_DATE <= '&EndPeriodDate' AND DESTINATION_TYPE_CODE = 'INVENTORY';

7 Pending Material

SELECT COUNT(*)

FROM MTL_TRANSACTIONS_INTERFACE

WHERE ORGANIZATION_ID = &OrgID AND TRANSACTION_DATE <= '&EndPeriodDate' AND PROCESS_FLAG <> 9;

8 Pending Shop Floor Move

SELECT COUNT(*) FROM WIP_MOVE_TXN_INTERFACE WHERE ORGANIZATION_ID = &OrgID AND TRANSACTION_DATE <= '&EndPeriodDate';

C. Resolution Required / Recommended

9 Unprocessed Shipping Transactions (Pending Transactions)

SELECT COUNT(*)

FROM WSH_DELIVERY_DETAILS WDD, WSH_DELIVERY_ASSIGNMENTS WDA, WSH_NEW_DELIVERIES WND,

WSH_DELIVERY_LEGS WDL, WSH_TRIP_STOPS WTS

WHERE WDD.SOURCE_CODE = 'OE' AND WDD.RELEASED_STATUS = 'C'

AND WDD.INV_INTERFACED_FLAG IN ('N' ,'P') AND WDD.ORGANIZATION_ID = &OrgID

AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID AND WND.DELIVERY_ID = WDA.DELIVERY_ID

AND WND.STATUS_CODE IN ('CL','IT') AND WDL.DELIVERY_ID = WND.DELIVERY_ID

AND WTS.PENDING_INTERFACE_FLAG = 'Y' AND TRUNC(WTS.ACTUAL_DEPARTURE_DATE) BETWEEN '&StartPeriodDate' AND '&EndPeriodDate'

AND WDL.PICK_UP_STOP_ID = WTS.STOP_ID;

Unprocessed material transaction are stored into the table
MTL_TRANSACTIONS_INTERFACE

Pending material transactions are stored into the the table MTL_MATERIAL_TRANSACTIONS_TEMP

Pending and Unprocessed Shopfloor move transactions are stored into the table WIP_MOVE_TXN_INTERFACE

Uncosted material transaction are stored into the table MTL_MATERIAL_TRANSACTIONS with COSTED_FLAG not null (N for uncosted, E for error)

Uncosted WIP transaction are stored into the table WIP_COST_TXN_INTERFACE

A Guide To Resolving Pending Transaction Issues

1.察看Pending Transactions
Path: Nav > Cost > Accounting Close Cycle > Inventory Accounting Periods

甲.Resolution Required区：一定要先解决才可关帐
01.Unprocessed Material field：MTL_MATERIAL_TRANSACTONS_TEMP有未处理的料件交易信息
02.Uncosted Material field：MTL_MATERIAL_TRANSACTIONS有未处理的会计分录
03.Pending WIP Costing：WIP_COST_TXN_INTERFACE有资源和制造费用的会计分录尚未处理
乙.Resolution Recommended区：可以关，但若关帐后则不能在处理
01.Pending Receiving：RCV_TRANSACTIONS_INTERFACE，于采购交易未处理如：从仓库收料或退回，可以关但会有警告讯息。
02.Pending Material：MTL_TRANSACTIONS_INTERFACE，有未处理的料件交易信息。
03.Pending Move：WIP_MOVE_TXN_INTERFACE，有未处理的Shop Floor搬移交易（Move Txn.）。

2.RESOLVING UNPROCESSED AND UNCOSTED MATERIAL TRANSACTIONS

甲.Resolution Required区：一定要先解决才可关帐
01.Unprocessed Material field：MTL_MATERIAL_TRANSACTONS_TEMP
可以经由此路径了解原因：解决问题更正，然后重新执行
Path: Nav > Inventory > Transactions > Pending Transactions
可以透过Resubmitted更正后重新执行或透过下列程序更正：

Update MTL_MATERIAL_TRANSACTIONS_TEMP
Set PROCESS_FLAG = ‘Y’,
LOCK_FLAG = ‘N’,
TRANSACTION_MODE = 3,
ERROR_CODE = NULL
Where TRANSACTION_ID = ‘& TRANSACTION_ID’;


02.Uncosted Material field：MTL_MATERIAL_TRANSACTIONS有未处理的会计分录
可以察看此Table中COSTED_FLAG are: N = Not Costed
E = Error
Null = Costed

只能透过SQL来更新处理（更新costed_flag =‘N’和transaction_group_id = NULL.）
Update MTL_MATERIAL_TRANSACTIONS
set COSTED_FLAG = ‘N’,
set TRANSACTION_GROUP_ID = NULL
where COSTED_FLAG = ‘E’ or COSTED_FLAG = ‘N’;

若有错误数据记录，可以透过查询Concurrent Process方式查询
Path: Nav > System Administrator > Concurrent > Request

yyanjob

沙发！！！！！！！！！！

wallace_shu

留个脚印！