Query Check responsibility assigned to a specific USER

SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name,
              SUBSTR (r.responsibility_name, 1, 60) responsiblity,
              SUBSTR (a.application_name, 1, 50) application
         FROM fnd_user u,
              fnd_user_resp_groups g,
              fnd_application_tl a,
              fnd_responsibility_tl r
        WHERE g.user_id(+) = u.user_id
          AND g.responsibility_application_id = a.application_id
          AND a.application_id = r.application_id
          AND g.responsibility_id = r.responsibility_id
          AND a.application_name = 'Purchasing'
     ORDER BY SUBSTR (user_name, 1, 30),
              SUBSTR (a.application_name, 1, 50),
              SUBSTR (r.responsibility_name, 1, 60)

Profile Value at any Level Query

SELECT DISTINCT pot.user_profile_option_name PROFILE,
                DECODE (a.profile_option_value,
                        '1', '1 (may be "Yes")',
                        '2', '2 (may be "No")',
                        a.profile_option_value
                       ) VALUE,
                DECODE (a.level_id,
                        10001, 'Site',
                        10002, 'Application',
                        10003, 'Responsibility',
                        10004, 'User',
                        '????'
                       ) level_identifier,
                DECODE (a.level_id,
                        10002, e.application_name,
                        10003, c.responsibility_name,
                        10004, d.user_name,
                        '-'
                       ) level_name
           FROM applsys.fnd_application_tl e,
                applsys.fnd_user d,
                applsys.fnd_responsibility_tl c,
                applsys.fnd_profile_option_values a,
                applsys.fnd_profile_options b,
                applsys.fnd_profile_options_tl pot
          WHERE 1 = 1
            AND UPPER (pot.user_profile_option_name) LIKE
                                                        UPPER ('%&v_profile%')
            AND pot.profile_option_name = b.profile_option_name
            AND b.application_id = a.application_id(+)
            AND b.profile_option_id = a.profile_option_id(+)
            AND a.level_value = c.responsibility_id(+)
            AND a.level_value = d.user_id(+)
            AND a.level_value = e.application_id(+)
            AND (   UPPER (e.application_name) LIKE
                                        UPPER ('%&appname_respname_username%')
                 OR UPPER (c.responsibility_name) LIKE
                                       UPPER ('%&&appname_respname_username%')
                 OR UPPER (d.user_name) LIKE
                                       UPPER ('%&&appname_respname_username%')
                )
       ORDER BY PROFILE, level_identifier, level_name, VALUE

oracle-apps-product-top-directories

col variable_name format a15
col value format a64
select variable_name, value
from   fnd_env_context
where  variable_name like '%\_TOP' escape '\''
and    concurrent_process_id =
     ( select max(concurrent_process_id) from fnd_env_context )
order by 1;

List of Form Functions attached to Responsibility

SELECT DISTINCT faa.application_name application, rtl.responsibility_name,
ffl.user_function_name, ff.function_name, ffl.description,
ff.TYPE
FROM fnd_compiled_menu_functions cmf,
fnd_form_functions ff,
fnd_form_functions_tl ffl,
fnd_responsibility r,
fnd_responsibility_vl rtl,
apps.fnd_application_all_view faa
WHERE cmf.function_id = ff.function_id
AND r.menu_id = cmf.menu_id
AND rtl.responsibility_id = r.responsibility_id
AND cmf.grant_flag = 'Y'
AND ff.function_id = ffl.function_id
AND faa.application_id(+) = r.application_id
AND UPPER(rtl.responsibility_name) LIKE '%TEST%'
AND r.end_date IS NULL
AND rtl.end_date IS NULL
ORDER BY rtl.responsibility_name;

FND_REQUEST.SUBMIT_REQUEST in R12

FND_REQUEST.SUBMIT_REQUEST is an API used to submit a concurrent program in ORACLE Applications.

Parameters:

Application : Short name of the application associated with the concurrent request to be submitted.
Program - Short name of the concurrent program (not the executable) for which the request should be submitted.
Description - Description of the request that is displayed in the Concurrent Requests form (Optional.)
Start_time - Time at which the request should start running, formatted as HH24:MI or HH24:MI:SS (Optional.)
Sub_request - Set to TRUE if the request is submitted from another request and should be treated as a sub-request.
Argument1...100 - Arguments for the concurrent request; up to 100 arguments are permitted. If submitted from Oracle Forms, you must specify all 100 arguments.

