Simple steps to Start/Stop Weblogic services

              Simple steps to Start/Stop Weblogic services

In order to Stop/Start Weblogic server for a complete bounce follow the below given high level steps:

Steps to Stop:

1. Stop Managed Servers

     cd <domain home>/<domain name>/bin

    ./stopManagedWebLogic.sh  <Server name> URL

2. Stop OPMN service

    cd <instance home>/bin

     ./opmnctl status

     ./opmnctl stopall

3. Kill the node manager

     ps -ef|grep weblogic.Node

     kill -9 the nodemanager process.

4. Stop the WebLogic service

     cd <domain home>/<domain name>/bin

      ./stopWebLogic.sh



 Steps to Start:



1.  Start the WebLogic service

       cd <domain home>/<domain name>/bin

       nohup ./startWebLogic.sh &

2.Start the Node Manager

        cd <wl_server home>/bin

       nohup ./startNodeManager.sh &

3.  Start OPMN service

        cd <instance home>/bin

           ./opmnctl status

           ./opmnctl startall

            ./opmnctl status

4. Start Managed Servers

       cd <domain home>/<domain name>/bin

       nohup ./startWebLogic.sh &

Adding Indirect responsibilities

How to Add Indirect Responsibilities

Requesting access:

Approvals Management should never be assigned directly.  It is inherited by a role.

These are good examples of role names that are usually used :

AME PO Viewer Role

AME PO Admin Role

AME HRSS Viewer Role

AME HRSS Admin Role

AME OPM Viewer Role

AME OPM Admin Role

Granting access:

Log on as SYSADMIN

Navigate to User Management->Users

1.      Enter in User Name you wish to update

2.       Click on Go to retrieve results

3.       Click on Update

4.       Click on Assign Roles

Enter in role name from the above list that is required

Log file locations 11i

 Log File Locations in EBS 11i

Database Tier Logs are

Alert Log File location:
$ORACLE_HOME/admin/$CONTEXT_NAME/bdump/alert_$SID.log


Trace file location:
$ORACLE_HOME/admin/$CONTEXT_NAME/udump

Database Tier:
$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/DDMMTime/adconfig.log

Application Tier Logs


Start/Stop script log files location:
$COMMON_TOP/admin/log/$CONTEXT_NAME/


Apache, Jserv, JVM log files locations:
$IAS_ORACLE_HOME/Apache/Apache/logs/ssl_engine_log
$IAS_ORACLE_HOME/Apache/Apache/logs/ssl_request_log
$IAS_ORACLE_HOME/Apache/Apache/logs/access_log
$IAS_ORACLE_HOME/Apache/Apache/logs/error_log
$IAS_ORACLE_HOME/Apache/JServ/logs


Concurrent log file location:
$APPLCSF  or $/$APPLLOG( if $APPLCSF is unset)


Patch log file location:
$APPL_TOP/admin/$TWO_TASK/log


Worker Log file location:
$APPL_TOP/admin/$TWO_TASK/log


AutoConfig log files location(Application tier):
$APPL_TOP/admin/$CONTEXT_NAME/log//DDMMTime/adconfig.log

User's GUID and OID update

                          Steps to update user's GUID

1. Find the current GUID of the user:

SQL> set lines 200
SQL> col USER_NAME for a40
SQL> select user_name,user_guid from fnd_user where user_name='Name';

USER_NAME                                USER_GUID
---------------------------------------- --------------------------------
Name                                              CB71E24AE410CF01E040020A1F8123AE

2. Check if there is a mismatch by running the script appusrguid.sql

SQL> @appusrguid.sql;

EBS / OID USER GUID

Enter value for description: <input user desc from front end>
old  20:        u.DESCRIPTION like '%&Description%'
new  20:        u.DESCRIPTION like '<user desc is displayed>'

EBS User        DESCRIPTION                    EBS GUID                            OID GUID
--------------- ------------------------------ -----------------------------------

Name               <desc>                                     C5DD6187FF                        CB71E24AE

3. Update the GUID

SQL> update fnd_user set user_guid='CB71E24AE' where user_name='Name';

SQL> Commit;

4. Now check if there is a mismatch:

SQL> @appusrguid.sql;

EBS / OID USER GUID

Enter value for description: <input user desc from front end>
old  20:        u.DESCRIPTION like '%&Description%'
new  20:        u.DESCRIPTION like '<user desc is displayed>'

