#!/usr/bin/ksh
#whatwfinvalid ()
#------------------------------------------------------------------------------#
#    Module Name:  whatwfinvalid                                               #
#                                                                              #
#    Purpose:      Report on the WorkFlows are Invalid.                        #
#                                                                              #
#    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  " # whatwfinvalid:            Oracle Apps WorFlows Invalid #"
            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
            clear
        else
            PASSWD=$1
            clear
        fi


#------------------------------------------------------------------------------#
#  Initialize                                                                  #
#------------------------------------------------------------------------------#
        SPOOLNM=/tmp/whatwfinvalid_$$.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	wfown	format A10      HEADING 'WF Owner'
        COLUMN	wfnam   format A55      HEADING 'WF Name'
        COLUMN	wftyp	format A10      HEADING 'WF Type'

	TTITLE  CENTER  '${ORASID} (${HNAME}) Invalid WorkFlow Objects' skip 1 -
	        CENTER  'WF Objects Found in DBA_Invalid_Objects'       skip 2

	SELECT	owner			wfown,
		object_name		wfnam,
		object_type		wftyp
	FROM    dba_invalid_objects
	WHERE	object_name like 'WF_%'
	ORDER BY 1,2;

EXIT;
END_FILE

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