PL/SQL Script/Program: End Date Multiple Responsibilities

 

Business Need:

Reduce the number of EBS Responsibilities (hats) for a given user.

With Minimum Tweaking, you may achieve additional features:

• Stop a single responsibility from entire business use
• Stop people with a language from accessing one more responsibilities using EBS Standard Multi Language feature
• User has 100 responsibilities in Production. Cloning this to a development environment requires only one Operating Unit and its responsibilities

Standard End Dating of FND User Responsibilities used to work in older versions (Eg: at least 12.1.3). Now responsibility is moved to Role/Permission tables and need API calls to control this.

 

Benefits:

Ease of use and less memory usage

Program / PL/SQL Script:

SET SERVEROUTPUT ON;
 
DECLARE
       CURSOR c1
       IS
    SELECT fu.user_name,
        fa.application_short_name,
        frt.responsibility_name,
        fr.responsibility_key,
        fsg.security_group_key
    FROM fnd_user_resp_groups_all ful,
        fnd_user fu,
        fnd_responsibility_tl frt,
        fnd_responsibility fr,
        fnd_security_groups fsg,
        fnd_application fa
    WHERE fu.user_id = ful.user_id
    AND frt.responsibility_id = ful.responsibility_id
    AND fr.responsibility_id = frt.responsibility_id
    AND fsg.security_group_id = ful.security_group_id
    AND fa.application_id = ful.responsibility_application_id
    AND frt.language = 'US'
    AND fu.user_name IN ('<user-name>')        -- change user name appropriately
                           -- remove user name, if this is applicable to all users
    AND frt.responsibility_name like 'XX%';    -- change resp name appropriately
 
BEGIN
 
       FOR i IN c1
       LOOP
             BEGIN
                    fnd_user_pkg.delresp (username => i.user_name,
                           resp_app => i.application_short_name,
                           resp_key => i.responsibility_key,
                           security_group => i.security_group_key);
                    COMMIT;
                    DBMS_OUTPUT.put_line ('User: '||i.user_name||'Resp: '||i.responsibility_name || ' has been End Dated Successfully !!!');
             EXCEPTION
                    WHEN OTHERS THEN
                           DBMS_OUTPUT.put_line (
                                 'Inner Exception: '
                                 || ' - '
                                 || i.responsibility_key
                                 || ' - '
                                 || SQLERRM);
             END;
       END LOOP;
 
EXCEPTION
WHEN OTHERS THEN
       DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
 
END;
/
 
COMMIT;
 

Keywords:

Oracle EBS, R12, R12.2.8, Oracle Applications

FND, AOL, Request Group, Responsibility, Security, Access Control, Hat