Menu Changes and Profile Options in Oracle Apps Sysadmin

function not available to this responsibility change responsibilities or contact System Administrator


the Menu then run the program called "Compile Security" and Parameter is "Y".

Help -> Examine -> failed

Profile Option: "Hide Diagnostics menu entry"  set to No

Profile Option : "FND: Diagnostics" set to  ->  "Yes"

Profile Option: "Utilities:Diagnostics" set to "Yes"

How to find the process sid from concurrent request id in Oracle Applications or Oracle EBS

/* Formatted on 2017/07/17 16:22 (Formatter Plus v4.8.8) */
SELECT d.username,d.status, d.MACHINE, a.request_id, d.SID, d.serial#, d.osuser, d.process, c.spid, d.inst_id
  FROM apps.fnd_concurrent_requests a,
       apps.fnd_concurrent_processes b,
       gv$process c,
       gv$session d
 WHERE a.controlling_manager = b.concurrent_process_id
   AND c.pid = b.oracle_process_id
   AND b.session_id = d.audsid
   AND a.request_id = 95410755  
   AND a.phase_code = 'R';

FND Responsibilities attached to Users as Directly or Indirectly

/* Formatted on 2016/08/08 14:38 (Formatter Plus v4.8.8) */
SELECT   ppf.full_name, fu.user_name racfid, loc.location_code,
         frt.responsibility_name
    FROM fnd_user_resp_groups_indirect furg,
         applsys.fnd_user fu,
         applsys.fnd_responsibility_tl frt,
         applsys.fnd_responsibility fr,
         applsys.fnd_application_tl fat,
         applsys.fnd_application fa,
         per_all_people_f ppf,
         per_assignments_f pax,
         hr_locations_all loc
   WHERE furg.user_id = fu.user_id
     AND furg.responsibility_id = frt.responsibility_id
     AND fr.responsibility_id = frt.responsibility_id
     AND fa.application_id = fat.application_id
     AND fr.application_id = fat.application_id
     AND frt.LANGUAGE = USERENV ('LANG')
     AND fr.end_date IS NULL
     AND fu.employee_id = ppf.person_id
     AND pax.person_id = ppf.person_id
     AND SYSDATE BETWEEN ppf.effective_start_date
                     AND NVL (ppf.effective_end_date, SYSDATE)
     AND SYSDATE BETWEEN pax.effective_start_date
                     AND NVL (pax.effective_end_date, SYSDATE)
     AND pax.location_id = loc.location_id
UNION
SELECT   ppf.full_name, fu.user_name racfid, loc.location_code,
         frt.responsibility_name
    FROM fnd_user_resp_groups_direct furg,
         applsys.fnd_user fu,
         applsys.fnd_responsibility_tl frt,
         applsys.fnd_responsibility fr,
         applsys.fnd_application_tl fat,
         applsys.fnd_application fa,
         per_all_people_f ppf,
         per_assignments_f pax,
         hr_locations_all loc
   WHERE furg.user_id = fu.user_id
     AND furg.responsibility_id = frt.responsibility_id
     AND fr.responsibility_id = frt.responsibility_id
     AND fa.application_id = fat.application_id
     AND fr.application_id = fat.application_id
     AND frt.LANGUAGE = USERENV ('LANG')
     AND fr.end_date IS NULL
     AND fu.employee_id = ppf.person_id
     AND pax.person_id = ppf.person_id
     AND SYSDATE BETWEEN ppf.effective_start_date
                     AND NVL (ppf.effective_end_date, SYSDATE)
     AND SYSDATE BETWEEN pax.effective_start_date
                     AND NVL (pax.effective_end_date, SYSDATE)
     AND pax.location_id = loc.location_id
-- AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))
ORDER BY 1,3,4;

DFF Query in Oracle Applications and Query to find DFF and Segments in Oracle Applications

