#!/usr/bin/ksh
#whatcolumn ()
#------------------------------------------------------------------------------#
#    Module Name:  whatcolumn                                                  #
#                                                                              #
#    Purpose:      Report on the Tables with COLUMN-NAME.                      #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    10-Oct-2009   T.Robinette   Module design/creation                        #
#                  F.Bender      Module design/creation                        #
#                  A.Estrada     Module design/creation                        #
#                  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  " # whatcolumn:              Oracle Apps Table-Column-Name #"
	    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 [7mAPPS[0m Passwd:    "

	    stty echo
	    echo 
	    #------------------------------------------------------------------#
	    #  Trap:  Reset CNTL-C CNTL-D CNTL-\ After Password Protection     #
	    #------------------------------------------------------------------#
	    trap        2
	    trap        3

	    read COLNAM?"        Enter Search-String [7mColumn-Name[0m: "
	    clear
	else
	    PASSWD=$1
	    COLNAM=$2
	    clear
	fi


#------------------------------------------------------------------------------#
#  Initialize                                                                  #
#------------------------------------------------------------------------------#
	SPOOLNM=/tmp/whatcolumn_$$.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 col1	NOPRINT NEW_VALUE       TIMEVAR

	COLUMN c_name	format A38	HEADING	'WF Component Name' WORD_WRAPPED
	COLUMN c_stat	format A10	HEADING	'Status'

        TTITLE  CENTER  '${ORASID} (${HNAME}) Tables with Column-Name' skip 2

	SELECT  DISTINCT(TBL.owner || '.' || TBL.table_name)
	FROM    all_tab_columns  TBL
	WHERE   TBL.column_name  LIKE  '%${COLNAM}%'
	  AND   TBL.owner =
        (SELECT decode(nvl(APP.APPLICATION_SHORT_NAME,'Not Found'),
                'SQLAP','AP','SQLGL','GL','OFA','FA',
                APP.APPLICATION_SHORT_NAME)
         FROM   fnd_oracle_userid         USR,
                fnd_application           APP,
                fnd_product_installations PRD
         WHERE  PRD.application_id      = APP.application_id(+)
           AND  PRD.oracle_id           = USR.oracle_id(+)
           AND  status                  = 'I'
           AND  APP.application_short_name IN 'AR')
	ORDER BY 1;
EXIT;
END_FILE

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

