#!/bin/ksh
#whatwfmailstatus_detail ()
#------------------------------------------------------------------------------#
#    Module Name:  whatwfmailstatus_detail                                     #
#                                                                              #
#    Purpose:      Report on the WorkFlow Mail Status Detail:                  #
#                    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_detail:  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_detail_$$.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

	SPOOL ${SPOOLNM}
        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN col1	NOPRINT NEW_VALUE       TIMEVAR

	COLUMN wfnote	FORMAT 999999	HEADING	'Notify|Mailer|ID'
	COLUMN wfrole	FORMAT A9	HEADING	'Notify|Role'
	COLUMN wfstat	FORMAT A5	HEADING	'WF|Status'
	COLUMN wffusr	FORMAT A8	HEADING	'Notify|From|User'
	COLUMN wftusr	FORMAT A8	HEADING	'Notify|To|User'
	COLUMN wfmsub	FORMAT A14	HEADING	'eMail Subject'
	COLUMN wftype	FORMAT A8	HEADING	'WF|Type'
	COLUMN wfukey	FORMAT A6	HEADING	'Work-|flow|UsrKey'
	COLUMN wfikey	FORMAT A7	HEADING	'Work-|flow|ItemKey'

	TTITLE  CENTER  '${ORASID} (${HNAME}) WF Notification Detail'	-
		LEFT    DATEVAR						-
		RIGHT   'Page:'    FORMAT 999 SQL.pno		skip 1  -
		CENTER  'WF_Notifications Sorted by WFMailer Notification ID' -
		LEFT	TIMEVAR					skip 1	-
		CENTER  'WF_Notification  Status: ${MAILST}'	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,
		from_user		wffusr,
		to_user			wftusr,
		subject			wfmsub,
		message_type		wftype,
		user_key		wfukey,
		item_key		wfikey
	  FROM	wf_notifications
	 WHERE	mail_status LIKE '%$MAILST%'
	ORDER BY notification_id;


EXIT;
END_FILE

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