EBS User        DESCRIPTION                    EBS GUID                            OID GUID
--------------- ------------------------------ -----------------------------------

Name               <desc>                                CB71E24AE                       CB71E24AE

Clearing Locks in Database as an APPS DBA

--LOCKS--
select inst_id,sid,to_char(logon_time,'dd-mon-yyyy hh24:mi:ss') from gv$session where lockwait is not null;

--- BLOCKING STATUS ---
SELECT
   s.blocking_session,
   s.sid,
   s.serial#,
   s.seconds_in_wait
FROM
   v$session s
WHERE
   blocking_session IS NOT NULL
  

  --SID DETAILS--
 select inst_id,sid,serial#,program,module,action,status,blocking_session,command,to_char(logon_time,'dd-mon-yyyy hh24:mi:ss') from gv$session where sid in(288, 1775)

 --BACKGROUND RUNNING CONCURRENT ---
 SELECT DISTINCT c.USER_CONCURRENT_PROGRAM_NAME,
      round(((sysdate-a.actual_start_date)*24*60*60/60),2) AS Process_time,
    a.request_id,a.parent_request_id,a.request_date,a.actual_start_date,a.actual_completion_date,
      (a.actual_completion_date-a.request_date)*24*60*60 AS end_to_end,
      (a.actual_start_date-a.request_date)*24*60*60 AS lag_time,
      d.user_name, a.phase_code,a.status_code,a.argument_text,a.priority
FROM     apps.fnd_concurrent_requests a,
    apps.fnd_concurrent_programs b ,
    apps.FND_CONCURRENT_PROGRAMS_TL c,
    apps.fnd_user d
WHERE   a.concurrent_program_id=b.concurrent_program_id AND
    b.concurrent_program_id=c.concurrent_program_id AND
    a.requested_by=d.user_id AND
    status_code='R' order by Process_time desc;

 ---SID FROM REQ ID ---
SELECT sid
FROM v$session
WHERE paddr LIKE
(SELECT addr
FROM v$process
WHERE spid =
(SELECT oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id = TO_NUMBER(18825704)
)
);

---DETAILS FROM USER ---

SQL> select inst_id,sid,serial# from gv$session where username='SCOTT';

   INST_ID        SID    SERIAL#
---------- ---------- ----------
         1        130        620

--- KILL THE SESSION ---

STEP 1:

SQL> exec system.killsession(1508);

PL/SQL procedure successfully completed.   

STEP 2:
   
SQL>  alter system kill session '130,620,@1';

System altered.

-- CHECK FINALLY --

SQL> select sid from v$session where lockwait is not null;

no rows selected
  
  

Concurrent Manager's Long running requests check

Queries on Concurrent Manager Long Running Request and its Diag.

1.How to Determine Which Manager Ran a Specific Concurrent Request?

col USER_CONCURRENT_QUEUE_NAME for a100
select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = '&conc_reqid';

2.Concurrent manager status for a given sid?

col MODULE for a20
col OSUSER for a10
col USERNAME for a10
set num 10
col MACHINE for a20
set lines 200
col SCHEMANAME for a10
select s.sid,s.serial#,p.spid os_pid,s.status, s.osuser,s.username, s.MACHINE,s.MODULE,
s.SCHEMANAME,
s.action from gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid = '&oracle_sid';


3. Find out request id from Oracle_Process Id:

select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from apps.fnd_concurrent_requests where
ORACLE_PROCESS_ID='&a';

4.To find sid,serial# for a given concurrent request id?

set lines 200
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';


