Thursday, August 23, 2012

Application object Library Queries


1. Query To get list of responsibilities.
========================================================================
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
===============================================================================
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'))  --Fujitsu Shipping Appointment Log Form,Fujitsu UOM Calculator

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

No comments:

Post a Comment