#!/bin/bash
# whatconcprogram ()
#------------------------------------------------------------------------------#
#    Module Name:  whatconcprogram                                             #
#                                                                              #
#    Purpose:      Report on the ConcRequest.                                  #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    01-Mar-2025   M.Barone      Module design/creation                        #
#                                                                              #
#------------------------------------------------------------------------------#
{
        DIRNAM=$(dirname $0)
        ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
        HNAME=$(uname -n)
                
        clear
        echo
        echo  " #--------------------------------------------------------#"
        echo  " # whatconcprogram:  Oracle E-Business ConcurrentRequests #"
        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 -e -p " Please Enter the Oracle APPS Passwd: " PASSWD 
            
        stty echo
        echo

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

        read -e -p "        Enter the Oracle ConcPgm Like: " CONCPG 

        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        on
        set     flush           on
        set     heading         on
        set     pause           off
        set     space           1
        set     termout         on
        set     verify          on
        set     linesize        80
        set     pagesize        9999

        SPOOL /tmp/whatconcprogram_$$.txt

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN  c_stop       NOPRINT 
        COLUMN  c_time       NOPRINT

        COLUMN  c_appl  FORMAT A15      Heading 'Application' -
                                                        WORD_WRAPPED
        COLUMN  c_desc  FORMAT A30      Heading 'Concurrent Pgm Desc' -
                                                        WORD_WRAPPED
        COLUMN  c_name  FORMAT A30      Heading 'Concurrent Pgm Name' -
                                                        WORD_WRAPPED

        BREAK   ON c_appl               skip 1 ON c_desc skip 0

        TTITLE  CENTER  'Oracle Concurrent Programs'                    -
                LEFT    DATEVAR                                         -
                RIGHT   'Page:'         FORMAT 999 SQL.pno      skip 1  -
                CENTER  'whatconcprogram -- Sorted by Application'      -
                LEFT    TIMEVAR                                 skip 1  -
                CENTER  'ConcProgram Name with the word ${CONCPG}' skip 2

  

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')    datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')   timevalue,
                fan.application_name             c_appl,
                fcp.user_concurrent_program_name c_name,
                fcp.description                  c_desc
        FROM    fnd_concurrent_programs_tl fcp,
                fnd_application_vl fan
        WHERE   (UPPER(fcp.description)                  LIKE UPPER('%${CONCPG}%')
          OR     UPPER(fcp.user_concurrent_program_name) LIKE UPPER('%${CONCPG}%'))
         AND     fcp.application_id      = fan.application_id
        ORDER BY fan.application_name, fcp.user_concurrent_program_name;

EXIT;

END_FILE
echo 
echo  "Please see /tmp/whatconcprogram_$$.txt"
echo 
}