#!/usr/bin/ksh
# whatconcdaily ()
#------------------------------------------------------------------------------#
#    Module Name:  whatconcdaily  (c) Copyright 2012                           #
#                                                                              #
#    Purpose:      Report on the ConcRequest Daily Summary.                    #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    10-Oct-2009   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  " # whatconcdaily:   Oracle Apps Concurrent Reqs (Summary) #"
        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 APPS Passwd: " 
            
        stty echo
        echo
        #------------------------------------------------------------------#
        #  Trap:  Reset CNTL-C CNTL-D CNTL-\ After Password Protection     #
        #------------------------------------------------------------------#
        trap        2
        trap        3

        clear
        ORANAM=${ORASID}

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

        CONNECT  APPS/$PASSWD

        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        80
        set     pagesize        999

        SPOOL /tmp/whatconcdaily_$$.txt

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        BREAK   ON c_desc               skip 1  ON c_sbmt skip 0
        BREAK   ON  fcr.description     skip 2  ON REPORT

        COMPUTE SUM LABEL 'Total'  OF   c_cntr  ON REPORT

        COLUMN  c_desc  FORMAT A29      Heading 'Concurrent Pgm Description'  -
                                                WORD_WRAPPED
        COLUMN  c_prog  FORMAT A29      Heading 'Concurrent|Program|Name'     -
                                                WORD_WRAPPED
        COLUMN  c_time  FORMAT 9,999.99 Heading 'Avg.|Run-Time|(Minutes)'
        COLUMN  c_cntr  FORMAT 999999   Heading 'Number|of|Executions'

        TTITLE  CENTER  'E-Business Concurrent Requests (Daily Summary)' -
                LEFT    DATEVAR                                         -
                RIGHT   'Page:'         FORMAT 999 SQL.pno      skip 1  -
                LEFT    TIMEVAR                                         -
                CENTER  'DBA ConcMgr Summary Report'            skip 2  -

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')           datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')          timevalue,
                fcp.description                         c_desc,
                user_concurrent_program_name            c_prog,
                avg((to_number(to_char(actual_completion_date,'SSSSS')) -
                     to_number(to_char(actual_start_date,'SSSSS')))/60)
                                                        c_time,
                count(user_concurrent_program_name)     c_cntr
        FROM    fnd_concurrent_programs_tl fcp,
                fnd_concurrent_requests    fcr
        WHERE   fcp.application_id                 =
                fcr.program_application_id
          AND   fcp.application_id        =
                fcr.program_application_id
          AND   fcp.concurrent_program_id =
                fcr.concurrent_program_id
          AND   fcp.language              = 'US'
          AND   to_char(actual_completion_date,'DD-MON-YY')  =
                to_char(actual_start_date,'DD-MON-YY')
          AND   to_char(actual_start_date,'DD-MON-YY') BETWEEN
                (SYSDATE -2)  AND  (SYSDATE)
        GROUP BY user_concurrent_program_name, fcp.description
        ORDER BY c_time DESC;

EXIT;

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