/* Formatted on 2016/03/10 09:49 (Formatter Plus v4.8.8) */
SELECT   ffv.descriptive_flexfield_name "DFF Name",
         ffv.application_table_name "Table Name", ffv.title "Title",
         ap.application_name "Application",
         ffc.descriptive_flex_context_code "Context Code",
         ffc.descriptive_flex_context_name "Context Name",
         ffc.description "Context Desc",
         ffc.enabled_flag "Context Enable Flag",
         att.column_seq_num "Segment Number",
         att.form_left_prompt "Segment Name",
         att.application_column_name "Column",
         fvs.flex_value_set_name "Value Set", att.display_flag "Displayed",
         att.enabled_flag "Enabled", att.required_flag "Required"
    FROM apps.fnd_descriptive_flexs_vl ffv,
         apps.fnd_descr_flex_contexts_vl ffc,
         apps.fnd_descr_flex_col_usage_vl att,
         apps.fnd_flex_value_sets fvs,
         apps.fnd_application_vl ap
   WHERE ffv.descriptive_flexfield_name = att.descriptive_flexfield_name
     AND ap.application_id = ffv.application_id
     AND ffv.descriptive_flexfield_name = ffc.descriptive_flexfield_name
     AND ffv.application_id = ffc.application_id
     AND ffc.descriptive_flex_context_code = att.descriptive_flex_context_code
     AND fvs.flex_value_set_id(+) = att.flex_value_set_id
     AND ((ffv.title = 'Additional Header Information'
     AND ap.application_name = 'Order Management')  or
     (ffv.title = 'Transaction Information'
     AND ap.application_name = 'Receivables'))
ORDER BY ap.application_name, ffc.descriptive_flex_context_code, att.column_seq_num

Responsibility and Attached operating unit Query in oracle application - Profile Option: MO: Operating Unit

SELECT   SUBSTR (pro1.user_profile_option_name, 1, 35) PROFILE,
         DECODE (pov.level_id,
                 10001, 'Site',
                 10002, 'Application',
                 10003, 'Resp',
                 10004, 'User'
                ) option_level,
         DECODE (pov.level_id,
                 10001, 'Site',
                 10002, appl.application_short_name,
                 10003, resp.responsibility_name,
                 10004, u.user_name
                ) level_value,
                (select SECURITY_PROFILE_NAME from hr.per_security_profiles
where SECURITY_PROFILE_ID= pov.profile_option_value) profile_name,      
 --NVL (pov.profile_option_value, 'Is Null') profile_option_value,
         (SELECT NAME
            FROM apps.hr_operating_units
           WHERE organization_id = pov.profile_option_value) opertingunit
    FROM apps.fnd_profile_option_values pov,
         apps.fnd_responsibility_tl resp,
         apps.fnd_application appl,
         apps.fnd_user u,
         apps.fnd_profile_options pro,
         apps.fnd_profile_options_tl pro1
   WHERE pro1.user_profile_option_name = ('MO: Security Profile')
     AND pro.profile_option_name = pro1.profile_option_name
     AND pro.profile_option_id = pov.profile_option_id
     AND pov.level_value = resp.responsibility_id(+)
     AND pov.level_value = appl.application_id(+)
     AND pov.level_value = u.user_id(+)
     AND pov.level_id = 10003
ORDER BY 3;

How To Change Look And Feel and Colors Of Oracle Applications forms in R12

By changing the profile option values you can change the display color for oracle forms in EBS.

Do the following:

1. Login to Oracle Applications as the System Administrator responsibility.
2. Navigate to Profile > System.
3. Ensure that the Site display is checked.
4. Query up 'Java Color Scheme' for the profile.
5. Select the appropriate color under the Site column.
6. Save the selection.
7. Sign off and back on (or change responsibility) for your new color scheme to take effect.

Be aware that the 'Java Color Scheme' profile has no effect if 'Java Look and Feel' is set to GENERIC.

how to make a form query only in oracle apps or oracle applications

Hi Everyone,                              This is one of the common requirement.They are many ways of doing this like form personalization ,Cutom.pll and others ..the easiest way to do it is by passing the parameter QUERY_ONLY="YES"  while creation Form Function.

But it doesn't gurantee all the forms will behave as expected because the form should be coded to make it work for this..not all standard forms have this..In HRMS Forms case there is another concept called task flow by which we can make the form read only.Steps:1.Check the form which need ot be made as query only.2.Go to the menu to which it is attached and the get the user function name of the form3.query the function from application developer(resp)-->application-->function

How to make a Oracle form 'Query Only'

There are many ways in which we can make a form Query only. We are going to discuss two
1) Using parameter QUERY_ONLY=YES
2) using Form Personalization
Method 1: Using parameter QUERY_ONLY=YES
Lets take an example of Purchase order enter form

