#!/usr/bin/ksh
# whatconcpending_dcc ()
#------------------------------------------------------------------------------#
#    Module Name:  whatconcpending_dcc                                         #
#                                                                              #
#    Purpose:      Report on the ConcRequest.                                  #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    12-May-2022   DCC           Module design/creation                        #
#                  M.Barone      Module design/creation                        #
#                                                                              #
# FND_CONCURRENT_REQUESTS (Phase_Code):                                        #
#  Completed (C), Pending (P), Running (R), Inactive (P)                       #
#                                                                              #
# FND_CONCURRENT_REQUESTS (Status_Code):                                       #
#  Cancelled (D)                                                               #
#  Disabled                                                                    #
#  Error (E)                                                                   #
#  Inactive (I)                                                                #
#  Normal (C), (I) or (R)                                                      #
#  No Manager                                                                  #
#  Paused (W)                                                                  #
#  Resuming   (I or Q)  And Phase_Code = 'R')                                  #
#  Scheduled  (I or Q)  And Phase_Code = 'P')                                  #
#  Standby    (I or Q)  And Phase_Code = 'P')                                  #
#  Suspended  (S), Terminated (X)                                              #
#  Terminating(T)                                                              #
#  Waiting    (Q)                                                              #
#  Warning    (G)                                                              #
#                                                                              #
#  No Manager,    On Hold: Hold_Flag 'Y'                                       #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)

       	clear
       	echo
       	echo  " #--------------------------------------------------------#"
       	echo  " # whatconcpending_dcc:     Concurrent Requests (Pending) #"
       	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
       	ORANAM=${ORASID}

#------------------------------------------------------------------------------#
#  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    	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 /tmp/whatconcpending_dcc_$$.txt

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

TTITLE  CENTER  'E-Business Concurrent Requests (Pending DCC)' -
                RIGHT   'Page:'         FORMAT 999 SQL.pno      skip 3

        SET PAGESIZE 400

        SELECT (select user_name
           from applsys.fnd_user
          where user_id = FCR.REQUESTED_BY) "Requested By",
        FCR.REQUEST_DATE "Request Date",
        FCR.REQUESTED_START_DATE "Requested Start",
        FCR.ACTUAL_START_DATE "Start",
        FCR.ACTUAL_COMPLETION_DATE "Completion",
        TO_CHAR(EXTRACT(DAY FROM NUMTODSINTERVAL(FCR.ACTUAL_COMPLETION_DATE - FCR.ACTUAL_START_DATE, 'DAY')), 'FM00') || ':' ||
        TO_CHAR(EXTRACT(HOUR FROM NUMTODSINTERVAL(FCR.ACTUAL_COMPLETION_DATE - FCR.ACTUAL_START_DATE, 'DAY')), 'FM00') || ':' ||
        TO_CHAR(EXTRACT(MINUTE FROM NUMTODSINTERVAL(FCR.ACTUAL_COMPLETION_DATE - FCR.ACTUAL_START_DATE, 'DAY')), 'FM00') || ':' ||
        TO_CHAR(EXTRACT(SECOND FROM NUMTODSINTERVAL(FCR.ACTUAL_COMPLETION_DATE - FCR.ACTUAL_START_DATE, 'DAY')), 'FM00') "ElpsTime",
        FCR.PHASE_CODE "PCode",
        DECODE(
                FCR.PHASE_CODE, 'P', 'Pending',
                                'R', 'Running',
                                'I', 'Inactive',
                                'C', 'Complete',
                                'Unknown'
              ) "Phase",
        FCR.STATUS_CODE "SCode",
        DECODE(
                FCR.STATUS_CODE, 'A', 'A Waiting',
                                 'B', 'Resuming',
                                 'C', 'Normal',
                                 'D', 'Cancelled',
                                 'E', 'Error',
                                 'F', 'Scheduled',
                                 'G', 'Warning',
                                 'H', 'On Hold',
                                 'I', 'Normal',
                                 'M', 'No Manager',
                                 'Q', 'Standby',
                                 'R', 'Normal',
                                 'S', 'Suspended',
                                 'T', 'Terminating',
                                 'U', 'Disabled',
                                 'W', 'Paused',
                                 'X', 'Terminated',
                                 'Z', 'Z Waiting',
                                 'Unknown'
              ) "Status",
        FCR.HOLD_FLAG "On Hold",
        FCR.REQUEST_ID "Request ID",
        FCR.PARENT_REQUEST_ID "Parent ID",
        FCR.CONCURRENT_PROGRAM_ID "Program ID",
        FCPR.DESCRIPTION "PrgDesc",
        FCR.DESCRIPTION "ReqDesc",
        (
          SELECT FCPT.USER_CONCURRENT_PROGRAM_NAME
            FROM APPLSYS.FND_CONCURRENT_PROGRAMS_TL FCPT
           WHERE FCPT.LANGUAGE = 'US'
             AND FCPT.APPLICATION_ID = FCR.PROGRAM_APPLICATION_ID
             AND FCPT.CONCURRENT_PROGRAM_ID = FCR.CONCURRENT_PROGRAM_ID
        ) "Program Name",
        (
          SELECT FRT.RESPONSIBILITY_NAME
            FROM APPLSYS.FND_RESPONSIBILITY_TL FRT
           WHERE FRT.LANGUAGE = 'US'
             AND FRT.APPLICATION_ID = FCR.RESPONSIBILITY_APPLICATION_ID
             AND FRT.RESPONSIBILITY_ID = FCR.RESPONSIBILITY_ID
        ) "Responsibility",
       (
         SELECT FCQ.USER_CONCURRENT_QUEUE_NAME
           FROM FND_CONCURRENT_PROCESSES FCP,
                FND_CONCURRENT_QUEUES_TL FCQ
          WHERE FCP.CONCURRENT_PROCESS_ID = FCR.CONTROLLING_MANAGER
            AND (
                  FCQ.APPLICATION_ID = FCP.QUEUE_APPLICATION_ID
                  AND
                  FCQ.CONCURRENT_QUEUE_ID = FCP.CONCURRENT_QUEUE_ID
                )
        ) "Manager"
   FROM APPLSYS.FND_CONCURRENT_REQUESTS FCR,
        APPLSYS.FND_CONCURRENT_PROGRAMS_TL FCPR
  WHERE 1 = 1
    AND FCR.HOLD_FLAG = 'Y'
    AND FCR.STATUS_CODE not in ( 'D', 'W' )
    AND (
          FCPR.APPLICATION_ID = FCR.PROGRAM_APPLICATION_ID
          AND
          FCPR.CONCURRENT_PROGRAM_ID = FCR.CONCURRENT_PROGRAM_ID
        );

	EXIT;

END_FILE

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