#!/usr/bin/ksh
#whatconcreqgroup ()
#------------------------------------------------------------------------------#
# Module Name: whatconcreqgroup #
# #
# Purpose: Report on the eBusiness Suite Concurrent Request Groups. #
# #
# Maintenance #
# Date Author Description #
# ----------- ------------ -------------------------------------------- #
# 10-Oct-2009 M.Barone Module design/creation #
#------------------------------------------------------------------------------#
{
 DIRNAM=$(dirname $0)
 ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
 HNAME=$(uname -n)

 #----------------------------------------------------------------------#
 # TEST: Number ($#) of arguments passed to this function? #
 #----------------------------------------------------------------------#
 if [ $# = 0 ]
 then
 clear
 echo
 echo " #--------------------------------------------------------#"
 echo " # whatconcreqgroup: Oracle Apps Request Groups #"
 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 REPTNM?" Enter the Oracle Conc-Report-Name: "

 clear
 else
 PASSWD=$1
 clear
 fi


#------------------------------------------------------------------------------#
# Initialize #
#------------------------------------------------------------------------------#
 SPOOLNM=/tmp/whatconcreqgroup_$$.txt

#------------------------------------------------------------------------------#
# SQL*Plus ("Here-Document") #
#------------------------------------------------------------------------------#
sqlplus -s /nolog <<-END_FILE

 CONNECT APPS/$PASSWD

 set pagesize 9000
 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 ${SPOOLNM}

 clear BREAKS
 clear BUFFER
 clear COLUMNS
 clear COMPUTES
 clear SCREEN
 clear SQL
 clear TIMING

 prompt #---------------------------------------------------------------#
 prompt #- eBusiness Suite 11i (whatconcreqgroup) -#
 prompt #---------------------------------------------------------------#

 COLUMN datevalue NOPRINT NEW_VALUE DATEVAR
 COLUMN timevalue NOPRINT NEW_VALUE TIMEVAR

 COLUMN rgname FORMAT A20 HEADING 'Request Group Name' WORD_WRAPPED
 COLUMN dsname FORMAT A30 HEADING 'ConcReport Name' WORD_WRAPPED
 COLUMN flname FORMAT A18 HEADING 'ConcExecution File' WORD_WRAPPED
 COLUMN exname FORMAT A8 HEADING 'Conc|Exec|Name' WORD_WRAPPED

 TTITLE CENTER '${ORASID} (${HNAME}) ConcReport Group' -
 LEFT DATEVAR skip 1 -
 CENTER 'Sorted by ConcReport Name' -
 LEFT TIMEVAR skip 2

 SELECT TO_CHAR(sysdate, 'DD-MON-YY') datevalue,
 TO_CHAR(sysdate, 'HH24:MI:SS') timevalue,
 FRGR.request_group_name rgname,
 FCPM.description dsname,
 FEXE.execution_file_name flname,
 FEXE.executable_name exname
 FROM fnd_request_group_units FRGU,
 fnd_concurrent_programs_vl FCPM,
 fnd_request_groups FRGR,
 fnd_executables FEXE
 WHERE FRGU.request_unit_id =
 FCPM.concurrent_program_id
 AND FRGU.REQUEST_GROUP_ID =
 FRGR.REQUEST_GROUP_ID
 AND FEXE.executable_id =
 FCPM.executable_id
 AND FCPM.description LIKE '%$REPTNM%'
 ORDER BY FRGR.request_group_name;

EXIT;
END_FILE

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