Step 1) Using Sysadmin Responsibility find out the Menu attached to Purchasing Super User responsibility for which you want to make Purchase Order form as read only.

Step 2) Now Navigate to Application>Menu and query for this form and find out the form function associate with enter purchase order form. In our case it is "Purchase Orders"

Step 3) Create a new form function name having logically similar name as that of original function and in the form tab make QUERY_ONLY=YES

Step 4) Attach this new function in the place of old function to the same menu

Save and let Compile Security Request complete

Test ResultNavigate to Purchasing Super User> Purchase Order

See now this form is read only

Method 2 : Using Form Personalization

Step 1) Navigate to PO form>Help>Diagnostic>custom Code>Personalize

Step 2) Now here you can disable Update, Delete and Insert

Test Case : Check PO form now

Concurrent Request Details with Concurrent Programs, Timings and status Query in Oracle Applications

SELECT DISTINCT
       fcr.request_id,
       fcr.actual_start_date,
       fcr.actual_completion_date,
       FLOOR (
            (  (fcr.actual_completion_date - fcr.actual_start_date)
             * 24
             * 60
             * 60)
          / 3600)
          HOURS,
       FLOOR (
            (  (  (fcr.actual_completion_date - fcr.actual_start_date)
                * 24
                * 60
                * 60)
             -   FLOOR (
                      (  (fcr.actual_completion_date - fcr.actual_start_date)
                       * 24
                       * 60
                       * 60)
                    / 3600)
               * 3600)
          / 60)
          MINUTES,
       ROUND (
          (  (  (fcr.actual_completion_date - fcr.actual_start_date)
              * 24
              * 60
              * 60)
           -   FLOOR (
                    (  (fcr.actual_completion_date - fcr.actual_start_date)
                     * 24
                     * 60
                     * 60)
                  / 3600)
             * 3600
           - (  FLOOR (
                     (  (  (  fcr.actual_completion_date
                            - fcr.actual_start_date)
                         * 24
                         * 60
                         * 60)
                      -   FLOOR (
                               (  (  fcr.actual_completion_date
                                   - fcr.actual_start_date)
                                * 24
                                * 60
                                * 60)
                             / 3600)
                        * 3600)
                   / 60)
              * 60)))
          SECS,
       DECODE (fcr.phase_code,
               'C', 'Completed',
               'I', 'Inactive',
               'P', 'Pending',
               'R', 'Running',
               'N/A')
          phase_code,
       DECODE (fcr.status_code,
               'A', 'Waiting',
               'B', 'Resuming',
               'C', 'Normal',
               'D', 'Cancelled',
               'E', 'Errored',
               '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',
               'N/A')
          status_code,
       fcr.outfile_name,
       fcr.number_of_arguments,
       fcr.argument_text,
       frt.responsibility_name,
       fav.application_name,
       fav.application_short_name appl_short_name,
       fu.user_name,
       fu.description user_description,
       fu.start_date user_start_date,
       fcp.user_concurrent_program_name,
       fcp.concurrent_program_name short_name,
       fe.executable_name,
       DECODE (fe.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',
               'N/A')
          execution_method,
       fe.execution_file_name
  FROM apps.fnd_concurrent_requests fcr,
       apps.fnd_user fu,
       apps.fnd_application_vl fav,
       apps.fnd_responsibility_tl frt,
       apps.fnd_concurrent_programs_vl fcp,
       apps.fnd_executables fe
 WHERE     fcr.requested_by = fu.user_id
       AND fcr.concurrent_program_id = fcp.concurrent_program_id
       AND fcr.responsibility_id = frt.responsibility_id
       AND fcr.responsibility_application_id = fav.application_id
       AND fcp.executable_id = fe.executable_id
       AND NVL ( :P_Requested_by, fu.user_name) = fu.user_name
       AND NVL ( :request_id, fcr.request_id) = fcr.request_id
       AND NVL ( :P_concurrent_program_name,
                fcp.user_concurrent_program_name) =
              fcp.user_concurrent_program_name;

