#!/usr/bin/ksh
#whatwfrole ()
#------------------------------------------------------------------------------#
#    Module Name:  whatwfrole                                                  #
#                                                                              #
#    Purpose:      Report on the WorkFlow Role Validation is Needed.           #
#                                                                              #
#    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  " # whatwfrole:       Oracle Apps WorFlow Role Validation. #"
            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/whatwfrole_$$.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        on
        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  uname   FORMAT  A20     HEADING 'User Name'
        COLUMN  rname   FORMAT  A40     HEADING 'WorkFlow Role Name'

        TTITLE  CENTER  '${ORASID} (${HNAME}) WorkFlow Role Validation ' skip 2

        prompt  WF_USER_ROLES Invalid (User / Role) Foreign Key
        prompt  Please execute ConcRequest:
        prompt  Workflow Directory Services User/Role Validation

        SELECT  user_name               uname,
                role_name               rname
        FROM    WF_USER_ROLES
        WHERE   not exists
                (SELECT null
                 FROM   WF_USERS
                 WHERE  USER_NAME = NAME
                 AND    USER_ORIG_SYSTEM = ORIG_SYSTEM
                 AND    USER_ORIG_SYSTEM_ID = ORIG_SYSTEM_ID)
                 OR     not exists
                (SELECT null
                 FROM   WF_ROLES
                 WHERE  ROLE_NAME = NAME
                 AND    ROLE_ORIG_SYSTEM = ORIG_SYSTEM
                 AND    ROLE_ORIG_SYSTEM_ID = ORIG_SYSTEM_ID)
                ORDER BY user_name,role_name;
                 

EXIT;
END_FILE

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