#!/bin/ksh
#whatwfactivity_detail ()
#------------------------------------------------------------------------------#
#    Module Name:  whatwfactivity_detail                                       #
#                                                                              #
#    Purpose:      Report on the WorkFlow Activity Statuses.                   #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    01-Feb-2007   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  " # whatwfactivity_detail:    Oracle Apps WorFlow Activity #"
	    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

	    echo " Please Enter APVRMDER/APVRMDER/CREATEPO/GLBATCH/"
	    echo "              POAPPRV/POERROR/PORCPT/POREQCHA/PORPOCHA"
	    echo "              POWFRQAG/"
	    read WFSTAT?"              REQAPPRV/WFERROR/WFTESTS:   "
	    echo
	    read WFITEM?"        Enter Workflow Item Key (#####-######): "

	    clear
	else
	    PASSWD=$1
	    WFSTAT=$2
	    WFITEM=$3
	    clear
	fi


#------------------------------------------------------------------------------#
#  Initialize                                                                  #
#------------------------------------------------------------------------------#
	SPOOLNM=/tmp/whatwfactivity_detail_$$.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	wftype	FORMAT A10  HEADING	'Workflow|Item|Type'
	COLUMN	wfbdat	FORMAT A9   HEADING	'WF|Activity|BeginDate|DD-Mon-YY'
	COLUMN	wfedat	FORMAT A9   HEADING	'WF|Activity|EndDate|DD-Mon-YY'
	COLUMN	wfactn	FORMAT A20  HEADING	'Workflow|Activity|Name'
	COLUMN	wfcode	FORMAT A11  HEADING	'WF Item|Activity|Result'
	COLUMN	wfstat	FORMAT A8   HEADING	'WF Item|Activity|Status'
	COLUMN	wfnfid	FORMAT 999999 HEADING	'WF Item|Notify|ID'
	COLUMN	wfnfst	FORMAT A7   HEADING	'Notify|Mailer|Status'

	BREAK on wftype skip 1 on wfbdat;

        TTITLE  CENTER  '${ORASID} (${HNAME}) WF Activity'  -
                LEFT    DATEVAR                                         -
                RIGHT   'Page:'    FORMAT 999 SQL.pno           skip 1  -
                CENTER  'WF_Item_Activity_Statuses ItemType: ${WFSTAT}' -
                LEFT    TIMEVAR                                 skip 1  -
                CENTER  'ItemType: ${WFSTAT}  ItemKey: ${WFITEM}'  skip 2

	SELECT	/* + PARALLEL (wf_item_activity_statuses, 6)  */
        	TO_CHAR(sysdate, 'DD-MON-YY')                   datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')                  timevalue,
		TO_CHAR(WFIAS.begin_date,'DD-MON-RR HH24:MI:SS')	wfbdat,
		TO_CHAR(WFIAS.end_date,'DD-MON-RR HH24:MI:SS') 		wfedat,
		WFATV.name||'/'||WFPRO.instance_label 			wfactn,
		WFIAS.activity_result_code 				wfcode,
		WFIAS.activity_status 					wfstat,
		WFIAS.notification_id 					wfnfid,
		WFNOT.status 						wfnfst
	FROM	wf_item_activity_statuses_h WFIAS,
		wf_process_activities WFPRO,
		wf_activities WFACT,
		wf_activities WFATV,
		wf_items WFITM,
		wf_notifications WFNOT
	WHERE	WFIAS.item_type = '${WFSTAT}'
	and     WFIAS.item_key  = '${WFITEM}'
	and     WFIAS.process_activity   =  WFPRO.instance_id
	and     WFPRO.activity_name      =  WFACT.name
	and     WFPRO.activity_item_type =  WFACT.item_type
	and     WFPRO.process_name       =  WFATV.name
	and     WFPRO.process_item_type  =  WFATV.item_type
	and     WFPRO.process_version    =  WFATV.version
	and     WFITM.item_type          =  '${WFSTAT}'
	and     WFITM.item_key           =  WFIAS.item_key
	and     WFITM.begin_date         >= WFACT.begin_date
	and     WFITM.begin_date      <  nvl(WFACT.end_date, WFITM.begin_date+1)
	and     WFNOT.notification_id(+) = WFIAS.notification_id
	ORDER BY WFIAS.begin_date, WFIAS.execution_time;

EXIT;
END_FILE

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