Concurrent Program with parameters value sets and default values query in Oracle Applications or Concurrent program Details

  SELECT a.application_name,
         cp.concurrent_program_name,
         cp.user_concurrent_program_name,
         dfcu.form_left_prompt,
         dfcu.display_flag,
         dfcu.required_flag,
         fvs.flex_value_set_name,
         dfcu.default_type,
         dfcu.DEFAULT_VALUE,
         dfcu.srw_param
    FROM apps.fnd_concurrent_programs_vl cp,
         apps.fnd_descriptive_flexs_vl df,
         apps.fnd_application_vl a,
         apps.fnd_descr_flex_contexts_vl dfc,
         apps.fnd_descr_flex_col_usage_vl dfcu,
         apps.fnd_flex_value_sets fvs
   WHERE     nvl(:p_conc_program_name, cp.user_concurrent_program_name) = cp.user_concurrent_program_name
         AND df.application_id = cp.application_id
         AND df.descriptive_flexfield_name =
                '$SRS$.' || cp.concurrent_program_name
         AND a.application_id = df.application_id
         AND dfc.application_id = df.application_id
         AND dfc.descriptive_flexfield_name = df.descriptive_flexfield_name
         AND dfcu.application_id = dfc.application_id
         AND dfcu.descriptive_flexfield_name = dfc.descriptive_flexfield_name
         AND dfcu.descriptive_flex_context_code =
                dfc.descriptive_flex_context_code
         AND fvs.flex_value_set_id = dfcu.flex_value_set_id
ORDER BY a.application_name, cp.concurrent_program_name, dfcu.column_seq_num;

Scheduled Concurrent Request Query in Oracle Applications

SELECT fl.meaning,
       fu.user_name,
       fu.description requestor,
       fu.end_date,
       NVL (fu.email_address, 'n/a') email_address,
       fcr.request_id,
       fcr.number_of_copies,
       fcr.printer,
       fcr.request_date,
       fcr.requested_start_date,
       fcp.user_concurrent_program_name,
       fcp.description,
       fcr.argument_text,
       frt.responsibility_name
  FROM apps.fnd_concurrent_requests fcr,
       apps.fnd_user fu,
       apps.fnd_lookups fl,
       apps.fnd_concurrent_programs_vl fcp,
       apps.fnd_responsibility_tl frt
 WHERE     fcr.requested_by = fu.user_id
       AND fl.lookup_type = 'CP_STATUS_CODE'
       AND fcr.status_code = fl.lookup_code
       AND fcr.program_application_id = fcp.application_id
       AND fcr.concurrent_program_id = fcp.concurrent_program_id
       AND fcr.responsibility_id = frt.responsibility_id
       AND NVL ( :Concurrent_program_name, fcp.user_concurrent_program_name) =
              fcp.user_concurrent_program_name
       AND fcr.phase_code = 'P'

Concurrent Programs Attached to Responsibilities Query

  SELECT frt.responsibility_name,
         frg.request_group_name,
         frgu.request_unit_type,
         frgu.request_unit_id,
         fcpt.user_concurrent_program_name
    FROM fnd_Responsibility fr,
         fnd_responsibility_tl frt,
         fnd_request_groups frg,
         fnd_request_group_units frgu,
         fnd_concurrent_programs_tl fcpt
   WHERE     frt.responsibility_id = fr.responsibility_id
         AND frg.request_group_id = fr.request_group_id
         AND frgu.request_group_id = frg.request_group_id
         AND fcpt.concurrent_program_id = frgu.request_unit_id
         AND frt.LANGUAGE = USERENV ('LANG')
         AND fcpt.LANGUAGE = USERENV ('LANG')
         AND NVL ( :conc_prg_name, fcpt.user_concurrent_program_name) =
                fcpt.user_concurrent_program_name
         AND NVL ( :Responsibility_name, frt.responsibility_name) =
                frt.responsibility_name
         AND NVL ( :REQUEST_GROUP, frg.request_group_name) =
                frg.request_group_name
ORDER BY 1,
         2,
         3,
         4