5.To find concurrent program name,phase code,status code for a given request id?
SELECT request_id, user_concurrent_program_name, DECODE(phase_code,'C','Completed',phase_code)
phase_code, DECODE(status_code,'D', 'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X',
'Terminated', 'C', 'Normal', status_code) status_code, to_char(actual_start_date,'dd-mon-
yy:hh24:mi:ss') Start_Date, to_char(actual_completion_date,'dd-mon-yy:hh24:mi:ss'),
completion_text FROM apps.fnd_conc_req_summary_v WHERE request_id = '&req_id' ORDER BY 6 DESC;

6.To find the sql query for a given concurrent request sid?

select sid,sql_text from gv$session ses, gv$sqlarea sql where
ses.sql_hash_value = sql.hash_value(+) and ses.sql_address = sql.address(+) and
ses.sid='&oracle_sid'
/

7. To find child requests

set lines 200
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name, DECODE
(sum.phase_code,'C','Completed',sum.phase_code) phase_code, DECODE(sum.status_code,'D',
'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X',
'Terminated', 'C', 'Normal', sum.status_code) status_code, sum.actual_start_date,
sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v sum,
apps.fnd_concurrent_requests req where req.request_id=sum.request_id and req.PARENT_REQUEST_ID =
'&parent_concurrent_request_id';


8. Cancelling Concurrent request :
update fnd_concurrent_requests
set status_code='D', phase_code='C'
where request_id=&req_id;

9. Kill sessions program wise

select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' immediate;' from v$session where
MODULE like '';


10 .Concurrent Request running by SID

SELECT a.request_id,
d.sid as Oracle_SID,
d.serial#,
d.osuser,
d.process,
c.SPID as OS_Process_ID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND d.sid = &SID;

11. Find out request id from Oracle_Process Id:

select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from fnd_concurrent_requests where
ORACLE_PROCESS_ID='&a';


12. Oracle Concurrent Request Error Script (requests which were error ed out)
SELECT a.request_id "Req Id"
,a.phase_code,a.status_code
, actual_start_date
, actual_completion_date
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.status_code = 'E'
AND a.phase_code = 'C'
AND actual_start_date > sysdate - 2
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE = 'US'
ORDER BY 5 DESC;


13. Request submitted by User

SELECT
user_concurrent_program_name,
request_date,
request_id,
phase_code,
status_code
FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_responsibility_tl fr,
fnd_user fu
WHERE
fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
and fcr.responsibility_id = fr.responsibility_id
and fcr.requested_by = fu.user_id
and user_name = '&user'
AND actual_start_date > sysdate - 1
ORDER BY REQUEST_DATE Asc;



14.Concurrent Program enable with trace

col User_Program_Name for a40
col Last_Updated_By for a30
col DESCRIPTION for a30
SELECT A.CONCURRENT_PROGRAM_NAME "Program_Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User_Program_Name",
SUBSTR(B.USER_NAME,1,15) "Last_Updated_By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID
-----------------------------------------------------------------------------
## QUERY TO FIND CURRENTLY LONG RUNNING REQUEST FOR MORE THAN 1 HOUR ##
-----------------------------------------------------------------------------

SELECT fcr.request_id, fcr.actual_start_date, floor((SYSDATE - actual_start_date)*24) || ' HOURS ' || mod(floor((SYSDATE- actual_start_date)*24*60),60) || ' MINS ' "Duration",
fcpt.user_concurrent_program_name,fu.user_name,fcr.argument_text,fcr.oracle_process_id,fcr.oracle_session_id,fcr.os_process_id,fcr.root_request_id
FROM apps.fnd_concurrent_requests fcr,apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_user fu
WHERE
fcr.concurrent_program_id = fcpt.concurrent_program_id
and
fcr.requested_by=fu.user_id
and
actual_start_date LIKE SYSDATE
AND (SYSDATE - actual_start_date) * 24 > 1
AND status_code ='R'
AND phase_code ='R';

-------------------------------------------------------------------------
## To check Program Past Runtime
## Need Program Name as input # if you know Exact Request Name ##
--------------------------------------------------------------------------
SELECT fcr.request_id, fcr.actual_start_date, fcr.actual_completion_date,
floor((actual_completion_date - actual_start_date)*24) || ' HOURS ' || mod(floor((actual_completion_date- actual_start_date)*24*60),60) || ' MINS ' "Duration",
fcpt.user_concurrent_program_name, fu.user_name
FROM apps.fnd_concurrent_requests fcr,apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_user fu
WHERE
fcr.concurrent_program_id = fcpt.concurrent_program_id
and
fcr.requested_by=fu.user_id
and
fcpt.user_concurrent_program_name='&program_name';

----------------------------------------------------------------
## To check Program Past Runtime , need Program Name as input
## if you know via Wild Character Search ##
-----------------------------------------------------------------

SELECT fcr.request_id, fcr.actual_start_date, fcr.actual_completion_date,
floor((actual_completion_date - actual_start_date)*24) || ' HOURS ' || mod(floor((actual_completion_date- actual_start_date)*24*60),60) || ' MINS ' "Duration",
fcpt.user_concurrent_program_name, fu.user_name
FROM apps.fnd_concurrent_requests fcr,apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_user fu
WHERE
fcr.concurrent_program_id = fcpt.concurrent_program_id
and
fcr.requested_by=fu.user_id
and
fcpt.user_concurrent_program_name like '&program_name%';


-------------------------------------------------------------------
## To find Detailed Request that are currently Running ##
## This will help get to what amoung those long request doing ##
-------------------------------------------------------------------
SELECT a.request_id
,a.oracle_process_id "SPID"
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
,a.description
,a.ARGUMENT_TEXT
,b.node_name
,b.db_instance
,a.logfile_name
,a.logfile_node_name
,q.concurrent_queue_name
,a.phase_code,a.status_code, a.completion_text
, actual_start_date
, actual_completion_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.phase_code = 'R'
AND a.status_code = 'R'
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
ORDER BY a.actual_start_date DESC;

------------------------------------------------------------------------------
## Followed by Take SPID from 1st Query and run 2nd Query ##
## Gives detail of That Session doing at Backend Database, the SQL Query running its waits ##
-----------------------------------------------------------------------------
SELECT b.sid, b.serial#, a.spid, b.program, b.osuser, b.machine,
b.TYPE, b.event, b.action, b.p1text, b.p2text, b.p3text, b.state, c.sql_text,b.logon_time
FROM v$process a, gv$session b, v$sqltext c
WHERE a.addr=b.paddr
AND b.sql_hash_value = c.hash_value
AND b.STATUS = 'ACTIVE'
AND a.spid = '13947'
ORDER BY a.spid, c.piece;

--------------------------------------------------------------------------------
Concurrent History Details
---------------------------------------------------------------------------------

select a.request_id "rqst_id"
      ,a.concurrent_program_id "conc_pgm_id"
      ,a.status_code||decode(a.resubmit_interval, null, null, '*') "stat_cd"
      ,b.os_process_id "appl_os_pid"
      ,v.spid "DB_OS_vspid"
      ,v.Inst_id "Instance"
      ,v.sid "vsid"
      ,v.action
      ,v.serial# "vserial#"
      ,a.priority "pri"
      ,substr(d.concurrent_queue_name,1,4) "que"
      ,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "time"
      --,round(f.time_avg,2) "avgtime"
      ,e.user_name "usr"
      ,decode(sign(a.requested_start_date - a.request_date),1,
              round(((sysdate-requested_start_date) * 1440),0),
              round(((sysdate-request_date) * 1440),0)) "walltime"
      ,c.concurrent_program_name||' - '||ct.user_concurrent_program_name "program"
      ,a.phase_code "Phase"
      ,a.status_code "Status"
  from APPLSYS.fnd_concurrent_requests a
      ,APPLSYS.fnd_concurrent_processes b
      ,APPLSYS.fnd_concurrent_programs c
      ,APPLSYS.fnd_concurrent_programs_tl ct
      ,APPLSYS.fnd_concurrent_queues d
      ,APPLSYS.fnd_user e
      --,APPLSYS.fcr_stats f
      ,(select s.inst_id,s.sid, s.serial#, p.spid,s.action from gv$session s, v$process p where s.paddr = p.addr) v
 where a.controlling_manager = b.concurrent_process_id
   and a.concurrent_program_id = c.concurrent_program_id
   --and a.concurrent_program_id = f.concurrent_program_id(+)
   --and a.program_application_id = f.application_id(+)
   and a.program_application_id = c.application_id
   and a.concurrent_program_id = ct.concurrent_program_id
   and a.program_application_id = ct.application_id
   and b.concurrent_queue_id = d.concurrent_queue_id
   and a.requested_by = e.user_id
   and a.phase_code in ('R','T')
   and a.oracle_process_id=v.spid(+)
 order by 10 asc

Database Links Queries

 Database Links Queries:

 **************************************************************
Name of Database
************************************************************** 

 SQL> select name from v$database;

**************************************************************
List of DB links
**************************************************************

 set pagesize 100
set lines 200
col host for a20
col db_link for a30
select * from dba_db_links order by created;

**************************************************************
 Using DB Link   --- check if it works correctly
**************************************************************  
 

select 'X' from dual@Name_of_dblink;
selet * from dual@Name_of_dblink;


**************************************************************
Creating DB links
**************************************************************  
CREATE DATABASE LINK "Name_of_dblink" CONNECT TO APPS IDENTIFIED BY <PWD> USING 'target_db';

Queries to Monitor Concurrent Requests Daily

Queries to Monitor Concurrent Requests Daily

Query 1 (CC Manager program wise exec report)

Select  to_char(actual_completion_date, 'HH24') "Hour",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'Standard Manager',1,0)) "Standard Manager",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'Long Running Jobs',1,0)) "Long Running Jobs",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'OE Manager',1,0)) "OE Manager",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'HR',1,0)) "HR",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'Long Running Jobs 2',1,0)) "Long Running Jobs 2",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'PO Output',1,0)) "PO Output",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'Workflow',1,0)) "Workflow",
--sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'Inventory Manager',1,0)) "Inventory Manager",
--sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'INV Remote Procedure Manager',1,0)) "INV Remote Procedure Manager",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'PA Streamline Manager',1,0)) "PA Streamline Manager"
--sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'PC Dedicated Manager',1,0)) "PC Dedicated Manager",
--sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'PO Document Approval Manager',1,0)) "PO Document Approval Manager"                   
from APPLSYS.fnd_concurrent_requests a,
fnd_concurrent_processes b,
fnd_concurrent_queues_vl c,
fnd_concurrent_programs_tl d                       
where b.CONCURRENT_QUEUE_ID = c.CONCURRENT_QUEUE_ID and
b.CONCURRENT_PROCESS_ID = a.controlling_manager
and a.CONCURRENT_PROGRAM_ID=d.CONCURRENT_PROGRAM_ID
--and d.user_concurrent_program_name = 'Workflow Background Process'
and TRUNC(a.ACTUAL_START_DATE) = TO_DATE(UPPER('&&enddt'),'DD-MON-YY')
group by to_char(a.actual_completion_date, 'HH24')                          
Order by to_char(a.actual_completion_date, 'HH24');


