EBS接口表

最新推荐文章于 2022-11-19 23:54:13 发布

转载最新推荐文章于 2022-11-19 23:54:13 发布 · 262 阅读

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
a.