Function Menu Attached Responsibilities Query in Oracle Applications

  SELECT frtl.responsibility_name,
         fr.responsibility_key,
         fm.menu_id,
         fm.menu_name,
         menu.function_id,
         menu.prompt,
         fffv.user_function_name,
         fffv.function_name,
         fffv.TYPE
    FROM (    SELECT CONNECT_BY_ROOT fmet.menu_id top_menu_id,
                     fmet.menu_id menu_id,
                     fmet.sub_menu_id,
                     fmet.function_id,
                     fmet.prompt
                FROM apps.fnd_menu_entries_vl fmet
          CONNECT BY     PRIOR fmet.sub_menu_id = fmet.menu_id
                     AND PRIOR fmet.prompt IS NOT NULL) menu,
         apps.fnd_responsibility fr,
         apps.fnd_responsibility_tl frtl,
         apps.fnd_menus fm,
         apps.fnd_form_functions_vl fffv
   WHERE     fr.menu_id = menu.top_menu_id
         AND fffv.function_id = menu.function_id
         AND fffv.TYPE <> 'SUBFUNCTION'
         AND menu.function_id IS NOT NULL
         AND menu.prompt IS NOT NULL
         AND fm.menu_id = menu.menu_id
         AND frtl.responsibility_id = fr.responsibility_id
         AND NVL ( :REsponsibility_name, frtl.responsibility_name) =
                frtl.responsibility_name
         AND NVL ( :Function_name, fffv.function_name) = fffv.function_name
         AND menu.function_id NOT IN (SELECT ffvl.function_id
                                        FROM apps.fnd_resp_functions frf,
                                             applsys.fnd_responsibility_tl frt,
                                             apps.fnd_form_functions_vl ffvl
                                       WHERE     frf.responsibility_id =
                                                    frt.responsibility_id
                                             AND frf.action_id =
                                                    ffvl.function_id
                                             AND frf.rule_type = 'F'
                                             AND frt.responsibility_name =
                                                    frtl.responsibility_name)
         AND menu.menu_id NOT IN (SELECT fmv.menu_id
                                    FROM apps.fnd_resp_functions frf,
                                         applsys.fnd_responsibility_tl frt,
                                         apps.fnd_menus_vl fmv
                                   WHERE     frf.responsibility_id =
                                                frt.responsibility_id
                                         AND frf.action_id = fmv.menu_id
                                         AND frf.rule_type = 'M'
                                         AND frt.responsibility_name =
                                                frtl.responsibility_name)
ORDER BY fffv.user_function_name;

Concurrent Programs running more than 1 hour in oracle applications

  SELECT fu.user_name,
         fu.description,
         request_id,
         fcp.user_concurrent_program_name,
         actual_start_date,
         TRUNC ( (SYSDATE - actual_start_date) * 24, 2) time_in_hours
    FROM apps.fnd_concurrent_requests fcr,
         apps.fnd_concurrent_programs_vl fcp,
         apps.fnd_user fu
   WHERE     actual_start_date LIKE SYSDATE
         AND fcr.request_date > TRUNC (SYSDATE)
         AND status_code = 'R'
         AND phase_code = 'R'
         AND fcr.concurrent_program_id = fcp.concurrent_program_id
         AND fu.user_id(+) = fcr.requested_by
         AND TRUNC ( (SYSDATE - actual_start_date) * 24, 2) > 1
-- AND fcp.concurrent_program_id = 750966
ORDER BY time_in_hours DESC

Request Set and Concurrent Programs Query

Provide Concurrent Program name to the following query.
It lists all the Request Sets which are created with the Concurrent Program given.

SELECT DISTINCT user_request_set_name
  FROM FND_REQUEST_SETS_TL
 WHERE request_set_id IN
          (SELECT request_set_id
             FROM FND_REQUEST_SET_PROGRAMS
            WHERE concurrent_program_id =
                     (SELECT CONCURRENT_PROGRAM_ID
                        FROM fnd_concurrent_programs_tl
                       WHERE upper(USER_CONCURRENT_PROGRAM_NAME) = upper( '&Enter_Prog_name')));

Query 2:
Provide the Request Set name to the following query.
It lists all concurrent programs of this Request Set.

SELECT USER_CONCURRENT_PROGRAM_NAME
  FROM fnd_concurrent_programs_tl
 WHERE CONCURRENT_PROGRAM_ID IN
          (SELECT concurrent_program_id
             FROM FND_REQUEST_SET_PROGRAMS
            WHERE request_set_id =
                     (SELECT request_set_id
                        FROM FND_REQUEST_SETS_TL
                       WHERE upper(user_request_set_name) = upper('&Request_Set_Name')));

