BannerUpdatesAndPatches.sql

select * from (
    select 'GENERAL' Module, GURVERS_RELEASE Version, GURVERS_STAGE_DATE Installed, '' Comments
    from GURVERS
    union
    select 'ACCOUNTS RECEIVABLE' Module, TURVERS_RELEASE Version, TURVERS_STAGE_DATE Installed, '' Comments
    from TURVERS
    union
    select 'STUDENT' Module, SURVERS_RELEASE Version, SURVERS_STAGE_DATE Installed, '' Comments
    from SURVERS
    union
    select 'FINANCIAL AID' Module, RURVERS_RELEASE Version, RURVERS_STAGE_DATE Installed, '' Comments
    from RURVERS
    union
    select 'WEB TAILOR' Module, TWGRVERS_RELEASE Version, TWGRVERS_STAGE_DATE Installed, '' Comments
    from TWGRVERS
    union
    select 'WEB GENERAL' Module, BWGVERS_RELEASE Version, BWGVERS_STAGE_DATE Installed, '' Comments
    from BWGVERS
    union
    select 'POSITION CONTROL' Module, NURVERS_RELEASE Version, NURVERS_STAGE_DATE Installed, '' Comments
    from NURVERS
    union
    select 'HUMAN RESOURCES' Module, PURVERS_RELEASE Version, PURVERS_STAGE_DATE Installed, '' Comments
    from PURVERS
    union
    select 'FINANCE' Module, FURVERS_RELEASE Version, FURVERS_STAGE_DATE Installed, '' Comments
    from FURVERS
    union
    select 'FINANCE SELF SERVICE' Module, BWFVERS_RELEASE Version, BWFVERS_STAGE_DATE Installed, '' Comments
    from BWFVERS
    union
    select 'ADVANCEMENT' Module, AURVERS_RELEASE Version, AURVERS_STAGE_DATE Installed, '' Comments
    from AURVERS
    union
    select 'ADVANCEMENT Self Service' Module, BWAVERS_RELEASE Version, BWAVERS_STAGE_DATE Installed, '' Comments
    from BWAVERS
    union
    select 'STUDENT SELF SERVICE' Module, BWSVERS_RELEASE Version, BWSVERS_STAGE_DATE Installed, '' Comments
    from BWSVERS
    union
    select 'FINANCIAL AID SELF SERVICE' Module, BWRVERS_RELEASE Version, BWRVERS_STAGE_DATE Installed, '' Comments
    from BWRVERS
    union
    select 'FACULTY AND ADVISORS SELF SERVICE' Module, BWLVERS_RELEASE Version, BWLVERS_STAGE_DATE Installed, '' Comments
    from BWLVERS
    union
    select 'EMPLOYEE SELF SERVICE' Module, BWPVERS_RELEASE Version, BWPVERS_STAGE_DATE Installed, '' Comments
    from BWPVERS
    union
    SELECT 
        case SUBSTR(GURPOST_PATCH, REGEXP_INSTR(GURPOST_PATCH, '_([A-Z,a-z]{3})', 1,
            REGEXP_COUNT(GURPOST_PATCH, '_([A-Z,a-z]{3})', 1, 'i'), 0, 'i') + 1, 3)
            when 'gen' then 'General'
            when 'ars' then 'Accounts Receivable'
            when 'tas' then 'Accounts Receivable'
            when 'stu' then 'Student'
            when 'res' then 'Financial Aid'
            when 'twb' then 'Web Tailor'
            when 'bwg' then 'Web General'
            when 'pos' then 'Position Control'
            when 'pay' then 'Human Resources'
            when 'fin' then 'Finance'
            when 'alu' then 'Advancement'
            when 'bwa' then 'Advancement Self Service'
            when 'bws' then 'Student Self Service'
            when 'bwr' then 'Financial Aid Self Service'
            when 'bwl' then 'Faculty and Advisors Self Service'
            when 'bwp' then 'Employee Self Service'
            when 'int' then 'Integration Components'
            else 'other'
        end module,
        gurpost_patch version, gurpost_applied_date Installed, gurpost_comment Comments
    FROM gurpost
)
where TRUNC (Installed) > sysdate - 365
ORDER BY 3 desc;

Banner Updates and Patches

This query consolidates information from the Version tables (*VERS) and the patch table (GURPOST) into a report of all updates and patches in the order they were applied starting with the most recent.

You can change default period of one year in the where clause.