Important Queries in oracle R12

1. Query To get list of responsibilities. 
========================================================================
/* Formatted on 6/24/2014 11:14:55 AM (QP5 v5.115.810.9015) */
SELECT (SELECT application_short_name
        FROM fnd_application fa
        WHERE fa.application_id = frt.application_id)
          application, frt.responsibility_id, frt.responsibility_name
FROM apps.fnd_responsibility_tl frt
WHERE frt.responsibility_name LIKE 'Quality Man%';
   
2. Query To get Menus Associated with responsibility  
=======================================================================
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 a.responsibility_id = '20538'
            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';

3. Query To get submenus and Function attached to this Main menu.
========================================================================
SELECT c.prompt, c.description
  FROM apps.fnd_menus_tl a, fnd_menu_entries_tl c
 WHERE a.menu_id = c.menu_id AND a.user_menu_name = 'F4 UK PAY Navigator';

4. Query To get assigned responsibility to a 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
     ORDER BY SUBSTR (user_name, 1, 30),
              SUBSTR (a.application_name, 1, 50),
              SUBSTR (r.responsibility_name, 1, 60);

5. Query To get responsibility and attached request groups.
======================================================================
SELECT   responsibility_name responsibility, request_group_name,
         frg.description
    FROM fnd_request_groups frg, fnd_responsibility_vl frv
   WHERE frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name

6. Query To get modified profile options.
======================================================================
SELECT   t.user_profile_option_name, profile_option_value, v.creation_date,
         v.last_update_date,
         v.creation_date - v.last_update_date "Change Date",
         (SELECT UNIQUE user_name
                   FROM fnd_user
                  WHERE user_id = v.created_by) "Created By",
         (SELECT user_name
            FROM fnd_user
           WHERE user_id = v.last_updated_by) "Last Update By"
    FROM fnd_profile_options o,
         fnd_profile_option_values v,
         fnd_profile_options_tl t
   WHERE o.profile_option_id = v.profile_option_id
     AND o.application_id = v.application_id
     AND start_date_active <= SYSDATE
     AND NVL (end_date_active, SYSDATE) >= SYSDATE
     AND o.profile_option_name = t.profile_option_name
     AND level_id = 10001
     AND t.LANGUAGE IN (SELECT language_code
                          FROM fnd_languages
                         WHERE installed_flag = 'B'
                        UNION
                        SELECT nls_language
                          FROM fnd_languages
                         WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;

7. Query To get modified profile options.
===============================================================================
SELECT   ffft.user_function_name "User Form Name", ffcr.SEQUENCE,
         ffcr.description, ffcr.rule_type, ffcr.enabled, ffcr.trigger_event,
         ffcr.trigger_object, ffcr.condition, ffcr.fire_in_enter_query,
         (SELECT user_name
            FROM fnd_user fu
           WHERE fu.user_id = ffcr.created_by) "Created By "
    FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft
   WHERE ffcr.ID = ffft.function_id
ORDER BY 1;

8. Query To get Patch Level.
================================================================================
SELECT a.application_name,
       DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,
       patch_level
  FROM apps.fnd_application_vl a, apps.fnd_product_installations b
 WHERE a.application_id = b.application_id;

9. Query To get all Functions
===============================================================================
SELECT   function_id, user_function_name, creation_date, description
    FROM fnd_form_functions_tl
ORDER BY user_function_name

10. Query To get all Request (conc. Program) attached to a responsibility
===============================================================================
SELECT   responsibility_name, frg.request_group_name,
         fcpv.user_concurrent_program_name, fcpv.description
    FROM fnd_request_groups frg,
         fnd_request_group_units frgu,
         fnd_concurrent_programs_vl fcpv,
         fnd_responsibility_vl frv
   WHERE frgu.request_unit_type = 'P'
     AND frgu.request_group_id = frg.request_group_id
     AND frgu.request_unit_id = fcpv.concurrent_program_id
     AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;

11. Query To get all request with application
===============================================================================
SELECT   fa.application_short_name, fcpv.user_concurrent_program_name,
         description,
         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,
         output_file_type, program_type, printer_name, minimum_width,
         minimum_length, concurrent_program_name, concurrent_program_id
    FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
   WHERE fcpv.application_id = fa.application_id
ORDER BY description

12. 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;

13. Query To calculate request time
===============================================================================
 
/* Formatted on 6/24/2014 11:16:57 AM (QP5 v5.115.810.9015) */
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;                                                   

14. 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)

