#!/usr/bin/ksh
#whatwfsum ()
#------------------------------------------------------------------------------#
#    Module Name:  whatwfsum                                                   #
#                                                                              #
#    Purpose:      Report on the WorkFlow Services (Status).                   #
#                                                                              #
#    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  " # whatwfsum:                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
            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  wfid   format 99999    HEADING 'WF ID'
        COLUMN  wfst   format A10      HEADING 'StartUp|Mode'
        COLUMN  wfsa   format A10      HEADING 'StartUp|Mode'
        COLUMN  wfty   format A20      HEADING 'WF|Component|Type'
        COLUMN  wfnm   format A30      HEADING 'WF Component Name' WORD_WRAPPED

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

        SELECT  component_id            wfid,
                startup_mode            wfst,
                component_status        wfsa,
                component_type          wfty,
                        component_name  wfnm
        FROM   fnd_svc_components
        ORDER BY component_id;


PROMPT
PROMPT
TTITLE CENTER '${ORASID} (${HNAME}) WorkFlow Notification' skip 2
PROMPT ======================================
PROMPT == Quick WF_NOTIFICATIONS Row Count ==
PROMPT ======================================

SELECT /* + PARALLEL (wf_notifications, 4) */
distinct(mail_status), count(*)
FROM  wf_notifications
GROUP BY mail_status;

EXIT;
END_FILE

echo
}