ORA-01591
Step 1. Select 'Rollback force '''||local_tran_id||'''' from sys.pending_trans$;
Find out the local_tran_id.
Step 2. Try to commit or rollback.
rollback force LOCAL_TRAN_ID;
or
commit force LOCAL_TRAN_ID;
Step 3. If still error . then log in use sys and execute :
set transaction use rollback segment system;
delete from dba_2pc_pending where local_tran_id = LOCAL_TRAN_ID;
delete from pending_sessions$ where local_tran_id = LOCAL_TRAN_ID;
delete from pending_sub_sessions$ where local_tran_id = LOCAL_TRAN_ID;
commit;
rollback force LOCAL_TRAN_ID;
1. 這是個初學農友的農友日誌~ 2. Oracle ERP 分享 3. Oracle DBA 心得 4. Android 開發心得 5. MoJo寵物手作粉絲成員 6. 程式客製及外包專案接案
2013年2月7日 星期四
2013年2月4日 星期一
Oracle ERP R12. CST_INVALID_WIP (The wip entity is either not defined or does not have a period balance entry.) - Discrete Job
Cause :
Records not found in WIP_PERIOD_BALANCES
(該工單於WIP_PERIOD_BALANCES裡,少了該交易期間的資料)
Solution :
I. Normally, when there are records in MTL_MATERIAL_TRANSACTIONS with the Costed_Flag = 'E' you would resubmit these records by doing the following :
(一般而言,若MMT裡有發現COSTED_FLAG=E時,正常異常排除的步驟如下)
a. Backup the rows to be updated. (將資料備份下來)
b. Turn off the Cost Manager. (先暫停Cost Manager,以避免干擾接下來要執行的步驟)
c. Run the SQL statement: (執行以下SQL)
UPDATE MTL_MATERIAL_TRANSACTIONS
SET costed_flag = 'N',
error_code = NULL,
error_explanation = NULL,
transaction_group_id = NULL
WHERE organization_id =
and transaction_source_id =
and costed_flag IS NOT NULL;
II. When this does not work, it is usually due to one or more of the following three conditions.
(若異常狀況仍存在,則可以是以下原因造成)
* Item costs are not defined for Frozen cost type in CST_ITEM_COSTS.
(CST_ITEM_COSTS沒有該品號的Frozen cost)
==> 請補建Item Cost 並Copy cost至Forezen cost
* WIP_PERIOD_BALANCES is missing rows for the acct_period_id.
(該工單於WIP_PERIOD_BALANCES缺少該期間的餘額資料)
==> 請於WIP_PERIOD_BALANCES補該筆工單該Period的資料
NOTE: THIS DOES NOT APPLY TO CLOSED DISCRETE JOBS WDJ.STATUS_TYPE = 12
(注意 : 該動作不適用於Closed的工單)
2013年1月31日 星期四
Oracle ERP R12. 使用interface批量變更品號的operation code . (Mass changes item's operation code use BOM_OP_SEQUENCES_INTERFACE. )
Sample code :
DECLARE
--
T_OLD_OP VARCHAR2(10) := '2T02'; --**** old operation code
T_NEW_OP VARCHAR2(10) := '2S02'; --**** new operation code
T_NEW_OP_ID NUMBER;
T_ORGANIZATION_ID NUMBER := 85; --organization id
T_DISABLE_DATE DATE := SYSDATE;
T_EFFECT_DATE DATE := SYSDATE+1/86400;
--
CURSOR C_LIST --item list ==> user define
IS
SELECT D.ORGANIZATION_ID,
D.INVENTORY_ITEM_ID,
A.ROUTING_SEQUENCE_ID,
B.OPERATION_SEQUENCE_ID,
B.OPERATION_SEQ_NUM,
C.STANDARD_OPERATION_ID
FROM BOM_OPERATIONAL_ROUTINGS A
JOIN BOM_OPERATION_SEQUENCES_V B--BOM_OPERATION_SEQUENCES B
ON A.COMMON_ROUTING_SEQUENCE_ID = B.ROUTING_SEQUENCE_ID
LEFT JOIN BOM_STANDARD_OPERATIONS C
ON B.STANDARD_OPERATION_ID = C.STANDARD_OPERATION_ID
JOIN MTL_SYSTEM_ITEMS_B D
ON A.ORGANIZATION_ID = D.ORGANIZATION_ID
AND A.ASSEMBLY_ITEM_ID = D.INVENTORY_ITEM_ID
JOIN BOM_DEPARTMENTS F
ON F.DEPARTMENT_ID = B.DEPARTMENT_ID
AND F.ORGANIZATION_ID = A.ORGANIZATION_ID
WHERE A.ORGANIZATION_ID = T_ORGANIZATION_ID
AND C.OPERATION_CODE = T_OLD_OP
AND (B.DISABLE_DATE IS NULL OR
B.DISABLE_DATE >= SYSDATE);
BEGIN
--Find the new operation id
SELECT BSO.STANDARD_OPERATION_ID
INTO T_NEW_OP_ID
FROM BOM_STANDARD_OPERATIONS BSO
WHERE BSO.ORGANIZATION_ID = T_ORGANIZATION_ID
AND BSO.OPERATION_CODE = T_NEW_OP;
FOR C1 IN C_LIST LOOP
--STEP 1. Disable old operation
BEGIN
INSERT INTO BOM_OP_SEQUENCES_INTERFACE
(ASSEMBLY_ITEM_ID,
ORGANIZATION_ID,
ROUTING_SEQUENCE_ID,
OPERATION_SEQUENCE_ID,
DISABLE_DATE,
TRANSACTION_TYPE,
PROCESS_FLAG,
BATCH_ID)
VALUES(C1.INVENTORY_ITEM_ID,
C1.ORGANIZATION_ID,
C1.ROUTING_SEQUENCE_ID,
C1.OPERATION_SEQUENCE_ID,
T_DISABLE_DATE,
'Update',
1,
1);
END;
--STEP 2. Add new operation
BEGIN
INSERT INTO BOM_OP_SEQUENCES_INTERFACE(
ASSEMBLY_ITEM_ID,
ORGANIZATION_ID,
ROUTING_SEQUENCE_ID,
OPERATION_SEQUENCE_ID,
OPERATION_SEQ_NUM,
EFFECTIVITY_DATE,
STANDARD_OPERATION_ID,
REFERENCE_FLAG,
TRANSACTION_TYPE,
PROCESS_FLAG,
BATCH_ID)
VALUES(C1.INVENTORY_ITEM_ID,
C1.ORGANIZATION_ID,
C1.ROUTING_SEQUENCE_ID,
C1.OPERATION_SEQUENCE_ID,
C1.OPERATION_SEQ_NUM,
T_EFFECT_DATE,
T_NEW_OP_ID,
1,
'Create',
1,
2);
END;
END LOOP;
END;
Step 3. Run BOM & Routing Import (Bill and Routing Interface) ==> batch id is set to 1 (For disable old operation code)
Step 4. Run BOM & Routing Import (Bill and Routing Interface) ==> batch id is set to 2 (For add new operation code)
DECLARE
--
T_OLD_OP VARCHAR2(10) := '2T02'; --**** old operation code
T_NEW_OP VARCHAR2(10) := '2S02'; --**** new operation code
T_NEW_OP_ID NUMBER;
T_ORGANIZATION_ID NUMBER := 85; --organization id
T_DISABLE_DATE DATE := SYSDATE;
T_EFFECT_DATE DATE := SYSDATE+1/86400;
--
CURSOR C_LIST --item list ==> user define
IS
SELECT D.ORGANIZATION_ID,
D.INVENTORY_ITEM_ID,
A.ROUTING_SEQUENCE_ID,
B.OPERATION_SEQUENCE_ID,
B.OPERATION_SEQ_NUM,
C.STANDARD_OPERATION_ID
FROM BOM_OPERATIONAL_ROUTINGS A
JOIN BOM_OPERATION_SEQUENCES_V B--BOM_OPERATION_SEQUENCES B
ON A.COMMON_ROUTING_SEQUENCE_ID = B.ROUTING_SEQUENCE_ID
LEFT JOIN BOM_STANDARD_OPERATIONS C
ON B.STANDARD_OPERATION_ID = C.STANDARD_OPERATION_ID
JOIN MTL_SYSTEM_ITEMS_B D
ON A.ORGANIZATION_ID = D.ORGANIZATION_ID
AND A.ASSEMBLY_ITEM_ID = D.INVENTORY_ITEM_ID
JOIN BOM_DEPARTMENTS F
ON F.DEPARTMENT_ID = B.DEPARTMENT_ID
AND F.ORGANIZATION_ID = A.ORGANIZATION_ID
WHERE A.ORGANIZATION_ID = T_ORGANIZATION_ID
AND C.OPERATION_CODE = T_OLD_OP
AND (B.DISABLE_DATE IS NULL OR
B.DISABLE_DATE >= SYSDATE);
BEGIN
--Find the new operation id
SELECT BSO.STANDARD_OPERATION_ID
INTO T_NEW_OP_ID
FROM BOM_STANDARD_OPERATIONS BSO
WHERE BSO.ORGANIZATION_ID = T_ORGANIZATION_ID
AND BSO.OPERATION_CODE = T_NEW_OP;
FOR C1 IN C_LIST LOOP
--STEP 1. Disable old operation
BEGIN
INSERT INTO BOM_OP_SEQUENCES_INTERFACE
(ASSEMBLY_ITEM_ID,
ORGANIZATION_ID,
ROUTING_SEQUENCE_ID,
OPERATION_SEQUENCE_ID,
DISABLE_DATE,
TRANSACTION_TYPE,
PROCESS_FLAG,
BATCH_ID)
VALUES(C1.INVENTORY_ITEM_ID,
C1.ORGANIZATION_ID,
C1.ROUTING_SEQUENCE_ID,
C1.OPERATION_SEQUENCE_ID,
T_DISABLE_DATE,
'Update',
1,
1);
END;
--STEP 2. Add new operation
BEGIN
INSERT INTO BOM_OP_SEQUENCES_INTERFACE(
ASSEMBLY_ITEM_ID,
ORGANIZATION_ID,
ROUTING_SEQUENCE_ID,
OPERATION_SEQUENCE_ID,
OPERATION_SEQ_NUM,
EFFECTIVITY_DATE,
STANDARD_OPERATION_ID,
REFERENCE_FLAG,
TRANSACTION_TYPE,
PROCESS_FLAG,
BATCH_ID)
VALUES(C1.INVENTORY_ITEM_ID,
C1.ORGANIZATION_ID,
C1.ROUTING_SEQUENCE_ID,
C1.OPERATION_SEQUENCE_ID,
C1.OPERATION_SEQ_NUM,
T_EFFECT_DATE,
T_NEW_OP_ID,
1,
'Create',
1,
2);
END;
END LOOP;
END;
Step 3. Run BOM & Routing Import (Bill and Routing Interface) ==> batch id is set to 1 (For disable old operation code)
Step 4. Run BOM & Routing Import (Bill and Routing Interface) ==> batch id is set to 2 (For add new operation code)
2013年1月29日 星期二
Oracle ERP R12. Receiving Transaction can not find the RT number .
Receipt number(RT No.) can not find the experience to share with you in this.
If you are sure that points to receipt also learned that the receipt number(RT No.), but acceptance and delivery can not be found the receipt number(RT No.) in the Receiving Transaction Function. Please check the RCV_TRANSACTIONS_INTERFACE , and if so, then in the confirmation after such interface delete. After re-entering the Receiving Transaction should be able to find the receipt number (RT No.).
The Function of Receiving Transaction Receipt Number LOV will exclude the Interface are still part of the data, in order to ensure that the data would not have repeated transactions.
在這跟各位分享一下收料單找不到的經驗。
若你確定已進行點收也得知收料單,但是卻在Receiving Transaction該Function裡找不到該收料點進行驗收及入庫動作時。 請檢查RCV_TRANSACTIONS_INTERFACE 是否有該收料單Pending的資料, 若有則在確認後將該筆interface刪除。之後再進入Receiving Transaction就應該可以找到該收料點了。
因為Receiving Transaction該Function的Receipt Number LOV會排除掉Interface尚有資料的部分,以確保同一筆資料不會有重覆的交易。
2013年1月14日 星期一
Oracle ERP R12. Delivery is closed but SO Line status is 'Picked'.
今天User反應一狀況,其表示有一筆Delivery 已做Ship Confirm ,而且Delivery 的狀態也是呈現Closed ,但是訂單項次(SO Line)的Status卻仍停在Picked !
解法如下 (執行Interface Trip Stop - SRS):
OM Module => Shipping => Interface => Run => Interface Trip Stop - SRS
解法如下 (執行Interface Trip Stop - SRS):
OM Module => Shipping => Interface => Run => Interface Trip Stop - SRS
訂閱:
文章 (Atom)