#!/usr/bin/ksh
# whatconcpending_detail ()
#------------------------------------------------------------------------------#
#    Module Name:  whatconcpending_detail                                      #
#                                                                              #
#    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_detail:  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_detail_$$.txt

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

       	SET PAGESIZE 200

        COLUMN  c_stop       NOPRINT
        COLUMN  c_time       NOPRINT


	COLUMN  c_desc  FORMAT A15      Heading 'ConcPgm Desc'
	COLUMN  c_pgid  FORMAT 99999999 Heading 'ConcPgm|ID'
	COLUMN  c_rqid  FORMAT 99999999 Heading 'ConcReq|Number'
	COLUMN  c_user  FORMAT A10      Heading 'Requestor'
	COLUMN  c_args  FORMAT A15      Heading 'Concurrent|Arguments'
	COLUMN  c_intv  FORMAT 9999     Heading 'ReSubmit|Interval'
	COLUMN  c_unit  FORMAT A8       Heading 'ReSubmit|Units'
	
	SELECT  fcp.description                 c_desc,
	        fcr.concurrent_program_id       c_pgid,
	        fcr.request_id                  c_rqid,
	        fus.user_name                   c_user,
	        fcr.argument_text               c_args,
	        fcr.resubmit_interval           c_intv,
	        fcr.resubmit_interval_unit_code c_unit
	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',
	       'GERMAN',
	       'FENCH',
	       'BRAZILIAN',
	       'SPANISH')                = fcr.nls_language
	  AND fcp.concurrent_program_id = fcr.concurrent_program_id
	  AND fcr.requested_by          = fus.user_id
	  AND fcr.phase_code            = 'P'
	ORDER BY c_desc, c_rqid;
	EXIT;

END_FILE

print
print "Please see /tmp/whatconcpending_detail_$$.txt"
print
}