15. Query Check Current Applied Patch
===============================================================================
SELECT   patch_name, patch_type, maint_pack_level, creation_date
    FROM applsys.ad_applied_patches
ORDER BY creation_date DESC

16. Script to check Concurrent Request Details
==============================================================================
SELECT   a.request_id, a.user_concurrent_program_name, a.requestor, b.argument_text,
         a.completion_text, a.actual_start_date, a.actual_completion_date,
         a.argument_text, b.logfile_name, b.logfile_node_name, b.outfile_name,
         b.outfile_node_name, a.responsibility_id, c.responsibility_name,
         c.description, a.phase_code, a.status_code
    FROM fnd_conc_req_summary_v a,
         fnd_concurrent_requests b,
         fnd_responsibility_vl c
   WHERE 1 = 1
     AND a.responsibility_id = c.responsibility_id
     --and trunc(a.request_date) >=   trunc(sysdate)-1
     AND TRUNC (a.request_date) > '30-Jul-2008'
     AND a.request_id = b.request_id
 --and  a.requestor not in ('SYSADMIN')
--and  a.requestor in ('INTERFACES')
 --and a.user_concurrent_program_name like '%Customer%'
 --and a.argument_text like '%posarbinv050607112000%'
 --and a.request_id between 427953 and 428200
--and a.request_id > 434045
--and responsibility_name = '&resp_name'
ORDER BY a.request_id DESC;

17. Script to print the Oracle Apps Version Number
========================================================
SELECT SUBSTR (a.application_short_name, 1, 5) code,
       SUBSTR (t.application_name, 1, 50) application_name,
       p.product_version VERSION
  FROM fnd_application a, fnd_application_tl t, fnd_product_installations p
 WHERE a.application_id = p.application_id
   AND a.application_id = t.application_id
   AND t.LANGUAGE = USERENV ('LANG') ;

18. Script to display status of all the Concurrent Managers
========================================================
SELECT DISTINCT concurrent_process_id "Concurrent Process ID",
                pid "System Process ID", os_process_id "Oracle Process ID",
                q.concurrent_queue_name "Concurrent Manager Name",
                p.process_status_code "Status of Concurrent Manager",
                TO_CHAR
                     (p.process_start_date,
                      'MM-DD-YYYY HH:MI:SSAM'
                     ) "Concurrent Manager Started at"
           FROM fnd_concurrent_processes p,
                fnd_concurrent_queues q,
                fnd_v$process
          WHERE q.application_id = queue_application_id
            AND q.concurrent_queue_id = p.concurrent_queue_id
            AND spid = os_process_id
            AND process_status_code NOT IN ('K', 'S')
       ORDER BY concurrent_process_id, os_process_id, q.concurrent_queue_name

19. Script For Audit Changes in Profile Options
==========================================================================
SELECT    '***Profile Option Name ***'
       || a.user_profile_option_name
       || '*** Was Updated with value '
       || '”'
       || b.profile_option_value
       || '”'
       || ' In The Last '
       || :p_no_of_days
       || ' days'
       || ' by '
       || (SELECT user_name
             FROM apps.fnd_user u
            WHERE u.user_id = b.last_updated_by) mesg
  FROM apps.fnd_profile_options_vl a,
       apps.fnd_profile_option_values b,
       apps.fnd_user c
 WHERE a.profile_option_id = b.profile_option_id
   AND b.last_updated_by = c.user_id
   AND (   b.last_update_date > SYSDATE - :p_no_of_days
        OR b.creation_date > SYSDATE - :p_no_of_days
       );
20. Script to find Object Locked and to kill the session
==============================================================================
SELECT vlo.os_user_name "OS USERNAME", vlo.oracle_username "DB USER",vs.SID,vs.SERIAL#,
       vp.spid "SPID", ao.owner "OWNER", ao.object_name "OBJECT LOCKED",
       ao.object_type,
       DECODE (vlo.locked_mode,
               1, 'NO LOCK',
               2, 'ROW SHARE',
               3, 'ROW EXCLUSIVE',
               4, 'SHARE',
               5, 'SHARE ROW EXCL',
               6, 'EXCLUSIVE',
               NULL
              ) "MODE OF LOCK",
       vs.status "CURRENT STATUS"
  FROM v$locked_object vlo, all_objects ao, v$session vs, v$process vp
 WHERE vlo.object_id = ao.object_id
   AND vs.status <> 'KILLED'
   AND vlo.session_id = vs.SID
   AND vs.paddr = vp.addr

