#!/usr/bin/ksh
## set -xv
# whatconclongrunning ()
#------------------------------------------------------------------------------#
#    Module Name:  whatconclongrunning   (c) Copyright                         #
#                                                                              #
#    Purpose:      Report on the ConcRequest – Long Running                    #
#                                                                              #
#    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)
 
        if      [ $# = 0 ]
        then
            clear
            echo
            echo  " #--------------------------------------------------------#"
            echo  " # whatconclongrunning:   Oracle ConcRequests (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 APPS Passwd: "
 
            stty echo
            echo
            #------------------------------------------------------------------#
            #  Trap:  Reset CNTL-C CNTL-D CNTL-\ After Password Protection     #
            #------------------------------------------------------------------#
            trap        2
            trap        3
 
            read CONCDT?"        Enter ConcReq Start-Date DD-MON-YY: "
 
            clear
        else
            PASSWD=$1
            CONDT=$(date '+%d-%b-%y')
            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/whatconclongrunning_$$.txt
 
        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR
 
        COLUMN  c_stop       NOPRINT
        COLUMN  c_time       NOPRINT
 
        COLUMN s_date FORMAT A9  HEADING 'Actual|Start|Date'
        COLUMN c_date FORMAT A9  HEADING 'Actual|Completion|Date'
        COLUMN c_hhmm FORMAT 9999 HEADING 'Execution|Time(Min)'
        COLUMN c_cpnm FORMAT A21 HEADING 'Concurrent Program Name' WORD_WRAPPED
        COLUMN c_rqid FORMAT 99999999 HEADING 'Request|ID'
        COLUMN c_intv FORMAT 99990 HEADING 'Resubmit|Interval'
        COLUMN c_unit FORMAT A8  HEADING 'Resubmit|Unit'
 
        TTITLE  CENTER  'E-Business ConcReq Long-Running -- Last 24Hours' -
        RIGHT   'Page:'         FORMAT 999 SQL.pno      skip 2
 
        SELECT  TO_CHAR(fcr.actual_start_date, 'DD-MON-YY HH24:MI')    s_date,
        TO_CHAR(fcr.actual_completion_date, 'DD-MON-YY HH24:MI')       c_date,
        ROUND(
         (fcr.actual_completion_date - fcr.actual_start_date) * 1440)  c_hhmm,
        fcv.user_concurrent_program_name                               c_cpnm,
        fcr.request_id                                                 c_rqid,
        fcr.resubmit_interval                                          c_intv,
        fcr.resubmit_interval_unit_code                                c_unit
        FROM  fnd_concurrent_programs_tl fcv,
        fnd_concurrent_requests    fcr
        WHERE  fcr.program_application_id = fcv.application_id
        AND  fcr.concurrent_program_id  = fcv.concurrent_program_id
        AND  fcr.phase_code            = 'C'
        AND  fcr.status_code           = 'C'
        AND  fcr.actual_start_date     IS NOT NULL
        AND  fcr.actual_completion_date IS NOT NULL
        AND  fcr.actual_completion_date - fcr.actual_start_date > 1/1440
        AND to_char(actual_start_date,'DD-MON-YY') = '${CONCDT}'
        ORDER BY fcr.actual_start_date;
 
EXIT;
 
END_FILE
print
print "Please see /tmp/whatconclongrunning_$$.txt"
print
}