#!/bin/ksh
# whatconcmgrs ()
#------------------------------------------------------------------------------#
#    Module Name:  whatconcmgrs  (c) Copyright 2012                            #
#                                                                              #
#    Purpose:      Report on the ConcRequest Manager Usage Summary.            #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    11-Aug-2015   F.Bender      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
        #----------------------------------------------------------------------#
        #  TEST:  Number ($#) of arguments passed to this function?            #
        #----------------------------------------------------------------------#
        if      [ $# = 0 ]
        then
            clear
            echo
            echo  " #--------------------------------------------------------#"
            echo  " # whatconcmgrs:   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 Password: "
 
            stty echo
            echo
            #------------------------------------------------------------------#
            #  Trap:  Reset CNTL-C CNTL-D CNTL-\ After Password Protection     #
            #------------------------------------------------------------------#
            trap        2
            trap        3
 
 
            clear
        else
            PASSWD=$1
            clear
        fi
 
        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/whatconcmgrs_$$.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  fcqvname  FORMAT A40    Heading 'Concurrent Pgm Description'
        COLUMN  fcqvcntr  FORMAT 999999   Heading 'Number|of|Executions'
 
        TTITLE  CENTER  'E-Business Concurrent Managers (MgrName Summary)' -
                LEFT    DATEVAR                                         -
                RIGHT   'Page:'         FORMAT 999 SQL.pno      skip 1  -
                LEFT    TIMEVAR                                         -
                CENTER  'DBA ConcMgr MgrName 30-Day Summary'    skip 2  -
 
        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')           datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')          timevalue,
                fcqv.user_concurrent_queue_name         fcqvname,
                count(1)                                fcqvcntr
          FROM
                apps.ood_concurrent_requests fcr,
                apps.fnd_concurrent_programs_vl fcpv,
                apps.fnd_lookup_values flv_phase,
                apps.fnd_lookup_values flv_status,
                apps.fnd_user fu,
                apps.fnd_responsibility_vl frv,
                apps.fnd_application_vl fav,
                ood_concurrent_processes fcp,
                fnd_concurrent_queues_vl fcqv
        WHERE   1=1
          AND fcr.concurrent_program_id   = fcpv.concurrent_program_id
          AND fcr.program_application_id  = fcpv.application_id
          AND fcr.phase_code            = flv_phase.lookup_code(+)
          AND 'CP_PHASE_CODE'           = flv_phase.lookup_type(+)
          AND  283                      = flv_phase.view_application_id(+)
          AND userenv('LANG')           = flv_phase.language(+)
          AND fcr.status_code           = flv_status.lookup_code(+)
          AND 'CP_STATUS_CODE'          = flv_status.lookup_type(+)
          AND 283                       = flv_status.view_application_id(+)
          AND userenv('LANG')           = flv_status.language(+)
          AND fcr.requested_by          = fu.user_id
          AND fcr.responsibility_id     = frv.responsibility_id
          AND fcr.responsibility_application_id = frv.application_id
          AND frv.application_id        = fav.application_id
          AND fcr.controlling_manager   = fcp.concurrent_process_id(+)
          AND fcp.concurrent_queue_id   = fcqv.concurrent_queue_id(+)
          AND fcp.queue_application_id  = fcqv.application_id(+)
          AND trunc( fcr.actual_start_date ) >= trunc( sysdate)-30
        GROUP BY fcqv.user_concurrent_queue_name;
 
EXIT;
 
END_FILE
print
print "Please see /tmp/whatconcmgrs_$$.txt"
print
}