alter system kill session  'SID,SERIAL#' 

 21.Script to check Flexfield Setup
 ==================================================================
 SELECT id_flex_structure_code structure_code,
       DECODE (dynamic_inserts_allowed_flag,
               'Y', 'OK: Dynamic',
               'ERROR: No Dynamic'
              ) dynamic,
       DECODE (freeze_flex_definition_flag,
               'Y', 'OK: Freezed',
               'ERROR: No Freezed'
              ) freeze,
       segment_num seg_number, segment_name seg_name,
       DECODE (required_flag,
               'Y', 'OK: Required',
               'ERROR: No required'
              ) required,
       DECODE (fvs.flex_value_set_name,
               NULL, NULL,
               fvs.flex_value_set_name
              ) value_set,
       DECODE (fvs.validation_type,
               'N', 'OK: No validation',
               'ERROR: ' || fvs.validation_type
              ) VALIDATION,
       DECODE (fvs.uppercase_only_flag,
               'N', 'OK: No',
               'ERROR: Uppercase Only'
              ) uppercase_only,
       DECODE (alphanumeric_allowed_flag,
               'Y', 'OK: Allowed',
               'ERROR: Not Allowed'
              ) alphanumeric,
       DECODE (numeric_mode_enabled_flag,
               'N', 'OK:Not justified',
               'ERROR: Justified'
              ) right_justify,
       DECODE (format_type,
               'C', 'OK: Char',
               'ERROR: ' || format_type
              ) format_type
  FROM apps.fnd_id_flex_structures_vl ffst,
       apps.fnd_id_flex_segments_vl ffsg,
       applsys.fnd_flex_value_sets fvs
 WHERE ffst.application_id = 401
   AND ffst.id_flex_code = 'MCAT'
   AND ffst.enabled_flag = 'Y'
   AND ffst.application_id = ffsg.application_id
   AND ffst.id_flex_code = ffsg.id_flex_code
   AND ffst.id_flex_num = ffsg.id_flex_num
   AND ffsg.enabled_flag = 'Y'
   AND fvs.flex_value_set_id(+) = ffsg.flex_value_set_id
   AND id_flex_structure_code = 'PLANNING';
  

   OR

   SELECT id_flex_structure_code structure_code, segment_num seg_number,
       segment_name seg_name, ffsg.application_column_name,
       DECODE (fvs.flex_value_set_name,
               NULL, NULL,
               fvs.flex_value_set_name
              ) value_set
  FROM apps.fnd_id_flex_structures_vl ffst,
       apps.fnd_id_flex_segments_vl ffsg,
       applsys.fnd_flex_value_sets fvs
 WHERE ffst.application_id = 101
   AND ffst.id_flex_code = 'GL#'
   AND ffst.enabled_flag = 'Y'
   AND ffst.application_id = ffsg.application_id
   AND ffst.id_flex_code = ffsg.id_flex_code
   AND ffst.id_flex_num = ffsg.id_flex_num
   AND ffsg.enabled_flag = 'Y'
   AND fvs.flex_value_set_id(+) = ffsg.flex_value_set_id
   AND id_flex_structure_code = 'Dach COA';


select * from apps.fnd_id_flex_structures_vl  where APPLICATION_ID = 401 

select * from applsys.fnd_application where APPLICATION_SHORT_NAME = 'INV'

22. Script to list Report With Parameters
======================================================================
SELECT   a.concurrent_program_name AS concurrent_program_name,
         a.user_concurrent_program_name AS user_concurrent_program_name,
         c.application_short_name AS application_short_name,
         b.column_seq_num AS column_seq_num, b.srw_param AS param_seq,
         b.form_left_prompt AS prompt,
         d.flex_value_set_name AS values_set_name
    FROM fnd_concurrent_programs_vl@ebs_to_aps a,
         fnd_descr_flex_col_usage_vl@ebs_to_aps b,
         fnd_application@ebs_to_aps c,
         fnd_flex_value_sets@ebs_to_aps d
   WHERE a.enabled_flag = 'Y'
     AND a.concurrent_program_name =
                                 SUBSTR (b.descriptive_flexfield_name, 7, 100)
     AND a.application_id = c.application_id
     AND b.enabled_flag = 'Y'
     AND b.flex_value_set_id = d.flex_value_set_id
     AND a.user_concurrent_program_name LIKE 'CM%'
ORDER BY a.concurrent_program_id, b.column_seq_num  

