#!/bin/ksh
#whatwfactivity_summary ()
#------------------------------------------------------------------------------#
#    Module Name:  whatwfactivity_summary                                      #
#                                                                              #
#    Purpose:      Report on the WorkFlow Activity Summary.                    #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    01-Feb-2007   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  " # whatwfactivity_summary:   Oracle Apps WorFlow Activity #"
	    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

	    echo " Please Enter APVRMDER/APVRMDER/CREATEPO/GLBATCH/"
	    echo "              POAPPRV/POERROR/PORCPT/POREQCHA/PORPOCHA"
	    echo "              POWFRQAG/"
	    read WFTYPE?"              REQAPPRV/WFERROR/WFTESTS/%:  "

	    clear
	else
	    PASSWD=$1
	    WFSTAT=$2
	    clear
	fi


#------------------------------------------------------------------------------#
#  Initialize                                                                  #
#------------------------------------------------------------------------------#
	SPOOLNM=/tmp/whatwfactivity_summary_$$.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	wfstat	FORMAT A10	HEADING	'Workflow|Type'
	COLUMN	wfcode	FORMAT A38	HEADING	'WF Message Activity Code'
	COLUMN	wfcntr	FORMAT 9999999	HEADING	'Count'

	BREAK on wftype skip 0 on wfstat skip 1 on wfcode;

        TTITLE  CENTER  '${ORASID} (${HNAME}) WF Activity’  -
                LEFT    DATEVAR                                         -
                RIGHT   'Page:'    FORMAT 999 SQL.pno           skip 1  -
                CENTER  'WF_Item_Activity_Statuses Sorted by ItemType'  -
                LEFT    TIMEVAR                                 skip 2

	SELECT	/* + PARALLEL (wf_item_activity_statuses, 6)  */
        	TO_CHAR(sysdate, 'DD-MON-YY')                   datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')                  timevalue,
		WFS.ITEM_TYPE			wftype,
		WFS.ACTIVITY_STATUS		wfstat,
		WFS.ACTIVITY_RESULT_CODE	wfcode,
		count(*)			wfcntr
	  FROM	WF_ITEM_ACTIVITY_STATUSES	WFS
	WHERE	WFS.ITEM_TYPE	LIKE  '%${WFTYPE}%'
 	GROUP BY WFS.ITEM_TYPE, WFS.ACTIVITY_STATUS, WFS.ACTIVITY_RESULT_CODE
	ORDER BY WFS.ITEM_TYPE, WFS.ACTIVITY_STATUS, WFS.ACTIVITY_RESULT_CODE;

EXIT;
END_FILE

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