Query2 (CCR Running more than 20 min's)

select fcr.request_id,rtrim(fct.user_concurrent_program_name) ccr_name,
fcu.user_name,
to_char(fcr.actual_start_date,'mm/dd/yyyy:HH24:mm:ss')  start_date,
to_char(fcr.actual_completion_date,'mm/dd/yyyy:HH24:mm:ss') completion_date,
trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date) * 24, 24 ) )   hrs,
trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date) * 24 * 60, 60 ) ) Min,
trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date) * 24 * 60 * 60, 60 ) ) sec
from APPLSYS.fnd_concurrent_requests fcr, APPLSYS.fnd_concurrent_programs fcp,
APPLSYS.fnd_concurrent_programs_tl fct, APPLSYS.fnd_user fcu
where fcp.concurrent_program_id=fcr.CONCURRENT_PROGRAM_ID and
fcp.concurrent_program_id=fct.CONCURRENT_PROGRAM_ID and
fcr.REQUESTED_BY=fcu.user_id and fcr.actual_start_date > ( sysdate - 1 ) and
(fcr.actual_completion_date - fcr.actual_start_date) * (24 * 60) > = 20
order by 7 desc,8 desc




Query 3 (CCR Running within 5 Minutes Details)

SELECT TO_CHAR (a.actual_completion_date , 'mm/dd/yyyy:HH24')||
        case
            when to_number(to_char(a.actual_completion_date,'mi')) between 01 and 05 then
                            ':05'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 06 and 10 then
                            ':10'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 11 and 15 then
                            ':15'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 16 and 20 then
                            ':20'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 21 and 25 then
                            ':25'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 26 and 30 then
                            ':30'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 31 and 35 then
                            ':35'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 36 and 40 then
                            ':40'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 41 and 45 then
                            ':45'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 46 and 50 then
                            ':50'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 51 and 55 then
                            ':55'
                            else
                  ':00'
        end time,
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'Standard Manager',1,0)) "Standard Manager",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'Long Running Jobs',1,0)) "Long Running Jobs",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'OE Manager',1,0)) "OE Manager",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'HR',1,0)) "HR",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'Long Running Jobs 2',1,0)) "Long Running Jobs 2",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'PO Output',1,0)) "PO Output",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'Workflow',1,0)) "Workflow",
--sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'Inventory Manager',1,0)) "Inventory Manager",
--sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'INV Remote Procedure Manager',1,0)) "INV Remote Procedure Manager",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'PA Streamline Manager',1,0)) "PA Streamline Manager"
--sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'PC Dedicated Manager',1,0)) "PC Dedicated Manager",
--sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'PO Document Approval Manager',1,0)) "PO Document Approval Manager"                   
from APPLSYS.fnd_concurrent_requests a,
fnd_concurrent_processes b,
fnd_concurrent_queues_vl c,
fnd_concurrent_programs_tl d                       
where b.CONCURRENT_QUEUE_ID = c.CONCURRENT_QUEUE_ID and
b.CONCURRENT_PROCESS_ID = a.controlling_manager
and a.CONCURRENT_PROGRAM_ID=d.CONCURRENT_PROGRAM_ID
--and d.user_concurrent_program_name = 'Workflow Background Process'
and TRUNC(a.ACTUAL_START_DATE) = TO_DATE(UPPER('&&enddt'),'DD-MON-YY')
GROUP BY TO_CHAR (a.actual_completion_date, 'mm/dd/yyyy:HH24')||
              case
                  when to_number(to_char(a.actual_completion_date,'mi')) between 01 and 05 then
                            ':05'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 06 and 10 then
                            ':10'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 11 and 15 then
                            ':15'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 16 and 20 then
                            ':20'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 21 and 25 then
                            ':25'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 26 and 30 then
                            ':30'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 31 and 35 then
                            ':35'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 36 and 40 then
                            ':40'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 41 and 45 then
                            ':45'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 46 and 50 then
                            ':50'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 51 and 55 then
                            ':55'
                            else
                  ':00'
              end      
