#!/bin/bash
# whatconcprogram_html ()
#------------------------------------------------------------------------------#
#    Module Name:  whatconcprogram_html                                        #
#                                                                              #
#    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_html: Oracle E-Business ConcRequests   #"
        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}

        SET     MARKUP HTML ON SPOOL ON
        SET     NEWPAGE NONE

        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     pagesize        9999

        SPOOL /tmp/whatconcprogram_html_$$.html

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN  c_stop       NOPRINT 
        COLUMN  c_time       NOPRINT

        COLUMN  c_appl  Heading 'Application'         WORD_WRAPPED
        COLUMN  c_desc  Heading 'Concurrent Pgm Desc' WORD_WRAPPED
        COLUMN  c_name  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_html_$$.html"
echo 
}