#!/usr/bin/ksh
# whatconcdate_requestor ()
#------------------------------------------------------------------------------#
#    Module Name:  whatconcdate_requestor   (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
        #----------------------------------------------------------------------#
        #  TEST:  Number ($#) of arguments passed to this function?            #
        #----------------------------------------------------------------------#
        if      [ $# = 0 ]
        then
            clear
                
	    echo
	    echo  " #--------------------------------------------------------#"
	    echo  " # whatconcdate_requestor:  Oracle ConcRequests (On Date) #"
	    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: "
	    read REQEST?"        Enter ConcReq Requestor-Name: "

	    clear
        else
            PASSWD=$1
	    CONCDT=$(date '+%d-%b-%y')
            clear
        fi

	    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/whatconcdate_requestor_$$.txt

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR
        COLUMN  c_stop          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_args  FORMAT A17      Heading 'Concurrent|Arguments'
        COLUMN  c_strt  FORMAT A9       Heading 'Start|Time'
        COLUMN  c_stop  FORMAT A9       Heading 'Eapsed|Time|Minutes'
        COLUMN  c_time  FORMAT 9,999.99 Heading 'Run-Time|(Minutes)'


        BREAK   ON c_desc               skip 1 ON c_sbmt skip 0

        TTITLE  CENTER  'E-Business Concurrent Requests (Detail)'       -
                LEFT    DATEVAR                                         -
                RIGHT   'Page:'         FORMAT 999 SQL.pno      skip 1  -
                CENTER  'whatconcdate_requestor -- Report for ${CONCDT}'   -
                LEFT    TIMEVAR                                 skip 2  

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')   datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')  timevalue,
                fcp.description                 c_desc,
                fcr.request_id                  c_rqid,
                fus.user_name                   c_user,
                fcr.argument_text               c_args,
                TO_CHAR(actual_start_date,      'DD-MON-YY HH24:MI:SS')
                                                c_strt,
                (((actual_completion_date) - (actual_start_date)) *(60*24))
                                                c_time,
                TO_CHAR(actual_completion_date, 'DD-MON-YY HH24:MI:SS')
                                                c_stop
        FROM    fnd_concurrent_programs_tl fcp,
                fnd_concurrent_requests    fcr,
                fnd_user                   fus
        WHERE   fcp.application_id       = fcr.program_application_id
          AND   DECODE(fcp.language,
                'US', 'AMERICAN',
                'D',  'GERMAN',
                'F',  'FENCH',
                'PTB','BRAZILIAN',
                'E',  'SPANISH')          = fcr.nls_language
          AND	fcp.concurrent_program_id = fcr.concurrent_program_id
          AND	fcr.requested_by          = fus.user_id
          AND   to_char(actual_start_date,'DD-Mon-YY')	= '${CONCDT}'
          AND    fus.user_name				= '${REQEST}'
        ORDER BY c_strt DESC;

EXIT;

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