Track Portal Page where Discoverer Portlet are stored

For customers having discoverer portlets it's an easy task to confirm whether they are executing correctly or not discoverer portlets by querying the PTM5_CACHE table on the Infrastructure. But it could be a nightmare if they have hundreads of these portlets and they want to open it through the Portal site. Indeed, how to find which is the Page where they are stored ? Following is a query that query Portal Page containing Discoverer Portlets :

Select Portal.WWPOB_Page$.Name
from Portal.WWPOB_Page$
where (Portal.WWPOB_Page$.ID ,Portal.WWPOB_Page$.SiteID) in
(Select Portal.WWV_Things.CornerID, Portal.WWV_Things.SiteID
from Portal.WWV_Things
where Portal.WWV_Things.Portlet_Inst_GuID in
(Select Portal.WWPOB_Portlet_Inst$.Portlet_Inst_GuID
from Portal.WWPOB_Portlet_Inst$
where Portal.WWPOB_Portlet_Inst$.Provider_ID =
and Portal.WWPOB_Portlet_Inst$.Portlet_ID = ));

You need to substitute the PORTLET_ID and PROVIDER_ID value with your own value. Those can be obtained with procedure explained above:

1. Login to Portal as the portal or orcladmin user;
2. Navigate to Administer -> Portlets;
3. Select the 'Display Portlet Repository' link;
4. Locate the provider containing your discoverer portlet (this may be under the Portlet Staging Area)
5. When the page with list of portlets (Discoverer Gauges, Discoverer Worksheet, Discoverer List of Worksheets) is displayed, click on the "Edit" link you see on the top right and you will get properties for this portlet (Portlet_id included) ;

To get filter data, pointing to specific workbook, you can query the Infrastructure DB by the following :

Select Distinct CacheTable.Cch_Url, Portal.WWPOB_Page$.Real_Name
from Portal.WWPOB_Page$, Discoverer5.Ptm5_Cache CacheTable
where (Portal.WWPOB_Page$.ID ,Portal.WWPOB_Page$.SiteID) in
(Select Portal.WWV_Things.CornerID, Portal.WWV_Things.SiteID
from Portal.WWV_Things
where Portal.WWV_Things.Portlet_Inst_GuID in
(Select Portal.WWPOB_Portlet_Inst$.Portlet_Inst_GuID
from Portal.WWPOB_Portlet_Inst$ Where Portal.WWPOB_Portlet_Inst$.Ref_Path in
(select Discoverer5.Ptm5_Instance.Ins_Id
from Discoverer5.Ptm5_Instance
where Discoverer5.Ptm5_Instance.Ins_Surrgt_Pk in
(select Discoverer5.Ptm5_Cache.Cch_Ins_Surrgt_Fk
from Discoverer5.Ptm5_Cache
where Discoverer5.Ptm5_Cache.Cch_Ins_Surrgt_Fk = CacheTable.Cch_Ins_Surrgt_Fk
and Discoverer5.Ptm5_Cache.Cch_Surrgt_Pk = CacheTable.Cch_Surrgt_Pk
and Discoverer5.Ptm5_Cache.Cch_Id = CacheTable.Cch_Id
and Discoverer5.Ptm5_Cache.Cch_Url = CacheTable.Cch_Url
and TO_CHAR(CCH_NEXT_UPDATE,'DD-MON-YY') != '02-JUN-01'))))
order by Portal.WWPOB_Page$.Real_Name;

In this case I took out the failing portlets with Update Date = '02-JUN-01'.