#!/usr/bin/ksh
# whatconcqueue ()
#-------------------------------------------------------------------------#
#    Module Name:  whatconcqueue  (c) Copyright May 2012                  #
#                                                                         #
#    Purpose:      Report on the Concurrent Queues.                       #
#                                                                         #
#    Maintenance                                                          #
#    Date          Author        Description                              #
#    -----------   ------------  ---------------------------------------  #
#    10-May-2012   M.Barone      Module design/creation                   #
#-------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)
                
	clear
	echo
	echo  " #---------------------------------------------------------#"
	echo  " # whatconcqueue:            Oracle Apps Concurrent Queues #"
	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/whatconcqueue_$$.txt

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN  cqdes   FORMAT A20   Heading 'Concurrent|Queue|Description' -
        COLUMN  cqnam   FORMAT A10   Heading 'Concurrent|Queue|Name'
        COLUMN  cqena   FORMAT A8    Heading 'Queue|Enable|Disable'
        COLUMN  cqsta   FORMAT A7    Heading 'Queue|Status'
        COLUMN  cqmax   FORMAT 9999  Heading 'Conc|Queue|Max'
        COLUMN  cqrun   FORMAT 99999 Heading 'Conc|Queue|Runing'
        COLUMN  cqtar   FORMAT 99999 Heading 'Conc|Queue|Target'
        COLUMN  cqnod   FORMAT A7    Heading 'Conc|Queue|Node'
        COLUMN  cqdia   FORMAT A4    Heading 'Conc|Diag|Lvl'

        BREAK   ON c_desc               skip 1 ON c_sbmt skip 0

        TTITLE  CENTER  'eBusiness Concurrent Queues'	              -
        LEFT    DATEVAR                                              -
        RIGHT   'Page:'         FORMAT 999 SQL.pno      skip 1 -
                CENTER  'whatconcqueue -- Sorted by Queue-Name' 	-
                LEFT    TIMEVAR                                 skip 2

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')   datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')  timevalue,
                user_concurrent_queue_name	cqdes,
                concurrent_queue_name		cqnam,
                DECODE(enabled_flag,
                     'Y', 'Enabled',
                     'N', 'Disabled',
                     enabled_flag)              cqena,
                max_processes	              cqmax,
                running_processes		       cqrun,
                target_processes		       cqtar,
                node_name			       cqnod,
                diagnostic_level		       cqdia
       FROM     fnd_concurrent_queues_vl
       ORDER BY user_concurrent_queue_name; 

EXIT;

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