Friday, February 15, 2013

Add responsibility through pl/sql

PL/SQL block to add responsibility

We have an API to do this : fnd_user_pkg.addresp

procedure AddResp(username       varchar2,
                  resp_app       varchar2,
                  resp_key       varchar2,
                  security_group varchar2,
                  description    varchar2,
                  start_date     date,
                  end_date       date)


--   Usage example in pl/sql
--   begin fnd_user_pkg.addresp('SCOTT', 'FND', 'APPLICATION_DEVELOPER',
--                              'STANDARD', 'DESCRIPTION', sysdate, null); end;
--   Input (Mandatory)
--   username:       User Name
--   resp_app:       Application Short Name
--   resp_key:       Responsibility Key
--   security_group: Security Group Key
--   description:    Description
--   start_date:     Start Date
--   end_date:       End Date


Take responsibility key value from

select responsibility_key from fnd_responsibility_vl
where responsibility_name = 'System Administrator' -- Query with your resp name to be added
;


Take resp_app from

select application_short_name from fnd_application
where application_short_name like 'SYS%';


CODE :

begin
fnd_user_pkg.addresp(

                        username       =>  v_user_name -- user_name from fnd_user table
                       ,resp_app       => 'SYSADMIN'
                        ,resp_key       => 'SYSTEM_ADMINISTRATOR'
                      ,security_group => 'STANDARD'
                        ,description    => 'Auto Assignment'
                         ,start_date     =>  SYSDATE
                        ,end_date       =>  SYSDATE + 365);
        COMMIT;       
        DBMS_OUTPUT.put_line ('Responsibility Added Successfully');
EXCEPTION
        WHEN OTHERS
        THEN
        DBMS_OUTPUT.put_line (  ' Responsibility is not added due to '|| SQLCODE ||SUBSTR (SQLERRM, 1, 100));
        ROLLBACK;
END;
/



Thanks!!