fantasy 的恬淡心情

環境：Oracle ERP r11.5.10.2

         Oracle DB 9.2.0.7

DECLARE
  V_USER      VARCHAR2(10) := 'TEST02'; --欲建立的帳號
  V_PWD       VARCHAR2(10) := '12345678'; --欲建立的帳號密碼
  V_RESP_NAME VARCHAR2(100) := 'Purchase'; --欲建立的Responsibility Name
  --
  V_USER_ID NUMBER;
  V_TEXT    VARCHAR2(100);
  V_RESP_ID NUMBER;
  V_APP_ID  NUMBER;
BEGIN
  V_TEXT := FND_WEB_SEC.CREATE_USER(P_USER    => V_USER
                                   ,P_PWD     => V_PWD
                                   ,P_USER_ID => V_USER_ID);

  DBMS_OUTPUT.PUT_LINE(V_TEXT || ' ' || TO_CHAR(V_USER_ID));

  IF V_TEXT = 'Y' THEN
    BEGIN
      SELECT R.RESPONSIBILITY_ID, R.APPLICATION_ID
        INTO V_RESP_ID, V_APP_ID
        FROM FND_RESPONSIBILITY R, FND_RESPONSIBILITY_TL T
       WHERE R.RESPONSIBILITY_ID = T.RESPONSIBILITY_ID
         AND R.APPLICATION_ID = T.APPLICATION_ID
         AND T.LANGUAGE = 'US'
         AND T.RESPONSIBILITY_NAME = V_RESP_NAME;
    
      FND_USER_RESP_GROUPS_API.INSERT_ASSIGNMENT(USER_ID                       => V_USER_ID
                                                ,RESPONSIBILITY_ID             => V_RESP_ID
                                                ,RESPONSIBILITY_APPLICATION_ID => V_APP_ID
                                                ,SECURITY_GROUP_ID             => 0
                                                ,START_DATE                    => TRUNC(SYSDATE)
                                                ,END_DATE                      => NULL
                                                ,DESCRIPTION                   => V_RESP_NAME);
    
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT('Create Responsibility has errors!');
    END;
  END IF;
END;