Example: Submission of Standard Order Import Program


SET SERVEROUTPUT ON;
DECLARE
     v_request_id                        NUMBER           DEFAULT 0;
   
    --Order Import Parameters
    p_operating_unit                     VARCHAR2(20)    := NULL;
    p_order_source                       VARCHAR2(20)    := 'XYZ';
    p_orig_sys_document_ref              VARCHAR2(20)    := NULL;
    p_operation_code                     VARCHAR2(20)    := NULL;
    p_validate_only                      VARCHAR2(20)    := 'N';
    p_debug_level                        VARCHAR2(20)    := '1';
    p_num_instances                      VARCHAR2(20)    := '4';
    p_sold_to_org_id                     VARCHAR2(20)    := NULL;
    p_sold_to_org                        VARCHAR2(20)    := NULL;
    p_change_sequence                    VARCHAR2(20)    := NULL;
    p_perf_param                         VARCHAR2(20)    := 'Y';
    p_rtrim_data                         VARCHAR2(20)    := 'N';
    p_pro_ord_with_null_flag             VARCHAR2(20)    := 'Y';
    p_default_org_id                     VARCHAR2(20)    := '83';
    p_validate_desc_flex                 VARCHAR2(20)    := 'N';

    -- End of Parameters -----

    v_context varchar2(100);


    FUNCTION set_context( i_user_name    IN  VARCHAR2
                         ,i_resp_name    IN  VARCHAR2
                         ,i_org_id       IN  NUMBER)
    RETURN VARCHAR2
    IS
        /* Inorder to reduce the content of the post I moved the implementation part of this function to another post and it is available here */
    END set_context;


BEGIN
      -- Setting the context ----
      v_context := set_context('&V_USER_NAME','&V_RESPONSIBILITY',82);
      IF v_context = 'F'
      THEN
        DBMS_OUTPUT.PUT_LINE('Error while setting the context');      
      END IF;

      DBMS_OUTPUT.PUT_LINE('Submit Order Import Concurrent Program');

      v_request_id:=  FND_REQUEST.SUBMIT_REQUEST (
               application  =>  'ONT'
              ,program      =>  'OEOIMP'
              ,description  =>  'Order Import'
              ,start_time   =>  SYSDATE
              ,sub_request  =>  NULL
              ,argument1    =>  p_operating_unit
              ,argument2    =>  p_order_source
              ,argument3    =>  p_orig_sys_document_ref
              ,argument4    =>  p_operation_code
              ,argument5    =>  p_validate_only
              ,argument6    =>  p_debug_level
              ,argument7    =>  p_num_instances
              ,argument8    =>  p_sold_to_org_id
              ,argument9    =>  p_sold_to_org
              ,argument10   =>  p_change_sequence
              ,argument11   =>  p_perf_param
              ,argument12   =>  p_rtrim_data
              ,argument13   =>  p_pro_ord_with_null_flag
              ,argument14   =>  p_default_org_id
              ,argument15   =>  p_validate_desc_flex
             );

       COMMIT;

       DBMS_OUTPUT.PUT_LINE('Request_id: '||v_request_id);

EXCEPTION WHEN OTHERS THEN      
       DBMS_OUTPUT.PUT_LINE(SQLCODE||' Error :'||SQLERRM);
END;

FND_PROGRAM Remove Concurrent Program from Request Group via API

DECLARE
   v_program_short_name    VARCHAR2 (200);
   v_program_application   VARCHAR2 (200);
   v_request_group         VARCHAR2 (200);
   v_group_application     VARCHAR2 (200);
BEGIN
   v_program_short_name  := 'XX_SHAREORACLEAPPS';
   v_program_application := 'Payables';
   v_request_group       := 'All Reports';
   v_group_application   := 'Assets';
   apps.fnd_program.remove_from_group
         (program_short_name       => v_program_short_name,
          program_application      => v_program_application,
          request_group            => v_request_group,
          group_application        => v_group_application
                               );
   COMMIT;
END;

oracle-apps-product-top-directories

col variable_name format a15
col value format a64
select variable_name, value
from   fnd_env_context
where  variable_name like '%\_TOP' escape '\'
and    concurrent_process_id =
     ( select max(concurrent_process_id) from fnd_env_context )
order by 1;