#!/bin/ksh
# whatconcpending ()
#------------------------------------------------------------------------------#
#    Module Name:  whatconcpending                                             #
#                                                                              #
#    Purpose:      Report on the ConcRequest.                                  #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    10-Oct-2009   T.Robinette   Module design/creation                        #
#                  F.Bender      Module design/creation                        #
#                  A.Estrada     Module design/creation                        #
#                  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  " # whatconcpending:         Concurrent Requests (Pending) #"
        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/whatconcpending_$$.txt
 
        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR
 
        COLUMN  c_stop       NOPRINT
        COLUMN  c_time       NOPRINT
 
        COLUMN  c_name  FORMAT A16      Heading 'ConcProgram Name' -
                                                        WORD_WRAPPED
        COLUMN  c_desc  FORMAT A16      Heading 'ConcProgram Desc' -
                                                        WORD_WRAPPED
        COLUMN  c_rqid  FORMAT 99999999 Heading 'ConcReq'
        COLUMN  c_proc  FORMAT A8       Heading 'OS Proc'
        COLUMN  c_intv  FORMAT 9999     Heading 'ReSubmit|Interval'
        COLUMN  c_unit  FORMAT A8       Heading 'ReSubmit|Units'
 
        TTITLE  CENTER  'E-Business Concurrent Requests (Pending)' -
                RIGHT   'Page:'         FORMAT 999 SQL.pno      skip 2
 
        SELECT  fcp.user_concurrent_program_name  c_name,
                fcp.description                 c_desc,
                fcr.request_id                  c_rqid,
                fcr.request_id                  c_rqid,
                fcr.os_process_id               c_proc,
                fcr.resubmit_interval           c_intv,
                fcr.resubmit_interval_unit_code c_unit
        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 fcr.phase_code            = 'P';
 
EXIT;
 
END_FILE
 
print
print "Please see /tmp/whatconcpending_$$.txt"
print
}