Query to find out the responsibility,Menu based on Function

SELECT DISTINCT a.responsibility_name, c.user_menu_name
           FROM apps.fnd_responsibility_tl a,
                apps.fnd_responsibility b,
                apps.fnd_menus_tl c,
                apps.fnd_menus d,
                apps.fnd_application_tl e,
                apps.fnd_application f
          WHERE a.responsibility_id(+) = b.responsibility_id
            AND b.menu_id = c.menu_id
            AND b.menu_id = d.menu_id
            AND e.application_id = f.application_id
            AND f.application_id = b.application_id
            AND a.LANGUAGE = 'US'
            AND b.menu_id IN (
                   SELECT menu_id
                     FROM fnd_menu_entries_vl
                    WHERE function_id IN (
                             SELECT function_id
                               FROM applsys.fnd_form_functions_tl
                              WHERE user_function_name =
                                                       'Fujitsu Trip Sequence'))

Query to find concurrent execution file name

SELECT fe.executable_name
      ,fe.execution_file_name
      ,flu.meaning execution_method
      ,fcp.concurrent_program_name
      ,fcpt.user_concurrent_program_name
FROM fnd_executables fe
      ,fnd_concurrent_programs fcp
      ,fnd_concurrent_programs_tl fcpt
      ,apps.fnd_lookup_values flu

Query to Fetch Menu and Function of given Responsibility

SELECT fr.responsibility_id,
       frt.responsibility_name,
       fm.menu_id,
       fm.menu_name,
       fmt.user_menu_name,
       fmet.prompt,
       fme.sub_menu_id,
       (SELECT fmtsub.user_menu_name
          FROM fnd_menus fmsub, fnd_menus_tl fmtsub
         WHERE     fmsub.menu_id = fme.sub_menu_id
               AND fmtsub.menu_id = fmsub.menu_id
               AND fmtsub.language = 'US')
          sub_menu,
       fme.function_id,
       (SELECT ffft.user_function_name
          FROM fnd_form_functions fff, fnd_form_functions_tl ffft
         WHERE     fff.function_id = fme.function_id
               AND fff.function_id = ffft.function_id
               AND ffft.language = 'US')
          user_function_name
  FROM fnd_responsibility fr,
       fnd_responsibility_tl frt,
       fnd_menus fm,
       fnd_menus_tl fmt,
       fnd_menu_entries fme,
       fnd_menu_entries_tl fmet
 WHERE     fr.responsibility_id = frt.responsibility_id
       AND UPPER (frt.responsibility_name) = 'XXX HR EMPLOYEE SELF SERVICE'
       AND frt.language = 'US'
       AND fm.menu_id = fr.menu_id
       AND fm.menu_id = fmt.menu_id
       AND fmt.language = 'US'
       AND fm.menu_id = fme.menu_id
       AND fme.menu_id = fmet.menu_id
       AND fmet.language = 'US'

       AND fme.entry_sequence = fmet.entry_sequence

Query to delete the data definitions and concurrent program

BEGIN
   xdo_ds_definitions_pkg.delete_row (
      x_application_short_name   => 'APPL_SHORT_NAME',
      x_data_source_code         => 'DATA_SOURCE_CODE');
   COMMIT;
END;


BEGIN
   fnd_global.apps_initialize (FND_GLOBAL.user_id, FND_GLOBAL.resp_id, FND_GLOBAL.resp_appl_id);
   COMMIT;
   FND_PROGRAM.delete_program (
      program_short_name   => 'PROG_SHORT_NAME',
      application          => 'Prog_appl_name');
   COMMIT;
END;

Query To Count Module Wise Report

SELECT   fa.application_short_name,
         DECODE (fcpv.execution_method_code,
                 'B', 'Request Set Stage Function',
                 'Q', 'SQL*Plus',
                 'H', 'Host',
                 'L', 'SQL*Loader',
                 'A', 'Spawned',
                 'I', 'PL/SQL Stored Procedure',
                 'P', 'Oracle Reports',
                 'S', 'Immediate',
                 fcpv.execution_method_code
                ) exe_method,
         COUNT (concurrent_program_id) COUNT
    FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
   WHERE fcpv.application_id = fa.application_id
GROUP BY fa.application_short_name, fcpv.execution_method_code
ORDER BY 1;

Query To calculate request time

