2013年2月7日 星期四

Oracle DBA - ORA-01591 lock held by in-doubt distributed transaction

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;  

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)

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