10 Replies Latest reply: Oct 3, 2011 2:37 AM by User317378 RSS

    Export Patch wizard report to excel

    User317378
      Hi,

      Is there a way to export the recommended patches list generated from the patch wizard to excel? I have tried the export to excel option but it failing.

      Thanks,
      Keith
        • 1. Re: Export Patch wizard report to excel
          mybro62-Oracle
          Where are you seeing the export to excel option? What version of EBS are you running?
          • 2. Re: Export Patch wizard report to excel
            User317378
            EBS 12.1.3

            I right click on the grid and select the option to export to excel. I then get then get a popup message that says "This web query returned no data. To change the query, click the arrow......"
            • 3. Re: Export Patch wizard report to excel
              mybro62-Oracle
              Currently this feature has not been implemented - there is an enhancement request pending to add this feature.
              • 4. Re: Export Patch wizard report to excel
                User317378
                Thanks for the update. is there a table that can be queried to generate my own report once the wizard has successfully run?
                • 5. Re: Export Patch wizard report to excel
                  mybro62-Oracle
                  This query will give you a lot less then the number you see as the count...
                  This is due to the fact that the same bug number can be required for
                  multiple products and for both extended and p1 support.

                  SELECT DISTINCT adb.bug_number
                  FROM ad_appl_tops adt,
                  ad_pm_patches adp,
                  ad_pa_analysis_run_bugs adb,
                  ad_snapshots ads,
                  ad_pm_patch_type_map apptm,
                  AD_PA_CRITERIA_PROD_FAMS apcpf,
                  ad_pm_prod_family_map appfm
                  WHERE adb.analysis_status IN ('MISSING','READY','APPLIED')
                  AND ads.SNAPSHOT_TYPE = 'G'
                  AND adt.NAME ='GLOBAL'
                  AND adb.analysis_run_id=[REQUEST ID FROM  RESULTS RIGHT AFTER FILTER NAME]
                  AND adb.appl_top_id = adt.APPL_TOP_ID
                  AND ads.appl_top_id = adt.APPL_TOP_ID
                  AND adb.bug_number = adp.BUG_NUMBER
                  AND apptm.bug_number = adb.bug_number
                  AND apptm.analysis_run_id=adb.analysis_run_id
                  AND apcpf.advisor_criteria_id='Support' --UNLESS THEY CREATED THEIR OWN FILTER
                  AND apcpf.RECOMMENDED_PATCH_FLAG in ('S', 'Y')
                  AND appfm.product_family_abbreviation=
                  apcpf.product_family_abbreviation
                  AND (apptm.product = appfm.product_abbreviation OR
                  adp.product_abbreviation=appfm.product_abbreviation)
                  • 6. Re: Export Patch wizard report to excel
                    User317378
                    Hi,

                    I tried running the query provided but i got the below error. It looks like the table ad_pm_patch_type_map doesn't exist.

                    EBS12.1.3
                    DB 11.2.0.2


                     1* select distinct(ANALYSIS_RUN_ID) from ad_pa_analysis_run_bugs
                    SQL> /
                    
                    ANALYSIS_RUN_ID
                    ---------------
                            2989333
                            2991010
                            2989380
                            2989418
                            2897614
                            2960198
                            2676802
                    
                    7 rows selected.
                    
                    SQL> SELECT DISTINCT adb.bug_number
                      2  FROM ad_appl_tops adt,
                      3  ad_pm_patches adp,
                      4  ad_pa_analysis_run_bugs adb,
                      5  ad_snapshots ads,
                      6  ad_pm_patch_type_map apptm,
                      7  AD_PA_CRITERIA_PROD_FAMS apcpf,
                      8  ad_pm_prod_family_map appfm
                      9  WHERE adb.analysis_status IN ('MISSING','READY','APPLIED')
                     10  AND ads.SNAPSHOT_TYPE = 'G'
                     11  AND adt.NAME ='GLOBAL'
                     12  AND adb.analysis_run_id=2991010
                     13  AND adb.appl_top_id = adt.APPL_TOP_ID
                     14  AND ads.appl_top_id = adt.APPL_TOP_ID
                     15  AND adb.bug_number = adp.BUG_NUMBER
                     16  AND apptm.bug_number = adb.bug_number
                     17  AND apptm.analysis_run_id=adb.analysis_run_id
                     18  AND apcpf.advisor_criteria_id='Support' --UNLESS THEY CREATED THEIR OWN FILTER
                     19  AND apcpf.RECOMMENDED_PATCH_FLAG in ('S', 'Y')
                     20  AND appfm.product_family_abbreviation=
                     21  apcpf.product_family_abbreviation
                     22  AND (apptm.product = appfm.product_abbreviation OR
                     23  adp.product_abbreviation=appfm.product_abbreviation)
                     24  /
                    ad_pm_patch_type_map apptm,
                    *
                    ERROR at line 6:
                    ORA-00942: table or view does not exist
                    
                    
                    SQL> desc ad_pm_patch_type_map
                    ERROR:
                    ORA-04043: object ad_pm_patch_type_map does not exist
                    
                    
                    SQL> select * from dba_objects where object_name='AD_PM_PATCH_TYPE_MAP';
                    
                    no rows selected
                    
                    SQL> select * from dba_objects where object_name like '%PATCH_TYPE%';
                    
                    no rows selected
                    • 7. Re: Export Patch wizard report to excel
                      mybro62-Oracle
                      Yes - that query is for 11i - I am working on the R12 query and will post it when I get it.
                      • 9. Re: Export Patch wizard report to excel
                        mybro62-Oracle
                        OK here you go. You need the request id from submit recommend patches - so from recommended patches results page click on the request set id link and then get the Submit Analyze Patches (Wrapper) request id in.


                        Recommended...


                        select
                        adp.bug_number patch_name,
                        adp.product_family_abbreviation ,
                        ad_pa_validate_criteriaset.get_cs_prod_fam_abbr(adp.product_abbreviation) product_family_abbreviation,
                        nvl(adp.product_abbreviation, ' ') ,
                        adp.bug_description ,
                        DECODE(adp.IS_FAMILY_PACK,'Y','Family Pack')||
                        DECODE(adp.IS_MAINT_PACK,'Y','Maintenance Pack')||
                        DECODE(adp.IS_MINI_PACK,'Y','Mini Pack')||
                        DECODE(adp.IS_HIGH_PRIORITY,'Y','High Priority Patch',NVL(adp.patch_type,'User Request')) Patch_Type ,
                        (SELECT COUNT(*) FROM ad_pa_anal_run_bug_prereqs aparbp
                        WHERE adb.analysis_run_bug_id = aparbp.analysis_run_bug_id ) prereq_cnt,
                        DECODE (adh.bug_number, NULL, 'N', 'Y'),
                        adt.name ,
                        adb.analysis_run_id ,
                        analysis_run_bug_id ,
                        ads.snapshot_id ,
                        ad_pa_validate_criteriaset.get_cs_prod_fam_name(adp.product_abbreviation) product_family_name ,
                        appi.product_name ,
                        (SELECT COUNT(*) FROM ad_pa_anal_run_bug_codelevels aparbc
                        WHERE adb.analysis_run_bug_id = aparbc.analysis_run_bug_id ) level_cnt ,
                        decode (adb.analysis_status,'MISSING','Missing','READY','Unapplied','APPLIED','Applied',adb.analysis_status) patch_status ,
                        nvl(decode(adp.has_msi_steps,'Y','Yes','N','No',adp.has_msi_steps), ' ') hasMsiSteps ,
                        adp.baseline ,
                        adp.entity_abbr ,
                        adp.patch_id
                        from
                        ad_pm_patches adp ,
                        ad_appl_tops adt ,
                        ad_pa_analysis_run_bugs adb ,
                        ad_pm_product_info appi ,
                        ad_snapshots ads
                        ,ad_hidden_patches adh
                        where
                        adb.analysis_run_id = 402352 --<requestid>
                        and adb.analysis_status in ('MISSING','READY','APPLIED')
                        and ads.SNAPSHOT_TYPE = 'G'
                        and adt.NAME ='GLOBAL'
                        and adb.RECOMMENDED_PATCH_TYPE in ('1','3')
                        and adb.appl_top_id = adt.APPL_TOP_ID
                        and ads.appl_top_id = adt.APPL_TOP_ID
                        and appi.product_abbreviation = adp.product_abbreviation
                        and adb.bug_number = adp.BUG_NUMBER
                        and adb.baseline = adp.baseline
                        and adp.patch_metadata_key = 'DEFAULT'
                        and (adp.is_high_priority = 'Y' OR adp.is_code_level='N' )
                        and adh.bug_number(+) = adp.BUG_NUMBER
                        order by patch_name


                        New Code ...


                        select
                        adp.bug_number patch_name,
                        adp.product_family_abbreviation ,
                        ad_pa_validate_criteriaset.get_cs_prod_fam_abbr(adp.product_abbreviation) product_family_abbreviation,
                        nvl(adp.product_abbreviation, ' ') ,
                        adp.bug_description ,
                        INITCAP(NVL(adp.patch_type,'User Request')) Patch_Type ,
                        (SELECT COUNT(*) FROM ad_pa_anal_run_bug_prereqs aparbp
                        WHERE adb.analysis_run_bug_id = aparbp.analysis_run_bug_id ) prereq_cnt,
                        DECODE (adh.bug_number, NULL, 'N', 'Y'),
                        adt.name ,
                        adb.analysis_run_id ,
                        analysis_run_bug_id ,
                        ads.snapshot_id ,
                        ad_pa_validate_criteriaset.get_cs_prod_fam_name(adp.product_abbreviation) product_family_name ,
                        appi.product_name ,
                        (SELECT COUNT(*) FROM ad_pa_anal_run_bug_codelevels aparbc
                        WHERE adb.analysis_run_bug_id = aparbc.analysis_run_bug_id ) level_cnt ,
                        decode (adb.analysis_status,'MISSING','Missing','READY','Unapplied','APPLIED','Applied',adb.analysis_status) patch_status ,
                        nvl(decode(adp.has_msi_steps,'Y','Yes','N','No',adp.has_msi_steps), ' ') hasMsiSteps ,
                        adp.baseline ,
                        adp.entity_abbr ,
                        adp.patch_id
                        from
                        ad_pm_patches adp ,
                        ad_appl_tops adt ,
                        ad_pa_analysis_run_bugs adb ,
                        ad_pm_product_info appi ,
                        ad_snapshots ads
                        ,ad_hidden_patches adh
                        where
                        adb.analysis_run_id = 402352 --<request_id>
                        and adb.analysis_status in ('MISSING','READY','APPLIED')
                        and ads.SNAPSHOT_TYPE = 'G'
                        and adt.NAME ='GLOBAL'
                        and adb.appl_top_id = adt.APPL_TOP_ID
                        and ads.appl_top_id = adt.APPL_TOP_ID
                        and appi.product_abbreviation = adp.product_abbreviation
                        and adb.bug_number = adp.BUG_NUMBER
                        and adb.baseline = adp.baseline
                        and adp.patch_metadata_key = 'DEFAULT'
                        and adp.is_code_level = 'Y'
                        and adh.bug_number(+) = adp.BUG_NUMBER
                        order by patch_name
                        • 10. Re: Export Patch wizard report to excel
                          User317378
                          Hi,

                          The scripts worked perfect.

                          Thanks again for your help.

                          Keith