Required Permissions

Account aggregation and Group aggregation

To assign the correct permissions, complete the following:

1. Using SQL*Plus, sign in to the Oracle database as APPS and run the following commands to find the rights present on the package, which can be either Invoker or Definer:

   Copy

   SELECT dbo.object_name,
   (DECODE(SIGN(bitand(options,16)),1,'INVOKER','DEFINER')) "authid" 
   FROM dba_objects dbo, sys.PROCEDURE$ p 
   WHERE p.obj# = dbo.object_id 
   AND dbo.object_type = 'PACKAGE' 
   AND dbo.object_name = 'xxx' 
   AND dbo.owner = 'APPS' 

   where xxx is HR_PERSON_API/ HR_PHONE_API/ FND_GLOBAL/ FND_PROFILE/ HR_ASSIGNMENT_API/ HR_EMPLOYEE_API / HR_EX_EMPLOYEE_API.

   For example, enter the following command to find the rights present on the HR_PERSON_API:

   Copy

   SELECT dbo.object_name, 
   (DECODE(SIGN(bitand(options,16)),1,'INVOKER','DEFINER')) "authid" 
   FROM dba_objects dbo, sys.PROCEDURE$ p 
   WHERE p.obj# = dbo.object_id 
   AND dbo.object_type = 'PACKAGE' 
   AND dbo.object_name = 'HR_PERSON_API' 
   AND dbo.owner = 'APPS'; 

2. If the package has Invoker rights, perform the following:

   Copy the Package scripts from here.

   Using SQL*Plus, sign in to the Oracle database as APPS and run the following scripts:

   • Run the @SP_UPDATE_EMAIL_API.sql

   • Run the @SP_UPDATE_EMAIL_API_BODY.sql

   • Run the @SP_CREATE_OR_UPDATE_PHONE.sql

   • Run the @SP_CREATE_OR_UPDATE_PHONE_BODY.sql

   • Run the @SP_FND_GLOBAL.sql

   • Run the @SP_FND_GLOBAL_BODY.sql

   • Run the @SP_FND_PROFILE.sql

   • Run the @SP_FND_PROFILE_BODY.sql

   • Run the @SP_HR_ASSIGNMENT_API.sql

   • Run the @SP_HR_ASSIGNMENT_API_BODY.sql

   • Run the @SP_HR_EMPLOYEE_API.sql

   • Run the @SP_HR_EMPLOYEE_API_BODY.sql

   • Run the @SP_HR_EX_EMPLOYEE_API.sql

   • Run the @SP_HR_EX_EMPLOYEE_API_BODY.sql

3. Sign in to the Oracle database as the database administrator to create the new administrator user account using SQL*Plus as follows:

   Copy

   create role ${new role};
   create user ${new user} identified by ${password};
   grant create session to ${new user};
   grant create synonym to ${new user};
   grant ${new role} to ${new user};

   • Grant permissions to the new role created (${new role}):

     Copy

     GRANT SELECT ON HR.HR_ALL_ORGANIZATION_UNITS_TL TO ${new role};
     GRANT SELECT ON HR.HR_ALL_POSITIONS_F_TL TO ${new role};
     GRANT SELECT ON HR.PER_ALL_PEOPLE_F TO ${new role};
     GRANT SELECT ON HR.PER_ALL_ASSIGNMENTS_F TO ${new role};
     GRANT SELECT ON HR.PER_ASSIGNMENT_STATUS_TYPES_TL TO ${new role};
     GRANT SELECT ON HR.PER_PERSON_TYPES_TL TO ${new role};
     GRANT SELECT ON HR.PER_JOB_GROUPS TO ${new role};
     GRANT SELECT ON HR.PER_JOBS TO ${new role};
     GRANT SELECT ON HR.PER_PHONES TO ${new role};
     GRANT SELECT ON HR.HR_ALL_ORGANIZATION_UNITS TO ${new role};
     GRANT SELECT ON HR.HR_ALL_POSITIONS_F_TL TO ${new role};
     GRANT SELECT ON HR.HR_ALL_POSITIONS_F TO ${new role};
     GRANT SELECT ON HR.PER_ASSIGNMENT_STATUS_TYPES TO ${new role};
     GRANT SELECT ON HR.PER_PERSON_TYPES TO ${new role};
     GRANT SELECT ON HR.PER_PERSON_TYPE_USAGES_F TO ${new role};
     GRANT SELECT ON APPS.FND_USER TO ${new role}; 

   • If the package has Definer rights, perform the following:

     Copy

     GRANT EXECUTE ON APPS.HR_PERSON_API TO ${new role};
     GRANT EXECUTE ON APPS.HR_PHONE_API TO ${new role};

   • If the package has Invoker rights, perform the following:

     Copy

     GRANT EXECUTE ON APPS.SP_UPDATE_EMAIL_API TO ${new role};
     GRANT EXECUTE ON APPS.SP_CREATE_OR_UPDATE_PHONE TO ${new role};
     GRANT SELECT ON APPS.HR_ALL_POSITIONS_F_TL TO ${new role};
     GRANT SELECT ON APPS.HR_ALL_ORGANIZATION_UNITS_TL TO ${new role};

