E-Business Suite: whatresponsibility: Report on the eBusiness Suite Responsibilities for a User.

What E-Busineess Responsilities are Assigned to an E-Business Suite User:


#!/usr/bin/ksh
#whatresponsibility ()
#-------------------------------------------------------------------------#
#    Module Name:  whatresponsibility  (c) Copyright May 2012             #
#                                                                         #
#    Purpose:      Report on the eBusiness Responsibilities for a User.   #
#                                                                         #
#    Maintenance                                                          #
#    Date          Author        Description                              #
#    -----------   ------------  ---------------------------------------- #
#    10-May-2012   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  " # whatresponsibility:        Responsibility/End_Date #"
            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 USRNAM?"     Enter the E-Business UserName (Ex. BARONE): "

            clear
        else
            PASSWD=$1
            USRNAM=$2
            clear
        fi


#-------------------------------------------------------------------------#
#  Initialize                                                             #
#-------------------------------------------------------------------------#
        SPOOLNM=/tmp/whatresponsibility_$$.txt

#-------------------------------------------------------------------------#
#  SQL*Plus ("Here-Document")                                             #
#-------------------------------------------------------------------------#
sqlplus  -s  /nolog  <<-END_FILE

        CONNECT  APPS/$PASSWD

        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}

        clear   BREAKS
        clear   BUFFER
        clear   COLUMNS
        clear   COMPUTES
        clear   SCREEN
        clear   SQL
        clear   TIMING

        prompt #----------------------------------------------------------#
        prompt #- E-Business Suite (whatresponsibility)                  -#
        prompt #----------------------------------------------------------#

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN uname    FORMAT A11     HEADING 'User Name'
        COLUMN aname    FORMAT A19     HEADING 'Application Name'
        COLUMN rname    FORMAT A20     HEADING 'Responsibilty Name'
        COLUMN resid    FORMAT 99999   HEADING 'Resp|ID'
        COLUMN reddt    FORMAT A9      HEADING 'FND_Resp|End_Date'
        COLUMN weddt    FORMAT A9      HEADING 'WorkFlow|UserRole|End_Date'

        TTITLE  CENTER  '${ORASID} (${HNAME}) Responsibility/Dates' -
                LEFT    DATEVAR                           skip 1    -
                CENTER  'Sorted by Responsibility Name'             -
                LEFT    TIMEVAR                           skip 2

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')           datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')          timevalue,
                FUS.user_name                           uname,   
                FAP.application_name                    aname,
                FRE.responsibility_name                 rname,
                FRE.responsibility_id                   resid,
                FRE.end_date                            reddt,
                WFU.end_date                            weddt
        FROM    apps.fnd_user                   FUS,
                apps.fnd_responsibility_vl      FRE,
                apps.fnd_application_tl         FAP,
                apps.wf_local_roles             WFL,
                apps.wf_user_role_assignments   WFU
        WHERE   FUS.user_name           = WFU.user_name
        AND     WFU.role_name           = WFL.name
        AND     WFL.display_name        = FRE.responsibility_name
        AND     FAP.application_id      = FRE.application_id
        AND     WFU.user_name           LIKE  '%${USRNAM}%'
        AND     FAP.language            = 'US'
        ORDER BY FUS.user_name, FRE.responsibility_name;

EXIT;
END_FILE

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