#!/usr/bin/ksh
#whatfndlob_user ()
#------------------------------------------------------------------------------#
#    Module Name:  whatfndlob_user                                             #
#                                                                              #
#    Purpose:      Report on the E-Business Suite FND_LOBS (Objects).          #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    22-Aug-2016   K.Kemp        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  " # whatfndlob_user:         Oracle FND_LOBS Large Objects #"
       	    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/whatfndlob_user_$$.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 (whatfndlob_user)                                -#
       	prompt #---------------------------------------------------------------#

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN pernam  FORMAT A19      HEADING 'User|Name'
        COLUMN fnddat  FORMAT A15      HEADING 'FND_LOBS|CreatedDate'
        COLUMN fnddoc  FORMAT 9999999  HEADING 'FND|Document|ID'
        COLUMN fndfil  FORMAT A15      HEADING 'FND_LOBS|File Name'
        COLUMN fndsiz  FORMAT 9999999  HEADING 'FND_LOBS|Size(Mb)'
        COLUMN fnddes  FORMAT A10      HEADING 'FND_LOBS|Description'

        TTITLE  CENTER  '${ORASID} (${HNAME}) FND_LOBS Objects'     -
                LEFT    DATEVAR                                     skip 1    -
               	CENTER  'Sorted by ProfileName'                               -
                LEFT    TIMEVAR                                     skip 2

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')          	 datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')         	 timevalue,
       		pp.full_name   				 pernam,
       		TO_CHAR (fd.creation_date,'DD-MON-YYYY') fnddat,
       		fd.document_id 				 fnddoc,
       		fl.file_name   				 fndfil,
       		round(dbms_lob.getlength(fl.file_data)/1024/1024,1) fndsiz,
       		fdt.description                          fnddes
       	FROM   	fnd_documents    fd,
       		fnd_documents_tl fdt,
       		fnd_lobs         fl,
       		fnd_user         fu,
                per_people_x     pp
       	WHERE  	fd.document_id = fdt.document_id
       	  AND  	fdt.media_id = fl.file_id(+)
       	  AND  	fd.created_by = fu.user_id
       	  AND   fu.employee_id = pp.person_id(+)
       	ORDER BY  pp.full_name, fd.creation_date desc;

EXIT;
END_FILE

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