whatpatchmodule

What EBusiness Suite Modules/Products are Updated by this Patch ?

Example: 

#---------------------------------------------------------------#

#- What Patch Modules Were Updated by Patch (whatpatchmodules) -#

#---------------------------------------------------------------#

               OATC (oratest) Apps What Patch Modules

             EBusiness Patch 16052604 Updated These Products

Product

ShortName

------------------------------

iby

#!/usr/bin/ksh

#whatpatchmodules ()

#-----------------------------------------------------------------------#

#    Module Name:  whatpatchmodule   (c) Copyright May 2012             #

#                                                                       #

#    Purpose:      Report on Modules/Products Updated by the Patch.     #

#                                                                       #

#    Maintenance                                                        #

#    Date          Author        Description                            #

#    -----------   ------------  -------------------------------------- #

#    10-May-2012   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  " # whatpatchmodule:       What Modules were updated #"

            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 Passwd Protection  #

            #-----------------------------------------------------------#

            trap        2

            trap        3

            read PTCHNM?" Please Enter the Patch-Number/Partial-Number: "

            clear

        else

            PASSWD=$1

            clear

        fi

#-----------------------------------------------------------------------#

#  Initialize                                                           #

#-----------------------------------------------------------------------#

        SPOOLNM=/tmp/whatpatchmodule_$$.txt

#-----------------------------------------------------------------------#

#  SQL*Plus ("Here-Document")                                           #

#-----------------------------------------------------------------------#

sqlplus  -s  /nolog  <<-END_FILE

        CONNECT  APPS/$PASSWD

        SPOOL ${SPOOLNM}

        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

        prompt #--------------------------------------------------------#

        prompt #- What Patch Modules Were Updated    (whatpatchmodule) -#

        prompt #--------------------------------------------------------#

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR

        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        TTITLE  CENTER  '${ORASID} (${HNAME}) Apps What Patch Modules'  -

                LEFT                                            skip 1  -

                CENTER  'EBusiness Patch ${PTCHNM} Updated Products'    -

                LEFT                                            skip 2

        alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

        COLUMN prodct   FORMAT A30 HEADING 'Product|ShortName'

        SELECT  DISTINCT(aprb.application_short_name) prodct

        FROM    ad_applied_patches      aap,

                ad_patch_drivers        apd,

                ad_patch_runs           apr,

                ad_patch_run_bugs       aprb

        WHERE   aap.applied_patch_id    = apd.applied_patch_id

          AND   apd.patch_driver_id     = apr.patch_driver_id

          AND   apr.patch_run_id        = aprb.patch_run_id

          AND   aprb.applied_flag       = 'Y'

          AND   aap.patch_name          = '${PTCHNM}';

END_FILE

print

print  "Please see  /tmp/whatpatchmodule_$$.txt"

print

}