23. Script to get current profile option value
=======================================================================
SELECT   fat.application_name, frv.responsibility_name,
         fpo.user_profile_option_name, pov.profile_option_value
    FROM applsys.fnd_application_tl fat,
         apps.fnd_responsibility_vl frv,
         apps.fnd_profile_option_values pov,
         apps.fnd_profile_options_vl fpo
   WHERE pov.application_id(+) = fpo.application_id
     AND pov.profile_option_id(+) = fpo.profile_option_id
     AND pov.level_value = frv.responsibility_id(+)
     AND fat.application_id = fpo.application_id
     AND fat.application_name = 'Master Scheduling/MRP'
     AND UPPER (fpo.user_profile_option_name) =
                                         'MRP:DEFAULT SOURCING ASSIGNMENT SET'
ORDER BY 1, 3, 2

SELECT o.profile_option_name,v.profile_option_value
  FROM fnd_profile_options o, fnd_profile_option_values v
 WHERE o.profile_option_name = NVL(:1,o.profile_option_name)
   AND v.level_id = NVL(:2,v.level_id)
   AND o.start_date_active <= SYSDATE
   AND NVL (o.end_date_active, SYSDATE) >= SYSDATE
   AND o.profile_option_id = v.profile_option_id
   AND o.application_id = v.application_id
   AND v.level_value = nvL(:3,v.level_value)

24. Script to get the locked objects
======================================================================
/* SQL For locked objects*/
SELECT   b.inst_id, b.session_id AS SID,
         NVL (b.oracle_username, '(oracle)') AS username,
         a.owner AS object_owner, a.object_name,
         DECODE (b.locked_mode,
                 0, 'None',
                 1, 'Null (NULL)',
                 2, 'Row-S (SS)',
                 3, 'Row-X (SX)',
                 4, 'Share (S)',
                 5, 'S/Row-X (SSX)',
                 6, 'Exclusive (X)',
                 b.locked_mode
                ) locked_mode,
         b.os_user_name
    FROM dba_objects a, gv$locked_object b
   WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;


Find Running, Pending, On Hold and Scheduled Requests

=======================================================================
 SELECT   fcr.request_id,
         DECODE (fcr.phase_code,
                 'P', DECODE (fcr.hold_flag,
                              'Y', 'Inactive',
                              fl_pend.meaning
                             ),
                 fl_pend.meaning
                ) phase,
         DECODE (fcr.phase_code,
                 'P', DECODE (fcr.hold_flag,
                              'Y', 'On Hold',
                              DECODE (SIGN (fcr.requested_start_date - SYSDATE),
                                      1, 'Scheduled',
                                      fl_stat.meaning
                                     )
                             ),
                 fl_stat.meaning
                ) status,
         fcpt.user_concurrent_program_name, fcr.increment_dates,
         fcr.resubmit_interval, fcr.resubmit_interval_unit_code,
         fcr.resubmit_interval_type_code, parent_request_id,
         fcr.requested_start_date, fu.user_name requested_by
    FROM fnd_concurrent_requests fcr,
         fnd_concurrent_programs_tl fcpt,
         fnd_lookups fl_pend,
         fnd_lookups fl_stat,
         fnd_user fu
   WHERE 1 = 1
     AND fcpt.concurrent_program_id = fcr.concurrent_program_id
     AND fcpt.LANGUAGE = USERENV ('LANG')
     AND fcr.phase_code = fl_pend.lookup_code
     AND fl_pend.lookup_type = 'CP_PHASE_CODE'
     AND fcr.status_code = fl_stat.lookup_code
     AND fl_stat.lookup_type = 'CP_STATUS_CODE'
     AND fl_pend.meaning != 'Completed'
     AND fu.user_id = fcr.requested_by
ORDER BY fcr.request_id DESC    
========================================
Profile Value at any Level

========================================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

=============================================
Which User is Locking the table
=============================================SELECT c.owner, c.object_name, c.object_type,
       fu.user_name locking_fnd_user_name,
       fl.start_time locking_fnd_user_login_time, vs.module, vs.machine,
       vs.osuser, vlocked.oracle_username, vs.SID, vp.pid,
       vp.spid AS os_process, vs.serial#, vs.status, vs.saddr, vs.audsid,
       vs.process
  FROM fnd_logins fl,
       fnd_user fu,
       v$locked_object vlocked,
       v$process vp,
       v$session vs,
       dba_objects c
 WHERE vs.SID = vlocked.session_id
   AND vlocked.object_id = c.object_id
   AND vs.paddr = vp.addr
   AND vp.spid = fl.process_spid(+)
   AND vp.pid = fl.pid(+)
   AND fl.user_id = fu.user_id(+)
   AND c.object_name LIKE '%' || UPPER ('&tabname_blank4all') || '%'
   AND NVL (vs.status, 'XX') != 'KILLED';

