Oracle ERP R12. 如何使用API (FND_USER_PKG)建立帳號. How to use FND_USER_PKG.CREATEUSER ?

以下是使用FND_USER_PKG.CREATEUSER 建立使用者的範例程式碼

Sample code :
FND_USER_PKG.CREATEUSER(X_USER_NAME            => :USER_NAME , --USER ACCOUNT
                                                        X_OWNER                     => 'SEED',
                                                        X_UNENCRYPTED_PASSWORD => 'ABC234',  --Default password
                                                        X_EMPLOYEE_ID          => :EMPLOYEE_ID, --Mapping to employee id
                                                        X_EMAIL_ADDRESS        => :EMAIL_ADDRESS); --user email

PS :
X_UNENCRYPTED_PASSWORD 需注意是否有啟用密碼的複雜度檢查，若有則必需符合其複雜度檢查規定

Oracle ERP R12. - 如何快速複製使用者權限 , How to grant privileges from one user to another user ?

以下Sample是當有新人時,可以快速複制user a 的權限至user b的作法 ~

My Sample code : (Package)

/*PACKAGE HEADER*/

CREATE OR REPLACE PACKAGE XX_FND_USER_RESP_PKG
IS
  PROCEDURE XX_FND_COPY_USER_RESP(I_FROM_USER_ID   IN NUMBER,
                                   I_TO_USER_ID     IN NUMBER,
                                   I_MODE           IN NUMBER DEFAULT 0);
  --                                
  PROCEDURE COPY_USER_RESP(I_FROM_USER_ID   IN NUMBER,
                           I_TO_USER_ID     IN NUMBER);
  --
  PROCEDURE DISABLE_USER_RESP(I_FROM_USER_ID   IN NUMBER,
                              I_TO_USER_ID     IN NUMBER);
  --
  PROCEDURE ENABLE_USER_RESP(I_FROM_USER_ID   IN NUMBER,
                             I_TO_USER_ID     IN NUMBER);
  --
  PROCEDURE DISABLE_USER_ALL_RESP(I_USER_ID   IN NUMBER);
END XX_FND_USER_RESP_PKG;

/*PACKAGE BODY*/

