#!/usr/bin/ksh
#whatwfstatus ()
#------------------------------------------------------------------------------#
#    Module Name:  whatconc_wfpurge                                            #
#                                                                              #
#    Purpose:      Report on the ConcReqs that Purge WorkFlows                 #
#                                                                              #
#    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  " # whatconc_wfpurge:   Oracle Apps ConcReq WorkFlow Purge #"
	    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 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/whatconc_wfpurge_$$.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

        TTITLE  CENTER  '${ORASID} (${HNAME}) ConcReq Workflow Purge'   -
                LEFT    DATEVAR                                         -
                RIGHT   'Page:'    FORMAT 999 SQL.pno           skip 1  -
		CENTER  ‘whatconc_wfpurge ConcReq Currently Scheduled’   -
                LEFT    TIMEVAR                                 skip 2


	COLUMN	NAME          FORMAT A18    HEADING "ConcReq Name"
	COLUMN	INTERNAL      FORMAT A8     HEADING "Internal|Name"
	COLUMN	DESCRIPTION   FORMAT A20    HEADING "Description"
	COLUMN	PARAMETER     FORMAT A10    HEADING "PARAMETER"
	COLUMN	DEFAULT_VALUE FORMAT A10    HEADING "Default|Value"
	COLUMN	ENABLED_FLAG  FORMAT A4     HEADING "Enab|led"
	COLUMN	REQUIRED_FLAG FORMAT A4     HEADING "Reqd"
 
	SELECT	p.USER_CONCURRENT_PROGRAM_NAME "NAME", 
		c.CONCURRENT_PROGRAM_NAME "INTERNAL",
		f.END_USER_COLUMN_NAME "PARAMETER",
		f.DEFAULT_VALUE,
		f.ENABLED_FLAG,
		f.REQUIRED_FLAG,
		f.DESCRIPTION
	FROM	FND_DESCR_FLEX_COL_USAGE_VL f,
		FND_CONCURRENT_PROGRAMS_TL p,
		FND_CONCURRENT_PROGRAMS c
	WHERE  substr(f.DESCRIPTIVE_FLEXFIELD_NAME,7,8) = c.CONCURRENT_PROGRAM_NAME
  	AND	c.CONCURRENT_PROGRAM_ID = p.CONCURRENT_PROGRAM_ID
	--AND	p.USER_CONCURRENT_PROGRAM_NAME LIKE '%Workflow%' 
  	AND	(f.DESCRIPTIVE_FLEXFIELD_NAME like '$SRS$.FNDWFPR%') 
  	AND	p.LANGUAGE = 'US'
	ORDER BY f.DESCRIPTIVE_FLEXFIELD_NAME, f.COLUMN_SEQ_NUM;


EXIT;
END_FILE

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