==========================================
Link Purchase Order and Requisition
=========================================SELECT prh.segment1 req_number, prh.authorization_status,
       prl.line_num req_line_num, prl.item_description req_item_description,
       prl.unit_price req_unit_price, prl.quantity req_quantity,
       pd.req_header_reference_num, pd.req_line_reference_num, pl.line_num,
       pl.item_description, pl.quantity, pl.amount, ph.segment1 po_number,
       prd.distribution_id, pd.req_distribution_id
  FROM po_requisition_headers_all prh,
       po_requisition_lines_all prl,
       po_req_distributions_all prd,
       po_distributions_all pd,
       po_line_locations_all pll,
       po_lines_all pl,
       po_headers_all ph
 WHERE prh.requisition_header_id = prl.requisition_header_id
   AND prh.org_id = prl.org_id
   AND prl.requisition_line_id = prd.requisition_line_id
   AND prl.org_id = prd.org_id
   AND prd.distribution_id = pd.req_distribution_id(+)
   AND prd.org_id = pd.org_id(+)
   AND pd.line_location_id = pll.line_location_id(+)
   AND pd.org_id = pll.org_id(+)
   AND pll.po_line_id = pl.po_line_id(+)
   AND pll.org_id = pl.org_id(+)
   AND pl.po_header_id = ph.po_header_id(+)
   AND pl.org_id = ph.org_id(+)

======================================================================
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 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 get the List of parameters for a Concurrent program in Oracle Applications
========================================================================
  SELECT p.user_concurrent_program_name "NAME"
       , c.concurrent_program_name "INTERNAL"
       , f.end_user_column_name "PARAMETER"
       , f.enabled_flag "ON_OFF"
       , f.DEFAULT_VALUE
       , f.required_flag
       , f.description
    FROM fnd_descr_flex_col_usage_vl f
       , fnd_concurrent_programs_tl p
       , fnd_concurrent_programs c
   WHERE SUBSTR( f.descriptive_flexfield_name
               , 7
               , 8 ) = c.concurrent_program_name
     AND c.concurrent_program_id = p.concurrent_program_id
     AND p.user_concurrent_program_name LIKE '%Purge Obsolete Workflow Runtime Data%'
     AND p.language = 'US'

ORDER BY f.descriptive_flexfield_name, f.column_seq_num;

=================================================================
Query to find the Trace file path for Concurrent Program
=================================================================
SELECT 'Request id: ' || request_id
     , 'Trace id: ' || oracle_process_id
     , 'Trace Flag: ' || req.enable_trace
     , 'Trace Name:  
' ||   dest.VALUE || '/' || LOWER (dbnm.VALUE) || '_ora_' || oracle_process_id || '.trc'
     , 'Prog. Name: ' || prog.user_concurrent_program_name
     , 'File Name: ' || execname.execution_file_name || execname.subroutine_name
     , 'Status : ' || DECODE (phase_code
                            , 'R', 'Running'
                             ) || '-' || DECODE (status_code
                                               , 'R', 'Normal'
                                                )
     , 'SID Serial: ' || ses.SID || ',' || ses.serial#
     , 'Module : ' || ses.module
  FROM fnd_concurrent_requests req
     , v$session ses
     , v$process proc
     , v$parameter dest
     , v$parameter dbnm
     , fnd_concurrent_programs_vl prog
     , fnd_executables execname
 WHERE req.request_id = :p_request_id
   AND req.oracle_process_id = proc.spid(+)
   AND proc.addr = ses.paddr(+)
   AND dest.NAME = 'user_dump_dest'
   AND dbnm.NAME = 'db_name'
   AND req.concurrent_program_id = prog.concurrent_program_id
   AND req.program_application_id = prog.application_id
   --- and prog.application_id = execname.application_id
   AND prog.executable_application_id = execname.application_id
   AND prog.executable_id = execname.executable_id;
   
   