SELECT   pt.user_concurrent_program_name user_concurrent_program_name,
         DECODE (P.concurrent_program_name,'ALECDC', P.concurrent_program_name || '[' || f.description|| ']',P.concurrent_program_name) concurrent_program_name,
         f.request_id,A.requestor,f.argument_text,
         f.actual_start_date actual_start_date,
         f.actual_completion_date actual_completion_date,
            FLOOR (((f.actual_completion_date - f.actual_start_date)* 24* 60* 60)/ 3600)|| ' HOURS '
         || FLOOR ((((f.actual_completion_date - f.actual_start_date)* 24* 60* 60)
                      -   FLOOR ((( f.actual_completion_date - f.actual_start_date)* 24* 60* 60)/ 3600)* 3600)/ 60)|| ' MINUTES '
         || ROUND ((((f.actual_completion_date - f.actual_start_date)* 24* 60* 60)
        -   FLOOR (((f.actual_completion_date - f.actual_start_date)* 24* 60* 60)/ 3600)* 3600
        - (FLOOR ((((f.actual_completion_date - f.actual_start_date) * 24* 60* 60)
        - FLOOR (((f.actual_completion_date - f.actual_start_date)* 24* 60* 60)/ 3600)* 3600)/ 60)* 60)))
         || ' SECS ' time_difference,
         DECODE (f.phase_code,'R', 'Running','C', 'Complete',f.phase_code) phase,
         f.status_code
    FROM apps.fnd_concurrent_programs P,
         apps.fnd_conc_req_summary_v a,
         apps.fnd_concurrent_programs_tl pt,
         apps.fnd_concurrent_requests f
   WHERE a.request_id = f.request_id
     AND f.concurrent_program_id = P.concurrent_program_id
     AND f.program_application_id = P.application_id
     AND f.concurrent_program_id = pt.concurrent_program_id
     AND f.program_application_id = pt.application_id
     AND pt.LANGUAGE = USERENV ('Lang')
     AND f.actual_start_date IS NOT NULL
--     AND pt.user_concurrent_program_name = '&Conc_prog_name'
     AND f.Request_Id  = :p_request_id
ORDER BY f.request_id DESC, f.actual_completion_date - f.actual_start_date DESC;      

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;

Oracle HRMS Query - Position

Select distinct
    PD.segment1 POSITION_NUMBER ,
    pd.segment2 POSITION_NAME ,
    pd.segment3 POSITION_GRADE ,
    (select name from hr_all_organization_units where ORGANIZATION_ID = pd.segment4) org ,
    pd.segment4 org_id,
    pd.segment5 POSITION_CODE,
    po.name position,
    j.name job ,
    po.position_id
From
    per_positions po ,
    per_jobs j ,
    hr_all_organization_units org  ,
    PER_POSITION_DEFINITIONS PD
where 1=1
AND po.BUSINESS_GROUP_ID = 81
and pd.POSITION_DEFINITION_ID = po.POSITION_DEFINITION_ID
and j.job_id   = po.job_id
and org.ORGANIZATION_ID = po.ORGANIZATION_ID
and po.position_id= 17624

List of Responsibilities associated with Form Functions

SELECT DISTINCT responsibility_id
FROM apps.fnd_responsibility_vl a
WHERE a.end_date IS NULL
AND a.menu_id IN (
SELECT menu_id
FROM apps.fnd_menu_entries_vl
START WITH menu_id IN (
SELECT menu_id
FROM apps.fnd_menu_entries_vl
WHERE function_id IN (
SELECT function_id
FROM applsys.fnd_form_functions a
WHERE function_name =
pc_function_name))
CONNECT BY PRIOR menu_id = sub_menu_id)
AND a.responsibility_id NOT IN (
SELECT responsibility_id
FROM apps.fnd_responsibility_vl
WHERE responsibility_id IN (
SELECT responsibility_id
FROM applsys.fnd_resp_functions resp
WHERE action_id IN (
SELECT function_id
FROM applsys.fnd_form_functions a
WHERE function_name =
pc_function_name)))
AND a.responsibility_id NOT IN (
SELECT responsibility_id
FROM apps.fnd_responsibility_vl
WHERE responsibility_id IN (
SELECT responsibility_id
FROM applsys.fnd_resp_functions resp
WHERE action_id IN (
SELECT menu_id
FROM apps.fnd_menu_entries_vl
START WITH menu_id IN (
SELECT menu_id
FROM apps.fnd_menu_entries_vl
WHERE function_id IN (
SELECT function_id
FROM applsys.fnd_form_functions a
WHERE function_name =
pc_function_name))
CONNECT BY PRIOR menu_id =
sub_menu_id)))
ORDER BY responsibility_id

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;