#!/usr/bin/ksh
#whatwfsum ()
#------------------------------------------------------------------------------#
#    Module Name:  whatwfsum                                                   #
#                                                                              #
#    Purpose:      Report on the WorkFlow Services (Status).                   #
#                                                                              #
#    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                        #
#------------------------------------------------------------------------------#
{
       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  " # whatwfsum:               Oracle Apps WorkFlow Services #"
	    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 Passwd: "

	    stty echo
	    echo 
	    #------------------------------------------------------------------#
	    #  Trap:  Reset CNTL-C CNTL-D CNTL-\ After Password Protection     #
	    #------------------------------------------------------------------#
	    trap        2
	    trap        3
	    clear
	else
	    PASSWD=$1
	    clear
	fi


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

#------------------------------------------------------------------------------#
#  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    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}
       COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
       COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

       COLUMN col1	NOPRINT NEW_VALUE       TIMEVAR

	COLUMN c_name	format A38	HEADING	'WF Component Name' WORD_WRAPPED
	COLUMN c_stat	format A10	HEADING	'Status'

       TTITLE  CENTER  '${ORASID} (${HNAME}) WorkFlow Services Summary' skip 2

	SELECT	DISTINCT
		'1' col1,
		component_name c_name,
		(CASE	component_status
		   WHEN 	'RUNNING'
		   THEN		'Up'
		   ELSE		'Down'
		 END) c_stat
	FROM	fnd_svc_components
	WHERE	startup_mode		= 'AUTOMATIC'
	AND	component_status	= 'RUNNING'
	AND	component_type 		= 'WF_MAILER'
	UNION ALL
	SELECT	'2' col1,
		'Agent Listeners',
		(CASE count(*)
		   WHEN 7
		   THEN		'Up'
		   ELSE		'Down'
		   END) c_stat
	FROM	fnd_svc_components
	WHERE	startup_mode		= 'AUTOMATIC'
	AND	component_status	= 'RUNNING'
	AND	component_type like 'WF%AGENT%'
	UNION ALL
	SELECT '3' col1,
		'Service Components',
		(CASE count(*)
		   WHEN 8
		   THEN		 'Up'
		   ELSE		'Down'
		   END) c_stat
	FROM	fnd_svc_components
	WHERE	startup_mode = 'AUTOMATIC'
	AND	component_status = 'RUNNING'
	UNION ALL
	SELECT '4' col1,
		fcpv.user_concurrent_program_name c_name,
		(CASE
		  WHEN count(fcpv.user_concurrent_program_name) >= 1
		  THEN		'Up'
		  ELSE		'Down'
		  END) c_stat
	FROM	apps.fnd_concurrent_requests fcr,
		apps.fnd_concurrent_programs_vl fcpv,
		apps.fnd_lookup_values flv_phase,
		apps.fnd_lookup_values flv_status,
		apps.fnd_user fu
	WHERE	1=1
	AND	fcr.concurrent_program_id=fcpv.concurrent_program_id
	AND	fcr.phase_code = flv_phase.lookup_code(+)
	AND	'CP_PHASE_CODE' = flv_phase.lookup_type(+)
	AND	283 = flv_phase.view_application_id(+)
	AND	userenv('LANG') = flv_phase.language(+)
	AND	fcr.status_code = flv_status.lookup_code(+)
	AND	'CP_STATUS_CODE' = flv_status.lookup_type(+)
	AND	283 = flv_status.view_application_id(+)
	AND	userenv('LANG') = flv_status.language(+)
	AND	fcr.requested_by=fu.user_id
	AND	fcr.phase_code IN ('R', 'P')   -- Running and Pending
	AND	fcr.resubmit_interval is not null
	AND	fcpv.concurrent_program_name like 'FNDWF%'
	GROUP BY fcpv.user_concurrent_program_name
	ORDER BY col1;


EXIT;
END_FILE

echo
}