SELECT request_id
     , TO_CHAR (request_date, 'DD-MON-YYYY HH24:MI:SS') request_date
     , TO_CHAR (requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start_date
     , 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 (SYSDATE, 'DD-MON-YYYY HH24:MI:SS') CURRENT_DATE
     , ROUND ((NVL (actual_completion_date, SYSDATE) - actual_start_date) * 24, 2) DURATION
  FROM fnd_concurrent_requests

 WHERE request_id = TO_NUMBER (:p_request_id);

How to Enable Personalization for OAF Pages

Set the values of following profiles to enable Personalization Page link in OAF Pages

Profile Name	Value
FND: Personalization Region Link Enabled	Yes
Personalize Self-Service Defn	Yes
Disable Self-Service Personal	No

Query to find Supplier, Sites and Contact Information - R12

SELECT DISTINCT asp.vendor_id     ,
  asp.segment1 AS supplier_number ,
  asp.vendor_name                 ,
  ass.vendor_site_code            ,
  hou.name                        ,
  ass.address_line1               ,
  ass.city                        ,
  ass.state                       ,
  ass.zip                         ,
  ass.country                     ,
  ass.phone                       ,
  person.person_first_name        ,
  person.person_last_name         ,
  pty_rel.primary_phone_number    ,
  (SELECT hcp5.phone_area_code
     FROM hz_contact_points HCP5
    WHERE HCP5.owner_table_name = 'HZ_PARTIES'
  AND APSC.REL_PARTY_ID         = HCP5.OWNER_TABLE_ID
  AND HCP5.CONTACT_POINT_TYPE   = 'PHONE'
  AND HCP5.PHONE_LINE_TYPE      = 'FAX'
  AND HCP5.PRIMARY_FLAG         = 'N'
  AND ROWNUM                    < 2
  ) FAX_AREA_CODE,
  (SELECT HCP6.PHONE_NUMBER
     FROM HZ_CONTACT_POINTS HCP6
    WHERE HCP6.OWNER_TABLE_NAME = 'HZ_PARTIES'
  AND APSC.REL_PARTY_ID         = HCP6.OWNER_TABLE_ID
  AND HCP6.CONTACT_POINT_TYPE   = 'FAX'
  AND HCP6.PRIMARY_FLAG         = 'N'
  AND ROWNUM                    < 2
  ) FAX,
  pty_rel.EMAIL_ADDRESS
   FROM AP_SUPPLIERS ASP   ,
  AP_SUPPLIER_SITES_ALL ASS,
  AP_SUPPLIER_CONTACTS APSC,
  hz_parties person        ,
  hz_parties pty_rel       ,
  HR_ALL_ORGANIZATION_UNITS hou
  WHERE 1                  =1
AND ass.vendor_id          = asp.vendor_id
AND apsc.per_party_id      = person.party_id
AND apsc.rel_party_id      = pty_rel.party_id
AND ass.org_id             = hou.organization_id
AND apsc.org_party_site_id = ass.party_site_id
AND hou.name               = 'ABCD'
ORDER BY HOU.NAME;

How to find APIs?

Its difficult for a developer to find out APIs from DBA_OBJECTS table, so Oracle Corp. has come up with a web portal named Oracle Integration Repository (iRep) to access all public APIs.

Oracle® Integration Repository

Oracle Integration Repository is a compilation of information about the numerous interface endpoints exposed by Oracle applications. The full list of public APIs and the purpose of each API is available in the integration repository. It provides a complete catalog of Oracle E-Business Suite’s business interfaces, and a comprehensive view of the interface mechanisms available. You can use this tool to easily discover and deploy the appropriate business interface from the catalog for integration with any system, application, or business partner.

Website URL: http://www.irep.oracle.com/

Release 12 Integration Repository

In Oracle EBS R12, Integration Repository is bundled as a part of EBS suite as a responsibility “Integrated SOA Gateway”.

Where are these APIs Stored?

Each API is placed in a file with extension as ‘pls'(pl/sql source code file). These pls files are stored in Unix box in the following path

$APPLICATION_TOP/patch/115/sql/

Query to find APIs

You can use the below query to find out the APIs to some extent

SELECT SUBSTR(a.OWNER,1,20) , SUBSTR(a.NAME,1,30) , SUBSTR(a.TYPE,1,20) , SUBSTR(u.status,1,10) Stat , u.last_ddl_time , SUBSTR(text,1,80) Description FROM dba_source a, dba_objects u WHERE 2 =2 AND u.object_name = a.name AND a.text LIKE '%Header%' AND a.type = u.object_type AND a.name LIKE 'PA_%API%' ORDER BY a.owner, a.name;

Here PA is application short name of Project Accounting application

Thanks http://oracleappsdna.com/

Where do I find info about Oracle EBS Public APIs?

Where do I find info about Oracle EBS Public APIs?

This is a question that I get multiple times each week in my role at API Wizard (a software product that leverages Oracle APIs).  

Oracle has make a huge investment, starting in Release 11 and continuing into each version of R12, in public APIs which allow you to perform nearly all of the same business processes that you can do with the Oracle forms.  You can create and update master data (customers, vendors, people, items, etc), transactional data (invoices, payments, orders, shipments, material transactions, etc), and configuration data (banks, value set values, etc).

Sounds great... and it is.  

But the question remains; how do you get a comprehensive list of available public APIs for Oracle EBS?

At a high-level, there are 4 main sources:

1) The Oracle Integration Repository
2) Oracle documenation
3) Metalink / My Oracle Support 
4) Web search

