Wednesday, August 21, 2013

Usefull AOL Queries



--> custom concurrent objects
SELECT   fp.application_name application_owner,
         fl.meaning TYPE,
         fcp.user_concurrent_program_name concurrent_request_name,
         fc.concurrent_program_name code,
         -- fc.EXECUTION_METHOD_CODE Type,
         fe.executable_name,
         fe.execution_file_name,
         fc.enabled_flag status
    FROM apps.fnd_concurrent_programs_tl fcp,
         apps.fnd_concurrent_programs fc,
         -- apps.fnd_request_group_units frgu,
         --apps.fnd_request_groups frg,
         apps.fnd_application_tl fp,
         apps.fnd_executables fe,
         apps.fnd_lookups fl
   WHERE 1 = 1
     -- ANDfrgu.request_unit_id =fcp.concurrent_program_id
     -- andfp.application_id= fc.application_id
     AND fp.application_id = fcp.application_id
     -- AND frgu.request_unit_id =fc.concurrent_program_id
     -- AND frgu.request_group_id = frg.request_group_id
     AND fc.concurrent_program_id = fcp.concurrent_program_id
     AND UPPER (fl.lookup_type) LIKE 'CP_EXECUTION_METHOD_CODE'
     AND fc.enabled_flag NOT IN ('N')
     AND fe.executable_id = fc.executable_id
     AND fl.lookup_code = fe.execution_method_code
     AND UPPER (fc.concurrent_program_name) LIKE 'XX%'
GROUP BY fp.application_name,
         fl.meaning,
         fcp.user_concurrent_program_name,
         fc.concurrent_program_name,
         fc.enabled_flag,
         fe.executable_name,
         fe.execution_file_name
        -- frt.RESPONSIBILITY_NAME,
        -- fc.APPLICATION_ID,
        -- fc.EXECUTION_METHOD_CODE
ORDER BY fp.application_name,
         fl.meaning,
         user_concurrent_program_name;


--> custom DB objects
SELECT   owner,
         object_type,
         object_name
    FROM all_objects
   WHERE (UPPER (object_name) LIKE 'XX%' OR UPPER (object_name) LIKE 'SRI%')
GROUP BY owner,
         object_type,
         object_name
ORDER BY owner,
         object_type,
         object_name;

--> custom reports
SELECT   fcp.user_concurrent_program_name concurrent_request_name,
         fc.concurrent_program_name code,
         -- fc.EXECUTION_METHOD_CODE Type,
         fl.meaning TYPE,
         fe.executable_name,
         fe.execution_file_name,
         fp.application_name application_owner,
         fc.enabled_flag status
    FROM apps.fnd_concurrent_programs_tl fcp,
         apps.fnd_concurrent_programs fc,
         -- apps.fnd_request_group_units frgu,
         --apps.fnd_request_groups frg,
         apps.fnd_application_tl fp,
         apps.fnd_executables fe,
         apps.fnd_lookups fl
   WHERE 1 = 1
     --  AND  frgu.request_unit_id =fcp.concurrent_program_id
     --   and  fp.application_id= fc.application_id
     AND fp.application_id = fcp.application_id
     --  AND frgu.request_unit_id =fc.concurrent_program_id
     -- AND frgu.request_group_id = frg.request_group_id
     AND fc.concurrent_program_id = fcp.concurrent_program_id
     AND UPPER (fl.lookup_type) LIKE 'CP_EXECUTION_METHOD_CODE'
     AND fc.enabled_flag NOT IN ('N')
     AND fl.meaning IN ('Oracle Reports')
     AND fe.executable_id = fc.executable_id
     AND fl.lookup_code = fe.execution_method_code
     AND fc.concurrent_program_name LIKE 'XX%'
GROUP BY fcp.user_concurrent_program_name,
         fc.concurrent_program_name,
         fc.enabled_flag,
         fl.meaning,
         fe.executable_name,
         fe.execution_file_name,
         -- frt.RESPONSIBILITY_NAME,
         -- fc.APPLICATION_ID,
         fp.application_name
         -- fc.EXECUTION_METHOD_CODE
ORDER BY user_concurrent_program_name;



--> custom index
SELECT   *
    FROM dba_indexes
   WHERE index_name LIKE 'XX%'
ORDER BY index_name;


--> custom menus
SELECT   fm.menu_name,
         fm.user_menu_name,
         fm.description
    FROM fnd_menus_vl fm
   WHERE 1 = 1
     AND (UPPER (fm.menu_name) LIKE 'XX%' OR UPPER (fm.menu_name) LIKE 'SRI%')
ORDER BY fm.menu_name;

--> custom forms
SELECT a.application_name,
       b.application_short_name,
       f.form_name,
       d.user_form_name
  FROM applsys.fnd_form f,
       applsys.fnd_application_tl a,
       applsys.fnd_application b,
       applsys.fnd_form_tl d
 WHERE f.application_id = a.application_id
   AND d.form_id = f.form_id
   AND a.application_id = b.application_id
   AND (UPPER (f.form_name) LIKE 'XX%' OR UPPER (f.form_name) LIKE 'SRI%')
   --ANDb.application_short_name LIKE UPPER('PA') ORDER BY 1,2,3,4;"
  

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect