EBS 11i error:selected records could not be locked

使用者在執行 Shipping Transactions 時產生錯誤

Error: This action cannot be performed on all seleceted records.
Error :The action can not be performed because the selected records could not be locked.
Table:WSH_DELIVERY_DETAILS

從Goolge去搜尋,似乎是一個bug,同事是說Item_Description這個欄位的最後面不可以有空白(可能是特殊字元)。

而查詢的結果,的確在item_description的最後面有空白,刪除後就ok了。

而網路上有人分享一個Script來檢查欄位。如下:

--Run the script loc_col.sql to check for trailing spaces within any of the item description.
--e.g.  WSH_DELIVERY_DETAILS 的 delivery_detail_id 是 104553
--將 &DEL_DETAIL_ID 設定為 104553

DECLARE
    CURSOR Cur1 is
         Select column_name  from fnd_columns
             Where table_id = ( Select table_id from fnd_tables
                                          Where table_name = 'WSH_DELIVERY_DETAILS' );

    check NUMBER(10) := 0;
    detail_id WSH_DELIVERY_DETAILS.Delivery_Detail_Id %TYPE;

BEGIN
    dbms_output.put_line('Script to identify the problematic fields :');
    dbms_output.put_line('============================================');

    FOR i IN Cur1 LOOP

        detail_id := &DEL_DETAIL_ID; --發生錯誤的delivery_detail_id
        SELECT COUNT(*)  into check FROM WSH_DELIVERY_DETAILS
            WHERE NVL(LENGTH(LTRIM(RTRIM( i.column_name ))), 0) <> LENGTH( i.column_name )
              AND Delivery_Detail_Id = detail_id;
 
        IF ( check > 0 ) THEN --發現欄位有多餘的字元
            dbms_output.put_line('Field:'||i.COLUMN_NAME ||
                    'for delivery detail '|| detail_id || ' has leading or trailing spaces');

        END IF;

    END LOOP;

    dbms_output.put_line('Script completed succefully.......');

END;

Oracle DB的listener.log清理