Let's break these down:

1) The Oracle Integration Repository

This is a resource that is part of every R12 system.  You access it using the seeded 'Integrated SOA Gateway' responsibility and choose the menu item 'Integration Repository'.  The Integration Repository contains about 70% of the Oracle APIs for whatever version of Oracle you're on, which doesn't sound great - but actually it's a huge accomplishment for Oracle as they have never had this information consolidated in one place before the integration repository.  

The Integration Repository provides a menu on the left broken out by module family and you can drill down to get to specific modules and then the public APIs or open interfaces available in those modules.  This is a great starting place -- but there are a few issues:

a) As mentioned above, if you don't find the API you're looking for here, it doesn't mean it doesn't exist, it just means it wasn't cataloged in the Integration Repository.
b) The Integration Repository in your environment will only list the APIs for the version of Oracle that you are running.  For example, if you are running 12.1.2 you'll see the APIs for version 12.1.2 but if you want to know if there is an API introduced in 12.1.3 (new APIs are added with each patchset), you can't determine that in your Integration Respository.
c) This is only available in R12.  The integration repository is not available in your R11 system.
d) The integration repository identifies the API and provides some information about the API parameters and a description of business process but it is not very detailed.

Oracle does maintain a version of the integration repository on their website at irep.oracle.com.  It used to contain R11 info and then was upgraded to R12 info but has been offline for the last few weeks.  It would be great if they would get that back online and provide the ability to search by version...  I am not holding my breath.

2) Oracle documenation (docs.oracle.com)

Oracle generates tons of documentation.  Each module mimimally has a user guide, an implementation guide, and a technical reference manual.  Some modules also have documents focusing solely on APIs and open interfaces, others embed this information into their other documents, and some don't document them at all.

It can be tedious work going through Oracle's documentation but worth when you find the guide that you need.  For the APIs and open interfaces which are documented, Oracle often provides sample scripts and talks about important concepts like environment initialization.

3) Metalink / My Oracle Support (support.oracle.com)

These days, good searching skills are often worth more than concrete knowledge.  There's a lot of information on metalink both in Oracle's notes/documents but also in Oracle forums, all of which you can search in one place.  I've found many an API this way.  Sometimes I get better results on google (see next bullet point) but sometimes Metalink is best.  

One thing to note is that if you have done your due dilligence but can't identfy an public API or open interface, you can raise an SR with Oracle and ask them if one exists.  Not only can they help you find it but if there is not, then can tell you if anyone has made an enhancement request for that API and you can ask them to add you to the list of customers requesting it.  That's one factor in how Oracle prioritizes its development initiatives.  

4) Web search

Not surprisingly, sometimes you get better information doing a web seach on google, bing, etc.   Actually, I often start here because if there are results I find them very quickly.  There are many forum questions (is there an Oracle EBS API for .....?) and there are many good blogs which describe working with these APIS.

~Thanks RobLepanto 

Tips for Oracle SQL Query Writing and Performance Tuning

I am working with Oracle SQL from day when I joined IT industry and I have been fascinated by number of things I have learned about SQL, different databases and its usage over the years.

In early phase of my career, I learned that writing SQL queries was not just about writing but writing so it can run efficiently. Performance of SQL queries are critical to any application that is built to use Oracle and I say Oracle because this blog is specifically for Oracle SQL.

The following is a list of some tips which can be used as guideline to write and review custom SQL queries. This is by no means an exhaustive list to get the best tuning results but can serve as a ready reference to avoid the common pitfalls while working with Oracle SQL:

1. Do not use the set operator UNION if the objective can be achieved through an UNION ALL. UNION incurs an extra sort operation which can be avoided.

2. Select ONLY those columns in a query which are required. Extra columns which are not actually used, incur more I/O on the database and increase network traffic.

3. Do not use the keyword DISTINCT if the objective can be achieved otherwise. DISTINCT incurs an extra sort operation and therefore slows your queries down.

4. If it is required to use a composite index, try to use the “Leading” column in the “WHERE” clause. Though Index skip scan is possible, it incurs extra cost in creating virtual indexes and may not be always possible depending on the cardinality of the leading columns.

