#!/bin/ksh
#whatwfmailstatus_summary ()
#------------------------------------------------------------------------------#
#    Module Name:  whatwfmailstatus_summaryHTML                                #
#                                                                              #
#    Purpose:      Report on the WorkFlow Mail Status Summary:                 #
#                    MAIL    Ready to Send eMail                               #
#                    SENT    Already Sent eMail                                #
#                    ERROR                                                     #
#    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  " # whatwfmailstatus_summary: Oracle Apps WorFlow 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 APPS Passwd:   "

	    stty echo
	    echo
	    #------------------------------------------------------------------#
	    #  Trap:  Reset CNTL-C CNTL-D CNTL-\ After Password Protection     #
	    #------------------------------------------------------------------#
	    trap        2
	    trap        3

	    read MAILST?" Please Enter MAIL/SENT/ERROR/%:  "
	    clear
	else
	    PASSWD=$1
	    MAILST=$2
	    clear
	fi


#------------------------------------------------------------------------------#
#  Initialize                                                                  #
#------------------------------------------------------------------------------#
	SPOOLNM=/tmp/whatwfmailstatus_summary_$$.html

#------------------------------------------------------------------------------#
#  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 markup HTML on  SPOOL on
	set newpage  none
	set pagesize 9999

	SPOOL ${SPOOLNM}

PROMPT ============================================================
PROMPT E-Business OPEN WF_Notification With Mail_Status = MAIL
PROMPT ============================================================

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN col1	NOPRINT NEW_VALUE       TIMEVAR

        COLUMN wfnote   FORMAT 9999999  HEADING 'Notify|Mailer|ID'
        COLUMN wfrole   FORMAT A20      HEADING 'Notify|Role'
        COLUMN wfstat   FORMAT A6       HEADING 'WF|Status'
        COLUMN wfmail   FORMAT A6       HEADING 'Notify|Mail|Status'
        COLUMN wffusr   FORMAT A20      HEADING 'Notify|From|User'
        COLUMN wftusr   FORMAT A20      HEADING 'Notify|To|User'
        COLUMN wfmsub   FORMAT A40      HEADING 'eMail Subject'
        COLUMN wfbdat   FORMAT A9       HEADING 'WFMail|Begin|Date'
        COLUMN wfedat   FORMAT A9       HEADING 'WFMail|End|Date'

        TTITLE  CENTER  '${ORASID} (${HNAME}) WF Notification Summary'	-
                LEFT    DATEVAR                                    	-
                RIGHT   'Page:'    FORMAT 999 SQL.pno		skip 1	-
                CENTER  'WF_Notifications Sorted by WFMailer Notification ID' -
                						skip 1  -
                CENTER  'WF_Notification  Status: ${MAILST}' 		-
                LEFT    TIMEVAR					skip 2

	SELECT  TO_CHAR(sysdate, 'DD-MON-YY')			datevalue,
		TO_CHAR(sysdate, 'HH24:MI:SS')			timevalue,
		notification_id					wfnote,
		recipient_role					wfrole,
		status						wfstat,
		mail_status					wfmail,
		from_user					wffusr,
		to_user						wftusr,
		subject						wfmsub,
		TO_CHAR(begin_date, 'DD-MON-YY HH:MI:SS')	wfbdat
	  FROM	wf_notifications
	 WHERE	mail_status LIKE '%$MAILST%'
	ORDER BY notification_id;


	SET markup HTML off

EXIT;
END_FILE

print
print  "Please see  /tmp/whatwfmailstatus_summary_$$.html"
print
}