4. Sign in using new user name ${new user} and create the following synonym:

   Copy

   CREATE OR REPLACE SYNONYM HR_ALL_ORGANIZATION_UNITS_TL FOR HR.HR_ALL_ORGANIZATION_UNITS_TL;
   CREATE OR REPLACE SYNONYM HR_ALL_POSITIONS_F_TL FOR HR.HR_ALL_POSITIONS_F_TL;
   CREATE OR REPLACE SYNONYM PER_ALL_ASSIGNMENTS_F FOR HR.PER_ALL_ASSIGNMENTS_F;
   CREATE OR REPLACE SYNONYM PER_ALL_PEOPLE_F FOR HR.PER_ALL_PEOPLE_F;
   CREATE OR REPLACE SYNONYM PER_ASSIGNMENT_STATUS_TYPES_TL FOR HR.PER_ASSIGNMENT_STATUS_TYPES_TL;
   CREATE OR REPLACE SYNONYM PER_JOBS FOR HR.PER_JOBS;
   CREATE OR REPLACE SYNONYM PER_JOB_GROUPS FOR HR.PER_JOB_GROUPS;
   CREATE OR REPLACE SYNONYM PER_PERSON_TYPES_TL FOR HR.PER_PERSON_TYPES_TL;
   CREATE OR REPLACE SYNONYM PER_PHONES FOR HR.PER_PHONES;
   CREATE OR REPLACE SYNONYM HR_ALL_ORGANIZATION_UNITS FOR HR.HR_ALL_ORGANIZATION_UNITS;
   CREATE OR REPLACE SYNONYM PER_JOBS FOR HR.PER_JOBS;
   CREATE OR REPLACE SYNONYM HR_ALL_POSITIONS_F FOR HR.HR_ALL_POSITIONS_F;
   CREATE OR REPLACE SYNONYM PER_PERSON_TYPES FOR HR.PER_PERSON_TYPES;
   CREATE OR REPLACE SYNONYM PER_ASSIGNMENT_STATUS_TYPES FOR HR.PER_ASSIGNMENT_STATUS_TYPES;
   CREATE OR REPLACE SYNONYM FND_USER FOR APPS.FND_USER;
   CREATE OR REPLACE SYNONYM PER_PERSON_TYPE_USAGES_F FOR HR.PER_PERSON_TYPE_USAGES_F;
   CREATE OR REPLACE SYNONYM PER_ALL_ASSIGNMENTS_F FOR HR.PER_ALL_ASSIGNMENTS_F;
   CREATE OR REPLACE SYNONYM PER_JOBS FOR HR.PER_JOBS;

• If the package has Definer rights, perform the following:

  Copy

  CREATE OR REPLACE SYNONYM HR_PERSON_API FOR APPS.HR_PERSON_API;
  CREATE OR REPLACE SYNONYM HR_PHONE_API FOR APPS.HR_PHONE_API;

• If the package has Invoker rights, perform the following:

  Copy

  CREATE OR REPLACE SYNONYM HR_PERSON_API for APPS.SP_UPDATE_EMAIL_API;
  CREATE OR REPLACE SYNONYM HR_PHONE_API for APPS.SP_CREATE_OR_UPDATE_PHONE;

Create Employee

1. For create employee, grant the following permissions to the new role (${new role}). Sign into the Oracle database as the database administrator to create the new administrator user account using SQL*Plus as follows:

   Copy

   GRANT SELECT ON APPS.FND_USER TO ${new role};
   GRANT SELECT ON APPS.FND_RESPONSIBILITY_VL TO ${new role};
   GRANT SELECT ON APPS.FND_APPLICATION_VL TO ${new role};
   GRANT SELECT ON HR.PER_JOBS TO ${new role}; 

   • If the package has Definer rights:

     Copy

     GRANT EXECUTE ON APPS.FND_GLOBAL TO ${new role};
     GRANT EXECUTE ON APPS.HR_EMPLOYEE_API TO ${new role};
     GRANT EXECUTE ON APPS.FND_PROFILE TO ${new role};
     GRANT EXECUTE ON APPS.HR_ASSIGNMENT_API TO ${new role}; 

   • If the package has invoker rights:

     Copy

     GRANT EXECUTE ON APPS.SP_FND_GLOBAL TO ${new role};
     GRANT EXECUTE ON APPS.SP_HR_EMPLOYEE_API TO ${new role};
     GRANT EXECUTE ON APPS.SP_FND_PROFILE TO ${new role};
     GRANT EXECUTE ON APPS.SP_HR_ASSIGNMENT_API TO ${new role}; 

