#!/usr/bin/ksh
#whatatg ()
#------------------------------------------------------------------------------#
#    Module Name:  whatatg                                                     #
#                                                                              #
#    Purpose:      Report on the ATG Version                                   #
#                                                                              #
#    Maintenance                                                               #
#    Date          Author        Description                                   #
#    -----------   ------------  --------------------------------------------  #
#    10-Oct-2009   T.Robinette   Module design/creation                        #
#                  F.Bender      Module design/creation                        #
#                  A.Estrada     Module design/creation                        #
#                  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  " # whatatg:   Oracle Application Technology Group Patches #"
            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
        else
            PASSWD=$1
            clear
        fi

#------------------------------------------------------------------------------#
#  Initialize                                                                  #
#------------------------------------------------------------------------------#
        SPOOLNM=/tmp/whatatg_$$.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 ATG (whatatg)                                          -#
        prompt #---------------------------------------------------------------#

        COLUMN  datevalue       NOPRINT NEW_VALUE       DATEVAR
        COLUMN  timevalue       NOPRINT NEW_VALUE       TIMEVAR

        COLUMN  bugnmbr format A8       HEADING 'Patch|Number'      WORD_WRAPPED
        COLUMN  bugdate format A11      HEADING 'Patch|Date'        WORD_WRAPPED
        COLUMN  bugdesc format A50      HEADING 'Patch Description' WORD_WRAPPED
        COLUMN  bugname format A7       HEADING 'Patch|Name'        WORD_WRAPPED

        TTITLE  CENTER  '${ORASID} (${HNAME}) Apps ATG Version'             -
                LEFT    DATEVAR                                     skip 1  -
                CENTER  'eBusiness 12 ATG (Application Technology Group)'   -
                LEFT    TIMEVAR                                     skip 2

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

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')   datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')  timevalue,
                RELEASE_NAME
        from fnd_product_groups;

        COLUMN  bugnmbr format A9       HEADING 'Patch|Number'      WORD_WRAPPED
        COLUMN  bugdate format A11      HEADING 'Patch|Date'        WORD_WRAPPED
        COLUMN  bugdesc format A40      HEADING 'Patch Description' WORD_WRAPPED

        SELECT  TO_CHAR(sysdate, 'DD-MON-YY')   datevalue,
                TO_CHAR(sysdate, 'HH24:MI:SS')  timevalue,
                 BUG_NUMBER                      bugnmbr,
                TO_CHAR(LAST_UPDATE_DATE, 'YYYY-MON-DD') bugdate,
        decode (bug_number,2728236, 'OWF.G INCLUDED IN 11.5.9',
        3031977, 'POST OWF.G ROLLUP 1 - 11.5.9.1',
        3061871, 'POST OWF.G ROLLUP 2 - 11.5.9.2',
        3124460, 'POST OWF.G ROLLUP 3 - 11.5.9.3',
        3171663, '11.5.9 Oracle E-Business Suite Consolidated Update 2',
        3316333, 'POST OWF.G ROLLUP 4 - 11.5.9.4.1',
        3314376, 'POST OWF.G ROLLUP 5 - 11.5.9.5',
        3409889, 'POST OWF.G ROLLUP 5 Consolidated Fixes For OWF.G RUP 5',
        3492743, 'POST OWF.G ROLLUP 6 - 11.5.9.6',
        3868138, 'POST OWF.G ROLLUP 7 - 11.5.9.7',
        3262919, 'FMWK.H',
        3262159, 'FND.H INCLUDE OWF.H',
        3258819, 'OWF.H INCLUDED IN 11.5.10',
        3438354, '11i.ATG_PF.H INCLUDE OWF.H',
        3140000, 'ORACLE APPLICATIONS RELEASE 11.5.10 MAINTENANCE PACK',
        3240000, '11.5.10 ORACLE E-BUSINESS SUITE CONSOLIDATED UPDATE 1',
        3460000, '11.5.10 ORACLE E-BUSINESS SUITE CONSOLIDATED UPDATE 2',
        3480000, 'ORACLE APPLICATIONS RELEASE 11.5.10.2 MAINTENANCE PACK',
        4017300, 'ATG_PF:11.5.10 Consolidated Update (CU1) ATG Product Family',
        4125550, 'ATG_PF:11.5.10 Consolidated Update (CU2) ATG Product Family',
        5121512, 'AOL USER RESPONSIBILITY SECURITY FIXES VERSION 1',
        6008417, 'AOL USER RESPONSIBILITY SECURITY FIXES 2b',
        6047864, 'REHOST JOC FIXES (BASED ON JOC 10.1.2.2) FOR APPS 11i',
        4334965, '11i.ATG_PF.H RUP3',
        4676589, '11i.ATG_PF.H.RUP4',
        5473858, '11i.ATG_PF.H.RUP5',
        5903765, '11i.ATG_PF.H.RUP6',
        6241631, '11i.ATG_PF.H.RUP7',
        4440000, 'Oracle Applications Release 12 Maintenance Pack',
        5082400, '12.0.1 Release Update Pack (RUP1)',
        5484000, '12.0.2 Release Update Pack (RUP2)',
        6141000, '12.0.3 Release Update Pack (RUP3)',
        6435000, '12.0.4 Release Update Pack (RUP4)',
        5907545, 'R12.ATG_PF.A.DELTA.1',5917344, 'R12.ATG_PF.A.DELTA.2',
        6077669, 'R12.ATG_PF.A.DELTA.3',6272680, 'R12.ATG_PF.A.DELTA.4',
        7237006, 'R12.ATG_PF.A.DELTA.6',
        6836355, '12.0.5 Release Update Pack (RUP5)',
        6728000, '12.0.6 Release Update Pack (RUP6)',
        7237006, '12.0.6 Release Update Pack (RUP6)',
        6678700, '12.1.1 Cumulative Update',
        7303030, '12.1.1 Cumulative Update',
        7303033, '12.1.2 Cumulative Update',
        9239090, '12.1.3 Cumulative Update',
        7651091, 'R12.ATG_PF.B.DELTA.2',
        8919491, 'R12.ATG_PF.B.DELTA.3',
        9817770, 'R12.ATG_PF.B.DELTA.3 Consolidated PostPatch',
        9239090, '12.1.3 Cumulative Update',
        17020683, '12.2.3 Cumulative Upgrade',
        17919161, '12.2.4 Cumulative Upgrade',
        17204589, '12.2.5 Consolidated Seed Table Upgrade',
        19676458, '12.2.5 Release Update Pack',
        21900901, '12.2.6 Release Update Pack',
        24690690, '12.2.7 Release Update Pack',
        26787767, '12.2.8 Release Update Pack',
        28840850, '12.2.9 Release Update Pack') bugdesc
        FROM AD_BUGS ADB
        WHERE   ADB.BUG_NUMBER in ('2728236', '3031977', '3061871', '3124460',
        '3316333',  '3314376',  '3409889',  '3492743',  '3262159',  '3262919',
        '3868138',  '3258819',  '3438354',  '3240000',  '3460000',  '3140000',
        '3480000',  '4017300',  '4125550',  '6047864',  '6008417',  '5121512',
        '4334965',  '4676589',  '5473858',  '5903765',  '6241631',  '4440000',
        '5082400',  '5484000',  '6141000',  '6435000',  '5907545',  '5917344',
        '6077669',  '6272680',  '7237006',  '6728000',  '6678700',  '7303030',
        '7303033',  '7651091',  '8919491',  '8917770',  '9239090',  '1720683',
        '17919161', '1724589', '19676458', '21900901', '24690690', '26787767',
        '28840850')
ORDER BY bugdate, bug_number;

END_FILE

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