concurrent program assigned to responsibility query or Query to get the list of responsibility's to which concurrent program has assigned

  SELECT DISTINCT *
    FROM apps.fnd_responsibility_tl
   WHERE     responsibility_id IN (SELECT responsibility_id
                                     FROM apps.fnd_responsibility_vl
                                    WHERE     request_group_id IN (SELECT request_group_id
                                                                     FROM apps.fnd_request_group_units
                                                                    WHERE request_unit_id =
                                                                             (SELECT DISTINCT
                                                                                     concurrent_program_id
                                                                                FROM Apps.fnd_concurrent_programs_tl
                                                                               WHERE user_concurrent_program_name =
                                                                                        ''))
                                          AND end_date IS NULL)
         AND "LANGUAGE" LIKE 'US'
ORDER BY responsibility_name

FND Concurrent Request details with concurrent program by user Query SQL in Oracle Applications

  SELECT fu.user_name,
         fcp.user_concurrent_program_name,
         fcr.request_id,
         fcr.request_date,
         fcr.actual_start_date,
         fcr.actual_completion_date,
         fcr.argument_text
    FROM apps.fnd_concurrent_programs_vl fcp,
         apps.fnd_concurrent_requests fcr,
         apps.fnd_user fu
   WHERE     fu.user_id = fcr.requested_by
         AND fcp.concurrent_program_id = fcr.concurrent_program_id
         AND fcp.user_concurrent_program_name =
                NVL ( :concurrent_program_name,
                     fcp.user_concurrent_program_name)
         AND fcr.request_date >= NVL ( :P_DATE, fcr.request_date)
         AND fu.user_name = NVL ( :P_USER_NAME, fu.user_name)
ORDER BY fcr.request_id DESC