Order by TO_CHAR (a.actual_completion_date, 'mm/dd/yyyy:HH24')||
              case
                            when to_number(to_char(a.actual_completion_date,'mi')) between 01 and 05 then
                            ':05'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 06 and 10 then
                            ':10'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 11 and 15 then
                            ':15'
                             when to_number(to_char(a.actual_completion_date,'mi')) between 16 and 20 then
                            ':20'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 21 and 25 then
                            ':25'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 26 and 30 then
                            ':30'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 31 and 35 then
                            ':35'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 36 and 40 then
                            ':40'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 41 and 45 then
                            ':45'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 46 and 50 then
                            ':50'
                            when to_number(to_char(a.actual_completion_date,'mi')) between 51 and 55 then
                            ':55'
                            else
                            ':00'
              end ;


Query 4 (Error out ccr Details)

SELECT a.request_id request_id,SUBSTR(a.user_concurrent_program_name,1,50) name, TO_CHAR(a.actual_start_date,'dd-mon-yy:hh24:mi:ss') st_time,
TO_CHAR(a.actual_completion_date,'dd-mon-yy:hh24:mi:ss') end_time,requestor,DECODE(a.phase_code, 'R','Running', 'P','Inactive', 'C','Completed', a.phase_code) phase_code,
DECODE(a.status_code, 'E','Error', 'C','Normal', 'X','Terminated', 'Q','On Hold', 'D','Cancelled', 'G','Warning', 'R','Normal', 'W','Paused', a.status_code) status_code
FROM apps.fnd_conc_req_summary_v a WHERE TRUNC(actual_completion_date) >= TRUNC(SYSDATE -1) AND a.status_code IN ('E','X','D')ORDER BY actual_start_date;


Query 5: CCR run for a given date:

select fcr.request_id,rtrim(fct.user_concurrent_program_name) ccr_name,
fcu.user_name,
to_char(fcr.actual_start_date,'mm/dd/yyyy:HH24:mm:ss')  start_date,
to_char(fcr.actual_completion_date,'mm/dd/yyyy:HH24:mm:ss') completion_date,
trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date) * 24, 24 ) )   hrs,
trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date) * 24 * 60, 60 ) ) Min,
trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date) * 24 * 60 * 60, 60 ) ) sec
from APPLSYS.fnd_concurrent_requests fcr, APPLSYS.fnd_concurrent_programs fcp,
APPLSYS.fnd_concurrent_programs_tl fct, APPLSYS.fnd_user fcu
where fcp.concurrent_program_id=fcr.CONCURRENT_PROGRAM_ID and
fcp.concurrent_program_id=fct.CONCURRENT_PROGRAM_ID and
fcr.REQUESTED_BY=fcu.user_id and TRUNC(REQUEST_DATE ) = TO_DATE(UPPER('&&enddt'),'DD-MON-YY')
order by 4 asc