Hi,
I have a query against ads_event (among others). The Object_Name returned is often an Instance Name rather than the originating rpt/doc name. Any info out there on how to get the originating rpt/doc name for those Instance Names?
Thanks!
SELECT
ADS_EVENT.Object_Name,
case when max("TOP_FOLDER"."TOP_FOLDER_NAME") in ('EBI Console','EBI Team','GHX Public Ad-Hoc Reports','GHX Public Official Reports','LCM','Official Customer Distributed Reports','Oracle Apps')
then max("TOP_FOLDER"."TOP_FOLDER_NAME")
else 'User Personal Folder'
end,
COUNT(*),
max(ADS_EVENT.Start_Time),
count( distinct ADS_EVENT.User_Name )
FROM
(
SELECT F1.Object_ID, MAX(F1.Object_Name) TOP_FOLDER_NAME
FROM ADS_EVENT F1 INNER JOIN ADS_OBJECT_TYPE_STR O1 ON F1.Object_Type_ID = O1.Object_Type_ID AND O1.Language = 'EN'
WHERE O1.Object_Type_Name LIKE '%Folder%'
GROUP BY F1.Object_ID
) "TOP_FOLDER" RIGHT OUTER JOIN ADS_EVENT ON (ADS_EVENT.Top_Folder_ID="TOP_FOLDER".Object_ID)
LEFT OUTER JOIN ADS_STATUS_STR ON (ADS_EVENT.Status_ID=ADS_STATUS_STR.Status_ID and ADS_EVENT.Event_Type_ID=ADS_STATUS_STR.Event_Type_ID AND ADS_STATUS_STR.Language='EN')
LEFT OUTER JOIN ADS_EVENT_TYPE_STR ON (ADS_EVENT.Event_Type_ID=ADS_EVENT_TYPE_STR.Event_Type_ID AND ADS_EVENT_TYPE_STR.Language='EN')
WHERE
(
ADS_EVENT.Object_Name <> ''
AND
ADS_EVENT_TYPE_STR.Event_Type_Name IN ( 'View','Run','Refresh' )
AND
ADS_STATUS_STR.Status_Name NOT IN ( 'Job Failed','Refresh Failed','View Failed' )
)
GROUP BY
ADS_EVENT.Object_Name