Concurrent Program Schedule Details Query

  SELECT fcr.request_id,
            fcpt.user_concurrent_program_name
         || NVL2 (fcr.description, ' (' || fcr.description || ')', NULL)
            conc_prog,
         fu.user_name requestor,
         fu.description requested_by,
         fu.email_address,
         frt.responsibility_name requested_by_resp,
         TRIM (fl.meaning) status,
         fcr.phase_code,
         fcr.status_code,
         fcr.argument_text "PARAMETERS",
         TO_CHAR (fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested,
         TO_CHAR (fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
            requested_start,
         TO_CHAR ( (fcr.requested_start_date), 'HH24:MI:SS') start_time,
         DECODE (fcr.hold_flag,  'Y', 'Yes',  'N', 'No') on_hold,
         CASE WHEN fcr.hold_flag = 'Y' THEN SUBSTR (fu.description, 0, 40) END
            last_update_by,
         CASE WHEN fcr.hold_flag = 'Y' THEN fcr.last_update_date END
            last_update_date,
         fcr.increment_dates,
         CASE
            WHEN fcrc.CLASS_INFO IS NULL
            THEN
                  'Yes: '
               || TO_CHAR (fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
            ELSE
               'n/a'
         END
            run_once,
         CASE
            WHEN fcrc.class_type = 'P'
            THEN
                  'Repeat every '
               || SUBSTR (fcrc.class_info, 1, INSTR (fcrc.class_info, ':') - 1)
               || DECODE (SUBSTR (fcrc.class_info,
                                    INSTR (fcrc.class_info,
                                           ':',
                                           1,
                                           1)
                                  + 1,
                                  1),
                          'N', ' minutes',
                          'M', ' months',
                          'H', ' hours',
                          'D', ' days')
               || DECODE (SUBSTR (fcrc.class_info,
                                    INSTR (fcrc.class_info,
                                           ':',
                                           1,
                                           2)
                                  + 1,
                                  1),
                          'S', ' from the start of the prior run',
                          'C', ' from the completion of the prior run')
            ELSE
               'n/a'
         END
            set_days_of_week,
         CASE
            WHEN     fcrc.class_type = 'S'
                 AND INSTR (SUBSTR (fcrc.class_info, 33), '1', 1) > 0
            THEN
                  'Days of week: '
               || DECODE (SUBSTR (fcrc.class_info, 33, 1), '1', 'Sun, ')
               || DECODE (SUBSTR (fcrc.class_info, 34, 1), '1', 'Mon, ')
               || DECODE (SUBSTR (fcrc.class_info, 35, 1), '1', 'Tue, ')
               || DECODE (SUBSTR (fcrc.class_info, 36, 1), '1', 'Wed, ')
               || DECODE (SUBSTR (fcrc.class_info, 37, 1), '1', 'Thu, ')
               || DECODE (SUBSTR (fcrc.class_info, 38, 1), '1', 'Fri, ')
               || DECODE (SUBSTR (fcrc.class_info, 39, 1), '1', 'Sat ')
            ELSE
               'n/a'
         END
            days_of_week
    FROM fnd_concurrent_requests fcr,
         fnd_user fu,
         fnd_concurrent_programs fcp,
         fnd_concurrent_programs_tl fcpt,
         fnd_printer_styles_tl fpst,
         fnd_conc_release_classes fcrc,
         fnd_responsibility_tl frt,
         fnd_lookups fl
   WHERE     fcpt.user_concurrent_program_name =
                NVL ( :P_PROGRAM_NAME, fcpt.user_concurrent_program_name)
         AND fcp.application_id = fcpt.application_id
         AND fcr.requested_by = fu.user_id
         AND fcr.concurrent_program_id = fcp.concurrent_program_id
         AND fcr.program_application_id = fcp.application_id
         AND fcr.concurrent_program_id = fcpt.concurrent_program_id
         AND fcr.responsibility_id = frt.responsibility_id
         AND fcr.print_style = fpst.printer_style_name(+)
         AND fcr.release_class_id = fcrc.release_class_id(+)
         AND fcr.status_code = fl.lookup_code
         AND fl.lookup_type = 'CP_STATUS_CODE'
         AND fcr.phase_code = 'P'
         AND 1 = 1
ORDER BY Fu.Description, Fcr.Requested_Start_Date ASC;

How to get Oracle API information in Oracle Applications

Go to System Administrator -> Assign
Integrated SOA Gateway Responsibility
Then Go to E-Business Suite menu  and select module and see API information with parameters and purpose.

API to Delete Values in Value Set in Oracle Applications

DECLARE
   l_err_msg   VARCHAR2 (500) := NULL;

   CURSOR c1
   IS
      SELECT ffv.flex_value_id, ffv.flex_value
        FROM fnd_flex_value_sets ffvs,
             fnd_flex_values ffv,
             fnd_flex_values_tl ffvt
       WHERE flex_value_set_name = 'PRICE_BOOK_LAYOUT'       -- Value Set Name
         AND ffv.flex_value_set_id = ffvs.flex_value_set_id
         AND ffvt.flex_value_id = ffv.flex_value_id
         AND ffvs.flex_value_set_id = ffv.flex_value_set_id
         AND ffv.flex_value = 'QP_PBP_REP_STRUCTO_CRETE'
         AND ffvt.LANGUAGE = 'US'
         AND ffv.enabled_flag = 'Y'
         AND ffv.summary_flag = 'N';
BEGIN
   FOR i IN c1
   LOOP
      fnd_flex_values_pkg.delete_row (i.flex_value_id);
      COMMIT;
      DBMS_OUTPUT.put_line (i.flex_value_id || ' Deleted  Successfully !!!!');
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      l_err_msg := SQLERRM;
      DBMS_OUTPUT.put_line ('Exception: ' || l_err_msg);
END;

Concurrent Program and XML Publisher or BI Publisher Data Definition and Template details

         select USER_CONCURRENT_PROGRAM_NAME, cpn.CONCURRENT_PROGRAM_NAME CONCURRENT_PROGRAM_SHORT_NAME,fe.EXECUTABLE_NAME 
         ,dsn.DATA_SOURCE_NAME, ds.DATA_SOURCE_CODE,tn.TEMPLATE_NAME,   t.TEMPLATE_CODE
          from apps.fnd_concurrent_programs_vl cpn, apps.FND_EXECUTABLES fe
          ,apps.XDO_DS_DEFINITIONS_b ds, apps.XDO_DS_DEFINITIONS_tl dsn, apps.XDO_TEMPLATES_B t
          ,apps.XDO_TEMPLATES_tl tn
          where  cpn.EXECUTABLE_ID = fe.EXECUTABLE_ID
          and ds.DATA_SOURCE_CODE = cpn.CONCURRENT_PROGRAM_NAME
          and ds.DATA_SOURCE_CODE = dsn.DATA_SOURCE_CODE
          and dsn.language = 'US'
          and ds.DATA_SOURCE_CODE = t.DATA_SOURCE_CODE
          and t.TEMPLATE_CODE = tn.TEMPLATE_CODE
          and tn.language = 'US'
          order by 1 desc;