2. Login by the new user name ${new user} and create the following synonym for create employee:

   Copy

   CREATE OR REPLACE SYNONYM PER_JOBS FOR HR.PER_JOBS;
   CREATE OR REPLACE SYNONYM FND_USER FOR APPS.FND_USER;
   CREATE OR REPLACE SYNONYM FND_APPLICATION_VL FOR APPS.FND_APPLICATION_VL;
   CREATE OR REPLACE SYNONYM FND_RESPONSIBILITY_VL FOR APPS.FND_RESPONSIBILITY_VL; 

   • If the package has Definer rights:

     Copy

     CREATE OR REPLACE SYNONYM FND_GLOBAL FOR APPS.FND_GLOBAL;
     CREATE OR REPLACE SYNONYM HR_EMPLOYEE_API FOR APPS.HR_EMPLOYEE_API;
     CREATE OR REPLACE SYNONYM FND_PROFILE FOR APPS.FND_PROFILE;
     CREATE OR REPLACE SYNONYM HR_ASSIGNMENT_API FOR APPS.HR_ASSIGNMENT_API;

   • If the package has invoker rights:

     Copy

     CREATE OR REPLACE SYNONYM FND_GLOBAL FOR APPS.SP_FND_GLOBAL;
     CREATE OR REPLACE SYNONYM HR_EMPLOYEE_API FOR APPS.SP_HR_EMPLOYEE_API;
     CREATE OR REPLACE SYNONYM FND_PROFILE FOR APPS.SP_FND_PROFILE;
     CREATE OR REPLACE SYNONYM HR_ASSIGNMENT_API FOR APPS.SP_HR_ASSIGNMENT_API; 

Disable Employee

1. For disable employee, grant the following permissions to the new role (${new role}). Sign in to the Oracle database as the database administrator to createthe new administrator user account using SQL*Plus as follows:

   Copy

   GRANT SELECT ON APPS.FND_USER TO ${new role};
   GRANT SELECT ON APPS.FND_RESPONSIBILITY_VL TO ${new role};
   GRANT SELECT ON APPS.FND_APPLICATION_VL TO ${new role};
   GRANT SELECT ON APPS.PER_PERSON_TYPES TO ${new role};
   GRANT SELECT ON HR.PER_PERIODS_OF_SERVICE TO ${new role}; 

   • If the package has Definer rights:

     Copy

     GRANT EXECUTE ON APPS.FND_GLOBAL TO ${new role};
     GRANT EXECUTE ON APPS.HR_EX_EMPLOYEE_API TO ${new role}; 

   • If the package has invoker rights:

     Copy

     GRANT EXECUTE ON APPS.SP_FND_GLOBAL TO ${new role};
     GRANT EXECUTE ON APPS.SP_HR_EX_EMPLOYEE_API TO ${new role};

2. Login by the new user name ${new user} and create the following synonym for disable employee:

   Copy

   CREATE OR REPLACE SYNONYM FND_USER FOR APPS.FND_USER;    
   CREATE OR REPLACE SYNONYM FND_RESPONSIBILITY_VL FOR APPS.FND_RESPONSIBILITY_VL;    
   CREATE OR REPLACE SYNONYM FND_APPLICATION_VL FOR APPS.FND_APPLICATION_VL;    
   CREATE OR REPLACE SYNONYM PER_PERSON_TYPES FOR HR.PER_PERSON_TYPES;    
   CREATE OR REPLACE SYNONYM PER_PERIODS_OF_SERVICE FOR HR.PER_PERIODS_OF_SERVICE; 

   • If the package has Definer rights:

     Copy

     CREATE OR REPLACE SYNONYM FND_GLOBAL FOR APPS.FND_GLOBAL;
     CREATE OR REPLACE SYNONYM HR_EX_EMPLOYEE_API FOR APPS.HR_EX_EMPLOYEE_API; 

   • If the package has invoker rights:

     Copy

     CREATE OR REPLACE SYNONYM FND_GLOBAL FOR APPS.SP_FND_GLOBAL;
     CREATE OR REPLACE SYNONYM HR_EX_EMPLOYEE_API FOR APPS.SP_HR_EX_EMPLOYEE_API;