Oracle Apps Knowledge Sharing

CREATE OR REPLACE PROCEDURE xxsh_delete_resp_assignment 
                            (piv_user_name  IN VARCHAR2,
                             piv_resp_name  IN VARCHAR2,
                             pov_status     OUT VARCHAR2,
                             pov_error_dtls OUT VARCHAR2
                            )
AS
   lv_application_name     VARCHAR2 (100) := NULL;
   lv_responsibility_key   VARCHAR2 (100) := NULL;
   lv_security_group       VARCHAR2 (100) := NULL;  
   ld_dummy_date           DATE;
   ln_count                NUMBER;
   le_stop_program         EXCEPTION;
BEGIN
   
   -- Whether user exists 
   BEGIN 
     SELECT end_date
       INTO ld_dummy_date
       FROM fnd_user 
      WHERE user_name = piv_user_name;
      
     IF ld_dummy_date < SYSDATE 
     THEN 
       pov_status     := 'ERROR';
       pov_error_dtls := 'Username already end dated';
       RAISE le_stop_program;
     END IF;
   EXCEPTION 
     WHEN NO_DATA_FOUND THEN 
       pov_status     := 'ERROR';
       pov_error_dtls := 'Username not found';
       RAISE le_stop_program;
   END;
   
   -- Whether responsibility iexists or not s already assigned to user  
   BEGIN 
     SELECT fa.application_short_name,
            fr.responsibility_key,
            frg.security_group_key
       INTO lv_application_name,
            lv_responsibility_key,
            lv_security_group
       FROM fnd_responsibility fr,
            fnd_application fa,
            fnd_security_groups frg,
            fnd_responsibility_tl frt
      WHERE fr.application_id = fa.application_id
        AND fr.data_group_id = frg.security_group_id
        AND fr.responsibility_id = frt.responsibility_id
        AND frt.LANGUAGE = USERENV ('LANG')
        AND frt.responsibility_name = piv_resp_name;
   EXCEPTION 
     WHEN NO_DATA_FOUND THEN 
       pov_status     := 'ERROR';
       pov_error_dtls := 'REsponsibility does not exists';
       RAISE le_stop_program; 
   END;
   
   -- Whether responsibility iexists or not s already assigned to user  
   BEGIN 
     SELECT a.end_date
       INTO ld_dummy_date
       FROM fnd_user_resp_groups_direct a,
            fnd_user b,
            fnd_responsibility_tl c
      WHERE a.user_id = b.user_id
        AND a.responsibility_id = c.responsibility_id 
        AND c.language = USERENV('LANG') 
        AND user_name = piv_user_name
        AND responsibility_name = piv_resp_name;
        
     IF ld_dummy_date < SYSDATE 
     THEN 
       pov_status     := 'ERROR';
       pov_error_dtls := 'Responsibility Assignment Already ended';
       RAISE le_stop_program;
     END IF;
     
   EXCEPTION 
     WHEN NO_DATA_FOUND THEN 
       pov_status     := 'ERROR';
       pov_error_dtls := 'REsponsibility is not assigned to User';
       RAISE le_stop_program; 
   END;     
   
   -- Call the API to end date responsibility assignment
   FND_USER_PKG.DELRESP (username       => piv_user_name,
                         resp_app       => lv_application_name,
                         resp_key       => lv_responsibility_key,
                         security_group => lv_security_group
                        );
   COMMIT;

   DBMS_OUTPUT.put_line (   'Responsiblity '
                         || piv_resp_name
                         || ' is removed from the user '
                         || piv_user_name
                         || ' Successfully'
                        );
EXCEPTION
   WHEN le_stop_program THEN 
      DBMS_OUTPUT.put_line ('Custom Error: '||pov_error_dtls);
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line ('Unhandled Error: '||SQLERRM);
END xxsh_delete_resp_assignment;
/
SHOW ERRORS;
Execution Script: 
SET SERVEROUTPUT ON;
DECLARE 
  lv_status        VARCHAR2(200);
  lv_error_message VARCHAR2(2000);
BEGIN 
  xxsh_delete_resp_assignment('SYSADMIN',
                              'CRM ETF Administration',
                              lv_status,
                              lv_error_message
                             );
END;