5. There should not be any Cartesian product in the query unless there is a definite requirement to do so. I know this is a silly point but we all have done this mistake at one point 

6. Wherever multiple tables are used, always refer to a column by either using an alias or using the fully qualified name. Do not leave the guess work for Oracle.

7. SQL statements should be formatted consistently (e.g the keywords should be in CAPS only) to aid readability. Now, this is not a performance tip really. However, it’s important and part of the practices.

8. If possible use bind variables instead of constant/literal values in the predicate filter conditions to reduce repeated parsing of the same statement.

9. Use meaningful aliases for tables/views

10. When writing sub-queries make use of the EXISTS operator where possible as Oracle knows that once a match has been found it can stop and avoid a full table scan (it does a SEMI JOIN).

11. If the selective predicate is in the sub query, then use IN.

12. If the selective predicate is in the parent query, then use EXISTS.

13. Do not modify indexed columns with functions such as RTRIM, TO_CHAR, UPPER, TRUNC as this will prevent the optimizer from identifying the index. If possible perform the modification on the constant side of the condition. If the indexed column is usually accessed through a function (e.g NVL), consider creating a function based index.

14. Try to use an index if less than 5% of the data needs to be accessed from a data set. The exception is a small table (a few hundred rows) which is usually best accessed through a FULL table scan irrespective of the percentage of data required.

15. Use equi-joins whenever possible, they improve SQL efficiency

16. Avoid the following kinds of complex expressions:

• NVL (col1,-999) = ….
• TO_DATE(), TO_NUMBER(), and so on

These expressions prevent the optimizer from assigning valid cardinality or selectivity estimates and can in turn affect the overall plan and the join method

17. It is always better to write separate SQL statements for different tasks, but if you must use one SQL statement, then you can make a very complex statement slightly less complex by using the UNION ALL operator

18. Joins to complex views are not recommended, particularly joins from one complex view to another. Often this results in the entire view being instantiated, and then the query is run against the view data

19. Querying from a view requires all tables from the view to be accessed for the data to be returned. If that is not required, then do not use the view. Instead, use the base table(s), or if necessary, define a new view.

20. While querying on a partitioned table try to use the partition key in the “WHERE” clause if possible. This will ensure partition pruning.

21. Consider using the PARALLEL hint (only when additional resources can be allocated) while accessing large data sets.

22. Avoid doing an ORDER BY on a large data set especially if the response time is important.

23. Consider changing the OPTIMIZER MODE to FIRST_ROWS(n) if the response time is important. The default is ALL_ROWS which gives better throughput.

24. Use CASE statements instead of DECODE (especially where nested DECODEs are involved) because they increase the readability of the query immensely.

25. Do not use HINTS unless the performance gains clear.

26. Check if the statistics for the objects used in the query are up to date. If not, use the DBMS_STATS package to collect the same.

27. It is always good to understand the data both functionally and it’s diversity and volume in order to tune the query. Selectivity (predicate) and Cardinality (skew) factors have a big impact on query plan. Use of Statistics and Histograms can drive the query towards a better plan.

28. Read explain plan and try to make largest restriction (filter) as the driving site for the query, followed by the next largest, this will minimize the time spent on I/O and execution in subsequent phases of the plan.

29. If Query requires quick response rather than good throughput is the objective, try to avoid sorts (group by, order by, etc.). For good throughput, optimizer mode should be set to ALL ROWS.

30. Queries tend to perform worse as they age due to volume increase, structural changes in the database and application, upgrades etc. Use Automatic Workload Repository (AWR) and Automatic Database Diagnostic Monitor (ADDM) to better understand change in execution plan and throughput of top queries over a period of time.

31. SQL Tuning Advisor and SQL Access Advisor can be used for system advice on tuning specific SQL and their join and access paths, however, advice generated by these tools may not be always applicable (point 28).

32. SQL Access paths for joins are an component determining query execution time. Hash Joins are preferable when 2 large tables need to be joined. Nested loops make work better when a large table is joined with a small table.

Disclaimer: Points listed above are only pointers and may not work under every circumstance. This check list can be used as a reference while fixing performance problems in the Oracle Database.

Suggested further readings

1. Materialized Views
2. Advanced Replication
3. Change Data Capture (Asynchronous)
4. Automatic Workload Repository (AWR) and Automatic Database Diagnostic Monitor (ADDM).
5. Partitioning strategies.

Now it’s turn if have any more tips which you have used then do add them in comment section… Your feedback is very valuable and it would be useful for other viewers too.