Concurrent Manager Queues

Here is the explanation how you can define concurrent managers and specify when a manager is enabled.

A concurrent manager is itself a concurrent program that starts other concurrent programs running. When an application user submits a request to run a program, the request is entered into a database table that lists all of the requests. Concurrent managers read requests from the table and start programs running. See: Concurrent Managers.

Next , we explain how to specify when a manager is enabled, how to use managers to balance your applications processing workload across different time periods, and how to associate a library of immediate concurrent programs to be called by your manager.

Defining new managers

You can define as many concurrent managers as you want. When you define a manager, you:

• Assign a predefined library of immediate concurrent programs to your manager.
  • Immediate concurrent programs are subroutines associated with concurrent managers. All other concurrent programs are spawned as independent processes at run time.
• Assign work shifts to your manager, which determines what days and times the manager works.
• For each work shift, you define the maximum number of operating system processes the manager can run concurrently to read requests (start programs) during the work shift.
• Specialize your manager to read only certain kinds of requests.

Program Libraries

For a program that is spawned, a concurrent manager initiates or spawns another operating system process. A program that is immediate runs as part of the concurrent manager's operating system process.

A program library contains immediate concurrent programs that can be called by your manager.

An immediate concurrent program must be registered with a program library. Application developers using Oracle Application Object Library can register concurrent programs with a program library.

The Oracle Application Object Library FNDLIBR program library contains Oracle Applications immediate concurrent programs, and is assigned to the Standard concurrent manager. In most cases, you will include the FNDLIBR library with your manager's definition.

The Concurrent Manager Managing request

A typical Concurrent manager is managing request in typical the flow which is described below.

And if Concurrent manager is having work shift set up , one the top of above process, the best can be described as:

How many Concurrent Managers available in oracle Apps

Similar to other products in there are seeded, Transaction and Other Concurrent Managers available. The Creation of custom manager is also possible , I am unable to throw some light, as i hadn't exposed to setting up of these area . In a typical implementation , it would be taken care by DBA's , and you can find some documents in system admin user guide for further details.Here , I am going to list of the concurrent manager which are used for some of processing within the product.Lets start to explore options for managers in EBS Suite:

The Seeded Concurrent Managers
Oracle seeds the Internal Manager and Conflict Manager with every implementation. Here are the list of some of them:

• Internal Manager

The internal manager is the highest level manager that controls all the other managers

• Conflict Resolution Manager

Resolves request conflicts. When programs are defined with incompatibilities with other programs, both programs will show up in the Conflict Resolution Manager if they are both trying to run at the same time. Programs that are submitted but have no place to run because of incorrect setup choices for the concurrent managers will also show up under the Conflict Resolution Manager.

The Transaction Managers

• Inventory Manager
  • Runs Inventory Programs. As seeded, the Inventory Manager has a number of individual programs assigned to it
• MRP Manager
  • Runs the Planning Manager. The MRP Manager is seeded by Oracle to only run the Planning Manager program. You may consider assigning a small number of other concurrent requests, including the MRP processes, to this manager if you are a heavy manufacturing user, to pull these critical processes out from under the Standard Manager.
• PA Streamline Manager
• Standard Manager
  • The general queue for handling requests. The Standard Manager was set up to run all concurrent requests.
  
  HAPPY LEARNING!
  

Oracle Applications Tablespace Model - OATM

The Oracle Applications Tablespace Model was another long awaited feature that got introduced in 11.5.10.Prior to 11.5.10 by default each of the oracle applications product would have two dedicated tablespace holding the data element and the other for storing the index eg GLD (For General Ledger base tables) and GLX (For indexes relation to the General Ledger product).This easily resulted in some 300 odd tablespaces to manage apart from the system, temp and the rollback tablespaces.

In the new Oracle Applications Tablespace Model (OATM) all these product related tablespaces have been consolidated in two main tablespaces one for holding the base tables and the other for holding the related indexes. Apart from these two tablespace you have an additional ten tablespaces including system tablespace undo tablespace and the temporary tablespace. Thereby reducing the total number of tablespace in the OATM to twelve.

Apart from the obvious ease of management and administration with a reduced number of tablespace being involved the OATM also provides benefits like efficient space utilization. This is achieved by supporting locally managed tablespaces as opposed to the dictionary managed tablespace in the previous model.

OATM also supports uniform extent allocation and auto allocate extent management. In uniform extent management all the extents have the same size and result in less fragmentation. Auto allocate extent management allows the system to determine the extent sizes automatically.

OATM also provides additional benefits when implementing Real Application Clusters (RAC) in Oracle Applications.

