End Date Responsibility from Oracle for List of Users

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72

SET SERVEROUTPUT ON; 
-- -----------------------------------------------------------------
-- End Date Responsibility from Oracle FND User
-- -----------------------------------------------------------------

DECLARE
--
    v_user_name             VARCHAR2(240);
    v_responsibility_name   VARCHAR2(240);
    l_user_name             VARCHAR2(240);
    l_resp_name             VARCHAR2(240);
    v_application_name      VARCHAR2(100) := NULL;
    v_responsibility_key    VARCHAR2(100) := NULL;
    v_security_group        VARCHAR2(100) := NULL;
--
    CURSOR cur_user_resp IS
        SELECT
            *
        FROM
            xxcb_user_resp;

--

BEGIN
    FOR i IN cur_user_resp LOOP
        v_user_name := i.user_name;
        v_responsibility_name := i.resp_name;
        v_application_name := NULL;
        v_responsibility_key := NULL;
        v_security_group := NULL;
    --
        SELECT
            fa.application_short_name,
            fr.responsibility_key,
            frg.security_group_key
        INTO
            v_application_name,v_responsibility_key,v_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 = v_responsibility_name;
--

        fnd_user_pkg.delresp(
            username         => v_user_name,
            resp_app         => v_application_name,
            resp_key         => v_responsibility_key,
            security_group   => v_security_group
        );
--

        COMMIT;
        dbms_output.put_line('Responsiblity '
         || v_responsibility_name
         || ' is removed from the user '
         || v_user_name
         || ' Successfully');

    END LOOP; -- i invoices
       --
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Error encountered while deleting responsibilty from the user and the error is ' || sqlerrm);
END;
/