Oracle Script to Copy Responsibilities of one user account to another user account

fnd_user_pkg.addresp is an Oracle Seeded API to add responsibilities to a user account.

 Please find below the pl/sql block using fnd_user_pkg.addresp API

 /*******************************************************************************
 *PURPOSE: To copy responsibilities of one oracle user account to another oracle user account *
 *AUTHOR: Oracle Apps Pie                                                *
 *******************************************************************************/
--
--
DECLARE
  --
  resp_count NUMBER := 0;
  
  CURSOR src_user_resp_details
  IS
    SELECT DISTINCT fa.application_short_name,
      fr.responsibility_key                  ,
      fsg.security_group_key,
      furga.end_date
       FROM apps.fnd_application fa      ,
      apps.fnd_responsibility fr         ,
      apps.fnd_user fu                   ,
      apps.fnd_user_resp_groups_all furga,
      apps.fnd_security_groups fsg
      WHERE 1                               = 1
    AND fu.user_name                        = 'JESSICA_BASA'
    AND fu.user_id                          = furga.user_id
    AND fa.application_id                   = fr.application_id
    AND furga.responsibility_id             = fr.responsibility_id
    AND furga.responsibility_application_id = fa.application_id
    AND fsg.security_group_id               = furga.security_group_id
    AND furga.end_date IS NULL;
  
  
BEGIN
  FOR user_resp_details_rec IN src_user_resp_details
  LOOP
    BEGIN
      --
      fnd_user_pkg.addresp
                 (username            => '&USER_NAME_2',
                  resp_app            => user_resp_details_rec.application_short_name,
                  resp_key            => user_resp_details_rec.responsibility_key,
                  security_group      => user_resp_details_rec.security_group_key,
                  description         => NULL,
                  start_date          => SYSDATE,
                  end_date            => NULL
                 );
      --
      resp_count := resp_count + 1;
      --
    EXCEPTION
    WHEN OTHERS THEN
      --
      DBMS_OUTPUT.put_line ( 'Error while Adding Responsibility: ' || SQLERRM );
      DBMS_OUTPUT.put_line ( 'resp_app: ' || user_resp_details_rec.application_short_name );
      DBMS_OUTPUT.put_line ( 'resp_key: ' || user_resp_details_rec.responsibility_key );
       
    END;
  END LOOP;
  
  DBMS_OUTPUT.put_line (resp_count || ' Responsibilities Successfully Copied!!' );

   COMMIT;

END;

NOTE: Please confirm user names using below query before using into API,
select * from fnd_user where user_name = &p_user_name

Comments

Post a Comment

Popular posts from this blog

While creating expense report facing the following error java.lang.IllegalArgumentException : peerExceptioms list should only contain OAException elements.

Problem:  While creating expense report in Internet expense module facing the following error java.lang.IllegalArgumentException : peerExceptions list should only contain OAException elements. Cause: "Default expense report" value is empty. Solution: "Default expense report" must have the appropiate value and it can be seen through HRMS responsibility. Navigation:  HRMS responsibility à People à Enter and Maintain and query the employees using employee’s full name à Assignment à  Purchase Order Information  There will be default expense account field SQL Query to get default expense account(Please enter the user name at the place of :P_USER_NAME) SELECT CODE_COMBINATION_ID, SEGMENT1||'-'||SEGMENT2||'-'||SEGMENT3||'-'||SEGMENT4||'-'||SEGMENT5||'-'||SEGMENT6||'-'||SEGMENT7  Default_Expense_Account FROM GL_CODE_COMBINATIONS WHERE CODE_COMBINATION_ID IN (SELECT DEFAULT_CODE_COMB_ID FROM PER_ALL_ASSIGNM
Read more

IExpense error : The specified approver is not a user of the application.

Image
Problem statement:   While creating the iexpense report user faces a issue that "The specified approver is not a user of the application. Please enter another approver. or select one from the list of values. Diagnosis: select * from per_all_people_f where full_name = &SupervisorName; Take the person_id and apply as an input in the below query select * from fnd_user where empolyee_id = &person_id; The result of the query will be no data found Soultion: Create the oracle/FND user for the supervisor. Navigation:  System administrator --> Define user check the below query, if it gives result then the issue has been resolved. Now you can try to re-create the expense report and you will be able to create the expense report this time select * from fnd_user where empolyee_id = &person_id;
Read more