Under the OATM the following twelve tablespaces are created as a default.
• APPS_TS_TX_DATA - This tablespace hold the translational tables of all Oracle Applications products. For example the GL_JE_HEADERS will be a part of APPS_TX_DATA.
• APPS_TS_TX_IDX - All the indexes on the product tables are kept under this tablespace.
• APPS_TS_SEED - The seeded data that is setup and reference data tables and indexes form this tablespace. For example your FND_DATABASES table would reside in the APPS_TS_SEED tablespace.
• APPS_TS_INTERFACE - All the interface tables are kept in this tablespace for example the GL_INTERFACE table.
• APPS_TS_SUMMARY - All objects that record summary information are grouped under this tablespace.
• APPS_TS_NOLOGGING - This tablespace contains the materialized views that are not used for summary purposes and other temporary
object that do not require redo log entries.
• APPS_TS_QUEUES - With the support for advanced queuing in Oracle Applications, the advanced queue tables and related objects form a part of this tablespace.
• APPS_TS_MEDIA - This tablespace holds multimedia objects like graphics sound recordings and spital data.
• APPS_TS_ARCHIVE - Tables that are obsolete in the current release of Oracle Applications 11i are stored here. These tables are preserved to maintain backward compatibility of custom programs or migration scripts.
• UNDO - The undo tablespace is used as automatic undo management is enabled by default in 11.5.10.This acts as a replacement to red log files.
• TEMP - The Temp tablespace is the default temporary tablespace for Oracle Applications.
• SYSTEM - This is the SYSTEM tablespace used by the Oracle Database.

For all new installation of 11.5.10 OATM is available as a default install. For prior applications system you can migrate your existing tables spaces to the oracle applications tablespace model.

For this purpose you have the Tablespace Migration Utility(TMU) which is available as a patch (3381489).

The TMU is a Perl based interactive utility that helps in migrating the oracle applications schemas to implement the OATM.

For additional information refer to the Oracle Applications Tablespace Migration Utility User Documentation.

HAPPY LEARNING!

E-Business Suite Timeout Parameters and Profiles

An unattended PC without the screen locked poses a security risk. Likewise, an unattended or long running E-Business Suite user session can also pose a risk. The E-Business Suite provides many configuration parameters and profile settings to control user sessions. I recommend reviewing these against your existing corporate policies and setting them according to our recommendations after testing their impact. The following sections describe those items that I recommend setting.

• ICX Timeout Profile Values

The following E-Business Suite profile options control screen timeouts for Forms, as well as Self Service sessions. Again, please note, some of the ICX profiles also control Forms Session timeouts! This can be confusing since Inter-Cartridge Exchange (ICX) is often associated with Self Service applications. This is no longer the case since the release of Framework for the ICX Profiles control the timeout functionality.

Parameter	Default	Recommendation
ICX:Session Timeout	None	30 (minutes)
ICX: Limit Time	4 (hours)	4 (hours)
ICX: Limit Connect	1000	2000

· ICX:Session Timeout - This profile option determines the length of time (in minutes) of inactivity in a user's form session before the session is disabled. Note that disabled does not mean terminated or killed. The user is provided the opportunity to re-authenticate and re-enable their timed-out session. If the re-authentication is successful, the disabled session is re-enabled and no work is lost. Otherwise, the session is terminated without saving pending work. This functionality is available via Patch 2012308 (included in 11.5.7, FND.E). Note: Setting the profile value to greater than 30 minutes can drain the JVM resources and cause ‘out of memory’ errors.

· ICX: Limit time - This profile option defines the maximum connection time for a connection – regardless of user activity. If 'ICX:Session Timeout' is set to NULL, then the session will last only as long as 'ICX: Limit Time', regardless of user activity.

· ICX: Limit connect - This profile option defines the maximum number of connection requests a user can make in a single session. Note that other EBS internal checks will generate connection requests during a user session, so it is not just user activity that can increment the count.

§ CRM Application Timeout Profile Values

CRM applications use the afore-mentioned ICX timeout profiles (ICX:Session Timeout, ICX: Limit Time, and ICX: Limit Connect), but additionally, CRM also utilizes the JTF_INACTIVE_SESSION_TIMEOUT profile option.

Parameter	Default	Recommendation
JTF_INACTIVE_SESSION_TIMEOUT	None	30 (minutes)

JTF_INACTIVE_SESSION_TIMEOUT - This profile option affects CRM-based products only, and serves the same purpose as the ICX:Session Timeout profile. This profile option exists for legacy reasons, and its value should be set the same as ICX:Session Timeout.

