SQL - Assign Responsibility

--assign resp
--Find the RESPONSIBILITY_ID from RESPONSIBILITY_NAME:

select RESPONSIBILITY_ID,RESPONSIBILITY_NAME from FND_RESPONSIBILITY_VL where RESPONSIBILITY_NAME like 'Sys%Admin%';

--Verify whether the User is already having the responsibility assigned:
select USER_NAME,USER_ID from fnd_user where USER_NAME = 'JXGUPTA';
select USER_ID,RESPONSIBILITY_ID from FND_USER_RESP_GROUPS_DIRECT where USER_ID=52741 and RESPONSIBILITY_ID=20420;

--Assign the Responsibility Using the Following SQL:
;
DECLARE

  v_user_name  VARCHAR2(30) := 'JXGUPTA';
  Enter_Resp_Id  VARCHAR2(100) ;
  v_resp_appl  VARCHAR2(100);
  v_resp_key   VARCHAR2(100);
  v_appl_id    VARCHAR2(30);

BEGIN
select APPLICATION_ID ,RESPONSIBILITY_KEY
  into v_appl_id ,v_resp_key
  from FND_RESPONSIBILITY_VL
where RESPONSIBILITY_ID=20420 ;

select APPLICATION_SHORT_NAME
  into v_resp_appl
  from FND_APPLICATION_VL  
where upper(APPLICATION_ID) = v_appl_id;

  fnd_user_pkg.addresp(username       => v_user_name
                      ,resp_app       => v_resp_appl
                      ,resp_key       => v_resp_key
                      ,security_group => 'STANDARD'
                      ,description    => 'Auto Assignment'
                      ,start_date     => SYSDATE
                      ,end_date       => SYSDATE + 1000);
END;