Adding responsibility to the user using API

 DECLARE

lv_user_name VARCHAR2 (20) := 'ABC.PQR';

lv_req_resp_key VARCHAR2 (50) := 'SYS-ADMIN';

lv_description VARCHAR2 (100) := 'Adding Responsibility to user using script';

lv_req_resp_name VARCHAR2 (200);

lv_appl_shrt_name VARCHAR2 (20);

lv_appl_name VARCHAR2 (50);

lv_resp_key VARCHAR2 (50);

BEGIN

SELECT fav.application_short_name,

fav.application_name,

frv.responsibility_name

INTO lv_appl_shrt_name, lv_appl_name, lv_req_resp_name

FROM fnd_application_vl fav, fnd_responsibility_vl frv

WHERE frv.application_id = fav.application_id

AND frv.responsibility_key = lv_req_resp_key;

fnd_user_pkg.addresp (username => lv_user_name,

resp_app => lv_appl_shrt_name,

resp_key => lv_req_resp_key,

security_group => 'STANDARD',

description => lv_description,

start_date => SYSDATE,

end_date => NULL

);

COMMIT;

DBMS_OUTPUT.put_line ('The responsibility ' || lv_req_resp_name || ' is added to the user ' || lv_user_name);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line ('Responsibility IS NOT added due to ' || SQLCODE || '; ' || SUBSTR (SQLERRM, 1, 250));

ROLLBACK;

END;

Program status check

 SELECT

    request_id,

    decode(status_code, 'A', 'Waiting', 'B', 'Resuming',

           'C', 'Normal', 'D', 'Cancelled', 'E',

           'Error', 'F', 'Scheduled', 'G', 'Warning',

           'H', 'On Hold', 'I', 'Normal', 'M',

           'No Manager', 'Q', 'Standby', 'R', 'Normal',

           'S', 'Suspended', 'T', 'Terminating', 'U',

           'Disabled', 'W', 'Paused', 'X', 'Terminated',

           'Z', 'Waiting')                                    status,

    decode(phase_code, 'C', 'Completed', 'I', 'Inactive',

           'P', 'Pending', 'R', 'Running', 'Others')          phase,

    to_char(request_date, 'DD-MON-YYYY HH24:MI:SS')           request_date,

    requestor,

    to_char(actual_start_date, 'DD-MON-YYYY HH24:MI:SS')      actual_start_date,

    to_char(actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') actual_completion_date,

    to_char(round((actual_completion_date - actual_start_date) * 1440, 0))

    || ' Mins '

    || to_char(round(mod((actual_completion_date - actual_start_date) * 86400, 60),

                     2))

    || ' Secs'                                                duration,

    argument_text,

    rtl.responsibility_name,

    program,

    program_short_name,

    rtl.responsibility_id,

    concurrent_program_id,

    priority_request_id,

    priority,

    printer,

    print_style,

    user_print_style,

    requested_start_date

FROM

    apps.fnd_conc_req_summary_v conc,

    apps.fnd_responsibility_tl  rtl

WHERE

        conc.responsibility_id = rtl.responsibility_id

--and status_code = 'E'

    AND language = 'US'

    AND program LIKE '%OPQR%'

    AND request_date > sysdate - 9

ORDER BY

    request_date DESC;