How to get a list of saved queries from Apps10?

hi maja,

you can use this screen

Solution Manager\Reporting\Ad-hoc Reporting\Quick Reports

i am also interested in extracting saved queries (from all users in all different IFS forms).

Saved queries are a core tool for our users to work with IFS. Many times these are pretty complex by using all posibilities of extended search sql statements.

with the upgrade to IFS cloud, we will need to re-implement all these queries in order to provide our users with efficient one-click queries .

The list of saved queries will help to work in a structured approach to re-implement what is necessary and not to forget anything.

I know, saved queries from IFS10 are saved in User profiles. 

Does anybody have a script to extract the information from User profiles?

IFS Saved Searches are saved per user profile, but not in the database.

Using the following SQL, you can see the user, the name of the saved search and the corresponding screen.

select Fndrr_Client_Profile_Api.Get_Owner(profile_id) PROFILE_ID,

       extractvalue(xmltype(PROFILE_BINARY_VALUE), '//NAME', 'xmlns="urn:ifsworld-com:schemas:custom_navigator_node"') SAVED_SEARCH,

       extractvalue(xmltype(PROFILE_BINARY_VALUE), '//URL', 'xmlns="urn:ifsworld-com:schemas:custom_navigator_node"') SAVED_SEARCH_SCREEN

  from FNDRR_CLIENT_PROFILE_VALUE t 

 WHERE PROFILE_BINARY_VALUE LIKE '%/SavedSearches/%' 

Maja, are we talking about Saved Searches or Saved Queries?

As Manulak wrote Saved Searches are user profile related and can be found here:

User defined Queries are also stored User profile related and can be found (as Kevser posted) here:

Cheers

Dirk

SavedSearches can also be queried from UserProfiles with following select:

    SELECT Fndrr_Client_Profile_Api.Get_Owner(t.profile_id) PROFILE_ID,
                 t.*,
                                 t.profile_binary_value as EncodedQuery
          
            FROM FNDRR_CLIENT_PROFILE_VALUE t, FNDRR_CLIENT_PROFILE_TAB s
           WHERE t.profile_id = s.Profile_Id
                --  and s.profile_name = 'IFSAPP-TEST'
                 AND Fndrr_Client_Profile_Api.Get_Owner(s.profile_id) = 'SNIK'
                 AND profile_section LIKE 'User/Windows/Application Data/Search' || '%'
                 AND profile_entry = 'CONDITION';

The query is encoded in CLOB-filed profile_binary_value.

Is there a way to decode the binary value to see the query conditions in readable format?

I am also looking for a way to extract the query conditions from profile_binary_value. If you extract the correct part at the end of the coded (base64) string, utl_encode.base64_decode can be used for decoding, but I have not found a way to get the conditions out of the entire string.