#!/bin/ksh
# whatconcrunningdetailDBTier ()
#-----------------------------------------------------------------------#
#    Module Name:  whatconcrunningdetailDBTier   (c) Copyright          #
#                                                                       #
#    Purpose:      Report on the ConcRequest.                           #
#                                                                       #
#    Maintenance                                                        #
#    Date          Author        Description                            #
#    -----------   ------------  -------------------------------------  #
#    10-May-2012   M.Barone      Module design/creation                 #
#                                                                       #
# FND_CONCURRENT_REQUESTS (Phase_Code):                                 #
#  Completed (C), Pending (P), Running (R), Inactive (P)                #
#                                                                       #
# FND_CONCURRENT_REQUESTS (Status_Code):                                #
#  Cancelled (D)                                                        #
#  Disabled                                                             #
#  Error (E)                                                            #
#  Inactive (I)                                                         #
#  Normal (C), (I) or (R)                                               #
#  No Manager                                                           #
#  Paused (W)                                                           #
#  Resuming   (I or Q)  And Phase_Code = 'R')                           #
#  Scheduled  (I or Q)  And Phase_Code = 'P')                           #
#  Standby    (I or Q)  And Phase_Code = 'P')                           #
#  Suspended  (S), Terminated (X)                                       #
#  Terminating(T)                                                       #
#  Waiting    (Q)                                                       #
#  Warning    (G)                                                       #
#                                                                       #
#  No Manager,    On Hold: Hold_Flag 'Y'                                #
#-----------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)
                
        clear
        echo
        echo  " #------------------------------------------------------#"
        echo  " # whatconcrunningdetailDBTier: Oracle Conc (Executing) #"
        echo  " #------------------------------------------------------#"
        echo

        #---------------------------------------------------------------#
        #  Trap:  Set CNTL-C CNTL-D CNTL-\ Before Password Protection   #
        #---------------------------------------------------------------#
        trap        "stty echo; return"     2
        trap        "stty echo; return"     3
        stty -echo

        read PASSWD?" Please Enter the Oracle SYSTEM Passwd: " 
            
        stty echo
        echo
        #---------------------------------------------------------------#
        #  Trap:  Reset CNTL-C CNTL-D CNTL-\ After Password Protection  #
        #---------------------------------------------------------------#
        trap        2
        trap        3

        read PDBNAM?"        Enter the Oracle ORACLE_PDB_NAME: " 

        clear
        ORANAM=${ORASID}

#-----------------------------------------------------------------------#
#  SQL*Plus ("Here-Document")                                           #
#-----------------------------------------------------------------------#
sqlplus  -s  /nolog  <<-END_FILE

        CONNECT  SYSTEM/$PASSWD
        ALTER SESSION SET CONTAINER = "${PDBNAM}";

        clear   BREAKS
        clear   BUFFER
        clear   COLUMNS
        clear   COMPUTES
        clear   SCREEN
        clear   SQL
        clear   TIMING

        set     feedback        off
        set     flush           on
        set     heading         on
        set     pause           off
        set     space           1
        set     termout         on
        set     verify          on
        set     linesize        120
        set     pagesize        999

        SPOOL /tmp/whatconcrunningdetailDBTier_$$.txt

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN  c_stop       NOPRINT 
        COLUMN  c_time       NOPRINT

        COLUMN  c_desc  FORMAT A20      Heading 'Concurrent Req Desc'
        COLUMN  c_rqid  FORMAT 99999999 Heading 'ConcReq'
        COLUMN  c_user  FORMAT A11      Heading 'Requestor'
        COLUMN  c_proc  FORMAT A8       Heading 'Unix|Process|ID'
        COLUMN  c_sbmt  FORMAT A9       Heading 'Submit|Time'
        COLUMN  c_strt  FORMAT A9       Heading 'Start|Time'
        COLUMN  c_args  FORMAT A18      Heading 'Concurrent|Arguments'

        BREAK   ON c_desc               skip 1 ON c_sbmt skip 0

        TTITLE  CENTER  'E-Business DBTier Concurrent Requests (Detail)'    -
                LEFT    DATEVAR                                      -
                RIGHT   'Page:'         FORMAT 999 SQL.pno   skip 1  -
                CENTER  'whatsconcrunningdetail -- Sorted by Start Time' -
                LEFT    TIMEVAR                              skip 2  

        COLUMN REQUEST justify left heading 'Request' format a10
        COLUMN PHASE heading 'Phase' format A8
        COLUMN STATUS heading 'Status' format A8
        COLUMN PROGRAM heading 'Program Name' format A25
        COLUMN REQUESTOR heading 'Requestor' format A9
        COLUMN START_TIME heading 'Start Time' format A15
        COLUMN RUN_TIME justify left heading 'Runtime(m)' format 9,9999.99
        COLUMN OSPID heading 'OSPID' format a5
        COLUMN SID heading 'DBSID' format 99999

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')   datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')  timevalue,
                substr(fcrv.request_id,1,10)REQUEST,
                decode(fcrv.phase_code,'P','Pending','R','Running','I','Inactive','Completed')PHASE,
                decode(fcrv.status_code,
                'A','Waiting',
                'B','Resuming',
                'C','Normal',
                'F','Scheduled',
                'G','Warning',
                'H','On Hold',
                'I','Normal',
                'M','No Manager',
                'Q','Standby',
                'R','Normal',
                'S','Suspended',
                'T','Terminating',
                'U','Disabled',
                'W','Paused',
                'X','Terminated',
                'Z','Waiting',fcrv.status_code)STATUS,
                substr(fcrv.program,1,25)PROGRAM,
                substr(fcrv.requestor,1,9)REQUESTOR,
                to_char(fcrv.actual_start_date,'MM/DD/RR HH24:MI')START_TIME,
                round(((sysdate - fcrv.actual_start_date)*1440),2)RUN_TIME,
                substr(fcr.oracle_process_id,1,7)OSPID,
                vs.sid SID
        FROM    apps.fnd_conc_req_summary_v fcrv,
                apps.fnd_concurrent_requests fcr,
                v\$session vs,
                v\$process vp
        where   fcrv.phase_code = 'R'
        and     fcrv.request_id = fcr.request_id
        and     fcr.oracle_process_id= vp.spid
        and     vs.paddr = vp.addr
        and     fcrv.concurrent_program_id not in ('40112','40113','36887')
        order by PHASE, STATUS, REQUEST desc; 

        EXIT;

END_FILE
print
print "Please see /tmp/whatconcrunningdetailDBTier$$.txt"
print
}