CREATE OR REPLACE PACKAGE BODY XX_FND_USER_RESP_PKG
IS
  PROCEDURE XX_FND_COPY_USER_RESP(I_FROM_USER_ID   IN NUMBER,
                                   I_TO_USER_ID     IN NUMBER,
                                   I_MODE           IN NUMBER DEFAULT 0)
  IS
    /*
    When     Who       What
    ===============================
    20130313 WenShen   Created for 複製某人權限至另一人
 
    說明 :
    1. I_MODE : 權限複製規則
       0 : 只新增短少的權限,但不將即有卻失效的權限予以復權
       1 : 新增短少的權限,且將即有卻失效的權限予以復權(且不影其它即有的權限)
       2 : 完整複製,將原有權限全數失效後再執行I_MODE 2
    */
  BEGIN
    --
    IF I_MODE = 0 THEN  --只新增短少的權限,但不將即有卻失效的權限予以復權
      --新增短少的權限
      COPY_USER_RESP(I_FROM_USER_ID,
                     I_TO_USER_ID);
    ELSIF I_MODE = 1 THEN --新增短少的權限,且將即有卻失效的權限予以復權
      --Step 1. 新增短少的權限
      COPY_USER_RESP(I_FROM_USER_ID,
                     I_TO_USER_ID);
                   
      --Step 2. 依來源USER權限設定將目標USER的權限的予以復權
      ENABLE_USER_RESP(I_FROM_USER_ID,
                       I_TO_USER_ID);
    ELSIF I_MODE = 2 THEN --完整複製,將原有權限全數失效後再執行I_MODE 2
      --Step 1. 將所有權限予以停權
      DISABLE_USER_ALL_RESP(I_TO_USER_ID);
   
      --Step 2. 新增短少的權限
      COPY_USER_RESP(I_FROM_USER_ID,
                     I_TO_USER_ID);
                   
      --Step 3. 依來源USER權限設定將目標USER的權限的予以復權
      ENABLE_USER_RESP(I_FROM_USER_ID,
                       I_TO_USER_ID);
    ELSE
      NULL;
    END IF;
  END;
  --
  PROCEDURE COPY_USER_RESP(I_FROM_USER_ID   IN NUMBER,
                           I_TO_USER_ID     IN NUMBER)
  IS
    T_AP_ID    NUMBER;
    T_RESP_ID NUMBER;
    --來源者權限表
    CURSOR C_F_RESP
    IS
    SELECT FR.RESPONSIBILITY_KEY
      FROM FND_USER_RESP_GROUPS_DIRECT FUR
      JOIN FND_RESPONSIBILITY FR
        ON FR.RESPONSIBILITY_ID = FUR.RESPONSIBILITY_ID
       AND (FR.END_DATE IS NULL OR FR.END_DATE >= SYSDATE)
     WHERE (FUR.END_DATE IS NULL OR FUR.END_DATE >= SYSDATE)
       AND NOT EXISTS (SELECT 'X'
                         FROM FND_USER_RESP_GROUPS_DIRECT FUR2
                        WHERE FUR2.USER_ID = I_TO_USER_ID
                          AND FUR2.RESPONSIBILITY_ID = FUR.RESPONSIBILITY_ID) --已存在不重覆轉
       AND FUR.USER_ID = I_FROM_USER_ID; --來源對象
  BEGIN
    FOR C1 IN C_F_RESP LOOP
      --取得RESP資訊
      SELECT FR.RESPONSIBILITY_ID,
             FR.APPLICATION_ID
        INTO T_RESP_ID,
             T_AP_ID
        FROM FND_RESPONSIBILITY FR
       WHERE FR.RESPONSIBILITY_KEY = C1.RESPONSIBILITY_KEY;
     
      --CALL API
      BEGIN
        FND_USER_RESP_GROUPS_API.INSERT_ASSIGNMENT(USER_ID                       => I_TO_USER_ID,
                                                   RESPONSIBILITY_ID             => T_RESP_ID,
                                                   RESPONSIBILITY_APPLICATION_ID => T_AP_ID,
                                                   SECURITY_GROUP_ID             => 0,
                                                   START_DATE                    => TRUNC(SYSDATE),
                                                   END_DATE                      => NULL,
                                                   DESCRIPTION                   => NULL);
        COMMIT;                                                
      EXCEPTION WHEN OTHERS THEN
        NULL;
      END;
    END LOOP;
  END;
  --
  PROCEDURE DISABLE_USER_RESP(I_FROM_USER_ID   IN NUMBER,
                              I_TO_USER_ID     IN NUMBER)
  IS
    T_AP_ID    NUMBER;
    T_RESP_ID  NUMBER;
    T_END_DATE DATE;
    --來源者權限表
    CURSOR C_F_RESP
    IS
    SELECT FR.RESPONSIBILITY_KEY,FUR2.START_DATE
      FROM FND_USER_RESP_GROUPS_DIRECT FUR
      JOIN FND_RESPONSIBILITY FR
        ON FR.RESPONSIBILITY_ID = FUR.RESPONSIBILITY_ID
      JOIN FND_USER_RESP_GROUPS_DIRECT FUR2
        ON FUR2.RESPONSIBILITY_ID = FUR.RESPONSIBILITY_ID
       AND FUR2.USER_ID = I_TO_USER_ID
     WHERE FUR.END_DATE < SYSDATE --已失效的部分
       AND FUR.USER_ID = I_FROM_USER_ID; --來源對象
  BEGIN
    FOR C1 IN C_F_RESP LOOP
      --取得RESP資訊
      SELECT FR.RESPONSIBILITY_ID,
             FR.APPLICATION_ID
        INTO T_RESP_ID,
             T_AP_ID
        FROM FND_RESPONSIBILITY FR
       WHERE FR.RESPONSIBILITY_KEY = C1.RESPONSIBILITY_KEY;
   
      --
      IF C1.START_DATE IS NOT NULL THEN
        IF C1.START_DATE > SYSDATE THEN
          T_END_DATE := C1.START_DATE;
        ELSE
          T_END_DATE := SYSDATE;
        END IF;    
      END IF;
   
      --
      BEGIN
        FND_USER_RESP_GROUPS_API.UPDATE_ASSIGNMENT(USER_ID                       => I_TO_USER_ID,
                                                   RESPONSIBILITY_ID             => T_RESP_ID,
                                                   RESPONSIBILITY_APPLICATION_ID => T_AP_ID,
                                                   SECURITY_GROUP_ID             => 0,
                                                   START_DATE                    => C1.START_DATE,
                                                   END_DATE                      => T_END_DATE,
                                                   DESCRIPTION                   => NULL);  
      EXCEPTION WHEN OTHERS THEN
        NULL;
      END;
    END LOOP;
  END;
  --
  PROCEDURE ENABLE_USER_RESP(I_FROM_USER_ID   IN NUMBER,
                             I_TO_USER_ID     IN NUMBER)
  IS
    T_AP_ID    NUMBER;
    T_RESP_ID NUMBER;
    --來源者權限表
    CURSOR C_F_RESP
    IS
    SELECT FR.RESPONSIBILITY_KEY,FUR2.START_DATE
      FROM FND_USER_RESP_GROUPS_DIRECT FUR
      JOIN FND_RESPONSIBILITY FR
        ON FR.RESPONSIBILITY_ID = FUR.RESPONSIBILITY_ID
      JOIN FND_USER_RESP_GROUPS_DIRECT FUR2
        ON FUR2.RESPONSIBILITY_ID = FUR.RESPONSIBILITY_ID
       AND FUR2.USER_ID = I_TO_USER_ID
     WHERE (FUR.END_DATE IS NULL OR FUR.END_DATE >= SYSDATE)
       AND FUR.USER_ID = I_FROM_USER_ID; --來源對象
  BEGIN
    FOR C1 IN C_F_RESP LOOP
      --取得RESP資訊
      SELECT FR.RESPONSIBILITY_ID,
             FR.APPLICATION_ID
        INTO T_RESP_ID,
             T_AP_ID
        FROM FND_RESPONSIBILITY FR
       WHERE FR.RESPONSIBILITY_KEY = C1.RESPONSIBILITY_KEY;
   
      --
      BEGIN
        FND_USER_RESP_GROUPS_API.UPDATE_ASSIGNMENT(USER_ID                       => I_TO_USER_ID,
                                                   RESPONSIBILITY_ID             => T_RESP_ID,
                                                   RESPONSIBILITY_APPLICATION_ID => T_AP_ID,
                                                   SECURITY_GROUP_ID             => 0,
                                                   START_DATE                    => C1.START_DATE,
                                                   END_DATE                      => NULL,
                                                   DESCRIPTION                   => NULL);  
      EXCEPTION WHEN OTHERS THEN
        NULL;
      END;
    END LOOP;
  END;
  --
  PROCEDURE DISABLE_USER_ALL_RESP(I_USER_ID   IN NUMBER)
  IS
    T_AP_ID    NUMBER;
    T_RESP_ID  NUMBER;
    T_END_DATE DATE;
    --來源者權限表
    CURSOR C_F_RESP
    IS
    SELECT FR.RESPONSIBILITY_KEY,FUR.START_DATE
      FROM FND_USER_RESP_GROUPS_DIRECT FUR
      JOIN FND_RESPONSIBILITY FR
        ON FR.RESPONSIBILITY_ID = FUR.RESPONSIBILITY_ID
     WHERE (FUR.END_DATE IS NULL OR FUR.END_DATE >= SYSDATE)
       AND FUR.USER_ID = I_USER_ID; --來源對象
  BEGIN
    FOR C1 IN C_F_RESP LOOP
      --取得RESP資訊
      SELECT FR.RESPONSIBILITY_ID,
             FR.APPLICATION_ID
        INTO T_RESP_ID,
             T_AP_ID
        FROM FND_RESPONSIBILITY FR
       WHERE FR.RESPONSIBILITY_KEY = C1.RESPONSIBILITY_KEY;
   
      --
      IF C1.START_DATE IS NOT NULL THEN
        IF C1.START_DATE > SYSDATE THEN
          T_END_DATE := C1.START_DATE;
        ELSE
          T_END_DATE := SYSDATE;
        END IF;    
      END IF;
      --
      BEGIN
        FND_USER_RESP_GROUPS_API.UPDATE_ASSIGNMENT(USER_ID                       => I_USER_ID,
                                                   RESPONSIBILITY_ID             => T_RESP_ID,
                                                   RESPONSIBILITY_APPLICATION_ID => T_AP_ID,
                                                   SECURITY_GROUP_ID             => 0,
                                                   START_DATE                    => C1.START_DATE,
                                                   END_DATE                      => T_END_DATE,
                                                   DESCRIPTION                   => NULL);  
      EXCEPTION WHEN OTHERS THEN
        NULL;
      END;
    END LOOP;
  END;
END XX_FND_USER_RESP_PKG;

/*使用說明 :*/

begin

  XX_fnd_user_resp_pkg.hpx_fnd_copy_user_resp(i_from_user_id => :i_from_user_id, ç A使用者USER ID

                                            i_to_user_id => :i_to_user_id, ç B使用者USER ID

                                            i_mode => :i_mode); ç 複製規則,如下說明

end;

I_MODE : 權限複製規則

       0 : 只新增使用者B短少的權限,但不將即有卻失效的權限予以復權

       1 : 新增使用者B短少的權限,且將即有但失效的權限予以復權(且不影使用者B較使用者A多的權限)

       2 : 完整複製,將使用者B原有權限全數失效後再執行I_MODE 2

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;  

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的工單)

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)