• Jserv (Java) Timeout Settings
  
  

Parameter	Recommendation
disco4iviewer.properties:session.timeout	5400000 (milliseconds)
formservlet.ini:FORMS60_TIMEOUT	55 (minutes)
formservlet.properties:session.timeout	5400000 (milliseconds)
jserv.conf:ApJServVMTimeout	360 (seconds)
mobile.properties:session.timeout	5400000 (milliseconds)
zone.properties:session.timeout	5400000 (milliseconds)
zone.properties:servlet.framework.initArgs	5400000 (milliseconds)

These settings are located at: ../*ora/iAS/Apache/Jserv/etc

JServ Timeout is specified by the value of the property session.timeout in the JServ configuration file zone.properties, and represents the number of milliseconds to wait before ending an idle JServ session (the default is 30 minutes). This timeout is used by products based on Oracle Applications Framework (OAF).

• Apache HTTP Timeout Settings

The following parameter settings control timeout behavior within Apache.

Parameter	Recommendation
httpd.conf:Timeout	300 (seconds)
httpd.conf:KeepAliveTimeout	15 (seconds)
httpd.conf:SSLSessionCacheTimeout	300 (seconds)

These settings are located: ../*ora/iAS/Apache/Apache/conf

• Forms 60 Environment Timeout Variables

The following parameter settings control timeout behavior within Oracle Forms.

Parameter	Recommendation
FORMS60_TIMEOUT	55 (minutes)
FORMS60_CATCHTERM	0

You should modify the APPL_TOP/.env setting to include the following settings:

FORMS60_CATCHTERM=0
FORMS60_TIMEOUT=55 (minutes)

I recommend using a timeout value of 55 because it is less than the 60 minute value recommended for the web apache timeout values. Note that these values may vary depending on security policies.

• Oracle Single Sign-On Server Timeouts

The following parameter setting controls timeout behavior within Oracle Single Sign-On.

‘Single Sign-On Session Duration’ represents the number of hours a user can be logged in to the server without being timed out and having to log in again. This timeout value can be specified from the "Edit SSO Server Configuration" link on the SSO Server Administration page. When a user logs in to Release 11i via the Single Sign-On Server, an SSO login session is created and remains valid for the duration specified by this setting.

If someone ask Apps DBA to change Session Idle Time out value How & where will you change ?
In order to answer first you have to understand what kind of seesions are in Apps 11i and what is Idle timeout ?
In Apps there are two broad categories of session
- Self Service Application Session ( Server by web server Apache & Jserv, like iRecruitment, iProcurement)
-Forms session ( served by your form session, like system Administrator)

What is Session Idle time ?
If Oracle Apps client is not doing any activity for some time (when application user goes for coffee or talks over phone) session during that time is called as Idle Session & because of security reason, performance issues and to free up system resource Oracle Applications terminates client session( both forms & self service) after idle time value is reached to the one mentioned in configuration file.

From FND.G or 11.5.9 or with introduction of AppsLocalLogin.jsp to enter into application, profile option "ICX Session Timeout" is used only to determine Forms Session Idle timeout value . This might be confusing as earlier this profile option used to control forms as well as self service application(with session.timeout) session.timeout is used to control Idle session timeout for Self Service Applications ( Served by Jserv via JVM )

From where ICX : Session Timeout & session.timeout get values ?

Autoconfig determines value for profile option "ICX: Session Timeout" and "session.timeout" from entry in context file ( $APPL_TOP/admin/SID_hostname.xml ) with parameter s_sesstimeout where value mentioned is in milliseconds so profile option ICX: Session Timeout value should be s_sesstimeout/ (1000 * 60) which means here its 10 Minutes. This value is also set in zone.properties in $IAS_ORACLE_HOME/Apache/Jserv where number mentioned is in milli second i.e. 600000 ( equal to 10 Minutes)session.timeout = 600000

session.timeout mentioned in zone.properties is in milli secondsICX Session Time out mentioned in profile option ICX: Session Timeout is in minutes so ICX session timeout=30 & session.timeout= 1800,000 are same 30 minutes

P.S. ICX Session time out was introduced in FND.D so if your FND version is below D you might not see this variable.

Important Things Apps DBA should consider while setting session timeout value ?
1.. If you keep session.timeout value too high , when some oracle application user accessing Self service application terminates his session, so longer idle session will drain JVM resource & can result in Java.Lang No Memory available issues .
2. If you keep it too low, users going out for tea or sitting idle for some time have to login again into application & can be annoying .
Thumb rule is session time out usually set to 30 minutes.

HAPPY LEARNING!

Gathering Stats for Workflow’s Queue Tables

Statistics gathering is an important activity in order to enable the cost based optimizer to take a decision on the execution plan to choose from the various possible ones with which a particular SQL statement can be run. It is recommended to gather the statistics for an object when it is in steady state, i.e. when the corresponding tables have a fair amount of data giving out statistics which would lead the best execution plan to be picked by CBO.

From 10g onwards , Oracle database introduced Automatic Optimizer Statistics Collection feature in which a scheduled job GATHER_STATS_JOB runs in the nights and gathers stats for tables with either empty or stale statistics using DBMS_STATS package APIs. This indeed is a great feature but for highly volatile tables such as AQ/Streams tables it is quite possible that when the stats collection job runs, these tables may not have the data that is representative of their full load period. One scenario can be stats collection on queue tables when they were empty, and the optimizer, as a result, choosing poor execution plans when they have a lot of data in them during normal workload hours.

As a direct fix to tackle the above issue, from 10gR2 database onwards, AQ table stats are getting locked immediately after its creation so that the auto stats collector doesn't change it in the future. This can be confirmed as below:

SQL> CREATE TYPE event_msg_type AS OBJECT ( name VARCHAR2(10), current_status NUMBER(5), next_status NUMBER(5) ); Type created. --Create Queue Table SQL> EXEC DBMS_AQADM.create_queue_table( queue_table=>'test_queue_tab', queue_payload_type=>'event_msg_type') PL/SQL procedure successfully completed. --Create Queue SQL> EXEC DBMS_AQADM.create_queue(queue_name =>'test_queue', queue_table=>'test_queue_tab'); PL/SQL procedure successfully completed. --Check for locks on stats SQL> select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null and owner='APPS' and table_name='TEST_QUEUE_TAB'; OWNER TABLE_NAME STAT ---------- ---------------- ----- APPS TEST_QUEUE_TAB ALL --Gather stats SQL> EXEC dbms_stats.gather_table_stats('APPS','TEST_QUEUE_TAB') BEGIN dbms_stats.gather_table_stats('APPS','TEST_QUEUE_TAB'); END; * ERROR at line 1: ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at "SYS.DBMS_STATS", line 18408 ORA-06512: at "SYS.DBMS_STATS", line 18429 ORA-06512: at line 1

As we can see above , queue table stats are locked and hence further stats gathering fails. We can still go ahead and use force parameter provided in DBMS_STATS package to override any lock on statistics:

SQL>exec dbms_stats.gather_table_stats('APPS','TEST_QUEUE_TAB' ,force=>TRUE) PL/SQL procedure successfully completed.

The recommended use is to gather statistics with a representative queue message load and keep the stats locked to avoid getting picked up by auto stats collector.

What happens in Apps Instance?

From Oracle Apps 11i (on top of Oracle Database 10gR2) instance onwards, the automatic statistics gathering job GATHER_STATS_JOB is disabled using $APPL_TOP/admin/adstats.sql , as can be confirmed below:

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = 'GATHER_STATS_JOB'; JOB_NAME ENABLE ------------------------------ ------ GATHER_STATS_JOB FALSE

Oracle Apps provides separate concurrent programs which make use of procedures in FND_STATS package to gather statistics for apps database objects. FND_STATS is basically a wrapper around DBMS_STATS and is recommended by Oracle for stats collection in Oracle Apps environment because of the flexibility it provides in identifying the objects with empty and stale stats. The modification threshold ( % of rows used to estimate) is fixed to 10% in DBMS_STATS while it can vary from 0 to 100% incase of FND_STATS.

But FND_STATS doesn't provide the force parameter to override any lock on statistics and hence fails while gathering statistics for a queue table whose stats are locked :

SQL> select owner, table_name, stattype_locked ,last_analyzed 2 from dba_tab_statistics where table_name='WF_DEFERRED' 3 and owner='APPLSYS'; OWNER TABLE_NAME STATT LAST_ANAL -------------- --------------- ----- --------- APPLSYS WF_DEFERRED ALL 23-FEB-09 SQL> exec fnd_stats.gather_table_stats('APPLSYS','WF_DEFERRED'); BEGIN fnd_stats.gather_table_stats('APPLSYS','WF_DEFERRED'); END; * ERROR at line 1: ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at "APPS.FND_STATS", line 1505 ORA-06512: at line 1

The same error is observed on running the Gather Table Statistics concurrent program for such queue tables. Gather Schema Statistics concurrent program in general skips the table incase any error is encountered while gathering its stats and hence the queue tables with locked stats are also skipped.

Hence currently , in order to carry out stat collection for Workflow queue tables whose stats are locked,the approach should be to unlock the queue table temporarily using DBMS_STAT’s Unlock APIs when the table have a representative load for the correct stats to be gathered, gather the statistics using FND_STATS and lock it again.

SQL> begin 2 dbms_stats.unlock_table_stats('APPLSYS','WF_DEFERRED'); 3 fnd_stats.gather_table_stats('APPLSYS','WF_DEFERRED'); 4 dbms_stats.lock_table_stats('APPLSYS','WF_DEFERRED'); 5 end; 6 / PL/SQL procedure successfully completed. SQL> select owner, table_name, stattype_locked ,last_analyzed 2 from dba_tab_statistics where table_name='WF_DEFERRED' 3 and owner='APPLSYS'; OWNER TABLE_NAME STATT LAST_ANALYZED ---------------- ---------------- ----- ------------- APPLSYS WF_DEFERRED ALL 12-APR-09

Another approach can be to unlock the WF queue table stats during the adpatch run itself as Oracle’s automatic stats collection feature is disabled in Apps environments and the stats collection in general occurs through the Apps provided concurrent programs. What say?

HAPPY LEARNING!

FND PROFILES Scripts

Responsibilities Listing
Purpose	To get list of responsibilities.
Description	Query useful when user wants to get application wise responsibility list
Parameters	None
Query	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;

Menus Listing
Purpose	To get Menus Associated with responsibility
Description	User to check menu attached with a reponsilblity
Parameters	responsibility_id Which user can get from query of section Responsibilities Listing
Query	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';

Submenu And Function Listing
Purpose	To get submenus and Function attached to this Main menu.
Description	By using this query user can check function and submenus attached to that specific menu
Parameters	User_menu_name Which user can get from query of section Menu Listing
Query	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';

User And Assigned Responsibility Listing
Purpose	To get assigned responsibility to a user.
Description	User wants to check responsibility attached to a specific user
Parameters	None
Query	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);

Responsibility And Assigned Request Group Listing
Purpose	To get responsibility and attached request groups.
Description	Every responsibility contains a request group(request group is basis of submitting requests)
Parameters	None
Query	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

Profile Option With Modification Date and User
Purpose	To get modified profile options.
Description	Query used for audit point of view i.e. when a profile is changed and by whom user
Parameters	None
Query	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;

Forms Personalization Listing
Purpose	To get modified profile options.
Description	Personalization is feature available in 11.5.10.X. For More detail on form Personalization Use Following Tables (Rule_id) is reference key for these tables applsys.fnd_form_custom_actions, applsys.fnd_form_custom_scopes
Parameters	None
Query	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;

Patch Level Listing
Purpose	To get Patch Level.
Description	Query used to view the patch level status of all modules
Parameters	None
Query	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;

Function Listing
Purpose	To get all Functions
Description	Complete forms and functions
Parameters	None
Query	SELECT function_id, user_function_name, creation_date, description FROM applsys.fnd_form_functions_tl order by order by user_function_name;

Request Attached To Responsibility Listing
Purpose	To get all Request attached to a responsibility
Description	View all request who have attached to a reponsiblity
Parameters	None
Query	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; /;

Request Listing Application Wise
Purpose	To get all request with application
Description	View all types of request Application wise
Parameters	None
Query	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

Count Module Wise Reports
Purpose	To Count Module Wise Report
Description	Application wise request counting
Parameters	None
Query	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;

Request Status Listing
Purpose	To calculate request time
Description	This query will shows report processing time
Parameters	None
Query	SELECT f.request_id , pt.user_concurrent_program_name user_concurrent_program_name , 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(p.concurrent_program_name,'ALECDC',p.concurrent_program_name||'['||f.description||']',p.concurrent_program_name) concurrent_program_name , decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase , f.status_code FROM apps.fnd_concurrent_programs p , apps.fnd_concurrent_programs_tl pt , apps.fnd_concurrent_requests f WHERE 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 ORDER by f.actual_completion_date-f.actual_start_date desc;

User And Responsibility Listing
Purpose	Check responsibility assigned to a specific USER
Description
Parameters	None
Query	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)

Applied Patch Listing
Purpose	Check Current Applied Patch
Description
Parameters	None
Query	SELECT patch_name, patch_type, maint_pack_level, creation_date FROM applsys.ad_applied_patches ORDER BY creation_date DESC

HAPPY LEARNING!