Query to Find Scheduled Concurrent Programs

SELECT DISTINCT
     fcr.request_id
     ,fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL) Concurrent_program_name
     ,fu.user_name requestor
     ,fu.description requested_by
     ,fu.email_address
     ,frt.responsibility_name requested_by_resp
     ,(SELECT meaning
         FROM fnd_lookups
        WHERE lookup_type= 'CP_PHASE_CODE'
          AND lookup_code =fcr.phase_code) program_Phase
     ,(SELECT meaning
         FROM fnd_lookups
        WHERE lookup_type= 'CP_STATUS_CODE'
        AND lookup_code =fcr.status_code) program_Status
     ,fcr.argument_text program_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
     ,fcr.resubmit_interval||' '||fcr.resubmit_interval_unit_code Resubmit_Interval
     ,NVL2(fcr.resubmit_interval,'PERIODICALLY',NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS','ONCE')) schedule_type
  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 1=1
   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 (fcpt.user_concurrent_program_name = :P_PROGRAM_NAME OR :P_PROGRAM_NAME IS NULL)
Order By Fu.Description;

Query to list all objects of responsibility ie. submenu, menu and function

-- Query to list all objects of responsibility--
SELECT lvl r_lvl,
         rownumber rw_num,
         entry_sequence seq,
         (lvl || '.' || rownumber || '.' || entry_sequence) menu_seq,
         menu_name,
         sub_menu_name,
         prompt,
         fm.description,
         TYPE,
         function_name,
         user_function_name,
         fff.description form_description
    FROM (    SELECT LEVEL lvl,
                     ROW_NUMBER ()
                     OVER (PARTITION BY LEVEL, menu_id, entry_sequence
                           ORDER BY entry_sequence)
                        AS rownumber,
                     entry_sequence,
                     (SELECT user_menu_name
                        FROM fnd_menus_vl fmvl
                       WHERE 1 = 1 AND fmvl.menu_id = fmv.menu_id)
                        menu_name,
                     (SELECT user_menu_name
                        FROM fnd_menus_vl fmvl
                       WHERE 1 = 1 AND fmvl.menu_id = fmv.sub_menu_id)
                        sub_menu_name,
                     function_id,
                     prompt,
                     description
                FROM apps.fnd_menu_entries_vl fmv
          START WITH menu_id =
                        (SELECT menu_id
                           FROM apps.fnd_responsibility_vl
                          WHERE UPPER (responsibility_name) =
                                   UPPER (:resp_name))
          CONNECT BY PRIOR sub_menu_id = menu_id) fm,
         apps.fnd_form_functions_vl fff
   WHERE fff.function_id(+) = fm.function_id
ORDER BY lvl, entry_sequence;
/

NOTE:
1. Please pass the responsibility in parameter.
2. It'll show all submenu and function level wise.
3. R_lvl is a sequence number which starts with Parent Menu Level and drills down to child menu
4. ROW_NUM is sequence number for sub menus.
5. Seq is a sequence number as per the Oracle Apps.

Namespace prefix 'ref' used but not declared. Error in BI Publisher Report.

Below error comes when we used higher version of BI Publisher and reports will be completing with warning.

[1/6/19 1:57:03 AM] [798109:RT11983076] Output file was found but is zero sized - Deleted
[1/6/19 1:57:03 AM] [UNEXPECTED] [798109:RT11983076] java.lang.reflect.InvocationTargetException
Caused by: org.xml.sax.SAXException: Namespace prefix 'ref' used but not declared.
oracle.xdo.parser.v2.XPathException: Namespace prefix 'ref' used but not declared.
        at oracle.xdo.parser.v2.XSLProcessor.reportException(XSLProcessor.java:806)
        at oracle.xdo.parser.v2.XSLProcessor.newXSLStylesheet(XSLProcessor.java:614)


In case MS Word (rtf template) Properties of the XML Field has source something like ‘<?ref:xdo0002?>’ then you will face this error.


Solution:
To resolve this issue we need to set "form field size" option as "Backward Compatible". See as follows.


Navigation: Open report Template => BI Publisher Tab => Option (highlighted as yellow) => Window will open => Select "Build" => Select "Backward Compatible" from "form field size" section.

 

Save the changes and upload the changed template and retest.